12
Migrating Databases To and From ASM with Recovery Manager

This chapter describes how to migrate a database into and out of an ASM disk group using Recovery Manager. It covers the following to pics:

• Migrating a Database into A SM
• Migrating the Flash Recovery Area to ASM
• Migrating a Database from ASM to Non-ASM Stora ge
• PL/SQL Scripts Used in Migrating to ASM St orage

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 strateg y.

A database can be moved from non-ASM disk storage directly into ASM, or you can back the database up to tape and then from tape backups move it into ASM. Moving the database to tape backup and then into ASM is recommended if your database is so large that you cannot have copies of the database on non-ASM disk storage and ASM disk storage simultaneously . You can back the database up to tape, convert your non-ASM disk storage into an ASM disk group, and then restore from tape to the A SM disk group.

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 imp orted into the database, before they can be migrated into ASM using this procedure.

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

Determine Your DBID

If you are not using a recovery catalog, you may need to know your DBID. You must r estore your control file from autobackup during the migration process, and you will need to set your DBID before you restore the cont rol file.

Your DBID should be part of the permanent records you keep about your database. I f you do not have it in your records, the easiest way to find out your DBID is to connect the RMAN client to the database to be migra ted. RMAN displays the DBID whenever it starts up. For example:

% rman TARGET /
Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2003, O
racle.  All rights reserved.

connected to target database: RDBMS (D
BID=774627068)

RMAN> exit

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 d ecide to migrate back to old (non-ASM) storage later. Information about datafiles is available by querying V$DATAFILE, a nd the control file names can be found in the CONTROL_FILES initialization parameter.

Generate RMAN Command File to Undo ASM Migr ation

If you need to migrate your database back to non-ASM storage l ater, 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 st arting point.

There is a PL/SQL script described in "Generatin g ASM-to-Non-ASM Storage Migration Script" which will generate the necessary RMAN commands for y ou. 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 u sing tapes.

Once you have completed the preparations in "Prepa ring to Migrate a Database to ASM", begin the migration procedure.

1. Disable change tracking.

   SQL> ALTER DATABASE DISABLE BLOCK CHANGE
   TRACKING;
   

2. If thi s is standby database, stop managed recovery mode.

   SQL> ALTER DATABASE RECOVER MANAGED STA
   NDBY DATABASE CANCEL;
   
   

3. Shut down the data base consistently. For example:

   SQL> SHUTDOWN IMMEDIATE
   

4. Modify the initialization parameter file of the ta rget database as follows:

• Set DB_CREATE_FILE_DEST< /code> and DB_CREATE_ONLINE_LOG_DEST_n (where n=1-4) to refer to the desired ASM disk groups.
• If the database uses a server parameter file (SPFILE), then remove the CONTROL_FILES parameter that specifies locations of the control file. The control file will be moved to the < code>DB_CREATE_* destination and the server parameter file will be automatically updated. If you are using a client-side param eter file (PFILE), then set the CONTROL_FILES parameter to the ASM alias for the control file name (for example, C ONTROL_FILES=+disk_group/cf1 ).
• Startup the database in nomount mode:

  RMAN> STARTUP NOMOUNT;
  

< li class="LN1" type="1" value="6">Restore the control file into new locations from location specified in the ol d SPFILE or PFILE:

RMAN> RESTORE CONTROLFILE FROM 'filename_of_old_control_file';

• Mount the datab ase. For example:

  RMAN> ALTER DATABASE MOUNT;
  

• Copy the database into the ASM disk group using the followin g command:

  RMAN> BACKUP AS COPY DATABASE FORMAT '+disk_group';
  

  You may be able to speed up the copy process by increasing RMAN pa rallelism. For example:

  RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
  RMAN> BACKUP AS COPY DATABASE FORMAT '+disk_group';
  
  

• Switch all datafiles into new ASM disk group

       RMAN> SWITCH DATABA
  SE TO COPY;
  
  

  All datafiles are now in ASM.

  If this database a standby database, then do not open the database or start manage d recovery at this time.

  If this is a primary database (that is, not a standby database), then you can open the database.

  RMAN> ALTER DATABASE OPEN;
  

  Note that there is no need to perform an OPEN RESETLOGS .

• If you are migrating a primary database, then move your online l ogs into ASM at this time. For each online redo log, create a new one in ASM, archive the current redo log, and then delete the old n on-ASM log. For a PL/SQL script that can perform this task for you, see "Migrating Online Redo Logs to A SM Storage".

  If this is a standby database, then o nline logs do not exist (only standby online logs exist) and therefore cannot be renamed at this point. However, you must delete any online log files that might have been created if the database was used as a primary database in the past. Delete these files using th e operating system delete command.

  When the standby database is activated, online logs wil l automatically be added as ASM files. For each standby online redo log file, create a new one in ASM, and delete an old one from the non-ASM storage. For a PL/SQL script that can perform this task for you, see "Migrating Standby Online Redo Log Files to ASM Storage".

At this poin t the migration is complete. The original datafiles, still in non-ASM storage, are cataloged as datafile copies in the RMAN repositor y. You can use them as backups, or reclaim the disk space by deleting them.

If you were usi ng change tracking for incremental backups, you can re-enable it now. For example:

SQL>
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

If you decide to mig rate back to old storage, you can switch back to the original datafiles, using the script created in "Pr eparing to Migrate a Database to ASM". If you have not yet deleted your original datafiles, you can also use the SWITCH DATABASE TO COPY command to switch back rather than going through the whole migration process.

Cleanup of Non-ASM Files After ASM Migration

You can delete the old database f iles to free disk space. The RMAN repository has records of the old datafiles, so you can delete these with an RMAN command. The old control files and online redo logs, however, are not in the repository and must be deleted with host operating system commands. This example shows how to perform this under Unix, using the rm command for the online redo logs and control files:

# delete datafiles
RMAN> DELETE COPY OF DATABASE;
RMA
N> HOST 'rm old_online_redo_logs';
RMAN> HOST 'rmold_control_files';

Using Tape Backups to Mi grate a Database to ASM

This alternative procedure is useful when yo u do not have enough disk space to hold both the non-ASM version of your database and the ASM disk group that will hold your database after the migration. The database is backed up from non-ASM storage to tape using RMAN, then restored from tape into ASM storage.

Once you have completed the preparations in "Preparing to Migrat e a Database to ASM", begin the migration procedure.

Performing Migration of a Database to ASM Storage us ing RMAN Tape Backup

To migrate your non-ASM database into ASM stora ge using a tape backup, use the following procedure:

1. Backup the whole database to tape using RMAN. For example:

        RMAN> BACKUP DEVICE
   TYPE SBT DATABASE;
     
   

   After backup is done, disable change tracki ng. For example:

   SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
   

   If this is standby database, stop managed recovery mode.

   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
   
   

Shut down the database consistently. For example:

SQL> SHUTDOWN IMMEDIATE

At this point you can delete the old database files and create your ASM disk groups.

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

• Set DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n (where n=1-4) to refer to the desired ASM disk groups.
• If the database uses a server parameter file (SPFILE), then remove the CONTROL_FILES parameter that specifies locations of the control file. The control file will be moved to the DB_CREATE_* destination and the server parameter file will be automatically updated.

  If you are using a client-side parameter file (PFILE), then set the CONTROL_FILES parameter to the ASM alias for the control file name (for example, CONTROL_FILES=+disk_group/cf 1 ).

• Startup the database in nomount mode. For example: RMAN> STARTUP NOMOUNT;
• Restore the control file from backup into its new locations. If you are using a reovery catalog, then issue this command:

       RMAN> RESTORE CONTROLFILE;
      
  

  If you are not using a recovery catalog, then you will need to set your DBID and allocate channels to pe rform the restore of your control file. For example:

       RMAN> RESTORE CONTROLFILE FROM
   AUTOBACKUP;
      
  

• Mount the database. For example:

       RMAN> ALTER DATABASE MOUNT;
  
  

• Now restore database into new location, using RMAN commands. You can enter these manuall y, as shown in this example:

      RMAN> RUN {
  

          2>  SET  NEWNAME FOR DATAFILE 1 TO NEW;
          3>  SET  NEWNAME FOR DATAFILE 2 TO NEW;
          4> ...add one line for each datafile
          5>  RESTORE DATABASE;
          6>  SWITCH ALL;
          6>  }
  
  

  Be sure to include a SET NEWNAME FOR DATAFILE filenumber TO NEW stat ement for each datafile in the database.

  The RESTORE DATABASE command instruc ts RMAN to restore into location DB_CREATE_FILE_DEST, which has now been updated to refer to the ASM disk group. New nam es are generated for each datafile automatically.

  If the database has a large number of da tafiles, it is more convenient to use the following PL/SQL script to generate the required RMAN commands for the RESTORE operation:

        set serveroutput on;
         declare
            cursor df is select file# from v$datafile;
         begin
            dbms_o
  utput.put_line('run');
            dbms_output.put_line('{');
            for dfrec in df loo
  p
            dbms_output.put_line('set newname for datafile ' ||
  
        dfrec.file# || ' to new;');
         end loop;
         dbms_output.put_line('restore d
  atabase;');
         dbms_output.put_line('switch all;');
         dbms_output.put_line('}');
  
         end;
         
  

Running this PL/SQL code causes the SQL*Plus client to display an RMAN script which you can save to a file and run as a command file in the RMAN c lient.

After this operation all datafiles are in ASM.

Now you must create new tempfiles for the temporary tablespaces. For each temporary tablespace, execute the following command:

RMAN> SQL "ALTER TABLESPACE tablespacename ADD TEMPFILE;"

If this a primary database, then recover the database and perform an OPEN RESETLOGS on the database.

RMAN> RECOVER DATABASE;

RMAN> ALTER DATABASE OPEN RESETLOGS;

Note that you must use th e RESETLOGS option because the control file is restored from backup.

If you we re using change tracking for incremental backups, you can re-enable it now. For example:

SQ
L> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

If you are mig rating a standby database, do not open the database at this time. If your standby database has standby online logs stored in the flas h recovery area, then you must move standby online log files into ASM storage. For each standby online redo log file, you must create a new one in ASM, and delete an old one from the non-ASM storage. For a PL/SQL script that can perform this task for you, see "PL/SQL Scripts Used in Migrating to ASM Storage".

Migrating the Flash Recovery Area to ASM

The following procedure assumes t hat you have a flash recovery area in non-ASM disk storage and you need to move it to an ASM disk group, possibly preserving its cont ents.

1. If logging for Flashback Database is ena bled, then disable it. This is needed because the logs for Flashback Database are located in the flash recovery area. For example: < pre class="CE1">SQL> ALTER DATABASE FLASHBACK OFF;

2. 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, controlfile, or archive logs are in recovery area , then stop managed recovery mode and shutdown database.

3. Modify the initialization parameter file of the target database as follows:
   • Set DB_RECOVERY_FILE_DEST to the desired ASM disk group.
   • Modify DB_RECOVERY_FILE_DEST_SIZE if you need to change the size of the flash recovery area.
4. If you shut down the database in step 2, then bring the database to a NOMOUNT state. For exampl e:

   RMAN> STARTUP NOMOUNT
   
   

   If the old recovery area has copy of the current controlfile, then restore controlfile 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;
   
   

5. If you are using tape backups, then you should back up the entire flash recovery are a to tape at this time. For example:

   RMAN> BACKUP RECOVERY AREA;
   

   <
   a name="1014827">
   

   You can also use the DELETE INPUT option when backin g up the flash recovery area to tape, if you want to immediately free the non-ASM space previously used to store flash recovery area files:

   RMAN> BACKUP RECOVERY AREA DELETE INPUT;
   

   Note: If you do not have tape, you should not delete the flash recovery area st orage.

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

   SQL> ALTER DATABASE FLASHBACK ON;
   
   
   
   
   
   
   Now, optionally, you can move your backups
   from old recovery area to the new location. To move the existing backupsets and archived redo log files, use these two commands:
   RMAN> BACKUP AS COPY ARCHIVELOG ALL DELETE INPUT;
   RMAN> BACKUP DEV
   ICE TYPE DISK BACKUPSET ALL DELETE INPUT;
   
   
   
   
   
   
   Then you must move your datafile copies. For each datafile copy in the old recovery area, use this command:
   RMAN> BACKUP AS COPY DATAFILECOPY "name" DELETE INPUT;
   
   
   
   
   
   
   where name is the path to the datafile copy in th
   e old recovery area location.
    
   If the old recovery area location contains a large number of
    files, you can use the following PL/SQL script to generate the RMAN commands required to relocate the files:
   set serveroutput on;
   declare
     cursor dfc is select name from v$dataf
   ile_copy
                    where status = 'A'
                      and is_recovery_dest_file
    = 'YES';
   begin
     dbms_output.put_line('run');
     dbms_output.put_li
   ne('{');
       dbms_output.put_line('backup as copy archivelog all delete input;');
       dbm
   s_output.put_line('backup device type disk backupset all delete 
   input;');
     for dfcrec in dfc loop
       dbms_output.put_line('backup as copy datafilecopy ''' ||
                            dfcrec.name ||
    '''delete input;');
     end loop;
     dbms_output.put_line('}');
   end;
   
   
   
   
   

7. If this database is a standby database, then do not open the database at this point. If this database is a physical standby database, then restart ma naged recovery mode.

   If this is a primary database (that is, not a standby database), t hen open the database:

   RMAN> alter database open;  
   

8. If this is a standby database, then the online l ogs cannot be renamed at this point. You must delete the files containing the online redo logs at the operating system level. When th e standby database is activated, new online logs will automatically be added as ASM files.

   If this is a primary database and you had online redo log files in the flash recovery area, then you should set up your database to store the online redo logs in the ASM disk group. For each online redo log group, you must create a new online redo log in the ASM disk group, archive the current logs, and delete the old log member. For a PL/SQL script that can perform this task for you, see "Migrating Online Redo Logs to ASM Storage".

9. If this is a standby database and you had standby online logs in the recovery area, you shou ld move standby online logs into ASM. For a PL/SQL script that can perform this task for you, see "Migra ting Standby Online Redo Log Files to ASM Storage".

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

se
t 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;

declare
   cursor orlc
is select lf.member, l.bytes
                    from v$log l, v$logfile lf

      where l.group# = lf.group#
                     and lf.type = 'ONLINE'

       order by l.thread#, l.sequence#;
   type numTab_t is table of number index by binary_integer;
   type charTab_t is table of varchar2(1024) index by binary_integer;
   byteslist numTab_t;
   namelist  charTab_t;
   procedure migrateorlfile(name IN varchar2, bytes IN number) is
      retry    number;
      stmt     varchar2(1024);
      als      var
char2(1024) := 'alter system switch logfile';
   begin
      select count(*) into retry f
rom v$logfile;
      stmt := 'alter database add logfile size ' || bytes;
      execute i
mmediate stmt;
      stmt := 'alter database drop logfile ''' || name || '''';
      for
i in 1..retry loop
         begin
            execute immediate stmt;
<
/a>            exit;
         exception
            when others then
            if i > retry then
               raise;
            end if;
            execute immediate als;
        end;
     end loop;
   end;
begin
   open orlc;
   fetch orlc bulk collect into n
amelist, byteslist;
   close orlc;
   for i in 1..namelist.count loop
<
/a>      migrateorlfile(namelist(i), byteslist(i));
   end loop;
end;

declare
   cursor srlc is select lf.member, l.bytes

       from v$standby_log l, v$logfile lf
                   where l.group# = lf.group#

                    and lf.type = 'STANDBY';
   type numTab_t is table of number index by binary_integer;
   type charTab_t is table of varchar2(1024) index by binary_integer;
   byteslist numTab_t;
<
a name="1021791">   namelist  charTab_t;
   procedure migratesrl(name IN varchar2, bytes IN number) is
      stmt     varchar2(1024);
   begin
      stmt := 'alter database a
dd standby logfile size ' || bytes;
      execute immediate stmt;
      stmt := 'alter da
tabase drop standby logfile ''' || name || '''';
      execute immediate stmt;
   end;
begin
   open srlc;
   fetch srlc bulk collect into namelist, bytesli
st;
   close srlc;
   for i in 1..namelist.count loop
      migrate
srl(namelist(i), byteslist(i));
   end loop;
end;