Steps To Migrate/Move a Database From Non-ASM to ASM And Vice-Versa

Step 1: Edit the pfile/spfile of DB to point the new control_file location on ASM

E.g. : if your disk group name is '+DATA'
control_files='+DATA/<dbid>/control01.ctl'

The setting here is actually an alias for the controlfile.

Step 2: Startup the database in nomount state

SQL> Startup nomount

Step 3: From RMAN session, copy the control file from old location to new location
RMAN> CONNECT TARGET

RMAN> RESTORE CONTROLFILE FROM '/u01/TST/control01.ctl';  ----- Here /u01/TST/control01.ctl is the old location of control file.


Step 4: From SQL session, mount the database

SQL> ALTER DATABASE MOUNT;

Step 5: Using RMAN, copy the datafile from NON-ASM to ASM
RMAN>BACKUP AS COPY DATABASE FORMAT '+DATA';

Step 6: Using RMAN, rename the datafile , using the following command
RMAN> SWITCH DATABASE TO COPY;

Step 7: Switch tempfile and open database.
RMAN> run {
set newname for tempfile 1 to '+DATA';
set newname for tempfile 2 to '+DATA';
...
switch tempfile all;
}

RMAN> ALTER DATABASE OPEN;

Step 8: Do the following maintenance
SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;
SQL> ALTER DATABASE ADD LOGFILE MEMBER '+ASM_Disk_group' TO GROUP 1;
SQL> ALTER DATABASE ADD LOGFILE MEMBER '+ASM_Disk_group' TO GROUP 2;
SQL> ALTER DATABASE DROP LOGFILE MEMBER '';
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER DATABASE DROP LOGFILE MEMBER '';
SQL> ALTER SYSTEM SWITCH LOGFILE;
... repeat for *all* online redo log members.
A redo log member can only be dropped after being archived and being in INACTIVE mode. If needed, switch logfile multiple times until the logfile is ready for dropping.

 STEP of MIGRATION from ASM to NON-ASM

1. Start your database with ASM.

2. Create pfile from spfile.

3. Edit pfile/spfile to reflect controlfile name in file system location.

4. Startup nomount the DB.
SQL> Startup nomount

5. Use RMAN to copy the control file from ASM to NON-ASM.

RMAN> RESTORE CONTROLFILE FROM '';

6. Mount the DB.
SQL> alter database mount;

7. Use RMAN to copy the database from ASM to NON-ASM.
RMAN> BACKUP AS COPY DATABASE format '/u01/oradata/nonasmdb/datafile/%U';

8. From RMAN.
RMAN> SWITCH DATABASE TO COPY;

9. Recreate the tempfile and redo logs as before. See step 7 and 8 above.