eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 今天买入的几本图书 | Blog首页 | 高级复制的整体规划也很重要 »

使用RMAN进行基于时间点的不完全恢复
modb.pro

上周一个朋友的数据库发生误删除操作,请我帮忙进行恢复。
由于备份比较充分,所以只需要通过RMAN进行一个基于时间点(这个时间点需要根据故障时间进行判断选取)。

首先启动实例:

[oracle@stat ~]$ export ORACLE_SID=order
[oracle@stat ~]$ 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.

这个错误是由于时间日期格式设置的问题。

设置正确的时间格式,进行基于时间点的不完全恢复:

[oracle@stat ~]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
[oracle@stat ~]$ 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> ;

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进生产数据库,完成恢复。

在有了充分的备份的前提下,这样的恢复是非常容易的。
这个故事再次告诉我们:备份重于一切

-The End-


历史上的今天...
    >> 2019-09-17文章:
    >> 2012-09-17文章:
    >> 2010-09-17文章:
    >> 2009-09-17文章:
    >> 2008-09-17文章:
    >> 2007-09-17文章:

By eygle on 2006-09-17 21:05 | Comments (4) | Backup&Recovery | 902 |

4 Comments

为什么恢复后还要导入导出一次??

这是介质恢复,在另外的主机上完成的,恢复了一个数据表,倒回生产库。

为什么要先做控制文件的恢复?
不先做控制文件的恢复不行吗?我每次都没有做这一步!

不光要设置
$NLS_DATE_FORMAT=’YYYY-MM-DD:HH24:MI:SS’

而且要设置
$NLS_LANG=american


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com