Arun Kumar
- 2 min read

Flashback Database to SCN

In this article we will be looking at database flashback to a particular SCN number.

Scenario: We will create a user inside database and then perform flashback database to SCN just before the user was created.

Note: only for flashback database activity, you must enable flashback database. For all other flashback activities like flashback table etc, you do not need flashback database to be enabled!

Enable Flashback Database

Make sure DB_RECOVERY_FILE_DEST parameter is set. This is the location where Oracle will store flashback logs

SQL> alter system set db_recovery_file_dest='/u02/flash_logs' SCOPE=spfile;

Set DB_RECOVERY_FILE_DEST parameter to 100 GB. You can even set it to a lower value for the activity purpose

SQL> alter system set db_recovery_file_dest_size=100G SCOPE=spfile;

Set the DB_FLASHBACK_RETENTION_TARGET parameter which specifies the upper limit (in minutes) on how far back in time the database can be flashed back

SQL> alter system set db_flashback_retention_target=2880;

Check the Flashback status at DB level. By default it will be disabled

SQL> select flashback_on from v$database;

Let us enable flashback database. For this we need to bounce the database

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database flashback on;
SQL> alter database open;

Create Sample User

Let us capture the database SCN number before we create a user

SQL> SELECT current_scn, SYSTIMESTAMP FROM v$database;

Current SCN: 2703232

Now create a user FLASH_USR and try to connect the database with same user

SQL> create user flash_usr identified by flash_usr;
SQL> grant connect, resource to flash_usr;
SQL> conn flash_usr/flash_usr;