Version 24.3.9121

• Prerequisites
  • Requirements
  • Limitations on Incremental Replication
  • Set Up Oracle for Enhanced CDC
• Oracle コネクタを追加
• Oracle への認証
  • Security Identifier (SID)
  • Service Name
  • Data Source
• Complete Your Connection

CData Sync アプリケーションからOracle コネクタを使用して、Oracle からデータを取得してサポートされている任意の同期先に移動できます。これを行うには、コネクタを追加し、コネクタへの認証を行い、接続を完了する必要があります。

Note: This connection can be used only as a source for the Enhanced Change Data Capture job type.

Prerequisites

Before you add and set up the Oracle source connector, you need to set up the Oracle database for enhanced change data capture (CDC), as explained in the following sections.

Requirements

Before you set up Oracle for enhanced CDC, ensure that you have one of the following supported versions: 12c, 19c, and 21c.

Limitations on Incremental Replication

The Oracle connector cannot capture data changes from the following schemas:

• appqossys
• audsys
• ctxsys
• dvsys
• dbsfwuser
• dbsnmp
• qsmadmin_internal
• lbacsys
• mdsys
• ojvmsys
• olapsys
• orddata
• ordsys
• outln
• sys
• system
• vecsys (Oracle 23+)
• wmsys
• xdb

The connector also cannot capture changes for certain tables:

• nested tables

• index-organized tables with names that have a prefix of SYS_IOT_OVER_

• spatial tables with names that start with MDRT_, MDRS_, or MDXT_

• Compression Advisor tables that match this pattern: CMP[3|4]$[0-9]+

Set Up Oracle for Enhanced CDC

Perform the following steps to enable Oracle LogMiner for tracking changes via the enhanced CDC capability in Sync:

1. Enable ARCHIVELOG mode on your database. To determine whether ARCHIVELOG mode is enabled already, submit the following statement:

   SELECT LOG_MODE FROM V$DATABASE
   

   If the output from this statement does not show ARCHIVELOG as the log mode, submit the following statements to enable ARCHIVELOG mode:

   SHUTDOWN IMMEDIATE;
   STARTUP MOUNT;
   ALTER DATABASE ARCHIVELOG;
   ALTER DATABASE OPEN;
   

2. Ensure that the database retains backups and archive logs for at least twenty-four hours. CData recommends retaining backups and logs for seven days, as shown in this example:

   RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
   

3. Enable minimal supplemental logging (must be enable at the database level).

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
   

4. Enable supplemental logging for every table.

   ALTER TABLE SchemaName.TableName ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS
   

   Note: You can enable supplemental logging for the entire database, as well. However, CData does not recommend that because doing so generates a significant volume of logs. This behavior affects performance because it substantially increases the time that is required to process those logs.

5. Grant your user permission to run Oracle LogMiner by following the instructions below for your specific database.

   Standalone Databases

   Submit the following commands to grant permissions to run Logminer:

   -- connect to the database with 'sys as SYSDBA'
   CREATE TABLESPACE logminer_tbs DATAFILE '/opt/oracle/oradata/ORCL/logminer_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
   CREATE USER <Username> IDENTIFIED BY <Password> DEFAULT TABLESPACE logminer_tbs QUOTA UNLIMITED ON logminer_tbs ;    
     
   GRANT CREATE SESSION TO <Username>; 
   GRANT SELECT ON V_$DATABASE to <Username>; 
   GRANT FLASHBACK ANY TABLE TO <Username>; 
   GRANT SELECT ANY TABLE TO <Username>; 
   GRANT SELECT_CATALOG_ROLE TO <Username>; 
   GRANT EXECUTE_CATALOG_ROLE TO <Username>; 
   GRANT SELECT ANY TRANSACTION TO <Username>; 
   GRANT LOGMINING TO <Username>; 
   GRANT CREATE TABLE TO <Username>; 
   GRANT LOCK ANY TABLE TO <Username>; 
   GRANT CREATE SEQUENCE TO <Username>; 
   GRANT EXECUTE ON DBMS_LOGMNR TO <Username>; 
   GRANT EXECUTE ON DBMS_LOGMNR_D TO <Username>; 
   GRANT SELECT ON V_$LOG TO <Username>; 
   GRANT SELECT ON V_$LOG_HISTORY TO <Username>; 
   GRANT SELECT ON V_$LOGMNR_LOGS TO <Username>; 
   GRANT SELECT ON V_$LOGMNR_CONTENTS TO <Username>; 
   GRANT SELECT ON V_$LOGMNR_PARAMETERS TO <Username>; 
   GRANT SELECT ON V_$LOGFILE TO <Username>; 
   GRANT SELECT ON V_$ARCHIVED_LOG TO <Username>; 
   GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO <Username>; 
   GRANT SELECT ON V_$TRANSACTION TO <Username>; 
   GRANT SELECT ON V_$MYSTAT TO <Username>; 
   GRANT SELECT ON V_$STATNAME TO <Username>;
   

   Multi-tenant Databases

   Submit the following commands to grant permissions to run Logminer:

   -- connect to the root container as 'sys as SYSDBA' and create a tablespace
   CREATE TABLESPACE logminer_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/logminer_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
   
   -- connect to your PDB as 'sys as SYSDBA' and create a tablespace with the same name
   CREATE TABLESPACE logminer_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/logminer_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
     
   -- reconnect to the root container as 'sys as SYSDBA' and create a common user with access to all containers
   CREATE USER <CommonUser> IDENTIFIED BY <Password> DEFAULT TABLESPACE logminer_tbs QUOTA UNLIMITED ON logminer_tbs CONTAINER=ALL;
   
   GRANT CREATE SESSION TO <CommonUser> CONTAINER=ALL; 
   GRANT SET CONTAINER TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ON V_$DATABASE to <CommonUser> CONTAINER=ALL; 
   GRANT FLASHBACK ANY TABLE TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ANY TABLE TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT_CATALOG_ROLE TO <CommonUser> CONTAINER=ALL; 
   GRANT EXECUTE_CATALOG_ROLE TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ANY TRANSACTION TO <CommonUser> CONTAINER=ALL; 
   GRANT LOGMINING TO <CommonUser> CONTAINER=ALL; 
   GRANT CREATE TABLE TO <CommonUser> CONTAINER=ALL; 
   GRANT LOCK ANY TABLE TO <CommonUser> CONTAINER=ALL; 
   GRANT CREATE SEQUENCE TO <CommonUser> CONTAINER=ALL; 
   GRANT EXECUTE ON DBMS_LOGMNR TO <CommonUser> CONTAINER=ALL; 
   GRANT EXECUTE ON DBMS_LOGMNR_D TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ON V_$LOG TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ON V_$LOG_HISTORY TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ON V_$LOGMNR_LOGS TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ON V_$LOGMNR_CONTENTS TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ON V_$LOGMNR_PARAMETERS TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ON V_$LOGFILE TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ON V_$ARCHIVED_LOG TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ON V_$TRANSACTION TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ON V_$MYSTAT TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ON V_$STATNAME TO <CommonUser> CONTAINER=ALL; 
   

   Note: An example of a common username is C##LMUSER.

6. Restart your database.

Oracle コネクタを追加

Sync でOracle のデータを使用できるようにするには、まず以下の手順でコネクタを追加する必要があります。

1. Sync のダッシュボードから接続ページを開きます。

2. 接続を追加をクリックしてコネクタを選択ページを開きます。

3. データソースタブをクリックしてOracle (Native) 行に移動します。

4. 行末にある接続を設定アイコンをクリックして、新しい接続ページを開きます。接続を設定アイコンが利用できない場合は、コネクタをダウンロードアイコンをクリックしてOracle (Native) コネクタをインストールします。新規コネクタのインストールについて詳しくは、接続を参照してください。

Oracle への認証

コネクタを追加したら、必須プロパティを設定する必要があります。

On the New Connection page, enter the connection name of your choice.

CData Sync supports authenticating to Oracle in a few ways. Select connection type below to proceed to the relevant section that contains the connection details.

• Service Identifier (default)
• Service Name
• Data Source

Security Identifier (SID)

To connect to Oracle resources by using a SID, specify the following settings:

• User - Enter the username that you use to authenticate to the Oracle database.

• Password - Enter the password that you use to authenticate to the Oracle database.

• Server - Enter the host name or IP address of the server that hosts the Oracle database. The default server is localhost.

• Port - Enter the port number for the Oracle server. The default port is 1521.

• SID - Enter the SID for your Oracle database. The default SID is ORCL.

Service Name

To connect to Oracle resources by using a service name, specify the following settings:

• User - Enter the username that you use to authenticate to the Oracle database.

• Password - Enter the password that you use to authenticate to the Oracle database.

• Server - Enter the host name or IP address of the server that hosts the Oracle database. The default server is localhost.

• Port - Enter the port number for the Oracle server. The default port is 1521.

• Service - Enter the service name for your Oracle database. The default SID is ORCL.

  To obtain the service name, submit the following query from the Oracle SQL*PLUS command line:

  global_name (select * from global_name)

Data Source

To connect to Oracle resources by using a data source, specify the following settings:

• User - Enter the username that you use to authenticate to the Oracle database.

• Password - Enter the password that you use to authenticate to the Oracle database.

• Data Source - Enter the data source for your Oracle database.

Complete Your Connection

To complete your connection:

1. 高度な設定タブで接続の高度な設定を定義します。（ただし、ほとんどの場合これらの設定は必要ありません。）

2. 作成およびテストをクリックして接続を作成します。