朋友用到的一次不完全恢復案例與大家分享

  前2天因為一次錯誤的truncate 操作，刪除了hz使用者下的幾個表，費了1天多時間才恢復出來，在這裡記錄下恢復過程和這個深刻的教訓。

  使用壞境： oracle11.2+rhce5.5 RAC,歸檔模式,資料庫有1TB容量。

   在配置流環境時，因為一個誤操作，刪除了hz使用者下的幾個表。在這個資料庫上還部署了其它幾個使用者，停機恢復資料顯然是不可能的，分析情況後可以使用下面兩種方式來恢復資料。

1、把當前庫的備份檔案和歸檔日誌傳到測試機上進行時間點不完全恢復，然後exp匯出需要誤刪除的資料。

2、使用duplicate表空間的方式生成輔助資料庫，在新庫中匯出需要的資料。

      參考：http://space.itpub.net/7199859/viewspace-79054

 

這次的資料恢復操作我我使用第一種方式，以下是操作步驟：

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這些基本的表空間是要恢復的。

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

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

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

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

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

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: {=suggested | filename | AUTO | CANCEL}
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: {=suggested | filename | AUTO | CANCEL}
/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: {=suggested | filename | AUTO | CANCEL}
/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: {=suggested | filename | AUTO | CANCEL}
/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: {=suggested | filename | AUTO | CANCEL}
/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>、對於這種只是丟失部分表的情況，就可以只還原需要的表空間來開啟資料庫，可以節省大量的恢復時間。