Oracle Autonomous Database Schema Advisor
The
ADB Schema Advisor is a light-weight utility that analyzes your on-premise or
cloud Oracle Database schemas for the suitability of migration to the Oracle
Autonomous Database. The Advisor discovers the schema objects and performs deep
analysis to highlight the differences when the object gets created on Oracle
Autonomous Data Warehouse or Oracle Autonomous Transaction Processing database,
including the reasons when the object cannot be created.
The Advisor will run on pre-existing Schema and
generates a report that highlights:
- The counts of discovered objects and a summary of migration status.
- Objects that cannot be migrated to the Autonomous Database due to the restrictions or lockdowns imposed on certain data types, Oracle Database options and SQL.
- The Objects that will migrate with modifications automatically done during the import or the object creation process
- Informational section containing certain best practice recommendations and guidance
Installation of Advisor:-
The autonomous database schema advisor can be installed from
11.2.0.4 onwards and it doesn’t require any additional license to use.
Download:-
We can download the advisor package from following MOS Note 2462677.1
Installation Steps:
Initiate the ADB Advisor installation by invoking the
following script as sys user.
sqlplus SYS AS SYSDBA @install_adb_advisor.sql <advisor
schema name> <password>
If the advisor schema is not procreated , the above script
will create it.
Once above script runs successfully, run the advisor to
generate report.
SQL> conn adbadvisor
Enter password:
Connected.
Generate the Advisor report by executing ADB_ADVISOR
package:
SQL>EXEC ADB_ADVISOR.REPORT(schemas=<List of schemas>'',
adb_type=<adb_type>'
Now here, list of schemas denotes the name of schemas on
which you would like to run the analyse.
Enter “ALL” to analyse all user schemas.
Adb_type denotes different types destination such as
- 'ATP' for Autonomous Transaction Processing (Serverless)
- 'ADW' for Autonomous Data Warehouse (Serverless)
- 'ATPD' for Autonomous Transaction Processing (Dedicated)
- 'ADWD' for Autonomous Data Warehouse (Dedicated)
The report has 4 sections (see Sample Report for ATP):
- Section 1: Summary Section with object counts
- Section 2: List of objects that will not migrate to ADB
- Section 3: List of objects that will migrate with changes
- Section 4: Informational section and migration guidelines
Here we would migrate into ADW
SQL>EXEC ADB_ADVISOR.REPORT(schemas => ‘ALL', adb_type
=> 'ADW');
==========================================================================================
==
ADW SCHEMA MIGRATION REPORT FOR APEXDEMO,SCOTT
==========================================================================================
ADB
Advisor Version : 19.4.0.0.0
Instance
Name : ora12c
Database
Name : ORA12C
Host
Name : server3.soumya.com
Database
Version : 12.1.0.2.0
Pluggable
Database : ora12c
Schemas
Analyzed : APEXDEMO,SCOTT
Analyzing
for : Autonomous Data Warehouse
(Serverless)
Report
Start date/time: 16-OCT-2020 11:09
------------------------------------------------------------------------------------------
--
SECTION 1: SUMMARY
------------------------------------------------------------------------------------------
Objects Objects Total
Object Will Not Will Migrate Objects
Object
Type Count Migrate With Changes Will Migrate
------------------------- -------------- -------------- -------------- --------------
CONSTRAINT 111 0 0 111
INDEX 41 0 0 41
PACKAGE 3 0 0 3
PACKAGE
BODY 3 0 0 3
SEQUENCE 8 0 0 8
TABLE 28 0 0 28
TRIGGER 21 0 0 21
User
Objects in SYS 120 120 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=120):
-------------------------------------------------------------------
Note:
User-defined objects were detected in SYS schema. Consider moving them out of
SYS prior to migration.
Owner Object Type Object Name
----------
------------------------------ ----------------------------------------
SYS UNDEFINED CDB$ROOT
SYS TABLE PARTITION WRP$_REPORTS
SYS INDEX PARTITION WRP$_REPORTS_DETAILS_IDX02
SYS INDEX PARTITION WRP$_REPORTS_DETAILS_IDX01
SYS INDEX PARTITION SYS_IL0000006450C00009$$
SYS TABLE PARTITION WRP$_REPORTS_DETAILS
SYS TABLE PARTITION WRP$_REPORTS_TIME_BANDS
SYS SEQUENCE WRM$_DEEP_PURGE_INTERVAL
SYS TABLE PARTITION WRH$_FILESTATXS
SYS INDEX PARTITION WRH$_FILESTATXS_PK
SYS TABLE PARTITION WRH$_SQLSTAT
SYS INDEX PARTITION WRH$_SQLSTAT_INDEX
SYS INDEX PARTITION WRH$_SQLSTAT_PK
SYS TABLE PARTITION WRH$_SYSTEM_EVENT
SYS INDEX PARTITION WRH$_SYSTEM_EVENT_PK
SYS TABLE PARTITION WRH$_WAITSTAT
SYS INDEX PARTITION WRH$_WAITSTAT_PK
SYS TABLE PARTITION WRH$_LATCH
SYS INDEX PARTITION WRH$_LATCH_PK
SYS TABLE PARTITION WRH$_LATCH_MISSES_SUMMARY
SYS INDEX PARTITION WRH$_LATCH_MISSES_SUMMARY_PK
SYS TABLE PARTITION WRH$_DB_CACHE_ADVICE
SYS INDEX PARTITION WRH$_DB_CACHE_ADVICE_PK
SYS TABLE PARTITION WRH$_ROWCACHE_SUMMARY
SYS INDEX PARTITION WRH$_ROWCACHE_SUMMARY_PK
SYS TABLE PARTITION WRH$_SGASTAT
SYS INDEX PARTITION WRH$_SGASTAT_U
SYS TABLE PARTITION WRH$_SYSSTAT
SYS INDEX PARTITION WRH$_SYSSTAT_PK
SYS TABLE PARTITION WRH$_PARAMETER
SYS INDEX PARTITION WRH$_PARAMETER_PK
SYS TABLE PARTITION WRH$_SEG_STAT
SYS INDEX PARTITION WRH$_SEG_STAT_PK
SYS TABLE PARTITION WRH$_SERVICE_STAT
SYS INDEX PARTITION WRH$_SERVICE_STAT_PK
SYS TABLE PARTITION WRH$_ACTIVE_SESSION_HISTORY
SYS INDEX PARTITION WRH$_ACTIVE_SESSION_HISTORY_PK
SYS TABLE PARTITION WRH$_TABLESPACE_STAT
SYS INDEX PARTITION WRH$_TABLESPACE_STAT_PK
SYS TABLE PARTITION WRH$_OSSTAT
SYS INDEX PARTITION WRH$_OSSTAT_PK
SYS TABLE PARTITION WRH$_SYS_TIME_MODEL
SYS DIRECTORY XMLDIR
SYS DIRECTORY ORACLE_OCM_CONFIG_DIR
SYS DIRECTORY ORACLE_OCM_CONFIG_DIR2
SYS INDEX PARTITION WRP$_REPORTS_IDX01
SYS INDEX PARTITION WRP$_REPORTS_IDX02
SYS INDEX PARTITION WRP$_REPORTS_IDX01
SYS INDEX PARTITION WRP$_REPORTS_IDX02
SYS TABLE PARTITION WRP$_REPORTS
SYS INDEX PARTITION WRP$_REPORTS_DETAILS_IDX02
SYS INDEX PARTITION WRP$_REPORTS_DETAILS_IDX01
SYS INDEX PARTITION SYS_IL0000006450C00009$$
SYS TABLE PARTITION WRP$_REPORTS_DETAILS
SYS TABLE PARTITION WRP$_REPORTS_TIME_BANDS
SYS INDEX PARTITION WRH$_FILESTATXS_PK
SYS INDEX PARTITION WRH$_SQLSTAT_INDEX
SYS INDEX PARTITION WRH$_SQLSTAT_PK
SYS INDEX PARTITION WRH$_SYSTEM_EVENT_PK
SYS INDEX PARTITION WRH$_WAITSTAT_PK
SYS INDEX PARTITION WRH$_LATCH_PK
SYS INDEX PARTITION WRH$_LATCH_CHILDREN_PK
SYS INDEX PARTITION WRH$_LATCH_PARENT_PK
SYS INDEX PARTITION WRH$_LATCH_MISSES_SUMMARY_PK
SYS INDEX PARTITION WRH$_DB_CACHE_ADVICE_PK
SYS INDEX PARTITION WRH$_ROWCACHE_SUMMARY_PK
SYS INDEX PARTITION WRH$_SGASTAT_U
SYS INDEX PARTITION WRH$_SYSSTAT_PK
SYS INDEX PARTITION WRH$_PARAMETER_PK
SYS INDEX PARTITION WRH$_SEG_STAT_PK
SYS INDEX PARTITION WRH$_DLM_MISC_PK
SYS INDEX PARTITION WRH$_SERVICE_STAT_PK
SYS INDEX PARTITION WRH$_ACTIVE_SESSION_HISTORY_PK
SYS INDEX PARTITION WRH$_TABLESPACE_STAT_PK
SYS INDEX PARTITION WRH$_OSSTAT_PK
SYS INDEX PARTITION WRH$_SYS_TIME_MODEL_PK
SYS INDEX PARTITION WRH$_SERVICE_WAIT_CLASS_PK
SYS INDEX PARTITION WRH$_INST_CACHE_TRANSFER_PK
SYS INDEX PARTITION WRH$_EVENT_HISTOGRAM_PK
SYS INDEX PARTITION WRH$_INTERCONNECT_PINGS_PK
SYS INDEX PARTITION WRH$_MVPARAMETER_PK
SYS INDEX PARTITION WRH$_CELL_GLOB_SUMM_PK
SYS INDEX PARTITION WRH$_CELL_DISK_SUMM_PK
SYS INDEX PARTITION WRH$_CELL_GLOBAL_PK
SYS INDEX PARTITION WRH$_CELL_IOREASON_PK
SYS INDEX PARTITION WRH$_CELL_DB_PK
SYS INDEX PARTITION WRH$_CELL_OPEN_ALERTS_PK
SYS INDEX PARTITION WRH$_IM_SEG_STAT_PK
SYS UNDEFINED NON$CDB
SYS INDEX PARTITION WRH$_SYS_TIME_MODEL_PK
SYS TABLE PARTITION WRH$_EVENT_HISTOGRAM
SYS TABLE PARTITION WRH$_LATCH_CHILDREN
SYS INDEX PARTITION WRH$_LATCH_CHILDREN_PK
SYS INDEX PARTITION WRH$_EVENT_HISTOGRAM_PK
SYS TABLE PARTITION WRH$_LATCH_PARENT
SYS INDEX PARTITION WRH$_LATCH_PARENT_PK
SYS TABLE PARTITION WRH$_MVPARAMETER
SYS INDEX PARTITION WRH$_MVPARAMETER_PK
SYS TABLE PARTITION WRH$_CELL_GLOBAL_SUMMARY
SYS INDEX PARTITION WRH$_CELL_GLOB_SUMM_PK
SYS TABLE PARTITION WRH$_CELL_DISK_SUMMARY
SYS INDEX PARTITION WRH$_CELL_DISK_SUMM_PK
SYS TABLE PARTITION WRH$_CELL_GLOBAL
SYS INDEX PARTITION WRH$_CELL_GLOBAL_PK
SYS TABLE PARTITION WRH$_CELL_IOREASON
SYS INDEX PARTITION WRH$_CELL_IOREASON_PK
SYS TABLE PARTITION WRH$_DLM_MISC
SYS INDEX PARTITION WRH$_DLM_MISC_PK
SYS TABLE PARTITION WRH$_CELL_DB
SYS INDEX PARTITION WRH$_CELL_DB_PK
SYS TABLE PARTITION WRH$_CELL_OPEN_ALERTS
SYS INDEX PARTITION WRH$_CELL_OPEN_ALERTS_PK
SYS TABLE PARTITION WRH$_IM_SEG_STAT
SYS INDEX PARTITION WRH$_IM_SEG_STAT_PK
SYS TABLE PARTITION WRH$_SERVICE_WAIT_CLASS
SYS INDEX PARTITION WRH$_SERVICE_WAIT_CLASS_PK
SYS TABLE PARTITION WRH$_INST_CACHE_TRANSFER
SYS INDEX PARTITION WRH$_INST_CACHE_TRANSFER_PK
SYS TABLE PARTITION WRH$_INTERCONNECT_PINGS
SYS INDEX PARTITION WRH$_INTERCONNECT_PINGS_PK
------------------------------------------------------------------------------------------
--
SECTION 3: FOLLOWING OBJECTS WILL MIGRATE WITH CHANGES
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
--
SECTION 4: MIGRATION ADDITIONAL INFO
------------------------------------------------------------------------------------------
1)
Tables and Partitions will be Compressed to QUERY HIGH in ADW (Count=28):
----------------------------------------------------------------------------
Note:
When migrating to ADW, if a table or partition DDL does not contain a
COMPRESSION clause, it will
be
created with a default compression of QUERY HIGH. To modify this behavior,
either add a compression
clause
of your choice (or even NOCOMPRESS) prior to the export or alter the
compression clause after
the
import.
Owner Object Type Object Name
--------------------
-------------------- ---------------------------------------
APEXDEMO TABLE APEX$_ACL
APEXDEMO TABLE APEX$_WS_FILES
APEXDEMO TABLE APEX$_WS_HISTORY
APEXDEMO TABLE APEX$_WS_LINKS
APEXDEMO TABLE APEX$_WS_NOTES
APEXDEMO TABLE APEX$_WS_ROWS
APEXDEMO TABLE APEX$_WS_TAGS
APEXDEMO TABLE APEX$_WS_WEBPG_SECTIONS
APEXDEMO TABLE APEX$_WS_WEBPG_SECTION_HISTORY
APEXDEMO TABLE DBASOUMYA
APEXDEMO TABLE DBASOUMYA_ERR$
APEXDEMO TABLE DEMO_CONSTRAINT_LOOKUP
APEXDEMO TABLE DEMO_CUSTOMERS
APEXDEMO TABLE DEMO_ORDERS
APEXDEMO TABLE DEMO_ORDER_ITEMS
APEXDEMO TABLE DEMO_PRODUCT_INFO
APEXDEMO TABLE DEMO_STATES
APEXDEMO TABLE DEMO_TAGS
APEXDEMO TABLE DEMO_TAGS_SUM
APEXDEMO TABLE DEMO_TAGS_TYPE_SUM
APEXDEMO TABLE DEPT
APEXDEMO TABLE EBA_DEMO_FILES
APEXDEMO TABLE EBA_DEMO_FILE_PROJECTS
APEXDEMO TABLE EMP
SCOTT TABLE BONUS
SCOTT TABLE DEPT
SCOTT TABLE EMP
SCOTT TABLE SALGRADE
2)
User defined tablespaces are not allowed in ATP-S and ADW-S (Serverless)
(Count=2):
--------------------------------------------------------------------------------------
Note:
Creation of tablespaces is disallowed in ATP and ADW (Serverless). The
tablespace clause gets ignored
and
all objects get created in 'DATA' tablespace. The following is the list of
schemas and the tablespaces
currently
in use.
APEXDEMO APEX_2300472439289881
SCOTT USERS
3)
Table columns are defined using BYTE length semantics in a single-byte (8-bit)
Character Set (Count=176):
------------------------------------------------------------------------------------------------------------
Note:
If you have columns defined using BYTE length semantics, data truncation may
occur when migrating
a
single byte Character Set to a multi byte Character Set, as in the Autonomous
Database. Refer to "Character
Set
Migration" section of Database Globalization Support Guide for more
details,
and run the below SQL
to
get the list of columns defined using BYTE length semantics.
[Use
the following SQL to get the full list of columns ...]
[SELECT
a.owner, a.object_type, a.object_name table_name, a.subobject_name column_name,
b.short_desc, b.long_desc FROM adb_advisor_info_tmp a, db_advisor_codes_tmp b
WHERE a.cd = b.cd AND a.cd = 304]
4)
Schema Owner's user attributes will be modified in ADB (Count=2):
--------------------------------------------------------------------
Note:
The following schema owner's DEFAULT TABLESPACE and/or DEFAULT PROFILE will be
modified in ADB.
DEFAULT
TABLESPACE for APEXDEMO will be modified from 'APEX_2300472439289881' to
DEFAULT
TABLESPACE for SCOTT will be modified from 'USERS' to 'DATA'
5)
Database Options currently in use but will not be available in the ADB
(Count=1):
------------------------------------------------------------------------------------
Note:
The following Database Options are detected as being used. ADB does not have
these Options installed.
Please
verify if the application/schema to be migrated depends on these options.
Tuning
Pack
6)
Database Parameters are detected as modified in the current database but can't
be modified in the ADB (Count=4):
-------------------------------------------------------------------------------------------------------------------
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.
control_management_pack_access
log_archive_dest_3
log_archive_dest_state_3
memory_target
------------------------------------------------------------------------------------------
--
END OF REPORT
------------------------------------------------------------------------------------------
Report
End Datetime : 16-OCT-2020 11:09
Report
Runtime : +000000000
00:00:35.679623000
PL/SQL
procedure successfully completed.
