【原創】你誤刪除了一張表,請使用備份+歸檔來將資料庫恢復到刪除表之前的狀態

leonarding發表於2013-05-02

topic:【原創】假設在有最後一次全庫備份之後,你誤刪除了一張表,請使用備份+歸檔來將資料庫恢復到刪除表之前的狀態。(不完全恢復)

 

                                 更多精彩內容盡在

進入Rman做全庫備份


[oracle@leonarding1backup]$ rman target /


Recovery Manager:Release 11.2.0.1.0 - Production on Tue Apr 30 11:08:29 2013


Copyright (c)1982, 2009, Oracle and/or its affiliates. All rights reserved.


connected totarget database: LEO1 (DBID=1692458681)


RMAN> backupfull database format              


'/u02/app/oracle/backup/full_bk1_%u%p%s.rmn'include current controlfile


plus


archivelog format'/u02/app/oracle/backup/arch_bk1_%u%p%s.rmn' delete all input;


2> 3> 4>




Starting backup at30-APR-13


current logarchived


using targetdatabase control file instead of recovery catalog


allocated channel:ORA_DISK_1


channelORA_DISK_1: SID=22 device type=DISK


RMAN-00571:===========================================================


RMAN-00569:=============== ERROR MESSAGE STACK FOLLOWS ===============


RMAN-00571:===========================================================


RMAN-03002:failure of backup plus archivelog command at 04/30/2013 11:13:27


RMAN-06059:expected archived log not found, loss of archived log compromisesrecoverability


ORA-19625: erroridentifying file /u02/app/oracle/archdata/1_75_813654649.dbf


ORA-27037: unableto obtain file status


Linux-x86_64Error: 2: No such file or directory


Additionalinformation: 3


75號歸檔日誌缺失,Rman在作業系統上找不到對應的日誌檔案


當手工刪除了歸檔日誌以後,Rman備份會檢測到日誌缺失,從而無法進一步繼續執行。
所以此時需要手工執行crosscheck過程,之後Rman備份可以恢復正常


RMAN> crosscheckarchivelog all;                     交叉檢查,有3個日誌被手工刪除


released channel:ORA_DISK_1


allocated channel:ORA_DISK_1


channelORA_DISK_1: SID=22 device type=DISK


validation failedfor archived log


archivedlog file name=/u02/app/oracle/archdata/1_75_813654649.dbf RECID=9STAMP=813790708


validation failedfor archived log


archivedlog file name=/u02/app/oracle/archdata/1_76_813654649.dbf RECID=7STAMP=813790702


validation failedfor archived log


archivedlog file name=/u02/app/oracle/archdata/1_77_813654649.dbf RECID=8STAMP=813790706


validationsucceeded for archived log


archived log filename=/u02/app/oracle/archdata/1_1_813790699.dbf RECID=10 STAMP=813791152


validationsucceeded for archived log


archived log filename=/u02/app/oracle/archdata/1_2_813790699.dbf RECID=11 STAMP=813913400


validationsucceeded for archived log


archived log filename=/u02/app/oracle/archdata/1_3_813790699.dbf RECID=12 STAMP=813913401


validationsucceeded for archived log


archived log filename=/u02/app/oracle/archdata/1_4_813790699.dbf RECID=13 STAMP=813913405


validationsucceeded for archived log


archived log filename=/u02/app/oracle/archdata/1_5_813790699.dbf RECID=14 STAMP=813933703


validationsucceeded for archived log


archived log filename=/u02/app/oracle/archdata/1_6_813790699.dbf RECID=15 STAMP=813933734


validationsucceeded for archived log


archived log filename=/u02/app/oracle/archdata/1_7_813790699.dbf RECID=16 STAMP=814090435


validationsucceeded for archived log


archived log filename=/u02/app/oracle/archdata/1_8_813790699.dbf RECID=17 STAMP=814090645


validationsucceeded for archived log


archived log filename=/u02/app/oracle/archdata/1_9_813790699.dbf RECID=18 STAMP=814090877


validationsucceeded for archived log


archived log filename=/u02/app/oracle/archdata/1_10_813790699.dbf RECID=19 STAMP=814091008


validationsucceeded for archived log


archived log filename=/u02/app/oracle/archdata/1_11_813790699.dbf RECID=20 STAMP=814091028


validationsucceeded for archived log


archived log filename=/u02/app/oracle/archdata/1_12_813790699.dbf RECID=21 STAMP=814091032


validationsucceeded for archived log


archived log filename=/u02/app/oracle/archdata/1_13_813790699.dbf RECID=22 STAMP=814091036


validationsucceeded for archived log


archived log filename=/u02/app/oracle/archdata/1_14_813790699.dbf RECID=23 STAMP=814091057


validationsucceeded for archived log


archived log filename=/u02/app/oracle/archdata/1_15_813790699.dbf RECID=24 STAMP=814091184


validationsucceeded for archived log


archived log filename=/u02/app/oracle/archdata/1_16_813790699.dbf RECID=25 STAMP=814091638


validationsucceeded for archived log


archived log filename=/u02/app/oracle/archdata/1_17_813790699.dbf RECID=26 STAMP=814091658


validationsucceeded for archived log


archived log filename=/u02/app/oracle/archdata/1_18_813790699.dbf RECID=27 STAMP=814091709


validationsucceeded for archived log


archived log filename=/u02/app/oracle/archdata/1_19_813790699.dbf RECID=28 STAMP=814092394


validationsucceeded for archived log


archived log filename=/u02/app/oracle/archdata/1_20_813790699.dbf RECID=29 STAMP=814092405


validationsucceeded for archived log


archived log filename=/u02/app/oracle/archdata/1_1_814098124.dbf RECID=30 STAMP=814098353


validationsucceeded for archived log


archived log filename=/u02/app/oracle/archdata/1_2_814098124.dbf RECID=31 STAMP=814100979


validationsucceeded for archived log


archived log filename=/u02/app/oracle/archdata/1_3_814098124.dbf RECID=32 STAMP=814101206


validationsucceeded for archived log


archived log filename=/u02/app/oracle/archdata/1_4_814098124.dbf RECID=33 STAMP=814101824


Crosschecked 27objects


RMAN> deleteexpired archivelog all;                              刪除所有過期歸檔日誌


released channel:ORA_DISK_1


allocated channel:ORA_DISK_1


channelORA_DISK_1: SID=22 device type=DISK


List of ArchivedLog Copies for database with db_unique_name LEO1


=====================================================================




Key     Thrd Seq     S Low Time


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


9       1   75      X 26-APR-13


        Name: /u02/app/oracle/archdata/1_75_813654649.dbf




7       1   76      X 26-APR-13


        Name: /u02/app/oracle/archdata/1_76_813654649.dbf




8       1   77      X 26-APR-13


        Name: /u02/app/oracle/archdata/1_77_813654649.dbf


Do you really wantto delete the above objects (enter YES or NO)? y     75  76   77號日誌被刪除


deleted archivedlog


archived log filename=/u02/app/oracle/archdata/1_75_813654649.dbf RECID=9 STAMP=813790708


deleted archivedlog


archived log filename=/u02/app/oracle/archdata/1_76_813654649.dbf RECID=7 STAMP=813790702


deleted archivedlog


archived log filename=/u02/app/oracle/archdata/1_77_813654649.dbf RECID=8 STAMP=813790706


Deleted 3 EXPIREDobjects




RMAN> backup fulldatabase format      現在Rman備份可以恢復正常,同時刪除備份過的歸檔日誌


'/u02/app/oracle/backup/full_bk1_%u%p%s.rmn'include current controlfile


plus


archivelog format'/u02/app/oracle/backup/arch_bk1_%u%p%s.rmn' delete all input;2> 3> 4>




Starting backup at30-APR-13


current logarchived


using channelORA_DISK_1


channelORA_DISK_1: starting archived log backup set


channelORA_DISK_1: specifying archived log(s) in backup set


input archived logthread=1 sequence=1 RECID=10 STAMP=813791152


input archived logthread=1 sequence=2 RECID=11 STAMP=813913400


input archived logthread=1 sequence=3 RECID=12 STAMP=813913401


input archived logthread=1 sequence=4 RECID=13 STAMP=813913405


input archived logthread=1 sequence=5 RECID=14 STAMP=813933703


input archived logthread=1 sequence=6 RECID=15 STAMP=813933734


input archived logthread=1 sequence=7 RECID=16 STAMP=814090435


input archived logthread=1 sequence=8 RECID=17 STAMP=814090645


input archived logthread=1 sequence=9 RECID=18 STAMP=814090877


input archived logthread=1 sequence=10 RECID=19 STAMP=814091008


input archived logthread=1 sequence=11 RECID=20 STAMP=814091028


input archived logthread=1 sequence=12 RECID=21 STAMP=814091032


input archived logthread=1 sequence=13 RECID=22 STAMP=814091036


input archived logthread=1 sequence=14 RECID=23 STAMP=814091057


input archived logthread=1 sequence=15 RECID=24 STAMP=814091184


input archived logthread=1 sequence=16 RECID=25 STAMP=814091638


input archived logthread=1 sequence=17 RECID=26 STAMP=814091658


input archived logthread=1 sequence=18 RECID=27 STAMP=814091709


input archived logthread=1 sequence=19 RECID=28 STAMP=814092394


channelORA_DISK_1: starting piece 1 at 30-APR-13


channelORA_DISK_1: finished piece 1 at 30-APR-13


piecehandle=/u02/app/oracle/backup/arch_bk1_0co8cds4112.rmn tag=TAG20130430T113324comment=NONE


channelORA_DISK_1: backup set complete, elapsed time: 00:00:07


channelORA_DISK_1: deleting archived log(s)


archived log filename=/u02/app/oracle/archdata/1_1_813790699.dbf RECID=10 STAMP=813791152


archived log filename=/u02/app/oracle/archdata/1_2_813790699.dbf RECID=11 STAMP=813913400


archived log filename=/u02/app/oracle/archdata/1_3_813790699.dbf RECID=12 STAMP=813913401


archived log filename=/u02/app/oracle/archdata/1_4_813790699.dbf RECID=13 STAMP=813913405


archived log filename=/u02/app/oracle/archdata/1_5_813790699.dbf RECID=14 STAMP=813933703


archived log filename=/u02/app/oracle/archdata/1_6_813790699.dbf RECID=15 STAMP=813933734


archived log filename=/u02/app/oracle/archdata/1_7_813790699.dbf RECID=16 STAMP=814090435


archived log filename=/u02/app/oracle/archdata/1_8_813790699.dbf RECID=17 STAMP=814090645


archived log filename=/u02/app/oracle/archdata/1_9_813790699.dbf RECID=18 STAMP=814090877


archived log filename=/u02/app/oracle/archdata/1_10_813790699.dbf RECID=19 STAMP=814091008


archived log filename=/u02/app/oracle/archdata/1_11_813790699.dbf RECID=20 STAMP=814091028


archived log filename=/u02/app/oracle/archdata/1_12_813790699.dbf RECID=21 STAMP=814091032


archived log filename=/u02/app/oracle/archdata/1_13_813790699.dbf RECID=22 STAMP=814091036


archived log filename=/u02/app/oracle/archdata/1_14_813790699.dbf RECID=23 STAMP=814091057


archived log filename=/u02/app/oracle/archdata/1_15_813790699.dbf RECID=24 STAMP=814091184


archived log filename=/u02/app/oracle/archdata/1_16_813790699.dbf RECID=25 STAMP=814091638


archived log filename=/u02/app/oracle/archdata/1_17_813790699.dbf RECID=26 STAMP=814091658


archived log filename=/u02/app/oracle/archdata/1_18_813790699.dbf RECID=27 STAMP=814091709


archived log filename=/u02/app/oracle/archdata/1_19_813790699.dbf RECID=28 STAMP=814092394


channelORA_DISK_1: starting archived log backup set


channelORA_DISK_1: specifying archived log(s) in backup set


input archived logthread=1 sequence=1 RECID=30 STAMP=814098353


channelORA_DISK_1: starting piece 1 at 30-APR-13


channelORA_DISK_1: finished piece 1 at 30-APR-13


piecehandle=/u02/app/oracle/backup/arch_bk1_0do8cdsc113.rmn tag=TAG20130430T113324comment=NONE


channelORA_DISK_1: backup set complete, elapsed time: 00:00:01


channelORA_DISK_1: deleting archived log(s)


archived log filename=/u02/app/oracle/archdata/1_1_814098124.dbf RECID=30 STAMP=814098353


channelORA_DISK_1: starting archived log backup set


channelORA_DISK_1: specifying archived log(s) in backup set


input archived logthread=1 sequence=20 RECID=29 STAMP=814092405


channelORA_DISK_1: starting piece 1 at 30-APR-13


channelORA_DISK_1: finished piece 1 at 30-APR-13


piecehandle=/u02/app/oracle/backup/arch_bk1_0eo8cdsd114.rmn tag=TAG20130430T113324comment=NONE


channelORA_DISK_1: backup set complete, elapsed time: 00:00:01


channelORA_DISK_1: deleting archived log(s)


archived log filename=/u02/app/oracle/archdata/1_20_813790699.dbf RECID=29 STAMP=814092405


channelORA_DISK_1: starting archived log backup set


channelORA_DISK_1: specifying archived log(s) in backup set


input archived logthread=1 sequence=2 RECID=31 STAMP=814100979


input archived logthread=1 sequence=3 RECID=32 STAMP=814101206


input archived logthread=1 sequence=4 RECID=33 STAMP=814101824


input archived logthread=1 sequence=5 RECID=34 STAMP=814102404


channelORA_DISK_1: starting piece 1 at 30-APR-13


channelORA_DISK_1: finished piece 1 at 30-APR-13


piecehandle=/u02/app/oracle/backup/arch_bk1_0fo8cdse115.rmn tag=TAG20130430T113324comment=NONE


channelORA_DISK_1: backup set complete, elapsed time: 00:00:01


channelORA_DISK_1: deleting archived log(s)


archived log filename=/u02/app/oracle/archdata/1_2_814098124.dbf RECID=31 STAMP=814100979


archived log filename=/u02/app/oracle/archdata/1_3_814098124.dbf RECID=32 STAMP=814101206


archived log filename=/u02/app/oracle/archdata/1_4_814098124.dbf RECID=33 STAMP=814101824


archived log filename=/u02/app/oracle/archdata/1_5_814098124.dbf RECID=34 STAMP=814102404


Finished backup at30-APR-13




Starting backup at30-APR-13


using channelORA_DISK_1


channelORA_DISK_1: starting full datafile backup set


channelORA_DISK_1: specifying datafile(s) in backup set


input datafilefile number=00001 name=/u02/app/oracle/oradata/LEO1/system01.dbf


input datafilefile number=00002 name=/u02/app/oracle/oradata/LEO1/sysaux01.dbf


input datafilefile number=00003 name=/u02/app/oracle/oradata/LEO1/undotbs01.dbf


input datafile filenumber=00005 name=/u02/app/oracle/oradata/LEO1/leo1.dbf


input datafilefile number=00004 name=/u02/app/oracle/oradata/LEO1/users01.dbf


channelORA_DISK_1: starting piece 1 at 30-APR-13


channelORA_DISK_1: finished piece 1 at 30-APR-13


piece handle=/u02/app/oracle/backup/full_bk1_0go8cdsg116.rmntag=TAG20130430T113336 comment=NONE


channelORA_DISK_1: backup set complete, elapsed time: 00:01:25


channelORA_DISK_1: starting full datafile backup set


channelORA_DISK_1: specifying datafile(s) in backup set


including currentcontrol file in backup set


channelORA_DISK_1: starting piece 1 at 30-APR-13


channelORA_DISK_1: finished piece 1 at 30-APR-13


piecehandle=/u02/app/oracle/backup/full_bk1_0ho8cdv5117.rmn tag=TAG20130430T113336comment=NONE


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


Finished backup at30-APR-13




Starting backup at30-APR-13


current logarchived


using channelORA_DISK_1


channelORA_DISK_1: starting archived log backup set


channelORA_DISK_1: specifying archived log(s) in backup set


input archived logthread=1 sequence=6 RECID=35 STAMP=814102503


channelORA_DISK_1: starting piece 1 at 30-APR-13


channelORA_DISK_1: finished piece 1 at 30-APR-13


piecehandle=/u02/app/oracle/backup/arch_bk1_0io8cdv7118.rmn tag=TAG20130430T113503comment=NONE


channelORA_DISK_1: backup set complete, elapsed time: 00:00:01


channelORA_DISK_1: deleting archived log(s)


archived log filename=/u02/app/oracle/archdata/1_6_814098124.dbf RECID=35 STAMP=814102503


Finished backup at30-APR-13




Starting ControlFile and SPFILE Autobackup at 30-APR-13


piecehandle=/u02/app/oracle/backup/control/cf_c-1692458681-20130430-01 comment=NONE


Finished ControlFile and SPFILE Autobackup at 30-APR-13


我們現在進入資料庫建立一個表leo1並插入三條記錄


SYS@LEO1>createtable leo1 (name varchar2(20),age number,riqi date);


Table created.


SYS@LEO1>insertinto leo1 values('leonarding',28,sysdate);


1 row created.


SYS@LEO1>insertinto leo1 values('sun_vn',26,sysdate);


1 row created.


SYS@LEO1>insertinto leo1 values('tiger',18,sysdate);


1 row created.


SYS@LEO1>commit;


Commit complete.


SYS@LEO1>select* from leo1;                          完成


NAME                        AGE RIQI


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


leonarding                   28 30-APR-13


sun_vn                      26 30-APR-13


tiger                        18 30-APR-13


SYS@LEO1>selectgroup#,members,bytes,archived,sequence#,status from v$log;


    GROUP#   MEMBERS      BYTES ARC  SEQUENCE# STATUS


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


         1          2  52428800 NO           7 CURRENT


         2          2  52428800 YES          5 INACTIVE


         5          2  52428800 YES          6 INACTIVE


我們建立表和插入資訊是寫入當前日誌組1


SYS@LEO1>altersystem switch logfile;              切換日誌組


System altered.


SYS@LEO1>altersystem switch logfile;


System altered.


SYS@LEO1>selectgroup#,members,bytes,archived,sequence#,status from v$log;


    GROUP#   MEMBERS      BYTES ARC  SEQUENCE# STATUS


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


         1          2  52428800 YES          7 INACTIVE


         2          2  52428800 YES          8 INACTIVE


         5          2  52428800 NO           9 CURRENT


現在組1已經完成歸檔,如果我們此時勿刪除了leo1表,我們可以把資料庫恢復到7號歸檔日誌的那一點,就可以恢復勿刪除的leo1表了。我們順便把當前redolog日誌也損壞了,看看可不可以扶起資料庫來。


SYS@LEO1>droptable leo1 purge;                  刪除表


我們刪除第五組的2個成員


[oracle@leonarding1LEO1]$ rm -rf redo05.log


[oracle@leonarding1LEO1]$ cd disk2/


[oracle@leonarding1disk2]$ rm -rf redo05_b.log


SYS@LEO1>startup


ORACLE instancestarted.


Total SystemGlobal Area  471830528 bytes


Fixed Size                  2214456 bytes


Variable Size             171967944 bytes


DatabaseBuffers          289406976 bytes


Redo Buffers                8241152 bytes


Database mounted.


ORA-00313: openfailed for members of log group 5 of thread 1


ORA-00312: onlinelog 5 thread 1:


'/u02/app/oracle/oradata/LEO1/disk2/redo05_b.log'


ORA-27037: unableto obtain file status


Linux-x86_64Error: 2: No such file or directory


Additionalinformation: 3


ORA-00312: onlinelog 5 thread 1: '/u02/app/oracle/oradata/LEO1/redo05.log'


ORA-27037: unableto obtain file status


Linux-x86_64Error: 2: No such file or directory


Additionalinformation: 3


找不到第五組redo,實際上已經被我們刪除了,進入rman進行恢復操作


[oracle@leonarding1backup]$ rman target /


Recovery Manager:Release 11.2.0.1.0 - Production on Tue Apr 30 12:47:55 2013


Copyright (c)1982, 2009, Oracle and/or its affiliates. All rights reserved.


connected totarget database: LEO1 (DBID=1692458681, not open)


RMAN> restoredatabase;        


Starting restoreat 30-APR-13


using targetdatabase control file instead of recovery catalog


allocated channel:ORA_DISK_1


channelORA_DISK_1: SID=133 device type=DISK




channelORA_DISK_1: starting datafile backup set restore


channelORA_DISK_1: specifying datafile(s) to restore from backup set


channelORA_DISK_1: restoring datafile 00001 to/u02/app/oracle/oradata/LEO1/system01.dbf


channelORA_DISK_1: restoring datafile 00002 to/u02/app/oracle/oradata/LEO1/sysaux01.dbf


channelORA_DISK_1: restoring datafile 00003 to /u02/app/oracle/oradata/LEO1/undotbs01.dbf


channelORA_DISK_1: restoring datafile 00004 to/u02/app/oracle/oradata/LEO1/users01.dbf


channelORA_DISK_1: restoring datafile 00005 to /u02/app/oracle/oradata/LEO1/leo1.dbf


channelORA_DISK_1: reading from backup piece /u02/app/oracle/backup/full_bk1_0go8cdsg116.rmn


channelORA_DISK_1: piece handle=/u02/app/oracle/backup/full_bk1_0go8cdsg116.rmntag=TAG20130430T113336


channelORA_DISK_1: restored backup piece 1


channelORA_DISK_1: restore complete, elapsed time: 00:02:05


Finished restoreat 30-APR-13


我們只需恢復到7號歸檔日誌狀態點,就可以找回我們刪除了的leo1表


RMAN> recoverdatabase until sequence 7 thread 1;




Starting recoverat 30-APR-13


using channelORA_DISK_1




starting mediarecovery




channelORA_DISK_1: starting archived log restore to default destination


channel ORA_DISK_1:restoring archived log


archived logthread=1 sequence=6


channelORA_DISK_1: reading from backup piece/u02/app/oracle/backup/arch_bk1_0io8cdv7118.rmn


channelORA_DISK_1: piece handle=/u02/app/oracle/backup/arch_bk1_0io8cdv7118.rmntag=TAG20130430T113503


channelORA_DISK_1: restored backup piece 1


channelORA_DISK_1: restore complete, elapsed time: 00:00:01


archived log filename=/u02/app/oracle/archdata/1_6_814098124.dbf thread=1 sequence=6


media recoverycomplete, elapsed time: 00:00:01


Finished recoverat 30-APR-13


SYS@LEO1>alterdatabase open resetlogs;


Database altered.


resetlogs做的幾件事:


1)資料檔案頭scn號為準,同步控制檔案和線上日誌檔案scn號


2)重新建立redolog日誌(建立一個空日誌),重置為unused


3)重置歸檔日誌序號從1開始編碼


4)讓資料庫重新進入一個新的生命週期


SYS@LEO1>select* from leo1;                                   


NAME                        AGE RIQI


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


leonarding                   28 30-APR-13


sun_vn                       26 30-APR-13


tiger                        18 30-APR-13


這個表存在說明應用了 sequence為7的歸檔


小結:到此我們的系列恢復實驗完美完成,裡面闡述了各種不同環境下的恢復測試,簡明瞭重做日誌和歸檔日誌關係,在什麼情況下可以恢復到什麼級別,牢實掌握如上資訊,可以讓你遇事不慌張事半功倍。




控制檔案恢復,重做日誌恢復,完全恢復,不完全恢復,備份


2013.4.30
天津&spring
分享技術~成就夢想
Blog:




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

相關文章