Category Archives: GoldenGate

Supplemental Logging for GoldenGate

There are several types of supplemental logging

  • Minimal
  • Primary Key
  • Unique Key
  • Foreign Key
  • All
  • Procedural Replication

Minimal Supplemental Logging

Minimal supplemental logging ensures that products leveraging LogMiner technology will have sufficient information to support chained rows and cluster tables.

Primary Key Supplemental Logging

Primary key supplemental logging includes the primary key for rows affected by UPDATE and DELETE changes.

Unique Key Supplemental Logging

Unique key supplemental logging includes all columns for a unique key are written to undo if any unique key columns are modified.

Foreign Key Supplemental Logging

Foreign key supplemental logging includes all other columns belonging to a foreign key will be logged in the undo if any foreign key columns are modified.

All Column Supplemental Logging

If no primary key or unique key is available then it is possible to specify that all columns are logged. In this case all columns in a row will be logged in the undo. When the row is replicated in the target database, equality predicates will be applied to all columns. LONG, LONG RAW and LOB columns will be omitted from the supplemental logging.

Procedural Replication Supplemental Logging

Procedural replication supplemental logging includes additional information in the redo log during invocation of procedures in Oracle-supplied packages for which procedural replication is supported. I have never investigated this option.

Supplemental Logging Levels

Oracle implements supplemental logging at database level, schema level and at table level. The three implementations are significantly different:

Database Level Supplemental Logging

Database level supplemental logging is configured in the control file. The parameter does not appear to be stored in the database itself.

Adding Heartbeat To GoldenGate

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.

goldengate make user


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;
/


GoldenGate Readiness Scripts from Oracle

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)