www.Orasql.Com
 
用户名: 密码:  验证码: 注册|找回密码 oracleawen's blog  
 
         当前位置 >> 技术论坛  发表帖子     论坛版块     查找帖子
一次truncate table 后的数据恢复-技术论坛||Oracle高手之路—Orasql.Com!
            一次truncate table 后的数据恢复
 
发贴人:oracle_awen
oracleawen@gmail.com448315233127.0.0.1
用户级别:管理员
发贴总数:1245
注册时间:2011-05-05 09:23:03
2011-06-01 08:15:45
一次truncate table 后的数据恢复 使用坏境 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 sqlplus release 11.2.0.1.0 production on thu nov 18 095635 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 000915 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 000850 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 001635 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 000205 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 001345 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 001446 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 000315 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 000446 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 001105 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 162500' using backup controlfile; ora-00279 change 2602636589 generated at 11/14/2010 131646 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 064325 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 160730 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 160825 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 000007 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 000007 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 081507 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 162500' 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 162500','yyyy-mm-dd hh24miss'); ora-00285 time not given as a string constant <4>、对于这种只是丢失部分表的情况,就可以只还原需要的表空间来打开数据库,可以节省大量的恢复时间。 [ 本帖最后由 gjm008 于 2010-11-18 1037 编辑 ]

      
 
  
 Orasql.Com|E-Mail:oracleawen@gmail.com|Phone:15271942915|Copyright © 2011|All Rights Reserved|