Product Introduction

What is OBDUMPER?

OBDUMPER is a data export tool in the form of a client developed in Java. You can use OBDUMPER to export data from the OceanBase database to files in SQL or CSV format. You can also use it to export objects defined in the database 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 or 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 in the OceanBase Database to files. Specifically, OBDUMPER provides the following features:

• Allows you to export data definition language (DDL) statements of object schemas in the 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 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.

• Provides a lite mode that does not rely on the account information of the system tenant.

Supported OceanBase Database versions

OBDUMPER supports the following OceanBase Database versions:

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 support exporting the schema definitions of unique prefix indexes, such as UNIQUE(c1(10)).

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

• OceanBase Database V2.2.50 and earlier versions in MySQL mode do not support exporting 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 virtual machine (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.

• The imported data files must be named in the format of table name.<any characters>.extension . Files that store data cleansing rules must be named in the format of table name.ctl .

• We recommend that you trigger a major compaction before you export data, to improve the data export performance.

Lite mode

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

In lite mode, you can only export object tables and views. The exported table schemas are incomplete because they lack index information. The performance of data export in lite mode is much inferior to that in regular mode.

Limits

Some limits are posed on the setting and use of the undo_retention parameter in the current OBDUMPER version.

The default value of this parameter is 0, and the default unit is second. If you set the undo retention period at timepoint t1, and t2 = t1 + 900s, you can query data within the time range of [t1, t2] at timepoint t2. After the undo_retention parameter is specified, the current session takes effect only on the data generated after timepoint t1. You can set the undo_retention parameter by using the following statement:

SET global undo_retention=900;

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

• If you specify a timepoint during data querying, the data after the last major compaction is returned. For example, if you initiated a major cluster compaction at timepoint t1, the earliest version of data that you can obtain is the data at timepoint t1.

• The queried table has been dropped and is in the recycle bin, you must first restore it from the recycle bin.

• Flashback queries are subject to minor compactions. If a major compaction has been performed and the undo_retention parameter is not specified, flashback queries cannot be performed. After the undo_retention parameter is specified, you can query data within the time range of [t1, t1 + undo_retention], where t1 is the timepoint when the major compaction was performed.