Monthly Archive: September 2018

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 1461

Below 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.log

One 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 🙂

Oracle GoldenGate – Integrated Capture

Oracle has introduced Integrated Extract or Integrated Capture from OGG 11.2.x. They bind this capture with the Oracle RDBMS and hence it is called Integrated Capture. The Classic Capture is decoupled with the Oracle Database where in the Integrated Capture is tightly coupled with the Oracle Database. Instead of the Extract process directly capturing the transactions from the database, in here, the Logmining Server plays the role. A LogMining server will be attached with this Integrated Capture process in the database to mine the redo or archive logs and captures the transactions. This process mainly works on the streams concepts. So, Integrated Capture has the below sub-processes.

  • READER – Reads logfiles and splits in to regions

  • PREPARER – Scans regions of logfiles and pre-filters based on extract parameters

  • BUILDER – Merges prepared records in SCN order

  • CAPTURE – Formats Logical Change Records (LCRs) and passes to Oracle GoldenGate Extract

 

Below is the architecture diagram of LogMining Server.

 

Once the Capture passes the LCRs to the Extract, the Extract process performs the mappings and transformations of the transactions according to the parameters which we had mentioned in the Extract process parameter file and finally writes them to the trail files called Local Trail Files.

So, the work of the Extract process in here is as below,

  • Requests LCRs from Logmining Server

  • Performs Mapping and Transformations

  • Writes the data to the Trail Files

Oracle GoldenGate supports all Oracle data and storage types. But to have this the source database should be at least 11.2.0.3 and a specific database bundle patch needs to be applied. Please refer the below doc,

Oracle GoldenGate — Oracle RDBMS Server Recommended Patches (Doc ID 1557031.1)

You can use integrated capture on an 11.2.0.3 downstream mining database for a source database with a compatibility less than 11.2.0.3, but in this mode, SECUREFILE LOBs, XML columns, Transparent Data Encryption, and UDTs have limited support based on database version and compatibility. But in here also, you need to apply the database specific bundle patch as per the below doc,

Oracle GoldenGate — Oracle RDBMS Server Recommended Patches (Doc ID 1557031.1)

Since the Integrated Capture is tightly coupled with the Oracle Database, there are many advantages when comparing to the Classic Capture. They are as below,

  • No additional setup is required when configured with Oracle RAC, ASM or TDE.

  • Integrated Capture uses the Logmining Server. This gives an additional advantage for the Integrated Capture to switch between different copies of redo and archive logs copies when there is a missing of log file due to disk corruption.

  • Faster filtering of tables

  • Supports all Oracle data and storage types

  • Efficiently handles point in time recovery and RAC integration. Whenever there is a inter-node failure, Integrated handles efficiently which is not in the case of Classic Capture.

  • It has an integrated log management which makes the RMAN to retain the archive logs automatically which are needed by the Integrated Extract process.

  • We all know that Oracle has introduced Multitenant Architecture (Container Database concepts) from Oracle 12c. Integrated Extract is the only mode which has the ability or supports capturing data from a Multitenant container database. Single Integrated Capture is more than enough to mine from multiple pluggable databases (PDBs).

  • For an Integrated Extract, no additional scripts are needed to be executed and this is called Native-DDL. For this, source database should be 11.2.0.4 with compatibility set to 11.2.0.4 or higher. If the source database is earlier than version 11.2.0.4 then DDL trigger must be used.

Integrated Capture vs Classic Capture

    • Tightly coupled with Oracle Database.

    • Can be used only with Oracle Database.

    • Supports parallelism. Multiple processes captures the data in parallel.

    • No requirement of setting ASMUSER or DBLOGREADER since the Logmining Server mines the redo or archive logs.

    • Whenever there is a node failure or addition of node in a RAC, no manual intervention required. It takes care automatically.

    • Works based on the STREAMS concepts.

    • Can be configured only when your OGG is 11.2.0.x or higher.

 

Pre-requisites for Integrated Extract

1.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

2. 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)

3. ARCHIVE LOG MODE

The database should be in ArchiveLog Mode when Oracle GoldenGate configured. Oracle GoldenGate captures the changes either from Redo Logs or Archive Logs. Please change the database to the ArchiveLog Mode.

Now, let’s go to the configuration of Integrated Extract process.

GGSCI (OGGR2-1.localdomain) 1> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

Add the parameters for the Integrated Extract process “INEXT”

GGSCI (OGGR2-1.localdomain) 2> edit params inext

EXTRACT inext
USERID ggadmin, PASSWORD oracle
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 100, PARALLELISM 2)
EXTTRAIL /ogg/dirdat/lt
TABLE source.T1;

Save the file and check the parameters once.

GGSCI (OGGR2-1.localdomain) 3> view params inext

EXTRACT inext
USERID ggadmin, PASSWORD oracle
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 100, PARALLELISM 2)
EXTTRAIL /ogg/dirdat/lt
TABLE source.T1;

TRANLOGOPTIONS [INTEGRATEDPARAMS (parameter[, …])] – This is an optional parameter. It passes the parameters set using this option to the oracle database Logmining Server.

 

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#GIORA233

As per the pre-requisite, I have already set the streams_pool_size. And also, all the other pre-requisites are taken care.

SQL> show parameter streams_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size big integer 208M

Register the extract process to the database with the below command.

GGSCI (OGGR2-1.localdomain as ggadmin@GGDB1) 8> register extract INEXT, database

2018-09-24 11:53:24 INFO OGG-02003 Extract INEXT successfully registered with database at SCN 4043243.

This will create a Logminer Build and the capture begins from the time that REGISTER EXTRACT is issued. A Capture will be created in the database with the name OGG$CAP_INEXT. The format for this is “OGG$CAP_EXTRACT-NAME”.

Below are the entries in the alert log file during the time of register.

 

ALERT LOG FILE

Logminer Bld: Build started
ALTER SYSTEM SWITCH ALL LOGFILE start (GGDB1)
Thread 1 advanced to log sequence 277 (LGWR switch)
Current log# 1 seq# 277 mem# 0: /vol1/GGDB1/redo01.log
ALTER SYSTEM SWITCH ALL LOGFILE complete (GGDB1)
Mon Sep 24 11:53:15 2018
Archived Log entry 273 added for thread 1 sequence 276 ID 0xd6ddca38 dest 1:
Mon Sep 24 11:53:15 2018
Logminer Bld: Lockdown Complete. DB_TXN_SCN is 0 4043243 LockdownSCN is 4043243
ALTER SYSTEM ARCHIVE LOG
Thread 1 cannot allocate new log, sequence 278
Checkpoint not complete
Current log# 1 seq# 277 mem# 0: /vol1/GGDB1/redo01.log
Thread 1 advanced to log sequence 278 (LGWR switch)
Current log# 2 seq# 278 mem# 0: /vol2/GGDB1/redo02.log
Archived Log entry 274 added for thread 1 sequence 277 ID 0xd6ddca38 dest 1:
Mon Sep 24 11:53:21 2018
Logminer Bld: Done
GoldenGate Capture: OGG$CAP_INEXT CAPTURE Created
CAPTURE OGG$CAP_INEXT: Start SCN: 4043243 (0x3db1eb.00000000)
CAPTURE OGG$CAP_INEXT: First SCN: 4043243 (0x3db1eb.00000000)
CAPTURE OGG$CAP_INEXT: Required Checkpoint SCN: 0 (0x0000.00000000)
CAPTURE OGG$CAP_INEXT: Captured SCN: 0 (0x0000.00000000)
CAPTURE OGG$CAP_INEXT: Applied SCN: 0 (0x0000.00000000)
CAPTURE OGG$CAP_INEXT: Capture Type: LOCAL
CAPTURE OGG$CAP_INEXT: Logminer Id: 3
CAPTURE OGG$CAP_INEXT: Source Database: GGDB1
GoldenGate Apply: OGG$INEXT APPLY Created
APPLY OGG$INEXT: Apply User: GGADMIN
APPLY OGG$INEXT: Apply Tag: 00
Setting XOUT_CLIENT_EXISTS to Y for Capture: OGG$CAP_INEXT
Mon Sep 24 11:53:23 2018
GoldenGate CAPTURE CP01 for OGG$CAP_INEXT started with pid=43, OS id=3064
CAPTURE OGG$CAP_INEXT: Start SCN: 4043243 (0x3db1eb.00000000)
CAPTURE OGG$CAP_INEXT: First SCN: 4043243 (0x3db1eb.00000000)
CAPTURE OGG$CAP_INEXT: Required Checkpoint SCN: 0 (0x0000.00000000)
CAPTURE OGG$CAP_INEXT: Captured SCN: 0 (0x0000.00000000)
CAPTURE OGG$CAP_INEXT: Applied SCN: 0 (0x0000.00000000)
CAPTURE OGG$CAP_INEXT: Capture Type: LOCAL
CAPTURE OGG$CAP_INEXT: Logminer Id: 3
CAPTURE OGG$CAP_INEXT: Source Database: GGDB1
First applied SCN of the GoldenGate path from capture (OGG$CAP_INEXT) to propagation () to apply (OGG$INEXT) is set to SCN: 4043243 (0x3db1eb.00000000)
GoldenGate CAPTURE CP01 for OGG$CAP_INEXT with pid=43, OS id=3064 is in combined capture and apply mode.
Capture OGG$CAP_INEXT is handling 1 applies.
-- capture is running in apply-state checkpoint mode.
Starting persistent Logminer Session with sid = 3 for GoldenGate Capture OGG$CAP_INEXT
LOGMINER: Parameters summary for session# = 3
LOGMINER: Number of processes = 0, Transaction Chunk Size = 1
LOGMINER: Memory Size = 60M, Checkpoint interval = 1000M
LOGMINER: SpillScn 0, ResetLogScn 925702
LOGMINER: summary for session# = 3
LOGMINER: StartScn: 4044055 (0x0000.003db517)
LOGMINER: EndScn: 0
LOGMINER: HighConsumedScn: 4043243 (0x0000.003db1eb)
LOGMINER: session_flag: 0xb0
LOGMINER: DDL CKPT is on.
LOGMINER: Read buffers: 64
LOGMINER: Memory LWM: limit 10M, LWM 50M, 83%
LOGMINER: Memory Release Limit: 1M
LOGMINER: LowCkptScn: 0 (0x0000.00000000)
LOGMINER: HighCkptScn: 0 (0x0000.00000000)
LOGMINER: SkipScn: 4043243 (0x0000.003db1eb)
Mon Sep 24 11:53:34 2018
LOGMINER: Begin mining logfile during dictionary load for session 3 thread 1 sequence 277, /arch/1_277_874177400.dbf
Mon Sep 24 11:54:04 2018
Archived Log entry 277 added for thread 1 sequence 280 ID 0xd6ddca38 dest 1:
Mon Sep 24 11:54:14 2018
LOGMINER: Gathering statistics on logminer dictionary. (102)
LOGMINER: End mining logfiles during dictionary load for session 3

Now, add the Integrated Extract process. The command for adding Classic and Integrated extract is same, the only difference is the keyword “INTEGRATED”.

GGSCI (OGGR2-1.localdomain as ggadmin@GGDB1) 4> add extract inext INTEGRATED tranlog begin now
EXTRACT (Integrated) added.

Add the Local trail file to the Integrated Extract process.

GGSCI (OGGR2-1.localdomain as ggadmin@GGDB1) 5> add exttrail /ogg/dirdat/lt extract inext
EXTTRAIL added.

You could see an Integrated Extract process is created.

GGSCI (OGGR2-1.localdomain as ggadmin@GGDB1) 6> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT STOPPED INEXT 00:00:00 00:02:46

Start the Integrated Process.

GGSCI (OGGR2-1.localdomain as ggadmin@GGDB1) 7> start inext

Sending START request to MANAGER ...
EXTRACT INEXT starting

Now the process is in RUNNING state.

GGSCI (OGGR2-1.localdomain as ggadmin@GGDB1) 8> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING INEXT 00:00:04 00:00:03

Once you start the Integrated Capture, we can see the capture process in the database will start mining the redo or archive log file. Below are the entries which we can see from the alert log file,

ALERT LOG FILE

Mon Sep 24 12:13:42 2018
CAPTURE OGG$CAP_INEXT: Start SCN: 4043243 (0x3db1eb.00000000)
CAPTURE OGG$CAP_INEXT: First SCN: 4043243 (0x3db1eb.00000000)
CAPTURE OGG$CAP_INEXT: Required Checkpoint SCN: 0 (0x0000.00000000)
CAPTURE OGG$CAP_INEXT: Captured SCN: 4043243 (0x3db1eb.00000000)
CAPTURE OGG$CAP_INEXT: Applied SCN: 0 (0x0000.00000000)
CAPTURE OGG$CAP_INEXT: Capture Type: LOCAL
CAPTURE OGG$CAP_INEXT: Logminer Id: 3
CAPTURE OGG$CAP_INEXT: Source Database: GGDB1
CAPTURE OGG$CAP_INEXT: Bytes of Redo Mined: 18508
CAPTURE OGG$CAP_INEXT: SGA Used: 4843080
CAPTURE OGG$CAP_INEXT: SGA Allocated: 63460120
GoldenGate CAPTURE CP01 for OGG$CAP_INEXT with pid=43, OS id=3064 stopped
Mon Sep 24 12:13:42 2018
GoldenGate Capture:OGG$CAP_INEXT cleared _SKIP_LCR_FOR_ASSERT
GoldenGate Capture: OGG$CAP_INEXT
setting IGNORE_UNSUPPORTED_TABLE for table (*)
GoldenGate Capture:OGG$CAP_INEXT setting _FILTER_PARTIAL_ROLLBACK:
Setting XOUT_CLIENT_EXISTS to Y for Capture: OGG$CAP_INEXT
Mon Sep 24 12:13:43 2018
Starting persistent Logminer Session with sid = 3 for GoldenGate Capture OGG$CAP_INEXT (OS id=4100).
LOGMINER: Parameters summary for session# = 3
LOGMINER: Number of processes = 4, Transaction Chunk Size = 1
LOGMINER: Memory Size = 99M, Checkpoint interval = 1000M
LOGMINER: SpillScn 0, ResetLogScn 925702
LOGMINER: summary for session# = 3
LOGMINER: StartScn: 4043243 (0x0000.003db1eb)
LOGMINER: EndScn: 0
LOGMINER: HighConsumedScn: 4043243 (0x0000.003db1eb)
LOGMINER: session_flag: 0x4f0
LOGMINER: DDL CKPT is on.
LOGMINER: Read buffers: 64
LOGMINER: Memory LWM: limit 10M, LWM 89M, 89%
LOGMINER: Memory Release Limit: 1M
LOGMINER: LowCkptScn: 0 (0x0000.00000000)
LOGMINER: HighCkptScn: 0 (0x0000.00000000)
LOGMINER: SkipScn: 4043243 (0x0000.003db1eb)
Mon Sep 24 12:13:43 2018
LOGMINER: session#=3 (OGG$CAP_INEXT), reader MS00 pid=45 OS id=4108 sid=50 started
Mon Sep 24 12:13:43 2018
LOGMINER: session#=3 (OGG$CAP_INEXT), builder MS01 pid=46 OS id=4111 sid=78 started
Mon Sep 24 12:13:43 2018
LOGMINER: session#=3 (OGG$CAP_INEXT), preparer MS02 pid=47 OS id=4113 sid=71 started
Mon Sep 24 12:13:43 2018
LOGMINER: session#=3 (OGG$CAP_INEXT), preparer MS03 pid=48 OS id=4115 sid=51 started
OGG Capture client successfully attached to GoldenGate Capture OGG$CAP_INEXT to receive uncommitted changes with pid=43 OS id=4100.
LOGMINER: Begin mining logfile for session 3 thread 1 sequence 277, /arch/1_277_874177400.dbf
LOGMINER: End mining logfile for session 3 thread 1 sequence 277, /arch/1_277_874177400.dbf
LOGMINER: Begin mining logfile for session 3 thread 1 sequence 278, /arch/1_278_874177400.dbf
LOGMINER: End mining logfile for session 3 thread 1 sequence 278, /arch/1_278_874177400.dbf
LOGMINER: Begin mining logfile for session 3 thread 1 sequence 279, /arch/1_279_874177400.dbf
LOGMINER: End mining logfile for session 3 thread 1 sequence 279, /arch/1_279_874177400.dbf
LOGMINER: Begin mining logfile for session 3 thread 1 sequence 280, /vol1/GGDB1/redo01.log
LOGMINER: End mining logfile for session 3 thread 1 sequence 280, /vol1/GGDB1/redo01.log
LOGMINER: Begin mining logfile for session 3 thread 1 sequence 281, /vol2/GGDB1/redo02.log
LOGMINER: End mining logfile for session 3 thread 1 sequence 281, /vol2/GGDB1/redo02.log
LOGMINER: Begin mining logfile for session 3 thread 1 sequence 282, /vol3/GGDB1/redo03.log

How to check if the extract process created or running is a Classic or Integrated? It is very simple. We can see the ‘Log Read Checkpoint’ as “Oracle Integrated Redo Logs”.

For a Classic Capture it will be as “Oracle Redo Logs”.

GGSCI (OGGR2-1.localdomain as ggadmin@GGDB1) 9> info extract INEXT

EXTRACT INEXT Last Started 2018-09-24 12:13 Status RUNNING
Checkpoint Lag 00:00:09 (updated 00:00:02 ago)
Process ID 3792
Log Read Checkpoint Oracle Integrated Redo Logs
2018-09-24 12:14:27
SCN 0.4066962 (4066962)

Below is the detailed information of the Integrated Capture.

GGSCI (OGGR2-1.localdomain as ggadmin@GGDB1) 10> info extract INEXT, detail

EXTRACT INEXT Last Started 2018-09-24 12:13 Status RUNNING
Checkpoint Lag 00:00:04 (updated 00:00:02 ago)
Process ID 3792
Log Read Checkpoint Oracle Integrated Redo Logs
2018-09-24 12:14:42
SCN 0.4066971 (4066971)

Target Extract Trails:

Trail Name Seqno RBA Max MB Trail Type

/ogg/dirdat/lt 0 1401 500 EXTTRAIL
Integrated Extract outbound server first scn: 0.4043243 (4043243)

Integrated Extract outbound server filtering start scn: 0.4043243 (4043243)

Extract Source Begin End

Not Available 2018-09-24 12:10 2018-09-24 12:14
Not Available * Initialized * 2018-09-24 12:10
Not Available * Initialized * 2018-09-24 12:10
Not Available * Initialized * 2018-09-24 12:10
Current directory /ogg

Report file /ogg/dirrpt/INEXT.rpt
Parameter file /ogg/dirprm/inext.prm
Checkpoint file /ogg/dirchk/INEXT.cpe
Process file /ogg/dirpcs/INEXT.pce
Error log /ogg/ggserr.log

Below are the entries in the report file of the Integrated Extract INEXT

***********************************************************************
                 Oracle GoldenGate Capture 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:34:23

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


                    Starting at 2018-09-29 11:16:38
***********************************************************************

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: 2543

Description:

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************

2018-09-29 11:16:38  INFO    OGG-03059  Operating system character set identified as UTF-8.

2018-09-29 11:16:38  INFO    OGG-02695  ANSI SQL parameter syntax is used for parameter parsing.

2018-09-29 11:16:40  INFO    OGG-01360  EXTRACT is running in Integrated mode.
EXTRACT inext
USERID ggadmin, PASSWORD ***

2018-09-29 11:16:45  INFO    OGG-03522  Setting session time zone to source database time zone 'GMT'.
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 100, PARALLELISM 2)
EXTTRAIL /ogg/dirdat/lt
LOGALLSUPCOLS

2018-09-29 11:16:45  WARNING OGG-04033   LOGALLSUPCOLS has set the NOCOMPRESSDELETES and GETUPDATEBEFORES parameters on.
TABLE source.T1;

2018-09-29 11:16:45  INFO    OGG-01635  BOUNDED RECOVERY: reset to initial or altered checkpoint.

2018-09-29 11:16:45  INFO    OGG-01815  Virtual Memory Facilities for: BR
    anon alloc: mmap(MAP_ANON)  anon free: munmap
    file alloc: mmap(MAP_SHARED)  file free: munmap
    target directories:
    /ogg/BR/INEXT.

Bounded Recovery Parameter:
BRINTERVAL = 4HOURS
BRDIR      = /ogg/

2018-09-29 11:16:46  INFO    OGG-01851  filecaching started: thread ID: 139768570574608.

2018-09-29 11:16:46  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:16:46  INFO    OGG-03522  Setting session time zone to source database time zone 'GMT'.

2018-09-29 11:16:51  INFO    OGG-06604  Database GGDB1 CPU info: CPU Count 1, CPU Core Count 0, CPU Socket Count 0.

2018-09-29 11:17:02  INFO    OGG-02248  Logmining server DDL filtering enabled.

2018-09-29 11:17:04  INFO    OGG-02068  Integrated capture successfully attached to logmining server OGG$CAP_INEXT using OGGCapture API.

2018-09-29 11:17:04  INFO    OGG-02089  Source redo compatibility version is: 11.2.0.4.0.

2018-09-29 11:17:04  INFO    OGG-02086  Integrated Dictionary will be used.

2018-09-29 11:17:08  WARNING OGG-02901  Replication of UDT and ANYDATA from redo logs is not supported with the Oracle compatible parameter setting. Using fetch instead.

2018-09-29 11:17:08  INFO    OGG-01055  Recovery initialization completed for target file /ogg/dirdat/lt000000000, at RBA 1401.

2018-09-29 11:17:08  INFO    OGG-01478  Output file /ogg/dirdat/lt is using format RELEASE 12.3.

2018-09-29 11:17:09  INFO    OGG-01026  Rolling over remote file /ogg/dirdat/lt000000000.

2018-09-29 11:17:09  INFO    OGG-01053  Recovery completed for target file /ogg/dirdat/lt000000001, at RBA 1461.

2018-09-29 11:17:09  INFO    OGG-01057  Recovery completed for all targets.

***********************************************************************
**                     Run Time Messages                             **
***********************************************************************


2018-09-29 11:17:10  INFO    OGG-03522  Setting session time zone to source database time zone 'GMT'.

As I already mentioned that the Integrated Capture is tightly coupled with Oracle Database, there are some in-built database view to monitor the progress of the Integrated capture at the database level. One of them and frequently used is DBA_CAPTURE

SQL> desc DBA_CAPTURE
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 CAPTURE_NAME NOT NULL VARCHAR2(30)
 QUEUE_NAME NOT NULL VARCHAR2(30)
 QUEUE_OWNER NOT NULL VARCHAR2(30)
 RULE_SET_NAME VARCHAR2(30)
 RULE_SET_OWNER VARCHAR2(30)
 CAPTURE_USER VARCHAR2(30)
 START_SCN NUMBER
 STATUS VARCHAR2(8)
 CAPTURED_SCN NUMBER
 APPLIED_SCN NUMBER
 USE_DATABASE_LINK VARCHAR2(3)
 FIRST_SCN NUMBER
 SOURCE_DATABASE VARCHAR2(128)
 SOURCE_DBID NUMBER
 SOURCE_RESETLOGS_SCN NUMBER
 SOURCE_RESETLOGS_TIME NUMBER
 LOGMINER_ID NUMBER
 NEGATIVE_RULE_SET_NAME VARCHAR2(30)
 NEGATIVE_RULE_SET_OWNER VARCHAR2(30)
 MAX_CHECKPOINT_SCN NUMBER
 REQUIRED_CHECKPOINT_SCN NUMBER
 LOGFILE_ASSIGNMENT VARCHAR2(8)
 STATUS_CHANGE_TIME DATE
 ERROR_NUMBER NUMBER
 ERROR_MESSAGE VARCHAR2(4000)
 VERSION VARCHAR2(64)
 CAPTURE_TYPE VARCHAR2(10)
 LAST_ENQUEUED_SCN NUMBER
 CHECKPOINT_RETENTION_TIME NUMBER
 START_TIME TIMESTAMP(6)
 PURPOSE VARCHAR2(19)
 CLIENT_NAME VARCHAR2(4000)
 CLIENT_STATUS VARCHAR2(8)
 OLDEST_SCN NUMBER
 FILTERED_SCN NUMBER

Query this view to check the status and progress of the Integrated Capture as below

SQL> select capture_name, start_scn, first_scn, logminer_id, source_database from dba_capture;

CAPTURE_NAME START_SCN FIRST_SCN LOGMINER_ID SOURCE_DATABASE
------------------------- ---------- ---------- ----------- -------------------------
OGG$CAP_INEXT 4043243 4043243 3 GGDB1

The detailed explanation of the DBA_CAPTURE can be checked from the below link,

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/ALL_CAPTURE.html#GUID-2B5CED0A-7CCB-479E-BFF8-028BD8BFBEFC

Hope you enjoyed the post. Will see about the Integrated Replicat process in my next post.

Cheers 🙂