GolddenGate的用户下所有表及DDL配置

1.gguser下所有表的变动情况及时反映到目的数据库

 

源机：

  GGSCI (localhost.localdomain) 3> edit params eorakk
EXTRACT EORAKK
USERID system, PASSWORD oracle
RMTHOST 192.168.0.100, MGRPORT 7809
RMTTRAIL  ./dirdat/kk
TABLE gguser.*;

 

目标机：

  REPLICAT RORAKK
USERID system, PASSWORD oracle
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/RORAKK.DSC, PURGE
--MAP gguser.tcustmer TARGET gguser.tcustmer;
--MAP gguser.tcustord TARGET gguser.tcustord;
MAP gguser.* TARGET gguser.*;

 

2.DDL语句同步

 1.安装存储表

 

 SQL> @marker_setup   --该脚本用以建立一个DDL标记表

 SQL> @ddl_setup         -- 以INITIALSETUP选项运行ddl_setup.sql将在数据库中创建捕获DDL语句的Trigger等必要组件 

 

 SQL> @role_setup    

 

role_setup脚本用以建立GGS_GGSUSER_ROLE角色

我们需要将该GGS_GGSUSER_ROLE授予给extract group参数中定义的userid用户

 

SQL> grant GGS_GGSUSER_ROLE TO system;

 

 

SQL> @ddl_enable

ddl_enable.sql将正式enable ddl捕获触发器，即:ALTER TRIGGER sys .&ddl_trigger_name ENABLE; 

 

SQL> @?/rdbms/admin/dbmspool

执行dbmspool包将在数据库中创建DBMS_SHARED_POOL包，之后需要用到

 

SQL> @ddl_pin
ddl_pin.sql通过dbms_shared_pool.keep存储过程将DDLReplication相关的对象keep在共享池中，以保证这些对象不要reload，提升性能 

 

2.配置gg

 源机：

  GGSCI (localhost.localdomain) 1> add extract load1, tranlog, begin now
EXTRACT added

 

GGSCI (localhost.localdomain) 3> add rmttrail ./dirdat/ma, megabytes 100, extract load1
RMTTRAIL added.

 

--GGSCI (localhost.localdomain) 4> encrypt password system
--No key specified, using default key...

--Encrypted password:  AACAAAAAAAAAAAGAMGMBUEODLIVEUFMJ

 GGSCI (localhost.localdomain) 6> edit params load1

 

加入：
extract load1
userid system, password oracle

RMTHOST 192.168.0.100, MGRPORT 7809
RMTTRAIL ./dirdat/ma
DDL INCLUDE MAPPED
Table gguser.*;

 

GGSCI (localhost.localdomain) 5> start extract load1

GGSCI (localhost.localdomain) 5> view report  load1

 

解决错误：

2011-04-21 01:31:20  ERROR   OGG-00303  Error (-2) decrypting string: Unknown encryption type.

使用encrypt password system出现此情况，因此不用系统生成的密码

 

3.配置目标

 GGSCI (localhost.localdomain) 4> add replicat rep1, exttrail ./dirdat/ma, begin now, checkpointtable system.checkpoint

GGSCI (localhost.localdomain) 6> edit params rp1

 

写入:
replicat rep1
userid system, password oracle
ASSUMETARGETDEFS
discardfile ./dirrpt/rep1.dsc, PURGE
HANDLECOLLISIONS
DDL
map gguser.*, target gguser.*;

 

GGSCI (localhost.localdomain) 7> start replicat rep1
ERROR: Parameter file /u01/app/oracle/gg11/dirprm/rep1.prm does not exist. ---查看文件是否存在

ERROR   OGG-00446  Checkpoint table SYSTEM.GGCHKPTABLE does not exist

解决办法：添加checkpoint， checkpoint需参考./GLOBALS中参数

 

 4.验证

 源机：

  SQL> conn gguser/gguser

  
SQL> create table t2(id number(10,0));

 

目标机：desc t2

 

源机：

  insert into t2 values(10000);

commit;

 

目标机:select * from t2