Oracle Environment Preparation (LogMiner)

  • Last update: September 24, 2024
  • Overview

    Version

    FineDataLink Version
    Functional Change

    /

    /

    4.1.2

    Supported the multi-tenant container database as the source end of pipeline tasks.

    By enabling Change Data Capture (CDC) for the data source, you can synchronize real-time data from data sources and the physical delete of data tables using the Data Pipeline function of FineDataLink.

    Data Pipeline supports two CDC modes for Oracle: XStream and LogMiner. This document primarily introduces how to enable the LogMiner-based CDC for Oracle databases. Additionally, the CDC configuration differs depending on whether the Oracle database is a Container Database (CDB) or a non-Container Database (non-CDB).

    Configure the database following this document before executing a synchronization task.

    iconNote:
    Archived logs consume a significant amount of disk space. If the disk space is full, it can affect business operations. Therefore, you are advised to regularly clean up expired archived logs.

    Checking the Current Database Version in Use

    Check if the version is supported by Data Pipeline.

    Data Pipeline supports data reading from Oracle databases of the following versions.

    Database Type
    Supported Database Version

    Oracle

    Oracle 10g, 11g, 12c, 18c, 19c, and Oracle Real Application Clusters (RAC)

    Log in to the database as the database admin.

    For example, for Oracle installed on a Windows system, enter sqlplus / as sysdba in the command prompt to go to the database operation page.

    You can also check the current database version.

    Operations on the Source-End CDB

    Enabling Archive Log

    1. Log in to the database as the database admin.

    2. Use the command to check the logging mode of the database:

    select log_mode from v$database;

    If the result returned is ARCHIVELOG, proceed to the section "Enabling Full Supplemental Logging."

     

    If the result returned is NOARCHIVELOG, continue with the following steps:

    • Log in to the server: sqlplus / as sysdba

    • Shut down the database: shutdown immediate;

    • Start and mount the database: startup mount;

    • Enable archiving and open the database:

    alter database archivelog;
    alter database open;

    Enabling Supplemental Logging

    Enabling Method for Oracle 10g and 11g

    Use the following command:

    alter database add supplemental log data;
    alter system switch logfile;
    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

    Special Operation on Oracle 12c, 18c, 19c, and RAC

    Use the following command to check if supplemental logging is enabled:

    SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_all FROM v$database;

    If the return values in three columns are all Yes or Implicit, both identification key logging and full supplemental logging are enabled. For details about database account information, see the section "Assigning Database Account Privileges."

    If the return values only in the first two columns are Yes or Implicit, only identification key logging is enabled. To enable full supplemental logging, see the section "Enabling Full Supplemental Logging."

    Enabling Method for CDB in Oracle 12c, 18c, 19c, and RAC

    To enable identification key logging for a single table, use the following command:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
    ALTER TABLE Schema_name.Table_name ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

    To enable identification key logging for all tables, use the following command:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

    Enabling Full Supplemental Logging

    To enable full supplemental logging for a single table, use the following command:

    ALTER TABLE Schema_name.Table_name ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

    To enable full supplemental logging for all tables, use the following command:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

    Submit the configuration changes:

    ALTER SYSTEM SWITCH LOGFILE;

    You can then use the following command to check if supplemental logging is enabled for a specific table:

    SELECT OWNER, TABLE_NAME, LOG_GROUP_NAME, ALWAYS, LOGGING_COLUMNS
    FROM DBA_LOG_GROUPS
    WHERE TABLE_NAME = 'Your_Table_Name';

    Assigning Database Account Privileges

    You need to create an account that can access the data source, which will be used in pipeline tasks to access the data source for data extraction and writing.

    For details about account creation, see Creating an Oracle Account.

    Operations on Oracle 10g and 11g

    Use the following command to create a user account and assign privileges:

    CREATE USER Username IDENTIFIED BY Password;
    GRANT create session, alter session, execute_catalog_role, select any dictionary, select any transaction, select any table, unlimited tablespace to Username;

    WeChatWorkScreenshot_c4a06ec3-d4b7-4c4d-95db-15528db53118.png

    At this time, you can use this user account to establish a data connection. For example, if the user Roxy is created, the data connection configuration page is shown in the following figure.

    2024-09-24_14-25-37.png

    Operations on Oracle 12c, 18c, and 19c Standard Edition

    Create a user and assign necessary privileges in Oracle 12c, 18c, and 19c (standard edition):

    1. Log in to the database as the database admin.

    2. Create a common user.

    CREATE USER Username IDENTIFIED BY Password;GRANT create session, alter session, select any dictionary, select any transaction,select any table,logmining, execute_catalog_role TO Username;

    Depending on the required privileges for the table, repeat the last command to grant the SELECT privilege.

    Checking User Privileges

    iconNote:
    No LOGMINING privilege is required for Oracle 11g and earlier versions.

    Use the following commands to check if the current user has the LOGMINER privilege:

    WITH required_sys_privileges AS (
        SELECT column_value AS PRIVILEGE
        FROM TABLE (
            sys.odcivarchar2list(
                'SELECT ANY TRANSACTION',
                'CREATE SESSION',
                'SELECT ANY DICTIONARY',
                'SELECT ANY TABLE',
                'FLASHBACK ANY TABLE',
                'LOGMINING'
                )
            )
    ),
        required_role_privileges AS (
            SELECT column_value AS ROLE_NAME
             FROM TABLE (
                 sys.odcivarchar2list(
                         'EXECUTE_CATALOG_ROLE',
                         'RESOURCE'
                     )
                 )
         ),
         required_table_privileges AS (
             SELECT column_value AS PRIVILEGE
             FROM TABLE (
                 sys.odcivarchar2list(
                         'DBMS_FLASHBACK',
                         'DBMS_LOGMNR',
                         'DBMS_LOGMNR_D'
                     )
                 )
         )
    SELECT r.PRIVILEGE,
           NVL2(p.USERNAME, 'OK''REQUIRED') AS PRIVILEGE_STATUS
    FROM required_sys_privileges r
             LEFT JOIN user_sys_privs p
                       ON r.PRIVILEGE = p.PRIVILEGE
    UNION
    SELECT r.ROLE_NAME,
           NVL2(p.USERNAME, 'OK''REQUIRED') AS PRIVILEGE_STATUS
    FROM required_role_privileges r
             LEFT JOIN user_role_privs p
                       ON r.ROLE_NAME = p.GRANTED_ROLE
    UNION
    SELECT r.PRIVILEGE,
           NVL2(p.GRANTEE, 'OK''REQUIRED') AS PRIVILEGE_STATUS
    FROM required_table_privileges r
             LEFT JOIN user_tab_privs p
                       ON r.PRIVILEGE = p.TABLE_NAME;

    Expand

    If the returned result is Required, the LOGMINER privilege is not enabled; if it is OK, the privilege is enabled.

    Enable the missing privilege.

    Operations on the Source-End PDB

    If the source-end database is a pluggable database (PDB), you cannot directly read the redo logs of the database, and you need to use the CDB to read them.

    Checking if Multi-Tenancy is Enabled for the Database

    1. Log in to the database as the database admin and use the following command to check if multi-tenancy is enabled for the database:

    SELECT CDB FROM V$DATABASE;

    Checking if the Root Container is in Use

    SELECT SYS_CONTEXT('USERENV''CON_NAME') FROM DUAL;

    Enabling Database Privileges

    For details about enabling database logging privileges, see sections "Enabling Archive Log,""Enabling Supplemental Logging" and "Enabling Full Supplemental Logging."

    Checking if the User is a Common User with the SET CONTAINER Privilege

    Starting All PDBs

    Use the command in SQL*Plus to check the current PDB status, as shown in the following figure.

    show pdbs;

    You can also use the following command on the database query page:

    SELECT name, open_mode
    FROM   v$pdbs
    ORDER BY name;

    Start PDBORCL and ORCLPDB using the following command if they are not started:

    iconNote:
    The PDB names need to be modified according to the actual database situation.
    ALTER PLUGGABLE DATABASE ORCLPDB1 OPEN;
    ALTER PLUGGABLE DATABASE PDBORCL OPEN;

    Check the PDB status again to ensure all PDBs are open.

    Creating an Account

    iconNote:
    When a common user is created in the CDB, the same user will also be created in the PDBs. If you omit container=all in the GRANT command when granting privileges to the CDB user, the user only has the granted privileges on the CDB. For the user to access the PDB, you must switch to the PDB and grant the privileges. If container=all is included in the GRANT command, the user has the granted privileges on both the CDB and the PDB.

    1. Create a user account.

    CREATE C## IDENTIFIED BY  CONTAINER=all;
    iconNote:
    When the Oracle database is in a multi-tenant mode, prefix the username with C##.

    2. Grant Privileges

    GRANT create session, alter session, select any dictionary, select any transaction,select any table,logmining, execute_catalog_role,set container TO "C##Username" container=all;

    This step only applies to the new account. For details about enabling database privileges for existing accounts, see the section "Enabling Database Privileges."

    ALTER USER "C##Username" QUOTA UNLIMITED ON "USERS";
    SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_all FROM v$database;
    GRANT create session, alter session, select any dictionary, select any transaction,select any table,logmining, execute_catalog_role TO "C##Username";

    Grant the V_$PDBS privilege to the user, as shown in the following figure.

    alter user "C##Username" set container_data=all for sys.v_$pdbs container = current;

    Check the privileges of the new account:

    SELECT USERNAME, OBJECT_NAME FROM CDB_CONTAINER_DATA WHERE USERNAME = 'C##Username' AND OBJECT_NAME = 'V_$PDBS';

    You can view the corresponding V_$PDBS privilege, as shown in the following figure.

    Log in to the Oracle server remotely, switch to SQL*Plus mode, and execute the privilege-granting command:

    grant restricted session TO "C##Username" container=all;

    Execute the command to grant table creation privileges:

    GRANT CREATE TABLE TO Username CONTAINER=ALL;

    After executing, check if the table creation privilege is granted:

    SELECT PRIVILEGE FROM USER_SYS_PRIVS WHERE PRIVILEGE = 'CREATE TABLE';

    Now, the account can create tables normally.

    Subsequent Operation

    After you configure the database, you can configure the data source in FineDataLink.

    Configure the data connection to source and target databases in FineDataLink before configuring pipeline tasks for real-time synchronization. This will allow you to control synchronization tasks by selecting the data source name during the task configuration process. For details, see Data Source Creation and Management.

    2024-09-24_14-20-28.png

    iconNote:
    Note that if the source-end database is a PDB, the selected database during data connection creation must be a PDB, as shown in the following figure.

    Set up the pipeline task. For details, see Pipeline Task Configuration.

    附件列表


    主题: Data Pipeline
    • Helpful
    • Not helpful
    • Only read

    滑鼠選中內容,快速回饋問題

    滑鼠選中存在疑惑的內容,即可快速回饋問題,我們將會跟進處理。

    不再提示

    8s后關閉

    Get
    Help
    Online Support
    Professional technical support is provided to quickly help you solve problems.
    Online support is available from 9:00-12:00 and 13:30-17:30 on weekdays.
    Page Feedback
    You can provide suggestions and feedback for the current web page.
    Pre-Sales Consultation
    Business Consultation
    Business: international@fanruan.com
    Support: support@fanruan.com
    Page Feedback
    *Problem Type
    Cannot be empty
    Problem Description
    0/1000
    Cannot be empty

    Submitted successfully

    Network busy