rmoff/OGG_Swingbench_Extract.md

## OGG_Swingbench_Extract.md

      
    Raw
  

              OGG_Swingbench_Extract.md
            
          
    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.