catalog損壞情況下的數(shù)據(jù)庫(kù)恢復(fù)實(shí)例

2024-07-21 02:06:42

字體：大中小

供稿：網(wǎng)友

catalog損壞情況下的數(shù)據(jù)庫(kù)恢復(fù)實(shí)例
一．環(huán)境描述

1．運(yùn)行環(huán)境：hp小型機(jī)，hpux操作系統(tǒng)，oracle 9.2.0.1，openview data protector備份管理軟件，esl9000帶庫(kù)。

數(shù)據(jù)庫(kù)全備腳本：

run {

allocate channel 'dev_0' type 'sbt_tape'

parms 'env=(ob2bartype=oracle8,ob2appname=oratest,ob2barlist=test2oratest)';

backup incremental level <incr_level> filesperset 1

format 'test2oratest<oratest_%s:%t:%p>.dbf'

database

include current controlfile;

sql 'alter system archive log current';

}

歸檔日志備份腳本：

run {

allocate channel 'dev_0' type 'sbt_tape'

parms 'env=(ob2bartype=oracle8,ob2appname=oratest,

ob2barlist=test2oratestarchivelog)';

backup incremental level <incr_level> filesperset 1

format 'test2oratestarchivelog<oratest_%s:%t:%p>.dbf'

archivelog all delete input;

}

2．故障描述：oracle數(shù)據(jù)庫(kù)（測(cè)試庫(kù)）運(yùn)行在歸檔模式下。正常情況下，備份管理軟件調(diào)用oracle rman進(jìn)行全庫(kù)和歸檔日志備份。備份文件存儲(chǔ)在esl9000磁帶庫(kù)上。由于磁盤陣列故障，造成目標(biāo)數(shù)據(jù)庫(kù)控制文件和數(shù)據(jù)文件全部損壞；同時(shí)，存放catalog的數(shù)據(jù)庫(kù)也被損壞。

由于catalog損壞，rman無法通過catalog找到備份集，restore無法成功。由于沒有控制文件，數(shù)據(jù)庫(kù)僅能啟動(dòng)到nomount狀態(tài)下。

二．背景知識(shí)

在oracle 816 以后的版本中,oracle提供了一個(gè)包:dbms_backup_restore。 dbms_backup_restore 包是由dbmsbkrs.sql 和 prvtbkrs.plb 這兩個(gè)腳本創(chuàng)建的。 catproc.sql 腳本運(yùn)行后會(huì)調(diào)用這兩個(gè)包。所以每個(gè)數(shù)據(jù)庫(kù)都有的這個(gè)包作為oracle服務(wù)器和操作系統(tǒng)之間io操作的接口，由rman直接調(diào)用。我們可以在數(shù)據(jù)庫(kù)nomount 情況下調(diào)用這些包 ,來達(dá)到數(shù)據(jù)庫(kù)恢復(fù)的目的。

三．恢復(fù)步驟

1．查找備份集

備份集可以在data protector的internal database中或日志記錄中找到。具體內(nèi)容（根據(jù)需要做了剪裁）如下：

sbt-25032 (?) 10/08/04 10:42:54 [normal] from: [email protected] "oratest" time: 10/08/04 10:42:54

         starting ob2bar backup: 05 test2:test2oratest<oratest_1:539001683:1>.dbf // oracle8

sbt-25032 (?) 10/08/04 10:46:14 [normal] from: [email protected] "oratest" time: 10/08/04 10:46:14

         completed ob2bar backup: 05 test2:test2oratest<oratest_1:539001683:1>.dbf // oracle8

sbt-25032 (?) 10/08/04 10:46:22 [normal] from: [email protected] "oratest" time: 10/08/04 10:46:22

         starting ob2bar backup: 05 test2:test2oratest<oratest_2:539001979:1>.dbf // oracle8

sbt-25032 (?) 10/08/04 11:02:45 [normal] from: [email protected] "oratest" time: 10/08/04 11:02:45

         completed ob2bar backup: 05 test2:test2oratest<oratest_2:539001979:1>.dbf // oracle8

sbt-28813 (?) 10/08/04 11:34:57 [normal] from: [email protected] "oratest" time: 10/08/04 11:34:57

         starting ob2bar backup: 05 test2:test2oratestarchivelog<oratest_3:539004793:1>.dbf // oracle8

sbt-28813 (?) 10/08/04 11:35:17 [normal] from: [email protected] "oratest" time: 10/08/04 11:35:17

         completed ob2bar backup: 05 test2:test2oratestarchivelog<oratest_3:539004793:1>.dbf // oracle8

。。。

sbt-28813 (?) 10/08/04 11:41:57 [normal] from: [email protected] "oratest" time: 10/08/04 11:41:57

         starting ob2bar backup: 05 test2:test2oratestarchivelog<oratest_26:539005316:1>.dbf // oracle8

sbt-28813 (?) 10/08/04 11:42:08 [normal] from: [email protected] "oratest" time: 10/08/04 11:42:08

         completed ob2bar backup: 05 test2:test2oratestarchivelog<oratest_26:539005316:1>.dbf // oracle8

sbt-28813 (?) 10/08/04 11:42:13 [normal] from: [email protected] "oratest" time: 10/08/04 11:42:13

         starting ob2bar backup: 05 test2:test2oratestarchivelog<oratest_27:539005332:1>.dbf // oracle8

sbt-28813 (?) 10/08/04 11:42:16 [normal] from: [email protected] "oratest" time: 10/08/04 11:42:16

         completed ob2bar backup: 05 test2:test2oratestarchivelog<oratest_27:539005332:1>.dbf // oracle8

從上述內(nèi)容可以看到存儲(chǔ)在帶庫(kù)上的全備的備份集文件為：

test2:test2oratest<oratest_1:539001683:1>.dbf

test2:test2oratest<oratest_2:539001979:1>.dbf

archivelog的備份集文件為：

          test2:test2oratestarchivelog<oratest_3:539004793:1>.dbf

。。。

          test2:test2oratestarchivelog<oratest_26:539005316:1>.dbf

test2:test2oratestarchivelog<oratest_27:539005332:1>.dbf

2.恢復(fù)控制文件

test2:/backup/test$sqlplus /nolog

sql*plus: release 9.2.0.1.0 - production on sat oct 9 14:30:54 2004

copyright (c) 1982, 2002, oracle corporation. all rights reserved.

sql> connect / as sysdba

connected.
sql>startup nomount
sql>declare

2 devtype varchar2(256);

3 done boolean;

4 begin

5 devtype:=sys.dbms_backup_restore.deviceallocate(type=>'sbt_tape',

ident=>'t1',params=>'env=(ob2bartype=oracle8,ob2appname=oratest,ob2barlist=test2oratest)');

6 sys.dbms_backup_restore.restoresetdatafile;

7 sys.dbms_backup_restore.restorecontrolfileto(

cfname=>'/backup/test/control01.ctl');

8 sys.dbms_backup_restore.restorebackuppiece(done=>done,

handle=>'test2oratest<oratest_2:539001979:1>.dbf', params=>null);

9 sys.dbms_backup_restore.devicedeallocate;

10 end;

11 /

[normal] from: [email protected] "oratest" time: 10/09/04 14:23:28

        starting ob2bar restore: 05 test2:test2oratest<oratest_2:539001979:1>.dbf // oracle8

[normal] from: [email protected] "oratest" time: 10/09/04 14:23:32

        completed ob2bar restore: 05 test2:test2oratest<oratest_2:539001979:1>.dbf // oracle8

sql>

由于rman做全庫(kù)備份的時(shí)候，最后備份控制文件，應(yīng)此可以從最后一個(gè)全備的備份集文件中恢復(fù)控制文件?；謴?fù)控制文件時(shí)，需將數(shù)據(jù)庫(kù)啟動(dòng)到nomount狀態(tài)?；謴?fù)的控制文件應(yīng)與init文件中定義的控制文件的路徑、文件名和數(shù)量相一致，否則無法把數(shù)據(jù)庫(kù)啟動(dòng)到mount狀態(tài)。

下面介紹程序包的內(nèi)容：

第五行分配一個(gè)device channel ，"sbt_tape"說明是從磁帶上恢復(fù)。params參數(shù)與data protector中rman備份腳本中的參數(shù)一致。

第六行初始化恢復(fù)過程，準(zhǔn)備進(jìn)行控制文件或數(shù)據(jù)文件恢復(fù)。

第七行指出待恢復(fù)文件和恢復(fù)文件的存儲(chǔ)位置。本處說明是恢復(fù)控制文件及存放存放控制文件的路徑、文件名。

第八行說明從哪個(gè)備份片中恢復(fù)

第九行釋放設(shè)備通道

3.恢復(fù)數(shù)據(jù)文件

首先需要知道每個(gè)備份集中包含哪些數(shù)據(jù)文件：

sql>alter database mount;

sql>select file# from v$backup_datafile where set_stamp=539001683;

     file#

----------

         2

         0

2 rows selected.

sql>

其中，set_stamp是備份集文件名中的時(shí)間戳；

0表示控制文件；

2是數(shù)據(jù)文件號(hào)。

文件號(hào)對(duì)應(yīng)的文件名可以在v$datafile中查到。

知道備份集中包含的數(shù)據(jù)文件號(hào)和文件名，就可以恢復(fù)數(shù)據(jù)文件了：

sql>declare

2 devtype varchar2(256);

3 done boolean;

4 begin

5 devtype:=sys.dbms_backup_restore.deviceallocate(type=>'sbt_tape',

ident=>'t1',params=>'env=(ob2bartype=oracle8,ob2appname=oratest,ob2barlist=test2oratest)');

6 sys.dbms_backup_restore.restoresetdatafile;

7 sys.dbms_backup_restore.restoredatafileto(dfnumber=>2,

toname=>'/backup/test/undotbsldb01.dbf');

8 sys.dbms_backup_restore.restorebackuppiece(done=>done,

handle=>'test2:test2oratest<oratest_1:539001683:1>.dbf', params=>null);

9 sys.dbms_backup_restore.devicedeallocate;

10 end;

11 /

[normal] from: [email protected] "oratest" time: 10/09/04 14:20:10

starting ob2bar restore: 05test2:test2oratest<oratest_1:539001683:1>.dbf // oracle8

[normal] from: [email protected] "oratest" time: 10/09/04 14:20:24

completed ob2bar restore: 05 test2:test2oratest<oratest_2:539001683:1>.dbf // oracle8

pl/sql procedure successfully completed.

sql>

重復(fù)上述操作，可以恢復(fù)所有數(shù)據(jù)文件。恢復(fù)的數(shù)據(jù)文件的存儲(chǔ)目錄可以不是原存儲(chǔ)目錄，因此，甚至可以在數(shù)據(jù)庫(kù)open狀態(tài)下從備份集中restore數(shù)據(jù)文件。

4.恢復(fù)archive log文件

sql>declare

2 devtype varchar2(256);

3 done boolean;

4 begin

5 devtype:=sys.dbms_backup_restore.deviceallocate(type=>'sbt_tape',ident=>'t1',

params=>'env=(ob2bartype=oracle8,ob2appname=oratest,

ob2barlist=test2oratestarchivelog)');

6 sys.dbms_backup_restore.restoresetarchivedlog;

7 sys.dbms_backup_restore.restorearchivedlogrange;

8 sys.dbms_backup_restore.restorebackuppiece(done=>done,

handle=>'test2:test2oratestarchivelog<oratest_26:539005316:1>.dbf', params=>null);

9 sys.dbms_backup_restore.devicedeallocate;

10 end;

11 /

[normal] from: [email protected] "oratest" time: 10/09/04 14:40:13

starting ob2bar restore: 05 test2:test2oratestarchivelog<oratest_27:539005316:1>.dbf // oracle8

[normal] from: [email protected] "oratest" time: 10/09/04 14:40:13

completed ob2bar restore: 05 test2:test2oratestarchivelog<oratest_27:539005316:1>.dbf // oracle8

sql>

根據(jù)第一步中獲得的archive log備份集的文件名，恢復(fù)所有archive log.

5. recover數(shù)據(jù)庫(kù)

sql> recover database until cancel using backup controlfile;

ora-00279: change 54285 generated at 10/08/2004 10:41:24 needed for thread 1

ora-00289: suggestion : /backup/test/archive/1_24.dbf

ora-00280: change 54285 for thread 1 is in sequence #24

specify log: {<ret>=suggested | filename | auto | cancel}

ora-00279: change 55331 generated at 10/08/2004 11:02:48 needed for thread 1

ora-00289: suggestion : /backup/test/archive/1_25.dbf

ora-00280: change 55331 for thread 1 is in sequence #25

ora-00278: log file '/backup/test/archive/1_24.dbf' no longer needed for this recovery

specify log: {<ret>=suggested | filename | auto | cancel}

ora-00279: change 56789 generated at 10/08/2004 11:33:12 needed for thread 1

ora-00289: suggestion : /backup/test/archive/1_26.dbf

ora-00280: change 56789 for thread 1 is in sequence #26

ora-00278: log file '/backup/test/archive/1_25.dbf' no longer needed for this recovery

specify log: {<ret>=suggested | filename | auto | cancel}

ora-00308: cannot open archived log '/backup/test/archive/1_26.dbf'

ora-27037: unable to obtain file status

hp-ux error: 2: no such file or directory

additional information: 3

sql> alter database open resetlogs;

database altered.

sql>

現(xiàn)在數(shù)據(jù)庫(kù)恢復(fù)已經(jīng)完成了。由于online redo log也損壞了，數(shù)據(jù)庫(kù)僅恢復(fù)到最后一個(gè)歸檔的日志文件。

一個(gè)小技巧：在上一步恢復(fù)archive log時(shí)，archive log文件可能非常多。如果不知道最早應(yīng)該恢復(fù)到哪一個(gè)archive log文件,可以先執(zhí)行一次本步操作，系統(tǒng)會(huì)提示最先需要的archive log文件，然后從最后的一個(gè)archive log備份集往前恢復(fù)直到該archive log文件被恢復(fù)。示例如下：

sql> recover database until cancel using backup controlfile;

ora-00279: change 54285 generated at 10/08/2004 10:41:24 needed for thread 1

ora-00289: suggestion : /backup/test/archive/1_24.dbf

ora-00280: change 54285 for thread 1 is in sequence #24

specify log: {<ret>=suggested | filename | auto | cancel}

ora-00308: cannot open archived log '/backup/test/archive/1_24.dbf'

ora-27037: unable to obtain file status

hp-ux error: 2: no such file or directory

additional information: 3

ora-01547: warning: recover succeeded but open resetlogs would get error below

ora-01152: file 1 was not restored from a sufficiently old backup

ora-01110: data file 1: '/backup/test/oradata/system01.dbf'

sql>

顯然，第一個(gè)需要的archive log文件是/backup/test/archive/1_24.dbf

五．小結(jié)

1．只要保存了完整的全庫(kù)備份和完整的歸檔日志備份，即使控制文件和恢復(fù)目錄全部損壞，數(shù)據(jù)庫(kù)還是可以恢復(fù)的。

2．根據(jù)上面一條可以看出，無論備份時(shí)是否采用了恢復(fù)目錄，恢復(fù)目錄在數(shù)據(jù)庫(kù)恢復(fù)時(shí)，并不是必須的。

3． rman做的備份可以不用rman恢復(fù)。

4． hp data protector的備份腳本在做全庫(kù)備份時(shí)，是一個(gè)一個(gè)備份數(shù)據(jù)文件和日志文件并形成單獨(dú)的備份集。數(shù)據(jù)庫(kù)全備期間的日志文件必須保存完整。

參考資料：
dba工作備忘錄之三:rman備份,沒用catalog ,但控制文件丟失,怎么解決? fenng

上一篇：SQL日志文件長(zhǎng)度過大的處理方法

下一篇：關(guān)于字符集的測(cè)試報(bào)告