在配置GoldenGate同步DDL语句(2)中我们针对多种DDL语句在GoldenGate环境中的同步进行了测试，但在默认情况下replicat在复制DDL语句失败时不会在replicat report或者ggserr.log等日志文件中产生该DDL失败的具体信息，这就会导致我们在发现DDL同步有error的情况下无法了解失败的原因。之后又去翻官方的文档，在中找到了DDLOPTIONS参数:
Use the DDLOPTIONS parameter to configure aspects of DDL processing other than filtering and string substitution. You can use multiple DDLOPTIONS statements, but using one is recommended. If using multiple DDLOPTIONS statements, make each of them unique so that one does not override the other. Multiple DDLOPTIONS statements are executed in the order listed.
这个参数可用的选项很多，我们需要用到的是REPORT选项:
Valid for Extract and Replicat (Oracle and Teradata). Controls whether or not expanded DDL processing information is written to the report file. The default of NOREPORT reports basic DDL statistics. REPORT adds the parameters being used and a step-by-step history of the operations that were processed
在replicat的参数文件中添加上”DDLOPTIONS REPORT”，可以让replicat在同步DDL语句时若出现问题，将该问题的详细情况记录到该replicat的report 文件中，以便找出DDL复制失败的root cause。
GGSCI (rh3.oracle.com) 9> view params rep1
replicat rep1
userid maclean,password maclean
ASSUMETARGETDEFS
discardfile /s01/discard/rep1.log,append,megabytes 10
-- Support DDL here
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5
DDLOPTIONS REPORT
map sender.* , target receiver.*;
GGSCI (rh3.oracle.com) 10> stop replicat rep1
Sending STOP request to REPLICAT REP1 ...
Request processed.
GGSCI (rh3.oracle.com) 11> start replicat rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting
在源端创建一外部表
SQL> CREATE TABLE extgg
2 ORGANIZATION EXTERNAL
3 (TYPE oracle_datapump
4 DEFAULT DIRECTORY dgg
5 LOCATION ('extgg.dat'))
6 AS
7 select * from dba_objects;
Table created.
之后检查replicat的report信息可以发现DDL复制失败的详细SQL语句等信息:
GGSCI (rh3.oracle.com) 12> view report rep1
***********************************************************************
Oracle GoldenGate Delivery for Oracle
Version 11.1.1.0.0 Build 078
Linux, x64, 64bit (optimized), Oracle 10 on Jul 28 2010 15:58:11
Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.
Starting at 2010-12-10 05:54:32
***********************************************************************
...............
2010-12-09 09:10:13 INFO OGG-01408 Restoring current schema for DDL operation to [MACLEAN].
2010-12-09 09:10:13 INFO OGG-00482 DDL found, operation [CREATE
GLOBAL TEMPORARY TABLE "SENDER"."SYS_TEMP_0FD9D6609_2B3F4ED" ("OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(128),"SUBOBJECT_NAME" VARCHAR2(30),"OBJECT_ID"
NUMBER,"DATA_OBJECT_ID" NUMBER,"OBJECT_TYPE" VARCHAR2(19),"CREATED" DATE,"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHA R2(19),"STATUS" VARCHAR2(7),"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),"SECONDARY" VARCHAR2(1) ) IN_MEMORY_METADATA
CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 4254950
921 ) NOPARALLEL (size 456)].
2010-12-09 09:10:13 INFO OGG-00489 DDL is of mapped scope, after mapping new operation
[CREATE GLOBAL TEMPORARY TABLE "RECEIVER"."SYS_TEMP_0FD9D6609_2B3F4ED"
("OWNER" VARCHAR2(30),"OBJECT_NAME" VARCHAR2(128),"SUBOBJECT_NAME"
VARCHAR2(30),"OBJECT_ID" NUMBER,"DATA_OBJECT_ID" NUMBER,"OBJECT_TYPE"
VARCHAR2(19),"CREATED" DATE,"LAST_DDL_TIME" DATE,"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),"SECONDARY" VARCHAR2(1) ) IN_MEMORY_METADATA CURSOR_SPECIF
IC_SEGMENT STORAGE (OBJNO 4254950921 ) NOPARALLEL (size 458)].
2010-12-09 09:10:13 INFO OGG-00487 DDL operation included [INCLUDE ALL], optype [CREATE],
objtype [TABLE], objowner [RECEIVER], objname [SYS_TEMP_0FD9D6609_2B3F4E
D].
2010-12-09 09:10:13 INFO OGG-01407 Setting current schema for DDL operation to [SYS].
2010-12-09 09:10:13 INFO OGG-00484 Executing DDL operation.
2010-12-09 09:10:18 INFO OGG-00495 DDL error ignored for next retry: error code
[DEFAULT],
filter [include all (default)], error text [Error code [900],
ORA-00900: invalid SQL statement, SQL CREATE GLOBAL TEMPORARY TABLE "RECEIVER"."SYS_TEMP_0FD9D6609_2B3F4ED"
("OWNER" VARCHAR2(30),"OBJECT_NAME" VARCHAR2(128),"SUBOBJECT_NAME" V
ARCHAR2(30),"OBJECT_ID" NUMBER,"DATA_OBJECT_ID" NUMBER,"OBJECT], retry [1].
2010-12-09 09:10:18 INFO OGG-01407 Setting current schema for DDL operation to [SYS].
2010-12-09 09:10:18 INFO OGG-00484 Executing DDL operation trying again due to RETRYOP parameter.
2010-12-09 09:10:23 INFO OGG-00495 DDL error ignored for next retry: error code [DEFAULT],
filter [include all (default)], error text
[Error code [900], ORA-00900
: invalid SQL statement, SQL CREATE GLOBAL TEMPORARY TABLE "RECEIVER"."SYS_TEMP_0FD9D6609_2B3F4ED"
("OWNER" VARCHAR2(30),"OBJECT_NAME" VARCHAR2(128),"SUBOBJECT_NAME" V
ARCHAR2(30),"OBJECT_ID" NUMBER,"DATA_OBJECT_ID" NUMBER,"OBJECT], retry [2].
2010-12-09 09:10:23 INFO OGG-01407 Setting current schema for DDL operation to [SYS].
另外需要注意的是如create table as select这样的建表DDL语句应该被杜绝使用，因为在goldengate复制环境中create table as select语句仍会以DDL形式被应用，这就导致源端和目标的端的数据不一致，如:
/* 源端中dba_object视图共有52046行数据 */
SQL> select count(*) from dba_objects;
COUNT(*)
----------
52046
SQL> create table ddlproblem as select * from dba_objects;
Table created.
SQL> select count(*) from ddlproblem;
COUNT(*)
----------
52047
而在目标端的复制情况:
SQL> desc ddlproblem
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> select count(*) from ddlproblem;
COUNT(*)
----------
51008
/* 因为replicat在目标端仅是简单的执行"create table as select"的建表DDL语句，
而源端和目标端中这个被select的对象中的数据并不一致，因而CTAS操作只会导致
2端的数据不一致
*/
Leave a Reply