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
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
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
You can check that it's running using the
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
GGSCI prompt enter
EDIT PARAM MGR and check that it is set to
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
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.
GGSCI prompt issue:
and check that it's running:
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.
GGSCI start up the datapump extract for the remote trail file:
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 (
[oracle@bigdatalite ogg]$ ls -l /u01/ogg-bd/dirdat/ total 0 -rw-r-----. 1 oracle oinstall 0 Sep 2 15:02 rt000000000
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
TCP/IP error 111 (Connection refused), endpoint: LOCALHOST:7810.