Oracle | GoldenGate

Oracle GoldenGate Version 12.1 - 12.3

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Page Note The concept behind this page is to combine documentation for GoldenGate installations for Oracle 11.2.0.4 through 18.3.0.1 databases with information about command syntax and examples. GoldenGate configuration is primarily a matter of putting the correct parameters into .prm files in the correct order and the number of parameters is far too voluminous to cover here at this time. In the future this will stabilize on just Oracle Database 18c and GoldenGate 12.3 or above.

Pre-Installation Environment Prep

GG software owner's environment

Production .bashrc

Interactive Installation

Unzipped install files

Post installation GGHOME directory files

Installation Step 1 of 6

Installation Step 2 of 6

Even when the GGHOME environment variable is set the software location ignores it and must be fixed

Installation Step 3 of 6

Review to make sure things are configured properly. Then save the response file to document your installation choices.

Installation Step 4 of 6

A good practice is to save the response files into the directory /home/oracle and then to place a copy into your change control respository.

Installation Step 5 of 6

Installation Step 6 of 6

Silent Installation

Unzipped install files

DB Prep SQL> conn / as sysdba ALTER DATABASE FORCE LOGGING; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; ALTER SYSTEM SET enable_goldengate_replication=TRUE COMMENT='Enabled GG Replication 15-MAR-2014 DAM' SID='*' SCOPE=BOTH;

GGSCI Commands

HISTORY

INFO ALL

INFO <extract>

GGSCI> info PONLINE4, showch

SHOW ALL

GGSCI (dc2sfdb02) 2> info all

      

      Program     Status      Group       Lag at Chkpt  Time Since Chkpt

      

      MANAGER     RUNNING     RDELTA1     00:00:00      00:00:00

      REPLICAT    RUNNING     RDELTA4     00:00:00      00:00:00

      REPLICAT    ABENDED     RONLINE1    00:43:41      15:36:06

      REPLICAT    RUNNING     RONLINE4    00:00:00      00:00:04

START

START EXTRACT ext3

STOP

GGSCI (dc4sfdb04) 10> stop

      

      Sending STOP request to EXTRACT EONLINE2 ...

      Request processed.

      

      Sending STOP request to EXTRACT PONLINE2 ...

      Request processed.

      

      GGSCI (dc4sfdb04 11> status *

      EXTRACT EONLINE2: STOPPED

      EXTRACT PONLINE2: STOPPED

ADD

Create a new pump process where the extract process already exists ADD EXTRACT <pump_process_name>, exttrailsource <path>, begin now

GGSCI> add extract uwpumpdb, exttrailsource /u01/app/oracle/product/gg/dirdat/a1, begin now

      

      -- edit the parameter file for the pump process to have the following contents:

      extract uwpumpdb

      userid gg, password gg

      rmthost ggtargethost, mgrport 7809

      rmttrail /u01/app/oracle/product/gg/dirdat/a1

      table uwclass.*;

      

      -- execute the following ggsci commands on the source server:

      GGSCI> add rmttrail /u01/app/oracle/product/gg/dirdat/a1, extract uwpumpdb

      GGSCI> start uwpumpdb

      

      /* After executing the above, start the manager process on the target server (ggtargethost) and the pump process will started 
      propagating data to the rmttrail files */

ADD

Create the replicat process and checkpoint table

GGSCI> dblogin userid gg, password gg

      GGSCI> add checkpointtable gg.checkpointtable;

      

      -- on remote host (ggtargethost)

      

      GGSCI> add replicat uwrepdb, exttrail /u01/app/oracle/product/gg/dirdat/a1, checkpointtable gg.checkpoint_table, begin now

View Report

To find an issue VIEW REPORT <pump_name>

GGSCI> view report uwronline

INSTALL

install ADDSERVICE USER <username> PASSWORD <password>

Log File Locations

GG_HOME/ggserr.log GG_HOME/dirrrpt GG_HOME/dirrpm

Installation

Create subdirectories After unzipping the GoldenGate software

1. Open a command prompt and navigate to the installation directory
cd /app/gghome
2. Run ggsci.exe
ggsci.exe

3. Run �CREATE SUBDIRS� command. You should see a message starting with �Creating subdirectories under the current directory

4. Run �EDIT PARAMS ./GLOBALS� command. Enter the line �CHECKPOINTTABLE dbo.ggschkpt� into the file that opens and save and close it.

5. Run �EDIT PARAMS MGR�. Enter the following lines into the file that opens and save and close it.

-- GoldenGate Manager Parameter file
PORT 7810
DYNAMICPORTLIST 7811-7819

6. Run the �EXIT� command to exit ggsci.exe.

To ensure the MANAGER stays running we want to have it setup as a Windows service. The command to set this up is

ggsci> install ADDSERVICE USER <username> PASSWORD <password>

View GG version

Default Manager Parameter File

Default Manager Report

Extract Registration

These are the commands issued by GoldenGate when registering an extract in the database DECLARE container_global_name VARCHAR2(4000) :='PDBPPDL.COMPUTE-A430291.ORACLECLOUD.INTERNAL'; container_short_name VARCHAR2(128) :='PDBPPDL'; extract_name VARCHAR2(100) := 'EI_PPDL'; firstScn NUMBER := 4845046; optimized_mode NUMBER :=0; outbound_server_name VARCHAR2(30); outbound_capture_name VARCHAR2(30); capture_queue_name VARCHAR2(30); outbound_comment VARCHAR2(125); queue_table_name VARCHAR2(30); source_global_name VARCHAR2(4000) := 'ORCLT.COMPUTE-A430291.ORACLECLOUD.INTERNAL'; startScn NUMBER := 4845444; BEGIN dbms_xstream_gg_adm.wait_for_inflight_txns := 'n'; dbms_xstream_gg_adm.synchronization := 'none'; dbms_xstream_gg_adm.is_goldengate := TRUE; queue_table_name := SUBSTR('OGG$Q_TAB_' || extract_name, 1, 30); capture_queue_name := SUBSTR('OGG$Q_' || extract_name, 1, 30); dbms_streams_adm.set_up_queue(queue_table => queue_table_name,queue_name => capture_queue_name); outbound_capture_name := SUBSTR('OGG$CAP_' || extract_name, 1, 30); outbound_server_name := SUBSTR('OGG$'|| extract_name, 1, 30); outbound_comment := extract_name || ' GoldenGate Extract'; dbms_xstream_gg.set_gg_session(); dbms_capture_adm.create_capture(queue_name => capture_queue_name, capture_name => outbound_capture_name, first_scn => firstScn, start_scn => startScn, source_database => source_global_name, source_root_name => source_global_name, capture_class => 'goldengate'); /* Construct the outbound server name */ dbms_xstream_gg_adm.add_outbound(server_name => outbound_server_name, capture_name=> outbound_capture_name, source_database => container_global_name, source_container_name=> container_short_name, source_root_name => source_global_name, committed_data_only => FALSE, wait_for_inflight_txns => 'N', synchronization => 'NONE', start_scn => startScn, comment => outbound_comment); dbms_xstream_gg.set_gg_session(FALSE); END; /