OBLOADER \& OBDUMPER V3.0.0

OBLOADER & OBDUMPER V3.0.0 released in December 2021 allows you to export a blocklist of table names based on a regular expression and supports the processing of exported data when you export data by specifying query conditions. In addition, the new version optimizes the usage of map and control files, component dependencies, and the code logic of OBLOADER and OBDUMPER.

Version information

• Current version: V3.0.0

• Previous version: V2.3.0

• Version release date: December 31, 2021

Supported OceanBase Database versions

OBLOADER & OBDUMPER V3.0.0 is applicable to OceanBase Database versions in the following table.

Feature updates

• OBLOADER and OBDUMPER:

  • Batch files for the Windows operating system are restructured.

  • OBLOADER and OBDUMPER are transformed by using the picocli framework.

  • The map and control rule files are merged. Map files are no longer supported, and the mapping feature of map files is integrated into control files.

• OBLOADER:

  • The --retain-empty-files option is added to support the export of empty tables.

  • The oceanbase-connector-java component can be replaced.

  • The routing component of OBLOADER is restructured.

• OBDUMPER:

  • The structures of database objects can be exported without the DBcat component.

  • A blocklist of table names can be exported based on a regular expression. The --exclude-table option is optimized to support fuzzy matching by using asterisks (*).

  • The --query-sql option is optimized. You can use the option together with the --ctl-path option to cleanse queried data based on the column names or column order. You can export data into files in the following formats: CSV, CUT, and SQL.

Fixed issues

• The issue is fixed where the connection to a database times out when you export data and structures from the database.

• The issue is fixed where route computing is inaccurate when data is imported based on field mapping.

Known issues

• OBLOADER and OBDUMPER: The object names, data file names, and rule file names specified by command-line options must be capitalized in the same way. By default, uppercase letters are used in the Oracle compatible mode and lowercase letters are used in the MySQL compatible mode.

• OBLOADER:

  • If a database contains tables with foreign keys, structures and data may not be imported in dependency order, which may cause the import failure.

  • OceanBase Database in the Oracle compatible mode does not support the import and export of virtual column data. This issue is fixed in the MySQL compatible mode where virtual columns are ignored during data export. Otherwise, virtual data cannot be imported.

  • In the MySQL compatible mode of OceanBase Database V1.4.x, the metadata of the RANGE_COLUMNS + KEY is defective in the virtual routing view.

• OBDUMPER:

  • When a foreign key contains multiple columns, the columns may not be sequentially exported. Example: FOREIGN KEY(c1,c2) REFERENCE(c1,c2).

  • OceanBase Database V1.4.72 in the MySQL compatible mode does not allow you to export the definitions of unique prefix indexes. Example: UNIQUE(c1(10)).

  • When you resolve the primary key conflict in OceanBase Database V1.4.79 in the MySQL compatible mode, the use of the insert ... where not exists statement may result in cross-partition insertion.

  • OceanBase Database of versions earlier than V2.2.50 in the Oracle compatible mode do not allow you to export the DDL statements of function-based indexes.

  • OceanBase Database V2.2.7x in the MySQL compatible mode does not allow you to export vertical partitions. For example, partitions created by using the PARTITION BY COLUMN statement cannot be exported.

  • OceanBase Database V2.2.7x in the MySQL compatible mode does not allow you to export synonyms. For example, synonyms created by using the CREATE SYNONYM statement cannot be exported.

  • OceanBase Database V2.2.7x in the MySQL compatible mode does not allow you to export columns that store index data. For example, columns in STORING(COLUMN_LIST) cannot be exported.

  • Data of the INTERVAL DAY(2) TO SECOND(0) type cannot be exported. For example, '0 5:0:0.000000' cannot be exported.

  • In the Oracle compatible mode, data of the NUMBER(n,m) type and data less than 1 are exported in scientific notation. For example, 0.0000007 is exported as 7E-7.

Usage notes

• For information about the standard CSV format, see the RFC 4180 specifications. We recommend that you import and export data in strict accordance with the RFC 4180 specifications.

• You need to modify the VM memory parameter in the script when you intend to import or export a large amount of data. Default value: -Xms4G -Xmx4G.

• All imported data files are named in the table.group.sequence.suffix format.

• Tables without primary keys do not support import resumption after an interruption.

• When you import data, make sure that the specified object name and data file name are capitalized in the same way. If a database contains both object names in uppercase and object names in lowercase, use double quotation marks (" ") to enclose the object names and file names in lowercase.

• On the Windows operating system, the name of a database object cannot contain the following characters \ / : * ? " < > |. Otherwise, the file cannot be exported.

• On the Windows operating system, the exclamation point (!) cannot be used as a delimiter.

• OBLOADER supports the following file formats:

  • DDL: A file in the DDL format contains only DDL statements.

  • SQL: A file in the SQL format contains only INSERT statements. Each statement occupies one line without line breaks.

  • MIX: A file in the MIX format can contain mixed types of statements, such as DDL and DML statements.

  • POS: In a file in the POS format, the data is generated based on the positions of byte offsets.

  • CUT: A file in the CUT format stores data separated by string. However, in a file in the CSV format, data is separated by single character.

Limited mode

Note

This mode applies only to services deployed on Alibaba Cloud.

• If you do not use the limited mode, you need to specify the root@sys or proxyro@sys user for OBLOADER and OBDUMPER. By default, the root@sys user is used.

• If you specify the --public-cloud option to export data in limited mode, you can export only data in tables.

• If you specify the --public-cloud option to export data in limited mode, you can export structure definitions of only tables and views. Table structure definitions do not contain index definitions. The exported table structures are incomplete because they lack index information. The performance of data export in limited mode is lower than that in regular mode.

• If you specify the --public-cloud option to use Alibaba Cloud services in limited mode, you do not need to specify the -t or -c option.

• As of the current version, the ALL_SOURCE view does not contain TYPE-related information, and OBDUMPER cannot export TYPE definitions.

• OBLOADER supports the limited mode. You can import data in limited mode by specifying the --public-cloud option. In limited mode, OBLOADER does not rely on users in the SYS tenant. Therefore, you do not need to specify the --sys-user or --sys-password option.

• The limited mode does not support all features of OBLOADER. The performance and stability are therefore affected in limited mode. In addition, only OceanBase Database V2.2.30 and later support throttling on servers. Therefore, when you use the limited mode, you need to run the following commands to modify the throttling settings on servers:

  alter system set freeze_trigger_percentage=50;
  alter system set minor_merge_concurrence=64;
  alter system set writing_throttling_trigger_percentage=80 tenant='xxx';