一次truncate table 后的数据恢复记录

这次的数据恢复操作我我使用第一种方式，以下是操作步骤：

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: {<RET>=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: {<RET>=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: {<RET>=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: {<RET>=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: {<RET>=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>、对于这种只是丢失部分表的情况，就可以只还原需要的表空间来打开数据库，可以节省大量的恢复时间。

 

本文转自 gjm008 51CTO博客，原文链接：http://blog.51cto.com/gaoshan/426326，如需转载请自行联系原作者