Oracle Flashback
Technology
Flashback Recovery is a new enhancement to the 10g database for
the DBA's toolkit. Effectively, it's an "Oh shit!" protection
mechanism for DBAs as it reduces recovery time from hours to minutes. Ask any
DBA about the main cause of application outage - other than hardware failure -
and the answer will be "human error". Such errors can result
in logical data corruption and can bring down the complete system. Part of
being human is making mistakes. Without advance planning, these errors are
extremely difficult to avoid, and can be very difficult to recover from.
Typical user-errors may include the accidental deletion of valuable data,
deleting the wrong data, or dropping the wrong table.
So, the big question is: How
can we protect a database from human error?
The answer is a technology called "Flashback" - a
major enhancement in 10g, as it revolutionizes recovery by working just on the
changed data.
Flashback provides:
- An 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, wouldn't you be willing to give 2% of the resources to Flashback
Database?
Below is a chat to show how quickly Flashback can recover your
database.
Architecture:
There are some basic prerequisites for Flashback recovery:
The database should be in Archive Log Mode. Issue the alter
database archivelog command when the database is mounted.
Some of the parameters should be set as follows:
- DB_FLASHBACK_RETENTION_TARGET: This specifies the time limit for deleted data to be maintained in the database. E.g. alter system set DB_FLASHBACK_RETENTION_TARGET=4320, i.e. 72 hours
- DB_RECOVERY_FILE_DEST_SIZE: This specifies the maximum data that can be retained. E.g. alter system set DB_RECOVERY_FILE_DEST_SIZE=536870912, i.e. 512 MB
- DB_RECOVERY_FILE_DEST: This specifies the destination for the flashback data. E.g. alter system set DB_RECOVERY_FILE_DEST='/u02/fra';
There are six basic types of Flashback recovery, discussed below
in detail:
1.
Flashback Query
2.
Flashback Version Query
3.
Flashback Transaction Query
4.
Flashback Table
5.
Flashback Drop (Recycle Bin)
6.
Flashback Database
Flashback Query:
You can perform a Flashback Query using a SELECT statement with
an AS OF clause. You can 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 specified time in the past. Suppose, for instance, that a DBA
discovers at 12:30 PM that data for employee JON has been deleted from the
employee table, and the DBA knows that at 9:30AM the data for JON was correctly
stored in the database. The DBA can use a Flashback Query to examine the
contents of the table at 9:30, to find out what data has 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, 2006:
SQL> SELECT * FROM employee AS OF TIMESTAMP TO_TIMESTAMP('2021-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS') WHERE name = '&NAME';
This update then restores John's information to the employee
table:
SQL> INSERT INTO employee (SELECT * FROM employee AS OF TIMESTAMP TO_TIMESTAMP('2021-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS') WHERE name = '&NAME');
Flashback Query Functions
The TIMESTAMP_TO_SCN and SCN_TO_TIMESTAMP functions have been
added to SQL and PL/SQL to simplify flashback operations:
DECLARE
l_scn NUMBER;
l_timestamp
TIMESTAMP;
BEGIN
l_scn
:= TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/24);
l_timestamp := SCN_TO_TIMESTAMP(l_scn);
END;
/
Flashback Version Query
The Flashback Version 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 a 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.
For example: we use the Dept table in Scott schema and update
dept 10 to 11, then 12, and then 13 (with a commit after every update). Thus we
have done three updates:
SQL> select
versions_starttime, versions_endtime, versions_xid, versions_operation, dept
from Dept versions between timestamp minvalue and maxvalue order by
VERSIONS_STARTTIME
SQL> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERYWHERE XID = '000A000A00000029';
Flashback Transaction Query
SQL> SELECT xid, start_scn START, commit_scn COMMIT,operation OP, logon_user USER, undo_sql FROM flashback_transaction_query WHERE xid = HEXTORAW('000200030000002D');
Recovering Tables Using the Flashback Table Feature
SQL> FLASHBACK TABLE Employee TOTIMESTAMP ('13-SEP-21 8:50:58','DD-MON-YY HH24: MI: SS');
Flashback
Drop (Recycle Bin)
The accidental dropping of objects has always been a problem for
users and DBAs alike. Users soon realize their mistake, but by then it's too
late - and until now there has been 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.
The Recycle Bin
The Recycle Bin is a virtual container for all dropped objects.
Under its lid, the objects occupy 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 dependent objects are not moved; they are
simply renamed with the prefix BIN$$. You can continue to access the data in a
dropped table, or even use Flashback Query on it.
SQL>
FLASHBACK TABLE dept_test TO BEFORE DROP;
Flashback
Database
We have been talking about object recovery. But what if
something happens to the whole database? This recovery 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
When the system comes back with FLASHBACK COMPLETE, open the
database with Resetlogs.
For example:
-- 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
No comments:
Post a Comment