Oracle cancel-based Incomplete recovery

Oracle cancel-based Incomplete recovery

Experiment: Oracle cancel-based Incomplete recovery

View lab environment

Lsnrctl status
Select open_mode from v $ database;
-- Monitoring and database status

Show parameter recovery;
Select flashback_on from v $ database;
Archive log list;
-- Configuration of flash back and archive

1) Prepare the environment: RMAN full-Database Backup
RMAN> backup as compressed backupset full database;
-- Compression backup
-- Backup full database; backup set

Starting backup at 20-MAR-13
Using target database control file instead of recovery catalog
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: SID = 45 device type = DISK
Channel ORA_DISK_1: starting compressed full datafile backup set
Channel ORA_DISK_1: specifying datafile (s) in backup set
Input datafile file number = 00001 name =/u01/app/oracle/oradata/ORA11GR2/system01.dbf
Input datafile file number = 00002 name =/u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf
Input datafile file number = 00003 name =/u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf
Input datafile file number = 00005 name =/u01/app/oracle/oradata/ORA11GR2/example01.dbf
Input datafile file number = 00004 name =/u01/app/oracle/oradata/ORA11GR2/users01.dbf
Channel ORA_DISK_1: starting piece 1 at 20-MAR-13
Channel ORA_DISK_1: finished piece 1 at 20-MAR-13
Piece handle =/u01/app/FRA/ORA11GR2/backupset/2013_03_20/empty _. bkp tag = TAG20130320T151949 comment = NONE
Channel ORA_DISK_1: backup set complete, elapsed time: 00:02:17
Channel ORA_DISK_1: starting compressed full datafile backup set
Channel ORA_DISK_1: specifying datafile (s) in backup set
Including current control file in backup set
Including current SPFILE in backup set
Channel ORA_DISK_1: starting piece 1 at 20-MAR-13
Channel ORA_DISK_1: finished piece 1 at 20-MAR-13
Piece handle =/u01/app/FRA/ORA11GR2/backupset/2013_03_20/o1_mf_ncsnf_TAG20130320T151949_8nlrx2qs _. bkp tag = TAG20130320T151949 comment
Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-MAR-13

 

 

2) create Test Data
SYS @ ORA11GR2> create table scott. t1 as select 1 as id from dual;

Table created.

SYS @ ORA11GR2> alter system archive log current;

System altered.

SYS @ ORA11GR2> create table scott. t2 as select 2 as id from dual;

Table created.

SYS @ ORA11GR2> alter system archive log current;

System altered.

SYS @ ORA11GR2> create table scott. t3 as select 3 as id from dual;

Table created.

SYS @ ORA11GR2> alter system archive log current;

System altered.

SYS @ ORA11GR2>
SYS @ ORA11GR2> select table_name, tablespace_name from dba_tables where owner = 'Scott 'AND table_name LIKE't _';

TABLE_NAME TABLESPACE_NAME
-------------------------------------------------------
T3 USERS
T2 USERS
T1 USERS

After constructing the test data, view the generated archive logs.
Ls/u01/app/oracle/fast_recovery_area/PROD/archivelog/2014_10_08
Ls-lrt

3) delete all data files and online data files

Delete in sqlplus:
SYS @ PROD> select name from v $ datafile;

NAME
--------------------
/U01/app/oracle/oradata/PROD/datafile/o1_mf_system_b2251bs1 _. dbf

/U01/app/oracle/oradata/PROD/datafile/o1_mf_sysaux_b2251bvo _. dbf

/U01/app/oracle/oradata/PROD/datafile/o1_mf_undotbs1_b2251bw5 _. dbf

/U01/app/oracle/oradata/PROD/datafile/o1_mf_users_b2251byw _. dbf

/U01/app/oracle/oradata/PROD/datafile/o1_mf_example_b2257d0c _. dbf

/U01/app/oracle/oradata/PROD/datafile/tbs_move_01.dbf

SYS @ PROD> select member from v $ logfile;

MEMBER
Certificate ----------------------------------------------------------------------------------------------------------------------------------
/U01/app/oracle/oradata/PROD/onlinelog/o1_mf_3_b22567o2 _. log
/U01/app/oracle/fast_recovery_area/PROD/onlinelog/o1_mf_3_b2256827 _. log
/U01/app/oracle/oradata/PROD/onlinelog/o1_mf_2_b2255zsg _. log
/U01/app/oracle/fast_recovery_area/PROD/onlinelog/o1_mf_2_b22560gb _. log
/U01/app/oracle/oradata/PROD/onlinelog/o1_mf_1_b2255npg _. log
/U01/app/oracle/fast_recovery_area/PROD/onlinelog/ow.mf_1_b2255nxl _. log

SYS @ PROD>! Rm/u01/app/oracle/oradata/PROD/datafile/*. dbf;

SYS @ PROD>! Rm/u01/app/oracle/oradata/PROD/onlinelog/*. log;

SYS @ PROD>! Rm/u01/app/oracle/fast_recovery_area/PROD/onlinelog/*. log;

Delete in OS:
[Oracle @ ocmu ORA11GR2] $ pwd
/U01/app/oracle/oradata/ORA11GR2
[Oracle @ ocmu ORA11GR2] $ ls
Control01.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
Control02.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
[Oracle @ ocmu ORA11GR2] $ rm *. log
[Oracle @ ocmu ORA11GR2] $ ls
Control01.ctl example01.dbf system01.dbf undotbs01.dbf
Control02.ctl sysaux01.dbf temp01.dbf users01.dbf
[Oracle @ ocmu ORA11GR2] $

4) Start the database to mount Mode
SYS @ ORA11GR2> shutdown abort;
ORACLE instance shut down.
SYS @ ORA11GR2> startup mount;
ORACLE instance started.

Total System Global Area 841162752 bytes
Fixed Size 1339768 bytes
Variable Size 532680328 bytes
Database Buffers 301989888 bytes
Redo Buffers 5152768 bytes
Database mounted.
SYS @ ORA11GR2>

5) restore the database using RMAN
RMAN> restore database;

Starting restore at 20-MAR-13
Using target database control file instead of recovery catalog
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: SID = 18 device type = DISK

Channel ORA_DISK_1: starting datafile backup set restore
Channel ORA_DISK_1: specifying datafile (s) to restore from backup set
Channel ORA_DISK_1: restoring datafile 00001 to/u01/app/oracle/oradata/ORA11GR2/system01.dbf
Channel ORA_DISK_1: restoring datafile 00002 to/u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf
Channel ORA_DISK_1: restoring datafile 00003 to/u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf
Channel ORA_DISK_1: restoring datafile 00004 to/u01/app/oracle/oradata/ORA11GR2/users01.dbf
Channel ORA_DISK_1: restoring datafile 00005 to/u01/app/oracle/oradata/ORA11GR2/example01.dbf
Channel ORA_DISK_1: reading from backup piece/u01/app/FRA/ORA11GR2/backupset/2013_03_20/o1_mf_nnndf_TAG20130320T151949_8nlrrrfm _. bkp
Channel ORA_DISK_1: piece handle =/u01/app/FRA/ORA11GR2/backupset/2013_03_20/blank _. bkp tag = TAG20130320T151949
Channel ORA_DISK_1: restored backup piece 1
Channel ORA_DISK_1: restore complete, elapsed time: 00:03:26
Finished restore at 20-MAR-13

RMAN>

6) cancel-based recovery
SYS @ ORA11GR2> recover database until cancel;
-- Completed in sqlplus

ORA-00279: change 883460 generated at 03/20/2013 15:19:50 needed for thread 1
ORA-00289: suggestion:
/U01/app/FRA/ORA11GR2/archivelog/2013_03_20/o1_mf_1_8_8nlrzy8w _. arc
ORA-00280: change 883460 for thread 1 is in sequence #8

Specify log: {<RET> = suggested | filename | AUTO | CANCEL}

-- The Application sequence #8 is 8 and the archive log file named o1_mf_4248_8nlrzy8w _. arc is directly pressed by the Enter key

ORA-00279: change 884069 generated at 03/20/2013 15:23:42 needed for thread 1
ORA-00289: suggestion:
/U01/app/FRA/ORA11GR2/archivelog/2013_03_20/o1_mf_rj9_8nls091w _. arc
ORA-00280: change 884069 for thread 1 is in sequence #9
ORA-00278: log file
'/U01/app/FRA/ORA11GR2/archivelog/2013_03_20/o1_mf_1_8_8nlrzy8w _. arc' no longer
Needed for this recovery

Specify log: {<RET> = suggested | filename | AUTO | CANCEL}

-- Apply this archive log file whose sequence #9 is 9 and press the Enter key.

ORA-00279: change 884101 generated at 03/20/2013 15:23:53 needed for thread 1
ORA-00289: suggestion:
/U01/app/FRA/ORA11GR2/archivelog/2013_03_20/o1_mf_1_10_8nls0os6 _. arc
ORA-00280: change 884101 for thread 1 is in sequence #10
ORA-00278: log file
'/U01/app/FRA/ORA11GR2/archivelog/2013_03_20/o1_mf_00009_8nls091w _. arc' no longer
Needed for this recovery

Specify log: {<RET> = suggested | filename | AUTO | CANCEL}
Cancel
-- Manually enter cancel to end the restoration process, regardless of whether the file is recoverable or not;
Media recovery canceled.
SYS @ ORA11GR2>
SYS @ ORA11GR2> alter database open resetlogs;

Database altered.

SYS @ ORA11GR2>

7) Check
SYS @ ORA11GR2> select table_name, tablespace_name from dba_tables where owner = 'Scott 'AND table_name LIKE't _';

TABLE_NAME TABLESPACE_NAME
-------------------------------------------------------
T2 USERS
T1 USERS

SYS @ ORA11GR2>
Note that two archive logs are applied, and the third archive log is cancel. You can also see from the results that only
There are two tables T1 and T2, and their redo logs are in the first two archives. All the redo entries in table T3 are in the third archive,
Because cancel is adopted, table T3 is not restored.

There is also an article for reference:

1 2 Next Page