Collect database objects using the migration evaluation data collector

Background information

The migration evaluation data collector is a tool used to collect user schema data for the migration evaluation service.

If you cannot connect to your Oracle instance over a public network, download the migration evaluation data collector and run it in your private network. After you collect the Data Definition Language (DDL) statements for the schema objects, you must manually upload the statements to the migration evaluation service for assessment.

Prerequisites

Ensure direct access to the Oracle database that you want to evaluate. The configured database user requires the following permissions:

• You must have at least the create session permission to establish a connection.

• The `select any dictionary` permission. The program scans the DBA_OBJECTS table to retrieve the objects for evaluation.

• The `select_catalog_role` role. This ensures that the program can use the DBMS_METADATA.GET_DDL function to obtain the DDL statements for the objects.

Run the following commands to check if the database user has the required permissions. This example assumes the Oracle database user for the evaluation task is named OMA.

-- The result of this SQL statement should include CONNECT, RESOURCE, and SELECT_CATALOG_ROLE
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'OMA';

-- The result of this SQL statement should include CREATE SESSION and SELECT ANY DICTIONARY
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'OMA';

To grant these permissions to the Oracle database user OMA, run the following statements.

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

Install the migration evaluation data collector

1. Log on to the OceanBase Management Console .

2. In the navigation pane on the left, click Migration Evaluation > Compatibility Assessment.

3. On the Assessment Tasks page, click Download Data Collector in the upper-right corner.

4. In the Download Data Collector dialog box, click Click to Download.

5. After you download the migration evaluation data collector, decompress the file to complete the installation.

   tar zxvf collector.tar.gz
   cd oma-collector-1.0.0

   After decompression, the directory contains the oma-collector-1.0.0.jar file and the dump folder.

Collect data

1. Navigate to the migration evaluation folder.

   cd oma-collector-1.0.0

2. Run the following command to collect data.

   java -jar oma-collector-1.0.0.jar \  
   -t ORACLE \                          # The database type. Valid values: ORACLE and MYSQL. The value must be in uppercase.
   -v 11g \                             # The database version. For Oracle, valid values are 11g, 12c, 18c, and 19c. For MySQL, valid values are 5.6, 5.7, and 8.0.
   -h 10.10.10.1 \                      # The database address.
   -P 1111 \                            # The database port.
   -u abcd \                            # The logon username.
   -p \                                 # The password of the logon user. After you press Enter, you are prompted to enter the password.
   -sn orcl11g.aa.oracle.com \          # The service name. If you connect using a system ID (SID), use the -sid parameter instead of -sn.
   -s "ABCD,AAA"                       # The schemas to scan. The value must be in uppercase.

3. Press Enter and enter the password for the source database.

4. Check the output.

   OceanBase Migration Assessment Collector version : 1.0.0
   Power by OceanBase Ant Group
   [INFO ] 17:10:50.320 [main] c.alibaba.druid.pool.DruidDataSource - {dataSource-1} inited
   [INFO ] 17:10:51.794 [main] c.a.o.o.c.scan.OracleDDLScanner - ALL OBJECT COUNT [384]
   [ OBDBA(384|384) ] Progress: ################################################## |100%
   [INFO ] 17:11:19.040 [main] c.a.o.oma.collector.OmaCollector - scan finished ... close the file stream
   [INFO ] 17:11:19.047 [main] c.a.o.oma.collector.OmaCollector -  OBDBA scan finish , write to file [.\dump\xxxx.oma.sql]
   [INFO ] 17:11:19.246 [main] c.a.o.o.c.scan.OracleDDLScanner - ALL OBJECT COUNT [0]
   [INFO ] 17:11:19.246 [main] c.a.o.oma.collector.OmaCollector - scan finished ... close the file stream
   [INFO ] 17:11:19.262 [main] c.a.o.oma.collector.OmaCollector -  AAA scan finish , can not found any object
   [INFO ] 17:11:19.262 [main] c.a.o.oma.collector.OmaCollector - Please upload [.\dump\xxxx.oma.sql] to the OceanBase public cloud environment [https://www.aliyun.com/product/oceanbase] to complete the assessment.

   After the command finishes running, a collection file named xxxx.oma.sql is generated in the dump directory. The program displays the following message: Please upload [.\dump\xxxx.oma.sql] to the OceanBase public cloud environment [https://www.aliyun.com/product/oceanbase] to complete the assessment.