Configuring Oracle Database

Overview

The following database set up steps are necessary to use the Oracle connector. The steps mentioned in the section "Setting up permissions" assume the use of the multitenancy configuration with a container database and at least one connectable database. If you intend to not use multitenancy configuration, please refer the section Setting up permissions - Non-CDB environment.

Some steps are based the advice of the Oracle Database Administration documentation. We recommend being familiar with this documentation before continuing.

What you will need

To complete the steps detailed in this guide, you'll need:

• Access to the SYSDBA account - many of the steps require administrator privileges.
• An understanding of which database and schemas you will be targeting for CDC.
• A list of tables which you want to monitor and capture changes for.

Configuring your Oracle database for CDC

The database will require preparation work to begin with. The initial step is to confirm the state of the Database log mode. This mode can be viewed by running the following command:

ARCHIVE LOG LIST;
/-
Example output
-----------------------------------------------------------------------------
Database log mode                         Archive Mode
Automatic archival                          Enabled
Archive destination                         USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence          8
Next log sequence to archive      9
Current log sequence                     9
-/

If the output value for Database log mode is No Archive Mode and the Automatic archival is disabled, you will need to adjust these settings to enable archiving of logs.

1. The database archive log can be enabled by running the SQL statements below:

ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

-- You can then view the log status again which should have updated:
ARCHIVE LOG LIST;

Please note that you may need to adjust the size of the of the recovery file destination or the path to this destination. This is achieved by running the following SQL statements:

ALTER SYSTEM SET db_recovery_file_dest_size = <REPLACE_WITH_YOUR_SIZE>;
ALTER SYSTEM SET db_recovery_file_dest = '/opt/oracle/oradata/recovery_area' scope=spfile;
 
-- If the above has been set, you will need to restart your database
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

2. Enable SUPPLEMENTAL logging for any tables you would like to monitor and consume changes from.

-- It is recommended to enable supplemental logging for individual tables to minimize the amount of information captured in the redo logs
ALTER TABLE inventory.customers ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

-- Minimal supplemental logging must be enabled at the database level and can be configured as follows.
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- You can view the status of logging for all tables owned by the current user using the following query
SELECT OWNER, LOG_GROUP_NAME, TABLE_NAME, LOG_GROUP_TYPE, "ALWAYS", "GENERATED"
FROM DBA_LOG_GROUPS
WHERE OWNER = '<YOUR_USER>'

To enable supplemental logging for your selected tables, you may need to login with a different user who has access to the correct schemas and tables.

3. A new TABLESPACE will need to be created with specific size and auto extend settings:

-- Please note that the size properties can be adjusted as required
-- Additionally, there is an option to configure MAXSIZE UNLIMITED. Monitoring of disk usage should be considered if applying this setting
CREATE TABLESPACE logminer_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/logminer_tbs.dbf' SIZE 50M REUSE AUTOEXTEND ON NEXT 10M;

The preparation work should now be complete. The next step is to create an common user account and grant the necessary permissions to enable the user to perform CDC operations. The necessary steps are covered within the next section.

Setting up permissions

First, create a common user account within the database. This user account will be used by the CDC agent for the monitoring and consumption of changes as they occur within the database. To continue with the set up, a user account with SYSDBA administrative privilege will be required.

1. Create a new user following the recommended naming convention:

-- Please ensure a secure password is provided
-- The tablespace created earlier should be set as the default
CREATE USER C##CDCUSER IDENTIFIED BY password DEFAULT TABLESPACE logminer_tbs QUOTA UNLIMITED ON logminer_tbs CONTAINER=ALL;

2. A number of permissions will need to be granted to this user to enable correct monitoring of the archive logs. These are detailed below:

GRANT CREATE SESSION TO c##dbzuser CONTAINER=ALL;
GRANT SET CONTAINER TO c##dbzuser CONTAINER=ALL;
GRANT SELECT ON V_$DATABASE to c##dbzuser CONTAINER=ALL;
GRANT FLASHBACK ANY TABLE TO c##dbzuser CONTAINER=ALL;
GRANT SELECT ANY TABLE TO c##dbzuser CONTAINER=ALL;
GRANT SELECT_CATALOG_ROLE TO c##dbzuser CONTAINER=ALL;
GRANT EXECUTE_CATALOG_ROLE TO c##dbzuser CONTAINER=ALL;
GRANT SELECT ANY TRANSACTION TO c##dbzuser CONTAINER=ALL;
GRANT LOGMINING TO c##dbzuser CONTAINER=ALL; 
GRANT CREATE TABLE TO c##dbzuser CONTAINER=ALL;
GRANT LOCK ANY TABLE TO c##dbzuser CONTAINER=ALL;
GRANT CREATE SEQUENCE TO c##dbzuser CONTAINER=ALL;
GRANT EXECUTE ON DBMS_LOGMNR TO c##dbzuser CONTAINER=ALL;
GRANT EXECUTE ON DBMS_LOGMNR_D TO c##dbzuser CONTAINER=ALL;
GRANT SELECT ON V_$LOG TO c##dbzuser CONTAINER=ALL;
GRANT SELECT ON V_$LOG_HISTORY TO c##dbzuser CONTAINER=ALL;
GRANT SELECT ON V_$LOGMNR_LOGS TO c##dbzuser CONTAINER=ALL;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO c##dbzuser CONTAINER=ALL;
GRANT SELECT ON V_$LOGMNR_PARAMETERS TO c##dbzuser CONTAINER=ALL;
GRANT SELECT ON V_$LOGFILE TO c##dbzuser CONTAINER=ALL;
GRANT SELECT ON V_$ARCHIVED_LOG TO c##dbzuser CONTAINER=ALL;
GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO c##dbzuser CONTAINER=ALL;
GRANT SELECT ON V_$TRANSACTION TO c##dbzuser CONTAINER=ALL;

Once all the appropriate permissions have been granted, this user is now ready to be used for a Matillion CDC pipeline.

Setting up permissions - non-CDB environment

First, create a local user account within the non-cdb database. This user account will be used by the CDC agent for the monitoring and consumption of changes as they occur within the database. To continue with the set up, a user account with SYSDBA administrative privilege will be required.

1. Create a new user following the recommended naming convention:

-- Please ensure a secure password is provided
-- The tablespace created earlier should be set as the default
CREATE USER CDCUSER IDENTIFIED BY password DEFAULT TABLESPACE logminer_tbs QUOTA UNLIMITED ON logminer_tbs;

2. A number of permissions will need to be granted to this user to enable correct monitoring of the archive logs. These are detailed below:

GRANT CREATE SESSION TO cdcuser;
GRANT SET CONTAINER TO cdcuser;
GRANT SELECT ON V_$DATABASE to cdcuser;
GRANT FLASHBACK ANY TABLE TO cdcuser;
GRANT SELECT ANY TABLE TO cdcuser;
GRANT SELECT_CATALOG_ROLE TO cdcuser;
GRANT EXECUTE_CATALOG_ROLE TO cdcuser;
GRANT SELECT ANY TRANSACTION TO cdcuser;
GRANT LOGMINING TO cdcuser; 
GRANT CREATE TABLE TO cdcuser;
GRANT LOCK ANY TABLE TO cdcuser;
GRANT CREATE SEQUENCE TO cdcuser;
GRANT EXECUTE ON DBMS_LOGMNR TO cdcuser;
GRANT EXECUTE ON DBMS_LOGMNR_D TO cdcuser;
GRANT SELECT ON V_$LOG TO cdcuser;
GRANT SELECT ON V_$LOG_HISTORY TO cdcuser;
GRANT SELECT ON V_$LOGMNR_LOGS TO cdcuser;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO cdcuser;
GRANT SELECT ON V_$LOGMNR_PARAMETERS TO cdcuser;
GRANT SELECT ON V_$LOGFILE TO cdcuser;
GRANT SELECT ON V_$ARCHIVED_LOG TO cdcuser;
GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO cdcuser;
GRANT SELECT ON V_$TRANSACTION TO cdcuser;

3. Once all the appropriate permissions have been granted, this user is now ready to be used for a Matillion CDC pipeline in a non-CDB environment.

Limitations

Change data capture has the following limitations:

• Oracle Autonomous Database isn't supported.
• When there are 1000 or more records in a table, set defaultRowPrefetch to 1000 in the advanced settings.

Next steps

With the above configuration items complete, you should now be in a position to create a CDC pipeline and connect the agent to your source Oracle database. Please consult the Matillion Data Loader Pipeline UI guide for next steps.