How To Setup Oracle User for Golden Gate Replication at Oracle Source DB

1. Create Golden Gate user in the Oracle Source DB.

SQL> create user ggadmin identified by ggadmin;

SQL> create role ggsource_apps;

SQL> grant create session, alter session to ggsource_apps;
SQL> grant resource to ggsource_apps;
SQL> grant select any dictionary to ggsource_apps;
SQL> grant flashback any table to ggsource_apps;
SQL> grant select any table to ggsource_apps;
SQL> grant execute on dbms_flashback to ggsource_apps;

SQL> grant ggsource_apps to ggadmin;

This is quite not right but we can just grant dba role to ggsource in order to make this work, doing so we may face future audit issue.

SQL> grant dba to ggsource;

2. The minimal supplemental logging must be enabled at the Source DB.

By default, the database logs only those column values that change. Before Oracle GoldenGate can start capturing real-time data, the Oracle database must be set to log the table key values whenever it logs a row change, so they are available in the redo logs. This is required for Oracle GoldenGate so that it can locate the correct row on the target for update and delete operations. This task is accomplished via the ADD TRANDATA GGSCI command.

We can use * for all tables within that schema

GGSCI> DBLOGIN USERID <login id>, PASSWORD <password>
GGSCI> ADD TRANDATA <owner>.<table>
GGSCI> ADD TRANDATA <owner>.*

What is this:
Supplemental Logging

Redo log files are generally used for instance recovery and media recovery. The data needed for such operations is automatically recorded in the redo log files. However, a redo-based application (Golden Gate) may require that additional columns be logged in the redo log files. The process of logging these additional columns is called supplemental logging.

By default, Oracle Database does not provide any supplemental logging, which means that by default LogMiner is not usable. Therefore, you must enable at least minimal supplemental logging before generating log files which will be analyzed by LogMiner.

SQL> alter database add supplemental log data;

Database altered.

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
YES

SQL> alter system switch logfile;

System altered.