catalog损坏情况下的数据库恢复实例

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&gt; connect / as sysdba  _
116
117_ Connected.  _
118
119###  _ SQL&gt;startup nomount  _
120
121_SQL &gt;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=&gt;'sbt_tape',  _
130
131_ ident=&gt;'T1',params=&gt;'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=&gt;'/backup/test/Control01.ctl');  _
138
139_ 8 sys.dbms_backup_restore.restoreBackupPiece(done=&gt;done,  _
140
141_ handle=&gt;'test2oratest<oratest_2:539001979:1>.dbf', params=&gt;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&gt;  
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&gt;alter database mount; 
180
181SQL&gt;select file# from v$backup_datafile where set_stamp= _539001683_ ; 
182
183FILE# 
184
185\---------- 
186
1872 
188
1890 
190
1912 rows selected. 
192
193SQL&gt;
194
195其中,  set_stamp是备份集文件名中的时间戳; 
196
1970表示控制文件; 
198
1992是数据文件号。 
200
201文件号对应的文件名可以在  v$datafile中查到。   
202  
203---  
204  
205知道备份集中包含的数据文件号和文件名,就可以恢复数据文件了: 
206
207_ SQL&gt;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=&gt;'sbt_tape',  _
216
217_ ident=&gt;'T1',params=&gt;'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=oratest,OB2BARLIST=test2oratest)');  _
218
219_ 6 sys.dbms_backup_restore.restoreSetDatafile;  _
220
221_ 7 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=&gt;2,  _
222
223_ toname=&gt;'/backup/test/undotbsldb01.dbf');  _
224
225_ 8 sys.dbms_backup_restore.restoreBackupPiece(done=&gt;done,  _
226
227_ handle=&gt;'test2:test2oratest<oratest_1:539001683:1>.dbf', params=&gt;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&gt; _  
248  
249---  
250  
251重复上述操作,可以恢复所有数据文件。恢复的数据文件的存储目录可以不是原存储目录,因此,甚至可以在数据库  open状态下从备份集中restore数据文件。 
252
2534.恢复archive log文件 
254
255_ SQL&gt;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=&gt;'sbt_tape',ident=&gt;'T1',  _
264
265_ params=&gt;'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=&gt;done,  _
274
275_ handle=&gt;'test2:test2oratestarchivelog<oratest_26:539005316:1>.dbf', params=&gt;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&gt; _  
292  
293---  
294  
295根据第一步中获得的  archive log备份集的文件名,恢复所有archive log. 
296
2975\. Recover数据库 
298
299SQL&gt; 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&gt; alter database open resetlogs; 
338
339Database altered. 
340
341SQL&gt;  
342  
343---  
344  
345现在数据库恢复已经完成了。由于  online redo log也损坏了,数据库仅恢复到最后一个归档的日志文件。 
346
347一个小技巧:在上一步恢复  archive log时,archive log文件可能非常多。如果不知道最早应该恢复到哪一个archive log文件,可以先执行一次本步操作,系统会提示最先需要的archive log文件,然后从最后的一个archive log备份集往前恢复直到该archive log文件被恢复。示例如下: 
348
349SQL&gt; 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&gt;
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>
Published At
Categories with 数据库类
Tagged with
comments powered by Disqus