February « 2010 « Oracle DBA – Tips and Techniques

Oracle GoldenGate Tutorial 9 – Monitoring GoldenGate

The following tutorial will briefly discuss the different commands we can use to monitor the GoldenGate environment and get statistics and reports on various extract and replicat operations which are in progress.

More details can be obtained from Chapter 19 of the Oracle GoldenGate Windows and Unix Administration guide – Monitoring GoldenGate processing.

Information on all GoldenGate processes running on a system

GGSCI (devu007) 21> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING DPUMP 00:00:00 00:00:04
EXTRACT RUNNING EXT1 00:00:00 00:00:09
EXTRACT

Continue reading Oracle GoldenGate Tutorial 9 – Monitoring GoldenGate

Oracle Goldengate Tutorial 8 – Filtering and Mapping data

Oracle GoldenGate not only provides us a replication solution that is Oracle version independent as well as platform independent, but we can also use it to do data transformation and data manipulation between the source and the target.

So we can use GoldenGate when the source and database database differ in table structure as well as an ETL tool in a Datawarehouse type environment.

We will discuss below two examples to demonstrate this feature – column mapping and filtering of data.

In example 1, we will filter the records that are extracted on the source and applied on the target – only rows where the JOB column value equals ‘MANAGER” in the MYEMP table will be considered for extraction.

In example 2, we will deal with a case where the table structure is different between the source database and the target database and see how column mapping is performed in such cases.

Example

Continue reading Oracle Goldengate Tutorial 8 – Filtering and Mapping data

Oracle GoldenGate Tutorial 7 – configuring DDL synchronization

In addition to providing replication support for all DML statements, we can also configure the GoldenGate environment to provide DDL support as well.

A number of prerequisite setup tasks need to be performed which we willl highlight here.

Run the following scripts from the directory where the GoldenGate software was installed.

The assumption here is that the database user GGS_OWNER has already been created and granted the required roles and privileges as discussed in our earlier tutorial.

Note – run the scripts as SYSDBA

SQL> @marker_setup

Marker setup script

You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:GGS_OWNER

Marker setup table script complete, running verification script…
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGS_OWNER

MARKER TABLE
——————————-
OK

MARKER SEQUENCE
——————————-
OK

Script complete.

SQL> alter session set recyclebin=OFF;
Session altered.

SQL> @ddl_setup

GoldenGate DDL

Continue reading Oracle GoldenGate Tutorial 7 – configuring DDL synchronization

Oracle GoldenGate Tutorial 6 – configuring Data Pump process

The Data Pump (not to be confused with the Oracle Export Import Data Pump) is an optional secondary Extract group that is created on the source system. When Data Pump is not used, the Extract process writes to a remote trail that is located on the target system using TCP/IP. When Data Pump is configured, the Extract process writes to a local trail and from here Data Pump will read the trail and write the data over the network to the remote trail located on the target system.

The advantages of this can be seen as it protects against a network failure as in the absence of a storage device on the local system, the Extract process writes data into memory before the same is sent over the network. Any failures in the network could then cause the Extract process to abort (abend). Also if we are doing any complex

Continue reading Oracle GoldenGate Tutorial 6 – configuring Data Pump process

Oracle GoldenGate Tutorial 5 – configuring online change synchronization

In our earlier tutorial, we examined how to create a GoldenGate environment for initial data capture and load.

In this tutorial, we will see how by using GoldenGate change synchronization, changes that occur on the source (Oracle 11g on Linux) are applied near real time on the target (Oracle 11g on AIX). The table on the source is the EMP table in SCOTT schema which is being replicated to the EMP table in the target database SH schema.

These are the steps that we will take:

Create a GoldenGate Checkpoint table
Create an Extract group
Create a parameter file for the online Extract group
Create a Trail
Create a Replicat group
Create a parameter file for the online Replicat group

Create the GoldenGate Checkpoint table

GoldenGate maintains its own Checkpoints which is a known position in the trail file from where the Replicat process will start processing after any kind of error or shutdown. This ensures data integrity and a

Continue reading Oracle GoldenGate Tutorial 5 – configuring online change synchronization

Oracle GoldenGate Tutorial 4 – performing initial data load

This example illustrates using the GoldenGate direct load method to extract records from an Oracle 11g database on Red Hat Linux platform and load the same into an Oracle 11g target database on an AIX platform.

The table PRODUCTS in the SH schema on the source has 72 rows and on the target database the same table is present only in structure without any data. We will be loading the 72 rows in this example from the source database to the target database using GoldenGate Direct Load method.

On Source

1) Create the Initial data extract process ‘load1′

GGSCI (redhat346.localdomain) 5> ADD EXTRACT load1, SOURCEISTABLE
EXTRACT added.

Since this is a one time data extract task, the source of the data is not the transaction log files of the RDBMS (in this case the online and archive redo log files) but the table data itself, that is why the keyword SOURCEISTABLE is used.

2) Create the parameter

Continue reading Oracle GoldenGate Tutorial 4 – performing initial data load

GoldenGate Tutorial 3 – Configuring the Manager process

The Oracle GoldenGate Manager performs a number of functions like starting the other GoldenGate processes, trail log file management and reporting.

The Manager process needs to be configured on both the source as well as target systems and configuration is carried out via a parameter file just as in the case of the other GoldenGate processes like Extract and Replicat.

After installation of the software, we launch the GoldenGate Software Command Interface (GGSCI) and issue the following command to edit the Manager parameter file

EDIT PARAMS MGR

The only mandatory parameter that we need to specify is the PORT which defines the port on the local system where the manager process is running. The default port is 7809 and we can either specify the default port or some other port provided the port is available and not restricted in any way.

Some other recommended optional parameters are AUTOSTART which which automatically start the Extract

Continue reading GoldenGate Tutorial 3 – Configuring the Manager process

GoldenGate Tutorial 2 – Installation (Oracle 11g on Linux)

This example will illustrate the installation of Oracle GoldenGate on an RHEL 5 platform. We had in an earlier post discussed the architecture and various components of a GoldenGate environment.

GoldenGate software is also available on OTN but for our platform we need to download the required software from the Oracle E-Delivery web site.

Select the Product Pack “Oracle Fusion Middleware” and the platform Linux X86-64.

Then select “Oracle GoldenGate on Oracle Media Pack for Linux x86-64″ and since we are installing this for an Oracle 11g database, we download “Oracle GoldenGate V10.4.0.x for Oracle 11g 64bit on RedHat 5.0″

$ unzip V18159-01.zip
Archive: V18159-01.zip
inflating: ggs_redhatAS50_x64_ora11g_64bit_v10.4.0.19_002.tar

$tar -xvof ggs_redhatAS50_x64_ora11g_64bit_v10.4.0.19_002.tar

$ export PATH=$PATH:/u01/oracle/ggs

$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/oracle/ggs

$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Linux, x64, 64bit (optimized), Oracle 11 on Sep 17 2009 23:51:28

GGSCI (redhat346.localdomain) 1>

GGSCI (redhat346.localdomain) 1> CREATE SUBDIRS

Creating subdirectories under current

Continue reading GoldenGate Tutorial 2 – Installation (Oracle 11g on Linux)

GoldenGate Tutorial 1 – Concepts and Architecture

GoldenGate enables us to extract and replicate data across a variety of topologies as shown the diagram below as well as the exchange and manipulation of data at the transactional level between a variety of database platforms like Oracle, DB2, SQL Server, Ingres, MySQL etc.

It can support a number of different business requirements like:

Business Continuity and High Availablity
Data migrations and upgrades
Decision Support Systems and Data Warehousing
Data integration and consolidation

Let us know look at the differents components and processes that make up a typical GoldenGate configuration on Oracle.

(source: Oracle GoldenGate Administration Guide)

Manager

The Manager process must be running on both the source as well as target systems before the Extract or Replicat process can be started and performs a number of functions including monitoring and starting other GoldenGate processes, managing the trail files and also reporting.

Extract

The Extract process runs on the source system and is the data caoture mechanism of

Continue reading GoldenGate Tutorial 1 – Concepts and Architecture

Oracle GoldenGate – an introduction

Oracle Corporation at the end of last year has acquired GoldenGate Software Inc which is one of the leading providers of real time data integration solutions. In the words of Hasan Rizvi, Senior Vice President, Oracle Fusion Middleware Product Development ….

“GoldenGate also enables high availability and disaster tolerance with core functionality that supports real-time data integration, while preserving performance and ensuring scalability. With this capability, business-critical applications such as online banking, claims processing and online shopping continue to operate without any disruption. GoldenGate’s solutions address all types of outages as a result of upgrades, maintenance, application of patches, and application or platform migrations.

The combination of GoldenGate and Oracle is expected to provide customers with a comprehensive platform for real-time data integration that delivers continuous data-change and replication functionality across heterogeneous environments”

The product is now available for download from the OTN site as well as via the Oracle E-Delivery.com web site

Continue reading Oracle GoldenGate – an introduction

Oracle DBA – Tips and Techniques

That’s Me

Feedback

Categories

Archives

Recent Posts

Administration