Thursday, October 5, 2023

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;

-------------------- ------------------ ---------------- ------------------- ------------------------ ----------------------------------------------------------     -----------

  8940721                 26-APR-23 16:13:14     14400                                  118111600640               495663759360                                       0

Today’s date is 18th May 2023 and You can see above that although retention target is 14400 minutes (10 days), yet I could restore back until 26th 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.



Flashback complete.

SQL> alter database open resetlogs;

Database altered.

No comments:

Post a Comment

Popular Posts - All Times