Performing Flashback Restore using SCN or Time

This document explains how to enable database flashback, creating restore points and performing recovery using guaranteed restore point. In the following I will explain how we can perform a time based, or SCN based flashback restore. Before proceeding further, make sure that flashback is enabled, and flashback logs are also getting created in the fast recovery area. Flashback logs are managed automatically, and oldest flashback logs are purged by oracle if there is a space crunch sensed in the fast recovery area. Oracle will try to keep flashback logs until the time (in the past) specified in the parameter db_flashback_retention_target. However, this value is only a soft limit and specified in minutes.

First of all, make sure that the SCN or time that you will use to perform flashback restore is valid and restore is possible. Following SQL confirms that until what time or

set lines 200 col FLASHBACK_SIZE for 999999999999999999 col ESTIMATED_FLASHBACK_SIZE for 99999999999999999 alter session set nls_date_format='DD-MON-YY HH24:MI:SS';   select * from v$flashback_database_log;   OLDEST_SCN       OLDEST_T IME           RETENTION_TARGET    FLASHBACK_SIZE    ESTIMATED_FLASHBACK_SIZE     CON_ID -------------------- ------------------ ---------------- ------------------- ------------------------ ----------------------------------------------------------     -----------   8940721                 26-APR-23 16:13:14     14400                                  118111600640               495663759360                                       0

Today’s date is 18^th May 2023 and You can see above that although retention target is 14400 minutes (10 days), yet I could restore back until 26^th April 2023 which is 22 days. This means that flashback logs were not purged because there is still ample free space available in the fast recovery area.

Stop the database using srvctl or sqlplus, startup in mount state from one instances (other instances should be down in case of RAC), perform flashback restore using either SCN or Time, and open database with resetlogs.

[oracl~]$ srvctl stop database -d my_db  SQL> startup mount Total System Global Area 1.0201E+11 bytes Fixed Size                 23307168 bytes Variable Size            6174015488 bytes Database Buffers         9.5563E+10 bytes Redo Buffers              245125120 bytes Database mounted.  SQL> flashback database to scn 8940721; Flashback complete. OR SQL> FLASHBACK DATABASE TO TIMESTAMP TO_DATE(04/26/23 16:13','MM/DD/YY hh24:mi'); Flashback complete. SQL> alter database open resetlogs; Database altered.