基于cancel的不完全恢复

场景：联机日志文件损坏或丢失，此处再增加一个数据文件也丢失的情况

步骤：

1.         查询当前表中数据，如下有初始6行记录

2.         插入一条记录变为7行

3.         做热备份

4.         再插入一条记录变为8条并归档

5.         再插入2条记录，存在于联机日志中

6.         查看当前的联机日志组：current，如下：group1

7.         删除current的联机日志组的所有成员以及数据文件tbs_tommie01.dbf

8.         shutdown abort

9.         装入数据库

10.     基于cancel恢复数据库，需要跟踪归档目录下的归档记录，直到恢复到不存在的archive log sequence，输入cancel回车

11.     resetlogs打开数据库--再研究，resetlogs做了什么事情

12.     查询表的记录，存在8行，联机日志里的两条丢失

13.     不完全恢复完成

 

SYS@PROD>select * from timmie.t1;            A ----------          1         10         20         20         30         40   6 rows selected.   SYS@PROD>insert into timmie.t1 values(50);   1 row created.   SYS@PROD>commit;   Commit complete.   SYS@PROD>alter database begin backup;   Database altered. -- 此处做热备份 cp /u01/app/oracle/oradata/PROD/disk1/system001.dbf         /home/oracle/hotbackup/20140128-1/ cp /u01/app/oracle/oradata/PROD/disk1/undotbs01.dbf         /home/oracle/hotbackup/20140128-1/ cp /u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf          /home/oracle/hotbackup/20140128-1/ cp /u01/app/oracle/oradata/PROD/disk1/example1.dbf          /home/oracle/hotbackup/20140128-1/ cp /u01/app/oracle/oradata/PROD/disk1/indx1.dbf             /home/oracle/hotbackup/20140128-1/ cp /u01/app/oracle/oradata/PROD/disk1/tools1.dbf            /home/oracle/hotbackup/20140128-1/ cp /u01/app/oracle/oradata/PROD/disk1/users1.dbf            /home/oracle/hotbackup/20140128-1/ cp /u01/app/oracle/oradata/PROD/disk1/oltp1.dbf             /home/oracle/hotbackup/20140128-1/ cp /u01/app/oracle/oradata/PROD/disk3/tbs_tommie01.dbf      /home/oracle/hotbackup/20140128-1/ SYS@PROD>alter database end backup;   Database altered.   SYS@PROD>insert into timmie.t1 values(60);   1 row created.   SYS@PROD>commit;   Commit complete.   SYS@PROD>alter system switch logfile;   System altered.   SYS@PROD>alter system switch logfile;   System altered.   SYS@PROD>alter system switch logfile;     System altered.   SYS@PROD>SYS@PROD> SYS@PROD> SYS@PROD>alter system switch logfile;   System altered.   SYS@PROD>insert into timmie.t1 values(70);   1 row created.   SYS@PROD>commit;   Commit complete.     SYS@PROD>select * from v$log;       GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIV ---------- ---------- ---------- ---------- ---------- ------ STATUS                           FIRST_CHANGE# FIRST_TIME -------------------------------- ------------- ------------          1          1          5  104857600          4 NO CURRENT                                 648904 25-JAN-14            2          1          4  104857600          4 YES INACTIVE                                648902 25-JAN-14            3          1          3  104857600          4 YES INACTIVE                                648898 25-JAN-14     SYS@PROD>insert into timmie.t1 values(80);   1 row created.   SYS@PROD>commit;   Commit complete.   SYS@PROD>select * from v$log;       GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIV ---------- ---------- ---------- ---------- ---------- ------ STATUS                           FIRST_CHANGE# FIRST_TIME -------------------------------- ------------- ------------          1          1          5  104857600          4 NO CURRENT                                 648904 25-JAN-14            2          1          4  104857600          4 YES INACTIVE                                648902 25-JAN-14            3          1          3  104857600          4 YES INACTIVE                                648898 25-JAN-14       SYS@PROD>select member from v$logfile where group#=1;   MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/PROD/disk1/redo101.log /u01/app/oracle/oradata/PROD/disk2/redo102.log /u01/app/oracle/oradata/PROD/disk3/redo103.log /u01/app/oracle/oradata/PROD/disk4/redo104.log   -- 此处删除group1的联机日志组成员，以及数据文件 rm -f /u01/app/oracle/oradata/PROD/disk1/redo101.log rm -f /u01/app/oracle/oradata/PROD/disk2/redo102.log rm -f /u01/app/oracle/oradata/PROD/disk3/redo103.log rm -f /u01/app/oracle/oradata/PROD/disk4/redo104.log     rm -f /u01/app/oracle/oradata/PROD/disk3/tbs_tommie01.dbf     SYS@PROD>shutdown abort; ORACLE instance shut down.   -- 此处使用热备份数据文件拷贝回数据文件所在目录 cp /home/oracle/hotbackup/20140128-1/system001.dbf         /u01/app/oracle/oradata/PROD/disk1/ cp /home/oracle/hotbackup/20140128-1/undotbs01.dbf         /u01/app/oracle/oradata/PROD/disk1/ cp /home/oracle/hotbackup/20140128-1/sysaux01.dbf          /u01/app/oracle/oradata/PROD/disk1/ cp /home/oracle/hotbackup/20140128-1/example1.dbf          /u01/app/oracle/oradata/PROD/disk1/ cp /home/oracle/hotbackup/20140128-1/indx1.dbf             /u01/app/oracle/oradata/PROD/disk1/ cp /home/oracle/hotbackup/20140128-1/tools1.dbf            /u01/app/oracle/oradata/PROD/disk1/ cp /home/oracle/hotbackup/20140128-1/users1.dbf            /u01/app/oracle/oradata/PROD/disk1/ cp /home/oracle/hotbackup/20140128-1/oltp1.dbf             /u01/app/oracle/oradata/PROD/disk1/ cp /home/oracle/hotbackup/20140128-1/tbs_tommie01.dbf      /u01/app/oracle/oradata/PROD/disk3/   SYS@PROD>startup mount ORACLE instance started.   Total System Global Area  314572800 bytes Fixed Size                  1219184 bytes Variable Size              71304592 bytes Database Buffers          239075328 bytes Redo Buffers                2973696 bytes Database mounted. SYS@PROD>recover database until cancel; ORA-00279: change 648828 generated at 01/25/2014 07:09:52 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/oradata/PROD/arc_dest_1/1_1_837755814.dbf ORA-00280: change 648828 for thread 1 is in sequence #1     Specify log: {=suggested | filename | AUTO | CANCEL}   ORA-00279: change 648896 generated at 01/25/2014 07:12:34 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/oradata/PROD/arc_dest_1/1_2_837755814.dbf ORA-00280: change 648896 for thread 1 is in sequence #2 ORA-00278: log file '/u01/app/oracle/oradata/PROD/arc_dest_1/1_1_837755814.dbf' no longer needed for this recovery     Specify log: {=suggested | filename | AUTO | CANCEL}   ORA-00279: change 648898 generated at 01/25/2014 07:12:35 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/oradata/PROD/arc_dest_1/1_3_837755814.dbf ORA-00280: change 648898 for thread 1 is in sequence #3 ORA-00278: log file '/u01/app/oracle/oradata/PROD/arc_dest_1/1_2_837755814.dbf' no longer needed for this recovery     Specify log: {=suggested | filename | AUTO | CANCEL}   ORA-00279: change 648902 generated at 01/25/2014 07:12:42 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/oradata/PROD/arc_dest_1/1_4_837755814.dbf ORA-00280: change 648902 for thread 1 is in sequence #4 ORA-00278: log file '/u01/app/oracle/oradata/PROD/arc_dest_1/1_3_837755814.dbf' no longer needed for this recovery     Specify log: {=suggested | filename | AUTO | CANCEL}   --下面的#5在归档目录里不存在，需要cancel了 -- -rw-r-----  1 oracle oinstall 10562048  1月 25 07:12 1_1_837755814.dbf -- -rw-r-----  1 oracle oinstall     1024  1月 25 07:12 1_2_837755814.dbf -- -rw-r-----  1 oracle oinstall     1536  1月 25 07:12 1_3_837755814.dbf -- -rw-r-----  1 oracle oinstall     1024  1月 25 07:12 1_4_837755814.dbf   ORA-00279: change 648904 generated at 01/25/2014 07:12:43 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/oradata/PROD/arc_dest_1/1_5_837755814.dbf ORA-00280: change 648904 for thread 1 is in sequence #5 ORA-00278: log file '/u01/app/oracle/oradata/PROD/arc_dest_1/1_4_837755814.dbf' no longer needed for this recovery   Specify log: {=suggested | filename | AUTO | CANCEL} cancel Media recovery cancelled. SYS@PROD>alter database open resetlogs;   Database altered.   SYS@PROD>select * from timmie.t1;            A ----------          1         10         20         20         30         40         50         60   8 rows selected.

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26521853/viewspace-1083924/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26521853/viewspace-1083924/