Flashback can be used to revert back database to previous point in time.
 
 How to Set-up Flashback Database

1. Ensure db_recovery_file_dest is set.

sqlplus '/ as sysdba'
alter system set db_recovery_file_dest='<FRA mount point>' SCOPE=spfile;

2. Ensure db_recovery_file_dest_size is set

alter system set db_recovery_file_dest_size=200G SCOPE=spfile;

3. Stop and start the database in mount stage

sqlplus '/ as sysdba'
shutdown immediate;
startup mount;
Turn flashback on using the following command if required to flashback to a any previous point in time.
alter database flashback on;
alter database open;
alter system set db_flashback_retention_target=2880;

The retention value is in mins

Verify Flashback Database is Already Enabled

1. Run the following commands to determing Flashback is turned on.

sqlplus '/ as sysdba'
select flashback_on from v$database;

Creating and Using Flashback Restore points

Creating a Restore point

Create a restore point whenever the database is at a state that it may needed to be flashed back to. Use the optional GUARANTEE FLASHBACK DATABASE clause to ensure that the restore point is not aged out of the flashback recovery area (FRA) as dictated by the db_flashback_retention_target parameter.

1. Put the database into mount mode now.

2. Create a restore point

sqlplus '/ as sysdba'
create restore point <restore point name> [GUARANTEE FLASHBACK DATABASE];

Rolling Back to a Restore Point

1. Identify the Restore point

sqlplus '/ as sysdba'
select name, time,guarantee_flashback_databse from v$restore_point;
exit

2. For a non RAC environment use the following commands to flashback to a restore point.

sqlplus '/ as sysdba'
shutdown immediate;
startup mount;
flashback database to restore point <restore point name>;
alter database open resetlogs;

3. For RAC instances use the following commands.

One one of the nodes run, srvctl stop database -d <database name> -o immediate
sqlplus '/ as sysdba'
startup mount;
flashback database to restore point <restore point name>;
alter database open resetlogs;
shutdown immediate;
exit
srvctl start database -d <database name>
Run crs_stat -t to confirm that the database is backup okay.

NOTES
 Any tables created and updated without the LOGGING option will be suseptable to block curruption errors when the database is flashed back. These can be remedied by issuing the TRUNCATE TABLE command against the affected object(s).

Dropping a Restore Point

1. Restore points can be dropped with the database open using the following commands

sqlplus '/ as sysdba'
drop restore point <restore point name>;
exit

Monitoring Flashback Logging

After enabling flashback logging, Oracle keeps track of the amount of logging generated. This can be queried from v$flashback_database_log, the estimate gets better with age. Note that this is the size of the flashback logs only and does not include space used by archive logs and RMAN backups.


sqlplus '/ as sysdba'
select estimated_flashback_size/1024/1024/1024 "EST_FLASHBACK_SIZE(GB)" from v$flashback_database_log;
exit

Finding the Earliest Flashback Point

Querying V$flashback_database_log will show you the earliest point you can flashback your database to based on the size of the FRA and the currently available flashback logs.


sqlplus '/ as sysdba'
select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;
exit

Disabling Flashback Database

Full any previous point in time flashback can be disabled with the database open.
Any unused Flashback logs will be automatically removed at this point and a message detailing the file deletion written to the alert log.

sqlplus '/ as sysdba'
ALTER DATABASE FLASHBACK OFF;
exit