Flashback New Features and Enhancements in Oracle Database


     

Flashback New Features and Enhancements in Oracle Database


Flashback Query
Flashback Version Query
Flashback Transaction Query
Flashback Table
Flashback Drop (Recycle Bin)

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 of 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;

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;


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
FROM   flashback_transaction_query
WHERE  xid = HEXTORAW('0600030021000000');



Flashback Table – (Note:-  Flashback Table not supported for user SYS)

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

Sql> FLASHBACK TABLE flashback_drop_test TO BEFORE DROP;

Sql> SELECT * FROM flashback_drop_test;

        ID
----------
         1
Tables in the recycle bin can be queried like any other table:

Sql> DROP TABLE flashback_drop_test;

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

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 DATA ARCHIVE ( 11G r-2)

-- create the Flashback Data Archive

CREATE FLASHBACK ARCHIVE DEFAULT fla1
 TABLESPACE tbs1
QUOTA 10G
RETENTION 5 YEAR
/


-- Specify the default Flashback Data Archive

ALTER FLASHBACK ARCHIVE fla1 SET DEFAULT
/

-- Enable Flashback Data Archive

Sys>  grant flashback archive administer to scott;


Scott> ALTER TABLE T1
FLASHBACK ARCHIVE
/



-- Optionally extra space can be added to Flashback data Archive

ALTER FLASHBACK ARCHIVE fla1
ADD TABLESPACE tbs1 QUOTA 5G
/


-- It is possible to change retention time

ALTER FLASHBACK ARCHIVE fla1 MODIFY RETENTION 7 YEAR
/


-- Purging the data in FBA

ALTER FLASHBACK ARCHIVE fla1
PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' day)
/

-- Droping the FBA

DROP FLASHBACK ARCHIVE fla1
/

*_FLASHBACK_ARCHIVE
*_FLASHBACK_ARCHIVE_TS
*_FLASHBACK_ARCHIVE_TABLES


FLASBACK  DATABASE

SHOW PARAMETER DB_FLASHBACK_RETENTION_TARGET

SELECT estimated_flashback_size,  flashback_size
FROM   V$FLASHBACK_DATABASE_LOG
/


SELECT oldest_flashback_scn, oldest_flashback_time
FROM   V$FLASHBACK_DATABASE_LOG
/


SELECT *
FROM   V$FLASHBACK_DATABASE_STAT
/


Guaranteed Restore Point

CREATE RESTORE POINT before_upgrade
GUARANTEE FLASHBACK DATABASE
/





Comments

Post a Comment

Welcome to the Oracle DBA Blog.

Stay Hungry Stay Foolish

Popular posts from this blog

ORACLE BACKUP AND RECOVERY

WHAT IS BACKUP AND RECOVERY IN ORACLE ·         Backup Strategy -           Whole DB backup -           Partial DB backup ·         Backup Type -           Full DB backup -           Incremental DB Backup ·         Backup Mode -           Online( Inconsistent ) DB backup -           Offline ( Closed/Consistent ) DB Backup Consistent and Inconsistent Backups A consistent backup is one in which the files being backed up contain all changes up to the same system change number (SCN) . This means that the files in the backup contain all the data taken from a same point in time. Unlike an inconsistent backup, a consistent whole database backup does not require recovery after it is restored. The only way to make a consistent whole database backup is to shut down the database with the NORMAL , IMMEDIATE , or TRANSACTIONAL options and make the backup while the database is closed. The important point is that you can open the database after restoring a consistent who
Read more