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
| 1. Make sure the database is in archive mode. 2. Configure the recovery area by setting the two parameters: DB_RECOVERY_FILE_DEST DB_RECOVERY_FILE_DEST_SIZE 3. Open the database in MOUNT EXCLUSIVE mode and turn on the flashback feature: SQL> STARTUP MOUNT EXCLUSIVE; Set the Flashback Database retention target: DB_FLASHBACK_RETENTION_TARGET To disable disable Flashback Database, issue the following command 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; |
Flashback Area Usage
| SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE; |
Check the status of Flashback ?
| select flashback_on, log_mode from v$database; |
To see a list of the currently defined restore points, use the V$RESTORE_POINT control file view
| SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT; |
| 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 SQL*Plus 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 SQL*Plus: 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 database queries
| 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 switchover or failover of physical and logical datavase
4) With 10gR2,New feature restore point is created which is convienent 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
How to recover database using RMAN
Flashback Database explained and limitation