Home » Oracle » Oracle Database » Top 10 Useful Queries for Oracle Flashback Database

Top 10 Useful Queries for Oracle Flashback Database

Here in this post, we will be checking useful Flashback Database queries like How to enable/disable  Flashback database, How Far Back Can We Flashback, how to check flashback status in Oracle, how to check flashback logs in Oracle, How to Flashback Oracle Database etc. Before moving into that, let’s 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 it down in online mode

See also  alter table rename column in oracle database

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 progress. With 11gR2, we can run the below query

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

how to check flashback logs in oracle

col log_file form a60
select inst_id, thread# thr, log#, sequence# seq, first_change#,
to_char(first_time,'dd-Mon-YYYY hh24:mi:ss') first_time
, name log_file
from gv$flashback_database_logfile
order by inst_id, thread#, sequence#;

select inst_id
,OLDEST_FLASHBACK_SCN
, to_char(OLDEST_FLASHBACK_TIME,'dd-mon-yyyy hh24:mi:ss') oldest_flashback_time
, to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') Curr_time
,RETENTION_TARGET, flashback_size
from gv$flashback_database_log
order by inst_id,OLDEST_FLASHBACK_TIME;

how to check flashback retention period in oracle

sqlplus / as sysdba
show parameter DB_FLASHBACK_RETENTION_TARGET

Some more material can be found on

See also  How to gather Statistics with DBMS_STATS Procedures

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

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top