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.

About the Author Atul Kumar

Leave a Comment:

3 comments
Svetoslav Gyurov says March 4, 2010

Hi,

Good job, but just to mention that you should drop the temporary tablespace and re-create it after migration as ‘backup as copy’ command does not copy the online logs and temporary tablespace. Also consider disabling the block change tracking before the migration and enable it again after the migration.

Regards,
Sve

Reply
subrahmanyam says May 3, 2010

hi this nonasm to asm is very good and its very simple
thanks alot ……………….

Reply
Neeraj says December 3, 2010

Thanks much for this guide, simple to follow. Everthing worked except the LAST STEP. I cant find any parameter file in +DATA

Reply
Add Your Reply

Not found