当误删除表空间，且没有备份，开启归档和闪回，可以使用flashback database恢复表空间
1.数据库版本 sys@TEST> select * from v$version;
BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production PL/SQL Release 11.2.0.3.0 – Production CORE 11.2.0.3.0Production TNS for Linux: Version 11.2.0.3.0 – Production NLSRTL Version 11.2.0.3.0 – Production
2.要恢复的数据 sys@TEST> select count(*) from test.a;
COUNT(*) 13238
3.当前的scn sys@TEST> select current_scn,to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) from v$database;
CURRENT_SCN TO_CHAR(SYSDATE,’YY
543532 2015-03-05 16:17:32
4.模拟误删除表空间
sys@TEST> drop tablespace test including contents and datafiles;
Tablespace dropped.
sys@TEST> select count(*) from test.a; –已经查找不到表了 select count(*) from test.a * ERROR at line 1: ORA-00942: table or view does not exist
5.关闭数据库并启动到mount; sys@TEST> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. sys@TEST> startup mount; ORACLE instance started.
Total System Global Area 1068937216 bytes Fixed Size2235208 bytes Variable Size645924024 bytes Database Buffers415236096 bytes Redo Buffers5541888 bytes Database mounted.
6.闪回数据库 sys@TEST> flashback database to scn ‘543532’;
Flashback complete.
sys@TEST> select file#,name,status from v$datafile;
FILE# NAMESTATUS1 /home/u01/app/oracle/oradata/test/system01.dbfSYSTEM2 /home/u01/app/oracle/oradata/test/sysaux01.dbfRECOVER3 /home/u01/app/oracle/oradata/test/undotbs01.dbfRECOVER4 /home/u01/app/oracle/oradata/test/users01.dbfRECOVER5 /home/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00005 RECOVER6 /home/u01/app/oracle/oradata/test/example.dbfRECOVER7 /home/u01/app/oracle/oradata/test/indx.dbfRECOVER8 /home/u01/app/oracle/oradata/test/tool.dbfRECOVER9 /home/u01/app/oracle/oradata/test/oltp.dbfRECOVER
flashback database 会恢复删除的表空间，但是数据文件需要自己指定
7.创建数据文件
sys@TEST> alter database create datafile 5 as ‘/home/u01/app/oracle/oradata/test/test.dbf’;
Database altered.
sys@TEST> select file#,name,status from v$datafile;
FILE# NAMESTATUS1 /home/u01/app/oracle/oradata/test/system01.dbfSYSTEM2 /home/u01/app/oracle/oradata/test/sysaux01.dbfRECOVER3 /home/u01/app/oracle/oradata/test/undotbs01.dbfRECOVER4 /home/u01/app/oracle/oradata/test/users01.dbfRECOVER5 /home/u01/app/oracle/oradata/test/test.dbfRECOVER6 /home/u01/app/oracle/oradata/test/example.dbfRECOVER7 /home/u01/app/oracle/oradata/test/indx.dbfRECOVER8 /home/u01/app/oracle/oradata/test/tool.dbfRECOVER9 /home/u01/app/oracle/oradata/test/oltp.dbfRECOVER
9 rows selected.
8.数据文件处于offline状态，修改为online； sys@TEST> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERRORCHANGE# TIME1 ONLINE ONLINE543533 05-MAR-152 ONLINE ONLINE543533 05-MAR-153 ONLINE ONLINE543533 05-MAR-154 ONLINE ONLINE543533 05-MAR-155 OFFLINE OFFLINE UNKNOWN ERROR540888 05-MAR-156 ONLINE ONLINE543533 05-MAR-157 ONLINE ONLINE543533 05-MAR-158 ONLINE ONLINE543533 05-MAR-159 ONLINE ONLINE
sys@TEST> alter database datafile 5 online;
Database altered.
9.恢复数据库，使用rman until scn RMAN> recover database until scn 543532;
Starting recover at 05-MAR-15 using channel ORA_DISK_1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 03/05/2015 16:49:01 RMAN-06556: datafile 1 must be restored from backup older than SCN 543532 –datafile 1 当前的scn是543533晚于543532，until scn 改为543533
sys@TEST> select file#,name,status,checkpoint_change# from v$datafile;
FILE# NAMESTATUS CHECKPOINT_CHANGE#1 /home/u01/app/oracle/oradata/test/system01.dbfSYSTEM5435332 /home/u01/app/oracle/oradata/test/sysaux01.dbfRECOVER5435333 /home/u01/app/oracle/oradata/test/undotbs01.dbfRECOVER5435334 /home/u01/app/oracle/oradata/test/users01.dbfRECOVER5435335 /home/u01/app/oracle/oradata/test/test.dbfRECOVER5428636 /home/u01/app/oracle/oradata/test/example.dbfRECOVER5435337 /home/u01/app/oracle/oradata/test/indx.dbfRECOVER5435338 /home/u01/app/oracle/oradata/test/tool.dbfRECOVER5435339 /home/u01/app/oracle/oradata/test/oltp.dbfRECOVER543533
RMAN> recover database until scn 543533;
Starting recover at 05-MAR-15 using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 3 is already on disk as file /home/u01/app/oracle/archivelog/1_3_873486141.dbf archived log for thread 1 with sequence 4 is already on disk as file /home/u01/app/oracle/archivelog/1_4_873486141.dbf archived log for thread 1 with sequence 5 is already on disk as file /home/u01/app/oracle/archivelog/1_5_873486141.dbf archived log for thread 1 with sequence 1 is already on disk as file /home/u01/app/oracle/oradata/test/redo01.log archived log file name=/home/u01/app/oracle/archivelog/1_3_873486141.dbf thread=1 sequence=3 archived log file name=/home/u01/app/oracle/archivelog/1_4_873486141.dbf thread=1 sequence=4 archived log file name=/home/u01/app/oracle/archivelog/1_5_873486141.dbf thread=1 sequence=5 media recovery complete, elapsed time: 00:00:01 Finished recover at 05-MAR-15
10.打开数据库，，并验证
sys@TEST> alter database open resetlogs;
Database altered.
sys@TEST> select count(*) from test.a;
COUNT(*) 13238
–原创文章，请勿转载
昨晚多几分钟的准备，今天少几小时的麻烦。
