How to migrate existing databases into ASM

RMAN can also be used to migrate existing databases into ASM. This can be done one file at a time or a complete database can be migrated into ASM in the same operation.

The following provides an example of the procedure you can use to relocate your entire database to an ASM disk group (assuming the use of a server parameter file):

• Obtain the file names of the current control files and online redo logs using V$CONTROLFILE and V$LOGFILE.
• Shut down the database consistently.
• Modify the server parameter file of your database as follows:
• Start the database with the NOMOUNT option.
• Set the DB_CREATE_FILE_DEST parameter to the desired ASM disk group.
• Remove the CONTROL_FILES parameter. It will be re-created automatically.
• Edit to replace the placeholder file and disk group references with your actual locations, and then run the following RMAN command file. This backs up the database, switches the current data files to the backups, renames the online redo logs, and re-creates the temporary tablespaces.

RESTORE CONTROLFILE FROM '/u1/c1.ctl';
ALTER DATABASE MOUNT;
BACKUP AS COPY DATABASE FORMAT '+YOURDG';
SWITCH DATABASE TO COPY;
# Repeat command for all online redo log members
SQL "ALTER DATABASE RENAME '/u1/log1' TO '+YOURDG' ";
ALTER DATABASE OPEN RESETLOGS;
# Repeat commands for all temporary tablespaces
SQL "ALTER TABLESPACE temp ADD TEMPFILE";
SQL "ALTER DATABASE TEMPFILE '/u1/temp1' DROP";

• Delete the old database files.

Note: This example illustrates the procedure for migrating a database into ASM. You may want to use other options and settings to migrate your specific databases into ASM. For a complete discussion of this topic, refer to the Oracle Database Backup and Recovery User’s Guide 11g Release 2.