# S209XDB.def: Collects XDB Information # $Id: S209XDB.def,v 1.5 2009/05/05 21:24:06 mschenke Exp $ # ARCS: $Header: /u02/webapps/workbench/cvs/RDA_4/src/scripting/lib/modules/S209XDB.def,v 1.5 2009/05/05 21:24:06 mschenke Exp $ # # Change History # 20090505 JCR Add XDB status information to xdb_info report. =head1 NAME S209XDB - Collects XDB Information =head1 DESCRIPTION This module collects the XDB diagnostic information. This module only applies to Oracle Database 9i Release 2 and later. The following reports can be generated and are regrouped under C: =cut echo tput('bold'),'Processing XDB module ...',tput('off') if !getSetting('DATABASE_INSTALLED') return # Initialization var $LOCAL = getSetting('DATABASE_LOCAL') var $MACHINE_NAME = getSetting('MACHINE_NAME') var $ORACLE_HOME = getSetting('ORACLE_HOME') var $ORACLE_SID = getSetting('ORACLE_SID') var $TOC = '%TOC%' var $TOP = '[[#Top][Back to top]]' var @TXT pretoc '^1:RDBMS' pretoc '1+:XDB' # Load the common macros run DBinfo() =head2 db_info - Database Overview Gets the database and XDB versions, and lists the database features. =cut debug ' Inside XDB module, getting database overview' set $sql {SELECT 'DB=' || v.version " FROM v$instance v; "SELECT 'XDB=' || dbms_registry.version('XDB') " FROM dual; "SELECT '|' || " s.comp_name || ' |' || " s.status || ' |' || " s.version || ' |' " FROM dba_registry s; } if loadSql($sql) {var $ver = value(grepLastSql('^DB=','f')) report db_info write '---+!! Database Overview' write $TOC write '---+ Versions' write '|*Database*|',$ver,' |' if value(grepLastSql('^XDB=','f')) {var $xdb = last write '|*XDB*|',$xdb,' |' if !compare ('VALID',$xdb,'9.2.0.3') write 'Version is unsupported. Minimum required version is 9.2.0.3.0.%BR%' } write $TOP prefix {write '---+ Other Database Features' write '|*Component*|*Status*|*Version*|' } loop $lin (grepLastSql('^\|')) write $lin if hasOutput(true) write $TOP toc '2:[[',getFile(),'][rda_report][Database Overview]]' } =head2 invalid - Invalid Objects Gets invalid database objects. =cut debug ' Inside XDB Module, getting invalid objects' report invalid prefix {write '---+ Invalid Objects' write '|*Type*|*Name*|' } set $sql {SELECT '|' || " object_type || ' |' || " owner || '.' || object_name || ' |' " FROM dba_objects " WHERE status = 'INVALID' " AND owner IN ('SYS','XDB'); } call writeSql($sql) if isCreated(true) {write $TOP toc '2:[[',getFile(),'][rda_report][Invalid Objects]]' } =head2 xdb_info - XDB Information Gets XDB status, XMLType tables, columns, indexes, and views that have been established. Indexes are only collect on Oracle Database 11g and later. Gets XML schemas that have been established. Gets items built with XML APIs. Gets repository resources that have been established. =cut debug ' Inside XDB Module, getting XDB information' report xdb_info var $TTL = '---+!! XDB Information' var @DBG = ('',\ ' Inside XDB Module, getting XDB status',\ ' Inside XDB Module, getting XDB configuration information',\ ' Inside XDB Module, getting XMLType tables',\ ' Inside XDB Module, getting XMLType columns',\ ' Inside XDB Module, getting XMLType indexes',\ ' Inside XDB Module, getting XMLType views',\ ' Inside XDB Module, getting XML schemas',\ ' Inside XDB Module, getting items built with XML APIs',\ ' Inside XDB Module, getting repository resources') var @TTL = ('',\ '---+ XDB Status',\ '---+ XDB Configuration Information',\ '---+ XMLType Tables',\ '---+ XMLType Columns',\ '---+ XMLType Indexes',\ '---+ XMLType Views',\ '---+ XML Schemas',\ '---+ Items Built with XML APIs',\ '---+ Repository Resources') var @HDR = ('',\ '|*Status*|',\ '|*Root Element Nodename*|*Node Value*|',\ '|*Owner*|*Storage Type*| *Count*|',\ '|*Owner*|*Storage Type*| *Count*|',\ '|*Owner*|*Storage Type*| *Count*|',\ '|*Owner*| *Count*|',\ '|*Owner*| *Count*|',\ '|*Owner*|*Name*|*Type*|',\ '|*User*| *Count*|') set $sql {SELECT '|' || dbms_registry.status('XDB') || ' |' " FROM dual; "PROMPT ___Macro_separator(2)___ "SELECT '|' || value(x).getrootelement() || ' |' || " extractValue(value(x),'/*') || ' |' " FROM table(xmlsequence(extract(xdburitype('/xdbconfig.xml').getXML(), " '//*[text()]'))) x; "PROMPT ___Macro_separator(3)___ "SELECT '|' || " owner || ' |' || " storage_type || ' | ' || " COUNT(*) || '|' " FROM dba_xml_tables " GROUP BY owner,storage_type; "PROMPT ___Macro_separator(4)___ "SELECT '|' || owner || ' |' || " storage_type || ' | ' || " COUNT(*) || '|' " FROM dba_xml_tab_cols " GROUP BY owner,storage_type; } if match($ver,'^11') {append $sql {PROMPT ___Macro_separator(5)___ "SELECT '|' || " index_owner || ' |' || " type || ' | ' || " COUNT(*) || '|' " FROM dba_xml_indexes " GROUP BY index_owner,type; } } append $sql {PROMPT ___Macro_separator(6)___ "SELECT '|' || " owner || ' | ' || " COUNT(*) || '|' " FROM dba_xml_views " GROUP BY owner; "PROMPT ___Macro_separator(7)___ "SELECT '|' || " owner || ' | ' || " COUNT(*) || '|' " FROM dba_xml_schemas " GROUP BY owner; "PROMPT ___Macro_separator(8)___ "SELECT '|' || " d.owner || ' |' || " d.name || ' |' || " d.type || ' |' " FROM dba_dependencies d " WHERE d.referenced_name IN " (SELECT o.object_name " FROM dba_objects o " WHERE o.object_name LIKE 'DBMS_XML%' " OR o.object_name LIKE 'DBMS_XSL%') " AND d.type != 'SYNONYM' " AND d.owner != 'SYS'; "PROMPT ___Macro_separator(9)___ "SELECT '|' || " a.username || ' | ' || " COUNT(r.xmldata) || '|' " FROM dba_users a,xdb.xdb$resource r " WHERE sys_op_rawtonum(extractvalue(value(r),'/Resource/OwnerID/text()')) " = a.user_id " GROUP BY a.username; } call separator(1) call writeSql($sql) call separator(0,'XDB Information') =head2 acl_dad - Network ACL and DAD Information Gets network access controls (ACL) that have been configured and finds Oracle Database Embedded PL/SQL Gateway (EPD) data access descriptor (DAD) usage. (Only available for Oracle Database 11g and later) =cut if match($ver,'^11') {debug ' Inside XDB Module, getting ACL and DAD information' report acl_dad var $TTL = '---+!! Network ACL and DAD Information' var @TTL = ('',\ '---+ Network Access Controls Configured',\ '---+ Oracle Database EPG Data Access Descriptor Usage') var @HDR = ('',\ '|*Host*|*Ports*|',\ '|*Name*|') set $sql {SELECT '|' || " host || '|' || " upper_port || ' through ' || lower_port || '|' " FROM dba_network_acls; "PROMPT ___Macro_separator(2)___ "SET serveroutput on size 100000 "DECLARE " l_nam dbms_epg.varchar2_table; "BEGIN " dbms_epg.get_dad_list(l_nam); " FOR i IN 1 .. l_nam.COUNT " LOOP " dbms_output.put_line('|' || l_nam(i) || ' |'); " END LOOP; "END; "/ } call separator(1) call writeSql($sql) call separator(0,'Network ACL and DAD Information') } # Disable the group title in next index if isTocCreated() toc '-:RDBMS' =head1 SEE ALSO L, L =begin credits =over 10 =item RDA 4.16: Coby Adams. =back =end credits =head1 COPYRIGHT NOTICE Copyright (c) 2002-2009, Oracle. All rights reserved. =head1 TRADEMARK NOTICE Oracle, JD Edwards, PeopleSoft, and Siebel are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. =cut