Oracle | DBMS_FLASHBACK

Oracle DBMS_FLASHBACK Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Part of this package was developed before the 10g release of command-line Flashback capabilities ... and is now obsolete. TRANSACTION_BACKOUT is a new capability added in 11g and is priceless.

AUTHID CURRENT_USER

Constants for Transaction Backout

Name	Data Type	Value
nocascade	BINARY_INTEGER	1
Default: Backs out specified transactions, expected to have no dependent transactions. If dependents an exception is raised and the error can be found in DBA_FLASHBACK_TXN_REPORT.
nocascade_force	BINARY_INTEGER	2
Backs out specified transactions, ignoring dependent transactions. Backout happens in reverse order of commit times.
noconflict_only	BINARY_INTEGER	3
Backs out changes to nonconflicting rows. The database remains consistent but atomicity is lost.
cascade	BINARY_INTEGER	4
Backs out specified transactions and all dependent transactions in a post-order fashion (children first: parents second).

Data Types Used CREATE OR REPLACE TYPE "SYS"."TXNAME_ARRAY" AS VARRAY(100) OF VARCHAR2(256);

Dependencies

DBMS_CAPTURE_ADM_INTERNAL	DBMS_STREAMS_RPC_INTERNAL	TIMESTAMP_TO_SCN
DBMS_LOGMNR_INTERNAL	DBMS_TRAN_LIB	TRANSACTION_BACKOUT_REPORT$
DBMS_LOGREP_EXP	DBMS_XSTREAM_UTL_IVK	TRANSACTION_BACKOUT_STATE$
DBMS_STREAMS_ADM_IVK	KUPM$MCP	TXNAME_ARRAY
DBMS_STREAMS_DATAPUMP	LOGMNR_DDL_TRIGGER_PROC	XID_ARRAY

Documented Yes: Packages and Types Reference

Exceptions

Error Code	Reason
ORA-08180	Time specified is too old
ORA-08181	Invalid system change number specified
ORA-08182	User cannot begin read-only or serializable transactions in Flashback mode
ORA-08183	User cannot enable Flashback within an uncommitted transaction
ORA-08184	User cannot enable Flashback within another Flashback session
ORA-08185	SYS cannot enable Flashback mode

First Available 9.0

Related Sysem Privileges FLASHBACK ANY TABLE

Security Model Owned by SYS with EXECUTE granted to MDSYS and the DBA role

Source {ORACLE_HOME}/rdbms/admin/dbmstran.sql

Subprograms

DISABLE	ENABLE_AT_TIME	TRANSACTION_BACKOUT
ENABLE_AT_SYSTEM_CHANGE_NUMBER	GET_SYSTEM_CHANGE_NUMBER

DISABLE

End Flashback Mode dbms_flashback.disable;

See Demo At Page Bottom

ENABLE_AT_SYSTEM_CHANGE_NUMBER

Enable flashback mode as of a specific SCN dbms_flashback.enable_at_system_change_number(query_scn IN NUMBER);

See Demo At Page Bottom

ENABLE_AT_TIME

Enable Flashback Mode As Of A Point-In-Time dbms_flashback.enable_at_time(query_time IN TIMESTAMP);

See Demo At Page Bottom

GET_SYSTEM_CHANGE_NUMBER

Get the current SCN dbms_flashback.get_system_change_number RETURN NUMBER;

SELECT dbms_flashback.get_system_change_number

      FROM dual;

TRANSACTION_BACKOUT

Transaction backout interface

Overload 1 dbms_flashback.transaction_backout( numtxns NUMBER, xids xid_array, options BINARY_INTEGER DEFAULT NOCASCADE, scnhint NUMBER DEFAULT 0);

conn sys@pdbdev as sysdba

      

      shutdown immediate;

      

      startup mount;

      

      alter database archivelog;

      alter database open;

      alter system archive log current;

      alter database add supplemental log data;

      

      conn uwclass/uwclass@pdbdev

      

      CREATE TABLE t1 (

      testcol VARCHAR2(3));

      

      CREATE TABLE t2 (

      testcol VARCHAR2(3));

      

      CREATE OR REPLACE TRIGGER row_level

      BEFORE INSERT

      ON t1

      FOR EACH ROW

      

      BEGIN

        INSERT INTO t2

        VALUES

        (:NEW.testcol);

      END row_level;

      /

      

      BEGIN

        INSERT INTO t1 VALUES ('ABC');

        INSERT INTO t1 VALUES ('DEF');

        COMMIT;

        user_lock.sleep(500);

        INSERT INTO t1 VALUES ('GHI');

        INSERT INTO t1 VALUES ('JKL');

        COMMIT;

        user_lock.sleep(500);

        INSERT INTO t1 VALUES ('MNO');

        COMMIT;

        user_lock.sleep(500);

      END;

      /

      

      SELECT * FROM t1;

      SELECT * FROM t2;

      

      SELECT versions_xid, versions_startscn, versions_endscn, versions_operation, testcol

      FROM t1

      VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;

      

      desc sys.xid_array

      

      SELECT table_name

      FROM user_all_tables;

      

      set serveroutput on

      

      -- choose the middle xid

      DECLARE

       xa sys.xid_array := sys.xid_array();

      BEGIN

        xa.extend;

        dbms_output.put_line(xa.last);

        xa(1) := '04000700A2020000';

        dbms_flashback.transaction_backout(1, xa);

      END;

      /

      

      SELECT * FROM t1;

      SELECT * FROM t2;

      

      COMMIT;

      

      SELECT table_name

      FROM user_all_tables;

      

      desc sys_ktftb_sql_table

      

      SELECT * FROM sys_ktftb_sql_table;

      

      desc sys_ktftb_key_table

      

      SELECT * FROM sys_ktftb_key_table;

Overload 2 dbms_flashback.transaction_backout( numtxns NUMBER, xids IN xid_array, options IN BINARY_INTEGER DEFAULT NOCASCADE, timehint IN TIMESTAMP);

TBD

Overload 3 dbms_flashback.transaction_backout( numtxns NUMBER, names txname_array, options BINARY_INTEGER DEFAULT NOCASCADE, scnhint NUMBER DEFAULT 0);

TBD

Overload 4 dbms_flashback.transaction_backout( numtxns NUMBER, names txname_array, options BINARY_INTEGER DEFAULT NOCASCADE, timehint TIMESTAMP);

TBD

Demos

DBMS_FLASHBACK by System Change Number conn uwclass/uwclass@pdbdev SELECT COUNT(*) FROM serv_inst; CREATE TABLE sibak AS SELECT * FROM serv_inst; VARIABLE scn_save NUMBER; exec :scn_save := dbms_flashback.get_system_change_number; print scn_save SELECT COUNT(*) FROM serv_inst; DELETE FROM serv_inst; COMMIT; SELECT COUNT(*) FROM serv_inst; DECLARE TYPE si_array IS TABLE OF serv_inst%ROWTYPE; si_data si_array; CURSOR flash_cur IS SELECT * FROM serv_inst; flash_rec flash_cur%ROWTYPE; BEGIN dbms_flashback.enable_at_system_change_number(:scn_save); OPEN flash_cur; dbms_flashback.disable; LOOP FETCH flash_cur BULK COLLECT INTO si_data LIMIT 250; FORALL i IN 1..si_data.COUNT INSERT INTO serv_inst VALUES si_data(i); EXIT WHEN flash_cur%NOTFOUND; END LOOP; COMMIT; CLOSE flash_cur; END; /

DBMS_FLASHBACK by Timestamp Recovery SELECT COUNT(*) FROM SERVERS; CREATE TABLE sbak AS SELECT * FROM servers; DELETE FROM servers; COMMIT; SELECT COUNT(*) FROM servers; exec dbms_flashback.enable_at_time(SYSTIMESTAMP - 10/1440); SELECT COUNT(*) FROM servers; SELECT * FROM servers; exec dbms_flashback.disable; SELECT * FROM servers; INSERT INTO servers SELECT * FROM sbak; COMMIT;