dbms_backup_restore包恢復控制檔案,資料檔案,歸檔檔案的測試案例
這個包恢復主要用於控制檔案丟失,沒備份和恢復目錄也不存在的情況下進行恢復,
但有rman 備份產生的備份集
測試環境 oracle 9.2.0.8+winxp sp2
1.用rman backup database include current controlfile plus archivelog delete input
備份資料庫
2.自動備份 CONFIGURE CONTROLFILE AUTOBACKUP OFF; 關閉
C:\Documents and Settings\Paul Yi>rman target /
Recovery Manager: Release 9.2.0.8.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: PUBTEST (DBID=799229701)
RMAN> backup database include current controlfile plus archivelog delete input;
Starting backup at 10-JUN-08
current log archived
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=265 stamp=657037381
input archive log thread=1 sequence=2 recid=266 stamp=657037399
input archive log thread=1 sequence=3 recid=267 stamp=657037410
input archive log thread=1 sequence=4 recid=268 stamp=657037416
input archive log thread=1 sequence=5 recid=269 stamp=657037420
input archive log thread=1 sequence=6 recid=270 stamp=657037422
input archive log thread=1 sequence=7 recid=271 stamp=657037462
input archive log thread=1 sequence=8 recid=272 stamp=657038049
channel ORA_DISK_1: starting piece 1 at 10-JUN-08
channel ORA_DISK_1: finished piece 1 at 10-JUN-08
piece handle=D:\BACKUP\3PJIJ6N2_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:09
channel ORA_DISK_1: deleting archive log(s)
archive log filename=D:\ARCHPAUL\PUBTEST_1_1.DBF recid=265 stamp=657037381
archive log filename=D:\ARCHPAUL\PUBTEST_1_2.DBF recid=266 stamp=657037399
archive log filename=D:\ARCHPAUL\PUBTEST_1_3.DBF recid=267 stamp=657037410
archive log filename=D:\ARCHPAUL\PUBTEST_1_4.DBF recid=268 stamp=657037416
archive log filename=D:\ARCHPAUL\PUBTEST_1_5.DBF recid=269 stamp=657037420
archive log filename=D:\ARCHPAUL\PUBTEST_1_6.DBF recid=270 stamp=657037422
archive log filename=D:\ARCHPAUL\PUBTEST_1_7.DBF recid=271 stamp=657037462
archive log filename=D:\ARCHPAUL\PUBTEST_1_8.DBF recid=272 stamp=657038049
Finished backup at 10-JUN-08
Starting backup at 10-JUN-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00001 name=D:\ORACLE\ORADATA\PUBTEST\SYSTEM01.DBF
input datafile fno=00002 name=D:\ORACLE\ORADATA\PUBTEST\UNDOTBS01.DBF
input datafile fno=00004 name=D:\ORACLE\ORADATA\PUBTEST\INDX01.DBF
input datafile fno=00006 name=D:\ORACLE\ORADATA\PUBTEST\USERS01.DBF
input datafile fno=00005 name=D:\ORACLE\ORADATA\PUBTEST\TOOLS01.DBF
input datafile fno=00003 name=D:\ORACLE\ORADATA\PUBTEST\EXAMPLE01.DBF
channel ORA_DISK_1: starting piece 1 at 10-JUN-08
channel ORA_DISK_1: finished piece 1 at 10-JUN-08
piece handle=D:\BACKUP\3QJIJ6NB_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:47
Finished backup at 10-JUN-08
Starting backup at 10-JUN-08
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=9 recid=273 stamp=657038106
channel ORA_DISK_1: starting piece 1 at 10-JUN-08
channel ORA_DISK_1: finished piece 1 at 10-JUN-08
piece handle=D:\BACKUP\3RJIJ6OQ_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=D:\ARCHPAUL\PUBTEST_1_9.DBF recid=273 stamp=657038106
Finished backup at 10-JUN-08
RMAN>
C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.8.0 - Production on Tue Jun 10 14:45:36 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
SQL> insert into test1 select * from test1;
8388608 rows created.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> commit;
Commit complete.
SQL> commit;
Commit complete.
SQL> alter system switch logfile; --日誌切換 生成歸檔
System altered.
SQL> select count(*) from test1;
COUNT(*)
----------
16777216
SQL> insert into test1 select * from test1 where rownum<10; --當前線上日誌
9 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from test1;
COUNT(*)
----------
16777225
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
刪除D:\oracle\oradata\pubtest\*.ctl.*.dbf,*.log 所有檔案 控制檔案,資料檔案,重做日誌檔案丟失
C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.8.0 - Production on Tue Jun 10 14:54:24 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 101785012 bytes
Fixed Size 454068 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
利用包恢復控制檔案包
SQL> declare
devtype varchar2(256);
done boolean;
begin
devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
sys.dbms_backup_restore.restoresetdatafile;
sys.dbms_backup_restore.restorecontrolfileto(cfname=>'D:\oracle\oradata\pubtest\control01.ctl');
sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'d:\backup\3LJIIK4O_1_1',params=>null); 紅色代表備份集
sys.dbms_backup_restore.devicedeallocate;
end;
/
PL/SQL procedure successfully completed.
複製control01.ctl 命名為control02.ctl,control03.ctl 和引數檔案一致
SQL> alter database mount;
Database altered.
利用包恢復資料檔案 注意檔名和路徑 可以透過檢視
select file#,name from v$datafile 查出來
SQL> declare
devtype varchar2(256);
done boolean;
begin
devtype:=sys.dbms_backup_restore.deviceallocate(type=>'',ident=>'t1');
sys.dbms_backup_restore.restoresetdatafile;
sys.dbms_backup_restore.restoredatafileto(dfnumber=>01,toname=>'d:\oracle\oradata\pubtest\system01.dbf');
sys.dbms_backup_restore.restoredatafileto(dfnumber=>02,toname=>'d:\oracle\oradata\pubtest\undotbs01.dbf');
sys.dbms_backup_restore.restoredatafileto(dfnumber=>03,toname=>'d:\oracle\oradata\pubtest\example01.dbf');
sys.dbms_backup_restore.restoredatafileto(dfnumber=>04,toname=>'d:\oracle\oradata\pubtest\indx01.dbf');
sys.dbms_backup_restore.restoredatafileto(dfnumber=>05,toname=>'d:\oracle\oradata\pubtest\tools01.dbf');
sys.dbms_backup_restore.restoredatafileto(dfnumber=>06,toname=>'d:\oracle\oradata\pubtest\users01.dbf');
sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'d:\backup\3NJIIRVU_1_1',params=>null);
sys.dbms_backup_restore.devicedeallocate;
end;
/
PL/SQL procedure successfully completed.
利用包恢復歸檔日誌檔案 也要設定歸檔路徑目標
因為這裡plus archivelog 命令備份會產生兩個歸檔
日誌備份集
SQL> declare
devtype varchar2(256);
done boolean;
begin
devtype:=sys.dbms_backup_restore.deviceallocate(type=>'',ident=>'t1');
sys.dbms_backup_restore.restoresetarchivedlog(destination=>'d:\archpaul\');
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>1);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>2);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>3);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>4);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>5);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>6);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>7);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>8);
sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'d:\backup\3PJIJ6N2_1_1',params=>null);
sys.dbms_backup_restore.devicedeallocate;
end;
/
PL/SQL procedure successfully completed.
SQL> declare
devtype varchar2(256);
done boolean;
begin
devtype:=sys.dbms_backup_restore.deviceallocate(type=>'',ident=>'t1');
sys.dbms_backup_restore.restoresetarchivedlog(destination=>'d:\archpaul\');
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>9);
sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'d:\backup\3RJIJ6OQ_1_1',params=>null);
sys.dbms_backup_restore.devicedeallocate;
end;
/
PL/SQL procedure successfully completed.
使用備份的控制檔案恢復資料庫 因為丟失當前線上日誌檔案
介質恢復時用until cancel open resetlogs開啟資料庫
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 780486 generated at 06/10/2008 14:34:09 needed for thread 1
ORA-00289: suggestion : D:\ARCHPAUL\PUBTEST_1_9.DBF
ORA-00280: change 780486 for thread 1 is in sequence #9
Specify log: {
auto
ORA-00279: change 780505 generated at 06/10/2008 14:35:06 needed for thread 1
ORA-00289: suggestion : D:\ARCHPAUL\PUBTEST_1_10.DBF
ORA-00280: change 780505 for thread 1 is in sequence #10
ORA-00278: log file 'D:\ARCHPAUL\PUBTEST_1_9.DBF' no longer needed for this
recovery
ORA-00279: change 781271 generated at 06/10/2008 14:46:04 needed for thread 1
ORA-00289: suggestion : D:\ARCHPAUL\PUBTEST_1_11.DBF
ORA-00280: change 781271 for thread 1 is in sequence #11
ORA-00278: log file 'D:\ARCHPAUL\PUBTEST_1_10.DBF' no longer needed for this
recovery
ORA-00279: change 781354 generated at 06/10/2008 14:47:05 needed for thread 1
ORA-00289: suggestion : D:\ARCHPAUL\PUBTEST_1_12.DBF
ORA-00280: change 781354 for thread 1 is in sequence #12
ORA-00278: log file 'D:\ARCHPAUL\PUBTEST_1_11.DBF' no longer needed for this
recovery
ORA-00279: change 781369 generated at 06/10/2008 14:47:46 needed for thread 1
ORA-00289: suggestion : D:\ARCHPAUL\PUBTEST_1_13.DBF
ORA-00280: change 781369 for thread 1 is in sequence #13
ORA-00278: log file 'D:\ARCHPAUL\PUBTEST_1_12.DBF' no longer needed for this
recovery
ORA-00279: change 781376 generated at 06/10/2008 14:47:57 needed for thread 1
ORA-00289: suggestion : D:\ARCHPAUL\PUBTEST_1_14.DBF
ORA-00280: change 781376 for thread 1 is in sequence #14
ORA-00278: log file 'D:\ARCHPAUL\PUBTEST_1_13.DBF' no longer needed for this
recovery
ORA-00308: cannot open archived log 'D:\ARCHPAUL\PUBTEST_1_14.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from test1;
COUNT(*)
----------
16777216
可以看到當前線上日誌檔案裡面的9條資料丟失
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7199859/viewspace-343273/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 恢復測試：擁有當時的全部歸檔，控制檔案，恢復丟失的資料檔案。
- 恢復案例：無歸檔，丟失全部控制檔案、日誌檔案恢復案例
- 控制檔案恢復測試
- RMAN恢復表空間,資料檔案,歸檔檔案,控制檔案等介紹
- RMAN恢復案例:無恢復目錄，丟失全部資料檔案、控制檔案、日誌檔案恢復
- 恢復案例：歸檔模式下丟失全部資料檔案的恢復模式
- 恢復案例：無歸檔，掉電，控制檔案全部丟失恢復
- 引數檔案控制檔案和資料檔案丟失的恢復
- 歸檔模式有備份丟失控制檔案和資料檔案後恢復模式
- rman恢復控制檔案測試--log
- 測試恢復5==使用2進位制形式檔案恢復控制檔案
- 利用歸檔來做資料檔案的恢復
- 控制檔案恢復—從trace檔案中恢復
- 在歸檔下恢復系統資料檔案
- 不完全恢復（資料檔案備份--新建表空間--控制檔案備份--日誌歸檔檔案）
- 歸檔模式下資料檔案丟失的恢復模式
- 同時丟失控制檔案與資料檔案的恢復
- 【/proc/檔案淺析】另類辦法恢復資料檔案和控制檔案
- RMAN備份與恢復（新舊控制檔案及歸檔日誌）測試
- RMAN備份資料檔案+控制檔案+歸檔日誌
- 備份與恢復--重建控制檔案後資料檔案損壞的恢復
- 使用DBMS_BACKUP_RESTORE來還原控制檔案和資料檔案REST
- Linux下誤刪資料檔案從檔案控制程式碼恢復資料檔案Linux
- rman 恢復---歸檔丟失and資料檔案損壞
- 誤刪除資料檔案、控制檔案的非RMAN恢復方法
- 【備份恢復】利用 備份控制檔案到指定目錄下的控制檔案 恢復控制檔案
- 歸檔模式，恢復沒有備份的資料檔案模式
- RMAN備份與恢復（新舊控制檔案及歸檔日誌）測試（敘）
- 使用舊的控制檔案備份來恢復控制檔案
- RMAN恢復控制檔案
- 手工恢復控制檔案
- 恢復案例：歸檔模式下丟失非系統表空間資料檔案的恢復模式
- 通過檔案控制程式碼恢復刪除的資料檔案
- 沒有控制檔案怎麼恢復資料庫dbms_backup_restore包 英文文件資料庫REST
- rman備份恢復-rman恢復資料檔案測試
- 恢復歸檔日誌檔案的常用方法
- rman恢復--歸檔模式有備份，丟失資料檔案的恢復模式
- rman恢復--歸檔模式無備份，丟失資料檔案的恢復模式