Oracle手工不完全恢复（一）：使用当前控制文件

实验环境

操作系统：CentOS 7.1

数据库：Oracle 11.2.0.4

目录

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

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

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

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

环境：

（1）提前对数据库做一次全库冷备份；

（2）在seiang用户下有一张test1表隶属于seiang表空间；

定位错误操作发生的时间或SCN：logminer

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

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

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

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

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

环境：

（1）提前对数据库做一次全库冷备份；

（2）在seiang用户下有一张test1表隶属于seiang表空间；

环境：

（1）提前有一套完成的数据文件的冷备份；

1. SYS@seiang11g>select owner,table_name,tablespace_name from dba_tables where owner='SEIANG';
   
2. 
   
3. OWNER TABLE_NAME TABLESPACE_NAME
   
4. ------------------------------ ------------------------------ ------------------------------
   
5. SEIANG TEST1 SEIANG
   
6. SEIANG TEST2 WJQ
   
7. SEIANG TEST3 WJQBEST
   
8. 
   
9. SYS@seiang11g>select * from seiang.test1;
   
10. 
    
11. ID NAME AGE
    
12. ---------- ------------------------------ ----------
    
13. 1 wjq 23
    
14. 2 seiang 24
    
15. 3 wjqdood 25
    
16. 4 wjqbest 30
    
17. 
    
18. --查看当前日志序号为2
    
19. SYS@seiang11g>select group#,sequence#,status from v$log;
    
20. 
    
21. GROUP# SEQUENCE# STATUS
    
22. ---------- ---------- ----------------
    
23. 1 1 INACTIVE
    
24. 2 2 CURRENT
    
25. 3 0 UNUSED
    
26. 
    
27. 
    
28. --修改数据并提交(sequence号2)
    
29. SYS@seiang11g>update seiang.test1 set age=100 where id=1;
    
30. 1 row updated.
    
31. 
    
32. SYS@seiang11g>commit;
    
33. Commit complete.
    
34. 
    
35. SYS@seiang11g>alter system switch logfile;
    
36. System altered.
    
37. 
    
38. --修改数据并提交(sequence号3)
    
39. SYS@seiang11g>update seiang.test1 set age=200 where id=1;
    
40. 1 row updated.
    
41. 
    
42. SYS@seiang11g>commit;
    
43. Commit complete.
    
44. 
    
45. SYS@seiang11g>alter system switch logfile;
    
46. System altered.
    
47. 
    
48. --修改数据并提交(sequence号4)
    
49. SYS@seiang11g>update seiang.test1 set age=300 where id=1;
    
50. 1 row updated.
    
51. 
    
52. SYS@seiang11g>commit;
    
53. Commit complete.
    
54. 
    
55. SYS@seiang11g>alter system switch logfile;
    
56. System altered.
    
57. 
    
58. --修改数据并提交(sequence号5)
    
59. SYS@seiang11g>update seiang.test1 set age=400 where id=1;
    
60. 1 row updated.
    
61. 
    
62. SYS@seiang11g>commit;
    
63. Commit complete.
    
64. 
    
65. SYS@seiang11g>alter system switch logfile;
    
66. System altered.
    
67. 
    
68. --修改数据并提交(sequence号6)
    
69. SYS@seiang11g>update seiang.test1 set age=500 where id=1;
    
70. 1 row updated.
    
71. 
    
72. SYS@seiang11g>commit;
    
73. Commit complete.
    
74. 
    
75. SYS@seiang11g>alter system switch logfile;
    
76. System altered.
    
77. 
    
78. --修改数据并提交(sequence号7)
    
79. SYS@seiang11g>update seiang.test1 set age=600 where id=1;
    
80. 1 row updated.
    
81. 
    
82. SYS@seiang11g>commit;
    
83. Commit complete.
    
84. 
    
85. SYS@seiang11g>alter system switch logfile;
    
86. System altered.
    
87. 
    
88. --当前联机日志序号为8
    
89. SYS@seiang11g>select group#,sequence#,status from v$log;
    
90. 
    
91. GROUP# SEQUENCE# STATUS
    
92. ---------- ---------- ----------------
    
93. 1 7 ACTIVE
    
94. 2 8 CURRENT
    
95. 3 6 ACTIVE
    
96. 
    
97. --查看相应的归档日志
    
98. SYS@seiang11g>select name from v$archived_log;
    
99. 
    
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
     
113. 
     
114. --关闭数据库
     
115. SYS@seiang11g >shutdown immediate
     
116. Database closed.
     
117. Database dismounted.
     
118. ORACLE instance shut down.
     
119. 
     
120. --模拟数据文件seiang损坏
     
121. SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
     
122. 
     
123. 
     
124. --重新打开数据库 ，由于数据文件的丢死，数据库无法open
     
125. SYS@seiang11g>startup
     
126. ORACLE instance started.
     
127. 
     
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'
     
136. 
     
137. 
     
138. SYS@seiang11g>select * from v$recover_file;
     
139. 
     
140. FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
     
141. ---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
     
142. 7 ONLINE ONLINE FILE NOT FOUND 0
     
143. 
     
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;
     
148. 
     
149. FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
     
150. ---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
     
151. 7 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
     
152. 
     
153. 
     
154. --查看一下归档日志的详细信息
     
155. SYS@seiang11g>select sequence#, name, first_change#, next_change# from v$archived_log;
     
156. 
     
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
     
170. 
     
171. 
     
172. --模拟归档日志5丢失或者损坏
     
173. SYS@seiang11g>host rm /u01/app/oracle/arch/arch_1_950972396_5.log
     
174. 
     
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
     
177. 
     
178. 
     
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
     
184. 
     
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
     
190. 
     
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
     
194. 
     
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
     
198. 
     
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
     
202. 
     
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
     
206. 
     
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
     
210. 
     
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;
     
218. 
     
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
    
19. 
    
20. --重新打开数据库
    
21. SYS@seiang11g>startup
    
22. ORACLE instance started.
    
23. 
    
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'
    
32. 
    
33. 
    
34. SYS@seiang11g>select file#,name,checkpoint_change# from v$datafile_header;
    
35. 
    
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
    
47. 
    
48. 9 rows selected.
    
49. 
    
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
    
55. 
    
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
    
61. 
    
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
    
65. 
    
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
    
69. 
    
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
    
73. 
    
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
    
77. 
    
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
    
81. 
    
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
    
86. 
    
87. 
    
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
    
93. 
    
94. 
    
95. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    
96. cancel
    
97. Media recovery cancelled.
    
98. 
    
99. --再次查看数据文件头的相关信息
    
100. SYS@seiang11g>select file#,name,checkpoint_change# from v$datafile_header;
     
101. 
     
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
     
113. 
     
114. --查看日志组信息，发现与5号脱节
     
115. SYS@seiang11g>select group#,sequence#,status from v$log;
     
116. 
     
117. GROUP# SEQUENCE# STATUS
     
118. ---------- ---------- ----------------
     
119. 1 7 INACTIVE
     
120. 3 6 INACTIVE
     
121. 2 8 CURRENT
     
122. 
     
123. --使用resetlogs打开数据库
     
124. SYS@seiang11g>alter database open resetlogs;
     
125. Database altered.
     
126. 
     
127. --日志序号从1开始记录
     
128. SYS@seiang11g>select group#,sequence#,status from v$log;
     
129. 
     
130. GROUP# SEQUENCE# STATUS
     
131. ---------- ---------- ----------------
     
132. 1 1 CURRENT
     
133. 2 0 UNUSED
     
134. 3 0 UNUSED
     
135. 
     
136. --确认表中的数据信息，发现在5号归档日志丢失后的修改都没有生效
     
137. SYS@seiang11g>select * from seiang.test1;
     
138. 
     
139. ID NAME AGE
     
140. ---------- -------------------------------------------------- ----------
     
141. 1 wjq 300
     
142. 2 seiang 24
     
143. 3 wjqdood 25
     
144. 4 wjqbest 30

相关连接：

作者：SEian.G（苦练七十二变，笑对八十一难）

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

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