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