Tuesday, January 17, 2012

Manually clone an Oracle database from a hot backup

Tasks performed on Source Database

/* 1. Perform switch for each Redo Log Group (V$LOG)*/
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;

/* 2. Note oldest online log sequence number */
ARCHIVE LOG LIST; 

/* 3. Copy database files */
ALTER DATABASE BEGIN BACKUP;
   --copy database files to new location
ALTER DATABASE END BACKUP;

/* 4. Perform switch for each Redo Log Group again.
   Note oldest online log sequence number.
   Copy archive logs with sequence numbers noted Before and After the data file backup */
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ARCHIVE LOG LIST;

/* 5. Create pfile for editing  */
create pfile='<path>/init<SID>.ora' from spfile;

/* 6. Create control file script */
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '<path>';



Tasks performed on Target Database

1. Edit the pfile for the new environment
    - File paths (*_DEST)
    - Memory settings
    - DBName
    - Achive Log Dest and Format indicators. For example:
       LOG_ARCHIVE_DEST_1='LOCATION=<path>'
       LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
       DB_RECOVERY_FILE_DEST_SIZE=<n>G
       DB_RECOVERY_FILE_DEST='<path>/flash_recovery_area'
   
2. Edit controlfile create script.

 - Remove all lines related to Set #1 (Using Set #2. in this case)
 - Remove STARTUP NOMOUNT
 - Remove RECOVER DATABASE USING BACKUP CONTROLFILE
 - Remove ALTER DATABASE OPEN RESETLOGS
 
 - Copy and Remove: ALTER TABLESPACE TEMP...

 - Add word SET to the CREATE CONTROL clause to rename the database.
      EG CREATE CONTROLFILE
         REUSE SET DATABASE <DB-NAME> RESETLOGS NOARCHIVELOG

      * RESETLOGS=Indicates contents of LOGFILE clause files are to be ignored
      * RESUE = Permits existing controlfiles to be overriden

3. Execute control file create script from NOMOUNT using edited PFILE.
    startup nomount pfile='<path>'
    SQL>@<create_control_file_script>

4. Perform Media recovery
   RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

5. Open with reset
   Alter database open resetlogs;

6. Generate archive log to validate path paths
   ALTER SYSTEM SWITCH LOGFILES

7. Create TEMP file.
  - ALTER TABLESPACE TEMP ADD TEMPFILE '<path>' ...

8. Create spfile from pfile. Restart;

9. Remove achived logs used in recovery.

No comments:

Post a Comment