2 Preparing the System for Oracle GoldenGate

This chapter describes how to prepare your system to use Oracle GoldenGate and includes the following sections:

2.1 Understanding and Obtaining the Oracle GoldenGate Distribution

For complete information about how to obtain Oracle Fusion Middleware software, see "Understanding and Obtaining Product Distributions" in Planning an Installation of Oracle Fusion Middleware.

To download the Oracle WebLogic Server and Coherence software for development or evaluation, see the following location on the Oracle Technology Network (OTN):

http://www.oracle.com/technetwork/middleware/fusion-middleware/downloads/index.html">>http://www.oracle.com/technetwork/middleware/fusion-middleware/downloads/index.html

For more information about locating and downloading Oracle Fusion Middleware products, see the Oracle Fusion Middleware Download, Installation, and Configuration Readme Files on OTN.

To obtain Oracle GoldenGate follow these steps:

  1. Go to Oracle Technology Network.

  2. Find the Oracle GoldenGate 12c (12.2.0.1) release and download the ZIP file onto your system.

2.2 Configuring ODBC on UNIX and Linux

You configure ODBC on UNIX and Linux by:

2.2.1 Specifying the Path to the Database Libraries

To point the ODBC driver manager to the database libraries, create an odbc.ini file that contains the DSN information. This file can be stored anywhere on the system. You will be instructed to set the path to this file in Setting the Path to the ODBC Driver Library and Initialization File.

This file must contain an [ODBC Data Sources] section at the beginning, followed by a list of DSNs for this installation. Each DSN must be described in its own section, and the Driver entry within each DSN section is mandatory. The following is an example of the required entries.

[ODBC Data Sources]
postgre=DataDirect 6.1 PostgreSQL Wire Protocol

[ODBC]
IANAAppCodePage=4
InstallDir=/home/fin/fin13004/postgres/v11201_120402

[postgre]
Driver=/home/fin/fin13004/postgres/v11201_120402/lib/GGpsql25.so
Description=DataDirect 6.1 PostgreSQL Wire Protocol
Database=fin
HostName=12.345.6.789
PortNumber=5432
LogonID=postgres
Password=postgre

2.2.2 Setting the Path to the ODBC Driver Library and Initialization File

Set the paths to the following components that are used by Replicat:

  • Set the environment variable ODBCINI to the path of the odbc.ini file. This is the file you created in Specifying the Path to the Database Libraries that directs the ODBC driver where to find the database libraries.

  • Set the shared-library environment variable to the path of the lib sub-directory of the Oracle GoldenGate installation directory. This sub-directory is where the ODBC driver (libodbc.so) and the license files are stored and where Replicat looks for them.

To set the variables in Korn shell

PATH=<absolute path>:$PATH
export PATH <ODBCINI | shared libraries variable>=<absolute path>:
$<ODBCINI | shared libraries variable>
export <ODBCINI | shared libraries variable>

To set the variables in Bourne shell

export PATH=<absolute path>:$PATH
export <ODBCINI | shared libraries variable>=<absolute path>:
$<ODBCINI | shared libraries variable>

To set the variables in C shell

setenv PATH <absolute path>:$PATH
setenv <ODBCINI | shared libraries variable> <absolute path>:
   $<ODBCINI | shared libraries variable>

Where:

  • <absolute path> is the full path to the odbc.ini file or the full path to the Oracle GoldenGate lib subdirectory, depending on which variable you are setting.

  • <shared libraries variable> is one of the following:

    Platform Environment variable

    IBM AIX

    LIBPATH

    HP-UX

    SHLIB_PATH

    Sun Solaris

    HP Tru64 (OSF/1)

    LINUX

    LD_LIBRARY_PATH


Example:

export LD_LIBRARY_PATH=/ggs/lib:$LD_LIBRARY_PATH

Note:

To view the libraries that are required by an Oracle GoldenGate process, use the ldd <process> shell command before starting the process. This command also shows an error message for any that are missing.

2.3 Configuring ODBC on Windows

You configure ODBC on Windows by:

2.3.1 Updating the Windows Registry with the Driver Information

The path to the ODBC driver must be added to the ODBC.INI and ODBCINST.INI keys under HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ in the Windows Registry. Oracle GoldenGate automates this process.

  1. At the Windows command prompt, change directories to the Oracle GoldenGate installation directory.

  2. Run the install program with the following input:

    install adddrivers
    

2.3.2 Creating a Data Source Name (DSN)

The DSN stores information about how to connect to the database.

  1. On the Windows system, open the Control Panel folder.

  2. Open the Administrative Tools folder.

  3. Open Data Sources (ODBC). The ODBC Data Source Administrator dialog box is displayed.

  4. Select the System DSN tab, and then click Add.

  5. Under Create New Data Source, select the PostgreSQL driver.

  6. Click Finish. The Create a New Data Source wizard is displayed.

  7. Supply the following:

    • For Client DSN, type a name for the DSN of the client name, up to 32 alpha-numeric characters, excluding special keyboard characters except for the underscore and dash.

    • (Optional) For Description, type a description of this DSN.

    • For the rest of the fields in this dialog, supply the correct information for the PostgreSQL database and its host.

  8. Click OK to close the dialog.

  9. Repeat these steps on the other systems where Oracle GoldenGate will interact with PostgreSQL.

2.4 Specifying the DSN in the Replicat Parameter File

Specify the DSN with the TARGETDB parameter in the Replicat parameter file. For more information about this parameter, see the Oracle GoldenGate Windows and UNIX Reference Guide.

2.5 Preparing Tables for Processing

The following table attributes must be addressed in an Oracle GoldenGate environment.

2.5.1 Disabling Triggers and Cascade Constraints

Disable triggers, cascade delete constraints, and cascade update constraints on the target tables, or alter them to ignore changes made by the Oracle GoldenGate database user. Oracle GoldenGate replicates DML that results from a trigger or cascade constraint. If the same trigger or constraint gets activated on the target table, it becomes redundant because of the replicated version, and the database returns an error. Consider the following example, where the source tables are "emp_src" and "salary_src" and the target tables are "emp_targ" and "salary_targ."

  1. A delete is issued for emp_src.

  2. It cascades a delete to salary_src.

  3. Oracle GoldenGate sends both deletes to the target.

  4. The parent delete arrives first and is applied to emp_targ.

  5. The parent delete cascades a delete to salary_targ.

  6. The cascaded delete from salary_src is applied to salary_targ.

  7. The row cannot be located because it was already deleted in step 5.

2.5.2 Assigning Row Identifiers

Oracle GoldenGate requires some form of unique row identifier on the source and target tables to locate the correct target rows for replicated updates and deletes.

2.5.2.1 Determining the Oracle GoldenGate Row Identifier to Use

Unless a KEYCOLS clause is used in the TABLE or MAP statement, Oracle GoldenGate selects a row identifier to use in the following order of priority:

  1. Primary key

  2. First unique key alphanumerically that does not contain a timestamp or a computed column.

  3. If none of the preceding key types exist (even though there might be other types of keys defined on the table) Oracle GoldenGate constructs a pseudo key of all columns that the database allows to be used in a unique key, excluding those that are not supported by Oracle GoldenGate in a key or those that are explicitly excluded by means of parameters in the Oracle GoldenGate configuration.

If there are other, non-usable keys on a table or if there are no keys at all on the table, Oracle GoldenGate logs an appropriate message to the report file. Constructing a key from all of the columns impedes the performance of Oracle GoldenGate on the source system. On the target, this key causes Replicat to use a larger, less efficient WHERE clause.

2.5.2.2 Using KEYCOLS to Specify a Custom Key

If a table does not have one of the preceding types of row identifiers, or if you prefer those identifiers not to be used, you can define a substitute key if the table has columns that always contain unique values. You define this substitute key by including a KEYCOLS clause within the Extract TABLE parameter and the Replicat MAP parameter. The specified key will override any existing primary or unique key that Oracle GoldenGate finds. For more information, see Reference for Oracle GoldenGate for Windows and UNIX.

2.6 Controlling Character Set Conversion

Replicat converts the character set of the source data to the character set of the target data when the two are different. To ensure that Replicat uses the correct character set for the target data, set the PostgreSQL environment variable PGCLIENTENCODING to the correct character set. Replicat sets the session character set to that value. If PGCLIENTENCODING is not set, Replicat sets the character set for the applied data to that of the target server.

For more information about how Oracle GoldenGate handles character sets, locale, and other aspects of globalization support, see the Oracle GoldenGate Windows and UNIX Administrator's Guide.