基于cancel的不完全恢复

逃离我推掉我的手 2022-08-13 00:47 151阅读 0赞

实验：基于cancel的不完全恢复

实验环境查看

lsnrctl status  
select open\_mode from v$database;  
  --监听与数据库状态

show parameter recovery;  
select flashback\_on from v$database;  
archive log list;  
  --闪回与归档的配置

1)准备环境：RMAN全库备份  
RMAN> backup as compressed backupset full database;  
       --压缩备份  
       --backup full database ;备份集备份

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/o1\_mf\_nnndf\_TAG20130320T151949\_8nlrrrfm\_.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=NONE  
channel ORA\_DISK\_1: backup set complete, elapsed time: 00:00:01  
Finished backup at 20-MAR-13

2)创建测试数据  
[SYS@ORA11GR2>create][SYS_ORA11GR2_create] table scott.t1 as select 1 as id from dual;

Table created.

[SYS@ORA11GR2>alter][SYS_ORA11GR2_alter] system archive log current;

System altered.

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

Table created.

[SYS@ORA11GR2>alter][SYS_ORA11GR2_alter] system archive log current;

System altered.

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

Table created.

[SYS@ORA11GR2>alter][SYS_ORA11GR2_alter] system archive log current;

System altered.

[SYS@ORA11GR2][SYS_ORA11GR2]>  
[SYS@ORA11GR2>select][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

完成测试数据构造后，查看生成的归档日志  
ls /u01/app/oracle/fast\_recovery\_area/PROD/archivelog/2014\_10\_08  
ls -lrt

3)删除所有数据文件和在线数据文件

在sqlplus里面删除：  
[SYS@PROD>select][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][SYS_PROD_select] member from v$logfile;

MEMBER  
\----------------------------------------------------------------------------------------------------------------------------------  
/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/o1\_mf\_1\_b2255nxl\_.log

[SYS@PROD>!rm][SYS_PROD_rm] /u01/app/oracle/oradata/PROD/datafile/\*.dbf;

[SYS@PROD>!rm][SYS_PROD_rm] /u01/app/oracle/oradata/PROD/onlinelog/\*.log;

[SYS@PROD>!rm][SYS_PROD_rm] /u01/app/oracle/fast\_recovery\_area/PROD/onlinelog/\*.log;

在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)数据库启动到mount模式  
[SYS@ORA11GR2>shutdown][SYS_ORA11GR2_shutdown] abort;  
ORACLE instance shut down.  
[SYS@ORA11GR2>startup][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][SYS_ORA11GR2]>

5)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/o1\_mf\_nnndf\_TAG20130320T151949\_8nlrrrfm\_.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恢复  
[SYS@ORA11GR2>recover][SYS_ORA11GR2_recover] database until cancel;  
   --在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\}

--应用sequence \#8为8，名字为o1\_mf\_1\_8\_8nlrzy8w\_.arc的归档日志文件，直接按回车键

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\_1\_9\_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\}

--应用sequence \#9为9的这个归档日志文件，直接按回车键

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\_1\_9\_8nls091w\_.arc' no longer  
needed for this recovery

Specify log: \{<RET>=suggested | filename | AUTO | CANCEL\}  
cancel  
  --手动输入cancel结束恢复，不管后面有没有可恢复文件，都会结束；  
Media recovery cancelled.  
[SYS@ORA11GR2][SYS_ORA11GR2]>  
[SYS@ORA11GR2>alter][SYS_ORA11GR2_alter] database open resetlogs;

Database altered.

[SYS@ORA11GR2][SYS_ORA11GR2]>

7）检查  
[SYS@ORA11GR2>select][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][SYS_ORA11GR2]>  
注 应用了两个归档日志，第三个归档日志cancel了，从结果中也可以看到，恢复以后的数据库中，只  
 存在T1,T2两张表，它们俩的重做日志就在前两个归档中，T3表的所有重做条目都在第三个归档中，  
 由于采取了cancel，所以，T3表并未恢复

[SYS_ORA11GR2_create]: mailto:SYS@ORA11GR2>create
[SYS_ORA11GR2_alter]: mailto:SYS@ORA11GR2>alter
[SYS_ORA11GR2]: mailto:SYS@ORA11GR2
[SYS_ORA11GR2_select]: mailto:SYS@ORA11GR2>select
[SYS_PROD_select]: mailto:SYS@PROD>select
[SYS_PROD_rm]: mailto:SYS@PROD>!rm
[SYS_ORA11GR2_shutdown]: mailto:SYS@ORA11GR2>shutdown
[SYS_ORA11GR2_startup]: mailto:SYS@ORA11GR2>startup
[SYS_ORA11GR2_recover]: mailto:SYS@ORA11GR2>recover