Oracle DBA – Tips and Techniques

In one of my earlier posts, I had showed some examples of using the CASE and EVAL column conversion functions available in GoldenGate.

Let us today take a look at two other functions STRCAT and STREXT.

STRCAT basically enables us to concatenate two strings or two character columns.

The STREXT function on the other hand is used to extract a portion of a string.

For example in our source database we have a table MYPHONE with the following structure:

SQL> desc myphone
Name Null? Type
—————————————– ——– —————————-
AREA_CODE

Continue reading Using the GoldgenGate column conversion functions – STRCAT and STREXT

Let us have a look at some GoldenGate column conversion functions – CASE and EVAL

To illustrate this example, we have a source and target table called EMPSAL and the structure of the tables in both databases is slightly different.

In the source table we have the JOB column and in the target database we have a TAX_BRACKET column.

In the first example, we will use CASE function to populate the target database TAX_BRACKET column based on the value for the JOB column in the source database.

In the second example, we will use the EVAL function to populate the target database TAX_BRACKET column based on a particular range of values for the SAL column in the source table.

Remember that since the table structure in source and target are different, we need to run the DEFGEN utility to generate a definitions file which we will then copy to the target GoldenGate

Continue reading Using the GoldenGate column conversion functions – CASE and EVAL

I have recently passed the Oracle GoldenGate 10 Essentials exam which recognizes you as a Oracle GoldenGate 10 Certified Implementation Specialist.

Note that the exam is currently based on GoldenGate 10.4 and not the 11g version of GoldenGate which has been out now for quite some time.

I must point out that while there are a number of ‘brain dumps’ like Pass4Sure and Test King to ‘help’ you pass the exam, I have seen the questions and answers that are provided by them and in most cases I found the answers to be provided to be grossly incorrect and wrong. So beware!

The best preparation for the exam is to go through the following sets of documentation:

Oracle Installation and Setup Guide
Reference Guide
Administration Guide

These and other 10.4 GoldenGate documentation can be obtained online here …

I am now available to provide my services in any GoldenGate implementation project around the world as well

Continue reading Passed the 1Z0-539 Exam – am now an Oracle Certified GoldenGate Implementation Specialist

There is an Oracle Metalink Note (Does Goldengate Support The New Oracle 10g Flashback Feature? [ID 966212.1]) which discusses this aspect, but I thought that I would like to see for myself how does the Extract and Replicat processes behave if we issue any FLASHBACK command on the source database.

The Metalink note states that GoldenGate supports the following Flashback operations:

Flashback Table to SCN
Flashback Table to timestamp
Flashback Table to restore point

and does not support operations like:

Flashback Table to before drop
Flashback database

I have tested both the Flashback Table To Timestamp as well as the Flashback Table To Before Drop operations and find that both seem to be supported by GoldenGate and replication continues inspite of these commands being issued on the source database.

Let us see the examples of the same.

I have an Oracle 11g Goldengate (11.1.1) environment configured with the source and target databases of version 11.2.0.2.

Extract and Replicat processes are running

Continue reading Does GoldenGate support Oracle Flashback?

I have been asked this question several times about using GoldenGate to replicate between source and target Oracle databases located on the same physical server. Do we need two manager processes, or do we need two separate Goldengate environments or what happens if one database is a version 11g database and the other is 10g – do we need to install two versions of GoldenGate and so on and so forth.

To answer these common questions:

1) You do not need two manager processes configured. In fact, I am not sure if we can have multiple manager processes on the same GoldenGate environment.
2) You do not need to install two GoldenGate environments in this particular case– one for source and one for target
3) You can replicate data from 11g to 10g and vice versa as well.
4) Both extract and replicat processes can run on the same host with one manager process for both

Let us test

Continue reading GoldenGate replication with source and target on the same physical host

Some time back I had written a post on GoldenGate column mapping and transformations.

I have been asked the question several times regarding how can we replicate data between tables which are not similar in structure or where the target table has columns which are just a subset of the columns present in the source table.

Let us look at a simple example to see how this can be done using the DEFGEN utility of GoldenGate.

In this example we have a source table MYOBJECTS2 (based on DBA_TABLES) and the target table structure is different as it has only three columns – OWNER, OBJECT_NAME and OBJECT_TYPE as shown below.

The table has 113238 rows which we need to replicate to the target database.

Source table

SQL> desc myobjects2
Name

Continue reading GoldenGate replication using a data definition file and DEFGEN utility

Oracle Goldengate provides a mechanism for capturing the before images of the row before any modifications were made to the row. Something very similar to the undo segment concept of the Oracle database.

This can be done using the GETUPDATEBEFORES parameter in either the Extract or Replicat parameter file which will essentially control whether or not the before images of updated columns are included in the records which are processed by GoldenGate.

We can use this not only for conflict resolution purposes but more importantly by comparing before and after images, we can identify net results of transactions, perform some delta calculations and also to maintain a transaction history.

Let us for example see a case where we are storing some currency exchange data in a table which is naturally subject to changes on a continuous basis because the currency rates naturally fluctuate over time.

We would like to maintain a history of

Continue reading Capturing GoldenGate Before Images Using GETUPDATEBEFORES