/*
|| Oracle 11g Flashback Transaction Backout Listing
||
|| Demonstrates Oracle Database 11g's LogMiner and Flashback Transaction Backout
|| (FTB) features, including:
|| - Creation of sample objects
|| - Creation of sample transactions
|| - Queries against LogMiner and FTB metadata
|
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| These examples are provided to demonstrate various features of Oracle 11gR1
|| Flashback Transaction Backout features, and they should be carefully proofread
|| before being executed against any existing Oracle database to avoid potential
|| damage!
*/
/*
|| Listing 1:
|| Preparing for FTB use:
|| 1.) Create objects for FTB target operations
|| 2.) Enable supplemental logging on primary key columns
|| 3.) Grant appropriate privileges to qualified user accounts
*/
DROP USER ap CASCADE;
CREATE USER ap
IDENTIFIED BY ap
DEFAULT TABLESPACE example
TEMPORARY TABLESPACE temp
PROFILE DEFAULT
QUOTA UNLIMITED ON example
;
GRANT CONNECT, RESOURCE TO ap;
GRANT CREATE PROCEDURE TO ap;
GRANT CREATE PUBLIC SYNONYM TO ap;
GRANT CREATE SEQUENCE TO ap;
GRANT CREATE SESSION TO ap;
GRANT CREATE SYNONYM TO ap;
GRANT CREATE TABLE TO ap;
GRANT DROP PUBLIC SYNONYM TO ap;
GRANT EXECUTE ANY PROCEDURE TO ap;
GRANT ap_read_only TO ap;
GRANT ap_read_only TO ap_secured;
DROP TABLE ap.vendors CASCADE CONSTRAINTS PURGE;
/*
|| Create AP.VENDORS and related objects
*/
-- Create table
CREATE TABLE ap.vendors (
vendor_id NUMBER NOT NULL
,active_ind CHAR(1) DEFAULT 'Y' NOT NULL
,name VARCHAR2(128) NOT NULL
,address_line_1 VARCHAR2(40) DEFAULT 'UNDEFINED' NOT NULL
,address_line_2 VARCHAR2(40)
,address_line_3 VARCHAR2(40)
,city VARCHAR2(40) NOT NULL
,state CHAR(2)
,country VARCHAR2(30) NOT NULL
,credit_card VARCHAR2(16) NOT NULL
,credit_limit NUMBER NOT NULL
)
TABLESPACE example;
-- Create indexes and constraints
ALTER TABLE ap.vendors
ADD CONSTRAINT vendors_pk
PRIMARY KEY (vendor_id)
USING INDEX (
CREATE INDEX ap.vendors_pk_idx
ON ap.vendors (vendor_id)
TABLESPACE example
);
ALTER TABLE ap.vendors
ADD CONSTRAINT vendors_active_ck
CHECK (active_ind IN ('Y','N'));
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
GRANT EXECUTE ON DBMS_FLASHBACK TO ap;
GRANT EXECUTE ON DBMS_LOCK TO ap;
GRANT SELECT ANY TRANSACTION TO ap;
/*
|| Listing 2:
|| Setting up transactions for FTB evaluation
*/
-- Create transactions for later evaluation with LogMiner and FTB
-----
-- Load test data into AP.VENDORS
-----
TRUNCATE TABLE ap.vendors;
INSERT INTO ap.vendors
VALUES(
101
,'Y'
,'FacesRUs, Inc.'
,'1313 Mockingbird Lane'
,NULL
,NULL
,'Seattle'
,'WA'
,'USA'
,3428179310003762
,100000.00
);
INSERT INTO ap.vendors
VALUES(
102
,'Y'
,'Scared Silly Makeup Gmbh'
,'1221 Frightful Plaza'
,'Suite 221'
,NULL
,'Redmond'
,'CA'
,'USA'
,5968100071234567
,75000.00
);
INSERT INTO ap.vendors
VALUES(
103
,'Y'
,'Blood Imitation Products, LLC'
,'1313 Gorey Lane'
,NULL
,NULL
,'Reston'
,'VA'
,'USA'
,6879110044283978
,35000.00
);
INSERT INTO ap.vendors
VALUES(
104
,'Y'
,'Tri-Color Corn NA'
,'1414 Epicurean Blvd.'
,NULL
,NULL
,'San Francisco'
,'CA'
,'USA'
,5967100039131004
,22500.00
);
INSERT INTO ap.vendors
VALUES(
105
,'Y'
,'Superhero Costumes Corporation'
,'1505 Clark Kent Drive'
,'Suite 200'
,NULL
,'Metropolis'
,'IL'
,'USA'
,38271780012103
,100000.00
);
COMMIT;
ALTER SYSTEM SWITCH LOGFILE;
-----
-- Next, apply two UPDATE statements against the sample data
-----
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE('Beginning SCN Range (before transactions): ' || DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER());
END;
/
SELECT TO_CHAR(SYSTIMESTAMP, 'yyyy-mm-dd hh24:mi:ss') "Current Time" FROM DUAL;
SET SERVEROUTPUT ON
BEGIN
-- Wait for 15 seconds
DBMS_LOCK.SLEEP(15);
UPDATE ap.vendors
SET address_line_2 = 'c/o Vlad Dracul'
,address_line_3 = 'Suite 666'
WHERE vendor_id = 101;
UPDATE ap.vendors
SET address_line_2 = 'c/o Victor Frahnkensteen'
,address_line_3 = 'Head Surgeon'
WHERE vendor_id = 102;
COMMIT;
DBMS_OUTPUT.PUT_LINE('SCN for Transaction #1: ' || DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER());
END;
/
ALTER SYSTEM SWITCH LOGFILE;
SELECT TO_CHAR(SYSTIMESTAMP, 'yyyy-mm-dd hh24:mi:ss') "Current Time" FROM DUAL;
/*
|| Listing 3:
|| Viewing LogMiner information about potential candidate transactions for FTB
*/
-----
-- How many transactions have affected the AP schema for a limited
-- specific SCN range?
------
TTITLE 'Summary of LogMiner Findings|(from V$LOGMNR_CONTENTS)'
COL scn FORMAT 99999999 HEADING 'SCN'
COL xid HEADING 'TransXID'
COL seg_owner FORMAT A08 HEADING 'Owner'
COL table_name FORMAT A15 HEADING 'Table Name'
COL sql_redo FORMAT A40 HEADING 'SQL Stmt' WRAP
COL sql_undo FORMAT A40 HEADING 'Reciprocal' WRAP
SELECT
seg_owner
,table_name
,xid
,scn
,COUNT(*)
FROM v$logmnr_contents
WHERE seg_owner = 'AP'
GROUP BY seg_owner, table_name, xid, scn
ORDER BY seg_owner, table_name, xid, scn
;
TTITLE OFF
Sat Dec 20 page
Summary of LogMiner Findings
(from V$LOGMNR_CONTENTS)
Owner Table Name TransXID SCN COUNT(*)
-------- --------------- ---------------- --------- ----------
AP VENDORS 01001E0010050000 1834296 1
AP VENDORS 010020000F050000 1834348 5
AP VENDORS 020001000B050000 1834285 1
AP VENDORS 03000100AA040000 1834341 1
AP VENDORS 05000F000D050000 1834362 2
AP VENDORS 07000300A1040000 1834091 1
AP VENDORS 0800000000050000 1834261 1
AP 020001000B050000 1834282 1
AP 02000B000B050000 1834144 1
AP 090006007A060000 1834130 1
10 rows selected.
-----
-- What are the actual LOGMINER detailed contents?
------
TTITLE 'Detailed Transactions and Reciprocals|(from V$LOGMNR_CONTENTS)'
COL scn FORMAT 99999999 HEADING 'SCN'
COL xid HEADING 'TransXID'
COL seg_owner FORMAT A08 HEADING 'Owner'
COL table_name FORMAT A15 HEADING 'Table Name'
COL sql_redo FORMAT A40 HEADING 'SQL Stmt' WRAP
COL sql_undo FORMAT A40 HEADING 'Reciprocal' WRAP
SELECT
scn
,xid
,seg_owner
,table_name
,sql_redo
,sql_undo
FROM v$logmnr_contents
WHERE seg_owner = 'AP'
;
TTITLE OFF
Detailed Transactions and Reciprocals
(from V$LOGMNR_CONTENTS)
SCN TransXID Owner Table Name Operatio SQL Stmt Reciprocal
--------- ---------------- ------ --------------- -------- ---------------------------------------- ----------------------------------------
1834348 010020000F050000 AP VENDORS INSERT insert into "AP"."VENDORS"("VENDOR_ID"," delete from "AP"."VENDORS" where "VENDOR
ACTIVE_IND","NAME","ADDRESS_LINE_1","ADD _ID" = '101' and "ACTIVE_IND" = 'Y' and
RESS_LINE_2","ADDRESS_LINE_3","CITY","ST "NAME" = 'FacesRUs, Inc.' and "ADDRESS_L
ATE","COUNTRY","CREDIT_CARD","CREDIT_LIM INE_1" = '1313 Mockingbird Lane' and "AD
IT") values ('101','Y','FacesRUs, Inc.', DRESS_LINE_2" IS NULL and "ADDRESS_LINE_
'1313 Mockingbird Lane',NULL,NULL,'Seatt 3" IS NULL and "CITY" = 'Seattle' and "S
le','WA','USA','3428179310003762','10000 TATE" = 'WA' and "COUNTRY" = 'USA' and "
0'); CREDIT_CARD" = '3428179310003762' and "C
REDIT_LIMIT" = '100000' and ROWID = 'AAA
SAbAAFAAAAG8AAA';
1834348 010020000F050000 AP VENDORS INSERT insert into "AP"."VENDORS"("VENDOR_ID"," delete from "AP"."VENDORS" where "VENDOR
ACTIVE_IND","NAME","ADDRESS_LINE_1","ADD _ID" = '102' and "ACTIVE_IND" = 'Y' and
RESS_LINE_2","ADDRESS_LINE_3","CITY","ST "NAME" = 'Scared Silly Makeup Gmbh' and
ATE","COUNTRY","CREDIT_CARD","CREDIT_LIM "ADDRESS_LINE_1" = '1221 Frightful Plaza
IT") values ('102','Y','Scared Silly Mak ' and "ADDRESS_LINE_2" = 'Suite 221' and
eup Gmbh','1221 Frightful Plaza','Suite "ADDRESS_LINE_3" IS NULL and "CITY" = '
221',NULL,'Redmond','CA','USA','59681000 Redmond' and "STATE" = 'CA' and "COUNTRY
71234567','75000'); " = 'USA' and "CREDIT_CARD" = '596810007
1234567' and "CREDIT_LIMIT" = '75000' an
d ROWID = 'AAASAbAAFAAAAG8AAB';
1834348 010020000F050000 AP VENDORS INSERT insert into "AP"."VENDORS"("VENDOR_ID"," delete from "AP"."VENDORS" where "VENDOR
ACTIVE_IND","NAME","ADDRESS_LINE_1","ADD _ID" = '103' and "ACTIVE_IND" = 'Y' and
RESS_LINE_2","ADDRESS_LINE_3","CITY","ST "NAME" = 'Blood Imitation Products, LLC'
ATE","COUNTRY","CREDIT_CARD","CREDIT_LIM and "ADDRESS_LINE_1" = '1313 Gorey Lane
IT") values ('103','Y','Blood Imitation ' and "ADDRESS_LINE_2" IS NULL and "ADDR
Products, LLC','1313 Gorey Lane',NULL,NU ESS_LINE_3" IS NULL and "CITY" = 'Reston
LL,'Reston','VA','USA','6879110044283978 ' and "STATE" = 'VA' and "COUNTRY" = 'US
','35000'); A' and "CREDIT_CARD" = '6879110044283978
' and "CREDIT_LIMIT" = '35000' and ROWID
= 'AAASAbAAFAAAAG8AAC';
1834348 010020000F050000 AP VENDORS INSERT insert into "AP"."VENDORS"("VENDOR_ID"," delete from "AP"."VENDORS" where "VENDOR
ACTIVE_IND","NAME","ADDRESS_LINE_1","ADD _ID" = '104' and "ACTIVE_IND" = 'Y' and
RESS_LINE_2","ADDRESS_LINE_3","CITY","ST "NAME" = 'Tri-Color Corn NA' and "ADDRES
ATE","COUNTRY","CREDIT_CARD","CREDIT_LIM S_LINE_1" = '1414 Epicurean Blvd.' and "
IT") values ('104','Y','Tri-Color Corn N ADDRESS_LINE_2" IS NULL and "ADDRESS_LIN
A','1414 Epicurean Blvd.',NULL,NULL,'San E_3" IS NULL and "CITY" = 'San Francisco
Francisco','CA','USA','5967100039131004 ' and "STATE" = 'CA' and "COUNTRY" = 'US
','22500'); A' and "CREDIT_CARD" = '5967100039131004
' and "CREDIT_LIMIT" = '22500' and ROWID
= 'AAASAbAAFAAAAG8AAD';
1834348 010020000F050000 AP VENDORS INSERT insert into "AP"."VENDORS"("VENDOR_ID"," delete from "AP"."VENDORS" where "VENDOR
ACTIVE_IND","NAME","ADDRESS_LINE_1","ADD _ID" = '105' and "ACTIVE_IND" = 'Y' and
RESS_LINE_2","ADDRESS_LINE_3","CITY","ST "NAME" = 'Superhero Costumes Corporation
ATE","COUNTRY","CREDIT_CARD","CREDIT_LIM ' and "ADDRESS_LINE_1" = '1505 Clark Ken
IT") values ('105','Y','Superhero Costum t Drive' and "ADDRESS_LINE_2" = 'Suite 2
es Corporation','1505 Clark Kent Drive', 00' and "ADDRESS_LINE_3" IS NULL and "CI
'Suite 200',NULL,'Metropolis','IL','USA' TY" = 'Metropolis' and "STATE" = 'IL' an
,'38271780012103','100000'); d "COUNTRY" = 'USA' and "CREDIT_CARD" =
'38271780012103' and "CREDIT_LIMIT" = '1
00000' and ROWID = 'AAASAbAAFAAAAG8AAE';
1834362 05000F000D050000 AP VENDORS UPDATE update "AP"."VENDORS" set "ADDRESS_LINE_ update "AP"."VENDORS" set "ADDRESS_LINE_
2" = 'c/o Vlad Dracul', "ADDRESS_LINE_3" 2" = NULL, "ADDRESS_LINE_3" = NULL where
= 'Suite 666' where "VENDOR_ID" = '101' "VENDOR_ID" = '101' and "ADDRESS_LINE_2
and "ADDRESS_LINE_2" IS NULL and "ADDRE " = 'c/o Vlad Dracul' and "ADDRESS_LINE_
SS_LINE_3" IS NULL and ROWID = 'AAASAbAA 3" = 'Suite 666' and ROWID = 'AAASAbAAFA
FAAAAG8AAA'; AAAG8AAA';
1834362 05000F000D050000 AP VENDORS UPDATE update "AP"."VENDORS" set "ADDRESS_LINE_ update "AP"."VENDORS" set "ADDRESS_LINE_
2" = 'c/o Victor Frahnkensteen', "ADDRES 2" = 'Suite 221', "ADDRESS_LINE_3" = NUL
S_LINE_3" = 'Head Surgeon' where "VENDOR L where "VENDOR_ID" = '102' and "ADDRESS
_ID" = '102' and "ADDRESS_LINE_2" = 'Sui _LINE_2" = 'c/o Victor Frahnkensteen' an
te 221' and "ADDRESS_LINE_3" IS NULL and d "ADDRESS_LINE_3" = 'Head Surgeon' and
ROWID = 'AAASAbAAFAAAAG8AAB'; ROWID = 'AAASAbAAFAAAAG8AAB';
/*
|| Listing 4:
|| Viewing FTB Details
*/
TTITLE 'Flashback Transaction Backout Details|(From DBA_FLASHBACK_TXN_REPORT)'
COL txnname FORMAT A35 HEADING "Compensating|Transaction|Name" WRAP
COL commit_dtm FORMAT A11 HEADING "COMMIT|Timestamp" WRAP
COL xidrpt FORMAT A60 HEADING "Compensating|Transaction|Details" WRAP
SELECT
compensating_txn_name txnname
,TO_CHAR(commit_time, 'yyyy-mm-dd hh24:mi:ss') commit_dtm
,TO_CHAR(xid_report) xidrpt
FROM dba_flashback_txn_report
;
TTITLE OFF
Flashback Transaction Backout Details
(From DBA_FLASHBACK_TXN_REPORT)
Compensating Compensating
Transaction COMMIT Transaction
Name Timestamp Details
----------------------------------- ----------- ------------------------------------------------------------
_SYS_COMP_TXN_7410033_TIM_1230612995 2008-12-29 <?xml version="1.0" encoding="ISO-8859-1"?>
22:57:27 <COMP_XID_REPORT XID="0A001C00AD040000" NAME="_SYS_COMP_TXN_
7410033_TIM_1230612995">
<TRANSACTION XID="05000F000D050000">
<CHARACTERISTICS>
</CHARACTERISTICS>
<UNDO_SQL>
<USQL exec="yes">
update "AP"."VENDORS" set "ADDRESS_LINE_2" = 'Suite 221',
"ADDRESS_LINE_3" = NULL where "VENDOR_ID" = '102' and "ADDR
ESS_LINE_2" = 'c/o Victor Frahnkensteen' and "ADDRESS_LINE_3
" = 'Head Surgeon'
</USQL>
<USQL exec="yes">
update "AP"."VENDORS" set "ADDRESS_LINE_2" = NULL, "ADDRE
SS_LINE_3" = NULL where "VENDOR_ID" = '101' and "ADDRESS_LIN
E_2" = 'c/o Vlad Dracul' and "ADDRESS_LINE_3" = 'Suite 666'
</USQL>
</UNDO_SQL>
<DEPENDENT_XIDS>
</DEPENDENT_XIDS>
</TRANSACTION>
<EXECUTED_UNDO_SQL>
<EXEC_USQL>update "AP"."VENDORS" set "ADDRESS_LINE_2" = 'Sui
te 221', "ADDRESS_LINE_3" = NULL where "VENDOR_ID" = '102' a
nd "ADDRESS_LINE_2" = 'c/o Victor Frahnkensteen' and "ADDRES
S_LINE_3" = 'Head Surgeon'
</EXEC_USQL>
<EXEC_USQL>update "AP"."VENDORS" set "ADDRESS_LINE_2" = NULL
, "ADDRESS_LINE_3" = NULL where "VENDOR_ID" = '101' and "ADD
RESS_LINE_2" = 'c/o Vlad Dracul' and "ADDRESS_LINE_3" = 'Sui
te 666'
</EXEC_USQL>
</EXECUTED_UNDO_SQL>
</COMP_XID_REPORT>
TTITLE 'Current Flashback Transaction Backout State|(From DBA_FLASHBACK_TXN_STATE)'
COL cmp_xid HEADING "Compensating|XID"
COL trn_xid HEADING "Transaction|XID"
COL dpd_xid HEADING "Dependent|XID"
COL backout_mode FORMAT A12 HEADING "Backout|Mode"
COL username FORMAT A12 HEADING "UserName"
SELECT
RAWTOHEX(compensating_xid) cmp_xid
,RAWTOHEX(xid) trn_xid
,RAWTOHEX(dependent_xid) dpd_xid
,backout_mode
,username
FROM dba_flashback_txn_state
;
TTITLE OFF
Current Flashback Transaction Backout State
(From DBA_FLASHBACK_TXN_STATE)
Compensating Transaction Dependent Backout
XID XID XID Mode UserName
---------------- ---------------- ---------------- ------------ ------------
090014004D060000 09001B0049060000 NONCONFLICT_ SYS
ONLY
0A001C00AD040000 05000F000D050000 NONCONFLICT_ SYS
ONLY