ITPub博客

首页 > 数据库 > Oracle > Oracle手工不完全恢复(一):使用当前控制文件

Oracle手工不完全恢复(一):使用当前控制文件

原创 Oracle 作者:迷倪小魏 时间:2017-08-02 22:12:00 0 删除 编辑

实验环境

操作系统:CentOS 7.1

数据库:Oracle 11.2.0.4


目录

示例一:基于SCN或时间点的恢复----恢复过去某个时间误删除的表

示例二:当前日志组损坏,造成数据库崩溃

示例三:归档日志丢失或损坏



示例一:基于SCN或时间点的恢复----恢复过去某个时间误删除的表


环境:

1)提前对数据库做一次全库冷备份;

2)在seiang用户下有一张test1表隶属于seiang表空间;


定位错误操作发生的时间或SCNlogminer


  1. SYS@seiang11g>select owner,table_name,tablespace_name from dba_tables 
  2.   2 where owner = 'SEIANG';

  3. OWNER TABLE_NAME TABLESPACE_NAME
  4. ------------------------------ ------------------------------ ------------------------------
  5. SEIANG TEST1 SEIANG
  6. SEIANG TEST2 WJQ
  7. SEIANG TEST3 WJQBEST

  8. --查看test1表中数据的内容
  9. SYS@seiang11g>select * from seiang.test1;

  10. ID NAME AGE
  11. ---------- ------------------------------------------------------------ ----------
  12.       1 wjq 23
  13.       2 seiang 24

  14. --查看当前日志序号为52
  15. SYS@seiang11g>select group#,sequence#,status from v$log;

  16.     GROUP# SEQUENCE# STATUS
  17. ---------- ---------- ----------------
  18.          1 52 CURRENT
  19.          2 50 INACTIVE
  20.          3 51 INACTIVE

  21. --误删除表test1表,同时purge
  22. SYS@seiang11g>set time on
  23. 11:32:47 SYS@seiang11g>drop table seiang.test1 purge;
  24. Table dropped.

  25. 11:34:49 SYS@seiang11g>commit;
  26. Commit complete.

  27. --进行两次日志切换
  28. 11:37:31 SYS@seiang11g>alter system switch logfile;
  29. System altered.

  30. 11:37:51 SYS@seiang11g>/
  31. System altered.

  32. --查看当前日志序号为54
  33. 11:38:00 SYS@seiang11g>select group#,sequence#,status from v$log;

  34.     GROUP# SEQUENCE# STATUS
  35. ---------- ---------- ----------------
  36.          1 52 ACTIVE
  37.          2 53 ACTIVE
  38.          3 54 CURRENT

  39. SYS@seiang11g>alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';11:38:04 SYS@seiang11g>select name from v$archived_log;

  40. NAME
  41. ------------------------------------------------------------
  42. ………(省略N条归档日志)……….
  43. /u01/app/oracle/arch/arch_1_949237404_49.log
  44. /u01/app/oracle/arch/arch_1_949237404_50.log
  45. /u01/app/oracle/arch/arch_1_949237404_51.log
  46. /u01/app/oracle/arch/arch_1_949237404_52.log(test1表的删除记录在该归档日志文件中)
  47. /u01/app/oracle/arch/arch_1_949237404_53.log


  48. 49 rows selected. SYS@seiang11g>select name,first_time,next_time from v$archived_log;

  49. NAME FIRST_TIME NEXT_TIME
  50. ------------------------------------------------------------ ------------------- -------------------
  51. ……..(此处省略部分归档)…………..
  52. /u01/app/oracle/arch/arch_1_949237404_49.log 2017-08-01 04:02:47 2017-08-01 15:00:39
  53. /u01/app/oracle/arch/arch_1_949237404_50.log 2017-08-01 15:00:39 2017-08-01 22:00:33
  54. /u01/app/oracle/arch/arch_1_949237404_51.log 2017-08-01 22:00:33 2017-08-02 00:00:03
  55. /u01/app/oracle/arch/arch_1_949237404_52.log 2017-08-02 00:00:03 2017-08-02 11:37:51
  56. /u01/app/oracle/arch/arch_1_949237404_53.log 2017-08-02 11:37:51 2017-08-02 11:37:59

  57. --添加一条日志条目,该日志记录了删除test1时的归档日志信息
  58. SYS@seiang11g>exec dbms_logmnr.add_logfile(logfilename => '/u01/app/oracle/arch/arch_1_949237404_52.log', options => dbms_logmnr.new);
  59. PL/SQL procedure successfully completed.

  60. --开始解析日志条目
  61. SYS@seiang11g>exec dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog);
  62. PL/SQL procedure successfully completed.

  63. --查看v$logmnr_contents视图,找出删除test1表时的SCN(1914743)和时间戳
  64. SYS@seiang11g>col sql_redo for a50
  65. SYS@seiang11g>select scn, timestamp, sql_redo from v$logmnr_contents where seg_owner='SEIANG';

  66.        SCN TIMESTAMP SQL_REDO
  67. ---------- ------------------- --------------------------------------------------
  68.    1914743 2017-08-02 11:32:53 drop table seiang.test1 purge;

  69. --查看数据文件头信息,挖掘出来的SCN比此时的SCN要小
  70. SYS@seiang11g>select file#,name,checkpoint_change# from v$datafile_header;

  71.      FILE# NAME CHECKPOINT_CHANGE#
  72. ---------- ------------------------------------------------------------ ------------------
  73.          1 /u01/app/oracle/oradata/OraDB11g/system01.dbf 1915266
  74.          2 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf 1915266
  75.          3 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf 1915266
  76.          4 /u01/app/oracle/oradata/OraDB11g/users01.dbf 1915266
  77.          5 /u01/app/oracle/oradata/OraDB11g/example01.dbf 1915266
  78.          6 /u01/app/oracle/oradata/OraDB11g/rman01.dbf 1915266
  79.          7 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf 1915266
  80.          8 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf 1915266
  81.          9 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf 1915266

  82. --关闭数据库,删除所有的数据文件
  83. SYS@seiang11g>shutdown immediate
  84. Database closed.
  85. Database dismounted.
  86. ORACLE instance shut down.

  87. [oracle@seiang11g OraDB11g]$ rm *.dbf
  88. [oracle@seiang11g OraDB11g]$
  89. [oracle@seiang11g OraDB11g]$ ll
  90. total 163164
  91. -rw-r----- 1 oracle oinstall 9781248 Aug 2 11:55 control01.ctl
  92. -rw-r----- 1 oracle oinstall 52429312 Aug 2 11:37 redo01.log
  93. -rw-r----- 1 oracle oinstall 52429312 Aug 2 11:37 redo02.log
  94. -rw-r----- 1 oracle oinstall 52429312 Aug 2 11:55 redo03.log

  95. --重新启动数据库,由于缺少数据文件而出现报错信息
  96. SYS@seiang11g>startup
  97. ORACLE instance started.

  98. Total System Global Area 1252663296 bytes
  99. Fixed Size 2252824 bytes
  100. Variable Size 788533224 bytes
  101. Database Buffers 452984832 bytes
  102. Redo Buffers 8892416 bytes
  103. Database mounted.
  104. ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
  105. ORA-01110: data file 1: '/u01/app/oracle/oradata/OraDB11g/system01.dbf'

  106. --查看需要恢复的数据文件
  107. SYS@seiang11g>select * from v$recover_file;

  108.      FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
  109. ---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
  110.          1 ONLINE ONLINE 1913765 02-AUG-17
  111.          2 ONLINE ONLINE 1913765 02-AUG-17
  112.          3 ONLINE ONLINE 1913765 02-AUG-17
  113.          4 ONLINE ONLINE 1913765 02-AUG-17
  114.          5 ONLINE ONLINE 1913765 02-AUG-17
  115.          6 ONLINE ONLINE 1913765 02-AUG-17
  116.          7 ONLINE ONLINE 1913765 02-AUG-17
  117.          8 ONLINE ONLINE 1913765 02-AUG-17
  118.          9 ONLINE ONLINE 1913765 02-AUG-17


  119. --还原冷备份的所有数据文件
  120. SYS@seiang11g>host cp /u01/app/oracle/UMAN_Backup/*.dbf /u01/app/oracle/oradata/OraDB11g


    --根据挖掘出来的SCN1914743),执行数据的不完全恢复

    SYS@seiang11g>recover database until change 1914743;

    Media recovery complete.

    或则是SYS@seiang11g>recover database until time 2017-08-02 11:32:53;


    --恢复完成后,查看当前日志序号54

    SYS@seiang11g>select group#,sequence#,status from v$log;


        GROUP#  SEQUENCE# STATUS

    ---------- ---------- ----------------

             1         52 INACTIVE

             3         54 CURRENT

             2         53 INACTIVE


    --查看数据文件头的SCN,是恢复后最新的SCN

    SYS@seiang11g>select file#,name,checkpoint_change# from v$datafile_header;


         FILE# NAME                                                         CHECKPOINT_CHANGE#

    ---------- ------------------------------------------------------------ ------------------

             1 /u01/app/oracle/oradata/OraDB11g/system01.dbf                           1914743

             2 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf                           1914743

             3 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf                          1914743

             4 /u01/app/oracle/oradata/OraDB11g/users01.dbf                            1914743

             5 /u01/app/oracle/oradata/OraDB11g/example01.dbf                          1914743

             6 /u01/app/oracle/oradata/OraDB11g/rman01.dbf                             1914743

             7 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf                           1914743

             8 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf                              1914743

             9 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf                          1914743


    --使用resetlogs打开数据库

    SYS@seiang11g>alter database open resetlogs;

    Database altered.


    --查看当前日志序号1开始记录,之前的归档日志全部作废

    SYS@seiang11g>select group#,sequence#,status from v$log;


        GROUP#  SEQUENCE# STATUS

    ---------- ---------- ----------------

             1          1 CURRENT

             2          0 UNUSED

             3          0 UNUSED


    --删除的test1表恢复成功

    SYS@seiang11g>select * from seiang.test1;


            ID NAME                                                                AGE

    ---------- ------------------------------------------------------------ ----------

             1 wjq                                                                  23

             2 seiang                                                               24


恢复原理: 

1.冷备份的数据文件*.dbf比现在要旧,那么数据文件的scn肯定就比现在的小;  

2.使用日志挖掘方法,在日志中找出删表时的scn或时间戳;  

3.在数据库shutdown的状态下,用冷备份的数据文件*.dbf,覆盖现在的*.dbf文件;保证数据文件完整性;

4.启动数据库到mount,恢复到日志挖掘的SCN或时间点(利用日志对数据文件重做一次)  

5.以resetlogs方式打开数据库,以前的日志就被覆盖了;


示例二:当前日志组损坏,造成数据库崩溃


环境:

1)提前对数据库做一次全库冷备份;

2)在seiang用户下有一张test1表隶属于seiang表空间;



  1. SYS@seiang11g>select owner,table_name,tablespace_name from dba_tables
  2.   2 where owner='SEIANG';
  3.     
  4. OWNER TABLE_NAME TABLESPACE_NAME
  5. ------------------------------ ------------------------------ ------------------------------
  6. SEIANG TEST1 SEIANG
  7. SEIANG TEST2 WJQ
  8. SEIANG TEST3 WJQBEST


  9. SYS@seiang11g>select * from seiang.test1;

  10.         ID NAME AGE
  11. ---------- ------------------------------ ----------
  12.          1 wjq 23
  13.          2 seiang 24

  14. --查看当前的日志序号为1
  15. SYS@seiang11g>select group#,sequence#,status from v$log;

  16.     GROUP# SEQUENCE# STATUS
  17. ---------- ---------- ----------------
  18.          1 1 CURRENT
  19.          2 0 UNUSED
  20.          3 0 UNUSED

  21. --在test1表中插入一条数据,提交,并切换日志,该插入信息记录在归档日志1中
  22. SYS@seiang11g>insert into seiang.test1 values(3,'wjqgood',25);
  23. 1 row created.
  24. SYS@seiang11g>commit;
  25. Commit complete.
  26. SYS@seiang11g>alter system switch logfile;
  27. System altered.

  28. --在test1表中插入一条数据,提交,并切换日志,该插入信息记录在归档日志2中
  29. SYS@seiang11g>insert into seiang.test1 values(4,'wjqbest',30);
  30. 1 row created.
  31. SYS@seiang11g>commit;
  32. Commit complete.
  33. SYS@seiang11g>alter system switch logfile;
  34. System altered.

  35. --查看当前的日志序号为3
  36. SYS@seiang11g>select group#,sequence#,status from v$log;

  37.     GROUP# SEQUENCE# STATUS
  38. ---------- ---------- ----------------
  39.          1 1 ACTIVE
  40.          2 2 ACTIVE
  41.          3 3 CURRENT

  42. --查看归档日志的信息
  43. SYS@seiang11g>select name from v$archived_log;

  44. NAME
  45. --------------------------------------------------------
  46. /u01/app/oracle/arch/arch_1_950971495_1.log
  47. /u01/app/oracle/arch/arch_1_950971495_2.log

  48. --在test1表中插入一条数据,提交,不切换日志,该插入信息记录在当前日志3中
  49. SYS@seiang11g>insert into seiang.test1 values(5,'wjqseiang',60);
  50. 1 row created.
  51. SYS@seiang11g>commit;
  52. Commit complete.

  53. --查看test1表中现有的数据信息
  54. SYS@seiang11g>select * from seiang.test1;

  55.         ID NAME AGE
  56. ---------- ------------------------------ ----------
  57.          1 wjq 23
  58.          2 seiang 24
  59.          3 wjqgood 25(在归档arch_1_950971495_1.log)
  60.          4 wjqbest 30(在归档arch_1_950971495_2.log)
  61.          5 wjqseiang 60(在当前日志redo03.log)


  62. --模拟当前日志丢失或损坏,数据库崩溃
  63. [oracle@seiang11g OraDB11g]$ rm redo03.log
  64. SYS@seiang11g>shutdown abort
  65. ORACLE instance shut down.


  66. --启动数据库,由于缺少当前日志信息出现报错信息
  67. SYS@seiang11g>startup
  68. ORACLE instance started.

  69. Total System Global Area 1252663296 bytes
  70. Fixed Size 2252824 bytes
  71. Variable Size 788533224 bytes
  72. Database Buffers 452984832 bytes
  73. Redo Buffers 8892416 bytes
  74. Database mounted.
  75. ORA-00313: open failed for members of log group 3 of thread 1
  76. ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/OraDB11g/redo03.log'
  77. ORA-27037: unable to obtain file status
  78. Linux-x86_64 Error: 2: No such file or directory
  79. Additional information: 3

  80. --还原所有的冷备份数据文件
  81. [oracle@seiang11g OraDB11g]$ rm *.dbf
  82. [oracle@seiang11g OraDB11g]$
  83. [oracle@seiang11g OraDB11g]$ ll
  84. total 163164
  85. -rw-r----- 1 oracle oinstall 9781248 Aug 2 14:57 control01.ctl
  86. -rw-r----- 1 oracle oinstall 52429312 Aug 2 14:56 redo01.log
  87. -rw-r----- 1 oracle oinstall 52429312 Aug 2 14:56 redo02.log
  88. -rw-r----- 1 oracle oinstall 52429312 Aug 2 14:56 redo03.log.bak
  1. [oracle@seiang11g OraDB11g]$ cp /u01/app/oracle/UMAN_Backup/*.dbf ./

  2. 在这里一定要注意的的是,一定要还原所有旧的冷备份的数据文件,如果不还原的话,就会出现如下的问题:
  3. SYS@seiang11g>recover database until cancel;
  4. ORA-00279: change 1921883 generated at 08/02/2017 14:28:01 needed for thread 1
  5. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_3.log
  6. ORA-00280: change 1921883 for thread 1 is in sequence #3
  7.     

  8. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
  9. cancel
  10. ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
  11. ORA-01194: file 1 needs more recovery to be consistent
  12. ORA-01110: data file 1: '/u01/app/oracle/oradata/OraDB11g/system01.dbf'

  13. ORA-01112: media recovery not started



  14. --尝试进行手工完全恢复,由于缺少当前日志失败
  15. SYS@seiang11g>recover database;
  16. ORA-00283: recovery session canceled due to errors
  17. ORA-00313: open failed for members of log group 3 of thread 1
  18. ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/OraDB11g/redo03.log'
  19. ORA-27037: unable to obtain file status
  20. Linux-x86_64 Error: 2: No such file or directory
  21. Additional information: 3


  22. --执行不完全恢复
  23. SYS@seiang11g>recover database until cancel;
  24. ORA-00279: change 1914401 generated at 08/02/2017 14:49:14 needed for thread 1
  25. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_3.log(注意提示信息)
  26. ORA-00280: change 1914401 for thread 1 is in sequence #3
  27. (该归档日志不存在)

  28. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
  29. cancel
  30. Media recovery cancelled.

  31. --恢复完成后,使用resetlogs打开数据库
  32. SYS@seiang11g>alter database open resetlogs;
  33. Database altered.

  34. --查看当前日志序号重新从1开始记录
  35. SYS@seiang11g>select group#,sequence#,status from v$log;

  36.     GROUP# SEQUENCE# STATUS
  37. ---------- ---------- ----------------
  38.          1 1 CURRENT
  39.          2 0 UNUSED
  40.          3 0 UNUSED

  41. --查看表test1中的数据信息
  42. SYS@seiang11g>select * from seiang.test1;

  43.         ID NAME AGE
  44. ---------- ------------------------------ ----------
  45.          1 wjq 23
  46.          2 seiang 24
  47.          3 wjqdood 25
  48.          4 wjqbest 30
  49. 可以发现存储在当前日志文件中的数据记录被回滚掉了!!!


示例三:归档日志丢失或损坏


环境:

1)提前有一套完成的数据文件的冷备份;

2)在seiang用户下有一张test1表隶属于seiang表空间;

  1. SYS@seiang11g>select owner,table_name,tablespace_name from dba_tables where owner='SEIANG';

  2. OWNER TABLE_NAME TABLESPACE_NAME
  3. ------------------------------ ------------------------------ ------------------------------
  4. SEIANG TEST1 SEIANG
  5. SEIANG TEST2 WJQ
  6. SEIANG TEST3 WJQBEST

  7. SYS@seiang11g>select * from seiang.test1;

  8.         ID NAME AGE
  9. ---------- ------------------------------ ----------
  10.          1 wjq 23
  11.          2 seiang 24
  12.          3 wjqdood 25
  13.          4 wjqbest 30

  14. --查看当前日志序号为2
  15. SYS@seiang11g>select group#,sequence#,status from v$log;

  16.     GROUP# SEQUENCE# STATUS
  17. ---------- ---------- ----------------
  18.          1 1 INACTIVE
  19.          2 2 CURRENT
  20.          3 0 UNUSED


  21. --修改数据并提交(sequence号2)
  22. SYS@seiang11g>update seiang.test1 set age=100 where id=1;
  23. 1 row updated.

  24. SYS@seiang11g>commit;
  25. Commit complete.

  26. SYS@seiang11g>alter system switch logfile;
  27. System altered.

  28. --修改数据并提交(sequence号3)
  29. SYS@seiang11g>update seiang.test1 set age=200 where id=1;
  30. 1 row updated.

  31. SYS@seiang11g>commit;
  32. Commit complete.

  33. SYS@seiang11g>alter system switch logfile;
  34. System altered.

  35. --修改数据并提交(sequence号4)
  36. SYS@seiang11g>update seiang.test1 set age=300 where id=1;
  37. 1 row updated.

  38. SYS@seiang11g>commit;
  39. Commit complete.

  40. SYS@seiang11g>alter system switch logfile;
  41. System altered.

  42. --修改数据并提交(sequence号5)
  43. SYS@seiang11g>update seiang.test1 set age=400 where id=1;
  44. 1 row updated.

  45. SYS@seiang11g>commit;
  46. Commit complete.

  47. SYS@seiang11g>alter system switch logfile;
  48. System altered.

  49. --修改数据并提交(sequence号6)
  50. SYS@seiang11g>update seiang.test1 set age=500 where id=1;
  51. 1 row updated.

  52. SYS@seiang11g>commit;
  53. Commit complete.

  54. SYS@seiang11g>alter system switch logfile;
  55. System altered.

  56. --修改数据并提交(sequence号7)
  57. SYS@seiang11g>update seiang.test1 set age=600 where id=1;
  58. 1 row updated.

  59. SYS@seiang11g>commit;
  60. Commit complete.

  61. SYS@seiang11g>alter system switch logfile;
  62. System altered.

  63. --当前联机日志序号为8
  64. SYS@seiang11g>select group#,sequence#,status from v$log;

  65.     GROUP# SEQUENCE# STATUS
  66. ---------- ---------- ----------------
  67.          1 7 ACTIVE
  68.          2 8 CURRENT
  69.          3 6 ACTIVE

  70. --查看相应的归档日志
  71. SYS@seiang11g>select name from v$archived_log;

  72. NAME
  73. ------------------------------------------------------
  74. /u01/app/oracle/arch/arch_1_950962051_1.log
  75. /u01/app/oracle/arch/arch_1_950962051_2.log
  76. /u01/app/oracle/arch/arch_1_950971495_1.log
  77. /u01/app/oracle/arch/arch_1_950971495_2.log
  78. /u01/app/oracle/arch/arch_1_950972396_1.log
  79. /u01/app/oracle/arch/arch_1_950972396_2.log
  80. /u01/app/oracle/arch/arch_1_950972396_3.log
  81. /u01/app/oracle/arch/arch_1_950972396_4.log
  82. /u01/app/oracle/arch/arch_1_950972396_5.log
  83. /u01/app/oracle/arch/arch_1_950972396_6.log
  84. /u01/app/oracle/arch/arch_1_950972396_7.log

  85. --关闭数据库
  86. SYS@seiang11g >shutdown immediate
  87. Database closed.
  88. Database dismounted.
  89. ORACLE instance shut down.

  90. --模拟数据文件seiang损坏
  91. SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/seiang01.dbf


  92. --重新打开数据库 ,由于数据文件的丢死,数据库无法open
  93. SYS@seiang11g>startup
  94. ORACLE instance started.

  95. Total System Global Area 1252663296 bytes
  96. Fixed Size 2252824 bytes
  97. Variable Size 788533224 bytes
  98. Database Buffers 452984832 bytes
  99. Redo Buffers 8892416 bytes
  100. Database mounted.
  101. ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
  102. ORA-01110: data file 7: '/u01/app/oracle/oradata/OraDB11g/seiang01.dbf'


  103. SYS@seiang11g>select * from v$recover_file;

  104.      FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
  105. ---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
  106.          7 ONLINE ONLINE FILE NOT FOUND 0

  107. --还原备份的数据文件
  108. SYS@seiang11g>host cp /u01/app/oracle/UMAN_Backup/seiang01.dbf /u01/app/oracle/oradata/OraDB11g/
  109.     
  110. SYS@seiang11g>select * from v$recover_file;

  111.      FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
  112. ---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
  113.          7 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17


  114. --查看一下归档日志的详细信息
  115. SYS@seiang11g>select sequence#, name, first_change#, next_change# from v$archived_log;

  116.  SEQUENCE# NAME FIRST_CHANGE# NEXT_CHANGE#
  117. ---------- -------------------------------------------------- ------------- ------------
  118.         1 /u01/app/oracle/arch/arch_1_950962051_1.log 1914744 1921854
  119.          2 /u01/app/oracle/arch/arch_1_950962051_2.log 1921854 1921883
  120.          1 /u01/app/oracle/arch/arch_1_950971495_1.log 1913766 1914386
  121.          2 /u01/app/oracle/arch/arch_1_950971495_2.log 1914386 1914401
  122.          1 /u01/app/oracle/arch/arch_1_950972396_1.log 1914402 1936446
  123.          2 /u01/app/oracle/arch/arch_1_950972396_2.log 1936446 1937042
  124.          3 /u01/app/oracle/arch/arch_1_950972396_3.log 1937042 1937100
  125.          4 /u01/app/oracle/arch/arch_1_950972396_4.log 1937100 1937110
  126.          5 /u01/app/oracle/arch/arch_1_950972396_5.log 1937110 1937123
  127.          6 /u01/app/oracle/arch/arch_1_950972396_6.log 1937123 1937139
  128.          7 /u01/app/oracle/arch/arch_1_950972396_7.log 1937139 1937148


  129. --模拟归档日志5丢失或者损坏
  130. SYS@seiang11g>host rm /u01/app/oracle/arch/arch_1_950972396_5.log

  131. [oracle@seiang11g OraDB11g]$ ll /u01/app/oracle/arch/arch_1_950972396_5.log
  132. ls: cannot access /u01/app/oracle/arch/arch_1_950972396_5.log: No such file or directory


  133. --执行恢复操作,当在需要归档日志5的时候出现错误
  134. SYS@seiang11g>recover datafile 7;
  135. ORA-00279: change 1913766 generated at 08/02/2017 11:12:22 needed for thread 1
  136. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_1.log
  137. ORA-00280: change 1913766 for thread 1 is in sequence #1

  138. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
  139. auto
  140. ORA-00279: change 1914386 generated at 08/02/2017 14:48:59 needed for thread 1
  141. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_2.log
  142. ORA-00280: change 1914386 for thread 1 is in sequence #2

  143. ORA-00279: change 1914402 generated at 08/02/2017 14:59:56 needed for thread 1
  144. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_1.log
  145. ORA-00280: change 1914402 for thread 1 is in sequence #1

  146. ORA-00279: change 1936446 generated at 08/02/2017 15:39:52 needed for thread 1
  147. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_2.log
  148. ORA-00280: change 1936446 for thread 1 is in sequence #2

  149. ORA-00279: change 1937042 generated at 08/02/2017 15:49:42 needed for thread 1
  150. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_3.log
  151. ORA-00280: change 1937042 for thread 1 is in sequence #3

  152. ORA-00279: change 1937100 generated at 08/02/2017 15:51:34 needed for thread 1
  153. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_4.log
  154. ORA-00280: change 1937100 for thread 1 is in sequence #4

  155. ORA-00279: change 1937110 generated at 08/02/2017 15:51:54 needed for thread 1
  156. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_5.log
  157. ORA-00280: change 1937110 for thread 1 is in sequence #5

  158. ORA-00308: cannot open archived log '/u01/app/oracle/arch/arch_1_950972396_5.log'
  159. ORA-27037: unable to obtain file status
  160. Linux-x86_64 Error: 2: No such file or directory
  161. Additional information: 3
  162.     
  163. --查看数据文件头,发现检查点不一致
  164. SYS@seiang11g>select file#,name,checkpoint_change# from v$datafile_header;

  165.      FILE# NAME CHECKPOINT_CHANGE#
  166. ---------- -------------------------------------------------- ------------------
  167.          1 /u01/app/oracle/oradata/OraDB11g/system01.dbf 1937617
  168.          2 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf 1937617
  169.          3 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf 1937617
  170.          4 /u01/app/oracle/oradata/OraDB11g/users01.dbf 1937617
  171.          5 /u01/app/oracle/oradata/OraDB11g/example01.dbf 1937617
  172.          6 /u01/app/oracle/oradata/OraDB11g/rman01.dbf 1937617
  173.          7 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf 1937110
  174.          8 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf 1937617
  175.          9 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf 1937617
  1. --关闭数据库 ,还原所有冷备份的数据文件

    SYS@seiang11g>shutdown abort

    ORACLE instance shut down.


    SYS@seiang11g>host cp /u01/app/oracle/UMAN_Backup/*.dbf /u01/app/oracle/oradata/OraDB11g/

  2. SYS@seiang11g>host ls -l /u01/app/oracle/oradata/OraDB11g/
  3. total 2279068
  4. -rw-r----- 1 oracle oinstall 7680 Aug 2 16:08 arch_1_950972396_5.log
  5. -rw-r----- 1 oracle oinstall 9781248 Aug 2 16:16 control01.ctl
  6. -rw-r----- 1 oracle oinstall 363077632 Aug 2 16:17 example01.dbf
  7. -rw-r----- 1 oracle oinstall 52429312 Aug 2 15:52 redo01.log
  8. -rw-r----- 1 oracle oinstall 52429312 Aug 2 15:58 redo02.log
  9. -rw-r----- 1 oracle oinstall 52429312 Aug 2 15:52 redo03.log
  10. -rw-r----- 1 oracle oinstall 31465472 Aug 2 16:17 rman01.dbf
  11. -rw-r----- 1 oracle oinstall 73408512 Aug 2 16:17 seiang01.dbf
  12. -rw-r----- 1 oracle oinstall 734011392 Aug 2 16:18 sysaux01.dbf
  13. -rw-r----- 1 oracle oinstall 796925952 Aug 2 16:18 system01.dbf
  14. -rw-r----- 1 oracle oinstall 30416896 Aug 2 16:18 temp01.dbf
  15. -rw-r----- 1 oracle oinstall 110108672 Aug 2 16:18 undotbs01.dbf
  16. -rw-r----- 1 oracle oinstall 5251072 Aug 2 16:18 users01.dbf
  17. -rw-r----- 1 oracle oinstall 20979712 Aug 2 16:18 wjq01.dbf
  18. -rw-r----- 1 oracle oinstall 20979712 Aug 2 16:18 wjqbest01.dbf

  19. --重新打开数据库
  20. SYS@seiang11g>startup
  21. ORACLE instance started.

  22. Total System Global Area 1252663296 bytes
  23. Fixed Size 2252824 bytes
  24. Variable Size 788533224 bytes
  25. Database Buffers 452984832 bytes
  26. Redo Buffers 8892416 bytes
  27. Database mounted.
  28. ORA-01190: control file or data file 1 is from before the last RESETLOGS
  29. ORA-01110: data file 1: '/u01/app/oracle/oradata/OraDB11g/system01.dbf'


  30. SYS@seiang11g>select file#,name,checkpoint_change# from v$datafile_header;

  31.      FILE# NAME CHECKPOINT_CHANGE#
  32. ---------- -------------------------------------------------- ------------------
  33.          1 /u01/app/oracle/oradata/OraDB11g/system01.dbf 1913765
  34.          2 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf 1913765
  35.          3 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf 1913765
  36.          4 /u01/app/oracle/oradata/OraDB11g/users01.dbf 1913765
  37.          5 /u01/app/oracle/oradata/OraDB11g/example01.dbf 1913765
  38.          6 /u01/app/oracle/oradata/OraDB11g/rman01.dbf 1913765
  39.          7 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf 1913765
  40.          8 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf 1913765
  41.          9 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf 1913765

  42. 9 rows selected.

  43. --恢复数据库 ,首先尝试完全恢复,同样在需要归档日志5的时候出现错误
  44. SYS@seiang11g>recover database;
  45. ORA-00279: change 1913766 generated at 08/02/2017 11:12:22 needed for thread 1
  46. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_1.log
  47. ORA-00280: change 1913766 for thread 1 is in sequence #1

  48. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
  49. auto
  50. ORA-00279: change 1914386 generated at 08/02/2017 14:48:59 needed for thread 1
  51. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_2.log
  52. ORA-00280: change 1914386 for thread 1 is in sequence #2

  53. ORA-00279: change 1914402 generated at 08/02/2017 14:59:56 needed for thread 1
  54. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_1.log
  55. ORA-00280: change 1914402 for thread 1 is in sequence #1

  56. ORA-00279: change 1936446 generated at 08/02/2017 15:39:52 needed for thread 1
  57. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_2.log
  58. ORA-00280: change 1936446 for thread 1 is in sequence #2

  59. ORA-00279: change 1937042 generated at 08/02/2017 15:49:42 needed for thread 1
  60. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_3.log
  61. ORA-00280: change 1937042 for thread 1 is in sequence #3

  62. ORA-00279: change 1937100 generated at 08/02/2017 15:51:34 needed for thread 1
  63. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_4.log
  64. ORA-00280: change 1937100 for thread 1 is in sequence #4

  65. ORA-00279: change 1937110 generated at 08/02/2017 15:51:54 needed for thread 1
  66. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_5.log
  67. ORA-00280: change 1937110 for thread 1 is in sequence #5

  68. ORA-00308: cannot open archived log '/u01/app/oracle/arch/arch_1_950972396_5.log'
  69. ORA-27037: unable to obtain file status
  70. Linux-x86_64 Error: 2: No such file or directory
  71. Additional information: 3


  72. --执行不完全恢复 ,成功
  73. SYS@seiang11g>recover database until cancel;
  74. ORA-00279: change 1937110 generated at 08/02/2017 15:51:54 needed for thread 1
  75. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_5.log
  76. ORA-00280: change 1937110 for thread 1 is in sequence #5


  77. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
  78. cancel
  79. Media recovery cancelled.

  80. --再次查看数据文件头的相关信息
  81. SYS@seiang11g>select file#,name,checkpoint_change# from v$datafile_header;

  82.      FILE# NAME CHECKPOINT_CHANGE#
  83. ---------- -------------------------------------------------- ------------------
  84.          1 /u01/app/oracle/oradata/OraDB11g/system01.dbf 1937110
  85.          2 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf 1937110
  86.          3 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf 1937110
  87.          4 /u01/app/oracle/oradata/OraDB11g/users01.dbf 1937110
  88.          5 /u01/app/oracle/oradata/OraDB11g/example01.dbf 1937110
  89.          6 /u01/app/oracle/oradata/OraDB11g/rman01.dbf 1937110
  90.          7 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf 1937110
  91.          8 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf 1937110
  92.          9 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf 1937110

  93. --查看日志组信息,发现与5号脱节
  94. SYS@seiang11g>select group#,sequence#,status from v$log;

  95.     GROUP# SEQUENCE# STATUS
  96. ---------- ---------- ----------------
  97.          1 7 INACTIVE
  98.          3 6 INACTIVE
  99.          2 8 CURRENT

  100. --使用resetlogs打开数据库
  101. SYS@seiang11g>alter database open resetlogs;
  102. Database altered.

  103. --日志序号从1开始记录
  104. SYS@seiang11g>select group#,sequence#,status from v$log;

  105.     GROUP# SEQUENCE# STATUS
  106. ---------- ---------- ----------------
  107.          1 1 CURRENT
  108.          2 0 UNUSED
  109.          3 0 UNUSED

  110. --确认表中的数据信息,发现在5号归档日志丢失后的修改都没有生效
  111. SYS@seiang11g>select * from seiang.test1;

  112.         ID NAME AGE
  113. ---------- -------------------------------------------------- ----------
  114.          1 wjq 300
  115.          2 seiang 24
  116.          3 wjqdood 25
  117.          4 wjqbest 30


相关连接:

 Oracle手工完全恢复案例:http://blog.itpub.net/31015730/viewspace-2142669/


作者:SEian.G(苦练七十二变,笑对八十一难)

ITPUBhttp://blog.itpub.net/31015730/

51CTOhttp://seiang.blog.51cto.com/



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

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

注册时间:2016-03-12

  • 博文量
    101
  • 访问量
    205515