/ /

/ /

Configure Migration Prerequisites for Oracle

When you migrate data from an Oracle source database, Relational Migrator automatically checks your database for needed configuration changes and generates a SQL script to implement them. Have a Database Administrator (DBA) review the script and run the commands on the database server. This page provides more details on the required configuration steps.

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.
Oracle 12c introduced the concept of a pluggable database (PDB). Some commands can be run on a PDB, while commands like enabling ARCHIVELOG must be run on the container/master database (CDB). For details on each architecture, see Overview of Container Databases and Pluggable Databases.
Some commands differ based on whether the database is single or multi-tenant. In a multi-tenant database, permissions must include the suffix CONTAINER=ALL. To check whether the database is multi-tenant or not, run the following SQL query:
```
SELECT cdb AS is_multitenant FROM v$database;
```

Steps

To configure your Oracle instance for snapshot jobs:

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.

Tip

To migrate data from a multi-tenant container database, create tablespaces and a Common User.

Create a service account:
```
CREATE USER <user> IDENTIFIED BY "<password>";
```
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;
```
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>;
```

Learn More

Relational Migrator relies on the open-source Debezium connector to capture row-level changes. For more details, see Debezium Oracle
For details on enabling archive logging, see Managing Archived Redo Logs.
For additional troubleshooting and debugging information, see the Debezium Oracle help blog post.

Back

Oracle

PostgreSQL

About this Task

Steps

Set up user permissions

Tip

Important

Learn More