在上一篇檔案中的OGG單向複制配置不支援DLL的同步，隻支援DML，因而本文在之前的基礎上增加對DDL語句的複制，下面是簡要配置過程記錄！
一：驗證之前的配置不支援DDL複制，這裡在source端，建立一張表，發現無法複制到target端！target端也建立相同的表後，DML操作可以成功複制
[root@db1 ~]# su - oracle
[oracle@db1 ~]$ sqlplus hr/hr
SQL> create table t2 (id number primary key,name varchar2(20));
Table created.
SQL> conn hr/hr@db2
Connected.
SQL> select tname from tab where tname='T2';
no rows selected
SQL> conn hr/hr
SQL> insert into t2 values (1,'one');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t2;
ID NAME
---------- --------------------
1 one
二：開始配置OGG支援DDL複制(在source端操作)
1：賦予ogg使用者相應的權限，修改全局配置檔案添加ggschema參數
SQL> conn /as sysdba
SQL> grant execute on utl_file to ogg;
Grant succeeded.
[oracle@db1 ~]$ cd $GGATE
[oracle@db1 ogg]$ ggsci
GGSCI (db1) 1> edit param ./GLOBALS
GGSCI (db1) 2> view param ./GLOBALS
ggschema ogg
2：運作相關的sql腳本
[oracle@db1 ogg]$ sqlplus /nolog
SQL> @marker_setup.sql
Marker setup script
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:ogg
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
Script complete.
SQL> show parameter recyclebin;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
SQL> alter system set recyclebin=off;
System altered.
recyclebin string OFF
SQL> @ddl_setup.sql
Oracle GoldenGate DDL Replication setup script
Verifying that current user has privileges to install DDL Replication...
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
Working, please wait ...
Spooling to file ddl_setup_spool.txt
Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...
Check complete.
Using OGG as a Oracle GoldenGate schema name.
RECYCLEBIN must be empty.
This installation will purge RECYCLEBIN for all users.
To proceed, enter yes. To stop installation, enter no.
Enter yes or no:yes
————————其他輸出省略————————
STATUS OF DDL REPLICATION
---------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
SQL> @role_setup.sql
GGS Role setup script
This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)
You will be prompted for the name of a schema for the GoldenGate database objects.
Enter GoldenGate schema name:ogg
Wrote file role_setup_set.txt
PL/SQL procedure successfully completed.
Role setup script complete
Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:
GRANT GGS_GGSUSER_ROLE TO <loggedUser>
where <loggedUser> is the user assigned to the GoldenGate processes.
SQL> grant ggs_ggsuser_role to ogg;
SQL> @ddl_enable.sql
Trigger altered.
SQL> @?/rdbms/admin/dbmspool.sql
Package created.
View created.
Package body created.
SQL> @ddl_pin.sql ogg
PL/SQL procedure successfully completed.
3：source端修改extract程序的params檔案，添加"ddl include all"參數，重新開機extract程序
GGSCI (db1) 1> view params eora_t1
extract eora_t1
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
ddl include all
userid ogg,password ogg
exttrail ./dirdat/aa
table hr.*;
GGSCI (db1) 2> stop extract eora_t1
Sending STOP request to EXTRACT EORA_T1 ...
Request processed.
GGSCI (db1) 3> start extract eora_t1
Sending START request to MANAGER ...
EXTRACT EORA_T1 starting
GGSCI (db1) 4> info extract eora_t1
EXTRACT EORA_T1 Last Started 2012-06-20 15:42 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:10 ago)
Log Read Checkpoint Oracle Redo Logs
2012-06-20 15:42:58 Seqno 3, RBA 50044416
SCN 0.567478 (567478)
4：target端修改replicat程序的params檔案，添加"ddl include all"和"ddlerror default ignore retryop maxretries 3 retrydelay 5" 參數，重新開機replicat程序
[root@db2 ~]# su - oracle
[oracle@db2 ~]$ cd $GGATE
[oracle@db2 ogg]$ ggsci
GGSCI (db2) 1> edit params rora_t1
GGSCI (db2) 2> view params rora_t1
replicat rora_t1
ddlerror default ignore retryop maxretries 3 retrydelay 5
handlecollisions
assumetargetdefs
discardfile ./dirrpt/rora_t1.dsc,purge
map hr.* ,target hr.*;
GGSCI (db2) 3> stop replicat rora_t1
Sending STOP request to REPLICAT RORA_T1 ...
GGSCI (db2) 4> start replicat rora_t1
REPLICAT RORA_T1 starting
GGSCI (db2) 5> info replicat rora_t1
REPLICAT RORA_T1 Last Started 2012-06-20 15:50 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:00 ago)
Log Read Checkpoint File ./dirdat/pa000000
First Record RBA 4780973
三：測試
[oracle@db1 ogg]$ sqlplus hr/hr
SQL> alter table t2 add location varchar2(200);
Table altered.
SQL> desc t2
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
NAME VARCHAR2(20)
LOCATION VARCHAR2(200)
SQL> create table t3 as select object_id,object_name from dba_objects;
SQL> select tname from tab where tname='T3';
TNAME
------------------------------
T3
SQL> desc t3;
OBJECT_ID NUMBER
OBJECT_NAME VARCHAR2(128)
本文轉自斬月部落格51CTO部落格，原文連結http://blog.51cto.com/ylw6006/904373如需轉載請自行聯系原作者
ylw6006
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)