flashbackdatabase恢复删除的表空间

《flashbackdatabase恢复删除的表空间》由会员分享，可在线阅读，更多相关《flashbackdatabase恢复删除的表空间（8页珍藏版）》请在装配图网上搜索。

1、flashbackdatabase恢复删除的表空间2015-03-060个评论 来源：Vincent的专栏收藏心我要投稿当误删除表空间，且没有备份，开启归档和闪回，可以使用flashback database 恢复表空间1. 数据库版本sysTEST select * from v$version;BANNEROracle Dat abase 11g Ent erprise Edition Release 11.2.0.3.0 - 64bi tProductionPL/SQL Release 11.2.0.3.0 - Produc tionCORE 11.2.0.3.0 Produc tion

2、TNS for Linux: Version 11.2.0.3.0 - Produc tionNLSRTL Version 11.2.0.3.0 - Produc tion2. 要恢复的数据sysTEST select count(*) from test .a;COUNT(*)1 132383. 当前的scnsysTEST select current_scn,to_char(sysdate, yyyy-mm-dd hh24:mi:ss) from v$database;CURRENT_SCN TO_CHAR(SYSDATE, YY543532 2015-03-05 16:17:324. 模

3、拟误删除表空间sysTEST drop tablespace test including contents and datafiles;Tablespace dropped.sysTEST select coun t(*) from t es t.a; -已经查找不到表了 selec t cou nt(*) from test.a*ERROR at line 1:0RA-00942: table or view does not exist5. 关闭数据库并启动到mount;sysTEST shutdown immediate;Database closed.Database dismoun

4、ted.ORACLE instance shut down.sysTEST startup mount;ORACLE instance started.Total System Global Area 1068937216 bytesFixed Size 2235208 bytesVariable Size 645924024 bytesDatabase Buffers 415236096 bytesRedo Buffers 5541888 bytesDatabase mounted.6闪回数据库sysTEST flashback database to scn 543532;Flashbac

5、k complete.sysTEST select file#,name,status from v$datafile;?STATUS1 FILE# NAME1 1/home/u01/app/oracle/oradata/test/system01.dbf2 2/home/u01/app/oracle/oradata/test/sysaux01.dbf3 3/home/u01/app/oracle/oradata/test/undotbs01.dbf4 4/home/u01/app/oracle/oradata/test/users01.dbfSYSTEMRECOVERRECOVERRECOV

6、ERSTATUSSYSTEMRECOVERRECOVERRECOVERRECOVERRECOVERRECOVERRECOVERRECOVERCHANGE# TIME5 5 /home/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00005 RECOVER6 6/home/u01/app/oracle/oradata/test/example.dbfRECOVER7 7/home/u01/app/oracle/oradata/test/indx.dbfRECOVER8 8/home/u01/app/oracle/oradata/test/tool.

7、dbfRECOVER9 9/home/uOl/app/oracle/oradata/test/oltp.dbfRECOVERflashback database会恢复删除的表空间，但是数据文件需要自己指定7.创建数据文件sysTEST alter database create datafile 5 as/home/u01/app/oracle/oradata/test/test.dbf;Database altered.sysTEST select file#,name,status from v$datafile;FILE# NAME1 l/home/uOl/app/oracle/orad

8、ata/test/systemOl.dbf2 2/home/uOl/app/oracle/oradata/test/sysauxOl.dbf3 3/home/uOl/app/oracle/oradata/test/undotbsOl.dbf4 4/home/uOl/app/oracle/oradata/test/usersOl.dbf5 5/home/uOl/app/oracle/oradata/test/test.dbf6 6/home/uOl/app/oracle/oradata/test/example.dbf7 7/home/uOl/app/oracle/oradata/test/in

9、dx.dbf8 8/home/uOl/app/oracle/oradata/test/tool.dbf9 9/home/uOl/app/oracle/oradata/test/oltp.dbf9 rows selected.&数据文件处于offline状态，修改为online；sysTEST select * from v$recover_file;?1 FILE# ONLINE ONLINE ERROR11 ONLINEONLINE543533 O5-MAR-1522 ONLINEONLINE543533 O5-MAR-1533 ONLINEONLINE543533 O5-MAR-1544

10、ONLINEONLINE543533 O5-MAR-1555 OFFLINEOFFLINE UNKNOWN ERROR54O888 O5-MAR-1566ONLINEONLINE54353305-MAR-1577ONLINEONLINE54353305-MAR-1588ONLINEONLINE54353305-MAR-1599ONLINEONLINEsysTEST alter database datafile 5 online;Database altered.9.恢复数据库，使用rman until scnRMAN recover database until sen 543532;Sta

11、rting recover at 05MART5using channel ORA_DISK_1RMAN-00571: =RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =RMAN-00571: =RMAN-03002: failure of recover command at 03/05/2015 16:49:01RMAN-06556: datafile 1 must be restored from backup older than SCN 543532- datafile 1 当前的 scn 是 543533 晚于 543532, until sc

12、n 改为 543533sysTEST select file#,name,status,checkpoint_change# fromv$datafile;?1 FILE# NAMESTATUS123456789123456789/home/u01/app/oracle/oradata/test/system01.dbf /home/u01/app/oracle/oradata/test/sysaux01.dbf /home/u01/app/oracle/oradata/test/undotbs01.dbf /home/u01/app/oracle/oradata/test/users01.d

13、bf /home/u01/app/oracle/oradata/test/test.dbf /home/u01/app/oracle/oradata/test/example.dbf /home/u01/app/oracle/oradata/test/indx.dbf /home/u01/app/oracle/oradata/test/tool.dbf /home/u01/app/oracle/oradata/test/oltp.dbfSYSTEMRECOVERRECOVERRECOVERRECOVERRECOVERRECOVERRECOVERRECOVERRMAN recover datab

14、ase until scn 543533;Starting recover at 05MART5 using channel ORA_DISK_1 starting media recoveryarchived log for thread 1 with sequence 3 is already on disk as file/home/u01/app/oracle/archivelog/l_3_873486141.dbfarchived log for thread 1 with sequence 4 is already on disk as file/home/u01/app/orac

15、le/archivelog/l_4_873486141.dbfarchived log for thread 1 with sequence 5 is already on disk as file/home/u01/app/oracle/archivelog/l_5_873486141.dbfarchived log for thread 1 with sequence 1 is already on disk as file/home/uOl/app/oracle/oradata/test/redoOl.logarchived log file name二/home/u01/app/ora

16、cle/archivelog/l_3_873486141.dbf thread=1 sequence=3archived log file name二/home/u01/app/oracle/archivelog/l_4_873486141.dbf thread=1 sequence=4archived log file name二/home/u01/app/oracle/archivelog/l_5_873486141.dbf thread=1 sequence=5media recovery complete, elapsed time: 00:00:01Finished recover at 05MAR1510.打开数据库，并验证sysTEST alter database open resetlogs;Database altered.sysTESTselect count(*) from test .a;