Migrating Non ASM DB to ASM DB
This is a sample document showing how to migrate Non ASM DB to ASM DB
1 create the ASM instance
2.Create the required DISK groups according to the needs
DB name DEVEL
SQL> select file_name from dba_data_files;
FILE_NAME
——————————————————————————–
/oracle/DEVEL/system01.dbf
/oracle/DEVEL/undotbs01.dbf
/oracle/DEVEL/sysaux01.dbf
/oracle/DEVEL/users01.dbf
/oracle/DEVEL/example01.dbf
SQL> select name from v$controlfile;
NAME
——————————————————————————–
/oracle/DEVEL/control01.ctl
SQL> select MEMBER from v$logfile;
MEMBER
——————————————————————————–
/oracle/DEVEL/redo03.log
/oracle/DEVEL/redo02.log
/oracle/DEVEL/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/DEVEL/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/DEVEL/CONTROLFILE/backup.264.583101555
+DATA1/DEVEL/CONTROLFILE/backup.265.583101651
Modify the spfile for the controlfile new path
SQL> alter system set control_files=’+DATA1/DEVEL/CONTROLFILE/backup.264.583101555′,’+DATA1/DEVEL/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/devel/controlfile/backup.264.583101555
+DATA1/devel/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/DEVEL/system01.dbf
output filename=+DATA1/devel/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/DEVEL/sysaux01.dbf
output filename=+DATA1/devel/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/DEVEL/example01.dbf
output filename=+DATA1/devel/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/DEVEL/undotbs01.dbf
output filename=+DATA1/devel/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/DEVEL/users01.dbf
output filename=+DATA1/devel/datafile/users.260.583100683 tag=TAG20060222T202229 recid=9 stamp=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/devel/controlfile/backup.261.583100685 tag=TAG20060222T202229 recid=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/devel/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>
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy “+DATA1/devel/datafile/system.256.583100553”
datafile 2 switched to datafile copy “+DATA1/devel/datafile/undotbs1.259.583100677”
datafile 3 switched to datafile copy “+DATA1/devel/datafile/sysaux.257.583100615”
datafile 4 switched to datafile copy “+DATA1/devel/datafile/users.260.583100683”
datafile 5 switched to datafile copy “+DATA1/devel/datafile/example.258.583100661”
RMAN> ALTER DATABASE OPEN;
database opened
SQL> select file_name from dba_data_files;
FILE_NAME
——————————————————————————–
+DATA1/devel/datafile/system.256.583100553
+DATA1/devel/datafile/undotbs1.259.583100677
+DATA1/devel/datafile/sysaux.257.583100615
+DATA1/devel/datafile/users.260.583100683
+DATA1/devel/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/devel/onlinelog/group_3.268.583102251
+DATA1/devel/onlinelog/group_2.267.583102163
+DATA1/devel/onlinelog/group_1.266.583102121
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/devel/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/DEVEL/PARAMETERFILE/spfile.273.583106111
Now make an entry in the pfile, like below
[oracle@RAC2 dbs]$ cat initDEVEL.ora
spfile=’+DATA1/DEVEL/PARAMETERFILE/spfile.273.583106111’
[oracle@RAC2 dbs]$
so now all the DB files are stored in ASM.
Please let me if you have some questions or doubts.