Flashback_Database

How to Configure and use Flashback Database

This feature allows you to view the state of your database at a specified prior point in time. Oracle does this by keeping copies of all modified data blocks in flashback logs.
The Flashback logs are written in the Flash Recovery Area; a directory specified by a new parameter db_recovery_file_dest.
Suppose you deleted/modified the configuration information for your application. Instead of performing a recovery operation on this database, you can just ask the database to “put the table back the way it was 5 minutes ago”.
Oracle automatically creates and manages Flashback Logs within the Flash Recovery Area. Since the Flash Recovery Area is configured with a space quota, the Flashback Logs are subject to those disk space restrictions. The size of Flashback Logs can vary considerably, depending on the read/write ratio of database changes during a given flashback-logging interval. A copy of the block changes is written to the Flashback Log. If, over the course of a day, 10% of the database blocks are updated, then the size of Flashback Logs for 24 hours is 1/10th the size of your database.
Flashback Database can be used on both the primary and standby database to quickly revert the databases to an earlier point-in-time to back out user errors. Alternatively, if the administrator decides to failover to a standby database, but those user-errors were already applied to the standby database (say, because Real Time Apply was enabled), the administrator may simply flashback the standby database to a safe point in time. Finally, the administrator has the added option not to use the Real Time Apply feature at one or more standby databases, and instead delay the application of redo data on those standby databases by a configurable amount of time, which provides a window of protection from such user errors or corruptions. The performance overhead of enabling Flashback Database is less than 2%.

These are the steps to setup Flasback Database:

1- Setup the DB in archive log mode (only needed if is not already in this mode)
SELECT LOG_MODE FROM SYS.V$DATABASE;

alter system set db_recovery_file_dest='+<FRA Diskgroup>' SCOPE=both;
or
alter system set DB_RECOVERY_FILE_DEST = 'C:\oracle\RMAN_Backup\Flash_Recovery_Area' SCOPE=both;

alter system set db_recovery_file_dest_size=100G SCOPE=both;
shutdown immediate
startup mount exclusive;
alter database archivelog;
alter database open;
archive log list;

How big the flashback area ought to be is contingent on quite a few factors, such as size of the datafiles, redologs, controlfiles. You have also to be aware of the mean frequency and number of your block changes, whether you store backups only on disk, or on disk and tape, and whether you use a redundancy-based retention policy, or a recovery window-based retention policy etc.
NOTE - In RAC environments, the Flash Recovery Area must be stored in the cluster's shares storage: ASM, NAS, etc

2- Then Enabling Flashback by
Shutdown
startup mount exclusive;
alter system set DB_RECOVERY_FILE_DEST_SIZE=100G scope=both;
alter system set DB_RECOVERY_FILE_DEST = 'C:\oracle\RMAN_Backup\Flash_Recovery_Area' SCOPE=both;

alter system set db_flashback_retention_target=4320 scope=both;     --flashback to be retained for three days (specified in minutes 1440 * 3)
alter database flashback on;
alter database open;

To be able to use the Flasback Table, yu will need the following parameters:
undo_management=auto
undo_retention=a number greater then zero
undo_tablespace=the name of your undo tablespace

select flashback_on from v$database;

So, that enables flashback for our database. We can query v$flashback_database_log to see what's going on
set linesize 200
select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
             8292258 18-JUN-10             4320        8192000                        0

We see that oldest SCN that we can flashback to is SCN 8292258. In other words this is our baseline SCN. The entire technology of flashback database is being implemented from this SCN. We can easily convert the SCN to a timestamp, if we are interested in seeing the time from which flashback database stands enabled.

select scn_to_timestamp(8292258) from dual;
SCN_TO_TIMESTAMP(8292258)
---------------------------------
18-JUN-10 10.49.28.000000000 AM

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. You may want to create the restore point in mount mode. If so, put the database into mount mode now.
shutdown immediate
startup mount exclusive;

2. Create a restore point
create restore point <restore point name> [GUARANTEE FLASHBACK DATABASE];

Rolling Back to a Restore Point

1. Identify the Restore point

select name, scn, time, guarantee_flashback_database from v$restore_point;

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

SQL> shutdown immediate;

SQL> startup mount;

SQL> flashback database to restore point <restore point name>;

SQL> 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'

SQL> startup mount;

SQL> flashback database to restore point <restore point name>;

SQL> alter database open resetlogs;

SQL> shutdown immediate;

SQL> quit

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 susceptible to block corruption 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

drop restore poijnt <restore point name>;

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.

1. Monitor flashback logs

SQL> select estimated_flashback_size/1024/1024/1024 "EST_FLASHBACK_SIZE(GB)" from v$flashback_database_log;

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.

1. Find the earliest flashback point

SQL> alter session set nls_date_format='dd/mm/yy hh24:mi:ss';

SQL> select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;

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.

1. Disabling flashback

SQL> ALTER DATABASE FLASHBACK OFF;

Types Of FlashBack Recoveries
There are basic 7 types are FlashBack recoveries, these are discussed below in details
1- Flashback Query
2- Flashback Version Query
3- Flashback Transaction Query
4- Flashback Table
5- Flashback Drop (Recycle Bin)
6- Flashback Database
7- Flashback Query Functions

Object Level	Scenario	Flashback Technology	Traditional Recovery
Database	Drop User	FLASHBACK DATABASE	Point-In-Time-Recovery
	Truncate Table	FLASHBACK DATABASE	Point-In-Time-Recovery
	Batch job errors out leaving a number of tables partially updated.	FLASHBACK DATABASE	Database Point-In-Time-Recovery
Table	Drop Table	FLASHBACK DROP	Point-In-Time-Recovery
	Update without the proper ‘where’ clause	FLASBACK TABLE	Point-In-Time-Recovery
	Recover deleted data or undoing incorrect changes, even after the changes are committed	FLASHBACK QUERY or FLASHBACK TABLE	Tablespace Point-In-Time-Recovery
	Comparing current data against the data at some time in the past	FLASHBACK QUERY	The space intentionally left blank..
Transaction	Batch Job runs twice, but not really sure of the objects affected	FLASHBACK VERSONS QUERY & FLASHBACK TRANSACTION QUERY	Database Point-In-Time-Recovery

1) Flashback Query :
You perform a Flashback Query using a SELECT statement with an AS OF clause. You use a Flashback Query to retrieve data as it existed at some time in the past. The query explicitly references a past time using a timestamp or SCN. It returns committed data that was current at that point in time. The black magic that makes this possible are UNDO tablespaces and automatic UNDO management
Example
This example uses a Flashback Query to examine the state of a table at a previous time. Suppose, for instance, that a DBA discovers at 12:30 PM that data for employee JOHN had been deleted from the employee table, and the DBA knows that at 9:30AM The data for JOHN was correctly stored in the database. The DBA can use a Flashback Query to examine the contents of the table at 9:30, to findout what data had been lost. If appropriate, the DBA can then re-insert the lost data in the database.
The following query retrieves the state of the employee record for JOHN at 9:30AM, April 4, 2003:
SELECT * FROM employee AS OF TIMESTAMP
   TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
   WHERE name = 'JOHN';

This update then restores John's information to the employee table:
INSERT INTO employee
    (SELECT * FROM employee AS OF TIMESTAMP
     TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
     WHERE name = 'JOHN');

2) Flashback Version Query
The Flashback Versions Query is a powerful tool for the DBA to run analysis and answer the question, 'How did this happen?'
Not only can the DBA run manual analysis, but this is a powerful tool for the application's developer as well. You can build customized applications for auditing purposes. Now everyone really is accountable for his or her actions. Various elements for this are shown below :
VERSIONS_XID - The transaction id that created this version of the row
VERSIONS_OPERATION - The action that created this version of the row (such as delete, insert, and update)
VERSIONS_STARTSCN - The SCN in which this row version first occurred
VERSIONS_ENDSCN - The SCN in which this row version was changed.

Eg : we use the Dept table in Scott schema & update dept 10 to 11 & then 12 & then 13(with commit on every update).Thus we have run 3 updates so a query like
select to_char(versions_starttime, 'DD-MON-YY HH24:mi:ss') versions_starttime,
       to_char(versions_endtime, 'DD-MON-YY HH24:mi:ss') versions_endtime,
       versions_xid, versions_operation, EMPNO
from Dept versions between timestamp minvalue and maxvalue
order by VERSIONS_STARTTIME;

will return something like :
VERSIONS_STARTTIME     VERSIONS_ENDTIME       VERSIONS_XID     V Dept
---------------------- ---------------------- ---------------- - ----------
01-DEC-03 03.57.12 PM 01-DEC-03 03.57.30 PM   0002002800000C61 I 10
01-DEC-03 03.57.30 PM 01-DEC-03 03.57.39 PM   000A000A00000029 U 11
01-DEC-03 03.57.39 PM 01-DEC-03 03.57.55 PM   000A000B00000029 U 12

We can automatically generate the undostatement (AS SYS USER) using :
SELECT UNDO_SQL
FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID = '04001A00B90A0000';
- output : update "SCOTT"."Dept" set "Dept" = '12' where ROWID = 'AAAMicAAEAAAAA/AAA';

3) Flashback Transaction Query
A Flashback Transaction Query is a query on the view FLASHBACK_TRANSACTION_QUERY. You use a Flashback Transaction Query to obtain transaction information, including SQL code that you can use to undo each of the changes made by the transaction.It uses the XID from above & shows the complete transaction steps for that xid ...eg
SELECT xid, start_scn START, commit_scn COMMIT, operation OP, logon_user USER, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW('000200030000002D');

XID               START   COMMIT OP       USER   UNDO_SQL
---------------- -----   ------ --       ----   ---------------------------
000200030000002D 195243 195244 DELETE   HR     insert into "HR"."EMP" ("EMPNO","EMPNAME","SALARY") values ('111','Mike','655');
000200030000002D 195243 195244 INSERT   HR     delete from "HR"."DEPT" where ROWID = 'AAAKD4AABAAAJ3BAAB';
000200030000002D 195243 195244 UPDATE   HR     update "HR"."EMP" set "SALARY" = '555' where ROWID = 'AAAKD2AABAAAJ29AAA';
000200030000002D 195243 113565 BEGIN    HR

4) Flashback Table
Just like the flashback query helps retrieve rows of a table, FLASHBACK TABLE helps restore the state of a table to a certain point in time even if a table structure changed has occurred since then.
It all depends on the undo_retention parameter.
You can see a list of objects that can be recoverd by using the following query:

drop table BANK;

show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
ACCOUNT          BIN$GJBH/nI3QIuQZWOapqJXHg==$0 TABLE        2007-10-17:09:42:45
CUSTOMER         BIN$qXjGDja/S2iCMedZ7wu3vg==$0 TABLE        2007-10-17:09:42:46
BANK             BIN$MFryFUanRRS2EVQD/hl9iQ==$0 TABLE        2007-11-09:09:42:45

or

select object_name, original_name , operation, type
from user_recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE
------------------------------ -------------------------------- --------- ---------
BIN$MFryFUanRRS2EVQD/hl9iQ==$0 BANK                             DROP      TABLE
BIN$qXjGDja/S2iCMedZ7wu3vg==$0 CUSTOMER                         DROP      TABLE
BIN$GJBH/nI3QIuQZWOapqJXHg==$0 ACCOUNT                          DROP      TABLE
BIN$2LBk+8W3T92RhXpBb6Y4PA==$0 UN_BANK_BANKCODECENTERID         DROP      INDEX

The FLASHBACK TABLE command uses the underlying flashback query technology to put the table back the way it was – providing no database integrity constraints would be violated. In addition to being able to simply put a table back the way it was in the past – the FLASHBACK TABLE command also allows you to undrop a database table. Example:

The table BANK is gone but note the presence of the new table BIN$MFryFUanRRS2EVQD/hl9iQ==$0. Here's what happened: The dropped table BANK, instead of completely disappearing, was renamed to a system-defined name. It stays in the same tablespace, with the same structure as that of the original table. If there are indexes or triggers defined on the table, they are renamed too, using the same naming convention used by the table. Any dependent sources such as procedures are invalidated; the triggers and indexes of the original table are instead placed on the renamed table BIN$MFryFUanRRS2EVQD/hl9iQ==$0, preserving the complete object structure of the dropped table.
The table and its associated objects are placed in a logical container known as the "recycle bin," which is similar to the one in your PC. However, the objects are not moved from the tablespace they were in earlier; they still occupy the space there. The recycle bin is merely a logical structure that catalogs the dropped objects. Use the following command from the SQL*Plus prompt to see its content (you'll need SQL*Plus 10.1 to do this):

SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
ACCOUNT          BIN$GJBH/nI3QIuQZWOapqJXHg==$0 TABLE        2007-10-17:09:42:45
CUSTOMER         BIN$qXjGDja/S2iCMedZ7wu3vg==$0 TABLE        2007-10-17:09:42:46
BANK             BIN$MFryFUanRRS2EVQD/hl9iQ==$0 TABLE        2007-11-09:09:42:45

This shows the original name of the table, BANK, as well as the new name in the recycle bin, which has the same name as the new table we saw created after the drop. (Note: the exact name may differ by platform.) To reinstate the table, all you have to do is use the FLASHBACK TABLE command:

SQL> FLASHBACK TABLE BANK TO BEFORE DROP;
FLASHBACK COMPLETE.

SQL> SELECT * FROM TAB;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BANK                           TABLE

Voila! The table is reinstated effortlessly. If you check the recycle bin now, it will be empty.

The following simple command will take us to the table state at the specified timestamp. eg
FLASHBACK TABLE Employee TO TIMESTAMP ('13-SEP-04 8:50:58','DD-MON-YY HH24: MI: SS');

5) Flashback Drop (Recycle Bin)
Dropping of objects by accident has always been a problem for users and DBAs alike. Users soon realize their mistake but then it's too late and historically there is no easy way to recover those dropped tables, indexes, constraints, triggers, etc.
When a user drops a table, Oracle automatically places it into the Recycle Bin.

What is the Recycle Bin?
The Recycle Bin is a virtual container where all dropped objects reside. Underneath the covers, the objects are occupying the same space as when they were created. If table EMP was created in the USERS tablespace, the dropped table EMP remains in the USERS tablespace. Dropped tables and any associated objects such as indexes, constraints, nested tables, and other dependant objects are not moved, they are simply renamed with a prefix of BIN$$. You can continue to access the data in a dropped table or even use Flashback Query against it.
FLASHBACK TABLE dept_test TO BEFORE DROP;

Remember, placing tables in the recycle bin does not free up space in the original tablespace. To free the space, you need to purge the bin using:
PURGE RECYCLEBIN;

But what if you want to drop the table completely, without needing a flashback feature? In that case, you can drop it permanently using:
DROP TABLE RECYCLETEST PURGE;
This command will not rename the table to the recycle bin name; rather, it will be deleted permanently.

Managing the Recycle Bin
If the tables are not really dropped in this process--therefore not releasing the tablespace--what happens when the dropped objects take up all of that space? The answer is simple: that situation does not even arise. When a tablespace is completely filled up with recycle bin data such that the datafiles have to extend to make room for more data, the tablespace is said to be under "space pressure." In that scenario, objects are automatically purged from the recycle bin in a first-in-first-out manner. The dependent objects (such as indexes) are removed before a table is removed. Similarly, space pressure can occur with user quotas as defined for a particular tablespace. The tablespace may have enough free space, but the user may be running out of his or her allotted portion of it. In such situations, Oracle automatically purges objects belonging to that user in that tablespace. In addition, there are several ways you can manually control the recycle bin. If you want to purge the specific table named TEST from the recycle bin after its drop, you could issue
PURGE TABLE TEST;

or using its recycle bin name:
PURGE TABLE "BIN$04LhcpndanfgMAAAAAANPw==$0";

This command will remove table TEST and all dependent objects such as indexes, constraints, and so on from the recycle bin, saving some space. If, however, you want to permanently drop an index from the recycle bin, you can do so using:
purge index in_test1_01;

which will remove the index only, leaving the copy of the table in the recycle bin. Sometimes it might be useful to purge at a higher level. For instance, you may want to purge all the objects in recycle bin in a tablespace USERS. You would issue:
PURGE TABLESPACE USERS;

You may want to purge only the recycle bin for a particular user in that tablespace. This approach could come handy in data warehouse-type environments where users create and drop many transient tables. You could modify the command above to limit the purge to a specific user only:
PURGE TABLESPACE USERS USER SCOTT;

A user such as SCOTT would clear his own recycle bin with
PURGE RECYCLEBIN;

You as a DBA can purge all the objects in any tablespace using
PURGE DBA_RECYCLEBIN;

As you can see, the recycle bin can be managed in a variety of different ways to meet your specific needs.

6) Flashback Database
We have talked abt object recovery before this, now lets talk what if something happens to database, this quickly rewinds the complete database to a previous time, to correct any logical data corruption.This can be used with both RMAN & SQL*Plus. Some of the options are :
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(1/24/12); --Recover up to 5 minutes ago
FLASHBACK DATABASE TO TIMESTAMP my_date;
FLASHBACK DATABASE TO BEFORE TIMESTAMP my_date;
FLASHBACK DATABASE TO SCN <SCN Number>

We can also "mark the DB" to a point to restore:
CREATE RESTORE POINT BEFORE_MY_CHANGES;
And then use the following to recover:
FLASHBACK DATABASE TO RESTORE POINT BEFORE_CHANGES;

After the system comes back with FLASHBACK COMPLETE, open the database with Resetlogs. Eg :
-- Create a dummy table.
CREATE TABLE flashback_database_test (id NUMBER(10)

-Flashback 5 Minutes
CONN sys/password AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP MOUNT EXCLUSIVE
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(1/24/12);
ALTER DATABASE OPEN RESETLOGS;

- Check that the table is gone.
DESC flashback_database_test

7) Flashback Query Functions
The TIMESTAMP_TO_SCN and SCN_TO_TIMESTAMP functions have been added to SQL and PL/SQL to simplify flashback operations: eg :
DECLARE
l_scn        NUMBER;
l_timestamp TIMESTAMP;
BEGIN
l_scn       := TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/24);
l_timestamp := SCN_TO_TIMESTAMP(l_scn);
END;
/

Limits
•    There is one little catch you need to be aware of: it doesn't work on the system tablespaces.
•    As fas as the performance overhead of enabling Flashback Database is concerned its less than 2%.

Turning Flashback OFF
Shutdown immediate;
startup mount exclusive;
alter database flashback off;

--Optional
-- Alter database noarchivelog;
alter database open;

Another Example:
For this, I am going to create a table S and then truncate it. But before truncating the table I need to find out the scn and/ or timestamp to which I will revert back to, after truncating my table.
create table s as select * from tab;

select current_scn, scn_to_timestamp(current_scn) from v$database;
CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
----------- ---------------------------------------------------------------------------
     591023 10-MAY-07 11.53.52.000000000 AM

truncate table s;

shutdown immediate
startup mount
flashback database to scn 591023;
alter database open resetlogs;

select * from s where rownum=1;
TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
ICOL$                          TABLE            4

select count(*) from s;
COUNT(*)
----------
      3339

So, that's it. Its that simple. On the other hand, if you aren't satisfied with what you have got after flashing back, you can simply undo the results of the entire flashback operation by issuing RECOVER DATABASE command. This will perform a complete recovery by applying all of the changes from the archived logs and restoring the status of the database to current.Or, in case you feel you haven't flashed back far enough, you can do so by running the FLASHBACK DATABASE command once again to go back further.