在上一篇文件中的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> create table t2 (id number primary key,name varchar2(20));
Table created.
SQL> conn hr/hr
Connected.
SQL> insert into t2 values (1,'one');
1 row created.
SQL> commit;
Commit complete.
SQL> conn hr/hr@db2
Connected.
SQL> select * from t2;
ID NAME
---------- --------------------
1 one
二：开始配置OGG支持DDL复制(在source端操作)
1：赋予ogg用户相应的权限，修改全局配置文件添加ggschema参数
SQL> conn /as sysdba
Connected.
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 ~]$ cd $GGATE
[oracle@db1 ogg]$ sqlplus /nolog
SQL> conn /as sysdba
Connected.
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
-------------------------------
OK
Script complete.
SQL> show parameter recyclebin;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
SQL> alter system set recyclebin=off;
System altered.
SQL> show parameter recyclebin;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string OFF
SQL> @ddl_setup.sql
Oracle GoldenGate DDL Replication setup script
Verifying that current user has privileges to install DDL Replication...
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
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
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.
Working, please wait ...
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
Script complete.
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.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
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;
Grant succeeded.
SQL> @ddl_enable.sql
Trigger altered.
SQL> @?/rdbms/admin/dbmspool.sql
Package created.
Grant succeeded.
View created.
Package body created.
SQL> @ddl_pin.sql ogg
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
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
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
ddl include all
ddlerror default ignore retryop maxretries 3 retrydelay 5
userid ogg,password ogg
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 ...
Request processed.
GGSCI (db2) 4> start replicat rora_t1
Sending START request to MANAGER ...
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> conn hr/hr@db2
Connected.
SQL> desc t2
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
NAME VARCHAR2(20)
LOCATION VARCHAR2(200)
SQL> conn hr/hr
Connected.
SQL> create table t3 as select object_id,object_name from dba_objects;
Table created.
SQL> conn hr/hr@db2
Connected.
SQL> select tname from tab where tname='T3';
TNAME
------------------------------
T3
SQL> desc t3;
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_ID NUMBER
OBJECT_NAME VARCHAR2(128)
©著作权归作者所有：来自51CTO博客作者ylw6006的原创作品，谢绝转载，否则将追究法律责任
oracleddlreplicationGoldenGate
共同学习，写下你的评论
评论加载中...
作者其他优质文章
