使用dbms_backup_restore進行資料庫恢復
使用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
相關文章
- 使用netbackup進行資料庫恢復案例資料庫
- 資料庫複製方式進行資料庫恢復資料庫
- 刪除表空間,有rman全備的恢復(使用dbms_backup_restore來進行恢復)REST
- 只有.dbf資料檔案進行資料庫恢復資料庫
- mysql使用binlog進行資料恢復MySql資料恢復
- 使用恢復建議恢復資料庫資料庫
- 資料庫resetlogs後進行rman恢復7資料庫
- 資料庫resetlogs後進行rman恢復6資料庫
- 資料庫resetlogs後進行rman恢復5資料庫
- 資料庫resetlogs後進行rman恢復4資料庫
- 資料庫resetlogs後進行rman恢復3資料庫
- 資料庫resetlogs後進行rman恢復2資料庫
- 資料庫resetlogs後進行rman恢復1資料庫
- 透過RMAN進行資料庫恢復（step by step）資料庫
- 應對RMAN-06026錯誤，使用dbms_backup_restore進行恢復REST
- DM7使用DMRAMN對多次故障恢復後使用不同資料庫的歸檔進行恢復資料庫
- 【資料庫資料恢復】SAP資料庫資料恢復案例資料庫資料恢復
- MySQL資料庫遭到攻擊篡改---使用備份和binlog進行資料恢復MySql資料庫資料恢復
- Mysql 誤刪資料進行恢復MySql
- 寶塔資料庫恢復 mysql資料庫丟失恢復 mysql資料庫刪除庫恢復 寶塔mysql資料庫恢復資料庫MySql
- rman恢復時跳過資料檔案，進行恢復
- 使用innobackupex恢復mysql資料庫MySql資料庫
- 使用data dump 恢復資料庫資料庫
- 【資料庫資料恢復】Sql Server資料庫資料恢復案例資料庫資料恢復SQLServer
- 升級失敗後對資料庫進行恢復 (2)資料庫
- 升級失敗後對資料庫進行恢復 (1)資料庫
- rman恢復 使用switch映像副本進行恢復
- DM7使用DMRMAN執行RAC資料庫恢復資料庫
- 恢復資料庫資料庫
- 【資料庫資料恢復】透過恢復NDF檔案修復資料庫的資料恢復過程資料庫資料恢復
- 利用undo進行資料的恢復操作
- 沒有控制檔案怎麼恢復資料庫dbms_backup_restore包 英文文件資料庫REST
- 如何在HarmonyOS對資料庫進行備份，恢復與加密資料庫加密
- oracle日誌挖機 找到scn號 進行資料庫恢復Oracle資料庫
- 【資料庫資料恢復】如何恢復Oracle資料庫truncate表的資料資料庫資料恢復Oracle
- 【資料庫資料恢復】windows server下SqlServer資料庫的資料恢復資料庫資料恢復WindowsServerSQL
- (Les16 執行資料庫恢復)-表空間恢復資料庫
- 【北亞資料恢復】oracle資料庫執行truncate table命令怎麼恢復資料？資料恢復Oracle資料庫