Required Goldengate User Privileges

For the configuration of a Goldengate set up we need to have a Goldengate user with some specific privileges ( as in production ENV we can’t provide DBA role to Goldengate user).

Below are the required privileges for any Goldengate User in oracle Database.

1. System changes:
alter system set STREAMS_POOL_SIZE=1024m scope=both;
alter system set ENABLE_GOLDENGATE_REPLICATION=true scope=both;
alter system set UNDO_RETENTION=86400 scope=both;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
alter database force logging;

2.User creation:
create tablespace gg_data datafile ‘/u01/app/oracle/oradata/orcl/gg_data01.dbf’ size 1000m autoextend on next 10m;

create user GGSADMIN identified by GGSADMINdefault tablespace gg_datatemporary tablespace temp;

3.User Privileges:

GG user privileges at source and target DB

grant connect,resource to GGSADMIN;
grant create session to GGSADMIN;
grant select any dictionary, select any table,SELECT ANY TRANSACTION to GGSADMIN;
grant create table to GGSADMIN;
grant alter any table to GGSADMIN;
grant alter system to GGSADMIN;
grant flashback any table to GGSADMIN;
grant execute on dbms_flashback to GGSADMIN;
grant execute on utl_file to GGSADMIN;
grant insert any table,update any table,delete any table,lock any table to GGSADMIN;
grant insert on system.logmnr_restart_ckpt$ to GGSADMIN;
grant update on sys.streams$_capture_process to GGSADMIN;
grant alter any sequence to GGSADMIN;

EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(‘GGSADMIN’,’*’,TRUE);