Let us we discuss a topic flashback technology - flashback database in oracle11g in that flashback database.

we had 3 methodologies

1)restore point

2)using  SCN

3)using Timestamp

using SCN:

Query the target  database flashback using scn

S-1:

[oracle@goldengatesource ~]$ export ORACLE_SID=CA

to verify our database in os level which we are logged in

[oracle@goldengatesource ~]$ echo $ORACLE_SID
CA
[oracle@goldengatesource ~]$ sqlplus '/as sysdba'

S-2: start the database

SQL> startup

ORACLE instance started.

Total System Global Area  347340800 bytes
Fixed Size                  1336456 bytes
Variable Size             289409912 bytes
Database Buffers           50331648 bytes
Redo Buffers                6262784 bytes
Database mounted.
Database opened.

S-3: To verify our database whether the  flashback is enabled or not using this query,if it's not enabled then follow the steps to enable flashback log

SQL> select flashback_on from  V$database;

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

shut down our database

SQL> shut immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.

 

S-4: start our database in mount stage

SQL> startup mount

ORACLE instance started.

Total System Global Area  347340800 bytes
Fixed Size                  1336456 bytes
Variable Size             289409912 bytes
Database Buffers           50331648 bytes
Redo Buffers                6262784 bytes
Database mounted.

S-5: check the archive logs status also , if it's not enabled in DB  then enabled it

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     21
Current log sequence           24

S-6: Enable archive log mode

SQL> alter database archivelog;

Database altered.

S-7: Enable Flashback

SQL> alter database flashback on;

Database altered.

S-8: Open our database

SQL> alter database open;

Database altered.

 

S-9: Check our  current_scn number  and timestamp

SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
    1084738 2016-05-10 06:52:08

 

S-10: open a another tab logged on scott  user

[oracle@goldengatesource ~]$export ORACLE_SID=CA
[oracle@goldengatesource ~]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 10 06:54:56 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


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

S-11: check the listed of tables available on Scott users

SQL> select * from tab;

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

S-12: now I created a new table and inserted the values in that  

SQL> CREATE TABLE flashback_database_test (id  NUMBER(10));

Table created.

SQL> insert into flashback_database_test values(1);

1 row created.

SQL> commit;

Commit complete.

check the scn and timestamp

SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
    1084866 2016-05-10 06:56:26

purge table command to remove a table or index from your recycle bin and release all of the space associated with the object

SQL>purge table flashback_database_test;

 

now the entire table has been lost

S-13: in sysdba login shut down our db

SQL> shut immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

start the database in mount

SQL> startup mount

ORACLE instance started.

Total System Global Area  347340800 bytes
Fixed Size                  1336456 bytes
Variable Size             289409912 bytes
Database Buffers           50331648 bytes
Redo Buffers                6262784 bytes
Database mounted.

flashback the database using scn

SQL> flashback database to scn 1084866;

Flashback complete.

once the flashback has been done go and Verify the data before opening with a reset logs

SQL>alter database open read only;

Database altered.

S-14: connect the scott user  and check the table and rows

SQL>conn scott/tiger

SQL> select * from tab;

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

6 rows selected.

SQL> select * from flashback_database_test;

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

our table has been retrieved with data perfectly,so shut down our database and startup on mount stage

SQL> alter database open resetlogs;

Database altered.