Configure Migration Prerequisites for Oracle

Set up user permissions

The following code creates a new Oracle service account for Relational Migrator to connect to the Oracle instance. Alternatively, you can use an existing Oracle service account to connect to Relational Migrator with the appropriate permissions.

1. Create a service account:

   CREATE USER '<user>'@'localhost' IDENTIFIED BY '<password>';

2. Confirm that the service account owns the tables in the migration job.

   Required permissions depend on whether the service account owns the tables used in the migration job. To check table ownership, run the following query:

   SELECT TABLE_NAME, OWNER
   FROM ALL_TABLES
   WHERE TABLE_NAME ='<table_name>'
   ORDER BY OWNER, TABLE_NAME;

3. Grant permissions to the service account.

   Important

   If you're migrating a multi-tenant container database as a common user, append CONTAINER=ALL when granting permissions. For example:

   GRANT CREATE SESSION TO <user> CONTAINER=ALL;

   If the service account is the table owner:

   GRANT CREATE SESSION TO <user>;
   GRANT SELECT ON V$DATABASE TO <user>;

   If the service account is not the table owner:

   GRANT CREATE SESSION TO <user>;
   GRANT SELECT_CATALOG_ROLE TO <user>;
   GRANT SELECT ANY TABLE TO <user>;
   GRANT SELECT ON V$DATABASE TO <user>;
   GRANT FLASHBACK ANY TABLE TO <user>;

Set up user permissions

The following code creates a new Oracle service account for Relational Migrator to connect to the Oracle instance. Alternatively, you can use an existing Oracle service account to connect to Relational Migrator with the appropriate permissions.

1. Create a service account:

   CREATE USER '<user>'@'localhost' IDENTIFIED BY '<password>';

2. Confirm that the service account owns the tables in the migration job.

   Required permissions depend on whether the service account owns the tables used in the migration job. To check table ownership, run the following query:

   SELECT TABLE_NAME, OWNER
   FROM ALL_TABLES
   WHERE TABLE_NAME ='<table_name>'
   ORDER BY OWNER, TABLE_NAME;

3. Grant permissions to the service account.

   Important

   If you're migrating a multi-tenant container database as a common user, append CONTAINER=ALL when granting permissions. For example:

   GRANT CREATE SESSION TO <user> CONTAINER=ALL;

   If the service account is the table owner:

   GRANT CREATE SESSION TO <user>;
   GRANT SELECT ON V$DATABASE TO <user>;

   If the service account is not the table owner:

   GRANT CREATE SESSION TO <user>;
   GRANT SELECT_CATALOG_ROLE TO <user>;
   GRANT SELECT ANY TABLE TO <user>;
   GRANT SELECT ON V$DATABASE TO <user>;
   GRANT FLASHBACK ANY TABLE TO <user>;

4. Grant additional permissions to the service account to run continuous migration jobs.

   Append CONTAINER=ALL if you're migrating a multi-tenant container database.

   GRANT SET CONTAINER TO <user>;
   GRANT EXECUTE_CATALOG_ROLE TO <user>;
   GRANT SELECT ANY TRANSACTION TO <user>;
   GRANT LOGMINING TO <user>;
   GRANT CREATE TABLE TO <user>;
   GRANT LOCK ANY TABLE TO <user>;
   GRANT CREATE SEQUENCE TO <user>;
   GRANT SELECT ON DBA_TABLESPACES TO <user>;
   GRANT EXECUTE ON DBMS_LOGMNR TO <user>;
   GRANT EXECUTE ON DBMS_LOGMNR_D TO <user>;
   GRANT SELECT ON V$LOG TO <user>;
   GRANT SELECT ON V$LOG_HISTORY TO <user>;
   GRANT SELECT ON V$LOGMNR_LOGS TO <user>;
   GRANT SELECT ON V$LOGMNR_CONTENTS TO <user>;
   GRANT SELECT ON V$LOGMNR_PARAMETERS TO <user>;
   GRANT SELECT ON V$LOGFILE TO <user>;
   GRANT SELECT ON V$ARCHIVED_LOG TO <user>;
   GRANT SELECT ON V$ARCHIVE_DEST_STATUS TO <user>;
   GRANT SELECT ON V$TRANSACTION TO <user>;
   GRANT SELECT ON V_$MYSTAT TO <user>;
   GRANT SELECT ON V_$STATNAME TO <user>;

Turn on archive logging

1. To see if archive logging is already enabled, run the following query:

   SELECT LOG_MODE FROM V$DATABASE;

   This outputs ARCHIVELOG if logging is enabled, or NOARCHIVELOG if it isn't.

2. If archive logging isn't already enabled, enable it.

   Relational Migrator can automatically generate code to enable logging. The following code is an example. If you're migrating a multi-tenant database, run these commands on the container/ master database.

   ALTER SYSTEM SET db_recovery_file_dest_size = 10G;
   ALTER SYSTEM SET db_recovery_file_dest = '/opt/oracle/oradata/recovery_area' scope=spfile;
   SHUTDOWN IMMEDIATE;
   STARTUP MOUNT
   ALTER DATABASE ARCHIVELOG;
   ALTER DATABASE OPEN;

Enable supplemental logging

1. Enable supplemental logging on the database:

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

2. Enable supplemental logging for every table in the migration:

   ALTER TABLE schemaName.tableName ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
   /* Additional ALTER TABLE STATEMENTS... */