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 LogMine mode for the Oracle database. In addition, the CDC configuration varies according to the Oracle database type (CDB or non-CDB).

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.

1. 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:
      NOTICE:

      • 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;
2. 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
3. Create a LogMiner user and grant permissions to the user.
   1. 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 select_catalog_role to roma_logminer_privs;
      grant LOGMINING 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.
   2. 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.
   3. Run the following command to modify log record parameters:

      alter database add supplemental log data (all) columns;
   4. 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.

1. 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:
      NOTICE:

      • 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;
2. 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
3. Create a LogMiner user and grant permissions to the user.
   1. 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 select on V_$DATABASE to c##roma_logminer_privs container=all;
      grant select_catalog_role 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.

   2. 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.
   3. Run the following command to modify log record parameters:

      alter database add supplemental log data (all) columns;
   4. Run the following command to disconnect the database:

      exit;