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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【/proc/檔案淺析】另類辦法恢復資料檔案和控制檔案
- 同名檔案替換怎麼恢復，恢復同名檔案
- 與控制檔案有關的恢復
- 從備份片中恢復某個指定得歸檔或者資料檔案
- Oracle使用備份檔案集恢復歸檔日誌Oracle
- word怎麼恢復儲存前的檔案，word檔案恢復
- 檔案替換後怎麼恢復，恢復被覆蓋的檔案
- 【RMAN】如果控制檔案損壞那麼如何恢復？恢復控制檔案的方式有哪幾種？
- 【伺服器資料恢復】StorNext檔案系統資料恢復案例伺服器資料恢復
- 剪下後的檔案可以恢復嗎？恢復剪下檔案怎麼辦？
- word自動儲存的檔案怎麼恢復，word檔案恢復
- 與控制檔案有關的恢復(二)
- 剪下的檔案還能恢復嗎，恢復剪貼丟失的檔案
- 【儲存資料恢復】WAFL檔案系統下raid資料恢復案例資料恢復AI
- 檔案中勒索恢復
- 【資料庫資料恢復】MongoDB資料庫檔案損壞的資料恢復案例資料庫資料恢復MongoDB
- 如何輕鬆歸檔檔案？2種方法輕鬆建立歸檔檔案！
- 電腦檔案丟失資料恢復資料恢復
- [20210225]控制檔案序列號滿的恢復.txt
- RMAN備份恢復典型案例——資料檔案存在壞快
- qq檔案失效怎麼恢復 qq已失效的檔案能不能恢復
- 360粉碎檔案可以恢復嗎，如何恢復360強力刪除的檔案
- 恢復ext4檔案系統被誤刪的檔案
- 檔案的基本管理和XFS檔案系統備份恢復
- 清除Oracle控制檔案中的歸檔資訊v$archived_logOracleHive
- eclipse 恢復誤刪檔案Eclipse
- Git恢復刪除的檔案Git
- uninstall 後的檔案如何恢復
- 【北亞資料恢復】MongoDB資料遷移檔案丟失的MongoDB資料恢復案例資料恢復MongoDB
- 【資料庫資料恢復】mdb_catalog.wt檔案丟失的MongoDB資料恢復案例資料庫資料恢復MongoDB
- 【伺服器資料恢復】xfs檔案系統資料丟失的資料恢復案例伺服器資料恢復
- 【伺服器資料恢復】VMware虛擬機器磁碟檔案恢復案例伺服器資料恢復虛擬機
- mysql通過frm、idb檔案恢復資料MySql
- SQL SEVER 缺少LOG檔案資料庫恢復SQL資料庫
- UAVStack之檔案資料歸集
- Linux伺服器資料恢復案例；ocfs2檔案系統資料恢復Linux伺服器資料恢復
- RAC控制檔案恢復（三種不同情況）
- rman恢復控制檔案的一個小錯誤
- U盤檔案被隱藏怎麼恢復 U盤檔案恢復隱藏的方法