Preparing the source database for GoldenGate setup

Oracle GoldenGate architecture consists of Extract process in the source database. This process mines the redo information and extracts the changes occurring in the source database objects. These changes are then written to the trail files. There are two types of Extract processes – Classic Capture and Integrated Capture. The Extract process requires some setup to be done in the source database. Some of the steps in the setup are different depending on the type of the Extract process. GoldenGate requires a database user to be created in the source database and various privileges to be granted to this user. This recipe explains how to set up a source database for GoldenGate replication.

Getting ready

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

How to do it…

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.

How it works…

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;

There's more…

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.

The Classic Capture mode

The Classic Capture mode is the traditional Extract process that has been there for a while. In this mode, GoldenGate accesses the database redo logs (also, archive logs for older transactions) to capture the DML changes occurring on the objects specified in the configuration files. For this, at the OS level, the GoldenGate user must be a part of the same database group which owns the database redo logs. If the redo logs of the source database are stored in an ASM diskgroup this capture method reads it from there. This capture mode is available for other RDBMS as well. However, there are some datatypes that are not supported in Classic Capture mode. One of the biggest limitations of the Classic Capture mode is its inability to read data from the compressed tables/tablespaces.

The Integrated Capture mode

In case of the Integrated Capture mode, GoldenGate works directly with the database log mining server to receive the data changes in the form of logical change records (LCRs). An LCR is a message with a specific format that describes a database change. This mode does not require any special setup for the databases using ASM, transparent data encryption, or Oracle RAC. This feature is only available for databases on Version 11.2.0.3 or higher. This Capture mode supports extracting data from source databases using compression. It also supports various object types which were previously not supported by Classic Capture.

Integrated Capture can be configured in an online or downstream mode. In the online mode, the log miner database is configured in the source database itself. In the downstream mode, the log miner database is configured in a separate database which receives archive logs from the source database. This mode offloads the log mining load from the source database and is quite suitable for very busy production databases. If you want to use the Integrated Capture mode with a source database Version 11.2.0.2 or earlier, you must configure the Integrated Capture mode in downstream capture topology, and the downstream mining database must be on Version 11.2.0.3 or higher.

Tip

You will need to apply a Bundle Patch specified in MOS Note 1411356.1 for full support of the datatypes offered by Integrated Capture.

See also

• Refer to the recipe S etting up an Integrated Capture Extract process later in this chapter and Creating an Integrated Capture with a downstream database for compressed tables in Chapter 7, Advanced Administration Tasks – I