配置GoldenGate同步DDL语句(3)

在配置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端的数据不一致
*/