Setup Oracle GoldenGate 23ai replication between two Oracle DBs
In this post we will setup a Oracle GoldenGate 23ai replication between two Oracle databases (19c and 23ai) on two different machines.
The initial replication setup contains an existing table (user1.t) with 1 million rows on the source database (19c) that will be replicated during the Initial Load to the destination database (23ai). After the initial load the regular pdb level extract and replicat will continue to replicate changes to the source table. We will also enable DDL replication which enables us to create new objects in the source schema that will be created on the target db. Since the databases are on two different virtual machines we need Distribution Paths that copy the Oracle GoldenGate trail files from the source to the destination system.
Table of Contents
Prerequisites
To start our replication setup we need the following environment:
- One source VM containing a patched Oracle 19c database. The installation is described here and here. In my case the hostname is lin7.
- One destination VM containing a Oracle 23ai database. Installation is described here. In my case the hostname is lin8.
The first task is to install and patch the Oracle GoldenGate 23ai software on both systems (including self signed SSL certificates) and create two new deployments (Service Manager and User Deployment) on each VM. These steps are documented here. In my case the name of the source deployment is depl1 and the name of the target deployment is depl2.
Setup the source and target databases
In the upcoming steps we will create a GoldenGate admin user (ggadmin) in the two pluggable databases (pdb1) and a user1 database user which contains the objects to replicate. An initial table t with 1 million rows will be created on the source database. Several required privileges are granted and we enable database archiving and force logging. This is needed so that all required information is written to the redo and archivelogs. After that we will setup the two database connections in Oracle GoldenGate and create a so called checkpoint and heartbeat table in the ggadmin schema(s).
# run as root on the source VM
cat >/tmp/1.sql <<EOF
alter session set container=pdb1;
create user ggadmin identified by changeme;
grant dba to ggadmin container=current;
exec DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(GRANTEE=>'ggadmin', PRIVILEGE_TYPE=>'CAPTURE', CONTAINER=>'CURRENT')
@/u01/app/ogg/ogg23ai_ora/lib/sql/legacy/sequence.sql ggadmin
exec dbms_streams_auth.grant_admin_privilege('GGADMIN')
grant dba to user1 identified by changeme;
-- create table user1.t containing 1m rows
create table user1.t tablespace users as select a2.* from all_objects a1, all_objects a2 where rownum<=1e6 order by a1.object_id;
conn / as sysdba
alter system set enable_goldengate_replication=true scope=spfile;
alter database force logging;
alter database add supplemental log data;
shutdown immediate
startup mount
alter database archivelog;
alter database open;
exit
EOF
su - oracle -c ". ora19.env; sqlplus / as sysdba @/tmp/1.sql"
Sample Output (click to expand):
[root@lin7 ~]# su - oracle -c ". ora19.env; sqlplus / as sysdba @/tmp/1.sql"
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 26 08:59:33 2025
Version 19.25.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.0.0
Session altered.
User created.
Grant succeeded.
PL/SQL procedure successfully completed.
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGADMIN
UPDATE_SEQUENCE STATUS:
Line/pos
----------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors
GETSEQFLUSH
Line/pos
----------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors
SEQTRACE
Line/pos
----------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors
MOVETARGETHWM
Line/pos
----------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors
REPLICATE_SEQUENCE STATUS:
Line/pos
----------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors
STATUS OF SEQUENCE SUPPORT
--------------------------------------------------------------
SUCCESSFUL installation of Oracle Sequence Replication support
PL/SQL procedure successfully completed.
Grant succeeded.
Table created.
Connected.
System altered.
Database altered.
Database altered.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 2432693880 bytes
Fixed Size 9180792 bytes
Variable Size 805306368 bytes
Database Buffers 1610612736 bytes
Redo Buffers 7593984 bytes
Database mounted.
Database altered.
Database altered.
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.0.0
[root@lin7 ~]#
# run as root on the target VM
cat >/tmp/1.sql <<EOF
alter session set container=pdb1;
grant connect, resource, ogg_apply to ggadmin identified by changeme;
grant create any table, alter any table, drop any table, insert any table, select any table to ggadmin;
grant unlimited tablespace to ggadmin;
@/u01/app/ogg/ogg23ai_ora/lib/sql/legacy/sequence.sql ggadmin
exec dbms_streams_auth.grant_admin_privilege('GGADMIN')
grant dba to user1 identified by changeme;
conn / as sysdba
alter system set enable_goldengate_replication=true scope=spfile;
alter database force logging;
alter database add supplemental log data;
shutdown immediate
startup mount
alter database archivelog;
alter database open;
exit
EOF
su - oracle -c "sqlplus / as sysdba @/tmp/1.sql"
Sample Output (click to expand):
[root@lin8 ~]# su - oracle -c "sqlplus / as sysdba @/tmp/1.sql"
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Fri Sep 26 08:57:58 2025
Version 23.5.0.24.07
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07
Session altered.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGADMIN
UPDATE_SEQUENCE STATUS:
Line/pos
----------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors
GETSEQFLUSH
Line/pos
----------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors
SEQTRACE
Line/pos
----------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors
MOVETARGETHWM
Line/pos
----------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors
REPLICATE_SEQUENCE STATUS:
Line/pos
----------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors
STATUS OF SEQUENCE SUPPORT
--------------------------------------------------------------
SUCCESSFUL installation of Oracle Sequence Replication support
PL/SQL procedure successfully completed.
Grant succeeded.
Connected.
System altered.
Database altered.
Database altered.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 2430137952 bytes
Fixed Size 5363296 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 8855552 bytes
Database mounted.
Database altered.
Database altered.
Disconnected from Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07
[root@lin8 ~]#
Now we will create tnsnames entries that Oracle GoldenGate uses to connect to the source and target database. We will also define these connections in Oracle GoldenGate as a useridalias (pdb1_19 on the source and pdb1_23 on the target).
# run as root on the source VM
su - ogg -c ". ogg_ora.env
cat >>/home/ogg/tns/tnsnames.ora <<EOF
pdb1_ora19 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lin7.fritz.box)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1.fritz.box)
)
)
EOF
echo 'connect https://lin7.fritz.box:9001 deployment depl1 as ggadmindepl1 password changeme !
alter credentialstore add user 'ggadmin@pdb1_ora19' alias pdb1_19 password changeme
info credentialstore
dblogin useridalias pdb1_19
add schematrandata user1
add checkpointtable ggadmin.checkpoint
add heartbeattable'|adminclient"
Sample Output (click to expand):
Oracle GoldenGate Administration Client for Oracle
Version 23.5.1.24.07 OGGCORE_23.5.0.0.0OGGRU_LINUX.X64_240728.2258_FBO
Copyright (C) 1995, 2024, Oracle and/or its affiliates. All rights reserved.
Linux, x64, 64bit (optimized) on Jul 29 2024 04:39:15
Operating system character set identified as UTF-8.
OGG (not connected) 1> connect https://lin7.fritz.box:9001 deployment depl1 as ggadmindepl1 password changeme !
OGG (https://lin7.fritz.box:9001 depl1) 2> alter credentialstore add user ggadmin@pdb1_ora19 alias pdb1_19 password changeme
2025-09-26T07:10:55Z INFO OGG-15114 Credential store altered.
OGG (https://lin7.fritz.box:9001 depl1) 3> info credentialstore
Default domain: OracleGoldenGate
Alias: pdb1_19
Userid: ggadmin@pdb1_ora19
OGG (https://lin7.fritz.box:9001 depl1) 4> dblogin useridalias pdb1_19
Successfully logged into database.
OGG (https://lin7.fritz.box:9001 depl1 as pdb1_19@ORCL) 5> add schematrandata user1
2025-09-26T07:10:56Z INFO OGG-01788 SCHEMATRANDATA has been added on schema "user1".
2025-09-26T07:10:56Z INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema "user1".
2025-09-26T07:10:56Z INFO OGG-10154 Schema level PREPARECSN set to mode NOWAIT on schema "user1"
OGG (https://lin7.fritz.box:9001 depl1 as pdb1_19@ORCL) 6> add checkpointtable ggadmin.checkpoint
OGG (https://lin7.fritz.box:9001 depl1 as pdb1_19@ORCL) 7> add heartbeattable
2025-09-26T07:10:57Z INFO OGG-14101 Successfully added heartbeat table.
[root@lin7 ~]#
# run as root on the target VM
su - ogg -c ". ogg_ora.env
cat >>/home/ogg/tns/tnsnames.ora <<EOF
pdb1_ora23 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lin8.fritz.box)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1.fritz.box)
)
)
EOF
echo 'connect https://lin8.fritz.box:9001 deployment depl2 as ggadmindepl2 password changeme !
alter credentialstore add user 'ggadmin@pdb1_ora23' alias pdb1_23 password changeme
info credentialstore
dblogin useridalias pdb1_23
add schematrandata user1
add checkpointtable ggadmin.checkpoint
add heartbeattable'|adminclient"
Sample Output (click to expand):
Oracle GoldenGate Administration Client for Oracle
Version 23.5.1.24.07 OGGCORE_23.5.0.0.0OGGRU_LINUX.X64_240728.2258_FBO
Copyright (C) 1995, 2024, Oracle and/or its affiliates. All rights reserved.
Linux, x64, 64bit (optimized) on Jul 29 2024 04:39:15
Operating system character set identified as UTF-8.
OGG (not connected) 1> connect https://lin8.fritz.box:9001 deployment depl2 as ggadmindepl2 password changeme !
OGG (https://lin8.fritz.box:9001 depl2) 2> alter credentialstore add user ggadmin@pdb1_ora23 alias pdb1_23 password changeme
2025-09-26T07:12:46Z INFO OGG-15114 Credential store altered.
OGG (https://lin8.fritz.box:9001 depl2) 3> info credentialstore
Default domain: OracleGoldenGate
Alias: pdb1_23
Userid: ggadmin@pdb1_ora23
OGG (https://lin8.fritz.box:9001 depl2) 4> dblogin useridalias pdb1_23
Successfully logged into database.
OGG (https://lin8.fritz.box:9001 depl2 as pdb1_23@ORCL) 5> add schematrandata user1
2025-09-26T07:12:47Z INFO OGG-01788 SCHEMATRANDATA has been added on schema "user1".
2025-09-26T07:12:47Z INFO OGG-30198 Partial JSON Update is disabled on schema "user1".
2025-09-26T07:12:47Z INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema "user1".
2025-09-26T07:12:47Z INFO OGG-10154 Schema level PREPARECSN set to mode NOWAIT on schema "user1"
OGG (https://lin8.fritz.box:9001 depl2 as pdb1_23@ORCL) 6> add checkpointtable ggadmin.checkpoint
OGG (https://lin8.fritz.box:9001 depl2 as pdb1_23@ORCL) 7> add heartbeattable
2025-09-26T07:12:49Z INFO OGG-14101 Successfully added heartbeat table.
[root@lin8 ~]#
Storing the CA certificates
Since we are using a secure deployment of Oracle GoldenGate we need to copy the public CA certificate into the local truststore. We can do that with the GUI (login to the ServiceManager => Certificate Management => click (+) on CA Certificates and add the certificate as shared) or with the Rest API:
# run as root on one of the VMs
(
cakey_node1=$(sshpass -p 'changeme' ssh -oStrictHostKeyChecking=no ogg@lin7 cat /home/ogg/keys/ca_cert.pem|tr -d '\n')
cakey_node2=$(sshpass -p 'changeme' ssh -oStrictHostKeyChecking=no ogg@lin8 cat /home/ogg/keys/ca_cert.pem|tr -d '\n')
cat >/tmp/j1.json <<EOF
{
"trustpointBundle": {
"trustpointPem": "$cakey_node1"
}
}
EOF
cat >/tmp/j2.json <<EOF
{
"trustpointBundle": {
"trustpointPem": "$cakey_node2"
}
}
EOF
# add CA certificate of lin8 to node1 (lin7)
curl -u ggma:changeme -k --progress-bar -L -X POST 'https://lin7.fritz.box:9000/services/v2/deployments/ServiceManager/certificates/truststore/CA_lin8' -H 'Content-Type: application/json' -H 'Accept: application/json' -d@/tmp/j2.json|jq
# add CA certificate of lin7 to node2 (lin8)
curl -u ggma:changeme -k --progress-bar -L -X POST 'https://lin8.fritz.box:9000/services/v2/deployments/ServiceManager/certificates/truststore/CA_lin7' -H 'Content-Type: application/json' -H 'Accept: application/json' -d@/tmp/j1.json|jq
)
Sample Output (click to expand):
[root@lin8 ~]# (
> cakey_node1=$(sshpass -p 'changeme' ssh -oStrictHostKeyChecking=no ogg@lin7 cat /home/ogg/keys/ca_cert.pem|tr -d '\n')
> cakey_node2=$(sshpass -p 'changeme' ssh -oStrictHostKeyChecking=no ogg@lin8 cat /home/ogg/keys/ca_cert.pem|tr -d '\n')
> cat >/tmp/j1.json <<EOF
> {
> "trustpointBundle": {
> "trustpointPem": "$cakey_node1"
> }
> }
> EOF
> cat >/tmp/j2.json <<EOF
> {
> "trustpointBundle": {
> "trustpointPem": "$cakey_node2"
> }
> }
> EOF
> # add CA certificate of lin8 to node1 (lin7)
> curl -u ggma:changeme -k --progress-bar -L -X POST 'https://lin7.fritz.box:9000/services/v2/deployments/ServiceManager/certificates/truststore/CA_lin8' -H 'Content-Type: application/json' -H 'Accept: application/json' -d@/tmp/j2.json|jq
> # add CA certificate of lin7 to node2 (lin8)
> curl -u ggma:changeme -k --progress-bar -L -X POST 'https://lin8.fritz.box:9000/services/v2/deployments/ServiceManager/certificates/truststore/CA_lin7' -H 'Content-Type: application/json' -H 'Accept: application/json' -d@/tmp/j1.json|jq
> )
###################################################################################################################################################################################################################################### 100.0%
{
"$schema": "api:standardResponse",
"links": [
{
"rel": "canonical",
"href": "https://lin7.fritz.box:9000/services/v2/deployments/ServiceManager/certificates/truststore/CA_lin8",
"mediaType": "application/json"
},
{
"rel": "self",
"href": "https://lin7.fritz.box:9000/services/v2/deployments/ServiceManager/certificates/truststore/CA_lin8",
"mediaType": "application/json"
}
],
"messages": []
}
###################################################################################################################################################################################################################################### 100.0%
{
"$schema": "api:standardResponse",
"links": [
{
"rel": "canonical",
"href": "https://lin8.fritz.box:9000/services/v2/deployments/ServiceManager/certificates/truststore/CA_lin7",
"mediaType": "application/json"
},
{
"rel": "self",
"href": "https://lin8.fritz.box:9000/services/v2/deployments/ServiceManager/certificates/truststore/CA_lin7",
"mediaType": "application/json"
}
],
"messages": []
}
[root@lin8 ~]#
Creating the structure of the replicated tables on the target database
To prepare for the initial load we need to create the empty table (user1.t) on the target pdb. First we create the DDL (Data Definition Language) of the table t on the source database.
# run as root on the source VM to create the DDL of the table user1.t
cat >/tmp/1.sql <<EOF
set long 10000 longc 10000 pages 0
alter session set container=pdb1;
select dbms_metadata.get_ddl('TABLE','T','USER1') from dual;
exit
EOF
su - oracle -c ". ora19.env; sqlplus / as sysdba @/tmp/1.sql"
Sample Output (click to expand):
[root@lin7 ~]# su - oracle -c ". ora19.env; sqlplus / as sysdba @/tmp/1.sql"
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 26 12:36:21 2025
Version 19.25.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.0.0
Session altered.
CREATE TABLE "USER1"."T"
( "OWNER" VARCHAR2(128) NOT NULL ENABLE,
"OBJECT_NAME" VARCHAR2(128) NOT NULL ENABLE,
"SUBOBJECT_NAME" VARCHAR2(128),
"OBJECT_ID" NUMBER NOT NULL ENABLE,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(23),
"CREATED" DATE NOT NULL ENABLE,
"LAST_DDL_TIME" DATE NOT NULL ENABLE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1),
"NAMESPACE" NUMBER NOT NULL ENABLE,
"EDITION_NAME" VARCHAR2(128),
"SHARING" VARCHAR2(18),
"EDITIONABLE" VARCHAR2(1),
"ORACLE_MAINTAINED" VARCHAR2(1),
"APPLICATION" VARCHAR2(1),
"DEFAULT_COLLATION" VARCHAR2(100),
"DUPLICATED" VARCHAR2(1),
"SHARDED" VARCHAR2(1),
"CREATED_APPID" NUMBER,
"CREATED_VSNID" NUMBER,
"MODIFIED_APPID" NUMBER,
"MODIFIED_VSNID" NUMBER
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.0.0
[root@lin7 ~]#
With this DDL we can create a empty table t on the target VM:
# run as root on the target VM
cat >/tmp/1.sql <<EOF
alter session set container=pdb1;
create table user1.t
(owner varchar2(128) not null enable,
object_name varchar2(128) not null enable,
subobject_name varchar2(128),
object_id number not null enable,
data_object_id number,
object_type varchar2(23),
created date not null enable,
last_ddl_time date not null enable,
timestamp varchar2(19),
status varchar2(7),
temporary varchar2(1),
generated varchar2(1),
secondary varchar2(1),
namespace number not null enable,
edition_name varchar2(128),
sharing varchar2(18),
editionable varchar2(1),
oracle_maintained varchar2(1),
application varchar2(1),
default_collation varchar2(100),
duplicated varchar2(1),
sharded varchar2(1),
created_appid number,
created_vsnid number,
modified_appid number,
modified_vsnid number
) tablespace users;
exit
EOF
su - oracle -c "sqlplus / as sysdba @/tmp/1.sql"
Sample Output (click to expand):
[root@lin8 ~]# su - oracle -c "sqlplus / as sysdba @/tmp/1.sql"
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Fri Sep 26 12:37:11 2025
Version 23.5.0.24.07
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07
Session altered.
Table created.
Disconnected from Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07
[root@lin8 ~]#
Setup the replication
We are now ready to setup and start the replication. First we will perform an initial load which copies the existing data (1m rows) from the source table into the currently empty target table t. After that we start the regular replicat that continously replicates change done in the source schema. After the initial load is done all related components are removed (extract,distpath and replicat).
Adding and starting the regular extract
Before we start with the initial load, we will setup and start the regular extract (ext1) on the source pdb. Not all extracted changes are needed. Only changes after the initial load are needed.
# run as root on the source VM
su - ogg -c ". ogg_ora.env
cat >/u01/app/ogg/depl1/etc/conf/ogg/EXT1.prm <<EOF
EXTRACT ext1
USERIDALIAS pdb1_19 DOMAIN OracleGoldenGate
EXTTRAIL tr
TABLE user1.*;
DDL INCLUDE ALL
EOF
echo 'connect https://lin7.fritz.box:9001 deployment depl1 as ggadmindepl1 password changeme !
add extract ext1 integrated tranlog begin now autostart yes delay 90
add exttrail tr extract ext1
dblogin useridalias pdb1_19
register extract ext1 database
start extract ext1'|adminclient"
Sample Output (click to expand):
[root@lin7 ~]# su - ogg -c ". ogg_ora.env
> cat >/u01/app/ogg/depl1/etc/conf/ogg/EXT1.prm <<EOF
> EXTRACT ext1
> USERIDALIAS pdb1_19 DOMAIN OracleGoldenGate
> EXTTRAIL tr
> TABLE user1.*;
> DDL INCLUDE ALL
> EOF
> echo 'connect https://lin7.fritz.box:9001 deployment depl1 as ggadmindepl1 password changeme !
> add extract ext1 integrated tranlog begin now autostart yes delay 90
> add exttrail tr extract ext1
> dblogin useridalias pdb1_19
> register extract ext1 database
> start extract ext1'|adminclient"
Oracle GoldenGate Administration Client for Oracle
Version 23.5.1.24.07 OGGCORE_23.5.0.0.0OGGRU_LINUX.X64_240728.2258_FBO
Copyright (C) 1995, 2024, Oracle and/or its affiliates. All rights reserved.
Linux, x64, 64bit (optimized) on Jul 29 2024 04:39:15
Operating system character set identified as UTF-8.
OGG (not connected) 1> connect https://lin7.fritz.box:9001 deployment depl1 as ggadmindepl1 password changeme !
OGG (https://lin7.fritz.box:9001 depl1) 2> add extract ext1 integrated tranlog begin now autostart yes delay 90
2025-09-26T11:18:03Z INFO OGG-08100 Integrated Extract added.
OGG (https://lin7.fritz.box:9001 depl1) 3> add exttrail tr extract ext1
2025-09-26T11:18:03Z INFO OGG-08100 EXTTRAIL added.
OGG (https://lin7.fritz.box:9001 depl1) 4> dblogin useridalias pdb1_19
Successfully logged into database.
OGG (https://lin7.fritz.box:9001 depl1 as pdb1_19@ORCL) 5> register extract ext1 database
2025-09-26T11:18:14Z INFO OGG-02003 Extract group EXT1 successfully registered with database at SCN 2922454.
OGG (https://lin7.fritz.box:9001 depl1 as pdb1_19@ORCL) 6> start extract ext1
2025-09-26T11:18:14Z INFO OGG-00975 Extract group EXT1 starting.
2025-09-26T11:18:14Z INFO OGG-15426 Extract group EXT1 started.
[root@lin7 ~]#
Perform the initial load for schema user1
The first step is to determine the instantiation SCN. This is the (source) SCN at which the target database should be in sync with the source database.
# run as root on the first VM
cat >/tmp/1.sql <<EOF
alter session set container=pdb1;
select min(start_scn) as inst_scn_open_transactions from gv\$transaction;
select min(scn)as inst_scn_no_open_transactions from (
select min(start_scn) as scn from gv\$transaction union all
select current_scn from gv\$database);
exit
EOF
su - oracle -c ". ora19.env; sqlplus / as sysdba @/tmp/1.sql"
Sample Output (click to expand):
[root@lin7 ~]# su - oracle -c ". ora19.env; sqlplus / as sysdba @/tmp/1.sql"
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 26 13:25:55 2025
Version 19.25.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.0.0
Session altered.
INST_SCN_OPEN_TRANSACTIONS
--------------------------
INST_SCN_NO_OPEN_TRANSACTIONS
-----------------------------
2946253
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.0.0
[root@lin7 ~]#
We take a note of the SCN (in my case: 2946253). Next we create the initial load replicat (repinit) on the target pdb and start it.
# run as root on the target VM
su - ogg -c ". ogg_ora.env
cat >/u01/app/ogg/depl2/etc/conf/ogg/REPINIT.prm <<EOF
REPLICAT repinit
USERIDALIAS pdb1_23
Map user1.*
Target user1.*;
EOF
echo 'connect https://lin8.fritz.box:9001 deployment depl2 as ggadmindepl2 password changeme !
dblogin useridalias pdb1_23
add replicat repinit exttrail lo checkpointtable ggadmin.checkpoint
start replicat repinit'|adminclient"
Sample Output (click to expand):
Oracle GoldenGate Administration Client for Oracle
Version 23.5.1.24.07 OGGCORE_23.5.0.0.0OGGRU_LINUX.X64_240728.2258_FBO
Copyright (C) 1995, 2024, Oracle and/or its affiliates. All rights reserved.
Linux, x64, 64bit (optimized) on Jul 29 2024 04:39:15
Operating system character set identified as UTF-8.
OGG (not connected) 1> connect https://lin8.fritz.box:9001 deployment depl2 as ggadmindepl2 password changeme !
OGG (https://lin8.fritz.box:9001 depl2) 2> dblogin useridalias pdb1_23
Successfully logged into database.
OGG (https://lin8.fritz.box:9001 depl2 as pdb1_23@ORCL) 3> add replicat repinit exttrail lo checkpointtable ggadmin.checkpoint
2025-09-26T11:35:17Z INFO OGG-08100 Replicat added.
OGG (https://lin8.fritz.box:9001 depl2 as pdb1_23@ORCL) 4> start replicat repinit
2025-09-26T11:35:17Z INFO OGG-00975 Replicat group REPINIT starting.
2025-09-26T11:35:17Z INFO OGG-15445 Replicat group REPINIT started.
Now we create and start the initial load extract on the source pdb. We need to adjust the instantiated SCN to the one noted earlier.
# run as root on the source VM
(
read -p 'Enter the instantiation SCN: ' inst_scn
su - ogg -c ". ogg_ora.env
cat >/u01/app/ogg/depl1/etc/conf/ogg/EXT1INIT.prm <<EOF
EXTRACT ext1init
USERIDALIAS pdb1_19 DOMAIN OracleGoldenGate
EXTFILE il Megabytes 50 Purge
TABLE user1.*, SQLPredicate 'As Of SCN $inst_scn';
EOF
echo 'connect https://lin7.fritz.box:9001 deployment depl1 as ggadmindepl1 password changeme !
add extract ext1init sourceistable
start extract ext1init'|adminclient"
)
Sample Output (click to expand):
Enter the instantiation SCN: 2946253
Oracle GoldenGate Administration Client for Oracle
Version 23.5.1.24.07 OGGCORE_23.5.0.0.0OGGRU_LINUX.X64_240728.2258_FBO
Copyright (C) 1995, 2024, Oracle and/or its affiliates. All rights reserved.
Linux, x64, 64bit (optimized) on Jul 29 2024 04:39:15
Operating system character set identified as UTF-8.
OGG (not connected) 1> connect https://lin7.fritz.box:9001 deployment depl1 as ggadmindepl1 password changeme !
OGG (https://lin7.fritz.box:9001 depl1) 2> add extract ext1init sourceistable
2025-09-26T11:38:24Z INFO OGG-08100 Extract added.
OGG (https://lin7.fritz.box:9001 depl1) 3> start extract ext1init
2025-09-26T11:38:24Z INFO OGG-00975 Extract group EXT1INIT starting.
2025-09-26T11:38:24Z INFO OGG-15426 Extract group EXT1INIT started.
We will be able to see the extract files generated in (ls -alhtr /u01/app/ogg/depl1/var/lib/data/).Now we only need to create the distpaths between the two machines. These will be responsible for copying the trail files from the source machine to the target machine (can be seen here: ls -alhtr ls /u01/app/ogg/depl2/var/lib/data/ ). We will create the paths for the initial load and for the regular replication in one step:
# run as root on the source VM
# dist path for the initial load
su - ogg -c ". ogg_ora.env
echo 'connect https://lin7.fritz.box:9001 deployment depl1 as ggadmindepl1 password changeme !
add distpath illo source trail://lin7:9002/services/v2/sources?trail=il target wss://lin8:9003/services/v2/targets?trail=lo
alter distpath illo target sizemb 75
start distpath illo'|adminclient"
# dist path for the regular replication
su - ogg -c ". ogg_ora.env
echo 'connect https://lin7.fritz.box:9001 deployment depl1 as ggadmindepl1 password changeme !
add distpath trts source trail://lin7:9002/services/v2/sources?trail=tr target wss://lin8:9003/services/v2/targets?trail=ts
alter distpath trts target sizemb 75
start distpath trts'|adminclient"
Sample Output (click to expand):
Oracle GoldenGate Administration Client for Oracle
Version 23.5.1.24.07 OGGCORE_23.5.0.0.0OGGRU_LINUX.X64_240728.2258_FBO
Copyright (C) 1995, 2024, Oracle and/or its affiliates. All rights reserved.
Linux, x64, 64bit (optimized) on Jul 29 2024 04:39:15
Operating system character set identified as UTF-8.
OGG (not connected) 1> connect https://lin7.fritz.box:9001 deployment depl1 as ggadmindepl1 password changeme !
OGG (https://lin7.fritz.box:9001 depl1) 2> add distpath illo source trail://lin7:9002/services/v2/sources?trail=il target wss://lin8:9003/services/v2/targets?trail=lo
The syntax for specifying authentication method using URI credentials is deprecated.
2025-09-26T11:43:19Z INFO OGG-08511 The path 'illo' has been added.
2025-09-26T11:43:19Z INFO OGG-30437 The authentication method was not specified. The authentication method was set to be the default client certificate.
OGG (https://lin7.fritz.box:9001 depl1) 3> alter distpath illo target sizemb 75
2025-09-26T11:43:19Z INFO OGG-08512 The path 'illo' has been updated.
OGG (https://lin7.fritz.box:9001 depl1) 4> start distpath illo
2025-09-26T11:43:21Z INFO OGG-08513 The path 'illo' has been started.
[root@lin7 ~]# # dist path for the regular replication
[root@lin7 ~]# su - ogg -c ". ogg_ora.env
> echo 'connect https://lin7.fritz.box:9001 deployment depl1 as ggadmindepl1 password changeme !
> add distpath trts source trail://lin7:9002/services/v2/sources?trail=tr target wss://lin8:9003/services/v2/targets?trail=ts
> alter distpath trts target sizemb 75
> start distpath trts'|adminclient"
Oracle GoldenGate Administration Client for Oracle
Version 23.5.1.24.07 OGGCORE_23.5.0.0.0OGGRU_LINUX.X64_240728.2258_FBO
Copyright (C) 1995, 2024, Oracle and/or its affiliates. All rights reserved.
Linux, x64, 64bit (optimized) on Jul 29 2024 04:39:15
Operating system character set identified as UTF-8.
OGG (not connected) 1> connect https://lin7.fritz.box:9001 deployment depl1 as ggadmindepl1 password changeme !
OGG (https://lin7.fritz.box:9001 depl1) 2> add distpath trts source trail://lin7:9002/services/v2/sources?trail=tr target wss://lin8:9003/services/v2/targets?trail=ts
The syntax for specifying authentication method using URI credentials is deprecated.
2025-09-26T11:43:22Z INFO OGG-08511 The path 'trts' has been added.
2025-09-26T11:43:22Z INFO OGG-30437 The authentication method was not specified. The authentication method was set to be the default client certificate.
OGG (https://lin7.fritz.box:9001 depl1) 3> alter distpath trts target sizemb 75
2025-09-26T11:43:22Z INFO OGG-08512 The path 'trts' has been updated.
OGG (https://lin7.fritz.box:9001 depl1) 4> start distpath trts
2025-09-26T11:43:24Z INFO OGG-08513 The path 'trts' has been started.
Now the extfile from the initial load will be copied to the target machine, and the replicat begins to insert the rows to the target table. We can monitor the process by looking at the number of rows of the target table:
# run as root on the target VM
su - oracle -c "echo 'alter session set container=pdb1;
select count(*) from user1.t;'|sqlplus -S / as sysdba"
Once the target table user1.t contains 1m rows the initial load is finished and we can continue to cleanup the initial load resources.
# run as root on any of the two VMs
su - ogg -c ". ogg_ora.env
echo 'connect https://lin7.fritz.box:9001 deployment depl1 as ggadmindepl1 password changeme !
delete extract ext1init
delete distpath illo
connect https://lin8.fritz.box:9001 deployment depl2 as ggadmindepl2 password changeme !
delete replicat repinit'|adminclient"
Sample Output (click to expand):
Oracle GoldenGate Administration Client for Oracle
Version 23.5.1.24.07 OGGCORE_23.5.0.0.0OGGRU_LINUX.X64_240728.2258_FBO
Copyright (C) 1995, 2024, Oracle and/or its affiliates. All rights reserved.
Linux, x64, 64bit (optimized) on Jul 29 2024 04:39:15
Operating system character set identified as UTF-8.
OGG (not connected) 1> connect https://lin7.fritz.box:9001 deployment depl1 as ggadmindepl1 password changeme !
OGG (https://lin7.fritz.box:9001 depl1) 2> delete extract ext1init
2025-09-26T12:57:35Z INFO OGG-08100 Extract group EXT1INIT deleted.
OGG (https://lin7.fritz.box:9001 depl1) 3> delete distpath illo
2025-09-26T12:57:37Z INFO OGG-08516 The path 'illo' has been deleted.
OGG (https://lin7.fritz.box:9001 depl1) 4> connect https://lin8.fritz.box:9001 deployment depl2 as ggadmindepl2 password changeme !
OGG (https://lin8.fritz.box:9001 depl2) 5> delete replicat repinit
2025-09-26T12:57:41Z INFO OGG-14052 No Heartbeat entries with [REPINIT], none deleted.
2025-09-26T12:57:41Z INFO OGG-08100 Replicat group REPINIT deleted.
Creating and starting the regular replication
After getting the two schemas synced we can finish the final replication setup. We already created the extract (ext1) and the distribution path. What is left is the creation and startup of the replicat.
# run as root on the second VM
(
read -p 'Enter the instantiation SCN: ' inst_scn
su - ogg -c ". ogg_ora.env
cat >/u01/app/ogg/depl2/etc/conf/ogg/REP.prm <<EOF
REPLICAT rep
USERIDALIAS pdb1_23
Map user1.*
Target user1.*;
DDL INCLUDE OBJNAME user1.*
EOF
echo 'connect https://lin8.fritz.box:9001 deployment depl2 as ggadmindepl2 password changeme !
dblogin useridalias pdb1_23
add replicat rep exttrail ts checkpointtable ggadmin.checkpoint autostart yes delay 45
start replicat rep atcsn $inst_scn'|adminclient"
)
Sample Output (click to expand):
Enter the instantiation SCN: 2896595
Oracle GoldenGate Administration Client for Oracle
Version 23.5.1.24.07 OGGCORE_23.5.0.0.0OGGRU_LINUX.X64_240728.2258_FBO
Copyright (C) 1995, 2024, Oracle and/or its affiliates. All rights reserved.
Linux, x64, 64bit (optimized) on Jul 29 2024 04:39:15
Operating system character set identified as UTF-8.
OGG (not connected) 1> connect https://lin8.fritz.box:9001 deployment depl2 as ggadmindepl2 password changeme !
OGG (https://lin8.fritz.box:9001 depl2) 2> dblogin useridalias pdb1_23
Successfully logged into database.
OGG (https://lin8.fritz.box:9001 depl2 as pdb1_23@ORCL) 3> add replicat rep exttrail ts checkpointtable ggadmin.checkpoint autostart yes delay 45
2025-09-26T13:07:00Z INFO OGG-08100 Replicat added.
OGG (https://lin8.fritz.box:9001 depl2 as pdb1_23@ORCL) 4> start replicat rep atcsn 2896595
2025-09-26T13:07:00Z INFO OGG-00975 Replicat group REP starting.
2025-09-26T13:07:00Z INFO OGG-15445 Replicat group REP started.
Verify the proper setup of the replication
To check if the replication and the DDL replication is set up correct, we can insert some rows into table user1.t and create another table user1.t2 and see if it gets created on the target pdb.
# run as root on the first VM
su - oracle -c ". ora19.env; echo 'alter session set container=pdb1;
-- double the rows in table t
insert into user1.t select * from user1.t;
commit;
-- create table t2 containing 2m rows
create table user1.t2 as select * from user1.t;'|sqlplus -S / as sysdba"
Sample Output (click to expand):
[root@lin7 ~]# su - oracle -c ". ora19.env; echo 'alter session set container=pdb1;
> -- double the rows in table t
> insert into user1.t select * from user1.t;
> commit;
> -- create table t2 containing 2m rows
> create table user1.t2 as select * from user1.t;'|sqlplus -S / as sysdba"
Session altered.
1000000 rows created.
Commit complete.
Table created.
[root@lin7 ~]#
On the target pdb we can verify that the replication performed 1m inserts into user1.t and that also the DDL for the table user1.t2 and the inserts have been replicated.
# run as root on the target VM
su - oracle -c "echo 'alter session set container=pdb1;
select count(*) as num_rows_t from user1.t;
select count(*) as num_rows_t2 from user1.t2;'|sqlplus -S / as sysdba"
Sample Output (click to expand):
[root@lin8 ~]# su - oracle -c "echo 'alter session set container=pdb1;
> select count(*) as num_rows_t from user1.t;
> select count(*) as num_rows_t2 from user1.t2;'|sqlplus -S / as sysdba"
Session altered.
NUM_ROWS_T
----------
2000000
NUM_ROWS_T2
-----------
2000000
[root@lin8 ~]#
Issues and Solutions
OGG-06606
At first when I added an extract to the VM with the 19c database I got the following error:
Add Extract fails with: OGG-06606 | A root container connection is required when requesting the list of known containers.This happens to be because of two bugs (33368352 and 35364793). Applying a recent DB RU patch solved the issue for me.
OGG-08518
At first I forgot to add the CA certificates to the truststores. The error I got was:
2025-09-22T10:29:54Z ERROR OGG-08518 Request on path ILLL failed, which is caused by 'Could not verify the server certificate of the remote server. Verify that the CA of the remote server certificate is added to the CA certificate store and verify the validity of the remote server certificate.'.Obviously the solution is to add the CA certificate of each machine to the opposite machines truststore as described here.
Leave a Reply