Configure your source Oracle database for a one-time migration

This page describes how to configure your Oracle source database for a one-time migration with Database Migration Service. Before you can use Database Migration Service to pull data from your source Oracle database, you must grant the appropriate privileges to the user account that will be used to connect to your database and access schema and code objects.

By default, Database Migration Service attempts to access all your object definitions through DBA_VIEWS. Access to DBA_VIEWS is granted with the SELECT ANY DICTIONARY privilege. If DBA views aren't accessible to the migration user account, Database Migration Service falls back to ALL_VIEWS. This approach provides you with the greatest degree of flexibility with respect to defining a security model that best matches your requirements.

For the exact configuration steps, refer to the section for your source database:

Configure an Amazon RDS for Oracle database

To configure your Amazon RDS source databases for a one-time migration, grant the user account that Database Migration Service will use the appropriate privileges

For each command, replace USER_NAME with the name of the user account you use for the migration. Running these commands grants privileges to read from V$DATABASE which is required for validation.

GRANT CONNECT TO USER_NAME;
GRANT CREATE SESSION TO USER_NAME;
GRANT SELECT ANY TABLE TO USER_NAME;
GRANT SELECT ANY DICTIONARY TO USER_NAME;
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$DATABASE','USER_NAME','SELECT');

Configure an Oracle database

To configure your Oracle source databases for a one-time migration, grant the user account that Database Migration Service will use the appropriate privileges

For each command, replace USER_NAME with the name of the user account you use for the migration. Running these commands grants privileges to read from V$DATABASE which is required for validation.

GRANT CONNECT TO USER_NAME;
GRANT CREATE SESSION TO USER_NAME;
GRANT SELECT ANY TABLE TO USER_NAME;
GRANT SELECT ANY DICTIONARY TO USER_NAME;
GRANT SELECT ON SYS.V_$DATABASE TO USER_NAME;

Configure an Oracle pluggable database (PDB)

Database Migration Service supports Oracle multi-tenant architecture, where a single container database (CDB) contains one or more pluggable databases (PDBs). Each pluggable database is a self-contained database with a unique ID and name, and can be managed independently.

To configure an Oracle pluggable database so that you can use it with Database Migration Service, perform the following steps:

  1. Create a common user.

    A common user has the same identity in the CDB$ROOT container and in the pluggable databases. A common user can connect to and perform operations within the root, and within any pluggable database in which it has privileges. The common username must start with C## or c##.

  2. Grant the appropriate privileges to the common user that will be used to connect to your database. Different permissions are required at the CDB$ROOT container and pluggable database levels.

    For each command, replace USER_NAME with the name of the user account you use for the migration. Running these commands grants privileges to read from V$DATABASE which is required for validation.

    • Connect to the CDB$ROOT container and run the following commands: 
      GRANT CREATE SESSION TO USER_NAME;
GRANT SET CONTAINER TO USER_NAME;
GRANT SELECT ON SYS.V_$DATABASE TO USER_NAME;
GRANT EXECUTE_CATALOG_ROLE TO USER_NAME;
    • Connect to the pluggable database and run the following commands: 
      GRANT CREATE SESSION TO USER_NAME;
GRANT SET CONTAINER TO USER_NAME;
GRANT SELECT ANY TABLE TO USER_NAME;
GRANT SELECT ANY DICTIONARY TO USER_NAME;
GRANT SELECT ON SYS.V_$DATABASE TO USER_NAME;