Tuesday, November 13, 2012

How to migrate NON ASM (datafiles, redologs, controlfiles, spfile, archive logs) to ASM and back

Today we will demonstrate migration NON ASM to ASM storage.

We have already ASM instance and 2 asm diskgroups DATA and FLSHBK


Our database name is mydb and it works Archivelog mode.

Be sure block change tracking is disabled

SQL> select status from v$block_change_tracking;

STATUS
----------
DISABLED

SQL>

If no, disable it

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Database altered.

SQL>


Firstly check filenames on the database using this SQL:


SQL> select member from v$logfile
union
select file_name from dba_data_files
union
select name from v$controlfile
union
select value from v$parameter where name='spfile' order by 1;  2    3    4    5    6    7

MEMBER
--------------------------------------------------------------------------------
/home/oracle/oracle/product/10.2.0/db_1/dbs/spfilemydb.ora
/home/oracle/oracle/product/10.2.0/oradata/mydb/control01.ctl
/home/oracle/oracle/product/10.2.0/oradata/mydb/control02.ctl
/home/oracle/oracle/product/10.2.0/oradata/mydb/control03.ctl
/home/oracle/oracle/product/10.2.0/oradata/mydb/example01.dbf
/home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log
/home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log
/home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log
/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf
/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf
/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf

12 rows selected.

SQL>


Let`s go.

Shutdown database :


SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Create pfile from spfile and edit

SQL> create pfile from spfile;

File created.

SQL> 


[oracle@localhost dbs]$ gedit initmydb.ora
..
*.control_files=(+DATA, +FLSHBK)
...

Save and exit, then create spfile from pfile


SQL> create spfile from pfile;

File created.

SQL>


Now startup database with nomount status via RMAN and restore control file from fresh backup:


[oracle@localhost dbs]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Nov 13 11:19:43 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area     285212672 bytes

Fixed Size                     1218992 bytes
Variable Size                 83887696 bytes
Database Buffers             197132288 bytes
Redo Buffers                   2973696 bytes

RMAN> restore controlfile from '/home/oracle/oracle/product/10.2.0/db_1/dbs/control.ctl';

Starting restore at 13-NOV-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+DATA/mydb/controlfile/current.256.799240855
output filename=+FLSHBK/mydb/controlfile/current.256.799240867
Finished restore at 13-NOV-12

RMAN>



Now our control files are located in two ASM disk gorups.

Open database with RESETLOGS option.


RMAN> alter database open resetlogs;

database opened

RMAN>

Because we restored control file!

SQL> select member from v$logfile
union
select file_name from dba_data_files
union
select name from v$controlfile
union
select value from v$parameter where name='spfile' order by 1;   2    3    4    5    6    7  

MEMBER
--------------------------------------------------------------------------------
+DATA/mydb/controlfile/current.256.799240855
+FLSHBK/mydb/controlfile/current.256.799240867
/home/oracle/oracle/product/10.2.0/db_1/dbs/spfilemydb.ora
/home/oracle/oracle/product/10.2.0/oradata/mydb/example01.dbf
/home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log
/home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log
/home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log
/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf
/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf
/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf

11 rows selected.

SQL> 

Now let`s migrate datafiles.

Repeat above steps:

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create pfile from spfile;

File created.

SQL> 

Edit pfile:

[oracle@localhost dbs]$ gedit initmydb.ora

*.db_create_file_dest=+DATA
*.db_recovery_file_dest=+FLSHBK

Now, create spfile from pfile

SQL> create spfile from pfile;

File created.

SQL> 

Start database and backup as copy of database provide ASM disk group(s), then switch datafiles

[oracle@localhost dbs]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Nov 13 11:35:09 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area     285212672 bytes

Fixed Size                     1218992 bytes
Variable Size                 83887696 bytes
Database Buffers             197132288 bytes
Redo Buffers                   2973696 bytes

RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';

Starting backup at 13-NOV-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf
output filename=+DATA/mydb/datafile/system.257.799241745 tag=TAG20121113T113544 recid=2 stamp=799241794
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
output filename=+DATA/mydb/datafile/sysaux.258.799241803 tag=TAG20121113T113544 recid=3 stamp=799241832
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/home/oracle/oracle/product/10.2.0/oradata/mydb/example01.dbf
output filename=+DATA/mydb/datafile/example.259.799241837 tag=TAG20121113T113544 recid=4 stamp=799241844
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf
output filename=+DATA/mydb/datafile/undotbs1.260.799241853 tag=TAG20121113T113544 recid=5 stamp=799241856
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf
output filename=+DATA/mydb/datafile/users.261.799241861 tag=TAG20121113T113544 recid=6 stamp=799241863
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
Finished backup at 13-NOV-12

Starting Control File and SPFILE Autobackup at 13-NOV-12
piece handle=+FLSHBK/mydb/autobackup/2012_11_13/s_799241353.257.799241869 comment=NONE
Finished Control File and SPFILE Autobackup at 13-NOV-12

RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "+DATA/mydb/datafile/system.257.799241745"
datafile 2 switched to datafile copy "+DATA/mydb/datafile/undotbs1.260.799241853"
datafile 3 switched to datafile copy "+DATA/mydb/datafile/sysaux.258.799241803"
datafile 4 switched to datafile copy "+DATA/mydb/datafile/users.261.799241861"
datafile 5 switched to datafile copy "+DATA/mydb/datafile/example.259.799241837"

RMAN> 


Now, open the database

RMAN> alter database open;

database opened

RMAN> 


[oracle@localhost dbs]$ sql

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 13 11:41:30 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select member from v$logfile
union
select file_name from dba_data_files
union
select name from v$controlfile
union
select value from v$parameter where name='spfile' order by 1;   2    3    4    5    6    7  

MEMBER
--------------------------------------------------------------------------------
+DATA/mydb/controlfile/current.256.799240855
+DATA/mydb/datafile/example.259.799241837
+DATA/mydb/datafile/sysaux.258.799241803
+DATA/mydb/datafile/system.257.799241745
+DATA/mydb/datafile/undotbs1.260.799241853
+DATA/mydb/datafile/users.261.799241861
+FLSHBK/mydb/controlfile/current.256.799240867
/home/oracle/oracle/product/10.2.0/db_1/dbs/spfilemydb.ora
/home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log
/home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log
/home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log

11 rows selected.

SQL> 

Not: tempfile not located on control file so we have to re-create new one and delete existing tempfile;


SQL> select name, bytes from v$tempfile;

 NAME                                                                                            BYTES
--------------------------------------------------------------------------------
/home/oracle/oracle/product/10.2.0/oradata/mydb/temp01.dbf          20971520


SQL> create temporary tablespace tempnew tempfile SIZE 100M extent management local uniform size 1M;

Tablespace created.

SQL> alter database default temporary tablespace tempnew;

Database altered.

SQL> drop tablespace temp including contents;

Tablespace dropped.

SQL> create temporary tablespace temp tempfile SIZE 100M extent management local uniform size 1M;

Tablespace created.

SQL> alter database default temporary tablespace temp;

Database altered.

SQL> drop tablespace tempnew including contents;

Tablespace dropped.

SQL> select name, bytes from v$tempfile;

NAME                                                                                       BYTES
--------------------------------------------------------------------------------
+DATA/mydb/tempfile/temp.263.799242427                            104857600



Now migrate redo logs and dropping old files, Set db_create_online_log_dest_1 value

SQL> alter system set db_create_online_log_dest_1='+DATA' scope=spfile;

System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              88082000 bytes
Database Buffers          192937984 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL>

SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;


GROUP# STATUS MEMBER
--------------------------------------------------------------------------------
         1 CURRENT   /home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log
         2 UNUSED     /home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log
         3 UNUSED     /home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log

SQL> 

As you see we have had 3 redo groups. To maintain redo files we have to use redo which status INACTIVE. To do that switch logfile.

SQL> alter system switch logfile;

System altered.

SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;

GROUP# STATUS MEMBER
--------------------------------------------------------------------------------
         1 ACTIVE       /home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log
         2 CURRENT   /home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log
         3 UNUSED     /home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log

SQL> alter system checkpoint;

System altered.

SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;

GROUP# STATUS MEMBER
--------------------------------------------------------------------------------
         1 INACTIVE   /home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log
         2 INACTIVE   /home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log
         3 UNUSED     /home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log


Now drop INAVCTIVE redo files and create new one

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 size 50M;

Database altered.

SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;

GROUP# STATUS MEMBER
--------------------------------------------------------------------------------
         1 UNUSED      +DATA/mydb/onlinelog/group_1.262.799243295
         2 INACTIVE   /home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log
         3 CURRENT   /home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 size 50M;

Database altered.

SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;

GROUP# STATUS MEMBER
--------------------------------------------------------------------------------
         1 UNUSED      +DATA/mydb/onlinelog/group_1.262.799243295
         2 UNUSED      +DATA/mydb/onlinelog/group_2.264.799243379
         3 CURRENT   /home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;

GROUP# STATUS MEMBER
--------------------------------------------------------------------------------
         1 INACTIVE    +DATA/mydb/onlinelog/group_1.262.799243295
         2 ACTIVE        +DATA/mydb/onlinelog/group_2.264.799243379
         3 CURRENT   /home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log


SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;

GROUP# STATUS MEMBER
--------------------------------------------------------------------------------
         1 CURRENT    +DATA/mydb/onlinelog/group_1.262.799243295
         2 INACTIVE    +DATA/mydb/onlinelog/group_2.264.799243379
         3 INACTIVE   /home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log


SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 size 50M;

Database altered.

SQL> alter system checkpoint;

System altered.

SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;

GROUP# STATUS MEMBER
--------------------------------------------------------------------------------
         1 INACTIVE    +DATA/mydb/onlinelog/group_1.262.799243295
         2 INACTIVE    +DATA/mydb/onlinelog/group_2.264.799243379
         3 CURRENT    +DATA/mydb/onlinelog/group_3.265.799243479

SQL> 

Now let`s re-create spfile on ASM disk group.

[oracle@localhost mydb]$ export ORACLE_SID=mydb
[oracle@localhost mydb]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 13 12:23:23 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create pfile from spfile;

File created.

SQL> create spfile='+DATA/spfile' from pfile;

File created.

SQL> 

Edit pfile :
[oracle@localhost dbs]$ pwd
/home/oracle/oracle/product/10.2.0/db_1/dbs
[oracle@localhost dbs]$ vi initmydb.ora 
...
*.spfile='+DATA/spfile'
...

Check file in ASM

[oracle@localhost ~]$ export ORACLE_SID=+ASM
[oracle@localhost ~]$ asmcmd
ASMCMD> ls
DATA/
FLSHBK/
ASMCMD> cd DATA
ASMCMD> ls
MYDB/
spfile
ASMCMD>

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> 

SQL> show parameter spfile

NAME                                 TYPE        VALUE
----------------------------------------------------------------------------
spfile                                    string         +DATA/spfile
SQL>


Delete old datafiles

[oracle@localhost dbs]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Nov 13 12:14:17 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: MYDB (DBID=2680024004)

RMAN> delete copy of database;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=138 devtype=DISK

List of Datafile Copies
Key     File S Completion Time Ckp SCN    Ckp Time        Name
------- ---- - --------------- ---------- --------------- ----
7       1    A 13-NOV-12       526093     13-NOV-12       /home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf
8       2    A 13-NOV-12       526093     13-NOV-12       /home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf
9       3    A 13-NOV-12       526093     13-NOV-12       /home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
10      4    A 13-NOV-12       526093     13-NOV-12       /home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf
11      5    A 13-NOV-12       526093     13-NOV-12       /home/oracle/oracle/product/10.2.0/oradata/mydb/example01.dbf

Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf recid=7 stamp=799241987
deleted datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf recid=8 stamp=799241987
deleted datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf recid=9 stamp=799241987
deleted datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf recid=10 stamp=799241987
deleted datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/example01.dbf recid=11 stamp=799241987
Deleted 5 objects


RMAN> 


Also delete other files : redo, control and tempfile

[oracle@localhost mydb]$ ls -lrt
total 192004
-rw-r----- 1 oracle oinstall 20979712 Oct 20  2011 temp01.dbf
-rw-r----- 1 oracle oinstall  7061504 Nov 13 11:13 control03.ctl
-rw-r----- 1 oracle oinstall  7061504 Nov 13 11:13 control02.ctl
-rw-r----- 1 oracle oinstall  7061504 Nov 13 11:13 control01.ctl
-rw-r----- 1 oracle oinstall 52429312 Nov 13 11:58 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Nov 13 11:59 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Nov 13 12:03 redo03.log
drwxr-xr-x 2 oracle oinstall     4096 Nov 13 12:10 arch
[oracle@localhost mydb]$ rm temp01.dbf 
[oracle@localhost mydb]$ rm redo0*.log
[oracle@localhost mydb]$ rm control0*
[oracle@localhost mydb]$ 

Now check ASM disk space:

[oracle@localhost mydb]$ export ORACLE_SID=+ASM
[oracle@localhost mydb]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 13 12:17:07 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select name, state, total_mb, free_mb from v$asm_diskgroup;

NAME                           STATE         TOTAL_MB    FREE_MB
------------------------------ ----------- ---------- ----------
DATA                           MOUNTED           3000        558
FLSHBK                       MOUNTED           2000       1726

SQL> 


Select above query:

SQL> select member from v$logfile
union
select file_name from dba_data_files
union
select name from v$controlfile
union
select value from v$parameter where name='spfile' order by 1;  2    3    4    5    6    7  

MEMBER
--------------------------------------------------------------------------------
+DATA/mydb/controlfile/current.256.799240855
+DATA/mydb/datafile/example.259.799241837
+DATA/mydb/datafile/sysaux.258.799241803
+DATA/mydb/datafile/system.257.799241745
+DATA/mydb/datafile/undotbs1.260.799241853
+DATA/mydb/datafile/users.261.799241861
+DATA/mydb/onlinelog/group_1.262.799243295
+DATA/mydb/onlinelog/group_2.264.799243379
+DATA/mydb/onlinelog/group_3.265.799243479
+DATA/spfile
+FLSHBK/mydb/controlfile/current.256.799240867

11 rows selected.

SQL>

All files were successfully moved to ASM

Now migrate Archivelogs to ASM disk group

edit pfile file
...
*.log_archive_dest_1=''
...
recreate spfile and startup database

SQL> select name,sequence# from v$archived_log;

NAME                                                                                                                           SEQUENCE#
---------------------------------------------------------------------------------------------------------
/home/oracle/oracle/product/10.2.0/oradata/mydb/arch/mydb_1_9_799241036.arc         9
/home/oracle/oracle/product/10.2.0/oradata/mydb/arch/mydb_1_10_799241036.arc       10
+FLSHBK/mydb/archivelog/2012_11_13/thread_1_seq_11.272.799253049                  11

3 rows selected.

And now migrate from ASM to FileSystem.

Let`s migrate spfile to FileSystem

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/spfile


SQL> create pfile='/home/oracle/oracle/product/10.2.0/db_1/dbs/initmydb.ora' from spfile='+DATA/spfile';

File created.

SQL> create spfile='/home/oracle/oracle/product/10.2.0/db_1/dbs/spfilemydb.ora' from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              96470608 bytes
Database Buffers          184549376 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME                    TYPE    VALUE
-------------------- -------- -------------------------------------------------------------
spfile                       string      /home/oracle/oracle/product/10.2.0/db_1/dbs/spfilemydb.ora
SQL> 

2 comments:

Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist   I got it in while compiling procedure, becaus...