rman 時間點的不完全恢複

上周一個朋友的資料庫發生誤删除操作，請我幫忙進行恢複。

由于備份比較充分，是以隻需要通過RMAN進行一個基于時間點（這個時間點需要根據故障時間進行判斷選取）。

首先啟動執行個體:

[[email protected] ~]$ export ORACLE_SID=order

[[email protected] ~]$ rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Thu Sep 14 22:43:50 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database (not started)

RMAN> set DBID=1341966532

executing command: SET DBID

RMAN> startup nomount;

Oracle instance started

Total System Global Area 2483027968 bytes

Fixed Size 1262344 bytes

Variable Size 654314744 bytes

Database Buffers 1811939328 bytes

Redo Buffers 15511552 bytes

恢複資料檔案并加載(mount)資料庫:

RMAN> restore controlfile from autobackup;

Starting restore at 14-SEP-06

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=541 devtype=DISK

channel ORA_DISK_1: looking for autobackup on day: 20060914

channel ORA_DISK_1: autobackup found: c-1341966532-20060914-02

channel ORA_DISK_1: control file restore from autobackup complete

output filename=/oradata/controlfile/o1_mf_28spy45z_.ctl

output filename=/oradata/controlfile/o2_mf_28spy45z_.ctl

Finished restore at 14-SEP-06

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

Restore資料庫:

RMAN> restore database;

Starting restore at 14-SEP-06

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=541 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /oradata/datafile/o1_mf_system_28spy7kl_.dbf

restoring datafile 00002 to /oradata/datafile/o1_mf_undotbs1_28spykdh_.dbf

restoring datafile 00003 to /oradata/datafile/o1_mf_sysaux_28spyo9s_.dbf

restoring datafile 00004 to /oradata/datafile/o1_mf_users_28spyvm8_.dbf

restoring datafile 00005 to /oradata/datafile/o1_mf_vascms_2c444bhj_.dbf

restoring datafile 00006 to /oradata/datafile/o1_mf_wapgame_2c44gz55_.dbf

restoring datafile 00007 to /oradata/datafile/o1_mf_vascms_2c4kn0b2_.dbf

channel ORA_DISK_1: reading from backup piece /data3/ordrbak/full_ORDER_20060913_169

channel ORA_DISK_1: restored backup piece 1

piece handle=/data3/ordrbak/orderfullback_ORDER_20060913_169 tag=order

channel ORA_DISK_1: restore complete, elapsed time: 00:03:06

Finished restore at 14-SEP-06

進行基于時間點的恢複：

RMAN> recover database until time '2006-09-14 19:00:00'

2> ;

Starting recover at 14-SEP-06

using channel ORA_DISK_1

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

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

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

RMAN-03002: failure of recover command at 09/14/2006 22:49:54

ORA-01861: literal does not match format string

RMAN> exit

Recovery Manager complete.

這個錯誤是由于時間日期格式設定的問題。

設定正确的時間格式，進行基于時間點的不完全恢複:

[[email protected] ~]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'

[[email protected] ~]$ rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Thu Sep 14 22:50:22 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: order (DBID=1341966532, not open)

RMAN> recover database until time '2006-09-14 19:00:00'

2> ;

(有次居然EXPORT之後仍報上面字元格式不正确的錯誤，後采用如下方法解決：

recover database until time "to_date('2009/06/22 15:28:00','yyyy/mm/dd hh24:mi:ss')";

)

Starting recover at 2006-09-14 22:50:26

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=544 devtype=DISK

starting media recovery

archive log thread 1 sequence 303 is already on disk as file

/oradata/archive/1_303_592917188.dbf

archive log thread 1 sequence 304 is already on disk as file

/oradata/archive/1_304_592917188.dbf

channel ORA_DISK_1: starting archive log restore to default destination

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=299

channel ORA_DISK_1: reading from backup piece /data3/ordrbak/arch_order_20060913_171

channel ORA_DISK_1: restored backup piece 1

piece handle=/data3/ordrbak/orderarch_order_20060913_171 tag=order

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

archive log filename=/oradata/archive/1_299_592917188.dbf thread=1 sequence=299

channel ORA_DISK_1: starting archive log restore to default destination

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=300

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=301

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=302

channel ORA_DISK_1: reading from backup piece /data3/ordrbak/arch_order_20060914_173

channel ORA_DISK_1: restored backup piece 1

piece handle=/data3/ordrbak/orderarch_order_20060914_173 tag=TAG20060914T033004

channel ORA_DISK_1: restore complete, elapsed time: 00:00:08

archive log filename=/oradata/archive/1_300_592917188.dbf thread=1 sequence=300

archive log filename=/oradata/archive/1_301_592917188.dbf thread=1 sequence=301

archive log filename=/oradata/archive/1_302_592917188.dbf thread=1 sequence=302

archive log filename=/oradata/archive/1_303_592917188.dbf thread=1 sequence=303

archive log filename=/oradata/archive/1_304_592917188.dbf thread=1 sequence=304

media recovery complete, elapsed time: 00:00:57

Finished recover at 2006-09-14 22:51:39

Resetlogs打開資料庫:

RMAN> alter database open resetlogs;

database opened

RMAN>

此時可以檢查資料的正确性，如果無誤就可以通過exp導出資料，再imp進生産資料庫，完成恢複。

在有了充分的備份的前提下，這樣的恢複是非常容易的。

這個故事再次告訴我們：備份重于一切。

from:http://www.eygle.com/archives/2006/09/rman_until_time_recovery.html