Configuring GoldenGate to replicate data from MSSQL Standard Edition (CDC, Change Data Capture)

Introduction

imageOne of GoldenGate 12.2 new features is replication support for MSSQL Standard Edition. In the previous GoldenGate 12.1 release, GoldenGate supported capture of DML only from SQL Server Enterprise Edition, due to the method of enabling Supplemental Logging, which is done via a feature only available to Enterprise Editions of SQL Server. Fortunately, beginning with Oracle GoldenGate 12.2, GoldenGate is able to enable Supplemental Logging for Standard Edition instances via a different mechanism. That mechanism to enable Supplemental Logging is done via certain SQL Server Replication components. These SQL Server Replication components are required to be installed and configured in order to enable Supplemental Logging,

Also it is very interesting that MSSQL doesn’t offer Change Data Capture for MSSQL Standard Edition. So if we have 100 offices with MSSQL and we need to collect only changed data to Data Warehouse then it is required to install MSSQL Enterprise Edition in every office because CDC is only available in MSSQL EE.

Continue reading ‘Configuring GoldenGate to replicate data from MSSQL Standard Edition (CDC, Change Data Capture)’ »

GoldenGate Monitor 12.2: how to change password for internal communications

When install GoldenGate Monitor we create special credentials used by GoldenGate Agent and GoldenGate Monitor to secure communications.

image

Also we set this credentials while configuring GoldenGate Agent using pw_agent_util.sh.

But is not well documented how these credentials are used and how change them. So let fill these gap.

Continue reading ‘GoldenGate Monitor 12.2: how to change password for internal communications’ »

Configuring GoldenGate Monitor 12.2

I already wrote about configuring GoldenGate agent for ODI and Enterprise Manager. Now I would like to talk about configuring it for GoldenGate Monitor. GoldenGate is interesting tool for GoldenGate monitoring. It is more powerful and flexible than GoldenGate plugin for Enterprise Manager but it is still standalone tool not integrated with OEM. So it has its own advantages and disadvantages.

From the technical standpoint configuring GoldenGate Agent for Monitor is a little bit different than configuring GoldenGate Agent for OEM and ODI. Also I see some inaccuracies in documentation and step-by-step configuration is not crystal clear. That’s why I writing this blog post — I would like to have short step-by-step instruction how to configure GoldenGate Agent for Monitor.

So let’s look at GoldenGate monitoring framework architecture

image

We can see

  1. GoldenGate Monitor has its own Server with Repository (which is Oracle Database)
  2. Each GoldenGate instance has Agent (Monitor Agent)
  3. Monitor Server and Monitor agents communicates through JMX (which can also be encrypted by SSL)
  4. Agent communicates with Manager to get information about GoldenGate state

Continue reading ‘Configuring GoldenGate Monitor 12.2’ »

GoldenGate 12.2.0.1 New Features: Metadata Encapsulation

Basics

imageGoldenGate pre-12.2.0.1 doesn’t store table structure in trail files and in some cases it was an issue: when we received trail file — we didn’t know source table structure from the file. We had only record and this record contained field values. But we didn’t know field names. So earlier we chose how to manage this. There are two ways:

— assume that source table is the same as target table (ASSUMETARGETDEFS). In this case first value from trail record was written to first field of target table, the second value to the second field, etc. But this method doesn’t work in general case where source and target tables are different

— extract table definitions from source and gave this definition to replicat (SOURCEDEFS). In this case trail files were parsed and applied according to the static source table definition (stored in file). This method gave us flexibility but increased number of manual work: when source table structure changes — we must regenerate DEF-file or we can lose data consistency.

Now we don’t need ASSUMETARGETDEFS and SOURCEDEFS anymore. GoldenGate automatically transfers definitions in trail files. Moreover it retransfer definition when table structure changes (but retransfer will be triggered only after DML on this table) and also retransfer will be done after switch to new trail file (also we should wait for first DML).

There are two special new record types for table definition transfer: DDR (Database Definition Record) and TDR (Table Definition Record). The first one describes database, the second one is for table structure.

Continue reading ‘GoldenGate 12.2.0.1 New Features: Metadata Encapsulation’ »

Configuring GoldenGate agent for ODI and Enterprise Manager (version 12.2)

Core GoldenGate is software completely written in C/C++. But many Oracle tools are written using Java. For example, Enterprise Manager and GoldenGate Monitor. ETL tool Oracle Data Integrator was also developed using Java. All these tools work with GoldenGate and communications should be secured: there should be authentication, encryption, etc.

Oracle uses special agent to make these communications possible. We call these agent imageGoldenGate Agent (previously it was called JAgent). If will look at GoldenGate 11.2 or 12.1 then we see this agent is embedded: there are folders cfg and dirjar in GoldenGate Home. First folder contains GoldenGate configuration, the second one contains java libraries (jar) for GoldenGate agent execution.

There were some limitation of embedded agent: you couldn’t create multiple agents (for OEM and GG Monitor), you must remove old agent and add new one to make communications with server (Monitor or OEM) more stable. Oracle has removed GoldenGate Monitor from core GoldenGate 12.2 installation to overcome these limitations. So we should install GoldenGate Agent before configuring communications with OEM and ODI. Unfortunately GoldenGate Agent documentation is a little bit poor. So am writing this article to compensate this issue.

Continue reading ‘Configuring GoldenGate agent for ODI and Enterprise Manager (version 12.2)’ »

Настройка репликации GoldenGate из Active Data Guard база данных

imageВ GoldenGate 12.1.2.1 появилась возможность реплицировать данные, подключившись к Active Data Guard (ADG) экземпляру. В отличие от режима ALO этот режим обеспечивает Real-Time репликацию. Плюс ADG дает возможность вообще не создавать подключение к источнику — достаточно подключения к экземпляру ADG.

Давайте посмотрим, как это работает. Сначала нам нужно создать экземпляр ADG.

Continue reading ‘Настройка репликации GoldenGate из Active Data Guard база данных’ »

MySQL quick start — some tricks

imageYesterday I tried to install MySQL 5.7.9 get GoldenGate replication from MySQL to Oracle Database working. There are some steps you need to do before you can start using of MySQL server. Those steps were described many times in blogs and forums but now we should do some different tricks for 5.7.9. Looks like Oracle evolving MySQL very quickly to make it more compatible with Oracle Database. Also there are some more strict policies in security area.

So I will describe steps to make MySQL work. That steps are actual for creating simple test database but can be inadequate for production database. Ok, let’s start.

Continue reading ‘MySQL quick start — some tricks’ »