Moving the database to Oracle Autonomous Cloud is a rather simple task but how do you avoid possible issues after the migration? How do you know in advance which objects will not at all migrate to Autonomous or which objects will be migrated with some changes? How do you get beforehand advice and guidelines on the migration to Autonomous?
Note from January 13th, 2022: Cloud Premigration Advisor Tool (CPAT) Analyzes Databases for Suitability of Cloud Migration (Doc ID 2758371.1) is the new/replaced tool. The link below for the “Oracle Autonomous Database Schema Advisor” is not working and the note is missing.
The new tool called “Oracle Autonomous Database Schema Advisor” can give you the answers to these questions.
All the details are in MOS Doc ID 2462677.1 but here are the basics and an example in which I will be moving my 20c schema to ATP Dedicated.
First, you need to install the advisor. Meaning 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 tiger:
[oracle@julian ~]$ sqlplus "/ as sysdba" SQL*Plus: Release 20.0.0.0.0 - Production on Thu Jun 18 07:24:31 2020 Version 20.2.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 20c EE High Perf Release 20.0.0.0.0 - Production Version 20.2.0.0.0 SQL> @install_adb_advisor.sql adb_advisor tiger ... Index created. Package created. Package body created. No errors. SQL>Second, you run the advisor as the user created in step one. What you need to specify is (1) the database schemas 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 (Serverless)
– ADW for Autonomous Data Warehouse (Serverless)
– 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 (I found it the hard way), something like:
ORA-06598: insufficient INHERIT PRIVILEGES privilege
PLS-00201: identifier ‘ADB_ADVISOR.REPORT’ must be declared
ORA-00942: table or view does not existIf the package complies with a loop index error and ORA-00942, just run GRANT SELECT ON DBA_XML_TABLES TO ADB_ADVISOR; and recompile.
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 guidanceHere is the output after I ran ADB_ADVISOR.REPORT:
SET SERVEROUTPUT ON FORMAT WRAPPED SET LINES 200 exec ADB_ADVISOR.REPORT(schemas=>'ALL', adb_type=>'ATPD'); PL/SQL procedure successfully completed. ========================================================================================== == ATPD SCHEMA MIGRATION REPORT FOR PDBADMIN,pdbuser,JULIAN ========================================================================================== ADB Advisor Version : 19.3.0.0.2 Instance Name : DB0223 Database Name : DB0223 Host Name : julian Database Version : 20.0.0.0.0 Pluggable Database : NOVOPDB1 Schemas Analyzed : PDBADMIN,PDBUSER,JULIAN Analyzing for : Autonomous Transaction Processing (Dedicated) Report Start date/time: 18-JUN-2020 07:47 ------------------------------------------------------------------------------------------ -- SECTION 1: SUMMARY ------------------------------------------------------------------------------------------ Objects Objects Total Object Will Not Will Migrate Objects Object Type Count Migrate With Changes Will Migrate ------------------------- -------------- -------------- -------------- -------------- CONSTRAINT 8 0 0 8 INDEX 4 0 0 4 INDEX PARTITION 4 0 0 4 TABLE 8 0 2 8 TABLE PARTITION 4 0 0 4 User Objects in SYS 87 87 0 0 User Objects in SYSTEM 0 0 0 0 ------------------------------------------------------------------------------------------ -- SECTION 2: FOLLOWING OBJECTS WILL NOT MIGRATE ------------------------------------------------------------------------------------------ 1) User-defined objects in SYS schema will not migrate (Count=87): ------------------------------------------------------------------ Note: User-defined objects were detected in SYS schema. Consider moving them out of SYS prior to migration. Owner Object Type Object Name ---------- ------------------------------ ---------------------------------------- SYS INDEX SYS_C008044 SYS TABLE AQ_SRVNTFN_TABLE_1 .... SYS EVALUATION CONTEXT AQ$_KUPC$DATAPUMP_QUETAB_1_V ... SYS MATERIALIZED ZONEMAP RDBMS_ZMAP SYS JOB ORA$_ATSK_AUTOZM SYS TABLE WRI$_ADV_OBJSPACE_TREND_DATA SYS TABLE WRI$_ADV_OBJSPACE_CHROW_DATA SYS TABLE WRI$_ADV_SEGADV_SEGROW ------------------------------------------------------------------------------------------ -- SECTION 3: FOLLOWING OBJECTS WILL MIGRATE WITH CHANGES ------------------------------------------------------------------------------------------ 1) Index Organized table will be created as regular table (Count=1): -------------------------------------------------------------------- Note: Index Organized tables are disallowed in ADB. When you create an IOT in ADB, the table gets created as non-IOT (regular table). When the Data Pump export file contains tables with IOT, use 'dwcs_cvt_iots:y' transformation at import time to transform IOTs as regular tables. JULIAN.PTIOT1 2) INMEMORY Tables will be created as NO INMEMORY Tables (Count=1): ------------------------------------------------------------------- Note: Database In-Memory is not enabled in ADB. All In-Memory tables and partitions will be created with NO INMEMORY clause. JULIAN.SALES ------------------------------------------------------------------------------------------ -- SECTION 4: MIGRATION ADDITIONAL INFO ------------------------------------------------------------------------------------------ 1) Database Parameters are detected as modified in the current database but can't be modified in the ADB (Count=13): -------------------------------------------------------------------------------------------------------------------- Note: The following init parameters are modified in your database that you would not be able to modify in ADB. Please refer to the Oracle Autonomous Database documentation on the parameters that you are allowed to modify. _exadata_feature_on _gc_policy_time _gc_undo_affinity _zonemap_auto_processing clonedb control_management_pack_access db_block_checksum enable_ddl_logging encrypt_new_tablespaces local_listener tablespace_encryption_default_algorithm tde_configuration use_large_pages ------------------------------------------------------------------------------------------ -- END OF REPORT ------------------------------------------------------------------------------------------ Report End Datetime : 18-JUN-2020 07:47 Report Runtime : +000000000 03:00:02.125000000 ------------------------------------------------------------------------------------------So, I am facing based on the report above the following 4 issues:
– I have user-defined objects in the SYS schema. They will not be migrated.
– My IOT table cannot be migrated as-is. It will be migrated as an normal, regular table.
– My INMEMORY table cannot be migrated as-is. It will be created as a NO INMEMORY table.
– I cannot use some of my init.ora parameters in ADB-D.My recommendation is that, before you migrate your schemas to Autonomous Cloud, to run the advisor. It will minimize your post-migration hassle.
Few additional comments about the advisor:
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.
If you want to query the data dictionary for the output, you may try:
SELECT a.owner, a.object_type, a.object_name, c.* FROM adb_advisor_objects_tmp a, adb_advisor_rejects_tmp b, adb_advisor_codes_tmp c WHERE a.id = b.id AND b.cd = c.cd;Final note: you can run the advisor on any database version from 10g to 20c!
Julian Dontcheff's Database Blog 