Oracle BBED 跳過歸檔實現完全恢復

kunlunzhiying發表於2016-12-19
原文網址 : http://blog.itpub.net/28211342/viewspace-2130944/
Oracle
很多時候丟失歸檔，但是客戶不想resetlogs open庫。可以使用bbed修改scn實現完全恢復。


點選(此處)摺疊或開啟

  1. [oracle@yws ~]$ df -h
  2. Filesystem Size Used Avail Use% Mounted on
  3. /dev/sda2 18G 9.1G 7.3G 56% /
  4. /dev/sda1 289M 16M 258M 6% /boot
  5. tmpfs 506M 0 506M 0% /dev/shm

  7. [oracle@yws yws]$ sqlplus / as sysdba

  9. SQL*Plus: Release 10.2.0.5.0 - Production on Fri Dec 20 18:59:57 2013

  11. Copyright (c) 1982, 2010, Oracle. All Rights Reserved.


  14. Connected to:
  15. Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
  16. With the Partitioning, OLAP, Data Mining and Real Application Testing options


  19. SQL> shutdown immediate; --關庫
  20. Database closed.
  21. Database dismounted.
  22. ORACLE instance shut down.
  23. SQL> exit
  24. Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
  25. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  26. [oracle@yws yws]$ mkdir /home/oracle/bak
  27. [oracle@yws yws]$ ls
  28. control01.ctl control03.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
  29. control02.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
  30. [oracle@yws yws]$ cp * /home/oracle/bak --物理全備
  31. [oracle@yws yws]$ sqlplus / as sysdba

  33. SQL*Plus: Release 10.2.0.5.0 - Production on Fri Dec 20 19:04:30 2013

  35. Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

  37. Connected to an idle instance.

  39. SQL> startup
  40. ORACLE instance started.

  42. Total System Global Area 285212672 bytes
  43. Fixed Size 1273276 bytes
  44. Variable Size 192938564 bytes
  45. Database Buffers 88080384 bytes
  46. Redo Buffers 2920448 bytes
  47. Database mounted.
  48. Database opened.
  49. SQL> archive log list
  50. Database log mode Archive Mode
  51. Automatic archival Enabled
  52. Archive destination /arc
  53. Oldest online log sequence 140
  54. Next log sequence to archive 142
  55. Current log sequence 142
  56. SQL> set linesize 1000


  59. SQL> select * from v$log;

  61.     GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
  62. ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
  63.          1 1 142 52428800 1 NO CURRENT 2241033 20-DEC-13
  64.          2 1 140 52428800 1 YES INACTIVE 2220503 18-DEC-13
  65.          3 1 141 52428800 1 YES INACTIVE 2230571 18-DEC-13

  67. SQL> alter system switch logfile; --切日誌產生歸檔

  69. System altered.

  71. SQL> /

  73. System altered.

  75. SQL> /

  77. System altered.

  79. SQL> /

  81. System altered.

  83. SQL> /

  85. System altered.

  87. SQL> /

  89. System altered.

  91. SQL> /

  93. System altered.

  95. SQL> /

  97. System altered.

  99. SQL> /

  101. System altered.

  103. SQL> select * from v$Log; --看序列號

  105.     GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
  106. ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
  107.          1 1 151 52428800 1 NO CURRENT 2242730 20-DEC-13
  108.          2 1 149 52428800 1 YES INACTIVE 2242726 20-DEC-13
  109.          3 1 150 52428800 1 YES INACTIVE 2242728 20-DEC-13


  112. SQL> archive log list;
  113. Database log mode Archive Mode
  114. Automatic archival Enabled
  115. Archive destination /arc
  116. Oldest online log sequence 149
  117. Next log sequence to archive 151
  118. Current log sequence 151
  119. SQL> exit
  120. Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
  121. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  122. [oracle@yws yws]$ cd /arc --檢視歸檔
  123. [oracle@yws arc]$ ls
  124. 1_113_832788550.dbf 1_120_832788550.dbf 1_127_832788550.dbf 1_134_832788550.dbf 1_141_832788550.dbf 1_148_832788550.dbf
  125. 1_114_832788550.dbf 1_121_832788550.dbf 1_128_832788550.dbf 1_135_832788550.dbf 1_142_832788550.dbf 1_149_832788550.dbf
  126. 1_115_832788550.dbf 1_122_832788550.dbf 1_129_832788550.dbf 1_136_832788550.dbf 1_143_832788550.dbf 1_150_832788550.dbf
  127. 1_116_832788550.dbf 1_123_832788550.dbf 1_130_832788550.dbf 1_137_832788550.dbf 1_144_832788550.dbf
  128. 1_117_832788550.dbf 1_124_832788550.dbf 1_131_832788550.dbf 1_138_832788550.dbf 1_145_832788550.dbf
  129. 1_118_832788550.dbf 1_125_832788550.dbf 1_132_832788550.dbf 1_139_832788550.dbf 1_146_832788550.dbf
  130. 1_119_832788550.dbf 1_126_832788550.dbf 1_133_832788550.dbf 1_140_832788550.dbf 1_147_832788550.dbf
  131. [oracle@yws arc]$ mv 1_149_832788550.dbf /home/oracle/ --移除在此期間產生的歸檔
  132. [oracle@yws arc]$ mv 1_150_832788550.dbf /home/oracle/
  133. [oracle@yws arc]$ mv 1_143_832788550.dbf /home/oracle/
  134. [oracle@yws arc]$ mv 1_144_832788550.dbf /home/oracle/
  135. [oracle@yws arc]$ mv 1_145_832788550.dbf /home/oracle/

  137. [oracle@yws yws]$ rm -rf users01.dbf --刪除資料檔案
  138. [oracle@yws yws]$ sqlplus / as sysdba

  140. SQL*Plus: Release 10.2.0.5.0 - Production on Fri Dec 20 19:09:37 2013

  142. Copyright (c) 1982, 2010, Oracle. All Rights Reserved.


  145. Connected to:
  146. Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
  147. With the Partitioning, OLAP, Data Mining and Real Application Testing options

  149. SQL> conn scott/tiger
  150. Connected.
  151. SQL> select * from emp; --檔案丟失 報錯
  152. select * from emp
  153.               *
  154. ERROR at line 1:
  155. ORA-01116: error in opening database file 4
  156. ORA-01110: data file 4: \'/u01/oracle/oradata/yws/users01.dbf\'
  157. ORA-27041: unable to open file
  158. Linux Error: 2: No such file or directory
  159. Additional information: 3


  162. SQL> conn / as sysdba
  163. Connected.
  164. SQL> select status from v$instance;

  166. STATUS
  167. ------------
  168. OPEN


  171. SQL> show user
  172. USER is \"SYS\"
  173. SQL> select * from v$recover_file;

  175. no rows selected

  177. SQL> alter system checkpoint;

  179. System altered.

  181. SQL> select * from v$recover_file;

  183. no rows selected

  185. SQL> select name from v$datafile;

  187. NAME
  188. --------------------------------------------------------------------------------
  189. /u01/oracle/oradata/yws/system01.dbf
  190. /u01/oracle/oradata/yws/undotbs01.dbf
  191. /u01/oracle/oradata/yws/sysaux01.dbf
  192. /u01/oracle/oradata/yws/users01.dbf
  193. /u01/oracle/oradata/yws/example01.dbf

  195. SQL> select * from v$recover_file;

  197. no rows selected

  199. SQL> shutdown immedIATE; --正常關庫報錯 2.1應該可以 2.5可能有保護機制
  200. ORA-01116: error in opening database file 4
  201. ORA-01110: data file 4: \'/u01/oracle/oradata/yws/users01.dbf\'
  202. ORA-27041: unable to open file
  203. Linux Error: 2: No such file or directory
  204. Additional information: 3
  205. SQL> select status from v$instance;

  207. STATUS
  208. ------------
  209. OPEN

  211. SQL> shutdown abort; --強制停庫
  212. ORACLE instance shut down.
  213. SQL> startup;
  214. ORACLE instance started.

  216. Total System Global Area 285212672 bytes
  217. Fixed Size 1273276 bytes
  218. Variable Size 192938564 bytes
  219. Database Buffers 88080384 bytes
  220. Redo Buffers 2920448 bytes
  221. Database mounted.
  222. ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
  223. ORA-01110: data file 4: \'/u01/oracle/oradata/yws/users01.dbf\'


  226. SQL> select status from v$instance;

  228. STATUS
  229. ------------
  230. MOUNTED

  232. SQL> select * from v$recover_file; --檢視需要恢復的檔案

  234.      FILE# ONLINE ONLINE_
  235. ---------- ------- -------
  236. ERROR CHANGE#
  237. ----------------------------------------------------------------- ----------
  238. TIME
  239. ---------
  240.          4 ONLINE ONLINE
  241. FILE NOT FOUND 0



  245. [oracle@yws oradata]$ mv users01.dbf yws/ --把原來的備份拿回來
  246. [oracle@yws oradata]$ cd yws/
  247. [oracle@yws yws]$ ls
  248. control01.ctl control03.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
  249. control02.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
  250. [oracle@yws yws]$ sqlplus / as sysdba

  252. SQL*Plus: Release 10.2.0.5.0 - Production on Fri Dec 20 19:21:08 2013

  254. Copyright (c) 1982, 2010, Oracle. All Rights Reserved.


  257. Connected to:
  258. Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
  259. With the Partitioning, OLAP, Data Mining and Real Application Testing options

  261. SQL> alter database open;
  262. alter database open
  263. *
  264. ERROR at line 1:
  265. ORA-01113: file 4 needs media recovery if it was restored from backup, or END
  266. BACKUP if it was not
  267. ORA-01110: data file 4: \'/u01/oracle/oradata/yws/users01.dbf\'


  270. SQL> recover datafile 4; --恢復
  271. ORA-00279: change 2242425 generated at 12/20/2013 19:00:23 needed for thread 1
  272. ORA-00289: suggestion : /arc/1_142_832788550.dbf
  273. ORA-00280: change 2242425 for thread 1 is in sequence #142


  276. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
  277. auto --自動模式讓他去選擇
  278. ORA-00279: change 2242713 generated at 12/20/2013 19:06:21 needed for thread 1
  279. ORA-00289: suggestion : /arc/1_143_832788550.dbf
  280. ORA-00280: change 2242713 for thread 1 is in sequence #143
  281. ORA-00278: log file \'/arc/1_142_832788550.dbf\' no longer needed for this
  282. recovery


  285. ORA-00308: cannot open archived log \'/arc/1_143_832788550.dbf\' --需要seq為143的歸檔 此檔案已經被我們移走
  286. ORA-27037: unable to obtain file status
  287. Linux Error: 2: No such file or directory
  288. Additional information: 3

使用bbed實現完全恢復


點選(此處)摺疊或開啟

  1. 點選(此處)摺疊或開啟
  2. SQL> select status from v$instance;
  4. STATUS
  5. ------------
  6. MOUNTED
  8. SQL> select name,CHECKPOINT_CHANGE# from v$datafile_header; --檢視資料檔案頭scn
  11. NAME CHECKPOINT_CHANGE#
  12. ---------------------------------------------------------------------------------------------------- ------------------
  13. /u01/oracle/oradata/yws/system01.dbf 2242908
  14. /u01/oracle/oradata/yws/undotbs01.dbf 2242908
  15. /u01/oracle/oradata/yws/sysaux01.dbf 2242908
  16. /u01/oracle/oradata/yws/users01.dbf 2242713
  17. /u01/oracle/oradata/yws/example01.dbf 2242908
  19. SQL> select to_char('2242713','XXXXXXXXX') from dual; --4號檔案scn 轉成16進位制值
  21. TO_CHAR('2
  22. ----------
  23. 223899
  26. bbed 修改
  29. BBED> set dba 1,1
  30. DBA 0x00400001 (4194305 1,1)
  32. BBED> set offset 484
  33. OFFSET 484
  35. BBED> p
  36. pad
  37. ---
  38. ub1 pad @484 0x5c
  40. BBED> dump --檢視1號資料檔案的scn
  41. File: /u01/oracle/oradata/yws/system01.dbf (1)
  42. Block: 1 Offsets: 484 to 995 Dba:0x00400001
  43. ------------------------------------------------------------------------
  44. 5c392200 0000a8bf 1e68c031 0100b552 97000000 95000000 1000af2e 02000000
  45. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  46. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  47. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  48. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  49. 0a000a00 0a000100 00000000 00000000 00000000 02004000 00000000 00000000
  50. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  51. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  52. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  53. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  54. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  55. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  56. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  57. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  58. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  59. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  61. <32 bytes per line>
  63. BBED> set mode edit;
  64. MODE Edit
  66. BBED> modify /x 5c3922 dba 4,1 offset 484 --修改4號檔案的資料檔案頭scn一致
  67. Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
  68. File: /u01/oracle/oradata/yws/users01.dbf (4)
  69. Block: 1 Offsets: 484 to 995 Dba:0x01000001
  70. ------------------------------------------------------------------------
  71. 5c392200 00000000 ad66c031 01000000 8f000000 02000000 00000000 02000000
  72. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  73. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  74. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  75. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  76. 0a000a00 0a000100 00000000 00000000 00000000 02000001 00000000 00000000
  77. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  78. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  79. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  80. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  81. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  82. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  83. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  84. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  85. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  86. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  88. <32 bytes per line>
  90. BBED> sum apply;
  91. Check value for File 4, Block 1:
  92. current = 0xcdd9, required = 0xcdd9
  94. BBED> exit
  96. [oracle@yws yws]$ sqlplus / as sysdba
  98. SQL*Plus: Release 10.2.0.5.0 - Production on Fri Dec 20 19:39:04 2013
  100. Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
  103. Connected to:
  104. Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
  105. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  107. SQL> startup force;
  108. ORACLE instance started.
  110. Total System Global Area 285212672 bytes
  111. Fixed Size 1273276 bytes
  112. Variable Size 192938564 bytes
  113. Database Buffers 88080384 bytes
  114. Redo Buffers 2920448 bytes
  115. Database mounted.
  116. ORA-01113: file 4 needs media recovery if it was restored from backup, or END
  117. BACKUP if it was not
  118. ORA-01110: data file 4: '/u01/oracle/oradata/yws/users01.dbf'
  119. SQL> recover database;
    Media recovery complete.
    SQL> alter database open;


    Database altered.

  120. 成功open。



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

相關文章