Migrating a Database into ASM

To take advantage of Automatic Storage Management with an existing database you must migrate that database into ASM. This migration is performed using Recovery Manager (RMAN) even if you are not using RMAN for your primary backup and recovery strategy.

A database can be moved from non-ASM disk storage directly into ASM, assuming you have enough disk space to hold the entire database both in non-ASM storage and in ASM storage. If you do not have enough disk space to have a complete copy of your database in ASM and another in non-ASM storage simultaneously, you can modify the procedure described here to back the database up to tape, create an ASM disk group that uses the old disk space, and then restore the database from tape into ASM.

Note:

Enterprise Manager provides a GUI-based option for migration of a database to ASM storage. See Oracle Database 2 Day DBA for details.

Limitation on ASM Migration with Transportable Tablespaces

The procedure described here does not work for transportable (foreign) tablespaces. Such tablespaces needs to be made read-write and imported into the database, before they can be migrated into ASM using this procedure.

Preparing to Migrate a Database to ASM

There are several steps required to prepare your database for migration and collect useful information you will need later, before you start the actual migration process.

Determine Names of Database Files

Obtain the filenames of the control files, datafiles, and online redo logs for your database. This information will useful if you decide to migrate back to old (non-ASM) storage later. Information about datafiles is available by querying V$DATAFILE, online log file names in V$LOGFILE, and the control file names can be found in the CONTROL_FILES initialization parameter.

Generate RMAN Command File to Undo ASM Migration

If you need to migrate your database back to non-ASM storage later, this process will be simplified if you generate an RMAN command file now with the necessary commands to perform this migration. Even if you make changes to your database later, such as adding datafiles, the command file you create now will serve as a useful starting point.

There is a PL/SQL script described in "Generating ASM-to-Non-ASM Storage Migration Script" which generates the necessary RMAN commands for you. Run this script and save the output as part of the permanent records you keep for your database.

Disk-Based Migration of a Database to ASM

If you have enough disk space that you can have both your entire non-ASM database and your ASM disk group on disk at the same time, you can do the migration directly without using tapes.

The procedure differs slightly between primary and standby databases. A number of the steps described in this procedure apply only in one or the other case. There are also a few steps where the procedure is different depending upon whether you are using a recovery catalog. The steps that vary are identified as necessary in the description of the process.

The process described in this section is intended to minimize downtime for your database. The example assumes that the destination ASM disk group for database files is +DISK, and a separate ASM disk group +FRA will be used to store the flash recovery area.

Note:

During the migration process all flashback logs are discarded. As a result, any guaranteed restore points in the database become unusable. You should drop all guaranteed restore points before performing the migration.

To perform the migration, carry out the following steps:

Back up your database files as copies to the ASM disk group.

BACKUP AS COPY INCREMENTAL LEVEL 0  DATABASE 
     FORMAT '+DISK'  TAG 'ORA_ASM_MIGRATION';

You can perform this backup with multiple channels to improve performance, depending upon your hardware configuration. For example:

run {
     allocate channel dev1 type disk;
     allocate channel dev2 type disk;
     allocate channel dev3 type disk;
     allocate channel dev4 type disk;
     BACKUP AS COPY INCREMENTAL LEVEL 0 DATABASE
           FORMAT '+DISK'  TAG 'ORA_ASM_MIGRATION;
}

To ensure that the backup can also be made consistent, archive the current redo log after the backup:

RMAN> sql 'alter system archive log current';

Note:

This backup may take a long time, depending upon the size of your database. If there has been a lot of activity on the database during the time the backup was created, you may wish to use the following procedure to create an incremental backup of the database afterwards, to refresh the copy with changes since the migration process started. If so, use the following script:

RMAN>  backup  incremental level 1 for recover of copy 
              with tag 'ORA_ASM_MIGRATION' database ;
RMAN> recover copy of database with tag 'ORA_ASM_MIGRATION';

This minimizes the time required for the media recovery performed just before the copy of the database in ASM is opened at the end of the migration process. You may also want to perform this step using multiple channels, if using them improves performance in your environment.

Create a copy of the SPFILE in the ASM disk group. In this example, the SPFILE for the migrated database will be stored as +DISK/spfile.

If the database is using an SPFILE already, then run these commands:
```
run {
   BACKUP AS BACKUPSET SPFILE;
   RESTORE SPFILE TO "+DISK/spfile";
}
```
If you are not using an SPFILE, then use CREATE SPFILE from SQL*Plus to create the new SPFILE in ASM. For example, if your parameter file is called /private/init.ora, use the following command:
```
SQL> create spfile='+DISK/spfile' from pfile='/private/init.ora';
```
If this is standby database, stop managed recovery mode.
```
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
```
Perform a consistent shutdown of the database.
```
RMAN> SHUTDOWN IMMEDIATE;
```
At this point, if you want the option of easily returning the database to non-ASM storage later, make copies of your current control file and all online logs. This command backs up the current control file to a non-ASM location:
```
RMAN> STARTUP MOUNT;
RMAN> BACKUP AS COPY CURRENT CONTROLFILE FORMAT '/disk1/pre-ASM-controfile.cf'; 
```
Note:
RMAN cannot be used to backup your online logs. You must use operating-system commands to copy them.
Now create an init.ora specifying the location of the new SPFILE, and start the instance with it. For example, create /tmp/pfile.ora with the following contents:
```
SPFILE=+DISK/spfile
```
Now start the database in NOMOUNT:
```
SQL> startup nomount PFILE="/tmp/pfile.ora";
```
The next step is to migrate the control file to ASM.

In SQL*Plus, change the CONTROL_FILES initialization parameter using the following command:
```
SQL> alter system set control_files='+DISK/ct1.f','+FRA/ct2.f' scope=spfile sid='*';
```
Now specify the location of the flash recovery area by setting DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE. Assuming that the desired size of the flash recovery area is 100 gigabytes, enter the following commands in SQL*Plus to set the parameters:
```
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=100G SID='*';
SQL> alter system set DB_RECOVERY_FILE_DEST='+FRA' SID='*';
```
Shut down and startup in NOMOUNT again, so that the changed parameters take effect. (The CONTROL_FILES parameter change only takes effect upon a restart because it is a static parameter.)

Then, use RMAN to actually create the new control files in ASM. For example, assuming that one of your original control file locations was /private/ct1.f, use the following command:
```
RMAN> shutdown immediate;
RMAN> startup nomount PFILE='/tmp/pfile.ora'; #using ASM SPFILE now
RMAN> restore controlfile from '/private/ct1.f';
RMAN> alter database mount;
RMAN> switch database to copy;
RMAN> recover database;
```
The next step is to migrate your tempfiles to ASM. You must use a SET NEWNAME command for each tempfile to direct it to ASM, then a SWITCH to make the new names take effect.
```
RMAN > run {
            set newname for tempfile 1 to '+DISK'
            set newname for tempfile 2 to '+DISK';
             ...
            switch tempfile all;
           }
```
The new tempfiles are created when you open the database.
Disable logging for Flashback Database, and then re-enable it again to start creating flashback logs in the new ASM flash recovery area. For example:
```
SQL> ALTER DATABASE FLASHBACK OFF;
SQL> ALTER DATABASE FLASHBACK ON;
```
Note:
Flashback logs cannot be migrated. All data in the flashback logs is lost.
The change tracking file cannot be migrated. You can only disable change tracking, then re-enable it, specifying an ASM disk location for the change tracking file:
```
SQL> alter database disable block change tracking;
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '+DISK';
```
At this point, if the database is a primary database, then open the database.
```
SQL> ALTER DATABASE OPEN;
```
For a standby database, resume managed recovery mode:
```
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
```
For a primary database, migrating the online logs is performed by adding new log group members in ASM, and then dropping the old members. The easiest way to perform this step is to use the PL/SQL script in "Migrating Online Logs of Primary Database to ASM".

For a standby database, you can follow similar steps to the script to drop the old standby redo logs and add new ones in the +DISK disk group, but the online redo logs cannot be migrated until the database is opened as a primary.

At this point the migration is complete. Your database and flash recovery area are stored in ASM. You may wish to move your existing flash recovery area backups using the process described in "Migrating Existing Backups to ASM Flash Recovery Area".