catalog 损坏情况下的数据库恢复实例
一.环境描述
1 .运行环境: HP 小型机, HPUX 操作系统, Oracle 9.2.0.1 , OpenView Data Protector 备份管理软件, ESL9000 带库。
数据库全备脚本:
run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=oratest,OB2BARLIST=test2oratest)';
backup incremental level
1<incr_level> filesperset 1
2
3format 'test2oratest<oratest_%s:%t:%p>.dbf'
4
5database
6
7include current controlfile;
8
9sql 'alter system archive log current';
10
11}
12
13---
14
15归档日志备份脚本:
16
17run {
18
19allocate channel 'dev_0' type 'sbt_tape'
20
21parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=oratest,
22
23OB2BARLIST=test2oratestarchivelog)';
24
25backup incremental level <incr_level> filesperset 1
26
27format 'test2oratestarchivelog<oratest_%s:%t:%p>.dbf'
28
29archivelog all delete input;
30
31}
32
33---
34
352 .故障描述: Oracle 数据库(测试库)运行在归档模式下。正常情况下,备份管理软件调用 Oracle RMAN 进行全库和归档日志备份。备份文件存储在 ESL9000 磁带库上。由于磁盘阵列故障,造成目标数据库控制文件和数据文件全部损坏;同时,存放 catalog 的数据库也被损坏。
36
37由于 catalog 损坏, RMAN 无法通过 catalog 找到备份集, restore 无法成功。由于没有控制文件,数据库仅能启动到 nomount 状态下。
38
39二.背景知识
40
41在 Oracle 816 以后的版本中 ,Oracle 提供了一个包 :DBMS_BACKUP_RESTORE 。 DBMS_BACKUP_RESTORE 包是由 dbmsbkrs.sql 和 prvtbkrs.plb 这两个脚本创建的。 catproc.sql 脚本运行后会调用这两个包。所以每个数据库都有的这个包作为 Oracle 服务器和操作系统之间 IO 操作的接口,由 RMAN 直接调用。我们可以在数据库 nomount 情况下调用这些包 , 来达到数据库恢复的目的。
42
43三.恢复步骤
44
451 .查找备份集
46
47备份集可以在 Data Protector 的 internal database 中或日志记录中找到。具体内容(根据需要做了剪裁)如下:
48
49_ SBT-25032 (?) 10/08/04 10:42:54 [Normal] From: OB2BAR@test2 "oratest" Time: 10/08/04 10:42:54 _
50
51_ Starting OB2BAR Backup: 05 test2:test2oratest<oratest_1:539001683:1>.dbf // Oracle8 _
52
53_ SBT-25032 (?) 10/08/04 10:46:14 [Normal] From: OB2BAR@test2 "oratest" Time: 10/08/04 10:46:14 _
54
55_ Completed OB2BAR Backup: 05 test2:test2oratest<oratest_1:539001683:1>.dbf // Oracle8 _
56
57_ SBT-25032 (?) 10/08/04 10:46:22 [Normal] From: OB2BAR@test2 "oratest" Time: 10/08/04 10:46:22 _
58
59_ Starting OB2BAR Backup: 05 test2:test2oratest<oratest_2:539001979:1>.dbf // Oracle8 _
60
61_ SBT-25032 (?) 10/08/04 11:02:45 [Normal] From: OB2BAR@test2 "oratest" Time: 10/08/04 11:02:45 _
62
63_ Completed OB2BAR Backup: 05 test2:test2oratest<oratest_2:539001979:1>.dbf // Oracle8 _
64
65_ SBT-28813 (?) 10/08/04 11:34:57 [Normal] From: OB2BAR@test2 "oratest" Time: 10/08/04 11:34:57 _
66
67_ Starting OB2BAR Backup: 05 test2:test2oratestarchivelog<oratest_3:539004793:1>.dbf // Oracle8 _
68
69_ SBT-28813 (?) 10/08/04 11:35:17 [Normal] From: OB2BAR@test2 "oratest" Time: 10/08/04 11:35:17 _
70
71_ Completed OB2BAR Backup: 05 test2:test2oratestarchivelog<oratest_3:539004793:1>.dbf // Oracle8 _
72
73_ 。。。 _ _ _
74
75_ SBT-28813 (?) 10/08/04 11:41:57 [Normal] From: OB2BAR@test2 "oratest" Time: 10/08/04 11:41:57 _
76
77_ Starting OB2BAR Backup: 05 test2:test2oratestarchivelog<oratest_26:539005316:1>.dbf // Oracle8 _
78
79_ SBT-28813 (?) 10/08/04 11:42:08 [Normal] From: OB2BAR@test2 "oratest" Time: 10/08/04 11:42:08 _
80
81_ Completed OB2BAR Backup: 05 test2:test2oratestarchivelog<oratest_26:539005316:1>.dbf // Oracle8 _
82
83_ SBT-28813 (?) 10/08/04 11:42:13 [Normal] From: OB2BAR@test2 "oratest" Time: 10/08/04 11:42:13 _
84
85_ Starting OB2BAR Backup: 05 test2:test2oratestarchivelog<oratest_27:539005332:1>.dbf // Oracle8 _
86
87_ SBT-28813 (?) 10/08/04 11:42:16 [Normal] From: OB2BAR@test2 "oratest" Time: 10/08/04 11:42:16 _
88
89_ Completed OB2BAR Backup: 05 test2:test2oratestarchivelog<oratest_27:539005332:1>.dbf // Oracle8 _
90
91从上述内容可以看到存储在带库上的全备的备份集文件为:
92
93test2:test2oratest<oratest_1:539001683:1>.dbf
94
95test2:test2oratest<oratest_2:539001979:1>.dbf
96
97Archivelog 的备份集文件为:
98
99test2:test2oratestarchivelog<oratest_3:539004793:1>.dbf
100
101。。。
102
103test2:test2oratestarchivelog<oratest_26:539005316:1>.dbf
104
105test2:test2oratestarchivelog<oratest_27:539005332:1>.dbf
106
1072 .恢复控制文件
108
109_ test2:/backup/test$sqlplus /nolog _
110
111_ SQL*Plus: Release 9.2.0.1.0 - Production on Sat Oct 9 14:30:54 2004 _
112
113_ Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. _
114
115_ SQL> connect / as sysdba _
116
117_ Connected. _
118
119### _ SQL>startup nomount _
120
121_SQL >DECLARE _
122
123_ 2 devtype varchar2(256); _
124
125_ 3 done boolean; _
126
127_ 4 BEGIN _
128
129_ 5 devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'sbt_tape', _
130
131_ ident=>'T1',params=>'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=oratest,OB2BARLIST=test2oratest)'); _
132
133_ 6 sys.dbms_backup_restore.restoreSetDatafile; _
134
135_ 7 sys.dbms_backup_restore.restoreControlfileTo( _
136
137_ cfname=>'/backup/test/Control01.ctl'); _
138
139_ 8 sys.dbms_backup_restore.restoreBackupPiece(done=>done, _
140
141_ handle=>'test2oratest<oratest_2:539001979:1>.dbf', params=>null); _
142
143_ 9 sys.dbms_backup_restore.deviceDeallocate; _
144
145_ 10 END; _
146
147_ 11 / _
148
149[Normal] From: OB2BAR@test2 "oratest" Time: 10/09/04 14:23:28
150
151Starting OB2BAR Restore: 05 test2:test2oratest<oratest_2:539001979:1>.dbf // Oracle8
152
153[Normal] From: OB2BAR@test2 "oratest" Time: 10/09/04 14:23:32
154
155Completed OB2BAR Restore: 05 test2:test2oratest<oratest_2:539001979:1>.dbf // Oracle8
156
157SQL>
158
159---
160
161由于 RMAN做全库备份的时候,最后备份控制文件,应此可以从最后一个全备的备份集文件中恢复控制文件。恢复控制文件时,需将数据库启动到nomount状态。恢复的控制文件应与init文件中定义的控制文件的路径、文件名和数量相一致,否则无法把数据库启动到mount状态。
162
163下面介绍程序包的内容:
164
165第五行 分配一个 device channel ,"sbt_tape"说明是从磁带上恢复。Params参数与Data Protector中RMAN备份脚本中的参数一致。
166
167第六行 初始化恢复过程,准备进行控制文件或数据文件恢复。
168
169第七行 指出待恢复文件和恢复文件的存储位置。本处说明是恢复控制文件及存放存放控制文件的路径、文件名。
170
171第八行 说明从哪个备份片中恢复
172
173第九行 释放设备通道
174
1753.恢复数据文件
176
177首先需要知道每个备份集中包含哪些数据文件:
178
179SQL>alter database mount;
180
181SQL>select file# from v$backup_datafile where set_stamp= _539001683_ ;
182
183FILE#
184
185\----------
186
1872
188
1890
190
1912 rows selected.
192
193SQL>
194
195其中, set_stamp是备份集文件名中的时间戳;
196
1970表示控制文件;
198
1992是数据文件号。
200
201文件号对应的文件名可以在 v$datafile中查到。
202
203---
204
205知道备份集中包含的数据文件号和文件名,就可以恢复数据文件了:
206
207_ SQL>DECLARE _
208
209_ 2 devtype varchar2(256); _
210
211_ 3 done boolean; _
212
213_ 4 BEGIN _
214
215_ 5 devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'sbt_tape', _
216
217_ ident=>'T1',params=>'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=oratest,OB2BARLIST=test2oratest)'); _
218
219_ 6 sys.dbms_backup_restore.restoreSetDatafile; _
220
221_ 7 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>2, _
222
223_ toname=>'/backup/test/undotbsldb01.dbf'); _
224
225_ 8 sys.dbms_backup_restore.restoreBackupPiece(done=>done, _
226
227_ handle=>'test2:test2oratest<oratest_1:539001683:1>.dbf', params=>null); _
228
229_ 9 sys.dbms_backup_restore.deviceDeallocate; _
230
231_ 10 END; _
232
233_ 11 / _
234
235_ [Normal] From: OB2BAR@test2 "oratest" Time: 10/09/04 14:20:10 _
236
237_ Starting OB2BAR Restore: 05test2:test2oratest<oratest_1:539001683:1>.dbf // Oracle8 _
238
239_ [Normal] From: OB2BAR@test2 "oratest" Time: 10/09/04 14:20:24 _
240
241_ Completed OB2BAR Restore: 05 test2:test2oratest<oratest_2:539001683:1>.dbf // Oracle8 _
242
243_ _
244
245_ PL/SQL procedure successfully completed. _
246
247_ SQL> _
248
249---
250
251重复上述操作,可以恢复所有数据文件。恢复的数据文件的存储目录可以不是原存储目录,因此,甚至可以在数据库 open状态下从备份集中restore数据文件。
252
2534.恢复archive log文件
254
255_ SQL>DECLARE _
256
257_ 2 devtype varchar2(256); _
258
259_ 3 done boolean; _
260
261_ 4 BEGIN _
262
263_ 5 devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'sbt_tape',ident=>'T1', _
264
265_ params=>'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=oratest, _
266
267_ OB2BARLIST=test2oratestarchivelog)'); _
268
269_ 6 sys.dbms_backup_restore.restoreSetArchivedLog; _
270
271_ 7 sys.dbms_backup_restore.restoreArchivedLogRange; _
272
273_ 8 sys.dbms_backup_restore.restoreBackupPiece(done=>done, _
274
275_ handle=>'test2:test2oratestarchivelog<oratest_26:539005316:1>.dbf', params=>null); _
276
277_ 9 sys.dbms_backup_restore.deviceDeallocate; _
278
279_ 10 END; _
280
281_ 11 / _
282
283[Normal] From: OB2BAR@test2 "oratest" Time: 10/09/04 14:40:13
284
285Starting OB2BAR Restore: 05 test2:test2oratestarchivelog<oratest_27:539005316:1>.dbf // Oracle8
286
287[Normal] From: OB2BAR@test2 "oratest" Time: 10/09/04 14:40:13
288
289Completed OB2BAR Restore: 05 test2:test2oratestarchivelog<oratest_27:539005316:1>.dbf // Oracle8
290
291_ SQL> _
292
293---
294
295根据第一步中获得的 archive log备份集的文件名,恢复所有archive log.
296
2975\. Recover数据库
298
299SQL> recover database until cancel using backup controlfile;
300
301ORA-00279: change 54285 generated at 10/08/2004 10:41:24 needed for thread 1
302
303ORA-00289: suggestion : /backup/test/archive/1_24.dbf
304
305ORA-00280: change 54285 for thread 1 is in sequence #24
306
307Specify log: {<ret>=suggested | filename | AUTO | CANCEL}
308
309ORA-00279: change 55331 generated at 10/08/2004 11:02:48 needed for thread 1
310
311ORA-00289: suggestion : /backup/test/archive/1_25.dbf
312
313ORA-00280: change 55331 for thread 1 is in sequence #25
314
315ORA-00278: log file '/backup/test/archive/1_24.dbf' no longer needed for this recovery
316
317Specify log: {<ret>=suggested | filename | AUTO | CANCEL}
318
319ORA-00279: change 56789 generated at 10/08/2004 11:33:12 needed for thread 1
320
321ORA-00289: suggestion : /backup/test/archive/1_26.dbf
322
323ORA-00280: change 56789 for thread 1 is in sequence #26
324
325ORA-00278: log file '/backup/test/archive/1_25.dbf' no longer needed for this recovery
326
327Specify log: {<ret>=suggested | filename | AUTO | CANCEL}
328
329ORA-00308: cannot open archived log '/backup/test/archive/1_26.dbf'
330
331ORA-27037: unable to obtain file status
332
333HP-UX Error: 2: No such file or directory
334
335Additional information: 3
336
337SQL> alter database open resetlogs;
338
339Database altered.
340
341SQL>
342
343---
344
345现在数据库恢复已经完成了。由于 online redo log也损坏了,数据库仅恢复到最后一个归档的日志文件。
346
347一个小技巧:在上一步恢复 archive log时,archive log文件可能非常多。如果不知道最早应该恢复到哪一个archive log文件,可以先执行一次本步操作,系统会提示最先需要的archive log文件,然后从最后的一个archive log备份集往前恢复直到该archive log文件被恢复。示例如下:
348
349SQL> recover database until cancel using backup controlfile;
350
351ORA-00279: change 54285 generated at 10/08/2004 10:41:24 needed for thread 1
352
353ORA-00289: suggestion : **/backup/test/archive/1_24.dbf**
354
355ORA-00280: change 54285 for thread 1 is in sequence #24
356
357Specify log: {<ret>=suggested | filename | AUTO | CANCEL}
358
359ORA-00308: cannot open archived log '/backup/test/archive/1_24.dbf'
360
361ORA-27037: unable to obtain file status
362
363HP-UX Error: 2: No such file or directory
364
365Additional information: 3
366
367ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
368
369ORA-01152: file 1 was not restored from a sufficiently old backup
370
371ORA-01110: data file 1: '/backup/test/oradata/system01.dbf'
372
373SQL>
374
375显然,第一个需要的 archive log 文件是 /backup/test/archive/1_24.dbf
376
377---
378
379五.小结
380
3811. 只要保存了完整的全库备份和完整的归档日志备份,即使控制文件和恢复目录全部损坏,数据库还是可以恢复的。
382
3832. 根据上面一条可以看出,无论备份时是否采用了恢复目录,恢复目录在数据库恢复时,并不是必须的。
384
3853. RMAN做的备份可以不用RMAN恢复。
386
3874. HP Data Protector的备份脚本在做全库备份时,是一个一个备份数据文件和日志文件并形成单独的备份集。数据库全备期间的日志文件必须保存完整。
388
389** 参考资料: **
390DBA 工作备忘录之三 :rman 备份 , 没用 catalog , 但控制文件丢失 , 怎么解决 ? Fenng</ret></ret></ret></ret></oratest_27:539005316:1></oratest_27:539005316:1></oratest_26:539005316:1></oratest_2:539001683:1></oratest_1:539001683:1></oratest_1:539001683:1></oratest_2:539001979:1></oratest_2:539001979:1></oratest_2:539001979:1></oratest_27:539005332:1></oratest_26:539005316:1></oratest_3:539004793:1></oratest_2:539001979:1></oratest_1:539001683:1></oratest_27:539005332:1></oratest_27:539005332:1></oratest_26:539005316:1></oratest_26:539005316:1></oratest_3:539004793:1></oratest_3:539004793:1></oratest_2:539001979:1></oratest_2:539001979:1></oratest_1:539001683:1></oratest_1:539001683:1></oratest_%s:%t:%p></incr_level></oratest_%s:%t:%p></incr_level>