使用dbms_backup_restore進行資料庫恢復

今天看到ITPUB上有個網友提問，使用dbms_backup_restore包進行恢復，以前沒有測試過，今天測試一下。我的測試環境是10204

想了一下，可能會用到這種恢復的情況，就是資料檔案部分丟失，控制檔案全部丟失，且備份只有資料檔案的全備、全部歸檔日誌和spfile，這時可以通過使用dbms_backup_restore來恢復資料檔案，然後通過指令碼重建controlfile，通過這種方式可以恢復資料庫。

因此如果控制檔案丟失，還原起來就比較麻煩，需要用dbms_backup_restore來進行恢復，其實想想，dbms_backup_restore其實也沒那麼常用了。因為：

1、控制檔案如果有自動備份，那麼可以從自動備份還原。

2、如果有單獨備份控制檔案，那麼可以從這個單獨備份的backupset處還原。

3、如果之前有做資料庫全備，全備中就包含了控制檔案和spfile的備份，可以從全備中還原。

4、從上面的四種方法都無法還原控制檔案，那麼只能從備份集restore資料檔案以及arch，然後重建控制檔案，再resetlogs開啟資料庫。

環境準備

對資料庫進行全備

RMAN> backup database format '/ora10g/backup/database_full_%U.dbf';

Starting backup at 04-DEC-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=254 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00006 name=/ora10g/oradata/ora10g/system02.dbf

input datafile fno=00007 name=/ora10g/oradata/ora10g/users02.dbf

input datafile fno=00001 name=/ora10g/oradata/ora10g/system01.dbf

input datafile fno=00003 name=/ora10g/oradata/ora10g/sysaux01.dbf

input datafile fno=00005 name=/ora10g/oradata/ora10g/example01.dbf

input datafile fno=00002 name=/ora10g/oradata/ora10g/undotbs01.dbf

input datafile fno=00004 name=/ora10g/oradata/ora10g/users01.dbf

channel ORA_DISK_1: starting piece 1 at 04-DEC-12

channel ORA_DISK_1: finished piece 1 at 04-DEC-12

piece handle=/ora10g/backup/database_full_0qns0pih_1_1.dbf tag=TAG20121204T102417 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25

Finished backup at 04-DEC-12

Starting Control File and SPFILE Autobackup at 04-DEC-12

piece handle=/ora10g/backup/c-4115709917-20121204-00 comment=NONE

Finished Control File and SPFILE Autobackup at 04-DEC-12

l 準備dbms_backup_restore包指令碼，以下指令碼可以從網上獲取，要根據自己的需求來修改。

--restore controlfile

DECLARE

devtype varchar2(256);

done boolean;

BEGIN

devtype := dbms_backup_restore.DeviceAllocate(type => '',ident => 't1');

dbms_backup_restore.RestoresetdataFile;

dbms_backup_restore.RestoreControlFileto('/ora10g/oradata/ora10g/control01.ctl');

dbms_backup_restore.RestoreBackupPiece('/ora10g/backup/c-4115709917-20121204-00',done => done);

dbms_backup_restore.RestoresetdataFile;

dbms_backup_restore.RestoreControlFileto('/ora10g/oradata/ora10g/control02.ctl');

dbms_backup_restore.RestoreBackupPiece('/ora10g/backup/c-4115709917-20121204-00',done => done);

dbms_backup_restore.RestoresetdataFile;

dbms_backup_restore.RestoreControlFileto('/ora10g/oradata/ora10g/control03.ctl');

dbms_backup_restore.RestoreBackupPiece('/ora10g/backup/c-4115709917-20121204-00',done => done);

dbms_backup_restore.DeviceDeallocate;

END;

/

紅色部分為controlfile 的備份資訊。

--restore datafile

DECLARE

devtype varchar2(256);

done boolean;

BEGIN

devtype := dbms_backup_restore.DeviceAllocate (type => '',ident => 't1');

dbms_backup_restore.RestoreSetDatafile;

dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => '/ora10g/oradata/ora10g/system01.dbf');

dbms_backup_restore.RestoreDatafileTo(dfnumber => 2,toname => '/ora10g/oradata/ora10g/undotbs01.dbf');

dbms_backup_restore.RestoreDatafileTo(dfnumber => 3,toname => '/ora10g/oradata/ora10g/sysaux01.dbf');

dbms_backup_restore.RestoreDatafileTo(dfnumber => 4,toname => '/ora10g/oradata/ora10g/users01.dbf');

dbms_backup_restore.RestoreDatafileTo(dfnumber => 5,toname => '/ora10g/oradata/ora10g/example01.dbf');

dbms_backup_restore.RestoreDatafileTo(dfnumber => 6,toname => '/ora10g/oradata/ora10g/system02.dbf');

dbms_backup_restore.RestoreDatafileTo(dfnumber => 7,toname => '/ora10g/oradata/ora10g/users02.dbf');

dbms_backup_restore.RestoreBackupPiece(done => done,handle => ' /ora10g/backup/database_full_0qns0pih_1_1.dbf', params => null);

dbms_backup_restore.DeviceDeallocate;

END;

/

紅色檔案部分為資料檔案的備份資訊

**該指令碼在本實驗中沒有使用，沒有修改。

--restore archived redolog

DECLARE

devtype varchar2(256);

done boolean;

BEGIN

devtype := dbms_backup_restore.DeviceAllocate (type => '',ident => 'FUN');

dbms_backup_restore.RestoreSetArchivedLog(destination=>'D:\ORACLE_BASE\achive\');

dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>1);

dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>2);

dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>3);

dbms_backup_restore.RestoreBackupPiece(done => done,handle => 'D:\ORACLE_BASE\RMAN_BACKUP\MYDB_LOG_BCK0DH1JGND_1_1', params => null);

dbms_backup_restore.DeviceDeallocate;

END;

/

*******************************************************************************

刪除控制檔案和部分資料檔案

$ ll

total 6716518

-rw-r----- 1 ora10g dba 7061504 Dec 4 10:41 control01.ctl

-rw-r----- 1 ora10g dba 7061504 Dec 4 10:41 control02.ctl

-rw-r----- 1 ora10g dba 7061504 Dec 4 10:41 control03.ctl

-rw-r----- 1 ora10g dba 104865792 Nov 23 16:38 example01.dbf

-rw-r----- 1 ora10g dba 52429824 Dec 3 03:05 redo01.log

-rw-r----- 1 ora10g dba 52429824 Dec 3 22:00 redo02.log

-rw-r----- 1 ora10g dba 52429824 Dec 4 10:41 redo03.log

-rw-r----- 1 ora10g dba 387981312 Dec 4 10:41 sysaux01.dbf

-rw-r----- 1 ora10g dba 524296192 Dec 4 10:41 system01.dbf

-rw-r----- 1 ora10g dba 1073750016 Dec 4 10:24 system02.dbf

-rw-r----- 1 ora10g dba 20979712 Dec 3 22:00 temp01.dbf

-rw-r----- 1 ora10g dba 57679872 Dec 4 10:41 undotbs01.dbf

-rw-r----- 1 ora10g dba 17047552 Dec 4 10:24 users01.dbf

-rw-r----- 1 ora10g dba 1073750016 Dec 4 10:24 users02.dbf

$ mv control01.ctl control01.ctl.bak

$ mv control02.ctl control02.ctl.bak

$ mv control03.ctl control03.ctl.bak

$ mv users01.dbf users01.dbf.bak

$ mv users02.dbf users02.dbf.bak

$ mv system01.dbf system01.dbf.bak

$ ll

total 6716518

-rw-r----- 1 ora10g dba 7061504 Dec 4 10:46 control01.ctl.bak

-rw-r----- 1 ora10g dba 7061504 Dec 4 10:46 control02.ctl.bak

-rw-r----- 1 ora10g dba 7061504 Dec 4 10:46 control03.ctl.bak

-rw-r----- 1 ora10g dba 104865792 Nov 23 16:38 example01.dbf

-rw-r----- 1 ora10g dba 52429824 Dec 3 03:05 redo01.log

-rw-r----- 1 ora10g dba 52429824 Dec 3 22:00 redo02.log

-rw-r----- 1 ora10g dba 52429824 Dec 4 10:45 redo03.log

-rw-r----- 1 ora10g dba 387981312 Dec 4 10:42 sysaux01.dbf

-rw-r----- 1 ora10g dba 524296192 Dec 4 10:43 system01.dbf.bak

-rw-r----- 1 ora10g dba 1073750016 Dec 4 10:24 system02.dbf

-rw-r----- 1 ora10g dba 20979712 Dec 3 22:00 temp01.dbf

-rw-r----- 1 ora10g dba 57679872 Dec 4 10:45 undotbs01.dbf

-rw-r----- 1 ora10g dba 17047552 Dec 4 10:24 users01.dbf.bak

-rw-r----- 1 ora10g dba 1073750016 Dec 4 10:24 users02.dbf.bak

使用dbms_backup_restor包恢復資料庫

將資料庫啟動到nomount狀態

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size 2168928 bytes

Variable Size 496887712 bytes

Database Buffers 1644167168 bytes

Redo Buffers 4259840 bytes

SQL>

執行恢復controlfile 指令碼，這個恢復也是需要有controlfile備份的！，如果沒有controlfile的備份，這步可以跳過

SQL> DECLARE

2 devtype varchar2(256);

3 done boolean;

4 BEGIN

5 devtype := dbms_backup_restore.DeviceAllocate(type => '',ident => 't1');

6 dbms_backup_restore.RestoresetdataFile;

7 dbms_backup_restore.RestoreControlFileto('/ora10g/oradata/ora10g/control01.ctl');

8 dbms_backup_restore.RestoreBackupPiece('/ora10g/backup/c-4115709917-20121204-00',done => done);

9 dbms_backup_restore.RestoresetdataFile;

10 dbms_backup_restore.RestoreControlFileto('/ora10g/oradata/ora10g/control02.ctl');

11 dbms_backup_restore.RestoreBackupPiece('/ora10g/backup/c-4115709917-20121204-00',done => done);

12 dbms_backup_restore.RestoresetdataFile;

13 dbms_backup_restore.RestoreControlFileto('/ora10g/oradata/ora10g/control03.ctl');

14 dbms_backup_restore.RestoreBackupPiece('/ora10g/backup/c-4115709917-20121204-00',done => done);

15 dbms_backup_restore.DeviceDeallocate;

16 END;

17 /

PL/SQL procedure successfully completed.

執行成功後，將資料庫啟動到mount狀態，並執行恢復資料檔案指令碼

SQL> alter database mount;

Database altered.

SQL> DECLARE

2 devtype varchar2(256);

3 done boolean;

4 BEGIN

5 devtype := dbms_backup_restore.DeviceAllocate (type => '',ident => 't1');

6 dbms_backup_restore.RestoreSetDatafile;

7 dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => '/ora10g/oradata/ora10g/system01.dbf');

8 dbms_backup_restore.RestoreDatafileTo(dfnumber => 2,toname => '/ora10g/oradata/ora10g/undotbs01.dbf');

9 dbms_backup_restore.RestoreDatafileTo(dfnumber => 3,toname => '/ora10g/oradata/ora10g/sysaux01.dbf');

10 dbms_backup_restore.RestoreDatafileTo(dfnumber => 4,toname => '/ora10g/oradata/ora10g/users01.dbf');

11 dbms_backup_restore.RestoreDatafileTo(dfnumber => 5,toname => '/ora10g/oradata/ora10g/example01.dbf');

12 dbms_backup_restore.RestoreDatafileTo(dfnumber => 6,toname => '/ora10g/oradata/ora10g/system02.dbf');

13 dbms_backup_restore.RestoreDatafileTo(dfnumber => 7,toname => '/ora10g/oradata/ora10g/users02.dbf');

14 dbms_backup_restore.RestoreBackupPiece(done => done,handle => '/ora10g/backup/database_full_0qns0pih_1_1.dbf', params => null);

15 dbms_backup_restore.DeviceDeallocate;

16 END;

17 /

PL/SQL procedure successfully completed.

執行成功，啟動資料庫

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

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

ORA-01110: data file 1: '/ora10g/oradata/ora10g/system01.dbf'

SQL> recover database using backup controlfile until cancel;

ORA-00283: recovery session canceled due to errors

ORA-19909: datafile 1 belongs to an orphan incarnation

ORA-01110: data file 1: '/ora10g/oradata/ora10g/system01.dbf'

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

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

ORA-01110: data file 1: '/ora10g/oradata/ora10g/system01.dbf'

這時，我們無法將資料庫開啟，一直報ORA-01152錯誤，說明資料庫的SCN號和資料檔案的SCN號不一致了。這是因為控制檔案我們是恢復回來的，要比資料檔案的SCN號要小。通過v$database和v$datafile的checkpoint_change#列，可以確定。

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

------------------

3154971

SQL> select file#,checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#

---------- ------------------

1 3185311

2 3185311

3 3185311

4 3185311

5 1300617

6 3185311

7 3185311

7 rows selected.

這個時候，就需要我們使用_allow_resetlogs_corruption的隱含引數來處理了。

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup mount;

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size 2168928 bytes

Variable Size 496887712 bytes

Database Buffers 1644167168 bytes

Redo Buffers 4259840 bytes

Database mounted.

重啟資料庫，使隱含引數生效。在次恢復資料庫

SQL>

SQL> recover database using backup controlfile until cancel;

ORA-00283: recovery session canceled due to errors

ORA-19909: datafile 1 belongs to an orphan incarnation

ORA-01110: data file 1: '/ora10g/oradata/ora10g/system01.dbf'

使用resetlogs開啟資料庫。

SQL> alter database open resetlogs;

Database altered.

檢查SCN

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

------------------

3185325

SQL> select file#,checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#

---------- ------------------

1 3185325

2 3185325

3 3185325

4 3185325

5 3185325

6 3185325

7 3185325

7 rows selected.

檢查資料檔案和控制檔案

$ cd /ora10g/oradata/ora10g/

$ ll

total 9912422

-rw-r----- 1 ora10g dba 7061504 Dec 4 11:10 control01.ctl

-rw-r----- 1 ora10g dba 7061504 Dec 4 10:47 control01.ctl.bak

-rw-r----- 1 ora10g dba 7061504 Dec 4 11:10 control02.ctl

-rw-r----- 1 ora10g dba 7061504 Dec 4 10:47 control02.ctl.bak

-rw-r----- 1 ora10g dba 7061504 Dec 4 11:10 control03.ctl

-rw-r----- 1 ora10g dba 7061504 Dec 4 10:47 control03.ctl.bak

-rw-r----- 1 ora10g dba 104865792 Dec 4 11:09 example01.dbf

-rw-r----- 1 ora10g dba 52429824 Dec 4 11:10 redo01.log

-rw-r----- 1 ora10g dba 52429824 Dec 4 11:09 redo02.log

-rw-r----- 1 ora10g dba 52429824 Dec 4 11:09 redo03.log

-rw-r----- 1 ora10g dba 387981312 Dec 4 11:09 sysaux01.dbf

-rw-r----- 1 ora10g dba 524296192 Dec 4 11:09 system01.dbf

-rw-r----- 1 ora10g dba 524296192 Dec 4 10:43 system01.dbf.bak

-rw-r----- 1 ora10g dba 1073750016 Dec 4 11:09 system02.dbf

-rw-r----- 1 ora10g dba 20979712 Dec 3 22:00 temp01.dbf

-rw-r----- 1 ora10g dba 57679872 Dec 4 11:09 undotbs01.dbf

-rw-r----- 1 ora10g dba 17047552 Dec 4 11:09 users01.dbf

-rw-r----- 1 ora10g dba 17047552 Dec 4 10:24 users01.dbf.bak

-rw-r----- 1 ora10g dba 1073750016 Dec 4 11:09 users02.dbf

-rw-r----- 1 ora10g dba 1073750016 Dec 4 10:24 users02.dbf.bak

後續處理，關閉隱含引數，重啟資料庫，對資料庫進行全備。

SQL> alter system set "_allow_resetlogs_corruption"=false scope=spfile;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size 2168928 bytes

Variable Size 496887712 bytes

Database Buffers 1644167168 bytes

Redo Buffers 4259840 bytes

Database mounted.

Database opened.

SQL>

檢查業務資料

SQL> conn scott/scott

Connected.

SQL>

SQL> select table_name from user_tables;

TABLE_NAME

------------------------------

DEPT

EMP

BONUS

SALGRADE

TEST_LOCK

DEPT_RILOCK

EMP_RILOCK

7 rows selected.

SQL>

後續思考：

使用dbms_backup_restor包使用存在侷限性

恢復時需要了解備份片資訊，說明備份資訊很重要啊！！！

恢復資料庫時需要指明file#與new_datafile_name，並且需要了解資料檔案備份在哪個備份片。

Rman已經完全可以實現，在使用這個感覺有些雞肋了。

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE

使用dbms_backup_restore進行資料庫恢復

相關文章