歸檔的備份之delete all input

oracle_zsx發表於2013-12-04
說明:
作業系統:oracle_linux_5.6_32
資料庫:oracle_11.2.0.3 單例項

問題:
    我一直有個問題,用delete in put這個命令的時候,備份的時候順便備份歸檔瞭然後刪掉了
    原來目錄下的歸檔,但是如果下次我們恢復的時候,需要歸檔怎麼辦,原來的位置下沒有
    歸檔了,oracle會自動應用剛剛備份的歸檔嗎。

實驗步驟如下:
(1)
檢視錶zsx中有多少條資料
SQL> select * from zsx;
        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

(2)
做一個全備包括歸檔:
RMAN> backup database format '/backup/2013_11_14_1055_%U.bak' 
plus archivelog delete all input;

(3)
插入10條資料
begin 
  for i in 11..20
  loop
     insert into zsx values (i);
     commit;
  end loop;
end;

(4)
SQL> alter system checkpoint;
SQL> alter system switch logfile;

(5)
繼續插入資料
begin 
  for i in 21..10000
  loop
     insert into zsx values (i);
     commit;
  end loop;
end;

(6)
會產生更多的歸檔,我們將歸檔備份走:
RMAN> backup archivelog all format '/backup/2013_11_14_1139_arc_%U.bak' delete input;

(7)
現在想做完全恢復,但是/arch下的歸檔都沒有了,而原先備份的時候只有10條資料
[oracle@RAC1 arch]$ ll
total 0

(8)
我們試著做完全恢復,看看會不會oracle自動用備份的歸檔。
[oracle@RAC1 rac1]$ rm -rf *.dbf

(9)
shutdown abort;

(10)
startup mount;

(11)
RMAN> restore database;

(12)
SQL> recover database;
ORA-00279: change 577511 generated at 11/26/2013 06:25:59 needed for thread 1
ORA-00289: suggestion : /arch/1_4_832476410.arc
ORA-00280: change 577511 for thread 1 is in sequence #4

Specify log: {=suggested | filename | AUTO | CANCEL}

注意:
這時候預設的歸檔路徑下沒有歸檔了,但是指定需要備份的歸檔而且路徑是在/arch,我們拭目以待,
敲個auto。
auto
ORA-00308: cannot open archived log '/arch/1_4_832476410.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

注意:
發現不行。

(13)
根據上面發現,我們需要4號歸檔,因為我們有備份,所以不怕。

(14)
恢復歸檔
既然需要4號,而且4號以後的都沒有了,那麼我們就從4號歸檔開始恢復:
RMAN> restore archivelog from logseq 4;

(15)
檢視歸檔路徑下的歸檔,從4號到我們備份時候的歸檔都有了
[oracle@RAC1 arch]$ ll
total 5788
-rw-r----- 1 oracle oinstall    1024 Nov 26 07:34 1_10_832476410.arc
-rw-r----- 1 oracle oinstall   50176 Nov 26 07:34 1_11_832476410.arc
-rw-r----- 1 oracle oinstall    4608 Nov 26 07:34 1_12_832476410.arc
-rw-r----- 1 oracle oinstall    2048 Nov 26 07:34 1_13_832476410.arc
-rw-r----- 1 oracle oinstall   18944 Nov 26 07:34 1_4_832476410.arc
-rw-r----- 1 oracle oinstall  260096 Nov 26 07:34 1_5_832476410.arc
-rw-r----- 1 oracle oinstall   22528 Nov 26 07:34 1_6_832476410.arc
-rw-r----- 1 oracle oinstall 5519360 Nov 26 07:34 1_7_832476410.arc
-rw-r----- 1 oracle oinstall    1024 Nov 26 07:34 1_8_832476410.arc
-rw-r----- 1 oracle oinstall    1024 Nov 26 07:34 1_9_832476410.arc

(16)
再次恢復:輸入auto
SQL> recover database;
ORA-00279: change 577511 generated at 11/26/2013 06:25:59 needed for thread 1
ORA-00289: suggestion : /arch/1_4_832476410.arc
ORA-00280: change 577511 for thread 1 is in sequence #4

Specify log: {=suggested | filename | AUTO | CANCEL}
auto

注意歸檔自動應用,並且介質恢復完成。
ORA-00279: change 578065 generated at 11/26/2013 06:42:33 needed for thread 1
ORA-00289: suggestion : /arch/1_6_832476410.arc
ORA-00280: change 578065 for thread 1 is in sequence #6
ORA-00278: log file '/arch/1_5_832476410.arc' no longer needed for this
recovery

ORA-00279: change 598139 generated at 11/26/2013 06:50:53 needed for thread 1
ORA-00289: suggestion : /arch/1_7_832476410.arc
ORA-00280: change 598139 for thread 1 is in sequence #7
ORA-00278: log file '/arch/1_6_832476410.arc' no longer needed for this
recovery

ORA-00279: change 609383 generated at 11/26/2013 07:02:21 needed for thread 1
ORA-00289: suggestion : /arch/1_8_832476410.arc
ORA-00280: change 609383 for thread 1 is in sequence #8
ORA-00278: log file '/arch/1_7_832476410.arc' no longer needed for this
recovery

ORA-00279: change 609385 generated at 11/26/2013 07:02:23 needed for thread 1
ORA-00289: suggestion : /arch/1_9_832476410.arc
ORA-00280: change 609385 for thread 1 is in sequence #9
ORA-00278: log file '/arch/1_8_832476410.arc' no longer needed for this
recovery

ORA-00279: change 609387 generated at 11/26/2013 07:02:24 needed for thread 1
ORA-00289: suggestion : /arch/1_10_832476410.arc
ORA-00280: change 609387 for thread 1 is in sequence #10
ORA-00278: log file '/arch/1_9_832476410.arc' no longer needed for this
recovery

ORA-00279: change 609389 generated at 11/26/2013 07:02:24 needed for thread 1
ORA-00289: suggestion : /arch/1_11_832476410.arc
ORA-00280: change 609389 for thread 1 is in sequence #11
ORA-00278: log file '/arch/1_10_832476410.arc' no longer needed for this
recovery

Log applied.
Media recovery complete.

(17)
開啟資料庫
SQL> alter database open;
Database altered.

(18)
檢視資料
SQL> select count(*) from zsx;
  COUNT(*)
----------
     10000
注意:
10000條資料都在,沒有發現丟失。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29107230/viewspace-1062261/,如需轉載,請註明出處,否則將追究法律責任。

相關文章