Configuring Oracle CDC (LogMiner)

Overview

Change Data Capture (CDC) enables ROMA Connect to synchronize data sources in real time and synchronously delete data tables.

ROMA Connect supports two CDC modes: XStream and LogMiner. This section describes how to enable the CDC function in LogMiner mode for the Oracle database. In addition, the CDC configuration varies according to the Oracle database type (CDB or non-CDB).

Currently, the CDC task supports only collection of physical database tables and does not support views.

Prerequisites

If the Oracle database is deployed in primary/secondary mode, the secondary Oracle database is not used.
Fields of the binary type, such as BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB, cannot be collected.
If a table contains fields of the SDO_GEOMETRY type, the Oracle database does not generate redo logs when data changes. Therefore, composite tasks cannot be used for data collection.

Configuration When the Oracle Database Is Not a CDB Database

It is recommended that the database administrator configure the CDC function.

Enable log archiving.
1. Run the following command to connect to the database as user sys:
  In practice, you can connect to the database in multiple modes. The following uses the command line mode as an example.
```
sqlplus /nolog
CONNECT sys/password@host:port AS SYSDBA;
```
  Among them:
  - password indicates the password of user sys of the database. You can obtain the password from the database administrator.
  - host indicates the IP address of the server where the database instance is located. Set this parameter based on site requirements.
  - port indicates the port used by the database instance. Set this parameter based on site requirements.
2. Run the following command to check whether the log archiving function is enabled:
```
archive log list;
```
  - If the message "Database log mode: No Archive Mode" is displayed, log archiving is disabled. Go to the next step.
  - If the message "Database log mode: No Archive Mode" is displayed, log archiving is enabled. Go to 1.f.
3. Run the following command to set archive log parameters:
```
alter system set db_recovery_file_dest_size = 100G;
alter system set db_recovery_file_dest = '/opt/oracle/oradata/recovery_area' scope=spfile;
```
  Among them:
  - 100G indicates the size of the log file storage space. Set this parameter based on site requirements.
  - /opt/oracle/oradata/recovery_area indicates the log storage path. Set this parameter based on site requirements. Ensure that the path has been created in advance.
4. Run the following command to enable log archiving function:
  - Enabling the log archiving function requires database restart, which will interrupt services. Exercise caution when performing this operation.
  - Archived logs occupy a large amount of disk space. If the disk space is full, services are affected. Therefore, you need to periodically delete expired archive logs.
```
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
```
5. Run the following command to check whether the log archiving function is enabled:
```
archive log list;
```
  If the message "Database log mode: Archive Mode" is displayed, log archiving is enabled.
6. Run the following command to exit the database:
```
exit;
```
Install the LogMiner tool.
1. Run the following command to connect to the database instance as user sys:
```
sqlplus sys/password@host:port/SID as sysdba
```
  Among them:
  - password indicates the password of user sys of the database. You can obtain the password from the database administrator.
  - host indicates the IP address of the server where the database instance is located. Set this parameter based on site requirements.
  - port indicates the port used by the database instance. Set this parameter based on site requirements.
  - SID indicates the name of the instance where the data to be synchronized is located. Set this parameter based on site requirements.
2. Run the following command to check whether LogMiner is installed successfully:
```
desc DBMS_LOGMNR
desc DBMS_LOGMNR_D
```
  - If no information is displayed, LogMiner is not installed. Go to the next step.
  - If information is displayed, LogMiner has been installed. Go to 3.
3. Run the following commands to install LogMiner:
```
@$ORACLE_HOME/rdbms/admin/dbmslm.sql
@$ORACLE_HOME/rdbms/admin/dbmslmd.sql
```

Create a LogMiner user and grant permissions to the user.

Run the following commands to create a LogMiner user role and configure permissions for the role:

create role roma_logminer_privs;
grant create session,
 execute_catalog_role,
 select any transaction,
flashback any table,
select any table,
lock any table,
 select any dictionary to roma_logminer_privs;
grant select on SYSTEM.LOGMNR_COL$ to roma_logminer_privs;
grant select on SYSTEM.LOGMNR_OBJ$ to roma_logminer_privs;
grant select on SYSTEM.LOGMNR_USER$ to roma_logminer_privs;
grant select on SYSTEM.LOGMNR_UID$ to roma_logminer_privs;
grant select on V_$DATABASE to roma_logminer_privs;
grant create table to roma_logminer_privs;
grant select_catalog_role to roma_logminer_privs;
grant LOGMINING to roma_logminer_privs;
grant lock any table to roma_logminer_privs; 
grant create sequence to roma_logminer_privs; 
grant execute on DBMS_LOGMNR to roma_logminer_privs; 
grant execute on DBMS_LOGMNR_D to roma_logminer_privs; 
grant select on V_$LOG to roma_logminer_privs; 
grant select on V_$LOG_HISTORY to roma_logminer_privs; 
grant select on V_$LOGMNR_LOGS to roma_logminer_privs; 
grant select on V_$LOGMNR_CONTENTS to roma_logminer_privs; 
grant select on V_$LOGMNR_PARAMETERS to roma_logminer_privs; 
grant select on V_$LOGFILE to roma_logminer_privs; 
grant select on V_$ARCHIVED_LOG to roma_logminer_privs; 
grant select on V_$ARCHIVE_DEST_STATUS to roma_logminer_privs; 
grant select on V_$TRANSACTION to roma_logminer_privs;

Among them:

roma_logminer_privs indicates the role name of the LogMiner user. Set this parameter based on site requirements.
grant LOGMINING to roma_logminer_privs; can be added only if the Oracle version is 12c. Otherwise, delete this row.

Run the following command to create user LogMiner:
```
create user roma_logminer identified by password default tablespace users;
grant roma_logminer_privs to roma_logminer;
alter user roma_logminer quota unlimited on users;
```
Among them:
- roma_logminer indicates the name of the LogMiner user. Set this parameter based on site requirements.
- password indicates the password of the LogMiner user. Set it based on site requirements.
- roma_logminer_privs indicates the role of the LogMiner user, which is created in 3.a.
Run the following command to modify log record parameters:
```
alter database add supplemental log data (all) columns;
```
Run the following command to disconnect the database:
```
exit;
```

Configuration When the Oracle Database Is a CDB Database

It is recommended that the database administrator configure the CDC function.

Enable log archiving.
1. Run the following command to connect to the database as user sys:
  In practice, you can connect to the database in multiple modes. The following uses the command line mode as an example.
```
sqlplus /nolog
CONNECT sys/password@host:port AS SYSDBA;
```
  Among them:
  - password indicates the password of user sys of the database. You can obtain the password from the database administrator.
  - host indicates the IP address of the server where the database instance is located. Set this parameter based on site requirements.
  - port indicates the port used by the database instance. Set this parameter based on site requirements.
2. Run the following command to check whether the log archiving function is enabled:
```
archive log list;
```
  - If the message "Database log mode: No Archive Mode" is displayed, log archiving is disabled. Go to the next step.
  - If the message "Database log mode: No Archive Mode" is displayed, log archiving is enabled. Go to 1.f.
3. Run the following command to set archive log parameters:
```
alter system set db_recovery_file_dest_size = 100G;
alter system set db_recovery_file_dest = '/opt/oracle/oradata/recovery_area' scope=spfile;
```
  Among them:
  - 100G indicates the size of the log file storage space. Set this parameter based on site requirements.
  - /opt/oracle/oradata/recovery_area indicates the log storage path. Set this parameter based on site requirements. Ensure that the path has been created in advance.
4. Run the following command to enable log archiving function:
  - Enabling the log archiving function requires database restart, which will interrupt services. Exercise caution when performing this operation.
  - Archived logs occupy a large amount of disk space. If the disk space is full, services are affected. Therefore, you need to periodically delete expired archive logs.
```
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
```
5. Run the following command to check whether the log archiving function is enabled:
```
archive log list;
```
  If the message "Database log mode: Archive Mode" is displayed, log archiving is enabled.
6. Run the following command to disconnect the database:
```
exit;
```
Install the LogMiner tool.
1. Run the following command to connect to the database instance as user sys:
```
sqlplus sys/password@host:port/SID as sysdba
```
  Among them:
  - password indicates the password of user sys of the database. You can obtain the password from the database administrator.
  - host indicates the IP address of the server where the database instance is located. Set this parameter based on site requirements.
  - port indicates the port used by the database instance. Set this parameter based on site requirements.
  - SID indicates the name of the instance where the data to be synchronized is located. Set this parameter based on site requirements.
2. Run the following command to check whether LogMiner is installed successfully:
```
desc DBMS_LOGMNR
desc DBMS_LOGMNR_D
```
  - If no information is displayed, LogMiner is not installed. Go to the next step.
  - If information is displayed, LogMiner has been installed. Go to 3.
3. Run the following commands to install LogMiner:
```
@$ORACLE_HOME/rdbms/admin/dbmslm.sql
@$ORACLE_HOME/rdbms/admin/dbmslmd.sql
```

Create a LogMiner user and grant permissions to the user.

Run the following commands to create a LogMiner user role and configure permissions for the role:

create role c##roma_logminer_privs container=all;
grant create session,
 execute_catalog_role,
 select any transaction,
flashback any table,
select any table,
lock any table,
logmining,
set container,
 select any dictionary to c##roma_logminer_privs container=all;
grant select on SYSTEM.LOGMNR_COL$ to c##roma_logminer_privs container=all;
grant select on SYSTEM.LOGMNR_OBJ$ to c##roma_logminer_privs container=all;
grant select on SYSTEM.LOGMNR_USER$ to c##roma_logminer_privs container=all;
grant select on SYSTEM.LOGMNR_UID$ to c##roma_logminer_privs container=all;
grant create table to c##roma_logminer_privs container=all;
grant select on V_$DATABASE to c##roma_logminer_privs container=all;
grant select_catalog_role to c##roma_logminer_privs container=all;
grant lock any table to c##roma_logminer_privs container=all; 
grant create sequence to c##roma_logminer_privs container=all; 
grant execute on DBMS_LOGMNR to c##roma_logminer_privs container=all; 
grant execute on DBMS_LOGMNR_D to c##roma_logminer_privs container=all; 
grant select on V_$LOG to c##roma_logminer_privs container=all; 
grant select on V_$LOG_HISTORY to c##roma_logminer_privs container=all;
grant select on V_$LOGMNR_LOGS to c##roma_logminer_privs container=all; 
grant select on V_$LOGMNR_CONTENTS to c##roma_logminer_privs container=all; 
grant select on V_$LOGMNR_PARAMETERS to c##roma_logminer_privs container=all; 
grant select on V_$LOGFILE to c##roma_logminer_privs container=all; 
grant select on V_$ARCHIVED_LOG to c##roma_logminer_privs container=all; 
grant select on V_$ARCHIVE_DEST_STATUS to c##roma_logminer_privs container=all; 
grant select on V_$TRANSACTION to c##roma_logminer_privs container=all;

c##roma_logminer_privs indicates the role name of the LogMiner user. Set this parameter based on site requirements.

Run the following command to create user LogMiner:
```
create user c##roma_logminer identified by password default tablespace users container=all;
grant c##roma_logminer_privs to c##roma_logminer container=all;
alter user c##roma_logminer quota unlimited on users container=all;
```
Among them:
- c##roma_logminer indicates the name of the LogMiner user. Set this parameter based on site requirements.
- password indicates the password of the LogMiner user. Set it based on site requirements.
- c##roma_logminer_privs indicates the role of the LogMiner user, which is created in 3.a.
Run the following command to modify log record parameters:
```
alter database add supplemental log data (all) columns;
```
Run the following command to disconnect the database:
```
exit;
```

Parent topic: Creating a Composite Data Integration Task

Previous topic: Creating a Composite Data Integration Task

Next topic: Configuring Oracle CDC (XStream)

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot