溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

Oracle手工不完全恢復(fù)（一）：使用當(dāng)前控制文件

發(fā)布時(shí)間：2020-08-07 09:27:07 來源：ITPUB博客 閱讀：162 作者：迷倪小魏 欄目：關(guān)系型數(shù)據(jù)庫

實(shí)驗(yàn)環(huán)境

操作系統(tǒng)：CentOS 7.1

數(shù)據(jù)庫：Oracle 11.2.0.4


目錄

示例一：基于SCN或時(shí)間點(diǎn)的恢復(fù)----恢復(fù)過去某個(gè)時(shí)間誤刪除的表

示例二：當(dāng)前日志組損壞，造成數(shù)據(jù)庫崩潰

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



示例一：基于SCN或時(shí)間點(diǎn)的恢復(fù)----恢復(fù)過去某個(gè)時(shí)間誤刪除的表


環(huán)境：

（1）提前對(duì)數(shù)據(jù)庫做一次全庫冷備份；

（2）在seiang用戶下有一張test1表隸屬于seiang表空間；


定位錯(cuò)誤操作發(fā)生的時(shí)間或SCN：logminer

恢復(fù)原理： 

1.冷備份的數(shù)據(jù)文件*.dbf比現(xiàn)在要舊，那么數(shù)據(jù)文件的scn肯定就比現(xiàn)在的??；  

2.使用日志挖掘方法，在日志中找出刪表時(shí)的scn或時(shí)間戳；  

3.在數(shù)據(jù)庫shutdown的狀態(tài)下，用冷備份的數(shù)據(jù)文件*.dbf，覆蓋現(xiàn)在的*.dbf文件；保證數(shù)據(jù)文件完整性；

4.啟動(dòng)數(shù)據(jù)庫到mount下,恢復(fù)到日志挖掘的SCN或時(shí)間點(diǎn)（利用日志對(duì)數(shù)據(jù)文件重做一次）  

5.以resetlogs方式打開數(shù)據(jù)庫，以前的日志就被覆蓋了；


示例二：當(dāng)前日志組損壞，造成數(shù)據(jù)庫崩潰


環(huán)境：

（1）提前對(duì)數(shù)據(jù)庫做一次全庫冷備份；

（2）在seiang用戶下有一張test1表隸屬于seiang表空間；


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


環(huán)境：

（1）提前有一套完成的數(shù)據(jù)文件的冷備份；

（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. --查看當(dāng)前日志序號(hào)為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. --修改數(shù)據(jù)并提交(sequence號(hào)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. --修改數(shù)據(jù)并提交(sequence號(hào)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. --修改數(shù)據(jù)并提交(sequence號(hào)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. --修改數(shù)據(jù)并提交(sequence號(hào)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. --修改數(shù)據(jù)并提交(sequence號(hào)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. --修改數(shù)據(jù)并提交(sequence號(hào)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. --當(dāng)前聯(lián)機(jī)日志序號(hào)為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. --查看相應(yīng)的歸檔日志
  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. --關(guān)閉數(shù)據(jù)庫
  115. SYS@seiang11g >shutdown immediate
  116. Database closed.
  117. Database dismounted.
  118. ORACLE instance shut down.

  120. --模擬數(shù)據(jù)文件seiang損壞
  121. SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/seiang01.dbf


  124. --重新打開數(shù)據(jù)庫 ，由于數(shù)據(jù)文件的丟死，數(shù)據(jù)庫無法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. --還原備份的數(shù)據(jù)文件
  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. --查看一下歸檔日志的詳細(xì)信息
  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. --執(zhí)行恢復(fù)操作，當(dāng)在需要?dú)w檔日志5的時(shí)候出現(xiàn)錯(cuò)誤
  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. --查看數(shù)據(jù)文件頭，發(fā)現(xiàn)檢查點(diǎn)不一致
  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. --關(guān)閉數(shù)據(jù)庫 ，還原所有冷備份的數(shù)據(jù)文件

    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. --重新打開數(shù)據(jù)庫
  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. --恢復(fù)數(shù)據(jù)庫 ，首先嘗試完全恢復(fù)，同樣在需要?dú)w檔日志5的時(shí)候出現(xiàn)錯(cuò)誤
  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. --執(zhí)行不完全恢復(fù) ，成功
  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. --再次查看數(shù)據(jù)文件頭的相關(guān)信息
  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. --查看日志組信息，發(fā)現(xiàn)與5號(hào)脫節(jié)
  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打開數(shù)據(jù)庫
  124. SYS@seiang11g>alter database open resetlogs;
  125. Database altered.

  127. --日志序號(hào)從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. --確認(rèn)表中的數(shù)據(jù)信息，發(fā)現(xiàn)在5號(hào)歸檔日志丟失后的修改都沒有生效
  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


相關(guān)連接：

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


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

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

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



向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手