Overview

What is OBDUMPER?

OBDUMPER is a client-side data export tool developed in Java. You can use OBDUMPER to export data from OceanBase Database to files in the SQL or CSV format. You can also use it to export objects defined in databases to files.

OBDUMPER is advantageous over other export tools such as MyDumper and SQL Developer in the following aspects:

• High performance

  OBDUMPER is specifically optimized for partitioned tables and tables without a primary key.

• Rich features

  OBDUMPER provides various features, including limited data export, various data formats, and global consistent nonlocking export.

Features

OBDUMPER allows you to export data in object schemas and tables from OceanBase Database to files. OBDUMPER provides the following features:

• Allows you to export the DDL statements of object schemas in a database.

• Allows you to export table data to files in the CSV or SQL format.

• Allows you to export data from some partitions of a partitioned table by specifying the names of the partitions.

• Allows you to specify global filters to export only the data that meets the specified conditions.

• Supports simple data cleansing rules.

• Supports global consistent nonlocking read to ensure the global consistency of the exported data.

• Supports system change number (SCN)-based or timestamp-based flashback queries to ensure the global consistency of the exported data.

• Supports data export in limited mode, in which you do not need to specify account information of users under the sys tenant.

• Allows you to export the data and upload it to Object Storage Service (OSS).

• Supports the merge of multiple data files exported from a table into one file. This feature reduces the number of exported data files.

• Supports the exported schema to contain TABLEGROUP information.

• Supports the read of backup data from the replica of an OceanBase cluster.

• Allows you to export custom SQL result sets into files of the SQL, CSV, and CUT formats.

Supported OceanBase Database versions

The following table describes OceanBase Database versions that are supported by OBDUMPER.

Notice

OBLOADER and OBDUMPER V3.0.0 and later are compatible with OceanBase Database Community Edition.

You can use OBLOADER and OBDUMPER in OceanBase Database Community Edition V3.1.2 and later.

Usage notes

• If a foreign key contains several columns, the order of the columns might change after the export. Example: FOREIGN KEY(c1,c2) REFERENCE (c1,c2).

• OceanBase Database V1.4.72 in MySQL mode does not allow you to export the schema definitions of unique prefix indexes, such as UNIQUE(c1(10)).

• OceanBase Database V1.4.x in MySQL mode does not allow you to export the definitions of generated columns, such as GENERATED ALWAYS AS (expr).

• OceanBase Database V2.2.50 and earlier versions in MySQL mode do not allow you to export the schema definition statements of function-based indexes.

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

• You need to modify the VM memory parameter, which is defaulted as -Xms4G -Xmx4G, in the script when you intend to import or export a large amount of data.

• 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 Oracle mode, and lowercase letters are used in MySQL mode.

• To improve the data export performance, we recommend that you trigger major compaction for one time before you export data.

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

• OceanBase Database V2.2.7x in MySQL 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 MySQL 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 MySQL mode does not allow you to export columns that store index data. For example, columns stored in STORING(COLUMN_LIST) cannot be exported.

• In the current and earlier versions, the ALL_SOURCE view lacks the TYPE-related information. OBDUMPER

• 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.

• Data in the interval day(2) to second(0) format cannot be exported. Example: '0 5:0:0.000000'.

Limited mode

Note

This mode applies to only services deployed on Alibaba Cloud.

• OBDUMPER provides a limited mode. You can export data in limited mode by specifying the --public-cloud option. In limited mode, OBDUMPER does not require the permissions of users under the system tenant. Therefore, you do not need to specify the --sys-user or the --sys-password option.

• If you do not use the limited mode, OBDUMPER still requires the permissions of the root and proxyro user under the sys tenant. The root user is the default user.

• 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 schema definitions of only TABLE and VIEW objects. Schemas of TABLE objects do not contain index definitions. The exported table schemas are incomplete because they lack index information. The performance of data export in limited mode is much 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.

Usage notes

This section describes how to use the undo_retention parameter.

The default value of this parameter is 0, in seconds. Assume that t1 is the point in time when an undo operation was performed and t2 equals t1 plus 900 seconds. You can query data generated within the time period specified by the [t1,t2] range at t2. After you set the undo_retention parameter, the current session only takes effect on the data that is generated after t1. Sample statement of setting the undo_retention parameter:

SET global undo_retention=900;

• You can obtain the effective SCN of OceanBase Database by querying the v$ob_timestamp_service view under the sys tenant.

• If you query data at a specified point in time, the data after the last major compaction is returned. For example, if you started major compaction in the cluster at t1, the earliest data that you can query is the data at t1.

• If the table that you want to query has been deleted and is in the recycle bin, you must restore it from the recycle bin.

• Flashback queries may fail in the case of minor compaction. If minor compaction occurs and the undo_retention parameter is not set, you cannot perform flashback queries. After the undo_retention parameter is set, you can query data within the time range of [t1, t1 + undo_retention], where t1 is the point in time when minor compaction occurs.