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 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 from OceanBase Database to files. Specifically, OBDUMPER enables you to perform the following operations:

• Export data definition language (DDL) statements of object schemas in the database.

• Export table data to files in the CSV or SQL format.

• Export data from some partitions of a partitioned table by specifying the names of the partitions.

• Specify global filters to export only the data that meets the specified conditions.

• Specify simple data processing rules.

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

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

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

• Export the data and upload it to Alibaba Cloud Object Storage Service (OSS).

Supported OceanBase Database versions

OBDUMPER supports the following OceanBase Database versions.

Notes

• If a foreign key contains several columns, for example, a foreign key defined by using the FOREIGN KEY(c1,c2) REFERENCE (c1,c2) statement, the order of the columns might change after the export.

• 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, for example, generated columns created by using GENERATED ALWAYS AS (expr).

• Oracle mode of OceanBase Database versions earlier than V2.2.50 does 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.

• To improve the data export performance, we recommend that you trigger a major compaction 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 support exporting 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 support exporting synonyms. For example, synonyms created by using the CREATE SYNONYM statement cannot be exported.

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

* In OBLOADER and OBDUMPER V2.2.0 and earlier, the ALL_SOURCE view lacks the TYPE-related information. OBDUMPER cannot export TYPE definitions.

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

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

• Data of the interval day(2) to second(0) type, for example, '0 5:0:0.000000', cannot be exported.

Limited mode

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

• If you do not use the limited mode, you need to specify the root@sys (default) or proxyro@sys user for the import or export tool.

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

• When you specify the --public-cloud option to export schemas in limited mode, you can export schema definitions of only TABLE and VIEW objects. Schemas of TABLE objects do not include 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.

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

Usage condition

Some limits are posed on the setting and use of the undo_retention parameter in OBDUMPER V2.2.0.

The default value of this parameter is 0, and the default unit is second. Assume that t1 is the timepoint of the Undo operation, and t2 = t1 + 900s. You can find data within the [t1,t2] range when you run a query at t2. After you set the undo_retention parameter, the current session only takes effect on the data that is generated after 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 OceanBase Database by querying the v$ob_timestamp_service view under the sys tenant.

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

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

• Flashback queries may fail in the case of minor compactions. If a minor compaction is triggered and the undo_retention parameter is not specified, flashback queries cannot be run. 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 minor compaction was performed.