Flashback间接恢复被删除的tablespace

前提，数据库处于archivelog mode
场景如下：

1.开启flashback

SQL> startup mount
ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 1262812 bytes
Variable Size 520096548 bytes
Database Buffers 12582912 bytes
Redo Buffers 2928640 bytes
Database mounted.
SQL>
SQL>
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> alter database flashback on;
Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

 

2. 准备试验数据

create tablespace test_rman_bak datafile '/oracle/oradata/monster/test_rman_bak.dbf' size 10M;
create user zhanglei identified by zhanglei default tablespace test_rman_bak;
grant dba to zhanglei;
conn zhanglei/zhanglei
create table A1
(id number(3),
name varchar2(20)
)
/

SQL> select * from zhanglei.a1;

        ID NAME
---------- --------------------
         1 test1
         2 test2
         3 test3

 

3.手动删除一个tablespace

--记录下删除前的SCN
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
8.7961E+12

SQL> col current_scn for 9999999999999
SQL> /

CURRENT_SCN
--------------
8796117159141



SQL> drop tablespace TEST_RMAN_BAK including contents and datafiles;
Tablespace dropped.

 

4.尝试直接flashback，看看结果如何

SQL> conn / as sysdba
Connected.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 1262812 bytes
Variable Size 520096548 bytes
Database Buffers 12582912 bytes
Redo Buffers 2928640 bytes
Database mounted.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oracle/oradata/monster/system01.dbf
/oracle/oradata/monster/pwrcard01.dbf
/oracle/oradata/monster/sysaux01.dbf
/oracle/oradata/monster/users01.dbf
/oracle/oradata/monster/logmnrts.dbf
/oracle/oradata/monster/tools.dbf
/oracle/oradata/monster/testcdc.dbf
/oracle/oradata/monster/undotbs01.dbf
/oracle/oradata/monster/rman.dbf

9 rows selected.

SQL> flashback database to scn 8796117159141;
flashback database to scn 8796117159141
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error
below
ORA-01245: offline file 10 will be lost if RESETLOGS is done
ORA-01111: name for data file 10 is unknown - rename to correct file
ORA-01110: data file 10: '/oracle/product/10.2.0/db_1/dbs/UNNAMED00010'

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01245: offline file 10 will be lost if RESETLOGS is done
ORA-01110: data file 10: '/oracle/oradata/monster/test_rman_bak.dbf'

 

5.手动建一个datafile,然后再次flashback

--从下面的结果可以看到，虽然上次的flashback没有成功完成，但是他却为我们生成了一个datafile
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oracle/oradata/monster/system01.dbf
/oracle/oradata/monster/pwrcard01.dbf
/oracle/oradata/monster/sysaux01.dbf
/oracle/oradata/monster/users01.dbf
/oracle/oradata/monster/logmnrts.dbf
/oracle/oradata/monster/tools.dbf
/oracle/oradata/monster/testcdc.dbf
/oracle/oradata/monster/undotbs01.dbf
/oracle/oradata/monster/rman.dbf
/oracle/product/10.2.0/db_1/dbs/UNNAMED00010

10 rows selected.

--用[alter database create datafile as]重建被删除的datafile
SQL> alter database create datafile '/oracle/product/10.2.0/db_1/dbs/UNNAMED00010' as '/oracle/oradata/monster/test_rman_bak.dbf';

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oracle/oradata/monster/system01.dbf
/oracle/oradata/monster/pwrcard01.dbf
/oracle/oradata/monster/sysaux01.dbf
/oracle/oradata/monster/users01.dbf
/oracle/oradata/monster/logmnrts.dbf
/oracle/oradata/monster/tools.dbf
/oracle/oradata/monster/testcdc.dbf
/oracle/oradata/monster/undotbs01.dbf
/oracle/oradata/monster/rman.dbf
/oracle/oradata/monster/test_rman_bak.dbf

10 rows selected.

--flashback成功完成
SQL> flashback database to scn 8796117159141;
Flashback complete.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

--数据库正常打开
SQL> alter database open resetlogs;
Database altered.

 

6.确认实验数据是否被成功恢复

SQL> select * from zhanglei.a1;

        ID NAME
---------- --------------------
         1 test1
         2 test2
         3 test3