Oracle GoldenGate – Integrated Replicat
Integrated Replicat was introduced from the Oracle GoldenGate 12c. Similar to Integrated Extract, Integrated Replicat is also tightly coupled with Oracle Database.
Integrated Replicat operates as below,
Reads the OGG Trail files which are pushed by the Data Pump process from the Source.
Performs filtering, mapping and conversion of data.
Constructs logical change records (LCRs) of the Source DML transactions in committed order. DDL operations are applied directly by Replicat.
Attaches the Database Inbound Server to the database by means of a Lightweight Streaming API.
Transfers the constructed LCRs to the Inbound Server which then applies the data to the Target Database.
Like Integrated Extract, you can also configure parallelism in Integrated Replicat process. Multiple Inbound Server child processes which are known as APPLY SERVERS applies data in parallel to the target database maintaining the original transaction atomicity. Below is the architecture diagram of the Integrated Replicat,
By default, Integrated Replicat applies the transactions Asynchronously. Transactions which are with no dependencies are applied safely executed and committed out of order to achieve fast throughput. Transactions which are dependencies are applied in the same order as on the source.
Integrated Replicat process also has it’s own sub processes which are as below,
REPLICAT – Reads the trail files and constructs the LCRs and then transfers it to the database using the LLghtweight Streaming API.
RECEIVER – Reads the LCRs
PREPARER – It computes the dependencies of the transactions and sort them in order.
COORDINATOR – Coordinates the transactions and maintains the order between the Applier processes.
APPLIER – Applies the transactions to the database. Also, performs the changes for the transactions received from coordinator including CDR and error handling.
If the Inbound Server does not support any column data types or any other feature, it automatically applies the transactions to the database in DIRECT APPLY (Classic mode) using the OCI.
The following features are applied in DIRECT MODE by the Integrated Replicat:
DDL operations
Sequence operations
SQLEXEC parameter within a TABLE or MAP parameter
EVENTACTIONS processing
UDT Note, if the extract uses USENATIVEOBJSUPPORT to capture the UDT, then integrated Replicat will apply it with the inbound server, otherwise it will be handled by Replicat directly.
For example, if you have more of DDL operations or Sequence operations, then the Integrated Replicat will switch to Direct Apply to apply these operations and then switch back to Integrated Mode. This degrades the performance of the Integrated Replicat process even though if you set sufficient parallelism and memory.
Below are the advantages of the Integrated Replicat over the Classic or Non-Integrated Replicat process.,
Integrated Replicat supports parallelism and hence it applies the transactions in parallel which gives good performance rate while preserving the integrity and atomicity of the source transaction.. The parallelism can be set using the parameter PARALLELISM and MAX_PARALLELISM. The parallel apply servers increases and decreases automatically according to the workload.
There is no need of Range Replicat processes if you have huge tables. Integrated Replicat is very easy to configure and only one IR is enough as it supports parallelism.
High-performance apply streaming is enabled for integrated Replicat by means of a lightweight application programming interface (API) between Replicat and the inbound server.
Integrated Replicat process coordinates and handles the barrier transactions among multiple server apply processes.
When a DDL operations is seen, Integrated Replicat processes it as direct transactions that force a barrier by waiting for server processing to complete before the DDL execution.
Integrated Replicat process easily handles the Transient duplicate primary key updates.
Integrated Replicat can be configured in a single or pluggable databases (Multitenant).
To configure Integrated Replicat process there are some requirements which are below,
The Target database should be Oracle 11.2.0.4 and later.
Supplemental Logging should be enabled on the Source.
When using this Integrated Replicat, you need to enable trandata at OGG level using ADD SCHEMATRANDATA and not with ADD TRANDATA. Also, you need to use the parameter LOGALLSUPCOLS at the source Extract paraemeter file. Else, you will be hitting “ORA-26688: missing key in LCR”.
Integrated Replicat process can be used only in Oracle Database. It doesn’t supports Non-Oracle databases.
Need to set STREAMS_POOL_SIZE
This plays a vital role in the Integrated Feature. You need to configure explicitly the Streams_Pool_Size when you configure the Integrated Mode of Capture.
Integrated Capture takes or consumes memory from the STREAMS_POOL_SIZE. There are some calculations to size the streams pool. Please check the below link on
how to size this STREAMS_POOL
Need to apply the RECOMMENDED PATCHES
There are some recommended patches to be applied to the Oracle Database when using the Integrated Capture/Replicat. Please check the below KM Note.,
Oracle GoldenGate — Oracle RDBMS Server Recommended Patches (Doc ID 1557031.1)
Latest GoldenGate/Database (OGG/RDBMS) Patch recommendations (Doc ID 2193391.1)
Let’s go in to the configuration now,
GGSCI (OGGR2-1.localdomain as ggadmin@GGDB1) 2> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING
Add the parameters for the Integrated Replicat process “INREP”
GGSCI (OGGR2-1.localdomain as ggadmin@GGDB1) 3> edit params INREP
REPLICAT inrep
USERID ggadmin, PASSWORD oracle
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
AssumeTargetDefs
MAP source.t1, TARGET target.t1;
Save the file and check the parameters once.
GGSCI (OGGR2-1.localdomain as ggadmin@GGDB1) 4> view params INREP REPLICAT inrep USERID ggadmin, PASSWORD oracle DBOPTIONS INTEGRATEDPARAMS(parallelism 6) AssumeTargetDefs MAP source.t1, TARGET target.t1;
DBOPTIONS INTEGRATEDPARAMS(parallelism 6) – denotes that for this Integrated Replicat, you are specifying a minimum number of parallel apply processes which will be 6.
To know more about these options, please refer to the below link,
https://docs.oracle.com/goldengate/1212/gg-winux/GIORA/addl_integrated_params.htm#GIORA245
All the pre-requisites are taken care already. In my last post, we saw about the Integrated Extract process. We need to register the Integrated Extract process. But, Integrated Replicat process will be registered automatically to the database. So, we don’t want to explicitly register the Integrated Replicat process. So, let’s just add the Integrated Replicat process,
GGSCI (OGGR2-1.localdomain as ggadmin@GGDB1) 5> add replicat inrep INTEGRATED exttrail ./dirdat/lt REPLICAT (Integrated) added.
You could see an Integrated Replicat process is created.
GGSCI (OGGR2-1.localdomain as ggadmin@GGDB1) 10> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT STOPPED INREP 00:00:00 00:01:38
Now Start the IR process,
GGSCI (OGGR2-1.localdomain as ggadmin@GGDB1) 14> start INREP Sending START request to MANAGER ... REPLICAT INREP starting
Now the process is in RUNNING state.
GGSCI (OGGR2-1.localdomain as ggadmin@GGDB1) 15> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING INREP 00:00:00 00:02:20
Below are the Entries in the Alert log file,
GoldenGate APPLY AP02 for OGG$INREP started with pid=25, OS id=3023 APPLY OGG$INREP: Apply User: GGADMIN APPLY OGG$INREP: Apply Tag: 00 APPLY OGG$INREP: Parameter Set by User: PARALLELISM Value: 6 APPLY OGG$INREP: Parameter Set by User: ALLOW_DUPLICATE_ROWS Value: Y APPLY OGG$INREP: Parameter Set by User: PRESERVE_ENCRYPTION Value: N APPLY OGG$INREP: Parameter Set by User: EAGER_SIZE Value: 15100 APPLY OGG$INREP: Parameter Set by User: CDGRANULARITY Value: ROW APPLY OGG$INREP: Parameter Set by User: SUPPRESSTRIGGERS Value: Y Sat Sep 29 11:42:29 2018 GoldenGate Apply Reader for OGG$INREP started AS01 with pid=27 OS id=3025 Sat Sep 29 11:42:29 2018 Sat Sep 29 11:42:29 2018 GoldenGate Apply Server for OGG$INREP started AS02 with pid=28 OS id=3027 Sat Sep 29 11:42:29 2018 GoldenGate Apply Server for OGG$INREP started AS04 with pid=30 OS id=3031 GoldenGate Apply Server for OGG$INREP started AS06 with pid=32 OS id=3035 Sat Sep 29 11:42:29 2018 Sat Sep 29 11:42:29 2018 GoldenGate Apply Server for OGG$INREP started AS03 with pid=29 OS id=3029 GoldenGate Apply Server for OGG$INREP started AS05 with pid=31 OS id=3033 Sat Sep 29 11:42:29 2018 GoldenGate Apply Server for OGG$INREP started AS07 with pid=33 OS id=3037 APPLY OGG$INREP: Source Database: NA APPLY OGG$INREP: Applied Message Number: NA APPLY OGG$INREP: Message Create Time: NA Sat Sep 29 11:42:30 2018 Propagation Receiver (CCA) for GoldenGate replicat and Apply OGG$INREP with pid=34, OS id=3021, objnum=0 started.
Below is the information in the report file of the Integrated Replicat process,
***********************************************************************
Oracle GoldenGate Delivery for Oracle
Version 12.3.0.1.0 OGGCORE_12.3.0.1.0_PLATFORMS_170721.0154_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jul 21 2017 20:36:55
Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.
Starting at 2018-09-29 11:42:22
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Wed Feb 2 18:40:23 EST 2011, Release 2.6.32-100.28.5.el6.x86_64
Node: OGGR2-1.localdomain
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 3016
Description:
2018-09-29 11:42:22 WARNING OGG-02904 Replication of PARTIAL XML containing NCHAR/NVARCHAR/NCLOB data may cause divergence.
***********************************************************************
** Running with the following parameters **
***********************************************************************
2018-09-29 11:42:22 INFO OGG-03059 Operating system character set identified as UTF-8.
2018-09-29 11:42:22 INFO OGG-02695 ANSI SQL parameter syntax is used for parameter parsing.
2018-09-29 11:42:22 INFO OGG-01360 REPLICAT is running in Integrated mode.
REPLICAT inrep
USERID ggadmin, PASSWORD ***
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
AssumeTargetDefs
MAP source.t1, TARGET target.t1;
2018-09-29 11:42:23 INFO OGG-06451 Triggers will be suppressed by default.
2018-09-29 11:42:23 INFO OGG-01815 Virtual Memory Facilities for: COM
anon alloc: mmap(MAP_ANON) anon free: munmap
file alloc: mmap(MAP_SHARED) file free: munmap
target directories:
/ogg/dirtmp.
Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Database Language and Character Set:
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "WE8MSWIN1252"
2018-09-29 11:42:23 INFO OGG-06604 Database GGDB1 CPU info: CPU Count 1, CPU Core Count 0, CPU Socket Count 0.
2018-09-29 11:42:23 WARNING OGG-05673 CSN-based duplicate suppression is disabled because there is no checkpoint table for this Replicat.
2018-09-29 11:42:23 INFO OGG-02545 Parameter GROUPTRANSOPS is ignored by Integrated Replicat when parallelism is greater than 1.
2018-09-29 11:42:23 INFO OGG-02527 Integrated Replicat does not populate a trace table.
2018-09-29 11:42:30 INFO OGG-02530 Integrated replicat successfully attached to inbound server OGG$INREP.
***********************************************************************
** Run Time Messages **
***********************************************************************
2018-09-29 11:42:30 INFO OGG-02243 Opened trail file /ogg/dirdat/lt000000000 at 2018-09-29 11:42:30.874662.
2018-09-29 11:42:30 WARNING OGG-02760 ASSUMETARGETDEFS is ignored because trail file /ogg/dirdat/lt000000000 contains table definitions.
2018-09-29 11:42:30 INFO OGG-03522 Setting session time zone to source database time zone 'GMT'.
2018-09-29 11:42:30 INFO OGG-02232 Switching to next trail file /ogg/dirdat/lt000000001 at 2018-09-29 11:42:30.874662 due to EOF. with current RBA 1,401.
2018-09-29 11:42:30 INFO OGG-03506 The source database character set, as determined from the trail file, is we8mswin1252.
Processed extract process graceful restart record at seq 1, rba 1401.How to check if the replicat process created or running is a Classic or Integrated? It is simple. We can see the keyword INTEGRATED in the below command output,
GGSCI (OGGR2-1.localdomain as ggadmin@GGDB1) 16> info INREP
REPLICAT INREP Last Started 2018-09-29 11:42 Status RUNNING
INTEGRATED
Checkpoint Lag 00:00:00 (updated 00:00:09 ago)
Process ID 3016
Log Read Checkpoint File /ogg/dirdat/lt000000001
2018-09-29 11:17:08.839373 RBA 1461Below is the detailed information of the Integrated Replicat process,
GGSCI (OGGR2-1.localdomain as ggadmin@GGDB1) 17> info INREP, detail
REPLICAT INREP Last Started 2018-09-29 11:42 Status RUNNING
INTEGRATED
Checkpoint Lag 00:00:00 (updated 00:00:04 ago)
Process ID 3016
Log Read Checkpoint File /ogg/dirdat/lt000000001
2018-09-29 11:17:08.839373 RBA 1461
INTEGRATED Replicat
DBLOGIN Provided, inbound server name is OGG$INREP in ATTACHED state
Current Log BSN value: (no data)
Low Watermark CSN value: (no data)
High Watermark CSN value: (no data)
Extract Source Begin End
/ogg/dirdat/lt000000001 * Initialized * 2018-09-29 11:17
/ogg/dirdat/lt000000000 * Initialized * First Record
./dirdat/lt000000000 * Initialized * First Record
Current directory /ogg
Report file /ogg/dirrpt/INREP.rpt
Parameter file /ogg/dirprm/inrep.prm
Checkpoint file /ogg/dirchk/INREP.cpr
Process file /ogg/dirpcs/INREP.pcr
Error log /ogg/ggserr.logOne of the difference between Classic Replicat and Integrated Replicat is, like Classic Replicat process, Integrated Replicat does not have a Checkpoint Table. In the above output, you can clearly see there is no checkpoint table details. So, where does the Integrated Replicat stores it’s checkpoint information apart from the checkpoint file? Integrated Replicat is integrated with database, which means Oracle database knows about it. There are several Oracle GoldenGate views defined in the database data dictionary which can be queried to display information about Oracle GoldenGate queues and processes.
To check the Checkpoint information, below is the view.,
SQL> desc ggadmin.DBA_GG_INBOUND_PROGRESS Name Null? Type ----------------------------------------- -------- ---------------------------- SERVER_NAME NOT NULL VARCHAR2(30) PROCESSED_LOW_POSITION VARCHAR2(4000) APPLIED_LOW_POSITION VARCHAR2(4000) APPLIED_HIGH_POSITION VARCHAR2(4000) SPILL_POSITION VARCHAR2(4000) OLDEST_POSITION VARCHAR2(4000) APPLIED_LOW_SCN NOT NULL NUMBER APPLIED_TIME DATE APPLIED_MESSAGE_CREATE_TIME DATE SOURCE_DATABASE NOT NULL VARCHAR2(128) LOGBSN VARCHAR2(4000)
To know about the Integrated Applier details,
SQL> desc ggadmin.DBA_GOLDENGATE_INBOUND Name Null? Type ----------------------------------------- -------- ---------------------------- REPLICAT_NAME VARCHAR2(4000) SERVER_NAME NOT NULL VARCHAR2(30) APPLY_USER VARCHAR2(30) USER_COMMENT VARCHAR2(4000) CREATE_DATE TIMESTAMP(6) STATUS VARCHAR2(8)
SQL> select replicat_name,server_name from ggadmin.DBA_GOLDENGATE_INBOUND; REPLICAT_NAME SERVER_NAME ------------------------------ ------------------------------ INREP OGG$INREP
SQL> desc ggadmin.DBA_APPLY Name Null? Type ----------------------------------------- -------- ---------------------------- APPLY_NAME NOT NULL VARCHAR2(30) QUEUE_NAME NOT NULL VARCHAR2(30) QUEUE_OWNER NOT NULL VARCHAR2(30) APPLY_CAPTURED VARCHAR2(3) RULE_SET_NAME VARCHAR2(30) RULE_SET_OWNER VARCHAR2(30) APPLY_USER VARCHAR2(30) APPLY_DATABASE_LINK VARCHAR2(128) APPLY_TAG RAW(2000) DDL_HANDLER VARCHAR2(98) PRECOMMIT_HANDLER VARCHAR2(98) MESSAGE_HANDLER VARCHAR2(98) STATUS VARCHAR2(8) MAX_APPLIED_MESSAGE_NUMBER NUMBER NEGATIVE_RULE_SET_NAME VARCHAR2(30) NEGATIVE_RULE_SET_OWNER VARCHAR2(30) STATUS_CHANGE_TIME DATE ERROR_NUMBER NUMBER ERROR_MESSAGE VARCHAR2(4000) MESSAGE_DELIVERY_MODE VARCHAR2(10) PURPOSE VARCHAR2(19)
SQL> select apply_name,queue_name from ggadmin.DBA_APPLY; APPLY_NAME QUEUE_NAME ------------------------------ ------------------------------ OGG$INEXT OGG$Q_INEXT OGG$INREP OGGQ$INREP
Hope this post given you a clear view about the Integrated Replicat process. Will explain about the Coordinated Replicat in my next post.
Cheers 🙂
Loading ...
Total Users : 1822621
Hi…
Can we know the exact rba replicate is reading from the trail file. Let me provide the scenario.
I do Info replicat REP and it provides me log read checkpoint at file009. However I ran a delete statement on source side which deleted 3 million rows and they are split in 5 trail files from seq file009 to file014. It is one transaction. How do we know how many records Gg replicate process has read and applied.
We are on GG 12.3 and have configured integrated parallel replicat with 6 processes for replicat.
Thanks for ur time
You can use the SEND command to check the progress of the replicat process. INFO command will give you old detail. It fetches the information from checkpoint file and the checkpoint file will get updated by the process only when it sees a COMMIT. SEND command will directly communicate to the process and gives you the latest information.
your articles are really helpful. Thanks for sharing.