ApsaraDB for OceanBase:Collect database objects by using the OMA collector

Background

The OMA 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 OMA collector, run it in the intranet, collect the DDL statements of the corresponding schema objects, and then manually upload the objects to OMA 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 OMA collector can scan the DBA_OBJECTS view to obtain objects to be assessed.

• 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.

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 OMA collector

1. Log on to the ApsaraDB for OceanBase console.

2. In the left-side navigation pane, choose Migration Assessment > Assessment Tasks.

3. Click Download Collector in the upper-right corner of the Assessment Task page.

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

5. Run the OMA collector by decompressing it without installation.

   For example, the OMA collector package is decompressed to the oma-collector-1.2.5 directory. In this example, 1.2.5 is the version number of the OMA collector. Run the following command to decompress the package:

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

   After the package is decompressed, you can find the oma-collector-1.2.5.jar package and the dump folder in the directory.

Collect data

1. Go to the directory of the OMA collector.

   Run the following command to go to the oma-collector-1.2.5 directory:

   cd oma-collector-1.2.5

2. Run the following command to collect data:

   java -jar oma-collector-1.2.5.jar \  # The oma-collector-1.2.5.jar is only an example. You need to replace it with the actual file name. 
   -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 V5.6, V5.7, and V8.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.

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

4. View the results.

   OceanBase Migration Assessment Collector version : 1.2.5
   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 , cannot 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. The program will prompt that: Upload [.\dump\xxxx.oma.sql] to ApsaraDB for OceanBase [https://www.aliyun.com/product/oceanbase] for assessment.