ORACLE GOLDEN GATE 双端配置

前端之家收集整理的这篇文章主要介绍了ORACLE GOLDEN GATE 双端配置前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

1.数据库相关配置

1.1源端

1.1.1创建表空间和ogg用户

sql>create tablespace tbs_gguser datafile '/u01/app/oracle/oradata/sky2/gguser.dbf' size 50m autoextend on;
sql>create user ogg identified by ogg default tablespace tbs_gguser quota unlimited on tbs_gguser;

1.1.2对用户ogg进行授权

sql>grant connect,resource to ogg;
sql>grant CREATE SESSION,ALTER SESSION to ogg;
sql>grant SELECT ANY DICTIONARY,SELECT ANY TABLE to ogg;
sql>grant ALTER ANY TABLE to ogg;
sql>grant FLASHBACK ANY TABLE to ogg;
sql>grant EXECUTE on DBMS_FLASHBACK to ogg;

1.1.3创建测试表

sql>conn scott/scott
sql>create table dept1 as select * from dept; 
给创建的dept1表定义主键(如果创建的测试表带有主键则不需要)
sql >alter table dept1 add constraint pk_dept1 primary key(deptno);

1.1.4 开启附加日志

sql>select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
可以看到附加日志没有开启,将它启用,切换之后查询是yes即可
sql>alter database add supplemental log data;
切一下归档
sql>alter system switch logfile;

1.1.5开启归档模式

sql>archive log list;
处于非归档模式,修改为归档模式
sql>shutdown immediate;
sql>startup mount
sql>alter database archivelog;
sql>alter database open;

1.1.6开启强制日志

sql>select force_logging from v$database;
sql>alter database force logging;

1.1.7添加捕捉数据改变的表

GGSCI > dblogin userid ogg,password ogg
GGSCI > add trandata scott.test01
GGSCI > info trandata scott.*

1.2目标端

1.2.1创建表空间和用户ogg

sql>create tablespace tbs_gguser datafile '/u01/app/oracle/oradata/sky3/gguser.dbf' size 50m autoextend on;
sql>create user ogg identified by ogg default tablespace tbs_gguser quota unlimited on tbs_gguser;

1.2.2对用户ogg进行授权

sql>grant connect,resource to ogg;
grant CREATE SESSION,ALTER SESSION to ogg;
grant SELECT ANY DICTIONARY,SELECT ANY TABLE to ogg;
grant ALTER ANY TABLE to ogg;
grant CREATE ANY TABLE to ogg;

1.2.3建测试表

sql>conn scott/scott
sql>create table dept1 as select * from dept where 1=2; 
给创建的dept1表定义主键(如果创建的测试表带有主键则不需要)
sql>alter table test01 add constraint pk_dept1 primary key(deptno); 
sql>grant all on dept1 to ogg;

2.OGG相关配置

2.1源端

2.1.1配置并启动MGR

GGSCI > EDIT PARAMS MGR
PORT 7809
PURGEOLDEXTRACTS ./dirdat,USECHECKPOINTS
GGSCI > start mgr

2.1.2配置并启动EXTRACT

GGSCI >EDIT PARAMS EXT_1
EXTRACT EXT_1
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg,PASSWORD ogg
EXTTRAIL ./dirdat/ex
TABLE scott.dept1;

--添加EXTARCT GGSCI >  ADD EXTRACT EXT_1,TRANLOG,BEGIN NOW

--添加TRAIL GGSCI> ADD EXTTRAIL ./dirdat/ex,EXTRACT EXT_1,MEGABYTES 5
GGSCI > start extract EXT_1
GGSCI > info extract EXT_1

2.1.3配置并启动PUMP

GGSCI > EDIT PARAMS PMP_1
EXTRACT PMP_1
SETENV  (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
PASSTHRU
RMTHOST 192.168.80.132,MGRPORT 7809
RMTTRAIL ./dirdat/ex
TABLE scott.test01;

--添加PUMP GGSCI> ADD EXTRACT PMP_1,EXTTRAILSOURCE ./dirdat/ex

--添加TRAIL GGSCI> ADD RMTTRAIL ./dirdat/ex,extract PMP_1,MEGABYTES 5

--启动data pump进程(2017.6.15添加:确保对端MGR已经开启,否则会出现错误GGSCI > start extract PMP_1

--查看状态 GGSCI > INFO EXTRACT PMP_1

2.2目标端

2.2.1配置并启动MGR

GGSCI > EDIT PARAMS MGR
PORT 7809
PURGEOLDEXTRACTS ./dirdat,USECHECKPOINTS
GGSCI > start mgr

2.2.2目标库配置replicat进程

创建GLOBALS参数
GGSCI > EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE ogg.ggschkpt
--为了让GLOBALS配置起作用,需要退出后重新进入GGSCI会话 GGSCI > exit
--添加replicat checkpoint table GGSCI > DBLOGIN USERID ogg,PASSWORD ogg
GGSCI > ADD CHECKPOINTTABLE
--添加REPILCATE GGSCI> ADD REPLICAT REP_1,EXTTRAIL ./dirdat/ex

--编辑replicat process REP_1参数 GGSCI>  EDIT PARAM REP_1
REPLICAT REP_1
SETENV  (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg,PASSWORD ogg
ASSUMetaRGETDEFS
DISCARDFILE ./dirrpt/REP_1.DSC,PURGE
MAP scott.dept1,TARGET scott.test01;

--启动进程 GGSCI > START REPLICAT REP_1

猜你在找的Oracle相关文章