GOLDENGATE 9.50关于DDL复制读书笔记(3)

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