It's a best practice to create a database user on both the source and target databases for Oracle GoldenGate replication. On the source, this user runs the data capture and maintains the heartbeat table for the Pump process. On the target, the user applies the replicated data, keeps the heartbeat table and the checkpoint tables. For bidirectional replication, you can used EXCLUDEUSER <Oracle_GoldenGate_User> to prevent the data looping.
The users should have their own tablespaces. You can create the SQL script for the privileges configuration and include it in your Oracle GoldenGate installation.
For Oracle GoldenGate capture, if the database is a multitenant container database (CDB), the Oracle GoldenGate user has to be a common user (with name starting with C##). The following is an example user creation in a multitenant database:
-- ----------------------------- -- Create Tablespace for C##GGADMIN -- ----------------------------- CREATE TABLESPACE gg_tbls_targetdb DATAFILE 'gg_tbls_targetdb.dbf' SIZE 100M REUSE AUTOEXTEND ON extent management local uniform size 256k; -- --------------------------- -- Create Default C##GGADMIN user -- --------------------------- create user c##ggadm identified by "" default tablespace gg_tbls_targetdb quota unlimited on gg_tbls_targetdb; grant unlimited tablespace to c##ggadmin; alter user c##ggadm set container_data=all container=current;
-------------------------------- -- Must Have -------------------------------- grant connect, resource to c##ggadm container=ALL; grant create session, alter session to c##ggadm container=ALL; -- Used to enable database logging, start/register extracts grant alter system to c##ggadm container=ALL; grant select_catalog_role to c##ggadmin; grant set container to c##ggadmin container = ALL; grant select any dictionary to c##ggadmin container = ALL; BEGIN dbms_goldengate_auth.grant_admin_privilege ( grantee => 'C##GGADM', privilege_type => 'CAPTURE', grant_select_privileges => TRUE, do_grants => TRUE, container => 'ALL' ); END; -------------------------------- -- Must Have Conditional -------------------------------- -- Used when you need to add trandata. grant alter any table to c##ggadm container=ALL;
The following privileges are not needed for the integrated capture post Oracle Database 11.2.0.4:
- grant select any dictionary
- grant flashback any table
- grant select any table
- grant execute on dbms_flashback
For Oracle GoldenGate integrated delivery, Oracle GoldenGate user doesn't have to be a common user for multi-container databases (CDB). You can create a PDB user shown as follows: and
-- ----------------------------- -- Create Tablespace for GGADMIN -- ----------------------------- CREATE TABLESPACE gg_tbls_targetdb DATAFILE 'gg_tbls_targetdb.dbf' SIZE 100M REUSE AUTOEXTEND ON extent management local uniform size 256k; -- --------------------------- -- Create Default GGADMIN user -- --------------------------- create user ggadm identified by "" default tablespace gg_tbls_targetdb quota unlimited on gg_tbls_targetdb; grant unlimited tablespace to ggadmin;
-------------------------------- -- Must Have -------------------------------- grant connect, resource to ggadm, grant create session, alter session to ggadm; BEGIN dbms_goldengate_auth.grant_admin_privilege ( grantee => 'ggadm', privilege_type => 'APPLY', grant_select_privileges => TRUE, do_grants => TRUE, container => 'ALL' ); END; / -------------------------------- -- Must Have Conditional -------------------------------- -- You can choose a subset of tables if the replication scope is well defined. grant select any table to ggadm; grant insert any table to ggadm; grant update any table to ggadm; grant delete any table to ggadm; -- You need this for creating checkpoint, heatbeat tables and the table creations in the DDL replications. grant CREATE ANY TABLE to ggadm; -- These are DDL privileges for the DDL replication -- You can choose a subset of privileges based on the DDL replication scope grant CREATE ANY CLUSTER to ggadm; grant CREATE ANY INDEXTYPE to ggadm; grant CREATE ANY OPERATOR to ggadm; grant CREATE ANY PROCEDURE to ggadm; grant CREATE ANY SEQUENCE to ggadm; grant CREATE ANY TRIGGER to ggadm; grant CREATE ANY TYPE to ggadm; grant ALTER ANY table to ggadm;
- grant select any dictionary
Answer: This is because you can't connect to a multitenant Oracle database with container user when applying the data to PDBs. Use a PDB user credential on Oracle GoldenGate replicat.
It's a best practice to create a different database user on target databases for different Oracle GoldenGate replications, especially when the replications belong to different business applications. This simplifies the access control of data delivery processes and the management of the heartbeat tables and the checkpoint tables.
For Oracle GoldenGate bi-directional replication, the GoldenGate user needs to have both capture and apply privileges, you can call the DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE() procedure to grant the privilege: â
BEGIN dbms_goldengate_auth.grant_admin_privilege ( grantee => 'C##GGADM', privilege_type => '*', grant_select_privileges => TRUE, do_grants => TRUE, container => 'ALL' ); END; /
- Oracle GoldenGate Documentation (12.2) - â4.1 Assigning Credentials to Oracle GoldenGate
- Using Oracle GoldenGate with Amazon RDS