Oracle9i introduced the DBMS_FLASHBACK package to allow queries
to reference older versions of the database. Oracle 10g has taken this
technology a step further making it simpler to use and much more flexible.
Note: Internally Oracle uses SCNs to track changes so any flashback
operation that uses a timestamp must be translated into the nearest SCN which
can result in a 3 second error.
·
Flashback Query
·
Flashback Version Query
·
Flashback Transaction Query
·
Flashback Table
·
Flashback Drop (Recycle Bin)
·
Flashback Database
·
Flashback Query Functions
Flashback Query
Flashback Query allows the contents of
a table to be queried with reference to a specific point in time, using the AS
OF
clause. Essentially it is the same as the DBMS_FLASHBACK functionality or
Oracle9i, but in a more convenient form. For example:
CREATE
TABLE flashback_query_test (
id
NUMBER(10)
);
SELECT
current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
CURRENT_SCN
TO_CHAR(SYSTIMESTAM
-----------
-------------------
722452 2004-03-29 13:34:12
INSERT
INTO flashback_query_test (id) VALUES (1);
COMMIT;
SELECT
COUNT(*) FROM flashback_query_test;
COUNT(*)
----------
1
SELECT
COUNT(*) FROM flashback_query_test AS OF TIMESTAMP TO_TIMESTAMP('2004-03-29
13:34:12', 'YYYY-MM-DD HH24:MI:SS');
COUNT(*)
----------
0
SELECT
COUNT(*) FROM flashback_query_test AS OF SCN 722452;
COUNT(*)
----------
0
Flashback
Version Query
Flashback
version query allows the versions of a specific row to be tracked during a
specified time period using the VERSIONS BETWEEN clause:
CREATE TABLE
flashback_version_query_test (
id NUMBER(10),
description VARCHAR2(50)
);
INSERT INTO
flashback_version_query_test (id, description) VALUES (1, 'ONE');
COMMIT;
SELECT current_scn,
TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
725202 2004-03-29 14:59:08
UPDATE flashback_version_query_test
SET description = 'TWO' WHERE id = 1;
COMMIT;
UPDATE flashback_version_query_test
SET description = 'THREE' WHERE id = 1;
COMMIT;
SELECT current_scn,
TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
725219 2004-03-29 14:59:36
COLUMN versions_startscn FORMAT
99999999999999999
COLUMN versions_starttime FORMAT A24
COLUMN versions_endscn FORMAT
99999999999999999
COLUMN versions_endtime FORMAT A24
COLUMN versions_xid FORMAT A16
COLUMN versions_operation FORMAT A1
COLUMN description FORMAT A11
SET LINESIZE 200
SELECT versions_startscn,
versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,
description
FROM
flashback_version_query_test
VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2004-03-29 14:59:08',
'YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2004-03-29 14:59:36', 'YYYY-MM-DD HH24:MI:SS')
WHERE
id = 1;
VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID V DESCRIPTION
------------------
------------------------ ------------------ ------------------------
---------------- - -----------
725212 29-MAR-04 02.59.16 PM
02001C0043030000 U THREE
725209 29-MAR-04 02.59.16 PM 725212 29-MAR-04 02.59.16
PM 0600030021000000 U TWO
725209 29-MAR-04 02.59.16 PM ONE
SELECT versions_startscn,
versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,
description
FROM
flashback_version_query_test
VERSIONS BETWEEN SCN 725202 AND 725219
WHERE
id = 1;
VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID V DESCRIPTION
------------------
------------------------ ------------------ ------------------------
---------------- - -----------
725212 29-MAR-04 02.59.16 PM
02001C0043030000 U THREE
725209 29-MAR-04 02.59.16 PM 725212 29-MAR-04 02.59.16
PM 0600030021000000 U TWO
725209 29-MAR-04 02.59.16 PM ONE
The
available pseudocolumn meanings are:
·
VERSIONS_STARTSCN or VERSIONS_STARTTIME - Starting SCN and
TIMESTAMP when row took on this value. The value of NULL is returned if the row
was created before the lower bound SCN ot TIMESTAMP.
·
VERSIONS_ENDSCN or VERSIONS_ENDTIME - Ending SCN and TIMESTAMP
when row last contained this value. The value of NULL is returned if the value
of the row is still current at the upper bound SCN ot TIMESTAMP.
·
VERSIONS_XID - ID of the transaction
that created the row in it's current state.
·
VERSIONS_OPERATION - Operation performed by
the transaction ((I)nsert, (U)pdate or (D)elete)
Flashback
Transaction Query
Flashback transaction query can be
used to get extra information about the transactions listed by flashback
version queries. The VERSIONS_XID column values from a
flashback version query can be used to query the FLASHBACK_TRANSACTION_QUERY view like:
SELECT xid, operation,
start_scn,commit_scn, logon_user, undo_sql
FROM
flashback_transaction_query
WHERE
xid = HEXTORAW('0600030021000000');
XID OPERATION START_SCN COMMIT_SCN
----------------
-------------------------------- ---------- ----------
LOGON_USER
------------------------------
UNDO_SQL
----------------------------------------------------------------------------------------------------
0600030021000000 UPDATE 725208 725209
SCOTT
update
"SCOTT"."FLASHBACK_VERSION_QUERY_TEST" set
"DESCRIPTION" = 'ONE' where ROWID = 'AAAMP9AAEAAAA
AYAAA';
0600030021000000 BEGIN 725208 725209
SCOTT
XID OPERATION START_SCN COMMIT_SCN
----------------
-------------------------------- ---------- ----------
LOGON_USER
------------------------------
UNDO_SQL
----------------------------------------------------------------------------------------------------
2 rows selected.
Flashback
Table
The
FLASHBACK
TABLE
command allows point in time recovery of individual tables subject to the
following requirements:
·
You
must have either the FLASHBACK ANY TABLE system privilege or have FLASHBACK object privilege on the
table.
·
You
must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
·
There
must be enough information in the undo tablespace to complete the operation.
·
Row
movement must be enabled on the table (ALTER TABLE tablename
ENABLE ROW MOVEMENT;).
The
following example creates a table, inserts some data and flashbacks to a point
prior to the data insertion. Finally it flashbacks to the time after the data
insertion:
CREATE TABLE flashback_table_test (
id NUMBER(10)
);
ALTER TABLE flashback_table_test
ENABLE ROW MOVEMENT;
SELECT current_scn FROM v$database;
CURRENT_SCN
-----------
715315
INSERT INTO flashback_table_test (id)
VALUES (1);
COMMIT;
SELECT current_scn FROM v$database;
CURRENT_SCN
-----------
715340
FLASHBACK TABLE flashback_table_test
TO SCN 715315;
SELECT COUNT(*) FROM
flashback_table_test;
COUNT(*)
----------
0
FLASHBACK TABLE flashback_table_test
TO SCN 715340;
SELECT COUNT(*) FROM
flashback_table_test;
COUNT(*)
----------
1
Flashback
of tables can also be performed using timestamps:
FLASHBACK TABLE flashback_table_test TO TIMESTAMP
TO_TIMESTAMP('2004-03-03 10:00:00', 'YYYY-MM-DD HH:MI:SS');
Flashback
Drop (Recycle Bin)
In
Oracle 10g the default action of a DROP TABLE command is to move the
table to the recycle bin (or rename it), rather than actually dropping it. The PURGE option can be used to
permanently drop a table.
The recycle bin is a logical collection of previously dropped objects, with
access tied to the DROP privilege. The contents of the
recycle bin can be shown using the SHOW RECYCLEBIN command and purged using
the PURGE
TABLE
command. As a result, a previously dropped table can be recovered from the
recycle bin:
CREATE TABLE flashback_drop_test (
id NUMBER(10)
);
INSERT INTO flashback_drop_test (id)
VALUES (1);
COMMIT;
DROP TABLE flashback_drop_test;
SHOW RECYCLEBIN
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
----------------
------------------------------ ------------ -------------------
FLASHBACK_DROP_T
BIN$TstgCMiwQA66fl5FFDTBgA==$0 TABLE
2004-03-29:11:09:07
EST
FLASHBACK TABLE flashback_drop_test TO
BEFORE DROP;
SELECT * FROM flashback_drop_test;
ID
----------
1
Tables
in the recycle bin can be queried like any other table:
DROP TABLE flashback_drop_test;
SHOW RECYCLEBIN
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------
------------ -------------------
FLASHBACK_DROP_T
BIN$TDGqmJZKR8u+Hrc6PGD8kw==$0 TABLE
2004-03-29:11:18:39
EST
SELECT * FROM
"BIN$TDGqmJZKR8u+Hrc6PGD8kw==$0";
ID
----------
1
If
an object is dropped and recreated multiple times all dropped versions will be
kept in the recycle bin, subject to space. Where multiple versions are present
it's best to reference the tables via the RECYCLEBIN_NAME. For any references to the
ORIGINAL_NAME it is assumed the most
recent object is drop version in the referenced question. During the flashback
operation the table can be renamed like:
FLASHBACK TABLE flashback_drop_test TO BEFORE DROP RENAME
TO flashback_drop_test_old;
Several
purge options exist:
PURGE TABLE tablename; -- Specific table.
PURGE INDEX indexname; -- Specific index.
PURGE TABLESPACE ts_name; -- All tables in a specific
tablespace.
PURGE TABLESPACE ts_name USER
username; -- All tables in a specific
tablespace for a specific user.
PURGE RECYCLEBIN; -- The current users
entire recycle bin.
PURGE DBA_RECYCLEBIN; -- The whole recycle bin.
Several
restrictions apply relating to the recycle bin:
·
Only
available for non-system, locally managed tablespaces.
·
There
is no fixed size for the recycle bin. The time an object remains in the recycle
bin can vary.
·
The
objects in the recycle bin are restricted to query operations only (no DDL or
DML).
·
Flashback
query operations must reference the recycle bin name.
·
Tables
and all dependent objects are placed into, recovered and purged from the
recycle bin at the same time.
·
Tables
with Fine Grained Access policies aer not protected by the recycle bin.
·
Partitioned
index-organized tables are not protected by the recycle bin.
·
The
recycle bin does not preserve referential integrity.
Flashback
Database
The
FLASHBACK
DATABASE
command is a fast alternative to performing an incomplete recovery. In order to
flashback the database you must have SYSDBA privilege and the flash
recovery area must have been prepared in advance.
If the database is in NOARCHIVELOG it must be switched to ARCHIVELOG mode:
CONN sys/password AS SYSDBA
ALTER SYSTEM SET
log_archive_dest_1='location=d:\oracle\oradata\DB10G\archive\' SCOPE=SPFILE;
ALTER SYSTEM SET
log_archive_format='ARC%S_%R.%T' SCOPE=SPFILE;
ALTER SYSTEM SET
log_archive_start=TRUE SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ARCHIVE LOG START
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
Flashback
must be enabled before any flashback operations are performed:
CONN sys/password AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP MOUNT EXCLUSIVE
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;
With
flashback enabled the database can be switched back to a previous point in time
or SCN without the need for a manual incomplete recovery. In the following
example a table is created, the database is then flashbacked to a time before
the table was created:
-- Create a dummy table.
CONN scott/tiger
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.
CONN scott/tiger
DESC flashback_database_test
Some
other variations of the flashback database command include:
FLASHBACK DATABASE TO TIMESTAMP
my_date;
FLASHBACK DATABASE TO BEFORE TIMESTAMP
my_date;
FLASHBACK DATABASE TO SCN my_scn;
FLASHBACK DATABASE TO BEFORE SCN my_scn;
The
window of time that is available for flashback is determined by the DB_FLASHBACK_RETENTION_TARGET parameter. The maximum
flashback can be determined by querying the V$FLASHBACK_DATABASE_LOG view. It is only possible
to flashback to a point in time after flashback was enabled on the database and
since the last RESETLOGS command.
Flashback
Query Functions
The
TIMESTAMP_TO_SCN and SCN_TO_TIMESTAMP functions have been added
to SQL and PL/SQL to simplify flashback operations:
SELECT *
FROM
emp AS OF SCN TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/24);
SELECT *
FROM
emp AS OF TIMESTAMP SCN_TO_TIMESTAMP(993240);
DECLARE
l_scn NUMBER;
l_timestamp TIMESTAMP;
BEGIN
l_scn :=
TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/24);
l_timestamp := SCN_TO_TIMESTAMP(l_scn);
END;
/
No comments:
Post a Comment