Assess the compatibility with objects|V3.3.2|OceanBase Migration Assessment|docs|OceanBase Documentations

OceanBase Migration Assessment (OMA) can assess the compatibility of OceanBase Database with Oracle, MySQL, PostgreSQL, TiDB, and DB2 LUW databases and provide a compatibility report. 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

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

-- 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 with reference 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 IP address of the source database.
--source-db-host 10.10.10.1 \    
# The port number of the source database.
--source-db-port port \      
# The username of the source database.    
--source-db-user username \      
# The password of the source database.     
--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 2.2.70

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 assessment reports

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, a folder and a package with the task name are generated in the report directory, which contain the assessment reports of the current task. The folder contains two HTML files. The index.html file is the complete assessment report. The basic.html file is a brief assessment report.

For known errors, check the causes of the incompatibility and suggestions for modifications in the report. For unknown errors, contact technical support engineers of OMA.

Assess the compatibility with MySQL objects

MySQL objects are assessed in the same way as for 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 IP address of the source database.
--source-db-host 10.10.10.1 \   
# The port number of the source database.
--source-db-port port \      
# The username of the source database.    
--source-db-user username \  
# The password of the source database.         
--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 2.2.x

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

Assess the compatibility with PostgreSQL objects

Example for assessing the compatibility with PostgreSQL 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. Configure a major version but not a minor version.
--source-db-version 10/11/12/13/14 \    
# The IP address of the source database.
--source-db-host 10.10.10.1 \        
# The port number of the source database.
--source-db-port 3306 \
# The username of the source database.                
--source-db-user username \       
# The password of the source database.          
--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. The value must be 2.2.x.  
--target-db-version 2.2.x

Assess the compatibility with TiDB objects

Example for assessing 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. Configure a major version but not a minor version.             
--source-db-version 3.0/4.0/5.0 \
# The IP address of the source database.      
--source-db-host 10.10.10.1 \
# The port number of the source database.        
--source-db-port port \     
# The username of the source database.            
--source-db-user username \
# The password of the source database.                
--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. The value must be 2.2.x.      
--target-db-version 2.2.x

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, execute the following statement:

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 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.
--source-db-user user \
# The password of the source database.
--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 3.1.20

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

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 of the source database.
--schemas db2inst1 \         
# The type of the destination database.    
--target-db-type OBORACLE \      
# The version of the destination database.
--target-db-version 3.1.20

Assess the compatibility with RDS for MySQL objects

You can execute the following statements 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 IP address of the source database.      
--source-db-host rds-public-address \
# The port number of the source database.
--source-db-port port \
# The username of the source database.
--source-db-user username \
# The password of the source database.
--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 3.2.2

Assess the compatibility with PolarDB for MySQL objects

You can execute the following statements 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 IP address of the source database.
--source-db-host polardb-public-address \
# The port number of the source database.   
--source-db-port port \
# The username of the source database.   
--source-db-user username \
# The password of the source database.                
--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 3.2.2

Assess the compatibility with PolarDB for Oracle objects

You can execute the following statements 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 IP address of the source database.
--source-db-host polardb-public-address \
# The port number of the source database.
--source-db-port port \
# The username of the source database.
--source-db-user username \
# The password of the source database.
--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 3.1.2