DBCAT is a lightweight command-line tool that provides features such as DDL conversion and schema comparison between databases. This topic describes how to use DBCAT to migrate schemas.
The DBCAT installation package is named in the dbcat-[Version number]-SNAPSHOT.tar.gz format. After you download and decompress the installation package, you can directly use it. The executable file is named dbcat.
Notice
DBCAT is a component of OceanBase Migration Service (OMS). You can obtain its installation package from OceanBase Technical Support.
Prepare the installation environment
DBCAT can run only on CentOS, macOS, and Windows. You must first install JDK 1.8 or later. You can also use OpenJDK. Then, specify the environment variable JAVA_HOME.
Here is an example of installing OpenJDK in CentOS:
$sudo yum -y install java-1.8.0-openjdk.x86_64
$which java
/usr/local/java/jdk1.8.0_261/bin/java
echo 'export JAVA_HOME=/usr/local/java/jdk1.8.0_261/' >> ~/.bash_profile
. ~/.bash_profile
Decompress the installation package:
tar zxvf dbcat-1.8.0-SNAPSHOT.tar.gz
cd dbcat-1.8.0-SNAPSHOT/
chmod +x bin/dbcat
$tree -L 3 --filelimit 30
.
├── bin
│ ├── dbcat
│ ├── dbcat.bat
│ └── dbcat-debug
├── conf
│ ├── dbcat.properties
│ └── logback.xml
├── docs
│ ├── README.docx
│ ├── README.md
│ └── README.txt
├── LEGAL.md
├── lib [45 entries exceeds filelimit, not opening dir]
├── LICENSE
├── meta
│ └── README
└── NOTICE
5 directories, 12 files
The following table describes the directories in the installation package.
| Directory name | Description |
|---|---|
| bin | The directory where the executable file is located. |
| conf | The directory where log files are located. |
| lib | The directory where the libraries required for running are located. |
| meta | The directory to which dictionary table data is exported in offline conversion scenarios. |
| ~/output | The directory where the SQL file and report file are located. This directory is generated during the running of DBCAT. |
Export schemas from an Oracle tenant of OceanBase Database
DBCAT supports online conversion. In other words, DBCAT can directly connect to the source database and export the objects from the database. If you have too many objects (such as 10,000 objects), the export may be slow.
Here is the export command of DBCAT:
bin/dbcat convert -H<host> -P<port> -u<user> -p<password> -D <database> --from <from> --to <to> --table '*'
To learn about more command options, run the bin/dbcat help convert command.
Required options:
| Option | With parameters | Description |
|---|---|---|
| -H or --host | Y | The IP address of the server on which the database is located. |
| -P or --port | Y | The TCP/IP port number of the database server. |
| -u or --user | Y | The username used to log on to the database. |
| -t or --tenant | Y | The name of the OceanBase Database tenant. |
| -c or --cluster | Y | The name of the OceanBase cluster. |
| -p or --password | Y | The password used to log on to the database. |
| -D or --database | Y | The name of the source database. If the source database is a DB2 database, the database name and mode name must be different. |
| --service-id | Y | The service ID for connecting to the Oracle database. |
| --service-name | Y | The service name for connecting to the Oracle database. |
| --as-sysdba | N | Specifies to log on to the Oracle database as the sysdba role. |
| --sys-user | Y | The username for logging on to the sys tenant of the OceanBase cluster. |
| --sys-password | Y | The password for logging on to the sys tenant of the OceanBase cluster. |
| --schema | Y | The schema name of the source database. If the source database is not a DB2 database, the schema name is the same as the database name. |
| --from | Y | The type of the source database. |
| --to | Y | The type of the destination database. |
| --all | N | Specifies to export all database objects. Default database objects are tables and views. |
Optional options:
| Option | With parameters | Description |
|---|---|---|
| -f or --file | Y | The output path of the SQL file. |
| --offline | N | Specifies to use the offline mode. |
| --target-schema | Y | The mode name of the destination database. |
| --table | Y | Specifies to export the specified tables from the database. |
| --view | Y | Specifies to export the specified views from the database. |
| --trigger | Y | Specifies to export the specified triggers from the database. |
| --synonym | Y | Specifies to export the specified synonyms from the database. |
| --sequence | Y | Specifies to export the specified sequences from the database. |
| --function | Y | Specifies to export the specified functions from the database. |
| --procedure | Y | Specifies to export the specified procedures from the database. |
| --dblink | Y | Specifies to export the specified DBLinks from the database. |
| --type | Y | Specifies to export objects of the specified types from the database. |
| --type-body | Y | Specifies to export objects of the specified type bodies from the database. |
| --package | Y | Specifies to export the specified packages from the database. |
| --package-body | Y | Specifies to export the specified package bodies from the database. |
| --no-quote | N | Specifies to generate DDL statements without quotation marks. |
| --no-schema | N | Specifies to generate DDL statements without schema name. |
| --target-schema | Y | Specifies to use the specified schema name for the generated DDL statements. |
| --exclude-type | Y | Specifies to exclude the specified type of objects from the export. You can use this option together with the --all option. For example, --all --exclude-type 'TABLE' specifies to exclude the TABLE type. |
Run the following command to migrate all schemas from the test database in an Oracle tenant of OceanBase Database V4.0.0 to an Oracle 12c database.
bin/dbcat convert -H 172.30.xxx.xxx -P 2883 -uroot -pxxxxx -D test --from oboracle40 --to oracle12c --table '*'
Notice:
You do not need to directly install DBCAT on the database host. Instead, you can install it on a host that can directly connect to the database host.
In the command, the --from and --to options respectively specify the source and destination database types, which must contain the database version number. The following table describes the source and destination databases supported by DBCAT.
Source database Destination database TiDB OBMYSQL PG OBMYSQL SYBASE OBORACLE MYSQL OBMYSQL ORACLE OBORACLE ORACLE OBMYSQL DB2 IBM i OBORACLE DB2 LUW OBORACLE DB2 LUW OBMYSQL OBMYSQL MYSQL OBORACLE ORACLE In the preceding table, OBMYSQL indicates MySQL tenants of OceanBase Database, and OBORACLE indicates Oracle tenants of OceanBase Database.
The following table describes the versions of supported source and destination databases.
Database Version TiDB tidb4
tidb5PG pgsql10 SYBASE sybase15 DB2 IBM i db2ibmi71 DB2 LUW db2luw970
db2luw1010
db2luw1050
db2luw111
db2luw115MYSQL mysql56
mysql57
mysql80ORACLE oracle9i
oracle10g
oracle11g
oracle12c
oracle18c
oracle19cOBMYSQL obmysql14x
obmysql21x
obmysql22x
obmysql200
obmysql211
obmysql2210
obmysql2230
obmysql2250
obmysql2271 - obmysql2277
obmysql30x
obmysql31x
obmysql32x
obmysql322
obmysql40OBORACLE oboracle2220
oboracle2230
oboracle2250
oboracle2270 - oboracle2277
oboracle21x
oboracle22x
oborcle30x
oboracle31x
oboracle32x
oboracle322
oboracle40
The files generated during running are stored in output under the home directory.
$tree ~/output/dbcat-20xx-xx-xx-164533/
/home/qing.meiq/output/dbcat-20xx-xx-xx-164533/
├── tpccdb
│ └── TABLE-schema.sql
└── tpccdb-conversion.html
1 directory, 2 files
Import the data to the Oracle database
The data exported by using DBCAT is an SQL file. To import the SQL file to the Oracle database, access the Oracle database and then run the @File name command to import the SQL file. Example:
SQL> @TABLE-schema.sql
Query OK, 0 rows affected (0.044 sec)
Notice
If the SQL file is not in the current directory, the absolute address of the SQL file is required.
Verify the data import result
Example: View the schema of a table in the OceanBase database and Oracle database.
View the schema of the bmsql_customer table in the source OceanBase database.
obclient [SYS]> desc bmsql_customer;
+----------------+---------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+----------------+---------------+------+-----+---------+-------+
| C_W_ID | NUMBER(38) | NO | PRI | NULL | NULL |
| C_D_ID | NUMBER(38) | NO | PRI | NULL | NULL |
| C_ID | NUMBER(38) | NO | PRI | NULL | NULL |
| C_DISCOUNT | NUMBER(4,4) | YES | NULL | NULL | NULL |
| C_CREDIT | CHAR(2) | YES | NULL | NULL | NULL |
| C_LAST | VARCHAR2(16) | YES | NULL | NULL | NULL |
| C_FIRST | VARCHAR2(16) | YES | NULL | NULL | NULL |
| C_CREDIT_LIM | NUMBER(12,2) | YES | NULL | NULL | NULL |
| C_BALANCE | NUMBER(12,2) | YES | NULL | NULL | NULL |
| C_YTD_PAYMENT | NUMBER(12,2) | YES | NULL | NULL | NULL |
| C_PAYMENT_CNT | NUMBER(38) | YES | NULL | NULL | NULL |
| C_DELIVERY_CNT | NUMBER(38) | YES | NULL | NULL | NULL |
| C_STREET_1 | VARCHAR2(20) | YES | NULL | NULL | NULL |
| C_STREET_2 | VARCHAR2(20) | YES | NULL | NULL | NULL |
| C_CITY | VARCHAR2(20) | YES | NULL | NULL | NULL |
| C_STATE | CHAR(2) | YES | NULL | NULL | NULL |
| C_ZIP | CHAR(9) | YES | NULL | NULL | NULL |
| C_PHONE | CHAR(16) | YES | NULL | NULL | NULL |
| C_SINCE | TIMESTAMP(6) | YES | NULL | sysdate | NULL |
| C_MIDDLE | CHAR(2) | YES | NULL | NULL | NULL |
| C_DATA | VARCHAR2(500) | YES | NULL | NULL | NULL |
+----------------+---------------+------+-----+---------+-------+
21 rows in set (0.002 sec)
View the schema of the bmsql_customer table in the destination Oracle database.
SQL> desc bmsql_customer;
Name Null? Type
----------------------------------------- -------- ----------------------------
C_W_ID NOT NULL NUMBER(38)
C_D_ID NOT NULL NUMBER(38)
C_ID NOT NULL NUMBER(38)
C_DISCOUNT NUMBER(4,4)
C_CREDIT CHAR(2)
C_LAST VARCHAR2(16)
C_FIRST VARCHAR2(16)
C_CREDIT_LIM NUMBER(12,2)
C_BALANCE NUMBER(12,2)
C_YTD_PAYMENT NUMBER(12,2)
C_PAYMENT_CNT NUMBER(38)
C_DELIVERY_CNT NUMBER(38)
C_STREET_1 VARCHAR2(20)
C_STREET_2 VARCHAR2(20)
C_CITY VARCHAR2(20)
C_STATE CHAR(2)
C_ZIP CHAR(9)
C_PHONE CHAR(16)
C_SINCE TIMESTAMP(6)
C_MIDDLE CHAR(2)
C_DATA VARCHAR2(500)
The schema of the bmsql_customer table is consistent in the source and destination databases.