Recovering Dropped tablespace using Flashback Database

Oracle Version :11g
OS: Rhel 6.4

#Turn on flashback and archivelog from mount stage
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             318769144 bytes
Database Buffers           88080384 bytes
Redo Buffers                4308992 bytes
Database mounted.

SQL>alter database archivelog;
Database altered.

SQL>  alter database flashback on;
Database altered.

SQL> alter database open;

Database altered.

#Now create a tablespace to test the scenario
SQL> create tablespace test datafile '/u01/app/oracle/oradata/prim/testtbs.dbf' size 20m;
Tablespace created.

SQL> create user test identified by test;
User created.

SQL> grant connect , reesource to test;

SQL> alter user test default tablespace test;
User altered.

SQL> conn test/test
Connected.

SQL> create table t1 (id number);

SQL> insert into t1 values(1);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from t1;
        ID
----------
         1


SQL> connect sqlplus as sysdba
Enter password:
Connected.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1075681

#Now drop the tablespace:-
SQL> drop tablespace test including contents and datafiles;
Tablespace dropped.

SQL> shutdown immediate
SQL> startup mount

SQL> flashback database to scn 1075681;
Flashback complete.

SQL> alter database open resetlogs;
Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/system01.dbf
/u01/app/oracle/oradata/prim/sysaux01.dbf
/u01/app/oracle/oradata/prim/undotbs01.dbf
/u01/app/oracle/oradata/prim/users01.dbf
/u01/app/oracle/oradata/prim/example01.dbf
/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006

SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006' as '/u01/app/oracle/oradata/prim/testtbs.dbf';
Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/system01.dbf
/u01/app/oracle/oradata/prim/sysaux01.dbf
/u01/app/oracle/oradata/prim/undotbs01.dbf
/u01/app/oracle/oradata/prim/users01.dbf
/u01/app/oracle/oradata/prim/example01.dbf
/u01/app/oracle/oradata/prim/testtbs.dbf

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
EXAMPLE
TEST

#Login to test schema and verify the data
SQL> conn test/test
Connected.
SQL> select * from t1;

        ID
----------
         1





No comments:

Post a Comment