The Oracle GoldenGate Adapter for Coherence

You have successfully accessed Oracle GoldenGate documentation as a prerequisite for installing the software.

Click Download.

You have successfully installed Oracle Coherence 12c.

Make sure you are positioned in the default directory of the oracle user (/home/oracle.) If necessery issue the command cd ~ while logged in as oracle.

Host01 - Linux

[oracle@host01 ~]$ cd ~ [oracle@host01 ~]$ unzip /stage/cgga.zip   Archive: /stage/cgga.zip inflating: cgga/cs-cap.ggsci inflating: cgga/cs-cgga.ggsci inflating: cgga/cs-stop.ggsci creating: cgga/dirprm/ ... many lines omitted for clarity ... inflating: cgga/workspace/CacheStoreDemo/build.xml inflating: cgga/workspace/CacheStoreDemo/.project inflating: cgga/workspace/CacheStoreDemo/setup.sql inflating: cgga/workspace/CacheStoreDemo/cs-coherence-cache-config.xml

In this example the file cgga.zip was saved in the /stage directory. If you downloaded it in a different directory, replace /stage with the directory in which you saved cgga.zip.

Unzipping the cgga.zip file creates the directory cgga under the oracle user default directory (/home/oracle/cgga.)

For this OBE to work, several environment variables must be set. In particular:

Variable	Purpose
ORACLE_BASE	Root directory for all Oracle RDBMS files
ORACLE_HOME	Below the ORACLE_BASE directory, the files for each database
ORACLE_SID	Oracle System Id which uniquely identifies a particular RDBMS instance
LD_LIBRARY_PATH	Location where all shared objects are stored
ANT_HOME	Points to the directory where Apache Ant is installed
JAVA_HOME	Points to the directory where Oracle Java JDK is installed
CGGA_DEMO_HOME	Points to the directory where the files needed by this OBE are installed
GG_HOME	Points to the directory where Oracle GoldenGate is installed
PATH	Environmental variable that tells the shell which directories to search for executable files in response to commands issued by a user. For this OBE to work, the shell must be able to locate the Ant executable and the Oracle GoldenGate executable

Edit the file /home/oracle/.bashrc (Note the dot before the file name.) In the Unix/Linux OS, a file named with a dot prefix is a hidden file. Use either vi or gedit to edit the .bashrc file.

Host01 - Linux

[oracle@host01 ~] vi .bashrc # .bashrc ... # non relevant content removed export ANT_HOME=/u01/app/apache-ant-1.9.4 export JAVA_HOME=/usr/java/jdk1.7.0_21 ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1 export ORACLE_SID=oggcoh export LD_LIBRARY_PATH=$JAVA_HOME/jre/lib/amd64/server:$ORACLE_HOME/lib:$GG_HOME:$LD_LIBRARY_PATH export CGGA_DEMO_HOME=$HOME/cgga export GG_HOME=$HOME/goldengate-v11.2.1.0.3 export PATH=$JAVA_HOME/bin:$ANT_HOME/bin:$GG_HOME:$PATH

Either source the .bashrc file (source .bashrc) or exit and restart a new shell to make sure that the new environment variables are read by the bash shell.

Confirm that key environment variables point to their correct directories and such.


Host01 - Linux

[oracle@host01 ~]$ echo ANT_HOME /opt/ant [oracle@host01 ~]$ echo $JAVA_HOME /usr/java/jdk1.7.0_21 [oracle@host01 ~] $ echo $GG_HOME /home/oracle/goldengate-v11.2.1.0.3 [oracle@host01 ~]$ echo $ CGGA_DEMO_HOME /home/oracle/cgga [oracle@host01 ~] $

The file cs-cgga.properties, located under /home/oracle/cgga/dirprm, is the crucial file which instructs the Oracle GoldenGate Java adapter on various elements of the Java subsystem, such as files and directory locations of the various components (TopLink and Coherence libraries/jar file, OBE classes, etc.) Edit the file and make sure that all elements included in the javawriter.bootoptions variable exist and are readable.

Host01 - Linux

[oracle@host01 ~]$ vi cgga/dirprm/cs-cgga.properties # ==================================================================== # List of active event handlers. handlers not in the list are ignored. # ==================================================================== gg.handlerlist=cache # ==================================================================== # Coherence cache updater # ==================================================================== gg.handler.cache.type=oracle.toplink.goldengate.CoherenceAdapter # print plain text output (custom velocity template) gg.handler.txtfile1.type=singlefile gg.handler.txtfile1.format=text gg.handler.txtfile1.mode=op gg.handler.txtfile1.file=./dirout/csdemo-output.txt # ==================================================================== # Native JNI library properties # ==================================================================== goldengate.userexit.nochkpt=true goldengate.userexit.writers=javawriter goldengate.log.logname=./dirout/csdemo-userexit-jcoh # ====================================== javawriter.bootoptions=-Djava.class.path=./dirprm:/u01/app/oracle/product/11.2.0/dbhome_1/jdbc/lib/ojdbc6.jar: ./ggjava/ggjava.jar:/home/oracle/Oracle/Middleware/Oracle_Home/coherence/lib/coherence.jar: /home/oracle/Oracle/Middleware/Oracle_Home/oracle_common/modules/javax.persistence_2.0.0.0_2-0.jar: /home/oracle/Oracle/Middleware/Oracle_Home/oracle_common/modules/oracle.toplink_12.1.2/eclipselink.jar: /home/oracle/Oracle/Middleware/Oracle_Home/oracle_common/modules/oracle.toplink_12.1.2/toplink-grid.jar: /home/oracle/cgga/workspace/CacheStoreDemo/bin -Xmx32M -Xms32M -Dtoplink.goldengate.persistence-unit=employee -Dlog4j.configuration=debug-log4j.properties -Dtangosol.coherence.distributed.localstorage=false -Dtangosol.coherence.cacheconfig=/home/oracle/cgga/workspace/CacheStoreDemo/cs-coherence-cache-config.xml -Dtangosol.coherence.localhost=127.0.0.1 -Dtangosol.coherence.ttl=0 -Dtangosol.coherence.clusterport=4617 [oracle@host01 dirprm]$ ls /u01/app/oracle/product/11.2.0/dbhome_1/jdbc/lib/ojdbc6.jar /u01/app/oracle/product/11.2.0/dbhome_1/jdbc/lib/ojdbc6.jar

IMPORTANT! The last entry (which begins with javawriter.bootoptions=) in the cs-cgga.properties file is only one long line, even if it is shown, for clarity, across several rows.

The javawriter.bootoptions property can be thought of as having portions or sections per product area, some of which are:


Product	Values added
Database	/u01/app/oracle/product/11.2.0/dbhome_1/jdbc/lib/ojdbc6.jar
Coherence Proper	/home/oracle/Oracle/Middleware/Oracle_Home/coherence/lib/coherence.jar
Coherence cache configuration and runtime	-Dtangosol.coherence.distributed.localstorage=false -Dtangosol.coherence.cacheconfig=/home/oracle/cgga/workspace/CacheStoreDemo/cs-coherence-cache-config.xml -Dtangosol.coherence.localhost=127.0.0.1 -Dtangosol.coherence.ttl=0 -Dtangosol.coherence.clusterport=4617
Coherence Toplink and JPA	/home/oracle/Oracle/Middleware/Oracle_Home/oracle_common/modules/javax.persistence_2.0.0.0_2-0.jar /home/oracle/Oracle/Middleware/Oracle_Home/oracle_common/modules/oracle.toplink_12.1.2/eclipselink.jar /home/oracle/Oracle/Middleware/Oracle_Home/oracle_common/modules/oracle.toplink_12.1.2/toplink-grid.jar
Toplink JPA	-Dtoplink.goldengate.persistence-unit=employee
GoldenGate Adapter	./ggjava/ggjava.jar ./dirprm
JVM and log4j	-Xmx32M -Xms32M -Dlog4j.configuration=debug-log4j.properties





It is crucial that no new line characters or spaces appear at the end of the logical rows otherwise the Java adapter will not work.

Save the file and exit the editor. To verify that all elements appearing in the javawriter.bootoptions variable exist and are accessible, display the content of the cs-cgga.properties file to the screen (using the cat command)and then highlight with the mouse each component of the variable and use the ls command to make sure that it exists on the file system.

Host01 - Linux

[oracle@host01 ~]$ cd ~/cgga/dirprm [oracle@host01 dirprm]$ ls /u01/app/oracle/product/11.2.0/dbhome_1/jdbc/lib/ojdbc6.jar /u01/app/oracle/product/11.2.0/dbhome_1/jdbc/lib/ojdbc6.jar [oracle@host01 dirprm]$ ls /home/oracle/Oracle/Middleware/Oracle_Home/coherence/lib/coherence.jar /home/oracle/Oracle/Middleware/Oracle_Home/coherence/lib/coherence.jar [oracle@host01 dirprm]$ ls /home/oracle/Oracle/Middleware/Oracle_Home/oracle_common/modules/javax.persistence_2.0.0.0_2-0.jar /home/oracle/Oracle/Middleware/Oracle_Home/oracle_common/modules/javax.persistence_2.0.0.0_2-0.jar [oracle@host01 dirprm]$ ls /home/oracle/Oracle/Middleware/Oracle_Home/oracle_common/modules/oracle.toplink_12.1.2/eclipselink.jar /home/oracle/Oracle/Middleware/Oracle_Home/oracle_common/modules/oracle.toplink_12.1.2/eclipselink.jar [oracle@host01 dirprm]$ ls /home/oracle/Oracle/Middleware/Oracle_Home/oracle_common/modules/oracle.toplink_12.1.2/toplink-grid.jar /home/oracle/Oracle/Middleware/Oracle_Home/oracle_common/modules/oracle.toplink_12.1.2/toplink-grid.jar [oracle@host01 dirprm]$ ls /home/oracle/cgga/workspace/CacheStoreDemo/bin cachestore META-INF [oracle@host01 dirprm]$

The file jdbc.properties, located under /home/oracle/cgga/workspace/CacheStoreDemo, contains the JDBC parameters used to connect to the Oracle RDBMS.

Change directory to /home/oracle/cgga/workspace/CacheStoreDemo and edit the jdbc.properties file. Make sure that all JDBC parameters are correctly set and correspond to a valid Oracle system user for your RDBMS. Varify the listener port and the Oracle System Id for your instance.

Host01 - Linux

[oracle@host01 ~]$ cd ~/cgga/workspace/CacheStoreDemo [oracle@host01 CacheStoreDemo]$ vi jdbc.properties #************************************* #Specify JDBC Connection Details here* #************************************* # Database User db.user=system # DB Password db.password=oracle # The database host db.host=localhost # The database SID db.sid=oggcoh # The database listener port db.port=1521 jdbc.url=jdbc:oracle:thin:@${db.host}:${db.port}:${db.sid} jdbc.driver=oracle.jdbc.OracleDriver

Save the file and exit the editor.

The file persistence.xml, located under /home/oracle/cgga/workspace/CacheStoreDemo/bin/META-INF, contains properties used to connect to the Oracle RDBMS.These properties are not modified by any script and may need to be changed to match your environment.


Change directory to /home/oracle/cgga/workspace/CacheStoreDemo/bin/META-INF and edit the persistence.xml file. Make sure that all properties are correctly set and correspond to a valid values for your environment.  Pay particular attention to the jdbc.url, jdbc.user and jdbc.password properties.

Host01 - Linux

[oracle@host01 ~]$ cd ~/cgga/workspace/CacheStoreDem/bin/META-INF [oracle@host01 META-INF]$ vi persistence.xml #************************************* <?xml version="1.0" encoding="UTF-8"?> <persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"> <persistence-unit name="employee" transaction-type="RESOURCE_LOCAL"> <class>cachestore.model.Employee</class> <properties> <!-- Default Login using Oracle for EclipseLink's internal connection pool --> <property name="javax.persistence.jdbc.driver" value="oracle.jdbc.OracleDriver" /> <property name="javax.persistence.jdbc.url" value="jdbc:oracle:thin:@localhost:1521:oggcoh" /> <property name="javax.persistence.jdbc.user" value="csdemo" /> <property name="javax.persistence.jdbc.password" value="csdemo" /> <!-- Minimal connections in internal pool --> . . . </properties> </persistence-unit> </persistence>.

Save the file and exit the editor.

Change directory to /home/oracle/cgga/workspace/CacheStoreDemo and invoke Ant with init as its target. The *.ggsci will be copied into $GG_HOME (the install directory for Oracle GoldenGate) and the *.prm files and the *.properties file will be copied in the dirprm directory under $GG_HOME.

Host01 - Linux

[oracle@host01 ~]$ cd ~/cgga/workspace/CacheStoreDemo/ [oracle@host01 CacheStoreDemo]$ ant init Buildfile: /home/oracle/cgga/workspace/CacheStoreDemo/build.xml define: init: [copy] Copying 3 files to /home/oracle/goldengate-v11.2.1.0.3 [copy] Copying 4 files to /home/oracle/goldengate-v11.2.1.0.3/dirprm  BUILD SUCCESSFUL Total time: 0 seconds [oracle@host01 CacheStoreDemo]$

While still located in the /home/oracle/cgga/workspace/CacheStoreDemo directory, invoke Ant again, this time using createusers as its target. Ant will use the values found in jdbc.properties for the DBA user (system) and for its password (oracle). However, Ant will allow you to confirm their values by typing Enter. You can override the values found in jdbc.properties by typing an alternative user and password instead of Enter. 

Host01 - Linux

[oracle@host01 CacheStoreDemo] ant createusers Buildfile: /home/oracle/cgga/workspace/CacheStoreDemo/build.xml define: init: createusers: [input] Please enter the dba user [system]: [system] [input] Please enter the dba password [oracle] [oracle] [sql] Executing commands [sql] 12 of 12 SQL statements executed successfully BUILD SUCCESSFUL Total time: 7 seconds [oracle@host01 CacheStoreDemo]$

The Ant createusers target creates two database users, CSDEMO, with password csdemo and NOTCSDEMO, with password notcsdemo. In addition, a few privileges are granted to those users.

Much of the provisioning of the database is handled by the ant scripts in the prior section.  Correctly setting logging is key to the success of the HotCache adapter. 


In particular implementations will need to:

• Enable Forced logging
• Enable Schema level logging
• Enable table level logging.

See Chapter 5 of the Oracle GoldenGate for Oracle Databases Installation and Setup Guide for more details and a complete list of possibly logging related options.


Note that is you using Oracle Database 11.2.0.4, 12.1.0.2 or later you need only issue a single command.
Specifically:
$ sqlplus system/password - suitably privileged account
SQLplus > ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;
SQLplus > ...

This only needs to be done once, and in a RAC environment, will take effect on all instances. It can be done without bouncing the database instance, and you’ll want to use SCOPE=BOTH or update the init.ora file to ensure that when the database is bounced, the setting remains in effect.

If this parameter is not set and/or missing privileges (exec DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE ( '<appropriate user>' ); ), you may see error, ORA-26947 or ORA-26945 when attempting to use any of the GoldenGate functionality.


For prior versions:

1. Enabled forced logging:
   $ sqlplus system/password - suitably privileged account
   SQLplus > ALTER DATABASE FORCE LOGGING;
   SQLplus > ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
   SQLplus >ALTER SYSTEM SWITCH LOGFILE;
   SQLplus > . . .
   
   
2. Enable Schema level logging
   $ cd Golden Gate Install directory
   $ ./ggsci
   GGSCI> DBLogin UserID <login>, Password <pswd>
   GGSCI> add SCHEMATRANSDATA <schema.1>
   GGSCI> Add SCHEMATRANSDATA <schema.2>
   . . .
   
   
3. Enable Table level logging
   $ cd Golden Gate Install directory
   $ ./ggsci
   GGSCI> DBLogin UserID <login>, Password <pswd>
   GGSCI> Add TranData <OWNER>.<TABLE1>
   GGSCI> Add TranData <OWNER>.<TABLE2>
   . . .
   
   

Host01 - Linux

[oracle@host01 CacheStoreDemo]$ cd scripts [oracle@host01 scripts]$ pwd /home/oracle/cgga/workspace/CacheStoreDemo/scripts [oracle@host01 scripts]$ ./cs-demo-start.sh

After you invoke the shell script which starts the demo application, the following actions are performed:

• An EMPLOYEE table is created in the csdemo user schema.
• A GoldenGate extract process "cs-cap"(ture) is created to monitor changes to all tables in the csdemo schema and produce a trail file.
• A GoldenGate extract process "cs-cgga" is created to monitor the trail file produced by the cs-cap extract.

The following windows are opened:

• "CacheStore Demo-CacheServer Console" -- the CacheServer that will be updated with database changes
• "CacheStore Demo-Extract Console" -- the console output of the cs-cap process.
• "CacheStore Demo-CGGA Console "-- the console of the Coherence GoldenGate Adapter processing events consumed by the cs-cgga extract process.
• "CacheStore Demo-Employee Cache Monitor" -- an Swing application that supports the creation, modification, and deletion of Employee objects in Coherence cache backed by a CacheStore.
• "CacheStore Demo-Cache Monitor" -- the console of "CacheStore Demo-Employee Cache Monitor" Swing application.
• "CacheStore Demo-Third Party SQLplus" -- a terminal running SQLplus logged into the database with the notcsdemo user id

Note that it can take up to 15-20 seconds for the application to start. The last window to appear is the "CacheStore Demo Employee-Cache Monitor", which is the GUI screen which allows for getting and putting objects into the Coherence cache. When this window appears on screen you can start interacting wiht the application. Confirm all windows exist before moving on. 

Confirm that Golden Gate has started by starting ggsci and issuing the info all command.
In the  terminal window used to start the scripts or in a new terminal window:



Host01 - Linux

[oracle@host01 ~] cd $GG_HOME [oracle@host01 goldengate-v11.2.1.0.3] ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (host01) 1> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING CS-CAP 00:00:00 00:00:10 EXTRACT RUNNING CS-CGGA 00:00:00 00:00:04 GGSCI (host01) 2> exit [oracle@host01 ~]


Note the three running processes Manager and the two extracts.

In the CacheStoreDemo-Cache Monitor window examine the Coherence default cache server output

Near the bottom you should see, when all startup is complete a log message resembling:
[java] 2014-08-29:... Oracle Coherence GE 12.1.2.0.0. . .
Member 3 joined Service TopLinkGridJPA...

The demostration has now successfully started.

Using the "CacheStore Demo Employee-Cache Monitor" window insert a new Employee entry with ID = 1, first name = 'John' and surname = 'Doe' then click on the "Put" button. The Event Log should immediately report the new entry stored in the Coherence cache.



Verify that the new entry has been checkpointed to the Oracle Employee table by selecting everything (the asterisk sign) from the Employee table. Enter the SQL select statement "select * from csdemo.employee;" in sqlplus.The Employee data should display in the sqlplus window.

Add employee Jane Roe, with Id 2, using SQL directly in the sqlplus window. Enetr the SQL statement "insert into csdemo.employee values (2,'Jane','Roe');" and then commit the transaction by entering "commit;"

Almost immediately, the Event Log text area in the "CacheStore Demo Employee-Cache Monitor" window should display the new entry, confirming that the Coherence cache has been updated via the Oracle GoldenGate Adapter.



To further verify that the Coherence cache has been updated, select the "CacheStore Demo Employee-Cache Monitor" window and enter the Employee Id 2 in the ID field, then click the "Get" button. The remaining fields in the form (Firstname and Lastname) should be populated with the Jane Roe entry.

Using sqlplus update the first name of employee number 1 from John to William. Enter the SQL statement "update csdemo.employee set firstname = 'William' where id = 1;" and then commit the transaction by entering "commit;"



Almost instantaneously, the Event Log text area in the "CacheStore Demo Employee-Cache Monitor" window should display the UPDATE event.

Using the "CacheStore Demo Employee-Cache Monitor" window enter the number one (1) into the ID field and click the "Remove" button.



The Event Log text area in the "CacheStore Demo Employee-Cache Monitor" window should display the DELETED event.

Using sqlplus delete the entry for the Employee whose last name is 'Roe'. Enter the SQL statement "delete from csdemo.employee where lastname = 'Roe';" and then commit the transaction by entering "commit;"



Almost instantaneously, the Event Log text area in the "CacheStore Demo Employee-Cache Monitor" window should display the DELETED event.

All entries have now been deleted from the Coherence cache and the Oracle RDBMS table. To verify that, using sqlplus, select all rows from the Employee table. sqlplus should display the message "no rows selected" to signal that the Employee table is empty.