大家好， 今天和大家分享的是 OGG 21C 实现 ORACLE 19C 到 postgre 13 的数据同步。
目前市面上可以做CDC 解析ORACLE redo 日志的工具来说，基本上都是都是收费的工具。 因为对于redo的破解绝对是行业的商业机密。市面上的免费的工具也都是基于通过 log miner 的实现的， 稳定性和日志解析的时候，对于源端的性能消耗还是比较大的。
如果你的公司不差钱的话， OGG 作为ORACLE 官方的同步工具自然是首选。国内还有迪思杰，国外有Qlink 等厂商也有收费的基于ORACLE redo 日志CDC的同步工具。
今天我们来做一个基于： OGG 21C 实现 ORACLE 19C 到 postgre 13 的数据同步。
实验的环境如下：
关于OGG的软件下载： 目前有2种方式
直接从官网上下载： https://www.oracle.com/middleware/technologies/goldengate-downloads.html#license-lightbox
这里我们选择的是传统的安装包，非微服务安装版。
下载 OGG for PG ： 这里我们选择的是传统的安装包，非微服务安装版。
你也可以去 https://edelivery.oracle.com/ 上面下载， 里面有OGG的历史版本的下载链接， 而且有支持更多种的数据库的OGG版本。
我们注意 OGG 版本和各种数据库的版本的兼容关系： 目前OGG 21 for postgreSQL 这个版本支持到 PG13
这个版本的兼容关系，可以从下载的解压包中的PDF 说明文件中找到。
在安装OGG的软件之前，我们需要在源端 oracle 和目标端 postgres 稍微配置一下：
源端 ORACLE:
配置环境变量： ORACLE_BASE，ORACLE_SID，ORACLE_HOME，ORA_INVENTORY ，LD_LIBRARY_PATH
export ORACLE_BASE=/u01/app/oracle export ORACLE_SID=db19c export ORACLE_HOME=${ORACLE_BASE}/product/19.0.0/dbhome_1 export ORA_INVENTORY=/u01/app/oraInventory export LD_LIBRARY_PATH=${ORACLE_HOME}/lib:/lib:/usr/lib PATH=${ORACLE_HOME}/bin:$PATH:$HOME/.local/bin:$HOME/bin export PATH
打开 archive log mode:
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL> alter system set log_archive_dest='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both sid='*';
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 4949276568 bytes
Fixed Size 8906648 bytes
Variable Size 973078528 bytes
Database Buffers 3959422976 bytes
Redo Buffers 7868416 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> exec dbms_goldengate_auth.grant_admin_privilege('app_ogg_user');
PL/SQL procedure successfully completed.
SQL> alter system set enable_goldengate_replication = TRUE;
System altered.
打开最小模式的数据库级别 logging:
SQL> alter database add supplemental log data;
Database altered.
创建OGG的账户：
SQL> create user app_ogg_user identified by "123456";
User created.
SQL> grant connect, resource to app_ogg_user;
Grant succeeded.
SQL> grant select any table to app_ogg_user;
Grant succeeded.
SQL> grant unlimited tablespace to app_ogg_user;
Grant succeeded.
安装OGG for oracle 的软件：
创建OGG的home 目录：
INFRA [oracle@ljzdcmongo004 Disk1]# mkdir -p /u01/ogg4oracle
解压软件启动图形界面安装：
INFRA [oracle@ljzdcmongo004 ogg]# unzip _fbo_ggs_Linux_x64_Oracle_shiphome.zip
INFRA [oracle@ljzdcmongo004 u01]# ./runInstaller
OGG的软件安装完毕后，我们需要配置一下 环境变量：
PATH=$PATH:$HOME/.local/bin:$HOME/bin export ORACLE_BASE=/u01/app/oracle export ORACLE_SID=db19c export ORACLE_HOME=${ORACLE_BASE}/product/19.0.0/dbhome_1 export OGG_HOME=/u01/ogg4oracle export ORA_INVENTORY=/u01/app/oraInventory export LD_LIBRARY_PATH=${ORACLE_HOME}/lib:/lib:/usr/lib:${OGG_HOME}/lib PATH=${ORACLE_HOME}/bin:$PATH:$HOME/.local/bin:$HOME/bin:${OGG_HOME} export PATH
ggsci 进入命令行 创建相关的命令：
GGSCI (ljzdcmongo004.cn.infra) 2> create subdirs
Creating subdirectories under current directory /home/oracle
Parameter file /u01/ogg4oracle/dirprm: created.
Report file /u01/ogg4oracle/dirrpt: created.
Checkpoint file /u01/ogg4oracle/dirchk: created.
Process status files /u01/ogg4oracle/dirpcs: created.
SQL script files /u01/ogg4oracle/dirsql: created.
Database definitions files /u01/ogg4oracle/dirdef: created.
Extract data files /u01/ogg4oracle/dirdat: created.
Temporary files /u01/ogg4oracle/dirtmp: created.
Credential store files /u01/ogg4oracle/dircrd: created.
Master encryption key wallet files /u01/ogg4oracle/dirwlt: created.
Dump files /u01/ogg4oracle/dirdmp: created.
编辑 manager的参见文件，并创建启动进程：
GGSCI (ljzdcmongo004.cn.infra) 3> edit param mgr
PORT 7809
GGSCI (ljzdcmongo004.cn.infra) 1> start mgr
Manager started.
GGSCI (ljzdcmongo004.cn.infra) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
目标端安装OGG for postgres:
PG 数据库端最基本的2项配置：
pg_hba.conf:
host all all 0.0.0.0/0 md5
postgresql.conf：
listen_addresses = '*'
创建同步的数据库和账户：
postgres@[local:/tmp]:2023=#108061 create user ogg_user with password '123456';
CREATE ROLE
postgres@[local:/tmp]:2023=#108061 create database OGGTEST owner=ogg_user;
CREATE DATABASE
安装解压 OGG的软件
mkdir -p /opt/ogg4pg unzip 213000_ggs_Linux_x64_PostgreSQL_64bit.zip tar -xvf ggs_Linux_x64_PostgreSQL_64bit.tar
配置OGG的环境变量：
OGG_HOME=/opt/ogg4pg PATH=$PATH:$HOME/.local/bin:$HOME/bin:$PG_HOME/bin:$OGG_HOME export ODBCINI=/opt/ogg4pg/odbc.ini
OGG是通过ODBC的方式连接到 postgres 上面的， 我们需要配置一个ODBC的参数文件 odbc.ini
[ODBC Data Sources] GG_Postgres=DataDirect 6.1 PostgreSQL Wire Protocol [ODBC] IANAAppCodePage=106 InstallDir=/opt/ogg4pg [GG_Postgres] Driver=/opt/ogg4pg/lib/GGpsql25.so Description=DataDirect 6.1 PostgreSQL Wire Protocol Database=OGGTEST HostName=wqdcsrv3352.cn.infra PortNumber=2023 LogonID=ogg_user Password=123456
在目标端创建OGG的路径：
GGSCI (wqdcsrv3352.cn.infra) 1> create subdirs
Creating subdirectories under current directory /opt/ogg4pg
Parameter file /opt/ogg4pg/dirprm: created.
Report file /opt/ogg4pg/dirrpt: created.
Checkpoint file /opt/ogg4pg/dirchk: created.
Process status files /opt/ogg4pg/dirpcs: created.
SQL script files /opt/ogg4pg/dirsql: created.
Database definitions files /opt/ogg4pg/dirdef: created.
Extract data files /opt/ogg4pg/dirdat: created.
Temporary files /opt/ogg4pg/dirtmp: created.
Credential store files /opt/ogg4pg/dircrd: created.
Master encryption key wallet files /opt/ogg4pg/dirwlt: created.
Dump files /opt/ogg4pg/dirdmp: created.
编辑manager的参数问价并启动manger 进程
GGSCI (wqdcsrv3352.cn.infra) 2> edit param mgr
PORT 7809
GGSCI (wqdcsrv3352.cn.infra) 3> start mgr
Manager started.
GGSCI (wqdcsrv3352.cn.infra) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
我们尝试在源端和目标端创建一张表进行同步，并测试一下数据库的联通性：
源端ORACLE:
SQL> alter session set current_schema=app_ogg_user;
Session altered.
SQL> create table t_order (id int not null primary key , good_name varchar2(100));
Table created.
尝试通过OGG登录数据库：
GGSCI (ljzdcmongo004.cn.infra) 1> dblogin userid app_ogg_user@db19c Password: Successfully logged into database. GGSCI (ljzdcmongo004.cn.infra as app_ogg_user@db19c) 3> list tables app_ogg_user.* "APP_OGG_USER"."T_ORDER" Found 1 tables matching list criteria. GGSCI (ljzdcmongo004.cn.infra as app_ogg_user@db19c) 4> capture tabledef "APP_OGG_USER"."T_ORDER" Table definitions for APP_OGG_USER.T_ORDER: ID NUMBER NOT NULL PK GOOD_NAME VARCHAR (100)
目标端PG：
postgres@[local:/tmp]:2023=#118683 \c oggtest ogg_user
psql (14.4, server 13.8)
You are now connected to database "oggtest" as user "ogg_user".
oggtest@[local:/tmp]:2023=>130871 create table t_order (id int not null primary key , good_name varchar(100));
CREATE TABLE
验证通过OGG登录一下：
GGSCI (wqdcsrv3352.cn.infra) 1> dblogin sourcedb gg_postgres userid ogg_user
Password:
2022-10-28 11:08:53 INFO OGG-03036 Database character set identified as UTF-8. Locale: en_US.UTF-8.
2022-10-28 11:08:53 INFO OGG-03037 Session character set identified as UTF-8.
Successfully logged into database.
GGSCI (wqdcsrv3352.cn.infra as ogg_user@gg_postgres) 3> list tables public.*
"public"."t_order"
Found 1 tables matching list criteria.
GGSCI (wqdcsrv3352.cn.infra as ogg_user@gg_postgres) 4> capture tabledef "public"."t_order"
Table definitions for public.t_order:
id NUMBER (10) NOT NULL PK
good_name VARCHAR (300)
源端配置 extract 进程：
GGSCI (ljzdcmongo004.cn.infra) 1> edit param epos
EXTRACT epos
USERID app_ogg_user@db19c, PASSWORD 123456
RMTHOST 10.67.38.50, MGRPORT 7809
RMTTRAIL ./dirdat/ep
TABLE app_ogg_user.t_order;
GGSCI (ljzdcmongo004.cn.infra) 3> add extract epos, tranlog, begin now
Integrated Extract added.
GGSCI (ljzdcmongo004.cn.infra) 4> add exttrail ./dirdat/ep, extract epos, megabytes 5
EXTTRAIL added.
这个要login 登录数据库，注册试一下 EXTRACT的进程， 否则会报错误： Logmining server does not exist on this Oracle database
参考 MOS: (Doc ID 1525272.1)
GGSCI (ljzdcmongo004.cn.infra as app_ogg_user@db19c) 12> REGISTER EXTRACT epos, DATABASE
2022-10-28 11:50:45 INFO OGG-02003 Extract group EPOS successfully registered with database at SCN 2656874.
启动进程：
GGSCI (ljzdcmongo004.cn.infra as app_ogg_user@db19c) 13> start epos
Sending START request to Manager ...
Extract group EPOS starting.
GGSCI (ljzdcmongo004.cn.infra as app_ogg_user@db19c) 14> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EPOS 00:00:00 00:00:00
由于是异构的数据库之前的同步，我们需要在源生成表的定义文件defgen，并且拷贝到客户端。
GGSCI (ljzdcmongo004.cn.infra) 1> edit param defgen DEFSFILE ./dirdef/order.def USERID app_ogg_user@db19c, password 123456 TABLE app_ogg_user.t_order;
生成表的def 文件：
INFRA [oracle@ljzdcmongo004 ogg4oracle]# ./defgen paramfile ./dirprm/defgen.prm
***********************************************************************
Oracle GoldenGate Table Definition Generator for Oracle
Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047
Oracle Linux 7, x64, 64bit (optimized), Oracle Database 21c and lower supported versions on Jul 28 2021 13:27:11
Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved.
Starting at 2022-10-28 13:30:13
***********************************************************************
Operating System Version:
Linux
Version #2 SMP Wed Jul 21 17:51:54 PDT 2021, Release 5.4.17-2102.203.6.el7uek.x86_64
Node: ljzdcmongo004.cn.infra
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 8992
***********************************************************************
** Running with the following parameters **
***********************************************************************
DEFSFILE ./dirdef/order.def
USERID app_ogg_user@db19c, password ***
2022-10-28 13:30:13 INFO OGG-03541 Oracle Environment Variables:
TNS_ADMIN = /u01/app/oracle/product/19.0.0/dbhome_1/network/admin
LD_LIBRARY_PATH = /u01/app/oracle/product/19.0.0/dbhome_1/lib:/lib:/usr/lib:/u01/ogg4oracle.
TABLE app_ogg_user.t_order;
Retrieving definition for APP_OGG_USER.T_ORDER.
Definitions generated for 1 table in ./dirdef/order.def.
拷贝同步表的def 文件到 目标端的 $OGG_HOME/dirdef 下面：
scp ./dirdef/order.def postgres@10.67.38.50:/opt/ogg4pg/dirdef
INFRA [postgres@wqdcsrv3352 dirdef]# ls -lhtr
total 2.0K
-rw-r--r-- 1 postgres postgres 1.2K Oct 28 2022 order.def
在目标端配置复制进程：
GGSCI (wqdcsrv3352.cn.infra) 1> edit param rpos
REPLICAT rpos
SOURCEDEFS ./dirdef/order.def
SETENV ( PGCLIENTENCODING = "UTF8" )
SETENV (ODBCINI="/opt/ogg4pg/odbc.ini" )
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
TARGETDB GG_Postgres, USERID ogg_user, PASSWORD 123456
DISCARDFILE ./dirrpt/diskg.dsc, purge
MAP app_ogg_user.t_order, TARGET public.t_order, COLMAP (id=id,good_name=good_name);
GGSCI (wqdcsrv3352.cn.infra) 2> add replicat rpos, NODBCHECKPOINT, exttrail ./dirdat/ep
Replicat added.
GGSCI (wqdcsrv3352.cn.infra) 3> start rpos
Sending START request to Manager ...
Replicat group RPOS starting.
GGSCI (wqdcsrv3352.cn.infra) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RPOS 00:00:00 00:00:07
测试数据的同步：
源端ORACLE插入数据：
SQL> insert into app_ogg_user.t_order values (1,'keep going! big bro!');
1 row created.
SQL> commit;
Commit complete.
目标端postgres 查看同步的数据：
oggtest@[local:/tmp]:2023=>23548 select * from t_order;
id | good_name
----+----------------------
1 | keep going! big bro!
(1 row)
Have a fun 😃 !
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
