ITPub博客

首页 > 数据库 > Oracle > 基于cancel的不完全恢复

基于cancel的不完全恢复

原创 Oracle 作者:nathanzhn 时间:2014-02-19 15:42:54 0 删除 编辑

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

步骤:

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-01-23

  • 博文量
    49
  • 访问量
    262598