Category: GoldenGate

Oracle GoldenGate 12.2 New Feature – Performance Toolkit Monitor

One of the features which I like most and released in Oracle GoldenGate 12.2 is the Oracle GoldenGate Performance Toolkit Monitor. This is a monitoring tool with Graphical User Interface programmed in JAVA.

The Performance Toolkit Monitor provides new metrics for diagnostics and tuning which includes the below.,:

CPU
I/O
Transactions in flight
Cache memory usage and statistics
Improved capture and apply performance

A simple jar file “OGGPTK.jar” needs to be run in the server to invoke this Performance Monitor tool. This free java program jar file can be downloaded from the below link,

https://java.net/projects/oracledi/downloads/download/GoldenGate/OGGPTK.jar

I have two Virtual Machines installed with Oracle GoldenGate 12.2.x

Virtual Machine 1 - OGGR2-1 - 192.168.0.10 - Manager, Extract and Extract Pump

Virtual Machine 2 - OGGR2-2 - 192.168.0.11 - Manager and Replicat

I had downloaded and copied the OGGPTK.jar file in the J:\OGGPTK.jar, This is my host machine where I had installed Virtual Box and have two Virtual Machines as mentioned above. The two virtual machines are already started and all the Oracle GoldenGate processes like Extract, Pump and Replicat are running currently.

picture1

picture2

Firstly, I need to start the OGGPTK.jar using the java command as below,

picture3

picture4

Oracle GoldenGate Performance Tool Kit window will be opened.

picture5

To monitor the GoldenGate instance, connection configuration must be done as below.,

picture6

picture7

Hostname of the Oracle GoldenGate instance and the Port of the Manager process running in that respective instance must be given as below. Save the configuration.

picture8

picture9

I have already configured the connection for my OGG instances. To start monitoring the Oracle GoldenGate instances go to File–>Connect–>Click the Connection Name

picture10

I have selected my Source OGG instance and the Monitoring windows for that instance will be opened as below. Click on any Process in the Left pane. For example Extract Process,

picture11

The below information can be seen in the OGGPTK window,

Process Performance
Thread Performance
Status and Configuration
Trail Files
Cache Statistics
Database Statistics
Queue Statistics
Inflight Transactions

Let us see what information each tab gives us.,

Thread Performance

picture12

Status and Configuration

picture13

Trail Files

picture14

Cache Statistics

picture15

Database Statistics

picture16

Queue Statistics

picture17

Inflight Transactions

picture18

The same kind of information we can see for the other Oracle GoldenGate processes in the instances too..

Hope this is helpful..!! Your comments and suggestions are always welcome. Thanks for your continuous support 🙂

Oracle GoldenGate 12.2 New Feature – New GoldenGate Extended Metrics

Oracle GoldenGate Extended Metrics provides the real-time insight of the Oracle GoldenGate processes Extract, Extract Pump and Replicat. This can be accessed through the RESTful Web Interface.

The New Metrics provides the below information,

Status and Configuration Information

Process and Thread Level Metrics for Extract, Pump and Replicat

Database Statistics for Extract and Replicat & Network Statistics for Pump

In-flight transactions and queue statistics for Extract

Table statistics for Replicat

This is a fine grained performance monitoring window which can be accessed through the RESTful Web Services as below,

extmetrics5

I have two Virtual Machines installed with Oracle GoldenGate 12.2.x

Virtual Machine 1 - OGGR2-1 - 192.168.0.10 - Manager, Extract and Extract Pump

Virtual Machine 2 - OGGR2-2 - 192.168.0.11 - Manager and Replicat

Below is how the information is displayed.,

General and Status information of the Extract process EXT1

Configuration information of the Extract process

Database information where the Extract Process EXT1 is running

Trail File information of the Extract process EXT1

Position, Lag and Checkpoint information of the Extract process EXT1

Performance information of the Extract process EXT1

extmetrics

extmetrics2

extmetrics3

Similar information can be seen for the Replicat process also which is running in the other virtual machine

extmetrics4

This is an excellent tool to monitor the progress and performance of the Oracle GoldenGate processes.

Oracle GoldenGate Integration with DATAPUMP – DBOPTIONS ENABLE_INSTANTIATION_FILTERING – Oracle GoldenGate 12.2 New Feature

There are many methods to perform the Initial Load before starting the Online Replication or Real Time Replication processes in Oracle GoldenGate. They are as below,

1. Oracle Transportable Tablespaces
2. Oracle Export / Import or Datapump (Expdp / Impdp)
3. RMAN
4. Oracle Warehouse Builder or Some third party tools like ETL etc.,

Oracle GoldenGate offers different types of Initial Loads.

1. File to Replicat
2. File to Database Utility
3. Direct Load
4. Direct Bulk Load.

In this article, we are going to see about Performing Initial Load with Oracle DataPump Utility (Expdp/Impdp). The below are the basic steps to perform the Initial Load using the Oracle DATAPUMP (Expdp/Impdp).

1. Create the necessary tables (only structures) on the Target side.
2. Start the Change Synchronization Capture / Online Extract process.
3. Start the Datapump Process.
4. Check the current scn of the database using the below command,

select current_scn from V$database;

5. Export the table from Source with the following parameter in the EXPDP flashback_scn=.
6. Import the table dump in the Target.
7. Start the Online Replication process on the Target with the below command,

start replicat , aftercsn

So many steps to be followed to do the Initial Load using Expdp/Impdp. Here the SCN plays a vital role to maintain the Data Integrity. From Oracle GoldenGate 12.2, this method of Initial Load using DATAPUMP utility has been made in more simpler way.

From Oracle GoldenGate 12.2, there is a new feature introduced. Below are the parameters,

ADD SCHEMATRANDATA|TRANDATA SCHEMA|SCHEMA.TABLE PREPARECSN

DBOPTIONS ENABLE_INSTANTIATION_FILTERING

You don’t want to note down the SCN and this will be taken care automatically when executing the ADD SCHEMATRANDATA or ADD TRANDATA command with the optional parameter PREPARECSN at the Oracle GoldenGate level.

ADD SCHEMATRANDATA SCHEMA PREPARECSN

ADD TRANDATA SCHEMA.TABLE PREPARECSN

When you use this parameter PREPARECSN when adding the trandata, it automatically prepares the SCN for that particular table or schema and the SCN part is automatically takencare. There is no requirement of noting down the SCN (FLASHBACK_SCN) over here.

WAIT - Wait for any in-flight transactions and prepare table instantiation.
LOCK - Put a lock on the table (to prepare for table instantiation).
NOWAIT - Default behavior, preparing for instantiation is done immediately.
NONE - No instantiation preparation occurs.

This is at the Source Side. At the Target side, you need to add the below parameter to the Replicat process parameter file,

DBOPTIONS ENABLE_INSTANTIATION_FILTERING

This parameter let’s the Replicat process know after which CSN it should start replicating or applying the data to the Target tables. On the target, the data pump import populates the system tables and views with instantiation SCNs using the DBOPTIONS ENABLE_INSTANTIATION_FILTERING parameter to enable table-level instantiation filtering. So the parameter,

1. Filters out DDL and DML records based on each table’s instantiation CSN

2. Eliminates need for HANDLE_COLLISIONS or specification of individual MAP for each imported table with the @FILTER(@GETENV(‘TRANSACTION’,‘CSN’) clause.

Let us see below in detail on how this feature works.

On the Source side, I have created table VEERA.TEST as below,

SQL> create table veera.test as select * from dba_objects;

Table created.
SQL> desc veera.test
Name                Null?    Type
------------------ --------- --------------
OWNER                        VARCHAR2(30)
OBJECT_NAME                  VARCHAR2(128)
SUBOBJECT_NAME               VARCHAR2(30)
OBJECT_ID                    NUMBER
DATA_OBJECT_ID               NUMBER
OBJECT_TYPE                  VARCHAR2(19)
CREATED                      DATE
LAST_DDL_TIME                DATE
TIMESTAMP                    VARCHAR2(19)
STATUS                       VARCHAR2(7)
TEMPORARY                    VARCHAR2(1)
GENERATED                    VARCHAR2(1)
SECONDARY                    VARCHAR2(1)
NAMESPACE                    NUMBER
EDITION_NAME                 VARCHAR2(30)
SQL> select count(1) from veera.test;

COUNT(1)
----------
88803

At the OGG level, I am executing the ADD SCHEMATRANDATA with the PREPARECSN option.

GGSCI (OGGR2-1.localdomain as ggadmin@GGDB1) 4> ADD SCHEMATRANDATA VEERA PREPARECSN

2016-09-30 12:02:18 INFO OGG-01788 SCHEMATRANDATA has been added on schema VEERA.

2016-09-30 12:02:18 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema VEERA.

2016-09-30 12:02:18 INFO OGG-10154 Schema level PREPARECSN set to mode NOWAIT on schema VEERA.
GGSCI (OGGR2-1.localdomain as ggadmin@GGDB1) 5> INFO SCHEMATRANDATA VEERA

2016-09-30 12:02:43 INFO OGG-06480 Schema level supplemental logging, excluding non-validated keys, is enabled on schema VEERA.

2016-09-30 12:02:43 INFO OGG-01980 Schema level supplemental logging is enabled on schema VEERA for all scheduling columns.

2016-09-30 12:02:43 INFO OGG-10462 Schema VEERA have 1 prepared tables for instantiation.

In the above output, you can clearly see the message Schema VEERA has been prepared for instantiation.

Already the Manager process was started and now I have started the Extract and Pump processes.

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

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING DMP1 00:00:00      00:00:07
EXTRACT RUNNING EXT1 00:00:00      00:00:00

Now, let us take an export of the schema VEERA. Here we do not need to mention any SCN (FLASHBACK_SCN) for taking an Export.

[oracle@OGGR2-1 ~]$ expdp directory=DATA_PUMP_DIR dumpfile=test.dmp tables=veera.test

[oracle@OGGR2-1 ~]$ expdp directory=DATA_PUMP_DIR dumpfile=test.dmp tables=veera.test

Export: Release 11.2.0.4.0 - Production on Fri Sep 30 12:11:13 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=test.dmp tables=veera.test
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 21 MB
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "VEERA"."TEST" 8.618 MB 88803 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/oracle/admin/GGDB1/dpdump/test.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Fri Sep 30 12:11:25 2016 elapsed 0 00:00:04

The Export (Expdp) has completed successfully. But during the course of the Expdp, I had inserted more rows in to the table VEERA.TEST and these rows are not captured in the export dump. The count before exporting and the count in the Expdp is same 88803.

SQL> insert into veera.test (select * from dba_objects);

88803 rows created.

SQL> commit;

Commit complete.

SQL> select count(1) from veera.test;

COUNT(1)
----------
177606

Since I had inserted more rows, the current count of the table is 177606.

At the Target side, edit the parameter of the Replicat process parameter file REP1 and add the below parameter,

DBOPTIONS ENABLE_INSTANTIATION_FILTERING
GGSCI (OGGR2-2.localdomain as ggadmin@GGDB2) 13> view params rep1

REPLICAT REP1
USERID ggadmin, PASSWORD oracle
MAP veera.test, TARGET veera.test;
GGSCI (OGGR2-2.localdomain as ggadmin@GGDB2) 15> view params rep1

REPLICAT REP1
USERID ggadmin, PASSWORD oracle
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
MAP veera.test, TARGET veera.test;

Already same table VEERA.TEST has been created in the Target side which you can see below,

SQL> desc veera.test
Name                Null?    Type
------------------ --------- --------------
OWNER                        VARCHAR2(30)
OBJECT_NAME                  VARCHAR2(128)
SUBOBJECT_NAME               VARCHAR2(30)
OBJECT_ID                    NUMBER
DATA_OBJECT_ID               NUMBER
OBJECT_TYPE                  VARCHAR2(19)
CREATED                      DATE
LAST_DDL_TIME                DATE
TIMESTAMP                    VARCHAR2(19)
STATUS                       VARCHAR2(7)
TEMPORARY                    VARCHAR2(1)
GENERATED                    VARCHAR2(1)
SECONDARY                    VARCHAR2(1)
NAMESPACE                    NUMBER
EDITION_NAME                 VARCHAR2(30)

I had moved the Export Dump file to the Target server using the SCP and I am importing the dump using the IMPDP utility as below,

[oracle@OGGR2-2 ~]$ impdp directory=DATA_PUMP_DIR dumpfile=test.dmp full=y

Import: Release 11.2.0.4.0 - Production on Fri Sep 30 12:28:23 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=test.dmp full=y
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "VEERA"."TEST" 8.618 MB 88803 rows
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Fri Sep 30 12:28:36 2016 elapsed 0 00:00:04
SQL> select count(1) from veera.test;

COUNT(1)
----------
88803

So, 88803 rows are imported to the table VEERA.TEST at the Target side. The rows which are inserted during the Export at the source are not come here.

Start the Replicat process REP1.

GGSCI (OGGR2-2.localdomain as ggadmin@GGDB2) 27> start replicat rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting

The Replicat process REP1 has started and it is in RUNNING state.

GGSCI (OGGR2-2.localdomain as ggadmin@GGDB2) 28> info all

Program  Status  Group Lag at Chkpt  Time Since Chkpt

MANAGER  RUNNING
REPLICAT RUNNING REP1  00:00:00      00:00:06

Below is the report file of the Replicat process REP1.

2016-09-30 12:37:34 INFO OGG-02243 Opened trail file /ogg/dirdat/ft000000004 at 2016-09-30 12:37:34.103888.

2016-09-30 12:37:34 INFO OGG-03522 Setting session time zone to source database time zone 'GMT'.

2016-09-30 12:37:34 INFO OGG-03506 The source database character set, as determined from the trail file, is we8mswin1252.
Processed extract process graceful restart record at seq 4, rba 1459.

2016-09-30 12:37:34 INFO OGG-06505 MAP resolved (entry veera.test): MAP "VEERA"."TEST", TARGET veera.test.

2016-09-30 12:37:36 WARNING OGG-06439 No unique key is defined for table TEST. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

2016-09-30 12:37:36 INFO OGG-02756 The definition for table VEERA.TEST is obtained from the trail file.

2016-09-30 12:37:36 INFO OGG-06511 Using following columns in default map by name: OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED,
LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME.

2016-09-30 12:37:36 INFO OGG-10155 Instantiation CSN filtering is enabled on table VEERA.TEST at CSN 2,972,839.

2016-09-30 12:37:36 INFO OGG-06510 Using the following key columns for target table VEERA.TEST: OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME.

The Replicat process REP1 automatically picks up the CSN and starts applying the record after this CSN. This you can clearly see it from the below line in the above report file.,

2016-09-30 12:37:36 INFO OGG-10155 Instantiation CSN filtering is enabled on table VEERA.TEST at CSN 2,972,839.

Check the count of the table VEERA.TEST at the target side and it is in sync now.

SQL> select count(1) from veera.test;

COUNT(1)
----------
177606

Hope the article was much helpful. Will see another new feature of Oracle GoldenGate 12.2 in the next article.

Oracle GoldenGate Studio 12.2.1 – Repository Creation Utility (RCU)

Hope you would have enjoyed installing the Oracle GoldenGate Studio in my last post. So the next step is to create a Repository for Oracle GoldenGate Studio.

A Repository database is needed for the Oracle GoldenGate Studio to store all the information of the architecture and mappings. To create the repository, Oracle GoldenGate studio provides us with REPOSITORY CREATION UTILITY (RCU). We need to run this to create the Repository.

Now, let get in to on How to create the Repository.

  1. As I already mentioned that there is a file “rcu” to be run to create the Repository. This file will be in the below mentioned directory,

Continue reading…

Oracle GoldenGate Studio 12.2.1

A Long time awaited Oracle GoldenGate Studio 12.2.1 got released on 21-Jan-2016. The binaries are available in the below link,

http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

oggs18

What is Oracle GoldenGate Studio?

Oracle GoldenGate Studio is a Graphical based designing tool for Oracle GoldenGate which enables deploying High volumes of data with real time replication by automatically handling table and column mappings. It also has a drag and drop feature of custom mappings. So Oracle GoldenGate Studio allows the new users to Oracle GoldenGate start configuring and replicating data in a ease manner with less effort. No worries about the experienced users, since they are offered with the Advanced Replication Options with Oracle GoldenGate Studio.
Continue reading…

ORACLE GOLDENGATE CHECKPOINTS

Each and every processes in Oracle GoldenGate has it’s own checkpoints to maintain data integrity.

This article explains the different types of checkpoints and the uses of it.

As we know there are multiple processes involved in Oracle GoldenGate replication, the below are the processes which maintains the checkpoint information for each and every operations it does.,

1. Extract or Capture Process
2. Secondary Extract or DataPump Process
3. Replicat Process

EXTRACT PROCESS

This process captures the committed changes from the Redo Log or Archive Log files and writes them to the Trail files called Local Trails. The Extract Process
not only captures the committed changes but also keeps track of all the Open transactions so that it ensures that it does not misses any data or no data loss.
Continue reading…

ORACLE GOLDENGATE INSERTALLRECORDS – To Maintain Transaction History

This parameter is used at the Replicat Side. When using this parameter, it causes Replicat process to insert each and every change that has been made to a record as a new record in the database. So whatever operations are performed in the source table (Insert or Delete or Update), everything will be converted in to the insert statement and will be inserted to the target table. The initial insert and subsequent updates and deletes are maintained as point-in-time snapshots.

There are two major reasons for using the parameter INSERTALLRECORDS,

1. When exception MAP statement is used in the target. The operations which causes error will be inserted in the Exception table. This is called as Error Handling.
Continue reading…

How to start the Oracle GoldenGate Collector Process Manually?

Oracle GoldenGate Server Collector process plays a vital role in the Oracle GoldenGate Replication. But we could not see this process in the INFO ALL command output. Because this process is started internally by the manager process automatically. But we all know that, we can also start the Collector process manually. But How to start it? Searched in google for the document but couldn’t find one. So thought of creating this article.

SERVER COLLECTOR PROCESS

There are two Extract Processes. Primary Extract and Secondary Extract (called DataPump / Extract Pump).The Extract Pump is the process at the Source side which communicates with the Target Manager process and requests manager to open the port for the Server Collector process at the Target Side.
So the Server Collector process will be invoked by the Target Manager process. The Collector process in turn receives the changes from the Extract Pump process and writes the changes to the Remote Trail files.

Continue reading…

COLS and COLSEXCEPT in Oracle GoldenGate

COLS | COLSEXCEPT

These parameters are used to Replicat only specific columns from the Source to Target. Suppose, let us consider a scenario, we have 10 columns in the source and we need only 9 columns to be replicated to the target table. Yes, this is possible using the above parameters.

COLS and COLSEXCEPT controls the columns for which data is captured. It means, only data of specific columns are captured and not all the columns.

This parameters are valid for Extract Process and should. Upon using this parameters, only the specified columns values are captured by the Extract Process and not all column’s values.
Continue reading…

Oracle GoldenGate 12c – Silent Installation

We all know that installation of Oracle GoldenGate 10g and 11g was simple,  just unzipping the software binaries. But we did not have any
Graphical Mode Installation. From Oracle GoldenGate 12c onwards, Oracle has introduced a new mechanism of installation with a most familiar
framework Oracle Universal Installer (OUI). This is the Graphical Mode of Installation (GUI Mode).

Please click here to know how to install Oracle GoldenGate 12c (Graphical Mode – Oracle Universal Installer).

Continue reading…