Oracle数据源

Oracle数据源为您提供读取和写入Oracle双向通道的功能,方便您后续可以通过向导模式和脚本模式配置数据同步任务。本文为您介绍DataWorks的Oracle数据同步能力支持情况。

支持的版本

离线读写

版本

离线读(Oracle Reader)

离线写(Oracle Writer)

Oracle 11.2 or 11gR2

支持

支持

Oracle 12.1 or 12cR1

支持

支持

Oracle 12.2 or 12cR2

支持(新版本特性不支持)

支持(新版本特性不支持)

Oracle 18.3

支持(新版本特性不支持)

支持(新版本特性不支持)

Oracle 19.x

支持(新版本特性不支持)

支持(新版本特性不支持)

Oracle 21.1

支持(新版本特性不支持)

支持(新版本特性不支持)

重要

Oracle Writer插件使用ojdbc6-12.1.1.jar驱动。

实时读

  • 支持:

    11g R2 以上12c non cdb18c non cdb19c non cdb19c cdb版本数据库。

  • 不支持:

    12c cdb18c cdb版本数据库。

说明

数据库容器CDB(Container Database)是Oracle 12c及之后版本的数据库新特性,用于承载多个可插拔数据库PDB(Pluggable Database)。

使用限制

  • 当前数据集成同步数据时,仅支持UTF8、AL32UTF8、AL16UTF16及ZHS16GBK编码格式。

  • Oracle单实例,当天实时同步最大支持同步500G增量数据。

  • DataWorks的数据集成实时同步Oracle数据是基于Oracle LogMiner日志分析工具实现的,Oracle仅支持在主库中为主库或备库开启补充日志。

  • 离线同步支持读取视图表。

支持的字段类型

字段类型

离线读(Oracle Reader)

离线写(Oracle Writer)

实时读

NUMBER

支持

支持

支持

BINARY FLOAT

支持

支持

支持

BINARY DOUBLE

支持

支持

支持

CHAR

支持

支持

支持

NCHAR

支持

支持

支持

VARCHAR2

支持

支持

支持

NVARCHAR2

支持

支持

支持

DATE

支持

支持

支持

TIMESTAMP

支持

支持

支持

TIMESTAMP WITH TIME ZONE

支持

支持

不支持

TIMESTAMP WITH LOCAL TIME ZONE

支持

支持

不支持

CLOB

支持

支持

支持

BLOB

支持

支持

支持

RAW

支持

支持

支持

ROWID

不支持

不支持

支持

UROWID

不支持

不支持

支持

FLOAT

支持

支持

支持

INTERVAL DAY TO SECOND

不支持

不支持

支持

INTERVAL YEAR TO MONTH

不支持

不支持

支持

BFILE

不支持

不支持

不支持

LONG

不支持

不支持

不支持

LONG RAW

不支持

不支持

不支持

NCLOB

支持

支持

不支持

STRUCT

支持

支持

不支持

User-Defined Types

不支持

不支持

不支持

AnyType

不支持

不支持

不支持

AnyData

不支持

不支持

不支持

AnyDataSet

不支持

不支持

不支持

XmlType

不支持

不支持

不支持

Spatial Types

不支持

不支持

不支持

Media Types

不支持

不支持

不支持

Oracle Reader针对Oracle类型的转换列表,如下所示。

类型分类

Oracle数据类型

整数类

NUMBER、RAWID、INTEGER、INT和SMALLINT

浮点类

NUMERIC、DECIMAL、FLOAT、DOUBLE PRECISIOON和REAL

字符串类

LONG、CHAR、NCHAR、VARCHAR、VARCHAR2、NVARCHAR2、CLOB、NCLOB、CHARACTER、CHARACTER VARYING、CHAR VARYING、NATIONAL CHARACTER、NATIONAL CHAR、NATIONAL CHARACTER VARYING、NATIONAL CHAR VARYING和NCHAR VARYING

日期时间类

TIMESTAMP和DATE

布尔型

BIT和BOOL

二进制类

BLOB、BFILE、RAW和LONG RAW

数据同步前准备:Oracle环境准备

在DataWorks上进行数据同步前,您需要参考本文提前在Oracle侧进行数据同步环境准备,以便在DataWorks上进行Oracle数据同步任务配置与执行时服务正常。以下为您介绍Oracle同步前的相关环境准备。

准备工作1:确认Oracle数据库版本

不同同步场景下支持的Oracle数据库版本不一致,同步前您需要检查Oracle数据库版本是否为支持的版本。

  1. 您可以通过如下任意语句查看Oracle数据库的版本。

    • 语句一:

      select * from v$version;
    • 语句二:

      select version from v$instance;
  2. 如果查看到的Oracle数据库版本为12c18c,则您需要使用如下语句进一步确认该数据库是否为cdb类型的数据库。DataWorks数据集成实时同步任务暂不支持使用12c18c版本的cdb类型的Oracle数据库。

    select name,cdb,open_mode,con_id from v$database;

准备工作2:创建账号并配置账号权限

您需要规划一个数据库的登录账户用于后续执行操作,此账号需要拥有Oracle的相关操作权限。

  1. 创建账号。操作详情请参见创建Oracle账号

  2. 配置权限。

    您可以参考以下命令为账号添加相关权限。如下执行语句在实际使用时,请替换'同步账号'为上述创建的账号。

    grant create session to '同步账号';  //授权同步账号登录数据库。
    grant connect to '同步账号';  //授权同步账号连接数据库。
    grant select on nls_database_parameters to '同步账号';  //授权同步账号查询数据库的nls_database_parameters系统配置。
    grant select on all_users to '同步账号';  //授权同步账号查询数据库中的所有用户。
    grant select on all_objects to '同步账号';  //授权同步账号查询数据库中的所有对象。
    grant select on DBA_MVIEWS to '同步账号';  //授权同步账号查看数据库的物化视图。
    grant select on DBA_MVIEW_LOGS to '同步账号';  //授权同步账号查看数据库的物化视图日志。
    grant select on DBA_CONSTRAINTS to '同步账号';  //授权同步账号查看数据库所有表的约束信息。
    grant select on DBA_CONS_COLUMNS to '同步账号';  //授权同步账号查看数据库中所有表指定约束中所有列的相关信息。
    grant select on all_tab_cols to '同步账号';  //授权同步账号查看数据库中表、视图和集群中列的相关信息。
    grant select on sys.obj$ to '同步账号';  //授权同步账号查看数据库中的对象。sys.obj$表是Oracle字典表中的对象基础表,存放Oracle的所有对象。
    grant select on SYS.COL$ to '同步账号';  //授权同步账号查看数据库表中列的定义信息。SYS.COL$用于保存表中列的定义信息。
    grant select on sys.USER$ to '同步账号';  //授权同步账号查看数据库的系统表。sys.USER$是用户会话的默认服务。
    grant select on sys.cdef$ to '同步账号';  //授权同步账号查看数据库的系统表。
    grant select on sys.con$ to '同步账号';  //授权同步账号查看数据库的约束信息。sys.con$记录了Oracle的相关约束信息。
    grant select on all_indexes to '同步账号';  //授权同步账号查看数据库的所有索引。
    grant select on v_$database to '同步账号';  //授权同步账号查看数据库的v_$database视图。
    grant select on V_$ARCHIVE_DEST to '同步账号';  //授权同步账号查看数据库的V_$ARCHIVE_DEST视图。
    grant select on v_$log to '同步账号';  //授权同步账号查看数据库的v_$log视图。v_$log用于显示控制文件中的日志文件信息。
    grant select on v_$logfile to '同步账号';  //授权同步账号查看数据库的v_$logfile视图。v_$logfile包含有关Redo日志文件的信息。
    grant select on v_$archived_log to '同步账号';  //授权同步账号查看数据库的v$archived_log视图。v$archived_log包含有关归档日志的相关信息。
    grant select on V_$LOGMNR_CONTENTS to '同步账号';  //授权同步账号查看数据库的V_$LOGMNR_CONTENTS视图。
    grant select on DUAL to '同步账号';   //授权同步账号查看数据库的DUAL表。DUAL是用来构成select语法规则的虚拟表,Oracle的中DUAL中仅保留一条记录。
    grant select on v_$parameter to '同步账号';  //授权同步账号查看数据库的v_$parameter视图。v$parameter是Oracle的动态字典表,保存了数据库参数的设置值。
    grant select any transaction to '同步账号';  //授权同步账号查看数据库的任意事务。
    grant execute on SYS.DBMS_LOGMNR to '同步账号';  //授权同步账号使用数据库的Logmnr工具。Logmnr工具可以帮助您分析事务,并找回丢失的数据。
    grant alter session to '同步账号';  //授权同步账号修改数据库的连接。
    grant select on dba_objects to '同步账号';  //授权同步账号查看数据库的所有对象。
    grant select on v_$standby_log to '同步账号';  //授权同步账号查看数据库的v_$standby_log视图。v_$standby_log包含备用库的归档日志。
    grant select on v_$ARCHIVE_GAP to '同步账号';  //授权同步账号查询缺失的归档日志。

    如果您涉及使用离线全量同步数据,还需要执行如下命令,授权同步账号所有表的查询权限。

    grant select any table to '同步账号'; 

    Oracle 12c及之后的版本需要执行如下命令,授权同步账号可以进行日志挖掘。Oracle 12c之前的版本,内置日志挖掘功能,无需执行该命令。

    grant LOGMINING TO '同步账号';

准备工作3:开启归档日志、补充日志并切换Redo日志文件

  1. 开启归档日志,SQL语句如下。

    shutdown immediate;
    startup mount;
    alter database archivelog;
    alter database open;
  2. 开启补充日志。

    您可以根据需要选择开启合适的补充日志,SQL语句如下。

    alter database add supplemental log data(primary key) columns; //为数据库的主键列开启补充日志。
    alter database add supplemental log data(unique) columns; //为数据库的唯一索引列开启补充日志。
  3. 切换Redo日志文件。

    开启补充日志后,您需要多次(一般建议执行5次)执行如下命令,切换Redo日志文件。

    alter system switch logfile;
    说明

    多次执行上述命令切换Redo日志文件,是保证当前日志文件被写满后可以切换至下一个日志文件。使执行过的操作记录不会丢失,便于后续恢复数据。

准备工作4:检查数据库的字符编码

您需要在当前使用的数据库中,执行如下命令检查数据库的字符编码。

select * from v$nls_parameters where PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');
  • v$nls_parameters用于存放数据库参数的设置值。

  • NLS_CHARACTERSETNLS_NCHAR_CHARACTERSET为数据库字符集和国家字符集,表明Oracle中两大类字符型数据的存储类型。

说明

前数据集成同步数据时,仅支持UTF8、AL32UTF8、AL16UTF16及ZHS16GBK编码格式。如果数据库中包含不支持的字符编码,请进行修改后再执行数据同步。

准备工作5:检查数据库表的数据类型

您可以使用查看表的SQL相关语句(SELECT)查询数据库表的数据类型。示例查看'tablename'表数据类型的语句如下。

select COLUMN_NAME,DATA_TYPE from all_tab_columns where TABLE_NAME='tablename'; 
  • COLUMN_NAME:表的列名称。

  • DATA_TYPE:对应列的数据类型。

  • all_tab_columns:存放数据库表所有列相关信息的视图。

  • TABLE_NAME:需要查询的目标表的名称。执行上述语句时,请替换'tablename'为实际需要查看的表名称。

您也可以执行select * from 'tablename';,查询目标表的所有信息,获取数据类型。

创建数据源

在进行数据同步任务开发时,您需要在DataWorks上创建一个对应的数据源,操作流程请参见创建并管理数据源详细的配置参数解释可在配置界面查看对应参数的文案提示

数据同步任务开发:Oracle同步流程引导

数据同步任务的配置入口和通用配置流程可参见下文的配置指导。

单表离线同步任务配置指导

单表实时同步任务配置指导

操作流程请参见DataStudio侧实时同步任务配置

整库离线、整库(实时)全增量、整库(实时)分库分表等整库级别同步配置指导

操作流程请参见数据集成侧同步任务配置

常见问题

  • 实时同步Oracle、PolarDB、MySQL任务重复报错

  • 主备同步数据恢复问题

    主备同步问题指Oracle使用主从灾备,当主库报错切换至备库后,备库从主库不断地通过binlog恢复数据。由于主备数据同步存在一定的时间差,在网络延迟等特定情况下,会导致备库同步恢复的数据与主库有较大差别,从备库同步的数据不是一份当前时间的完整镜像。

  • 一致性约束

    Oracle在数据存储划分中属于RDBMS系统,对外可以提供强一致性数据查询接口。例如,在一次同步任务启动运行的过程中,当该库存在其它数据写入方写入数据时,由于数据库本身的快照特性,Oracle Reader不会获取到写入的新数据。

    上述是在Oracle Reader单线程模型下实现数据同步的一致性。Oracle Reader根据您配置的信息并发抽取数据,则不能严格保证数据一致性。

    当Oracle Reader根据splitPk进行数据切分后,会先后启动多个并发任务完成数据同步。多个并发任务相互之间不属于同一个读事务,同时多个并发任务存在时间间隔。因此该数据并不是完整的、一致的数据快照信息。

    针对多线程的一致性快照需求,目前在技术上无法实现,只能从工程角度解决。工程化的方式存在取舍,在此提供以下解决思路,您可以根据自身情况进行选择。

    • 使用单线程同步,即不再进行数据切片。缺点是速度比较慢,但是能够很好保证一致性。

    • 关闭其它数据写入方,保证当前数据为静态数据。例如,锁表、关闭备库同步等。缺点是可能影响在线业务。

  • 数据库编码问题

    Oracle Reader底层使用JDBC进行数据抽取,JDBC天然适配各类编码,并在底层进行了编码转换。因此Oracle Reader无需您指定编码,可以自动获取编码并转码。

  • 增量数据同步的方式

    Oracle Reader使用JDBC SELECT语句完成数据抽取工作,因此您可以使用SELECT…WHERE…进行增量数据抽取,方式如下:

    • 数据库在线应用写入数据库时,填充modify字段为更改时间戳,包括新增、更新、删除(逻辑删除)。对于该类应用,Oracle Reader只需要where条件后跟上一同步阶段时间戳即可。

    • 对于新增流水型数据,Oracle Reader在where条件后跟上一阶段最大自增ID即可。

    对于业务上无字段区分新增、修改数据的情况,Oracle Reader无法进行增量数据同步,只能同步全量数据。

  • SQL安全性

    Oracle Reader为您提供querySql功能,您可以自行实现SELECT抽取语句。Oracle Reader本身对querySql不进行任何安全性校验。

附录:脚本Demo与参数说明

离线任务脚本配置方式

如果您配置离线任务时使用脚本模式的方式进行配置,您需要按照统一的脚本格式要求,在任务脚本中编写相应的参数,详情请参见通过脚本模式配置离线同步任务,以下为您介绍脚本模式下数据源的参数配置详情。

Reader脚本Demo

{
    "type": "job",
    "version": "2.0",
    "steps": [
        {
            "stepType": "oracle",
            "parameter": {
                "selectedDatabase": "AUTOTEST",
                "indexes": [],
                "datasource": "oracle_test",
                "envType": 0,
                "useSpecialSecret": true,
                "column": [
                    "id"
                ],
                "where": "",
                "splitPk": "id",
                "encoding": "UTF-8",
                "table": "AUTOTEST.table01"
            },
            "name": "Reader",
            "category": "reader"
        },
        {
            "stepType": "odps",
            "parameter": {
            },
            "name": "Writer",
            "category": "writer"
        },
        {
            "name": "Processor",
            "stepType": null,
            "category": "processor",
            "copies": 1,
            "parameter": {
                "nodes": [],
                "edges": [],
                "groups": [],
                "version": "2.0"
            }
        }
    ],
    "setting": {
        "executeMode": null,
        "errorLimit": {
            "record": ""
        },
        "speed": {
            "concurrent": 2,
            "throttle": false
        }
    },
    "order": {
        "hops": [
            {
                "from": "Reader",
                "to": "Writer"
            }
        ]
    }
}

Reader脚本参数

参数

描述

是否必选

默认值

datasource

数据源名称,脚本模式支持添加数据源,该配置项输入的内容必须和添加的数据源名称保持一致。

selectedDatabase

待同步数据库的schema。

table

选取的需要同步的表名称,格式需要配置为schema.tableName

说明

例如,selectedDatabaseAUTOTEST,表名称为table01,则table需配置为AUTOTEST.table01

column

所配置的表中需要同步的列名集合,使用JSON的数组描述字段信息。默认使用所有列配置,例如["*"]

  • 支持列裁剪,即可以导出部分列。

  • 支持列换序,即可以不根据表Schema信息的顺序导出列。

  • 支持常量配置,您需要按照JSON格式进行配置。

    ["id", "1", "'mingya.wmy'", "null", "to_char(a + 1)", "2.3" , "true"]
    • id为普通列名。

    • 1为整型数字常量。

    • 'mingya.wmy'为字符串常量(注意需要加上一对单引号)。

    • null为空指针。

    • to_char(a + 1)为表达式。

    • 2.3为浮点数。

    • true为布尔值。

  • column必须显示填写,不允许为空。

splitFactor

切分因子,可以配置同步数据的切分份数,如果配置了多并发,会按照并发数 * splitFactor份来切分。例如,并发数=5,splitFactor=5,则会按照5*5=25份来切分,在5个并发线程上执行。

说明

建议取值范围:1~100,过大会导致内存溢出。

5

splitMode

切分模式,包括:

  • averageInterval:平均采样,根据splitPK找到最大值和最小值,然后按照切分数目平均切分。

  • randomSampling:随机采样,在所有数据中随机找到一定数目作为切分点。

说明

splitMode参数需要与splitPk参数配合使用。

  • 当splitPk为数值类型时,splitMode需要配置为averageInterval

  • 当splitPk为字符串类型时,splitMode需要配置为randomSampling

randomSampling

splitPk

Oracle Reader进行数据抽取时,如果指定splitPk,表示您希望使用splitPk代表的字段进行数据分片,数据同步因此会启动并发任务进行数据同步,可以提高数据同步的效能。

  • 推荐使用表主键作为splitPk,因为表主键通常情况下比较均匀,因此切分出来的分片也不容易出现数据热点。

  • splitPk仅支持配置为数值类型或字符串类型,并且splitMode参数需要与splitPk参数配合使用。

    • 当splitPk为数值类型时,splitMode需要配置为averageInterval

    • 当splitPk为字符串类型时,splitMode需要配置为randomSampling

  • 如果不填写splitPk,将视作您不对单表进行切分,Oracle Reader使用单通道同步全量数据。

说明

splitPK字段在视图的情况下不能使用ROWID。

where

筛选条件,Oracle Reader根据指定的columntablewhere条件拼接SQL,并根据该SQL进行数据抽取。例如,在测试时指定where条件为row_number()

  • where条件可以有效地进行业务增量同步 。

  • where条件不配置或为空时,将视作全表同步数据 。

querySql(高级模式,向导模式不支持)

在部分业务场景中,where配置项不足以描述所筛选的条件,您可以通过该配置来自定义筛选SQL。当您配置该项后,数据同步系统就会忽略tablecolumn等配置,直接使用该配置项的内容对数据进行筛选。例如,需要进行多表Join后同步数据,则使用select a,b from table_a join table_b on table_a.id = table_b.id。当您配置querySql时,Oracle Reader直接忽略tablecolumnwhere条件的配置。

fetchSize

该配置项定义了插件和数据库服务器端每次批量数据获取条数,该值决定了数据同步系统和服务器端的网络交互次数,能够较大的提升数据抽取性能。

说明

fetchSize值过大(>2048)可能造成数据同步进程OOM。

1,024

Writer脚本Demo

{
    "type":"job",
    "version":"2.0",//版本号。
    "steps":[
        { 
            "stepType":"stream",
            "parameter":{},
            "name":"Reader",
            "category":"reader"
        },
        {
            "stepType":"oracle",//插件名。
            "parameter":{
                "postSql":[],//执行数据同步任务之后执行的SQL语句。
                "datasource":"",
                "session":[],//数据库连接会话参数。
                "column":[//字段。
                    "id",
                    "name"
                ],
                "encoding":"UTF-8",//编码格式。
                "batchSize":1024,//一次性批量提交的记录数大小。
                "table":"",//表名。
                "preSql":[]//执行数据同步任务之前执行的SQL语句。
            },
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{
            "record":"0"//错误记录数。
        },
        "speed":{
            "throttle":true,//当throttle值为false时,mbps参数不生效,表示不限流;当throttle值为true时,表示限流。
            "concurrent":1, //作业并发数。
            "mbps":"12"//限流,此处1mbps = 1MB/s。
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}

Writer脚本参数

参数

描述

是否必选

默认值

datasource

数据源名称,脚本模式支持添加数据源,此配置项填写的内容必须要与添加的数据源名称保持一致。

table

目标表名称,如果表的schema信息和上述配置username不一致,请使用schema.table的格式填写table信息。

writeMode

选择导入模式,仅支持insert into。当主键或唯一性索引冲突时,会写不进去冲突的行,以脏数据的形式体现。

insert into

column

目标表需要写入数据的字段,字段之间用英文逗号分隔。例如"column": ["id","name","age"]。如果要依次写入全部列,使用*表示。例如"column":["*"]

preSql

执行数据同步任务之前率先执行的SQL语句。目前向导模式仅允许执行一条SQL语句,脚本模式可以支持多条SQL语句,例如清除旧数据。

postSql

执行数据同步任务之后执行的SQL语句。目前向导模式仅允许执行一条SQL语句,脚本模式可以支持多条SQL语句,例如加上某一个时间戳。

batchSize

一次性批量提交的记录数大小,该值可以极大减少数据同步系统与Oracle的网络交互次数,并提升整体吞吐量。如果该值设置过大,会导致数据同步运行进程OOM异常。

1,024