Overview|V4.0.0|OceanBase Loader and Dumper|docs|OceanBase Documentations

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 point in time 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.

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 table names are case-sensitive, enclose them in brackets ([ ]). For example, --table '[test]' indicates the table named test, and the file name is in the format of test.group.sequence.suffix. --table '[TEST]' indicates the table named TEST, and the file name is in the format of 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 1.4.72 in MySQL mode does not allow you to export the definitions of unique prefix indexes, for example, UNIQUE(c1(10)).
OceanBase Database 1.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 2.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 2.2.7x in MySQL mode does not allow you to export the definitions of SYNONYM objects.
OceanBase Database 2.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 2.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 the following 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 will be reported during data export.
When you export a CUT file, if a field contains the specified field delimiter, the delimiter character in the field is escaped. For example, if the content of a field is abc|def and | is the field delimiter (--column-splitter "|"), the exported field is converted to abc\|def.
The --no-sys option identifies scenarios without the sys tenant password in a private cloud environment. It is equivalent to the --public-cloud option in a private cloud environment in OBDUMPER 3.2.0 and earlier versions. In OBDUMPER 3.3.0 and later versions, public cloud environments and private cloud environments are treated differently. The --public-cloud option is used only for public cloud environments, and the --no-sys option is used only for private cloud environments.

Limited mode

Note
The limited mode is applicable only to scenarios where you cannot directly specify the --sys-user or --sys-password option.
The limited mode enabled by using the --no-sys option is applicable to private cloud environments without sys tenant passwords. The limited mode enabled by using the --public-cloud option is applicable to public cloud environments without sys tenant passwords.

When you export data from an OceanBase cluster deployed in a public cloud in limited mode (enabled by using the --public-cloud option), you do not need to specify the -t and -c connection options. When you export data from an OceanBase cluster deployed in a private cloud in limited mode (enabled by using the --no-sys option), you need to specify the -t connection option, and additionally the -c option if you connect to the OceanBase Database Proxy (ODP) service. 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.

Notice
In limited mode, OBDUMPER does not support exporting table indexes of OceanBase Oracle 2.2.30 and earlier versions.

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 point in time 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 takes effect only 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 system change number (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.