PolarDB structure and privilege verification tool - PolarDB

After you migrate Oracle workloads to PolarDB for PostgreSQL (Compatible with Oracle), you must confirm that the destination database matches the Oracle source database in both object structure and user privileges. The PolarDB structure and privilege verification tool compares the object structures and the users, roles, and privileges on both sides, and generates HTML verification reports to help you quickly identify migration differences.

Scenarios

This tool applies to the following two scenarios:

After you complete structure migration from Oracle to PolarDB by using Data Transmission Service (DTS), use this tool to compare the object structures of the source and destination databases.
After you use the migration tool described in Migrate roles, users, and permissions from Oracle to PolarDB for PostgreSQL (Compatible with Oracle) to migrate Oracle users, roles, and privileges to PolarDB, use this tool to compare the users and privileges on both sides.

The tool only reads metadata and generates reports. It does not modify business objects in the Oracle source database or the PolarDB destination database.

Scope of application

Oracle source database requirements

The verification account must have at least the CONNECT, CREATE SESSION, and SELECT_CATALOG_ROLE privileges. Example:

GRANT CONNECT, CREATE SESSION, SELECT_CATALOG_ROLE TO <Oracle username>;

PolarDB destination database requirements

The destination database is PolarDB for PostgreSQL (Compatible with Oracle).
The account that connects to PolarDB must have permissions to query compatibility views such as DBA_USERS, DBA_ROLES, DBA_ROLE_PRIVS, DBA_SYS_PRIVS, DBA_TAB_PRIVS, and DBA_OBJECTS.

Note

Before you run structure verification, use Data Transmission Service (DTS) to complete structure migration from Oracle to PolarDB.
Before you run privilege verification, use the migration tool described in Migrate roles, users, and permissions from Oracle to PolarDB for PostgreSQL (Compatible with Oracle) to complete user, role, and privilege migration.

Runtime environment requirements

Deploy the tool on a machine that can connect to both Oracle and PolarDB at the same time, such as an Elastic Compute Service (ECS) instance. The runtime environment requires Java 1.8 or later.

Deploy the verification tool

Download the verification tool JAR file polardb-schema-verification-20260827.jar, and upload it to the machine that meets the runtime environment requirements described above.
Run the following command to check the tool version:
```
java -jar polardb-schema-verification.jar --version
```
Create a config.json file with the following content. Adjust the database connection strings and the log directory based on your environment. The verification process requires this configuration file.
```
{
  "source": {
    "url": "jdbc:oracle:thin:@//<Oracle host IP>:<port>/<service name>",
    "username": "<Oracle username>",
    "password": "<Oracle password>"
  },
  "destination": {
    "url": "jdbc:polardb://<PolarDB cluster endpoint>:<port>/<database name>",
    "username": "<PolarDB username>",
    "password": "<PolarDB password>"
  },
  "log": {
    "dir": "/path/to/log"
  },
  "report": {
    "dir": "/path/to/report"
  },
  "check": {
    "schemas": ["APP1", "APP2"]
  }
}
```
- source: the connection to the Oracle source database.
- destination: the connection to the PolarDB destination database. Specify the endpoint of the PolarDB cluster.
- log.dir: the directory for verification logs.
- report.dir: the directory for the generated verification reports.
- check.schemas: the list of schemas to verify. If you omit this field, set it to empty, or set it to "schemas": ["*"], all schemas are verified.

Structure verification

Structure verification assumes that you have used DTS to complete structure migration from Oracle to PolarDB. Before you run the verification, confirm that the config.json field in check.schemas specifies the intended verification scope.

Run the verification

Run the following command to compare objects such as tables, views, indexes, sequences, functions, stored procedures, and packages between Oracle and PolarDB:

java -jar polardb-schema-verification.jar --mode structure --config config.json

After the command completes, the tool generates the report.dir verification report in the structure_comparison_<database>_<timestamp>.html directory.

Interpret the report

The structure verification report includes the following content:

Schemas: compares the schemas of Oracle and PolarDB and shows the In Both, Only in Oracle, and Only in PolarDB states. The built-in schemas of Oracle and PolarDB remain on this tab, so that you can check the system schema differences between the two sides.
Object Distribution Summary: summarizes, by schema and object type, the number of objects that exist on both sides, exist only in Oracle, exist only in PolarDB, and are different.
Objects: shows object details, including the schema, object name, sub-object, object type, comparison result, and reason.
Filter: filters by schema, object type, and comparison result. You can also hide built-in objects, partitioned objects, materialized view tables, and invalid objects so that objects generated by partitioning or materialization do not obscure real differences.

The comparison states have the following meanings:

In Both: the object or schema exists on both sides.
Only in Oracle, Only in PolarDB: the object or schema exists only on the corresponding side.
Different: the object properties are different.

Privilege verification

Privilege verification assumes that you have used the migration tool described in Migrate roles, users, and permissions from Oracle to PolarDB for PostgreSQL (Compatible with Oracle) to complete user, role, and privilege migration. Before you run the verification, confirm that the config.json field in check.schemas specifies the intended verification scope.

Run the verification

Run the following command to compare the users, roles, and privileges between Oracle and PolarDB:

java -jar polardb-schema-verification.jar --mode user --config config.json

After the command completes, the tool generates the report.dir verification report in the user_privilege_<database>_<timestamp>.html directory.

Interpret the report

The privilege verification report includes the following tabs:

Users: compares users and their associated profiles.
Roles: compares roles.
Role Privileges: compares the roles granted to users or roles.
System Privileges: compares system privileges.
Table Privileges: compares object privileges on tables, views, functions, and other objects. The verification scope is controlled by check.schemas.

Each tab supports filtering by comparison result and keyword. Select Hide Built-in Objects to hide built-in users, built-in roles, and their associated privilege records.