Oracle手工不完全恢復（一）：使用當前控制檔案

迷倪小魏發表於2017-08-02
Oracle

實驗環境

作業系統：CentOS 7.1

資料庫：Oracle 11.2.0.4


目錄

示例一：基於SCN或時間點的恢復----恢復過去某個時間誤刪除的表

示例二：當前日誌組損壞，造成資料庫崩潰

示例三：歸檔日誌丟失或損壞



示例一：基於SCN或時間點的恢復----恢復過去某個時間誤刪除的表


環境：

（1）提前對資料庫做一次全庫冷備份；

（2）在seiang使用者下有一張test1表隸屬於seiang表空間；


定位錯誤操作發生的時間或SCN：logminer


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

  5. OWNER TABLE_NAME TABLESPACE_NAME
  6. ------------------------------ ------------------------------ ------------------------------
  7. SEIANG TEST1 SEIANG
  8. SEIANG TEST2 WJQ
  9. SEIANG TEST3 WJQBEST

  11. --檢視test1表中資料的內容
  12. SYS@seiang11g>select * from seiang.test1;

  14. ID NAME AGE
  15. ---------- ------------------------------------------------------------ ----------
  16.       1 wjq 23
  17.       2 seiang 24

  19. --檢視當前日誌序號為52
  20. SYS@seiang11g>select group#,sequence#,status from v$log;

  22.     GROUP# SEQUENCE# STATUS
  23. ---------- ---------- ----------------
  24.          1 52 CURRENT
  25.          2 50 INACTIVE
  26.          3 51 INACTIVE

  28. --誤刪除表test1表，同時purge
  29. SYS@seiang11g>set time on
  30. 11:32:47 SYS@seiang11g>drop table seiang.test1 purge;
  31. Table dropped.

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

  36. --進行兩次日誌切換
  37. 11:37:31 SYS@seiang11g>alter system switch logfile;
  38. System altered.

  40. 11:37:51 SYS@seiang11g>/
  41. System altered.

  43. --檢視當前日誌序號為54
  44. 11:38:00 SYS@seiang11g>select group#,sequence#,status from v$log;

  46.     GROUP# SEQUENCE# STATUS
  47. ---------- ---------- ----------------
  48.          1 52 ACTIVE
  49.          2 53 ACTIVE
  50.          3 54 CURRENT

  52. 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;

  54. NAME
  55. ------------------------------------------------------------
  56. ………(省略N條歸檔日誌)……….
  57. /u01/app/oracle/arch/arch_1_949237404_49.log
  58. /u01/app/oracle/arch/arch_1_949237404_50.log
  59. /u01/app/oracle/arch/arch_1_949237404_51.log
  60. /u01/app/oracle/arch/arch_1_949237404_52.log（test1表的刪除記錄在該歸檔日誌檔案中）
  61. /u01/app/oracle/arch/arch_1_949237404_53.log


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

  66. NAME FIRST_TIME NEXT_TIME
  67. ------------------------------------------------------------ ------------------- -------------------
  68. ……..(此處省略部分歸檔)…………..
  69. /u01/app/oracle/arch/arch_1_949237404_49.log 2017-08-01 04:02:47 2017-08-01 15:00:39
  70. /u01/app/oracle/arch/arch_1_949237404_50.log 2017-08-01 15:00:39 2017-08-01 22:00:33
  71. /u01/app/oracle/arch/arch_1_949237404_51.log 2017-08-01 22:00:33 2017-08-02 00:00:03
  72. /u01/app/oracle/arch/arch_1_949237404_52.log 2017-08-02 00:00:03 2017-08-02 11:37:51
  73. /u01/app/oracle/arch/arch_1_949237404_53.log 2017-08-02 11:37:51 2017-08-02 11:37:59

  75. --新增一條日誌條目，該日誌記錄了刪除test1時的歸檔日誌資訊
  76. SYS@seiang11g>exec dbms_logmnr.add_logfile(logfilename => '/u01/app/oracle/arch/arch_1_949237404_52.log', options => dbms_logmnr.new);
  77. PL/SQL procedure successfully completed.

  79. --開始解析日誌條目
  80. SYS@seiang11g>exec dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog);
  81. PL/SQL procedure successfully completed.

  83. --檢視v$logmnr_contents檢視，找出刪除test1表時的SCN（1914743）和時間戳
  84. SYS@seiang11g>col sql_redo for a50
  85. SYS@seiang11g>select scn, timestamp, sql_redo from v$logmnr_contents where seg_owner='SEIANG';

  87.        SCN TIMESTAMP SQL_REDO
  88. ---------- ------------------- --------------------------------------------------
  89.    1914743 2017-08-02 11:32:53 drop table seiang.test1 purge;

  91. --檢視資料檔案頭資訊，挖掘出來的SCN比此時的SCN要小
  92. SYS@seiang11g>select file#,name,checkpoint_change# from v$datafile_header;

  94.      FILE# NAME CHECKPOINT_CHANGE#
  95. ---------- ------------------------------------------------------------ ------------------
  96.          1 /u01/app/oracle/oradata/OraDB11g/system01.dbf 1915266
  97.          2 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf 1915266
  98.          3 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf 1915266
  99.          4 /u01/app/oracle/oradata/OraDB11g/users01.dbf 1915266
  100.          5 /u01/app/oracle/oradata/OraDB11g/example01.dbf 1915266
  101.          6 /u01/app/oracle/oradata/OraDB11g/rman01.dbf 1915266
  102.          7 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf 1915266
  103.          8 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf 1915266
  104.          9 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf 1915266

  106. --關閉資料庫，刪除所有的資料檔案
  107. SYS@seiang11g>shutdown immediate
  108. Database closed.
  109. Database dismounted.
  110. ORACLE instance shut down.

  112. [oracle@seiang11g OraDB11g]$ rm *.dbf
  113. [oracle@seiang11g OraDB11g]$
  114. [oracle@seiang11g OraDB11g]$ ll
  115. total 163164
  116. -rw-r----- 1 oracle oinstall 9781248 Aug 2 11:55 control01.ctl
  117. -rw-r----- 1 oracle oinstall 52429312 Aug 2 11:37 redo01.log
  118. -rw-r----- 1 oracle oinstall 52429312 Aug 2 11:37 redo02.log
  119. -rw-r----- 1 oracle oinstall 52429312 Aug 2 11:55 redo03.log

  121. --重新啟動資料庫，由於缺少資料檔案而出現報錯資訊
  122. SYS@seiang11g>startup
  123. ORACLE instance started.

  125. Total System Global Area 1252663296 bytes
  126. Fixed Size 2252824 bytes
  127. Variable Size 788533224 bytes
  128. Database Buffers 452984832 bytes
  129. Redo Buffers 8892416 bytes
  130. Database mounted.
  131. ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
  132. ORA-01110: data file 1: '/u01/app/oracle/oradata/OraDB11g/system01.dbf'

  134. --檢視需要恢復的資料檔案
  135. SYS@seiang11g>select * from v$recover_file;

  137.      FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
  138. ---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
  139.          1 ONLINE ONLINE 1913765 02-AUG-17
  140.          2 ONLINE ONLINE 1913765 02-AUG-17
  141.          3 ONLINE ONLINE 1913765 02-AUG-17
  142.          4 ONLINE ONLINE 1913765 02-AUG-17
  143.          5 ONLINE ONLINE 1913765 02-AUG-17
  144.          6 ONLINE ONLINE 1913765 02-AUG-17
  145.          7 ONLINE ONLINE 1913765 02-AUG-17
  146.          8 ONLINE ONLINE 1913765 02-AUG-17
  147.          9 ONLINE ONLINE 1913765 02-AUG-17


  150. --還原冷備份的所有資料檔案

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


    --根據挖掘出來的SCN（1914743），執行資料的不完全恢復

    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表空間；



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


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

  14.         ID NAME AGE
  15. ---------- ------------------------------ ----------
  16.          1 wjq 23
  17.          2 seiang 24

  19. --檢視當前的日誌序號為1
  20. SYS@seiang11g>select group#,sequence#,status from v$log;

  22.     GROUP# SEQUENCE# STATUS
  23. ---------- ---------- ----------------
  24.          1 1 CURRENT
  25.          2 0 UNUSED
  26.          3 0 UNUSED

  28. --在test1表中插入一條資料，提交，並切換日誌，該插入資訊記錄在歸檔日誌1中
  29. SYS@seiang11g>insert into seiang.test1 values(3,'wjqgood',25);
  30. 1 row created.
  31. SYS@seiang11g>commit;
  32. Commit complete.
  33. SYS@seiang11g>alter system switch logfile;
  34. System altered.

  36. --在test1表中插入一條資料，提交，並切換日誌，該插入資訊記錄在歸檔日誌2中
  37. SYS@seiang11g>insert into seiang.test1 values(4,'wjqbest',30);
  38. 1 row created.
  39. SYS@seiang11g>commit;
  40. Commit complete.
  41. SYS@seiang11g>alter system switch logfile;
  42. System altered.

  44. --檢視當前的日誌序號為3
  45. SYS@seiang11g>select group#,sequence#,status from v$log;

  47.     GROUP# SEQUENCE# STATUS
  48. ---------- ---------- ----------------
  49.          1 1 ACTIVE
  50.          2 2 ACTIVE
  51.          3 3 CURRENT

  53. --檢視歸檔日誌的資訊
  54. SYS@seiang11g>select name from v$archived_log;

  56. NAME
  57. --------------------------------------------------------
  58. /u01/app/oracle/arch/arch_1_950971495_1.log
  59. /u01/app/oracle/arch/arch_1_950971495_2.log

  61. --在test1表中插入一條資料，提交，不切換日誌，該插入資訊記錄在當前日誌3中
  62. SYS@seiang11g>insert into seiang.test1 values(5,'wjqseiang',60);
  63. 1 row created.
  64. SYS@seiang11g>commit;
  65. Commit complete.

  67. --檢視test1表中現有的資料資訊
  68. SYS@seiang11g>select * from seiang.test1;

  70.         ID NAME AGE
  71. ---------- ------------------------------ ----------
  72.          1 wjq 23
  73.          2 seiang 24
  74.          3 wjqgood 25（在歸檔arch_1_950971495_1.log）
  75.          4 wjqbest 30（在歸檔arch_1_950971495_2.log）
  76.          5 wjqseiang 60（在當前日誌redo03.log）


  79. --模擬當前日誌丟失或損壞，資料庫崩潰
  80. [oracle@seiang11g OraDB11g]$ rm redo03.log
  81. SYS@seiang11g>shutdown abort
  82. ORACLE instance shut down.


  85. --啟動資料庫，由於缺少當前日誌資訊出現報錯資訊
  86. SYS@seiang11g>startup
  87. ORACLE instance started.

  89. Total System Global Area 1252663296 bytes
  90. Fixed Size 2252824 bytes
  91. Variable Size 788533224 bytes
  92. Database Buffers 452984832 bytes
  93. Redo Buffers 8892416 bytes
  94. Database mounted.
  95. ORA-00313: open failed for members of log group 3 of thread 1
  96. ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/OraDB11g/redo03.log'
  97. ORA-27037: unable to obtain file status
  98. Linux-x86_64 Error: 2: No such file or directory
  99. Additional information: 3

  101. --還原所有的冷備份資料檔案
  102. [oracle@seiang11g OraDB11g]$ rm *.dbf
  103. [oracle@seiang11g OraDB11g]$
  104. [oracle@seiang11g OraDB11g]$ ll
  105. total 163164
  106. -rw-r----- 1 oracle oinstall 9781248 Aug 2 14:57 control01.ctl
  107. -rw-r----- 1 oracle oinstall 52429312 Aug 2 14:56 redo01.log
  108. -rw-r----- 1 oracle oinstall 52429312 Aug 2 14:56 redo02.log
  109. -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.     

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

  15. ORA-01112: media recovery not started



  19. --嘗試進行手工完全恢復，由於缺少當前日誌失敗
  20. SYS@seiang11g>recover database;
  21. ORA-00283: recovery session canceled due to errors
  22. ORA-00313: open failed for members of log group 3 of thread 1
  23. ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/OraDB11g/redo03.log'
  24. ORA-27037: unable to obtain file status
  25. Linux-x86_64 Error: 2: No such file or directory
  26. Additional information: 3


  29. --執行不完全恢復
  30. SYS@seiang11g>recover database until cancel;
  31. ORA-00279: change 1914401 generated at 08/02/2017 14:49:14 needed for thread 1
  32. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_3.log（注意提示資訊）
  33. ORA-00280: change 1914401 for thread 1 is in sequence #3
  34. （該歸檔日誌不存在）

  36. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
  37. cancel
  38. Media recovery cancelled.

  40. --恢復完成後，使用resetlogs開啟資料庫
  41. SYS@seiang11g>alter database open resetlogs;
  42. Database altered.

  44. --檢視當前日誌序號重新從1開始記錄
  45. SYS@seiang11g>select group#,sequence#,status from v$log;

  47.     GROUP# SEQUENCE# STATUS
  48. ---------- ---------- ----------------
  49.          1 1 CURRENT
  50.          2 0 UNUSED
  51.          3 0 UNUSED

  53. --檢視錶test1中的資料資訊
  54. SYS@seiang11g>select * from seiang.test1;

  56.         ID NAME AGE
  57. ---------- ------------------------------ ----------
  58.          1 wjq 23
  59.          2 seiang 24
  60.          3 wjqdood 25
  61.          4 wjqbest 30
  62. 可以發現儲存在當前日誌檔案中的資料記錄被回滾掉了！！！


示例三：歸檔日誌丟失或損壞


環境：

（1）提前有一套完成的資料檔案的冷備份；

（2）在seiang使用者下有一張test1表隸屬於seiang表空間；

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

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

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

  11.         ID NAME AGE
  12. ---------- ------------------------------ ----------
  13.          1 wjq 23
  14.          2 seiang 24
  15.          3 wjqdood 25
  16.          4 wjqbest 30

  18. --檢視當前日誌序號為2
  19. SYS@seiang11g>select group#,sequence#,status from v$log;

  21.     GROUP# SEQUENCE# STATUS
  22. ---------- ---------- ----------------
  23.          1 1 INACTIVE
  24.          2 2 CURRENT
  25.          3 0 UNUSED


  28. --修改資料並提交(sequence號2)
  29. SYS@seiang11g>update seiang.test1 set age=100 where id=1;
  30. 1 row updated.

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

  35. SYS@seiang11g>alter system switch logfile;
  36. System altered.

  38. --修改資料並提交(sequence號3)
  39. SYS@seiang11g>update seiang.test1 set age=200 where id=1;
  40. 1 row updated.

  42. SYS@seiang11g>commit;
  43. Commit complete.

  45. SYS@seiang11g>alter system switch logfile;
  46. System altered.

  48. --修改資料並提交(sequence號4)
  49. SYS@seiang11g>update seiang.test1 set age=300 where id=1;
  50. 1 row updated.

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

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

  58. --修改資料並提交(sequence號5)
  59. SYS@seiang11g>update seiang.test1 set age=400 where id=1;
  60. 1 row updated.

  62. SYS@seiang11g>commit;
  63. Commit complete.

  65. SYS@seiang11g>alter system switch logfile;
  66. System altered.

  68. --修改資料並提交(sequence號6)
  69. SYS@seiang11g>update seiang.test1 set age=500 where id=1;
  70. 1 row updated.

  72. SYS@seiang11g>commit;
  73. Commit complete.

  75. SYS@seiang11g>alter system switch logfile;
  76. System altered.

  78. --修改資料並提交(sequence號7)
  79. SYS@seiang11g>update seiang.test1 set age=600 where id=1;
  80. 1 row updated.

  82. SYS@seiang11g>commit;
  83. Commit complete.

  85. SYS@seiang11g>alter system switch logfile;
  86. System altered.

  88. --當前聯機日誌序號為8
  89. SYS@seiang11g>select group#,sequence#,status from v$log;

  91.     GROUP# SEQUENCE# STATUS
  92. ---------- ---------- ----------------
  93.          1 7 ACTIVE
  94.          2 8 CURRENT
  95.          3 6 ACTIVE

  97. --檢視相應的歸檔日誌
  98. SYS@seiang11g>select name from v$archived_log;

  100. NAME
  101. ------------------------------------------------------
  102. /u01/app/oracle/arch/arch_1_950962051_1.log
  103. /u01/app/oracle/arch/arch_1_950962051_2.log
  104. /u01/app/oracle/arch/arch_1_950971495_1.log
  105. /u01/app/oracle/arch/arch_1_950971495_2.log
  106. /u01/app/oracle/arch/arch_1_950972396_1.log
  107. /u01/app/oracle/arch/arch_1_950972396_2.log
  108. /u01/app/oracle/arch/arch_1_950972396_3.log
  109. /u01/app/oracle/arch/arch_1_950972396_4.log
  110. /u01/app/oracle/arch/arch_1_950972396_5.log
  111. /u01/app/oracle/arch/arch_1_950972396_6.log
  112. /u01/app/oracle/arch/arch_1_950972396_7.log

  114. --關閉資料庫
  115. SYS@seiang11g >shutdown immediate
  116. Database closed.
  117. Database dismounted.
  118. ORACLE instance shut down.

  120. --模擬資料檔案seiang損壞
  121. SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/seiang01.dbf


  124. --重新開啟資料庫 ，由於資料檔案的丟死，資料庫無法open
  125. SYS@seiang11g>startup
  126. ORACLE instance started.

  128. Total System Global Area 1252663296 bytes
  129. Fixed Size 2252824 bytes
  130. Variable Size 788533224 bytes
  131. Database Buffers 452984832 bytes
  132. Redo Buffers 8892416 bytes
  133. Database mounted.
  134. ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
  135. ORA-01110: data file 7: '/u01/app/oracle/oradata/OraDB11g/seiang01.dbf'


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

  140.      FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
  141. ---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
  142.          7 ONLINE ONLINE FILE NOT FOUND 0

  144. --還原備份的資料檔案
  145. SYS@seiang11g>host cp /u01/app/oracle/UMAN_Backup/seiang01.dbf /u01/app/oracle/oradata/OraDB11g/
  146.     
  147. SYS@seiang11g>select * from v$recover_file;

  149.      FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
  150. ---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
  151.          7 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17


  154. --檢視一下歸檔日誌的詳細資訊
  155. SYS@seiang11g>select sequence#, name, first_change#, next_change# from v$archived_log;

  157.  SEQUENCE# NAME FIRST_CHANGE# NEXT_CHANGE#
  158. ---------- -------------------------------------------------- ------------- ------------
  159.         1 /u01/app/oracle/arch/arch_1_950962051_1.log 1914744 1921854
  160.          2 /u01/app/oracle/arch/arch_1_950962051_2.log 1921854 1921883
  161.          1 /u01/app/oracle/arch/arch_1_950971495_1.log 1913766 1914386
  162.          2 /u01/app/oracle/arch/arch_1_950971495_2.log 1914386 1914401
  163.          1 /u01/app/oracle/arch/arch_1_950972396_1.log 1914402 1936446
  164.          2 /u01/app/oracle/arch/arch_1_950972396_2.log 1936446 1937042
  165.          3 /u01/app/oracle/arch/arch_1_950972396_3.log 1937042 1937100
  166.          4 /u01/app/oracle/arch/arch_1_950972396_4.log 1937100 1937110
  167.          5 /u01/app/oracle/arch/arch_1_950972396_5.log 1937110 1937123
  168.          6 /u01/app/oracle/arch/arch_1_950972396_6.log 1937123 1937139
  169.          7 /u01/app/oracle/arch/arch_1_950972396_7.log 1937139 1937148


  172. --模擬歸檔日誌5丟失或者損壞
  173. SYS@seiang11g>host rm /u01/app/oracle/arch/arch_1_950972396_5.log

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


  179. --執行恢復操作，當在需要歸檔日誌5的時候出現錯誤
  180. SYS@seiang11g>recover datafile 7;
  181. ORA-00279: change 1913766 generated at 08/02/2017 11:12:22 needed for thread 1
  182. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_1.log
  183. ORA-00280: change 1913766 for thread 1 is in sequence #1

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

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

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

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

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

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

  211. ORA-00308: cannot open archived log '/u01/app/oracle/arch/arch_1_950972396_5.log'
  212. ORA-27037: unable to obtain file status
  213. Linux-x86_64 Error: 2: No such file or directory
  214. Additional information: 3
  215.     
  216. --檢視資料檔案頭，發現檢查點不一致
  217. SYS@seiang11g>select file#,name,checkpoint_change# from v$datafile_header;

  219.      FILE# NAME CHECKPOINT_CHANGE#
  220. ---------- -------------------------------------------------- ------------------
  221.          1 /u01/app/oracle/oradata/OraDB11g/system01.dbf 1937617
  222.          2 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf 1937617
  223.          3 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf 1937617
  224.          4 /u01/app/oracle/oradata/OraDB11g/users01.dbf 1937617
  225.          5 /u01/app/oracle/oradata/OraDB11g/example01.dbf 1937617
  226.          6 /u01/app/oracle/oradata/OraDB11g/rman01.dbf 1937617
  227.          7 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf 1937110
  228.          8 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf 1937617
  229.          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

  20. --重新開啟資料庫
  21. SYS@seiang11g>startup
  22. ORACLE instance started.

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


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

  36.      FILE# NAME CHECKPOINT_CHANGE#
  37. ---------- -------------------------------------------------- ------------------
  38.          1 /u01/app/oracle/oradata/OraDB11g/system01.dbf 1913765
  39.          2 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf 1913765
  40.          3 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf 1913765
  41.          4 /u01/app/oracle/oradata/OraDB11g/users01.dbf 1913765
  42.          5 /u01/app/oracle/oradata/OraDB11g/example01.dbf 1913765
  43.          6 /u01/app/oracle/oradata/OraDB11g/rman01.dbf 1913765
  44.          7 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf 1913765
  45.          8 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf 1913765
  46.          9 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf 1913765

  48. 9 rows selected.

  50. --恢復資料庫 ，首先嚐試完全恢復，同樣在需要歸檔日誌5的時候出現錯誤
  51. SYS@seiang11g>recover database;
  52. ORA-00279: change 1913766 generated at 08/02/2017 11:12:22 needed for thread 1
  53. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_1.log
  54. ORA-00280: change 1913766 for thread 1 is in sequence #1

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

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

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

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

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

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

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


  88. --執行不完全恢復 ，成功
  89. SYS@seiang11g>recover database until cancel;
  90. ORA-00279: change 1937110 generated at 08/02/2017 15:51:54 needed for thread 1
  91. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_5.log
  92. ORA-00280: change 1937110 for thread 1 is in sequence #5


  95. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
  96. cancel
  97. Media recovery cancelled.

  99. --再次檢視資料檔案頭的相關資訊
  100. SYS@seiang11g>select file#,name,checkpoint_change# from v$datafile_header;

  102.      FILE# NAME CHECKPOINT_CHANGE#
  103. ---------- -------------------------------------------------- ------------------
  104.          1 /u01/app/oracle/oradata/OraDB11g/system01.dbf 1937110
  105.          2 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf 1937110
  106.          3 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf 1937110
  107.          4 /u01/app/oracle/oradata/OraDB11g/users01.dbf 1937110
  108.          5 /u01/app/oracle/oradata/OraDB11g/example01.dbf 1937110
  109.          6 /u01/app/oracle/oradata/OraDB11g/rman01.dbf 1937110
  110.          7 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf 1937110
  111.          8 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf 1937110
  112.          9 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf 1937110

  114. --檢視日誌組資訊，發現與5號脫節
  115. SYS@seiang11g>select group#,sequence#,status from v$log;

  117.     GROUP# SEQUENCE# STATUS
  118. ---------- ---------- ----------------
  119.          1 7 INACTIVE
  120.          3 6 INACTIVE
  121.          2 8 CURRENT

  123. --使用resetlogs開啟資料庫
  124. SYS@seiang11g>alter database open resetlogs;
  125. Database altered.

  127. --日誌序號從1開始記錄
  128. SYS@seiang11g>select group#,sequence#,status from v$log;

  130.     GROUP# SEQUENCE# STATUS
  131. ---------- ---------- ----------------
  132.          1 1 CURRENT
  133.          2 0 UNUSED
  134.          3 0 UNUSED

  136. --確認表中的資料資訊，發現在5號歸檔日誌丟失後的修改都沒有生效
  137. SYS@seiang11g>select * from seiang.test1;

  139.         ID NAME AGE
  140. ---------- -------------------------------------------------- ----------
  141.          1 wjq 300
  142.          2 seiang 24
  143.          3 wjqdood 25
  144.          4 wjqbest 30


相關連線：

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


作者：SEian.G（苦練七十二變，笑對八十一難）

ITPUB：http://blog.itpub.net/31015730/

51CTO：http://seiang.blog.51cto.com/



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31015730/viewspace-2142995/，如需轉載，請註明出處，否則將追究法律責任。

相關文章