Resolving Row and Transaction Inconsistencies

Resolving row and transaction inconsistencies might require a combination of Flashback Query, Flashback Version Query, Flashback Transaction Query, and the compensating SQL statements constructed from undo statements to rectify the

See Also: Oracle Database Administrator's Guide, Oracle Database Backup and Recovery Basics, and Oracle Database Concepts for more information about flashback technology and automatic undo management

problem. This section describes a general approach using a human resources example to resolve row and transaction inconsistencies caused by erroneous or malicious user errors.

Flashback Query, a feature introduced in the Oracle9i Database, enables an administrator or user to query any data at some point in time in the past. This powerful feature can be used to view and reconstruct data that might have been deleted or changed by accident. For example:

SELECT * FROM EMPLOYEES AS OF TIMESTAMP

TO_DATE('28-Jun-06 14:00','DD-Mon-YY HH24:MI') WHERE ...

This partial statement displays rows from the EMPLOYEES table starting from 2 p.m.

on June 28, 2006. Developers can use this feature to build self-service error correction into their applications, empowering end users to undo and correct their errors without delay, rather than burdening administrators to perform this task. Flashback Query is very simple to manage, because the database automatically keeps the necessary information to reconstruct data for a configurable time into the past.

Flashback Version Query provides a way to view changes made to the database at the row level. It is an extension to SQL and enables the retrieval of all the different versions of a row across a specified time interval. For example:

SELECT * FROM EMPLOYEES

VERSIONS BETWEEN TIMESTAMP

TO_DATE('28-Jun-06 14:00','dd-Mon-YY hh24:mi') AND TO_DATE('28-Jun-06 15:00','dd-Mon-YY hh24:mi') WHERE ...

This statement displays each version of the row, each entry changed by a different transaction, between 2 and 3 p.m. today. A DBA can use this to pinpoint when and how data is changed and trace it back to the user, application, or transaction. This enables the DBA to track down the source of a logical corruption in the database and correct it. It also enables application developers to debug their code.

Flashback Transaction Query provides a way to view changes made to the database at the transaction level. It is an extension to SQL that enables you to see all changes made by a transaction. For example:

SELECT UNDO_SQL

FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = '000200030000002D';

This statement shows all of the changes that resulted from this transaction. In addition, compensating SQL statements are returned and can be used to undo changes made to all rows by this transaction. Using a precision tool like this, the DBA and application developer can precisely diagnose and correct logical problems in the database or application.

Consider a human resources (HR) example involving the SCOTT schema. The HR manager reports to the DBA that there is a potential discrepancy in Ward's salary.

Sometime before 9:00 a.m., Ward's salary was increased to $1875. The HR manager is uncertain how this occurred and wishes to know when the employee's salary was increased. In addition, he instructed his staff to reset the salary to the previous level of

$1250. This was completed around 9:15 a.m.

The following steps show how to approach the problem.

1. Assess the problem.

Fortunately, the HR manager has provided information about the time when the change occurred. You can query the information as it was at 9:00 a.m. with Flashback Query.

SELECT EMPNO, ENAME, SAL FROM EMP

AS OF TIMESTAMP TO_DATE('28-JUN-06 09:00','dd-Mon-yy hh24:mi') WHERE ENAME = 'WARD';

EMPNO ENAME SAL 7521 WARD 1875

To can confirm that you have the correct employee by the fact that Ward's salary was $1875 at 09:00 a.m. Rather than using Ward's name, you can now use the employee number for subsequent investigation.

2. Query past rows or versions of the data to acquire transaction information.

Although it is possible to restrict the row version information to a specific date or SCN range, you might want to query all the row information that is available for the employee WARD using Flashback Version Query.

SELECT EMPNO, ENAME, SAL, VERSIONS_STARTTIME, VERSIONS_ENDTIME FROM EMP

VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE WHERE EMPNO = 7521

ORDER BY NVL(VERSIONS_STARTSCN,1);

EMPNO ENAME SAL VERSIONS_STARTTIME VERSIONS_ENDTIME --- --- --- 7521 WARD 1250 28-JUN-06 08.48.43 AM 28-JUN-06 08.54.49 AM 7521 WARD 1875 28-JUN-06 08.54.49 AM 28-JUN-06 09.10.09 AM 7521 WARD 1250 28-JUN-06 09.10.09 AM

You can see that WARD's salary was increased from $1250 to $1875 at 08:54:49 the same morning and was subsequently reset to $1250 at approximately 09:10:09.

Also, you can modify the query to determine the transaction information for each of the changes affecting WARD using a similar Flashback Version Query. This time use the VERSIONS_XID pseudocolumn.

SELECT EMPNO, ENAME, SAL, VERSIONS_XID FROM EMP

VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE WHERE EMPNO = 7521

ORDER BY NVL(VERSIONS_STARTSCN,1);

EMPNO ENAME SAL VERSIONS_XID --- --- --- 7521 WARD 1250 0006000800000086 7521 WARD 1875 0009000500000089 7521 WARD 1250 000800050000008B

The ID of the erroneous transaction that increased WARD's salary to $1875 was

"0009000500000089".

3. Query the erroneous transaction and the scope of its effect.

With the transaction information (VERSIONS_XID pseudocolumn), you can now query the database to determine the scope of the transaction, using Flashback Transaction Query.

SELECT UNDO_SQL

FROM FLASHBACK_TRANSACTION_QUERY

WHERE XID = HEXTORAW('0009000500000089');

UNDO_SQL

update "SCOTT"."EMP" set "SAL" = '950' where ROWID = 'AAACV4AAFAAAAKtAAL';

update "SCOTT"."EMP" set "SAL" = '1500' where ROWID = 'AAACV4AAFAAAAKtAAJ';

update "SCOTT"."EMP" set "SAL" = '2850' where ROWID = 'AAACV4AAFAAAAKtAAF';

update "SCOTT"."EMP" set "SAL" = '1250' where ROWID = 'AAACV4AAFAAAAKtAAE';

update "SCOTT"."EMP" set "SAL" = '1600' where ROWID = 'AAACV4AAFAAAAKtAAB';

6 rows selected.

You can see that WARD's salary was not the only change that occurred in the transaction. The information that was changed for the other four employees at the same time as WARD can now be passed back to the HR manager for review.

4. Determine if the corrective statements should be executed.

If the HR manager decides that the corrective changes suggested by the UNDO_SQL column are correct, then the DBA can execute those statements individually.

5. Query the FLASHBACK_TRANSACTION_QUERY view for additional transaction information. For example, to determine the user that performed the erroneous update, issue the following query:

SELECT LOGON_USER FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = HEXTORAW('0009000500000089');

LOGON_USER

---MSMITH

In this example, the query shows that the user MSMITH was responsible for the erroneous transaction.