Collect database objects by using the migration assessment collector - ApsaraDB for OceanBase

You can manually collect database object definitions or use the migration assessment collector, which automatically collects database objects. This topic describes how to use the migration assessment collector to collect database objects.

Background

The migration assessment collector is a tool that collects user schema data on Alibaba Cloud.

In an Alibaba Cloud environment, you cannot connect to your Oracle instances over the Internet. You need to download the migration assessment collector, run it in the intranet, collect the DDL statements of the corresponding schema objects, and then manually upload the objects to the migration assessment service for assessment.

Prerequisites

The Oracle database to be assessed can be directly accessed. The configured database user must have the following privileges:

The CREATE SESSION privilege to ensure normal connection.
The SELECT ANY DICTIONARY privilege to ensure that the migration assessment collector can scan the DBA_OBJECTS view to obtain objects to be assessed.
The select_catalog_role role to ensure that the migration assessment collector can call the DBMS_METADATA.GET_DDL function to obtain the DDL statements of corresponding objects.

You can execute the following statement to check whether the database user has the required privileges. The following takes the Oracle database user "OMA" as an example.

-- 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 preceding privileges to the Oracle database user "OMA", execute the following statements:

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

Install the migration assessment collector

Important

The migration assessment collector is written in Java and depends on Java Development Kit (JDK) 1.8 and later.

Log on to the ApsaraDB for OceanBase console.
In the left-side navigation pane, choose Migration Assessment > Compatibility Assessment.
On the Assessment Tasks page, click Download Collector in the upper-right corner.
In the Download Collector dialog box that appears, click Click to Download.
Run the migration assessment collector by decompressing it without installation.
```
tar zxvf collector.tar.gz
cd oma-collector-1.0.0
```
After the package is decompressed, you can find the oma-collector-1.0.0.jar package and the dump folder in the directory.

Collect data

Go to the directory of the migration assessment collector.
```
cd oma-collector-1.0.0
```

Run the following command to collect data:

java -jar oma-collector-1.0.0.jar \  
-t ORACLE \                          # The database type, which is Oracle or MySQL. You must specify the database type in uppercase. 
-v 11g \                             # The version of the database. Oracle databases 11g, 12c, 18c, and 19c, and MySQL databases 5.6, 5.7, and 8.0 are supported. 
-h 10.10.10.1 \                      # The IP address of the database. 
-P 1111 \                            # The port number of the database. 
-u abcd \                            # The logon username. 
-p \                                 # The logon password. You need to enter the password manually after pressing the Enter key. 
-sn orcl11g.aa.oracle.com \          # The service name. If SID is used for connection, replace -sn with -sid. 
-s "ABCD,AAA"                       # The schema to be scanned. Uppercase letters are required.

Press the Enter key and enter the password of the source database.

View the results.

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 - Upload [.\dump\xxxx.oma.sql] to ApsaraDB for OceanBase [https://www.aliyun.com/product/oceanbase] for assessment.

After the data is collected, the collection file xxxx.oma.sql is generated in the dump directory. You will be prompted to upload the file to ApsaraDB for OceanBase for assessment, as shown in the preceding return results.