Steps To Migrate/Move a Database From Non-ASM to ASM And Vice-Versa (Doc ID 252219.1)

 

APPLIES TO:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 20-May-2015***

PURPOSE

This article describes the steps to migrate a database from Non-ASM to ASM and vice-versa.

To setup ASM, please follow

Note 452924.1 - How to Prepare Storage for ASM
Note 265633.1 - ASM Technical Best Practices
Note 249992.1 - New Feature on ASM (Automatic Storage Manager).

SCOPE

A scheduled downtime is needed.

DETAILS

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.

For 10gR1 or you don't want to use an alias for controlfile, please set control_files='+DATA' and use the following note to update the parameter again in the pfile/spfile after the controlfile is created at step #3.

Note 468458.1  How To Move Controlfile To ASM

If you later want to duplicate an extra copy of control file, please see

Note 345180.1  How to duplicate a controlfile when ASM is involved

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 '+<diskgroupname>' TO GROUP 1;
SQL> ALTER DATABASE ADD LOGFILE MEMBER '+<diskgroupname>' TO GROUP 2;
SQL> ALTER DATABASE DROP LOGFILE MEMBER '<old file name>';
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER DATABASE DROP LOGFILE MEMBER '<old file name>';
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. 

STEPS of MIGRATION from ASM to NON-ASM

A. Start your database with ASM.

B. Create pfile from spfile.

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

D. Startup nomount the DB.

SQL> Startup nomount

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

RMAN> RESTORE CONTROLFILE FROM '';

F. Mount the DB.

SQL> alter database mount;

G. Use RMAN to copy the database from ASM to NON-ASM.

RMAN> BACKUP AS COPY DATABASE format '/u01/oradata/nonasmdb/datafile/%U';

H. From RMAN.

RMAN> SWITCH DATABASE TO COPY;

I. Recreate the tempfile and redo logs as before. See step G and H above.

NOTE:  FYI, there is a new feature to asmcmd:
Note 1610615.1 How to Move a Datafile from Filesystem to ASM Using ASMCMD CP Command. 

For Windows use the next document

How to Move from Non-ASM to ASM on Windows? Note 1914862.1

Reference

Migrating Databases To and From ASM with Recovery Manager

Additional Resources

Community Discussions: Storage Management

Still have questions? Use the above community to search for similar discussions or start a new discussion on this subject.

REFERENCES

NOTE:1610615.1 - How to Move a Datafile from Filesystem to ASM Using ASMCMD CP Command.
NOTE:452924.1 - How to Prepare Storage for ASM
NOTE:1914862.1 - How to Move from Non-ASM to ASM on Windows?
NOTE:345180.1 - How to duplicate a controlfile when ASM is involved
NOTE:468458.1 - How To Move Controlfile To ASM
NOTE:265633.1 - ASM Technical Best Practices For 10g and 11gR1 Release