Migrating a Database into ASM

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" on page 16-10 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:

1. 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.

 

2. 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’;

 

3. 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;

 

4. 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.

 

5. 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";

6. 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='*';

7. Now specify the location of the flash recovery area by settingDB_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=’*’;

 

8. Shut down and startup inNOMOUNT 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;

9. The next step is to migrate your tempfiles to ASM. You must use aSET 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.

10. 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.

 

11. 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';

12. 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;

13. For a primary database, migrating the online logs is performed byadding 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" on page 16-10.

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" on
page 16-9.