Oracle Cloud Infrastructure Documentation

Set up Oracle GoldenGate for online migrations

To use your own GoldenGate instance, Oracle Cloud Infrastructure Database Migration service has a few additional prerequisite tasks, create GoldenGate users on the source database and unlock the GoldenGate user on the target database (optional).

Note

You can have a single user for database connection, if you have the required privileges. For the source database, the user for CDB and PDBs has all the privileges for GoldenGate and Data Pump.

Create GoldenGate Users on the Source Database

On the source database, you must create a GoldenGate administration user, for example ggadmin.

If the source database is multitenant, create the user in the PDB, and also create a different user in the CDB root, for example c##ggadmin.

To create ggadmin, connect to the PDB and run the following commands:
CREATE TABLESPACE gg_admin DATAFILE '+DATA/ggadmin_data.dbf' SIZE 100m AUTOEXTEND ON NEXT 100m;
CREATE USER ggadmin IDENTIFIED BY ggadmin_pwd DEFAULT TABLESPACE gg_admin TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON gg_admin;
GRANT CONNECT TO ggadmin;
GRANT RESOURCE TO ggadmin;
GRANT CREATE TO ggadmin;
GRANT SELECT_CATALOG_ROLE TO ggadmin;
GRANT DV_GOLDENGATE_ADMIN TO ggadmin;
GRANT DV_GOLDENGATE_REDO_ACCESS TO ggadmin;
GRANT ALTER SYSTEM TO ggadmin;
GRANT ALTER USER TO ggadmin;
GRANT DATAPUMP_EXP_FULL_DATABASE TO ggadmin;
GRANT DATAPUMP_IMP_FULL_DATABASE TO ggadmin;
GRANT SELECT ANY DICTIONARY TO ggadmin;
GRANT SELECT ANY TRANSACTION TO ggadmin;
GRANT INSERT ANY TABLE TO ggadmin;
GRANT UPDATE ANY TABLE TO ggadmin;
GRANT DELETE ANY TABLE TO ggadmin;
GRANT LOCK ANY TABLE TO ggadmin;
GRANT CREATE ANY TABLE TO ggadmin;
GRANT CREATE ANY INDEX TO ggadmin;
GRANT CREATE ANY CLUSTER TO ggadmin;
GRANT CREATE ANY INDEXTYPE TO ggadmin;
GRANT CREATE ANY OPERATOR TO ggadmin;
GRANT CREATE ANY PROCEDURE TO ggadmin;
GRANT CREATE ANY SEQUENCE TO ggadmin;
GRANT CREATE ANY TRIGGER TO ggadmin;
GRANT CREATE ANY TYPE TO ggadmin;
GRANT CREATE ANY SEQUENCE TO ggadmin;
GRANT CREATE ANY VIEW TO ggadmin;
GRANT ALTER ANY TABLE TO ggadmin;
GRANT ALTER ANY INDEX TO ggadmin;
GRANT ALTER ANY CLUSTER TO ggadmin;
GRANT ALTER ANY INDEXTYPE TO ggadmin;
GRANT ALTER ANY OPERATOR TO ggadmin;
GRANT ALTER ANY PROCEDURE TO ggadmin;
GRANT ALTER ANY SEQUENCE TO ggadmin;
GRANT ALTER ANY TRIGGER TO ggadmin;
GRANT ALTER ANY TYPE TO ggadmin;
GRANT ALTER ANY SEQUENCE TO ggadmin;
GRANT CREATE DATABASE LINK TO ggadmin;
GRANT EXECUTE ON dbms_lock TO ggadmin;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('ggadmin');
To create c##ggadmin, connect to the CDB and run the following commands:
CREATE USER c##ggadmin IDENTIFIED BY cggadmin_pwd CONTAINER=ALL DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON USERS;
GRANT CONNECT TO c##ggadmin CONTAINER=ALL;
GRANT RESOURCE TO c##ggadmin CONTAINER=ALL;
GRANT CREATE TABLE TO c##ggadmin CONTAINER=ALL;
GRANT CREATE VIEW TO c##ggadmin CONTAINER=ALL;
GRANT CREATE SESSION TO c##ggadmin CONTAINER=ALL;
GRANT SELECT_CATALOG_ROLE TO c##ggadmin CONTAINER=ALL;
GRANT DV_GOLDENGATE_ADMIN TO c##ggadmin CONTAINER=ALL;
GRANT DV_GOLDENGATE_REDO_ACCESS TO c##ggadmin CONTAINER=ALL;
GRANT ALTER SYSTEM TO c##ggadmin CONTAINER=ALL;
GRANT ALTER USER TO c##ggadmin CONTAINER=ALL;
GRANT SELECT ANY DICTIONARY TO c##ggadmin CONTAINER=ALL;
GRANT SELECT ANY TRANSACTION TO c##ggadmin CONTAINER=ALL;
GRANT EXECUTE ON dbms_lock TO c##ggadmin CONTAINER=ALL;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('c##ggadmin',CONTAINER=>'ALL');

Create or Unlock the GoldenGate User on the Target Database

On co-managed targets:

If the target is not Oracle Autonomous Database, create a ggadmin user in the target PDB. This user is similar to the ggadmin user you created on the source database, but will require more privileges. See Establishing Oracle GoldenGate Credentials for information about privileges required for a "Replicat all modes" user.

On Autonomous targets:

Autonomous Database has a pre-created ggadmin user that you must unlock. These commands need to be run on the GoldenGate Target instance.

  1. Connect to the target database as admin. 

    export TNS_ADMIN=/u02/deployments/Target/etc
export ORACLE_HOME=/u01/app/client/oracle19
$ $ORACLE_HOME/bin/sqlplus admin/ADW_password@ADW_name

    An example of the ADW_name would be targetatp_high.

  2. Unlock ggadmin. 

    SQL> ALTER USER ggadmin IDENTIFIED BY ggadmin_password ACCOUNT UNLOCK;

  3. Verify that ggadmin is unlocked. 

    export TNS_ADMIN=/u02/deployments/Target/etc
export ORACLE_HOME=/u01/app/client/oracle19
$ORACLE_HOME/bin/sqlplus ggadmin/ADW_password@ADW_name
Note

Oracle Cloud Infrastructure Database Migration Service supports only those scenarios where the Target database and Oracle GoldenGate, both run on private IP addresses.

Parent topic: Reference