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
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
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
