导航：首页 > 数据库 >

linux下搭建oracle ogg的过程

发表于：2024-11-16 作者：千家信息网编辑

千家信息网最后更新 2024年11月16日，这篇文章主要介绍"linux下搭建oracle ogg的过程"，在日常操作中，相信很多人在linux下搭建oracle ogg的过程问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对

千家信息网最后更新 2024年11月16日linux下搭建oracle ogg的过程

这篇文章主要介绍"linux下搭建oracle ogg的过程"，在日常操作中，相信很多人在linux下搭建oracle ogg的过程问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答"linux下搭建oracle ogg的过程"的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

原库：
create user usera identified by usera;
grant resource ,connect,dba to usera;
create table usera.test1 as select * from dba_objects where 1=2;
alter table usera.test1 add constraint pk_test_table primary key(object_id) enable;
目标库：
create user userb identified by userb;
grant resource ,connect,dba to userb;
create table USERB.TEST2 as select * from dba_objects where 1=2;
alter table uSERB.TEST2 add constraint pk_test_table2 primary key(object_id) enable;

原库和目标库:
create tablespace GOLDENGATE_DATASPACE datafile '/u01/app/oracle/oradata/mydb/GOLDENGATE_DATASPACE.dbf' size 2g;
create user goldengate identified by ggs_1234 default tablespace GOLDENGATE_DATASPACE temporary tablespace temp;
grant resource,connect,dba to goldengate;
grant unlimited tablespace to goldengate;

原库和目标库:
SELECT SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING FROM V$DATABASE; ####必须都为YES
ALTER SYSTEM SWITCH LOGFILE;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER SYSTEM SWITCH LOGFILE;
alter database force logging;
SELECT SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING FROM V$DATABASE;

archive log list ###必须为归档模式
shutdown immediate
startup mount;
alter database archivelog;
alter database open;
archive log list

下载软件support.oracle.com：
补丁程序与更新程序--》产品【Oracle GoldenGate】--》发行版【GGATE 11.1.1.1.0~20】--》平台【Linux x86】
本环境是rhel5 32位：p13072170_111112_LINUX.zip

将ogg安装在/u01/app/ogg下
[oracle@host03 ~]$ echo $ORACLE_BASE
/u01/app/oracle
su - oracle
cd /u01/app/
mkdir ogg
cd ogg
将p13072170_111112_LINUX.zip上传到/u01/app/ogg/目录下
unzip p13072170_111112_LINUX.zip
tar -xvf fbo_ggs_Linux_x86_ora11g_32bit.tar
[oracle@host03 ~]$ PWD
/u01/app/oracle/ogg
mkdir dirdat
mkdir dirrpt
mkdir dirprm
mkdir dirpcs
service iptables status确认防火墙关闭
getenforce 确认selinux关闭

启动mgr：
cd /u01/app/ogg
./ggsci
info all
edit params mgr
######## [oracle@host03 dirprm]$ cat mgr.prm
########PORT 7809
########DYNAMICPORTLIST 7800-7810
########PURGEOLDEXTRACTS ./dirdat/*/*, USECHECKPOINTS, MINKEEPDAYS 7
########AUTOSTART ER *
########AUTORESTART ER *,RETRIES 5, WAITMINUTES 3, RESETMINUTES 30
########[oracle@host03 dirprm]$ pwd
########/u01/app/ogg/dirprm
EDIT PARAMS ./GLOBALS
########[oracle@host03 ogg]$ cat GLOBALS
########CHECKPOINTTABLE goldengate.ggschkpt
########[oracle@host03 ogg]$ pwd
########/u01/app/ogg
GGSCI (host03.example.com) 1> info all
Program Status Group Lag Time Since Chkpt
MANAGER STOPPED

GGSCI (host03.example.com) 2> start mgr
Manager started.

GGSCI (host03.example.com) 4> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING

目标端添加checkpoint表
GGSCI (host03.example.com) 7> dblogin userid goldengate
Password:
Successfully logged into database.

GGSCI (host03.example.com) 8> ADD CHECKPOINTTABLE ggschkpt
Successfully created checkpoint table GGSCHKPT.

GGSCI (host03.example.com) 10> info trandata usera.test1
Logging of supplemental redo log data is disabled for table USERA.TEST1.

GGSCI (host03.example.com) 11> DELETE TRANDATA usera.test1
Logging of supplemental redo log data is already disabled for table USERA.TEST1.

GGSCI (host03.example.com) 12> ADD TRANDATA usera.test1
Logging of supplemental redo data enabled for table USERA.TEST1.

GGSCI (host03.example.com) 13> INFO TRANDATA usera.test1
Logging of supplemental redo log data is enabled for table USERA.TEST1

目标端定义文件，不同数据库类型可能需要用到。
--[oracle@host03 ogg]$ vi ./dirprm/defgen20160908.prm
--[oracle@host03 ogg]$ cat ./dirprm/defgen20160908.prm
--DEFSFILE ./dirdef/ecom20110908.def
--USERID GOLDENGATE, PASSWORD ggs_1234
--TABLE usera.test1;
--[oracle@host03 ogg]$ ./defgen parameter ./dirprm/defgen20160908.prm
--2017-09-17 23:39:11 ERROR OGG-00012 Command line error:invalid startup syntax: parameter.
--2017-09-17 23:39:11 ERROR OGG-01668 PROCESS ABENDING.

[oracle@host03 ogg]$ cat /dev/null> ./dirrpt/extecom.dsc
[oracle@host03 ogg]$ cat /dev/null> ./dirrpt/dppecom.dsc
[oracle@host03 ogg]$ cat /dev/null> ./dirrpt/iniecom.dsc
[oracle@host03 ogg]$ mkdir ./dirdat/ecom
[oracle@host03 ogg]$ cd dirdat/ecom/
[oracle@host03 ecom]$ ls #确保为空

编辑源库抽取进程参数文件
[oracle@host03 ogg]$ pwd
/u01/app/ogg
[oracle@host03 ogg]$ vi ./dirprm/extecom.prm
############EXTRACT extecom
############SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1")
############SETENV (ORACLE_SID="mydb")
############USERID goldengate, password ggs_1234
############
############discardfile ./dirrpt/extecom.dsc, append, megabytes 1000
############discardrollover at 3:00
############
############warnlongtrans 2h, checkinterval 3m
############
############EXTTRAIL ./dirdat/ecom/ss, megabytes 100
############NUMFILES 3000
############DYNAMICRESOLUTION
############EOFDELAYCSECS 10
############
############TABLE usera.test1;

#编辑源库投递进程参数文件
vi ./dirprm/dppecom.prm
############EXTRACT dppecom
############RMTHOST 192.168.56.101, MGRPORT 7809
############RMTTRAIL ./dirdat/target/rs
############DISCARDFILE ./dirrpt/dppecom.dsc, PURGE
############PASSTHRU
############DYNAMICRESOLUTION
############EOFDELAYCSECS 10
############
############TABLE usera.test1;

目标库：
[oracle@host03 ogg]$ cat /dev/null>./dirrpt/repecom.dsc
[oracle@host03 ogg]$ cat /dev/null>./dirrpt/rinecom.dsc

#创建目标库初始化装载进程参数文件
vi ./dirprm/rinecom.prm
#########REPLICAT repecom
#########
#########SETENV (NLS_LANG= "american_america.ZHS16GBK")
#########SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1")
#########SETENV (ORACLE_SID="mydb")
#########USERID goldengate, password ggs_1234
#########--SOURCEDEFS ./dirdef/ecom20110908.def
#########
#########ASSUMETARGETDEFS
#########--HANDLECOLLISIONS
#########
#########reperror default,discard
#########DISCARDFILE ./dirrpt/repecom.dsc, PURGE, megabytes 1000
#########
#########--EXTTRAIL ./dirdat/target/rs
#########
#########NUMFILES 150
#########DYNAMICRESOLUTION
#########ALLOWNOOPUPDATES
#########GROUPTRANSOPS 1000
#########
#########MAP USERA.TEST1, TARGET USERB.TEST2;

添加进程
[oracle@host03 ogg]$ pwd
/u01/app/ogg
[oracle@host03 ogg]$ mkdir dirchk
抽取进程：
GGSCI (host03.example.com) 15> ADD EXTRACT extecom, tranlog, begin now
EXTRACT added.

GGSCI (host03.example.com) 16> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXTECOM 00:00:00 00:00:03

GGSCI (host03.example.com) 18> ADD EXTTRAIL ./dirdat/ecom/ss, EXTRACT extecom, megabytes 100
EXTTRAIL added.

GGSCI (host03.example.com) 19> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXTECOM 00:00:00 00:00:39

投递进程：
GGSCI (host03.example.com) 20> ADD EXTRACT dppecom, exttrailsource ./dirdat/ecom/ss
EXTRACT added.

GGSCI (host03.example.com) 21> ADD RMTTRAIL ./dirdat/target/rs, EXTRACT dppecom, megabytes 100
RMTTRAIL added.

GGSCI (host03.example.com) 22> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED DPPECOM 00:00:00 00:00:22
EXTRACT STOPPED EXTECOM 00:00:00 00:02:47

复制进程：
GGSCI (host03.example.com) 23> add replicat repecom, exttrail ./dirdat/ecom/ss
REPLICAT added.

GGSCI (host03.example.com) 24> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED DPPECOM 00:00:00 00:01:06
EXTRACT STOPPED EXTECOM 00:00:00 00:03:31
REPLICAT STOPPED REPECOM 00:00:00 00:00:18

GGSCI (host03.example.com) 30> delete replicat repecom ##因为目录不同比较好
Deleted REPLICAT REPECOM.

GGSCI (host03.example.com) 31> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED DPPECOM 00:00:00 00:02:53
EXTRACT STOPPED EXTECOM 00:00:00 00:05:18

[oracle@host03 ogg]$ pwd
/u01/app/ogg
[oracle@host03 ogg]$ mkdir ./dirdat/target/

GGSCI (host03.example.com) 32> add replicat repecom, exttrail ./dirdat/target/rs
REPLICAT added.
GGSCI (host03.example.com) 33> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED DPPECOM 00:00:00 00:03:42
EXTRACT STOPPED EXTECOM 00:00:00 00:06:07
REPLICAT STOPPED REPECOM 00:00:00 00:00:02

启动进程：
[oracle@host03 ogg]$ pwd
/u01/app/ogg
[oracle@host03 ogg]$ mkdir dirtmp
[oracle@host03 dirprm]$ mv rinecom.prm repecom.prm

GGSCI (host03.example.com) 52> start EXTECOM
Sending START request to MANAGER ...
EXTRACT EXTECOM starting

GGSCI (host03.example.com) 56> start DPPECOM
Sending START request to MANAGER ...
EXTRACT DPPECOM starting

GGSCI (host03.example.com) 57> start REPECOM
Sending START request to MANAGER ...
REPLICAT REPECOM starting

测试咯
原库：
sqlplus usera/usera
select * from test1; #无记录
insert into test1 select * from dba_objects where rownum<2;
commit;
select * from test1; #一条记录

目标库：
sqlplus userb/userb
select * from test2; #一条记录，说明同步成功。

查看ogg进程：
GGSCI (host03.example.com) 62> stats DPPECOM
Sending STATS request to EXTRACT DPPECOM ...
Start of Statistics at 2017-09-18 00:25:20.
Output to ./dirdat/target/rs:
Extracting from USERA.TEST1 to USERA.TEST1:
*** Total statistics since 2017-09-18 00:22:18 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Daily statistics since 2017-09-18 00:22:18 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Hourly statistics since 2017-09-18 00:22:18 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Latest statistics since 2017-09-18 00:22:18 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

End of Statistics.