flashback database 恢复删除的表空间

当误删除表空间，且没有备份，开启归档和闪回，可以使用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.0 Production
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 Size 2235208 bytes
Variable Size 645924024 bytes
Database Buffers 415236096 bytes
Redo Buffers 5541888 bytes
Database mounted.

6.闪回数据库
sys@TEST> flashback database to scn ‘543532’;

Flashback complete.

sys@TEST> select file#,name,status from v$datafile;

FILE# NAME                                                         STATUS

1 /home/u01/app/oracle/oradata/test/system01.dbf               SYSTEM
     2 /home/u01/app/oracle/oradata/test/sysaux01.dbf               RECOVER
     3 /home/u01/app/oracle/oradata/test/undotbs01.dbf              RECOVER
     4 /home/u01/app/oracle/oradata/test/users01.dbf                RECOVER
     5 /home/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00005   RECOVER
     6 /home/u01/app/oracle/oradata/test/example.dbf                RECOVER
     7 /home/u01/app/oracle/oradata/test/indx.dbf                   RECOVER
     8 /home/u01/app/oracle/oradata/test/tool.dbf                   RECOVER
     9 /home/u01/app/oracle/oradata/test/oltp.dbf                   RECOVER

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#,status from v$datafile;

FILE# NAME                                                         STATUS

1 /home/u01/app/oracle/oradata/test/system01.dbf               SYSTEM
     2 /home/u01/app/oracle/oradata/test/sysaux01.dbf               RECOVER
     3 /home/u01/app/oracle/oradata/test/undotbs01.dbf              RECOVER
     4 /home/u01/app/oracle/oradata/test/users01.dbf                RECOVER
     5 /home/u01/app/oracle/oradata/test/test.dbf                   RECOVER
     6 /home/u01/app/oracle/oradata/test/example.dbf                RECOVER
     7 /home/u01/app/oracle/oradata/test/indx.dbf                   RECOVER
     8 /home/u01/app/oracle/oradata/test/tool.dbf                   RECOVER
     9 /home/u01/app/oracle/oradata/test/oltp.dbf                   RECOVER

9 rows selected.

8.数据文件处于offline状态，修改为online；
sys@TEST> select * from v$recover_file;

FILE# ONLINE  ONLINE_ ERROR                                  CHANGE# TIME

1 ONLINE  ONLINE                                          543533 05-MAR-15
     2 ONLINE  ONLINE                                          543533 05-MAR-15
     3 ONLINE  ONLINE                                          543533 05-MAR-15
     4 ONLINE  ONLINE                                          543533 05-MAR-15
     5 OFFLINE OFFLINE UNKNOWN ERROR                           540888 05-MAR-15
     6 ONLINE  ONLINE                                          543533 05-MAR-15
     7 ONLINE  ONLINE                                          543533 05-MAR-15
     8 ONLINE  ONLINE                                          543533 05-MAR-15
     9 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#,status,checkpoint_change# from v$datafile;

FILE# NAME                                                         STATUS  CHECKPOINT_CHANGE#

1 /home/u01/app/oracle/oradata/test/system01.dbf               SYSTEM              543533
     2 /home/u01/app/oracle/oradata/test/sysaux01.dbf               RECOVER             543533
     3 /home/u01/app/oracle/oradata/test/undotbs01.dbf              RECOVER             543533
     4 /home/u01/app/oracle/oradata/test/users01.dbf                RECOVER             543533
     5 /home/u01/app/oracle/oradata/test/test.dbf                   RECOVER             542863
     6 /home/u01/app/oracle/oradata/test/example.dbf                RECOVER             543533
     7 /home/u01/app/oracle/oradata/test/indx.dbf                   RECOVER             543533
     8 /home/u01/app/oracle/oradata/test/tool.dbf                   RECOVER             543533
     9 /home/u01/app/oracle/oradata/test/oltp.dbf                   RECOVER             543533

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

–原创文章，请勿转载