How To Replicate Data from Oracle to Postgres Using GoldenGate (Doc ID 1544137.1)

APPLIES TO:

Oracle GoldenGate - Version 11.2.1.0.0 and later
Information in this document applies to any platform.

GOAL

Starting with Oracle GoldenGate 11.2.1.0.2 data can now be replicated between Oracle and Postgres. This note contains a basic setup how to replicate data between Oracle and Postgres which can be easily modified to fit your needs.

SOLUTION

To replicate data between an Oracle and a PostgreSQL database a GoldenGate installation for both databases is required. In this example the host where the Oracle database set up is done is called SOURCE and the machine with the PostgreSQL database is called TARGET. In addition to keep it simple no data pump process is configured. Instead the extract process writes the data to the target machine and the replicat process reads the extract file directly.

0. Installing and setting up Oracle GoldenGate connecting to an Oracle database

Before you install the GoldenGate software please make sure the following environment variables are set and point to your Oracle database installation:

Also please make sure the Oracle database is in archive log mode. If the source database is not in archivelog mode, enable it using the steps below:

In addition you should enable minimum supplemental logging:

Once the Oracle database is prepared you can start installing GoldenGate. When the software was downloaded from Oracle's software delivery cloud (https://edelivery.oracle.com) or from "My Oracle Support portal" please place the downloaded file into the directory you want to designate as GoldenGate home, unzip the file and inflate it using the tar command.

Now add the GoldenGate directory to your LD_LIBRARY_PATH and PATH:

The first step is to open the GoldenGate command line interface and to create the necessary subdirectories:

The second step is to create a parameter file for the manager which at least contains a PORT number for the manager:

Save the parameter file, exit from the editor, start the manager and verify if it is running:

1. Installing and setting up Oracle GoldenGate on the Postgres machine

The installation of GoldenGate on the Postgres machine is similar to the installation on the Oracle box. Get the download file and then unzip and untar it in a directory you want to use as GoldenGate Home directory. Then add the lib directory to the LD_LIBRARY_PATH.

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.

The odbc.ini file is similar to an address book for the odbc driver. It is located by default in the ODBC_HOME directory, but can be placed anywhere you like.

A side note how odbc works: The odbc driver (nothing else than a library) gets a request to connect to a server described in the odbc.ini file. The alias for the description of the server is called:
Data Source Name (=DSN).
Then the driver reads the information from the odbc.ini file according to the specified DSN and connects to the server.

The prerequisite for the odbc driver to connect to the server is the configured odbc.ini.
It is divided into 3 different sections:

[ODBC Data Source] is the section that contains all the available DSNs.
[] contains the different names of the DSNs and specifies the connect details.
[ODBC] is the general section for the odbc driver

There's an example in the GoldenGate Postgres installation guide:

Although most of the parameters are self explaining a few words to the odbc.ini file.

The section [ODBC Data Sources] contains in general a list of available data sources (which you can name as you want) and the sample from the manual has one data source called postgre. The configuration behind the data source postgre is found in the section [postgre].
It contains the hostname and port of the Postgres server, the Postgres database itself, the driver library being used and the user id and password of the remote database server (LogonID and password are not required, they can be specified also in the replicat parameter file).

The [ODBC] section contains general parameters like a code page specification.

As not everybody might be experienced configuring ODBC DSNs let's create an odbc.ini step by step. As we know we first define the [ODBC Data Sources] section with a DSn name of our choice, then create the [ODBC] section

[ODBC Data Sources]
=DataDirect 6.1 PostgreSQL Wire Protocol

[ODBC]

These are generic settings which you can simply copy/paste and where you have to replace with any name of your choice, for example GG_Postgres.

IANAAppCodePage=4

is being used for national language support. The value 4 represents the ISO-8859-1 character set, 106 a Unicode UTF8 character set. The setting should always reflect the character set of the Postgres database. More details about the setting are covered in another note (1543702.1)

A common way to determine which value you have to set IANAAppCodePage for is:

• Determine the code page of your database where the ODBC driver is connecting to.

• Determine the MIBenum value that corresponds to your database code page using this link http://www.iana.org/assignments/character-sets

On this web page, search for the name of your database code page. This name will be listed as an alias or the name of a character set and will have a MIBenum value associated with it and use the MIBenum as the value for IANAAppCodePage.

Here a table with common values:

InstallDir=/home/oracle/ggs

Make sure to change the path to the location of your GoldenGate installation directory.

[]

This will be the ODBC DSN (Data Source Name) similar to the Oracle tns name containing all necessary details to connect to the Postgres database. It's name should match the value in the [ODBC Data Sources] section. Next configuration parameter is the ODBC driver library. Just make sure to replace with your GoldenGate home directory:

Driver=/lib/GGpsql25.so

The parameter

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。