Top 10 Useful Queries for Oracle Flashback Database

Here in this post, we will checking useful Flashback Database queries .Before moving into that, lets check first what is Oracle flashback technology

Introduction to Flashback Database 

It is faster than traditional point-in-time recovery. The traditional recovery method uses backups and redo log files; Flashback Database uses a new type of log file called the Flashback Database log. The Oracle database server periodically logs before images of data blocks in the Flashback Database logs. The data block images are used to quickly back out changes to the database during Flashback Database.We will be looking at Useful Flashback Database queries

It reduces the time required to recover the database to a point in time. The time to restore a database is proportional to the number of changes that need to be backed out, not the size of the database.

Once it is enabled, a new RVWR background process is started. This process is similar to the LGWR (log writer) process. The new process writes Flashback Database data to the Flashback Database logs.

Here are the Top 10 Useful Flashback Database queries

How to enable/disable  Flashback database

How to enable Flashback Database
Make sure the database is in archive mode.
Configure the recovery area by setting the two parameters:
DB_RECOVERY_FILE_DEST
DB_RECOVERY_FILE_DEST_SIZE
Open the database in MOUNT EXCLUSIVE mode and turn on the flashback feature:
SQL> STARTUP MOUNT EXCLUSIVE;
SQL> ALTER DATABASE FLASHBACK ON;
Set the Flashback Database retention target:
DB_FLASHBACK_RETENTION_TARGET
How to disable Flashback Database
SQL> ALTER DATABASE FLASHBACK OFF;

With 11gR2, we can enable flashback on without bringing down in online mode

How Far Back Can We Flashback To (Time)?

select to_char(oldest_flashback_time,'dd-mon-yyyy hh24:mi:ss') "Oldest Flashback Time"
from v$flashback_database_log;

How Far Back Can We Flashback To (SCN)?

col oldest_flashback_scn format 99999999999999999999999999
select oldest_flashback_scn from v$flashback_database_log;

How to check the Flashback Area Usage

SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

how to check flashback status in oracle

select flashback_on, log_mode from v$database;

how to check restore point in oracle

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
FROM V$RESTORE_POINT;

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
FROM V$RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

How to create a restore point

1.Connect SQLPlus to a target database. 
2.Ensure that the database is open or mounted. If the database is mounted, then it must have been shut down cleanly (unless it is a physical standby database). 
3.Run the CREATE RESTORE POINT statement. 
The following example shows how to create a normal restore point in SQLPlus:
SQL> CREATE RESTORE POINT before_upgrade;
This example shows how to create a guaranteed restore point:
SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

How to Flashback oracle Database

Startup mount
Using SCN :-
SQL> flashback database to SCN 78587587587;
Using Timestamp :-
flash back to 1 hr
SQL> flashback database to TIMESTAMP(sysdate - 1/24);
Flashback 5 minutes.
SQL> FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(1/24/12);
flash back to a specific time period
SQL> flashback database to timestamp to_date('17-AP-2016 19:50:00','DD-MON-YYYY HH24:MI:SS');
Using Log sequence Number and RMAN :-
SQL> flashback database to sequence=50 thread=1;

How to monitor the flashback database progress

We need to check the alert log for the progress .With 11gR2, we can run below query

SQL> select sid,message from v$session_longops where
sofar <> totalwork;

Some important notes about Flashback
(1) With 10gR2,Flashback database across resetlogs
(2) With 10gR2,Flashback database across physical standby activation
(3) With 10gR2,Flashback database works across switch over or fail over of physical and logical database
(4) With 10gR2,New feature restore point is created which is convenient way to undo changes.

Internal working of Flashback database
Oracle does not record before images of changed block in flashback logs after every change.It does record the before images but it at different interval so the database can flashback to the limit said in init ora file.Flashback database uses smartly both the flashback logs and archive logs to perform the flashback.
When we execute the command Flashback database, Oracle first looks out for the before images of the block in the flashback logs.It will replace the changed block from images in the flashback logs.After that it uses archive log to recover the database to the point of flashback.So you will always find like this alert log
So steps internally
1) Apply the flashback logs
2) Apply the archive logs to recover to the point of flashback

Some more material can be found on

How to flashback when we have dataguard
How to rollback the patch after failed cutover phase in R12.2
Oracle database recovery
recover database using RMAN
Oracle Flashback Database
Oracle Flashback Query