Transaction Recovery is a feature of Oracle database that allows DBAs to recover from transactions which have undesirable effects. Sometimes it happens that a transaction performs an undesirable effect, and we determine later that this transaction should not have been committed. In this case, we do not want to perform database point-in-time recovery as the rest of database is working fine. We only have a problem with one transaction. Oracle allows us to provide approximate times of the problematic transaction and search the archived and online redo log files for transactions performed during said time. Then we can identify the transaction and roll it back. Transaction recovery is very easy to use from Oracle Enterprise Manager, but it requires some setup to perform before we can use it.

Configuration
The database needs to be in archivelog mode so that transactions not available in online log files can still be found

SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE
------------
NOARCHIVELOG

This database is in NOARCHIVELOG mode, so it needs to be altered

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area  855982080 bytes
Fixed Size                  2180544 bytes
Variable Size             641731136 bytes
Database Buffers          209715200 bytes
Redo Buffers                2355200 bytes
Database mounted.
SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE
------------
ARCHIVELOG

Additionally, we need to add supplemental logs so that Oracle store enough information to redo or undo committed transaction. Let’s add minimal and primary key columns supplemental logs:

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK
  2    FROM V$DATABASE;

SUPPLEME SUP
-------- ---
NO       NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

Database altered.

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK
  2    FROM V$DATABASE;

SUPPLEME SUP
-------- ---
YES      YES

Example
Let’s create a table and do two transactions on it. We’ll consider the second transaction as problematic and candidate for rollback/flashback:

SQL> CREATE TABLE tnx_test
  2    (anumber     NUMBER,
  3     avarchar2   VARCHAR2(10),
  4     adate       DATE
  5    );

Table created.

SQL> ALTER TABLE tnx_test ADD CONSTRAINT pk_txn_test PRIMARY KEY (anumber);

Table altered.

SQL> REM 'Wait for 5 seconds'
SQL> EXEC DBMS_LOCK.SLEEP(5);

PL/SQL procedure successfully completed.

SQL> INSERT INTO tnx_test VALUES (1, 'FIRST ROW', SYSDATE);

1 row created.

SQL> INSERT INTO tnx_test VALUES (2, 'SECOND ROW', SYSDATE);

1 row created.

SQL> INSERT INTO tnx_test VALUES (3, 'THIRD ROW', SYSDATE);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FIRST_TRANSACTION_TIME FROM DUAL;

FIRST_TR
--------
11:38:18

SQL> REM 'Wait for 10 seconds'
SQL> EXEC DBMS_LOCK.SLEEP(10);

PL/SQL procedure successfully completed.

SQL> UPDATE tnx_test SET avarchar2 = 'UPDATED' WHERE anumber = 1;

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') SECOND_TRANSACTION_TIME FROM DUAL;

SECOND_T
--------
11:38:29

SQL> SELECT anumber, avarchar2 FROM tnx_test;

   ANUMBER AVARCHAR2
---------- ----------
         1 UPDATED
         2 SECOND ROW
         3 THIRD ROW

Now, we can use Oracle Enterprise Manager to find these transactions and recover. Use the “View and Manage Transactions” link under the “Availability” menu

Provide “Start Time”, “End Time” with date and “Table” parameters to narrow down transaction search as shown in figure below

The following page shows transactions within our parameters. You can see that 4th transaction is one we are looking for. Click its “Transaction ID” to view its details

The following page shows the details of our transaction. “SQL Redo” is showing sql statement which we used to update a row on TNX_TEST. Click the “Flashback Transaction” to get rid of this transaction.

Following page is showing the details of transaction which is going to be flashback. Click the “Finish” button to perform flashback.

Let’s query our table to find original data.

SQL> SELECT anumber, avarchar2 FROM tnx_test;

   ANUMBER AVARCHAR2
---------- ----------
         1 FIRST ROW
         2 SECOND ROW
         3 THIRD ROW

Limitations

  • Truncate operations cannot be flashbacked.
  • If table structure is changed, then previous transactions on it cannot be flashbacked.
  • If a column is marked as UNUSED, then previous transactions on it cannot be flashbacked.
  • If table is dropped, then previous transactions on it cannot be flashbacked.

Link to script that contains the examples in this post.

Comment