一.DDL 說明

1.1 說明

這部分的說明，在系列四和系列六中已經(jīng)說明：

Oracle Golden Gate 系列六 --11gR2 Ora2Ora 單向復(fù)制GG 示例

http://blog.csdn.net/tianlesoftware/article/details/6950018

 

Oracle Golden Gate 系列四--GG 安裝 與 卸載 理論知識

http://blog.csdn.net/tianlesoftware/article/details/6937183

 

不過為了便于整理與查閱，還是將DDL 同步這部分內(nèi)容單獨整理出來。

 

GoldenGate的DDL同步只支持兩邊一致的數(shù)據(jù)庫，限制條件較多（如不能進行字段映射、轉(zhuǎn)換等）。這些限制可以參考：

Oracle Gloden Gate 系列三--GG 支持與不支持的對象類型與操作 說明

http://blog.csdn.net/tianlesoftware/article/details/6933969

 

GG 支持DDL 也是通過創(chuàng)建一些table 來保存這些DDL 的信息，關(guān)于這些table 的具體說明，在系列四的第二小節(jié)：啟用GG 對DDL 操作的支持有詳細說明:

Oracle Golden Gate 系列四--GG 安裝 與 卸載 理論知識

http://blog.csdn.net/tianlesoftware/article/details/6937183

 

對于這些存放DDL 信息表的管理的理論支持，參考系列五的第四小結(jié)：Managing theOracle DDL replication environment。

Oracle Golden Gate 系列五--GG 使用配置 說明

http://blog.csdn.net/tianlesoftware/article/details/6947973

 

這里要注意：DDL的抓取不是通過日志抓取來捕獲的，而是通過觸發(fā)器來實現(xiàn)，所以對源數(shù)據(jù)庫的性能影響要比單純的數(shù)據(jù)抓取要大很多。

所以盡量不要使用GoldenGate的DDL復(fù)制功能，在大多數(shù)業(yè)務(wù)系統(tǒng)中，實際上不會有頻繁的數(shù)據(jù)庫結(jié)構(gòu)變動，完全可以通過手工的方式進行維護。

 

1.2 DDL 參數(shù)語法說明

       在配置DDL 復(fù)制時，需要在Extract 和Replicat 進程的配置文件里添加ddl屬性。 現(xiàn)在看一下這個屬性的具體說明。

這個部分內(nèi)容在官方文檔的的ConfiguringDDL synchronization for an Oracle database 小結(jié)有詳細說明。

DDL語句包含的主要語法如下：

DDL [

{INCLUDE | EXCLUDE}
[, MAPPED | UNMAPPED | OTHER | ALL]
[, OPTYPE <type>]
[, OBJTYPE ‘<type>’]
[, OBJNAME “<name>”]
[, INSTR ‘<string>’]
[, INSTRCOMMENTS ‘<comment_string>’]
]
[...]

 

1.2.1 {INCLUDE | EXCLUDE}

Use INCLUDE and EXCLUDEto identify the beginning of an inclusion or exclusion clause.

(1)  An inclusion clause containsfiltering criteria that identifies the DDL that this parameter will affect.

(2)  An exclusion clause containsfiltering criteria that excludes specific DDL from this parameter.

 

The inclusion orexclusion clause must consist of the INCLUDE or EXCLUDE keyword followed by anyvalid combination of other options of the parameter that is being applied.

 

If you use EXCLUDE, you must create acorresponding INCLUDE clause.

--如果要使用Exclude，那么必須先指定include。

 

For example, the following is invalid:

DDL EXCLUDEOBJNAME “hr.*”

--這種語法是錯誤的。

However, you can use either of thefollowing:

DDL INCLUDE ALL,EXCLUDE OBJNAME “hr.*”

DDL INCLUDE OBJNAME “fin.*” EXCLUDE “fin.ss”

       --正確語法

 

An EXCLUDE takes priority over any INCLUDEs that contain the same criteria. You can use multipleinclusion and exclusion clauses.

 

1.2.2 [, MAPPED | UNMAPPED | OTHER | ALL]

Use MAPPED, UNMAPPED,OTHER, and ALL to apply INCLUDE or EXCLUDE based on the DDL operation scope.

（1） MAPPED applies INCLUDE or EXCLUDE toDDL operations that are of MAPPED scope. MAPPED filtering is performed beforefiltering that is specified with other DDL parameter options.

（2） UNMAPPED applies INCLUDE or EXCLUDEto DDL operations that are of UNMAPPED scope.

（3） OTHER applies INCLUDE or EXCLUDE toDDL operations that are of OTHER scope.

（4） ALL applies INCLUDE or EXCLUDE toDDL operations of all scopes.

 

       關(guān)于這4種scope的不同說明，具體參考官網(wǎng)文檔。

 

1.2.3 OPTYPE <type>

Use OPTYPE toapply INCLUDE or EXCLUDE to a specific type of DDL operation, such as CREATE, ALTER,and RENAME. For <type>, use any DDL command that is valid for thedatabase.

For example, to include ALTER operations,the correct syntax is:

DDL INCLUDEOPTYPE ALTER

 

1.2.4 OBJTYPE‘<type>’

Use OBJTYPE toapply INCLUDE or EXCLUDE to a specific type of database object. For <type>,use any object type that is valid for the database, such as TABLE, INDEX, and TRIGGER.For an Oracle materialized view and materialized views log, the correct typesare snapshot and snapshot log, respectively. Enclose the name of the objecttype within single quotes.

 

For example:

DDL INCLUDEOBJTYPE ‘INDEX’

DDL INCLUDEOBJTYPE ‘SNAPSHOT’

 

For Oracle object type USER, do not use theOBJNAME option, because OBJNAME expects “owner.object” whereas USER only has aschema.

 

1.2.5 OBJNAME“<name>”

Use OBJNAME toapply INCLUDE or EXCLUDE to the fully qualified name of an object, for example owner.table_name.This option takes a doublequoted string as input. You can use a wildcard onlyfor the object name.

 

Example:

DDL INCLUDEOBJNAME “accounts.*”

 

Do not use OBJNAME for the Oracle USER object,because OBJNAME expects “owner.object” whereas USER only has a schema.

  When using OBJNAMEwith MAPPED in a Replicat parameter file, the value for OBJNAME must refer tothe name specified with the TARGET clause of the MAP statement. For example,given the following MAP statement, the correct value is OBJNAME “fin2.*”.

MAP fin.exp_*,TARGET fin2.*;

 

In the following example, a CREATE TABLE statementexecutes like this on the source:

CREATE TABLEfin.exp_phone;

 

And like this on the target:

CREATE TABLEfin2.exp_phone;

 

If a targetowner is not specified in the MAP statement, Replicat maps it to the databaseuser that is specified with the USERID parameter.

  For DDL thatcreates triggers, synonyms, and indexes, the value for OBJNAME must be the nameof the base object, not the name of the trigger, synonym, or index.

For example, toinclude the following DDL statement, the correct value is “hr.accounts,” not “hr.insert_trig.”

CREATE TRIGGERhr.insert_trig ON hr.accounts;

 

For RENAME operations,the value for OBJNAME must be the new table name. For example, to include thefollowing DDL statement, the correct value is “hr.acct.”

ALTER TABLEhr.accounts RENAME TO acct;

 

The following is an example of how tocombine DDL parameter options.

DDL &

INCLUDE UNMAPPED &

OPTYPE alter &

OBJTYPE ‘table’ &

OBJNAME “users.tab*” &

INCLUDE MAPPED OBJNAME “*” &

EXCLUDE MAPPED OBJNAME"temporary.tab*"

 

 

1.3 DDL語句格式及注意事項

 1.3.1 同步除Oracle數(shù)據(jù)庫自帶的用戶外所有用戶的DDL操作

在Extract和 Replicat進程中加入下面的內(nèi)容:

ddl include all

 

       會自動同步所有除系統(tǒng)用戶（SYS/SYSTEM）之外的DDL 操作，但是這些對象的DML 操作是否同步則受限與Extract 和 Data Pump 進程里的table 參數(shù)控制。如：

 

GGSCI (gg1) 41> view param ext1

 extract ext1

userid ggate@gg1, password ggate

--rmthost gg2,mgrport 7809

--rmttrail /u01/ggate/dirdat/lt

exttrail /u01/ggate/dirdat/lt

ddl include all

table dave.*;

 

1.3.2 只同步一個用戶的數(shù)據(jù)

使用DDL 的語法：

ddl include allobjname dave.*;

 

示例如下：

GGSCI (gg1) 51> view param ext1

 

extract ext1

userid ggate@gg1, password ggate

--rmthost gg2,mgrport 7809

--rmttrail /u01/ggate/dirdat/lt

exttrail /u01/ggate/dirdat/lt

ddl include all objname dave.*;

table dave.*;

 

1.4 啟用DDL

開啟DDL同步的基本配置步驟為：

（1）關(guān)閉ORACLE的回收站功能。

（2）選擇一個數(shù)據(jù)庫schema存放支持DDL的GoldenGate對象，運行相應(yīng)創(chuàng)建腳本。

（3）編輯globals參數(shù)文件。

（4）修改extl和repl的配置文件

 

 

具體操作步驟：

（1）關(guān)閉數(shù)據(jù)庫回收站：

SQL>alter system set recyclebin=offscope=both;

 

（2）編輯globals參數(shù)文件：

GGSCI>edit param ./globals

添加以下內(nèi)容后保存：

GGSCHEMA ggate  --標(biāo)明支持DDL的GG對象存放在哪個schema下

 

（3）執(zhí)行創(chuàng)建腳本：

首先需要命令行進入GG安裝目錄下，然后再運行sqlplus執(zhí)行腳本，如果不進入目錄下腳本執(zhí)行會報錯（應(yīng)該是由于GG腳本中子腳本嵌套使用相對路徑的問題所造成）。

SQL>@marker_setup.sql    --提示輸入目標(biāo)schema

SQL>@ddl_setup.sql       --提示輸入目標(biāo)schema,輸入initialsetup最后輸入yes

SQL>@role_setup.sql

SQL>grant GGS_GGSUSER_ROLE to ggate; --不進行該步賦權(quán)后面起進程會報錯

SQL>@ddl_enable.sql      --使觸發(fā)器生效

 

（4）修改提取進程和復(fù)制進程的配置文件。

       在Extract 里添加：

ddl include all

 

在Replicat 里添加：

ddl include all

ddlerror default ignore retryop       

此時repl必須指定assumetargetdefs屬性，這表明只有兩邊數(shù)據(jù)庫結(jié)構(gòu)一致的情況下才可以啟用DDL復(fù)制。另外，開啟DDL同步不能再只映射單表了，對整個模式下的對象都有效。

1.5 清除DDL 同步設(shè)置

如果因為DDL 同步出現(xiàn)很多問題，最簡單的方法就是卸載之后重建。安裝目錄下只提供了清除對象的腳本，可以如下操作：

 

首先要求把所有的GG進程停掉，包括mgr進程

SQL>@ddl_disable.sql  --首先使DDL觸發(fā)器失效

SQL>@ddl_remove.sql

SQL>@marker_remove.sql

 

role_setup.sql沒有對應(yīng)的清除腳本，但是這塊不影響配置信息的清除然后重新再創(chuàng)建腳本。

 

二.  清除DDL 同步示例

因為我之前的環(huán)境中已經(jīng)啟用了DDL，所以我們先清除DDL，在啟用。

 

2.1 停GG 進程

GGSCI (gg1) 85> info all

Program    Status      Group       Lag           Time Since Chkpt

 

MANAGER    RUNNING                                          

EXTRACT    RUNNING     DPUMP       00:00:00      00:00:04   

EXTRACT    RUNNING     EXT1        00:00:00      00:00:02   

 

GGSCI (gg1) 86> stop ext1

 

Sending STOP request to EXTRACT EXT1 ...

Request processed.

 

 

GGSCI (gg1) 87> stop dpump

 

Sending STOP request to EXTRACT DPUMP ...

Request processed.

 

GGSCI (gg1) 88> stop mgr

Manager process is required by other GGSprocesses.

Are you sure you want to stop it (y/n)? y

 

Sending STOP request to MANAGER ...

Request processed.

Manager stopped.

 

GGSCI (gg1) 89> info all

Program    Status      Group       Lag           Time Since Chkpt

 

MANAGER    STOPPED                                          

EXTRACT    STOPPED     DPUMP       00:00:00      00:00:27   

EXTRACT    STOPPED     EXT1        00:00:00      00:00:33   

 

GGSCI (gg1) 90>

 

2.2 進入GG 根目錄，執(zhí)行腳本

       這里一定要進入根目錄，不然執(zhí)行腳本時會報錯。

 

gg1:/home/oracle> cd $GGATE

gg1:/u01/ggate> sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.3.0 Production onWed Nov 16 21:23:28 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

 

SQL> @ddl_disable.sql

Trigger altered.

 

SQL> @ddl_remove.sql

DDL replication removal script.

WARNING: this script removes all DDLreplication objects and data.

 

You will be prompted for the name of aschema for the GoldenGate database objects.

NOTE: The schema must be created prior torunning this script.

 

Enter GoldenGate schema name:ggate

Working, please wait ...

Spooling to file ddl_remove_spool.txt

Script complete.

 

SQL> @marker_remove.sql

 

Marker removal script.

WARNING: this script removes all markerobjects and data.

 

You will be prompted for the name of aschema for the GoldenGate database objects.

NOTE: The schema must be created prior torunning this script.

 

Enter GoldenGate schemaname:ggate

 

PL/SQL procedure successfully completed.

 

Sequence dropped.

Table dropped.

Script complete.

 

三. 啟用DDL同步示例

 3.1 禁用數(shù)據(jù)庫回收站

SQL>alter system set recyclebin=off scope=both;

 

如果啟用DDL 支持，必須關(guān)閉recyclebin。官網(wǎng)的解釋如下：

If therecyclebin is enabled, the Oracle GoldenGate DDL trigger session receivesimplicitrecycle bin DDL operations that cause the trigger to fail.

  --注意這在10g里，必須關(guān)閉recycle bin，在11g以后的版本，可以不用關(guān)閉。

 

3.2 創(chuàng)建存放DDL 信息的user并賦權(quán)

SQL> create user ggate identified by ggate default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource to ggate;

Grant succeeded.

SQL> grant execute on utl_file to ggate;

Grant succeeded.

 

 

3.3 編輯globals參數(shù)文件：

GGSCI>edit param ./globals

添加以下內(nèi)容后保存：

GGSCHEMA ggate 

--標(biāo)明支持DDL的GG對象存放在哪個schema下，這個schema就是我們之前創(chuàng)建的schema。

 

3.4 執(zhí)行安裝腳本

注意：退出所有使用Oracle 的session，然后進入GG的安裝目錄，最后執(zhí)行sqlplus，使用SYSDBA權(quán)限的用戶執(zhí)行如下腳本：

 

SQL>@marker_setup.sql    --提示輸入目標(biāo)schema

SQL>@ddl_setup.sql       --提示輸入目標(biāo)schema,輸入initialsetup最后輸入yes

SQL>@role_setup.sql

SQL>grant GGS_GGSUSER_ROLE to ddw; --不進行該步賦權(quán)后面起進程會報錯

SQL>@ddl_enable.sql      --使觸發(fā)器生效

 

gg1:/u01/ggate> echo $GGATE

/u01/ggate

gg1:/u01/ggate> sqlplus / as sysdba;

 

SQL*Plus: Release 11.2.0.3.0 Production onWed Nov 16 21:39:18 2011

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

--腳本1

SQL> @marker_setup.sql

 

Marker setup script

 

You will be prompted for the name of aschema for the GoldenGate database objects.

NOTE: The schema must be created prior torunning this script.

NOTE: Stop all DDL replication beforestarting this installation.

 

Enter GoldenGate schema name:ggate

 

Marker setup table script complete, runningverification script...

Please enter the name of a schema for theGoldenGate database objects:

Setting schema name to GGATE

 

MARKER TABLE

-------------------------------

OK

 

MARKER SEQUENCE

-------------------------------

OK

 

Script complete.

 

--腳本2：

SQL> @ddl_setup.sql;

 GoldenGate DDL Replication setupscript

 

Verifying that current user hasprivilegesto install DDL Replication...

 

You will be prompted for the name ofaschema for the GoldenGate database objects.

NOTE: For an Oracle 10g source, thesystemrecycle bin must be disabled. For Oracle 11g and later, it can beenabled.

--注意這里提示我們在10g里，必須關(guān)閉recycle bin，在11g以后的版本，可以不用關(guān)閉。

NOTE: The schema must be created priortorunning this script.

NOTE: Stop all DDL replicationbeforestarting this installation.

 

--提示輸入GG的用戶：

Enter GoldenGate schema name:ggate

 

You will be prompted for the modeofinstallation.

To install or reinstall DDLreplication,enter INITIALSETUP

To upgrade DDL replication, enter NORMAL

--這里讓我們選擇安裝模式： install 和 reinstall 選擇INITIALSETUP

Enter mode of installation:INITIALSETUP

 

Working, please wait ...

Spooling to file ddl_setup_spool.txt

 

Checking for sessions that are holdinglockson Oracle Golden Gate metadata tables ...

 

Check complete.

 

Using GGATE as a GoldenGate schemaname,INITIALSETUP as a mode of installation.

 

Working, please wait ...

 

DDL replication setup scriptcomplete,running verification script...

Please enter the name of a schema fortheGoldenGate database objects:

Setting schema name to GGATE

 

DDLORA_GETTABLESPACESIZE STATUS:

……

 

STATUS OF DDL REPLICATION

-------------------------------------------------------------------------------------------------------

SUCCESSFUL installation of DDLReplicationsoftware components

 

Script complete.

 

--腳本3：

SQL> @role_setup.sql;

 

GGS Role setup script

 

This script will drop and recreate the roleGGS_GGSUSER_ROLE

To use a different role name, quitthisscript and then edit the params.sql script to change the gg_role parameterto the preferred name. (Do not run the script.)

 

You will be prompted for the name of a schemafor the GoldenGate database objects.

NOTE: The schema must be created priortorunning this script.

NOTE: Stop all DDL replication before startingthis installation.

--同樣輸入GG用戶名：

Enter GoldenGate schema name: ggate

Wrote file role_setup_set.txt

 

PL/SQL procedure successfully completed.

 

Role setup script complete

 

Grant this role to each user assigned totheExtract, GGSCI, and Manager processes, by using the following SQL command:

--這里提示我們賦權(quán)給相關(guān)的用戶：

GRANT GGS_GGSUSER_ROLE TO<loggedUser>

where <loggedUser> is theuserassigned to the GoldenGate processes.

 

--腳本4：賦權(quán)

SQL> grant GGS_GGSUSER_ROLE to ggate;

Grant succeeded.

 

--腳本5：

SQL> @ddl_enable.sql;

Trigger altered.

 

注意這里腳本創(chuàng)建的table都是使用默認的名稱，當(dāng)然也可以修改這些table的默認名，具體這塊參考之前的文檔中的說明。

 

3.5 配置Extract和 Replicat 參數(shù)

修改提取進程和復(fù)制進程的配置文件，分別加入ddl include all屬性。如果只對某個用戶，則使用如下格式：

ddl include all objname dave.*;

 

此時repl必須指定assumetargetdefs屬性，這表明只有兩邊數(shù)據(jù)庫結(jié)構(gòu)一致的情況下才可以啟用DDL復(fù)制。

另外，開啟DDL同步不能再只映射單表了，對整個模式下的對象都有效。加入DDL復(fù)制之后，數(shù)據(jù)復(fù)制的lag明顯增加了。

 

Extract 參數(shù)：

GGSCI (gg1) 35> view param ext1

 

extract ext1

userid ggate@gg1, password ggate

--rmthost gg2,mgrport 7809

--rmttrail /u01/ggate/dirdat/lt

exttrail /u01/ggate/dirdat/lt

ddl include all

table dave.*;

 

Data Pump 參數(shù)：

GGSCI (gg1) 15> view param dpump

 

extract dpump

userid ggate@gg1, password ggate

rmthost gg2, mgrport 7809

rmttrail /u01/ggate/dirdat/lt

passthru

table dave.*;

 

修改Replicat 參數(shù)：

GGSCI (gg2) 170> view param rep1

 

replicat rep1

ASSUMETARGETDEFS

userid ggate@gg2,password ggate

discardfile/u01/ggate/dirdat/rep1_discard.txt, append, megabytes 10

--HANDLECOLLISIONS

ddl include all

ddlerror default ignore retryop       

map dave.*, target dave.*;

 

 

3.6 驗證DDL 同步

GGSCI (gg1) 3> start mgr

Manager started.

 

GGSCI (gg1) 4> start ext1

Sending START request to MANAGER ...

EXTRACT EXT1 starting

 

GGSCI (gg1) 5> start dpump

Sending START request to MANAGER ...

EXTRACT DPUMP starting

 

GGSCI (gg1) 11> info all

 

Program    Status      Group       Lag           Time Since Chkpt

 

MANAGER    RUNNING                                          

EXTRACT    RUNNING     DPUMP       00:00:00      00:36:04   

EXTRACT    RUNNING     EXT1        00:00:00      00:00:09   

 

這里要注意，如果啟用了Data Pump，那么必須等Target 端的GG 進程啟動以后才能啟動，不然會報：

2011-11-1621:57:09  WARNING OGG-01223  TCP/IP error 111 (Connection refused).

 

在Source DB 上創(chuàng)建一張測試表：

 SQL> conn dave/dave;

Connected.

SQL> create table anqing as select *from sys.all_users;

Table created.

 SQL>

 

到Target DB上驗證：

SQL> select count(1) from anqing;

  COUNT(1)

----------

       33

 

DDL 同步驗證成功。

 

Source 在DML操作：

SQL> insert into anqing select * fromsys.all_users;

33 rows created.

SQL> commit;

Commit complete.

 

Target 上驗證DML：

SQL> select count(*) from anqing;

 COUNT(*)

----------

       66

 

同步成功，至此DDL的測試結(jié)束。

 

 

 

 

-------------------------------------------------------------------------------------------------------

版權(quán)所有，文章允許轉(zhuǎn)載，但必須以鏈接方式注明源地址，否則追究法律責(zé)任!

Blog:     http://blog.csdn.net/tianlesoftware

Weibo: http://weibo.com/tianlesoftware

Email:   tianlesoftware@gmail.com

Skype: tianlesoftware

 

-------加群需要在備注說明Oracle表空間和數(shù)據(jù)文件的關(guān)系，否則拒絕申請----

DBA1 群：62697716(滿);   DBA2 群：62697977(滿)  DBA3 群：62697850(滿)  

DBA 超級群：63306533(滿);  DBA4 群：83829929(滿) DBA5群： 142216823(滿) 

DBA6 群：158654907(滿)   DBA7 群：69087192(滿)  DBA8 群：172855474

DBA 超級群2：151508914  DBA9群：102954821     聊天 群：40132017(滿)