Preparing Oracle source databases vba_preview

Configuring Oracle for EDB Transporter migrations 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:

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 Transporter, 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

EDB Transporter's 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> 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

CDCReader installation 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

More information

Your database is ready for CDC migration.

For more information, see the Debezium Oracle Connector documentation.

↑ Up

EDB Transporter

Preparing Postgres source databases

Preparing Oracle source databases vba_preview

Oracle configuration

Enable archive log mode

Enable database supplemental logging

Enable supplemental logging for table columns

Verify redo logs for adequate count and size

Create a user with limited privileges for data migration

Tablespace preparation

User creation and access grants

Grant SELECT on source tables

Validate configuration

More information

↑ Up

Next →

Grant `SELECT` on source tables