Docs Menu
Docs Home
/
Relational Migrator
/
Migration Jobs
/
Configure Prerequisites

Configure Migration Prerequisites for Oracle

On this page

  • About this Task
  • Steps
  • Learn More

To run migration jobs from an Oracle source database, the database may require some configuration changes. If Relational Migrator determines the database needs configuration changes, it automatically generates a SQL script with the required changes. It is recommended to have a Database Administrator (DBA) review the commands in this script and perform their execution on the database server. The Oracle configurations depend on the type of migration job:

  • Snapshot migration jobs migrate all the data and then stops.

  • Continuous migration job run a snapshot and then enter a CDC stage to continuously replicate data changes.

For details on supported versions of Oracle, see Supported Databases and Versions.

About this Task

  • If you're migrating from an Oracle 12c instance, you must run commands as the SYSDBA role.

  • In Oracle 12c the concept of a pluggable database was introduced. Some of these commands can be run on PDB(plugable database) while commands such as enabling ARCHIVELOG must be run on the CDB(container/master database). For details on each architecture, see Overview of Container Databases and Pluggable Databases.

  • Supplemental logging is not allowed in Oracle Express editions.

Steps

1

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. Grant select permissions to the service account:

    The required permission for the service account depend on whether the tables are owned by the service account used to run 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;

    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>;
1

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. Grant select permissions to the service account:

    The required permission for the service account depend on whether the tables are owned by the service account used to run 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;

    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>;

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

    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>;
2

Turn on LogMiner at the database level

To run continous jobs against Oracle, you must enable LogMiner at the database level. The following code-block is an example of automatically-generated code, which you can run manually by substituting your database name:

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;

Tip

To check if LogMiner is already enabled, run the following query:

 SELECT LOG_MODE FROM V$DATABASE;
3

Enable supplemental logging

  1. To run continuous migration jobs against Oracle, you must enable supplemental logging at the database level:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

  2. You must also enable supplemental logging for every table in the migration:

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

Learn More

Back

MySQL

Next

PostgreSQL