Objective: Let us setup oracle golden gate 21.3 replication using adminclient.
Overall steps:
Create a tablespace
Create a schema
Creata a table
Setup Extract & Start it from adminclient
Expdp the table from source to target using scn method
Make Few changes
Setup Replicat & Start the replicat
Make changes to see if the data replication works
Create a schema
Creata a table
Setup Extract & Start it from adminclient
Expdp the table from source to target using scn method
Make Few changes
Setup Replicat & Start the replicat
Make changes to see if the data replication works
Schema setup:
create tablespace OPDB1_GGTEST_TBSPC datafile size 1m autoextend on next 1m;
create user OPDB1_GGTEST_USER identified by opdb1_ggtest_user default tablespace OPDB1_GGTEST_TBSPC;
grant connect,resource,create table to OPDB1_GGTEST_USER;
alter user OPDB1_GGTEST_USER quota unlimited on OPDB1_GGTEST_TBSPC;
create table OPDB1_GGTEST_USER.OPDB1_GGTEST_TBL tablespace OPDB1_GGTEST_TBSPC 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;
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;
GG Extract notes:
Local Extracts: Running on the same machine
Integerated Extracts: Extract data from the remote setup
Extract Process: Extracts DML and DDL to a trail file. The TX are stored as they occur in a sequential manner to maintain speed and integerity.
Types of Extract: Source tables (used for initial load) & Database Recovery Logs or TX logs (Change sync)
By default EXTRACT from the time REGISTER EXTRACT command is run
No need to create checkpoint table in case parallel replicat needs to be setup, since parallel replicat command adds checkpoint table implictly
Checkpoint table is used for resuming the recovery or replication in target quickly, since it keeps track of the replication file position
GG Replicat notes:
Use case of replicat:
1. Initial Load (which is used for 1 time static data load from src to tgt)
2. Change Sync (which is the regular one that syncs data between src and tgt)
Types of replicats:
1. classic (less overhead, parallelism is complex and needs parameterization)
2. Coordinated (coordinator process for multiple sql operations in parallel. classic with automatic dependency handle, which is provided by coorinator)
3. Integrated (read trail, reconstructs change, apply through an inbound)
4. Parallel ( parallel data replication - integerated & nonintegerated -> integerated is there for oracle alone)
5. Initial Load
Recommended method between oracle dbs - Parallel Integerated
Parallel Replicat:
Mapper - TX mapper (Src->Tgt table) from Trail File
Master - Collater and Scheduler (coallates the mapped TX and scheduler dependency calculation -> batching)
Applier - Applies the TX in batch to the target
Barrier and DDL TX are handled automatically in parallel replicat.
The TX incovling unsupported column type are applied through direct OCI as like non-integerated approach.
Prallel Integerated requires supplemental logging and db version 12.2.0.1+. Also checkpoint table is mandaory.
Coordinated:
Trail is read concurrently by multiple threads in coordinated mode.
When encountering Barrier TX, the txs are rolledback as needed to TX starting point in all threads and then once barrier TX is applied, the other TXs are applied by threds.
Steps to kick off extract:
1) Register the extract:
A) Connect to deployment
CONNECT https://remotehost:srvmgrport DEPLOYMENT deployment_name AS deployment_user PASSWORD deployment_password
Actual:
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
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>
B) Connect to db:
OGG (https://remotehost:16000oracle_source)> DBLOGIN USERIDALIAS alias
Actual:
dblogin USERIDALIAS cggadmin
Output:
OGG (http://127.0.0.1:9011 oggdep01) 3> dblogin USERIDALIAS cggadmin
Successfully logged into database CDB$ROOT.
OGG (http://127.0.0.1:9011 oggdep01 as cggadmin@ORA19C/CDB$ROOT) 4>
C) Register the extract:
REGISTER EXTRACT sales DATABASE
REGISTER EXTRACT sales DATABASE ADD CONTAINER (customers)
Actual:
REGISTER EXTRACT EXTS DATABASE >> We already have EXTS extract group
REGISTER EXTRACT EXTS DATABASE ADD CONTAINER (OPDB1) >> OPDB1 is already part of the EXTRACT group
Output:
OGG (http://127.0.0.1:9011 oggdep01 as cggadmin@ORA19C/CDB$ROOT) 18> REGISTER EXTRACT EXTS DATABASE ADD CONTAINER (OPDB1)
2023-06-29T22:33:30Z ERROR OGG-08232 Error: Database container OPDB1 has already been registered.
OGG (http://127.0.0.1:9011 oggdep01 as cggadmin@ORA19C/CDB$ROOT) 19>
2) ADD TRANDATA
Before adding TRANDATA, pls setup min supplemental logging, force logging, archivelog in the source db.
ADD SCHEMATRANDATA hr
Actual:
ADD SCHEMATRANDATA OPDB1.OPDB1_GGTEST_USER
Output:
OGG (http://127.0.0.1:9011 oggdep01 as cggadmin@ORA19C/CDB$ROOT) 19> ADD SCHEMATRANDATA OPDB1.OPDB1_GGTEST_USER
2023-06-29T22:45:49Z INFO OGG-01788 SCHEMATRANDATA has been added on schema "OPDB1_GGTEST_USER".
2023-06-29T22:45:49Z INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema "OPDB1_GGTEST_USER".
2023-06-29T22:45:49Z INFO OGG-10154 Schema level PREPARECSN set to mode NOWAIT on schema "OPDB1_GGTEST_USER"
OGG (http://127.0.0.1:9011 oggdep01 as cggadmin@ORA19C/CDB$ROOT) 20>
3) ADD HEARTBEAT TABLE
DBLOGIN to SOURCE AND TARGET - run the below command:
ADD HEARTBEATTABLE
Actual:
INFO CREDENTIALSTORE
dblogin useridalias ggadmin_opdb1
ADD HEARTBEATTABLE
Output:
OGG (http://127.0.0.1:9011 oggdep01 as cggadmin@ORA19C/CDB$ROOT) 24> INFO CREDENTIALSTORE
Default domain: OracleGoldenGate
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
OGG (http://127.0.0.1:9011 oggdep01 as cggadmin@ORA19C/CDB$ROOT) 25>
OGG (http://127.0.0.1:9011 oggdep01 as cggadmin@ORA19C/CDB$ROOT) 25> dblogin useridalias ggadmin_opdb1
Successfully logged into database OPDB1.
OGG (http://127.0.0.1:9011 oggdep01 as ggadmin_opdb1@ORA19C/OPDB1) 26> ADD HEARTBEATTABLE
2023-06-29T22:54:15Z ERROR OGG-14041 "ggadmin"."GG_HEARTBEAT_SEED" exists already.
OGG (http://127.0.0.1:9011 oggdep01 as ggadmin_opdb1@ORA19C/OPDB1) 27>
>> Since I already have a replication setup, the heartbeat table already exists in source.
Repeat for target:
OGG (http://127.0.0.1:9011 oggdep01 as ggadmin_opdb1@ORA19C/OPDB1) 27> dblogin useridalias ggadmin_DBCASLNT
Successfully logged into database.
OGG (http://127.0.0.1:9011 oggdep01 as ggadmin_DBCASLNT@DBCASLNT/OPDB1) 28> ADD HEARTBEATTABLE
2023-06-29T22:59:42Z ERROR OGG-14041 "ggadmin"."GG_HEARTBEAT_SEED" exists already.
OGG (http://127.0.0.1:9011 oggdep01 as ggadmin_DBCASLNT@DBCASLNT/OPDB1) 29>
>>> Same case in target.
4) ADD CHECKPOINT Table in target:
ADD CHECKPOINTTABLE ggadmin.ggs_checkpoint
Actual:
dblogin useridalias ggadmin_DBCASLNT
ADD CHECKPOINTTABLE GGADMIN.GG_CHECKPOINT
Output:
OGG (http://127.0.0.1:9011 oggdep01 as ggadmin_DBCASLNT@DBCASLNT/OPDB1) 30> ADD CHECKPOINTTABLE GGADMIN.GG_CHECKPOINT
2023-06-29T23:06:46Z ERROR OGG-00664 OCI Error ORA (status = 955-ORA-00955: name is already used by an existing object
)
OGG (http://127.0.0.1:9011 oggdep01 as ggadmin_DBCASLNT@DBCASLNT/OPDB1) 31>
5) Add Extracts (Change data capture, we will use pump to perform initial data load):
Command:
edit params <paramfile>
Actual:
Since we already have a replication setup, let us pull the existing params file and then edit it to add the new schema to be replicated.
Output:
OGG (http://127.0.0.1:9011 oggdep01 as ggadmin_opdb1@ORA19C/OPDB1) 36> info extract exts detail
Extract EXTS Last Started 2023-06-25 17:06 Status STOPPED
Description src
Checkpoint Lag 00:00:00 (updated 103:04:02 ago)
Log Read Checkpoint Oracle Integrated Redo Logs
2023-06-25 17:13:21
SCN 0.3151143 (3151143)
...
Report file /oggdata/oggdep01/var/lib/report/EXTS.rpt
Parameter file /oggdata/oggdep01/etc/conf/ogg/EXTS.prm
Checkpoint file /oggdata/oggdep01/var/lib/checkpt/EXTS.cpe
Process file /oggdata/oggdep01/var/run/EXTS.pce
Error log /oggdata/oggdep01/var/log/ggserr.log
OGG (http://127.0.0.1:9011 oggdep01 as ggadmin_opdb1@ORA19C/OPDB1) 37>
OGG (http://127.0.0.1:9011 oggdep01 as ggadmin_opdb1@ORA19C/OPDB1) 38> view params EXTS.prm
USERIDALIAS cggadmin DOMAIN OracleGoldenGate
EXTTRAIL src/es
SOURCECATALOG OPDB1
DDL INCLUDE MAPPED
TABLE OPDB1_PUBLIC_USER.*;
OGG (http://127.0.0.1:9011 oggdep01 as ggadmin_opdb1@ORA19C/OPDB1) 39>
OGG (http://127.0.0.1:9011 oggdep01 as ggadmin_opdb1@ORA19C/OPDB1) 40> edit params EXTS.prm
EXTRACT exts << EXTRACT group name
USERIDALIAS cggadmin DOMAIN OracleGoldenGate << USERID credentials & DSN
EXTTRAIL src/es << TRAILFILE PREFIX
SOURCECATALOG OPDB1 << Source DB
DDL INCLUDE MAPPED
TABLE OPDB1_PUBLIC_USER.*;
TABLE OPDB1_GGTEST_USER.*; <<< new line added
OGG (http://127.0.0.1:9011 oggdep01 as ggadmin_opdb1@ORA19C/OPDB1) 40> edit params EXTS.prm
2023-06-29T23:24:13Z INFO OGG-10183 Parameter file EXTS.prm passed validity check.
OGG (http://127.0.0.1:9011 oggdep01 as ggadmin_opdb1@ORA19C/OPDB1) 41>
6) Add trailfile
ADD EXTTRAIL /ggs/dirdat/lt, EXTRACT exte
Actual:
ADD EXTTRAIL src/es, EXTRACT exts
ADD RMTTRAIL tgt/es, EXTRACT exts
In our case it already exists
OGG (http://127.0.0.1:9011 oggdep01 as ggadmin_opdb1@ORA19C/OPDB1) 44> info exttrail *
Local Trail: src/es
Seqno Length: 9
Flip Seqno Length: no
Extract: EXTS
Seqno: 3
RBA: 1388
File Size: 500M
OGG (http://127.0.0.1:9011 oggdep01 as ggadmin_opdb1@ORA19C/OPDB1) 45>
<<< I am skipping remote trail file, since the replication works fine already without rmttrail (Remote trail).
7) Start the extract
Extract should have already been added. In our case it already exists.
Command:
dblogin USERIDALIAS cggadmin << connect to cdb
info extract *
start extract EXTS
info extract *
STATus extract exts
STATs extract exts
Actual output:
OGG (http://127.0.0.1:9011 oggdep01) 2> dblogin USERIDALIAS cggadmin
Successfully logged into database CDB$ROOT.
OGG (http://127.0.0.1:9011 oggdep01 as cggadmin@ORA19C/CDB$ROOT) 3> info extract *
Extract EXTS Last Started 2023-06-25 17:06 Status STOPPED
Description src
Checkpoint Lag 00:00:00 (updated 141:14:37 ago)
Log Read Checkpoint Oracle Integrated Redo Logs
2023-06-25 17:13:21
SCN 0.3151143 (3151143)
Encryption Profile LocalWallet
OGG (http://127.0.0.1:9011 oggdep01 as cggadmin@ORA19C/CDB$ROOT) 4> start extract EXTS
2023-07-01T13:28:10Z INFO OGG-00975 Extract group EXTS starting.
2023-07-01T13:28:10Z INFO OGG-15426 Extract group EXTS started.
OGG (http://127.0.0.1:9011 oggdep01 as cggadmin@ORA19C/CDB$ROOT) 5> info extract *
Extract EXTS Last Started 2023-06-25 17:06 Status RUNNING
Description src
Checkpoint Lag 00:00:00 (updated 141:14:48 ago)
Process ID 32077
Log Read Checkpoint Oracle Integrated Redo Logs
2023-06-25 17:13:21
SCN 0.3151143 (3151143)
Encryption Profile LocalWallet
OGG (http://127.0.0.1:9011 oggdep01 as cggadmin@ORA19C/CDB$ROOT) 6>
OGG (http://127.0.0.1:9011 oggdep01 as cggadmin@ORA19C/CDB$ROOT) 7> STATUS EXTRACT *
EXTRACT EXTS: RUNNING
OGG (http://127.0.0.1:9011 oggdep01 as cggadmin@ORA19C/CDB$ROOT) 8>
OGG (http://127.0.0.1:9011 oggdep01 as cggadmin@ORA19C/CDB$ROOT) 9> STATs extract exts
Sending STATS request to Extract group EXTS ...
No active extraction maps.
DDL replication statistics (for all trails):
*** Total statistics since extract started ***
Operations 10.00
Mapped operations 10.00
Unmapped operations 0.00
Other operations 0.00
Excluded operations 0.00
OGG (http://127.0.0.1:9011 oggdep01 as cggadmin@ORA19C/CDB$ROOT) 10>
8) Get the Instantiation scn from source, copy the schema to target.
A) Get the Instantiation scn from source:
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
SELECT CURRENT_SCN, 'CURRENT', CURRENT_DATE,
NULL, NULL, NULL, 'SYS', NULL, NULL, NULL
from v$database
ORDER BY 1;
Actual output:
SQL> set lines 300
SQL> set pages 2000
SQL> col username for a10
SQL> col osuser for a10
SQL> 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
SELECT CURRENT_SCN, 'CURRENT', CURRENT_DATE,
NULL, NULL, NULL, 'SYS', NULL, NULL, NULL
from v$database
ORDER BY 1; 2 3 4 5 6 7 8 9 10
START_SCN TSTATUS START_DAT SID SERIAL# INST_ID USERNAME OSUSER SSTATUS LOGON_TIM
---------- ---------------- --------- ---------- ---------- ---------- ---------- ---------- -------- ---------
3540599 CURRENT 01-JUL-23 SYS
SQL>
when no records returned above - use below:
SELECT MIN(SCN) as INSTANTIATION_SCN
FROM (SELECT MIN(START_SCN) as SCN
FROM gv$transaction
UNION ALL
SELECT CURRENT_SCN
FROM gv$database);
B) Copy schema to target:
Src: use thr scn we determined in last step in the expdp param (flashback_scn)
expdp directory=OPDB1_DIR schemas=OPDB1_GGTEST_USER dumpfile=OPDB1_GGTEST_USER_GGinstantiate.dmp logfile=OPDB1_GGTEST_USER_GGinstantiate.log FLASHBACK_SCN=3540599
Actual output:
[oracle@vcentos79-oracle-sa1 ~]$ expdp directory=OPDB1_DIR schemas=OPDB1_GGTEST_USER dumpfile=OPDB1_GGTEST_USER_GGinstantiate.dmp logfile=OPDB1_GGTEST_USER_GGinstantiate.log FLASHBACK_SCN=3540599
Export: Release 19.0.0.0.0 - Production on Sat Jul 1 14:40:11 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Username: sys@OPDB1 as sysdba
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_SCHEMA_01": sys/********@OPDB1 AS SYSDBA directory=OPDB1_DIR schemas=OPDB1_GGTEST_USER dumpfile=OPDB1_GGTEST_USER_GGinstantiate.dmp logfile=OPDB1_GGTEST_USER_GGinstantiate.log FLASHBACK_SCN=3540599
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "OPDB1_GGTEST_USER"."OPDB1_GGTEST_TBL" 24.23 KB 999 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/oraarch/OPDB1_GGTEST_USER_GGinstantiate.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Jul 1 14:41:18 2023 elapsed 0 00:00:46
[oracle@vcentos79-oracle-sa1 ~]$
Tgt:
create tablespace OPDB1_GGTEST_TBSPC datafile size 1m autoextend on next 1m;
impdp directory=DBCASLNT_DIR schemas=OPDB1_GGTEST_USER dumpfile=OPDB1_GGTEST_USER_GGinstantiate.dmp logfile=OPDB1_GGTEST_USER_GGinstantiate.log
[no need to mention flashback_scn, since this is supported only on network_link import. Also we took expdp with the scn]
Actual output:
SQL> create tablespace OPDB1_GGTEST_TBSPC datafile size 1m autoextend on next 1m;
Tablespace created.
SQL>
[oracle@vcentos79-oracle-sby1 ~]$ impdp directory=DBCASLNT_DIR schemas=OPDB1_GGTEST_USER dumpfile=OPDB1_GGTEST_USER_GGinstantiate.dmp logfile=OPDB1_GGTEST_USER_GGinstantiate.log
Import: Release 19.0.0.0.0 - Production on Sat Jul 1 14:50:59 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01": /******** AS SYSDBA directory=DBCASLNT_DIR schemas=OPDB1_GGTEST_USER dumpfile=OPDB1_GGTEST_USER_GGinstantiate.dmp logfile=OPDB1_GGTEST_USER_GGinstantiate.log
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "OPDB1_GGTEST_USER"."OPDB1_GGTEST_TBL" 24.23 KB 999 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at Sat Jul 1 14:51:30 2023 elapsed 0 00:00:26
[oracle@vcentos79-oracle-sby1 ~]$
10) Add some data to the source schema & table:
insert into OPDB1_GGTEST_TBL
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 < 100;
SQL> desc OPDB1_GGTEST_TBL
Name Null? Type
----------------------------------------- -------- ----------------------------
EID NUMBER
PINCODE NUMBER
BRANCHCODE VARCHAR2(4000)
ENAME VARCHAR2(4000)
SQL> insert into OPDB1_GGTEST_TBL
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 < 100; 2 3 4
99 rows created.
SQL> commit;
Commit complete.
SQL>
11) Let us add replication param now
Command:
dblogin useridalias ggadmin_DBCASLNT
edit params name [name is the name of the replicat group, make necessary entries]
view params name << to verify the params
Actual: I already have a replicat group, but need to update it with new schema, so I have to use a way to let existing replicat continue but add new one with specific SCN
Map SOURCE.*, target TARGET.*, FILTER ( @GETENV('TRANSACTION', 'CSN') > <SCNNO>);
Output:
Map OPDB1_GGTEST_USER.*, target OPDB1_GGTEST_USER.*, FILTER ( @GETENV('TRANSACTION', 'CSN') > 3540599);
OGG (http://127.0.0.1:9011 oggdep01 as ggadmin_DBCASLNT@DBCASLNT/CDB$ROOT) 24> edit params rept
2023-07-01T15:03:36Z INFO OGG-10183 Parameter file REPT.prm passed validity check. << param validation passed
OGG (http://127.0.0.1:9011 oggdep01 as ggadmin_DBCASLNT@DBCASLNT/CDB$ROOT) 25> view params rept
REPLICAT rept
USERIDALIAS ggadmin_DBCASLNT DOMAIN OracleGoldenGate
--DDL EXCLUDE ALL
DDLERROR default discard
REPERROR (default,discard)
DDLOPTIONS REPORT
SOURCECATALOG OPDB1
MAP OPDB1_PUBLIC_USER.*, TARGET OPDB1_PUBLIC_USER.*;
Map OPDB1_GGTEST_USER.*, target OPDB1_GGTEST_USER.*, FILTER ( @GETENV('TRANSACTION', 'CSN') > 3540599);
OGG (http://127.0.0.1:9011 oggdep01 as ggadmin_DBCASLNT@DBCASLNT/CDB$ROOT) 26>
OGG (http://127.0.0.1:9011 oggdep01 as ggadmin_DBCASLNT@DBCASLNT/CDB$ROOT) 15>
12) Start Replicat
command:
start replicat <replicat group name>
Actual:
start replicat rept
Actual output:
OGG (http://127.0.0.1:9011 oggdep01 as ggadmin_DBCASLNT@DBCASLNT/CDB$ROOT) 27> start replicat rept
2023-07-01T15:04:31Z INFO OGG-00975 Replicat group REPT starting.
2023-07-01T15:04:31Z INFO OGG-15445 Replicat group REPT started.
OGG (http://127.0.0.1:9011 oggdep01 as ggadmin_DBCASLNT@DBCASLNT/CDB$ROOT) 28> status replicat rept
REPLICAT REPT: STOPPED
OGG (http://127.0.0.1:9011 oggdep01 as ggadmin_DBCASLNT@DBCASLNT/CDB$ROOT) 29> status replicat rept
REPLICAT REPT: RUNNING
OGG (http://127.0.0.1:9011 oggdep01 as ggadmin_DBCASLNT@DBCASLNT/CDB$ROOT) 30>
..
OGG (http://127.0.0.1:9011 oggdep01 as ggadmin_DBCASLNT@DBCASLNT/CDB$ROOT) 30> info replicat rept
Replicat REPT Last Started 2023-07-01 16:04 Status RUNNING
Description tgt
Parallel
Checkpoint Lag 142:59:25 (updated 00:00:09 ago)
Process ID 5393
Log Read Checkpoint File src/es000000003
2023-06-25 17:06:44.520548 RBA 1388
Settings Profile Default
Encryption Profile LocalWallet
OGG (http://127.0.0.1:9011 oggdep01 as ggadmin_DBCASLNT@DBCASLNT/CDB$ROOT) 31>
13) Let us check the record count in table.
remember we inserted 999 records through pump
SQL> select count(1) from "OPDB1_GGTEST_USER"."OPDB1_GGTEST_TBL";
COUNT(1)
----------
1098
SQL>
So another 99 records we added in source is copied here properly.
14) Examining the logfile:
/oggdata/oggdep01/var/log
[oracle@vcentos79-oracle-sa1 log]$ ls -altr
total 101076
drwxr-x---. 7 oracle oinstall 63 Jun 12 20:02 ..
-rw-r-----. 1 oracle oinstall 0 Jun 12 20:02 recvsrvr-stdout.log
-rw-r-----. 1 oracle oinstall 10467995 Jun 22 08:01 restapi.log.9
-rw-r-----. 1 oracle oinstall 10471575 Jun 22 08:11 restapi.log.8
-rw-r-----. 1 oracle oinstall 10474376 Jun 25 10:11 restapi.log.7
-rw-r-----. 1 oracle oinstall 10482864 Jun 25 10:31 restapi.log.6
-rw-r-----. 1 oracle oinstall 10481594 Jun 25 10:51 restapi.log.5
-rw-r-----. 1 oracle oinstall 10480824 Jun 25 16:44 restapi.log.4
-rw-r-----. 1 oracle oinstall 10474205 Jun 25 17:03 restapi.log.3
-rw-r-----. 1 oracle oinstall 1823662 Jun 25 17:06 gglog-EXTS.dmp
-rw-r-----. 1 oracle oinstall 10476326 Jun 25 17:21 restapi.log.2
-rw-r-----. 1 oracle oinstall 10471575 Jun 25 17:41 restapi.log.1
drwxr-x---. 2 oracle oinstall 4096 Jun 25 17:41 .
-rw-r-----. 1 oracle oinstall 92195 Jul 1 08:16 recvsrvr.log
-rw-r-----. 1 oracle oinstall 6373 Jul 1 08:16 distsrvr-stdout.log
-rw-r-----. 1 oracle oinstall 149616 Jul 1 08:16 distsrvr.log
-rw-r-----. 1 oracle oinstall 59334 Jul 1 15:53 extract.log
-rw-r-----. 1 oracle oinstall 6729 Jul 1 16:00 gglog-REPT.dmp
-rw-r-----. 1 oracle oinstall 8062 Jul 1 16:00 ER-events.log
-rw-r-----. 1 oracle oinstall 459156 Jul 1 16:04 adminsrvr.log
-rw-r-----. 1 oracle oinstall 5374256 Jul 1 16:06 restapi.log
-rw-r-----. 1 oracle oinstall 1020878 Jul 1 16:06 pmsrvr.log
-rw-r-----. 1 oracle oinstall 53834 Jul 1 16:06 replicat.log
-rw-r-----. 1 oracle oinstall 115360 Jul 1 16:06 ggserr.log
[oracle@vcentos79-oracle-sa1 log]$ view ggserr.log
[oracle@vcentos79-oracle-sa1 log]$ view extract.log
[oracle@vcentos79-oracle-sa1 log]$
Snippet of replicat.log:
2023-07-01T16:06:41.320+0100 INFO | INFO OGG-00482 Oracle GoldenGate Delivery for Oracle, REPT.prm.backup: DDL found, operation [create table opdb1_public_user.test as select * from dba_users where rownum <3 (size 78)]. (main)
2023-07-01T16:06:41.320+0100 INFO | INFO OGG-00489 Oracle GoldenGate Delivery for Oracle, REPT.prm.backup: DDL is of mapped scope, after mapping new operation [create table "OPDB1_PUBLIC_USER"."TEST" as select * from dba_users where rownum <3 (size 82)]. (main)
..
2023-07-01T16:06:43.396+0100 INFO | INFO OGG-00494 Oracle GoldenGate Delivery for Oracle, REPT.prm.backup: DDL error discarded: error code [DEFAULT], filter [include all (default)], error text [Current time: 2023-07-01 16:06:43
Error text [Error code [955], Error code [1435], ORA-01435: user does not exist
], operation [create table "OPDB1_GGTEST_USER"."OPDB1_GGTEST_TBL" tablespace OPDB1_GGTEST_TBSPC as
select level eid,mod(level,6) pincode,rpad('X',mod(level,5)) branchcode,lpad('A',mod(level,5))||rpad('B',mod(level,]
Operation failed at seqno 4 rba 13,971.]. (main)
2023-07-01T16:06:43.416+0100 INFO | INFO OGG-01408 Oracle GoldenGate Delivery for Oracle, REPT.prm.backup: Restoring current schema for DDL operation to schema GGADMIN. (main)
2023-07-01T16:06:43.427+0100 INFO | INFO OGG-06506 Oracle GoldenGate Delivery for Oracle, REPT.prm.backup: Wildcard MAP resolved (entry OPDB1.OPDB1_GGTEST_USER.*): Map "OPDB1"."OPDB1_GGTEST_USER"."OPDB1_GGTEST_TBL", target OPDB1_GGTEST_USER."OPDB1_GGTEST_TBL", FILTER ( @GETENV('TRANSACTION', 'CSN') > 3540599). (main)
2023-07-01T16:06:46.130+0100 INFO | INFO OGG-02756 Oracle GoldenGate Delivery for Oracle, REPT.prm.backup: The definition for table OPDB1.OPDB1_GGTEST_USER.OPDB1_GGTEST_TBL is obtained from the trail file. (main)
2023-07-01T16:06:46.130+0100 INFO | INFO OGG-06511 Oracle GoldenGate Delivery for Oracle, REPT.prm.backup: Using following columns in default map by name: EID, PINCODE, BRANCHCODE, ENAME. (main)
2023-07-01T16:06:46.130+0100 INFO | INFO OGG-06510 Oracle GoldenGate Delivery for Oracle, REPT.prm.backup: Using the following key columns for target table OPDB1_GGTEST_USER.OPDB1_GGTEST_TBL: EID, PINCODE, BRANCHCODE, ENAME. (main)
If we observe above, we can see GG captured the DDL very properly and then reproduced them in target. Anyway we got the table loaded in advance, so could be the error.
15) replication stats:
command:
stats replicat name [name of the replicat group]
Actual:
stats replicat rept
Actual output:
stats replicat rept
OGG (http://127.0.0.1:9011 oggdep01 as ggadmin_DBCASLNT@DBCASLNT/CDB$ROOT) 31> stats replicat rept
Sending STATS request to Replicat group REPT ...
Parallel Replicat statistics:
*** Total statistics since 2023-07-01 16:04:42 ***
Total transactions 11.00
Serialized transactions 10.00
Due to:
DDL executions 10.00
Workload dependency statistics:
CHILD PARENT COUNT
============================================================================
Other Table dependencies:
TABLE TYPE COUNT
============================================================================
*** Daily statistics since 2023-07-01 16:04:42 ***
Total transactions 11.00
Serialized transactions 10.00
Due to:
DDL executions 10.00
Workload dependency statistics:
CHILD PARENT COUNT
============================================================================
Other Table dependencies:
TABLE TYPE COUNT
============================================================================
..
*** Latest statistics since 2023-07-01 16:04:42 ***
Total inserts 99.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 99.00
End of statistics.
Let us 10 more records and see what happens:
insert into OPDB1_GGTEST_TBL
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 < 10;
16:19:40 SQL> select count(1) from "OPDB1_GGTEST_USER"."OPDB1_GGTEST_TBL";
COUNT(1)
----------
1107
Elapsed: 00:00:00.00
16:19:46 SQL>
extract updates the trail:
[oracle@vcentos79-oracle-sa1 src]$ ls -altr
total 52
drwxr-x---. 3 oracle oinstall 17 Jun 18 20:22 ..
-rw-r-----. 1 oracle oinstall 1329 Jun 18 21:26 es000000000
-rw-r-----. 1 oracle oinstall 2450 Jun 21 07:21 es000000001
-rw-r-----. 1 oracle oinstall 7202 Jun 25 17:06 es000000002
-rw-r-----. 1 oracle oinstall 1388 Jul 1 14:28 es000000003
drwxr-x---. 2 oracle oinstall 101 Jul 1 14:28 .
-rw-r-----. 1 oracle oinstall 29339 Jul 1 16:18 es000000004 <<< updated extract file
[oracle@vcentos79-oracle-sa1 src]$ pwd
/oggdata/oggdep01/var/lib/data/src
[oracle@vcentos79-oracle-sa1 src]$
replication stats:
*** Total statistics since replicat started ***
Operations 10.00
Mapped operations 10.00
Unmapped operations 0.00
Other operations 0.00
Excluded operations 0.00
Errors 2.00
Retried errors 0.00
Discarded errors 2.00
Ignored errors 0.00
..
*** Latest statistics since 2023-07-01 16:04:42 ***
Total inserts 108.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 108.00 <<< from 99 to 108 now
End of statistics.
Try a truncate in source:
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 1 16:23:00 2023 <<< 16:23
...
SQL> truncate table OPDB1_GGTEST_TBL;
Table truncated.
SQL> select count(1) from OPDB1_GGTEST_TBL;
COUNT(1)
----------
0
SQL>
replicat log:
2023-07-01T16:24:13.669+0100 INFO | INFO OGG-00482 Oracle GoldenGate Delivery for Oracle, REPT.prm.backup: DDL found, operation [truncate table OPDB1_GGTEST_TBL (size 31)]. (main)
2023-07-01T16:24:13.669+0100 INFO | INFO OGG-00540 Oracle GoldenGate Delivery for Oracle, REPT.prm.backup: Metadata not cleared for "OPDB1_GGTEST_USER".OPDB1_GGTEST_TBL because of TRUNCATE. (main)
2023-07-01T16:24:13.669+0100 INFO | INFO OGG-00489 Oracle GoldenGate Delivery for Oracle, REPT.prm.backup: DDL is of mapped scope, after mapping new operation [truncate table "OPDB1_GGTEST_USER"."OPDB1_GGTEST_TBL" (size 53)]. (main)
2023-07-01T16:24:13.669+0100 INFO | INFO OGG-10451 Oracle GoldenGate Delivery for Oracle, REPT.prm.backup: DDL operation included [default], optype [TRUNCATE], objtype [TABLE], catalog "OPDB1", objowner "OPDB1_GGTEST_USER", objname "OPDB1_GGTEST_TBL". (main)
2023-07-01T16:24:13.813+0100 INFO | INFO OGG-01407 Oracle GoldenGate Delivery for Oracle, REPT.prm.backup: Setting current schema for DDL operation to schema OPDB1_GGTEST_USER. (main)
2023-07-01T16:24:13.813+0100 INFO | INFO OGG-00484 Oracle GoldenGate Delivery for Oracle, REPT.prm.backup: Executing DDL operation. (main)
2023-07-01T16:24:13.956+0100 INFO | INFO OGG-00483 Oracle GoldenGate Delivery for Oracle, REPT.prm.backup: DDL operation successful. (main)
2023-07-01T16:24:13.956+0100 INFO | INFO OGG-01408 Oracle GoldenGate Delivery for Oracle, REPT.prm.backup: Restoring current schema for DDL operation to schema GGADMIN. (main)
no updates to extract log.
Output in target:
16:24:30 SQL> select count(1) from "OPDB1_GGTEST_USER"."OPDB1_GGTEST_TBL";
COUNT(1)
----------
0
Elapsed: 00:00:00.00
16:27:37 SQL>
Let us test the other existing schema replication, to be sure it works as expected:
Source:
SQL> insert into T1 values(5,'01JUN23');
1 row created.
SQL> select * from t1;
ID USR
---------- ----------
5 01JUN23
1 id
2 2id
SQL> commit;
Commit complete.
SQL>
Target:
16:27:37 SQL> select * from opdb1_public_user.t1;
ID USR
---------- ----------
1 id
2 2id
Elapsed: 00:00:00.03
16:31:00 SQL> /
ID USR
---------- ----------
1 id
2 2id
5 01JUN23
Elapsed: 00:00:00.00
16:31:29 SQL>
So we verified the new incremental replication change and old replication setups work as expected.
This closes GG replication setup using adminclient. The same procedure covers how to add incremental changes to the existing replication.
Thanks
Nicely explained with detailed example.
ReplyDelete