The following procedure is intended to minimize database downtime. 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 Oracle ASM Using RMAN" on page 8-3. If you are not migrating the recovery area to Oracle ASM, then you must modify some of the steps, which are noted.
To migrate the database to Oracle ASM:
1. Follow the steps in "Preparing to Migrate the Database to Oracle ASM Using RMAN" on page 8-3.
2. Restore or create a server parameter file in Oracle ASM storage.
Note: If you are not migrating the recovery area, then skip this step.
Note: The following procedure switches between SQL*Plus and RMAN, so keep a terminal window open for each utility.
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 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. 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 Oracle ASM locations.
Set the DB_CREATE_FILE_DEST and optional DB_CREATE_ONLINE_LOG_DEST_
n initialization parameters to Oracle ASM disk groups. If the database uses a recovery area, then change the recovery area location to the Oracle 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 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='*';
4. Set the CONTROL_FILES initialization parameter to Oracle ASM locations.
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='*';
5. Migrate the control file to Oracle ASM and mount the control file.
Note: If you are not migrating the fast 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 Oracle ASM location for migration of the online redo logs.
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 data files to Oracle ASM.
Use RMAN to switch to the database copy that you created in step 5 in "Preparing to Migrate the Database to Oracle ASM Using RMAN" on page 8-3. 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;
}
7. If the database uses block change tracking or Flashback Database, then enable these features.
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 Oracle 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.
Note: If you are not migrating the recovery area, then you do not need to enable Flashback Database because you did not disable it.
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. The PL/SQL script
assumes that the Oracle Managed Files initialization parameters specified in step 3 are set.
Example 8–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 fast recovery area to Oracle ASM as follows:
a. If foreign archived logs exists in the recovery area, then you cannot migrate them to Oracle ASM. Execute the following command at the RMAN prompt:
RMAN> DELETE REMOTE ARCHIVELOG ALL;
b. Back up archived redo log files, backup sets, and data file copies to Oracle 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;
}