What is Oracle Flashback Transaction Query?

English

When you perform an erroneous update or deletion in an Oracle Database, there are times when you want to accurately identify specific operations and restore them quickly. By utilizing Oracle Flashback Transaction Query (FLASHBACK_TRANSACTION_QUERY), you can automatically obtain restoration SQL from past operation histories, allowing for recovery with minimal downtime.

In this article, we provide a thorough explanation based on the 19c environment, covering everything from how to use FLASHBACK_TRANSACTION_QUERY to the configuration of supplemental logging required for restoration and actual recovery procedures.

Conclusion: The Fastest Procedure to Restore Transactions

  1. Enable Minimum Supplemental Logging: This is a mandatory setting to display the UNDO_SQL column.
  2. Identify the Target SCN: Use V$DATABASE or the VERSIONS clause to identify the SCN (System Change Number) at the time the operation occurred.
  3. Extract History: Retrieve the target XID (Transaction ID) and UNDO_SQL from the FLASHBACK_TRANSACTION_QUERY view.
  4. Execute Restoration SQL: Run the obtained UNDO_SQL and perform a COMMIT.

Background and Basics: Mechanism and Terminology Definitions

This feature utilizes UNDO data, which holds the data as it existed before changes, to reconstruct the reverse operation (e.g., a DELETE for an INSERT) in SQL format.

  • SCN (System Change Number): A logical timestamp used by Oracle to manage the sequence of transactions.
  • Supplemental Logging: A mode that records additional information in the logs to uniquely identify rows. If this is not enabled, the SQL for restoration will not be generated.

Procedures and Implementation: Environment Preparation and Data Manipulation

These are the verification steps on an actual machine. The following operations require SYSDBA privileges or SELECT ANY TRANSACTION privileges.

1. Enable Minimum Supplemental Logging

This is a mandatory setting to ensure values are correctly output to the UNDO_SQL column.

-- Check current settings (If "NO", activation is required)
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

-- Enable minimum supplemental logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- Confirm it is enabled (Should be "YES")
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

2. Create Verification Data

Create a test table and commit the initial data.

-- Create test table
CREATE TABLE test_flashback (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(100)
);

-- Insert initial data
INSERT INTO test_flashback VALUES (1, 'Apple');
INSERT INTO test_flashback VALUES (2, 'Banana');
COMMIT;

3. Check SCN Before Operations and Modify Data

To make it easier to track the history later, check the current SCN before updating and deleting data.

-- Get current SCN
SELECT CURRENT_SCN FROM V$DATABASE;

-- Update data
UPDATE test_flashback SET name = 'Avocado' WHERE id = 1;
COMMIT;

-- Delete data
DELETE FROM test_flashback WHERE id = 2;
COMMIT;

-- Get SCN after operations
SELECT CURRENT_SCN FROM V$DATABASE;

Execution Example: Checking Transaction History and Restoring

Identify the content of the erroneous operation and obtain/execute the SQL to revert it.

Execute History Check SQL

Query the FLASHBACK_TRANSACTION_QUERY view. Ensure the table name is specified in uppercase.

-- Format settings for display
SET LINESIZE 200
COL OPERATION FOR A15
COL UNDO_SQL FOR A60
COL TABLE_NAME FOR A20
COL LOGON_USER FOR A15

-- Extract history and restoration SQL
SELECT 
  XID,
  START_SCN,
  COMMIT_SCN,
  OPERATION,
  TABLE_NAME,
  LOGON_USER,
  UNDO_SQL
FROM FLASHBACK_TRANSACTION_QUERY
WHERE TABLE_NAME = 'TEST_FLASHBACK'
ORDER BY START_SCN DESC;
  • XID: An ID that uniquely identifies a transaction.
  • UNDO_SQL: Executing this SQL allows you to cancel the operation performed at that time.

Restore Data

Execute the output UNDO_SQL and finalize the changes.

-- Restore the deleted record (ID:2) (An INSERT statement is generated)
INSERT INTO "TEST1"."TEST_FLASHBACK"("ID","NAME") VALUES ('2','Banana');

-- Revert the updated name (ID:1) (An UPDATE statement is generated)
UPDATE "TEST1"."TEST_FLASHBACK" SET "NAME" = 'Apple' WHERE ROWID = 'AAASJTAAHAAAAGGAAA';

COMMIT;

-- Final verification of data
SELECT * FROM test_flashback;

Full Execution Log

SQL> SELECT supplemental_log_data_min FROM v$database;

SUPPLEMENTAL_LOG_DATA_MI
------------------------
NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;  ★Set minimum supplemental logging

Database altered.

SQL> SELECT supplemental_log_data_min FROM v$database;

SUPPLEMENTAL_LOG_DATA_MI
------------------------
YES

SQL> alter system switch logfile;

System altered.

SQL> CREATE TABLE test_flashback (
  2    id NUMBER PRIMARY KEY,
  3    name VARCHAR2(100)
  4  );

Table created.

SQL> INSERT INTO test_flashback VALUES (1, 'Apple');

1 row created.

SQL> INSERT INTO test_flashback VALUES (2, 'Banana');

1 row created.

SQL> col name for a30
SQL> SELECT * FROM test_flashback;

        ID NAME
---------- ------------------------------
         1 Apple
         2 Banana

SQL> commit;

Commit complete.

SQL> SELECT CURRENT_SCN FROM v$database;

CURRENT_SCN
-----------
    2298362

SQL> UPDATE test_flashback SET name = 'Avocado' WHERE id = 1;  ★Update

1 row updated.

SQL> SELECT * FROM test_flashback;

        ID NAME
---------- ------------------------------
         1 Avocado
         2 Banana

SQL> commit;

Commit complete.

SQL> DELETE FROM test_flashback WHERE id = 2;  ★Delete

1 row deleted.

SQL> SELECT * FROM test_flashback;

        ID NAME
---------- ------------------------------
         1 Avocado

SQL> commit;

Commit complete.

SQL> SELECT CURRENT_SCN FROM v$database;

CURRENT_SCN
-----------
    2298475

SQL> set linesize 120
SQL> col operation for a20
SQL> col undo_sql for a100
SQL> col table_name for a20
SQL> col logon_user for a20
SQL> SELECT
  2    XID,
  3    START_SCN,
  4    COMMIT_SCN,
  5    OPERATION,
  6    TABLE_NAME,
  7    LOGON_USER,
  8    UNDO_SQL
  9  FROM FLASHBACK_TRANSACTION_QUERY
 10  WHERE TABLE_NAME = 'TEST_FLASHBACK'
 11  ORDER BY START_SCN DESC;

XID               START_SCN COMMIT_SCN OPERATION            TABLE_NAME           LOGON_USER
---------------- ---------- ---------- -------------------- -------------------- --------------------
UNDO_SQL
----------------------------------------------------------------------------------------------------
06001800F2030000    2298428    2298460 DELETE               TEST_FLASHBACK       TEST1
insert into "TEST1"."TEST_FLASHBACK"("ID","NAME") values ('2','Banana');  ★

07002000F1030000    2298377    2298408 UPDATE               TEST_FLASHBACK       TEST1
update "TEST1"."TEST_FLASHBACK" set "NAME" = 'Apple' where ROWID = 'AAASJTAAHAAAAGGAAA';  ★

0800160017040000    2298274    2298341 INSERT               TEST_FLASHBACK       TEST1
delete from "TEST1"."TEST_FLASHBACK" where ROWID = 'AAASJTAAHAAAAGGAAB';

0800160017040000    2298274    2298341 INSERT               TEST_FLASHBACK       TEST1
delete from "TEST1"."TEST_FLASHBACK" where ROWID = 'AAASJTAAHAAAAGGAAA';

SQL> insert into "TEST1"."TEST_FLASHBACK"("ID","NAME") values ('2','Banana');  ★

1 row created.

SQL> update "TEST1"."TEST_FLASHBACK" set "NAME" = 'Apple' where ROWID = 'AAASJTAAHAAAAGGAAA';  ★

1 row updated.

SQL> SELECT * FROM test_flashback;  ★Returned to original state

        ID NAME
---------- ------------------------------
         1 Apple
         2 Banana

SQL> commit;

Commit complete.

Troubleshooting: Typical Errors

Error / PhenomenonCauseAction
UNDO_SQL is NULLSupplemental logging is not set.Enable the setting. Only operations after the setting change will be targeted.
ORA-01466Reference immediately after table definition change.Flashback queries are restricted immediately after DDL (table definition changes).
History is not displayedExceeded UNDO retention period.Check the UNDO_RETENTION parameter. Old history will be overwritten.

Operational, Monitoring, and Security Notes

  • Privilege Management: Since FLASHBACK_TRANSACTION_QUERY allows visibility into transactions across the entire database, keep privilege granting to a minimum.
  • UNDO Period: History retention depends on UNDO_RETENTION. In environments with frequent large-scale updates, there is a risk that history will disappear quickly.
  • Consistency: Before executing UNDO_SQL, always check if it contradicts the current data state (e.g., unique constraints).

FAQ: How to use Oracle SQL Flashback

Q1: Can I look up history by time instead of SCN?

A: Yes, by using the VERSIONS BETWEEN TIMESTAMP clause, you can check change history by specifying a date and time range.

Q2: Can I restore operations performed before enabling supplemental logging?

A: No, only transactions that occur after the setting change are eligible for UNDO_SQL generation.

Q3: Can I recover a dropped (DROP) table itself?

A: This feature is for restoring data (rows). To recover the table itself, use FLASHBACK TABLE ... TO BEFORE DROP (Recycle Bin feature).

Summary

  • Required Setting: Execute ALTER DATABASE ADD SUPPLEMENTAL LOG DATA before starting operations.
  • Investigation: Identify the UNDO_SQL using FLASHBACK_TRANSACTION_QUERY.
  • Restoration: Execute the obtained SQL and perform a COMMIT.
  • Note: Action must be taken within the UNDO retention period.

This article covers Oracle Database 19c (screens and default values may differ for other versions).

[reference]
Using Oracle Flashback Technology

How to Perform Backup and Recovery Using Oracle RMAN Commands
In Oracle Database operations, automating and streamlining backup and recovery is essential. This article explains the p…
oracle-master00000.com
2026.03.27

コメント

Copied title and URL