Privileges required for Oracle Migration users

The new preparation script only prepares the GGADMIN or C##GGADMIN migration users for offline and online migrations. These are the only supported users to use for initial load and replication. If you need to prepare your GGADMIN or C##GGADMIN users manually, these are the required grants:

Note:

The assessment functionality only supports source databases with version equal or higher than Oracle Database 19c (19.23). Only GGADMIN is supported for initial load and replication.

For scenarios where the database is earlier than Oracle Database 19c (19.23), you need to use the Create migration feature under Migration menu.

For Non-ADB pluggables or single tenant architecture database, where the database version is Oracle Database 19c (19.23) and above, and the migration will be offline, then:

CREATE USER GGADMIN IDENTIFIED BY password
 DEFAULT TABLESPACE users
 TEMPORARY TABLESPACE temp;
CREATE ROLE DMSROLE;
GRANT CREATE SESSION TO DMSROLE;
GRANT DV_GOLDENGATE_ADMIN TO DMSROLE;
GRANT DV_GOLDENGATE_REDO_ACCESS TO DMSROLE;
GRANT ALTER USER TO DMSROLE;
GRANT DATAPUMP_EXP_FULL_DATABASE TO DMSROLE;
GRANT DATAPUMP_IMP_FULL_DATABASE TO DMSROLE;
GRANT SELECT ANY DICTIONARY TO DMSROLE;
GRANT SELECT ANY TRANSACTION TO DMSROLE;
GRANT LOCK ANY TABLE TO DMSROLE;
GRANT CREATE ANY CLUSTER TO DMSROLE;
GRANT CREATE ANY INDEXTYPE TO DMSROLE;
GRANT CREATE ANY OPERATOR TO DMSROLE;
GRANT CREATE ANY SEQUENCE TO DMSROLE;
GRANT CREATE ANY TRIGGER TO DMSROLE;
GRANT CREATE ANY TYPE TO DMSROLE;
GRANT ALTER ANY TABLE TO DMSROLE;
GRANT ALTER ANY CLUSTER TO DMSROLE;
GRANT ALTER ANY INDEXTYPE TO DMSROLE;
GRANT ALTER ANY OPERATOR TO DMSROLE;
GRANT ALTER ANY SEQUENCE TO DMSROLE;
GRANT ALTER ANY TRIGGER TO DMSROLE;
GRANT ALTER ANY TYPE TO DMSROLE;
GRANT CREATE DATABASE LINK TO DMSROLE;
GRANT ALTER SYSTEM TO DMSROLE;
GRANT ALTER DATABASE TO DMSROLE;
GRANT INSERT ANY TABLE TO GGADMIN;
GRANT UPDATE ANY TABLE TO GGADMIN;
GRANT DELETE ANY TABLE TO GGADMIN;
GRANT DROP ANY TABLE TO GGADMIN;
GRANT DROP ANY INDEX TO GGADMIN;
GRANT DROP ANY VIEW TO GGADMIN;
GRANT DROP ANY PROCEDURE TO GGADMIN;
GRANT CREATE ANY TABLE TO GGADMIN;
GRANT CREATE ANY INDEX TO GGADMIN;
GRANT CREATE ANY PROCEDURE TO GGADMIN;
GRANT CREATE ANY VIEW TO GGADMIN;
GRANT ALTER ANY INDEX TO GGADMIN;
GRANT ALTER ANY PROCEDURE TO GGADMIN;
GRANT DMSROLE TO GGADMIN;
GRANT SELECT ON V_$SESSION  TO GGADMIN
GRANT SELECT ON V_$TRANSACTION  TO GGADMIN
GRANT SELECT ON V_$DATABASE TO GGADMIN

For Online migrations when the database version is earlier than Oracle AI Database 26ai, also run:

EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('GGADMIN', CONTAINER=>'CURRENT'); 

For Online migrations when the database version is Oracle AI Database 26ai, also run:

GRANT OGG_CAPTURE TO DMSROLE CONTAINER=CURRENT;
GRANT OGG_APPLY TO DMSROLE CONTAINER=CURRENT;
GRANT OGG_APPLY_PROCREP TO DMSROLE CONTAINER=CURRENT;

If the database version is earlier than Oracle Database 19c (19.23), the architecture is multitenant and you will perform an online migration , then you also must create the common user (for example, C##GGADMIN) in CDB$ROOT as follows:

CREATE USER C##GGADMIN IDENTIFIED BY "Password" CONTAINER=ALL DEFAULT
TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON USERS;
CREATE ROLE C##DMSROLE;
GRANT CREATE SESSION TO C##DMSROLE CONTAINER=ALL;
GRANT CREATE VIEW TO C##DMSROLE CONTAINER=ALL;
GRANT CREATE TABLE TO C##DMSROLE CONTAINER=ALL;
GRANT ALTER SYSTEM TO C##DMSROLE CONTAINER=ALL;
GRANT SELECT ANY DICTIONARY TO C##DMSROLE CONTAINER=ALL;
GRANT DV_GOLDENGATE_ADMIN TO C##DMSROLE CONTAINER=ALL;
GRANT DV_GOLDENGATE_REDO_ACCESS TO C##DMSROLE CONTAINER=ALL;
GRANT C##DMSROLE TO C##GGADMIN CONTAINER=ALL;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('C##GGADMIN',CONTAINER=>'ALL');

When Autonomous AI Database is the instance, run the following with ADMIN user:

GRANT PDB_DBA TO DMSROLE; 
GRANT DMSROLE TO GGADMIN;
GRANT SELECT ON SYS.V_$SESSION TO GGADMIN;
GRANT SELECT ON SYS.V_$TRANSACTION TO GGADMIN;
GRANT SELECT ON SYS.V_$DATABASE TO GGADMIN;
ALTER USER GGADMIN IDENTIFIED BY "YOUR_PASSWORD" ACCOUNT UNLOCK;
EXEC DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL(username => 'GGADMIN');

RDS grants for GGADMIN:

GRANT UNLIMITED TABLESPACE TO GGADMIN;
EXEC RDSADMIN.RDSADMIN_UTIL.GRANT_SYS_OBJECT('CDEF$', 'GGADMIN', 'SELECT');
EXEC RDSADMIN.RDSADMIN_UTIL.GRANT_SYS_OBJECT('USER$', 'GGADMIN', 'SELECT');
EXEC RDSADMIN.RDSADMIN_UTIL.GRANT_SYS_OBJECT('COL$', 'GGADMIN', 'SELECT');
EXEC RDSADMIN.RDSADMIN_UTIL.GRANT_SYS_OBJECT('CON$', 'GGADMIN', 'SELECT');
EXEC RDSADMIN.RDSADMIN_UTIL.GRANT_SYS_OBJECT('OBJ$', 'GGADMIN', 'SELECT');
EXEC RDSADMIN.RDSADMIN_UTIL.GRANT_SYS_OBJECT('SEG$', 'GGADMIN', 'SELECT');
EXEC RDSADMIN.RDSADMIN_UTIL.GRANT_SYS_OBJECT('TAB$', 'GGADMIN', 'SELECT');
EXEC RDSADMIN.RDSADMIN_UTIL.GRANT_SYS_OBJECT('V_$SESSION', 'GGADMIN', 'SELECT');
EXEC RDSADMIN.RDSADMIN_UTIL.GRANT_SYS_OBJECT('V_$TRANSACTION', 'GGADMIN', 'SELECT');
EXEC RDSADMIN.RDSADMIN_UTIL.GRANT_SYS_OBJECT('V_$DATABASE', 'GGADMIN', 'SELECT');
EXEC RDSADMIN.RDSADMIN_DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE( GRANTEE=>'GGADMIN', PRIVILEGE_TYPE=>'CAPTURE', GRANT_SELECT_PRIVILEGES=>TRUE, DO_GRANTS=>TRUE );
GRANT CREATE SESSION TO DMSROLE;
GRANT SELECT ANY DICTIONARY TO DMSROLE;
GRANT SELECT_CATALOG_ROLE TO DMSROLE;
GRANT CREATE VIEW TO DMSROLE;
GRANT DATAPUMP_EXP_FULL_DATABASE TO DMSROLE;
GRANT DATAPUMP_IMP_FULL_DATABASE TO DMSROLE;
GRANT READ ON DIRECTORY BDUMP TO DMSROLE;
GRANT DMSROLE TO GGADMIN;