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 standard formats of CSV, SQL, CUT, ORC, and Parquet.
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 result sets of custom queries to files in standard formats of CSV, SQL, CUT, ORC, and Parquet.
Allows you to export globally consistent data without locking tables by reading snapshot versions.
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 control 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 namedtest, and the file name is in the format of test.group.sequence.suffix.--table '[TEST]'indicates the table namedTEST, and the file name is in the format of TEST.group.sequence.suffix.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, for 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 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 separator. 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-columnsoption 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-formatoption, 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 separator, the separator character in the field is escaped. For example, if the content of a field is
abc|defand | is the field separator (--column-splitter "|"), the exported field is converted toabc\|def.The
--no-sysoption indicates that the user cannot provide the sys tenant password in a private cloud environment. In OBLOADER & OBDUMPER V3.3.0 and later versions, public cloud environments and private cloud environments are treated differently. The--public-cloudoption is used only for public cloud environments, and the--no-sysoption is used only for private cloud environments.Before you use OBDUMPER to export data from OceanBase Database V3.2.4.0 and later, set the system parameter
open_cursorsto a larger value. Otherwise, an error may occur during the export. After the data is exported, reset the system parameter to the initial value, for example,ALTER SYSTEM SET open_cursors = 65535;.If the schema of a table has been changed in OceanBase Database V4.0.0.0 or later, you cannot use OBDUMPER to export the baseline data obtained after the lasted major compaction (or consistent snapshot data). You can manually initiate a major compaction and then re-export the most recent baseline data.
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_serviceview 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_retentionvariable is not specified, flashback queries cannot be performed.After the
undo_retentionvariable 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.