OBLOADER \& OBDUMPER V3.1.0

OBLOADER & OBDUMPER V3.1.0 released in April 2022 allows you to use key files, set session variables, and accurately delimit data files. When you export data, OBDUMPER supports the preprocessing of data in all formats and allows you to export related information of table groups. When you export data of the DATE type, OBDUMPER allows you to export the to_date() function. This version also optimizes the logic that is used to split large files during data import and improves the pagination query performance during the export of tables that have a primary key.

Version information

• Current version: V3.1.0

• Previous version: V3.0.1

• Version release date: April 10, 2022

Supported OceanBase Database versions

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

Feature updates

• OBLOADER and OBDUMPER:

  • When you import or export data, you can use a key file instead of specifying the password of the SYS tenant on the CLI.

  • Accurate delimitation is supported for data files in the following formats: CUT, CSV and SQL.

• OBLOADER:

  • The control file supports more preprocessing functions.

  • The large file splitting logic is optimized to improve the accuracy of splitting.

• OBDUMPER:

  • The following session variables can be set: ob_query_timeout, ob_trx_timeout, net_read_timeout, net_write_timeout, and proxy_route_policy.

  • Data files of the following formats can be preprocessed: CUT, CSV and SQL.

  • The performance of pagination queries is optimized for the export of tables that have a primary key. This way, the export is more stable compared with normal streaming queries.

  • Table structures can be exported with related information of table groups.

  • The to_date() function can be exported during the export of data of the DATE type (only in the SQL format).

Fixed issues

• The issue is fixed where the --public-cloud option becomes invalid when OBLOADER imports data.

• The issue is fixed where an error for global index export occurs when OBDUMPER exports structures.

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';