巧用“rman“的TSPITR技术找回删除的表

backup as compressed backupset database include current controlfile plus all archivedlog format=......;SQL> select * from t;ID NAME BRITH---------- -------------------- --------------1 a 14-12月-06SQL> set time on12:29:30 SQL> SELECT TABLE_NAME FROM USER_TABLES;TABLE_NAME------------------------------TTT12:41:48 SQL> create table ttt(id int) tablespace test;表已创建。12:42:07 SQL> insert into ttt values(1);已创建 1 行。12:42:12 SQL> commit;提交完成。12:42:14 SQL> drop table t;表已删除。C:>RMAN TARGET SYS/SYSTEM@TEST AUXILIARY SYS/SYSTEM@AUXI CATALOG RMAN/RMAN@ORCL恢复治理器: Release 10.2.0.1.0 - Production on 星期五 12月 15 15:02:21 2006Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到目标数据库: TEST (DBID=1906627575)连接到恢复目录数据库已连接到辅助数据库: TEST (未装载)RMAN> RUN{2> allocate auxiliary channel c1 type disk;3> recover tablespace 'TEST' until time "to_date('12/15/2006 12:42:14' , 'mm/dd/yyyy hh24:mi:ss')" AUXILIARY DESTINATION 'E:oracleproduct10.2.0oradataauxi';4> release channel c1;5> }分配的通道: c1通道 c1: sid=156 devtype=DISK启动 recover 于 15-12月-06内存脚本的内容:{# set the until clauseset until time "to_date('12/15/2006 12:42:14' , 'mm/dd/yyyy hh24:mi:ss')";# restore the controlfilerestore clone controlfile;# mount the controlfilesql clone 'alter database mount clone database';# archive current online log for tspitr to a resent until timesql 'alter system archive log current';# avoid unnecessary autobackups for structural changes during TSPITRsql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';# resync catalog after controlfile restoreresync catalog;}正在执行内存脚本正在执行命令: SET until clause启动 restore 于 15-12月-06通道 c1: 正在开始恢复数据文件备份集通道 c1: 正在复原控制文件通道 c1: 正在读取备份段 E:RMANATEST_09I50SMU_1_1.BAK通道 c1: 已恢复备份段 1段句柄 = E:RMANATEST_09I50SMU_1_1.BAK 标记 = TAG20061215T124006通道 c1: 恢复完成, 用时: 00:00:03输出文件名=E:ORACLEPRODUCT10.2.0ORADATAAUXICONTROL01.CTL输出文件名=E:ORACLEPRODUCT10.2.0ORADATAAUXICONTROL02.CTL完成 restore 于 15-12月-06sql 语句: alter database mount clone databasesql 语句: alter system archive log currentsql 语句: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;正在启动全部恢复目录的 resync完成全部 resync内存脚本的内容:{# generated tablespace point-in-time recovery script# set the until clauseset until time "to_date('12/15/2006 12:42:14' ,'mm/dd/yyyy hh24:mi:ss')";plsql <<<-- tspitr_2declaresqlstatement varchar2(512);offline_not_needed exception;pragma exception_init(offline_not_needed, -01539);beginsqlstatement := 'alter tablespace '|| 'TEST' ||' offline for recover';krmicd.writeMsg(6162, sqlstatement);krmicd.execSql(sqlstatement);exceptionwhen offline_not_needed thennull;end; >>>;# set a destination filename for restoreset newname for datafile 1 to"E:ORACLEPRODUCT10.2.0ORADATAAUXISYSTEM01.DBF";# set a destination filename for restoreset newname for datafile 2 to"E:ORACLEPRODUCT10.2.0ORADATAAUXIUNDOTBS01.DBF";# set a destination tempfileset newname for tempfile 1 to"E:ORACLEPRODUCT10.2.0ORADATAAUXITEMP01.DBF";# set a destination tempfileset newname for tempfile 2 to"E:ORACLEPRODUCT10.2.0ORADATAAUXITMP.DBF";# set a destination filename for restoreset newname for datafile 4 to"E:ORACLEPRODUCT10.2.0ORADATATESTTEST.DBF";# rename all tempfilesswitch clone tempfile all;# restore the tablespaces in the recovery set plus the auxilliary tablespacesrestore clone datafile 1, 2, 4;switch clone datafile all;#online the datafiles restored or flippedsql clone "alter database datafile 1 online";#online the datafiles restored or flippedsql clone "alter database datafile 2 online";#online the datafiles restored or flippedsql clone "alter database datafile 4 online";# make the controlfile point at the restored datafiles, then recover themrecover clone database tablespace "TEST", "SYSTEM", "UNDOTBS1" delete archivelog;alter clone database open resetlogs;# PLUG HERE the creation of a temporary tablespace if export fails due to lack# of temporary space.# For example in Unix these two lines would do that:#sql clone "create tablespace aux_tspitr_tmp# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";}正在执行内存脚本正在执行命令: SET until clausesql 语句: alter tablespace TEST offline for recover正在执行命令: SET NEWNAME正在执行命令: SET NEWNAME正在执行命令: SET NEWNAME正在执行命令: SET NEWNAME正在执行命令: SET NEWNAME启动 restore 于 15-12月-06通道 c1: 正在开始恢复数据文件备份集通道 c1: 正在指定从备份集恢复的数据文件正将数据文件00001恢复到E:ORACLEPRODUCT10.2.0ORADATAAUXISYSTEM01.DBF正将数据文件00002恢复到E:ORACLEPRODUCT10.2.0ORADATAAUXIUNDOTBS01.DBF正将数据文件00004恢复到E:ORACLEPRODUCT10.2.0ORADATATESTTEST.DBF通道 c1: 正在读取备份段 E:RMANATEST_08I50SL7_1_1.BAK通道 c1: 已恢复备份段 1段句柄 = E:RMANATEST_08I50SL7_1_1.BAK 标记 = TAG20061215T124006通道 c1: 恢复完成, 用时: 00:01:09完成 restore 于 15-12月-06数据文件 4 已转换成数据文件副本输入数据文件副本 recid=9 stamp=609260651 文件名=E:ORACLEPRODUCT10.2.0ORADATATESTTEST.DBFsql 语句: alter database datafile 1 onlinesql 语句: alter database datafile 2 onlinesql 语句: alter database datafile 4 online启动 recover 于 15-12月-06正在开始介质的恢复存档日志线程 1 序列 116 已作为文件 E:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREATESTARCHIVELOG2006_12_15O1_MF_1_116_2R49V29R_.ARC 存在于磁盘上存档日志线程 1 序列 117 已作为文件 E:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREATESTARCHIVELOG2006_12_15O1_MF_1_117_2R4L4S2X_.ARC 存在于磁盘上存档日志文件名 =E:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREATESTARCHIVELOG2006_12_15O1_MF_1_116_2R49V29R_.ARC 线程 =1 序列 =116存档日志文件名 =E:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREATESTARCHIVELOG2006_12_15O1_MF_1_117_2R4L4S2X_.ARC 线程 =1 序列 =117介质恢复完成, 用时: 00:00:02完成 recover 于 15-12月-06数据库已打开内存脚本的内容:{# export the tablespaces in the recovery sethost 'exp userid ="SYS/SYSTEM@AUXI as sysdba" point_in_time_recover=y tablespaces=TEST file=tspitr_a.dmp';# shutdown clone before importshutdown clone immediate# import the tablespaces in the recovery sethost 'imp userid ="SYS/SYSTEM@TEST as sysdba" point_in_time_recover=y file=tspitr_a.dmp';# online/offline the tablespace importedsql "alter tablespace TEST online";sql "alter tablespace TEST offline";# enable autobackups in case user does open resetlogs from RMAN after TSPITRsql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';# resync catalog after tspitr finishedresync catalog;}正在执行内存脚本Export: Release 10.2.0.1.0 - Production on 星期五 12月 15 15:04:34 2006Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集服务器使用 ZHS16CGB231280 字符集 (可能的字符集转换)注: 将不导出表数据 (行)即将导出表空间时间点恢复对象...对于表空间 TEST.... 正在导出簇定义. 正在导出表定义. . 正在导出表 TTEXP-00091: 正在导出有问题的统计信息。EXP-00008: 碰到 ORACLE 错误 6552ORA-06552: PL/SQL: Compilation unit analysis terminatedORA-06553: PLS-553: 无法识别字符集名称. . 正在导出表 TEXP-00091: 正在导出有问题的统计信息。EXP-00008: 碰到 ORACLE 错误 6552ORA-06552: PL/SQL: Compilation unit analysis terminatedORA-06553: PLS-553: 无法识别字符集名称. . 正在导出表 TTT. 正在导出引用完整性约束条件. 正在导出触发器. 终止时间点恢复导出成功终止, 但出现警告。主机命令完成数据库已关闭数据库已卸载Oracle 实例已关闭Import: Release 10.2.0.1.0 - Production on 星期五 12月 15 15:05:01 2006Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options经由常规路径由 EXPORT:V10.02.01 创建的导出文件即将导入表空间时间点恢复对象...已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入导入服务器使用 ZHS16CGB231280 字符集 (可能的字符集转换). 正在将 SYS 的对象导入到 SYS. 正在将 XYS 的对象导入到 XYS. . 正在导入表 "TT". . 正在导入表 "T". . 正在导入表 "TTT". 正在将 SYS 的对象导入到 SYS成功终止导入, 没有出现警告。主机命令完成sql 语句: alter tablespace TEST onlinesql 语句: alter tablespace TEST offlinesql 语句: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;正在启动全部恢复目录的 resync完成全部 resync已删除辅助实例文件 E:ORACLEPRODUCT10.2.0ORADATAAUXICONTROL01.CTL已删除辅助实例文件 E:ORACLEPRODUCT10.2.0ORADATAAUXICONTROL02.CTL已删除辅助实例文件 E:ORACLEPRODUCT10.2.0ORADATAAUXISYSTEM01.DBF已删除辅助实例文件 E:ORACLEPRODUCT10.2.0ORADATAAUXIUNDOTBS01.DBF已删除辅助实例文件 E:ORACLEPRODUCT10.2.0ORADATAAUXITEMP01.DBF已删除辅助实例文件 E:ORACLEPRODUCT10.2.0ORADATAAUXITMP.DBF完成 recover 于 15-12月-06--====================================14:48:22 SQL> select tablespace_name , status from dba_tablespaces;TABLESPACE_NAME STATUS------------------------------ ------------------SYSTEM ONLINEUNDOTBS1 ONLINESYSAUX ONLINETEMP ONLINETEST OFFLINETMP ONLINETEST1 ONLINE已选择7行。15:06:14 SQL> alter tablespace test online;表空间已更改。15:06:28 SQL> show userUSER 为 "XYS"15:06:32 SQL> select * from t;ID NAME BRITH---------- -------------- --------1 a 2006/12/14 08:44:24