Skip Headers
Oracle® Database Backup and Recovery User's Guide
11g Release 1 (11.1)

Part Number B28270-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

26 Performing ASM Data Migration

This chapter describes how to migrate data into and out of Automatic Storage Management (ASM) storage with RMAN. This chapter includes the following topics:

Overview of ASM Data Migration

This section explains the basic concepts and tasks involved in migrating data to and from ASM.

Purpose of ASM Data Migration

Alternatives to ASM storage include file systems, raw disks, and SAN configurations. ASM includes a number of benefits over these storage alternatives, including performance optimization, redundancy protection, and load balancing. You do not need to obtain a third-party Logical Volume Manager because ASM manages disks for you. Oracle Real Application Clusters (Oracle RAC) databases benefit from ASM because it provides ready-made shared storage.

If a database currently uses a storage system other than ASM, then you can migrate all or part of the database into ASM, thereby simplifying database administration. You can also migrate a flash recovery area to ASM.

Native operating system commands such as Linux cp or Windows COPY cannot write or read files in ASM storage. Because RMAN can read and write ASM files, you can use RMAN to copy datafiles into and out of ASM storage or between ASM disk groups. This technique is useful if you need to store backups on user-managed disks.

Basic Concepts of ASM Data Migration

You can migrate data to ASM with RMAN even if you are not using RMAN as your primary backup tool. The migration requires one RMAN database backup.

If you have sufficient disk space to hold the entire database both in ASM and alternative storage systems, then you can move a database directly into ASM. If you do not have sufficient storage, then you can back the database up to tape, create an ASM disk group that uses old disk space, and restore the database from tape to ASM.

After you set the location of the new recovery area, existing backups remain in the old recovery area and count against the total disk quota of the recovery area. The backups are deleted from the old recovery area when space is needed. These backups are usable by RMAN. You do not need to move legacy backups to the new ASM recovery area unless you need disk space. To free space consumed by files in the old recovery area, you can back them up to tape or migrate them to the ASM recovery area.

Note:

A foreign archived redo log is a log received by a logical standby database for a LogMiner session. Foreign archived redo logs cannot be migrated.

Migrating a database from ASM to an alternative storage system is similar to migration from an alternative storage system to ASM. The primary change is to modify each step to refer to file locations in the alternative storage system.

See Also:

Oracle Database Storage Administrator's Guide to learn how to migrate the database to ASM with Enterprise Manager

Basics Steps of Data Migration to ASM

The basic steps of migrating the whole database and flash recovery area from alternative storage to ASM are as follows:

  1. Back up the database and server parameter file, and disable Oracle Flashback Database.

    This step is described in "Preparing to Migrate the Database to ASM".

  2. Restore files to ASM, recover the database, and optionally migrate the flash recovery area to ASM.

    This step is described in "Migrating the Database to ASM".

To migrate files from alternative storage to ASM, see "Migrating a Database from ASM to Alternative Storage".

Preparing to Migrate the Database to ASM

This section explains how to prepare the database for migration. This section makes the following assumptions:

Note:

If you do not want to migrate the flash recovery area, then skip step 10.

To prepare the database for ASM migration:

  1. If the COMPATIBLE setting for the database is less than 11.0.0, then make any read-only transportable tablespaces read/write.

    Read-only transportable tablespaces cannot be migrated because RMAN cannot back them up.

  2. If the database is a physical standby database, and if managed recovery is started, then stop managed recovery.

    The following example starts a SQL*Plus session on the database and stops managed recovery:

    % sqlplus /nolog
    SQL> CONNECT SYS/password@mydb AS SYSDBA
    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
    

    Keep this terminal window open.

  3. Copy the server parameter file or initialization parameter file to a temporary location.

    The following example uses an operating system utility to copy the server parameter file:

    % cp spfileMYDB.ora orig_spfileMYDB.ora
    
  4. In a new terminal window, start an RMAN session on the database to be migrated.

    The following example connects RMAN to the recovery catalog and the database to be migrated:

    % rman
    RMAN> CONNECT TARGET SYS/password@mydb
    RMAN> CONNECT CATALOG rman/password@catdb
    
  5. Back up the datafiles to the ASM disk group.

    The following example uses a RUN command to make a level 0 incremental backup and allocates four channels to increase the backup speed. Increase or decrease this number accordingly. The format clause specifies +DATA, which is the name of the ASM disk group to be used for storing the database.

    RUN
    {
      ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
      ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
      ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;
      ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;
      BACKUP AS COPY
        INCREMENTAL LEVEL 0
        DATABASE
        FORMAT '+DATA'
        TAG 'ORA_ASM_MIGRATION';
    }
    
  6. If block change tracking is enabled for the database, then optionally make a level 1 incremental backup that you can use later to recover the database copy.

    The following example makes an incremental level 1 copy of the level 0 backup created in the previous step:

    RUN
    {
      ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
      ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
      ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;
      ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;
      BACKUP INCREMENTAL LEVEL 1 
        FOR RECOVER OF COPY WITH TAG 'ORA_ASM_MIGRATION' 
        DATABASE;
    }
    
  7. If the database is in ARCHIVELOG mode, and if the database is open, then archive the online logs.

    The following example uses the SQL command to archive the current redo logs:

    RMAN> SQL "ALTER SYSTEM ARCHIVE LOG CURRENT";
    
  8. If the database instance is currently using a server parameter file, then back it up.

    The following example backs up the server parameter file:

    RMAN> BACKUP AS BACKUPSET SPFILE;
    
  9. If block change tracking is enabled, then disable it.

    The following command disables block change tracking:

    RMAN> SQL "ALTER DATABASE DISABLE BLOCK CHANGE TRACKING";
    
  10. If Flashback Database is enabled, then disable it and drop any guaranteed restore points.

    Note:

    If you are not migrating the recovery area, then skip this step.

    Disabling Flashback Database is necessary because you cannot migrate flashback logs to ASM. The following command disables Flashback Database:

    RMAN> SQL "ALTER DATABASE FLASHBACK OFF";
    

    The following command drops the guaranteed restore point named Q106:

    RMAN> SQL "DROP RESTORE POINT Q106";
    
  11. Shut down the database consistently.

    The following command shuts down the database:

    RMAN> SHUTDOWN IMMEDIATE;
    

Migrating the Database to ASM

The following procedure is intended to minimize database downtime. Note that the steps differ slightly depending on whether you are migrating a primary or standby database. The procedure makes the same assumptions described in "Preparing to Migrate the Database to ASM". If you are not migrating the recovery area to ASM, then you need to modify some of the steps, which are noted.

Note:

The following procedure switches between SQL*Plus and RMAN, so keep a terminal window open for each utility.

To migrate the database to ASM:

  1. Follow the steps in "Preparing to Migrate the Database to ASM".

  2. Restore or create a server parameter file in ASM storage.

    The steps depend on whether the database is using a server parameter file:

    • If the database is using a server parameter file, then restore it to the ASM disk group with the following commands, where sid is the SID of the instance:

      RMAN> STARTUP MOUNT;
      RMAN> RESTORE SPFILE TO '+DATA/spfilesid.ora';
      RMAN> SHUTDOWN IMMEDIATE;
      
    • If the database is not using a server parameter file, then create one in ASM. Execute the CREATE SPFILE command in SQL*Plus as follows, where sid is the SID of the database (the command spans two lines):

      SQL> CREATE SPFILE='+DATA/spfilesid.ora' FROM PFILE='?/dbs/initsid.ora';
      

      Afterward, delete spfilesid.ora and initsid.ora from the ?/dbs directory and create a new initsid.ora with the following line of content:

      SPFILE='+DATA/spfilesid.ora'
      
  3. Set Oracle Managed Files initialization parameters to ASM locations.

    Note:

    If you are not migrating the flash recovery area, then do not change the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE initialization parameter settings. However, you must set DB_CREATE_ONLINE_LOG_DEST_n parameter to an ASM location for migration of the online redo logs.

    Set the DB_CREATE_FILE_DEST and optional DB_CREATE_ONLINE_LOG_DEST_n initialization parameters to ASM disk groups. If the database uses a recovery area, then change the recovery area location to the ASM disk group. Also, change the recovery area size.

    Execute commands in SQL*Plus as shown in the following example. The example assumes that the size of the flash recovery area is 100 GB and specifies the disk group +FRA for the flash recovery area.

    SQL> STARTUP FORCE NOMOUNT;
    SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST='+DATA' SID='*';
    SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=100G SID='*';
    SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+FRA' SID='*';
    
  4. Set the CONTROL_FILES initialization parameter to ASM locations.

    If you are migrating the flash recovery area, then enter the following commands in SQL*Plus to restart the database instance and set the control file locations to disk groups +DATA and +FRA:

    SQL> STARTUP FORCE NOMOUNT;
    SQL> ALTER SYSTEM SET CONTROL_FILES='+DATA','+FRA' SCOPE=SPFILE SID='*';
    

    If you are not migrating the flash recovery area, then enter the following commands in SQL*Plus to restart the database instance and set the control file locations to disk group +DATA:

    SQL> STARTUP FORCE NOMOUNT;
    SQL> ALTER SYSTEM SET CONTROL_FILES='+DATA','+DATA' SCOPE=SPFILE SID='*';
    
  5. Migrate the control file to ASM and mount the control file.

    Switch to the RMAN terminal to restore the control file. In the following example, original_cf_name is a control file name in the initialization parameter file before migration:

    RMAN> STARTUP FORCE NOMOUNT;
    RMAN> RESTORE CONTROLFILE FROM 'original_cf_name';
    RMAN> ALTER DATABASE MOUNT;
    
  6. Migrate the datafiles to ASM.

    Use RMAN to switch to the database copy that you created in step 5 in "Preparing to Migrate the Database to ASM". The switch renames all the datafiles to files on ASM disk groups. Afterward, recover the database. If incremental backups were taken, then RMAN applies them during recovery. For example, enter the following commands at the RMAN prompt:

    SWITCH DATABASE TO COPY;
    RUN
    {
      ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
      ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
      ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;
      ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;
      RECOVER DATABASE;
    }
    
  7. If the database uses block change tracking or Flashback Database, then enable these features.

    Note:

    If you are not migrating the recovery area, then you do not need to enable Flashback Database because you did not disable it.

    For example, enter the following statements in SQL*Plus:

    SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '+DATA';
    SQL> ALTER DATABASE FLASHBACK ON;
    
  8. Place the database in its normal operation mode.

    The normal operational mode depends on whether the database is a primary or standby database:

    • If the database is a primary database, then open it as follows:

      SQL> ALTER DATABASE OPEN;
      
    • If the database is a standby database, then resume managed recovery mode as follows:

      SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
      
  9. Drop the tempfiles and re-create them in ASM.

    Use SQL*Plus to re-create the tempfiles. In the following example, the name of the tempfile in the original storage is tempfile_name. The name of the temporary tablespace is temp_tbs_name.

    SQL> ALTER DATABASE TEMPFILE 'tempfile_name' DROP;
    SQL> ALTER TABLESPACE temp_tbs_name ADD TEMPFILE;
    
  10. Migrate the online redo log files.

    If this is a primary database, then add new log group members in ASM and drop the old members. You can use the following PL/SQL script to migrate the online redo log groups into an ASM disk group. The PL/SQL script assumes that the Oracle Managed Files initialization parameters specified in step 3 are set.

    Example 26-1 Migrating the Online Redo Logs

    SET SERVEROUTPUT ON;
    DECLARE
       CURSOR rlc IS
          SELECT GROUP# GRP, THREAD# THR, BYTES, 'NO' SRL
          FROM   V$LOG
          UNION
          SELECT GROUP# GRP, THREAD# THR, BYTES, 'YES' SRL
          FROM   V$STANDBY_LOG
          ORDER BY 1;
       stmt     VARCHAR2(2048);
    BEGIN
       FOR rlcRec IN rlc LOOP
          IF (rlcRec.srl = 'YES') THEN
             stmt := 'ALTER DATABASE ADD STANDBY LOGFILE THREAD ' ||
                     rlcRec.thr || ' SIZE ' || rlcRec.bytes;
             EXECUTE IMMEDIATE stmt;
             stmt := 'ALTER DATABASE DROP STANDBY LOGFILE GROUP ' || rlcRec.grp;
             EXECUTE IMMEDIATE stmt;
          ELSE
             stmt := 'ALTER DATABASE ADD LOGFILE THREAD ' ||
                     rlcRec.thr || ' SIZE ' ||  rlcRec.bytes;
             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 'ALTER SYSTEM SWITCH LOGFILE';
                   EXECUTE IMMEDIATE 'ALTER SYSTEM CHECKPOINT GLOBAL';
                   EXECUTE IMMEDIATE stmt;
             END;
          END IF;
       END LOOP;
    END;
    /
    
  11. Optionally, migrate backups and copies in the old flash recovery area to ASM as follows:

    1. If foreign archived logs exists in the recovery area, then you cannot migrate them to ASM. Execute the following command at the RMAN prompt:

      RMAN> DELETE REMOTE ARCHIVELOG ALL;
      
    2. Back up archived redo log files, backup sets, and datafile copies to ASM. For example, execute the following command at the RMAN prompt:

      RUN
      {
        ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
        ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
        ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;
        ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;
      
        BACKUP AS COPY ARCHIVELOG ALL DELETE INPUT;
        BACKUP BACKUPSET ALL DELETE INPUT;
        BACKUP AS COPY DATAFILECOPY ALL DELETE INPUT;
      }
      

Migrating a Database from ASM to Alternative Storage

Migrating a database from ASM to an alternative storage system is essentially the reverse of the migration to ASM. Modify the steps in "Preparing to Migrate the Database to ASM" and "Migrating the Database to ASM" as follows:

Moving Datafiles Between ASM Disk Groups

You may want to move an active datafile in an ARCHIVELOG mode database from one ASM disk group to another. In this case, you use BACKUP AS COPY to copy the datafile to the new disk group and SET NEWNAME and SWITCH commands to rename the datafile in the control file.

For this scenario, assume that you use ASM disk groups ASMDSK2 and ASMDSK1. You want to move datafile +ASMDSK2/rdbms/datafile/tbs_5.256.565313879 to disk group ASMDSK1.

To move a datafile from one ASM disk group to another ASM disk group:

  1. Start an RMAN session on the target database and generate a report that shows the names of the datafiles.

    For example, execute the following commands after starting the RMAN client. Note both the datafile number and the datafile name of the file to be moved.

    CONNECT TARGET SYS/password@prod1
    REPORT SCHEMA;
    
  2. Back up the datafile to the new ASM disk group.

    For example, issue the following BACKUP AS COPY command to back up the datafile on ASMDSK2 to ASMDSK1:

    BACKUP AS COPY
      DATAFILE "+ASMDSK2/rdbms/datafile/tbs_5.256.565313879"
      FORMAT   "+ASMDSK1";
    

    You could also specify the datafile by datafile number as in the following example:

    BACKUP AS COPY
      DATAFILE 23
      FORMAT   "+ASMDSK1";
    
  3. Find the name of the datafile that you intend to move to a new disk group and take it offline.

    For example, execute the following SQL command in the RMAN client. Note that the you use two single quotes around the name of the datafile:

    SQL "ALTER DATABASE DATAFILE 
      ' ' +ASMDSK2/rdbms/datafile/tbs_5.256.565313879 ' ' OFFLINE";
    
  4. Point the control file to the newly created copy of the datafile.

    For example, run the SWITCH ... TO COPY command in the RMAN client as follows. The TO COPY option of SWITCH switches the datafile to the most recent copy of the datafile. You can specify the datafile by name or number.

    SWITCH DATAFILE "+ASMDSK2/rdbms/datafile/tbs_5.256.565313879" TO COPY;
    

    The output of this command will display the new name of the datafile.

  5. Recover the renamed datafile.

    For example, run the RECOVER command in the RMAN client as follows. Note that you can specify the datafile by name or number.

    RECOVER DATAFILE "+ASMDSK1/rdbms/datafile/tbs_5.256.603733209";
    
  6. Bring the datafile online.

    For example, execute the SQL command in the RMAN client as follows:

    SQL "ALTER DATABASE DATAFILE
      ' ' +ASMDSK1/rdbms/datafile/tbs_5.256.603733209 ' ' ONLINE";
    
  7. Delete the datafile copy from the original ASM disk group.

    In this scenario, +ASMDSK2/rdbms/datafile/tbs_5.256.565313879 is the original datafile in ASMDSK2. Because you issued SET NEWNAME and SWITCH commands for this datafile, the original file is now recorded in the RMAN repository as a datafile copy. You can execute a DELETE command in the RMAN client as follows to remove this file:

    DELETE DATAFILECOPY "+ASMDSK2/rdbms/datafile/tbs_5.256.603733209";