Movatterモバイル変換

[0]ホーム
URL:

✨ As of November 2023, Arcion has become a part of Databricks.Learn more here ✨
Oracle traditional database

Oracle Source Setup Guide#

Follow the instructions below to set up a replication pipeline from your Oracle database to your destination using Arcion’s Oracle connector.

Note: The first five steps (I-V) describe how to prepare the Oracle Database for replication. You must execute these steps in an Oracle client. The remaining steps (VI-VIII) allow you to configure Replicant.

I. Obtain the JDBC Driver for Oracle#

Replicant requires the JDBC driver for Oracle as a dependency. To obtain the appropriate driver, follow the steps below:

II. Set up Oracle User#

  1. Create a new user for Replicant with the following command:

    CREATEUSER<USERNAME> IDENTIFIEDBY<PASSWORD>DEFAULT TABLESPACE<user-defined-tablesace>QUOTA unlimitedon<user-defined-tablespace>TEMPORARY TABLESPACE TEMP;

  2. Provide the create session permission:

    GRANTCREATESESSIONTO<USERNAME>;

  3. Grant the select permission for all the tables that are part of the replication:

    GRANTSELECTON<TABLENAME>TO<USERNAME>;

    OR

    The select permission can be granted on all of the tables

    GRANTSELECTANYTABLETO<USERNAME>;

III. Set up Change Data Capture (CDC)#

Enable ARCHIVELOG mode#

To use log-based CDC, the Oracle database must be in ARCHIVELOG mode. To check what mode the database is in, use theARCHIVE LOG LIST command. To set the database in ARCHIVELOG mode, use the following commands:

SHUTDOWNIMMEDIATESTARTUP MOUNTALTERDATABASE ARCHIVELOGALTERDATABASEOPEN

Set permissions#

  1. Once the database is in ARCHIVELOG mode, grant the EXECUTE_CATALOG_ROLE role to use the DBMS_LOGMNR package:

    GRANT EXECUTE_CATALOG_ROLETO<USERNAME>

  2. Provide the following permissions to allow Replicant to access the START_LOGMNR procedure:

    For Oracle version 11g, execute the following command:

    GRANTSELECTANYTRANSACTIONTO<USERNAME>;

    For Oracle versions 12C and above, execute the following command:

    GRANT LOGMININGTO<USERNAME>;

  3. You also need to provide the following permission to allow Replicant to accessv_$logmnr_contents:

    For Oracle versions below 19c, grant the following permissions:

    GRANTSELECTON v_$logmnr_contentsTO<USERNAME>;GRANTSELECTON gv_$archived_logto<USERNAME>;

    For Oracle versions 19C and above, Replicant requires additional access to v_$logfile.:

    GRANTSELECTON v_$logmnr_contentsTO<USERNAME>;GRANTSELECTON gv_$archived_logto<USERNAME>;GRANTSELECTON v_$logfileTO<USERNAME>;

Enable logs#

You have to enable either primary key or all column logging at either the database level or the table level.

If you use table level logging, you must enable it for the CDC heartbeat table as well.

Database level Supplemental logging#

Table level supplemental logging#

Only enable eitherPRIMARY KEY logging orALL column logging, not both.

IV. Set up Global Permissions#

Quick Definitions

  1. Provide the following privilege for one time access:

    GRANTSELECTON DBA_SEGMENTSTO<USERNAME>;

  2. Provide the following continuous access permissions; these are necessary during snapshot as well as continious real-time replication:

    GRANTSELECTON gv_$databaseTO<USERNAME>;GRANTSELECTON gv_$transactionTO<USERNAME>;GRANTSELECTON gv_$sessionTO<USERNAME>;--Not required for replicant release 20.8.13.7 and above

  3. Grant the following continuous access permission for the tables involved in Replication:

    GRANT FLASHBACKON<TABLE_NAME>TO<USERNAME>;

    OR

    Enable Flashback for all tables

    GRANT FLASHBACKANYTABLETO<USERNAME>;

  4. Provide access to the below system views for schema migration:

    GRANTSELECTON ALL_TABLESTO<USERNAME>;GRANTSELECTON ALL_VIEWSTO<USERNAME>;GRANTSELECTON ALL_CONSTRAINTSTO<USERNAME>;GRANTSELECTON ALL_CONS_COLUMNSTO<USERNAME>;GRANTSELECTON ALL_PART_TABLESTO<USERNAME>;GRANTSELECTON ALL_PART_KEY_COLUMNSTO<USERNAME>;GRANTSELECTON ALL_TAB_COLUMNSTO<USERNAME>;GRANTSELECTON SYS.ALL_INDEXESTO<USERNAME>;GRANTSELECTON SYS.ALL_IND_COLUMNSTO<USERNAME>;GRANTSELECTON SYS.ALL_IND_EXPRESSIONSTO<USERNAME>;

V. Set up Connection Configuration#

  1. From$REPLICANT_HOME, navigate to the connection configuration file:

    vi conf/conn/oracle.yaml

  2. You can store your connection credentials in a secrets management service and tell Replicant to retrieve the credentials. For more information, seeSecrets management.

    Otherwise, you can put your credentials like usernames and passwords in plain form like the sample below:

    type:ORACLEhost:localhost#Replace localhost with your oracle host nameport:1521#Replace the default port number 1521 if neededservice-name:IO#Replace IO with the service name of your Oracle Listenerusername:'REPLICANT'#Replace REPLICANT with your username to connect to Oraclepassword:'Replicant#123'#Replace Replicant#123 with the your user's passwordmax-connections:30#Maximum number of connections replicant can open in Oraclecontinuous-log-mining:true
    If you’re using Oracle 19c, please setcontinuous-log-mining tofalse. For more information, seeDesupport of theCONTINUOUS_MINE feature of LogMiner.

VI. Set up Filter Configuration#

  1. From$REPLICANT_HOME, navigate to the filter configuration file:

    vi filter/oracle_filter.yaml

  2. In accordance to your replication needs, specify the schema(s)/table(s) which is to be replicated. Use the format explained below:

    allow:#In this example, data of object type Table in the schema REPLICANT will be replicatedschema:"REPLICANT"types: [TABLE]#From the shemca REPLCIANT, only the Orders, Customers, and Returns tables will be replicated.#Note: Unless specified, all tables in the schema will be replicatedallow:Orders:#Within Orders, only the US and AUS columns will be replicatedallow: ["US, AUS"]Lineitem:#All columns in the table Lineitem  will be replicated without any predicatesCustomers:#Within Customers, only the product and service columns will be replicated as long as they meet the condition C_CUSTKEY < 5000allow: ["product","service"]conditions:"C_CUSTKEY < 5000"

    Below is the filter file template you must follow:

    allow:schema:<your_schema_name>types:<your_object_type>allow:your_table_name_1:allow: ["your_column_name"]conditions:"your_condition"your_table_name_2:your_table_name_3:allow: ["your_column_name"]conditions:"your_condition"

For a detailed explanation of configuration parameters in the filter file, read:Filter Reference

VII. Set up Extractor Configuration#

For real-time replication, you must create a heartbeat table in the source Oracle.

  1. Create a heartbeat table in the schema you are going to replicate with the following DDL:

    CREATETABLE"<schema>"."replicate_io_cdc_heartbeat"("timestamp" NUMBERNOTNULL,PRIMARYKEY("timestamp"));

  2. GrantINSERT,UPDATE, andDELETE privileges to the user configured for replication

  3. From$REPLICANT_HOME, navigate to the extractor configuration file:

    vi conf/src/oracle.yaml

  4. The configuration file has two parts:

    • Parameters related to snapshot mode.
    • Parameters related to realtime mode.

    Parameters related to snapshot mode#

    The following parameters are specific to Oracle as source:

    • fetch-create-sql: This option can be used to apply exact create SQL on source to target.

      This parameter is available only for Oracle->Oracle pipeline.

    • fetch-create-sql-no-constraints: This option is used to split create table SQL and Primary/Unique/Foreign Key constraints as different SQLs. So as part of schema migration we create tables without constraints and after the snapshot is complete the constraints are applied. Application of constraints post snapshot is configured by applier config init-constraint-post-snapshot.

      This parameter is available only for Oracle->Oracle pipeline.

    • serialize-fetch-createSql: This option is used to fetch create SQL in serialized manner after fetching table schema.

    • serialize-fetch-create-sql-no-constraints: This option is used to fetch SQL of Primary/Unique/Foreign Key constraints in serialized manner.

      This parameter is available only for Oracle->Oracle pipeline.

    • native-load: With this parameter set, Replicant uses the Oracle Data Pump Export (expdp) utility to load table data instead of JDBC. This enables Replicant to efficiently handle large-scale data. For more information, seeOracle Native Export.

    The following configuration parameters are available undernative-load:

    • enable:true orfalse. Whether to enablenative-load.
    • stage-type: The type of staging area. Allowed values areSHARED_FS andASM. The staging area can either be a shared directory or Oracle ASM.
    • directory: The Oracle directory object corresponding to thestage-type. For more information, seeCreate directory object in Source and Target Oracle.
    • path: Full path to the NFS (Network File System) representing the directory shared between Replicant and Oracle.

    The following is a sample configuration for snapshot mode:

    snapshot:threads:16fetch-size-rows:10_000verify-row-count:false_fetch-exact-row-count:true_traceDBTasks:true#  inf-number-behavior: EXCEPTION   # EXCEPTION, ROUND, CONVERT_TO_NULL#  flashback-query: true#  parallel-query: true#  fetch-user-roles: true#   native-load:#     enable: false#     stage-type: SHARED_FS#     directory: SHARED_STAGE#     shared-path: FULL_PATH_TO_NFS #full path to the NFS shared directory#   per-table-config:#   - schema: tpch#     tables:#       lineitem1:#         row-identifier-key: [ORDERKEY, LINENUMBER]#         extraction-priority: 1 #Higher value is higher priority. Both positive and negative values are allowed. Default priority is 0 if unspecified.#       lineitem1:#         row-identifier-key: [ORDERKEY, LINENUMBER]#        products:#          per-partition-config:#          - partition-name: SYS_P461#            row-count: 0#          - partition-name: SYS_P462#            row-count: 0#          - partition-name: SYS_P463#            row-count: 1#          - partition-name: SYS_P464#            row-count: 3#        part:#          row-count: 2000
    • Supplyingsplit-key in theper-table-configsection is not required (and not supported) for Oracle source.
    • We strongly recommend that you specifyrow-identifier-key inper-table-config section for tables not having PK/UK constraints defined on the source Oracle system.

    Parameters related to realtime mode#

    If you want to operate in realtime mode, you can use therealtime section to specify your configuration. The following Oracle specific parameters are available:

    • block-ddl-transaction[v20.09.14.3]: This option blocks fetching logs for DDL operation from Oracle.

    • use-current-scn[v20.09.14.8]: Instart-postion section, this option allows using currentscn value for to start reading realtime operations.

    • start-scn[v20.09.14.3]: Instart-postion section this option allows using user specifiedscn value for to start reading realtime operations.

    • inter-source-latency-s[v20.10.07.16]: In thestart-position section this config option in seconds represents the lag between primary and standby Oracle in case the source-failover feature is enabled.

    • log-miner-dict-file[v21.09.17.6]: If specified, this file will be used as the dictionary for log mining instead of using the online dictionary. The file must be accessible by Oracle.

    • oldest-active-txn-window-hr[v22.07.19.3]: Specifies the time period in hours up to which Replicant should fetch the oldest transaction SCN.

      Default: By default, this parameter is set to24.

    The following is a sample configuration for realtime mode:

    realtime:threads:4_traceDBTasks:true#fetch-size-rows: 0heartbeat:enable:trueschema:"tpch"interval-ms:10000table-name:replicate_io_cdc_heartbeatstart-position:start-scn:2362927

    Support for DDL replication#

    Replicantsupports DDL replication for real-time Oracle source. For more information,contact us.

For a detailed explanation of configuration parameters in the Extractor file, seeExtractor Reference.

[8]ページ先頭
©2009-2026 Movatter.jp