前一阵子做的从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