OceanBase Migration Assessment (OMA) allows you to assess the compatibility with SQL statements and PL/SQL statements.
Background
Run start.sh with reference to the sample command in this topic. For more information about the parameters in the script, see Product form. In Microsoft Windows, replace sh bin/start.sh with start.bat.
Connect to an Oracle database to assess the compatibility with its SQL statements
Before you connect to an Oracle database and assess the compatibility with SQL statements, if you use OMA V3.2.1-BP9 and later, you can configure the OracleCheckerConfig.json configuration file in the config/ directory of the OMA installation directory to specify the schemas and tables to be skipped. The following example shows the content of a OracleCheckerConfig.json file:
{
"skipSchemaPattern": "",
"skipSchemaList": [],
"skipTablePattern": "SYS_EXPORT*|ET\\$*|GV\\$*|V\\$",
"skipTableList": ["x$kccic", "user$"],
"skipHintPattern":"",
"skipHintList": []
}
| Parameter | Description |
|---|---|
| skipSchemaPattern | The regular expression of schemas to be skipped. |
| skipSchemaList | The list of schemas to be skipped. |
| skipTablePattern | The regular expression of tables to be skipped. |
| skipTableList | The list of tables to be skipped. |
| skipHintPattern | The hints. SQL statements that contain the specified hints are skipped. Usually, these SQL statements are executed by Oracle database tools. |
| skipHintList | The hints. SQL statements that contain the specified hints are skipped. Usually, these SQL statements are executed by Oracle database tools. |
The searching and matching process is not case-sensitive. If an SQL statement contains the specified schema, table, or hint, OMA does not assess the statement and tags it as compatible.
At present, OMA pulls SQL and PL/SQL statements from the V$SQL view of the Oracle database for compatibility assessment. The assessment process and reports are the same as those for Oracle objects.
sh bin/start.sh \
# The task name, which can be set to any value.
--name test_11g \
# The analysis mode.
--mode ANALYZE \
# The data source. COLLECT indicates that the data is collected from a database.
--from-type COLLECT \
# The assessment method. For an Oracle database, use the SOURCE_TARGET 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.
--source-db-user username \
# The password of the source database.
--source-db-password password \
# The service name of the source database. This parameter can be replaced with --source-db-sid, which indicates the SID.
--source-db-service-name orcl11g.us.oracle.com \
# The type of the destination database.
--target-db-type OBORACLE \
# The version of the destination database. 2.2.50 indicates V2.2.5x, 2.2.70 indicates V2.2.7x, and 3.1.20 indicates V3.1.x.
--target-db-version 3.1.20 \
# The SQL statements to be collected. You can specify whether to collect SQL statements from the V$SQL or SQLArea view.
--scan-sql "\"SELECT sql_fulltext FROM v\\\$sql where rownum < 10\""
Notice
When you use the syntax --scan-sql ""SELECT sql_fulltext FROM v\$sql where rownum < 10"", note the following items:
You must have the required privileges on the corresponding view.
The content specified in the
sql_fulltextfield will be collected. Therefore,SELECT sql_fulltextis required.The dollar sign ($) in
v$sqlwill be escaped multiple times. Therefore, enter three backslashes (\\\) before it.
Assess the compatibility with SQL statements in a TEXT file
OMA can obtain SQL statements from a TEXT file for assessment. The assessment process and reports are the same as those for Oracle objects.
Multiple SQL statements in a TEXT file must be separated with double dollar signs ($$). Example:
select biz_param_code , biz_param_val , biz_param_chn FROM biz_parameter WHERE biz_param_code = 'b0';
$$
select biz_param_code , biz_param_val , biz_param_chn INTO :b0 , :b1 , :b2 FROM biz_parameter WHERE biz_param_code = :b0
$$
select biz_param_code , biz_param_val , biz_param_chn FROM biz_parameter WHERE biz_param_code = 'b0';
$$
Assessment in Oracle mode
sh bin/start.sh \
# The task name, which can be set to any value.
--name test \
# The analysis mode.
--mode ANALYZE \
# The data source. You can also set this parameter to MYBATIS or IBATIS.
--from-type TEXT \
# The assessment mode.
--evaluate-mode SOURCE_TARGET \
# The path of the source file.
--source-file "/home/admin/oma/1table.sql" \
# The type of the source database.
--source-db-type ORACLE \
# The version of the source database.
--source-db-version 11g \
# The schema to be assessed.
--schemas DEFAULT \
# The type of the destination database.
--target-db-type OBORACLE \
# The version of the destination database
# 2.2.50 indicates V2.2.5x, 2.2.70 indicates V2.2.7x, 3.1.20 indicates V3.x, and 3.2.1 indicates V3.2.x.
--target-db-version 2.2.70 \
# The number of parallel threads in the assessment task.
--process-thread-count 5
Assessment in MySQL mode
sh bin/start.sh \
# The task name, which can be set to any value.
--name test \
# The analysis mode.
--mode ANALYZE \
# The data source. You can also set this parameter to MYBATIS or IBATIS.
--from-type TEXT \
# The assessment mode.
--evaluate-mode SOURCE_TARGET \
# The path of the source file.
--source-file "/home/admin/oma/a.sql" \
# The type of the source database.
--source-db-type MYSQL \
# The version of the source database.
--source-db-version 5.7 \
# The schema to be assessed.
--schemas DEFAULT \
# The type of the destination database.
--target-db-type OBMYSQL \
# The version of the destination database
--target-db-version 2.2.x \
# The number of parallel threads in the assessment task.
--process-thread-count 5
Assess the compatibility with SQL statements in a MyBatis file
OMA can scan your MyBatis SqlMap configuration file to directly extract the SQL statements executed by the applications from the configuration file, and assess the compatibility with the SQL statements.
--source-file configuration method
OMA can scan your MyBatis SqlMap configuration file to directly extract the SQL statements executed by the applications from the configuration file, and assess the compatibility with the SQL statements. You can use the --source-file parameter to specify a single file to be assessed or the directory that stores multiple files to be assessed. The specified directory can be an absolute path or a relative path, provided that OMA can access your SqlMap configuration file through the path. 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.
--from-type MYBATIS \
# The assessment mode.
--evaluate-mode SOURCE_TARGET \
# The path of the source file.
--source-file ./oma/mybatis/ \
# The type of the source database.
--source-db-type ORACLE \
# The version of the source database.
--source-db-version 11g \
# The schema to be assessed.
--schemas DEFAULT \
# The type of the destination database.
--target-db-type OBORACLE \
# The version of the destination database
--target-db-version 3.1.20
--mapper-config configuration method
Sometimes you may reference external variables in your SqlMap configuration file. For example, you may reference the petColumnList variable defined in the B.xml configuration file for your A.xml configuration file, as shown in the following example:
<!-- Fragment of the A.xml configuration file -->
<!-- Referencing a sql fragment and a result map in another file -->
<select id="selectPet" parameterType="java.lang.Integer"
resultMap="com.alipay.oceanbase.apache.ibatis.submitted.xml_external_ref.PetMapper.petResult">
SELECT
<include
refid="com.alipay.oceanbase.apache.ibatis.submitted.xml_external_ref.PetMapper.petColumnList"
/>
FROM pet
WHERE pet_id = #{id}
</select>
<!-- Fragment of the B.xml configuration file -->
<sql id="petColumnList">
pet_id, owner_id, pet_name
</sql>
In this scenario, you can specify another SqlMap configuration file C.xml by using the --mapper-config parameter. The C.xml file contains information about the referencing statements and referenced XML files. Specify the absolute path of the SqlMap configuration file in URL format. Example:
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<mappers>
<mapper url="file:///home/admin/oma/mybatis/A.xml"/>
<mapper url="file:///home/admin/oma/mybatis/B.xml"/>
</mappers>
</configuration>
Assume that the name of the SqlMap configuration file C.xml is MapperConfig.xml. Execute the following statement to run OMA:
sh bin/start.sh \
# The task name, which can be set to any value.
--name mybatisTest \
# The analysis mode.
--mode ANALYZE \
# The data source.
--from-type IBATIS \
# The assessment mode.
--evaluate-mode SOURCE_TARGET \
# The name of the SqlMap configuration file.
--mapper-config MapperConfig.xml \
# The type of the source database.
--source-db-type ORACLE \
# The version of the source database.
--source-db-version 11g \
# The schema to be assessed.
--schemas DEFAULT \
# The type of the destination database.
--target-db-type OBORACLE \
# The version of the destination database
--target-db-version 2.2.70 \
# The number of parallel threads in the assessment task.
--process-thread-count 5
When you specify an SqlMap configuration file by using the --mapper-config parameter, you can use either the absolute path or relative path of the file, provided that OMA can access the file through the path.
Assess the compatibility with SQL statements in an iBatis file
OMA can scan your iBatis SqlMap configuration file to directly extract the SQL statements executed by the applications from the file, and assess the compatibility with the SQL statements.
Set the --source-file parameter to the directory where your SqlMap configuration file is stored. The directory can be an absolute path or a relative path, provided that OMA can access your SqlMap configuration file through the path. 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.
--from-type IBATIS \
# The assessment mode.
--evaluate-mode SOURCE_TARGET \
# The path of the source file.
--source-file ./oma/ibatis/ \
# The type of the source database.
--source-db-type ORACLE \
# The version of the source database.
--source-db-version 11g \
# The schema to be assessed.
--schemas DEFAULT \
# The type of the destination database.
--target-db-type OBORACLE \
# The version of the destination database
--target-db-version 3.1.20