Flashback the Database Using SCN

Bringing Back The Database to Previous State after Importing a schema Using Flashback Technology         

Step 1 : Firstly we should enable flashback on the database.


                      shutdown immediate;

     startup mount;        

     alter database flashback on;  

     alter database open;      



Step 2: To check whether the flashback in enabled or not issue the below query.,

                     select flashback_on from v$database;


SQL> select flashback_on from v$database;

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


Step 3: create the schema to which you have to import the data (dump).


                       create user test identified by test default tablespace users;       

                       grant connect,resource,debug connect session,debug any procedure to test;     


SQL> create user test identified by laser default tablespace users;

User created.

SQL> grant connect,resource to test;

Grant succeeded.



Step 4: Make sure that there is no data in the newly created schema "test".

     connect test/test@db3

     select * from tab;         


The result will be "no rows selected"


SQL> conn test/laser@db3

Connected.

SQL> select * from tab;

no rows selected




Step 5: We need to take a note of the current SCN (System Change Number) of the database. To know the 
current SCN issue the below query.,


     select current_scn from v$database;     



SQL> select current_scn from v$database;

CURRENT_SCN
-----------
       1039995



Step 6: Import the data to the newly created schema 'test'


SQL> host imp file='d:\db3\exp_scott.dmp' commit=y ignore=y fromuser=scott touser=test

Import: Release 11.2.0.3.0 - Production on Mon Mar 4 12:46:15 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: sys/oracle@db3 as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via direct path

Warning: the objects were exported by SCOTT, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into TEST
. . importing table                        "BONUS"          0 rows imported
. . importing table                         "DEPT"          4 rows imported
. . importing table                          "EMP"         14 rows imported
. . importing table                     "SALGRADE"          5 rows imported
About to enable constraints...
Import terminated successfully without warnings.


Step 7: Check the tables of the new schema 'test'.


     connect test/laser@db3     

     select * from tab;        


SQL> conn test/laser@db3
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE




Step 8: If you need to bring back the database to the previous stage (the stage before import) issue the following command.,

NOte: You had taken the current scn of the database before importing the data.


     conn sys/password@db3 as sysdba

     Shutdown immediate;

     startup mount;

     flashback database to scn scn_no;

     alter database open resetlogs;



SQL> conn sys/oracle@db3 as sysdba
Connected.



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

SQL> startup mount
ORACLE instance started.

Total System Global Area 1703624704 bytes
Fixed Size                  2255864 bytes
Variable Size             989856776 bytes
Database Buffers          704643072 bytes
Redo Buffers                6868992 bytes
Database mounted.


SQL> flashback database to scn 1039995;

Flashback complete.


SQL> alter database open resetlogs;

Database altered.



Step 9: Check the archive log list of the database. It will start from the sequence 1.


archive log list;



SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            D:\db3\arch
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1


Step 10: connect as schema 'test' and check whether there is any tables/data in it.,


    conn test/test@db3       

    select * from tab;                       



SQL> conn test/laser@db3

Connected.


SQL> select * from tab;

no rows selected