暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / OGG For Oracle 11.2.0.4 To PostgreSQL 11.7 on RHEL 6.10 简单同步

OGG For Oracle 11.2.0.4 To PostgreSQL 11.7 on RHEL 6.10 简单同步

原创 张玉龙 2020-03-26
3390

一. 安装包下载

OGG下载地址
https://www.oracle.com/middleware/technologies/goldengate-downloads.html
PostgreSQL下载地址
https://www.postgresql.org/ftp/source/
参考文档：
How To Replicate Data from Oracle to Postgres Using GoldenGate (Doc ID 1544137.1)

二. 测试环境

源端 目标端
操作系统 RHEL 6.10 RHEL 6.10
IP 192.168.56.19 192.168.56.70
数据库版本 Oracle 11.2.0.4 PostgreSQL 11.7
OGG版本 112103_ggs_Linux_x64_ora11g_64bit 122022_ggs_Linux_x64_PostgreSQL_64bit

三. 源端Oracle环境配置

1. 创建OGG用户

# /usr/sbin/useradd -u 7809 -g oinstall ogg
# echo "ogg" | passwd --stdin ogg

ogg用户要加到oinstall组中，否则ogg不能访问redo文件。

2. 创建OGG目录

# mkdir -p /ogg
# chown -R ogg:oinstall /ogg

3. 解压OGG安装包

# unzip 112103_ggs_Linux_x64_ora11g_64bit.zip
# tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar -C /ogg/
# chown -R ogg:oinstall /ogg

4. 配置OGG环境变量

# vi /home/ogg/.bash_profile
export OGG_HOME=/ogg
export ORACLE_SID=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$ORACLE_HOME/bin:$OGG_HOME:$PATH:$HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$ORACLE_HOME/rdbms/lib:$OGG_HOME:/lib:/usr/lib
umask 022

需要注意要有ORACLE_SID环境变量，否则不能使用监听服务。

5. 配置Oracle数据库

打开 Oracle 数据库归档模式

SQL> alter system set log_archive_dest_1 = 'LOCATION=/arch' scope=spfile;
SQL> shutdown immediate;
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;

开启 force log 和 supplemental log

SQL> alter database force logging;
SQL> alter database add supplemental log data;

创建ogg用户

SQL> create tablespace ogg_tbs datafile '/oradata/orcl/ogg_tbs01.dbf' size 100M autoextend on;
SQL> create user ogg identified by ogg default tablespace ogg_tbs;
SQL> grant dba to ogg;

四. 目标端PostgreSQL环境配置

1. 创建OGG用户

# /usr/sbin/groupadd -g 1000 oinstall
# /usr/sbin/useradd -u 7809 -g oinstall ogg
# echo "ogg" | passwd --stdin ogg

2. 创建OGG目录

# mkdir -p /ogg
# chown -R ogg:oinstall /ogg

3. 解压OGG安装包

# unzip 122022_ggs_Linux_x64_PostgreSQL_64bit.zip
# tar -xvf ggs_Linux_x64_PostgreSQL_64bit.tar -C /ogg/
# chown -R ogg:oinstall /ogg

4. 配置OGG环境变量

# vi /home/ogg/.bash_profile
export OGG_HOME=/ogg
export ODBCINI=/ogg/odbc.ini
export PGBASE=/pgsql/11.7
export PGUSER=postgres
export PGPORT=5432
export PGDATA=$PGBASE/data
export PATH=$PGBASE/bin:$OGG_HOME:$PATH:$HOME/bin
export LD_LIBRARY_PATH=$PGBASE/lib:$OGG_HOME/lib:$OGG_HOME:$LD_LIBRARY_PATH

5. 安装PostgreSQL数据库，已经安装PostgreSQL可忽略此步骤

关闭防火墙

# service iptables stop
# chkconfig iptables off

安装系统包

# yum install wget *readline* *zlib* openssl openssl-devel pam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc gcc-c++ -y

配置/etc/hosts文件

# vi /etc/hosts
192.168.56.70   oggpg

创建用户postgres

# /usr/sbin/useradd -u 5432 postgres
# echo "postgres" | passwd --stdin postgres

创建PostgreSQL数据库存放目录

# mkdir -p /pgsql/11.7
# chown -R postgres:postgres /pgsql

配置postgres用户环境变量

# vi /home/postgres/.bash_profile
export PGBASE=/pgsql/11.7
export PGUSER=postgres
export PGPORT=5432
export PGDATA=$PGBASE/data
export PATH=$PGBASE/bin:$PATH:$HOME/bin
export LD_LIBRARY_PATH=$PGBASE/lib:$LD_LIBRARY_PATH

安装PostgreSQL

# tar -zxvf postgresql-11.7.tar.gz
# chown -R postgres:postgres postgresql-11.7
# cd postgresql-11.7
# ./configure --prefix=/pgsql/11.7
# gmake world
# gmake install-world
# su - postgres
$ initdb --locale=C --encoding=UTF8

6. 配置PostgreSQL数据库

配置pg_hba.conf文件

[postgres@oggpg ~]$ vi $PGDATA/pg_hba.conf
host    all             all             192.168.56.0/24         trust

配置postgresql.auto.conf文件

[postgres@oggpg ~]$ vi $PGDATA/postgresql.auto.conf
listen_addresses = '*'
port = 5432
logging_collector = on

启动数据库

[postgres@oggpg ~]$ pg_ctl -D $PGDATA start

PostgreSQL数据库创建ogg用户

[postgres@oggpg ~]$ psql
postgres=# create user ogg SUPERUSER ENCRYPTED PASSWORD 'ogg';
postgres=# CREATE DATABASE orcl OWNER ogg;
postgres=# \c orcl ogg
orcl=# create schema ogg;

创建用于连接PostgreSQL数据库的odbc.ini文件

[root@oggpg ~]# su - ogg
[ogg@oggpg ~]$ vi $OGG_HOME/odbc.ini
[ODBC Data Sources]
GG_Postgres=DataDirect 11 PostgreSQL Wire Protocol
[ODBC]
IANAAppCodePage=106
InstallDir=/ogg
[GG_Postgres]
Driver=/ogg/lib/GGpsql25.so
Description=DataDirect 11 PostgreSQL Wire Protocol
Database=orcl
HostName=192.168.56.70
PortNumber=5432
LogonID=ogg
Password=ogg

五. 源端和目标端创建演示表

本文不涉及数据量较大，需要初始化数据的过程。因此，我们仅在Oracle和PostgreSQL中创建一个简单的表来演示数据的同步过程。

1. 源端Oracle数据库创建测试表

[oracle@test ~]$ sqlplus test/test
SQL> create table ggtest (col1 number, col2 varchar2(20));
SQL> alter table ggtest add primary key (col1);

2. 目标端PostgreSQL数据库创建测试表

[postgres@oggpg ~]$ psql -d orcl -U ogg
orcl=# CREATE TABLE "public"."ggtest"
(
  "col1" integer NOT NULL,
  "col2" varchar(20),
  CONSTRAINT "PK_Col111" PRIMARY KEY ("col1")
);
orcl=# \dt
        List of relations
 Schema |  Name  | Type  | Owner 
--------+--------+-------+-------
 public | ggtest | table | ogg

六. 源端配置OGG的MGR进程

创建OGG的工作目录

[root@test ~]# su - ogg
[ogg@test ~]$ cd /ogg
[ogg@test ogg]$ ./ggsci 

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.



GGSCI (test) 1> create subdirs

Creating subdirectories under current directory /ogg

Parameter files                /ogg/dirprm: already exists
Report files                   /ogg/dirrpt: created
Checkpoint files               /ogg/dirchk: created
Process status files           /ogg/dirpcs: created
SQL script files               /ogg/dirsql: created
Database definitions files     /ogg/dirdef: created
Extract data files             /ogg/dirdat: created
Temporary files                /ogg/dirtmp: created
Stdout files                   /ogg/dirout: created

配置 MGR 进程

GGSCI (test) 2> edit param mgr
PORT 7809

GGSCI (test) 3> start mgr
Manager started.

GGSCI (test) 4> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING

七. 目标端端配置OGG的MGR进程

创建OGG的工作目录

[root@oggpg ~]# su - ogg
[ogg@oggpg ~]$ cd /ogg
[ogg@oggpg ogg]$ ./ggsci 

Oracle GoldenGate Command Interpreter
Version 12.2.0.2.2 OGGCORE_12.2.0.2.0_PLATFORMS_170630.0419
Linux, x64, 64bit (optimized), PostgreSQL on Jun 30 2017 06:27:44
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.

GGSCI (oggpg) 1> create subdirs

Creating subdirectories under current directory /ogg

Parameter files                /ogg/dirprm: created
Report files                   /ogg/dirrpt: created
Checkpoint files               /ogg/dirchk: created
Process status files           /ogg/dirpcs: created
SQL script files               /ogg/dirsql: created
Database definitions files     /ogg/dirdef: created
Extract data files             /ogg/dirdat: created
Temporary files                /ogg/dirtmp: created
Credential store files         /ogg/dircrd: created
Masterkey wallet files         /ogg/dirwlt: created
Dump files                     /ogg/dirdmp: created

配置 MGR 进程

GGSCI (oggpg) 2> edit param mgr
PORT 7809

GGSCI (oggpg) 3> start mgr
Manager started.

GGSCI (oggpg) 4> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING

八. 源端配置OGG的抽取进程和传输进程

配置 Extract 进程

GGSCI (test) 5> edit params ext01
EXTRACT ext01
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
SETENV (TNS_ADMIN="/u01/app/oracle/product/11.2.0/db_1/network/admin")
SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0/db_1")
SETENV (ORACLE_SID="orcl")
USERID ogg, PASSWORD ogg
DISCARDFILE /ogg/dirrpt/ext01.dsc,APPEND,MEGABYTES 1024
EXTTRAIL ./dirdat/aa 
TABLE test.ggtest;

GGSCI (test) 6> ADD EXTRACT ext01, TRANLOG, BEGIN NOW
GGSCI (test) 7> ADD EXTTRAIL ./dirdat/aa, EXTRACT EXT01
GGSCI (test) 8> start extract ext01
GGSCI (test) 9> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXT01       00:02:30      00:00:08

配置 Pump 进程

GGSCI (test) 10> edit params dp01
EXTRACT dp01
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
PASSTHRU
RMTHOST 192.168.56.70, MGRPORT 7809
RMTTRAIL /ogg/dirdat/pa
TABLE test.ggtest;

GGSCI (test) 11> ADD EXTRACT dp01, EXTTRAILSOURCE /ogg/dirdat/aa
GGSCI (test) 12> ADD RMTTRAIL /ogg/dirdat/pa, EXTRACT dp01, MEGABYTES 5
GGSCI (test) 13> start dp01
GGSCI (test) 14> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                           
EXTRACT     RUNNING     DP01        00:00:00      00:00:00    
EXTRACT     RUNNING     EXT01       00:00:00      00:00:10

生成define文件

GGSCI (test) 15> edit params defgen
defsfile ./dirdef/defgen.def
userid ogg, password ogg
TABLE test.ggtest;

GGSCI (test) 16> exit
[ogg@test ogg]$ ./defgen paramfile ./dirprm/defgen.prm

***********************************************************************
        Oracle GoldenGate Table Definition Generator for Oracle
 Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258
   Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 16:58:29
 
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

                    Starting at 2020-03-26 20:57:43
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Thu May 24 18:18:25 EDT 2018, Release 2.6.32-754.el6.x86_64
Node: test
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: 8450

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
defsfile ./dirdef/defgen.def
userid ogg, password ***
TABLE test.ggtest;
Retrieving definition for TEST.GGTEST

Definitions generated for 1 table in ./dirdef/defgen.def

传送到目标端

[ogg@test ogg]$ scp /ogg/dirdef/defgen.def 192.168.56.70:/ogg/dirdef

九. 目标端端配置OGG的应用进程

目标端添加checkpiont表

GGSCI (oggpg) 1> dblogin sourcedb GG_Postgres userid ogg
Password: 

2020-03-26 21:13:44  INFO    OGG-03036  Database character set identified as UTF-8. Locale: root.

2020-03-26 21:13:44  INFO    OGG-03037  Session character set identified as UTF-8.
Successfully logged into database.

GGSCI (oggpg as ogg@GG_Postgres) 2> add checkpointtable ogg.checkpointtab

Successfully created checkpoint table ogg.checkpointtab.

目标端配置Replicat 进程

GGSCI (oggpg as ogg@GG_Postgres) 3> EDIT PARAMS rep01

REPLICAT rep01
SOURCEDEFS /ogg/dirdef/defgen.def
SETENV(PGCLIENTENCODING = "UTF8" )
SETENV(ODBCINI="/ogg/odbc.ini" )
SETENV(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
TARGETDB GG_Postgres,userid ogg,password ogg
DISCARDFILE /ogg/dirrpt/rep_aa.rpt, append
MAP test.ggtest, TARGET public.ggtest, COLMAP (COL1=col1,COL2=col2);

GGSCI (oggpg as ogg@GG_Postgres) 4> add replicat rep01,exttrail /ogg/dirdat/pa,begin now,checkpointtable ogg.checkpointtab
GGSCI (oggpg as ogg@GG_Postgres) 5> start rep01
GGSCI (oggpg as ogg@GG_Postgres) 6> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     REP01       00:00:00      00:00:02

十. 验证数据同步

GGSCI (test) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     DP01        00:00:00      00:00:04    
EXTRACT     RUNNING     EXT01       00:00:00      00:00:08  

GGSCI (oggpg as ogg@GG_Postgres) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     REP01       00:00:00      00:00:09    

[oracle@test ~]$ sqlplus test/test
SQL> insert into ggtest values (1,'hello world!');
SQL> insert into ggtest values (2,'你好');
SQL> commit;

[postgres@oggpg ~]$ psql -d orcl -U ogg
psql (11.7)
Type "help" for help.

orcl=# select * from ggtest;
 col1 |     col2     
------+--------------
    1 | hello world!
    2 | 你好
(2 rows)
oraclepostgresqlogg
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论