GoldenGate 12.1.2 not firing insert trigger

Getting report of goldengate Replicate process which is shows after upgrade from 11g to 12c.

Even though checkpoint table also upgraded. But Replicate process unable to start. 

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

2021-02-11 04:13:31  INFO    OGG-06451  Triggers will be suppressed by default.

--More--(36%)
2021-02-11 04:13:31  INFO    OGG-01815  Virtual Memory Facilities for: COM
    anon alloc: mmap(MAP_ANON)  anon free: munmap
    file alloc: mmap(MAP_SHARED)  file free: munmap
    target directories:
    /opt/app/p0bfo3d1/ggs/12.1.2.1.0/dirtmp.

CACHEMGR virtual memory values (may have been adjusted)
CACHESIZE:                                2G
CACHEPAGEOUTSIZE (default):               8M
PROCESS VM AVAIL FROM OS (min):           4G
CACHESIZEMAX (strict force to disk):   3.41G

Database Version:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0    Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

Database Language and Character Set:
NLS_LANGUAGE     = "AMERICAN"
NLS_TERRITORY    = "AMERICA"
NLS_CHARACTERSET = "WE8ISO8859P1"

***********************************************************************
**                     Run Time Messages                             **
***********************************************************************
 
Opened trail file /opt/app/ORCL/ggs/11.2.1.0.3/trails/y1000331 at 2021-02-11 05:01:20
 
2021-02-11 05:01:20  INFO    OGG-03506  The source database character set, as determined from the trail file, is we8iso8859p1.
 
2021-02-11 05:01:20  INFO    OGG-06505  MAP resolved (entry ggsuser.ggs_heartbeat): MAP "GGSUSER"."GGS_HEARTBEAT", TARGET ggsuser.ggs_lagtime, KEY
COLS (ID), INSERTALLRECORDS, COLMAP (USEDEFAULTS, id = 0, committime = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'), groupname  = @GETENV ('GGENVIRONME
NT', 'GROUPNAME'), ).
Using the following default columns with matching names:
  SOURCETIME=SOURCETIME, TARGETTIME=TARGETTIME, LAGTIME=LAGTIME
 
2021-02-11 05:01:21  INFO    OGG-06510  Using the following key columns for target table GGSUSER.GGS_LAGTIME: ID.
 
 
2021-02-11 05:01:21  WARNING OGG-00869  OCI Error ORA-00001: unique constraint (GGSUSER.GGS_LAGTIME_PK) violated (status = 1), SQL <INSERT INTO "G
GSUSER"."GGS_LAGTIME" ("ID","COMMITTIME","GROUPNAME") VALUES (:a0,:a1,:a2)>.
 
2021-02-11 05:01:21  WARNING OGG-01004  Aborted grouped transaction on 'GGSUSER.GGS_LAGTIME', Database error 1 (OCI Error ORA-00001: unique constr
aint (GGSUSER.GGS_LAGTIME_PK) violated (status = 1), SQL <INSERT INTO "GGSUSER"."GGS_LAGTIME" ("ID","COMMITTIME","GROUPNAME") VALUES (:a0,:a1,:a2)
>).
 
2021-02-11 05:01:21  WARNING OGG-01003  Repositioning to rba 16083229 in seqno 331.
 
2021-02-11 05:01:21  WARNING OGG-01154  SQL error 1 mapping GGSUSER.GGS_HEARTBEAT to GGSUSER.GGS_LAGTIME OCI Error ORA-00001: unique constraint (G
GSUSER.GGS_LAGTIME_PK) violated (status = 1), SQL <INSERT INTO "GGSUSER"."GGS_LAGTIME" ("ID","COMMITTIME","GROUPNAME") VALUES (:a0,:a1,:a2)>.
 
2021-02-11 05:01:21  WARNING OGG-01003  Repositioning to rba 16083229 in seqno 331.
Source Context :
  SourceModule            : [er.errors]
  SourceID                : [/scratch/aime1/adestore/views/aime1_adc4150384/oggcore/OpenSys/src/app/er/errors.cpp]
  SourceFunction          : [take_rep_err_action]
  SourceLine              : [701]
  ThreadBacktrace         : [13] elements
                          : [/opt/app/ORCL/ggs/12.1.2.1.0/libgglog.so(CMessageContext::AddThreadContext()+0x1e) [0x7f69e005bdae]]
                          : [/opt/app/ORCL/ggs/12.1.2.1.0/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x340
) [0x7f69e0056ac0]]
                          : [/opt/app/ORCL/ggs/12.1.2.1.0/libgglog.so(_MSG_ERR_MAP_TO_TANDEM_FAILED(CSourceContext*, ggs::gglib::ggapp::CQualD
BObjName<(DBObjType)1> const&, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&, CMessageFactory::MessageDisposition)+0x53) [0x7f69e004fcff]
]
                          : [/opt/app/ORCL/ggs/12.1.2.1.0/replicat(take_rep_err_action(short, int, char const*, extr_ptr_def*, __std_rec_hdr*,
 char*, file_def*, bool)+0xdda) [0x580b82]]
                          : [/opt/app/ORCL/ggs/12.1.2.1.0/replicat(process_record(file_def*&, short&, extr_ptr_def*&, extr_ptr_def*&, int&, bo
ol&, int&, int&, bool)+0xcaa) [0x5d0f5a]]
                          : [/opt/app/ORCL/ggs/12.1.2.1.0/replicat(process_replicat_loop()+0xe11) [0x5ad7f1]]
                          : [/opt/app/ORCL/ggs/12.1.2.1.0/replicat(replicat_main(int, char**)+0x5ca) [0x5c6b7a]]
                          : [/opt/app/ORCL/ggs/12.1.2.1.0/replicat(ggs::gglib::MultiThreading::MainThread::ExecMain()+0x4f) [0x699c0f]]
                          : [/opt/app/ORCL/ggs/12.1.2.1.0/replicat(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::T
hread::ThreadArgs*)+0x104) [0x699f44]]
                          : [/opt/app/ORCL/ggs/12.1.2.1.0/replicat(ggs::gglib::MultiThreading::MainThread::Run(int, char**)+0x8b) [0x69a04b]]
                          : [/opt/app/ORCL/ggs/12.1.2.1.0/replicat(main+0x3f) [0x5c580f]]
                          : [/lib64/libc.so.6(__libc_start_main+0x100) [0x3b5481ed20]]
                          : [/opt/app/ORCL/ggs/12.1.2.1.0/replicat() [0x517099]]
 
2021-02-11 05:01:21  ERROR   OGG-01296  Error mapping from GGSUSER.GGS_HEARTBEAT to GGSUSER.GGS_LAGTIME.

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

 Solution:

I had to troubleshoot a situation, after GoldenGate capture some DML and replicate that, Oracle database needs to run insert trigger making some business integration.

After to upgrade this enviroment from GG 11.1.1.1 to 12.1.2 and DB 11.2.0.3 to 12.1.0.2, was identified that GoldenGate wasn’t running this triggers

So, found interesting resolution on Oracle Docs:

SUPPRESSTRIGGERS | NOSUPPRESSTRIGGERS

Valid for nonintegrated Replicat for Oracle. Controls whether or not triggers are fired during the Replicat session. Provides an alternative to manually disabling triggers. (Integrated Replicat does not require disabling of triggers on the target system.)

SUPPRESSTRIGGERS is the default and prevents triggers from firing on target objects that are configured for replication with Oracle GoldenGate. SUPPRESSTRIGGERS is valid for Oracle 11.2.0.2 and later 11gR2 versions. SUPPRESSTRIGGERS is not valid for 11gR1.

So, added 'DBOPTIONS NOSUPPRESSTRIGGERS' in the replicat parameter file.

OR

However, altough you probably want most of the triggers not to be executed on the target, it can be possible to override this setting, for example when you implement a heartbeat table.

The way to exclude the trigger from the SUPRRESSTRIGGERS setting is to grant the trigger an exception. This can be done as follows on the target:

SQL> EXEC dbms_ddl.set_trigger_firing_property('HR','trg_tbl_heartbeat', FALSE);'

The result of the statement above is that trigger hr.trg_tbl_heartbeat will go off in the target database.

=================================================================================

2nd.

If above steps are not working and still Replicate process Abended then go for below the steps.

 upgrade Goldengate from 11g to 12c ?

If yes, you have two options :

1) Change the double quotes to single quotes

2) Add NOUSEANSISQLQUOTES into your GLOBALS file and restart the processes
/
NOUSEANSISQLQUOTES is intended for backward compatibility with the parameter files of Oracle GoldenGate versions that predate version 12c, where strings in double quotes are intended to be literals and case-sensitive column names are not supported (whether or not they are within quotes).

Reference : https://docs.oracle.com/goldengate/1212/gg-winux/GWURF/gg_parameters183.htm#GWURF728

12c GoldenGate Silent Installation

Most of the customer and Env. not using GUI based installation so here i'm giving you small demonstration to installing Oracle goldengate 12c silent installation.

For GUI based. run simply ./runInstaller and pass require parameters according to you env.

Below the steps for silent installations.

=====================
-Software Location of GG
=====================
/u01/fbo_ggs_Linux_x64_shiphome/Disk1/response

oggcore.rsp ----> response file

less oggcore.rsp | grep oracle.install.responseFileVersion

==================================
-Below the parameters update in response file
==================================

oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v12_1_2

INSTALL_OPTION=ORA12c
SOFTWARE_LOCATION=/oracle/gg
START_MANAGER=true
MANAGER_PORT=7809
DATABASE_LOCATION=/u01/app/oracle/product/12.1.0/dbhome_1
INVENTORY_LOCATION=/u01/app/oraInventory
UNIX_GROUP_NAME=oinstall

$ ./runInstaller -silent -responseFile /home/oracle/ggresp.rsp

==================
-Inventory Verification
==================

You can confirm the installation of GoldenGate by simply looking at the Oracle inventory.
The inventory file is located at /u01/app/oraInventory/ContentsXML

-bash-3.2$ less inventory.xml | grep -i gg
<HOME NAME="OraHome15" LOC="/u01/app/ogg" TYPE="O" IDX="28"/>

Reference :
Oracle 11g GoldenGate Installation
https://oraclegurukul.blogspot.com/2015/01/oracle-11g-goldengate-installation.html

OGG-01033 There is a problem in network communication, a remote file problem, encryption keys for target and source do not match (if using ENCRYPT) or an unknown error.

Initial Load issue in  12c.

-- Report error.

2018-10-28 10:08:02  ERROR   OGG-01033  There is a problem in network communication, a remote file problem, encryption keys for target and source do not match (if using ENCRYPT) or an unknown error. (Remote file used is /u01/app/oracle/product/ogg/dirdat/initload.dat, reply received is Failed resolving output file /u01/app/oracle/product/ogg/dirdat/initload.dat (error: 2, No such file or directory)).
2018-10-28 10:08:02  

ERROR   OGG-01668  PROCESS ABENDING.

-- Solution.

This error will come in goldengate version 12.2, as new parameter ALLOWOUTPUTDIR  has been introduced , which need to be added in ./GLOBALS file on target .

syntax – ALLOWOUTPUTDIR   [ path of the remote trail location]

1. stop the manager and the replicates

ggsci> stop mgr

ggsci> stop *

ggsci> edit params ./GLOBAL     <-------------------- Put the parameter ALLOWOUTPUTDIR

2.  edit the ./GLOBAL

ALLOWOUTPUTDIR /u01/ogg/dirdat/

3. stat mgr and replicates
ggsci> start mgr
ggsci> start *

-- Source 

./extract <param_file> <report_file> for checking any issue before deploying automate.

Active-Active Replication with Conflict Detection and Resolution Part 1

Let us look at a simple example to illustrate GoldenGate’s Active-Active Replication with an introduction to Conflict Detection and Resolution.

Let’s call the two sites we are going to use for Active-Active replication as Site A and Site B.

On Site A we will have the following groups created

• Extract – ext1
• Data Pump – dpump1
• Replicat – rep1

On Site B we will have the following groups created

• Extract – ext2
• Data Pump – dpump2
• Replicat – rep2

On Site A we have the following trails set up

Extract/Dump - lt    • aa – local extract trail which will be written to by ext1
RMttrail - rt             • ab – remote trail which will be processed by data pump extract group dpump1. This will be shipped over the network to Site B 

On Site B we have the following trails set up

Extract/Dump - ac     • ac – local extract trail which will be written to by ext2
RMttrail – ad             • ad – remote trail which will be processed by data pump extract group dpump2. This will be shipped over the network to Site A

Database setup

SQL> Alter user sh identified by sh account unlock;

User altered.

SQL> Conn sh/sh

Connected.

Create the following objects on both databases (Site A and Site B)

SQL> create table inventory
(prod_id number,
prod_category varchar2(20),
qty_in_stock number,
last_dml timestamp default systimestamp);

Table created.

SQL> alter table inventory add constraint pk_inventory primary key (prod_id) ;

Table altered.

SQL> grant all on inventory to GGS_ADMIN;

Grant succeeded.

CREATE OR REPLACE TRIGGER INVENTORY_CDR_TRG

BEFORE UPDATE

ON INVENTORY

REFERENCING NEW AS New OLD AS Old

FOR EACH ROW

BEGIN

IF SYS_CONTEXT ('USERENV','SESSION_USER') != 'GGS_ADMIN'

THEN

:NEW.LAST_DML := SYSTIMESTAMP;

END IF;

END;

/

Trigger created.

Create the extract (EXT1) and data pump (DPUMP1) on Site A

GGSCI (pdemvrhl061) 14> add extract ext1 tranlog begin now
EXTRACT added.

GGSCI (pdemvrhl061) 4> add exttrail /u01/app/GGS_ADMIN/dirdat/aa extract ext1
EXTTRAIL added.

GGSCI (pdemvrhl061) 16> add extract dpump1 exttrailsource /u01/app/GGS_ADMIN/dirdat/aa
EXTRACT added.

GGSCI (pdemvrhl061) 17> add rmttrail /u01/app/GGS_ADMIN/dirdat/ab extract dpump1
RMTTRAIL added.

GGSCI (pdemvrhl061) 14> edit params ext1

EXTRACT ext1
USERID GGS_ADMIN, PASSWORD GGS_ADMIN
EXTTRAIL /u01/app/GGS_ADMIN/dirdat/aa
TRANLOGOPTIONS EXCLUDEUSER GGS_ADMIN
TABLE sh.inventory,
GETBEFORECOLS (
ON UPDATE KEYINCLUDING (prod_category,qty_in_stock, last_dml),                                   # GETBEFORECOLS ( ON UPDATE ALL, ON DELETE ALL )
ON DELETE KEYINCLUDING (prod_category,qty_in_stock, last_dml));

GGSCI (pdemvrhl061) 15> edit params dpump1

EXTRACT dpump1
USERID GGS_ADMIN, PASSWORD GGS_ADMIN
RMTHOST 10.32.206.62, MGRPORT 7809, TCPBUFSIZE 100000
RMTTRAIL /u01/app/GGS_ADMIN/dirdat/ab
PASSTHRU
TABLE sh.inventory;

On site B add replicat (REP1)

GGSCI (pdemvrhl061) 10> edit params ./GLOBAL

            GGSCHEMA ggate

            CHECKPOINTTABLE ggs_admin.checkpoint

GGSCI (pdemvrhl061) 20> add checkpointtable ggs_admin.checkpoint

GGSCI (pdemvrhl061) 21> ADD REPLICAT rep1, EXTTRAIL /u01/app/gg/dirdat/ab, checkpointtable ggs_admin.checkpoint

REPLICAT added.

GGSCI (pdemvrhl062) 10> edit params rep1

REPLICAT rep1
ASSUMETARGETDEFS
USERID GGS_ADMIN, PASSWORD GGS_ADMIN
DISCARDFILE /u01/app/GGS_ADMIN/discard.txt, append,
MAP sh.inventory, TARGET sh.inventory;

Create the extract (EXT2) and data pump (DPUMP2) on Site B

GGSCI (pdemvrhl062) 3> add extract ext2 tranlog begin now
EXTRACT added.

GGSCI (pdemvrhl062) 4> add exttrail /u01/app/GGS_ADMIN/dirdat/ac extract ext2
EXTTRAIL added.

GGSCI (pdemvrhl062) 5> add extract dpump2 exttrailsource /u01/app/GGS_ADMIN/dirdat/ac
EXTRACT added.

GGSCI (pdemvrhl062) 6> add rmttrail /u01/app/GGS_ADMIN/dirdat/ad extract dpump2
RMTTRAIL added.

GGSCI (pdemvrhl062) 31> edit params ext2

EXTRACT ext2
USERID GGS_ADMIN, PASSWORD GGS_ADMIN
EXTTRAIL /u01/app/GGS_ADMIN/dirdat/ac
TRANLOGOPTIONS EXCLUDEUSER GGS_ADMIN
TABLE sh.inventory,
GETBEFORECOLS (
ON UPDATE KEYINCLUDING (prod_category,qty_in_stock, last_dml),
ON DELETE KEYINCLUDING (prod_category,qty_in_stock, last_dml));

GGSCI (pdemvrhl062) 32> edit params dpump2

EXTRACT dpump2
USERID GGS_ADMIN, PASSWORD GGS_ADMIN
RMTHOST 10.32.206.61, MGRPORT 7809, TCPBUFSIZE 100000
RMTTRAIL /u01/app/GGS_ADMIN/dirdat/ad
PASSTHRU
TABLE sh.inventory;

On site A add replicat (REP2)

GGSCI (pdemvrhl061) 10> edit params ./GLOBAL

            GGSCHEMA ggate

            CHECKPOINTTABLE ggs_admin.checkpoint

GGSCI (pdemvrhl061) 20> add checkpointtable ggs_admin.checkpoint

GGSCI (pdemvrhl061) 21> ADD REPLICAT rep2, EXTTRAIL /u01/app/gg/dirdat/ad, checkpointtable ggs_admin.checkpoint

REPLICAT added.

GGSCI (pdemvrhl061) 10> edit params rep2

REPLICAT rep2

ASSUMETARGETDEFS

USERID ggs_admin, PASSWORD ggs_admin

DISCARDFILE /u01/app/gg/discard.txt, append,

MAP sh.inventory, TARGET sh.inventory; 

On both Site A and Site B, add trandata

GGSCI (pdemvrhl061) 17> dblogin userid GGS_ADMIN password GGS_ADMIN
Successfully logged into database.

GGSCI (pdemvrhl061) 12> add trandata sh.inventory cols (prod_category,qty_in_stock, last_dml)

Logging of supplemental redo data enabled for table SH.INVENTORY.

GGSCI (pdemvrhl061) 13> info trandata sh.inventory

Logging of supplemental redo log data is enabled for table SH.INVENTORY.

Columns supplementally logged for table SH.INVENTORY: PROD_ID, PROD_CATEGORY, QTY_IN_STOCK, LAST_DML.

GGSCI (pdemvrhl062) 18> dblogin userid GGS_ADMIN password GGS_ADMIN
Successfully logged into database.

GGSCI (pdemvrhl062) 14> add trandata sh.inventory cols (prod_category,qty_in_stock, last_dml)

Logging of supplemental redo data enabled for table SH.INVENTORY.

GGSCI (pdemvrhl062) 15> info trandata sh.inventory

Logging of supplemental redo log data is enabled for table SH.INVENTORY.

Columns supplementally logged for table SH.INVENTORY: PROD_ID, PROD_CATEGORY, QTY_IN_STOCK, LAST_DML.

Start the Extract and Data Pump process on Site A

GGSCI (pdemvrhl061) 31> start extract ext1

Sending START request to MANAGER …
EXTRACT EXT1 starting

GGSCI (pdemvrhl061) 23> start extract dpump1

Sending START request to MANAGER …
EXTRACT DPUMP1 starting

GGSCI (pdemvrhl061) 32> info extract ext1

EXTRACT EXT1 Last Started 2013-03-22 17:12 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:03 ago)
Log Read Checkpoint Oracle Redo Logs
2013-03-22 17:12:14 Seqno 250, RBA 30170624
SCN 0.6827610 (6827610)

GGSCI (pdemvrhl061) 34> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING DPUMP1 00:00:00 00:00:07
EXTRACT RUNNING EXT1 00:00:00 00:00:03

Start the Extract and Data Pump process on Site B

GGSCI (pdemvrhl062) 22> start extract ext2

Sending START request to MANAGER …
EXTRACT EXT2 starting

GGSCI (pdemvrhl062) 23> start extract dpump2

Sending START request to MANAGER …
EXTRACT DPUMP2 starting

GGSCI (pdemvrhl062) 24> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING DPUMP2 00:00:00 00:26:01
EXTRACT RUNNING EXT2 00:00:00 00:00:09

On Site A start the Replicat process REP1

GGSCI (pdemvrhl061) 38> start replicat rep1

Sending START request to MANAGER …
REPLICAT REP1 starting

GGSCI (pdemvrhl061) 39> status replicat rep1
REPLICAT REP1: RUNNING

On Site B start the Replicat process REP2

GGSCI (pdemvrhl062) 26> start replicat rep2

Sending START request to MANAGER …
REPLICAT REP2 starting

GGSCI (pdemvrhl062) 27> status replicat rep2
REPLICAT REP2: RUNNING

INSERT a row from Site A

SQL> select name from v$database;

NAME
———
TESTDB1

SQL> insert into inventory values (100,’TV’,100,sysdate);

1 row created.

SQL> commit;

Commit complete.

Check if row is replicated on Site B

SQL> select name from v$database;

NAME
———
TESTDB2

SQL> select * from inventory;

PROD_ID PROD_CATEGORY QTY_IN_STOCK LAST_DML
———- ——————– ———— ———
100 TV 100 22-MAR-13

From Site B now INSERT another record

SQL> insert into inventory values (101,’DVD’,10,sysdate);

1 row created.

SQL> commit;

Commit complete.

From Site A check if the replication has taken place

SQL> select * from inventory;

PROD_ID PROD_CATEGORY QTY_IN_STOCK LAST_DML
———- ——————– ———— ———
100 TV 100 22-MAR-13
101 DVD 10 22-MAR-13

Note: Simultaneously transaction different session so which is one to perform 1^st DML then commit that’s write on disk.

configuring DDL synchronization Part 7

In addition to providing replication support for all DML statements, we can also configure the GoldenGate environment to provide DDL support as well.

A number of prerequisite setup tasks need to be performed which we willl highlight here.

Run the following scripts from the directory where the GoldenGate software was installed.

The assumption here is that the database user GGS_ADMIN has already been created and granted the required roles and privileges as discussed in our earlier tutorial.

Note - run the scripts as SYSDBA

SQL> @marker_setup

Marker setup script

You will be prompted for the name of a schema for the GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:GGS_ADMIN

Marker setup table script complete, running verification script...

Please enter the name of a schema for the GoldenGate database objects:

Setting schema name to GGS_ADMIN

MARKER TABLE

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

OK

MARKER SEQUENCE

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

OK

Script complete.

SQL> alter session set recyclebin=OFF;

Session altered.

SQL> @ddl_setup

GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

You will be prompted for the name of a schema for the GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: On Oracle 10g and up, system recycle bin must be disabled.

NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:GGS_ADMIN

You will be prompted for the mode of installation.

To install or reinstall DDL replication, enter INITIALSETUP

To upgrade DDL replication, enter NORMAL

Enter mode of installation:INITIALSETUP

Working, please wait ...

Spooling to file ddl_setup_spool.txt

Using GGS_ADMIN as a GoldenGate schema name, INITIALSETUP as a mode of installation.

Working, please wait ...

RECYCLEBIN must be empty.

This installation will purge RECYCLEBIN for all users.

To proceed, enter yes. To stop installation, enter no.

Enter yes or no:yes

DDL replication setup script complete, running verification script...

Please enter the name of a schema for the GoldenGate database objects:

Setting schema name to GGS_ADMIN

DDLORA_GETTABLESPACESIZE STATUS:

Line/pos   Error

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

No errors  No errors

CLEAR_TRACE STATUS:

Line/pos   Error

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

No errors  No errors

CREATE_TRACE STATUS:

Line/pos   Error

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

No errors  No errors

TRACE_PUT_LINE STATUS:

Line/pos   Error

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

No errors  No errors

INITIAL_SETUP STATUS:

Line/pos   Error

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

No errors  No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos   Error

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

No errors  No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos   Error

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

No errors  No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos   Error

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

No errors  No errors

DDL HISTORY TABLE

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

OK

DDL HISTORY TABLE(1)

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

OK

DDL DUMP TABLES

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

OK

DDL DUMP COLUMNS

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

OK

DDL DUMP LOG GROUPS

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

OK

DDL DUMP PARTITIONS

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

OK

DDL DUMP PRIMARY KEYS

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

OK

DDL SEQUENCE

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

OK

GGS_TEMP_COLS

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

OK

GGS_TEMP_UK

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

OK

DDL TRIGGER CODE STATUS:

Line/pos   Error

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

No errors  No errors

DDL TRIGGER INSTALL STATUS

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

OK

DDL TRIGGER RUNNING STATUS

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

ENABLED

STAYMETADATA IN TRIGGER

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

OFF

DDL TRIGGER SQL TRACING

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

0

DDL TRIGGER TRACE LEVEL

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

0

LOCATION OF DDL TRACE FILE

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

/u01/app/oracle/diag/rdbms/gavin/gavin/trace/ggs_ddl_trace.log

Analyzing installation status...

STATUS OF DDL REPLICATION

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

SUCCESSFUL installation of DDL Replication software components

Script complete.

SQL>

SQL> @role_setup

GGS Role setup script

This script will drop and recreate the role GGS_GGSUSER_ROLE

To use a different role name, quit this script and then edit the params.sql script to change

the gg_role parameter to the preferred name. (Do not run the script.)

You will be prompted for the name of a schema for the GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:GGS_ADMIN

Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.

Role setup script complete

Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

GRANT GGS_GGSUSER_ROLE TO

where  is the user assigned to the GoldenGate processes.

SQL> grant ggs_ggsuser_role to ggs_admin;

Grant succeeded.

SQL> @ddl_enable

Trigger altered.

SQL> @ddl_pin GGS_ADMIN

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Turn Recyclebin OFF

We need to set the parameter recyclebin to OFF via the ALTER SYSTEM SET RECYCLEBIN=OFF command in order to prevent this error which we will see if we try and configure DDL support and then start the Extract process.

2010-02-19 11:13:30 GGS ERROR 2003 RECYCLEBIN must be turned off. For 10gr2 and up, set RECYCLEBIN in parameter file to OFF. For 10gr1, set _RECYCLEBI
N in parameter file to FALSE. Then restart database and extract.
2010-02-19 11:13:30 GGS ERROR 190 PROCESS ABENDING.

Enable additional logging at the table level

Note- We had earlier enabled additional supplemental logging at the database level. Using the ADD TRANDATA command we now enable it at even the table level as this is required by GoldenGate for DDL support.

GGSCI (redhat346.localdomain) 5> DBLOGIN USERID ggs_admin, PASSWORD ggs_admin
Successfully logged into database.

GGSCI (redhat346.localdomain) 6> ADD TRANDATA scott.emp

Logging of supplemental redo data enabled for table SCOTT.EMP.

Edit the parameter file for the Extract process to enable DDL synchronization

We had earlier created a parameter file for an Extract process ext1. We now edit that parameter file and add the entry
DDL INCLUDE MAPPED

This means that DDL support is now enabled for all tables which have been mapped and in this case it will only apply to the SCOTT.EMP table as that is the only table which is being processed here. We can also use the INCLUDE ALL or EXCLUDE ALL or wildcard characters to specify which tables to enable the DDL support for.

GGSCI (redhat346.localdomain) 1> EDIT PARAM EXT1

EXTRACT ext1
USERID ggs_admin, PASSWORD ggs_admin
RMTHOST 10.53.100.100, MGRPORT 7809
RMTTRAIL /u01/app/gg/dirdat/rt
DDL INCLUDE MAPPED
TABLE scott.emp;

Test the same

We will now alter the structure of the EMP table by adding a column and we can see that this new table structure is also reflected on the target system.

On Source

SQL> ALTER TABLE EMP ADD NEW_COL VARCHAR2(10);
Table altered.

On Target

SQL> desc emp
Name Null? Type
—————————————– ——– —————————-
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(20)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
MYCOL VARCHAR2(10)
NEW_COL VARCHAR2(10)