Prepare GoldenGate Environment

Create an Oracle GoldenGate user that can connect to the source and target databases for transactional data. An Oracle GoldenGate user requires a database user with the following privileges:

Extract (Source Side):
CREATE SESSION
ALTER SESSION
RESOURCE
SELECT ANY DICTIONARY
FLASHBACK ANY TABLE or FLASHBACK ON owner.table
SELECT ANY TABLE or SELECT ON owner.table
EXECUTE ON DBMS_FLASHBACK

Replicat (Target Side):
CREATE SESSION
ALTER SESSION
RESOURCE
SELECT ANY DICTIONARY
SELECT ANY TABLE or SELECT ON owner.table
CREATE TABLE
INSERT, UPDATE, DELETE ON owner.table

At source DB

SQL>create user gguser identified by gguser;
SQL>grant CREATE SESSION, ALTER SESSION, RESOURCE, SELECT ANY DICTIONARY, FLASHBACK ANY TABLE, SELECT ANY TABLE, EXECUTE ON DBMS_FLASHBACK to gguser;

Enable minimal supplemental logging by executing the following command:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;


To ensure that the redo and archive logs contain supplemental log data, switch the logs by executing the following command:


SQL> ALTER SYSTEM SWITCH LOGFILE;

At Target DB


SQL>create user gguser identified by gguser;
SQL>grant CREATE SESSION, ALTER SESSION, RESOURCE, SELECT ANY DICTIONARY, FLASHBACK ANY TABLE, SELECT ANY TABLE, EXECUTE ON DBMS_FLASHBACK to gguser;

you may need to create grant table scripts
select 'grant select, insert, update, delete on '||table_name||' to gguser;' from tabs;