Oracle Flashback Technology

Oracle Flashback Database

At this article I will present The Flashback Oracle Tech. As we all know this features let us view the past state of objects.

Also with this amazing technology we can perform queries that shows a details history and Recovery tables to a previous point in time.Also Rollback transaction.

Let me doing some example OF FLASHBACK DATABASE.

First of all we have to flashback ON. So, connect to SQLplus And switch the flashback ON at mount mode.However I would like to set my flashback location…

SQL>ALTER SYSTEM SET log_archive_dest_1='location=/u01/app/veysideneme/archive' SCOPE=SPFILE;

SQL>ALTER SYSTEM SET log_archive_format='ARC%S_%R.%T' SCOPE=SPFILE;

AND now

[oracle@ora ~]$ sqlplus / as sysdba

SQL>STARTUP MOUNT EXCLUSIVE

SQL>ALTER DATABASE FLASHBACK ON;

SQL>ALTER DATABASE OPEN;

Now check our Db_flashback_retention_target time

SQL> show parameter flashback

NAME                                 TYPE        VALUE

---------------------------------- ----------- ------------------------------

db_flashback_retention_target        integer     1440

So, a day we can return back…

And Now I will connect as my user and create a dummy table to exampled.

SQL> conn veysi/veysi

Connected.

SQL> CREATE TABLE flshbck_db_test ( id NUMBER(20) );

Table created.

SQL> conn / as sysdba

Connected.

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>startup mount exclusive;

ORACLE instance started.

Now lets get back to time

SQL> Flashback database to timestamp sysdate-(4/8/14);

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

Lets check table is gone or not!

SQL> conn veysi/veysi

Connected.

SQL> desc flshbck_db_test 

ERROR:

ORA-04043: object flshbck_db_test does not exist

As we see the table already is gone.

Let me doing some other example.

First creating a table to exampled..

SQL> create table flshbck_db_test as select * from dba_objects where rownum < 501;

Table created.

Select my table to check

SQL> select count(*) from flshbck_db_test;

COUNT(*)

---------

500

I will use the SCN number to get back the table so,lets look at the SCN of database

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

 CURRENT_SCN

------------

1116143

Now truncate the table…

SQL> truncate table flshbck_db_test;

Table truncated..

Check the table

SQL> select count(*) from flshbck_db_test;

COUNT(*)

---------

0

Now close the database and use flashback to back

SQL> shu immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

Open mount mode,

SQL> startup mount;

ORACLE instance started.

Database mounted.

Now  get the table back

SQL> Flashback database to scn 1116143;

Flashback complete.

.Open the database by resetlogs.

SQL> alter database open resetlogs;

Database altered.

Select my table to check

SQL> select count(*) from flshbck_db_test;

COUNT(*)

---------

500