专栏首页python3配置GoldenGate同步DDL语句(

配置GoldenGate同步DDL语句(

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 数据分析处理库Pandas——时间

    py3study
  • django-1-框架介绍

    用django框架做web开发必须要用到python虚拟环境,而且一个虚拟环境只能创建一个django项目,如果创建多个django项目的画将会出现一些莫名奇妙...

    py3study
  • python计算机基础(一)

    py3study
  • LeetCode 359. 日志速率限制器(哈希map)

    该日志会被打印出来,需要满足一个条件:当且仅当日志内容 在过去的 10 秒钟内没有被打印过。

    Michael阿明
  • MySQL DDL详情揭露

    MySQL中DDL语句,即数据定义语言,用于创建、删除、修改、库或表结构,对数据库或表的结构操作。常见的有create,alter,drop等。这类语句通常会耗...

    MySQL技术
  • cookie和session介绍

    在网站中,http请求是无状态的。也就是说即使第一次和服务器连接后并且登录成功后,第二次请求服务器依然不能知道当前请求是哪个用户。cookie的出现就是为了解决...

    大学里的混子
  • mysql8.0原子ddl特性

    MySQL8.0支持原子DDL。原子DDL将DDL操作相关联的数据字典更新、存储引擎操作和二进制日志写入合并到单个原子事务中。

    卖菜小弟
  • Kali Linux Web渗透测试手册(第二版) - 2.7 - 获取和修改cookie

    thr0cyte,Gr33k,花花,MrTools,R1ght0us,7089bAt,

    用户1631416
  • Oracle9i新特性-使用DBMS_METADATA包获得对象DDL语句

    从Oracle9i开始Oracle提供了一个新的系统包DBMS_METADATA,可以用于提取对象创建的DDL语句。

    数据和云01
  • 反编译一款小说阅读软件 android逆向(三)

    用户1127566

扫码关注云+社区

领取腾讯云代金券