Undo The Past ---Use FlashBack Technology with Oracle Database 10g.


FlashBack Recovery is a new enhancement in 10g for the toolkit of a DBA.

Effectively, it's a "oh shit!"
proctection mechanism for DBAs as it reduces recovery time from hours
to minutes. If we ask any DBA abt the main causes of application outage
other than hardware failure, the answer will be "Human Errors".
Such errors can result in "logical" data corruption & can bring
bown the complete system. Part of being human is making mistakes. But
these errors are extremely difficult to avoid and can be particularly
difficult to recover from without advance planning. Typical user-errors
may include accidental deletion of valuable data, deleting the wrong
data, and dropping the wrong table.



So the question comes.......How to guard against Human error ?

And
the answer for this comes in a techonology called "FalshBack", which is
a major enhancement in 10g as it revolutionizes recovery by operating
just on the changed data. Thus time it takes to recover the error is
now equal to the same amount of time it took to make the mistake.



Flashback Database provides:


  • Effective way to recover from complex human errors
  • Faster database point-in-time recovery
  • Simplified management and administration
  • Little performance overhead




The performance overhead of enabling Flashback Database is less than 2%.
While you may not be willing to sacrifice any performance overhead for
your production database, think about the trade-off. If you could
recover the database in minutes instead of hours, saving your company
millions of dollars in lost revenue, would you then give 2% of the
resources to Flashback Database?



Architecture



There are some basic pre-requesits for FlashBack recovery , these are :


  • Database should be in ArchiveLog Mode :- On mount state use :alter database archivelog;
  • Some of the parameters should be set as below :


1. DB_FLASHBACK_RETENTION_TARGET - This specifies the timelimit for deleted data to be maintained in database

Eg alter system set DB_FLASHBACK_RETENTION_TARGET=4320 , ie 72hrs



2. DB_RECOVERY_FILE_DEST_SIZE: This specifies the max data that can be retained ,

Eg alter system set DB_RECOVERY_FILE_DEST_SIZE=536870912 , ie 512Mb



3. DB_RECOVERY_FILE_DEST : This specifies the destination for the flashback data ,

Eg alter system set DB_RECOVERY_FILE_DEST='/u02/fra';





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






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.



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:



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

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

SQL>select
versions_starttime, versions_endtime, versions_xid, versions_operation,
Dept 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 undo statement using :

SQL>SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY

WHERE XID = '000A000A00000029';

- 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



SQL>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 rows selected



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. The
following simple command will take us to the table state at the
specified timestamp.

eg

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

Flashback Drop provides a
safety net when dropping objects in Oracle Database 10g. 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.



SQL>FLASHBACK TABLE dept_test TO BEFORE DROP;





6)Flashback Database

We
have talked abt object recovery berore 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);
  • FLASHBACK DATABASE TO TIMESTAMP my_date;
  • FLASHBACK DATABASE TO BEFORE TIMESTAMP my_date;
  • FLASHBACK DATABASE TO SCN my_scn


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%.


Conclusion:

Flashback
is an insurance feature. FLASHBACK should be considered another tool in
the belt, rather than a luxury that allows us to be careless about the
data simply because we have the ability to recover it easily.



Used References and Recommended Readings


  1. Oracle Database Backup and Recovery Basics - 10g Release 2 (10.2)
  2. Oracle Database Backup and Recovery Advanced User's Guide - 10g Release 2 (10.2)
  3. Oracle Database Backup and Recovery Reference - 10g Release 2 (10.2)

Comments:

Pankaj, can a Flashback image be mounted to a different server (same operating system) for backup to tape?

Posted by David Runyon on July 12, 2006 at 11:28 AM PDT #

No it cant be used from tape backups as flashback also uses Undo segment , thats why we have retention policy , after the retention policy , flashback cant be performed . Hope it clears something :) takecare PAnkaj

Posted by Pankaj Chandiramani on July 16, 2006 at 06:21 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

This blog is authored by Pankaj Chandiramani , Product Manager for Enterprise Manager . Main Focus of the blog will be on Enterprise Manager 12c with deep coverage of Database Life Cycle Management that includes Database Provisioning , Database Patching , Config , Compliance etc .

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today