Using Oracle GoldenGate for Real-Time Data Integration


Learn how to install, set up, and configure Oracle GoldenGate to easily achieve seamless replication of data between Oracle Database 10g and 11g

by Porus Homi Havewala

Published April 2010

Oracle GoldenGate is a best-of-breed, easy-to-deploy product used to replicate and integrate transactional data with subsecond speed among a variety of enterprise systems. Oracle GoldenGate provides the flexibility to move data between like-to-like and heterogeneous systems, including different versions of Oracle Database, different hardware platforms, and between Oracle and non-Oracle databases including Microsoft SQL Server, IBM DB2 for open systems and z/OS, Sybase, and more.

This article demonstrates how this product can be installed, set up, and configured to easily achieve the seamless replication of data between Oracle Database 10g and 11g, and also serves as an introduction to the speed capability of this product.

Acquired by Oracle in 2009, Oracle GoldenGate is found in more than 4,000 worldwide solution implementations across a range of industries. The software performs real-time, log-based change data capture (CDC) and is able to move large volumes of transactional data between heterogeneous databases with extremely low latency and a very minimal footprint.

Typically you deploy the GoldenGate capture, data pump, and delivery processes, and these processes can be placed on many different operating systems and databases. All or part of the data can be replicated, depending on your requirements at the target database. In the case of Oracle GoldenGate for Oracle databases, data manipulation language (DML) as well as data definition language (DDL) operations can be replicated. (Currently DDL operations can be replicated only between similar, not dissimilar, Oracle versions.)

Oracle GoldenGate officially supports Oracle9i Release 2 to Oracle Database 11g Release 2, and DML/DDL support exists for all these versions. The product works with both the Enterprise and Standard Editions of the Oracle Database.

As mentioned previously, data can be moved between similar or dissimilar supported versions of Oracle Database and between an Oracle database and a non-Oracle database. Data can be filtered, mapped, and transformed in most cases. Note that filtering, mapping, and transformation of data is not supported when DDL support is active (in other words, when GoldenGate is being used for DDL replication between similar Oracle Database versions).

Oracle GoldenGate is DBA-centric, so DBAs learn how to use it very easily. Implementation times are also minimal—some sites have gone live 100 percent in one month.

What You Get

The Oracle GoldenGate product line includes

  • Oracle GoldenGate
  • Management Pack for Oracle GoldenGate (also called Oracle GoldenGate Director)
  • Oracle GoldenGate Veridata
  • Oracle GoldenGate Application Adapters
  • Oracle GoldenGate for Mainframe

As a value-add benefit to customers, the Oracle GoldenGate core license includes a full use license for Oracle Active Data Guard and a full use license for XStream in Oracle Database. XStream is an API to Oracle Streams. Oracle Active Data Guard enables standby databases to be actively open for reporting, even when redo data is being applied at the same time. This is an excellent feature that allows production reporting and query work to be offloaded to the standby.

Oracle Streams has been traditionally used to replicate data only between Oracle databases. Oracle GoldenGate offers replication between non-Oracle databases as well and is easy to set up.

We should note that the published Oracle - GoldenGate Statement of Direction states “Given the strategic nature of Oracle GoldenGate, Oracle Streams will continue to be supported, but will not be actively enhanced. Rather, the best elements of Oracle Streams will be evaluated for inclusion with Oracle GoldenGate.”

Aim of This Demonstration

For the purpose of this demonstration, you should have three databases on a single Microsoft Windows server. These databases are HRPRD1, HRPRD2, and HRPRD3. The database versions are Oracle Database 11 g, Oracle Database 10 g, and Oracle Database 10 g, respectively.

The SYS and SYSTEM password of these three databases has been set to hrpassword1 for testing purposes. This password can be changed later.

The aim of the demonstration is to set up one-way replication between the HR.EMPLOYEES table in the two Oracle 10g databases, HRPRD2 and HRPRD3. Any DML changes to this table on the first database should be carried over to the second database. This replication will then be enhanced to include simultaneous replication to the Oracle 11g database, HRPRD1. As a result, any updates, inserts, or deletes on the table in HRPRD2 will successfully update HRPRD3 as well as HRPRD1.

Such a replication strategy from Oracle Database 10g to Oracle Database 10g and then to Oracle Database 11g is frequently used in upgrade scenarios, where it is necessary to replicate data before a cutover to the upgraded database version. Oracle GoldenGate offers the easy solution.

Note that in the case of Oracle databases, you do not need to create datasource names (DSNs) for the databases using the Microsoft ODBC datasource administrator. Oracle GoldenGate replication will connect directly to the Oracle database using the listener.

What to Download

First of all, for a Windows system, you need to download and install the Microsoft Visual C ++ 2005 SP1 Redistributable Packagefrom the Microsoft Download Center. This is used to install runtime components of Visual C++ Libraries required to run the application, if the computer doesn’t already have Visual C++ installed.

Next you need to download the Oracle GoldenGate software from Oracle Technology Network. For the Windows platform, at the time of writing, OTN currently carries only the 64-bit version: Oracle GoldenGate v10.4.0.x for Oracle 10g 64-bit on Windows 2000, XP, and 2003 (8 MB). To download the 32-bit Windows version, you must use the Oracle E-Delivery site. (Note that E-Delivery downloads carry a 30-day trialware license, not the OTN Developer License.) On this site, log in and search for the "Oracle Fusion Middleware Product Pack" (because Oracle GoldenGate is considered to be an Oracle Fusion Middleware product, not an Oracle Database product) for the Microsoft Windows (32-bit) Platform, and click Go.

A list of media packs appears. From this list, first select Oracle GoldenGate on Oracle Media Pack for Microsoft Windows (32-bit).

This media pack has these components:

Oracle GoldenGate V10.4.0.x for Oracle 10g on Windows 2000, XP, and 2003

V18162-01

Size: 8.4 MB

Oracle GoldenGate V10.4.0.x for Oracle 11g on Windows 2000, XP, and 2003

V18164-01

Size: 8.4 MB

Oracle GoldenGate Documentation

V18423-01

Size: 20 MB

Download all three zip files because you need Oracle GoldenGate for Oracle 10g as well as Oracle 11g.
Next, move back to the list of media packs for Oracle Fusion Middleware and select Management Pack for Oracle GoldenGate (v2.0.0.2) Media Pack for Microsoft Windows.
The management pack media pack has these components:

Oracle GoldenGate Director V2.0.0.x Server on Windows

V18127-01

Size: 228 MB

Oracle GoldenGate Director V2.0.0.x Client on Windows

V18128-01

Size:  55 MB

Oracle GoldenGate Director Documentation

V18129-01

Size: 952 KB

Download all of these as well, if you wish to set up the management pack. The management pack is a standalone product installed on your Windows server. The original name for the product is Oracle GoldenGate Director, and it consists of a multitiered client-server application that allows organizations to monitor and manage their Oracle GoldenGate deployments with ease.

Using the GUI interface of Oracle GoldenGate Director, it is possible to centrally design and configure Oracle GoldenGate and also manage and monitor the different Oracle GoldenGate processes that have been set up to replicate data across the servers in a company.

There are other media packs available in the main list of media packs, such as Oracle GoldenGate Veridata Media Pack for Microsoft Windows.

The Oracle GoldenGate Veridata software, installed as a server with agents on different platforms, is responsible for comparing one set of data to another without any downtime in order to identify data that is not synchronized. This comparison can happen 24/7, and it can run concurrently along with high-volume replication. Data can also be compared across different platforms.

Besides the main media packs mentioned above, other Oracle GoldenGate packs are displayed in the list. These are meant for non-Oracle databases such as Sybase, SQL Server, IBM DB2, Teradata, and also JMS and Flat Files, indicating the heterogeneous nature of Oracle GoldenGate.

Installation Steps

The Oracle GoldenGate documentation zip file (V18423-01.zip ) that you have downloaded contains the Oracle GoldenGate Oracle Installation and Setup Guide Version 10.4 (gg_ora_inst_v104.pdf) that explains the installation steps for Oracle GoldenGate for Oracle databases on Linux, UNIX, and Windows.

Note that there are other installation documents for Oracle GoldenGate for Sybase, SQL Server, DB2, MySQL, Teradata, and so on.

First, unzip Oracle GoldenGate for Oracle 10 g, V18162-01.zip, into a new Oracle GoldenGate software directory without any spaces in the directory name. As an example, you can use C:\OGG10G as the Oracle GoldenGate directory. The software code in this directory will be used to set up the replication between the first and second Oracle 10g databases.

Since your third database is an Oracle Database 11g version , you also need to unzip Oracle GoldenGate for Oracle 11 g, V18164-01.zip. You need to use a separate directory C:\OGG11G as the location for this version of Oracle GoldenGate. The software code in this directory will be used to control the replication to the third Oracle Database 11g database.

If you have Oracle9i databases that you want to replicate to or from, you need a different version of the Oracle GoldenGate code for Oracle9i. However, the Oracle9i version of Oracle GoldenGate cannot be downloaded from the Oracle Websites. You may need to raise a system request (SR) with My Oracle Support if you require this version.

Follow these steps at the Command prompt :

mkdir C:\OGG10G
cd C:\OGG10G
unzip c:\V18162-01.zip

mkdir C:\OGG11G
cd C:\OGG11G
unzip C:\V18164-01.zip

# start the 10
                                 g databases if not already started
net start OracleServiceHRPRD2
net start OracleServiceHRPRD3

# start the 11
                                 g database if not already started
net start OracleServiceHRPRD1
 
                              

Setting Environment Variables

Now, you can open two command windows (Start..Run..Cmd) and set the corresponding environment variables such as ORACLE_HOME, ORACLE_SID, LD_LIBRARY_PATH, and so on to the appropriate values for Oracle Database 10g and Oracle Database 11g.

For setting up replication between the Oracle 10g databases, set the variables as follows:

set  ORACLE_HOME=C:\Oracle\product\10.2.0\db_1
set ORACLE_SID=HRPRD2
set  PATH=%ORACLE_HOME%\bin;%PATH%
set  LD_LIBRARY_PATH=C:\OGG10G; %ORACLE_HOME%\LIB; 
%ORACLE_HOME%\jdbc\lib
set  CLASSPATH=;C:\Program 
Files\Java\jre6\lib\ext\QTJava.zip;%ORACLE_HOME%\jdbc\lib
 

The ORACLE_SID can be set to either HRPRD2 or HRPRD3, which are both Oracle 10g databases. Note the additions to PATH, LD_LIBRARY_PATH, and CLASSPATH.

The other alternative is to put these changes as system variables; you can do this by using My computer..Properties..Advanced..Environment Variables..System Variables in Windows XP Professional. Add the new system variables and change the path as noted above.

In this case, the drawback of two database versions on one single server is that the system variable changes must be made each time the Manager service for either Oracle GoldenGate for Oracle 10 g, or Oracle GoldenGate for Oracle 11g is started. You would have to add the system variables pertaining to Oracle Database 11g, and then start up the Oracle GoldenGate for Oracle 11g Manager service.  After this you would need to change the system variables to the ones required for Oracle Database 10 g, and then start up the Oracle GoldenGate for Oracle 10g Manager service. Because this is a test system, doing this may be fine. But in a production scenario, you need to take a more automated approach, such as a scripted approach, to set the variables and start the Manager services in the script itself.

GGSCI Command Interpreter

After setting the environment variables in the command window or at the My Computer level, move to the Oracle GoldenGate folder and run the Oracle GoldenGate Command Interpreter for Oracle (GGSCI) program either at the command line (see Figure 1) or from Windows Explorer.

 


Figure 1 Oracle GoldenGate Command Interpreter


You can now issue the command CREATE SUBDIRS in GGSCI to create the Oracle GoldenGate working subdirectories. This is seen in Figure 2, where the important directories created are listed.

 


Figure 2 Creating working subdirectories


Running the Oracle GoldenGate Manager as a Local Program

You can either run the Oracle GoldenGate Manager as a local program in a command window or install it as a Windows service that starts automatically when Windows is started. The latter is recommended.

If you want to run it as a local command, simply call the Manager executable in the directory with a parameter file. The syntax is:

C:\OGG10G>mgr
Usage:  MGR PARAMFILE <param file> [REPORTFILE <report file>]
              [PID <process id>] [PORT  <port number>]
              [USESUBDIRS] [NOUSESUBDIRS]
              [PAUSEATEND] [NOPAUSEATEND]
              [CD <directory>]

The essential parameter is the PARAMFILE. For this, you must create a parameter file in GGSCI as follows:

GGSCI (HaviPori) 1> edit params mgr

This will create a new mgr.prm file in the C:\OGG10G\dirprm directory. Type in the port number that will be used to run the manager process, and save the file.

PORT 7809

The default port is 7809. Ensure that whatever port number is chosen, it should not be used by any other program and there should be no firewall restrictions. This parameter must be specified because PORT is the only required parameter for the Manager. The Port is used by GGSCI to send requests to the Manager to start processes, and it is also used by the Extract process for various actions.

After you have created the parameter file, you can start the Manager at the Windows command prompt as follows:

C:\OGG10G>mgr paramfile c:\OGG10G\dirprm\mgr.prm

This starts the Manager and displays the message seen in Figure 3:

 

 havewala-goldengate-f3
Figure 3 Starting Manager at Windows command prompt


You must keep this window open to keep the Manager running. If you log out, the window will be closed and the Manager will be stopped.

Installing Manager as a Windows Service

An alternative to the manual method of starting the Manager described above is to install the Manager as an automatic Windows service.

This is mandatory in the case of a Windows cluster, because only a service can be automatically failed over to the passive server. On noncluster systems, setting up the Manager as a service is optional but highly recommended.

When the Manager process is created as a service in Windows, the default name of the service is GGSMGR. You can specify a custom name for this service. This is important if you need more than one Oracle GoldenGate Manager service on the same server—such as one service of Oracle GoldenGate for Oracle 10g, and the other service of Oracle GoldenGate for Oracle 11g. You also need a separate Manager service if you intend to run Oracle GoldenGate Veridata.

Specify a different name for the Manager service in the following way. At the GGSCI command prompt, issue the command

EDIT PARAMS ./GLOBALS

In the notepad editor that appears, choose to create a new file (if it is the first time you are doing this) and then enter the following line and save the GLOBALS file:

MGRSERVNAME OracleGGSMGR1

Use the name OracleGGSMGR1 as per the normal convention of Oracle services on Windows that normally have an Oracle prefix—for example, services like OracleDBConsole<instancename>, OracleJobScheduler<instancename>.

Creating the Manager service is easily done via the Installprogram in the C:\OGG10G directory. Use the install addservice command, as shown in Figure 4:

 

 havewala-goldengate-f4
Figure 4 Creating the Manager service


The new service appears as an automatic service in the Windows services list (see Figure 5):

 

 havewala-goldengate-f5
Figure 5 Manager service properties


The Manager service runs as the local system account, or it can be configured to run as a specific account by using the user and password options that can be specified with the addservice command. It can now be started by the following command:

GGSCI (HaviPori) 1> start manager

Starting Manager as service ('OracleGGSMGR1')...
Service started.
 

Installing Windows Event Messages

The Install program can also be used to install event messages into the Windows registry so they can be displayed by the Windows Event Manager. This can be done using the following command:

C:\OGG10G>install addevents
Oracle GoldenGate messages installed successfully.
Install program terminated normally.
 

The files category.dll and ggsmsg.dll can also be copied from the C:\OGG10G directory to the C:\WINDOWS\system32 directory to enable the generation of specific, rather than generic, errors.

Set up Replication—The Extract Process on HRPRD2

The Oracle GoldenGate Manager process is up and running. The next step is to set up replication between the databases.

For this, you have to create and configure at least one Extract and Replicat group. In these groups, you specify the data that you wish to capture and replicate.

The Extract process captures the data changes that are taking place and sends these changes to a “trail,” which is on the target server. The Replicat process is active on the target server and is responsible for the actual replication, by taking the data changes from the trail and applying them on the target database.

You also need to prepare the databases for the Oracle GoldenGate Replication by turning on supplemental logging at the database level. Do this using the following commands while logged on to the databases as SYSDBA:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.

SQL> alter system switch logfile;
System altered

You are now ready to add an Extract group.

GGSCI (HaviPori) 1> ADD EXTRACT emp_ext, TRANLOG, BEGIN NOW
EXTRACT added.
 

You have specified TRANLOG, meaning that the transaction (redo) log is the datasource. BEGIN can be followed by the timestamp in the log at which to begin processing, or NOW indicates immediately. TRANLOG can alternatively be used with the EXTSEQNO (sequence number of an Oracle redo log at which to begin) and EXTRBA (relative byte address within that log) options.

Note that only eight characters are allowed for the name of the Extract group. Full help is available if you specify

GGSCI (HaviPori) 1> help add extract

The next step is to add either a RMTTRAIL or an EXTTRAIL. The former is used with remote machines, and the latter is recommended if you are setting up replication on the same local machine. You will use the EXTTRAIL, which is created by this command:

GGSCI (HaviPori) 1> ADD EXTTRAIL C:\OGG10G\dirdat\et, EXTRACT emp_ext
EXTTRAIL added.
 

The EXTTRAIL is specified to be a physical subdirectory on the server—preferably under the dirdat subdirectory, which is one of the created working directories for Oracle GoldenGate replication.

The filename specified can be only two characters, because when the actual file is created, a numeric suffix is appended to the two specified characters to create the filename. For example, a file C:\OGG10G\dirdat\et000001 will be created as an extract file when the EXTRACT process starts and data changes occur.

The syntax of RMTTRAIL is the same; if you are using this, then simply substitute the word EXTTRAIL with RMTTRAIL. The main difference is in the parameter file for the extract—this parameter file is created in the next step. In the case of RMTTRAIL, the parameter file will have additional parameters that describe the remote host and the remote manager port number.

You now create the parameter file for the Extract group emp_ext as follows:

GGSCI (HaviPori) 1> EDIT PARAM emp_ext

In this file, type in the lines

EXTRACT emp_ext
USERID system@localhost:1521/HRPRD2, PASSWORD hrpassword1
EXTTRAIL C:\OGG10G\dirdat\et
TABLE hr.employees;

You have specified the table HR.EMPLOYEES to be extracted in this Extract group, from the first of your Oracle 10g databases, which is HRPRD2.

If you were using a RMTTRAIL, then the changed lines would simply be

RMTHOST <remote host>, MGRPORT <remote manager port number> 
RMTTRAIL C:\<remote GoldennGate Directory>\dirdat\rt
 

You can now start the extract with the following command:

GGSCI (HaviPori) 1> START EXTRACT EMP_EXT
Sending START request to MANAGER ('OracleGGSMGR1') ...
EXTRACT EMP_EXT starting
 

The status and information about the process can be seen as follows:

GGSCI (HaviPori) 2> STATUS EXTRACT EMP_EXT
EXTRACT EMP_EXT: RUNNING

GGSCI (HaviPori) 3> INFO EXTRACT EMP_EXT
EXTRACT    EMP_EXT   Last Started 2009-12-08 13:04   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:00 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2009-12-08 13:05:58  Seqno 11, RBA 578560
 

You can also get detailed information about the process:

GGSCI (HaviPori) 4> INFO EXTRACT EMP_EXT detail
EXTRACT    EMP_EXT   Last Started 2009-12-08 13:04   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:00 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2009-12-08 13:07:19  Seqno 11, RBA 596992

  Target Extract Trails:
  Remote Trail Name                                Seqno        RBA     Max MB
  C:\OGG10G\dirprm\et                                  0        923         10
  Extract Source                          Begin             End
  D:\ORADATA\HRPRD2\REDO01.LOG         2009-12-08 12:59  2009-12-08 13:07
  Not Available                           * Initialized *   2009-12-08 12:59

Current directory     C:\OGG10G
Report file                C:\OGG10G\dirrpt\EMP_EXT.rpt
Parameter file         C:\OGG10G\dirprm\EMP_EXT.prm
Checkpoint file        C:\OGG10G\dirchk\EMP_EXT.cpe
Process file             C:\OGG10G\dirpcs\EMP_EXT.pce
Error log                C:\OGG10G \ggserr.log
 

Troubleshooting Extracts

If there are any errors, the extract would appear as STOPPED. In this case, you have to investigate the errors.

First, check the report file C:\OGG10G\dirrpt\EMP_EXT.rpt, which is mentioned in the detailed information above.

Next, see if there are any indicative messages in the log files ggserr.log and sqlnet.log, which are in the main C:\OGG10G directory.

The ggserr.log file contains event information such as:

2009-12-02  14:53:26  GGS INFO        301   Oracle GoldenGate
Manager for Oracle, mgr.prm:  Command received from GGSCI on host 10.10.10.10 (START EXTRACT  EMP_EXT ).
2009-12-02  14:53:26  GGS INFO        302   Oracle GoldenGate Manager for Oracle, mgr.prm:  
EXTRACT EMP_EXT starting.

These events can also be seen in the following way:

GGSCI (HaviPori) 1> VIEW GGSEVT
…
….
2009-12-02 15:09:34  GGS INFO        302  Oracle GoldenGate Manager for Oracle,
mgr.prm:  EXTRACT EMP_EXT starting.
2009-12-02 15:13:26  GGS INFO        399  Oracle GoldenGate Command Interpreter
for Oracle:  GGSCI command (HaviPoriH): EDIT PARAM emp_ext.
 

The sqlnet.log file may show errors such as

TNS-12557: TNS:protocol adapter not loadable

This particular error indicates a database connection issue, so you need to explore at the listener level. Make sure the Path includes the Oracle Home bin subdirectory right at the start. There may be other TNS errors that may indicate other solutions.

EMP_EXT.rpt may show an error such as the following:

2009-12-08 13:01:27  GGS ERROR       182  OCI Error beginning
 session (status = 28009-ORA-28009: connection as SYS should be as SYSDBA or SYSOPER).
2009-12-08 13:01:27  GGS ERROR       190  PROCESS ABENDING.
 

This indicates that the Extract process is trying to log on as sys without being the SYSDBA. Simply change the login in the parameter file for the Extract to “system”.

We should note here that specifying SYSDBA at the end of the login line in the Extract parameter file doesn’t work, although it does work with the DBLOGIN command in GGSCI that you will see next.

The ggserr.log file may show similar errors:

2009-12-03 00:43:16  GGS INFO        399  Oracle GoldenGate 
Command Interpreter for Oracle:  GGSCI command (HaviPoriH): start manager.
2009-12-03 00:43:25  GGS ERROR       182  Oracle GoldenGate 
Manager for Oracle, mgr.prm:  OCI Error during OCIServerAttach 
(status = 12154-ORA-12154: TNS:could not resolve the connect identifier specified).
2009-12-03 00:43:25  GGS ERROR       190  Oracle GoldenGate 
Manager for Oracle, mgr.prm:  PROCESS ABENDING.
 

This indicates an issue with the login credentials supplied in the Manager parameter file, mgr.prm. The Manager has not started as a result.

Another possible error can be seen in the Extract report file. For example, EMP_EXT.rpt can have this error:

2009-12-07 16:40:08  GGS ERROR       190  No minimum supplemental
 logging is enabled. This may cause extract process to handle key 
update incorrectly if key column is not in first row piece.
2009-12-07 16:40:08  GGS ERROR       190  PROCESS ABENDING.
 

The solution to this is obviously to enable supplemental logging at the database level. This has already been done, so this error will not be seen in your case.

Setting Up the Replicat Process on HRPRD3

You now move to the target database, HRPRD3, which in your case is on the same server. Because this is an Oracle 10g database as well, you can use the same Oracle GoldenGate for Oracle 10g installation in the subdirectory C:\OGG10G and the same Manager service. If this were on a different server, you would need to install the appropriate Oracle GoldenGate software for the database version and then configure a new Manager service on that server.

The first step is to use the GGSCI command DBLOGIN to connect to the database, so that other commands can be issued that will work on the database.

Note that DBLOGIN works fine as follows:

GGSCI (HaviPori) 1> 
DBLOGIN USERID system@localhost:1521/HRPRD3 PASSWORD hrpassword1
Successfully logged into database.
 

There are two other parameters, SOURCEDB and TARGETDB, seen in the GGSCI help. These parameters are for non-Oracle databases only.

Now you need to add a checkpoint table for the employees table that you are replicating:

GGSCI (HaviPori) 2> ADD CHECKPOINTTABLE HR.EMPLOYEES_CHKPT
Successfully created checkpoint table HR.EMPLOYEES_CHKPT.
 

Checkpoints that are stored in this table refer to the current read and write positions of the Replicat process. This is used to prevent the loss of data in case the process needs to be restarted, or if there is any fault in the server or a hiccup in the network that would otherwise result in data loss. The other advantage is that multiple Extract or Replicat processes can be read from the same set of trails by using checkpoints.

Checkpoints are optional in the sense that they are not required for Extract and Replicat processes that run in batch mode, because such processes can always be restarted. However, checkpoints are necessary in the case of continuously operating Extract and Replicat processes. They are normally maintained as files in the dirchk subdirectory, but in the case of Replicat they can optionally be stored in the database in the checkpoint table.

If you specify the checkpoint table in the GLOBALS parameter file, the above command can use that specification. In this scenario, the command could simply be

GGSCI (HaviPori) 3> ADD CHECKPOINTTABLE
No checkpoint table specified, using GLOBALS specification (hr.employees_chkpt).
Successfully created checkpoint table HR.EMPLOYEES_CHKPT.
 

You can now add the Replicat group as follows, specifying the exact same EXTTRAIL that was used by the Extract group set up in the first database. So the Replicat group feeds on or consumes the trail created by the Extract group:

GGSCI (HaviPori) 4> ADD REPLICAT emp_rep, EXTTRAIL C:\OGG10G\dirdat\et, CHECKPOINTTABLE hr.employees_chkpt, 
REPLICAT added.
 

Edit the parameter file for this Replicat group as follows:

GGSCI (HaviPori) 5> EDIT PARAM emp_rep

In the new file, enter the following:

REPLICAT emp_rep
USERID system@localhost:1521/HRPRD3, PASSWORD hrpassword1
ASSUMETARGETDEFS
MAP hr.employees, TARGET hr.employees;
 

Because the tables have exactly the same DDL structure, you use the ASSUMETARGETDEFS parameter.

Now you can start the Replicat group:

GGSCI (HaviPori) 6> start REPLICAT emp_rep
Sending START request to MANAGER ('GGSMGR') ...
REPLICAT EMP_REP starting

Wait a few seconds to see the status; if you try immediately, the status may say “stopped.” When you see the status as running”, check the detailed information, and also issue an info all command to show all running processes:

GGSCI (HaviPori) 7> status REPLICAT emp_rep
REPLICAT EMP_REP: STOPPED

GGSCI (HaviPori) 8> status REPLICAT emp_rep
REPLICAT EMP_REP: RUNNING

GGSCI (HaviPori) 11> info REPLICAT emp_rep detail
REPLICAT   EMP_REP   Last Started 2009-12-08 13:35   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:01 ago)
Log Read Checkpoint  File C:\OGG10G\dirdat\et000001
                     2009-12-08 13:33:24.000000  RBA 985

  Extract Source                          Begin             End
  C:\OGG10G\dirdat\et000001               2009-12-08 13:33  2009-12-08 13:33
  C:\OGG10G\dirdat\et000000               * Initialized *   2009-12-08 13:33

Current directory    C:\OGG10G
Report file          C:\OGG10G\dirrpt\EMP_REP.rpt
Parameter file       C:\OGG10G\dirprm\EMP_REP.prm
Checkpoint file      C:\OGG10G\dirchk\EMP_REP.cpr
Checkpoint table     HR.EMPLOYEES_CHKPT
Process file         C:\OGG10G\dirpcs\EMP_REP.pcr
Error log            C:\OGG10G\ggserr.log

GGSCI (HaviPori) 12> info all
Program     Status      Group       Lag           Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     EMP_EXT     00:00:00      00:00:03
REPLICAT    RUNNING     EMP_REP     00:00:00      00:00:06
 

You can test out replication at this point between HRPRD2 and HRPRD3, your two Oracle Database 10g databases. However, you decide to wait until replication is also set up to the Oracle 11g database, HRPRD1, which is done in the next step.

Setting Up the Replicat Process on HRPRD1

Your Oracle 11g database, HRPRD1, is also on the same server. Since it is a 11g database version,  you need to use the Oracle GoldenGate for Oracle 11g software which you had unzipped at the start of this article into the C:\OGG11G directory.

For the Oracle 11g database home, set the environment variables as follows:

set ORACLE_HOME=C:\app\havipori\product\11.1.0\db_1
set ORACLE_SID=HRPRD1
set PATH=%ORACLE_HOME%\bin;%PATH%
set LD_LIBRARY_PATH=C:\OGG11G; %ORACLE_HOME%\LIB; %ORACLE_HOME%\jdbc\lib
set CLASSPATH=;C:\Program Files\Java\jre6\lib\ext\QTJava.zip;%ORACLE_HOME%\jdbc\lib
 

You follow similar steps to the Oracle GoldenGate for Oracle 10g installation and create a Manager service named OracleGGSMGR2 and start it. You create the working subdirectories and the mgr.prm file. These steps are shown below:

C:\OGG11G>ggsci 
GGSCI (HaviPori) 1> EDIT PARAMS ./GLOBALS
 

Type in MGRSERVNAME OracleGGSMGR2 in this file and save it.

C:\OGG11G>install addservice
Service 'OracleGGSMGR2' created.
Install program terminated normally.

GGSCI (HaviPori) 1>create subdirs
Creating subdirectories under current directory C:\OGG11G

Parameter files                C:\OGG11G\dirprm: created
Report files                   C:\OGG11G\dirrpt: created
Checkpoint files               C:\OGG11G\dirchk: created
Process status files           C:\OGG11G\dirpcs: created
SQL script files               C:\OGG11G\dirsql: created
Database definitions files     C:\OGG11G\dirdef: created
Extract data files             C:\OGG11G\dirdat: created
Temporary files                C:\OGG11G\dirtmp: created
Veridata files                 C:\OGG11G\dirver: created
Veridata Lock files            C:\OGG11G\dirver\lock: created
Veridata Out-Of-Sync files     C:\OGG11G\dirver\oos: created
Veridata Out-Of-Sync XML files C:\OGG11G\dirver\oosxml: created
Veridata Parameter files       C:\OGG11G\dirver\params: created
Veridata Report files          C:\OGG11G\dirver\report: created
Veridata Status files          C:\OGG11G\dirver\status: created
Veridata Trace files           C:\OGG11G\dirver\trace: created
Stdout files                   C:\OGG11G\dirout: created

GGSCI (HaviPori) 2>  EDIT PARAMS mgr
 

Type in the file the following lines and save it. Note the different port number used and that the Manager is now logging on to HRPRD1.

PORT 7810
USERID system@localhost:1521/HRPRD1 PASSWORD hrpassword1
 

You can now start the Manager. The next step is to use DBLOGIN to log in to the Oracle 11g database and create the checkpoint table in this database.

GGSCI (HaviPori) 3> start manager
Starting Manager as service ('OracleGGSMGR2')...
Service started.

GGSCI (HaviPori) 4> DBLOGIN USERID system@localhost:1521/HRPRD1 PASSWORD hrpassword1
Successfully logged into database.

GGSCI (HaviPori) 5> ADD CHECKPOINTTABLE HR.EMPLOYEES_CHKPT
Successfully created checkpoint table HR.EMPLOYEES_CHKPT.
 

Now, you are ready to create the second Replicat group in this database HRPRD1. Note that you are using the exact same trail as was used before by the database HRPRD3.

So the same trail coming from HRPRD2 is being consumed by two databases, HRPRD3 and HRPRD1, by two Replicat groups. This is quite impressive.

If you don’t use BEGIN NOW in the ADD REPLICAT command, the Replicat  process will just start at the beginning of the trail, and that is fine.

GGSCI (HaviPori) 6> ADD REPLICAT emp_rep, EXTTRAIL C:\OGG10G\dirdat\et, CHECKPOINTTABLE hr.employees_chkpt,

REPLICAT added.

GGSCI (HaviPori) 7> EDIT PARAM emp_rep
 

In this file, enter the following lines:

REPLICAT emp_rep
USERID system@localhost:1521/HRPRD1, PASSWORD hrpassword1
ASSUMETARGETDEFS
HANDLECOLLISIONS
DISCARDFILE c:\OGG11G\dirrpt\emp_ext.dsc, purge
MAP hr.employees, TARGET hr.employees;
 

Notice the extra parameters in this file, such as HANDLECOLLISIONS and DISCARDFILE. This is to handle errors such as duplicates in the tables. You have put these here as an option; you could have put them in the first Replicat group parameter file as well.

HANDLECOLLISIONS performs automatic error handling for initial data loads during the time the source database is active. Be sure to remove this parameter after the initial load is complete. The DISCARDFILE parameter specifies the file where the discarded records will be saved.

Now start the Replicat group and check the status:

GGSCI (HaviPori) 8> start REPLICAT emp_rep
Sending START request to MANAGER ('GGSMGR2') ...
REPLICAT EMP_REP starting

GGSCI (HaviPori) 9> status REPLICAT emp_rep
REPLICAT EMP_REP: STOPPED
 

The status shows as stopped, but there is no indication of the error in any log file in the C:\OGG11G directory.

The only way to force the error to appear is to run the Replicat from the command prompt. This is for debugging purposes—the error, if any, appears onscreen rather than in the report file. But in the Oracle GoldenGate for Oracle 11g version, this seems to be the way for getting it to work, although we concede that it could be an environment issue on the server we used for testing. The command to use is as follows:

C:\OGG11G> replicat paramfile c:\OGG11G\dirprm\emp_rep.prm

***********************************************************************
                 Oracle GoldenGate Delivery for Oracle
                      Version 10.4.0.19 Build 002
        Windows (optimized), Oracle 11 on Sep 18 2009 16:44:02
Copyright (C) 1995, 2009, Oracle and/or its affiliates.  All rights reserved.

                    Starting at 2009-12-09 11:46:28
***********************************************************************
Operating System Version:
Microsoft Windows XP Professional, on x86
Version 5.1 (Build 2600: Service Pack 2)

Process id: 1504
Description:
***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
REPLICAT emp_rep
USERID system@localhost:1521/HRPRD1, PASSWORD ***********
ASSUMETARGETDEFS
HANdlecollisions
Discardfile c:\OGG11G\dirrpt\emp_ext.dsc, purge
MAP hr.employees, TARGET hr.employees;

CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE:                         64K
CACHESIZE:                              512M
CACHEBUFFERSIZE (soft max):               4M
CACHEPAGEOUTSIZE (normal):                4M
PROCESS VM AVAIL FROM OS (min):           1G
CACHESIZEMAX (strict force to disk):    881M

Database Version:
Oracle Database 11
                                 g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

Database Language and Character Set:
NLS_LANG environment variable not set, using default value AMERICAN_AMERICA.WE8M
SWIN1252.
NLS_LANGUAGE     = "AMERICAN"
NLS_TERRITORY    = "AMERICA"
NLS_CHARACTERSET = "WE8MSWIN1252"

Warning: NLS_LANG is not set. Please refer to user manual for more information.

***********************************************************************
**                     Run Time Messages                             **
***********************************************************************
Opened trail file C:\OGG10G\dirdat\et000001 at 2009-12-09 11:46:35

MAP resolved (entry HR.EMPLOYEES):
  MAP HR.EMPLOYEES, TARGET hr.employees;
Using following columns in default map by name:
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE,
  JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID

Using the following key columns for target table HR.EMPLOYEES: EMPLOYEE_ID.
 
                              

At this point, the Replicat process waits onscreen. If you open a new command window and start GGSCI and check the status, the Replicat process shows as RUNNING. So the replication in Oracle Database 11g starts working in this way.

The extra parameters in this file, such as HANDLECOLLISIONS and DISCARDFILE, were purposely placed due to duplicate record errors that had appeared onscreen when running the Replicat process from the command line. The errors are seen in the output below; they indicate that the initial issue was with duplicate records.

On further investigation, it was found that a user had inserted records manually in the target Oracle 11g database, and this was the same as the data inserted in the source Oracle 10g database after Oracle GoldenGate replication had been started. This had led to the duplication of records that caused the replication issue.

…
***********************************************************************
**                     Run Time Messages                             **
***********************************************************************
Opened trail file C:\OGG10G\dirdat\et000000 at 2009-12-09 11:17:51

Switching to next trail file C:\OGG10G\dirdat\et000001 at 2009-12-09 11:17:51 du
e to EOF, with current RBA 923
Opened trail file C:\OGG10G\dirdat\et000001 at 2009-12-09 11:17:51

Processed extract process graceful restart record at seq 1, rba 923.

MAP resolved (entry HR.EMPLOYEES):
  MAP HR.EMPLOYEES, TARGET hr.employees;
Using following columns in default map by name:
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE,
  JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID

Using the following key columns for target table HR.EMPLOYEES: EMPLOYEE_ID.

2009-12-09 11:17:54  GGS WARNING     218  Aborted grouped transaction on 'HR.EMP
LOYEES', Database error 1 (ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violat
ed).

2009-12-09 11:17:54  GGS WARNING     218  Repositioning to rba 985 in seqno 1.

2009-12-09 11:17:54  GGS WARNING     218  SQL error 1 mapping HR.EMPLOYEES to HR
.EMPLOYEES OCI Error ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violated (st
atus = 1), SQL <INSERT INTO "HR"."EMPLOYEES" ("EMPLOYEE_ID","FIRST_NAME","LAST_N
AME","EMAIL","PHONE_NUMBER","HIRE_DATE","JOB_ID","SALARY","COMMISSION_PCT","MANA
GER_ID","DEPARTMENT_ID") VALUES (:a0,:a1,:a2,:a3,:a4,:a5>.

2009-12-09 11:17:54  GGS WARNING     218  Repositioning to rba 985 in seqno 1.
….
 

The way to resolve this was to use the parameters HANDLECOLLISIONS and DISCARDFILE and restart the Replicat process. The DISCARDFILE supplies more information in the case of discarded records. Once the Replicat process is up and running at the command prompt, let it complete running through the trail, discarding records as necessary.

Then stop the Replicat process in the command window by pressing Ctrl-C. This displays the following:

Enter X to exit or C to continue: X
***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************
Last record for the last committed transaction is the following:
___________________________________________________________________
Trail name :  C:\OGG10G\dirdat\et000001
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :   196 (x00c4)    IO Time    : 2009-12-08 13:43:36.000000
IOType     :     5  (x05)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :         11       AuditPos   : 1188880
Continued  :     N  (x00)     RecCount   :     1  (x01)

2009-12-08 13:43:36.000000 Insert             Len   196 RBA 1295
Name: HR.EMPLOYEES
___________________________________________________________________

Reading C:\OGG10G\dirdat\et000001, current RBA 1604, 2 records
Report at 2009-12-09 11:47:56 (activity since 2009-12-09 11:46:35)

From Table HR.EMPLOYEES to HR.EMPLOYEES:
       #                   inserts:         2
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0
       #         insert collisions:         2

Last log location read:
     FILE:      C:\OGG10G\dirdat\et000001
     SEQNO:     1
     RBA:       1604
     TIMESTAMP: Not Available
     EOF:       YES
     READERR:   400
 

Now, you can take out the HANDLECOLLISIONS parameter by using “—” as a comment prefix on the line. Then restart the Replicat process at the command line. Because the duplicate record issue is resolved, the process will now work and replicate any future changes to the source database.

Test the Replication

The records in the HR.EMPLOYEES table on all three databases—HRPRD1, HRPRD2, and HRPRD3—can be seen in the command windows in Figure 6. There are exactly 107 records in all three databases. The SELECT command issued is seen in the third window.

 

 havewala-goldengate-f6
Figure 6 Three databases with employee records


You can now INSERT and COMMIT rows in the HR.EMPLOYEES table of the source database HRPRD2 and see if these rows get replicated to the target databases HRPRD3 and HRPRD1. The INSERT is seen in Figure 7.

 

 havewala-goldengate-f7
Figure 7 Inserting into HRPRD2 employee table


Running the SELECT again in all three databases shows that the replication has occurred to the target databases instantaneously, and all the databases now have 109 records (as shown in Figure 8). So replication using Oracle GoldenGate for Oracle 10g and Oracle 11g is a success.

 

 havewala-goldengate-f8
Figure 8 Replication to other databases instantly


You have seen that replication from Oracle Database 10g to 10g databases works fine. Replication from Oracle Database 10g to 11g databases also works, but only if the Replicat process is started at the command prompt, as in "replicat paramfile c:\OGG11G\dirprm\emp_rep.prm". Starting the Replicat process from GGSCI keeps it at stopped status. This is probably an environment error on our test server. We should note here that there are no errors and it works fine if started at the command prompt.

Capabilities, Resource Requirements, and Impact

You have seen the Extract and Replicat processes of Oracle GoldenGate in action. Up to 300 concurrent Extract and Replicat processes are supported per instance (that is, one Manager process) by the  GGSCI command interface—a significant number.

However, the memory usage also increases with more such processes – about 25-55 MB of memory are required by each Extract and Replicat process. The memory usage could actually be more, and this depends on the number of concurrent transactions, and the transaction sizes. This is always debatable.

There is also a 3 to 5 percent CPU impact of Oracle GoldenGate Replication on the source system, depending on the number of redo logs being generated.

What you have set up here is one-way replication. It is also possible to set up two-way multimaster (multisite update) replication. Oracle GoldenGate handles conflict resolution in such installations by studying and understanding when both sites are updating the same record. You can set up rules for the conflict resolution (such as time stamp, or which side always wins, and so on), and you can also set up user exits on how to resolve the conflict.

Oracle GoldenGate can replicate data even if there are no unique keys in the tables of the target database. This is possible on all supported databases, provided you can identify the columns that are unique or use the entire row as a key—because some type of uniqueness is required for any deletes or updates.

Currently, Oracle GoldenGate cannot replicate double byte data, but this is on the road map in a future release. Right now, double byte data can be handled as a pass-through to a standby database for disaster recovery purposes.

Oracle GoldenGate Director

This product from Oracle GoldenGate is now called the Oracle Management Pack for GoldenGate. It is a multitiered client-server application for monitoring and managing Oracle GoldenGate deployment in a GUI interface. You can manage multiple instances (Manager processes) of Oracle GoldenGate if the remote clients of Oracle GoldenGate Director are installed on the hosts where these instances are running.

The clients all connect to the central installation of the Oracle GoldenGate Director server , which uses Oracle WebLogic Server 11g (10.3.1) as the functional application server. Previously, the earlier JBoss Application Server 3.2.7 was shipped with Oracle GoldenGate Director. This is now replaced with Oracle WebLogic Server, with the caveat that the latter has to be preinstalled and working on the system before the Oracle GoldenGate Director server is installed.

The Oracle GoldenGate Director server has a Web interface and requires its own database repository of at least 200MB in an existing Oracle database—or you can use MySql or Microsoft SQL Server for the repository. As a security measure, Oracle GoldenGate Director installation requires that the database password used for the repository must be at least eight characters and contain at least one numeric and one alphanumeric character.

If you define a process in GGSCI, then you can configure, monitor, and control the process with Oracle GoldenGate Director. Other standalone processes that are executed from the OS command line cannot be monitored by Oracle GoldenGate Director (for example, the DDLGEN process).

The Extract process, Replicat process, Manager process, trails (local or remote), files (local or remote), and the Extract and Replicat tasks can all be configured, monitored, and controlled with Oracle GoldenGate Director on the Windows, UNIX, and NonStop platforms.

Conclusion and Further Information

It is possible to specify additional parameters in the Oracle GoldenGate Manager parameter file that allow dynamic port allocation, enable autostart of replication processes (such as Extract and Replicat) when the Manager starts, and also set up central maintenance (purging) of all trails.

The Oracle GoldenGate Administration Guide Version 10.4 explains these advanced Manager parameters in detail. This guide also describes configuring the Extract and  Replicat processes in different ways, and using Oracle GoldenGate replication for various purposes such as live reporting, real-time data distribution, real-time data warehousing, maintaining a live standby database and active-active high availability, and also configuring DDL synchronization. Oracle GoldenGate would also be very useful in database upgrade scenarios where a database needs to be upgraded with near-zero downtime – simply set up Oracle GoldenGate replication from the old database version to the new database version, and switch over all your clients to point to the new database at the cutover time. This would be an excellent choice, for example, if you were moving your existing databases to the Oracle Exadata V2 database machine for consolidation of all your databases on the fastest database machine in the world.

Another useful reference for Oracle GoldenGate is the Oracle GoldenGate Troubleshooting and Tuning Guide Version 10.4. Both this and the Administration Guide are found in the documentation zip file downloaded at the beginning of this article.

We would like to thank the Oracle product managers for Oracle Fusion Middleware and Oracle GoldenGate, who assisted happily (in true Oracle spirit) with the Oracle GoldenGate replication setup described in this article. We look forward to the planned integration of Oracle GoldenGate Director with Oracle Enterprise Manager Grid Control.

Oracle Enterprise Manager Grid Control is a powerful enterprise management product from Oracle. If you want to learn how to automate the patching of all your Oracle RAC or non–Oracle RAC databases, Oracle Automatic Storage Management (ASM) instances, and Oracle Clusterware in your environment, read an article from the author titled “Patch a Thousand Databases, Using Oracle Enterprise Manager Grid Control”. For easily setting up Oracle Recovery Manager (Oracle RMAN) backups for your corporate databases using Oracle Enterprise Manager Grid Control, read “ Oracle RMAN Backups: Pushing the Easy Button”.

And for understanding how Oracle Enterprise Manager Grid Control can save a lot of time and resources when used for Oracle Data Guard setup, management (including switchover or failover), and monitoring, read another recent article from the author, “ Easy Disaster Proof Production with Grid Control ”.

Enjoy the world of Oracle.


Porus Homi Havewala, an Oracle ACE director (Database), is a Principal Consultant at S & I Systems Pte Ltd, an Oracle Platinum Partner in Singapore. He has extensive experience in Oracle technology since 1994 and has served as a production DBA, senior consultant, e-business technical DBA and systems administrator, development DBA, and database designer/modeler (using Oracle Designer). He was involved as the lead database architect and technical team leader for the first production Oracle Enterprise Manager Grid Control site in the world, a telecommunications giant in Australia. He is an avid Oracle Enterprise Manager Grid Control evangelist and has written several OTN articles on managing the Grid.