ITRemote

Remote IT Services

logdump

ghdr on

detail on

open <trailfile name>

count

This will detail the record count

Jinyu's blog is a great description of how to handle these

Adding Checkpoint Tables

Monitoring For Stability

Adding Heartbeat To GoldenGate

This pertains to the version of GoldenGate 12.2 => 18.1

ADD HEARTBEATTABLE [, FREQUENCY number in seconds] [, RETENTION_TIME number in days] | [, PURGE_FREQUENCY number in days]

FREQUENCY = Specifies how often the heartbeat seed table and heartbeat table are updated. For example, how frequently heartbeat records are generated. The default is 60 seconds.

RETENTION_TIME = Specifies when heartbeat entries older than the retention time in the history table are purged. The default is 30 days.

PURGE_FREQUENCY = Specifies how often the purge scheduler is run to delete table entries that are older than the retention time from the heartbeat history . The default is 1 day.

What is written to trail file 12.2 / 12.3

What is written to trail file 12.1


CREATE USER

create tablespace gg_data datafile '/u01/app/oracle/oradata/srcxxt01/gg_data.dbf' size 1024M;

create user ogguser identified by ogg
default tablespace gg_data temporary tablespace temp
quota unlimited on gg_data;
grant select any table to ogguser ;
grant select any transaction to ogguser ;
grant select any dictionary to ogguser ;
grant flashback  any table to ogguser ;
grant execute on dbms_flashback to ogguser ;
grant create session, alter session to ogguser ;
grant connect,resource to ogguser ;
grant alter any table to ogguser ;
grant alter system to ogguser ;
grant execute on sys.dbms_internal_clkm to ogguser ;
exec dbms_goldengate_auth.grant_admin_privilege('ogguser');






create user testrep identified by testrep
default tablespace users temporary tablespace temp;

grant connect to testrep;
alter user testrep default role connect;
grant create session to testrep;
grant resource to testrep;
alter user testrep quota unlimited on users;



truncate table stress1;
truncate table stress2;
truncate table stress3;
truncate table stress4;
truncate table stress5;
truncate table stress6;
truncate table stress7;




make_testrep_objects.sql

DROP TABLE STRESS1 CASCADE CONSTRAINTS ;

CREATE TABLE STRESS1 (
  FIELD1   VARCHAR2 (10)  NOT NULL,
  FIELD2   VARCHAR2 (10)  NOT NULL,
  FIELD3   VARCHAR2 (10)  NOT NULL,
  NUM1     NUMBER        NOT NULL,
  NUM2     NUMBER,
  DATE1    DATE,
  FIELD4   VARCHAR2 (20),
  FIELD5   VARCHAR2 (20),
  FIELD6   VARCHAR2 (20),
  NUM3     NUMBER,
  NUM4     NUMBER,
  FIELD7   VARCHAR2 (20),
  FIELD14  VARCHAR2 (20),
  FIELD8   VARCHAR2 (20),
  FIELD9   VARCHAR2 (20),
  FIELD10  VARCHAR2 (20),
  FIELD11  VARCHAR2 (20),
  FIELD12  VARCHAR2 (20),
  FIELD13  VARCHAR2 (20),
  DATE2    DATE,
  DATE3    DATE,
  NUM5     NUMBER,
  NUM6     NUMBER,
  NUM7     NUMBER,
  NUM8     NUMBER,
  NUM9     NUMBER,
  NUM10    NUMBER,
  NUM11    NUMBER,
  FIELD15  VARCHAR2 (20),
  CONSTRAINT STRESS1_PK
  PRIMARY KEY ( NUM1, FIELD1, FIELD2, FIELD3 )
    USING INDEX
     TABLESPACE USERS PCTFREE 10
     STORAGE ( INITIAL 128K NEXT 128K PCTINCREASE 0 ))
 TABLESPACE USERS
   PCTFREE 10   PCTUSED 40
   INITRANS 1   MAXTRANS 255
 STORAGE (
   INITIAL 2M NEXT 2M PCTINCREASE 0
   MINEXTENTS 1 MAXEXTENTS 4096 )
   NOCACHE;


drop sequence stress1_seq;


create sequence stress1_seq start with 1
increment by 1;


DROP TABLE STRESS2 CASCADE CONSTRAINTS ;


CREATE TABLE STRESS2 (
  FIELD1   VARCHAR2 (10)  NOT NULL,
  FIELD2   VARCHAR2 (10)  NOT NULL,
  FIELD3   VARCHAR2 (10)  NOT NULL,
  NUM1     NUMBER        NOT NULL,
  NUM2     NUMBER,
  DATE1    DATE,
  FIELD4   VARCHAR2 (20),
  FIELD5   VARCHAR2 (20),
  FIELD6   VARCHAR2 (20),
  NUM3     NUMBER,
  NUM4     NUMBER,
  FIELD7   VARCHAR2 (20),
  FIELD14  VARCHAR2 (20),
  FIELD8   VARCHAR2 (20),
  FIELD9   VARCHAR2 (20),
  FIELD10  VARCHAR2 (20),
  FIELD11  VARCHAR2 (20),
  FIELD12  VARCHAR2 (20),
  FIELD13  VARCHAR2 (20),
  DATE2    DATE,
  DATE3    DATE,
  NUM5     NUMBER,
  NUM6     NUMBER,
  NUM7     NUMBER,
  NUM8     NUMBER,
  NUM9     NUMBER,
  NUM10    NUMBER,
  NUM11    NUMBER,
  FIELD15  VARCHAR2 (20),
  CONSTRAINT STRESS2_PK
  PRIMARY KEY ( NUM1, FIELD1, FIELD2, FIELD3 )
    USING INDEX
     TABLESPACE USERS PCTFREE 10
     STORAGE ( INITIAL 128K NEXT 128K PCTINCREASE 0 ))
 TABLESPACE USERS
   PCTFREE 10   PCTUSED 40
   INITRANS 1   MAXTRANS 255
 STORAGE (
   INITIAL 2M NEXT 2M PCTINCREASE 0
   MINEXTENTS 1 MAXEXTENTS 4096 )
   NOCACHE;

drop sequence stress2_seq;



create sequence stress2_seq start with 1
increment by 1;


DROP TABLE STRESS3 CASCADE CONSTRAINTS ;

CREATE TABLE STRESS3 (
  FIELD1   VARCHAR2 (10)  NOT NULL,
  FIELD2   VARCHAR2 (10)  NOT NULL,
  FIELD3   VARCHAR2 (10)  NOT NULL,
  NUM1     NUMBER        NOT NULL,
  NUM2     NUMBER,
  DATE1    DATE,
  FIELD4   VARCHAR2 (20),
  FIELD5   VARCHAR2 (20),
  FIELD6   VARCHAR2 (20),
  NUM3     NUMBER,
  NUM4     NUMBER,
  FIELD7   VARCHAR2 (20),
  FIELD14  VARCHAR2 (20),
  FIELD8   VARCHAR2 (20),
  FIELD9   VARCHAR2 (20),
  FIELD10  VARCHAR2 (20),
  FIELD11  VARCHAR2 (20),
  FIELD12  VARCHAR2 (20),
  FIELD13  VARCHAR2 (20),
  DATE2    DATE,
  DATE3    DATE,
  NUM5     NUMBER,
  NUM6     NUMBER,
  NUM7     NUMBER,
  NUM8     NUMBER,
  NUM9     NUMBER,
  NUM10    NUMBER,
  NUM11    NUMBER,
  FIELD15  VARCHAR2 (20),
  CONSTRAINT STRESS3_PK
  PRIMARY KEY ( NUM1, FIELD1, FIELD2, FIELD3 )
    USING INDEX
     TABLESPACE USERS PCTFREE 10
     STORAGE ( INITIAL 128K NEXT 128K PCTINCREASE 0 ))
 TABLESPACE USERS
   PCTFREE 10   PCTUSED 40
   INITRANS 1   MAXTRANS 255
 STORAGE (
   INITIAL 2M NEXT 2M PCTINCREASE 0
   MINEXTENTS 1 MAXEXTENTS 4096 )
   NOCACHE;

drop sequence stress3_seq;

create sequence stress3_seq start with 1
increment by 1;


DROP TABLE STRESS4 CASCADE CONSTRAINTS ;

CREATE TABLE STRESS4 (
  FIELD1   VARCHAR2 (10)  NOT NULL,
  FIELD2   VARCHAR2 (10)  NOT NULL,
  FIELD3   VARCHAR2 (10)  NOT NULL,
  NUM1     NUMBER        NOT NULL,
  NUM2     NUMBER,
  DATE1    DATE,
  FIELD4   VARCHAR2 (20),
  FIELD5   VARCHAR2 (20),
  FIELD6   VARCHAR2 (20),
  NUM3     NUMBER,
  NUM4     NUMBER,
  FIELD7   VARCHAR2 (20),
  FIELD14  VARCHAR2 (20),
  FIELD8   VARCHAR2 (20),
  FIELD9   VARCHAR2 (20),
  FIELD10  VARCHAR2 (20),
  FIELD11  VARCHAR2 (20),
  FIELD12  VARCHAR2 (20),
  FIELD13  VARCHAR2 (20),
  DATE2    DATE,
  DATE3    DATE,
  NUM5     NUMBER,
  NUM6     NUMBER,
  NUM7     NUMBER,
  NUM8     NUMBER,
  NUM9     NUMBER,
  NUM10    NUMBER,
  NUM11    NUMBER,
  FIELD15  VARCHAR2 (20),
  CONSTRAINT STRESS4_PK
  PRIMARY KEY ( NUM1, FIELD1, FIELD2, FIELD3 )
    USING INDEX
     TABLESPACE USERS PCTFREE 10
     STORAGE ( INITIAL 128K NEXT 128K PCTINCREASE 0 ))
 TABLESPACE USERS
   PCTFREE 10   PCTUSED 40
   INITRANS 1   MAXTRANS 255
 STORAGE (
   INITIAL 2M NEXT 2M PCTINCREASE 0
   MINEXTENTS 1 MAXEXTENTS 4096 )
   NOCACHE;


drop sequence stress4_seq;

create sequence stress4_seq start with 1
increment by 1;



DROP TABLE STRESS5 CASCADE CONSTRAINTS ;

CREATE TABLE STRESS5 (
  FIELD1   VARCHAR2 (10)  NOT NULL,
  FIELD2   VARCHAR2 (10)  NOT NULL,
  FIELD3   VARCHAR2 (10)  NOT NULL,
  NUM1     NUMBER        NOT NULL,
  NUM2     NUMBER,
  DATE1    DATE,
  FIELD4   VARCHAR2 (20),
  FIELD5   VARCHAR2 (20),
  FIELD6   VARCHAR2 (20),
  NUM3     NUMBER,
  NUM4     NUMBER,
  FIELD7   VARCHAR2 (20),
  FIELD14  VARCHAR2 (20),
  FIELD8   VARCHAR2 (20),
  FIELD9   VARCHAR2 (20),
  FIELD10  VARCHAR2 (20),
  FIELD11  VARCHAR2 (20),
  FIELD12  VARCHAR2 (20),
  FIELD13  VARCHAR2 (20),
  DATE2    DATE,
  DATE3    DATE,
  NUM5     NUMBER,
  NUM6     NUMBER,
  NUM7     NUMBER,
  NUM8     NUMBER,
  NUM9     NUMBER,
  NUM10    NUMBER,
  NUM11    NUMBER,
  FIELD15  VARCHAR2 (20),
  CONSTRAINT STRESS5_PK
  PRIMARY KEY ( NUM1, FIELD1, FIELD2, FIELD3 )
    USING INDEX
     TABLESPACE USERS PCTFREE 10
     STORAGE ( INITIAL 128K NEXT 128K PCTINCREASE 0 ))
 TABLESPACE USERS
   PCTFREE 10   PCTUSED 40
   INITRANS 1   MAXTRANS 255
 STORAGE (
   INITIAL 2M NEXT 2M PCTINCREASE 0
   MINEXTENTS 1 MAXEXTENTS 4096 )
   NOCACHE;

drop sequence stress5_seq;


create sequence stress5_seq start with 1
increment by 1;


DROP TABLE STRESS6 CASCADE CONSTRAINTS ;
                                                                               265,1         42%
begin
  for loop_var in 1..overall loop
     for loop_ins in 1..ins loop
       insert into stress7 values(
       'thisisa','temporary','tablevalue',stress7_seq.nextval,12345,sysdate,
       'newwewil','determine','helloworld',12345,987654,
       '10charvld','11charfld','another1','biggest','smallest',
       'ashortone','dkdksjj',NULL,sysdate-1,sysdate-2,
       12345,23456,34567,45678,56789,98766,56783,
       'thelastone');
   end loop;
   commit;
   end loop;
end;
/

create or replace procedure rock_and_roll
(ins in number default 10, overall in number default 10)
as
 loop_var number(38);
 loop_ins number(38);
 tmpvar varchar2(50);
begin
  for loop_var in 1..overall loop
     for loop_ins in 1..ins loop
       insert into stress1 values(
       'thisisa','temporary','tablevalue',stress1_seq.nextval,12345,sysdate,
       'newwewil','determine','helloworld',12345,987654,
       '10charvld','11charfld','another1','biggest','smallest',
       'ashortone','dkdksjj',NULL,sysdate-1,sysdate-2,
       12345,23456,34567,45678,56789,98766,56783,
       'thelastone');
       insert into stress2 values(
       'thisisa','temporary','tablevalue',stress2_seq.nextval,12345,sysdate,
       'newwewil','determine','helloworld',12345,987654,
       '10charvld','11charfld','another1','biggest','smallest',
       'ashortone','dkdksjj',NULL,sysdate-1,sysdate-2,
       12345,23456,34567,45678,56789,98766,56783,
       'thelastone');
       insert into stress3 values(
       'thisisa','temporary','tablevalue',stress3_seq.nextval,12345,sysdate,
       'newwewil','determine','helloworld',12345,987654,
       '10charvld','11charfld','another1','biggest','smallest',
       'ashortone','dkdksjj',NULL,sysdate-1,sysdate-2,
       12345,23456,34567,45678,56789,98766,56783,
       'thelastone');
       insert into stress4 values(
       'thisisa','temporary','tablevalue',stress4_seq.nextval,12345,sysdate,
       'newwewil','determine','helloworld',12345,987654,
       '10charvld','11charfld','another1','biggest','smallest',
       'ashortone','dkdksjj',NULL,sysdate-1,sysdate-2,
       12345,23456,34567,45678,56789,98766,56783,
       'thelastone');
       insert into stress5 values(
       'thisisa','temporary','tablevalue',stress5_seq.nextval,12345,sysdate,
       'newwewil','determine','helloworld',12345,987654,
       '10charvld','11charfld','another1','biggest','smallest',
       'ashortone','dkdksjj',NULL,sysdate-1,sysdate-2,
       12345,23456,34567,45678,56789,98766,56783,
       'thelastone');
       insert into stress6 values(
       'thisisa','temporary','tablevalue',stress6_seq.nextval,12345,sysdate,
       'newwewil','determine','helloworld',12345,987654,
       '10charvld','11charfld','another1','biggest','smallest',
       'ashortone','dkdksjj',NULL,sysdate-1,sysdate-2,
       12345,23456,34567,45678,56789,98766,56783,
       'thelastone');
       insert into stress7 values(
       'thisisa','temporary','tablevalue',stress7_seq.nextval,12345,sysdate,
       'newwewil','determine','helloworld',12345,987654,
       '10charvld','11charfld','another1','biggest','smallest',
       'ashortone','dkdksjj',NULL,sysdate-1,sysdate-2,
       12345,23456,34567,45678,56789,98766,56783,
       'thelastone');
   end loop;
   commit;
   end loop;
end;
/

GG New Features 19c

GoldenGate DB Ready

Complete Database Profile OGG readiness check for Classic Extract

MOS Note:1298562.1:
Oracle GoldenGate database Complete Database Profile check script for Oracle DB (All Schemas) Classic Extract 

This script is intended to query all of the non default database users to identify current configuration and identify any unsupported data types or types that may need special considerations for Oracle GoldenGate in an Oracle environment.

GoldenGate Integrated Capture and Integrated Replicat Healthcheck Script (Doc ID 1448324.1)

Oracle 12 Setup

GoldenGate Setup for the Hub

Steps to setup a new environment on a node.

Assumptions.

 

These are all the manual steps required to implement resiliency for the GoldenGate hub.   Mike Culp has written scripts to do this process. In general, using scripting for a process that covers many different servers is a best practice approach, as it insures the installs are done in an identical fashion.  This reduces complexity of all maintenance.

 

Setup Overview

Each cluster on the Hub has its’ own database.  This database is used to supply a file system(s) for the trail files and other OGG files required for recovery.   The DBFS filesystem for the OGG files can only be mounted on one node at a time as a best practice. Otherwise duplicate processes could be started, which could cause corruption in the replication hub.  The data disk can be mounted on all systems if desired. If there is only one data group in a replication instance, it is possible to put all files on that one mount, and mount it to only one node at a time. But it is very difficult to undo that decision.

 

  1. Create a directories off the root filesystem system.
  2. Grant the oracle user full control of the dir.
  3. Create the tablespace for DBFS – standard create tablespace command.  The following example uses a bigfile tablespace but that is NOT required.
    1. create bigfile tablespace dbfs_tblsp datafile '+DBFS_DG' size 32g autoextend on next 8g maxsize 300g NOLOGGING EXTENT MANAGEMENT LOCAL AUTOALLOCATE  SEGMENT SPACE MANAGEMENT AUTO ;
  4. Create a user for that tablespace.
    1. At minimum, database users must have the following privileges to create a file system: GRANT CONNECT, CREATE SESSION, RESOURCE, CREATE TABLE, and CREATE PROCEDURE, and DBFS_ROLE
      1. create user dbfs_user identified by dbfs_passwd default tablespace dbfs_tblsp quota unlimited on dbfs_tblsp;
      2. grant create session, create table, create view, create procedure, dbfs_role to dbfs_user;
  5. Create the file system, logging in as the user created above.
    1. start dbfs_create_filesystem dbfs_tblsp dbfs_mnt
      1. This script takes two arguments:
        1. dbfs_tblsp: tablespace for the DBFS database objects
        2. dbfs_mnt : filesystem name, this can be any string and will appear as a directory under the mount point
    2. Validate the system is configured with nocache, logging
      1. SQL>  SELECT logging, cache FROM dba_lobs WHERE tablespace_name='DBFS_TBLSP';

LOGGING CACHE

------- ----------

YES     NO

  1. Modify/rename the mount-dbfs.conf and mount-dbfs.sh scripts for this instantiation.  Each OGG instance will have its own “name” used to identify it within crs. As such a different set of conf and sh script will be needed for each OGG instance.
  2. Test the mount-dbfs.sh script.  (start, stop , status). This validates the setup.
    1. Start should mount the file system.
    2. Stop should unmount.
    3. Status should return the status.
  3. Create a service for the instantation.
    1. srvctl add service -database db_name -service ggname_svc -preferred node1 -available node2
  4. Create the clusterware setup for the dbfs file system.  Use cluster resource to only allow it to be mounted one one node.  The data dbfs system can use local_resource.
    1. $GRID_HOME/bin/crsctl add resource dbfs_mnt \

-type cluster_resource \

-attr "ACTION_SCRIPT=/ora01/scripts/ggcommon/mount-dbfs.sh CHECK_INTERVAL=30 RESTART_ATTEMPTS=10 , \  START_DEPENDENCIES='hard(ora.raca_domain.db)pullup(ora.raca_domain.db)',STOP_DEPENDENCIES='hard(ora.raca_domain.db)',SCRIPT_TIMEOUT=300"

 

  1. Test crsctl to start,stop and relocate to validate it works.

$GRID_HOME/bin/start resource dbfs_mnt

  1. Create the goldengate soft links to the dbfs mounts.
    1. cd $GG_HOME
    2. mv dirprm dirprm.old
    3. mv dirchk dirchk.old
    4. mv dircrd dircrd.old
    5. mv dirrpt dirrpt.old
    6. ln –s /mnt/dbfs_mnt/dirprm dirprm
    7. ln –s /mnt/dbfs_mnt/dirchk dirchk
    8. ln –s /mnt/dbfs_mnt/dircrd dircrd
    9. ln –s /mnt/dbfs_mnt/dirrpt dirrpt
  2. Create the crs definition for goldengate using the XAG interface
    1. $XAG_HOME/bin/agctl add goldengate GG_SOURCE \ --gg_home /u01/oracle/goldengate \ --oracle_home /u01/app/oracle/product/12.2.0/dbhome_1 \  --db_services ggname_svc --use_local_services --filesystems dbfs_mnt
  3. Create the manager parameter file
  4. Start and stop the goldengate instance using agctl.   Validate the mgr process started.
  5. Create the required data dbfs file systems for the data.
  6. Create the crsctl definitions, if desired.  The only difference is -cluster_resource is not used, as the data can be mounted on all systems with no issues.  The only reason for the crsctl definitions is to startup the mount system automatically, there is no failover required.

The system is ready for replication, however the same setup, with the exception of the XAG and CRS configuration is required for all nodes/clusters that may run this instantiation of GoldenGate.  The XAG and the crs setup is only done once per cluster.

 

MRC Consulting LLC • info@it-remote.com • (864) 630-2118
Visit our FacebookVisit our InstagramVisit our TwitterVisit our LinkedIn
Copyright 2026 MRC Consulting LLC
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram