合 How To Replicate Data from Oracle to Postgres Using GoldenGate (Doc ID 1544137.1)
- APPLIES TO:
- GOAL
- SOLUTION
- 0. Installing and setting up Oracle GoldenGate connecting to an Oracle database
- 1. Installing and setting up Oracle GoldenGate on the Postgres machine
- 2. Demo table in Oracle and Postgres
- 3. Verify Oracle DB connection
- 4. Verify Postgres ODBC connection
- 5. GoldenGate extract process
- 6. Create DEFINITIONS File
- 7. Copy DEFGEN File
- 8. Postgres replicat
- 9. Testing the configuration
- REFERENCES
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:
1 2 3 | ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH |
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:
1 2 3 4 5 | 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; |
In addition you should enable minimum supplemental logging:
1 | alter database add supplemental log data; |
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:
1 2 | export PATH=$PATH:/home/oracle/ggs export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/home/oracle/ggs/lib |
The first step is to open the GoldenGate command line interface and to create the necessary subdirectories:
1 2 3 4 5 6 7 8 9 10 11 12 13 | GGSCI (SOURCE.ORACLE.COM) 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 |
The second step is to create a parameter file for the manager which at least contains a PORT number for the manager:
1 2 3 4 5 | GGSCI (SOURCE.ORACLE.COM) 2> edit param mgr add the following content to the parameter file: PORT 7809 |
Save the parameter file, exit from the editor, start the manager and verify if it is running:
1 2 3 4 5 6 7 | GGSCI (SOURCE.ORACLE.COM) 3> start mgr GGSCI (SOURCE.ORACLE.COM) 4> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER 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.
1 2 3 4 5 | mkdir ggs cd ggs unzip V34006-01.zip tar xvf *.tar [oracle@TARGET ggs]$ export LD_LIBRARY_PATH=/home/oracle/ggs/lib |
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: