- Arun Kumar
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;
Flashback Database
Assume that the user has been created by mistake and you want to flashback database to the SCN just before the user creation
Shutdown DB and startup mount
SQL> shut immediate;
SQL> startup mount;
Flashback database to SCN before user creation and open database with resetlogs option
SQL> Flashback database to scn <scn_no>;
SQL> Alter database open resetlogs;