This post is part of a series of blog posts on the Oracle Autonomous Database.

There is a new tool called “Oracle Autonomous Database Schema Advisor” that can help answer questions such as:

• What issues will I encounter during migration?
• What objects are incompatible / cannot be migrated to ADB? How do I know?
• What beforehand advice and guidelines can I receive prior to the migration to Autonomous Database?

All the details are in MOS Doc ID 2462677.1

First, you need to download and install the advisor. Run the script install_adb_advisor.sql (as SYSDBA) which you download from the MOS note above. The script will create a user, 7 tables, 4 indexes and a package. I have decided to call the user adb_advisor and the password will be XXXXX:

SQL> @install_adb_advisor.sql adb_advisor XXXXX

…

Index created.
Package created.
Package body created.
No errors.

Next, run the advisor as the user created in step one. What you need to specify is (1) the database schema/s and (2) the ADB type you will be using in the Autonomous Cloud. You can list maximum 30 schemas in a single advisor run or just use schemas=>’ALL’. And these are the 4 options for the ADB type:

– ATP for Autonomous Transaction Processing (Shared)
– ADW for Autonomous Data Warehouse (Shared)
– ATPD for Autonomous Transaction Processing (Dedicated)
– ADWD for Autonomous Data Warehouse (Dedicated)

Do not try to run the script as SYSDBA, you will be getting all sorts of errors.

e.g. ADB_ADVISOR.REPORT(schemas=>’ALL’, adb_type=>’ATP’);

The Advisor will generate a report with the following information:

– The counts of discovered objects and a summary of migration status

– Objects that cannot be migrated due to the restrictions and lockdowns imposed by the Autonomous Database on certain data types, database options and SQL statements

– The objects that will migrate with modifications that are automatically made during the import process or upon the execution of object creation DDL

– Informational section containing certain best practice recommendations and guidance

Common issues detected by the advisor:

–User-defined objects in the SYS schema. They will not be migrated.
– IOT table cannot be migrated as-is. It will be migrated as a normal, regular table.
– INMEMORY table cannot be migrated as-is. It will be created as a NO INMEMORY table.
– Modified init.ora parameters in your source database that you would not be permitted to alter in ADB. e.g. db_block_checksum, enable_ddl_logging, encrypt_new_tablespaces, local_listener, tablespace_encryption_default_algorithm, tde_configuration, use_large_pages

By default, the output gets truncated when the number of rows exceeds the maximum limit set in the Advisor package. You can reset the number of rows by running the following command prior to running the Advisor.

SQL> exec ADB_ADVISOR.setmaxrows(500);
You have to reset the max rows every time you run the Advsior as the settings is not saved in the database.

My recommendation is that, before you migrate your schemas to Autonomous Cloud, to run the advisor. It will minimize your post-migration hassle.