Oracle Goldengate 11g Complete Cookbook

In order to install Oracle GoldenGate, we must have downloaded the binaries from the Oracle Technology Network website for your Linux platform. We have downloaded Oracle GoldenGate Version 11.2.0.1.0.1 in this recipe. Ensure that you check the checksum of the file once you have downloaded it.

Oracle GoldenGate binaries are installed in a directory called GoldenGate Home. This directory should be owned by the OS user (ggate) which will be the owner of GoldenGate binaries. This user must be a member of the dba group. After you have downloaded the binaries, you need to uncompress the media pack file by using the unzip utility as given in the following steps:

Oracle provides GoldenGate binaries in a compressed format. In order to install the binaries you unzip the compressed file, and then expand the archive file into a required directory. This unpacks all the binaries. However, GoldenGate also requires some important subdirectories under GoldenGate Home which are not created by default. These directories are created using the CREATE SUBDIRS command. The following is the list of the subdirectories that get created with this command:

In order to install Oracle GoldenGate, we must have downloaded the binaries from the Oracle Technology Network website for your Windows platform. We have downloaded GoldenGate Version 11.2.0.1.0.1 in this recipe. Ensure that you check the checksum of the file once you have downloaded it.

Oracle GoldenGate binaries are installed in a directory called GoldenGate Home. After you have downloaded the binaries, you need to uncompress the media pack file by using the unzip utility:

Oracle provides GoldenGate binaries in a compressed format. The installation involves unzipping the file into a required directory. This unpacks all the binaries. However, GoldenGate also requires some important subdirectories under GoldenGate Home which are not created by default. These directories are created using the CREATE SUBDIRS command. The following is the list of the subdirectories that get created with this command:

We must have a list of the tables that we want to replicate between two environments.

Oracle GoldenGate requires supplemental logging to be enabled at the database level and table level. Use the following steps to enable the required supplemental logging:

Supplemental logging enables the database to add extra columns in the redo data that is required by GoldenGate to correctly identify the rows in the target database. We must enable database-level minimum supplemental logging before we can enable it at the table level. When we enable it at the table level, a supplemental log group is created for the table that consists of the columns on which supplemental logging is enabled. The columns which form a part of this group are decided based on the key constraints present on the table. These columns are decided based on the following priority order:

GoldenGate only considers unique keys which don't have any virtual columns, any user-defined types, or any function-based columns. We can also manually specify which columns we want to be a part of the supplemental log group.

GGSCI> ADD TRANDATA <SCHEMA>.*

There are two ways to enable supplemental logging. The first method is to enable it using GGSCI, using the ADD TRANDATA command. The second method is to use sqlplus and run the ALTER TABLE ADD SUPPLEMENTAL LOG DATA command. The latter method is more flexible and allows a person to specify the name of the supplemental log group. However, when you use Oracle GoldenGate to add supplemental logging it creates supplemental log group names using the format, GGS_<TABLE_NAME>_<OBJECT_NUMBER>. If the overall supplemental log group name is longer than 30 characters, GoldenGate truncates the table name as required. Oracle support recommends that we use the first method for enabling supplemental logging for objects to be replicated using Oracle GoldenGate. The GGS_* supplemental log group format enables GoldenGate to quickly identify the supplemental log groups in the database.

If you are planning to use GoldenGate to capture all transactions in the source database and convert them into INSERT for the target database, for example, for reporting/auditing purposes, you'll need to enable supplemental logging on all columns of the source database tables.

You should have identified the various datatypes of the objects that you plan to replicate.

The following is a high-level list of the datatypes that are supported by Oracle GoldenGate v11.2.1.0.1:

There are some additional details that one needs to consider while evaluating the supported datatypes for a GoldenGate version. For example, the user-defined datatypes are only supported if the source and target tables have the same structures. Both Classic and Integrated Capture modes support XML types which are stored as XML, CLOB, and XML binary. However, XML type tables stored as Object Relational are only supported in Integrated Capture mode.

The support restrictions apply to a few other factors apart from the datatypes. Some of these are as Manipulating Data:

You must select a database user ID for the source database setup. For example, GGATE_ADMIN.

Run the following steps in the source database to set up the GoldenGate user as follows:

sqlplus sys/**** as sysdba
CREATE USER GGATE_ADMIN identified by GGATE_ADMIN;
GRANT CREATE SESSION, ALTER SESSION to GGATE_ADMIN;
GRANT ALTER SYSTEM TO GGATE_ADMIN;
GRANT CONNECT, RESOURCE to GGATE_ADMIN;
GRANT SELECT ANY DICTIONARY to GGATE_ADMIN;
GRANT FLASHBACK ANY TABLE to GGATE_ADMIN;
GRANT SELECT ANY TABLE TO GGATE_ADMIN;
GRANT SELECT ON DBA_CLUSTERS TO GGATE_ADMIN;
GRANT EXECUTE ON DBMS_FLASHBACK TO GGATE_ADMIN;
GRANT SELECT ANY TRANSACTION To GGATE_ADMIN;

The following steps are only required for Integrated Capture Extract (Version 11.2.0.2 or higher):

EXEC DBMS_GoldenGate_AUTH.GRANT_ADMIN_PRIVILEGE('GGATE_ADMIN');
GRANT SELECT ON SYS.V_$DATABASE TO GGATE_ADMIN;

The following steps are only required for Integrated Capture Extract (Version 11.2.0.1 or earlier):

EXEC DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('GGATE_ADMIN');
GRANT BECOME USER TO GGATE_ADMIN;
GRANT SELECT ON SYS.V_$DATABASE TO GGATE_ADMIN;

Set up a TNS Entry for the source database in $ORACLE_HOME/network/admin/tnsnames.ora.

The preceding commands can be used to set up the GoldenGate user in the source database. The Integrated Capture required some additional privileges as it needs to interact with the database log mining server.

You will notice that in the previous commands, we have granted SELECT ANY TABLE to the GGATE_ADMIN user. In production environments, where least required privileges policies are followed, it is quite unlikely that such a setup would be approved by the compliance team. In such cases, instead of granting this privilege, you can grant the SELECT privilege on individual tables that are a part of the source replication configuration. You can use dynamic SQL to generate such commands.

In our example schema database, we can generate the commands for all tables owned by the user SCOTT as follows:

select 'GRANT SELECT ON '||owner||'.'||table_name||' to GGATE_ADMIN;' COMMAND from dba_tables where owner='SCOTT'
COMMAND
------------------------------------------------------------------
GRANT SELECT ON SCOTT.DEPT to GGATE_ADMIN;
GRANT SELECT ON SCOTT.EMP to GGATE_ADMIN;
GRANT SELECT ON SCOTT.BONUS to GGATE_ADMIN;
GRANT SELECT ON SCOTT.SALGRADE to GGATE_ADMIN;

In this recipe we saw the steps required to set up a the GoldenGate user in the database. The Extract process required various privileges to be able to mine the changes from the redo data. At this stage it's worth discussing the two types of Extract processes and the differences between both.

You must select a database user ID for a target database setup. For example, GGATE_ADMIN, because the GoldenGate user also requires a table in the target database to maintain its status. It needs some quota assigned on a tablespace to be able to create a table. You might want to create a separate tablespace, grant quota and assign it as default for the GGATE_ADMIN user. We will assign a GGATE_ADMIN_DAT tablespace to the GGATE_ADMIN user in this recipe.

Run the following steps in the target database to set up a GoldenGate user:

sqlplus sys/**** as sysdba
CREATE USER GGATE_ADMIN identified by GGATE_ADMIN DEFAULT TABLESPACE GGATE_ADMIN_DAT;
ALTER USER GGATE_ADMIN QUOTA UNLIMITED ON GGATE_ADMIN_DAT;
GRANT CREATE SESSION, ALTER SESSION to GGATE_ADMIN;
GRANT CONNECT, RESOURCE to GGATE_ADMIN;
GRANT SELECT ANY DICTIONARY to GGATE_ADMIN;
GRANT SELECT ANY TABLE TO GGATE_ADMIN;
GRANT INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE TO GGATE_ADMIN;
GRANT CREATE TABLE TO GGATE_ADMIN;

You can use these commands to set up a GoldenGate user in the target database. The GoldenGate user in the target database requires access to the database plus update/insert/delete privileges on the target tables to apply the changes. In the preceding commands, we have granted SELECT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE, and INSERT ANY TABLE privileges to the GGATE_ADMIN user. However, if for production database reasons your organization follows the least required privileges policy, you will need to grant these privileges on the replicated target tables individually. If the number of replicated target tables is large, you can use dynamic SQL to generate such commands. In our example demo database, we can generate these commands for the SCOTT schema objects as follows:

select 'GRANT SELECT, INSERT, UPDATE, DELETE ON '||owner||'.'||table_name||' to GGATE_ADMIN;' COMMAND from dba_tables where owner='SCOTT'
COMMAND
------------------------------------------------------------------
GRANT SELECT, INSERT, UPDATE, DELETE ON SCOTT.DEPT to GGATE_ADMIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCOTT.EMP to GGATE_ADMIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCOTT.SALGRADE to GGATE_ADMIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCOTT.BONUS to GGATE_ADMIN;

The replicated changes are applied to the target database on a row-by-row basis. The Replicat process needs to maintain its status so that it can be resumed in case of failure. The checkpoints can be maintained in a database table or in a file on disk. The best practice is to create a Checkpoint table and use it to maintain the replicat status. This also enhances the performance as the replicat applies the changes to the database using asynchronous COMMIT with the NOWAIT option. If you do not use a Checkpoint table, the replicat maintains the checkpoint in a file and applies the changes to the databases using a synchronous COMMIT with the WAIT option.

Before setting up a Manager process, you must have installed GoldenGate binaries. A Manager process requires a port number to be defined in its configuration. Ensure that you have chosen the port to be used for the GoldenGate manager instance that you are going to set up.

In order to configure a Manager process, you need to create a configuration file. The following are the steps to create a parameter file for the Manager process:

All GoldenGate processes use a parameter file for configuration. In these files various parameters are defined. These parameters control the way the process functions. The steps to create the Manager process are broadly described as follows:

The Manager process performs the following administrative and resource management functions:

The preceding parameters specified are defined as follows:

The Manager process can be configured to perform some more administrative tasks. The following are some other key parameters that can be added to the Manager process configuration:

Before adding the Classic Capture Extract process, ensure that you have completed the following steps in the source database environment:

The following are the steps to configure a Classic Capture Extract process in the source database:

In the preceding steps we have configured a Classic Capture Extract process to replicate all tables for a SCOTT user. For this we first configure an Extract process parameter file and add the configuration parameter to it. Once the parameter file is created, we then add the Extract process to the source manager instance. This is done using the ADD EXTRACT command in step 5. In step 6, we associate a local trail file with the Extract process and then we start it. When you start the Extract process you will see the following output:

GGSCI (prim1-ol6-112.localdomain) 11> start extract EGGTEST1
Sending START request to MANAGER ...
EXTRACT EGGTEST1 starting

You can check the status of the Extract process using the following command:

GGSCI (prim1-ol6-112.localdomain) 10> status extract EGGTEST1
EXTRACT EGGTEST1: STARTED

There are a few additional parameters that can be specified in the extract configuration as follows:

If your source database ie this parameter to enable extract to build the metadata for each table when the exs a very busy OLTP production system and you cannot afford to add additional load of GoldenGate process on it, you can however offload GoldenGate processing to another server by adding some extra configuration. You will need to configure the source database to ship the redo logs to a standby site and set up a GoldenGate manager instance on that server. The Extract processes will be configured to read from the archived logs on the standby system. For this you specify an additional parameter as follows:

TRANLOGOPTIONS ARCHIVEDLOGONLY ALTARCHIVEDLOGDEST <path>

Before adding the Integrated Capture Extract, ensure that you have completed the following steps in the source database environment:

You can follow the given steps to configure an Integrated Capture Extract process:

The steps for configuring an Integrated Capture process are broadly the same as the ones for the Classic Capture process. We first create a parameter file in steps 1 to 4. In step 5, we add the extract to the GoldenGate instance. In step 6, we add a local extract trail file and in the next step we start the Extract process.

When you start the Extract process you will see the following output:

GGSCI (prim1-ol6-112.localdomain) 11> start extract EGGTEST1
Sending START request to MANAGER ...
EXTRACT EGGTEST1 starting

You can check the status of the Extract process using the following command:

GGSCI (prim1-ol6-112.localdomain) 10> status extract EGGTEST1
EXTRACT EGGTEST1: RUNNING

As described earlier, an Integrated Capture process can be configured with the mining dictionary in the source database or in a separate database called a downstream mining database. When you configure the Integrated Capture Extract process in the downstream mining database mode, you need to specify the following parameter in the extract configuration file:

TRANLOGOPTIONS MININGUSER OGGMIN@MININGDB, MININGPASSWORD *****

You will also need to connect to MININGDB using MININGUSER before registering the Extract process:

MININGDBLOGIN USERID <MININGUSER>@MININGDB, PASSWORD ******

This mining user has to be set up in the same way as the GoldenGate Admin user is set up in the source database.

Some additional parameters that should be added to the extract configuration are as follows:

Before adding the Datapump extract, you must have a manager instance running. You should have added the main extract and a local trail location to the instance configuration. You will also need the target environment details, for example, hostname, manager port no., and the remote trail file location.

Just like other GoldenGate processes, the Datapump process requires creating a parameter file with some parameters. The following are the steps to configure a Datapump process in a GoldenGate source environment:

Once you have added the parameters to the Datapump parameter file and saved it, you need to add the process to the GoldenGate instance. This is done using the ADD EXTRACT command in step 5. In step 6,, we associate a remote trail with the Datapump process and in step 7 we start the Datapump process. When you start the Datapump process you will see the following output:

GGSCI (prim1-ol6-112.localdomain) 10> start extract PGGTEST1
Sending START request to MANAGER ...
EXTRACT PGGTEST1 starting

You can check the status of the Datapump process using the following command:

GGSCI (prim1-ol6-112.localdomain) 10> status extract PGGTEST1
EXTRACT PGGTEST1: RUNNING

The following are some additional parameters/options that can be specified in the datapump configuration:

Before setting up replicat in the target system, you must have configured and started the Manager process.

Follow the following steps to configure a replicat in the target environment:

In the preceding procedure we first create a Checkpoint table in the target database. As the name suggests, the Replicat process uses this table to maintain its checkpoints. In case the Replicat process crashes and it is restarted, it can read this Checkpoint table and start applying the changes from the point where it left.

Once you have added a Checkpoint table, you need to create a parameter file for the Replicat process. Once the process parameter file is created, it is then added to the GoldenGate instance. At this point, we are ready to start the Replicat process and apply the changes to the target database. You should see an output similar to the following:

GGSCI (stdby1-ol6-112.localdomain) 10> start replicat RGGTEST1
Sending START request to MANAGER ...
REPLICAT RGGTEST1 starting

You can check the status of the Replicat process using the following command:

GGSCI (stdby1-ol6-112.localdomain) 10> status replicat RGGTEST1
REPLICAT RGGTEST1: RUNNING

Following are the common parameters that are specified in the replicat configuration: