Skip to content

Instantly share code, notes, and snippets.

@rmoff
Last active January 11, 2023 09:49
Show Gist options
  • Save rmoff/5a78bdcd0cf24941ed4b30b604fb44ad to your computer and use it in GitHub Desktop.
Save rmoff/5a78bdcd0cf24941ed4b30b604fb44ad to your computer and use it in GitHub Desktop.

This is a set of instructions for use with the blog article Streaming data from Oracle using Oracle GoldenGate and Kafka Connect.

@rmoff / September 15, 2016


Preparing the Database for Oracle GoldenGate

First we prepare the database for Oracle GoldenGate. Connect as SYSDBA:

sqlplus / as sysdba

and then run the following:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE FORCE LOGGING;
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;
EXIT

We'll have two GoldenGate processes running; standard Oracle GoldenGate for the Extract (and as already installed in BigDataLite at /u01/ogg) and then Oracle GoldenGate for Big Data (as installed at /u01/ogg-bd).

For the initial OGG process we first configure the Manager. All OGG configuration is done from the command line and I'd strongly recommend using rlwrap so that you get commandline history and searching (via Ctrl-R).

Configure Oracle GoldenGate Manager Processes

The first thing we'll configure is the OGG Manager:

cd /u01/ogg
rlwrap ./ggsci

At the GGSCI prompt enter EDIT PARAM MGR and in the resulting vi edit session add the following:

DynamicPortList 20000-20099
PurgeOldExtracts ./dirdat/*, UseCheckPoints, MinKeepHours 2
Autostart Extract E*
AUTORESTART Extract *, WaitMinutes 1, Retries 3

Save and exit vi, and then enter at the GGSCI prompt

start mgr

You can check that it's running using the info command:

GGSCI (bigdatalite.localdomain) 1> info mgr

Manager is running (IP port bigdatalite.localdomain.7811, Process ID 23018).

Do the same for the second OGG process, which is the Oracle GoldenGate for Big Data (OGG-BD):

cd /u01/ogg-bd/
rlwrap ./ggsci

At the GGSCI prompt enter EDIT PARAM MGR and check that it is set to

PORT 7810

Close the edit session and start the manager

GGSCI (bigdatalite.localdomain) 3> start mgr
Manager started.


GGSCI (bigdatalite.localdomain) 4> info mgr

Manager is running (IP port bigdatalite.localdomain.7810, Process ID 24416).

Define Oracle GoldenGate Extract

Return to the original ggsci prompt (that you launched from /u01/ogg). Double check that you are on the right one, otherwise this won't work.

Now run the following to set up the schema logging. Note that we're connecting to the pluggable database instance orcl:

DBLOGIN USERID SYSTEM@localhost:1521/orcl PASSWORD welcome1
ADD SCHEMATRANDATA SOE ALLCOLS

Now register the integrated Extract process, in the Container Database - this'll take a minute or two to complete:

DBLOGIN USERID SYSTEM PASSWORD welcome1
REGISTER EXTRACT EXT1 DATABASE  CONTAINER (ORCL)

We can now define the extract itself:

ADD SCHEMATRANDATA ORCL.SOE
ADD EXTRACT EXT1, INTEGRATED TRANLOG, BEGIN NOW

Write a trail file for the extract

ADD EXTTRAIL ./dirdat/lt EXTRACT EXT1

Specify parameters for the extract:

EDIT PARAM EXT1

In the edit session paste:

EXTRACT EXT1
USERID SYSTEM, PASSWORD welcome1
EXTTRAIL ./dirdat/lt
SOURCECATALOG ORCL
TABLE SOE.*;

Save and close the file. Now we're ready to start the extract.

From the GGSCI prompt issue:

START EXT1

and check that it's running:

INFO EXT1

if you get STARTING then re-issue the command until it says RUNNING - it may take a few minutes. If you get ABENDED then head to /u01/ogg/ggserr.log to see details of the problem.

GGSCI (bigdatalite.localdomain as SYSTEM@cdb/CDB$ROOT) 24> INFO EXT1

EXTRACT    EXT1      Last Started 2016-09-02 14:46   Status RUNNING
Checkpoint Lag       00:00:07 (updated 00:00:06 ago)
Process ID           23721
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2016-09-02 14:47:05
                     SCN 0.11185093 (11185093)

Now if you open another window (or even better with GNU screen hit Ctrl A and then press c) and list out the folder specified you should see a file has been created:

[oracle@bigdatalite dirdat]$ ls -l /u01/ogg/dirdat/
total 4
-rw-r-----. 1 oracle oinstall 1406 Sep  2 14:46 lt000000000

Assuming that the EXT1 extract is running successfully, you can now create a remote trail file (which in this example is actually just local). In the same GGSCI session as you defined EXT1 (i.e. the /u01/ogg instance), run:

ADD EXTRACT EXTDP1 EXTTRAILSOURCE ./dirdat/lt BEGIN NOW
ADD RMTTRAIL ./dirdat/rt EXTRACT EXTDP1

Edit the parameters for the second extract:

EDIT PARAM EXTDP1

In the edit session paste:

EXTRACT EXTDP1
RMTHOST LOCALHOST, MGRPORT 7810
RMTTRAIL ./dirdat/rt
SOURCECATALOG ORCL
TABLE SOE.*;

Note that the MGRPORT must match the port that the ogg-bd manager (the second one that we set up in the section above) is listening on -- run INFO MGR to check if necessary.

Back in GGSCI start up the datapump extract for the remote trail file:

START EXTDP1

This should start up successfully:

GGSCI (bigdatalite.localdomain as SYSTEM@cdb/CDB$ROOT) 33> INFO EXTDP1

EXTRACT    EXTDP1    Last Started 2016-09-02 14:51   Status RUNNING
Checkpoint Lag       00:16:25 (updated 00:00:00 ago)
Process ID           23939
Log Read Checkpoint  File ./dirdat/lt000000000
                     2016-09-02 14:35:28.000000  RBA 0

and over in the ogg-bd data folder there should now be the remote trail file (rt000000000):

[oracle@bigdatalite ogg]$ ls -l /u01/ogg-bd/dirdat/
total 0
-rw-r-----. 1 oracle oinstall 0 Sep  2 15:02 rt000000000

If EXTDP1 fails to start then one thing to check is that the ogg-bd Manager process is running. If it's not you'll see this error in the ggserr.log:

TCP/IP error 111 (Connection refused), endpoint: LOCALHOST:7810.
@zarez
Copy link

zarez commented Feb 28, 2017

When you create the pump extract, you need to add login info in parameter file, otherwise will get this when starting EXTDP1:

2017-02-28 15:22:06 ERROR OGG-01453 Oracle GoldenGate Capture for Oracle, extdp1.prm: Database login information not specified in parameter file.
2017-02-28 15:22:06 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, extdp1.prm: PROCESS ABENDING.

@Emperoni
Copy link

I also had to run:
ADD RMTTRAIL ./dirdat/rt, EXTRACT EXTDP1

@ddtxra
Copy link

ddtxra commented Nov 14, 2019

I can't make the EXT1 "RUNNING" I always get "STOPPED" after starting without any error:


GGSCI (bigdatalite.localdomain as SYSTEM@cdb/CDB$ROOT) 37> START EXT1

Sending START request to MANAGER ...
EXTRACT EXT1 starting


GGSCI (bigdatalite.localdomain as SYSTEM@cdb/CDB$ROOT) 38> INFO EXT1

EXTRACT    EXT1      Initialized   2019-11-14 07:02   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:06:21 ago)
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2019-11-14 07:02:46
                     SCN 0.0 (0)

Any idea?

@ddtxra
Copy link

ddtxra commented Nov 14, 2019

I quitted the GGSCI terminal and started again and it worked....

@venkatkumbha
Copy link

Replicat is ABENDING with below exception . Any one was facing this issue?

019-11-30 06:19:56 INFO OGG-00996 Oracle GoldenGate Delivery, rconf.prm: REPLICAT RCONF started.
2019-11-30 06:19:56 INFO OGG-02243 Oracle GoldenGate Delivery, rconf.prm: Opened trail file ./dirdat/rt000000000 at 2019-11-30 06:19:56.253977.
2019-11-30 06:19:56 INFO OGG-03506 Oracle GoldenGate Delivery, rconf.prm: The source database character set, as determined from the trail file, is UTF-8.
2019-11-30 06:19:56 INFO OGG-06506 Oracle GoldenGate Delivery, rconf.prm: Wildcard MAP resolved (entry ..*): MAP "ORCL"."SOE"."LOGON", TARGET "ORCL"."SOE"."LOGON".
2019-11-30 06:19:56 INFO OGG-02756 Oracle GoldenGate Delivery, rconf.prm: The definition for table ORCL.SOE.LOGON is obtained from the trail file.
2019-11-30 06:19:56 INFO OGG-06511 Oracle GoldenGate Delivery, rconf.prm: Using following columns in default map by name: LOGON_ID, CUSTOMER_ID, LOGON_DATE.
2019-11-30 06:19:56 INFO OGG-06510 Oracle GoldenGate Delivery, rconf.prm: Using the following key columns for target table ORCL.SOE.LOGON: LOGON_ID, CUSTOMER_ID, LOGON_DATE.
2019-11-30 06:19:56 ERROR OGG-15051 Oracle GoldenGate Delivery, rconf.prm: Java or JNI exception:
java.lang.AbstractMethodError: oracle.goldengate.kafkaconnect.formatter.KafkaConnectFormatter.metaDataChanged(Loracle/goldengate/datasource/DsEvent;Loracle/goldengate/datasource/meta/DsMetaData;)V.
2019-11-30 06:19:56 ERROR OGG-01668 Oracle GoldenGate Delivery, rconf.prm: PROCESS ABENDING.
2019-11-30 06:21:45 INFO OGG-00987 Oracle GoldenGate Command Interpreter: GGSCI command (oracle): info rconf

Appreciate your help

@bradleyhurley
Copy link

@venkatkumbha - I ran into the same issue. I was hoping you had a solution to share.

@mihirthakrar
Copy link

Replicat is ABENDING with below exception . Any one was facing this issue?

019-11-30 06:19:56 INFO OGG-00996 Oracle GoldenGate Delivery, rconf.prm: REPLICAT RCONF started.
2019-11-30 06:19:56 INFO OGG-02243 Oracle GoldenGate Delivery, rconf.prm: Opened trail file ./dirdat/rt000000000 at 2019-11-30 06:19:56.253977.
2019-11-30 06:19:56 INFO OGG-03506 Oracle GoldenGate Delivery, rconf.prm: The source database character set, as determined from the trail file, is UTF-8.
2019-11-30 06:19:56 INFO OGG-06506 Oracle GoldenGate Delivery, rconf.prm: Wildcard MAP resolved (entry ..*): MAP "ORCL"."SOE"."LOGON", TARGET "ORCL"."SOE"."LOGON".
2019-11-30 06:19:56 INFO OGG-02756 Oracle GoldenGate Delivery, rconf.prm: The definition for table ORCL.SOE.LOGON is obtained from the trail file.
2019-11-30 06:19:56 INFO OGG-06511 Oracle GoldenGate Delivery, rconf.prm: Using following columns in default map by name: LOGON_ID, CUSTOMER_ID, LOGON_DATE.
2019-11-30 06:19:56 INFO OGG-06510 Oracle GoldenGate Delivery, rconf.prm: Using the following key columns for target table ORCL.SOE.LOGON: LOGON_ID, CUSTOMER_ID, LOGON_DATE.
2019-11-30 06:19:56 ERROR OGG-15051 Oracle GoldenGate Delivery, rconf.prm: Java or JNI exception:
java.lang.AbstractMethodError: oracle.goldengate.kafkaconnect.formatter.KafkaConnectFormatter.metaDataChanged(Loracle/goldengate/datasource/DsEvent;Loracle/goldengate/datasource/meta/DsMetaData;)V.
2019-11-30 06:19:56 ERROR OGG-01668 Oracle GoldenGate Delivery, rconf.prm: PROCESS ABENDING.
2019-11-30 06:21:45 INFO OGG-00987 Oracle GoldenGate Command Interpreter: GGSCI command (oracle): info rconf

Appreciate your help

I also have the same issue. Looking for solution.

@leozleung
Copy link

Replicat is ABENDING with below exception . Any one was facing this issue?

019-11-30 06:19:56 INFO OGG-00996 Oracle GoldenGate Delivery, rconf.prm: REPLICAT RCONF started.
2019-11-30 06:19:56 INFO OGG-02243 Oracle GoldenGate Delivery, rconf.prm: Opened trail file ./dirdat/rt000000000 at 2019-11-30 06:19:56.253977.
2019-11-30 06:19:56 INFO OGG-03506 Oracle GoldenGate Delivery, rconf.prm: The source database character set, as determined from the trail file, is UTF-8.
2019-11-30 06:19:56 INFO OGG-06506 Oracle GoldenGate Delivery, rconf.prm: Wildcard MAP resolved (entry ..*): MAP "ORCL"."SOE"."LOGON", TARGET "ORCL"."SOE"."LOGON".
2019-11-30 06:19:56 INFO OGG-02756 Oracle GoldenGate Delivery, rconf.prm: The definition for table ORCL.SOE.LOGON is obtained from the trail file.
2019-11-30 06:19:56 INFO OGG-06511 Oracle GoldenGate Delivery, rconf.prm: Using following columns in default map by name: LOGON_ID, CUSTOMER_ID, LOGON_DATE.
2019-11-30 06:19:56 INFO OGG-06510 Oracle GoldenGate Delivery, rconf.prm: Using the following key columns for target table ORCL.SOE.LOGON: LOGON_ID, CUSTOMER_ID, LOGON_DATE.
2019-11-30 06:19:56 ERROR OGG-15051 Oracle GoldenGate Delivery, rconf.prm: Java or JNI exception:
java.lang.AbstractMethodError: oracle.goldengate.kafkaconnect.formatter.KafkaConnectFormatter.metaDataChanged(Loracle/goldengate/datasource/DsEvent;Loracle/goldengate/datasource/meta/DsMetaData;)V.
2019-11-30 06:19:56 ERROR OGG-01668 Oracle GoldenGate Delivery, rconf.prm: PROCESS ABENDING.
2019-11-30 06:21:45 INFO OGG-00987 Oracle GoldenGate Command Interpreter: GGSCI command (oracle): info rconf

Appreciate your help

i have the same issue too...

@black13spell
Copy link

Replicat is ABENDING with below exception . Any one was facing this issue?

019-11-30 06:19:56 INFO OGG-00996 Oracle GoldenGate Delivery, rconf.prm: REPLICAT RCONF started.
2019-11-30 06:19:56 INFO OGG-02243 Oracle GoldenGate Delivery, rconf.prm: Opened trail file ./dirdat/rt000000000 at 2019-11-30 06:19:56.253977.
2019-11-30 06:19:56 INFO OGG-03506 Oracle GoldenGate Delivery, rconf.prm: The source database character set, as determined from the trail file, is UTF-8.
2019-11-30 06:19:56 INFO OGG-06506 Oracle GoldenGate Delivery, rconf.prm: Wildcard MAP resolved (entry ..*): MAP "ORCL"."SOE"."LOGON", TARGET "ORCL"."SOE"."LOGON".
2019-11-30 06:19:56 INFO OGG-02756 Oracle GoldenGate Delivery, rconf.prm: The definition for table ORCL.SOE.LOGON is obtained from the trail file.
2019-11-30 06:19:56 INFO OGG-06511 Oracle GoldenGate Delivery, rconf.prm: Using following columns in default map by name: LOGON_ID, CUSTOMER_ID, LOGON_DATE.
2019-11-30 06:19:56 INFO OGG-06510 Oracle GoldenGate Delivery, rconf.prm: Using the following key columns for target table ORCL.SOE.LOGON: LOGON_ID, CUSTOMER_ID, LOGON_DATE.
2019-11-30 06:19:56 ERROR OGG-15051 Oracle GoldenGate Delivery, rconf.prm: Java or JNI exception:
java.lang.AbstractMethodError: oracle.goldengate.kafkaconnect.formatter.KafkaConnectFormatter.metaDataChanged(Loracle/goldengate/datasource/DsEvent;Loracle/goldengate/datasource/meta/DsMetaData;)V.
2019-11-30 06:19:56 ERROR OGG-01668 Oracle GoldenGate Delivery, rconf.prm: PROCESS ABENDING.
2019-11-30 06:21:45 INFO OGG-00987 Oracle GoldenGate Command Interpreter: GGSCI command (oracle): info rconf

Appreciate your help

I have the same issue 🚶

@leozleung did you get a chance to solve it ?

@charleschemai
Copy link

2023-01-11T12:47:12.307+0300 INFO OGG-06510 Oracle GoldenGate Delivery, REPKFK.prm: Using the following key columns for target table TESTOGG.CUSTOMER_ORDER: ID.
2023-01-11T12:47:12.334+0300 ERROR OGG-15051 Oracle GoldenGate Delivery, REPKFK.prm: Java or JNI exception:
java.lang.AbstractMethodError: oracle.goldengate.kafkaconnect.formatter.KafkaConnectFormatter.metaDataChanged(Loracle/goldengate/datasource/DsEvent;Loracle/goldengate/datasource/meta/DsMetaData;)V.
2023-01-11T12:47:12.334+0300 INFO OGG-02932 Oracle GoldenGate Delivery, REPKFK.prm: Reading /u01/app/oracle/ogg/dirdat/aa000000000, current SEQNO 0, RBA 1,999, 0 records, m_file_seqno = 0, m_file_rba = 2,218.
2023-01-11T12:47:12.334+0300 ERROR OGG-01668 Oracle GoldenGate Delivery, REPKFK.prm: PROCESS ABENDING.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment