【Oracle】Oracle到PostgreSQL的OGG同步配置
前一阵子做的从Oracle实时同步到PostgreSQL，查到的资料很少，后经查询MOS发现有几个OGG的小版本支持Oracle到PostgreSQL的同步，过程整理后跟大家分享一下。
OS：OEL 5.8 x64
1.源端检查以下环境变量
ORACLE_HOME
ORACLE_SID
LD_LIBRARY_PATH
2.检查源端归档情况，如果没有，开启归档
alter system set log_archive_dest='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both sid='*';
shutdown immediate
startup mount
alter database archivelog;
alter database open;
3.源库开启附加日志创建goldengate用户并授权
alter database add supplemental log data;
create user goldengate identified by goldengate;
grant dba to goldengate;
4.源端解压ogg软件，并配置以下环境变量加到.bash_profile中。
export PATH=$PATH:/home/oracle/ggs
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/home/oracle/ggs/lib
5.源端create subdirs
GGSCI (test) 1> create subdirs
Creating subdirectories under current directory /home/oracle/ggs
Parameter files /home/oracle/ggs/dirprm: already exists
Report files /home/oracle/ggs/dirrpt: created
Checkpoint files /home/oracle/ggs/dirchk: created
Process status files /home/oracle/ggs/dirpcs: created
SQL script files /home/oracle/ggs/dirsql: created
Database definitions files /home/oracle/ggs/dirdef: created
Extract data files /home/oracle/ggs/dirdat: created
Temporary files /home/oracle/ggs/dirtmp: created
Stdout files /home/oracle/ggs/dirout: created
6.源端配置mgr进程
GGSCI (test) 2> edit param mgr
加个简单参数即可
PORT 7809
7.源端启动mgr
GGSCI (test) 3> start mgr
GGSCI (test) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
8.目标端建一个oracle用户，并部署ogg软件
mkdir ggs
cd ggs
tar xvf *.tar
[oracle@edb1 ggs]$ export LD_LIBRARY_PATH=/home/oracle/ggs/lib
9.目标端配置odbc.ini文件
[ODBC Data Sources]
GG_Postgres=DataDirect 9.3 PostgreSQL Wire Protocol
[ODBC]
IANAAppCodePage=106
InstallDir=/home/oracle/ggs
[GG_Postgres]
Driver=/home/oracle/ggs/lib/GGpsql25.so
Description=DataDirect 9.3 PostgreSQL Wire Protocol
Database=badly9db
HostName=edb1.test.com
PortNumber=5432
LogonID=badly9
Password=badly9
参数说明
[ODBC Data Sources]里边配置该ODBC的别名，本文件中也就是GG_Postgres
[ODBC]:
IANAAppCodePage指的是字符集的设置 这里的106值得是UTF8，如果是4则为ISO-8859-1，注意这个应该始终和postgres的字符集设置相同，不同字符集对应的值见附件。
InstallDir对应ogg的安装目录
[GG_Postgres]:这里的名称对应的是上边ODBC的别名
Driver这里指向的是ogg安装目录下的lib/GGpsql25.so
Description是描述
Database填写数据库名称
HostName填写本机的hostname，可以解析的即可。
PosrNumber是postgres的监听端口。
LogonID填写postgres的用户名
password填写postgres的密码
之后再指定环境变量ODBCINI，加入到oracle用户.bash_profile中。并生效（odbc.ini建在哪里就改成对应的路径）
export ODBCINI=/home/oracle/ggs/odbc.ini
10.目标端create subdirs
[oracle@edb1 ggs]$ ./ggsci
GGSCI (edb1) 1> create subdirs
Creating subdirectories under current directory /home/oracle/ggs
Parameter files /home/oracle/ggs/dirprm: already exists
Report files /home/oracle/ggs/dirrpt: created
Checkpoint files /home/oracle/ggs/dirchk: created
Process status files /home/oracle/ggs/dirpcs: created
SQL script files /home/oracle/ggs/dirsql: created
Database definitions files /home/oracle/ggs/dirdef: created
Extract data files /home/oracle/ggs/dirdat: created
Temporary files /home/oracle/ggs/dirtmp: created
Stdout files /home/oracle/ggs/dirout: created
11.目标端配置mgr
GGSCI (edb1) 2> edit param mgr
PORT 7809
启动mgr
GGSCI (edb1) 3> start mgr
Manager started.
GGSCI (edb1) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
12.在oracle中和postgres中创建测试表
Oracle
SQL> connect jp/jp
Connected.
SQL> create table ggtest (col1 number, col2 varchar2(20));
Table created.
SQL> alter table ggtest add primary key (col1);
Table altered.
Postgres
CREATE TABLE "public"."ggtest"
(
"col1" integer NOT NULL,
"col2" varchar(20),
CONSTRAINT "PK_Col111" PRIMARY KEY ("col1")
)
13.确认Oracle端OGG连接
GGSCI (test) 8> dblogin userid goldengate, password goldengate
Successfully logged into database.
GGSCI (test) 9> list tables *
JP.GGTEST
Found 1 tables matching list criteria.
GGSCI (test) 10> capture tabledef JP.GGTEST
Table definitions for JP.GGTEST:
COL1 NUMBER NOT NULL PK
COL2 VARCHAR (20)
14.确认Postgres端OGG连接
GGSCI (edb1) 4> dblogin sourcedb gg_postgres userid badly9
Password:
2015-12-17 16:51:18 INFO OGG-03036 Database character set identified as UTF-8. Locale: en_US.
2015-12-17 16:51:18 INFO OGG-03037 Session character set identified as UTF-8.
Successfully logged into database.
GGSCI (edb1) 5> list tables *
public.ggtest
Found 1 tables matching list criteria.
GGSCI (edb1) 3> capture tabledef "public"."ggtest"
Table definitions for public.ggtest:
col1 NUMBER (10) NOT NULL PK
col2 VARCHAR (20)
15.源端创建extract进程（测试使用不建立pump了）
GGSCI (test) 4> edit param epos
EXTRACT epos
USERID goldengate, PASSWORD goldengate
RMTHOST edb1, MGRPORT 7809
RMTTRAIL ./dirdat/ep
TABLE jp.ggtest;
GGSCI (test) 5> add extract epos, tranlog, begin now
EXTRACT added.
GGSCI (test) 6> add exttrail ./dirdat/ep, extract epos, megabytes 5
EXTTRAIL added.
GGSCI (test) 7> start epos
Sending START request to MANAGER ...
EXTRACT EPOS starting
GGSCI (test) 8> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EPOS 00:00:00 00:00:05
16.源端创建DEFINITIONS文件
GGSCI (test) 10> edit param defgen
DEFSFILE ./dirdef/GGTEST.def
USERID goldengate, password goldengate
TABLE JP.GGTEST;
[oracle@test ggs]$ ./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 2015-12-17 16:48:09
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Wed May 26 10:38:10 EDT 2010, Release 2.6.18-194.3.1.0.2.el5
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: 16880
***********************************************************************
** Running with the following parameters **
***********************************************************************
DEFSFILE ./dirdef/GGTEST.def
USERID goldengate, password ********
TABLE JP.GGTEST;
Retrieving definition for JP.GGTEST
Definitions generated for 1 table in ./dirdef/GGTEST.def
Content of the Defgen File:
[oracle@test ggs]$ more ./dirdef/GGTEST.def
*+- Defgen version 2.0, Encoding UTF-8
*
* Definitions created/modified 2015-12-17 16:48
*
* Field descriptions for each column entry:
*
* 1 Name
* 2 Data Type
* 3 External Length
* 4 Fetch Offset
* 5 Scale
* 6 Level
* 7 Null
* 8 Bump if Odd
* 9 Internal Length
* 10 Binary Length
* 11 Table Length
* 12 Most Significant DT
* 13 Least Significant DT
* 14 High Precision
* 15 Low Precision
* 16 Elementary Item
* 17 Occurs
* 18 Key Column
* 19 Sub Data Type
*
Database type: ORACLE
Character set ID: UTF-8
National character set ID: UTF-16
Locale: neutral
Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14
*
Definition for table JP.GGTEST
Record length: 262
Syskey: 0
Columns: 2
COL1 64 50 0 0 0 1 0 50 50 50 0 0 0 0 1 0 1 2
COL2 64 200 56 0 0 1 0 200 200 0 0 0 0 0 1 0 0 0
End of definition
之后通过scp将def文件传输到目标端
scp ./dirdef/GGTEST.def edb1:/home/oracle/ggs/dirdef/GGTEST.def
17.目标端配置Replicat进程
GGSCI (edb1) 1> edit param rpos
REPLICAT rpos
SOURCEDEFS ./dirdef/GGTEST.def
SETENV ( PGCLIENTENCODING = "UTF8" )
SETENV (ODBCINI="/home/oracle/ggs/odbc.ini" )
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
TARGETDB GG_Postgres, USERID badly9, PASSWORD badly9
DISCARDFILE ./dirrpt/diskg.dsc, purge
MAP JP.GGTEST, TARGET public.ggtest, COLMAP (COL1=col1,COL2=col2);
PGCLIENTENCODING 指的是PG的字符集
GGSCI (edb1) 2> add replicat rpos, NODBCHECKPOINT, exttrail ./dirdat/ep
REPLICAT added.
GGSCI (edb1) 3> start rpos
Sending START request to MANAGER ...
REPLICAT REPKG starting
GGSCI edb1) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RPOS 00:00:00 00:00:07
18.测试同步
Oracle
SQL> insert into ggtest values(1,'test');
1 row created.
SQL> commit;
Commit complete.
Postgres
[pg@edb1 ~]$ psql -d badly9db -h 192.168.70.10 -p 5432 -U badly9 -W
Password for user badly9:
psql (9.3.9)
Type "help" for help.
badly9db=> select count(*) from public.ggtest;
count
-------
1
(1 row)
badly9db=> select * from public.ggtest;
col1 | col2
------+------
1 | test
(1 row)
同步成功