Skip to content

GoldenGate 23ai configuration for Oracle to Oracle replication

Preamble

Every type I try the new version of GoldenGate I face, again and again, the same dilemma. Oracle is claiming that granting DBA to GoldenGate account is bad security principle:

If the GoldenGate Administrator user has the DBA role, additional object privileges are not needed. However, there might be security constraints granting the DBA role to the GoldenGate Administration user. The DBA role is not necessarily required for Oracle GoldenGate.

But in official documentation and in plenty of Oracle official documents the DBA role is systematically granted crushing the least privilege approach and they simply say:

Granting DBA role is not mandatory for every user. Privileges should be granted depending on the actions that the user needs to perform on the database. For example, to grant DML operation privileges to insert, update, and delete transactions to ggadmin, use the GRANT ANY INSERT/UPDATE/DELETE privileges and to further allow users to work with tables and indexes as part of DML operations, use the GRANT CREATE/DROP/ALTER ANY TABLE/INDEX privileges.
If privileges are missing, then the DBA has to grant necessary privileges additionally.

But I have never ever seen Oracle trying to make the exercise… I have tried for this blog post but it was really a pain…

My container database is in 19.28 Enterprise Edition and I have created two pluggable databases:

  • PDB1 my source pluggable database
  • PDB2 my target pluggable database

They are in the same container but I will act as if they are in different container on different servers, so using distpath processes to transfer EXTFILE (initial loading) and EXTTRAIL (normal flow). My server is a Virtual machine under VirtualBox with 4 threads and 8GB running Oracle Linux Server release 9.6.

Nowadays you obviously have to do the GoldenGate microservices architecture and in my 23ai version this is even mandatory:

Oracle GoldenGate Classic Architecture: Oracle GoldenGate 23ai no longer supports the Classic Architecture. This is a big one, and we have a Migration Utility to help you move from Classic to Microservices architecture.

The GoldenGate release I have used is 23ai (23.9) and all in running in a virtual machine under VirtualBox with Oracle Linux 9.6.

I have wasted a lot of time trying to follow the Oracle recommandation for the replicat process, without knowing that this type of process is not able to read EXTFILE. Only the non-integrated (classic) replicate process is able to read EXTFILE:

For best performance for an OLTP workload, parallel Replicat in non-integrated mode is recommended.

So you have two option, stick to non-integrated replicate and being able to have only one replicate process:

goldengate23ai_01
goldengate23ai_01

Or willing to have a performant parallel replicate process and have two distinct flows, one for initial loading (xxINIT processes) and one for normal operations (xx01 processes):

goldengate23ai_02
goldengate23ai_02

GoldenGate 23ai installation

For My Oracle Support (MOS), patches version take the Oracle GoldenGate for Oracle DB (Complete Install) (Patchset) release you plan to install (I have taken the latest at the time of writing this post). Then simply install the product, I have installed it in /u01/app/oracle/product/23.0.0/gghome_1 (might be a good idea to chose an home name that contains the database flavor of this GoldenGate installation).

If like me your /tmp is mounted with noxec option do (create your chosen folder before):

export TEMP=/u01/tmp

After this you need to create a deployment and install the required Service Manager (SM). This SM will be common to all database flavor deployments (one per host). Launch oggca.sh to create this SM and your Oracle deployment. Chosen installation directory cannot be in ORACLE_HOME. You can, better, choose /u01/goldengate_sm (not what I have initially chosen in below picture) has this directory will only contains the unique per host Service Manager (SM):

goldengate23ai_03
goldengate23ai_03

Administrator account and password:

goldengate23ai_04
goldengate23ai_04

Deployment directories cannot be located inside the Service Manager deployment directory.

goldengate23ai_05
goldengate23ai_05

Choosing same ggma for all:

goldengate23ai_06
goldengate23ai_06

Service Manager web admin page:

goldengate23ai_07
goldengate23ai_07

I am also using this manage_goldengate script to stop/start GoldenGate if my server reboot:

#!/bin/bash

# Check if a parameter is provided
if [ -z "$1" ]; then
  echo "Usage: $0 {start|stop|status}"
  exit 1
fi

export OGG_HOME=/u01/app/oracle/product/23.0.0/gghome_1/
export OGG_SRVMGR_DIRECTORY=/u01/goldengate_deployment
export OGG_ETC_HOME=$OGG_SRVMGR_DIRECTORY/etc
export OGG_VAR_HOME=$OGG_SRVMGR_DIRECTORY/var

case "$1" in
  start)
    echo "Starting the GoldenGate..."
    $OGG_SRVMGR_DIRECTORY/bin/startSM.sh
    ;;
  stop)
    echo "Stopping the GoldenGate..."
    $OGG_SRVMGR_DIRECTORY/bin/stopSM.sh
    ;;
  status)
    PID=$(cat "${OGG_VAR_HOME}/run/ServiceManager.pid" 2>/dev/null)
    if [ -n "$PID" ] && kill -0 "$PID" 2>/dev/null; then
      echo "Service Manager is running (PID: ${PID})"
    else
      echo "Service Manager is not running"
    fi
    ;;
  *)
    echo "Invalid parameter: $1"
    echo "Usage: $0 {start|stop|status}"
    exit 1
    ;;
esac

I have also downloaded and applied the latest available RU (23.9) for GoldenGate from https://www.oracle.com/security-alerts/. If same problem than me, take latest OPatch from Oracle database 23ai when upgrading it !

I strongly suggest to use the GoldenGate graphical interface to perform all your creations/modifications as it ease a lot the process. Those interfaces are accessible on all the port you have specified wile installing the product. So for me it will be http://localhost:9001:

goldengate23ai_08
goldengate23ai_08

GoldenGate 23ai database preparation

CDB level:

create pluggable database pdb1
admin user yjaquier identified by secure_password
roles = (dba)
default tablespace users datafile '/u01/app/oracle/oradata/ORCL1/pdb1/users01.dbf' size 100m autoextend off
file_name_convert = ('/u01/app/oracle/oradata/ORCL1/pdbseed/', '/u01/app/oracle/oradata/ORCL1/pdb1/');

Despite what official documentation says, I have not been able to activate supplemental logging at pluggable database level and, for me, it must be done at container level. When trying at pluggable database level I had all sort of errors displayed below. And all pluggable databases inherit the setting, not very granular, I hope it will be improved:

SQL> alter pluggable database pdb1 add supplemental log data subset database replication;
alter pluggable database pdb1 add supplemental log data subset database replication
*
ERROR at line 1:
ORA-65046: operation not allowed from outside a pluggable database

SQL> alter session set container=pdb1;

Session altered.

SQL> alter pluggable database pdb1 add supplemental log data subset database replication;
alter pluggable database pdb1 add supplemental log data subset database replication
*
ERROR at line 1:
ORA-31541: Supplemental logging is not enabled in CDB$ROOT.

So the script, waiting this story on pluggable database to be corrected:

alter system set enable_goldengate_replication = true scope = both;
alter system set undo_management = auto scope = both;
alter system set undo_retention = 86400 scope = both;
alter system set streams_pool_size = 1g;
alter database force logging;
alter database add supplemental log data subset database replication;
alter system switch logfile;
select * from cdb_supplemental_logging;
-- Below can be run on all pluggable databases
select * from dba_supplemental_logging;
select
  supplemental_log_data_min,supplemental_log_data_pk,
  supplemental_log_data_ui, supplemental_log_data_fk, 
  supplemental_log_data_all, supplemental_log_data_pl,
  supplemental_log_data_sr
from v$database;

Creating the c##ggadmin global account on source and target container databases, same for me for this blog post (privilege_type can be set to * to have same script everywhere and/or set bidirectional replication):

create user c##ggadmin identified by "secure_password" container=all default tablespace users quota unlimited on users temporary tablespace temp;

Privileges for capture:

grant connect to c##ggadmin container=all;
grant resource to c##ggadmin container=all; -- Heartbeat table (sequence)
grant create view to c##ggadmin container=all; -- Heatbeat table
grant select any dictionary to c##ggadmin container=all; -- Heatbeat table
grant create table to c##ggadmin container=all; -- Checkpoint table
exec dbms_goldengate_auth.grant_admin_privilege(grantee => 'c##ggadmin', privilege_type => 'CAPTURE', grant_optional_privileges => '*', container => 'ALL');
grant execute_catalog_role to c##ggadmin container=all;
alter user c##ggadmin set container_data=all container=current;
grant alter system to c##ggadmin container=all;
grant alter user to c##ggadmin container=all;
grant select any transaction to c##ggadmin container=all;
grant logmining to c##ggadmin container=all;
grant exp_full_database to c##ggadmin container=all;
grant execute any type to c##ggadmin container=all;
grant alter any table to c##ggadmin container=all; -- To be able to use ADD TRANDATA

Privileges for apply:

--create user c##ggadmin identified by "secure_password" container=all default tablespace users temporary tablespace temp;
--alter user c##ggadmin quota unlimited on users;
grant connect to c##ggadmin container=all;
grant create view to c##ggadmin container=all;
grant select any dictionary to c##ggadmin container=all; -- Heatbeat table
grant create table to c##ggadmin container=all;
grant resource to c##ggadmin container=all; -- Heartbeat table (sequence)
grant create any table to c##ggadmin container=all; -- Replicate DDL
grant insert any table to c##ggadmin container=all; -- Replicate DML, individual grants can be given per object but painful
grant update any table to c##ggadmin container=all; -- Replicate DML, individual grants can be given per object but painful
grant delete any table to c##ggadmin container=all; -- Replicate DML, individual grants can be given per object but painful
grant drop any table to c##ggadmin container=all; -- To be able to TRUNCATE in another schema and DDL support
exec dbms_goldengate_auth.grant_admin_privilege(grantee => 'c##ggadmin', privilege_type => 'APPLY', grant_optional_privileges => '*', container => 'ALL');

Support for Oracle Sequences:

SQL> @/u01/app/oracle/product/23.0.0/gghome_1/lib/sql/legacy/sequence.sql
Please enter the name of a schema for the GoldenGate database objects:
c##ggadmin
Setting schema name to C##GGADMIN

Once this GoldenGate super account has been created take the opportunity to create all required database connections (source container, source pluggable, target pluggable):

goldengate23ai_09
goldengate23ai_09

I have also modified the GLOBALS parameters either with graphical interface or using:

OGG (not connected) 1> connect http://192.168.56.101:9000 as ggma password "secure_password*"
Using default deployment 'deployment01'

OGG (http://192.168.56.101:9000 deployment01) 2> edit globals
2025-07-25T10:08:29Z  INFO    OGG-10183  Parameter file GLOBALS passed validity check.

OGG (http://192.168.56.101:9000 deployment01) 3> view globals
GGSCHEMA c##ggadmin

Source pluggable database preparation

Create user on source pluggable database (PDB1) and target pluggable database (PDB2):

SQL> alter session set container=pdb1;

Session altered.

SQL> drop user app01 cascade;

User dropped.

SQL> create user app01 identified by "secure_password"
     default tablespace users
     quota unlimited on users
     temporary tablespace temp;

User created.

SQL> grant connect, resource to app01;

Grant succeeded.

Then create below data model on PDB1 (I plan to instantiate it on target pluggable database) with app01 user. I have added a primary key in my employees table to be in best possible configuration (without a PK all columns are used for unicity):

SQL> connect app01/"secure_password"@pdb1
Connected.
SQL> create table employees (
     id number not null,
     name varchar2(50),
     constraint employees_pk primary key (id) enable);

Table created.

SQL> desc employees
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 NAME                                               VARCHAR2(50)

SQL> insert into employees values(1, 'Yannick Jaquier');

1 row created.

SQL> commit;

Commit complete.

You need then to add supplemental logging on your table (can be also done at schema level). For table level, only, you can also use the database command:

alter table app01.employees add supplemental log data(primary key) columns;

But it is strongly recommended to do it with GoldenGate admin client:

[oracle@server01 ~]$ adminclient
Oracle GoldenGate Administration Client for Oracle
Version 23.9.0.25.07 OGGCORE_23.9.0.0.0OGGRU_LINUX.X64_250714.1653_FBO

Copyright (C) 1995, 2025, Oracle and/or its affiliates. All rights reserved.

Linux, x64, 64bit (optimized) on Jul 14 2025 22:59:48
Operating system character set identified as UTF-8.

OGG (not connected) 1> connect http://192.168.56.101:9000 as ggma password "secure_password"
Using default deployment 'deployment01'

OGG (http://192.168.56.101:9000 deployment01) 2> info credentialstore

Default domain: OracleGoldenGate

  Alias: orcl1
  Userid: c##ggadmin@server01.domain.com:1531/orcl1

  Alias: pdb2
  Userid: c##ggadmin@server01.domain.com:1531/pdb2

  Alias: pdb1
  Userid: c##ggadmin@server01.domain.com:1531/pdb1

OGG (http://192.168.56.101:9000 deployment01) 2> dblogin useridalias pdb1
Successfully logged into database.

OGG (http://192.168.56.101:9000 deployment01 as pdb1@ORCL1) 3> add trandata app01.employees
2025-07-10T15:54:43Z  INFO    OGG-15132  Logging of supplemental redo data enabled for table APP01.EMPLOYEES.
2025-07-10T15:54:43Z  INFO    OGG-15133  TRANDATA for scheduling columns has been added on table APP01.EMPLOYEES.
2025-07-10T15:54:43Z  INFO    OGG-15438  Enabling logical replication (with all keys) on auto capture table APP01.EMPLOYEES.
2025-07-10T15:54:43Z  INFO    OGG-15135  TRANDATA for instantiation CSN has been added on table APP01.EMPLOYEES.

Doing it with GoldenGate adminclient is also a nice option to see it afterwards if you have not forgotten something (can also be done with graphical interface):

OGG (http://192.168.56.101:9000 deployment01 as pdb1@ORCL1) 4> info trandata app01.*

Logging of supplemental transaction log data is enabled for table APP01.EMPLOYEES.

Logical Replication for table APP01.EMPLOYEES is enabled.

Columns supplementally logged for table APP01.EMPLOYEES:
  - "ID"

Prepared CSN for table APP01.EMPLOYEES: 3595148

GoldenGate 23ai initial loading preparation

The GoldenGate initial loading process will only copy data from source to target, the objects MUST be created on target database first. This GoldenGate instantiation is reserved for small objects, for larger implementation you should move to RMAN, DataGuard-Split or Split-Mirror Copy.

The teammate of GoldenGate instantiation is Data Pump to transfert meta data. Using default Data Pump directory on source pluggable databse (PDB1):

SQL> select directory_path from dba_directories where directory_name='DATA_PUMP_DIR';

DIRECTORY_PATH
--------------------------------------------------------------------------------
/u01/app/oracle/admin/orcl1/dpdump/38C891EBF32C72B7E0636538A8C0AAA0

Export meta data of your objects (you can also work at schema level):

[oracle@server01 ~]$ expdp \"sys@pdb1 as sysdba\" directory=data_pump_dir dumpfile=app01.dmp content=metadata_only tables=app01.employees

Export: Release 19.0.0.0.0 - Production on Thu Jul 10 14:59:31 2025
Version 19.27.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLE_01":  "sys/********@pdb1 AS SYSDBA" directory=data_pump_dir dumpfile=app01.dmp content=metadata_only tables=app01.employees flashback_scn=3473336
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/orcl1/dpdump/38C891EBF32C72B7E0636538A8C0AAA0/app01.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Thu Jul 10 15:00:03 2025 elapsed 0 00:00:26

Transfer the file to target system (for me it is a simple copy to target DATA_PUMP_DIR directory):

[oracle@server01 ~]$ cp /u01/app/oracle/admin/orcl1/dpdump/38C891EBF32C72B7E0636538A8C0AAA0/app01.dmp /u01/app/oracle/admin/orcl1/dpdump/38CB046F8C117ED9E0636538A8C0C456

Then import the data pump file:

[oracle@server01 ~]$ impdp \"sys@pdb2 as sysdba\" directory=data_pump_dir dumpfile=app01.dmp content=metadata_only

Import: Release 19.0.0.0.0 - Production on Thu Jul 10 15:02:44 2025
Version 19.27.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "sys/********@pdb2 AS SYSDBA" directory=data_pump_dir dumpfile=app01.dmp content=metadata_only
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Thu Jul 10 15:03:33 2025 elapsed 0 00:00:38

I insert a line in my table to be closer to a real use case:

SQL> insert into employees values(1, 'Yannick Jaquier');

1 row created.

SQL> commit;

Commit complete.

GoldenGate 23ai normal flow extract process

As I do Oracle to Oracle I can try DDL replication:

DDL replication is only supported between Oracle to Oracle databases and between MySQL to MySQL databases.

You can do it with the graphical interface or use those commands:

add extract ext01 integrated tranlog begin now

dblogin useridalias orcl1

register extract ext01 database container (pdb1)

edit params ext01

view params ext01

dblogin useridalias orcl1

register extract ext01 database container (pdb1)

add exttrail ext01/ex, extract ext01, megabytes 1

My EXT01, extract process, has those parameters:

EXTRACT ext01
USERIDALIAS orcl1 DOMAIN OracleGoldenGate
EXTTRAIL ext01/ex
DDL INCLUDE MAPPED --Include DDL only for concerned objects
SOURCECATALOG PDB1
TABLE app01.employees;

Do not start it for now we will start it after the initial loading and the distpath is in place… On the graphical interface do not use “Create and run” and use only “Create”.

Monitor closely the very important GoldenGate log file, that is, for me:

tail -f -n 100 /u01/goldengate_deployment01/var/log/ggserr.log

You can get it by getting details of any process:

OGG (http://192.168.56.101:9000 deployment01) 2> info extract extinit detail

Extract    EXTINIT   Last Started 2025-07-25 09:57   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table PDB1.APP01.EMPLOYEES
                     2025-07-25 09:57:02  Record 1
Task                 SOURCEISTABLE
Encryption Profile   LocalWallet

  Extract Source                          Begin             End

  Database                                2025-07-25 09:57  2025-07-25 09:57:02.52
  Database                                * Initialized *   First Record


Current directory    /home/oracle

Report file          /u01/goldengate_deployment01/var/lib/report/EXTINIT.rpt
Parameter file       /u01/goldengate_deployment01/etc/conf/ogg/EXTINIT.prm
Checkpoint file      /u01/goldengate_deployment01/var/lib/checkpt/EXTINIT.cpe
Process file         /u01/goldengate_deployment01/var/run/EXTINIT.pce
Error log            /u01/goldengate_deployment01/var/log/ggserr.log

GoldenGate 23ai initial load extract process

Get the current SCN with the query provided by Oracle:

-- Query for active transactions
SELECT T.START_SCN, T.STATUS TSTATUS, T.START_DATE,
       S.SID, S.SERIAL#, S.INST_ID, S.USERNAME, S.OSUSER, S.STATUS SSTATUS, S.LOGON_TIME
FROM gv$transaction T
INNER JOIN gv$session S ON s.saddr = t.ses_addr
UNION ALL
--
-- Query for current status
SELECT CURRENT_SCN, 'CURRENT', CURRENT_DATE,
       NULL, NULL, NULL, 'SYS', NULL, NULL, NULL
from v$database
ORDER BY 1;

SCN is 4110145 for me… Keep this SCN in mind we will use it, again, later on…

Create an Initial Load Extract process (equivalent of ADD EXTRACT extinit SOURCEISTABLE) with the graphical interface or those commands:

add extract extinit sourceistable
edit params extinit
view params extinit
start extract extinit

Use this parameter file:

EXTRACT extinit
USERIDALIAS orcl1 DOMAIN OracleGoldenGate
EXTFILE extinit/ex, MEGABYTES 1, PURGE
SOURCECATALOG PDB1
TABLE app01.employees, SQLPREDICATE "AS OF SCN 4110145";

Remark:
Default directory of trail files is /u01/goldengate_deployment01/var/lib/data, so for me:

[oracle@server01 ~]$ ll /u01/goldengate_deployment01/var/lib/data/extinit/
total 4
-rw-r----- 1 oracle dba 1878 Jul 11 14:46 ex000000

This initial load extract start, run and stop once its job is done !

GoldenGate 23ai Distribution paths process

We need to transfer those EXTFILE files and EXTTRAIL trail files to target system to be then ingested by target database. Start by creating an identifier on source system to access to your remote system:

OGG (not connected) 1> connect http://192.168.56.101:9000 as ggma password "secure_password"
Using default deployment 'deployment01'

OGG (http://192.168.56.101:9000 deployment01) 2> alter credentialstore add user ggma domain Network password "secure_password"
OGG (http://192.168.56.101:9000 deployment01) 3> info credentialstore domain Network

Domain: Network

Alias: ggma
Userid: ggma

Either use the graphical interface or those commands for EXTFILE transfer (if you have chosen to target performance transfer the EXTFILE to repinit/ex instead):

add distpath distinit
source trail://192.168.56.101:9002/services/v2/sources?trail=extinit/ex
target ws://192.168.56.101:9003/services/v2/targets?trail=rep01/ex
authentication useridalias ggma domain Network

start distpath distinit

For EXTTRAIL transfer:

add distpath dist01
source trail://192.168.56.101:9002/services/v2/sources?trail=ext01/ex
target ws://192.168.56.101:9003/services/v2/targets?trail=rep01/ex
authentication useridalias ggma domain Network

alter distpath dist01 target sizemb 1

stop distpath distinit
start distpath dist01

If EXTFILE and EXTTRAIL end up in same directory both distpath process cannot write at same time so you must stop distinit process once it has transferred everything or you get:

2025-07-14T11:48:03.952+0200  ERROR   OGG-08518  Oracle GoldenGate Distribution Service for Oracle:  Request on path dist01 failed, which is caused by 'An unrecoverable error was detected by the remote side. (Reply received is 'Conflict detected, duplicate request for target trail file received. Trail is also being written by path 'distinit'')'.

GoldenGate 23ai normal flow extract process starting

Once the intial loading process has been done it is interesting, for testing, to insert a row to see if they are taken into account by the normal flow processes…

Start the normal flow extract process after the same exact SCN extracted when creating the initial loading extract process:

start extract ext01 aftercsn 4110145

GoldenGate 23ai replicat processes

Integrated replicat is deprecated and the clear way to go is parallel non-integrated:

Integrated Replicat: Integrated Replicat is deprecated in Oracle GoldenGate 23ai. As an alternative, users should transition to using parallel Replicat.

If you are in the scenario of one replicat then this one MUST be in classic mode for EXTFILE:

add replicat rep01 exttrail rep01/ex checkpointtable c##ggadmin.checkpoint
edit params rep01
show params rep01
start replicat rep01

My REP01, replicat process, had those parameters:

REPLICAT rep01
USERIDALIAS pdb2 DOMAIN OracleGoldenGate
SOURCECATALOG PDB1
MAP app01.*, TARGET app01.*;

If you have two processes then repinit must be created as above while for the rep01 you can now target performance:

add replicat parallel rep01 exttrail rep01/ex checkpointtable c##ggadmin.checkpoint
edit params rep01
show params rep01
start replicat rep01

The used parameters are exactly the same…

GoldenGate 23ai User Defined Type (UDT) and DDL testing

I have also tried with a data model that includes an insert with User Defined Type (UDF):

SQL> create type app01.address_t as object (
  street varchar2(50),
  city varchar2(30),
  zip_code varchar2(10));
/

Type created.

SQL> drop table app01.employees;

Table dropped.

SQL> create table app01.employees (
     id number not null,
     name varchar2(50),
     address address_t,
     constraint employees_pk primary key (id) enable);

Table created.

SQL> desc employees
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 NAME                                               VARCHAR2(50)
 ADDRESS                                            ADDRESS_T

SQL> insert into employees values(1, 'Yannick Jaquier', address_t('39 Chemin du Champ-des-Filles', 'Plan-les-Ouates', '1228'));

1 row created.

SQL> commit;

Commit complete.

DDL trial (can also do a TRUNCATE):

SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL> desc app01.employees
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 NAME                                               VARCHAR2(50)

SQL> alter table app01.employees add (address varchar2(100));

Table altered.

SQL> desc app01.employees
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 NAME                                               VARCHAR2(50)
 ADDRESS                                            VARCHAR2(100)

GoldenGate 23ai: clean all extract, distpath and replicat process

If doing multiple testing it is really helpful to create a script (clean.txt) like:

stop distpath all
stop er *
delete distpath distinit
delete distpath dist01
delete replicat rep01
delete extract extinit
delete extract ext01
delete exttrail ext01/ex

And execute it with:

OGG (http://192.168.56.101:9000 deployment01) 2> obey /home/oracle/clean.txt

Conclude by purging the trail files:

rm -rf /u01/goldengate_deployment01/var/lib/data/*

GoldenGate 23ai initial load with expdp/impdp

If your source database is big and made of very big tables it is strongly suggested, by Oracle, to perform your initial loading using Export and Import Data Pump. The key option is FLASHBACK_SCN:

# The following example is with the same SCN that I have used with GoldenGate Initial loading. The SQL query to obtain it, is the same
[oracle@server01 ~]$ expdp \"sys@pdb1 as sysdba\" directory=data_pump_dir dumpfile=app01.dmp tables=app01.employees flashback_scn=4110145

Remark:
Instead of TABLES parameter you can also use SCHEMAS.

Then load it on target database. This time it will create the skeleton of the tables as well as loading the data till SCN 4110145:

[oracle@server01 ~]$ impdp \"sys@pdb2 as sysdba\" directory=data_pump_dir dumpfile=app01.dmp

Issues encountered

OCI-21700: object does not exist or is marked for deletion

Complete error is:

Oracle GoldenGate Capture for Oracle, EXT01.prm: Error 21700 on OCI call [getting typecode] - 
SQL> connect c##ggadmin/"secure_password"@orcl1
Connected.
SQL> alter session set container=pdb1;

Session altered.

SQL> desc app01.employees;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 NAME                                               VARCHAR2(50)
 ADDRESS                                            APP01.ADDRESS_T

SQL> select * from  app01.employees;
ERROR:
OCI-22303: type "APP01"."ADDRESS_T" not found
SQL> grant execute any type to c##ggadmin container=all;
SQL> connect c##ggadmin/"secure_password"@orcl1
Connected.
SQL> alter session set container=pdb1;

Session altered.

SQL> select * from  app01.employees;

no rows selected

OGG-02026 Attempt to set checkpoint retention to value 7.000000 failed.

Complete error is:

2025-07-03 15:04:13  ERROR   OGG-02026  Attempt to set checkpoint retention to value 7.000000 failed.
2025-07-03 15:04:13  ERROR   OGG-00662  OCI Error ORA-06550: line 1, column 121:
PLS-00201: identifier 'DBMS_CAPTURE_ADM' must be declared
ORA-06550: line 1, column 121:
PL/SQL: Statement ignored
Help: https://docs.oracle.com/error-help/db/ora-06550/
 (status = 6550).

Solve with:

grant execute_catalog_role to c##ggadmin container=all;

ORA-01031: insufficient privileges

Comple error is:

2025-07-03 15:13:27  ERROR   OGG-02028  Failed to attach to logmining server OGG$CAP_EXT01 error 1,031 - ORA-01031: insufficient privileges
Help: https://docs.oracle.com/error-help/db/ora-01031/.

Or in alert.log file:

GoldenGate CAPTURE CP01 for OGG$CAP_EXT01 with pid=68, OS id=16960 stopped
Errors in file /u01/app/oracle/diag/rdbms/orcl1/orcl1/trace/orcl1_cp01_16960.trc:
ORA-26922: user C##GGADMIN does not have LOGMINING privilege

Testing it live:

SQL> exec dbms_logmnr.start_logmnr()
BEGIN dbms_logmnr.start_logmnr(); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_LOGMNR", line 72
ORA-06512: at line 1

Found in the official documentation:

SQL> grant logmining to c##ggadmin container=all;

Grant succeeded.

Cannot obtain Pluggable Database information for metadata handle 844,424,933,290,867

2025-07-04T10:48:17.922+0200 ERROR OGG-06215 Oracle GoldenGate Capture for Oracle, EXT01.prm: Cannot obtain Pluggable Database information for metadata handle 844,424,933,290,867.
2025-07-04T10:48:18.886+0200 ERROR OGG-02078 Oracle GoldenGate Capture for Oracle, EXT01.prm: Extract encountered a fatal error in a processing thread and is abending.
2025-07-04T10:48:18.948+0200 ERROR OGG-02171 Oracle GoldenGate Capture for Oracle, EXT01.prm: Error reading LCR from data source. Status 510, data source type TranLogDataSource.
2025-07-04T10:48:18.948+0200 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, EXT01.prm: PROCESS ABENDING.

Absolutely no reference on Internet about it, if you grant DBA role then error will gone. I found in DBMS_METADATA package reference the following:

For all objects that have passwords, except database links (for example, users and roles), the following rules apply:

  • A user who has the SELECT_CATALOG_ROLE can see all metadata for an object except the passwords for that object.
  • The SYS user, users who have the EXP_FULL_DATABASE role, and users who own an object can see all metadata for that object, including passwords.

So tried below and it worked:

SQL> grant exp_full_database to c##ggadmin container=all;

Grant succeeded.

OGG-02926 Missing end generated transaction marker while PR/IR end processing initial load trail file and switching to trail file ‘%s’, number of records read %d.

This one is really tricky and I add to open a Service Request with Oracle to get the answer:

EXTFILE is an extract file type, typically a flat file created by the Extract process using the EXTFILE parameter.

Parallel Replicat (Integrated or Nonintegrated) is designed to read Oracle GoldenGate trail files (local/remote trails, often named like ./dirdat/aa), not simple extract files (EXTFILE).

Classic Replicat can read both trail files and extract files (EXTFILE and EXTTRAIL).

So if you want to use GoldenGate for your initial loading you have to use a classic replicat or create two flows with one using parallel replicat for the normal flow…

OGG-02024 An attempt to gather information about the logmining server configuration from the Oracle database failed.

The complete error message is:

2025-07-24T17:30:40.306+0200  ERROR   OGG-02024  Oracle GoldenGate Capture for Oracle, EXT01.prm:  An attempt to gather information about the logmining server configuration from the Oracle database failed.
2025-07-24T17:30:40.306+0200  ERROR   OGG-10556  Oracle GoldenGate Capture for Oracle, EXT01.prm:  No data found when executing SQL statement SELECT apply_name  FROM all_apply  WHERE apply_name = SUBSTR(UPPER('OGG$' || :1), 1, 30).
2025-07-24T17:30:40.383+0200  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, EXT01.prm:  PROCESS ABENDING.

Missing command:

register extract ext01 database

References

About Post Author

Share the knowledge!

Comments

  1. am using Non-Secure (HTTP/WS) from source. encounter the following error
    ERROR OGG-08518 Request on path ORAPP failed, which is caused by ‘Receive TCP params error: ‘.

Leave a Reply

Your email address will not be published. Required fields are marked *