Thursday Apr 30, 2015

How To Setup Oracle GoldenGate When Performing the DB2 11.1 Upgrade

After the announcement of DB2 11.1 support in Oracle GoldenGate for DB2 z/OS ,a lot of questions was received on how to setup Oracle GoldenGate when performing the DB2 11.1 upgrade. This blog provides some instructions and explanations. 

DB2 11.1 increases the log record sequence numbers from 6 bytes to 10 bytes. The log reading API changed significantly to support the new log record format.  Oracle GoldenGate provides support for DB2 11.1 with a version specific build.  In other words, starting with Oracle GoldenGate 12.1.2.1.4, two downloadable builds will be provided to support DB2 z/OS: 

  • GoldenGate for DB2 10 and earlier versions
  • GoldenGate for DB2 11
If upgrading to DB2 11.1 in a data sharing configuration and you’ll be upgrading the subsystems in the group gradually (i.e. you’ll have a mixed DB2 11.1 & DB2 v10.1/9.1 group for some period of time), we first recommend that you upgrade the existing GoldenGate being used to the GoldenGate version that you plan to use once you’ve upgraded to DB2 11.1.  At the time of writing this document, the earliest version of GoldenGate that supports DB2 11.1 is 12.1.2.1.4.  

The diagram below depicts the GoldenGate and data sharing configuration prior to upgrading the first subsystem to DB2 11.1.
Picture
Please make sure you are not using the data sharing group name i.e. ADDO in the extract connection parameter. For example if the data sharing group name is ADDO, and the subsystem SSIDs of the group are ADD1 and ADD2..., please use the SSID name instead. When you use the data sharing group name, GoldenGate will connect to any of the subsystems to access log files from all of the subsystems in the data sharing group. However, during the upgrade process, we need to make sure the GoldenGate extract is connected to a specific subsystem of the group that will not be upgraded to DB2 v11.1 initially. For example,

SOURCEDB ADD1 userid uuuuuu, password ppppppp


To quickly modify a GoldenGate extract connection to another subsystem in the data sharing group, it is common practice to use an include file to define the connection parameter.  For example, the following “extract-conn.inc” file denoted in the “INCLUDE” parameter would contain the connection parameter above:

INCLUDE extract-conn.inc

In this example, you can keep the extract connected to ADD1 while upgrading the other members of the data sharing group to DB2 11.1. Data from all members in the data sharing group will be captured by GoldenGate. 
Picture
As soon as you upgrade one member of the data sharing group to DB2 11.1, you can choose to use the new GoldenGate for DB2 z/OS 11 build and connect the extract to that subsystem and capture log records from all the other subsystems in the data sharing group as illustrated below:
Picture
The DB2 IFI allows a GoldenGate extract to access log files for all DB2 subsystems that are a part of the DB2 data sharing group no matter which LPAR these subsystems are running in.  GoldenGate can capture from all members of a data sharing group even if there are different DB2 subsystem versions.  To clarify this further:

  • GoldenGate can connect to a DB2 11.1 subsystem and successfully capture log records from DB2 10.1 subsystem(s) that are also a part of the DB2 data sharing group.
  • In like manner, GoldenGate can also connect to a DB2 10.1 subsystem and successfully capture log records from DB2 11.1 subsystem(s) that are a part of the DB2 data sharing group.

Please refer to KM 1060540.1 if you need more information about the Oracle GoldenGate support for DB2 z/OS data sharing group.

If you have further question or suggestions,  please feel free to reach me at @@jinyu512

(Thanks my colleague Mark Geisler, Richard Johnson and Greg Wood for reviewing this doc.)

Thursday Jan 29, 2015

Oracle GoldenGate 12c for Oracle Database - Integrated Capture sharing capture session

Oracle GoldenGate for Oracle Database has introduced several features in Release 12.1.2.1.0. In this blog post I would like to explain one of the interesting features:  “Integrated Capture (a.k.a. Integrated Extract) sharing capture session”. This feature allows making the creation of additional Integrated Extracts faster by leveraging existing LogMiner dictionaries. As Integrated Extract requires registering the Extract let’s first see what is ‘Registering the Extract’?

REGISTER EXTRACT EAMER DATABASE

The above command registers the Extract process with the database for what is called “Integrated Capture Mode”. In this mode the Extract interacts directly with the database LogMining server to receive data changes in the form of logical change records (LCRs).

When you create Integrated Extract prior to release Oracle GoldenGate 12.1.2.1.0, you might have seen the delay in registering the Extract with database. It is mainly because the creation of Integrated Extract involves dumping the dictionary and then processing that dictionary to populate LogMiner tables for each session, which causes overhead to online systems and hence it requires extra time to startup. The same process is being followed when you create additional Integrated Extract.

What if you could use the existing LogMiner dictionaries to create the additional Integrated Extract? This is what it has been done in this release. Additional Integrated Extract creation can be made faster significantly by leveraging existing LogMiner dictionaries which have been mined already. Hence no more separate copy of the LogMiner dictionaries to be dumped with each Integrated Extract. As a result, it will make the creation of additional Integrated Extracts much faster and helps avoid significant database overhead caused by dumping and processing the dictionary.

In order to use the feature, you should have Oracle DB version 12.1.0.2 or higher, and Oracle GoldenGate for Oracle version 12.1.2.1.0 or higher. The feature is currently supported for non-CDB databases only.

Command Syntax:

REGISTER EXTRACT group_mame DATABASE

..

{SHARE [AUTOMATIC | extract | NONE]}

It has primarily three options to select with; NONE is default if you don’t specify anything.

AUTOMATIC option will clone/share the LogMiner dictionary from the existing closest capture. If no suitable clone candidate is found, then a new LogMiner dictionary is created.

Extract option will clone/share from the capture session associated for the specified Extract. If this is not possible, then an error occurs the register does not complete.

NONE option does not clone or create a new LogMiner dictionary; this is the default.

While you use the feature, the SHARE options should be followed by SCN and specified SCN must be greater than or equal to at least one of the first SCN of existing captures and specified SCN should be less than current SCN.

Let’s see few behaviors prior to 12.1.2.1.0 release and with SHARE options. 'Current SCN’ indicates the current SCN value when register Extract command was executed in following example scenario.

Capture Name

LogMiner ID

First SCN

Start SCN

LogMiner Dictionary ID (LM-DID)

EXT1

1

60000

60000

1

EXT2

2

65000

65000

2

EXT3

3

60000

60000

3

EXT4

4

65000

66000

2

EXT5

5

60000

68000

1

EXT6

6

70000

70000

4

EXT7

7

60000

61000

1

EXT8

8

65000

68000

2

Behavior Prior to 12.1.2.1.0 – No Sharing

Register extract EXT1 with database (current SCN: 60000)

Register extract EXT2 with database (current SCN: 65000)

Register extract EXT3 with database SCN 60000 (current SCN: 65555)

Register extract EXT4 with database SCN 61000   à Error!!

Registration of Integrated Extract EXT1, EXT2 and EXT3 happened successfully where as EXT4 fails because the LogMiner server does not exist at SCN 61000.

Also take a note that all Integrated Extract (EXT1 – EXT3) created dictionaries separately (LogMiner Dictionary IDs are different, now onwards I’ll call them LM-DID).

New behavior with different SHARE options

  • Register extract EXT4 with database SHARE AUTOMATIC (current SCN: 66000)

EXT4 automatically chose the capture session EXT2 as it has Start SCN 65000 which is nearer to current SCN 66000. Hence EXT4 & EXT3 capture sessions would share the same LM-DID 2.

  • Register extract EXT5 with database SHARE EXT1 (current SCN: 68000)

EXT5 is sharing the capture session EXT1. Since EXT1 is up and running, it doesn’t give any error. LM-DID 1 would be shared across EXT 5 and EXT1 capture sessions.

  • Register extract EXT6 with database SHARE NONE (current SCN: 70000)

EXT6 is being registered with SHARE NONE option; hence the new LogMiner dictionary will be created or dumped. Please see LM-DID column for EXT6 in above table. It contains LM-DID value 4.

  • Register extract EXT7 with database SCN 61000 SHARE NONE (current SCN: 70000)

It would generate an error as similar to EXT4 @SCN61000. The LogMiner Server doesn’t exist at SCN 61000 and since the SHARE option is NONE, it won’t share the existing LogMiner dictionaries as well. This is same behavior as prior to 12.1.2.1.0 release.

  • Register extract EXT7 with database SCN 61000 SHARE AUTOMATIC (current SCN: 72000)

EXT7 is sharing the capture session EXT1 as it is the closest for SCN61000. You must have noticed that the EXT7 @SCN61000 scenario has passed with SHARE AUTOMATIC option, which was not the case earlier (EXT4 @61000).

  • Register extract EXT8 with database SCN 68000 SHARE EXT2 (current SCN: 76000)

EXT8 extract is sharing EXT2 capture session. Hence sharing of LogMiner dictionaries happens between EXT8 & EXT2

This feature is not only providing you faster start up for additional Integrated Extract, but also resolves few scenarios which wasn’t possible earlier. If you are using this feature and had questions or comments, please let me know by leaving your comments below. I’ll reply to you as soon as possible.

Wednesday Oct 01, 2014

Streaming Relational Transactions to Flume using Oracle GoldenGate

In prior articles, we have introduced architectures for streaming transactions from Oracle GoldenGate to HDFS, Hive, and HBase. In this article we are adding to this list by presenting a solution for streaming transactions into the Flume service. 

Apache Flume is a distributed, fault tolerant, and available service for efficiently collecting, aggregating, and moving large amounts of streaming data into HDFS. It can be configured into a variety of distribution mechanisms, such as delivery to multiple clusters, or rolling of HDFS files based on time or size. 

As shown in the diagram below, streaming database transactions to Flume is accomplished by developing a custom handler using Oracle GoldenGate's Java API and Flume's Avro RPC APIs.

The custom handler is deployed as an integral part of the Oracle GoldenGate Pump process.   The Pump process and the custom adapter are configured through the Pump parameter file and custom adapter's properties file. The Pump process executes the adapter in its address space. The Pump reads the Trail File created by the Oracle GoldenGate Capture process and passes the transactions to the adapter. The adapter then writes the transactions to a Flume Avro RPC source at the given host/port defined in the configuration file. The Flume Agent streams the data to the final destination; in the supplied example Flume writes into an HDFS directory for subsequent consumption by Hive. 

A sample implementation of the Flume Adapter for Oracle GoldenGate is provided at the My Oracle Support site as Knowledge Base article 1926867.1

Wednesday Aug 20, 2014

GoldenGate 12c - MySQL Active-Active Replication Setup

Oracle GoldenGate supports active-active configurations for DB2 on z/OS, LUW, and IBM i, MySQL, Oracle, SQL/MX,SQL Server, Sybase, and Teradata. However, the setup is different from database to database. In this example, I will show you how to setup a the active-active data replication between two MySQL database instances.[Read More]

Tuesday Jul 15, 2014

Oracle GoldenGate 12c Capture for SQL Server

In Oracle GoldenGate 12c we have expanded the heterogeneity of the product by adding support for the latest versions of the major databases. 

In Oracle GoldenGate 12.1.2.0.1 for SQL Server, which is available for download on My Oracle Support, we offer support for capturing from and delivering to SQL Server 2012. Oracle GoldenGate 12c also supports SQL Server 2008 and SQL Server 2008 R2 Capture and Delivery.

Two other highly anticipated new features for Oracle GoldenGate 12c for SQL Server, are the ability for Capture to read from SQL Server compressed transaction log backups and the ability to restrict the need for Capture to read from transaction log backups, required when customers use 3rd party transaction log backup utilities.

Some of the key capabilities of Oracle GoldenGate for SQL Server are as follows:

  • Oracle GoldenGate now supports Capture from SQL Server 2012 beginning with GoldenGate 12.1.2.0.1.
  • Oracle GoldenGate for SQL Server can be configured to Capture from tables without Primary Keys.
  • Oracle GoldenGate 12c for SQL Server supports SQL Server native backup compression and includes the optional configuration to never have to read from transaction log backups, which enables any log backup utility to be used.
  • Capture process can work in conjunction with SQL Server Transactional Replication, and Change Data Capture.
  • Oracle GoldenGate for SQL Server can provide real-time data to heterogeneous target databases, and receive real-time data from heterogeneous databases.

A complete list of supported features and details on how to implement those features are available in the Oracle GoldenGate 12c Documentation Library, available at the following address: http://docs.oracle.com/goldengate/1212/gg-winux/index.html

I also recommend reading our white paper Using Oracle GoldenGate 12c with SQL Server”  that provides more details on how GoldenGate supports SQL Server databases.

Tuesday Jul 08, 2014

Oracle GoldenGate Veridata Repair is here!

By Joe deBuzna, Director, Oracle Data Integration Product Management

Fix your out of sync data with a single click using the new Oracle GoldenGate Veridata 12.3 heterogeneous data repair feature!

I am so happy to finally be typing that sentence because I get asked about the Veridata Repair feature all the time. So YES, it’s really here! Call me biased, but this is one of the most highly sought after, killer features in the Oracle Data Integration product line, if not all of Oracle Fusion Middleware. And while it’s part of the Oracle GoldenGate product suite, it can compare and fix data between any two databases regardless of how you’re moving the data around: Oracle GoldenGate, Oracle Data Integrator, Oracle Streams, Logical Data Guard, Oracle Advanced Replication, SQL scripts, custom batch jobs, SQL*Loader – it does not matter! As long as Veridata has the right database connection permissions and supports the platform, you’re good to go.

And why does data go out of sync? Let me count the main ones, so we can keep this post short and sweet. The big ones we see include bad source data (e.g. out of bounds characters from wrong NLS settings and bad dates) got inserted or updated, no logging source operations caused data gaps in logical replication products, and bulk data loads/copies into target systems were timed incorrectly (improperly “instantiated” for replication). And here’s the one we don’t like to talk about: we work in extremely complicated IT environments and sometimes people just make mistakes. Sometimes we inherit those mistakes. And it’s ok to have mistakes. But far better if you can quickly identify them and it is absolutely crucial to fix them as soon as possible to avoid domino effects of inconsistent data.

So don’t just hope your data is in sync, prove it and have confidence knowing it. Have confidence when you’re about to pull the switch on your cloud migration or failover; have confidence looking your CEO in the eye telling him that his “single source of truth” still is; have confidence that complex, multi-master conflict detection and resolution setup you designed, or inherited, is actually keeping everything in sync.

You can learn more about the repair feature via a few new resources we have for you below. Take a look and see how you can pick and chose only which tables and specific rows you want to repair; what we did to make this feature fast and secure; how we can suppress target triggers; and more:

You can also watch a half hour video of me talking about and giving a demo of both Oracle GoldenGate Veridata and Monitor 12.1.3 here:

Oracle University Learning Stream video for Oracle GoldenGate Monitor and Oracle GoldenGate Veridata.

For Oracle GoldenGate Veridata 12.1.3 we now include in a single download Oracle GoldenGate Veridata Server, Veridata Java agents, Oracle GoldenGate Monitor Server, and Oracle GoldenGate Monitor agents. This single download can be found on eDelivery.oracle.com and on our Oracle Technology Network page.

And since we’re now based on Oracle Weblogic Server 12.1.3, you’ll need to also download the Oracle Application Development Runtime Infrastructure.

To see which platforms are supported, you can click on the Certifications tab in My Oracle Support, or download the spreadsheet “System Requirements and Supported Platforms for Oracle Fusion Middleware 12c (12.1.3)”.

Finally, please feel free to leave comments and feedback below. For those willing to share, I’m always looking for interesting stories on how you’ve seen data going out of sync and how you ended up fixing it.


Tuesday Jul 01, 2014

New Release for Oracle GoldenGate Monitor is here!

 By Tom Chu, Oracle GoldenGate Product Management

Oracle GoldenGate Management Pack provides extensive enterprise-wide monitoring and management of your Oracle GoldenGate solutions The license include:

  • Oracle Enterprise Manager Plug-in. Provides secure viewing, management, and alerting capabilities for Oracle GoldenGate while leveraging Oracle Enterprise Manager framework 

  • Oracle GoldenGate Monitor. A stand-alone product that provides secure viewing, management, and alerting capabilities for Oracle GoldenGate with end-to-end topology solution displays and customizable topology views

  • Oracle GoldenGate Director. A stand-alone product for basic configuration, management, monitoring, and alerting for legacy Oracle GoldenGate deployments

Oracle GoldenGate Monitor version 12.1.3 was released on June 26th, 2014 and delivers the following new features:

  • Control Oracle GoldenGate processes Capture (Extract) and Delivery (Replicat) by:

    • Initiating "start", "stop", and  "kill" commands
    • Configuration management, and ability to edit configuration files
  • Access report and discard files to understand processing and diagnose problems.

  • Integration with Oracle Web Logic Server 12.1.3 to provide secure single sign on (SSO).

  • New metrics including “Seconds Since Last GoldenGate Checkpoint”, and a class of “delta” metrics that track the change between samples for existing “total count” metrics.

  • Support for monitoring Oracle GoldenGate instances running on IBM DB2 for z/OS

For more information, please refer to Oracle Management Pack for Oracle GoldenGate data sheet and to download please visit Oracle GoldenGate Downloads.

Tuesday Mar 25, 2014

Useful Tips on Oracle GoldenGate 12c Installation, Instantiation, and Setup

Written by Nick Wagner, Director of Product Management, Oracle Database High Availability

This is the first of 3 blog posts that I’m writing that will cover some of the questions I get from customers doing a first time implementation of Oracle GoldenGate. I’m not trying to reinvent the wheel, so to speak, so I’m going to reference a number of different My Oracle Support notes that go into more depth about a particular subject or process.

This first post will be about getting the environment set up and installation of Oracle GoldenGate. Also, for simplicity, this will assume a like to like replication environment as well as one-way replication. Future blogs will cover bi-directional replication and environments where the source and target objects have different structures.

Installation

Installation should be done on direct attached storage unless you are installing Oracle GoldenGate in a cluster or for failover, in that case the use of a dedicated NAS or SAN device is fine. Oracle GoldenGate is a quickly evolving product and we have a standard release and patching process. For example, in Oracle GoldenGate 11.2 and 12.1, it’s typically a 3 week patching cycle. So, always use the most recent version of Oracle GoldenGate so you have the most recent stable release. If you are using Oracle GoldenGate 11.2.1, you can just download the most recent release from My Oracle Support. If you are using Oracle GoldenGate 12.1.2, then you should download the installation from OTN or edelivery.oracle.com, and then download and upgrade to the most recent version from My Oracle Support and use Opatch to patch it to the latest release.

Instantiation

There are numerous articles on Oracle GoldenGate instantiation, the two most common are mentioned below.  Essentially, if you can do an Oracle hot backup that’s the easiest way to establish the target database. If that’s not possible, look at Oracle export/import or data pump, and if that can’t be used, you can use GoldenGate's own instantiation. In a situation where you are going to be doing transformation in the Delivery process (Replicat), then GoldenGate's own instantation, while being the slowest, is probably the best choice, as any transformation done in the Delivery can be applied during the initial load as well. It saves you from having to load the data, then transform it.

For like to like, the first is Oracle GoldenGate Best Practices: Instantiation from an Oracle Source Database [Article ID 1276058.1] this document is very comprehensive and covers Oracle to Oracle as well as heterogeneous options. And the other document: Oracle GoldenGate: Initial Load Techniques and References (Doc ID 1311707.1) can be used as well and has a few examples and would be ideal for targets where transformation is going to be used.

Initial Setup

The initial setup of the production database can be quite involved. However, the most important aspect is ensuring that the source database has the correct supplemental logging enabled. In the case where all (or a majority) of the tables are being replicated, it’s easier to configure supplemental logging at the schema level instead of the table level. Using the GGSCI command ADD SCHEMATRANDATA ensures that as the application changes, and new tables are added or keys changed on a table the underlying supplementally logged columns still adhere to those required by Oracle GoldenGate.

If there are tables without primary keys I would recommend that you review this article Supplemental logging – How to Handle Tables Without Primary Keys or Unique Indexes With Oracle GoldenGate [Article ID 1271578.1] that covers the way Oracle GoldenGate handles these types of tables and why it’s important to handle them correctly. If tables without keys aren’t managed properly, you could get anything from poor performance all the way to data corruption. The default way, may not always be the best way of handling these objects. Especially if there is a unique column that just doesn’t have a unique constraint on it.

Another simple way to reduce maintenance on Oracle GoldenGate is wildcarding. For example, if you have  Capture processes (also called Extract) running as a pump and pulling data from a Trail File, and all the data in the Trail File should be sent to the target, then use TABLE *.*; and that will instruct Capture to send all the data that it reads. That way, if a table is added to the Capture reading from the redo logs, you won’t need to make any changes to the Capture running as a pump is necessary. This same concept can even be used in the Delivery, where ASSUMETARGETDEFS is valid. Just use MAP *.*, TARGET *.*; and any table added to the Capture reading from the transaction logs will be sent all the way down to the target.

And that brings the first blog post to a close. I’ll be happy to answer any questions…

Saturday Dec 28, 2013

GoldenGate 12c - Coordinated Delivery Example

This blog provides an example of setting up a coordinated replicat group using Oracle GoldenGate for MySQL.
[Read More]

Wednesday Dec 11, 2013

GoldenGate 12c - What is Coordinated Delivery?

In Oracle GoldenGate 12c, the parallel apply is improved with two features: Integrated Delivery and Coordinated Delivery. Different from the Integrated Delivery, Coordinated Delivery is not limited to Oracle Database.This blog discusses the new Coordinated Delivery feature by answering two questions:


  • Why do I need Coordinated Delivery?

  • What is Coordinated Delivery?
  • [Read More]

    Monday Jul 29, 2013

    Oracle GoldenGate Patch Release 11.2.1.0.7 is Available

     Author: Nick Wagner, Director of Product Management for Oracle GoldenGate

    Oracle GoldenGate 11.2.1.0.7 cumulative patch release is now generally available and can be downloaded from the My Oracle Support website.  This patch includes support for MySQL NDB Cluster 7.1 and 7.2, as well as Sybase ASE 15.7 support.   There are number of fixes and stabilization enhancements in this release, especially for those using the Oracle GoldenGate Management Pack to monitor GoldenGate. 

    The full list of fixed tickets in each release can be found in the release notes available here.

    Wednesday Feb 27, 2013

    Support for Timestamp in Oracle GoldenGate for Sybase

    This article explains the Sybase timestamp support in Oracle GoldenGate.[Read More]

    Monday Feb 11, 2013

    Setting up a One-way Replication from Sybase to Oracle

    This blog provides an example on how to set up one-way replication from Sybase to Oracle.[Read More]

    Tuesday Feb 05, 2013

    Deeper Look into Integrated Capture for Oracle Database

    With Oracle GoldenGate 11gR2 we announced a new "Integrated Capture" process for the Oracle Database. In our launch webcast back in September'12 our product team presented this feature along with other key features such as intelligent conflict management, advanced encryption, global deployment support and more.  You can watch that webcast on demand here.

    We now have a recorded presentation with more details on this new feature. You can watch the GoldenGate Integrated Capture screencast to get a deeper look into this feature.

    Integrated Capture for Oracle Database is very unique in the industry. This feature enables a deeper integration into the database engine and serves as the foundation for new features GoldenGate for Oracle Database will be adding  in the future.

    As we mentioned in our recent Maximum Availability with Oracle GoldenGate webcast, GoldenGate's Classic Capture is still available for both Oracle Database, as well as other non-Oracle databases GoldenGate supports. Below is an overview of supported Oracle Database versions and features for both Classic and Integrated Capture.

    Also please make sure to check out our new resources on GoldenGate's continuous availability solutions to learn more about Integrated Capture feature and other key new features.

    Friday Feb 01, 2013

    Installing Oracle GoldenGate for Sybase

    Though documented in the Oracle GoldenGate for Sybase Installation Guide, there are still many questions on how to install Oracle GoldenGate for Sybase. In this blog I want to walk you through the setup steps and discuss the tip and tricks in the installation process.

    [Read More]
    About

    Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality

    Search

    Archives
    « July 2015
    SunMonTueWedThuFriSat
       
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
     
           
    Today