Dontcheff

An example of the Oracle Autonomous Database Advisor

In Autonomous, DBA, Init.ora, New features, Oracle database on June 22, 2020 at 08:33

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?

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 exist

If 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 guidance

Here 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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: