Setup Golden Gate replicate on oracle DB 19c (which already has TDE & Database Vault enabled) with source db version of 12.2

Setup Golden Gate replicate on oracle DB 19c (which already has TDE & Database Vault enabled) with source db version of 12.2

Extract from : 12.2.0.1
Replicate to : 19.3.0
Direction: Uni (from source to target)

Assumption: 

1.Extract is already running 

2.Instantiation is completed

3.A cold backup of the db as needed

4.droping the ggadmin user & GG_DATA tablespace from source (in case we used rman to restore the source db)

5.drop SUPPLEMENTAL log in target (since it is a unidirectional replication and application will point to it at sometime)

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
22:08:38 SQL> select name,open_mode,log_mode,force_logging,SUPPLEMENTAL_LOG_DATA_MIN,CURRENT_SCN from v$database;
NAME     ,OPEN_MODE           ,LOG_MODE    ,FORCE_LOGGING                          ,SUPPLEME,CURRENT_SCN
---------,--------------------,------------,---------------------------------------,--------,-----------
GGTGT04T ,READ WRITE          ,ARCHIVELOG  ,YES                                    ,NO      ,    1028106
Elapsed: 00:00:00.00

6.reset ENABLE_GOLDENGATE_REPLICATION /*+ wrong - we need this parameter - so no reset */
NAME                                ,TYPE       ,VALUE
------------------------------------,-----------,------------------------------
enable_goldengate_replication       ,boolean    ,FALSE

7.If you have DataGuard, leave force_logging asis

Steps:

1. Prepare the target db for replicat
2. Add credential for GG extract using cURL or admin client web UI (BUI)
3. Setup the GG replicat using admin client
4. Start the GG replicat

Reference url: oracle document

1. Prepare the target db for replicat:

From the doc:

ALTER SESSION SET CONTAINER=dbwest; -- ours is a standalone db
CREATE USER ggadmin IDENTIFIED BY PASSWORD CONTAINER=CURRENT; -- standalone
CREATE USER ggadmin IDENTIFIED BY PASSWORD
GRANT ALTER SYSTEM TO ggadmin CONTAINER=CURRENT;
GRANT CREATE SESSION TO ggadmin CONTAINER=CURRENT;
GRANT ALTER ANY TABLE TO ggadmin CONTAINER=CURRENT;
GRANT RESOURCE TO ggadmin CONTAINER=CURRENT;
GRANT DBA TO ggadmin CONTAINER=CURRENT;
GRANT DV_GOLDENGATE_ADMIN, DV_GOLDENGATE_REDO_ACCESS TO GGADMIN CONTAINER=CURRENT;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('ggadmin');

1.a) actual setup commands

CREATE TABLESPACE GG_DATA_TGT DATAFILE '/oradata/GGTGT04T/gg_data_tgt_01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M;

-- as dbv_owner user
CREATE USER ggadmin_tgt IDENTIFIED BY "ggadmin_tgt" DEFAULT TABLESPACE GG_DATA_TGT TEMPORARY TABLESPACE TEMP;
grant connect to ggadmin_tgt;
alter user ggadmin_tgt quota unlimited on GG_DATA_TGT;
GRANT DV_GOLDENGATE_ADMIN, DV_GOLDENGATE_REDO_ACCESS TO ggadmin_tgt;

-- other permission as sys
GRANT ALTER SYSTEM TO ggadmin_tgt;
GRANT CREATE SESSION TO ggadmin_tgt;
GRANT ALTER ANY TABLE TO ggadmin_tgt;
GRANT RESOURCE TO ggadmin_tgt;
GRANT DBA TO ggadmin_tgt;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('ggadmin_tgt');

Actual output:

SQL> select name,open_mode from v$database;
NAME      OPEN_MODE
--------- --------------------
GGTGT04T  MOUNTED

SQL> alter database open;
Database altered.

SQL> select name,open_mode from v$database;
NAME      OPEN_MODE
--------- --------------------
GGTGT04T  READ WRITE

SQL> CREATE TABLESPACE GG_DATA_TGT DATAFILE '/oradata/GGTGT04T/gg_data_tgt_01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M;
Tablespace created.

SQL> conn dbv_owner/dbv_owner1#
Connected.

SQL> CREATE USER ggadmin_tgt IDENTIFIED BY "ggadmin_tgt" DEFAULT TABLESPACE GG_DATA_TGT TEMPORARY TABLESPACE TEMP;
User created.

SQL> grant connect to ggadmin_tgt;
Grant succeeded.

SQL> alter user ggadmin_tgt quota unlimited on GG_DATA_TGT;
User altered.

SQL> GRANT DV_GOLDENGATE_ADMIN, DV_GOLDENGATE_REDO_ACCESS TO ggadmin_tgt;
Grant succeeded.


SQL> conn / as sysdba
Connected.

SQL> GRANT ALTER SYSTEM TO ggadmin_tgt;
Grant succeeded.

SQL> GRANT CREATE SESSION TO ggadmin_tgt;
Grant succeeded.

SQL> GRANT ALTER ANY TABLE TO ggadmin_tgt;
Grant succeeded.

SQL> GRANT RESOURCE TO ggadmin_tgt;
Grant succeeded.

SQL> GRANT DBA TO ggadmin_tgt;
Grant succeeded.

SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('ggadmin_tgt');
PL/SQL procedure successfully completed.
SQL>

1.b) DB Setup verify

set lines 300
set pages 3000
set colsep ,
col username for a15
col account_status for a15
set time on
set timing on
alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';
select name,open_mode,log_mode,force_logging,SUPPLEMENTAL_LOG_DATA_MIN,CURRENT_SCN from v$database;
select instance_name,status from v$instance;
show parameter ENABLE_GOLDENGATE_REPLICATION;
select username,account_status,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,created from dba_users where username='GGADMIN_TGT';
select tablespace_name,sum(bytes)/1024/1024,sum(maxbytes)/1024/1024 from dba_data_files where tablespace_name='GG_DATA_TGT' group by tablespace_name;

Actual output:

SQL> set lines 300
set pages 3000
set colsep ,
col username for a15
col account_status for a15
set time on
set timing on

alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';
Session altered.
Elapsed: 00:00:00.00

22:30:41 SQL>

22:30:41 SQL> select name,open_mode,log_mode,force_logging,SUPPLEMENTAL_LOG_DATA_MIN,CURRENT_SCN from v$database;
NAME     ,OPEN_MODE           ,LOG_MODE    ,FORCE_LOGGING                          ,SUPPLEME,CURRENT_SCN
---------,--------------------,------------,---------------------------------------,--------,-----------
GGTGT04T ,READ WRITE          ,ARCHIVELOG  ,YES                                    ,NO      ,    1032053
Elapsed: 00:00:00.00

22:30:46 SQL> select instance_name,status from v$instance;

INSTANCE_NAME   ,STATUS
----------------,------------
GGTGT04T        ,OPEN
Elapsed: 00:00:00.00

22:31:03 SQL> show parameter ENABLE_GOLDENGATE_REPLICATION;
NAME                                ,TYPE       ,VALUE
------------------------------------,-----------,------------------------------
enable_goldengate_replication       ,boolean    ,FALSE

22:31:09 SQL> select username,account_status,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,created from dba_users where username='GGADMIN_TGT';
USERNAME       ,ACCOUNT_STATUS ,DEFAULT_TABLESPACE            ,TEMPORARY_TABLESPACE          ,CREATED
---------------,---------------,------------------------------,------------------------------,--------------------
GGADMIN_TGT    ,OPEN           ,GG_DATA_TGT                   ,TEMP                          ,26/AUG/2023 22:29:18
Elapsed: 00:00:00.02

22:31:17 SQL> select tablespace_name,sum(bytes)/1024/1024,sum(maxbytes)/1024/1024 from dba_data_files where tablespace_name='GG_DATA_TGT' group by tablespace_name;
TABLESPACE_NAME               ,SUM(BYTES)/1024/1024,SUM(MAXBYTES)/1024/1024
------------------------------,--------------------,-----------------------
GG_DATA_TGT                   ,                 100,             32767.9844
Elapsed: 00:00:00.01
22:31:32 SQL>

1.c) create a guarnteed restore point

create restore point PRE_GGREPLICAT guarantee flashback database;

If at all we need to replicate the steps, we can resume from here.

2. Add credential for GG extract using cURL or admin client web UI (BUI)

Verify in adminclient:

INFO CREDENTIALSTORE

Actual output:

OGG (not connected) 1> connect http://127.0.0.1:9011 deployment oggdep01 user ggsca password ggsca

OGG (http://127.0.0.1:9011 oggdep01) 2> INFO CREDENTIALSTORE
Default domain: OracleGoldenGate
  Alias: ggsrc04t
  Userid: ggadmin@192.168.194.10:1521/ggsrc04t

  Alias: ggsrc01t
  Userid: GGADMIN@192.168.194.10:1521/ggsrc01t

  Alias: ggadmin_opdb1
  Userid: ggadmin@127.0.0.1:1521/OPDB1

  Alias: cggadmin
  Userid: c##ggadmin@127.0.0.1:1521/ORA19C

  Alias: ggadmin_DBCASLNT
  Userid: ggadmin@192.168.194.6:1521/DBCASLNT

  Alias: ggsrc03t
  Userid: ggadmin@192.168.194.10:1521/ggsrc03t

  Alias: ggtgt04t
  Userid: ggadmin_tgt@192.168.194.11:1521/ggtgt04t
OGG (http://127.0.0.1:9011 oggdep01) 3>

3. Setup the GG replicat using admin client

export OGG_HOME=/u01/app/oracle/product/21.3.0/ogg_home_1
export PATH=$PATH:$OGG_HOME/bin
adminclient

connect http://127.0.0.1:9011 deployment oggdep01 user ggsca password ggsca

DBLOGIN USERIDALIAS ggtgt04t

ADD HEARTBEATTABLE

ADD CHECKPOINTTABLE ggadmin_tgt.ggs_checkpoint

Actual output:

OGG (http://127.0.0.1:9011 oggdep01) 3> DBLOGIN USERIDALIAS ggtgt04t
Successfully logged into database.

OGG (http://127.0.0.1:9011 oggdep01 as ggtgt04t@GGTGT04T) 4> ADD HEARTBEATTABLE
2023-08-26T21:39:28Z  INFO    OGG-14101  Successfully added heartbeat table.

OGG (http://127.0.0.1:9011 oggdep01 as ggtgt04t@GGTGT04T) 5> ADD CHECKPOINTTABLE ggadmin_tgt.ggs_checkpoint
OGG (http://127.0.0.1:9011 oggdep01 as ggtgt04t@GGTGT04T) 6>

On the target db:

22:40:22 SQL> select owner,object_name,objecT_type,created from dba_objects where owner='GGADMIN_TGT' order by created;
OWNER          ,OBJECT_NAME         ,OBJECT_TYPE         ,CREATED
---------------,--------------------,--------------------,--------------------
GGADMIN_TGT    ,GG_HEARTBEAT_SEED   ,TABLE               ,26/AUG/2023 22:39:25
GGADMIN_TGT    ,GG_HEARTBEAT        ,TABLE               ,26/AUG/2023 22:39:25
GGADMIN_TGT    ,GG_HEARTBEAT_HISTORY,TABLE               ,26/AUG/2023 22:39:25
GGADMIN_TGT    ,GG_LAG              ,VIEW                ,26/AUG/2023 22:39:25
GGADMIN_TGT    ,GG_LAG_HISTORY      ,VIEW                ,26/AUG/2023 22:39:25
GGADMIN_TGT    ,GG_UPDATE_HB_TAB    ,PROCEDURE           ,26/AUG/2023 22:39:27
GGADMIN_TGT    ,GG_PURGE_HB_TAB     ,PROCEDURE           ,26/AUG/2023 22:39:27
GGADMIN_TGT    ,GG_UPDATE_HEARTBEATS,JOB                 ,26/AUG/2023 22:39:28
GGADMIN_TGT    ,GG_PURGE_HEARTBEATS ,JOB                 ,26/AUG/2023 22:39:28
GGADMIN_TGT    ,GGS_CHECKPOINT      ,TABLE               ,26/AUG/2023 22:39:36
GGADMIN_TGT    ,SYS_C006989         ,INDEX               ,26/AUG/2023 22:39:36
GGADMIN_TGT    ,GGS_CHECKPOINT_LOX  ,TABLE               ,26/AUG/2023 22:39:36
GGADMIN_TGT    ,SYS_C006995         ,INDEX               ,26/AUG/2023 22:39:36
13 rows selected.
Elapsed: 00:00:00.04
22:40:37 SQL>

3.b) Add replicat process:

ADD REPLICAT rpt2, PARALLEL, EXTTRAIL src/r3, checkpointtable ggadmin_tgt.ggs_checkpoint

Param file: /oggdata/oggdep01/etc/conf/ogg/RPT2.prm
REPLICAT rpt2
USERIDALIAS ggtgt04t DOMAIN OracleGoldenGate
--DDL EXCLUDE ALL
DDLERROR default discard
REPERROR (default,discard)
DDLOPTIONS REPORT
MAP encvault_test.*, target encvault_test.*,  FILTER ( @GETENV('TRANSACTION', 'CSN') > 459732);

info replicat rpt2

Actual output:

OGG (http://127.0.0.1:9011 oggdep01 as ggtgt04t@GGTGT04T) 6> ADD REPLICAT rpt2, PARALLEL, EXTTRAIL src/r3, checkpointtable ggadmin_tgt.ggs_checkpoint

2023-08-26T21:41:39Z  INFO    OGG-08100  Parallel Replicat added.

OGG (http://127.0.0.1:9011 oggdep01 as ggtgt04t@GGTGT04T) 7> info replicat rpt2
Replicat   RPT2      Initialized  2023-08-26 22:41   Status STOPPED
Parallel
Checkpoint Lag       00:00:00 (updated 00:00:07 ago)
Log Read Checkpoint  File src/r3000000000
                     First Record  RBA 0
Encryption Profile   LocalWallet

OGG (http://127.0.0.1:9011 oggdep01 as ggtgt04t@GGTGT04T) 8> info replicat rpt2 detail
Replicat   RPT2      Initialized  2023-08-26 22:41   Status STOPPED
Parallel
Checkpoint Lag       00:00:00 (updated 00:00:12 ago)
Log Read Checkpoint  File src/r3000000000
                     First Record  RBA 0
Encryption Profile   LocalWallet
Current Log BSN value: (no data)

Low Watermark CSN value: (no data)

High Watermark CSN value: (no data)


Current directory    /
Report file          /oggdata/oggdep01/var/lib/report/RPT2.rpt (does not yet exist)
Parameter file       /oggdata/oggdep01/etc/conf/ogg/rpt2.prm (does not yet exist)
Checkpoint file      /oggdata/oggdep01/var/lib/checkpt/RPT2.cpr
Checkpoint table     ggadmin_tgt.ggs_checkpoint
Process file         /oggdata/oggdep01/var/run/RPT2.pcr
Error log            /oggdata/oggdep01/var/log/ggserr.log
OGG (http://127.0.0.1:9011 oggdep01 as ggtgt04t@GGTGT04T) 9>

[oracle@vcentos79-oracle-sa1 ~]$ vi /oggdata/oggdep01/etc/conf/ogg/rpt2.prm

[oracle@vcentos79-oracle-sa1 ~]$ cat /oggdata/oggdep01/etc/conf/ogg/rpt2.prm
REPLICAT rpt2
USERIDALIAS ggtgt04t DOMAIN OracleGoldenGate
--DDL EXCLUDE ALL
DDLERROR default discard
REPERROR (default,discard)
DDLOPTIONS REPORT
MAP encvault_test.*, target encvault_test.*,  FILTER ( @GETENV('TRANSACTION', 'CSN') > 459732);
[oracle@vcentos79-oracle-sa1 ~]$

4. Start the GG replicat

start replicat rpt2 < we have mentioned the csn number to start the replication in param file.

info replicat rpt2

status replicat rpt2

Actual output:

OGG (http://127.0.0.1:9011 oggdep01 as ggtgt04t@GGTGT04T) 13> info replicat rpt2 detail

Replicat   RPT2      Last Started 2023-08-27 14:25   Status RUNNING

Parallel

Checkpoint Lag       00:14:21 (updated 00:00:08 ago)

Process ID           22911

Log Read Checkpoint  File src/r3000000002

                     2023-08-27 14:11:28.912725  RBA 14566

Encryption Profile   LocalWallet

Current Log BSN value: 526850

Low Watermark CSN value: 526967

High Watermark CSN value: 526967

Current directory    /

Report file          /oggdata/oggdep01/var/lib/report/RPT2.rpt

Parameter file       /oggdata/oggdep01/etc/conf/ogg/rpt2.prm

Checkpoint file      /oggdata/oggdep01/var/lib/checkpt/RPT2.cpr

Checkpoint table     ggadmin_tgt.ggs_checkpoint

Process file         /oggdata/oggdep01/var/run/RPT2.pcr

Error log            /oggdata/oggdep01/var/log/ggserr.log

OGG (http://127.0.0.1:9011 oggdep01 as ggtgt04t@GGTGT04T) 14> 

note:

If you face the below error, it could be oracle bug 33701569. In this case you need to remove the csn from the param file and then alter replicat to begin from correct extseq and extrba

ERROR   OGG-12111  JSON element '/' does not match any schemas

Ex.:

alter replicat rpt2, extseqno 1, extrba 36083

GG replication validation:

In source:

create table ggtest1

as

select level eid,mod(level,6) pincode,rpad('X',mod(level,5)) branchcode,lpad('A',mod(level,5))||rpad('B',mod(level,5)) ename

from dual

connect by level < 1000;

alter table ggtest1 add constraint primary key (eid);

insert into ggtest1

select (level+1000) eid,mod(level,6) pincode,rpad('X',mod(level,5)) branchcode,lpad('A',mod(level,5))||rpad('B',mod(level,5)) ename

from dual

connect by level <= 1000;

Actual output:

SQL> create table ggtest1

as

select level eid,mod(level,6) pincode,rpad('X',mod(level,5)) branchcode,lpad('A',mod(level,5))||rpad('B',mod(level,5)) ename

from dual

connect by level < 1000;  2    3    4    5

Table created.

SQL> alter table ggtest1 add constraint eid_pk primary key (eid);

Table altered.

SQL> select count(1) from ggtest1;

  COUNT(1)

----------

       999

Check in target after replication process kicked off:

SQL> select count(1) from encvault_test.ggtest1;

  COUNT(1)

----------

       999

After loading some data in source:

SQL> insert into ggtest1

select (level+1000) eid,mod(level,6) pincode,rpad('X',mod(level,5)) branchcode,lpad('A',mod(level,5))||rpad('B',mod(level,5)) ename

from dual

connect by level <= 1000;  2    3    4

1000 rows created.

SQL> commit;

Commit complete.

SQL> select count(1) from encvault_test.ggtest1;

  COUNT(1)

----------

      1999

SQL>

Check in target:

SQL> select count(1) from encvault_test.ggtest1;

  COUNT(1)

----------

       999

SQL> /

  COUNT(1)

----------

      1999

SQL>

This closes the blog.

Please refer to the YouTube video corresponding to the blog: