Overview

What is OBDUMPER?

OBDUMPER is a client data export tool developed in Java. You can use it to export data and the DDL statements of objects in OceanBase Database to files.

OBDUMPER has the following advantages over other export tools such as MyDumper and SQL Developer:

• Higher performance: OBDUMPER is specifically optimized for partitioned tables and tables without a primary key.
• More features: OBDUMPER provides various features, including data preprocessing, support for various data formats, and global consistent nonlocking export.

Features

OBDUMPER mainly provides the following features:

• Allows you to export the DDL statements of database objects.

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

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

• Allows you to configure data preprocessing rules to convert data before export.

• Allows you to export data at any transaction port or time point after flashback queries based on the system change number (SCN) or timestamp.

• Allows you to upload exported data to Object Storage Service (OSS).

• Allows you to export data from a follower replica of an OceanBase cluster (which is different from a standby cluster).

• Allows you to export custom query result sets in the SQL, CSV, and CUT formats.

• Ensures global consistency for the exported data by reading the snapshot version when tables are not locked.

Supported OceanBase Database versions

The following table describes OceanBase Database versions and modes that are supported.

Notice
OBDUMPER 3.0.0 supports OceanBase Community Edition 3.1.2 and later.

Usage notes

• 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 in the script when you try to 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. If case sensitivity is required, you can enclose the table name in square brackets ([ ]). Example: --table '[test]' represents the test table, and the file name format is test.group.sequence.suffix. --table '[TEST]' represents the TEST table, and the filename format is TEST.group.sequence.suffix.

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

• 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 definitions of unique prefix indexes. Example: UNIQUE(c1(10)).

• OceanBase Database V1.4.x in MySQL mode does not allow you to export the definitions of generated columns. For example, columns generated by using the GENERATED ALWAYS AS (expr) statement cannot be exported.

• 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 the definitions of SYNONYM objects.

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

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

• OceanBase Database in Oracle mode does not allow you to export the definitions of TYPE objects.

• OceanBase Database in Oracle mode does not allow you to export data of the INTERVAL DAY(2) TO SECOND(0) type.

• On the Windows OS, the exclamation point (!) cannot be used as a delimiter. An object name in the database cannot contain special characters \ / : * ? " < > | Otherwise, neither the database object definitions nor the table data can be exported.

• By default, when the table data to be exported contains a generated column, the data of the generated column is exported. You can use the --exclude-virtual-columns option to specify not to export the data of the generated column.

• When you use the --date-value-format, --time-value-format, --datetime-value-format, --timestamp-value-format, --timestamp-tz-value-format, or --timestamp-ltz-value-format option, specify the time value in the correct format. Otherwise, an error is reported during the data export.

Limited mode

Note
The limited mode is applicable to scenarios where you cannot directly specify the --sys-user or --sys-password option.

• If you enable the limited mode (by specifying --public-cloud) when you export data from an OceanBase cluster deployed in a public cloud, you do not need to specify the -t or -c option. When you export data in limited mode from an OceanBase cluster deployed in a private cloud, you must specify the -t option, and additionally the -c option for using OceanBase Database Proxy (ODP). If you do not enable the limited mode, the --sys-user and --sys-password options must be specified for OBDUMPER.

• If you enable the limited mode (by specifying --public-cloud) when you export object definitions, you can export definitions of only tables and views. Table definitions cannot contain index and comment information. In addition, data export performance in limited mode is lower than that in unlimited mode.

Flashback export

To use the flashback export feature, you need to set the system variable undo_retention to an appropriate value.

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 execute a query at t2. After you set undo_retention, the current session only takes effect on the data that is generated after t1. The default value of this system variable is 0, in seconds. Sample statement for setting the undo_retention variable:

SET global undo_retention=900;

• Currently, you can only query the v$ob_timestamp_service view in the SYS tenant to obtain the valid OceanBase SCN.

• You can query data after the latest major compaction at a specific point in time. For example, if you started a major compaction 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 are affected by minor compactions. If a minor compaction has been performed and the undo_retention variable is not specified, flashback queries cannot be performed.

• After the undo_retention variable is set, you can query data within the time range of [t1,t1 + undo_retention], where t1 is the point in time when the minor compaction occurs.