Preparing Oracle source database Innovation Release

Suggest edits

Configure your Oracle source database and create a dedicated migration user before starting your migration. How much you need to configure depends on what you plan to do:

  • Schema ingestion and assessment only (EDB Postgres AI agent): You don't need to make database-level changes. You only need to specify a migration user with read access to Oracle data dictionary views — specifically those granted via SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY — and the CREATE TABLE privilege. The assessment script uses the CREATE TABLE privilege to create temporary tables during execution. No permanent changes are made to your database. If your goal is to, for example, evaluate a large Oracle estate before committing to migration, skip directly to Configuring the migration user.

  • Data migration (DMS agent): Requires additional database-level configuration — ARCHIVELOG mode, supplemental logging, dedicated tablespaces, and expanded LogMiner privileges on the migration user. Complete all sections on this page.

If you plan to run both schema assessment and data migration, complete all sections. The migration user you create here serves both agents.

Prerequisites

  • Identify your Oracle architecture (CDB/PDB multitenant vs. non-CDB).
  • Collect the names of the schemas and tables you want to migrate.

Configuring the database

Data migration only

This section is required for data migration. If you are performing schema assessment only, skip to Configuring the migration user.

Enabling ARCHIVELOG mode

Migration tools require the database to be in ARCHIVELOG mode to ensure that data changes are captured during the transfer.

  1. Connect to the database as SYSDBA to perform administrative tasks:

  2. Verify the status:

    archive log list;

    If the status is NOARCHIVELOG, you must enable it.

  3. Configure the Fast Recovery Area (FRA) and enable logging:

    Tip
    • Create a directory for Fast Recovery Area (FRA) in advance.
    • Ensure the db_recovery_file_dest_size is large enough to hold at least 24 hours of redo logs during peak migration loads to prevent the database from hanging.
    ALTER SYSTEM SET db_recovery_file_dest_size = <RECOVERY_FILE_DEST_SIZE> SCOPE=BOTH;
ALTER SYSTEM SET db_recovery_file_dest = '<RECOVERY_FILE_DEST>' SCOPE=BOTH;
    Note

    Setting db_recovery_file_dest and db_recovery_file_dest_size configures Oracle's FRA, which is a required storage location for the archived logs Debezium reads.

    These parameters are critical for database stability. Always consult your DBA before setting them in a production environment.

  4. Restart the database and enable ARCHIVELOG:

    SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

  5. Confirm the change: 

    archive log list;

Enabling supplemental logging

Supplemental logging ensures that the redo logs contain enough information for the DMS agent to identify changed rows.

  1. Connect to the database as SYSDBA to perform administrative tasks:

  2. Enable supplemental logging:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Optionally, ensure adequate redo log space

Undersized redo logs cause frequent log switching, which can slow down or destabilize the migration. If your redo logs are small relative to your database's change volume, increase their size and count before starting the migration.

More information...

The data migration process consists of two main phases: a consistent snapshot of the data, followed by a continuous stream of changes. This change stream is powered by LogMiner and the Oracle database redo logs.

The lifetime of a change in the redo logs is limited. It's determined by the size and number of the logs, as well as the database's change throughput. Redo logs that are too small can cause frequent log switching, which negatively impacts migration performance.


  1. Connect to the root container or database instance as SYSDBA:

  2. To view the current log file configuration, run the following SQL queries:

    SELECT GROUP#, TYPE, MEMBER FROM V_$LOGFILE;
    Output
        GROUP# TYPE    MEMBER
---------- ------- --------------------------------------------------
         1 ONLINE  /opt/oracle/oradata/ORCLCDB/redo03.log
         2 ONLINE  /opt/oracle/oradata/ORCLCDB/redo01.log
         3 ONLINE  /opt/oracle/oradata/ORCLCDB/redo04.log
    SELECT GROUP#, ARCHIVED, BYTES/1024/1024 MB, STATUS FROM V_$LOG;
    Output
        GROUP# ARC  MB STATUS
---------- --- --- ----------------
         1 YES 2000 INACTIVE
         3 YES 2000 INACTIVE
         3 NO  2000 CURRENT

    For example, the output might show three log groups, each with a size of 2000MB, which may be too small for many production environments.

  3. If needed, you can adjust the redo logs using commands like these:

    ALTER DATABASE ADD LOGFILE GROUP 4 ('/opt/oracle/oradata/ORCLCDB/redo04.log') SIZE 8G;
ALTER DATABASE ADD LOGFILE GROUP 5 ('/opt/oracle/oradata/ORCLCDB/redo05.log') SIZE 8G;
ALTER DATABASE ADD LOGFILE GROUP 6 ('/opt/oracle/oradata/ORCLCDB/redo06.log') SIZE 8G;
ALTER DATABASE ADD LOGFILE GROUP 7 ('/opt/oracle/oradata/ORCLCDB/redo07.log') SIZE 8G;
ALTER SYSTEM ARCHIVE LOG CURRENT;
ALTER SYSTEM CHECKPOINT;
ALTER SYSTEM ARCHIVE LOG CURRENT;
ALTER SYSTEM CHECKPOINT;
ALTER SYSTEM ARCHIVE LOG CURRENT;
ALTER SYSTEM CHECKPOINT;
ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;

    These commands create four 8GB redo log groups, each with a single redo log file.

Note

Always consult your DBA to determine the appropriate redo log size for your production environment.

Configuring tables

Data migration only

This section is required for data migration. If you are performing schema assessment only, skip to Configuring the migration user.

Enabling table-level supplemental logging

While database-level logging (enabled in the previous step) provides the foundation, you must also specify which individual tables require full column logging. This ensures that the DMS agent receives a before and after image of every row change.

Note

  • For Oracle, the terms schema and user are used interchangeably. When you run the SQL commands for data migration, to avoid errors, be sure to set the schema name to the correct user account.

  • For Oracle XE compatibility restrictions, see limitations.

More information...

Supplemental logging refers to the capture of additional information in Oracle redo logs, such as before state. This extra redo log information is needed for log-based applications like EDB DMS to capture change events. See Oracle's Supplemental Logging documentation for more information.

To use Debezium for data migration, you must enable supplemental logging at both the database and table levels. This is crucial for Debezium to correctly capture change data:

  • Database-level supplemental logging is the foundation for change data capture (CDC). It ensures that every change message includes a tracking number, which is typically the primary key of the modified row. This allows you to uniquely identify which specific record was changed. Without this, Debezium can't link a change event to its source row.

  • Table-level supplemental logging provides the detailed information. It ensures that the change message includes a before image of the entire row, that is, a complete copy of all columns before the change occurred. This is especially useful for understanding exactly what was changed, as Debezium can compare the before and after states. Without this, Debezium captures only the updated column and not the full context of the change.


  1. Connect to the database:

  2. Enable logging for your specific tables. You must run this command for every table you intend to migrate:

    ALTER TABLE <SCHEMA_NAME>.<TABLE_NAME> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

    For example: 

    ALTER TABLE HR_APP.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE FINANCE_APP.SALARIES ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
Generating commands for a whole schema

If you have dozens of tables, don't run them manually. You can generate the SQL commands for an entire schema by running this query (replace YOUR_SCHEMA_1 with your schema name):

SELECT 'ALTER TABLE ' || owner || '.' || table_name || ' ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;' 
FROM dba_tables 
WHERE owner = 'YOUR_SCHEMA_1';

Copy the output and execute it to update the entire schema at once.

Preparing tablespaces for the migration user

The Data Migration Service (DMS) and the underlying Debezium engine require dedicated tablespaces to store internal metadata, log-mining details, and tracking offsets. This keeps migration-related overhead separate from your application data.

Initial size: 25MB is the minimum for testing. Production environments typically require 512MB or more.

File paths: Always consult your Database Administrator (DBA) to determine the correct directory for .dbf files on your server.

More information...

To perform data migration, both DMS and its underlying Debezium library require a ​​dedicated user​​. Before creating this user, set up the required tablespaces.

For CDB architecture, you need two tablespaces:

  • CDB-level tablespace — This tablespace is for Debezium's internal processes. It's used for log mining and other management operations that must run at the container database level.
  • PDB-level tablespace — This one is for Debezium operations that are specific to your pluggable database. It handles processes related to the individual tables you're capturing within that PDB.

Configuring the migration user

To simplify the migration process, we recommend creating a single Common User (C## for multitenant CDB/PDB configurations) or a dedicated system user (for non-CDB configurations). This unified user handles both schema assessment via the EDB Postgres AI agent and live data migration via the DMS agent.

  1. Connect to the root container or database instance as SYSDBA:

  2. Create a new user for migrations:

  3. Grant all of the following privileges:

  4. Verify the migration user can see your source tables from the CDB root. If this returns no rows, the grants weren't applied with CONTAINER=ALL — regrant all privileges as shown above.

  5. Grant SELECT on source tables:

  6. Grant SELECT permissions to your specific tables. You must run this command for every table you intend to migrate:

    GRANT SELECT ON <SCHEMA_NAME>.<TABLE_NAME> TO <C##_MIGRATION_USER/MIGRATION_USER>

  7. Set the default tablespace:

  8. Exit sql, and perform a quick connection test with your migration user:

  9. Verify the migration user can read data from the source tables:

Validating your database and user configurations

Data migration only

This validation script is recommended for data migration. It doesn't cover schema assessment requirements.

Ensure your database configurations and created user meet all requirements to function with the agents:

  1. Navigate to the EDB DMS agent folder in /opt/cdcagent/reader/.

  2. Create an array that contains the name of all the tables that need to be migrated in <schema_name>.<table_name> format. For example:

    arr=(test1.table1 test1.table2 test1.table3 test1.table4)

  3. Run the script with the necessary parameters. For example:

    You get a response similar to the following:

    *** [Transporter] - Validate system user connectivity
[Pass] Provide username and password of system user can connect to database.

 *** [Transporter] - Validate debezium user connectivity
[Pass] Provide username and password of debezium user can connect to database.

 *** [Transporter] - Validate Archive Log mode
[Pass] Database log mode is Archive Mode.

 *** [Transporter] - Validate fast recovery area parameters
[Pass] Recovery area path was set.
[Pass] Recovery area size limit meets requirements.
[Pass] Recovery area remaining size meets requirements.

 *** [Transporter] - Validate if database supplemental logging is enabled
[Pass] Supplemental logging check for database is passed.

 *** [Transporter] - Validate if table supplemental logging is enabled
[Pass] All source tables enable supplemental logging.

 *** [Transporter] - Validate redo log count&size
[Pass] Redo log count meets the requirements.
[Pass] Redo log size meets the requirements.  
[Pass] Redo logs are all active.

 *** [Transporter] - Validate if tablespace for migration with Limited Privileges were created
[Pass] LOGMINER_TBS tablespace has enough datafiles.
[Pass] LogMiner DataFile Size meet the requirements.
[Pass] LogMiner DataFile Max Size meet the requirements.
[Pass] LogMiner DataFile are auto-extendable.

 *** [Transporter] - Validate if user for migration with Limited Privileges were created
Oracle version is 19, which is 12c or newer.
[Pass] This user has all sys privileges that migration needs.
[Pass] This user has all role privileges that migration needs.
[Pass] This user has all select privilege on specific tables that migration needs.
[Pass] This user has all execute privilege on specific tables that migration needs.

 *** [Transporter] - Validate if user for migration with LOGMINER_TBS tablespace
[Pass] User for migration use LOGMINER_TBS tablespace.

    Your database is ready for CDC migration.

Note

Address any [Failed] or [Suggestion] statuses by modifying the source database settings as recommended here. [Failed] checks are blocking issues that you must resolve before the DMS can execute the data migration. [Suggestion] checks allow migration but can negatively affect performance if left unaddressed.

More information

Next step

Configure the EDB Postgres AI agent

← Prev

Configuring your auxiliary machine or bastion host

↑ Up

Migrate your Oracle database to an HM-managed database

Next →

Configuring the EDB Postgres AI agent to register your Oracle database