Transcription
Oracle Autonomous Database Schema AdvisorVersion 19.3Date: Sep 2019
Table of ContentsDISCLAIMER . - 3 INTENDED AUDIENCE . - 3 INTRODUCTION . - 4 ORACLE AUTONOMOUS DATABASE SCHEMA ADVISOR. - 4 INSTALLING THE ADVISOR . - 4 SUPPORTED VERSIONS AND LICENSING . - 4 DOWNLOAD. - 4 INSTALLATION STEPS . - 5 RUNNING THE ADVISOR . - 6 ADVISOR OUTPUT . - 7 SAMPLE REPORT FOR ATP . - 7 DE-INSTALLING THE ADVISOR . - 13 -Oracle Autonomous Database Schema Advisor-2-
DisclaimerThe following is intended to outline our general product direction. It is intended for information purposesonly and may not be incorporated into any contract. It is not a commitment to deliver any material, code, orfunctionality, and should not be relied upon in making purchasing decisions. The development, release, andtiming of any features or functionality described for Oracle’s products remains at the sole discretion ofOracle.Intended AudienceReaders are assumed to have hands-on experience with Oracle Database technologies from the perspective ofa DBA or Data Administrator.Oracle Autonomous Database Schema Advisor-3-
IntroductionOracle is revolutionizing how data is managed with the introduction of the world’s first "self-driving"database, the Oracle Autonomous Database. This ground-breaking Oracle Database technology automatesmanagement to deliver unprecedented availability, performance, and security—at a significantly lower cost.Autonomous Database is available exclusively through Oracle and is offered in two versions optimized tomeet the specialized requirements for Online Transaction Processing and Data Warehousing. Both usemachine learning and automation to eliminate complexity, human error, and manual management, helping toensure higher reliability, security, and more operational efficiency. Autonomous Transaction ProcessingService allows much simpler application development and deployment for enabling real-time analytics,personalization, and fraud detection. Autonomous Data Warehouse delivers unprecedented reliability,performance, and highly elastic data management that enables data warehouse deployment in seconds.You can directly migrate your Oracle Database to the Oracle Autonomous Database and into different targetdeployments on the Oracle Cloud depending on your requirements and business needs. Oracle provides awell-defined set of tools for this purpose that not only allow you assess the existing databases, but also toolsto perform the actual migration in a hands-free and automated approach.Oracle Autonomous Database Schema AdvisorThe very first task that you would perform prior to the migration is to analyze the current Oracle Databasesand report any concerns that may arise during the migration. The ADB Schema Advisor is a light-weightutility that analyzes the on-premise or cloud Oracle Database schemas for the suitability of migration to theAutonomous Database. The Advisor discovers the schema objects and performs deep analysis to highlight isany differences exist when the object gets created on Oracle Autonomous Data Warehouse or OracleAutonomous Transaction Processing database.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 andlockdowns imposed by the Autonomous Database on certain data types, Database options andSQL statements/clauses.The Objects that will migrate with modifications that are automatically done during the importprocess or upon the execution of object creation DDL.Informational section containing certain best practice recommendations and guidanceAdvisor’s output is described in detail in the Advisor Output section below.Installing the AdvisorSupported Versions and LicensingThe Advisor can be installed on Oracle Database Version 11.2.0.4 and above. Currently there is no licenserequired to download and run the Advisor.DownloadDownload the Advisor PL/SQL Package from the MOS Note 2462677.1Oracle Autonomous Database Schema Advisor-4-
Installation Steps1. Create a User in the same Database or the PDB as the schema that will be analyzed:CREATE USER ADB ADVISOR IDENTIFIED by password DEFAULT TABLESPACE tablespace ;Note: You may choose a different user name if you like.2. Grant the following privileges to ADB ADVISOR User:GRANT CREATE SESSION, CREATE PROCEDURE TO ADB ADVISOR;GRANT SELECT ON V VERSION TO ADB ADVISOR;GRANT SELECT ON V PARAMETER TO ADB ADVISOR;GRANT SELECT ON V INSTANCE TO ADB ADVISOR;GRANT SELECT ON V DATABASE TO ADB ADVISOR;GRANT SELECT ON NLS DATABASE PARAMETERS TO ADB ADVISOR;GRANT SELECT ON DBA LOBS TO ADB ADVISOR;GRANT SELECT ON DBA INDEXES TO ADB ADVISOR;GRANT SELECT ON DBA OBJECTS TO ADB ADVISOR;GRANT SELECT ON DBA SYNONYMS TO ADB ADVISOR;GRANT SELECT ON DBA DEPENDENCIES TO ADB ADVISOR;GRANT SELECT ON DBA TABLES TO ADB ADVISOR;GRANT SELECT ON DBA TAB COLS TO ADB ADVISOR;GRANT SELECT ON DBA CONSTRAINTS TO ADB ADVISOR;GRANT SELECT ON DBA DB LINKS TO ADB ADVISOR;GRANT SELECT ON DBA PROFILES TO ADB ADVISOR;GRANT SELECT ON DBA USERS TO ADB ADVISOR;GRANT SELECT ON DBA TYPES TO ADB ADVISOR;GRANT SELECT ON DBA TAB PARTITIONS TO ADB ADVISOR;GRANT SELECT ON DBA TAB SUBPARTITIONS TO ADB ADVISOR;GRANT SELECT ON DBA EXTERNAL TABLES TO ADB ADVISOR;GRANT SELECT ON DBA SEGMENTS TO ADB ADVISOR;GRANT SELECT ON DBA MVIEW LOGS TO ADB ADVISOR;GRANT SELECT ON DBA FEATURE USAGE STATISTICS TO ADB ADVISOR;Oracle Autonomous Database Schema Advisor-5-
Grant the following when you have Oracle XML DB installed:GRANT SELECT ON DBA XML TABLES TO ADB ADVISOR;GRANT SELECT ON DBA XML TAB COLS TO ADB ADVISOR;GRANT SELECT ON DBA XML SCHEMAS TO ADB ADVISOR;Also grant the following when installing in Database 12c and later:GRANT SELECT ON DBA ILMOBJECTS TO ADB ADVISOR;3. Install the Advisor PL/SQL Package using a SQL*Plus session: sqlplus ADB ADVISOR/ password @adb advisor.plb4. Check for errors:SQL SHOW ERRORS;Running the AdvisorFollow the steps outlined below to generate the ADB Schema Advisor report:1) Start a SQL*Plus session as the ADB ADVISOR user:SQL CONNECT ADB ADVISOR/ password 2) Setup the SQL*Plus environment:SQL SET SERVEROUTPUT ON FORMAT WRAPPEDSQL SET LINES 2003) Generate the Advisor report by executing ADB ADVISOR package:SQL EXEC ADB ADVISOR.REPORT(schemas ' List of Schemas ', adb type ' adb type ');Where List of Schemas is a comma separated list of Schemas that you like to analyze, and adb type is oneof the following migration destinations: 'ATP' for Autonomous Transaction Processing (Serverless)'ADW' for Autonomous Data Warehouse (Serverless)'ATPD' for Autonomous Transaction Processing (Dedicated)'ADWD' for Autonomous Data Warehouse (Dedicated)Note: You may specify a maximum of 30 schemas in a single advisor run.4) For example, to generate an Advisor report for SH, HR and SCOTT schemas when the target ADB type is anAutonomous Transaction Processing (Dedicated), run the following command:Oracle Autonomous Database Schema Advisor-6-
SQL EXEC ADB ADVISOR.REPORT(schemas 'SH, HR, SCOTT', adb type 'ATPD');Advisor OutputThe report has 4 sections (see Sample Report for ATP): Section 1: Summary Section with object countsSection 2: List of objects that will not migrate to ADBSection 3: List of objects that will migrate with changesSection 4: Informational section and migration guidelinesSample Report for ATP ATPD SCHEMA MIGRATION REPORT FOR SH2,XDB Instance Name:Database Name:Host Name:Database Version :Pluggable Database:Schemas Analyzed :Analyzing for:Report 2,XDBAutonomous Transaction Processing (Dedicated)19-SEP-2019 ------------------------------------------ ATPD MIGRATION -------------------------------------------Object ASE LINKFUNCTIONINDEXINDEX PARTITIONINDEXTYPELIBRARYOPERATORPACKAGEPACKAGE BODYPROCEDUREQUEUESEQUENCESYNONYMTABLETABLE PARTITIONTABLE SUBPARTITIONTRIGGERTYPETYPE 6253862080897511Oracle Autonomous Database Schema AdvisorObjectsNot sMigratedWith 8089759-7-
XML SCHEMAXMLTYPE TABLEXMLSCHEMAUser Objcts in ------------------- ATPD OBJECTS NOT -------------------------------------------1. XMLType Tables will not migrate (Count ote: XMLType Tables with CLOB or Object-Relational storage is not supported inAutonomous Database. Use the BINARY storage option instead.SH2.XMLTYPE CLOBXDB.XDB COMPLEX TYPEXDB.XDB ALL MODELXDB.XDB SEQUENCE MODELXDB.XDB ELEMENTXDB.XDB ANYATTRXDB.XDB ANYXDB.XDB GROUP REFXDB.XDB ATTRGROUP DEFXDB.XDB SCHEMAXDB.XDB RESOURCEXDB.XDB RESCONFIGXDB.XDB STATSSH2.PurchaseOrder51 TABXDB.XDB SIMPLE TYPEXDB.XDB CHOICE MODELXDB.XDB ATTRIBUTEXDB.XDB GROUP DEFXDB.XDB ATTRGROUP REFXDB.XDB ACLXDB.XDB CONFIG2. Tables with XMLType Columns will not migrate (Count ----------Note: Tables with XMLType columns defined with CLOB or Object-Relational storageare not supported in Autonomous Database. Use the BINARY storage option instead.SH2.PO XML TABXDB.XDB XTABXDB.XDB DXPTAB3. XML Schema Objects will not migrate (Count ---Note: XML Schemas are not supported in Autonomous db/stats.xsd4. Tables with Media data types will not migrate (Count -------------Note: Columns with Media data types are not allowed in Autonomous Database. Considerusing SecureFiles LOB as Oracle Multimedia is desupported in 19c.SH2.MEDIA TAB1SH2.MEDIA TAB11SH2.MEDIA TAB2SH2.MEDIA TAB4Oracle Autonomous Database Schema AdvisorSH2.MEDIA TAB10SH2.MEDIA TAB3-8-
SH2.MEDIA TAB5SH2.MEDIA TAB7SH2.MEDIA TAB8SH2.MEDIA TAB6SH2.MEDIA TAB95. User-defined objects in SYS and SYSTEM schemas will not migrate (Count -----------------------------Note: User-defined objects were detected in SYS/SYSTEM schemas. Consider moving themout prior to the migration.Object Owner-------------------SYSSYSSYSSYSSYSSYSObject Type-------------------DATABASE LINKDIRECTORYLOB PARTITIONLOB PARTITIONLOB PARTITIONVIEWObject Name-----------------------------SYS HUBMY DIRSYS LOB0000014863C00016 SYS LOB0000014863C00016 SYS LOB0000014863C00016 QT73724 BUFFER6. Objects that depend on user-defined objects in SYS and SYSTEM schemas will also notmigrate (Count ------Note: Consider moving the user-defined objects in SYS and SYSTEM schemas prior tomigration. The following objects of the schemas being analyzed directly or indirectlydepend on those objects:Object OwnerObject TypeObject NameOwnerReferenced TypeReferenced Name-------------------- -------------------- --------------------------------------- -------------------- -----------------------------SH2PROCEDUREACCESSES SYS OBJECT DIRECTVIEWQT73724 BUFFERSH2PROCEDUREACCESSES SYS OBJECT VIA SYNONYSYNONYMBUFFER SYNSH2SYNONYMBUFFER SYNVIEWQT73724 BUFFERSH2SYNONYMMY DIR SYNDIRECTORYMY DIRSH2VIEWVIEW ACCESS SYS OBJECT DIRECTVIEWQT73724 BUFFERSH2VIEWVIEW ACCESS SYS OBJECT SYNONYMSYNONYMBUFFER --------------------------- ATPD OBJECTS MIGRATED WITH ------------------------------------------1. BASICFILE LOBs will be changed to SECUREFILE LOBs (Count ---------------Note: Table has BASICFILE LOBs. All Basicfile LOBs will be automatically convertedto SECUREFILE LOBs at import time.SH2.LOCATIONS IOTSH2.MEDIA TAB10SH2.MEDIA TAB2SH2.MEDIA TAB4XDB.XDB H INDEXSH2.MEDIA TAB1SH2.MEDIA TAB3XDB.XDB XIDX IMP T2. NOLOGGING storage attribute will be changed to LOGGING (Count ---------------------Note: Table, Partition or Subpartition created with NOLOGGING will automaticallybe created in ATP and ADW as LOGGING.Oracle Autonomous Database Schema Advisor-9-
SH2.SALES NOLOG(TABLE 1)SH2.SALES SUBPARTITIONS(PARTS 2)SH2.SALES SUBPARTITIONS(SUBPARTS 16)SH2.SALES PART NOLOGGING(PARTS 2)XDB.XDB XIDX IMP T(TABLE 1)3. External Table will be created as regular table (Count -------------Note: Consider using DBMS CLOUD package on ADB to create External Tables that useCloud Object Storage. If you try to create a non-Cloud Object Storage External table,it will be created as a non-External table.SH2.COUNTRIES EXT4. Index Organized table will be created as regular table (Count --------------------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 filecontains tables with IOT, use 'dwcs cvt iots:y' transformation at import time totransform IOTs are regular tables.SH2.DR MYINDEX NSH2.LOCATIONS IOTSH2.DR MYINDEX3 N5. Table Cluster will be created as regular table (Count ------------Note: Table Clusters are disallowed in ADB. When you create a table with a CLUSTERclause on the ADB, the table gets created as a regular table.SH2.EMPLOYEES CLUST6. INMEMORY Tables will be created as regular tables (Count ---------------Note: Database In-Memory is not enabled in ADB. All In-Memory tables and partitionswill be created with NO INMEMORY clause.SH2.DBIM ONETAB (TABLE 1)(SUBPARTSSH2.QUARTERLY REGIONAL SPART ------------------------------------------ ATPD MIGRATION ADDITIONAL ---------------------------------------1. Parallel DEGREE 1 specified on INDEX (Count ----Note: If a PARALLEL clause is specified on the index in your current database, itremains with the index when it gets created, via data pump or manual, in both ATPServerless and Dedicated. This can lead to SQL statements running in parallelunbeknownstto the end user. To specify serial execution, change the INDEX parallel clause toNOPARALLEL or set the PARALLEL degree to 1.SH2.SALES NOPART PAR32 IDX12. Columns are defined using BYTE semantics in a single-byte DB characterset(Count ----------------------------------------Note: Characterset used by the Autonomous Database is AL32UTF8 (Multi-byte). Butif you create a table with a column that uses BYTE semantics, it may not allow youto fit all characters as "Multi-byte" needs more bytes to store one character. Ensurethat your BYTE columns are transformed to CHARACTER semantics before the migrationas Oracle Data Pump currently does not do this.Oracle Autonomous Database Schema Advisor- 10 -
SH2.CONTEXTDOCS TAB (COLS 1)SH2.MYTABLE1 (COLS 1)SH2.QUARTERLY REGIONAL SPART INMEM (COLS 2)(COLS 1) XDB.XDB ANY (COLS 14)XDB.XDB COMPLEX TYPE (COLS 49)XDB.XDB H LINK (COLS 1)XDB.XDB SEQUENCE MODEL (COLS 3)XDB.XDB XTABCOLS (COLS 1)XDB.JSON USER COLLECTION METADATA (COLS 5)SH2.VIEW ACCESS SYS OBJECT DIRECT (COLS 6)SH2.MEDIA TAB11 (COLS 1)XDB.XDB ATTRGROUP DEF (COLS 3)XDB.XDB IMPORT QN INFO (COLS 1)XDB.RESOURCE VIEW (COLS 1)SH2.MEDIA TAB10 (COLS 8)SH2.MEDIA TAB9 (COLS 1)XDB.XDB ATTRIBUTE (COLS 12)XDB.XDB MOUNTS (COLS 2)XDB.XDB RESOURCE (COLS 6)XDB.XDB XIDX PARAM T (COLS 1)SH2.MEDIA TAB2 (COLS 13)SH2.ORDERS QT (COLS 14)SH2.ZIGGY BITMAP (COLS 1)XDB.XDB ALL MODEL (COLS 3)XDB.XDB ATTRGROUP REF (COLS 3)XDB.XDB XIDX IMP T (COLS 3)SH2.DBIM ONETAB (COLS 1)SH2.MEDIA TAB3 (COLS 8)SH2.MEDIA TAB7 (COLS 1)SH2.SALES SUBPARTITIONS (COLS 1)XDB.JSON USERS (COLS 2)XDB.XDB ANYATTR (COLS 14)XDB.XDB PATH INDEX PARAMS (COLS 4)SH2.AQ ORDERS QT (COLS 14)SH2.COUNTRIES EXT (COLS 3)SH2.MEDIA TAB8 (COLS 1)XDB.XDB CHOICE MODEL (COLS 3)XDB.XDB IMPORT TT INFO (COLS 1)XDB.XDB NLOCKS (COLS 1)XDB.XDB SIMPLE TYPE (COLS 39)SH2.DR MYINDEX N (COLS 1)SH2.SALES CLUSTER LO (COLS 1)SH2.SALES PART NOLOGGING (COLS 1)XDB.XDB TTSET (COLS 1)XDB.XDB XTAB (COLS 2)SH2.AQ ORDERS QT F (COLS 11)SH2.VIEW ACCESS SYS OBJECT SYNONYMS (COLS 6)SH2.EMPLOYEES CLUST (COLS 1)SH2.MEDIA TAB6 (COLS 1)XDB.X QN46MP5MEPA004NE0KWN0SK0K1LN (COLS 1)XDB.XDB DBFS VIRTUAL FOLDER (COLS 1)XDB.XDB ELEMENT (COLS 24)XDB.JSON COLLECTION METADATA V (COLS 29)SH2.DR MYINDEX3 N (COLS 1)XDB.X NM46MP5MEPA004NE0KWN0SK0K1LNXDB.XDB D LINK (COLS 1)XDB.XDB XIDX PART TAB (COLS 3)XDB.XDB ROOT INFO V (COLS 10)SH2.MYTABLE TEXT (COLS 1)XDB.XDB INDEX DDL CACHE (COLS 11)SH2.MEDIA TAB4 (COLS 8)XDB.XDB GROUP DEF (COLS 3)XDB.XDB SCHEMA (COLS 6)SH2.MEDIA TAB5 (COLS 1)XDB.JSON COLLECTION METADATA (COLS 6)XDB.XDB IMPORT NM INFO (COLS 1)SH2.DR MYINDEX3 I (COLS 1)SH2.QUARTERLY REGIONAL SALES (COLS 2)XDB.JSON USER ROLES (COLS 2)XDB.XDB ROOT INFO (COLS 10)SH2.LOCATIONS IOT (COLS 1)XDB.XDB GROUP REF (COLS 4)XDB.XDB NONCEKEY (COLS 1)SH2.MEDIA TAB1 (COLS 9)XDB.APP USERS AND ROLES (COLS 2)XDB.DOCUMENT LINKS (COLS 4)SH2.DR MYINDEX I (COLS 1)XDB.JSON USER CREDENTIALS (COLS 2)XDB.XDB DXPTAB (COLS 2)3. Schema owner User attributes will be modified in ADB (Count ------------------Default PROFILE will be Modified for XDB from 'MAQPROFILE' to 'DEFAULT'4. User PROFILEs will not migrate to ADB (Count ---Note: In ATP and ADW, all users will be assigned the 'DEFAULT' Profile. Additionally,you are not allowed to create additional User PROFILEs.Oracle Autonomous Database Schema Advisor- 11 -
MAQPROFILE5. DEFAULT user PROFILE will be altered in the ADB at migration time (Count ---------------------------------Note: In ADB a user's profile will be set to 'DEFAULT' and you are not allowed tocreate additional PROFILEs. Below are the differences in user's current profileand the DEFAULT profile in ADB.Profile DEFAULT vs. ADB's DEFAULT profile (assigned to -PASSWORD LIFE TIMEPASSWORD REUSE MAXPASSWORD REUSE TIMEPASSWORD VERIFY FUNCTIONCLOUD VERIFY FUNCTIONCURRENT LIMITS IN EDNULLNEW LIMITS IN ADB-----------------36041Profile MAQPROFILE vs. ADB's DEFAULT profile (assigned to ----COMPOSITE LIMITCONNECT TIMECPU PER SESSIONFAILED LOGIN ATTEMPTSIDLE TIMEINACTIVE ACCOUNT TIMELOGICAL READS PER CALLLOGICAL READS PER SESSIONPASSWORD GRACE TIMEPASSWORD LIFE TIMEPASSWORD LOCK TIMEPASSWORD REUSE MAXPASSWORD REUSE TIMEPASSWORD VERIFY FUNCTIONCLOUD VERIFY FUNCTIONPRIVATE SGASESSIONS PER USERCURRENT LIMITS IN AULTDEFAULTDEFAULT10003903DEFAULT180DEFAULTNEW LIMITS IN FAULTUNLIMITEDUNLIMITED6. Database Options currently in use but will not be available in the ADB (Count ------------------------------------Note: The following Database Options are detected as being used. ADB does not havethese Options installed. Please verify if the application/schema to be migrateddepends on these options.Database Resident Connection PoolingJava in DBOracle MultimediaTuning Pack7. Database Parameters are set in your database but can't be set in the ADB (Count --------------------------------------Note: The following init parameters are set in your database that you would not beable to set in ADB. Please refer to the Oracle Autonomous Database documentationon the parameters that you are allowed to modify/set in the autonomous database.enable pluggable databasememory targetOracle Autonomous Database Schema Advisor- 12 -
PL/SQL procedure successfully completed.De-installing the AdvisorIf you wish to de-install the Advisor from your database, execute the following statement with DBAprivileges:SQL DROP USER ADB ADVISOR CASADE;Oracle Autonomous Database Schema Advisor- 13 -
Autonomous Data Warehouse delivers unprecedented reliability, performance, and highly elastic data management that enables data warehouse deployment in seconds. You can directly migrate your Oracle Database to the Oracle Autonomous Database and into different target deployments on the Oracle Cloud depending on your requirements and business needs.
