Migration from Non ASM DB to ASM DB

  Migration Steps from  Non ASM DB to ASM DB

1 create the ASM instance

2.Create the required DISK groups according to the needs

DB name COBRA

SQL> select file_name from dba_data_files;

FILE_NAME
——————————————————————————–
/oracle/COBRA/system01.dbf
/oracle/COBRA/undotbs01.dbf
/oracle/COBRA/sysaux01.dbf
/oracle/COBRA/users01.dbf
/oracle/COBRA/example01.dbf

SQL> select name from v$controlfile;

NAME
——————————————————————————–
/oracle/COBRA/control01.ctl

SQL> select MEMBER from v$logfile;

MEMBER
——————————————————————————–
/oracle/COBRA/redo03.log
/oracle/COBRA/redo02.log
/oracle/COBRA/redo01.log

Shutdown the database.

SQL> SHUTDOWN IMMEDIATE

Start the database in nomount mode.

RMAN> STARTUP NOMOUNT (make sure you start with the spfile )

Restore the controlfile into ASM

RMAN> restore controlfile to ‘+DATA1? from ‘/oracle/COBRA/control01.ctl’;

Starting restore at 22-FEB-06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=37 devtype=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 22-FEB-06

Find the contlrol file in ASM

ASMCMD> find -t CONTROLFILE +DATA1 *

+DATA1/COBRA/CONTROLFILE/backup.264.583101555
+DATA1/COBRA/CONTROLFILE/backup.265.583101651

Modify the spfile for the controlfile new path

SQL> alter system set control_files=’+DATA1/COBRA/CONTROLFILE/backup.264.583101555?,’+DATA1/COBRA/CONTROLFILE/backup.265.583101651? scope=spfile;

System altered.

Shutdown the database and restart in mount state

SQL> shutdown

ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 1220072 bytes
Variable Size 142606872 bytes
Database Buffers 327155712 bytes
Redo Buffers 2973696 bytes
Database mounted.

SQL> select name from v$controlfile;

NAME
——————————————————————————–
+DATA1/cobra/controlfile/backup.264.583101555
+DATA1/cobra/controlfile/backup.265.583101651

Now move the datafiles to ASM

(here as my DB size was too small i used this simple method, where as in big sized DB you should implement some other strategies to do everything in in parallel as the downtime will be minimal)

RMAN> BACKUP AS COPY DATABASE FORMAT ‘+DATA1?;

Starting backup at 22-FEB-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=37 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/oracle/COBRA/system01.dbf
output filename=+DATA1/cobra/datafile/system.256.583100553 tag=TAG20060222T202229 recid=5 stamp=583100614
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/oracle/COBRA/sysaux01.dbf
output filename=+DATA1/cobra/datafile/sysaux.257.583100615 tag=TAG20060222T202229 recid=6 stamp=583100652
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/oracle/COBRA/example01.dbf
output filename=+DATA1/cobra/datafile/example.258.583100661 tag=TAG20060222T202229 recid=7 stamp=583100675
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/oracle/COBRA/undotbs01.dbf
output filename=+DATA1/cobra/datafile/undotbs1.259.583100677 tag=TAG20060222T202229 recid=8 stamp=583100680
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/oracle/COBRA/users01.dbf
output filename=+DATA1/cobra/datafile/users.260.583100683 tag=TAG20060222T202229 recid=9stamp=583100684
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DATA1/cobra/controlfile/backup.261.583100685 tag=TAG20060222T202229recid=10 stamp=583100686
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 22-FEB-06
channel ORA_DISK_1: finished piece 1 at 22-FEB-06
piece handle=+DATA1/cobra/backupset/2006_02_22/nnsnf0_tag20060222t202229_0.262.583100689 tag=TAG20060222T202229 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 22-FEB-06

RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy “+DATA1/cobra/datafile/system.256.583100553?
datafile 2 switched to datafile copy “+DATA1/cobra/datafile/undotbs1.259.583100677?
datafile 3 switched to datafile copy “+DATA1/cobra/datafile/sysaux.257.583100615?
datafile 4 switched to datafile copy “+DATA1/cobra/datafile/users.260.583100683?
datafile 5 switched to datafile copy “+DATA1/cobra/datafile/example.258.583100661?

RMAN> ALTER DATABASE OPEN;

database opened

SQL> select file_name from dba_data_files;

FILE_NAME
——————————————————————————–
+DATA1/cobra/datafile/system.256.583100553
+DATA1/cobra/datafile/undotbs1.259.583100677
+DATA1/cobra/datafile/sysaux.257.583100615
+DATA1/cobra/datafile/users.260.583100683
+DATA1/cobra/datafile/example.258.583100661

Move the redo logs to ASM

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
———- ———- ———- ———- ———- — —————-
FIRST_CHANGE# FIRST_TIM
————- ———
1 1 5 104857600 1 YES INACTIVE
567335 22-FEB-06

2 1 6 104857600 1 YES INACTIVE
567340 22-FEB-06

3 1 7 52428800 1 NO CURRENT
567343 22-FEB-06

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 (’+DATA1?) size 100M;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 (’+DATA1?) size 100M;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
———- ———- ———- ———- ———- — —————-

FIRST_CHANGE# FIRST_TIM
————- ———
1 1 8 104857600 1 NO CURRENT
567349 22-FEB-06

2 1 6 104857600 1 YES INACTIVE
567340 22-FEB-06

3 1 7 52428800 1 YES INACTIVE
567343 22-FEB-06

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 (’+DATA1?) size 100M;

Database altered.

SQL> select member from v$logfile;

MEMBER
——————————————————————————–
+DATA1/cobra/onlinelog/group_3.268.583102251
+DATA1/cobra/onlinelog/group_2.267.583102163
+DATA1/cobra/onlinelog/group_1.266.583102121

You may be able to speed up the copy process by increasing RMAN parallelism:

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4;

RMAN> BACKUP AS COPY SPFILE FORMAT ‘+DATA1?;

Starting backup at 22-FEB-06
using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=32 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 22-FEB-06
channel ORA_DISK_1: finished piece 1 at 22-FEB-06
piece handle=+DATA1/cobra/backupset/2006_02_22/nnsnf0_tag20060222t211333_0.270.583103615 tag=TAG20060222T211333 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 22-FEB-06

Now find the spfile in the ASM

ASMCMD> find -t parameterfile +DATA1 *

+DATA1/COBRA/PARAMETERFILE/spfile.273.583106111

Now make an entry in the pfile, like below

[oracle@RAC2 dbs]$ cat initCOBRA.ora

spfile=’+DATA1/COBRA/PARAMETERFILE/spfile.273.583106111

You can skip to the end and leave a response. Pinging is currently not allowed.

Leave a Reply