Migration of Non ASM oracle database to ASM databsae

MIGRATION FROM NON ASM TO ASM

1 First we need to set below parameter in parameter file for controlfile.

NOTE: I have two disk group here I am using "DATA" disk group.

control_files='+data'

Step: 2 Now we need to restart the database so that new parameter value will take effect.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 263639040 bytes

Fixed Size 1332552 bytes

Variable Size 218106552 bytes

Database Buffers 37748736 bytes

Redo Buffers 6451200 bytes

Step:3 We will restore controlfile on ASM file system .

]$rman target /

RMAN> restore controlfile from ‘/disk1/oradata/test/CONTROL01.ctl';

Starting restore at 10-dec-11using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=151 device type=DISK

channel ORA_DISK_1: copied control file copyoutput file name=+data/test/controlfile/current.256.652270419Finished restore at 10-dec-11

Step:4 Mount Oracle Database and take backup of database.

RMAN> alter database mount;

database mountedreleased channel: ORA_DISK_1

RMAN> backup as copy database format '+data';

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/test/datafile/system.257.652270565"

datafile 2 switched to datafile copy "+DATA/test/datafile/sysaux.258.652270761"

datafile 3 switched to datafile copy "+DATA/test/datafile/undotbs1.259.652270927"

datafile 4 switched to datafile copy "+DATA/test/datafile/users.261.652270989"

Step:5 Now you can open the database .

SQL> alter database open resetlogs;

Database altered.

Step:6 Drop old temporary tablespace  and create new temporay tablespace

SQL> create temporary tablespace temp2 tempfile '/disk1/oradata/test/temp2.dbf'

Tablespace created.

SQL> alter database default temporary tablespace temp2;

Database altered.

SQL> select tablespace_name, file_name, bytes from dba_temp_files;

TABLESPACE_NAME FILE_NAME BYTES

------------------------------ ------------------------------ ----------

TEMP2                              +DATA/test/tempfile/temp2.dbf

Step:7 Recreate All redolog group on ASM diskgroup

SQL> select group#, member from v$logfile

GROUP# MEMBER

---------- ------------------------------------

3           /DISK1/ORADATA/TEST/REDO03.LOG

2           /DISK1/ORADATA/TEST/REDO02.LOG

1           /DISK1/ORADATA/TEST/REDO01.LOG

SQL> select group#,status from v$log;

GROUP# STATUS

---------- ----------------

1 CURRENT

2 UNUSED

3 UNUSED

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select group#,status from v$log;

GROUP# STATUS

---------- ----------------

1 ACTIVE

2 ACTIVE

3 CURRENT

SQL>alter database add logfile group 4 (‘+data/redolog4.log’) size 10m;

SQL>alter database add logfile group 5 (‘+data/redolog5.log’) size 10m;

SQL>alter system switch logfile;

SQL>alter system switch logfile;

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3 ;

Database altered.