Preparing your Oracle source database for schema ingestion Innovation Release

Suggest edits
Note

This configuration isn't required for data migration. However, it provides a helpful migration assessment and facilitates schema conversion by ingesting schema.

Registering an Oracle database with the Hybrid Manager (HM) agent provides migration assessment and schema extraction capabilities.

To assess an Oracle database, the EDB Postgres AI agent needs an Oracle user with access to the database and the necessary permissions to retrieve assessment data. These permissions are required for automated schema extraction and database migration assessment. The EDB Postgres AI agent will still function if these roles aren’t assigned but in reduced feature capacity.

Prerequisites

Install Oracle's SQL*Plus utility on the auxiliary machine you're using to run the EDB Postgres AI agent.

Create and configure an Oracle user

Create an Oracle user (a common user for CDBs, regular user for non-CDBs) for schema extraction and assessment with the EDB Postgres AI agent. You can use this same Oracle user later for data migration with the DMS agent. Create the user following your organization's usual provisioning process, and ensure the user receives the recommended privileges.

Alternatively, you can use an existing user if their current permissions meet the following requirements and aren't overly broad.

To create the user, log in to Oracle CDB as sysdba. Or, if you're using a non-CDB architecture, log in to your non-CDB database as sysdba.

The Oracle user you create needs access to these resources that allow it to connect, read schema metadata, and perform the assessment:

V$VERSION
V$INSTANCE
V$LICENSE
sys.all_tables
sys.all_tab_privs

The user must have the following privileges:

  • CONNECT for database connection.
  • CREATE TABLE to create temporary working tables during the assessment process.
  • SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY to read database metadata and schema information.
Note

These permissions are the minimum required for schema extraction and assessment. Granting additional permissions may be necessary depending on your specific Oracle database configuration and security policies.

The CREATE TABLE privilege is used by the assessment script to create temporary tables during execution. No permanent changes are made to your database.

SELECT_CATALOG_ROLE provides read access to data dictionary views (recommended for least privilege). Alternatively, SELECT ANY DICTIONARY is a more comprehensive system privilege that ensures access to all dictionary objects. Either will work for the assessment.

Avoid logging in as SYS AS SYSDBA, SYSTEM, or any other user with the DBA role unless absolutely necessary, as this can pose security risks.

You will later specify this user in the provider.onprem.databases.dsn section of the EDB Postgres AI agent configuration file.

↑ Up

Migrate your Oracle database to an HM-managed database

Next →

Preparing your Oracle source for data migration