Preparing the target database for GoldenGate setup

On the target side of the GoldenGate architecture, the collector processes receive the trail files shipped by the Extract/Datapump processes from the source environment. The collector process receives these files and writes them locally on the target server. For each row that gets updated in the source database, the Extract process generates a record and writes it to the trail file. The Replicat process in the target environment reads these trail files and applies the changes to the target database using native SQL calls. To be able to apply these changes to the target tables, GoldenGate requires a database user to be set up in the target database with some privileges on the target objects. The Replicat process also needs to maintain its status in a table in the target database so that it can resume in case of any failures. This recipe explains the steps required to set up a GoldenGate user in the target database.

Getting ready

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.

How to do it…

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;

How it works…

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;

There's more…

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.