16 Migrating Databases To and From ASM with Recovery Manager

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.

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.

To perform the migration, carry out the following steps:

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

Setting Initialization Parameters for Flash Recovery Area in ASM

The following process moves the flash recovery area from non-ASM disk storage to an ASM disk group named +FRA.

Specify the location of the flash recovery area by setting DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE. (For this example, assume the intended size of the flash recovery area is 100 gigabytes.) If you are using an SPFILE then in SQL*Plus enter the following commands:

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=100G SID=9*9;
SQL> alter system set DB_RECOVERY_FILE_DEST=9+FRA9 SID=9*9;

If you are using a PFILE, then shut down the database, edit the above parameters in the PFILE with the new values for DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE and restart the instance.

Migrating the Control File to an ASM Flash Recovery Area

In this example, it is assumed that you have already set the initialization parameters for a flash recovery area in ASM storage, using the process in "Setting Initialization Parameters for Flash Recovery Area in ASM". It is also assumed that one control file is already stored in +DISK/ct1.f, the other in non-ASM storage. The goal is to move the non-ASM control file to the flash recovery area and store it as +FRA/ct2.f.

• In SQL*Plus, bring the database to NOMOUNT:

  SQL> SHUTDOWN IMMEDIATE;
  SQL> STARTUP NOMOUNT;
  

  Change the CONTROL_FILES initialization parameter to refer to the new location. If you are using an SPFILE, use the following command:

  SQL> alter system set control_files='+DISK/ct1.f','+FRA/ct2.f' scope=spfile sid='*';
  

  If using a PFILE, edit the PFILE with the new for the CONTROL_FILES initialization parameter.

  Shut down and startup in NOMOUNT again, so that the changed CONTROL_FILES parameter takes effect.

• Then, use RMAN to actually create the new control files in ASM.

  RMAN> restore controlfile from '+DISK/ct1.f';
  RMAN> alter database mount;
  

• If you were using flashback logging before to support flashback database, you can re-enable it now. For example:

  SQL> ALTER DATABASE FLASHBACK ON;
  

The following procedure changes the database configuration so that the flash recovery area is used for all future backups.

1. The first step is to change the initialization parameters for the database to store the flash recovery area in ASM, as described in Setting Initialization Parameters for Flash Recovery Area in ASM.

   If this database is a primary database and your online logs, control file or archived redo logs are in the flash recovery area, then perform a consistent shutdown of your database. For example:

   SQL> SHUTDOWN IMMEDIATE
   

   If this database is a standby database and your standby online logs, control file, or archive logs are in recovery area, then stop managed recovery mode and shut down the database.

   Modify the initialization parameter file of the target database as follows:

   • Set DB_RECOVERY_FILE_DEST to the desired ASM disk group.

2. Modify DB_RECOVERY_FILE_DEST_SIZE if you need to change the size of the flash recovery area.

   If you shut down the database in step 2, then bring the database to a NOMOUNT state. For example:

   RMAN> STARTUP NOMOUNT
   

   If the old flash recovery area has copy of the current control file, then restore control file from the old DB_RECOVERY_FILE_DEST and mount the database again.

   RMAN> RESTORE CONTROLFILE FROM 'filename_of_old_control_file';
   RMAN> ALTER DATABASE MOUNT;
   

   The next step is to migrate the control file from the old flash recovery area to the new flash recovery area. In this example, one control file is stored as +DISK/ct1.f, the other as +FRA/ct2.f.

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

   If you were using flashback logging before to support flashback database, you can re-enable it now. For example:

   SQL> ALTER DATABASE FLASHBACK ON;
   

At this point, all future files that are directed to the flash recovery area are created in the new ASM flash recovery area location.

Changing Flashback Log Location to ASM Flash Recovery Area

In this example, it is assumed that you have already set the initialization parameters for a flash recovery area in ASM storage, using the process in "Setting Initialization Parameters for Flash Recovery Area in ASM".

Because the actual flashback logs cannot be migrated, the only step required to move the location of flashback logs to the new ASM flash recovery area is to disable and then enable flashback logging. After a clean shutdown, mount the database and run the following commands in SQL*Plus:

SQL> ALTER DATABASE FLASHBACK OFF;
SQL> ALTER DATABASE FLASHBACK ON;

Future flashback logs will be created in the new flash recovery area. The old flashback logs are automatically deleted from non-ASM storage.

Migrating Online Logs to ASM Flash Recovery Area

In this example, it is assumed that you have already set the initialization parameters for a flash recovery area in ASM storage, using the process in "Setting Initialization Parameters for Flash Recovery Area in ASM".

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 database must be open to perform this task.

The easiest way to perform this step is to use a PL/SQL script based upon the one in "Migrating Online Logs of Primary Database to ASM". However, change the script so that it does not specify the disk group name.

For example, change the code that creates the online logs from:

stmt := 'alter database add logfile thread ' ||
                 rlcRec.thr || ' ''+DISK'' size ' || 
                 rlcRec.bytes_k || 'K';

to:

stmt := 'alter database add  logfile thread ' ||
                 rlcRec.thr || ' size ' || 
                 rlcRec.bytes_k || 'K';

Also change the code that creates the standby logs from:

stmt := 'alter database add standby logfile thread ' ||
                 rlcRec.thr || ' ''+DISK'' size ' || 
                 rlcRec.bytes_k || 'K';

to the following code:

stmt := 'alter database add standby logfile thread ' ||
                 rlcRec.thr || ' size ' || 
                 rlcRec.bytes_k || 'K';

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 +FRA disk group, but the online redo logs cannot be migrated until the database is opened as a primary.

Once you have run your script, the migration of online logs is complete.

Migrating Existing Backups to ASM Flash Recovery Area

In this example, it is assumed that you have already set the initialization parameters for a flash recovery area in ASM storage, using the process in "Setting Initialization Parameters for Flash Recovery Area in ASM".

To back up the existing archived redo log files to the new flash recovery area, use this command:

RMAN> BACKUP AS COPY ARCHIVELOG ALL DELETE INPUT;

To move backup sets to the new flash recovery area, use this command:

RMAN> BACKUP DEVICE TYPE DISK BACKUPSET ALL DELETE INPUT;

To move all datafile copies to the new flash recovery area location, use this command:

RMAN> BACKUP AS COPY DATAFILECOPY ALL DELETE INPUT;

At this point, all backups have been moved from the old flash recovery area to the new one.

Generating ASM-to-Non-ASM Storage Migration Script

You can use the following PL/SQL script to generate a series of RMAN commands that you can use to migrate your database back from ASM to non-ASM disk storage.

set serveroutput on;
declare
    cursor df is select file#, name from v$datafile;
begin
    dbms_output.put_line('run');
    dbms_output.put_line('{');
    for dfrec in df loop
        dbms_output.put_line('set newname for datafile ' ||
            dfrec.file# || ' to ''' || dfrec.name ||''' ;');
    end loop;
    dbms_output.put_line('restore database;');
    dbms_output.put_line('switch all;');
    dbms_output.put_line('}');
end;

Run this PL/SQL script and save the output into a file. The result is an RMAN script which you can save to a file and later run as a command file in the RMAN client to migrate your datafiles back out of ASM storage to their original non-ASM locations. Even if you later add or delete datafiles, this script provides a useful starting point for a migration script that will work for the new database.

Migrating Online Logs of Primary Database to ASM

The following PL/SQL script can be used to migrate the online redo log groups into ASM, as part of migrating a database or a flash recovery area into ASM. For each online redo log group, the script adds a log file stored in ASM, archives the current redo logs, and then drops the non-ASM log file.

declare
   cursor rlc is
      select group# grp, thread# thr, bytes/1024 bytes_k, 'NO' srl
        from v$log
      union
      select group# grp, thread# thr, bytes/1024 bytes_k, 'YES' srl
        from v$standby_log
      order by 1;
   stmt     varchar2(2048);
   swtstmt  varchar2(1024) := 'alter system switch logfile';
   ckpstmt  varchar2(1024) := 'alter system checkpoint global';
begin
   for rlcRec in rlc loop
      if (rlcRec.srl = 'YES') then
         stmt := 'alter database add standby logfile thread ' ||
                 rlcRec.thr || ' ''+DISK'' size ' || 
                 rlcRec.bytes_k || 'K';
         execute immediate stmt;
         stmt := 'alter database drop standby logfile group ' || rlcRec.grp;
         execute immediate stmt;
      else
         stmt := 'alter database add logfile thread ' ||
                 rlcRec.thr || ' ''+DISK'' size ' ||  
                 rlcRec.bytes_k || 'K';
         execute immediate stmt;
         begin
            stmt := 'alter database drop logfile group ' || rlcRec.grp;
            dbms_output.put_line(stmt);
            execute immediate stmt;
         exception
            when others then
               execute immediate swtstmt;
               execute immediate ckpstmt;
               execute immediate stmt;
         end;
      end if;
   end loop;
end;