Using Oracle GoldenGate for Real-Time Data Integration

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:

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:

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:

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.

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:

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

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

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:

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:

Figure 4 Creating the Manager service

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

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

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:

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

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.
 
 

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:

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;
 

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
 
 

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_NAME",
 "EMAIL","PHONE_NUMBER","HIRE_DATE","JOB_ID","SALARY","COMMISSION_PCT","MANAGER_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.

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.

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.

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 11gdatabases 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.