Here in this post, we will be checking useful Flashback Database queries . 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 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 progress.With 11gR2, we can run the 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 failover of physical and logical database
(4) With 10gR2, New feature restore point is created which is a convenient way to undo changes.
Internal working of Flashback database
Oracle does not record before images of changed blocks in flashback logs after every change.It does record the before images but it at different intervals 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 an 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 the flashback
Some more material can be found on