Oracle GoldenGate: Transactional Data Capture from WebLogic Java Messaging Service

Introduction

Oracle GoldenGate (OGG) includes an optional set of adapters that adds functionality to capture transactional data from a JMS Queue or Topic and format this data for apply to a relational database. This article presents basic configuration options and discusses best practices as they relate to Oracle GoldenGate for Java version 11.2.1.0 and the capture of data from the Oracle WebLogic JMS Server (WebLogic).

Installation and configuration of WebLogic and OGG database apply is outside the scope of this article. However, to understand the concepts presented readers should have an understanding of these products and their use.

Main Article

The OGG Message Capture Adapter is implemented as a Vendor Access Module (VAM) plug-in to a generic Extract process. The generic Extract is contained in a special Oracle GoldenGate version that has no database functionality. The VAM is used to interact with WebLogic and read messages from a JMS Queue or Topic. An external set of files to define properties, rules, and definitions for how messages are to be parsed and mapped to records in the OGG Trail.

Figure 1 depicts an overview of the JMS message capture environment.

oggjmscapture

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Key components of the OGG message capture environment include:

  1. Extract Parameter File: Contains parameters that identify the VAM shared object library and property file location.
  2. Properties File: Contains values that set the connection properties and parsing rules for reading the XML messages.
  3. GENDEF: A separate utility that uses the properties file and parser-specific data definitions to create an OGG source definitions file.
  4. Data Pump Extract: Reads the OGG Trail created by the Message Capture Extract and delivers the records to the target database server.

 Functional Overview

When the Extract is started a connection is made to the message system through a generic JMS interface. If a connection cannot be established, the Extract will abend.

If a connection is established, the JMS Handler portion of the VAM shared object will request the next message from the JMS Queue. To consume messages a local JMS transaction is started. A read is executed to retrieve the next message from the Queue and the contents of the message is returned. If the read fails because no message exists, an end-of-file message is returned and the Extract sleeps for the amount of time specified by the EOFDELAY parameter setting (default: 1 second).

When all of the messages that make up a transaction have been read, the VAM shared object parses the message into OGG Trail atomic data format and the Extract writes the transaction records to the OGG Trail. The local JMS transaction is then committed and the messages removed from the Queue. If an error occurs during the message read, parsing, or write; the current JMS transaction is rolled back, leaving the messages in the Queue, and the Extract abends.

Core Product Specific

The OGG Message Adapter VAM is core product specific; meaning the version 11.2 adapter is designed to function only with the OGG 11.2 core product. This is due to changes in the core product which prevents the VAM from being linked with other versions of the Extract program.

Implementation Process

The process for implementing OGG delivery to JMS includes:

  1. Download and install the OGG Java Adapter package.
  2. Configure the OGG JMS VAM Extract.
  3. Generate definitions for the JMS message data.
  4. Create the OGG Java Extract Data Pump (Java Data Pump).

Install the Adapter Package

Because the OGG Java Adapter requires links to the WebLogic Java classes, it must be installed on a server running WebLogic. The adapter package consists of two parts, the OGG Java Adapter and Oracle GoldenGate Generic.

OGG Java Adapter is comprised of the user exit, java libraries, and sample configuration files.

Oracle GoldenGate Generic is a version of the OGG application with no built-in database support.

To install the package:

  1. Create a directory on the WebLogic server to hold the OGG files.
  2. Unzip OGG Generic into this location.
  3. Unzip OGG Java Adapter into this location.
  4. From a Linux shell, start the GGSCI utility and execute the command CREATE SUBDIRS.

Configure the VAM Extract

The VAM Extract consumes data from the JMS Queue and formats it to OGG Trail specifications.

EXTRACT v_oggq
VAM libggjava_vam.so PARAMS(dirprm/v_oggq_new.properties)
REPORTCOUNT EVERY 5 minutes, rate
GETUPDATEBEFORES
TRANLOGOPTIONS VAMCOMPATIBILITY 1
TRANLOGOPTIONS GETMETADATAFROMVAM
EXTTRAIL ./dirdat/vj
TABLE EAST.*;

In the Extract parameter file, the setting VAM libggjava_vam.so PARAMS(dirprm/v_oggq_new.properties) specifies the name of the VAM library and the location of the properties file, TRANLOGOPTIONS VAMCOMPATIBILITY 1 specifies the original implementation of VAM is to be used, and TRANLOGOPTIONS GETMETADATAFROMVAM specifies that metadata will be sent by the VAM.

Configure the VAM Properties File

Message capture is configured by the settings in the VAM properties file. The file name and location is set via the PARAMS option of the Extract VAM parameter. The properties file will contain configuration settings that specify logging characteristics, parser mappings, and JMS connection properties.

 Best Practice: The VAM properties file should reside in the dirprm directory of the OGG installation location.

Configure Logging

Define the VAM log location, logging level for the individual VAM modules, and report generation interval.

goldengate.log.logname=dirrpt/v_oggq_jni
goldengate.log.level=WARN
goldengate.log.tostdout=false
goldengate.log.tofile=true
goldengate.log.modules=COPYBOOK,DEFSCHEMA,DELIMITEDPARSER,FILEPROVIDER,FIXEDPARSER,JAVAVAM,JAVAVAMIMPL,JMSPROVIDER,
LOGMALLOC,PARSER,PROVIDER,RECMGR,SFXML,TXSTORE,UEUTIL,UTILS,XMLPARSER,XMLRULES

gg.report.time=1 hr

goldengate.log.logname identifies the location and name of the log file.

goldengate.log.level sets the logging level to warning and error messages only, options are INFO, WARN, ERROR, or DEBUG.

goldengate.log.tostdout specifies if log data should be written to stdout (a terminal window).

goldengate.log.tofile specifies if log data should be written to the file location identified by goldengate.log.logname.

goldengate.log.modules identifies the program modules for which log data should be generated.

gg.report.time denotes the time interval for VAM report generation.

Set the Data Source and Message Identifier Overrides

Define the message data source, whether local transactions are to be used, and the starting point for the message sequence id.

gg.source=jms
gg.jms.localtx=false
gg.jms.id=time

Setting gg.jms.localtx=false disables local JMS transactions, and uses the client_acknowledge features of the VAM. Setting gg.jms.id=time means the system timestamp is used as each message’s sequence id.

Connect to JMS and Retrieve Messages

### JNDI properties
java.naming.provider.url=t3://localhost:7003
java.naming.factory.initial=weblogic.jndi.WLInitialContextFactory
java.naming.security.principal=weblogic
java.naming.security.credentials=Oracle_1

gg.jms.destination=OGGQ
gg.jms.connectionFactory=OGGCF
gg.jms.id=time

### Java and WebLogic classpath env settings
jvm.bootoptions=-Xmx512m -Xms256m -Djava.class.path=.:dirprm:ggjava/ggjava.jar:/u01/app/oracle/Middleware/patch_wls1
211/profiles/default/sys_manifest_classpath/weblogic_patch.jar:/u01/app/oracle/Middleware/patch_ocp371/profiles/defau
lt/sys_manifest_classpath/weblogic_patch.jar:/usr/lib/jvm/java-1.6.0-openjdk-1.6.0.0.x86_64/lib/tools.jar:/u01/app/
oracle/Middleware/wlserver_12.1/server/lib/weblogic_sp.jar:/u01/app/oracle/Middleware/wlserver_12.1/server/lib/web
logic.jar:/u01/app/oracle/Middleware/modules/features/weblogic.server.modules_12.1.1.0.jar:/u01/app/oracle/Middleware
/wlserver_12.1/server/lib/webservices.jar:/u01/app/oracle/Middleware/modules/org.apache.ant_1.7.1/lib/ant-all.jar:
/u01/app/oracle/Middleware/modules/net.sf.antcontrib_1.1.0.0_1-0b2/lib/ant-contrib.jar:/u01/app/oracle/Middleware/
patch_wls1211/profiles/default/sys_manifest_classpath/weblogic_patch.jar:/u01/app/oracle/Middleware/patch_ocp371/
profiles/default/sys_manifest_classpath/weblogic_patch.jar:/usr/lib/jvm/java-1.6.0-openjdk-1.6.0.0.x86_64/lib/tools.
jar:/u01/app/oracle/Middleware/wlserver_12.1/server/lib/weblogic_sp.jar:/u01/app/oracle/Middleware/wlserver_12.1/
server/lib/weblogic.jar:/u01/app/oracle/Middleware/modules/features/weblogic.server.modules_12.1.1.0.jar:/u01/app/
oracle/Middleware/wlserver_12.1/server/lib/webservices.jar:/u01/app/oracle/Middleware/modules/org.apache.ant_1.7.1/
lib/ant-all.jar:/u01/app/oracle/Middleware/modules/net.sf.antcontrib_1.1.0.0_1-0b2/lib/ant-contrib.jar

As shown in the sample properties file above, the JMS interface must be configured with specific characteristics:

  1. Java Naming and Directory Interface (JNDI) connection properties: JMS server URL, Initial Context properties, Connection Factory Name, and Destination Name.
  2. JMS Queue or Topic name.
  3. Security information: JNDI authentication credentials and JMS user name and password.
  4. The java.class.path for the JMS client.

Message Parsing

The parser settings in the properties file define how the JMS text message data and header properties will be translated into database transactions and operations.

## Parser settings
parser.type=xml
xml.sourcedefs=dirdef/east.def
xml.rules=tx_rule

### transactions
tx_rule.type=tx
tx_rule.match=/t
tx_rule.subrules=op_rule
tx_rule.txid=*txid

### operations
op_rule.type=op
op_rule.match=./o
op_rule.seqid=*seqid
op_rule.timestamp=*ts
op_rule.schemaandtable=@t
op_rule.optype=@s
op_rule.optype.insertval=I
op_rule.optype.updateval=U
op_rule.optype.deleteval=D
op_rule.subrules=col_rule

### subrules.columns
col_rule.type=col
col_rule.match=./c
col_rule.index=@i
col_rule.before.value=./b/text()
col_rule.before.isnull=./bn/exists()
col_rule.after.value=./a/text()
col_rule.after.isnull=./an/exists()

To translate the JMS text message, the parser must find required information from the JMS header, system generated values, or static values. This required data includes: the transaction identifier, sequence identifier, timestamp, table name. operation type, and column data specific to a particular table name and operation type.

A sample transaction from our WebLogic JMS Queue is shown below:

<t><o t='EAST.CATEGORIES' s='I' d='2014-02-26 15:34:28.322846' p='00000000000000001159'>
<c i='0'><a><![CDATA[1]]></a></c><c i='1'><a><![CDATA[category_hardware.gif]]></a></c>
<c i='2'><a><![CDATA[0]]></a></c><c i='3'><a><![CDATA[1]]></a></c>
<c i='4'><a><![CDATA[2011-04-04:08:31:00.000000000]]></a></c><c i='5'><an/></c></o>
<o t='EAST.CATEGORIES' s='I' d='2014-02-26 15:34:28.322846' p='00000000000000001419'>
<c i='0'><a><![CDATA[2]]></a></c><c i='1'><a><![CDATA[category_software.gif]]></a></c>
<c i='2'><a><![CDATA[0]]></a></c><c i='3'><a><![CDATA[2]]></a></c>
<c i='4'><a><![CDATA[2011-04-04:08:31:00.000000000]]></a></c><c i='5'><an/></c></o>
<o t='EAST.CATEGORIES' s='I' d='2014-02-26 15:34:28.322846' p='00000000000000001654'>
<c i='0'><a><![CDATA[3]]></a></c><c i='1'><a><![CDATA[category_dvd_movies.gif]]></a></c>
<c i='2'><a><![CDATA[0]]></a></c><c i='3'><a><![CDATA[3]]></a></c>
<c i='4'><a><![CDATA[2011-04-04:08:31:00.000000000]]></a></c><c i='5'><an/></c></o>
<o t='EAST.CATEGORIES' s='I' d='2014-02-26 15:34:28.322846' p='00000000000000002610'>
<c i='0'><a><![CDATA[7]]></a></c><c i='1'><a><![CDATA[subcategory_speakers.gif]]></a></c>
<c i='2'><a><![CDATA[1]]></a></c><c i='3'><a><![CDATA[0]]></a></c>
<c i='4'><a><![CDATA[2011-04-04:08:31:00.000000000]]></a></c><c i='5'><an/></c></o>
</t>

The message is in MINXML format, so the VAM properties file must be configured accordingly. In the sample message, we can see a single transaction, identified by the <t> and </t> tags, consisting of four insert operations performed on the EAST.CATEGORIES source table. Our properties file transaction rule will be set to recognize the root element for a transaction, tx_rule.match=/t.

Operation specific data is contained within the <o> and </o> tags. Within the operation tag, the source schema and table names are identified by the tag t= and the operation type by s=. The operation type I denotes an Insert. Our properties file is configured accordingly:

To identify the root element for an operation: op_rule.match=./o

To identify the schema and table name within the operation element: op_rule.schemaandtable=@t

To identify the operation type: op_rule.optype=@s

To identify and insert operation: op_rule.optype.insertval=I

To identify and update operation: op_rule.optype.updateval=U

To identify a delete operation: op_rule.optype.deleteval=D

Use the current system time as the transaction timestamp: op_rule.timestamp=*ts

Generate a unique sequence id for each transaction record: op_rule.seqid=*seqid

Because each record contains a transaction timestamp and sequence id, we could have used the message data by specifying op_rule.timestamp=@d and op_rule.seqid=@p.

Column data is contained within the <c> and </c> tags. Column index numbers are identified by the i= tag. Column data is contained with the <a> and </a> tags; while NULL columns are identified by <a> and </an>. To properly parse our sample message, the properties column settings will be:

To identify the root element of a column: col_rule.match=./c
To identify the column index: col_rule.index=@i . The column index will be verified against the source definitions file.
To specify how to obtain before values used for updates and deletes: col_rule.before.value=./b/text()
To identify if a column before value is null: col_rule.before.isnull=./bn/exists()
To specify how to obtain after values used for updates and deletes: col_rule.after.value=./a/text()
To identify if a column before value is null: col_rule.after.isnull=./an/exists()

 XML Sourcedefs

The properties file setting, xml.sourcedefs=dirdef/east.def, specifies the location of the source definitions file that will be used for column index verification. This file contains information about the source table, columns names, and data types. The definition for the EAST.CATEGORIES table shown in our sample JMS transaction is below:

*+- Defgen version 2.0, Encoding UTF-8
*
* Definitions created/modified  2013-05-06 10:23
*
*  Field descriptions for each column entry:
*
*     1    Name
*     2    Data Type
*     3    External Length
*     4    Fetch Offset
*     5    Scale
*     6    Level
*     7    Null
*     8    Bump if Odd
*     9    Internal Length
*    10    Binary Length
*    11    Table Length
*    12    Most Significant DT
*    13    Least Significant DT
*    14    High Precision
*    15    Low Precision
*    16    Elementary Item
*    17    Occurs
*    18    Key Column
*    19    Sub Data Type
*
Database type: ORACLE
Character set ID: windows-1252
National character set ID: UTF-16
Locale: neutral
Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14
*
Definition for table EAST.CATEGORIES
Record length: 170
Syskey: 0
Columns: 6
CATEGORIES_ID     134     12        0  0  0 1 0      8      8      8 0 0 0 0 1    0 1 3
CATEGORIES_IMAGE   64     64       12  0  0 1 0     64     64      0 0 0 0 0 1    0 0 0
PARENT_ID         134     12       82  0  0 1 0      8      8      8 0 0 0 0 1    0 0 3
SORT_ORDER        134      8       94  0  0 1 0      8      8      8 0 0 0 0 1    0 0 3
DATE_ADDED        192     29      106  0  0 1 0     29     29     29 0 6 0 0 1    0 0 0
LAST_MODIFIED     192     29      138  0  0 1 0     29     29     29 0 6 0 0 1    0 0 0
End of definition
*

This data is generated via the OGG DEFGEN utility. If the message data originated from a relational database supported by Oracle GoldenGate, run DEFGEN against the source database tables to create the defines. If the message data did not originate in a relational database, DEFGEN may be run against the target database tables.

If using the target tables to generate the defines, table column data types must match the data in the message. For example, by reviewing the sample message data we can make conclusions about how the target table EAST.CATEGORIES must be created:

  1. The table has six columns.
  2. Columns 0, 2, and 3 appear to be numeric in nature.
  3. Column 1 is appears to be character data.
  4. Column 4 is a timestamp.
  5. Column 5 cannot be determined

 Generate JMS Message Definitions

OGG for Java includes a utility, GENDEF, that is used to generate a data definitions file from the properties file settings and other parser specific data definition values. The GENDEF output file is then specified in the Extract Data Pump or Replicat SOURCEDEFS parameter so the process can interpret the data contained in the OGG Trail created by the VAM.

The syntax for GENDEF is gendef [-prop {prop-file}] [-out {out-file}]. To generate a definitions file based upon the values in our sample properties file:

[oracle@oel5wl OGGJMS_112]$ ./gendef -prop ./dirprm/v_oggq_new.properties -out ./dirdef/voggq.def
Using property file: ./dirprm/v_oggq_new.properties
Outputting definition to: ./dirdef/voggq.def
Source_file = dirdef/east.def

Summary

Oracle GoldenGate Adapters for Java provides for data capture from JMS Queues and Topics. This data is parsed and formatted for storage in an OGG Trail, which can then be read by an Extract Data Pump for transmission to a downstream server running Oracle GoldenGate for apply to a relational database. This article presented information detailing the installation and configuration of the modules required for JMS capture.

Reference: Oracle GoldenGate Adapters Administrator’s Guide for Java 11g release 2 (11.2.1.0.0).

Add Your Comment