OGG DDL和DML的配置

源库配置：（DDL，DML）(不做DDL可以不用跑那些脚本，并且参数要删掉DDL那句)

归档模式

开启补充日志和强制日志

alter database add supplemental log data;

alter database force logging;

创建OGG管理用户

create user ogg identified by ogg quota unlimited on users;

grant dba,sysdba to ogg;

创建要进行数据复制的用户

create user sender identified by sender quota unlimited on users;

grant create session ,resource to sender;

 

GRANT EXECUTE ON utl_file TO ogg;

编辑全局文件-----------------源端

EDIT PARAMS ./GLOBALS

加入GGSCHEMA ogg -----------OGG管理员

 

在ogg安装目录下登录数据库

@marker_setup.sql   填写OGG管理员

@ddl_setup.sql      填写OGG管理员

@role_setup.sql     填写OGG管理员  

GRANT GGS_GGSUSER_ROLE TO ogg;

@ddl_enable.sql
@marker_status.sql

 

登录到OGG管理界面

执行create subdirs

增加管理进程

edit param mgr

打开mgr

Start mgr

登录：

DBLOGIN USERID ogg, PASSWORD ogg

增加要传输的表

ADD TRANDATA sender.*   （‘*’表示sender下的所有表）

Info trandata sender.*      (开启传输表)

 

增加抽取进程

ADD EXTRACT capdb1, TRANLOG, BEGIN NOW

ADD EXTTRAIL ./dirdat/lt, EXTRACT capdb1

add exttrail ./dirdat/ts,extract eiex01,megabytes 5

EDIT PARAM capdb1

 

投递进程

ADD EXTRACT pumpdb1, EXTTRAILSOURCE ./dirdat/lt,BEGIN NOW

ADD RMTTRAIL ./dirdat/rt, EXTRACT pumpdb1

EDIT PARAM pumpdb1

目标库

创建OGG管理员

create user ogg identified by ogg quota unlimited on users;

grant dba,sysdba to ogg;

创建接收用户

create user receiver identified by receiver quota unlimited on users;

grant create session ,resource to receiver;

登录OGG管理界面

执行create subdirs

增加管理进程

edit param mgr

Start mgr;

用ogg登录

dblogin userid ogg password ogg

增加检查点

ADD CHECKPOINTTABLE  ogg.checkpoint

ADD REPLICAT repdb1, EXTTRAIL ./dirdat/rt, BEGIN NOW, CHECKPOINTTABLE ogg.checkpoint

EDIT PARAM repdb1

在目标库开启接收进程

Start *

然后在源库开启抽取和投递进程

Start *

 

进程错误可用alter extract(replicat) 进程名字,begin now修改

 

 

@ddl_setup.sql   报错

GRANT CREATE TABLE,CREATE SEQUENCE TO OGG; 这几个权限一定得给，直接给DBA没用

 

 

 

 

 

如果归档存放在ASM：

 需要配置oracle和grid 的tnsnames.ora

 Oracle的：

       ASM =

 (DESCRIPTION =

   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.206.81)(PORT = 1521))

   (CONNECT_DATA =

     (SERVER = DEDICATED)

     (SERVICE_NAME = +ASM)

     (SID_NAME = +ASM)

   )

  )

 

Grid 的：

ASM =

 (DESCRIPTION =

   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.206.81)(PORT = 1521))

   (CONNECT_DATA =

     (SERVER = DEDICATED)

     (SERVICE_NAME = +ASM)

     (SID_NAME = +ASM)

   )

  )

 

配置完尝试在plsql登陆验证：

附上脚本（双向的，归档在ASM）

源端

GGSCI (yuan) 57> view params eiex01

extract eiex01
userid ggs,password ggs
tranlogoptions excludeuser ggs
exttrail /ogg/dirdat/ts
TRANLOGOPTIONS ASMUSER sys@ASM,ASMPASSWORD oracle
ddl include objname test.*
table test.*;

GGSCI (yuan) 58> view params dpmp01

extract dpmp01
passthru
rmthost 192.168.206.82,mgrport 7809
rmttrail ./dirdat/td
table test.*;

GGSCI (yuan) 59> view params repl

replicat repl
 userid ggs,password ggs
 HANDLECOLLISIONS
 ASSUMETARGETDEFS
discardfile /ogg/dirrpt/repl.dec,purge
ddl include objname test.*
map test.*,target test.*;

目标端

GGSCI (mubiao) 31> view params eiex01

extract eiex01
userid ggs,password ggs
tranlogoptions excludeuser ggs
exttrail /ogg/dirdat/tt
TRANLOGOPTIONS ASMUSER sys@ASM,ASMPASSWORD oracle
ddl include objname test.*
table test.*;

GGSCI (mubiao) 32> view params dpmp01

extract dpmp01
passthru
rmthost 192.168.206.81,mgrport 7809
rmttrail ./dirdat/tt
table test.*;

GGSCI (mubiao) 33> view params repl

replicat repl
userid ggs,password ggs
HANDLECOLLISIONS
ASSUMETARGETDEFS
discardfile ./dirrpt/repl.dec,purge
ddl include objname test.*
map test.*,target test.*;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31401161/viewspace-2133280/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31401161/viewspace-2133280/