Analysis of [error code [942], ora-00942 in synchronization of DDL statements in ogg

Recently, we encountered an OGG environment configured with DDL synchronization. During the synchronization, there was a synchronization exception on the backup library, and Fatal Error executing DDL Replication was reported: Error [Error Code [942], ORA-00942: By analyzing the OGG error log GGserr.log, it can be found that during the normal DDL synchronization, there is an operation to set the current SCHEMA, while during the synchronization error, the OGG software does not set the current SCHEMA operation; at this time, the USER OGGDBA executes the DDL, so the orA-00942 table does not exist.
In the future, I will further query from MOS. This problem may be caused by a BUG. The following documents are related to this problem:
OGG V11.2 Extract operator Session schema name in to the trails Causing replicat to fail on DDL Operations (ORA-00942) Table or View does not exist (document ID 1484499.1) for DDL Replication, Replicat Sets Session To different Schema than original one in source (document ID 1270161.1)
Related error reporting and troubleshooting analysis:
1. As can be seen from the reperp.rPT file, the information of the current OGG version is as follows:

$ cat REPPER.rpt
***********************************************************************
                 Oracle GoldenGate Delivery for Oracle
    Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
   Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:48:07</span>

2. From the GGserr.log file, it can be found that the log information when the problem occurred: ### Normal DDL synchronization information when OGG backup was applied: — column edit condition deleted the irrelevant column output

2018-01-30 21:38:47 :  Setting current schema for DDL operation to [JS_PERSONAL_TSM].
2018-01-30 21:38:47 :  Executing DDL operation.
2018-01-30 21:38:47 :  DDL operation successful.
2018-01-30 21:38:47 :  Restoring current schema for DDL operation to [ogg].
===========>>>>>Start a DDL sync.
2018-01-30 21:38:47 :  DDL found, operation [ALTER TABLE "AAABBB" ADD CONSTRAINT "FK_BMK_ABCDE_3" FOREIGN KEY ("KEK_INDEX") REFERENCES "T_ABCDE" ("KMS_ABCDE_INDEX") ENABLE NOVALIDATE  (size 131)].
2018-01-30 21:38:47 :  DDL is of mapped scope, after mapping new operation [ALTER TABLE JS_PERSONAL_TSM."AAABBB" ADD CONSTRAINT "FK_BMK_ABCDE_3" FOREIGN KEY ("KEK_INDEX") REFERENCES "T_ABCDE" ("KMS_ABCDE_INDEX") ENABLE NOVALIDATE  (size 147)].
2018-01-30 21:38:47 :  DDL operation included [INCLUDE MAPPED], optype [ALTER], objtype [TABLE], objowner [JS_PERSONAL_TSM], objname [AAABBB].
2018-01-30 21:38:47 :  Setting current schema for DDL operation to [JS_PERSONAL_TSM].
2018-01-30 21:38:47 :  Executing DDL operation.
2018-01-30 21:38:47 :  DDL operation successful.
2018-01-30 21:38:47 :  Restoring current schema for DDL operation to [ogg].
===========>>>>>stop a DDL sync.
===========>>>>>Start a DDL sync.
2018-01-30 21:38:47 :  DDL found, operation [ALTER TABLE "AAABBB" ADD CONSTRAINT "FK_BMK_ABCDE_2" FOREIGN KEY ("MAC_WK_INDEX") REFERENCES "T_ABCDE" ("KMS_ABCDE_INDEX") ENABLE NOVALIDATE  (size 134)].
2018-01-30 21:38:47 :  DDL is of mapped scope, after mapping new operation [ALTER TABLE JS_PERSONAL_TSM."AAABBB" ADD CONSTRAINT "FK_BMK_ABCDE_2" FOREIGN KEY ("MAC_WK_INDEX") REFERENCES "T_ABCDE" ("KMS_ABCDE_INDEX") ENABLE NOVALIDATE  (size 150)].
2018-01-30 21:38:47 :  DDL operation included [INCLUDE MAPPED], optype [ALTER], objtype [TABLE], objowner [JS_PERSONAL_TSM], objname [AAABBB].
2018-01-30 21:38:47 :  Executing DDL operation.
2018-01-30 21:38:47 :  Fatal error executing DDL replication: error [Error code [942], ORA-00942: 琛ㄦ垨瑙嗗浘涓嶅瓨鍦SQL ALTER TABLE JS_PERSONAL_TSM."AAABBB" ADD CONSTRAINT "FK_BMK_ABCDE_2" FOREIGN KEY ("MAC_WK_INDEX") REFERENCES "T_ABCDE" ("KMS_ABCDE_INDEX") ENABLE NOVALIDATE  /* GOLDENGATE_DDL_REPLICATION */], due to explicit ABEND error handling and filter [include all (default)].
2018-01-30 21:38:47 :  PROCESS ABENDING.
===========>>>>>DDL Synchronized post-error process ABENDING

From the above normal DDL synchronization log information, it can be found that when DDL is synchronized, the information displayed on the OGG backup library is as follows:
Line 1 log: DDL found. What is the specific DDL operation statement
Row 3: DDL operation included, which shows the operations, object types, OWNER, etc., that are mapped into the REP process in the OGG library. Row 4: Setting current schema for DDL operation to [JS_PERSONAL_TSM]., namely, to set the current schema for the subsequent execution of DDL statement, the fifth row log: executive DDL operation.
Line 6 Log: DDL operation Successful.
Mice with ALZHEIMER’s Disease Have been restored in mice with alzheimer’s disease. Restores the current SCHEMA to the OGG synchronization user.
The DDL synchronization information when the problem occurred was applied in the OGG backup library, and the current SCHEMA setting was missing in step 4, so the OGG user was used at this time and the table could not be found when executing the DDL statement: [Error Code [942], OrA-00942:
Further reason analysis: by referring to the log information of OGG data synchronization, it can be found that compared to the log information of successful DDL synchronization, there is less operation of Setting current schema for DDL operation when OGG data synchronization occurs. At this point, an abnormal operation SCHEMA is used, so the report of executing DDL statement does not exist -[Error code [942], OrA-00942.
Validation on the source side: — Look at the GGS_MARKER TABLE under the OGG user on the source side, press the OPTIME information of the field or the file information of the MARKER_TEXT field (ALTER TABLE “AAABBB” ADD CONSTRAINT*), find the DDL statement that is causing the problem, and see if there is a problem with the DDL capture. Select * from GGS_MARKER

Read More: