Enable and disable Flashback Database in Oracle

Configure the Flashback for the Database in Oracle

Flashback database is related to protect data from loss and overcome the problems like user error, corruption etc. Flashback has faster point in time recovery also called Database point in time recovery(DBPITR). Make sure database is in archivelog mode.

By configure flashback, we can directly apply the application patch on database,
if something wrong then roll-back it. .

Check Flashback Database is Enabled

select flashback_on from v$database;

Configure the Flashback Database
1. Set the db_recovery_file_dest parameter

ALTER SYSTEM SET db_recovery_file_dest='C:\flashback' scope=spfile;

2. Set the db_recovery_file_dest_size parameter for size allocated for flashback recovery

ALTER SYSTEM SET db_recovery_file_dest_size=20G scope=spfile;

3. Configure the DB_FLASHBACK_RETENTION_TARGET Parameter in minutes.

-- For 1 days retention
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440; # 1 days
-- For 2 day retention
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880; # 2 days
-- For 3 day retention
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days

4. Enable the flashback database.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;

Disable the flashback for tablespace if not required

ALTER TABLESPACE users FLASHBACK OFF;
ALTER TABLESPACE users FLASHBACK ON;

1 thought on “Enable and disable Flashback Database in Oracle

  1. Pingback: Create RESTORE POINT before activity using Flashback recovery in Oracle | Smart way of Technology

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.