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

Recent Posts

See All

MySQL Installation on Linux

This article demonstrates MySQL installation on Oracle Enterprise Linux 6.5 version. Installation of MySQL Make sure you are able to connect internet via virtual machine. Try to ping google.com and pr

Add ISO image storage repository on XenServer

If you have used VMware ESXI, its very easy to upload ISO files to the data store and create VMs. With XenServer, you must create your own ISO repository to upload ISO files. Create ISO directory Acce

Automate RMAN Backup using Shell Script

In a real environment, off course you will not manually trigger all the Oracle database backups. You need an automated mechanism to trigger RMAN backups. In this article we will look at RMAN backup au

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis