Shulou(Shulou.com)06/01 Report--

Through IMAGE COPY technology, we can get a complete copy of the database, open the database quickly, and avoid the long time of database restore and recovery. The following is a case study of a complete database recovery using IMAGE COPY. Since the test is performed on the same database host, it is important to note that the ORACLE_SID environment variable and the files under the related path cannot be overwritten.

-1. Create a DB level 0 IMAGE COPY backup

[oracle@sqlaudit ~] $rman target / RMAN > BACKUP AS COPY INCREMENTAL LEVEL 0 TAG 'SRCDB-IMAGE' DATABASE FORMAT' / oradata/srcdb_img/SRCDB-LVL0-%U'

-- 2. Create test data

[oracle@sqlaudit ~] $sqlplus / as sysdbaSQL > create table scott.test_backup 2 as 3 select rownum rn from dual connect by rownum BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF TAG 'SRCDB-IMAGE' DATABASE FORMAT' / oradata/srcdb_img/SRCDB-LVL1-%U' PLUS ARCHIVELOG Starting backup at 2018-01-31 07:48:41current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting archived log backup setchannel ORA_DISK_1: specifying archived log (s) in backup setinput archived log thread=1 sequence=2 RECID=1 STAMP=961989888input archived log thread=1 sequence=3 RECID=2 STAMP=966842424input archived log thread=1 sequence=4 RECID=3 STAMP=966842833input archived log thread=1 sequence=5 RECID=4 STAMP=966842838input archived log thread=1 sequence=6 RECID=5 STAMP=966842911input archived log thread=1 sequence=7 RECID=6 STAMP=966842915input archived log thread=1 sequence=8 RECID=7 STAMP=966844055input archived log thread=1 sequence=9 RECID=8 STAMP=966844059input archived log thread=1 sequence=10 RECID=9 STAMP=966844121channel ORA_DISK_1: starting piece 1 at 07:48: 42channel ORA_DISK_1: finished piece 1 at 2018-01-31 07:48:43piece handle=/oracle/app/oracle/product/11.2.0.4/db_1/dbs/12sq1nmp_1_1 tag=SRCDB-IMAGE comment=NONEchannel ORA_DISK_1: backup set complete Elapsed time: 00:00:01Finished backup at 2018-01-31 07:48:43Starting backup at 2018-01-31 07:48:43using channel ORA_DISK_1channel ORA_DISK_1: starting incremental level 1 datafile backup setchannel ORA_DISK_1: specifying datafile (s) in backup setinput datafile file number=00001 name=/oracle/app/oracle/oradata/srcdb/system01.dbfinput datafile file number=00002 name=/oracle/app/oracle/oradata/srcdb/sysaux01.dbfinput datafile file number=00005 name=/oracle/app/oracle/oradata/srcdb/ggtbs01.dbfinput datafile File number=00003 name=/oracle/app/oracle/oradata/srcdb/undotbs01.dbfinput datafile file number=00004 name=/oracle/app/oracle/oradata/srcdb/users01.dbfchannel ORA_DISK_1: starting piece 1 at 2018-01-31 07:48:43channel ORA_DISK_1: finished piece 1 at 2018-01-31 07:48:44piece handle=/oradata/srcdb_img/SRCDB-LVL1-13sq1nmr_1_1 tag=SRCDB-IMAGE comment=NONEchannel ORA_DISK_1: backup set complete Elapsed time: 00:00:01channel ORA_DISK_1: starting incremental level 1 datafile backup setchannel ORA_DISK_1: specifying datafile (s) in backup setincluding current control file in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: starting piece 1 at 2018-01-31 07:48:45channel ORA_DISK_1: finished piece 1 at 2018-01-31 07:48:46piece handle=/oradata/srcdb_img/SRCDB-LVL1-14sq1nms_1_1 tag=SRCDB-IMAGE comment=NONEchannel ORA_DISK_1: backup set complete Elapsed time: 00:00:01Finished backup at 2018-01-31 07:48:46Starting backup at 2018-01-31 07:48:46current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting archived log backup setchannel ORA_DISK_1: specifying archived log (s) in backup setinput archived log thread=1 sequence=11 RECID=10 STAMP=966844126channel ORA_DISK_1: starting piece 1 at 2018-01-31 07:48:46channel ORA_DISK_1: finished piece 1 at 2018-01-31 07:48:47piece handle=/oracle/app/oracle/ Product/11.2.0.4/db_1/dbs/15sq1nmu_1_1 tag=SRCDB-IMAGE comment=NONEchannel ORA_DISK_1: backup set complete Elapsed time: 00:00:01Finished backup at 2018-01-31 07:48:47

-- 4. Restore DB level 0 IMAGE COPY backups

RMAN > RECOVER COPY OF DATABASE WITH TAG 'SRCDB-IMAGE' Starting recover at 2018-01-31 07:48:57using channel ORA_DISK_1channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile copies to recoverrecovering datafile copy file number=00001 name=/oradata/srcdb_img/SRCDB-LVL0-data_D-SRCDB_I-595837900_TS-SYSTEM_FNO-1_0nsq1nivrecovering datafile copy file number=00002 name=/oradata/srcdb_img/SRCDB-LVL0-data_D-SRCDB_I-595837900_TS-SYSAUX_FNO-2_0osq1nj2recovering datafile copy file number=00003 name=/oradata/ Srcdb_img/SRCDB-LVL0-data_D-SRCDB_I-595837900_TS-UNDOTBS1_FNO-3_0qsq1njkrecovering datafile copy file number=00004 name=/oradata/srcdb_img/SRCDB-LVL0-data_D-SRCDB_I-595837900_TS-USERS_FNO-4_0rsq1njlrecovering datafile copy file number=00005 name=/oradata/srcdb_img/SRCDB-LVL0-data_D-SRCDB_I-595837900_TS-GGTBS_FNO-5_0psq1njhchannel ORA_DISK_1: reading from backup piece / oradata/srcdb_img/SRCDB-LVL1-0vsq1nko _ 1_1channel ORA_DISK_1: piece handle=/oradata/srcdb_img/SRCDB-LVL1-0vsq1nko_1_1 tag=SRCDB-IMAGEchannel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete Elapsed time: 00:00:01channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile copies to recoverrecovering datafile copy file number=00001 name=/oradata/srcdb_img/SRCDB-LVL0-data_D-SRCDB_I-595837900_TS-SYSTEM_FNO-1_0nsq1nivrecovering datafile copy file number=00002 name=/oradata/srcdb_img/SRCDB-LVL0-data_D-SRCDB_I-595837900_TS-SYSAUX_FNO-2_0osq1nj2recovering datafile copy file number=00003 name=/oradata/srcdb_img/SRCDB-LVL0-data_D -SRCDB_I-595837900_TS-UNDOTBS1_FNO-3_0qsq1njkrecovering datafile copy file number=00004 name=/oradata/srcdb_img/SRCDB-LVL0-data_D-SRCDB_I-595837900_TS-USERS_FNO-4_0rsq1njlrecovering datafile copy file number=00005 name=/oradata/srcdb_img/SRCDB-LVL0-data_D-SRCDB_I-595837900_TS-GGTBS_FNO-5_0psq1njhchannel ORA_DISK_1: reading from backup piece / oradata/srcdb_img/SRCDB-LVL1-13sq1nmr_1_1channel ORA_DISK_1: piece handle= / oradata/srcdb_img/SRCDB-LVL1-13sq1nmr_1_1 tag=SRCDB-IMAGEchannel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete Elapsed time: 00:00:01Finished recover at 2018-01-31 07:48:59

-5. The source database performs backup control file operations to obtain the information in the log

SQL > alter database backup controlfile to trace;-- Set # 1. NORESETLOGS caseSTARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "SRCDB" NORESETLOGS FORCE LOGGING ARCHIVELOG.

-6. Create a new object for audit in subsequent full recovery

SQL > create table scott.test22 (id number); SQL > alter system switch logfile;SQL > shutdown immediate

-7. The parameter file required to create the IMAGE COPY database (the db_name in the parameter file cannot be modified, and the audit_file_dest and control_files paths must be adjusted)

[oracle@sqlaudit ~] $cd $ORACLE_HOME/ DBs [oracle @ sqlaudit dbs] $cp orapwsrcdb orapwsrcdbnew [oracle@sqlaudit dbs] $strings spfilesrcdb.ora > initsrcdbnew.ora [oracle@sqlaudit dbs] $cat initsrcdbnew.ora | grep'/ oracle/'*.audit_file_dest='/oracle/app/oracle/admin/srcdbnew/adump'*.control_files='/oradata/srcdb_img/control01.ctl','/oradata/srcdb_img/control02.ctl' [oracle@sqlaudit dbs] $mkdir-p/oracle/ app/oracle/admin/srcdbnew/adump

-8. Start the database instance

[oracle@sqlaudit dbs] $export ORACLE_SID= srcdbnewn [oracle @ sqlaudit dbs] $sqlplus / as sysdbaSQL > startup nomount

-- 9. Copy online log files to a new directory for full recovery

[oracle@sqlaudit archive] $cp / oracle/app/oracle/oradata/srcdb/redo01.log / oradata/srcdb_img/ redo01.log [oracle @ sqlaudit archive] $cp / oracle/app/oracle/oradata/srcdb/redo02.log / oradata/srcdb_img/redo02.log [oracle@sqlaudit archive] $cp / oracle/app/oracle/oradata/srcdb/redo03.log / oradata/srcdb_img/redo03.log

-10. Rebuild control file

CREATE CONTROLFILE REUSE DATABASE "SRCDB" NORESETLOGS MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292LOGFILE GROUP 1'/ oradata/srcdb_img/redo01.log' SIZE 50m BLOCKSIZE 512, GROUP 2'/ oradata/srcdb_img/redo02.log' SIZE 50m BLOCKSIZE 512, GROUP 3'/ oradata/srcdb_img/redo03.log' SIZE 50m BLOCKSIZE 512DATAFILE'/ oradata/srcdb_img/SRCDB-LVL0-data_D-SRCDB_I-595837900_TS-SYSTEM_FNO-1_0nsq1niv' '/ oradata/srcdb_img/SRCDB-LVL0-data_D-SRCDB_I-595837900_TS-SYSAUX_FNO-2_0osq1nj2',' / oradata/srcdb_img/SRCDB-LVL0-data_D-SRCDB_I-595837900_TS-UNDOTBS1_FNO-3_0qsq1njk','/ oradata/srcdb_img/SRCDB-LVL0-data_D-SRCDB_I-595837900_TS-USERS_FNO-4_0rsq1njl' '/ oradata/srcdb_img/SRCDB-LVL0-data_D-SRCDB_I-595837900_TS-GGTBS_FNO-5_0psq1njh'CHARACTER SET WE8MSWIN1252 SQL > SELECT NAME FROM V$DATAFILE

-- 11. Source library check check log files that need to be registered

[oracle@sqlaudit dbs] $export ORACLE_SID=srcdbSQL > startup mount;RMAN > list backup of archivelog all BS Key Size Device Type Elapsed Time Completion Time-19 3.50K DISK 00:00:00 2018-01-31 07:48:46 BP Key: 19 Status: AVAILABLE Compressed: NO Tag: SRCDB -IMAGE Piece Name: / oracle/app/oracle/product/11.2.0.4/db_1/dbs/15sq1nmu_1_1 List of Archived Logs in backup set 19 Thrd Seq Low SCN Low Time Next SCN Next Time- -1 11 969636 2018-01-31 07:48:41 969648 2018-01-31 07:48:46

-12. The new library registers the log file of the source library

SQL > ALTER DATABASE REGISTER LOGFILE'/ oracle/archive/1_11_961988430.dbf';SQL > ALTER DATABASE REGISTER LOGFILE'/ oracle/archive/1_12_961988430.dbf';SQL > ALTER DATABASE REGISTER LOGFILE'/ oracle/archive/1_13_961988430.dbf';SQL > ALTER DATABASE REGISTER LOGFILE'/ oracle/archive/1_14_961988430.dbf';SQL > RECOVER DATABASE;SQL > ALTER DATABASE OPEN

-- 13. Create a new temporary file

SQL > ALTER TABLESPACE TEMP ADD TEMPFILE'/ oradata/srcdb_img/temp01.dbf' SIZE 100m AUTOEXTEND OFF

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.