Assess the compatibility with objects|V4.2.4|OceanBase Migration Assessment| docs|Distributed Database

OceanBase Migration Assessment (OMA) assesses the compatibility of converting database objects of Oracle Database, MySQL, PostgreSQL, TiDB, DB2 LUW, RDS for MySQL, PolarDB for MySQL, PolarDB for Oracle, DRDS, openGauss, and OceanBase Database instances (both MySQL and Oracle tenants) to database objects of OceanBase Database during data migration or synchronization. OMA also generates an assessment report after the assessment is complete. This topic describes how to assess the compatibility with different database objects.

Assess the compatibility with Oracle objects

Prerequisites

The Oracle database to be assessed is directly accessible and the configured database user has the CREATE SESSION and CREATE RESOURCE privileges to ensure proper connection. Oracle databases of version 10g, 11g, 12c, 18c, and 19c are supported.
The configured database user has the SELECT ANY DICTIONARY privilege to ensure that the OMA collector can scan the DBA_OBJECTS view to obtain objects to be assessed.
The configured database user has the select_catalog_role role to ensure that the OMA collector can call the DBMS_METADATA.GET_DDL function to obtain the DDL statements of corresponding objects.

Privileges

You can execute the following statements to check whether the user has the required privileges. Assume that you want to use the Oracle user OMA to perform an assessment task.

-- The return results of this SQL statement should contain CONNECT, RESOURCE, and SELECT_CATALOG_ROLE.
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'OMA';

-- The return results of this SQL statement should contain CREATE SESSION and SELECT ANY DICTIONARY:
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'OMA';

To grant the required privileges to the user, execute the following statements:

GRANT CREATE SESSION,RESOURCE TO OMA;
GRANT SELECT ANY DICTIONARY TO OMA;
GRANT SELECT_CATALOG_ROLE TO OMA;

Usage

Run start.sh by referring to the following sample commands. For more information about the parameters in the script, see Product form. In Microsoft Windows, replace sh bin/start.sh with start.bat.

sh bin/start.sh \
# The task name, which can be set to any value.
--name test \    
# The analysis mode.
--mode ANALYZE \         
# The data source. DB indicates a database.        
--from-type DB \  
# The assessment mode.               
--evaluate-mode SOURCE_TARGET \  
# The type of the source database.
--source-db-type ORACLE \      
# The version of the source database.  
--source-db-version 11g \        
# The address of the source database.
--source-db-host xxx.xxx.xxx.xxx \    
# The port of the source database.
--source-db-port port \      
# The username of the source database account.    
--source-db-user username \      
# The password of the source database account.     
--source-db-password password \    
# The service name of the source database.   
--source-db-service-name orcl11g.us.oracle.com \
# The schema to be assessed.
--schemas "OBDBA" \              
# The type of the destination database.
--target-db-type OBORACLE \      
# The version of the destination database.
--target-db-version 4.2.1

Progress prompt

During the assessment process, OMA displays the following progress prompt:

[INFO ] 16:25:10.695 [work-thread-1] c.a.o.o.scheduler.tasks.CollectTask - scan finish ...
[INFO ] 16:25:10.695 [work-thread-0] c.a.o.oma.scheduler.tasks.ReportTask - report task report-DB-29e9e finish...
[INFO ] 16:25:10.695 [work-thread-1] c.a.o.o.scheduler.tasks.ScheduleTask - task : collect-DB -- 74eed finish ...
[INFO ] 16:25:10.695 [work-thread-0] c.a.o.o.scheduler.tasks.ScheduleTask - task : report-DB -- 29e9e finish ...
[INFO ] 16:25:15.612 [main] c.a.o.o.s.ScheduleServiceImpl - finished ...
[INFO ] 16:25:15.612 [main] c.a.o.o.s.ScheduleServiceImpl - finished running...
[INFO ] 16:25:15.637 [main] c.a.o.o.s.ScheduleServiceImpl - finished create report...
[ OBDBA(2|332) ] Progress: #################################################################################################### |100%

View the assessment report

After the assessment is completed, the following assessment report is displayed on the client:

OMA has completed the assessment. Assessment report briefing:
Task name: test_11g-20210527_162427 Start time: 2021.05.27 16:24:27 End time: 2021.05.27 16:25:15

SCHEMA: OBDBA Time spent: 47895 ms
+-------------------------------------------------------------------+
| schema: schema  | source: sourceDB      | target: targetDB      |
+-----------------+---------+---------+---------+---------+---------+
|  Object Type    |  pass   | convert | failure |  total  | percent |
+-----------------+---------+---------+---------+---------+---------+
| TABLE           | 186     | 0       | 1       | 187     |  99.5 % |
+-----------------+---------+---------+---------+---------+---------+
| PROCEDURE       | 4       | 0       | 0       | 4       |  100.0% |
+-----------------+---------+---------+---------+---------+---------+
| TRIGGER         | 0       | 0       | 1       | 1       |  0.0  % |
+-----------------+---------+---------+---------+---------+---------+
| PACKAGE         | 1       | 0       | 0       | 1       |  100.0% |
+-----------------+---------+---------+---------+---------+---------+
| SEQUENCE        | 2       | 0       | 0       | 2       |  100.0% |
+-----------------+---------+---------+---------+---------+---------+
| INDEX           | 127     | 0       | 0       | 127     |  100.0% |
+-----------------+---------+---------+---------+---------+---------+
| VIEW            | 9       | 0       | 0       | 9       |  100.0% |
+-----------------+---------+---------+---------+---------+---------+
| PACKAGE BODY    | 1       | 0       | 0       | 1       |  100.0% |
+-----------------+---------+---------+---------+---------+---------+

After the assessment is completed, the assessment result is written to the db/oma.sqlite file in the root directory of OMA. You can view the assessment report by using reportTool in the root directory of OMA.
1. Go to the reportTool directory, double-click the index.html file.
2. On the page that appears, click Authorize and View Report.
3. Select and open the oma.sqlite file.
  
  Generally, the oma.sqlite file is stored in the db folder under the root directory of OMA.
4. On the page that appears after you open the oma.sqlite file, click Select Report in the upper-right corner and select the migration assessment report that you want to view from the drop-down list.
  
  On the migration assessment page, you can click Migration Plan Recommendation, Object Assessment Overview, SQL Assessment Overview, or Database Profile to view more information.
  - Migration Plan Recommendation
  On the Migration Plan Recommendation tab, you can view information about migration feasibility analysis, cluster tenant recommendation, and distributed restructuring recommendation. OMA assesses the overall feasibility, migration risks, and compatibility to recommend an appropriate migration plan.
  - Object Assessment Overview
  On the Object Assessment Overview tab, you can view information in the Compatibility Overview and Compatibility Details sections.
  - SQL Assessment Overview
  On the SQL Assessment Overview tab, you can view information in the Compatibility Overview and Summary of SQL Incompatibilities sections.
  - Database Profile
  On the Database Profile tab, you can view information in the Database Instance Overview, Special SQL Statement Statistical Tables, Data Volume Analysis, and Abnormal SQL Statements in Original Database sections.

Assess the compatibility with MySQL objects

MySQL objects are assessed in the same way as Oracle objects, but the command parameters are different. MySQL 5.6, 5.7, and 8.0 are supported.

sh bin/start.sh \
# The task name, which can be set to any value.
--name test \    
# The analysis mode.
--mode ANALYZE \      
# The data source. DB indicates a database.           
--from-type DB \      
# The assessment mode.           
--evaluate-mode SOURCE_TARGET \  
# The type of the source database.
--source-db-type MYSQL \         
# The version of the source database.
--source-db-version 5.7 \        
# The address of the source database.
--source-db-host xxx.xxx.xxx.xxx \   
# The port of the source database.
--source-db-port port \      
# The username of the source database account.    
--source-db-user username \  
# The password of the source database account.         
--source-db-password password \       
# The schema to be assessed.
--schemas "test" \     
# The type of the destination database.          
--target-db-type OBMYSQL \       
# The version of the destination database.
--target-db-version 4.2.1

The assessment process and reports are the same as those for Oracle objects.

Assess the compatibility with PostgreSQL objects

If the source is a PostgreSQL database, the destination can be OBMYSQL, or OBORACLE V3.2.3 or later.

You can refer to the following example to assess the compatibility with OBMYSQL objects:

sh bin/start.sh \
# The task name, which can be set to any value.
--name task_name \   
# The analysis mode.   
--mode ANALYZE \   
# The data source. DB indicates a database.                       
--from-type DB \           
# The assessment mode.                                
--evaluate-mode SOURCE_TARGET \                 
# The type of the source database.              
--source-db-type POSTGRESQL \           
# The supported source database versions. Specify major versions without minor versions.
--source-db-version 10/11/12/13/14 \    
# The address of the source database.
--source-db-host xxx.xxx.xxx.xxx \        
# The port of the source database.
--source-db-port 3306 \   
# The username of the source database account.                
--source-db-user username \       
# The password of the source database account.          
--source-db-password password \         
# The schema to be assessed.  
# For a PostgreSQL database, a schema name must be prefixed with the database name. The two names are separated by a period (.).       
--schemas "postgres.public" \           
# The type of the destination database. The value must be OBMYSQL.   
--target-db-type OBMYSQL \                       
# The version of the destination database.
--target-db-version 4.2.1

You can refer to the following example to assess the compatibility with OBORACLE objects:

sh bin/start.sh \
# The task name, which can be set to any value.
--name task_name \   
# The analysis mode.   
--mode ANALYZE \   
# The data source. DB indicates a database.                     
--from-type DB \          
# The assessment mode.              
--evaluate-mode SOURCE_TARGET \        
# The type of the source database.
--source-db-type POSTGRESQL \           
# The supported source database versions. Specify major versions without minor versions.
--source-db-version 11 \    
# The address of the source database.
--source-db-host xxx.xxx.xxx.xxx \        
# The port of the source database.
--source-db-port 3306 \
# The username of the source database account.                
--source-db-user username \       
# The password of the source database account.          
--source-db-password password \      
# The schema to be assessed.
# For a PostgreSQL database, a schema name must be prefixed with the database name. The two names are separated by a period (.).       
--schemas "postgres.public" \           
# The type of the destination database. The value must be OBMYSQL.
--target-db-type OBORACLE \            
# The version of the destination database.
--target-db-version 4.2.1

Assess the compatibility with TiDB objects

You can refer to the following example to assess the compatibility with TiDB objects:

sh bin/start.sh \
# The task name, which can be set to any value.
--name task_name \      
# The analysis mode.
--mode ANALYZE \     
# The data source. DB indicates a database.                   
--from-type DB \           
# The assessment mode.             
--evaluate-mode SOURCE_TARGET \  
# The type of the source database.       
--source-db-type TIDB \    
# The supported source database versions. Specify major versions without minor versions.             
--source-db-version 3.0/4.0/5.0 \
# The address of the source database.      
--source-db-host xxx.xxx.xxx.xxx \
# The port of the source database.        
--source-db-port port \     
# The username of the source database account.            
--source-db-user username \
# The password of the source database account.                
--source-db-password password \
# The schema to be assessed. You can assess only one schema for a TiDB instance at a time.            
--schemas "yewu_test" \  
# The type of the destination database. The value must be OBMYSQL.               
--target-db-type OBMYSQL \        
# The version of the destination database.  
--target-db-version 4.2.1

Assess the compatibility with DB2 LUW objects

You can use one of the following two methods to assess the compatibility with DB2 LUW objects:

Use OMA to directly connect to the source database and collect the object to be assessed.
Use the db2look tool to extract the database objects to be assessed and transfer the objects to OMA.

If you use OMA to collect objects from the DB2 LUW database, refer to the following example:

sh bin/start.sh \
# The task name, which can be set to any value.
--name test11 \
# The analysis mode.
--mode ANALYZE \
# The data source. DB indicates a database.
--from-type DB \
# The assessment mode.
--evaluate-mode SOURCE_TARGET \
# The type of the source database.
--source-db-type DB2LUW \
# The version of the source database.
--source-db-version 11.5.0 \
# The address of the source database.
--source-db-host host \
# The port of the source database.
--source-db-port port \
# The username of the source database account.
--source-db-user user \
# The password of the source database account.
--source-db-password password \
# The name of the source database.
--source-db-name dbname \
# The schema to be assessed.
--schemas schema \
# The type of the destination database.
--target-db-type OBORACLE \
# The version of the destination database.
--target-db-version 4.2.1

If you use the db2look tool to extract objects from the DB2 LUW database, refer to the following example:

sh bin/start.sh \
# The task name, which can be set to any value.
--name textTest \    
# The analysis mode.            
--mode ANALYZE \
# The data source. TEXT indicates a file.                
--from-type TEXT \       
# The assessment mode.        
--evaluate-mode SOURCE_TARGET \  
# The location of the DDL file.
--source-file db2look.sql \      
# The type of the source database.
--source-db-type DB2LUW \     
# The version of the source database.   
--source-db-version 11.1 \      
# The schema in the source database.
--schemas db2inst1 \         
# The type of the destination database.    
--target-db-type OBORACLE \      
# The version of the destination database.
--target-db-version 4.2.1

OMA V3.4.0 and later can assess the compatibility for data migration from a DB2 LUW database to a MySQL tenant of OceanBase Database V3.2.3. OMA can assess the following items:

Data fields
DDL statements: including database tables, indexes, and partitions
SQL statements: including SQL syntax and functions

sh bin/start.sh \
# The task name, which can be set to any value.
--name test11 \
# The analysis mode.
--mode ANALYZE \
# The data source. DB indicates a database.
--from-type DB \
# The assessment mode.
--evaluate-mode SOURCE_TARGET \
# The type of the source database.
--source-db-type DB2LUW \
# The version of the source database.
--source-db-version 10.1.0 \
# The address of the source database.
--source-db-host host \
# The port of the source database.
--source-db-port port \
# The username of the source database account.
--source-db-user user \
# The password of the source database account.
--source-db-password password \
# The name of the source database.
--source-db-name dbname \
# The schema to be assessed.
--schemas "s1,s2" \
# The type of the destination database.
--target-db-type OBMYSQL \
# The version of the destination database.
--target-db-version 4.2.1

Assess the compatibility with RDS for MySQL objects

You can refer to the following example to assess the compatibility with RDS for MySQL objects:

sh bin/start.sh \
# The task name, which can be set to any value.
--name test \
# The analysis mode.
--mode ANALYZE \
# The data source. DB indicates a database.
--from-type DB \
# The assessment mode.  
--evaluate-mode SOURCE_TARGET \
# The type of the source database.
--source-db-type RDSMYSQL \
# The version of the source database.
--source-db-version X \
# The address of the source database.      
--source-db-host rds-public-address \
# The port of the source database.
--source-db-port port \
# The username of the source database account.
--source-db-user username \
# The password of the source database account.
--source-db-password password \
# The schema to be assessed.
--schemas "s1,s2"
# The type of the destination database.
--target-db-type OBMYSQL \
# The version of the destination database.
--target-db-version 4.2.1

Assess the compatibility with PolarDB for MySQL objects

You can refer to the following example to assess the compatibility with PolarDB for MySQL objects:

sh bin/start.sh \
# The task name, which can be set to any value.
--name test \
# The analysis mode.
--mode ANALYZE \
# The data source. DB indicates a database.     
--from-type DB \
# The assessment mode.    
--evaluate-mode SOURCE_TARGET \
# The type of the source database.   
--source-db-type POLARMYSQL \
# The version of the source database.
--source-db-version X \
# The address of the source database.
--source-db-host polardb-public-address \
# The port of the source database.   
--source-db-port port \
# The username of the source database account.   
--source-db-user username \
# The password of the source database account.                
--source-db-password password \
# The schema to be assessed.
--schemas "s1,s2"
# The type of the destination database.
--target-db-type OBMYSQL \
# The version of the destination database.
--target-db-version 4.2.1

Assess the compatibility with PolarDB for Oracle objects

You can refer to the following example to assess the compatibility with PolarDB for Oracle objects:

sh bin/start.sh \
# The task name, which can be set to any value.
--name test \
# The analysis mode.
--mode ANALYZE \
# The data source. DB indicates a database.
--from-type DB \
# The assessment mode.
--evaluate-mode SOURCE_TARGET \
# The type of the source database.
--source-db-type POLARO \
# The version of the source database.
--source-db-version X \
# The address of the source database.
--source-db-host polardb-public-address \
# The port of the source database.
--source-db-port port \
# The username of the source database account.
--source-db-user username \
# The password of the source database account.
--source-db-password xxxx \
# The schema to be assessed.
--schemas "s1,s2"
# The type of the destination database.
--target-db-type OBORACLE \
# The version of the destination database.
--target-db-version 4.2.1

Assess the compatibility with DRDS objects

OMA V3.4.0 and later allow you to assess the compatibility with DRDS objects by using commands, assess the compatibility with table definitions in DRDS, and convert the table definitions. Here is an example:

sh bin/start.sh \
# The task name, which can be set to any value.
--name test \
# The analysis mode.
--mode ANALYZE \
# The data source. DB indicates a database.
--from-type DB \
# The assessment mode.
--evaluate-mode SOURCE_TARGET \
# The type of the source database.
--source-db-type DRDS \
# The version of the source database.
--source-db-version 8.0 \
# The address of the source database.
--source-db-host host \
# The port of the source database.
--source-db-port port \
# The username of the source database account.
--source-db-user username \
# The password of the source database account.
--source-db-password password \
# Table name mapping, in the schema.oldTableName:schema.newTableName format. Separate multiple table name mappings with commas (,).
--table-map AAA:DDD,TTT:YYY \
# The schema to be assessed.
--schemas oma \
# The type of the destination database.
--target-db-type OBMYSQL \
# The version of the destination database.
--target-db-version 4.2.1

Assess the compatibility with SQL Server objects

OMA V3.4.0 and later allow you to assess the compatibility with SQL Server objects by using commands. You can refer to the following example to assess the compatibility with SQL Server objects:

sh bin/start.sh
# The task name, which can be set to any value.
--name testSQLServer \
# The analysis mode.
--mode ANALYZE \
# The data source. DB indicates a database.
--from-type DB \
# The assessment mode.
--evaluate-mode SOURCE_TARGET \
# The type of the source database.
--source-db-type SQLSERVER \
# The version of the source database.
--source-db-version X \
# The IP address of the source database.
--source-db-host host \
# The port number of the source database.
--source-db-port port \
# The username of the source database account.
--source-db-user username \
# The password of the source database account.
--source-db-password password \
# The type of the destination database.
--target-db-type OBORACLE \
# The version of the destination database.
--target-db-version 3.2.30

Assess the compatibility with openGauss objects

OMA V4.0.0 and later allow you to assess the compatibility with openGauss objects by using commands. You can refer to the following example to assess the compatibility with openGauss objects:

sh bin/start.sh \
# The task name, which can be set to any value.
--name task_name \   
# The analysis mode.   
--mode ANALYZE \   
# The data source. DB indicates a database.                       
--from-type DB \           
# The assessment mode.                                
--evaluate-mode SOURCE_TARGET \                 
# The type of the source database.              
--source-db-type GAUSSDB \           
# The supported source database versions. Specify major versions without minor versions.
--source-db-version X \    
# The address of the source database.
--source-db-host host \        
# The port of the source database.
--source-db-port port \   
# The username of the source database account.                
--source-db-user username \       
# The password of the source database account.          
--source-db-password password \         
# The schema to be assessed.  
# For an openGauss database, a schema name must be prefixed with the database name. Separate the database name and schema name with a period (.).       
--schemas "postgres.public" \           
# The type of the destination database. The value must be OBMYSQL.   
--target-db-type OBMYSQL \                       
# The version of the destination database.
--target-db-version 4.2.1

Assess the compatibility with objects in a MySQL tenant of OceanBase Database

OMA V4.2.2 and later allow you to assess the compatibility with objects in a MySQL tenant of OceanBase Database by using commands. You can refer to the following example to assess the compatibility with objects in a MySQL tenant of OceanBase Database:

sh bin/start.sh \
# The task name, which can be set to any value.
--name task_name \   
# The analysis mode.   
--mode ANALYZE \   
# The data source. DB indicates a database.                       
--from-type DB \           
# The assessment mode.                                
--evaluate-mode SOURCE_TARGET \                 
# The type of the source database.              
--source-db-type OBMYSQL \           
# The version of the source database.
--source-db-version 3.2.4 \    
# The address of the source database.
--source-db-host host \        
# The port of the source database.
--source-db-port port \   
# The username of the source database account.                
--source-db-user username \       
# The password of the source database account.          
--source-db-password password \         
# The schema to be assessed.  
--schemas "oma" \           
# The type of the destination database. The value must be OBMYSQL.   
--target-db-type OBMYSQL \                       
# The version of the destination database.
--target-db-version 4.2.1

Assess the compatibility with objects in an Oracle tenant of OceanBase Database

OMA V4.2.2 and later allow you to assess the compatibility with objects in an Oracle tenant of OceanBase Database by using commands. You can refer to the following example to assess the compatibility with objects in an Oracle tenant of OceanBase Database:

sh bin/start.sh \
# The task name, which can be set to any value.
--name task_name \   
# The analysis mode.   
--mode ANALYZE \   
# The data source. DB indicates a database.                       
--from-type DB \           
# The assessment mode.                                
--evaluate-mode SOURCE_TARGET \                 
# The type of the source database.              
--source-db-type OBORACLE \           
# The version of the source database.
--source-db-version 3.2.4 \    
# The address of the source database.
--source-db-host host \        
# The port of the source database.
--source-db-port port \   
# The username of the source database account.                
--source-db-user username \       
# The password of the source database account.          
--source-db-password password \         
# The schema to be assessed.        
--schemas "oma" \           
# The type of the destination database. The value must be OBORACLE.   
--target-db-type OBORACLE \                       
# The version of the destination database.
--target-db-version 4.2.1

Enterprise Edition

Assess the compatibility with objects