Monthly Archive: August 2019

Oracle GoldenGate: Time recorded in the Jagent Log File is Not Correct

Oracle GoldenGate: Time recorded in the Jagent Log File is Not Correct

Installed Oracle GoldenGate Management Pack. When installing the JAgent 12.2.1 on the OGG target instances, came up with a curious behavior. In the ogg_agent.log files, the time is not the local time. The time shown in the log file is 8 hours ahead although System Time is correct. The latest data under “ogg_agent.log” was as below,

[2016-12-06T08:45:29.442+00:00] [JAGENT] [NOTIFICATION] [OGGMON-20051] [com.goldengate.monitor.jagent.mbeans.impl.Agent] [tid: RMI TCP Connection(56)-146.222.96.163] [ecid: 0000LZEiZuLFKAwtOCq2Td1OHHUM00000A,0] DerivedMBean is getting build... 

Later came to know that the cause of the issue was the incorrect setting or Java parameter missed in the Java command.

Please follow the below steps to resolve the issue,

1. edit jagent parameter file

2. add -Duser.timezone=Hongkong in command

For example, you will be having the below parameters in the Jagent parameter file,

COMMAND java -Dconfig.dir=/u05/app/goldengate/ggagent/agent1/cfg -Djava.util.logging.config.class=oracle.core.ojdl.logging.LoggingConfiguration
-Doracle.core.ojdl.logging.config.file=/u05/app/goldengate/ggagent/agent1/cfg/logging-config.xml -Doracle.core.ojdl.logging.componentId=JAGENT -jar
-Xms512m -Xmx1024m /u05/app/goldengate/ogg_agent1/oggmon/ogg_agent/dirjar/jagent.jar
Program is 'java', arguments are '-Dconfig.dir=/u05/app/goldengate/ggagent/agent1/cfg
-Djava.util.logging.config.class=oracle.core.ojdl.logging.LoggingConfiguration -Doracle.core.ojdl.logging.config.file=/u05/app/goldengate/ggagent/agent1
/cfg/logging-config.xml -Doracle.core.ojdl.logging.componentId=JAGENT -jar -Xms512m -Xmx1024m /u05/app/goldengate/ogg_agent1/oggmon/ogg_agent
/dirjar/jagent.jar'


So you need to add this -Duser.timezone=Hongkong in the first line after the COMMAND java

Note: Respective Timezones should be added according to the issue faced.

3. Restart the JAgent process from GGSCI.

GGSCI> stop JAgent

GGSCI> start JAgent

Remote Capture – OGG-00446 Could Not Find Archived Log

Remote Capture – OGG-00446 Could Not Find Archived Log

Remote Extract process is failing with the below error,

OGG-00446 Oracle GoldenGate Capture for Oracle, ext1.prm: Could not find archived log

Source Database and Target Database are installed in a separate server. Oracle GoldenGate is installed in a separate server. So, all the three are in their respective servers individually.

Database is running in Archive log mode. Oracle Client 11.2.0.1.0 is installed in the server where OGG is installed.

When using the Remote capture, we need to use the below parameter in the Extract parameter file.

TRANLOGOPTIONS DBLOGREADER

This parameter is not supported for the Oracle client 11.2.0.1.0. Here the Client version installed in 11.2.0.1.0

If one wants to be able to access archive logs from a different Oracle installation and using Classic Extract, then we need to use TRANLOGOPTOINS DBLOGREADER parameter. This does not imply or mean we are using ASM at the remote installation, it is just a means for OGG to read the remote archive logs.

The client version 11.2.0.1.0 is unable to use TRANLOGOPTOINS DBLOGREADER which is what is needed.

Install Oracle Client 11.2.0.4 and use the parameter TRANLOGOPTIONS DBLOGREADER in the Extract process parameter file. Restart the Extract process.

OGG-15051 Java or JNI exception: hadoop.hbase.HTableDescriptor.addFamily in Cloudera

OGG-15051 Java or JNI exception: hadoop.hbase.HTableDescriptor.addFamily in Cloudera

Oracle GoldenGate Replicat process for Cloudera HBase abend with the below error.

                                         Oracle GoldenGate Delivery
                         Version 12.2.0.1.160823 OGGCORE_OGGADP.12.2.0.1.0_PLATFORMS_170324.1532
                             Linux, x64, 64bit (optimized), Generic on Mar 24 2017 17:11:07

2017-10-12 05:01:44 INFO OGG-06511 Using following columns in default
map by name: ROW_ID, LAST_UPD, PAR_ROW_ID, BU_ID, NAME, CUST_STAT_CD,INTEGRATION_ID, OU_NUM,OU_TYPE_CD,
PAR_OU_ID, PR_ADDR_ID, ACCNT_TYPE_CD, PROCESS_TIMESTAMP,X_BILL_TYPE_BT, STAT_REASON_CD.
2017-10-12 05:01:44 INFO OGG-06510 Using the following key columns for
target table SCHEMA.TABLE: ROW_ID.
2017-10-12 05:01:59 INFO OGG-01021 Command received from GGSCI: STATS.
Oct 12, 2017 5:02:56 AM org.apache.hadoop.conf.Configuration
warnOnceIfDeprecated
INFO: hadoop.native.lib is deprecated. Instead, use io.native.lib.available
Exception in thread "main" java.lang.NoSuchMethodError:
org.apache.hadoop.hbase.HTableDescriptor.addFamily(Lorg/apache/hadoop/hbase/HColumnDescriptor;)Lorg/apache/hadoop/hbase/HTableDescriptor;
at oracle.goldengate.handler.hbase.operations.HBase1TableWriter.createTable(HBase1TableWriter.java:254)
at oracle.goldengate.handler.hbase.operations.HBase1TableWriter.open(HBase1TableWriter.java:96)
at oracle.goldengate.handler.hbase.operations.HBase1Writer.getTableWriter(HBase1Writer.java:167)
at oracle.goldengate.handler.hbase.operations.HBase1Writer.put(HBase1Writer.java:110)
at oracle.goldengate.handler.hbase.operations.HBaseInsertDBOperation.processOp(HBaseInsertDBOperation.java:78)
at oracle.goldengate.handler.hbase.HBaseHandler.processOp(HBaseHandler.java:468)
at oracle.goldengate.handler.hbase.HBaseHandler.transactionCommit(HBaseHandler.java:411)
at oracle.goldengate.datasource.DsEventManager$2.send(DsEventManager.java:422)
at oracle.goldengate.datasource.DsEventManager$EventDispatcher.distributeEvent(DsEventManager.java:231)
at oracle.goldengate.datasource.DsEventManager.fireTransactionCommit(DsEventManager.java:430)
at oracle.goldengate.datasource.AbstractDataSource.fireTransactionCommit(AbstractDataSource.java:506)
at oracle.goldengate.datasource.UserExitDataSource.commitActiveTransaction(UserExitDataSource.java:1546)
at oracle.goldengate.datasource.UserExitDataSource.commitTx(UserExitDataSource.java:1469)

2017-10-12 05:02:56 ERROR OGG-15051 Java or JNI exception:
java.lang.NoSuchMethodError:
org.apache.hadoop.hbase.HTableDescriptor.addFamily(Lorg/apache/hadoop/hbase/HColumnDescriptor;)Lorg/apache/hadoop/hbase/HTableDescriptor;.

The root cause is Compatibility Issue. I came across a bug for this issue.

Bug 25591766 – HBase 1.2: java.lang.NoSuchMethodError: ….addFamily

In the bug it has been mentioned to set the below parameter, but already the below parameter is set and still facing the same issue.

gg.handler..hBase98Compatible=true.

This is a well known compatibility problem with Cloudera HBase. The root cause is that the Cloudera HBase client libraries broke binary compatibility with the Apache HBase libraries. You must be on OGGBD 12.3.0.1.x or earlier.

What the problem is and how to work around it, is explained in the documentation:

https://docs.oracle.com/goldengate/bd123010/gg-bd/GADBD/GUID-A6C0DEC9-480F-4782-BD2A-54FEDDE2FDD9.htm#GADBDGUID-8873BA41-35B8-4307-B406-EA9835DAA477

Go to section 1.2.2.2

The OGGBD 12.3.1.1.0 release provided improved support by resolving the offending HBase method using Java reflection. Therefore, if you are ready to move to OGGBD 12.3.1.1.0 or later this issue is resolved automatically.

See the documentation:

https://docs.oracle.com/goldengate/bd123110/gg-bd/GADBD/using-hbase-handler.htm#GADBD-GUID-1A9BA580-628B-48BD-9DC0-C3DF9722E0FB

Go to section 5.6.5

Cheers 🙂

OGG 12.2 – Extract Abend – OGG-00458 Cannot Find Metadata Property

OGG 12.2 – Extract Abend – OGG-00458 Cannot Find Metadata Property

Extract Process Abends with the below error,

2016-07-05 00:03:34 ERROR OGG-00458 Cannot find metadata property G41. DDL metadata [,G1='alter table "schema"."table" shrink space CHECK ',,C6='GGS.GGS_MARKER',,C5='6399704',,C2='4377220081',,S='99751',,W='schema',,X='table',,Y='TABLE',,Z='ALTER',,A1='104',,A1='104',,..........

Cause of the Issue was, by default “auto space advisor” is enabled in the Oracle Database. This advisor, shrinks the space of large tables and this causes the Oracle GoldenGate Extract process to get abended with the below error,

2016-07-05 00:03:34 ERROR OGG-00458 Cannot find metadata property G41. DDL metadata [,G1='alter table "schema"."table" shrink space CHECK ',,C6='GGS.GGS_MARKER',,C5='6399704',,C2='4377220081',,S='99751',,W='schema',,X='table',,Y='TABLE',,Z='ALTER',,A1='104',,A1='104',,..........

This is a Bug,

Bug 23154554 - Extract abends: OGG-00458 Cannot find metadata property G23. DDL metadata

But there is a Workaround for this, you can disable the “auto space advisor” feature which is the cause for triggering shrink space for large tables.

Steps to disable the “auto space advisor”

1. Check the status of the “auto space advisor”.

SQL> SELECT client_name, status FROM dba_autotask_client;
set lines 180 pages 1000
col client_name for a40
col attributes for a60
select client_name, status,attributes,service_name from dba_autotask_client;

2. Execute the below to disable the “auto space advisor”.

BEGIN
DBMS_AUTO_TASK_ADMIN.disable(
client_name => 'auto space advisor',
operation => NULL,
window_name => NULL);
END;
/

3. Check if the “auto space advisor” is disabled.

set lines 180 pages 1000
col client_name for a40
col attributes for a60
select client_name, status,attributes,service_name from dba_autotask_client;

Restart the Extract process after disabling the “auto space advisor”.

Note: Please check with your Oracle Core DBA before disabling this “auto space advisor”..

Oracle GoldenGate Integrated Replicat slow and does not use BATCHSQL

Oracle GoldenGate Integrated Replicat slow and does not use BATCHSQL

Oracle GoldenGate Integrated Replicat slow and does not use BATCHSQL.

The BATCHSQL is specified , though in the report file we do not see any

batchsql statistics:
BATCHSQL statistics:
Batch operations: 0
Batches: 0
Batches executed: 0
Queues: 0
Batches in error: 0
Normal mode operations: 11055655
Immediate flush operations: 0
PK collisions: 0
UK collisions: 0
FK collisions: 0
Thread batch groups: 0
Commits: 0
Rollbacks: 0
Queue flush calls: 0
Ops per batch: N/A
Ops per batch executed: N/A
Ops per queue: N/A
Parallel batch rate: N/A

and the replicat is very slow. Lag is 7 mins but could be max 30 secs

From the IR healthcheck report we can see that the BATCHSQL is enabled:

BATCHSQL = Y

Though from the AWR report we can see that by the goldengate operations the rows/exec is ~1 like:

Executions 	Rows Processed 	Rows per Exec 	Elapsed Time (s) 	SQL Module 		SQL  Text
============== ============== 	============= 	================ 	==========     	=================================
565,577         670,971 	0.19    	1,111.16 	 	GoldenGate 	INSERT /*+ restrict_all_ref_c...
365,150 	361,452 	0.99 		586.7872.7 	 	GoldenGate 	INSERT /*+ restrict_all_ref_c...
201,967 	215,429 	0.95 		482.7754.6 	 	GoldenGate 	INSERT /*+ restrict_all_ref_c...
201,195 	213,216 	0.87 		438.8554.5 	 	GoldenGate 	INSERT /*+ restrict_all_ref_c...

This is because, BATCHSQL iS directly related to the EAGER_SIZE when used in the Integrated Replicat. This is very important to note in here. We should always not assume as BATCHSQL will always improve the performance. Sometimes it degrades the performance as well. When used with Integrated Replicat process, we need to be even more careful.

For Integrated Replicat, the OGG BATCHSQL statistics are never maintained. It will always show as normal mode in the OGG report file whether BATCHSQL is enabled or not. The only way to determine if BATCHSQL is in use on the IR inbound server is to use AWR and look at the AWR SQL Executions report. If the Rows per Exec > 1, then BATCHSQL is in operation.

You can tell if BATCHSQL is configured (for the inbound server) by looking in the healthcheck summary at Replicat parameters section

Integrated Replicat
key parameters (Details).

APPLY_NAME Replicat
Name PARALLELISM MAX_PARALLELISM COMMIT_SERIALIZATION EAGER_SIZE BATCHSQL BATCH_SQL_MODE MAX_SGA_SIZE
OPTIMIZE_PROGRESS_TABLE
OGG$ROACDW ROACDW 4 50 DEPENDENT_TRANSACTIO NS 9500 Y INFINITE N

A bulk load implies that a single transaction was performed. If this is the case, then the inbound server will apply in EAGER mode. Meaning the transaction will begin applying in the inbound server before the entire transaction is received by the inbound server. Eagerly applied transactions cannot take advantage of BATCHSQL.

BATCHSQL iS directly related to the EAGER_SIZE when used in the Integrated Replicat. The default EAGER_SIZE of the in Oracle GoldenGate 12.1.x is 9500 and from OGG 12.2 is 15100.

So, if the number of operations in a Transaction is greater than 9500, then BATCHSQL will have no effect. So, the number of LCR’s should be below or lesser than 9500 (12.1) and 15000 (12.2) so that BATCHSQL will be used by the Integrated Replicat process.

If the number of transactions is greater then 9500 (12.1) or 15000 (12.2), then the Integrated Replicat will not apply the transactions in parallel and will apply it in a serialized way and hence we could see the “Rows per Exec = 1” in the “SQL ordered by executions” in AWR.

For example, if in your case, if you have most of the transactions having operations nearing to 1 Lakh, then it is better to increase the EAGER_SIZE to 100000. But please do remember, if you increase the EAGER_SIZE, you need to have sufficient amount of memory too. So, do increase the STREAMS_POOL_SIZE also.

Once you increase the EAGER_SIZE to 100000. Create a table and insert 90000 records and commit the transaction. Then update 80000 records and commit the transaction. Generate the AWR report and check if the “Rows per Exec > 1” in the “SQL ordered by executions”

Hope this clearly explained how to use BATCHSQL in Integrated Replicat process.

Cheers 🙂