玉面飞龙的BLOG

Oracle logical standby当遇见数据不一致的情况下，apply进程会报错并且logical standby apply会停止。发生错误的相关数据信息会写到alert.log和DBA_LOGSTDBY_EVENTS中。

如果被修改的数据仍然在UNDO中，则可以快捷方便的使用flashback query查找并恢复。现在Oracle的flashback query语法越来越丰富了。手头可准备一些相应的脚本来及时恢复程序修改错了的数据。

A.10.4 Troubleshooting ORA-1403 Errors with Flashback Transactions

The error occurs when data in a table that is being managed by SQL Apply is modified directly on the standby database and then the same data is modified on the primary database. When the modified data is updated on the primary database and is subsequently received on the logical standby database, SQL Apply verifies the original version of the data is present on the standby database before updating the record. When this verification fails, the ORA-1403: No Data Found error is returned.

The Initial Error

When SQL Apply verification fails, the error message is reported in the alert log of the logical standby database and a record is inserted in the DBA_LOGSTDBY_EVENTS view.The information in the alert log is truncated, while the error is reported in it’s entirety in the database view. For example:

LOGSTDBY stmt: UPDATE "SCOTT"."MASTER"
SET
"NAME" = 'john'
WHERE
"PK" = 1 and
"NAME" = 'andrew' and
ROWID = 'AAAAAAAAEAAAAAPAAA'

LOGSTDBY status: ORA-01403: no data found
LOGSTDBY PID 1006, oracle@staco03 (P004)
LOGSTDBY XID 0x0006.00e.00000417, Thread 1, RBA 0x02dd.00002221.10

The Investigation

The first step is to analyze the historical data of the table that caused the error. This can be achieved using the VERSIONS clause of the SELECT statement. For example, you can issue the following query on the primary database:

SELECT VERSIONS_XID
, VERSIONS_STARTSCN
, VERSIONS_ENDSCN
, VERSIONS_OPERATION
, PK
, NAME
FROM SCOTT.MASTER
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE PK = 1
ORDER BY NVL(VERSIONS_STARTSCN,0);
VERSIONS_XID     VERSIONS_STARTSCN VERSIONS_ENDSCN V  PK NAME
—————- —————– ————— - — ——-
03001900EE070000           3492279         3492290 I   1 andrew
02000D00E4070000           3492290                 D   1 andrew

Depending upon the amount of undo retention that the database is configured to retain (UNDO_RETENTION) and the activity on the table, the information returned might be extensive and you may need to change the versions between syntax to restrict the amount of information returned.From the information returned, it can be seen that the record was first inserted at SCN 3492279 and then was deleted at SCN 3492290 as part of transaction ID 02000D00E4070000.Using the transaction ID, the database should be queried to find the scope of the transaction. This is achieved by querying the FLASHBACK_TRANSACTION_QUERY view.

SELECT OPERATION
, UNDO_SQL
FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID = HEXTORAW(’02000D00E4070000′);
OPERATION  UNDO_SQL
———- ————————————————
DELETE     insert into “SCOTT”.”MASTER”(”PK”,”NAME”) values(’1′,’andrew’);
BEGIN

Note that there is always one row returned representing the start of the transaction. In this transaction, only one row was deleted in the master table. The UNDO_SQL column when executed will restore the original data into the table.

SQL> INSERT INTO "SCOTT"."MASTER"("PK","NAME") VALUES ('1','ANDREW');SQL> COMMIT;

When you restart SQL Apply, the transaction will be applied to the standby database:

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;