Skip to content

Migrating to Oracle ASM Using RMAN

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

    Native operating system commands such as Linux cp or Windows COPY cannot write or read files in Oracle ASM storage. Because RMAN can read and write Oracle ASM files, you can use RMAN to copy data files into and out of Oracle ASM storage or between Oracle ASM disk groups.

    Preparing Migrating to Oracle ASM Using RMAN

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

    • You want to migrate the database to two Oracle ASM disk groups: +DATA for the database and +FRA for the fast recovery area.
    • The database to be migrated to Oracle ASM storage is named mydb.

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

    A physical standby database is a copy of a production database that you can use for disaster protection.

    For example, connect SQL*Plus to the database with SYSBACKUP privileges (rather than SYSDBA privileges) to enforce the separation of duty security model, and run the following statement to stop managed recovery:

    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

    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

    Back up the data files to the Oracle ASM disk group.

    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';
    }

    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;
    }

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

    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;

    If block change tracking is enabled, then disable it.

    The following command disables block change tracking:

    RMAN> SQL "ALTER DATABASE DISABLE BLOCK CHANGE TRACKING";

    If Flashback Database is enabled, then disable it and drop any guaranteed restore points.

    Disabling Oracle Flashback Database is necessary because you cannot migrate flashback logs to Oracle 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";

    Shut down the database consistently.

    The following command shuts down the database:

    RMAN> SHUTDOWN IMMEDIATE;

    Migrating the Database to Oracle ASM Using RMAN

    If the database is using a server parameter file, then restore it to the Oracle 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 Oracle ASM. Run the CREATE SPFILE command in SQL*Plus as follows, where sid is the SID of the database:

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

    Run commands in SQL*Plus as shown in the following example. The example assumes that the size of the fast recovery area is 100 GB and specifies the disk group +FRA for the fast 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='*';

    If you are migrating the fast 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 fast 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='*';
    

    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;

    Migrate the data files to Oracle ASM.

    The switch renames all the data files to files on Oracle 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;
    }

    If the database uses block change tracking or Flashback Database, then enable these features.

    SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '+DATA';
    SQL> ALTER DATABASE FLASHBACK ON;

    Place the database in its normal operation mode.

    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;

    Drop the tempfiles and re-create them in Oracle ASM.

    SQL> ALTER DATABASE TEMPFILE 'tempfile_name' DROP;
    SQL> ALTER TABLESPACE temp_tbs_name ADD TEMPFILE;

    Migrate the online redo log files.

    If this is a primary database, then add new log group members in Oracle ASM and drop the old members. You can use the following PL/SQL script to migrate the online redo log groups into an Oracle ASM disk group.

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

    RMAN> DELETE ARCHIVELOG ALL;

    Back up archived redo log files, backup sets, and data file copies to Oracle ASM. For example, run 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 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;
    /
    

    For More Information check Oracle Help Center:

    Migrate to Oracle ASM