FLASHBACK CONCEPTS IN ORACLE

Flashback Concepts:

The flash recovery area serves as the default storage area for all files related to backup and restore operations.

The flash recovery area provides the following benefits:

Single storage location for all recovery-related files.
Automatic management of recovery-related disk space.
Faster backup and restore operations, since you don’t need to restore tape backups.
Increased reliability of backups, since disks are generally safer storage devices than tapes. 

The flash recovery area may contain the following files:

1) Datafile copies: The new RMAN command BACKUP AS COPY can be used to create image copies of all datafiles and automatically store in the flash recovery area.
 2)Control file autobackups: The database places any control file backups it generates in the flash recovery area.
 3)Archived redo log files: If you store Archived redo log files in the flash recovery area, Oracle will automatically delete the files. 

 4)Online redo log files: Oracle recommends that you save a multiplexed copy of your online redo log files in the flash recovery area. The following statements can create online redo logs in the flash recovery area: CREATE DATABASE, ALTER DATABASE ADD LOGFILE, ALTER DATABASE ADD STANDBY LOGFILE, and ALTER DATABASE OPEN RESETLOGS.
5)Current control files: Oracle also recommends that you store a multiplexed current control file in the flash recovery area.
 6)RMAN backup pieces : If configuration of RMAN parameters for storing backup pieces is not set, by default rman will write the backup pieces to FRA ( if enabled) while taking backup.
7)Flashback logs: If you enable the flashback database feature, Oracle copies images of each altered block in every datafile into flashback logs stored in the flash recovery area.

Note: Oracle calls the multiplexed redo log files and control files in the flash recovery area permanent files, since they should never be deleted and are part of the live database. Oracle terms all the other files in the flash recovery area (recovery related files) transient files, since Oracle will delete them eventually after they have become obsolete or have already been copied to tape.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u02/oradata/ritesh/arch

1)To enable flashback we need to set size and destination for flashback logs.

SQL> alter system set db_recovery_file_dest_size=500m;

SQL> alter system set db_recovery_file_dest='/u02/oradata/ritesh/flashback';

2)To check configured parameters.

SQL> show parameter recovery_file_dest;

NAME                                 TYPE              VALUE

db_recovery_file_dest                           string

db_recovery_file_dest_size               big integer 500M

3)To enable Flashback enter the following command

SQL> alter database flashback on;

SQL> alter database open;

Note: When we enable FRA then oracle will generate archive logs in DB_RECOVERY_FILE_DEST location. Hence the destination mount point will fill  very quickly due to flashback logs and archived logs.

Change the archiver log destination by using log_archive_dest_1

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

4)To change archivelog file destination

SQL> alter system set log_archive_dest_1='LOCATION=/u02/oradata/ritesh/arch';

SQL> ARCHIVE LOG LIST;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u02/oradata/ritesh/arch

5)Check whether FRA is working or not

SQL> drop table vamshi.t1;

SQL> select * from vamshi.t1;

ORA-00942: table or view does not exist

SQL> flashback table vamshi.t1 to before drop;

Flashback complete.

SQL> select count(*) from vamshi.t1;

 COUNT(*)

         6

6) Flashback recover using SCN:

SQL> select current_scn from v$database;

CURRENT_SCN

     230178

SQL> drop table vamshi.t1 purge;

SQL> shut immediate;

SQL> startup mount;

SQL>  flashback database to scn 230178;

Flashback complete.

àCheck your table in database by opening database in Readonly mode before opening database in resetlogs

SQL> alter database open READ ONLY;

SQL> select count(*) from vamshi.t1;

 COUNT(*)

   6

SQL> alter database open resetlogs;

Note:-When we Perform recovery using Flashback database should  be opened with resetlogs.

7) Flashback recover until TIME

[oracle@oracledb1 ~]$ date

Fri Mar 22 18:10:24 IST 2013

SQL> drop table vamshi.t1 purge;

SQL> shut immediate;

SQL> startup mount;

SQL> flashback database to timestamp to_timestamp('18:10:24 2013/03/22','HH24:MI:SS YYYY/MM/DD');

Flashback complete.

SQL> alter database open READ ONLY;

Database altered.

SQL> select count(*) from vamshi.t1;

COUNT(*)

         6

SQL> SHUT IMMEDIATE;

SQL> startup mount;

SQL> alter database open resetlogs;

8) To Disable current  Flash Recovery Area

SQl>alter system set DB_RECOVERY_FILE_DEST=”  “;

9)To move  Flash Recovery Area to another destination

SQL>alter system set DB_RECOVERY_FILE_DEST=’<NEW LOCATION>’ scope=both;

Different views based on Flash Recovery Area

V$FLASH_RECOVERY_AREA_USAGE

V$RECOVERY_FILE_DEST


also see http://learnappsdbaskills.blogspot.com/2013/07/flashfast-recovery-area-fra-in-oracle.html