[Oracle] Migrating NON-ASM to ASM

// non-asm 데이터베이스를 asm으로 마이그레이션하기

 

// spfile backup

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

RMAN> SQL "ALTER SYSTEM ARCHIVE LOG CURRENT";

 

// If the database instance is currently using a server parameter file, then back it up.

RMAN> BACKUP AS BACKUPSET SPFILE; // If block change tracking is enabled, then disable it. RMAN> SQL "ALTER DATABASE DISABLE BLOCK CHANGE TRACKING"; // If Flashback Database is enabled, then disable it and drop any guaranteed restore points. 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.

RMAN> SHUTDOWN IMMEDIATE;

 

// Restore or create a server parameter file in Oracle ASM storage.

RMAN> STARTUP MOUNT; RMAN> RESTORE SPFILE TO '+DATA/spfilesid.ora'; RMAN> SHUTDOWN IMMEDIATE;

 

// Set Oracle Managed Files initialization parameters to Oracle ASM locations.

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

 

// Set the CONTROL_FILES initialization parameter to Oracle ASM locations.

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:

 

// Migrate the control file to Oracle ASM and mount the control file.

RMAN> STARTUP FORCE NOMOUNT; RMAN> RESTORE CONTROLFILE FROM 'original_cf_name'; RMAN> ALTER DATABASE MOUNT;

 

// Migrate the data files to Oracle ASM.

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;

 

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

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

 

// Optionally, migrate backups and copies in the old fast recovery area to Oracle ASM as follows:

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