GOLDENGATE 9.50关于DDL复制读书笔记.doc

GOLDENGATE 9.50关于DDL复制读书笔记1、 DDL marker table该表的缺省名称为：GGS_MARKER,用来存储数据库表中的DDL变化，该表只能插入INSERT；2、 Sequence on marker table用以标识表GGS_MARKER的列数据，类似于数据库的sequence数据；3、 DDL history tableDDL 历史记录表；4、 Object ID history table用以记录DDL语句操作的数据库对象；5、 DDL triggerDDL操作引发的操作，把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、编写全局参数文件GLOBALSGGSSCHEMA DATABASE_SYSDBA;3、一些参数和对象的名称改变必须在安装过程中修改，在运行过程中修改必须使用固定的脚本文件；并且参数名称的修改必须在GLOBALS文件中声明；4、关闭ORACLE RECYCLE9I中没有，否则将无法安装；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 和RENAMEGOLDENGATE处理两个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 yourDDL 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、 初始化导入 initial load 3、 Wildcard resolution通配符解决方案通过“*”与标准的配置语句结合，来实现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 ExitsGoldenGate user exits are not supported for use with DDL synchronization6、 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进程抓取或则忽略；通常意义上来说，在配置双向的时候，我们通常使用DDLOPTIONSIGNOREREPLICATES, GETAPPLOPS；这样EXT只抓取事物交易中的DDL，而忽略来自REP的操作；在一个级联配置中，使用DDLOPTIONS参数 GETREPLICATES, IGNOREAPPLOPS.Extract must capture the DDL from Replicat on each intermediary systemand 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 objectsaffected 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 theDDL for Table1. If Table2 has a foreign key to Table1, then its DML and DDLoperations also should be processed by ReplicatA.11、Multi-target configurations如果一个EXT进程写多个队列，他们被不同的REP进程读，这样EXT将所有的DDL操作写入队列TRAIL中，而每个REP进程通过自己的参数文件进行DDL的过滤；11、 Understanding DDL scopesDDL范围 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 41are supportedTABLE、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 Replicatconfiguration, it is applied to the target with the same owner and object name asin the source DDL.13、Other scope14、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 .SyntaxINCLUDE | 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 | ALLUse MAPPED, UNMAPPED, OTHER, and ALL to apply INCLUDE or EXCLUDE based on theDDL operation scope. MAPPED applies INCLUDE or EXCLUDE to DDL operations that are of MAPPEDscope. MAPPED filtering is performed before filtering that is specified withother DDL parameter options. UNMAPPED applies INCLUDE or EXCLUDE to DDL operations that are of UNMAPPEDscope. 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 ALTEROBJTYPE DDL数据对象类型参数使用 OBJTYPE 在 INCLUDE or EXCLUDE 中，声明一个特别的数据库对象对于, use such as TABLE, INDEX,TRIGGER, USER, ROLE. DDL INCLUDE OBJTYPE INDEXOBJNAME “”-目标对象名称参数使用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 INDEXINSTRCOMMENTS 使用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 optionsThe 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 temporary.tab*第一部分是UMAPPED部分，指定INCLUDE是修改表结构，指定的SCHEMA下的TAB起头的表；而且DDL语句中必须有alter payables才为有效果；第二部为MAPPED部分，所有的目标对象的DDL操作都被INCLUDE；被去除的部分为TEMPORARYASCHEMA下的TAB起头的表的DDL操作；How GoldenGate handles derived object namesDDL 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. Aderived object is an object that inherits some attributes of the base object toperform 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 nameand is subject to mapping with TABLE or MAP under the MAPPED scope. The derivedobject 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 fromthat of the base object. Or, you can use one MAP statement to handle both. In thecase of MAP, the conversion of derived object names on the target works asfollows.MAP exists for base object, but not derived objectIf there is a MAP statement for the base object, but not for the derived object, theresult is an implicit mapping of the derived object. Assuming the DDL statementincludes MAPPED, Replicat gives the derived object the same target schema asthat of the base object. The name of the derived object stays the same as in thesource 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 storingderived objects in the same schema as the base object. It ensures the correctname conversion even if the name of the derived object is not fully qualified inthe source statement. Also, when indexes are stored in the same target schemaas the base object, an implicit mapping eliminates the need to map derived objectnames explicitly.MAP exists for base and derived objectsIf 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 TARGETclause. 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 targetschema than that of the base object, or when the target name must be differentthan that of the source.MAP exists for derived object, but not base objectIf 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 targetDDL statement is the same as that of the source. To map a derived object, thechoices are: Use an explicit MAP statement for the base object. If names permit, map both base and derived objects in the same MAPstatement by means of a wildcard. Create a MAP statement for each object, depending on how you want thenames 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 objectsThe following explains how GoldenGate handles new tables that are createdfrom: RENAME and ALTER RENAME CREATE TABLE AS SELECTRENAME and ALTER TABLE RENAMEIn RENAME and ALTER TABLE RENAME operations, the base object is always the newtable 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 SELECTCREATE TABLE AS SELECT statements include SELECT statements and INSERTstatements 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 theirnames must be identical to the ones on the source.In a MAP statement, GoldenGate only maps the name of the new table (CREATETABLE ) to the TARGET specification, but does not map the names of theunderlying objects from the AS SELECT clause. There could be dependencies onthose objects that could cause data inconsistencies if the names were convertedto the TARGET specification.The following shows an example of a CREATE TABLE AS SELECT statement on thesource and how it would be replicated to the target by GoldenGate.CREATE TABLE a.tab1 AS SELECT * FROM a.tab2The 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 thesource: tab2.To keep the data in the underlying objects consistent on source and target, youcan configure them for data replication by GoldenGate. In the precedingexample, you could use the following statements to accommodate thisrequirement:Source:TABLE a.tab*;Target:MAPEXCLUDE a.tab2MAP a.tab*, TARGET a.x*;MAP a.tab2, TARGET a.tab2;Disabling the mapping of derived objectsYou can disable the mapping of derived names by using the DDLOPTIONSparameter with the NOMAPDERIVED option. See page 408.Setting DDL processing optionsUse the DDLOPTIONS parameter to configure aspects of DDL processing otherthan filtering and string substitution. You can use multiple DDLOPTIONSstatements, but using one is recommended. If using multiple DDLOPTIONSstatements, make each of them unique so that one does not override the other.Multiple DDLOPTIONS statements are executed in the orde