GOLDENGATE 9.50关于DDL复制读书笔记

GOLDENGATE 9.50关于DDL复制读书笔记

1、

DDL marker table

该表的缺省名称为：GGS_MARKER,用来存储数据库表中的DDL变化，该表只能插入INSERT；

2、 3、

Sequence on marker table DDL history table

用以标识表GGS_MARKER的列数据，类似于数据库的sequence数据；

DDL 历史记录表；

4、 Object ID history table

用以记录DDL语句操作的数据库对象；

5、 DDL trigger

DDL操作引发的操作，把DDL语句写入GGS_MARKER、以及history表； 6、

DDL SHEMA

需要用来复制DDL操作的数据库用户名，必须在参数文件GLOBALS中声明； 7、

USER ROLE

用户用以复制DDL操作的规则 8、

Internal setup table;

内部安装表，用以记录内部数据使用； 9、

DUMPDLL TABLES

代表一系列表，用以存储DUMPDLL命令查询的数据库状态数据—元数据； 10、

DDL_PIN

用以跟踪DDL操作的标记，以及性能提高的参数；

11、 ddl_cleartrace.sql 清空DDL跟踪文件脚本； 12、 ddl_status.sql

用来查询DDL安装包的安装情况； 13、 Marker_status.sql

用以查询MARKER表的安装情况； 14、 Ddl_tracelevel.sql 设置DDL跟踪文件的等级； 15、

Object ID history table

用以记录DDL语句操作的数据库对象；

二、安装DDL的顺序

1、首先确认安装使用的数据库用户，必须有SYSDBA权限； 2、编写全局参数文件GLOBALS

GGSSCHEMA DATABASE_SYSDBA;

3、一些参数和对象的名称改变必须在安装过程中修改，在运行过程中修改必须使用固定的脚本文件；并且参数名称的修改必须在GLOBALS文件中声明；

4、关闭ORACLE RECYCLE—9I中没有，否则将无法安装； 5、安装目录必须和GOLDENGATE是同一个目录； 6、

三、数据库与DDL同步相关的指南

1、GOLDENGATE 支持DDL复制的目标和操作范围

Clusters----集群 Functions---函数 Indexes---索引 Packages---数据包 Procedures—过程 Roles---规则

Sequences---序列号

Synonyms---同义词--- GOLDENGATE关于Synonyms复制，不复制Synonyms里面的数据；

Tables---数据表

Tablespaces---表空间 Triggers---触发器 Types---类型 Views—视图

materialized views (also known as--snapshots)物化视图

users---用户

2、ORACLE有些保留的SCHEMA是不允许使用的（注意）

四、TABLE DEFINE---表的定义；

当DDL数据被复制的时候，相关的表的数据转换，GOLDENGATE是不允许一些特殊的数据类型的转换的；

参考详细可以支持的数据类型转换；

DDL数据复制时候，目标和源数据库操作的对象必须是一致的；

ASSUMETARGETDEFS参数在REPLICAT参数文件的使用，而SOURCEDEFS参数在源端使用，一旦数据库对象产生了DDL操作那必然，REP进程会ABEND；

五、TRUNCATE操作

GOLDENGATE支持TRUNCATE操作是独立与DDL同步的一部分内容；在9。5以前就有；

目前9。5的DDL包支持所有包括TRUNCATE以及其它表操作的数据库表记录； 在9。5以前的版本中专门用了GETTRUNCATES参数来控制；

The standalone TRUNCATE feature supports the replication of TRUNCATE TABLE, but no other DDL.

The full DDL feature supports TRUNCATE TABLE, ALTER TABLE TRUNCATE PARTITION, and other DDL

六、ALTER TABLE NAME 和RENAME

GOLDENGATE处理两个DDL操作是将RENAME转换为了ALTER TABLE NAME，但是RENAME并不需要使用SCHEMA，但是ALTER TABLE NAME却需要SCHEMA；

新表的名称不能超过18个字符；

七、OPERATION ON LOBS COLUMNS---大对象数据列复制的操作；

DDL操作不会被复制，当指定的目标表正在执行DML操作的时候；EXT从数据库日志中读取到记录后，会自动按顺序产生事务的记录，所以数据库的状态数据—元数据用来被使用来解决这个问题，用以提示是否有DML操作出现；

异常发生在大对象数据上，EXT抓取一个大对象数据的值通过回闪查询，但是ORACLE并不支持象除了DROP以外的DDL操作提供这样一个值的功能；

当大对象数据列被操作的时候，大对象列的数据结构需要反射目前的元数据，但是在日志中，大队列的数据记录反映的是旧的数据库元数据；

但是GOLDENGATE为了从结构上解决这个问题，GOLDENGATE通过名称、类型、长度、编译成一些的小列数据；这样大数据的读取就变成了从小列数据中取得了；

但是由于DDL的出现，可能导致在小列数据DROP或则没有及时刷新，这样事务日志中记录的数据与实际的数据可能无法保证一致性；

例如如果存在DDL将某一列删除后，重新建设了一个同名但是数据类型不同的列，这样复制进程REP在目标端可能导致错误；

为了解决这个问题，注意： A、 保证抓取时间的延迟必须在一个限度； B、 或则保证DDL在DML后面被复制；

C、 如果让包含大对象数据列的表仅仅执行DM操作，表可以使用一个唯一的小数

据字段作为主键，或则建设一个复合的主键，或则通过全字段组合识别，如果一定要使用DDL复制，必须注意的是“ 1、 暂停源数据的DML的EXT；

2、 确保REP队列中的DML数据已经完成； 3、 然后执行DDL； 4、 恢复DML；

八、OPERATION ON UDTS---DDL用于用户定义DDL复制操作需要注意的 I、 DDL可能包含了部分用户自己定义的DML应用范围内，导致应用错误；比如

数据修改数据类型，而DML无法实现被复制；

II、

九、没有使用的字段UNUSERD COLUMNS

通常情况下，GOLDENGATE不支持通过表中没有使用的字段进行数据复制，但是9。5通过“DBOPTIONS ALLOWUNUSEDCOLUMN “实现了这个功能；

首先，两端都必须有这样一个没有使用的字段，这样在REP的参数文件中，使用“Include ALTER TABLE...SET UNUSED statements in your DDL replication configuration. “

十、Comments in SQL

如果DDL语句中保留了数据对象，那复制的语句也必然回保留那个数据对象； 比如CREATE SCHEMA1。TABLENAME1

目标端自然也是 CREATE SCHEMA。TABLENAME1

十一、Special characters—特殊字符

!, $, and #. 字符被限制使用在参数文件中的 mapped with TABLE or MAP 中；

十二、Compilation errors

如果一个存储过程、功能、函数、功能包中的CREATE出错，但是GOLDENGATE还是会继续执行DDL复制的；

十三、ALTER TABLE MOVE TABLESPACE

修改表空间大小必须要确认数据是否出现丢失或者改变的问题；

十四、GoldenGate-related guidelines for DDL support 一些部署DDL同步的限制和知道考虑的地方；

1、 Ensuring data continuity after DDL—保证数据在复制

DDL后

的持续复制；

例如，复制一个DML操作到一个源端数据库中由CREATE或则RENAME的表，EXT以及REP的配置文件中都必须在TABLE，和MAP参数中包含住，否则将出现DML错误；

创建了一个新的用户，将新的数据表移动到新的用户名下，这样同样需要在配置文件里面增加制定的相关参数，否则将出现DML复制被丢失的情况；

2、 初始化导入

3、 Wildcard resolution—通配符解决方案

initial load

通过“*”与标准的配置语句结合，来实现DDL的同步，为了保证通配符的正常使用，通常参数WILDCARDRESOLVE parameter is set to DYNAMIC by default；

4、 SQLEXEC

在实现DDL复制功能的时候，使用SQLEXEC功能必须考虑到调用的存储过程或则SQL语句的函数可能和复制DDL操作所影响的对象可能一致，这样就必须保证在执行SQL时候，被DDL修改的数据库对象能够正确的实现所谓的SQLEXEC的调用； 所以，DDL的操作必然是有相对的优先级的；

而SQLEXEC调用的过程或则函数中包含的对象，在MGR启动以前，也就是DDL复制以前就是必须已经存在的；因此，DDL的复制操作的对象是和EXEC的对象是不可能一致的，所以必须给DDL的过滤条件给过滤开；否则DDL将很可能修改了某些数据对象的结构，导致SQLEXEC无法实现调用；

5、 User Exits

GoldenGate user exits are not supported for use with DDL synchronization

6、 Identifying GoldenGate DDL operations

区分GOLDENGATE DDL操作，To identify GoldenGate DDL operations, the following comment ispart of each Extract and Replicat DDL statement:

/* GOLDENGATE_DDL_REPLICATION */

7、 Controlling the propagation of Replicat DDL 缺省的情况下，EXT忽略本地REP的DDL复制操作；通过DDL语句中的

/* GOLDENGATE_DDL_REPLICATION */

来实现；

DDLOPTIONS下的参数 GETREPLICATES 、IGNOREREPLICATES 来控制本地的REP的DDL复制操作是否被EXT进程捕获或则忽略；

DDLOPTIONS的参数GETAPPLOPS 、IGNOREAPPLOPS 除了控制REP外的DDL还包括控制从数据库事务的DDL是否被EXT进程抓取或则忽略；

通常意义上来说，在配置双向的时候，我们通常使用DDLOPTIONS

IGNOREREPLICATES, GETAPPLOPS；这样EXT只抓取事物交易中的DDL，而忽略来自REP的操作；

在一个级联配置中，使用DDLOPTIONS参数 GETREPLICATES, IGNOREAPPLOPS.

Extract must capture the DDL from Replicat on each intermediary system and cascade it to the next system downstream.

8、 DDL and data pumps---数据泵

A data pump does not process DDL. It can only pass it to the next process.

Without DDL support, a data pump cannot filter—过滤 or manipulate—操作控制 DML on objects

affected by DDL operations. The pump must be configured in pass-through—传递 mode.

9、 configure pass-through mode---如何配置传递模式

1在data pump的参数文件中引如. PASSTHRU 参数，如果遇到DLL操作，而没有设置PASSTHRU，data pump 会abend

2.不要为 data pump配置任何DLL的参数：DDLOPTIONS, DDLSUBST, DDLERROR, PURGEDDLHISTORY, PURGEMARKERHISTORY或则其它的跟踪使用DDL的参数，这些参数都将导致data pump abend.

10、 Pallel Extract and Replicat processes-- 并行的EXT、REP进程；

可以通过并行的进程来分别处理不同的数据对象，如此提高系统的性能，当我们采用并行进程的时候，应该如此配置；

某些对象的 DDL、 DML 应该使用一个共同的EXT或则REP进程； ??所有业务数据相关的数据对象必须在一个进程里面；

保证有关联的DDL和DML在一个进程里面，必须保证数据的完整性；

For example, if ReplicatA processes DML for Table1, then it should also process the DDL for Table1. If Table2 has a foreign key to Table1, then its DML and DDL operations also should be processed by ReplicatA.

11、Multi-target configurations

如果一个EXT进程写多个队列，他们被不同的REP进程读，这样EXT将所有的DDL操作写入队列TRAIL中，而每个REP进程通过自己的参数文件进行DDL的过滤；

11、 Understanding DDL scopes—DDL范围

GOLDENGATE将数据库objects 按范围分类，一个对象范围事实上是由DDL可以始终的操作来进行区分的；

??MAPPED ??UNMAPPED ??OTHER

通过这三个参数来控制DDL的操作上的过滤；

Mapped scope---MAP范围

数据库对象在进程配置文件中 参数 TABLE and MAP 后，导致EXT、REP对指定对象的操

作进行过滤；除非出现覆盖他们的规则被特殊声明；

.例如，For objects in TABLE and MAP statements, 而the DDL operations listed in Table 41 are supported

TABLE、MAP不支持一些被一些操作影响数据库对象名称的特殊的DDL操作；

例如，对于EXT来说，MAPPED的范围确定队列中哪写DDL是需要被REP进程所复制的；REP会自动检测MAPPED的范围，并将DDL转换成MAPPED中TARGET的目标下；

例如：

Extract (source) Replicat (target)

TABLE fin.expen; TABLE hr.tab*;

MAP fin.expen, TARGET fin2.expen2; MAP hr.tab*, TARGET hrBackup.bak_*

当源端：ALTER TABLE fin.expen ADD notes varchar2(100);

EXT检测到TABLE中的参数设置将它抓获放入队列

而目标端：检测到，MAP fin.expen, TARGET fin2.expen2;则转换DDL到FIN2下执行 ALTER TABLE fin2.expen2 ADD notes varchar2(100); 同样：

Source: CREATE TABLE hr.tabPayables ... ;

Target: CREATE TABLE hrBackup.bak_tabPayables ...; 当复制的对象在MAPPED范围内，我们可以忽略DDL的DDL的一些参数，除非我们希望可以转换在不同的SCHEMA下面执行某些DDL操作；

并且我们必须在MAPPED的范围内的目标端做好相关的SCHEMA的指定；

12、Unmapped scope---不复制的范围—REP进程；

如果某个DDL涉及的数据对象不在TABLE和MAP范围参数中的任何一个，那他就是UNMAPPED范围内的；

一个数据库对象目标在源端UNMAPPED范围内， (not in an Extract TABLEstatement),但是在目标端的MAPPED范围内；(in a Replicat MAP statement), DDL将直接被用源端的（数据库对象+DDL）进行操作执行；. When DDL is of UNMAPPED scope in the Replicat configuration, it is applied to the target with the same owner and object name as in the source DDL.

13、Other scope

14、Enabling and filtering DDL for synchronization

通常情况下，在源数据库中，DDL是不被抓取的，我们必须配置EXT进程的参数文件让EXT进程抓取DDL；

在目标端，DDL通常是被缺省复制的，这是为了保证事务提交的数据被完整的保留；所以，通常意义下，REP进程在没有参数配置的情况下会将所有的DDL都复制在目标数据库；

当然我们可以通过配置REP关于DDL复制的过滤参数来实现对DDL语句的选择性复制；

EXT进程根据自己的参数文件，去抓获符合参数文件规定的DDL操作，然后将DDL语句写入队列文件中；

REP进程，它复制所有的队列里面的DDL操作到目标数据库；尽管在没有任何参数的前提下也会如此；

所谓的过滤事实上就是，去include or exclude DDL ??scope ??object type ??operation type ??object name ??strings in the DDL command syntax or comments, or both

仅仅一个DDL参数能被在进程的参数文件中使用，所以我们必须集合multiple inclusion and exclusion options to filter the DDL 以达到我们的过滤目标 ；

集合的复合型的参数设置申明用“AND”进行连接；所有符合这个组合参数设置申明的DDL都会被完全复制；

所以，集合的复合型参数设置必须在使用前经过测试；

Syntax DDL [

{INCLUDE | EXCLUDE}

[, MAPPED | UNMAPPED | OTHER | ALL] [, OPTYPE ]

[, OBJTYPE ‘’] [, OBJNAME “”] [, INSTR ‘’]

[, INSTRCOMMENTS ‘’] ] [...]

Syntax

INCLUDE | EXCLUDE

使用INCLUDE and EXCLUDE 用以标记出包含还是不包含从句；

一个包含还是不包含从句，必须用INCLUDE or EXCLUDE 关键字；后面用一些通过一些DDL参数进行有效的组合；

Forexample, the following is invalid:

DDL EXCLUDE OBJNAME “hr.*”

However, you can use either of the following:

DDL INCLUDE ALL, EXCLUDE OBJNAME “hr.*” DDL INCLUDE OBJNAME “fin.*” EXCLUDE “fin.ss”

同样的标准级别内，EXCLUDE 拥有比INCLUDEs 更高的权限；所以我们可以使用多方组合的inclusion and exclusion clauses.

MAPPED | UNMAPPED | OTHER | ALL

Use MAPPED, UNMAPPED, OTHER, and ALL to apply INCLUDE or EXCLUDE based on the DDL operation scope. ??MAPPED applies INCLUDE or EXCLUDE to DDL operations that are of MAPPED scope. MAPPED filtering is performed before filtering that is specified with other DDL parameter options. ??UNMAPPED applies INCLUDE or EXCLUDE to DDL operations that are of UNMAPPED scope. ??OTHER applies INCLUDE or EXCLUDE to DDL operations that are of OTHER scope. ??ALL applies INCLUDE or EXCLUDE to DDL operations of all scopes.

DDL EXCLUDE ALL---是一个比较特殊的进程参数，用来在禁止所有的DDL复制后，为GOLDENGATE保持每天更新的数据库元数据，

当需要阻止DDL操作被复制的时候而保证DML被完全的复制，我们可以使用DDL EXCLUDE ALL；

这样系统将为GOLDENGATE提供当前数据库的元数据，因此避免了ER进程的停止和重新启动；

DDL EXCLUDE ALL不需要INCLUDE从句的使用，为了完全避免任何DDL的元数据或则操作被复制，完全忽略了DDL的参数，DDL TRIGGER将继续在历史表中记录这些操作；除非手动禁止；

OPTYPE ----DDL命令类型参数

使用OPTYPE 在INCLUDE or EXCLUDE 去特别声明一个DDL的操作类型，例如：

as CREATE, ALTER, and RENAME. ，对于后面的 , 使用DDL command 例如：:

DDL INCLUDE OPTYPE ALTER

OBJTYPE ??DDL数据对象类型参数

使用 OBJTYPE 在 INCLUDE or EXCLUDE 中，声明一个特别的数据库对象对于, use such as TABLE, INDEX,TRIGGER, USER, ROLE.

DDL INCLUDE OBJTYPE ?INDEX?

OBJNAME “”---目标对象名称参数

使用OBJNAME to apply INCLUDE or EXCLUDE to the name of an object,例如表名；

目标对象支持使用通配符；如果没有指定相关的对象拥有人SCHEMA，则缺省使用GOLDENGATE的用户；

For example:

Owner is GoldenGate:

DDL INCLUDE OBJNAME “tab_customers”

Owner is accounts:

DDL INCLUDE OBJNAME “accounts.*”

当在REP配置参数文件中使用对象名称用来MAPPED，则复制怒表对象的SCHEMA如果缺少就是自动增加在目标端通名的SCHEMA下面；

MAP fin.exp_*, TARGET fin2.*;

source:

CREATE TABLE fin.exp_phone;

the target:

CREATE TABLE fin2.exp_phone;

当DDL的创建触发键和索引的时候，OBJECET的名字就必须是最基础的目标对象的名字；而不是直接是索引或则触发器本身；

include the following DDL statement, the correct value is “hr.accounts,” not “hr.insert_trig.”

对于RENAME操作，OBJECTNAME的值，必须是一个表名称，否则GOLDENGATE很难确定具体要改的表名属于哪一个SCHEMA下，因为可能规则上定义的并不是同名的SCHEMA；

INSTR ??

使用INSTR 在INCLUDE or EXCLUDE 命令行中，DDL语句中包含了特别字节固定命令；例如：.

DDL INCLUDE ALL EXCLUDE INSTR ?CREATE INDEX?

INSTRCOMMENTS ??

使用INSTRCOMMENTS 在命令INCLUDE or EXCLUDE 后的 DDL 语句中包含特殊的字符内容；但是不是DDL命令本身；通过使用INSTRCOMMENTS，我们可以通过内容来作为一个过滤的条件； 例如：

DDL INCLUDE ALL EXCLUDE INSTRCOMMENTS ?SOURCE ONLY?

在这个过滤例子中，DDL对与DDL命令中包含的/*source only*/复制；

可以通过INSTR和INSTRCOMMENTS两个组合来过滤更精确的DDL复制；

Example: Combining DDL parameter options

The following is an example of how to combine DDL parameter options.

DDL &

INCLUDE UNMAPPED & OPTYPE alter & OBJTYPE ?table? &

OBJNAME “users.tab*” &

INSTRCOMMENTS ?alter payables? &

INCLUDE MAPPED OBJNAME “*” &

EXCLUDE MAPPED OBJNAME \

第一部分是UMAPPED部分，指定INCLUDE是修改表结构，指定的SCHEMA下的TAB起头的表；而且DDL语句中必须有‘alter payables’才为有效果；

第二部为MAPPED部分，所有的目标对象的DDL操作都被INCLUDE；被去除的部分为TEMPORARYA—SCHEMA下的TAB起头的表的DDL操作；

How GoldenGate handles derived object names

DDL operations can contain a base object name and also a derived object name. A base object is an object that contains data, such as a table or sequence. A derived object is an object that inherits some attributes of the base object to

perform a function related to that object. Consider the following DDL statement:

CREATE INDEX hr.indexPayrollDate ON TABLE hr.tabPayroll (payDate);

In this case, the table is the base object. Its name (hr.tabPayroll) is the base name

and is subject to mapping with TABLE or MAP under the MAPPED scope. The derived object is the index, and its name (hr.indexPayrollDate) is the derived name.

You can map a derived name in its own TABLE or MAP statement, separately from that of the base object. Or, you can use one MAP statement to handle both. In the case of MAP, the conversion of derived object names on the target works as follows.

MAP exists for base object, but not derived object

If there is a MAP statement for the base object, but not for the derived object, the result is an implicit mapping of the derived object. Assuming the DDL statement includes MAPPED, Replicat gives the derived object the same target schema as that of the base object. The name of the derived object stays the same as in the source statement. For example, assume the following: Assume the following source DDL statement:

CREATE INDEX hr.indexPayrollDate ON TABLE hr.tabPayroll (payDate);

The CREATE INDEX statement is executed by Replicat on the target as:

CREATE INDEX hrBackup.indexPayrollDate ON TABLE hrBackup.tabPayroll (payDate);

The rule for the implicit mapping is based the typical industry practice of storing derived objects in the same schema as the base object. It ensures the correct name conversion even if the name of the derived object is not fully qualified in the source statement. Also, when indexes are stored in the same target schema as the base object, an implicit mapping eliminates the need to map derived object names explicitly.

MAP exists for base and derived objects

If there is a MAP statement for the base object and also one for the derived object, the result is an explicit mapping. Assuming the DDL statement includes MAPPED, Replicat converts the owner and name of each object according to its own TARGET clause. For example, assume the following: Assume the following source DDL statement:

CREATE INDEX hr.indexPayrollDate ON TABLE hr.tabPayroll (payDate);

The CREATE INDEX statement is executed by Replicat on the target as:

CREATE INDEX hrIndex.indexPayrollDate ON TABLE hrBackup.tabPayroll (payDate);

Use an explicit mapping when the indexes must be stored in a different target schema than that of the base object, or when the target name must be different than that of the source.

MAP exists for derived object, but not base object

If there is a MAP statement for the derived object, but not for the base object, Replicat does not perform any name conversion for either object. The target DDL statement is the same as that of the source. To map a derived object, the choices are: ??Use an explicit MAP statement for the base object. ??If names permit, map both base and derived objects in the same MAP statement by means of a wildcard. ??Create a MAP statement for each object, depending on how you want the names converted.

Extract (source) Replicat (target)

TABLE hr.tab*; TABLE hr.index*;

MAP hr.tab*, TARGET hrBackup.*; MAP hr.index*, TARGET hrIndex.*;

New tables as derived objects

The following explains how GoldenGate handles new tables that are created from: ??RENAME and ALTER RENAME

??CREATE TABLE AS SELECT

RENAME and ALTER TABLE RENAME

In RENAME and ALTER TABLE RENAME operations, the base object is always the new table name. In the following examples, the base object name is considered to be “index_paydate.”

ALTER TABLE hr.indexPayrollDate RENAME TO index_paydate;

or...

RENAME hr.indexPayrollDate TO index_paydate;

The derived object name is “hr.indexPayrollDate.”

CREATE TABLE AS SELECT

CREATE TABLE AS SELECT statements include SELECT statements and INSERT

statements that affect any number of underlying objects. On the target,

GoldenGate obtains the data for the AS SELECT clause from the target database. The objects in the AS SELECT clause must exist in the target database, and their names must be identical to the ones on the source.

In a MAP statement, GoldenGate only maps the name of the new table (CREATE TABLE ) to the TARGET specification, but does not map the names of the underlying objects from the AS SELECT clause. There could be dependencies on those objects that could cause data inconsistencies if the names were converted to the TARGET specification.

The following shows an example of a CREATE TABLE AS SELECT statement on the source and how it would be replicated to the target by GoldenGate.

CREATE TABLE a.tab1 AS SELECT * FROM a.tab2

The MAP statement for Replicat is:

MAP a.tab*, TARGET a.x*;

The target DDL statement that is applied by Replicat is this:

CREATE TABLE a.xtab1 AS SELECT * FROM a.tab2;

not this:

CREATE TABLE a.xtab1 AS SELECT * FROM a.xtab2;

The name of the table in the AS SELECT * FROM clause remains as it was on the source: tab2.

To keep the data in the underlying objects consistent on source and target, you can configure them for data replication by GoldenGate. In the preceding example, you could use the following statements to accommodate this requirement: Source:

TABLE a.tab*;

Target:

MAPEXCLUDE a.tab2

MAP a.tab*, TARGET a.x*; MAP a.tab2, TARGET a.tab2;

Disabling the mapping of derived objects

You can disable the mapping of derived names by using the DDLOPTIONS

parameter with the NOMAPDERIVED option. See page 408.

Setting DDL processing options

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. Note Before you create a DDLOPTIONS parameter statement, it might help to review “How DDL is evaluated for processing” on page 411.

Syntax DDLOPTIONS

[, ADDTRANDATA]

[, DEFAULTUSERPASSWORDPASSWORD [ENCRYPTKEY DEFAULT | ENCRYPTKEY ]] [, GETAPPLOPS | IGNOREAPPLOPS] [, GETREPLICATES | IGNOREREPLICATES] [, MAPDERIVED | NOMAPDERIVED] [, NOCROSSRENAME]

[, REMOVECOMMENTS {BEFORE | AFTER}]

[, REPLICATEPASSWORD | NOREPLICATEPASSWORD] [, REPORT | NOREPORT]

Syntax

ADDTRANDATA

This option is valid for Extract. Use ADDTRANDATA to: ??enable supplemental logging automatically for new tables created with a

CREATE TABLE.

??update supplemental logging for tables affected by an ALTER TABLE to add or

drop columns. ??update supplemental logging for tables that are renamed. ??update supplemental logging for tables where unique or primary keys are added or dropped.

By default, the ALTER TABLE that adds the supplemental logging is not replicated to the target unless the GETREPLICATES parameter is in use.

For new tables created with CREATE TABLE, ADDTRANDATA produces the same results as the default ADD TRANDATA command in GGSCI by issuing the Oracle ALTER TABLE command with the ADD SUPPLEMENTAL LOG GROUP option. GoldenGate executes this command when the CREATE TABLE or ALTER TABLE is captured on the source. If you have special requirements for the supplemental logging, use the ADD

TRANDATA command, not DBOPTIONS ADDTRANDATA. For more information about the ADD TRANDATA command, see the GoldenGate for Windows and UNIX Reference Guide.

For renamed tables, ADDTRANDATA deletes the supplemental log group for the old table and creates it for the new one. If you do not use ADDTRANDATA and tables

will be renamed, do the following to create the log group before doing the rename.

To create a log group without ADDTRANDATA

1. Drop the supplemental log group from the database interface or by using the DELETE TRANDATA command in GGSCI. 2. Rename the table.

3. Create the new supplemental log group from the database interface or by using the ADD TRANDATA command in GGSCI.

There might be a lag between the time when an original DDL operation occurs and when the ADD TRANDATA takes effect. During this time, do not allow DML operations (insert, update, delete) on the affected table if the data is to be replicated; otherwise, it will not be captured.

To determine when DML can be resumed after ADDTRANDATA 1. Run GGSCI.

2. Open the Extract parameter file.

EDIT PARAMS

3. If the DDLOPTIONS parameter does not include the REPORT option, add it to the parameter statement, and then save and close the file.

DDLOPTIONS [, other DDLOPTIONS options], REPORT

4. Stop and start Extract to make the parameter change effective.

STOP EXTRACT START EXTRACT

5. View the Extract process report.

VIEW REPORT

6. Look for the ALTER TABLE operation that added the supplemental log group to the table. It looks similar to the following:

2007-03-20 15:11:42 GGS INFO 2100 Successfully added TRAN DATA for table with the key, table [QATEST1.MYTABLE], operation [ALTER TABLE \

\].

7. Based on the report, make a note of the time that the ADD TRANDATA took effect.

8. Permit DML operations on the new table.

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

DEFAULTUSERPASSWORD

This option is valid for Replicat. Use DEFAULTUSERPASSWORD for a replicated

{CREATE | ALTER} USER IDENTIFIED BY statement to specify a different password from the one used in the source statement. By default, the source password is replicated to the target.

You can provide a clear-text or encrypted password. Replicat will replace the

placeholder that Extract writes to the trail with the specified password. Options are as follows: ??DDLOPTIONS DEFAULTUSERPASSWORD : Specifies a clear-text password.

??DDLOPTIONS DEFAULTUSERPASSWORD ENCRYPTKEY

:

Specifies a password that was encrypted with a user-defined method and specifies a lookup key in an ENCKEYS file on the target system.

??DDLOPTIONS DEFAULTUSERPASSWORD ENCRYPTKEY

DEFAULT:

Specifies a password that was encrypted by using a random key generated by GoldenGate. On the target, the password is decrypted automatically without requiring an ENCKEYS file. Examples:

DDLOPTIONS DEFAULTUSERPASSWORD ocean

DDLOPTIONS DEFAULTUSERPASSWORD AACAAAAAAAAAAADADESGTFTATAOEEIKB ENCRYPTKEY superkey1

DDLOPTIONS DEFAULTUSERPASSWORD AACAAAAAAAAAAADALHSFYDIEWDEIEIHC ENCRYPTKEY DEFAULT

When using DEFAULTUSERPASSWORD, use the DDLOPTIONS parameter with the NOREPLICATEPASSWORD option in the Extract parameter file.

For more information about how to use these encryption options, see page 291.

GETAPPLOPS | IGNOREAPPLOPS

This option is valid for Extract. It controls whether or not DDL operations produced by local applications except Replicat are included in the content that Extract writes to a trail or file. For more information, see Chapter 15 on page 273.

GETREPLICATES | IGNOREREPICATES

This option is valid for Extract. It controls whether or not DDL operations produced by Replicat are included in the content that Extract writes to a trail or file. For more information, see Chapter 15 on page 273.

MAPDERIVED | NOMAPDERIVED

This option is valid for Replicat. Use MAPDERIVED and NOMAPDERIVED to control whether or not Replicat converts the name of a derived object according to a TARGET clause of a MAP statement that includes it. ??MAPDERIVED: Allows mapping of derived object names according to the rules stated in “How GoldenGate handles derived object names” on page 397. MAPDERIVED is the default. ??NOMAPDERIVED: Prevents name mapping. NOMAPDERIVED overrides any explicit MAP statements that contain the name of the base or derived object. Source DDL that contains derived objects is replicated with the same owner and object names on the target.

Table 42 shows the results of MAPDERIVED compared to NOMAPDERIVED, based on whether there is a MAP statement just for the base object, just for the derived

object, or for both.

本文来源：https://www.bwwdw.com/article/sdb6.html