Oracle 实时复制到 PostgreSQL或EnterpriseDB-阿里云开发者社区

开发者社区> 阿里云数据库> 正文

Oracle 实时复制到 PostgreSQL或EnterpriseDB

简介:

Oracle GoldenGate支持种类繁多的数据库的同步,功能非常强大。
从mysql, oracle同步到PostgreSQL的又一利器。

开源的从mysql,oracle同步到PostgreSQL软件举例:
https://github.com/EnterpriseDB/mysql_fdw
http://pgxn.org/dist/oracle_fdw/
https://github.com/vmware/tungsten-replicator
http://pubs.vmware.com/continuent/tungsten-replicator-4.0/index.html

GoldenGate主页
http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

Oracle GoldenGate Studio 12.2.1.0.0  
DownloadOracle GoldenGate Studio V12.2.1.0.0 - 1 of 2 (1.9 GB)   
DownloadOracle GoldenGate Studio V12.2.1.0.0 - 2 of 2 (8.6 MB)   
  
Oracle GoldenGate 12.2.0.1.1  
DownloadOracle GoldenGate V12.2.0.1.1 for Oracle on Linux x86-64 (454 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for Oracle on Solaris Sparc (64 bit) (237 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for Oracle on Solaris x86-64 (64 bit) (215 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for Oracle on Windows (64 bit) (213 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for Oracle on HPUX IA64 (407 MB)  
DownloadOracle GoldenGate V12.2.0.1.1 for Oracle on AIX (232 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for DB2 9.7 on AIX (68 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for DB2 10.1,10.5 on AIX (68 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for DB2 9.7 on Linux x86-64 (189 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for DB2 10.1,10.5 on Linux x86-64 (189 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for DB2 9.7 on Solaris Sparc (64 bit) (79 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for DB2 10.1,10.5 on Solaris Sparc (64 bit) (79 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for DB2 9.7 on Solaris x86-64 (70 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for DB2 10.1,10.5 on Solaris x86-64 (70 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for DB2 9.7 on Windows (64 bit) (57 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for DB2 10.1,10.5 on Windows (64 bit) (57 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for MySQL on Linux x86-64 (190 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for MySQL on Solaris Sparc (64 bit) (99 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for MySQL on Solaris x86-64 (64 bit) (90 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for MySQL on Windows (64 bit) (59 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for SQL Server on Windows (64 bit) (58 MB)  
DownloadOracle GoldenGate V12.2.0.1.1 for PostgreSQL on Linux x86-64 (207 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for Informix on Linux x86-64 (192 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for Informix on Solaris Sparc 64 bit (80 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for Informix on AIX (69 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for Informix on Windows (64 bit) (57 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for SQL/MX on HP NonStop Itanium (OSS: H06 or J06) (285 MB)  
DownloadOracle GoldenGate V12.2.0.1.1 for Sybase on AIX (69 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for Sybase on Linux x86-64 (192 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for Sybase on Solaris Sparc (64 bit) (64 bit) (80 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for Sybase on Solaris x86-64 (64 bit)(64 bit) (71 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for Sybase on Windows (64 bit) (57 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for Teradata on AIX (68 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for Teradata on Linux x86-64 (190 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for Teradata on Solaris Sparc (64 bit) (80 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for Teradata on Solaris x86-64 (71 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for Teradata on Windows (64 bit) (57 MB)  
DownloadOracle GoldenGate V12.2.0.1.1 for TimesTen on Linux x86-64 (186 MB)   
DownloadOracle GoldenGate V12.2.0.1.1 for TimesTen on Windows (64bit) (57 MB)  

本文内容主要参考自
http://blog.csdn.net/staricqxyz/article/details/11096203

以Oracle为源端,PostgreSQL为目标端为例。
在源端和目标端都需要部署GoldenGate的软件。

GoldenGate for Oracle, 使用oci连接数据库
GoldenGate for PostgreSQL, 使用ODBC连接数据库(没有使用libpq是个遗憾啊)

数据流并不是直接入库,而是写入目标端goldengate接收到一个本地文件,然后再解析本地文件,写入目标端数据库。

说明
GGSCI (ggos) 表示Oracle端的goldengate
GGSCI (ggpgt) 表示PostgreSQL端的goldengate

Oracle端部署,假设数据库已经安装好了。
确保Oracle处于归档打开的状态。
Installing and setting up Oracle GoldenGate connecting to an Oracle database
Also please make sure the Oracle database is in archive log mode

[oracle@ggos ~]$ sqlplus / as sysdba    
    
SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 4 21:00:12 2013    
    
Copyright (c) 1982, 2013, Oracle.  All rights reserved.    
    
    
Connected to:    
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production    
With the Partitioning, OLAP, Data Mining and Real Application Testing options    
    
SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/arch';    
    
System altered.    
    
SQL> shutdown immediate    
Database closed.    
Database dismounted.    
ORACLE instance shut down.    
SQL> startup mount    
ORACLE instance started.    
    
Total System Global Area  835104768 bytes    
Fixed Size                  2257840 bytes    
Variable Size             717229136 bytes    
Database Buffers          113246208 bytes    
Redo Buffers                2371584 bytes    
Database mounted.    
SQL> alter database archivelog;    
    
Database altered.    
    
SQL> alter database open;    
    
Database altered.    
    
SQL> archive log list    
Database log mode              Archive Mode    
Automatic archival             Enabled    
Archive destination            /u01/app/oracle/oradata/arch    
Oldest online log sequence     3    
Next log sequence to archive   5    
Current log sequence           5    
SQL>     
  
In addition you should enable minimum supplemental logging  
code      
SQL> alter database add supplemental log data;    
    
Database altered.    
code      
SQL> alter database force logging;    
    
Database altered.    
code      
SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;    
    
FOR SUPPLEME    
--- --------    
YES YES    

将goldengate加入环境变量
Add the GoldenGate directory to your LD_LIBRARY_PATH and PATH

export PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/u01/app/oracle/ggs    
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/oracle/ggs/lib    

打开goldengate命令行接口,创建必要的目录
The first step is to open the GoldenGate command line interface and to create the necessary subdirectories

[oracle@ggos ggs]$ ./ggsci     
    
Oracle GoldenGate Command Interpreter for Oracle    
Version 11.2.1.0.7 16934304 OGGCORE_11.2.1.0.7_PLATFORMS_130709.1600.1_FBO    
Linux, x64, 64bit (optimized), Oracle 11g on Jul 18 2013 07:04:28    
    
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.    
    
    
    
GGSCI (ggos) 1> create subdirs    
    
Creating subdirectories under current directory /u01/app/oracle/ggs    
    
Parameter files                /u01/app/oracle/ggs/dirprm: already exists    
Report files                   /u01/app/oracle/ggs/dirrpt: created    
Checkpoint files               /u01/app/oracle/ggs/dirchk: created    
Process status files           /u01/app/oracle/ggs/dirpcs: created    
SQL script files               /u01/app/oracle/ggs/dirsql: created    
Database definitions files     /u01/app/oracle/ggs/dirdef: created    
Extract data files             /u01/app/oracle/ggs/dirdat: created    
Temporary files                /u01/app/oracle/ggs/dirtmp: created    
Stdout files                   /u01/app/oracle/ggs/dirout: created    
    
    
GGSCI (ggos) 2>     

继续在goldengate命令行中操作,创建manager的参数文件,只是需要配置一个端口
The second step is to create a parameter file for the manager which at least contains a PORT number for the manager

GGSCI (ggos) 3> edit param mgr    

add the following content to the parameter file

GGSCI (ggos) 2> view param mgr      
    
PORT 7809    

保存参数文件,启动manager
Save the parameter file, exit from the editor, start the manager and verify if it is running

GGSCI (ggos) 1> start mgr    
    
Manager started.    
    
    
GGSCI (ggos) 2> info all    
    
Program     Status      Group       Lag at Chkpt  Time Since Chkpt    
    
MANAGER     RUNNING                                               
    
    
GGSCI (ggos) 3> info mgr    
    
Manager is running (IP port ggos.7809).    

PostgreSQL端部署,假设PostgreSQL已安装。
Installing and setting up Oracle GoldenGate on the PostgreSQL machine
Then add the lib directory to the LD_LIBRARY_PATH and PATH

export LD_LIBRARY_PATH=/data/pgsql/lib:/data/pgsql/ggs/lib    
export PATH=/data/pgsql/bin:/data/pgsql/ggs:$PATH    

ODBC驱动已打包在GoldenGate安装包,但是需要自己创建odbc.ini配置文件。
配置文件内容举例,
GoldenGate uses an ODBC connection to connect to the Postgres database.
The ODBC driver is shipped with the installation and on Unix you have to create the ODBC configuration file which is commonly called odbc.ini on your own
Here the file I used

[postgres@ggpgt ggs]$ pwd    
/data/pgsql/ggs    
[postgres@ggpgt ggs]$ cat odbc.ini     
[ODBC Data Sources]    
GG_Postgres=DataDirect 6.1 PostgreSQL Wire Protocol    
[ODBC]    
IANAAppCodePage=106    
InstallDir=/data/pgsql/ggs    
[GG_Postgres]    
Driver=/data/pgsql/ggs/lib/GGpsql25.so    
Description=DataDirect 6.1 PostgreSQL Wire Protocol    
Database=repdb    
HostName=ggpgt    
PortNumber=1521    
LogonID=zwc    
Password=zwc    

配置ODBC环境变量
Finally we need to export an ODBC environment variable which is called ODBCINI and points to the odbc.ini file we just created

export ODBCINI=/data/pgsql/ggs/odbc.ini    

配置goldengate,包括创建必要的目录,配置manager端口,启动manager。
We've prepared the ODBC set up, now let's start with the GoldenGate set up.
Similar to the installation on the Oracle database host we first create the GoldenGate subdirectories

[postgres@ggpgt ggs]$ ./ggsci     
    
Oracle GoldenGate Command Interpreter    
Version 11.2.1.0.2 OGGCORE_11.2.1.0.2T3_PLATFORMS_120724.2205    
Linux, x64, 64bit (optimized), PostgreSQL on Jul 25 2012 00:32:24    
    
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.    
    
    
    
GGSCI (ggpgt) 1> create subdirs    
    
Creating subdirectories under current directory /data/pgsql/ggs    
    
Parameter files                /data/pgsql/ggs/dirprm: already exists    
Report files                   /data/pgsql/ggs/dirrpt: created    
Checkpoint files               /data/pgsql/ggs/dirchk: created    
Process status files           /data/pgsql/ggs/dirpcs: created    
SQL script files               /data/pgsql/ggs/dirsql: created    
Database definitions files     /data/pgsql/ggs/dirdef: created    
Extract data files             /data/pgsql/ggs/dirdat: created    
Temporary files                /data/pgsql/ggs/dirtmp: created    
Stdout files                   /data/pgsql/ggs/dirout: created    

create the Manager parameter file and start the manager

GGSCI (ggpgt) 3> view param mgr       
    
PORT 7809    
  
Once we created the parameter file we can start the manager and check its status  
code      
GGSCI (ggpgt) 9> start mgr    
    
Manager started.    
    
    
GGSCI (ggpgt) 10> info all    
    
Program     Status      Group       Lag at Chkpt  Time Since Chkpt    
    
MANAGER     RUNNING                                               
    
    
GGSCI (ggpgt) 11> info mgr    
    
Manager is running (IP port ggpgt.7809).    

在Oracle数据库中创建一张测试源表,需要主键
本文不包括全量复制的内容,有需要的同学请参考Goldengate手册。
Demo table in Oracle and PostgreSQL
As mentioned, it is a basic set up without using initial load nor a data pump process.
So we only create a simple table in Oracle and Postgres to replicate data. My Oracle database has a user called postgres and this user will now own a table ggtest

[oracle@ggos ~]$ sqlplus zwc    
    
SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 4 21:40:03 2013    
    
Copyright (c) 1982, 2013, Oracle.  All rights reserved.    
    
Enter password:     
    
Connected to:    
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production    
With the Partitioning, OLAP, Data Mining and Real Application Testing options    
    
SQL> create table ggtest(col1 number,col2 varchar2(50));    
    
Table created.    
    
SQL> alter table ggtest add primary key(col1);    
    
Table altered.    

创建PostgreSQL目标表,需要主键
Then you can connect with a Postgres utility to the Postgres database and create a similar table in the public schema

[postgres@ggpgt ~]$ psql -U zwc -d repdb -h ggpgt    
Password for user zwc:     
psql (9.2.4)    
Type "help" for help.    
    
repdb=> create table "public"."ggtest"    
repdb-> (    
repdb(> "col1" integer NOT NULL,    
repdb(>  "col2" varchar(20),    
repdb(> CONSTRAINT "PK_Col111" PRIMARY KEY ("col1")    
repdb(> );    
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "PK_Col111" for table "ggtest"    
CREATE TABLE    
repdb-> \d+ ggtest    
                               Table "public.ggtest"    
 Column |         Type          | Modifiers | Storage  | Stats target | Description     
--------+-----------------------+-----------+----------+--------------+-------------    
 col1   | integer               | not null  | plain    |              |     
 col2   | character varying(20) |           | extended |              |     
Indexes:    
    "PK_Col111" PRIMARY KEY, btree (col1)    
Has OIDs: no    

检查goldengate到Oracle的连通性,设置捕获表。
Verify Oracle DB connection
To check the connection to the Oracle database we can use the GoldenGate command interface, log into the Oracle db, list the tables we can capture and check their data types

[oracle@ggos ggs]$ ./ggsci    
    
Oracle GoldenGate Command Interpreter for Oracle    
Version 11.2.1.0.7 16934304 OGGCORE_11.2.1.0.7_PLATFORMS_130709.1600.1_FBO    
Linux, x64, 64bit (optimized), Oracle 11g on Jul 18 2013 07:04:28    
    
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.    
    
    
    
GGSCI (ggos) 1> dblogin userid zwc, password zwc    
Successfully logged into database.    
    
GGSCI (ggos) 2> list tables *    
ZWC.GGTEST    
    
Found 1 tables matching list criteria.    
    
GGSCI (ggos) 3> capture tabledef zwc.ggtest    
Table definitions for ZWC.GGTEST:    
COL1                           NUMBER NOT NULL PK    
COL2                           VARCHAR (50)    

检查goldengate到PostgreSQL的连通性,查看目标表。
Verify PostgreSQL ODBC connection
To check the ODBC connection to the Postgres database we also use the GoldenGate command line tool

[postgres@ggpgt ggs]$ ./ggsci     
    
Oracle GoldenGate Command Interpreter    
Version 11.2.1.0.2 OGGCORE_11.2.1.0.2T3_PLATFORMS_120724.2205    
Linux, x64, 64bit (optimized), PostgreSQL on Jul 25 2012 00:32:24    
    
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.    
    
    
    
GGSCI (ggpgt) 1> dblogin sourcedb gg_postgres userid zwc    
Password:     
    
2013-09-04 13:56:35  INFO    OGG-03036  Database character set identified as UTF-8. Locale: en_US.    
    
2013-09-04 13:56:35  INFO    OGG-03037  Session character set identified as UTF-8.    
Successfully logged into database.    
    
GGSCI (ggpgt) 2> list tables *       
public.ggtest    
public.t    
    
Found 2 tables matching list criteria.    
    
GGSCI (ggpgt) 3> capture tabledef "public"."ggtest"    
Table definitions for public.ggtest:    
col1                                                                                                                             NUMBER (10) NOT NULL PK    
col2                                                                                                                             VARCHAR (20)    

在Oracle端的goldengate配置抽取进程的参数,允许多个抽取进程,每个抽取进程都需要对应一个配置文件。
例子中的epos是指一个名为epos的文件。
GoldenGate extract process
In the following section we create an extract process that captures the changes for the GGTEST table in the Oracle database and copies the changes directly to the Postgres machine
Every process needs it config file, so let's create it for the extract process

GGSCI (ggos) 1> edit param epos    
  
epos配置文件的配置如下,注意到需要配置远程主机名,远程的TRAIL文件前缀/data/pgsql/ggs/dirdat/ep,捕获的表名:  
with these parameters  
code      
GGSCI (ggos) 3> view param epos    
    
EXTRACT epos    
SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")    
SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1")    
SETENV (ORACLE_SID="oraprod")    
USERID zwc, PASSWORD zwc    
RMTHOST ggpgt, MGRPORT 7809    
RMTTRAIL /data/pgsql/ggs/dirdat/ep    
TABLE zwc.ggtest;    

添加extrace, exttrail,并启动抽取进程。
My extract process is called epos and it connects as user ZWC using the password zwc to the Oracle database.
It will extract changes on the Oracle table ggtest stored in the zws schema and will put the information into a trail file on my Postgres machine.
Once we created the parameter file we can add the extract process and start it

GGSCI (ggos) 4> add extract epos, tranlog, begin now    
EXTRACT added.    
    
    
GGSCI (ggos) 5> add exttrail /data/pgsql/ggs/dirdat/ep, extract epos, megabytes 5    
EXTTRAIL added.    
    
    
GGSCI (ggos) 6> start epos    
    
Sending START request to MANAGER ...    
EXTRACT EPOS starting    
    
    
GGSCI (ggos) 7> info all    
    
Program     Status      Group       Lag at Chkpt  Time Since Chkpt    
    
MANAGER     RUNNING                                               
EXTRACT     RUNNING     EPOS        00:00:00      00:00:00        
  
code      
GGSCI (ggos) 12> info extract epos    
    
EXTRACT    EPOS      Last Started 2013-09-04 22:07   Status RUNNING    
Checkpoint Lag       00:00:00 (updated 00:00:00 ago)    
Log Read Checkpoint  Oracle Redo Logs    
                     2013-09-04 22:07:58  Seqno 7, RBA 18612736    
                     SCN 0.1040942 (1040942)    

因为是异构复制,目标端必须有一个源端生成的定义文件来告诉GG该如何转换捕获的数据。
创建定义文件
Create DEFINITIONS File
We're replicating data in a heterogeneous environment, so we need to give the process loading the data into the Postgres database more details about the data in the extract file.
This is done by creating a definitions file using defgen. As usual we have to create a parameter file

GGSCI (ggos) 17> edit param defgen    
  
GGSCI (ggos) 17> view param defgen    
    
DEFSFILE /u01/app/oracle/ggs/dirdef/GGTEST.def    
USERID zwc, password zwc    
TABLE ZWC.GGTEST;    

调用defgen程序,生成defgen.prm参数文件。
Now exit from ggsci and call defgen on the command line and add the reference to the defgen parameter file just created

[oracle@ggos ggs]$ ./defgen paramfile ./dirprm/defgen.prm    
    
    
***********************************************************************    
        Oracle GoldenGate Table Definition Generator for Oracle    
 Version 11.2.1.0.7 16934304 OGGCORE_11.2.1.0.7_PLATFORMS_130709.1600.1    
   Linux, x64, 64bit (optimized), Oracle 11g on Jul 18 2013 04:10:02    
     
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.    
    
    
    
    
                    Starting at 2013-09-04 22:24:21    
***********************************************************************    
    
    
Operating System Version:    
Linux    
Version #1 SMP Wed Feb 22 17:37:40 EST 2012, Release 2.6.32-300.10.1.el5uek    
Node: ggos    
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: 29365    
    
    
***********************************************************************    
**            Running with the following parameters                  **    
***********************************************************************    
DEFSFILE /u01/app/oracle/ggs/dirdef/GGTEST.def    
USERID zwc, password ***    
TABLE ZWC.GGTEST;    
Retrieving definition for ZWC.GGTEST    
    

读取上一步生成好的定义文件。
Definitions generated for 1 table in /u01/app/oracle/ggs/dirdef/GGTEST.def

[oracle@ggos ggs]$ cat dirdef/GGTEST.def     
*+- Defgen version 2.0, Encoding UTF-8    
*    
* Definitions created/modified  2013-09-04 22:24    
*    
*  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: windows-936    
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 ZWC.GGTEST    
Record length: 112    
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     50       56  0  0 1 0     50     50      0 0 0 0 0 1    0 0 0    
End of definition    

将定义文件的内容拷贝到目标端,同一个相对目录下面。
Copy DEFGEN File
As the replicat process needs details about the source database, we copy the generated definitions file located on our Oracle machine in the /dirdef/GGTEST.def to the target machine where the Postgres database is installed into the ./dirdef/GGTEST.def directory.

[oracle@ggos ggs]$ scp dirdef/GGTEST.def postgres@ggpgt:/data/pgsql/ggs/dirdef    
The authenticity of host 'ggpgt (192.168.1.235)' can't be established.    
RSA key fingerprint is a2:87:27:d3:58:63:76:72:ab:d3:84:8e:43:fc:d7:be.    
Are you sure you want to continue connecting (yes/no)? yes    
Warning: Permanently added 'ggpgt,192.168.1.235' (RSA) to the list of known hosts.    
postgres@ggpgt's password:     
GGTEST.def                                                                               100%  996     1.0KB/s   00:00        
[oracle@ggos ggs]$     

在PostgreSQL端,设置复制进程参数文件,每个复制进程必须对应一个参数文件。
PostgreSQL replicat
Our extract process is set up to write all changes for the table GGTEST directly to the Postgres database machine into an extract file located in the dirdat directory.
So we only need a process that reads those changes from the trail file and distributes it to the Postgres database.
This process is called a replicat process running on the Postgres machine and it needs a parameter file

GGSCI (ggpgt) 6> edit param rpos    
  
GGSCI (ggpgt) 6> view param rpos    
    
REPLICAT rpos    
SOURCEDEFS /data/pgsql/ggs/dirdef/GGTEST.def    
SETENV ( PGCLIENTENCODING = "UTF8" )    
SETENV (ODBCINI="/data/pgsql/ggs/odbc.ini" )    
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")    
TARGETDB GG_Postgres, USERID zwc, PASSWORD zwc    
DISCARDFILE /data/pgsql/ggs/dirrpt/diskg.dsc, purge    
MAP ZWC.GGTEST, TARGET public.ggtest, COLMAP (COL1=col1,COL2=col2);    

启动复制进程
Create the replicat process, start it and verify if it is running

GGSCI (ggpgt) 8> add replicat rpos, NODBCHECKPOINT, exttrail /data/pgsql/ggs/dirdat/ep    
REPLICAT added.    
    
    
GGSCI (ggpgt) 9> start rpos    
    
Sending START request to MANAGER ...    
REPLICAT RPOS starting    
    
    
GGSCI (ggpgt) 10> info all    
    
Program     Status      Group       Lag at Chkpt  Time Since Chkpt    
    
MANAGER     RUNNING                                               
REPLICAT    RUNNING     RPOS        00:00:00      00:00:00        
    
    
GGSCI (ggpgt) 11> info all    
    
Program     Status      Group       Lag at Chkpt  Time Since Chkpt    
    
MANAGER     RUNNING                                               
REPLICAT    RUNNING     RPOS        00:00:00      00:00:02        
    
    
GGSCI (ggpgt) 12> view report rpos    
    
    
***********************************************************************    
                      Oracle GoldenGate Delivery    
     Version 11.2.1.0.2 OGGCORE_11.2.1.0.2T3_PLATFORMS_120724.2205    
   Linux, x64, 64bit (optimized), PostgreSQL on Jul 25 2012 01:37:05    
     
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.    
    
    
                    Starting at 2013-09-04 14:34:27    
***********************************************************************    
    
Operating System Version:    
Linux    
Version #1 SMP Fri Jul 22 04:43:29 EDT 2011, Release 2.6.18-274.el5    
Node: ggpgt    
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: 22305    
    
Description:     
    
***********************************************************************    
**            Running with the following parameters                  **    
***********************************************************************    
    
2013-09-04 14:34:27  INFO    OGG-03035  Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.    
REPLICAT rpos    
SOURCEDEFS /data/pgsql/ggs/dirdef/GGTEST.def    
SETENV ( PGCLIENTENCODING = "UTF8" )    
Set environment variable (PGCLIENTENCODING=UTF8)    
SETENV (ODBCINI="/data/pgsql/ggs/odbc.ini" )    
Set environment variable (ODBCINI=/data/pgsql/ggs/odbc.ini)    
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")    
Set environment variable (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)    
TARGETDB GG_Postgres, USERID zwc, PASSWORD ***    
    
2013-09-04 14:34:27  INFO    OGG-03036  Database character set identified as UTF-8. Locale: en_US.    
    
2013-09-04 14:34:27  INFO    OGG-03037  Session character set identified as UTF-8.    
DISCARDFILE /data/pgsql/ggs/dirrpt/diskg.dsc, purge    
MAP ZWC.GGTEST, TARGET public.ggtest, COLMAP (COL1=col1,COL2=col2);    
    
2013-09-04 14:34:27  INFO    OGG-01815  Virtual Memory Facilities for: COM    
    anon alloc: mmap(MAP_ANON)  anon free: munmap    
    file alloc: mmap(MAP_SHARED)  file free: munmap    
    target directories:    
    /data/pgsql/ggs/dirtmp.    
    
CACHEMGR virtual memory values (may have been adjusted)    
CACHESIZE:                                2G    
CACHEPAGEOUTSIZE (normal):                8M    
PROCESS VM AVAIL FROM OS (min):           4G    
CACHESIZEMAX (strict force to disk):   3.41G    
    
Database Version:    
PostgreSQL    
Version 09.02.0004    
ODBC Version 03.52.0000    
    
Driver Information:    
GGpsql25.so    
Version 06.10.0068 (B0059, U0030)    
ODBC Version 03.52    
    
***********************************************************************    
**                     Run Time Messages                             **    
***********************************************************************    
    
Opened trail file /data/pgsql/ggs/dirdat/ep000000 at 2013-09-04 14:34:27    

测试复制是否生效,在源端插入记录
Testing the configuration
Finally we can now test the whole set up and insert a record into the Oracle database

[oracle@ggos ~]$ sqlplus zwc    
    
SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 4 22:38:16 2013    
    
Copyright (c) 1982, 2013, Oracle.  All rights reserved.    
    
Enter password:     
    
Connected to:    
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production    
With the Partitioning, OLAP, Data Mining and Real Application Testing options    
    
SQL> insert into ggtest values(1,'Hi Zhong Weicheng');    
    
1 row created.    
    
SQL> commit;    
    
Commit complete.    
    
SQL> select * from ggtest;    
    
      COL1 COL2    
---------- --------------------------------------------------    
         1 Hi Zhong Weicheng    

查看目标端是否已接收到这条记录。
Check with a PostgreSQL tool if our newly inserted record is replicated

[postgres@ggpgt ggs]$ psql -U zwc -d repdb    
psql (9.2.4)    
Type "help" for help.    
    
repdb=> select * from ggtest;    
 col1 |       col2            
------+-------------------    
    1 | Hi Zhong Weicheng    
(1 row)    
    
repdb=>     

使用logdump可以DUMP trail文件,检查其内容。
Check with GoldenGate tool logdump

[postgres@ggpgt ggs]$ ./logdump     
    
Oracle GoldenGate Log File Dump Utility    
Version 11.2.1.0.2 OGGCORE_11.2.1.0.2T3_PLATFORMS_120724.2205    
    
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.    
    
    
     
Logdump 1 >open /data/pgsql/ggs/dirdat/ep000000    
Current LogTrail is /data/pgsql/ggs/dirdat/ep000000     
Logdump 2 >ghdr ON    
Logdump 3 >headertoken on        
Logdump 4 >usertoken on    
Logdump 5 >detail on    
Logdump 6 >detail data    
Logdump 7 >n    
TokenID x46 'F' Record Header    Info xff80  Length 1050     
TokenID x30 '0' TrailInfo        Info x00  Length  429     
TokenID x31 '1' MachineInfo      Info x00  Length  102     
TokenID x32 '2' DatabaseInfo     Info x00  Length  357     
TokenID x33 '3' ProducerInfo     Info x00  Length  126     
TokenID x34 '4' ContinunityInfo  Info x00  Length    8     
TokenID x5a 'Z' Record Trailer   Info xff80  Length 1050     
    
2013/09/04 22:07:34.254.624 FileHeader           Len  1042 RBA 0     
Name: *FileHeader*     
 3000 01ad 3000 0008 4747 0d0a 544c 0a0d 3100 0002 | 0...0...GG..TL..1...      
 0003 3200 0004 2000 0000 3300 0008 02f2 0bc2 c871 | ..2... ...3........q      
 2820 3400 0023 0021 7572 693a 6767 6f73 3a3a 7530 | ( 4..#.!uri:ggos::u0      
 313a 6170 703a 6f72 6163 6c65 3a67 6773 3a45 504f | 1:app:oracle:ggs:EPO      
 5336 0000 2100 1f2f 6461 7461 2f70 6773 716c 2f67 | S6..!../data/pgsql/g      
 6773 2f64 6972 6461 742f 6570 3030 3030 3030 3700 | gs/dirdat/ep0000007.      
 0001 0138 0000 0400 0000 0039 ff00 0800 0000 0000 | ...8.......9........      
     
Logdump 8 >n    
TokenID x47 'G' Record Header    Info x01  Length  145     
TokenID x48 'H' GHDR             Info x00  Length   45     
TokenID x44 'D' Data             Info x00  Length   34     
TokenID x54 'T' GGS Tokens       Info x00  Length   46     
TokenID x5a 'Z' Record Trailer   Info x01  Length  145     
___________________________________________________________________     
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)      
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)      
RecLength  :    34  (x0022)   IO Time    : 2013/09/04 14:38:19.020.741       
IOType     :     5  (x05)     OrigNode   :   255  (xff)     
TransInd   :     .  (x03)     FormatType :     R  (x52)     
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)     
AuditRBA   :          7       AuditPos   : 18997776     
Continued  :     N  (x00)     RecCount   :     1  (x01)     
    
2013/09/04 14:38:19.020.741 Insert               Len    34 RBA 1050     
Name: ZWC.GGTEST     
After  Image:                                             Partition 4   G  s       
 0000 0005 0000 0001 3100 0100 1500 0000 1148 6920 | ........1........Hi       
 5a68 6f6e 6720 5765 6963 6865 6e67                | Zhong Weicheng      
Column     0 (x0000), Len     5 (x0005)      
 0000 0001 31                                      | ....1      
Column     1 (x0001), Len    21 (x0015)      
 0000 0011 4869 205a 686f 6e67 2057 6569 6368 656e | ....Hi Zhong Weichen      
 67                                                | g      

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
阿里云数据库
使用钉钉扫一扫加入圈子
+ 订阅

帮用户承担一切数据库风险,给您何止是安心!

官方博客
链接