一次truncate table 後的資料恢復[轉帖]
使用壞境： oracle11.2+rhce5.5 RAC,歸檔模式,資料庫有1TB容量。
在配置流環境時，因為一個誤操作，刪除了hz使用者下的幾個表。在這個資料庫上還部署了其它幾個使用者，停機恢復資料顯然是不可能的，分析情況後可以使用下面兩種方式來恢復資料。
1、把當前庫的備份檔案和歸檔日誌傳到測試機上進行時間點不完全恢復，然後exp匯出需要誤刪除的資料。
2、使用duplicate表空間的方式生成輔助資料庫，在新庫中匯出需要的資料。
1、複製資料檔案和2節點的歸檔日誌檔案。由於故障庫的備份是nfs掛載到備份機上的，省去了複製資料檔案的時間，只需要把2節點下的archive日誌檔案複製到測試庫上就行。
2、建立新庫的pfile引數檔案，複製故障庫的spfile檔案，修改引數並刪除有關rac的不需要引數資訊。
3、建立新庫的密碼檔案.
orapwd file=/u02/app/oracle/product/11.2.0/db1/dbs/orapwklir passord=oracle entries=10 force=y
4、建立新庫的dump目錄。
5、啟動新庫到nomount下。
[oracle@kms2 dbs]$ export ORACLE_SID=klir
[oracle@kms2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 18 09:56:35 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/u02/pfile_klir.ora'
6、從備份集中恢復控制檔案,並啟動到mount狀態下。
RMAN> restore controlfile from /orabk/ctl_c-949039848-20101116-00.ctl;
RMAN> alter database mount;
7、恢復表空間。因為只是一個表空間下的一些表被刪除了，朋友建議我只恢復有問題的表空間就可以了，當然system，sysaux,undo這些基本的表空間是要恢復的。
RMAN> restore tablespace system;
Starting restore at 16-NOV-10
Starting implicit crosscheck backup at 16-NOV-10
allocated channel: ORA_DISK_1
Crosschecked 198 objects
Finished implicit crosscheck backup at 16-NOV-10
Starting implicit crosscheck copy at 16-NOV-10
using channel ORA_DISK_1
Finished implicit crosscheck copy at 16-NOV-10
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DG1/system01.dbf
channel ORA_DISK_1: reading from backup piece /orabk/db_KLIR_13771_1_1_735012733.dbf
channel ORA_DISK_1: piece handle=/orabk/db_KLIR_13771_1_1_735012733.dbf tag=TAG20101114T021212
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:09:15
Finished restore at 16-NOV-10
RMAN> restore tablespace sysaux;
Starting restore at 16-NOV-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to +DG1/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /orabk/db_KLIR_13770_1_1_735012528.dbf
channel ORA_DISK_1: piece handle=/orabk/db_KLIR_13770_1_1_735012528.dbf tag=TAG20101114T021212
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:08:50
Finished restore at 16-NOV-10
RMAN> restore tablespace HZDATATBS;
Starting restore at 16-NOV-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00011 to +DG1/hzdata01.dbf
channel ORA_DISK_1: reading from backup piece /orabk/db_KLIR_13770_1_1_735012528.dbf
channel ORA_DISK_1: piece handle=/orabk/db_KLIR_13770_1_1_735012528.dbf tag=TAG20101114T021212
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:16:35
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00154 to +DG1/hzdata02.dbf
channel ORA_DISK_1: reading from backup piece /orabk/db_KLIR_13938_1_1_735186686.dbf
channel ORA_DISK_1: piece handle=/orabk/db_KLIR_13938_1_1_735186686.dbf tag=TAG20101116T023123
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:05
Finished restore at 16-NOV-10
RMAN> restore tablespace hzindtbs;
Starting restore at 16-NOV-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00017 to +DG1/hzind02.dbf
channel ORA_DISK_1: restoring datafile 00021 to +DG1/hzind03.dbf
channel ORA_DISK_1: reading from backup piece /orabk/db_KLIR_13773_1_1_735012734.dbf
channel ORA_DISK_1: piece handle=/orabk/db_KLIR_13773_1_1_735012734.dbf tag=TAG20101114T021212
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:13:45
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00012 to +DG1/hzind01.dbf
channel ORA_DISK_1: reading from backup piece /orabk/db_KLIR_13771_1_1_735012733.dbf
channel ORA_DISK_1: piece handle=/orabk/db_KLIR_13771_1_1_735012733.dbf tag=TAG20101114T021212
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:14:46
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00153 to +DG1/hzind04.dbf
channel ORA_DISK_1: reading from backup piece /orabk/db_KLIR_13937_1_1_735186478.dbf
channel ORA_DISK_1: piece handle=/orabk/db_KLIR_13937_1_1_735186478.dbf tag=TAG20101116T023123
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:15
Finished restore at 17-NOV-10
RMAN> restore tablespace undotbs2;
Starting restore at 17-NOV-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to +DG1/undotbs201.dbf
channel ORA_DISK_1: reading from backup piece /orabk/db_KLIR_13770_1_1_735012528.dbf
channel ORA_DISK_1: piece handle=/orabk/db_KLIR_13770_1_1_735012528.dbf tag=TAG20101114T021212
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:46
Finished restore at 17-NOV-10
RMAN> restore tablespace undotbs1;
Starting restore at 17-NOV-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to +DG1/undotbs101.dbf
channel ORA_DISK_1: reading from backup piece /orabk/db_KLIR_13773_1_1_735012734.dbf
channel ORA_DISK_1: piece handle=/orabk/db_KLIR_13773_1_1_735012734.dbf tag=TAG20101114T021212
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:11:05
Finished restore at 17-NOV-10
8.Offline 其它不用的表空間。
SQL> alter database datafile 5,7 offline drop;
Database altered.
SQL> alter database datafile 13,14,15,16 offline drop;
Database altered.
9、不完全恢復資料庫。
SQL> recover database until time '2010-11-16 16:25:00' using backup controlfile;
ORA-00279: change 2602636589 generated at 11/14/2010 13:16:46 needed for thread
1
ORA-00289: suggestion : +ARCHIVE
ORA-00280: change 2602636589 for thread 1 is in sequence #5709
Specify log: {
thread_1_seq_5709.528.735293603
ORA-00308: cannot open archived log 'thread_1_seq_5709.528.735293603'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Specify log: {
/orabk/aaa/thread_1_seq_5709.528.735293603
ORA-00279: change 2602636589 generated at 11/14/2010 06:43:25 needed for thread
2
ORA-00289: suggestion : +ARCHIVE
ORA-00280: change 2602636589 for thread 2 is in sequence #6914
ORA-00289: suggestion : +ARCHIVE
ORA-00280: change 2609694870 for thread 1 is in sequence #5824
ORA-00278: log file '/orabk/aaa/arch11/1_5823_719402218.dbf' no longer needed
for this recovery
Specify log: {
/orabk/aaa/arch11/1_5824_719402218.dbf
ORA-00279: change 2609942602 generated at 11/16/2010 16:07:30 needed for thread
2
ORA-00289: suggestion : +ARCHIVE
ORA-00280: change 2609942602 for thread 2 is in sequence #6990
ORA-00278: log file '/orabk/aaa/arch22/2_6989_719402218.dbf' no longer needed
for this recovery
........................省略中間部分
Specify log: {
/orabk/aaa/arch22/2_6990_719402218.dbf
ORA-00279: change 2609955345 generated at 11/16/2010 16:08:25 needed for thread
1
ORA-00289: suggestion : +ARCHIVE
ORA-00280: change 2609955345 for thread 1 is in sequence #5825
ORA-00278: log file '/orabk/aaa/arch11/1_5824_719402218.dbf' no longer needed
for this recovery
Specify log: {
/orabk/aaa/arch11/1_5825_719402218.dbf
Log applied.
Media recovery complete.
10、開啟資料庫。
SQL> alter database open resetlogs;
經過幾分鐘的等待後，資料成功開啟，登入資料庫查詢需要的檔案都已經恢復出來了。謝天謝地！
這次資料恢復操作，用了近1天的時間，當時發生問題時我都快搞懵了，幸好在朋友的幫助下順利完資料恢復，在這裡要非常感謝他們！順便記錄下恢復期間遇到的幾個問題。
<1>、有些日誌檔案在故障庫的日誌目錄裡是沒有的，需要從備份中還原，不同節點的日誌檔案恢復時要寫上thread=*命令，例如:
RMAN> restore archivelog from logseq 6914 until logseq 6915 thread=2;
Starting restore at 17-NOV-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=182 instance=klir2 device type=DISK
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=6914
channel ORA_DISK_1: reading from backup piece /orabk/arc_KLIR_13864_1_1_735184831.dbf
channel ORA_DISK_1: piece handle=/orabk/arc_KLIR_13864_1_1_735184831.dbf tag=TAG20101116T020016
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=6915
channel ORA_DISK_1: reading from backup piece /orabk/arc_KLIR_13866_1_1_735184956.dbf
channel ORA_DISK_1: piece handle=/orabk/arc_KLIR_13866_1_1_735184956.dbf tag=TAG20101116T020016
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 17-NOV-10
否則會提示以下錯誤，
RMAN> restore archivelog from logseq 6914 until logseq 6915;
Starting restore at 17-NOV-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=182 instance=klir2 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/17/2010 08:15:07
RMAN-20242: specification does not match any archived log in the repository
<2>、故障庫恢復出日誌檔案後，因為在asm磁碟組裡，要進入asmcmd裡複製出日誌檔案。
ASMCMD> cp +ARCHIVE/klir/archivelog/2010_11_17/thread_1_seq_5705.369.735274973 /orabk/aaa/
<3>、恢復命令要寫對，剛開始寫的幾種命令都不正確。
SQL> recover database until time '2010-11-16 16:25:00'
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database until time to_date('2010-11-16 16:25:00','yyyy-mm-dd HH24:MI:SS');
ORA-00285: TIME not given as a string constant
<4>、對於這種只是丟失部分表的情況，就可以只還原需要的表空間來開啟資料庫，可以節省大量的恢復時間。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7490392/viewspace-1041731/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- truncate table 誤刪除資料後的恢復
- Oracle中truncate table後的資料恢復(Oracle資料恢復工具-ODU)Oracle資料恢復
- oracle logminer恢復truncate table的資料Oracle
- 恢復被執行truncate table的表資料
- 【資料庫資料恢復】Oracle資料庫誤truncate table的資料恢復案例資料庫資料恢復Oracle
- 【資料庫資料恢復】誤truncate table的Oracle資料庫資料恢復方案資料庫資料恢復Oracle
- 【資料庫資料恢復】oracle資料庫誤truncate table怎麼恢復資料？資料庫資料恢復Oracle
- TRUNCATE資料恢復資料恢復
- 【北亞資料恢復】oracle資料庫執行truncate table命令怎麼恢復資料？資料恢復Oracle資料庫
- 【資料庫資料恢復】如何恢復Oracle資料庫truncate表的資料資料庫資料恢復Oracle
- 使用ODU恢復被truncate表的資料
- 使用hellodba的工具恢復truncate表的資料
- truncate表後恢復方法總結
- 使用ODU恢復oracle被truncate的表資料Oracle
- 聯機日誌檔案損壞後的恢復方法[轉帖]
- 記一次 oracle 資料庫在當機後的恢復Oracle資料庫
- 記一次資料恢復資料恢復
- 一次客戶資料庫恢復的過程 [轉]資料庫
- 一次特殊的資料庫恢復資料庫
- 恢復outlook的資料(轉)
- 資料庫的一次資料恢復過程資料庫資料恢復
- Sql server日誌損壞後的資料恢復(轉)SQLServer資料恢復
- Oracle Truncate表恢復(ODU)Oracle
- 【北亞資料恢復】誤操作導致雲伺服器表被truncate，表內資料被delete的資料恢復資料恢復伺服器delete
- MySQL資料災難挽救之truncate tableMySql
- open resetlogs後資料恢復資料恢復
- 恢復資料,資料塊恢復
- (轉)sqlserver 資料恢復方法SQLServer資料恢復
- chkdsk 後資料丟失的恢復方法
- Mongodb資料庫誤刪後的恢復MongoDB資料庫
- git reset --hard 操作後的資料恢復Git資料恢復
- 一次恢復oracle的嘗試(轉)Oracle
- 記一次刪庫到資料恢復資料恢復
- 一次Oracle資料庫恢復過程Oracle資料庫
- 資料恢復：AMDU資料抽取恢復資料恢復
- 一次客戶資料庫恢復的過程資料庫
- MYSQL叢集的備份與恢復最終版本(轉帖)MySql
- drop table和truncate table的區別