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

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

SQL> create user ggadmin identified by ggadmin;

SQL> create role ggtarget_hqapps;

SQL> grant create session, alter session to ggtarget_hqapps;
SQL> grant resource to ggtarget_hqapps;
SQL> grant select any dictionary to ggtarget_hqapps;
SQL> grant select any table to ggtarget_hqapps;
SQL> grant insert, update, delete on <owner>.<table> to ggtarget_hqapps;
SQL> grant create table to ggtarget_hqapps;

SQL> grant ggtarget_hqapps to ggadmin;

For granting to all tables belong to particular user we can use following SQL by login to that user example: HQAPPS. This will produce the GRANT sql statement. We are going to grant everything related to this HQAPPS for GGADMIN.

SQL> select 'grant insert, update, delete on ' || table_name 
     || ' to ggtarget_hqapps;' from tabs;

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. edit GLOBALS parameter, either using vi or call it through ggsci

$ cd $GG_HOME
$ ggsci

GGSCI> edit param GLOBALS

insert following parameter and save the file

checkpointtable GGS_CHECKPOINT