Preparing Oracle source databases

Configuring Oracle for EDB Data Migration Services (EDB DMS) requires sysdba privileges.

Configure an Oracle source database to:

• Enable archive log mode.
• Enable supplemental logging for the database and table columns of interest.
• Ensure adequate redo log space is available.
• Create a user with limited privileges to carry out the data migration.

Execute SQL statements with sqlplus or a similar client.

This command propmpts you for the password for <ORA_USER>:

sqlplus <ORA_USER>@<ORA_HOST>:<ORA_PORT>/<ORA_SID> as sysdba

Where:

• <ORA_HOST> is the Oracle DB hostname.
• <ORA_PORT> is the Oracle DB port.
• <ORA_SID> is the Oracle System ID for the DB or CDB/PDB combination.
• <ORA_USER> is an Oracle DB username with sysdba privileges.

Oracle configuration

To perform Oracle configuration:

1. Enable archive log mode.
2. Enable database supplemental logging.
3. Enable supplemental logging for table columns.
4. Verify redo logs for adequate count and size.
5. Create a user with limited privileges for data migration.
6. Grant SELECT on source tables.
7. Validate configuration.

Enable archive log mode

Oracle databases can operate in ARCHIVELOG or NOARCHIVELOG mode. In ARCHIVELOG mode, filled redo logs are archived rather than put back into log rotation to be overwritten. This mode is needed for the change data capture (CDC) process to use LogMiner and produce a complete history of changes after an initial consistent snapshot.

To see the database mode:

archive log list;

The returned content indicates the database mode:

Database log mode 	Archive Mode
...or
Database log mode 	No Archive Mode

If ARCHIVELOG mode is enabled, confirm with your DBA that the size of your recovery file destination is appropriate for your workload.

When enabling archive log mode, you need to enable a fast recovery area. For more information on enabling an Oracle fast recovery area, see Enabling the Fast Recovery Area in the Oracle documentation.

To enable archive logging:

ORACLE_SID=<YOUR_SID> sqlplus /nolog

CONNECT <SYSDBA_USER>/<SYSDBA_USER>_PWD AS SYSDBA
alter system set db_recovery_file_dest_size = <RECOVERY_FILE_DEST_SIZE>;
alter system set db_recovery_file_dest = '<RECOVERY_FILE_DEST>' scope=spfile;
shutdown immediate
startup mount
alter database archivelog;
alter database open;
archive log list;
exit;

Where:

• <YOUR_SID> is the Oracle DB system ID.
• <SYSDBA_USER> is the name of a user with sysdba privileges.
• <SYSDBA_USER_PWD> is the password for <SYSDBA_USER>.
• <RECOVERY_FILE_DEST_SIZE> is the size allowed for the recovery behavior, for example, 100G for 100 gigabytes.
• <RECOVERY_FILE_DEST> is the file system path for an Oracle fast recovery area. This path can be a directory, file system, or Oracle Automatic Storage Management. Consult your DBA for guidance.

The archive log list output shows the database is now in archive log mode.

Enable database supplemental logging

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 some log-based applications, such as EDB DMS, to capture change events. See Supplemental Logging in the Oracle documentation for more information.

You can enable supplemental logging at the database and table level. The following command enables minimal supplemental logging required for LogMiner to function at the database level:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Enable supplemental logging for table columns

For every table you want to migrate, you must enable supplemental logging. To do so for all columns in a table, apply the following statement:

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

Where <TABLE> is the identifier for the table to migrate.

Use ALTER with all table columns you want to migrate.

Verify redo logs for adequate count and size

The migration process involves two phases. The first is a consistent snapshot. The second is continuous streaming of database changes. This stream of database changes is powered by LogMiner and the Oracle DB redo logs.

Database changes have a limited lifetime on the redo logs before the change is no longer present in the log history. This lifetime depends on the size of the redo logs, the number of redo logs, and the change throughput to the database. Also, undersized logs cause frequent log switching and affect migration performance.

To examine the state of the database redo logs:

SELECT GROUP#, TYPE, MEMBER FROM V_$LOGFILE;

    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;

    GROUP# ARC  MB STATUS
---------- --- --- ----------------
         1 YES 2000 INACTIVE
         3 YES 2000 INACTIVE
         3 NO  2000 CURRENT

This example uses three log groups of size 2000MB. Each group has one file member. This might be too small for many production databases. You can safely adjust the redo logs with synchronous commands such as the following:

  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 result in four new 8GB log groups. Each group has a single log file.

Consult your DBA for appropriate production sizing.

Create a user with limited privileges for data migration

Tablespace preparation

Provide a database user with adequate roles to carry out the CDC process.

Then, we recommend creating a tablespace for the CDC user. For container databases, you need to create a pluggable database as well.

This example creates a tablespace and datafiles for CDC migration. Your database settings might vary, but a common configuration with SMALLFILE tablespaces and an 8kB database block size results in a maximum of 32GB of storage avaiable per MAXSIZE tablespace datafile. Therefore, you might need to add multiple AUTOEXTEND datafiles when this limit might be exceeded.

-- Create the tablespace, or in the case of a CDB/PDB, create the CDB tablespace
CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '/opt/oracle/oradata/ORCLCDB/logminer_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

-- For CDB/PDB deployments we must specify at least one tablespace datafile for the PDB
CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCPDB1/logminer_tbs_1.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
-- Additional data files can be added with as follows
ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/logminer_tbs_2.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Where:

• <TABLESPACE_NAME> is the tablespace name for the CDC migration user to use.

User creation and access grants

With the tablespace files in place, you can create a user with appropriate access grants for CDC migration.

For a CDB/PDB database setup, note the tablespace default and quota:

    CREATE USER <MIGRATION_USER> IDENTIFIED BY <MIGRATION_USER_PASSWORD>
        DEFAULT TABLESPACE <TABLESPACE_NAME>
        QUOTA UNLIMITED ON <TABLESPACE_NAME>
        CONTAINER=ALL;
    GRANT CREATE SESSION TO <MIGRATION_USER> CONTAINER=ALL;
    GRANT SET CONTAINER TO <MIGRATION_USER> CONTAINER=ALL;
    GRANT SELECT ON V_$DATABASE to <MIGRATION_USER> CONTAINER=ALL;
    GRANT FLASHBACK ANY TABLE TO <MIGRATION_USER> CONTAINER=ALL;
    GRANT SELECT ANY TABLE TO <MIGRATION_USER> CONTAINER=ALL;
    GRANT SELECT_CATALOG_ROLE TO <MIGRATION_USER> CONTAINER=ALL;
    GRANT EXECUTE_CATALOG_ROLE TO <MIGRATION_USER> CONTAINER=ALL;
    GRANT SELECT ANY TRANSACTION TO <MIGRATION_USER> CONTAINER=ALL;
    GRANT SELECT ANY DICTIONARY TO <MIGRATION_USER> CONTAINER=ALL;
    GRANT LOGMINING TO <MIGRATION_USER> CONTAINER=ALL;
    GRANT CREATE TABLE TO <MIGRATION_USER> CONTAINER=ALL;
    GRANT LOCK ANY TABLE TO <MIGRATION_USER> CONTAINER=ALL;
    GRANT CREATE SEQUENCE TO <MIGRATION_USER> CONTAINER=ALL;
    GRANT EXECUTE ON DBMS_LOGMNR TO <MIGRATION_USER> CONTAINER=ALL;
    GRANT EXECUTE ON DBMS_LOGMNR_D TO <MIGRATION_USER> CONTAINER=ALL;
    GRANT SELECT ON V_$LOGMNR_LOGS TO <MIGRATION_USER> CONTAINER=ALL;
    GRANT SELECT ON V_$LOGMNR_CONTENTS TO <MIGRATION_USER> CONTAINER=ALL;
    GRANT SELECT ON V_$LOGFILE TO <MIGRATION_USER> CONTAINER=ALL;
    GRANT SELECT ON V_$ARCHIVED_LOG TO <MIGRATION_USER> CONTAINER=ALL;
    GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO <MIGRATION_USER> CONTAINER=ALL;
    GRANT SELECT ON V_$TRANSACTION TO <MIGRATION_USER> CONTAINER=ALL;

For a non-CDB database:

    CREATE USER <MIGRATION_USER> IDENTIFIED BY <MIGRATION_USER_PASSWORD>
        DEFAULT TABLESPACE <TABLESPACE_NAME>
        QUOTA UNLIMITED ON <TABLESPACE_NAME>;
    GRANT CREATE SESSION TO <MIGRATION_USER>;
    GRANT SELECT ON V_$DATABASE to <MIGRATION_USER>;
    GRANT FLASHBACK ANY TABLE TO <MIGRATION_USER>;
    GRANT SELECT ANY TABLE TO <MIGRATION_USER>;
    GRANT SELECT_CATALOG_ROLE TO <MIGRATION_USER>;
    GRANT EXECUTE_CATALOG_ROLE TO <MIGRATION_USER>;
    GRANT SELECT ANY TRANSACTION TO <MIGRATION_USER>;
    GRANT SELECT ANY DICTIONARY TO <MIGRATION_USER>;
    GRANT LOGMINING TO <MIGRATION_USER>;
    GRANT CREATE TABLE TO <MIGRATION_USER>;
    GRANT LOCK ANY TABLE TO <MIGRATION_USER>;
    GRANT CREATE SEQUENCE TO <MIGRATION_USER>;
    GRANT EXECUTE ON DBMS_LOGMNR TO <MIGRATION_USER>;
    GRANT EXECUTE ON DBMS_LOGMNR_D TO <MIGRATION_USER>;
    GRANT SELECT ON V_$LOGMNR_LOGS TO <MIGRATION_USER>;
    GRANT SELECT ON V_$LOGMNR_CONTENTS TO <MIGRATION_USER>;
    GRANT SELECT ON V_$LOGFILE TO <MIGRATION_USER>;
    GRANT SELECT ON V_$ARCHIVED_LOG TO <MIGRATION_USER>;
    GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO <MIGRATION_USER>;
    GRANT SELECT ON V_$TRANSACTION TO <MIGRATION_USER>;

Where:

• <MIGRATION_USER> is the name of the user to create for CDC migration table access.
• <MIGRATION_USER_PASSWORD> is the password for the migration user.
• <TABLESPACE_NAME> is the tablespace for <MIGRATION_USER>.

Grant SELECT on source tables

The new <MIGRATION_USER> needs SELECT access to source tables. Oracle doesn't support granting access to an entire schema, so you need to do this for each table.

GRANT SELECT ON <TABLE_NAME> TO <MIGRATION_USER>

Where:

• <MIGRATION_USER> is the name of the user to create for CDC migration table access.
• <TABLE_NAME> is the name of an individual table to migrate.

Validate configuration

The EDB DMS Reader installation (packaged as cdcreader) comes with a helper script that validates the Oracle configuration and helps you identify any issues. After you configure the database, we recommend running the script to ensure all checks pass.

Run the script without arguments to print the usage:

/opt/cdcreader/oracleConfigValidation.sh

SSL configuration

Ensure you configure your source database server to accept SSL connections to allow the EDB DMS Reader to connect to it. You must create a server certificate and a server private key, for example, with OpenSSL, to enable this configuration.

More information

Your database is ready for CDC migration.

For more information, see the Debezium Oracle Connector documentation.