# S325PDA.def: Collects Portal Diagnostics Information # $Id: S325PDA.def,v 1.20 2008/02/25 17:32:55 mschenke Exp $ # ARCS: $Header: /home/cvs/repository/RDA_4/src/scripting/lib/modules/S325PDA.def,v 1.20 2008/02/25 17:32:55 mschenke Exp $ # # Change History # 20080225 MSC Change the copyright notice. =head1 NAME S325PDA - Collects Portal Diagnostics Information =head1 DESCRIPTION This module collects the Portal diagnostics-related information. The following reports can be generated and are regrouped under C: =cut echo tput('bold'),'Processing PDA module ...',tput('off') # Initialization var $APACHE_TOP = getSetting('APACHE_TOP','') var $J2EE_TOP = getSetting('J2EE_TOP','') var $ADVANCED = getSetting('PDA_ADVANCED',0) var $ERROR_TAIL = getSetting('PDA_ERROR_TAIL') var $EXPENSIVE_LIMIT = getSetting('PDA_EXPENSIVE_LIMIT') var $FULL_URL = getSetting('PDA_FULL_URL') var $MISSING_LIMIT = getSetting('PDA_MISSING_LIMIT') var $ORACLE_HOME = getSetting('ORACLE_HOME','') var $PDA_LOCAL = getSetting('PDA_LOCAL') var $SIZE_LIMIT = getSetting('PDA_SIZE_LIMIT') var $TAIL = getSetting('RDA_TAIL',1000) var $TOC = '%TOC%' var $TOP = '[[#Top][Back to top]]' toc '1:Portal Diagnostics' # Load the common macros var %HTTPDERRLOG run library() run DBinfo() run IAS() # Define a macro to group linked files macro group_shares {var ($lvl,$shr,$mod,$bas,$flg) = @arg var $grp = '' var $det = $lvl incr $det loop $id (findShares($shr,$mod)) {# Share the report var $fil = getShare($id) var $lnk = setShare($id) # Check for a new section var $dir = dirname($fil) if compare('ne',$dir,$grp) {if $flg toc $lvl,':',cond($bas,catDir(splitDir($dir,$bas)),$dir),' ' var $grp = $dir } # Create the toc index associated with the file toc $det,':[[',$lnk,'][rda_report][',\ cond($flg,basename($fil),catDir(splitDir($dir,$bas))),']]' } } # Change the database context if $PDA_LOCAL {var $sid = getSetting('ORACLE_SID') var $usr = getSetting('SQL_LOGIN') var $dba = getSetting('SQL_SYSDBA') } elsif getSetting('PDA_SID') {var $sid = last var $usr = uc(getSetting('PDA_LOGIN','SYSTEM')) var $dba = getSetting('PDA_SYSDBA') # Determine the user password if compare('eq',$usr,'/') var ($usr,$pwd) = ('','') elsif hasSqlPassword($usr,$sid) var $pwd = undef else var $pwd = askPassword(concat('Enter ',$usr,'@',$sid,' user password:')) # Change the current user var $old = setSqlLogin($usr,$pwd,$sid,$dba) # If requested, change the database context if getSetting('PDA_SWITCH') var $env = setSqlSid($sid) else var $env = undef var $try = setSqlFailure(0) # Test the database connection call testSql() } else {var $sid = undef # Disable the database access var $env = undef var $old = setSqlLogin('','') var $try = setSqlFailure(-1) } =head2 rep_info - Portal Repository Information Collects the repository information from the Portal database. Run PDA in advanced mode for a detailed report collection. =cut debug ' Inside PDA module, gathering the Portal Repository Information' report rep_info var $TTL = '---+!! Portal Repository Information ' var @DBG = ('',\ ' Inside PDA repository, getting portal version',\ ' Inside PDA repository, getting database version',\ ' Inside PDA repository, getting oneoffs/patches applied',\ ' Inside PDA repository, getting patch inventory log information',\ ' Inside PDA repository, getting OWA PL/SQL toolkit version',\ ' Inside PDA repository, getting duplicate OWA packages',\ ' Inside PDA repository, getting OS user environment',\ ' Inside PDA repository, getting database characterset support',\ ' Inside PDA repository, getting INIT.ORA parameters',\ ' Inside PDA repository, getting SGA information',\ '',\ '',\ ' Inside PDA repository, getting total java objects',\ ' Inside PDA repository, getting total java objects owned by user',\ ' Inside PDA repository, \ getting total list of DB objects by object type',\ ' Inside PDA repository, getting total number of invalid objects',\ ' Inside PDA repository, getting list of invalid objects',\ ' Inside PDA repository, \ getting list of users with tablespace information',\ ' Inside PDA repository, getting free space in tablespace',\ ' Inside PDA repository, getting extents of tablespaces',\ ' Inside PDA repository, getting users tablespace quota',\ ' Inside PDA repository, getting proxy settings',\ ' Inside PDA repository, \ getting java.net.socketpermission permissions granted?',\ ' Inside PDA repository, getting java system permissions granted?',\ ' Inside PDA repository, \ getting java.util.propertypermission permissions granted?',\ '',\ '',\ '',\ ' Inside PDA repository, getting intermedia user',\ ' Inside PDA repository, getting total intermedia objects',\ ' Inside PDA repository, getting intermedia index status',\ '',\ ' Inside PDA repository, getting database locking information',\ '',\ ' Inside PDA repository, getting SSO hash object status',\ ' Inside PDA repository, getting login server enabler table',\ ' Inside PDA repository, getting language',\ ' Inside PDA repository, getting cookies',\ ' Inside PDA repository, getting WPIUTL information',\ ' Inside PDA repository, getting VPD policy',\ ' Inside PDA repository, getting OID cache settings',\ ' Inside PDA repository, getting webcache settings',\ ' Inside PDA repository, getting SSO query path') var @TTL = ('',\ '---+ Portal Version',\ '---+ Database Version',\ '---+ Oneoffs/Patches Applied',\ '---+ Patch Inventory Log Information',\ '---+ OWA PL/SQL Toolkit Version',\ '---+ Duplicate OWA Packages',\ '---+ OS User Environment',\ '---+ Database Characterset Support',\ '---+ INIT.ORA Parameters',\ '---+ SGA Information',\ '',\ '',\ '---+ Total Java Objects',\ '---+ Total Java Objects Owned by User ',\ '---+ Total List of Database Objects by Object Type',\ '---+ Total Number of Invalid Objects',\ '---+ List of Invalid Objects',\ '---+ List of Users With Tablespace Information',\ '---+ Free Space in Tablespace',\ '---+ Extents of Tablespaces',\ '---+ Users Tablespace Quota',\ '---+ Proxy Settings',\ '---+ Is java.net.SocketPermission Permissions Granted?',\ '---+ Is Java System Permissions Granted?',\ '---+ Is java.util.PropertyPermission Permissions Granted?',\ '---+ Provider settings that impact caching',\ '',\ '',\ '---+ Intermedia User',\ '---+ Total Intermedia Objects',\ '---+ Intermedia Index Status',\ '',\ '---+ Database Locking Information',\ '---+ DBA Blockers Information',\ '',\ '---+ SSO Hash Object Status',\ '---+ Login Server Enabler Table',\ '---+ Language',\ '---+ Cookies',\ '---+ WPIUTL Information',\ '---+ VPD Policy',\ '---+ OID Cache Settings',\ '---+ Webcache Settings',\ '---+ SSO Query Path') var @TXT = ('',\ '',\ "Check version / edition - Standard or Production. Check the \ [[http://metalink.oracle.com/metalink/plsql/certify.welcome]\ [_blank][Certification Matrix]] for details.",\ "The list of patches applied to this instance. If no records are \ present that means that no patches were applied.",\ "This represents the log history for all patches.",\ "Check the toolkit version. If less than 9.0.4.0.1 then check the \ [[http://www.oracle.com/technology/products/ias/portal/\ upgrade.html][_blank][upgrade]] information.",\ "Make sure you do not have duplicate copies of OWA packages. You \ should see the output as below:%BR%\ ``SYS.......PACKAGE``%BR%\ ``SYS.......PACKAGE BODY``%BR%\ ``PUBLIC....SYNONYM``",\ "If this script is run from the middle-tier, you should see the \ ``Terminal Name`` and the ``Language`` it is set to. Compare \ these settings with the ``Language, Database Characterset \ Support`` and the ``init.ora`` settings below",\ '',\ "Note: The following recommended values represent **minimum** \ recommended values, if your values are greater than recommended \ ones and you still see errors, please contact Oracle Support.",\ '',\ '',\ '',\ '',\ '',\ '',\ "There should be no INVALID objects in the database pertaining to \ the owners within Portal and SYS owner. If there are any, \ recompile. Use the ``utlrp.sql`` script under the database \ home to recompile.",\ '',\ '',\ "Make sure you have enough free space in the tablespace that \ Portal uses. If it is low, increase the tablespace. Low space \ would affect the functioning of Portal.",\ "Check for the extents, extent Management and Segment Space \ Management for tablespace. If Segment Space Management is set to \ manual means the tablespace is not auto extentable.",\ "In a default settings, you should not get any output for this \ query. If the quota has been set for the user, then you will see \ some rows returned. Check the quota that is used and make sure \ that the user has not used all the quota allocated. Otherwise, \ you may encounter problems like WWC-41400.",\ "Check the Proxy and the client to see if they are correctly set. \ If they are not set correctly, you may have problems connecting \ to Portal.",\ "Ensure Portal Owner Java permissions are granted ``The \ java.io.InterruptedIOException Connection establishment timed \ out`` error can occur if the Portal owner is not granted the \ correct Java permissions. If the required permission has not been \ granted, the above browser error will be accompanied by the \ error ``java.security.AccessControlException the Permission \ (java.net.SocketPermission ip_address connect, resolve) has not \ been granted to portal_owner`` in the database trace file.",\ "If no rows are returned from either of the above queries, execute \ the following statement to grant the permission ``call \ dbms_java.grant_permission('portal_owner',\ 'java.net.SocketPermission','*','connect,resolve')``. \ Ensure SYS Java permissions are granted. The error ``Java call \ terminated by uncaught Java exception \ java.lang.ExceptionInInitializerError(WWC-43000)`` is dumped in \ the database trace file when read, write permissions for Java \ properties are not granted to the SYS user. The permissions must \ be granted for the Java component in the Oracle9iAS Portal \ database to run correctly. The following query will indicate if \ these permissions have been granted.",\ "If the permissions are not granted (i.e. no rows are returned \ from the above query), execute the following statement when \ connected as SYS: ``call dbms_java.grant_permission\ ('SYS','SYS:java.util.PropertyPermission','*','read,write')``",\ "Provider settings that impact caching. There are a couple of \ settings in the [Web] provider registration that can impact the \ caching. In particular, the prompt setting of the Checkbox \ entitled [x] Require session specific information such as \ session id, user, and login time. If not required, it should not \ be set since it forces portlet caching to occur at the session \ level. You can check to see which providers have this set by \ issuing the following command from SQL*Plus when logged in as \ the Portal schema owner. You should receive output similar to the \ following ``NAME CREATED_ON CREATED_BY Provider_Name 06-MAR-02 \ PORTAL_PUBLIC Provider_Test 06-MAR-02 PORTAL_PUBLIC``. If you do \ not need session specific information for these providers, you \ can update them through the user interface or issue the following \ command from SQL*Plus when logged in as the Portal schema \ owner.%BR%\ ``UPDATE wwpro_providers$``%BR%\ `` SET require_session_data = 0``%BR%\ `` WHERE name = [provider name you want to change];``%BR%\ ``COMMIT;``",\ '',\ '',\ '',\ "Make sure you have at least 245 Intermedia Objects in the \ database.",\ '',\ '',\ '',\ '',\ '',\ "These objects are important as they are used when logging in to \ Portal. If these objects does not exists than you may get errors \ like WWC-41439. They need to be manually loaded. If they exists, \ check if they are VALID. See note 134729.1 for details.",\ "Check your ``httpd.conf`` file. Make sure that the servername, \ port used in the file is reflected exactly in the \ ``WWSEC_ENABLER_CONFIG_INFO$`` table. Make sure you are using the \ servername with the domain. Also, check for the slashes in the \ URL. If the settings are improper you may get error \ ``WWC-41439``. To rectify the problem, you can run the \ ``PTLASST`` script.%BR%\ ``PTLASST`` command line utility has been removed in the 10.1.2 \ release. All of the functionality that was provided by \ ``PTLASST`` in the previous release is now available through the \ Portal Dependency Settings tool ``PTLCONFIG``.%BR%\ This table also stores settings for the Partner Application.",\ '',\ '',\ "This would show whether if WPIUTL table exists and does it have \ the EXECUTE privilege. See note 161238.1 for details.",\ '',\ '',\ '',\ '') var @HDR = ('',\ '|*Version* |',\ '|*Version* |',\ '| *Bugno*|',\ '| *Bugno*|*Time* | *Action*|*Comments* |',\ '|*Version* |',\ '|*Owner* |*Object Type* |',\ '|*Terminal* |*Language* |',\ '|*Parameter* |*Value* |',\ '|*Name* |*Value* |',\ '|*Pool* |*Name* | *Value*|',\ '',\ '',\ '| *Count*|',\ '|*Owner* | *Count*|',\ '|*Owner* |*Object Type* | *Count*|',\ '| *Count*|',\ '|*Owner* |*Object Name* |*Object Type* |*Status* |',\ '|*User Name* |*Created* |*Default Tablespace* \ |*Temporary Tablespace* |',\ '|*Tablespace Name* | *Freespace*|',\ '|*Tablespace Name* | *Intial Extent*| *Next Extent*| \ *PCT Increase* |*Allocation Type* |*Segment Space Management* |\ *Extent Management* |',\ '|*Username* |*Tablespace Name* | *Quota*| *Used*|',\ '|*Proxy* |*Client* |',\ '|*Kind* |*Grantee* |*Type Schema* |*Type Name* |*Name* |\ *Action* |*Enabled* | *Seq*|',\ '|*Grantee* |*Granted Role* |*Admin Option* |*Default Role* |',\ '|*Kind*|*Grantee*|*Type Schema*|*Type Name*|*Name*|*Action*|\ *Enabled*| *Seq*|',\ '|*Name* |*Created On* |*Created By* |',\ '',\ '',\ '|*Username* |*Created* |*Default Tablespace* |\ *Temporary Tablespace* |',\ '| *Count*|',\ '|*Index Name* |*Index Type* |*Status* |',\ '',\ '|*Addr* |*Kaddr* | *SID*|*Type* | *Id1*| *Id2*| *Lmode*| \ *Request*| *Ctime*| *Block*|',\ '| *Holding Session*|',\ '',\ '|*Object Name* |*Owner* |*Status* |',\ '|*Lsnr Token* |*Site Token* |*Site ID* |*Ls Login URL* |\ *URLcookie Version* |*Encryption Key* |*Encryption Mask Pre* |\ *Encryption Mask Post* |*URL Cookie IP Check* |',\ '|*Language* |',\ '|*Cookie Name* |',\ '|*Table Name* |*Grantee* |*Owner* |*Grantor* |*Privilege* |',\ '|*Object Name* |*Policy Name* |*Enable* |',\ '|*Name* |*Value* |',\ '|*Name* |*Value* |',\ '|*Path* |') # Get the Portal version set $sql {SELECT '|' || " version || ' |' " FROM wwc_version$; } call separator(1) call loadSql($sql) call writeLastSql() if field('\|',1,grepLastSql('\d+(\.\d+){1,}','f')) var $VERSION = trim(last) else var $VERSION = getVersion('oracle.portaltogo.server') var $OC4J = match($VERSION,'^10\.1\.4') # Get other database information set $sql {SELECT '|' || " banner || ' |' " FROM v$version; "PROMPT ___Macro_separator(3)___ "SELECT '| ' || " bugno || '|' " FROM wwutl_patch$ " ORDER BY bugno; "PROMPT ___Macro_separator(4)___ "SELECT '| ' || " bugno || '|' || " TO_CHAR(timestamp,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " DECODE(action,0,'START',1,'END') || ' |' || " comments || ' |' " FROM wwutl_patchlog$ " ORDER BY timestamp; "PROMPT ___Macro_separator(5)___ "SELECT '|' || " owa_util.get_version || ' |' " FROM dual; "PROMPT ___Macro_separator(6)___ "SELECT '|' || " owner || ' |' || " object_type || ' |' " FROM dba_objects " WHERE object_name = 'OWA'; "PROMPT ___Macro_separator(7)___ "SELECT '|' || " NVL(USERENV('TERMINAL'),'UNKNOWN') || ' |' || " USERENV('LANGUAGE') || ' |' " FROM dual; "PROMPT ___Macro_separator(8)___ "SELECT '|' || " parameter || ' |' || " value || ' |' " FROM nls_database_parameters; "PROMPT ___Macro_separator(9)___ "SELECT '|' || " UPPER(name) || ' |' || " UPPER(NVL(value,'-')) || ' |' " FROM V$parameter " WHERE LOWER(name) IN ('java_pool_size','large_pool_size', " 'shared_pool_size','_system_trig_enabled','db_name', " 'db_domain','db_block_size','db_cache_size', " 'instance_name','service_names','open_cursors', " 'cursor_sharing','max_enabled_roles','mts_dispatchers', " 'sessions','processes','compatible', " 'o7_dictionary_accessibility','nls_language','event', " 'optimizer_mode','job_queue_processes') " ORDER BY name; "PROMPT ___Macro_separator(10)___ "SELECT '|' || " UPPER(NVL(pool,'-')) || ' |' || " UPPER(NVL(name,'-')) || ' | ' || " bytes || '|' " FROM v$sgastat " WHERE name IN ('fixed_sga','free memory','sessions','processes', " 'memory in use','db_block_buffers'); "PROMPT ___Macro_separator(13)___ "SELECT '| ' || " COUNT(1) || '|' " FROM all_objects " WHERE object_type LIKE 'JAVA%'; "PROMPT ___Macro_separator(14)___ "SELECT '|' || " owner || ' | ' || " COUNT(1) || '|' " FROM dba_objects " WHERE object_type LIKE 'JAVA%' " GROUP BY owner; "PROMPT ___Macro_separator(15)___ "SELECT '|' || " owner || ' |' || " object_type || ' | ' || " COUNT(object_type) || '|' " FROM dba_objects " GROUP BY owner,object_type " ORDER BY owner,object_type; "PROMPT ___Macro_separator(16)___ "SELECT '| ' || " COUNT(1) || '|' " FROM all_objects " WHERE status = 'INVALID'; "PROMPT ___Macro_separator(17)___ "SELECT '|' || " owner || ' |' || " object_name || ' |' || " object_type || ' |' || " status || ' |' " FROM all_objects " WHERE status = 'INVALID' " ORDER BY owner,object_name; "PROMPT ___Macro_separator(18)___ "SELECT '|' || " username || ' |' || " TO_CHAR(created,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " default_tablespace || ' |' || " temporary_tablespace || ' |' " FROM dba_users " ORDER BY username; "PROMPT ___Macro_separator(19)___ "SELECT '|' || " tablespace_name || ' | ' || " SUM(bytes)/1048576 || '|' " FROM dba_free_space " WHERE tablespace_name IN ( " SELECT default_tablespace " FROM dba_users) " GROUP BY tablespace_name " ORDER BY tablespace_name; "PROMPT ___Macro_separator(20)___ "SELECT '|' || " tablespace_name || ' | ' || " initial_extent || '| ' || " next_extent || '| ' || " pct_increase || '|' || " allocation_type || ' |' || " segment_space_management || ' |' || " extent_management || ' |' " FROM dba_tablespaces " ORDER BY tablespace_name; "PROMPT ___Macro_separator(21)___ "SELECT '|' || " username || ' |' || " tablespace_name || ' | ' || " DECODE(GREATEST(max_bytes, -1),-1,'Unrestricted', " TO_CHAR(max_bytes/1024,'999,999,990')) || '| ' || " bytes/1024 || '|' " FROM dba_ts_quotas; "PROMPT ___Macro_separator(22)___ "SELECT '|' || " proxy || ' |' || " client || ' |' " FROM proxy_users; "PROMPT ___Macro_separator(23)___ "SELECT '|' || " kind || ' |' || " grantee || ' |' || " type_schema || ' |' || " type_name || ' |' || " name || ' |' || " action || ' |' || " enabled || ' | ' || " seq || '|' " FROM dba_java_policy " WHERE type_name = 'java.net.SocketPermission' " AND enabled = 'ENABLED'; "PROMPT ___Macro_separator(24)___ "SELECT '|' || " grantee || ' |' || " granted_role || ' |' || " admin_option || ' |' || " default_role || ' |' " FROM dba_role_privs " WHERE granted_role = 'JAVASYSPRIV'; "PROMPT ___Macro_separator(25)___ "SELECT '|' || " kind || ' |' || " grantee || ' |' || " type_schema || ' |' || " type_name || ' |' || " name || ' |' || " action || ' |' || " enabled || ' | ' || " seq || '|' " FROM dba_java_policy " WHERE grantee = 'SYS' " AND type_name = 'java.util.PropertyPermission' " AND enabled = 'ENABLED'; "PROMPT ___Macro_separator(26)___ "SELECT '|' || " name || ' |' || " TO_CHAR(created_on,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " created_by || ' |' " FROM wwpro_providers$ " WHERE require_session_data = 1; "PROMPT ___Macro_separator(29)___ "SELECT '|' || " username || ' |' || " TO_CHAR(created,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " default_tablespace || ' |' || " temporary_tablespace || ' |' " FROM dba_users " WHERE username LIKE ('%CTX%'); "PROMPT ___Macro_separator(30)___ "SELECT '| ' || " COUNT(*) || '|' " FROM all_objects " WHERE object_name LIKE '%CTX%'; "PROMPT ___Macro_separator(31)___ "SELECT '|' || " index_name || ' |' || " index_type || ' |' || " status || ' |' " FROM dba_indexes " WHERE index_name LIKE '%WWSBR%'; "PROMPT ___Macro_separator(33)___ "SELECT '|' || " addr || ' |' || " kaddr || ' | ' || " sid || '|' || " type || ' | ' || " id1 || '| ' || " id2 || '| ' || " lmode || '| ' || " request || '| ' || " ctime || '| ' || " block || '|' " FROM v$lock; "PROMPT ___Macro_separator(34)___ "SELECT '| ' || " holding_session || '|' " FROM dba_blockers; "PROMPT ___Macro_separator(36)___ "SELECT '|' || " object_name || ' |' || " owner || ' |' || " status || ' |' " FROM all_objects " WHERE object_name IN ('oracle/security/sso/SSOHash','SSOExtDB', " 'WWSSO_AUTH_EXTERNAL') " ORDER BY object_name; "PROMPT ___Macro_separator(37)___ "SELECT '|' || " lsnr_token || ' |' || " site_token || ' |' || " site_id || ' |' || " ls_login_url || ' |' || " urlcookie_version || ' |' || " encryption_key || ' |' || " encryption_mask_pre || ' |' || " encryption_mask_post || ' |' || " url_cookie_ip_check || ' |' " FROM wwsec_enabler_config_info$; "PROMPT ___Macro_separator(38)___ "SELECT '|' || " language || ' |' " FROM wwnls_strings$ " WHERE subscriber_id = 1 " AND ROWNUM < 2; "PROMPT ___Macro_separator(39)___ "SELECT '|' || " cookie_name || ' |' " FROM wwctx_cookie_info$; "PROMPT ___Macro_separator(40)___ "SELECT '|' || " table_name || ' |' || " grantee || ' |' || " owner || ' |' || " grantor || ' |' || " privilege || ' |' " FROM dba_tab_privs " WHERE grantee = 'PUBLIC' " AND table_name = 'WPIUTL'; "PROMPT ___Macro_separator(41)___ "SELECT '|' || " object_name || ' |' || " policy_name || ' |' || " enable || ' |' " FROM user_policies; "PROMPT ___Macro_separator(42)___ "SELECT '|' || " n.name || ' |' || " DECODE(n.type_name,'DATE',TO_CHAR(v.date_value,'DD-Mon-YYYY'), " 'NUMBER',TO_CHAR(v.number_value), " 'INTEGER',TO_CHAR(v.number_value), " 'STRING',v.varchar2_value) || ' |' " FROM wwpre_name$ n,wwpre_path$ p,wwpre_value$ v " WHERE v.name_id = n.id " AND n.path_id = p.id " AND p.name = 'directory' " ORDER BY n.name; "PROMPT ___Macro_separator(43)___ "SELECT '|' || " n.name || ' |' || " DECODE(n.type_name,'DATE',TO_CHAR(v.date_value,'DD-Mon-YYYY'), " 'NUMBER',TO_CHAR(v.number_value), " 'INTEGER',TO_CHAR(v.number_value), " 'STRING',v.varchar2_value) || ' |' " FROM wwpre_name$ n,wwpre_path$ p,wwpre_value$ v " WHERE v.name_id = n.id " AND n.path_id = p.id " AND p.name = 'webcache' " ORDER BY n.name; "PROMPT ___Macro_separator(44)___ "SELECT '|' || " varchar2_value || ' |' " FROM wwpre_value$ " WHERE name_id = ( " SELECT /*+ INDEX(wwpre_name$ wwpre_name_uk1) */ id " FROM wwpre_name$ " WHERE name='query_path' " AND path_id = ( " SELECT id " FROM wwpre_path$ " WHERE name = 'login_server' " AND parent_id = ( " SELECT id " FROM wwpre_path$ " WHERE name = 'portal' " AND parent_id = ( " SELECT id " FROM wwpre_path$ " WHERE name = 'oracle' " AND parent_id IS NULL)))) " AND level_type = 'S' " AND level_id = 0; } # Get the advanced level queries if $ADVANCED {var $DBG[11] = ' Inside PDA repository, getting analyzed table statistics' var $DBG[12] = ' Inside PDA repository, getting analyzed index statistics' var $DBG[27] = ' Inside PDA repository, getting portal objects and grantees' var $DBG[28] = ' Inside PDA repository, getting privilege list' var $DBG[32] = ' Inside PDA repository, \ getting CTX_User_Index_Errors information' var $TTL[11] = '---+ Analyzed Table Statistics' var $TTL[12] = '---+ Analyzed Index Statistics' var $TTL[27] = '---+ Portal Objects And Grantees' var $TTL[28] = '---+ Privilege List' var $TTL[32] = '---+ CTX_User_Index_Errors Information' var $TXT[32] = "If you see errors in the CTX_User_Index_Errors table, \ search the error in ``metalink`` for details." var $HDR[11] = '|*Table Name* |' var $HDR[12] = '|*Index Name* |' var $HDR[27] = '|*Grantee* |*Privilege* |*Object* |' var $HDR[28] = '|*Object Type* |*Allowable Privilege* | *Privilege Code*|' var $HDR[32] = '|*Error Index Name* |*Error Text Key* |*Error Timestamp* |\ *Error Text* |' append $sql {PROMPT ___Macro_separator(11)___ "SELECT '|' || " table_name || ' |' " FROM user_tables " WHERE num_rows IS NOT NULL; "PROMPT ___Macro_separator(12)___ "SELECT '|' || " index_name || ' |' " FROM user_indexes " WHERE num_rows IS NOT NULL; "PROMPT ___Macro_separator(27)___ "SELECT '|' || " u.user_name || '(User)' || ' |' || " p.object_type_name || '/' || p.privilege || ' |' || " p.name || ' |' " FROM wwsec_sys_priv$ p,wwsec_person$ u " WHERE p.grantee_type = 'USER' " AND p.grantee_user_id = u.id "UNION ALL "SELECT '|' || " g.name || '(Group)' || ' |' || " p.object_type_name || '/' || p.privilege || ' |' || " p.name || ' |' " FROM wwsec_sys_priv$ p,wwsec_group$ g " WHERE p.grantee_type = 'GROUP' " AND p.grantee_group_id = g.id; "PROMPT ___Macro_separator(28)___ "SELECT '|' || " o.name || ' |' || " p.name || ' | ' || " p.privilege_code || '|' " FROM wwsec_priv_object_type$ o,wwsec_privilege$ p " WHERE o.id = p.object_type_id " ORDER BY o.name ASC,p.privilege_code DESC; "PROMPT ___Macro_separator(32)___ "SET long 65536 "SELECT '[[[' || CHR(10) || '|' || " err_index_name || ' |' || " err_textkey || ' |' || " TO_CHAR(err_timestamp,'DD-Mon-YYYY HH24:MI:SS') ||' |' || " REPLACE(REPLACE(REPLACE(LTRIM(err_text), " '|','|'), " '<','<'), " '>','>') || '|' || " CHR(10) || ']]]' " FROM ctx_user_index_errors; } } call separator(2) call writeSql($sql) call separator(0,'Portal Repository Information') =head2 users - Portal User Information Gathers the list of Portal users from C. =cut debug ' Inside PDA module, gathering the WWSEC_Person$ table information' report users prefix {write '---+ WWSEC_Person$ Table Information' write '|*User Name* |*DB User* |*Portal User* |' } set $sql {SELECT '|' || " user_name || ' |' || " db_user || ' |' || " portal_user || ' |' " FROM wwsec_person$; } call writeSql($sql) if hasOutput(true) write $TOP if isCreated(true) toc '2:[[',getFile(),'][rda_report][Portal User Information]]' =head2 portal_status - Portal Tier HTTP Status Gathers the HTTP status of URLs from Portal tier. =head2 infra_status - Infrastructure HTTP Status Gathers the HTTP status of URLs from Infrastructure. =cut pretoc '2:HTTP Request Status' debug ' Inside PDA module, getting server list for checking HTTP status' macro check_url {var ($sch,$srv,$pth,$tag) = @arg if match($sch,'https?:') {var $url = concat('http://',$srv,$pth) var $req = createRequest('GET',$url) var $rsp = submitRequest($req) if isSuccess($rsp) write '|',$tag,'|Success|',$url,'|' else {var ($sta) = getRspCode($rsp) var $msg = getRspMessage($rsp) if compare('eq',$sch,'https:') {write '|',$tag,'|Failed with http (',$sta,'-',$msg,')|',$url,'|' return 1 } write '|',$tag,'|Failed (',$sta,'-',$msg,')|',$url,'|' } } else write '|',$tag,'|Skipped|',$sch,'//',$srv,$pth,'|' return 0 } # Extract distinct server names set $sql {SELECT '1//'||home_url " FROM orasso.wwsso_papp_configuration_info$ " WHERE home_url LIKE '%portal.home'; "SELECT '2//'||ls_login_url " FROM orasso.wwsec_enabler_config_info$; } call loadSql($sql) var (%dup1,%dup2) = () loop $lin (grepLastSql('^\d//')) {var ($qry,$sch,$key) = split('//',$lin,3) var ($key) = match($key,'^([^\/]*)') if !match($key,':\d+$') {if compare('eq',$sch,'https:') var $key = concat($key,':443') elsif compare('eq',$sch,'http:') var $key = concat($key,':80') } if compare('eq',$qry,'1') var $dup1{$key} = $sch else var $dup2{$key} = $sch } # Get the HTTP status for portal tier if %dup1 {debug ' Inside PDA module, gathering portal tier HTTP status' report portal_status write '---+!! Portal tier HTTP Status Information' write '|*Operation*|*HTTP Status*|*URL Used*|' var @pth = ('/images/home.gif','/pls/portal/portal.home') var @tag = ('Webcache Access','Portal Access') var $cnt = 0 loop $srv (keys(%dup1)) {incr $cnt,check_url($dup1{$srv},$srv,$pth[0],$tag[0]) incr $cnt,check_url($dup1{$srv},$srv,$pth[1],$tag[1]) } if $cnt write 'Note: The failure could be because of using http instead of https' toc '3:[[',getFile(),'][rda_report][Portal Tier HTTP Status]]' } # Get the HTTP status for infrastructure if %dup2 {debug ' Inside PDA module, gathering infrastructure HTTP status' report infra_status write '---+!! Infrastructure HTTP Status Information' write '|*Operation*|*HTTP Status*|*URL Used*|' var @pth = ('/images/home.gif','/pls/orasso/orasso.home','/oiddas') var @tag = ('HTTP Server','SSO Access','OIDDAS Access') var $cnt = 0 loop $srv (keys(%dup2)) {incr $cnt,check_url($dup2{$srv},$srv,$pth[0],$tag[0]) incr $cnt,check_url($dup2{$srv},$srv,$pth[1],$tag[1]) incr $cnt,check_url($dup2{$srv},$srv,$pth[2],$tag[2]) } if $cnt write 'Note: The failure could be because of using http instead of https' toc '3:[[',getFile(),'][rda_report][Infrastructure HTTP Status]]' } unpretoc =head2 Performance Analysis Provides a summary of the performance information logged by mod_plsql. =cut debug ' Inside PDA module, analyzing the performance' # Define parsing macros macro extract_time {var ($val) = @arg var ($tim) = match($val,'^(\d+)') return $tim } macro split_time {var (\%tbl,$key) = @arg var $tim = concat($key,'Time') var ($tbl{$key},$tbl{$tim}) = split(',',$tbl{$key}) var $tbl{$tim} = extract_time($tbl{$tim}) } # Define the reporting macros macro fmt_average {var ($val,$tot) = @arg if $tot return sprintf('%d',expr('/',$val,$tot)) return 0 } macro fmt_percent {var ($val,$tot) = @arg if $tot return sprintf('%3.2f%%',expr('/',expr('*',100,$val),$tot)) return '0.00%' } macro write_url_stat {var (\%tbl,$ttl,$flg) = @arg import $cnt,%tb_det,%tb_dur,%tb_req,%tb_url,$TOP prefix {write '---+ URL Summary - Sorted by ',$ttl if $flg write '| *%Total Time*| *Req Time*| *Proc Time*| *DB Proc Time*| \ *Cache Time*| *Session Time*| *ConnPool Time*| *Tidy Time*| \ *Page Calls Time*| *Desc Time*| *Perceived*| *Unaccounted*|\ *URL/Proc*|' else write '| *Count*| *Volume*| *%Total Time*| *Average Time*| *%Public*| \ *%Desc*| *Error Count*|*URL*|' } loop $url (keys(%tbl,'ND')) {if $flg {var $val = $tb_req{$url} decr $val,$tb_det{concat('cacheTime_',$url)} decr $val,$tb_det{concat('connPoolTime_',$url)} decr $val,$tb_det{concat('cookieTime_',$url)} decr $val,$tb_det{concat('describeTime_',$url)} decr $val,$tb_det{concat('pageCallsTime_',$url)} decr $val,$tb_det{concat('procTime_',$url)} decr $val,$tb_det{concat('sessionTidyTime_',$url)} decr $val,$tb_det{concat('streamTime_',$url)} write '| ',fmt_percent($tb_req{$url},$tb_dur{'reqTime'}),'| ',\ fmt_average($tb_req{$url},$tb_url{$url}),'| ',\ fmt_average($tb_det{concat('procTime_',$url)},$tb_url{$url}),'| ',\ fmt_average($tb_det{concat('dbProcTime_',$url)},$tb_url{$url}),'| ',\ fmt_average($tb_det{concat('cacheTime_',$url)},$tb_url{$url}),'| ',\ fmt_average($tb_det{concat('cookieTime_',$url)},$tb_url{$url}),'| ',\ fmt_average($tb_det{concat('connPoolTime_',$url)},$tb_url{$url}),'| ',\ fmt_average($tb_det{concat('sessionTidyTime_',$url)},\ $tb_url{$url}),'| ',\ fmt_average($tb_det{concat('pageCallsTime_',$url)},$tb_url{$url}),\ '| ',\ fmt_average($tb_det{concat('describeTime_',$url)},$tb_url{$url}),'| ',\ fmt_average($tb_det{concat('reqUserTime_',$url)},$tb_url{$url}),'| ',\ fmt_average($val,$tb_url{$url}),'|',$url,' |' } else write '| ',$tb_url{$url},'| ',\ fmt_percent($tb_url{$url},$cnt),'| ',\ fmt_percent($tb_req{$url},$tb_dur{'reqTime'}),'| ',\ fmt_average($tb_req{$url},$cnt),'| ',\ fmt_percent($tb_det{concat('public_',$url)},$tb_url{$url}),'| ',\ fmt_percent($tb_det{concat('describe_',$url)},$tb_url{$url}),'| ',\ nvl($rep_total_urlerrors,0),'|',$url,' |' } if hasOutput(true) write $TOP } # Get the log file to analyze var ($max,$fil) = (0) if $OC4J {# Get the most recent application log var $dir = catDir($J2EE_TOP,'OC4J_Portal','application-deployments','portal') loop $log (grepDir($dir,'^application\.log$','ir')) {var $lst = getLastModify($fil) if expr('>',$lst,$max) var ($fil,$max) = ($log,$lst) } } else {# Get the Apache error file (more recent complete one in case of rotation) call httpServer_getListenerConf(true) call httpServer_getListenerLogs(true) loop $log (keys(%HTTPDERRLOG)) {if expr('>',$HTTPDERRLOG{$log},$max) var ($fil,$max) = ($log,$HTTPDERRLOG{$log}) } } # Treat the error file if createBuffer('pda','T',$fil,$ERROR_TAIL) {# Skip the first line when the buffer does not contain the whole file if expr('>',last,0) {call getLine('pda') var $src = sprintf('last %3.1f MiB of %s',expr('/',$ERROR_TAIL,1048576),$fil) } else var $src = $fil # Initialize reports pretoc '2:Performance Analysis' report pls_expensive prefix {write '---+!! Expense Summary' write '| *Line No*|*Time* |*IP Addr*|*Status*| *Req Time*| *Proc Time*| \ *DB Proc Time*| *Cache Time*| *Cookie Time*| *Tidy Time*| \ *ConnPool Time*| *Page Calls Time*| *Bytes*| *Describe Time*| \ *Missing Time*|*URL*|' } suspend pls_expensive report pls_ignored prefix {write '---+!! Ignored Errors' write '---## Information Taken from ',$src write '' } suspend pls_ignored report pls_invalid_cookies prefix {write '---+!! Invalid Cookies' write '---## Information Taken from ',$src write '' } suspend pls_invalid_cookies report pls_missing_time prefix {write '---+!! Missing Time' write '---## Information Taken from ',$src write '| *Line No*|*Time* |*IP Addr*|*Status*| *Req Time*| *Proc Time*| \ *DB Proc Time*| *Cache Time*| *Cookie Time*| *Tidy Time*| \ *ConnPool Time*| *Page Calls Time*| *Bytes*| *Describe Time*| \ *Missing Time*|*URL*|' } suspend pls_missing_time report pls_parser_errors prefix {write '---+!! Parser Errors' write '---## Information Taken from ',$src write '' } suspend pls_parser_errors report pls_unknown_missing_time prefix {write '---+!! Unknown Missing Time' write '---## Information Taken from ',$src write '| *Line No*|*Time* |*IP Addr*|*Status*| *Req Time*| *Proc Time*| \ *DB Proc Time*| *Cache Time*| *Cookie Time*| *Tidy Time*| \ *ConnPool Time*| *Page Calls Time*| *Bytes*| *Describe Time*| \ *Missing Time*|*URL*|' } suspend pls_unknown_missing_time # Parse the error file var $num = 0 while true {# Read the next line var $lin = chomp(getLine('pda')) break !defined($lin) incr $num # Ignore blank line next !$lin # Ignore some messages upfront if $OC4J {next !match($lin,'\[module=PortalServlet\b') var $lin = replace($lin,'\[module=PortalServlet[^\]]*]',' ') } next match($lin,'(IAS/PM/PROC_READY|\ "OraDAV:|\ "File does not exist|\ " client stopped connection |\ " still did not exit, |\ "User not authorized|\ "had to be described|\ "Proxy log On failed|\ "Stale Connection|\ "Cleanup Thread Started|\ "Cleanup Thread Exiting|\ "is deprecated|\ "No DAD configuration found|\ "Using default charset)') # Ignore begin messages next match($lin,'\s+\[info\].*mod_plsql:\s+(Setting Charset|\ DBCharSet is|\ Setting Language|\ Setting Territory|\ Deinitialized|\ Cleanup Thread|\ Caching is enabled|\ Found duplicate session|\ Removing session cache)') # Decode the error log line var ($pre,%tbl) = split('\s+(\w+)=',replace($lin,'\s{2,}',' ',true)) # Check if this is a connection pool cleanup entry if match($lin,'conncleanup') incr $tb_cnt{'connCleanup'} # Ignore blank lines or lines with less fields than needed if missing($tbl{'status'}) {# Check if this is a mod_plsql error message if match($lin,'mod_plsql:') {if match($lin,'Cookie:') {# Treat an invalid error resume pls_invalid_cookies write $num,' : ',$lin suspend pls_invalid_cookies } else {# Treat a PL/SQL error stack var $stk = field('mod_plsql:\s*',1,$lin) while true {# Read the next line var $pos = getPos('pda') var $lin = chomp(getLine('pda')) break !defined($lin) # Detect the next record if match($lin, '^\[') {# Unread the last line call setPos('pda',$pos) break } incr $num # Add the line to the error stack var $stk = join("\012",$stk,$lin) } incr $tb_err{$stk} next } } # Reject the line next match($lin,'Connection reset by peer|Broken pipe:') resume pls_ignored write $num,' : ',$lin suspend pls_ignored next } # Get URL and IP address if $OC4J {# Ignore non-modplsql lines if !match($lin,'portal\:\s+\[perf(\-repos)?\]') {resume pls_ignored write $num,' : unknown input ',$lin suspend pls_ignored next } # Transform the URL to allow for cumulative stats if compare('eq',$tbl{'url'},'session') var $url = field('\?',0,$tbl{'referer'}) else var $url = field('\?',0,$tbl{'url'}) if match($url,'^\w+:') {var (undef,undef,undef,$url) = split('\/',$url,4) var $url = concat('/',$url) } if match($url,'/page/') var $url = '/page/' elsif match($url,'/docs/') var $url = '/docs/' elsif match($url,'/url/') var $url = '/url/' elsif !$FULL_URL {if match($url,'^[^\.]*\.([^\/]*)') var ($url) = (last) } # Determine the IP address var $adr = $tbl{'ipAddr'} } else {# Ignore non-modplsql lines if !match($lin,'\[info].*mod_plsql:\s+\[perf\]') {resume pls_ignored write $num,' : unknown input ',$lin suspend pls_ignored next } # Transform the URL to allow for cumulative stats var ($adr,$url) = \ match($pre,'mod_plsql:\s+\[perf\]\s+(\d+\.\d+\.\d+\.\d+\s+)?(.*)$') if match($url,'/docs/') var $url = '/docs/' elsif match($url,'/url/') var $url = '/url/' elsif !$FULL_URL {if match($url,'^[^\.]*\.(.*)$') var ($url) = (last) } # Determine the IP address if !$adr var ($adr) = match($pre,'\[client\s+(\d+\.\d+\.\d+\.\d+)') } # Parse some fields loop $key ('cacheTime','dbProcTime','procTime','reqTime','reqUserTime',\ 'sessionTidyTime','streamTime') var $tbl{$key} = extract_time($tbl{$key}) loop $key ('connNSSO','connRO','connSU','cookie','describe','pageCalls') call split_time(\%tbl,$key) # Make sure that negative numbers do not mess up our calculations if or(expr('<',$tbl{'cacheTime'},0),\ expr('<',$tbl{'dbProcTime'},0),\ expr('<',$tbl{'describeTime'},0),\ expr('<',$tbl{'reqTime'},0),\ expr('<',$tbl{'streamTime'},0)) {resume pls_parser_errors write $num,': Negative time in ',$lin suspend pls_parser_errors next } # Update the status count var $sta = $tbl{'status'} incr $tb_sta{$sta} if expr('>=',$sta,400) incr $tb_det{concat('errors_',$url)} # Update the connection pooling information var $sta = $tbl{'connSU'} if compare('eq',$sta,'HIT') incr $tb_cnt{'connSU_hit'} elsif compare('eq',$sta,'NEW') incr $tb_cnt{'connSU_new'} elsif compare('eq',$sta,'(null)') incr $tb_cnt{'connSU_null'} else incr $tb_cnt{'connSU_unknown'} var $sta = $tbl{'connRO'} if compare('eq',$sta,'HIT') incr $tb_cnt{'connRO_hit'} elsif compare('eq',$sta,'NEW') incr $tb_cnt{'connRO_new'} elsif compare('eq',$sta,'(null)') incr $tb_cnt{'connRO_null'} else incr $tb_cnt{'connRO_unknown'} var $sta = $tbl{'connSSO'} if compare('eq',$sta,'HIT') incr $tb_cnt{'connSSO_hit'} elsif compare('eq',$sta,'NEW') incr $tb_cnt{'connSSO_new'} elsif compare('eq',$sta,'(null)') incr $tb_cnt{'connSSO_null'} else incr $tb_cnt{'connSSO_unknown'} var $tbl{'connPoolTime'} = 0 incr $tbl{'connPoolTime'},$tbl{'connNSSOTime'} incr $tbl{'connPoolTime'},$tbl{'connROTime'} incr $tbl{'connPoolTime'},$tbl{'connSUTime'} incr $tb_dur{'connPoolTime'},$tbl{'connPoolTime'} # Update the session cache information var $sta = $tbl{'cookie'} if compare('eq',$sta,'CREATE') {incr $tb_cnt{'sessionCache_create'} incr $tb_dur{'sessionCache_create'},$tbl{'cookieTime'} } elsif compare('eq',$sta,'HIT') {incr $tb_cnt{'sessionCache_hit'} incr $tb_dur{'sessionCache_read'},$tbl{'cookieTime'} } elsif compare('eq',$sta,'MISS') {incr $tb_cnt{'sessionCache_miss'} incr $tb_dur{'sessionCache_decrypt'},$tbl{'cookieTime'} } elsif compare('eq',$sta,'(null)') incr $tb_cnt{'sessionCache_null'} else {resume pls_parser_errors write $num,': Unknown session cache status in ',$lin suspend pls_parser_errors } incr $tb_dur{'sessionCache'},$tbl{'cookieTime'} # Update the PLSQL cache information var $sta = $tbl{'cache'} if compare('eq',$sta,'HIT,EXPIRES') {incr $tb_cnt{'plsqlCache_hitexpires'} incr $tb_dur{'plsqlCache_hitexpires'},$tbl{'cacheTime'} } elsif compare('eq',$sta,'HIT,PING') {incr $tb_cnt{'plsqlCache_hitping'} incr $tb_dur{'plsqlCache_hitping'},$tbl{'cacheTime'} } elsif compare('eq',$sta,'MISS,NEW') {incr $tb_cnt{'plsqlCache_missnew'} incr $tb_dur{'plsqlCache_missnew'},$tbl{'cacheTime'} } elsif compare('eq',$sta,'MISS,STALE') {incr $tb_cnt{'plsqlCache_missstale'} incr $tb_dur{'plsqlCache_missstale'},$tbl{'cacheTime'} } elsif compare('eq',$sta,'(null)') {incr $tb_cnt{'plsqlCache_null'} incr $tb_dur{'plsqlCache_null'},$tbl{'cacheTime'} } else {resume pls_parser_errors write $num,': Unknown plsql cache status in ',$lin suspend pls_parser_errors } # Update "Total" statistics incr $tb_url{$url} incr $tb_req{$url},$tbl{'reqTime'} if compare('eq',$tbl{'describe'},'Yes') {incr $tb_det{concat('describe_',$url)} incr $tb_det{concat('describeTime_',$url)},$tbl{'describeTime'} } incr $tb_det{concat('cacheTime_',$url)},$tbl{'cacheTime'} incr $tb_det{concat('connPoolTime_',$url)},$tbl{'connPoolTime'} incr $tb_det{concat('cookieTime_',$url)},$tbl{'cookieTime'} incr $tb_det{concat('dbProcTime_',$url)},nvl($tbl{'dbProcTime'},'-1') incr $tb_det{concat('pageCallsTime_',$url)},$tbl{'pageCallsTime'} incr $tb_det{concat('procTime_',$url)},$tbl{'procTime'} incr $tb_det{concat('sessionTidyTime_',$url)},$tbl{'sessionTidyTime'} incr $tb_det{concat('streamTime_',$url)},$tbl{'streamTime'} if $tbl{'reqUserTime'} {incr $tb_det{concat('reqUserTime_',$url)},$tbl{'reqUserTime'} incr $tb_dur{'reqUserTime'},$tbl{'reqUserTime'} } if compare('eq',$tbl{'user'},'PUBLIC') incr $tb_det{concat('public_',$url)} # Update the Oracle error code list var $err = $tbl{'oraError'} if and($err,compare('ne',$err,'(null)')) {var $key = concat('oraerr_',$url) if !match($tb_det{$key},concat('\b',$err,'\b')) var $tb_det{$key} = join(', ',$tb_det{$key},concat('ORA-',$err)) } # Update the processing time incr $tb_dur{'cacheTime'},$tbl{'cacheTime'} incr $tb_dur{'dbProcTime'},$tbl{'dbProcTime'} incr $tb_dur{'describeTime'},$tbl{'describeTime'} incr $tb_dur{'pageCallsTime'},$tbl{'pageCallsTime'} incr $tb_dur{'procTime'},$tbl{'procTime'} incr $tb_dur{'reqTime'},$tbl{'reqTime'} incr $tb_dur{'streamTime'},$tbl{'streamTime'} incr $tb_dur{'sessionTidyTime'},$tbl{'sessionTidyTime'} incr $tb_cnt{'url'} # Check if this was an expensive request var $val = $tbl{'reqTime'} decr $val,$tbl{'procTime'} decr $val,$tbl{'cacheTime'} decr $val,$tbl{'cookieTime'} decr $val,$tbl{'sessionTidyTime'} decr $val,$tbl{'connPoolTime'} decr $val,$tbl{'pageCallsTime'} decr $val,$tbl{'describeTime'} decr $val,$tbl{'streamTime'} if expr('>',$tbl{'reqTime'},$EXPENSIVE_LIMIT) {incr $tb_exp{$url} resume pls_expensive write '| ',$num,'|',field('[\[\]]',1,$pre),' |',$adr,' |',\ $tbl{'status'},' | ',$tbl{'reqTime'},'| ',$tbl{'procTime'},'| ',\ $tbl{'dbProcTime'},'| ',$tbl{'cacheTime'},'| ',\ $tbl{'cookieTime'},'| ',$tbl{'sessionTidyTime'},'| ',\ $tbl{'connPoolTime'},'| ',$tbl{'pageCallsTime'},'| ',\ $tbl{'bytes'},'| ',$tbl{'describeTime'},'| ',$val,'|',$url,' |' suspend pls_expensive } # Check if this request has a lot of missing time if expr('>',$val,$MISSING_LIMIT) {resume pls_missing_time write '| ',$num,'|',field('[\[\]]',1,$pre),' |',$adr,' |',\ $tbl{'status'},' | ',$tbl{'reqTime'},'| ',$tbl{'procTime'},'| ',\ $tbl{'dbProcTime'},'| ',$tbl{'cacheTime'},'| ',\ $tbl{'cookieTime'},'| ',$tbl{'sessionTidyTime'},'| ',\ $tbl{'connPoolTime'},'| ',$tbl{'pageCallsTime'},'| ',\ $tbl{'bytes'},'| ',$tbl{'describeTime'},'| ',$val,'|',$url,' |' suspend pls_missing_time # Missing time exists but not related to byte size if expr('<',$tbl{'bytes'},$SIZE_LIMIT) {resume pls_unknown_missing_time write '| ',$num,'|',field('[\[\]]',1,$pre),' |',$adr,' |',\ $tbl{'status'},' | ',$tbl{'reqTime'},'| ',$tbl{'procTime'},'| ',\ $tbl{'dbProcTime'},'| ',$tbl{'cacheTime'},'| ',\ $tbl{'cookieTime'},'| ',$tbl{'sessionTidyTime'},'| ',\ $tbl{'connPoolTime'},'| ',$tbl{'pageCallsTime'},'| ',\ $tbl{'bytes'},'| ',$tbl{'describeTime'},'| ',$val,'|',$url,' |' suspend pls_unknown_missing_time } } } # Write out the global section report pls_summary prefix {write '---+!! Performance Analysis' write '---## Information Taken from ',$src } var $cnt = $tb_cnt{'url'} if !$cnt {write 'No mod_plsql performance records were found in the log file. Either \ no mod_plsql records were made, or performance logging is not \ enabled (``LogLevel`` should be set to ``info`` in httpd.conf, and \ ``logmode`` to ``perf`` for the servlet logging).' if !$OC4J {write write 'For iAS 1.0.2, debugModules should also be set to info in \ wdbsvr.app. For iAS 9.0.2.5 and higher, you can optionally set \ PlsqlInfoLogging to InfoDebug at the DAD level. This allows for \ Portal cookie logging as well.' } if !$VERSION {write write 'The Portal version could not be determined neither from the Database \ nor from the Inventory. Therefore, it could not be use to determine \ which is the right log file to analyze.' } } else {write $TOC # Session cache information var $tb_dur{'sessionCache'} = 0 incr $tb_dur{'sessionCache'},$tb_dur{'sessionCache_create'} incr $tb_dur{'sessionCache'},$tb_dur{'sessionCache_decrypt'} incr $tb_dur{'sessionCache'},$tb_dur{'sessionCache_read'} var $tb_cnt{'sessionCache'} = 0 incr $tb_cnt{'sessionCache'},$tb_cnt{'sessionCache_create'} incr $tb_cnt{'sessionCache'},$tb_cnt{'sessionCache_hit'} incr $tb_cnt{'sessionCache'},$tb_cnt{'sessionCache_miss'} # Write out global information write '---+ Performance Summary' write "|*Total URL's*| ",$cnt,'|' write '|*Total Request Time*| ',$tb_dur{'reqTime'},' ms|' write '|*Average per Request*| ',\ fmt_average($tb_dur{'reqTime'},$cnt),' ms|' write '|*Idle Session Cleanup Count*| ',$tb_cnt{'connCleanup'},'|' write '|*Perceived Time*| ',\ fmt_percent($tb_dur{'reqUserTime'},$tb_dur{'reqTime'}),'|' write '|*Time Saved*| ',\ expr('-',$tb_dur{'reqTime'},$tb_dur{'reqUserTime'}),' ms|' write $TOP write '---+ Performance Statistics' write '|*Breakup*| *%Req Time*| *Average*| *Total Time*|' write '|Total DBProc Time| ',\ fmt_percent($tb_dur{'dbProcTime'},$tb_dur{'reqTime'}),'| ',\ fmt_average($tb_dur{'dbProcTime'},$cnt),' ms| ',\ nvl($tb_dur{'dbProcTime'},0),' ms|' write '|Total PLSQL Cache Time| ',\ fmt_percent($tb_dur{'cacheTime'},$tb_dur{'reqTime'}),'| ',\ fmt_average($tb_dur{'cacheTime'},$cnt),' ms| ',\ nvl($tb_dur{'cacheTime'},0),' ms|' write '|Total Session Cache Time| ',\ fmt_percent($tb_dur{'sessionCache'},$tb_dur{'reqTime'}),'| ',\ fmt_average($tb_dur{'sessionCache'},$cnt),' ms| ',\ nvl($tb_dur{'sessionCache'},0),' ms|' write '|Total Session Tidy Time| ',\ fmt_percent($tb_dur{'sessionTidyTime'},$tb_dur{'reqTime'}),'| ',\ fmt_average($tb_dur{'sessionTidyTime'},$cnt),' ms| ',\ nvl($tb_dur{'sessionTidyTime'},0),' ms|' write '|Total Connection Pool Time| ',\ fmt_percent($tb_dur{'connPoolTime'},$tb_dur{'reqTime'}),'| ',\ fmt_average($tb_dur{'connPoolTime'},$cnt),' ms| ',\ nvl($tb_dur{'connPoolTime'},0),' ms|' var $val = $tb_dur{'procTime'} decr $val,$tb_dur{'dbProcTime'} write '|Total ProcTime v/s DBProcTime| ',\ fmt_percent($val,$tb_dur{'reqTime'}),'| ',\ fmt_average($val,$cnt),' ms| ',\ $val,' ms|' write '|Total Extra Pagecall Time| ',\ fmt_percent($tb_dur{'pageCallsTime'},$tb_dur{'reqTime'}),'| ',\ fmt_average($tb_dur{'pageCallsTime'},$cnt),' ms| ',\ nvl($tb_dur{'pageCallsTime'},0),' ms|' write '|Total Describe Time| ',\ fmt_percent($tb_dur{'describeTime'},$tb_dur{'reqTime'}),'| ',\ fmt_average($tb_dur{'describeTime'},$cnt),' ms| ',\ nvl($tb_dur{'describeTime'},0),' ms|' var $val = $tb_dur{'reqTime'} decr $val,$tb_dur{'procTime'} decr $val,$tb_dur{'cacheTime'} decr $val,$tb_dur{'sessionCache'} decr $val,$tb_dur{'sessionTidyTime'} decr $val,$tb_dur{'connPoolTime'} decr $val,$tb_dur{'pageCallsTime'} decr $val,$tb_dur{'describeTime'} decr $val,$tb_dur{'streamTime'} write '|Unaccounted Time| ',\ fmt_percent($val,$tb_dur{'reqTime'}),'| ',\ fmt_average($val,$cnt),' ms| ',\ $val,' ms|' write $TOP # Write out the status code summary write '---+ Status Code Summary' write '|*Status Code*| *%Percent*| *Count*|' loop $sta (keys(%tb_sta)) write '|',$sta,'| ',\ fmt_percent($tb_sta{$sta},$cnt),'| ',\ $tb_sta{$sta},'|' write $TOP # Write out connection pooling information write '---+ Connection Pool Summary' write '|*Breakup*| *%Percent*| *Count*|' var $val = 0 incr $val,$tb_cnt{'connSU_hit'} incr $val,$tb_cnt{'connNSSO_hit'} write '|Hits| ',\ fmt_percent($val,$cnt),'| ',\ $val,'|' var $val = 0 incr $val,$tb_cnt{'connSU_new'} incr $val,$tb_cnt{'connNSSO_new'} write '|Creates| ',\ fmt_percent($val,$cnt),'| ',\ $val,'|' var $val = 0 incr $val,$tb_cnt{'connSU_unknown'} incr $val,$tb_cnt{'connRO_unknown'} incr $val,$tb_cnt{'connNSSO_unknown'} write '|Unaccounted| ',\ fmt_percent($val,$cnt),'| ',\ $val,'|' write $TOP # Write out session cache information if $tb_cnt{'sessionCache'} {write '---+ Session Cache Summary' write '|*Breakup*| *%Percent*| *Count*| *Average*| *%Req Time*| \ *Total Time*|' write '|Hits| ',\ fmt_percent($tb_cnt{'sessionCache_hit'},$tb_cnt{'sessionCache'}),'| ',\ nvl($tb_cnt{'sessionCache_hit'},0),'| ',\ fmt_average($tb_dur{'sessionCache_read'},\ $tb_cnt{'sessionCache_hit'}),'ms| ',\ fmt_percent($tb_dur{'sessionCache_read'},$tb_dur{'reqTime'}),'| ',\ nvl($tb_dur{'sessionCache_read'},0),' ms|' write '|Missed (Decrypt)| ',\ fmt_percent($tb_cnt{'sessionCache_miss'},\ $tb_cnt{'sessionCache'}),'| ',\ nvl($tb_cnt{'sessionCache_miss'},0),'| ',\ fmt_average($tb_dur{'sessionCache_decrypt'},\ $tb_cnt{'sessionCache_miss'}),'ms| ',\ fmt_percent($tb_dur{'sessionCache_decrypt'},$tb_dur{'reqTime'}),'| ',\ nvl($tb_dur{'sessionCache_decrypt'},0),' ms|' write '|New (Create)| ',\ fmt_percent($tb_cnt{'sessionCache_create'},\ $tb_cnt{'sessionCache'}),'| ',\ nvl($tb_cnt{'sessionCache_create'},0),'| ',\ fmt_average($tb_dur{'sessionCache_create'},\ $tb_cnt{'sessionCache_create'}),'ms| ',\ fmt_percent($tb_dur{'sessionCache_create'},$tb_dur{'reqTime'}),'| ',\ nvl($tb_dur{'sessionCache_create'},0),' ms|' write '|Null Entries| ',\ fmt_percent($tb_cnt{'sessionCache_null'},\ $tb_cnt{'sessionCache'}),'| ',\ nvl($tb_cnt{'sessionCache_null'},0),'| | | |' } write $TOP # Write out PLSQL cache information write '---+ PLSQL Cache Summary' write '|*Breakup*| *%Percent*| *Count*| *Average*| *%Req Time*| *Total Time*|' write '|Hit,Expires| ',\ fmt_percent($tb_cnt{'plsqlCache_hitexpires'},$cnt),'| ',\ nvl($tb_cnt{'plsqlCache_hitexpires'},0),'| ',\ fmt_average($tb_dur{'plsqlCache_hitexpires'},\ $tb_cnt{'plsqlCache_hitexpires'}),' ms| ',\ fmt_percent($tb_dur{'plsqlCache_hitexpires'},$tb_dur{'reqTime'}),'| ',\ nvl($tb_dur{'plsqlCache_hitexpires'},0),' ms|' write '|Hit,Ping| ',\ fmt_percent($tb_cnt{'plsqlCache_hitping'},$cnt),'| ',\ nvl($tb_cnt{'plsqlCache_hitping'},0),'| ',\ fmt_average($tb_dur{'plsqlCache_hitping'},\ $tb_cnt{'plsqlCache_hitping'}),' ms| ',\ fmt_percent($tb_dur{'plsqlCache_hitping'},$tb_dur{'reqTime'}),'| ',\ nvl($tb_dur{'plsqlCache_hitping'},0),' ms|' write '|Miss,New| ',\ fmt_percent($tb_cnt{'plsqlCache_missnew'},$cnt),'| ',\ nvl($tb_cnt{'plsqlCache_missnew'},0),'| ',\ fmt_average($tb_dur{'plsqlCache_missnew'},\ $tb_cnt{'plsqlCache_missnew'}),' ms| ',\ fmt_percent($tb_dur{'plsqlCache_missnew'},$tb_dur{'reqTime'}),'| ',\ nvl($tb_dur{'plsqlCache_missnew'},0),' ms|' write '|Miss,Stale| ',\ fmt_percent($tb_cnt{'plsqlCache_missstale'},$cnt),'| ',\ nvl($tb_cnt{'plsqlCache_missstale'},0),'| ',\ fmt_average($tb_dur{'plsqlCache_missstale'},\ $tb_cnt{'plsqlCache_missstale'}),' ms| ',\ fmt_percent($tb_dur{'plsqlCache_missstale'},$tb_dur{'reqTime'}),'| ',\ nvl($tb_dur{'plsqlCache_hitexpires'},0),' ms|' write '|Null Entries| ',\ fmt_percent($tb_cnt{'plsqlCache_null'},$cnt),'| ',\ nvl($tb_cnt{'plsqlCache_null'},0),'| ',\ fmt_average($tb_dur{'plsqlCache_null'},\ $tb_cnt{'plsqlCache_null'}),' ms| ',\ fmt_percent($tb_dur{'plsqlCache_null'},$tb_dur{'reqTime'}),'| ',\ nvl($tb_dur{'plsqlCache_null'},0),' ms|' write '|Unaccounted| ',\ fmt_percent($tb_cnt{'plsqlCache_unknown'},$cnt),'| ',\ nvl($tb_cnt{'plsqlCache_unknown'},0),'| ',\ fmt_average($tb_dur{'plsqlCache_unknown'},\ $tb_cnt{'plsqlCache_unknown'}),' ms| ',\ fmt_percent($tb_dur{'plsqlCache_unknown'},$tb_dur{'reqTime'}),'| ',\ nvl($tb_dur{'plsqlCache_unknown'},0),' ms|' write $TOP # Dump statistics for each URL prefix {write '---+ URL Summary - Sorted by URL Name' write '|*URL*| *Count*| *%Volume*| *%Total Time*| *Average Time*| \ *%Public*| *%Desc*| *Error Count*|' } loop $url (keys(%tb_url)) {var $tot = $tb_url{$url} write '|',$url,' | ',\ $tot,'| ',\ fmt_percent($tot,$cnt),'| ',\ fmt_percent($tb_req{$url},$tb_dur{'reqTime'}),'| ',\ fmt_average($tb_req{$url},$cnt),'| ',\ fmt_percent($tb_det{concat('public_',$url)},$tot),'| ',\ fmt_percent($tb_det{concat('describes_',$url)},$tot),'| ',\ nvl($tb_det{concat('errors_',$url)},0),'|' } if hasOutput(true) write $TOP # Produce the URL statistics call write_url_stat(\%tb_req,'%Total Time') call write_url_stat(\%tb_url,'Volume') call write_url_stat(\%tb_req,'%Total Time (with Time-Breakup Details)',true) # Dump error statistics for each URL prefix {write '---+ URL Error Status Summary' write '|*URL/Proc*| *Error Count*| *Error%*|*Oracle Error Codes*|' } loop $url (keys(%tb_url)) {var $key = concat('errors_',$url) if $tb_det{$key} write '|',$url,' | ',\ $tb_det{$key},'| ',\ fmt_percent($tb_det{$key},$tb_url{$url}),'|',\ $tb_det{concat('oraerr_',$url)},' |' } if hasOutput(true) write $TOP # Write out Expensive URL Summary prefix {write '---+ Expensive URL Summary' write '|*URL*| *Count*|' } loop $key (keys(%tb_exp)) write '|',$key,' | ',$tb_exp{$key},'|' if hasOutput(true) write $TOP } if isCreated() toc '3:[[',getFile(),'][rda_report][Summary]]' # Dump error hash report pls_ora_error var $sep = "---+ Summary of URL's and Errors Encountered" loop $key (keys(%tb_err)) {write $sep var $sep = '---' write 'Encountered ',$tb_err{$key},' error(s) of type' write '' write $key write '' } if isCreated() toc '3:[[',getFile(),'][rda_report][URLs and Errors Encountered]]' # Finalize reports resume pls_expensive if isCreated() toc '3:[[',getFile(),'][rda_report][Expense Summary]]' resume pls_ignored if isCreated() {write '' toc '3:[[',getFile(),'][rda_report][Ignored Errors]]' } resume pls_invalid_cookies if isCreated() {write '' toc '3:[[',getFile(),'][rda_report][Invalid Cookies]]' } resume pls_missing_time if isCreated() toc '3:[[',getFile(),'][rda_report][Missing Time]]' resume pls_parser_errors if isCreated() {write '' toc '3:[[',getFile(),'][rda_report][Parser Errors]]' } resume pls_unknown_missing_time if isCreated() toc '3:[[',getFile(),'][rda_report][Unknown Missing Time]]' unpretoc } =head2 oiddiag - OID Diagnostics Gathers the OID connection information. =cut debug ' Inside PDA module, listing the OID Diagnostics information' report oiddiag prefix write '---+!! OID Diagnostics Information' set $sql {SET serveroutput on "DECLARE " l_version wwc_version$.version%type; " l_is_sso BOOLEAN := wwctx_api.is_in_login_server; " l_host VARCHAR2(100); " l_port VARCHAR2(100); " l_app_dn VARCHAR2(32000); " l_session dbms_ldap.session; " l_vals wwsec_oid.vc_arr; " l_found BOOLEAN; " l_status NUMBER; " l_id NUMBER; " l_dns owa.vc_arr; " error_oiddiag_continue EXCEPTION; " error_oiddiag_discontinue EXCEPTION; " " FUNCTION get_oid_entry_dns(p_base IN VARCHAR2, " p_filter IN VARCHAR2 DEFAULT 'objectclass=*') " RETURN owa.vc_arr " IS " l_session dbms_ldap.session; " l_status NUMBER; " l_attrs dbms_ldap.string_collection; " l_result dbms_ldap.message; " l_count NUMBER; " l_dns owa.vc_arr; " BEGIN " l_session := wwsec_oid.bind_application; " l_attrs(1) := 'dn'; " l_status := dbms_ldap.search_s(ld => l_session, " base => p_base, " scope => dbms_ldap.SCOPE_SUBTREE, " filter => p_filter, " attrs => l_attrs, " attronly => 0, " res => l_result); " IF l_status <> dbms_ldap.SUCCESS " THEN " RAISE value_error; " END IF; " l_count := dbms_ldap.count_entries(ld => l_session, " msg => l_result); " IF l_count = 0 " THEN " RETURN l_dns; " END IF; " l_result := dbms_ldap.first_entry(ld => l_session, " msg => l_result); " l_dns(1) := wwsec_oid.get_normalized_dn( " dbms_ldap.get_dn(ld => l_session, " ldapentry => l_result)); " FOR i IN 2..l_count " LOOP " l_result := dbms_ldap.next_entry(ld => l_session, " msg => l_result); " l_dns(i) := wwsec_oid.get_normalized_dn( " dbms_ldap.get_dn(ld => l_session, " ldapentry => l_result)); " END LOOP; " RETURN l_dns; " END get_oid_entry_dns; " " PROCEDURE check_user(p_username IN VARCHAR2) " IS " l_guid wwsec_person$.guid%type; " l_dn wwsec_person$.dn%type; " l_birthdate VARCHAR2(100); " l_hiredate VARCHAR2(100); " l_user_info wwsec_person%rowtype; " BEGIN " l_user_info := wwsec_oid.get_user_info(p_username => p_username, " p_guid => l_guid, " p_dn => l_dn, " p_birthdate => l_birthdate, " p_hiredate => l_hiredate); " IF l_dn IS NOT NULL " THEN " dbms_output.put_line('|*USER*|' || p_username || ' ' || l_dn || '|'); " END IF; " EXCEPTION " WHEN wwsec_oid.multiple_matches_found THEN " dbms_output.put_line('|*Error*|%RED%There are multiple ' || " p_username || " ' users%ENDCOLOR%|'); " l_dns := get_oid_entry_dns(wwsec_oid.get_user_create_base, " '(&(objectclass=orcluserv2)(' || " wwsec_oid.get_user_nickname_attr || " '=' || " p_username ||'))'); " IF l_dns.count > 0 " THEN " FOR i IN l_dns.FIRST..l_dns.LAST " LOOP " dbms_output.put_line(i || '. ' || l_dns(i)); " END LOOP; " END IF; " WHEN wwsec_oid.not_found_exception THEN " l_dns := get_oid_entry_dns(wwsec_oid.get_user_create_base, " '(&(objectclass=orcluserv2)(cn=' || " p_username || '))'); " IF l_dns.count > 0 " THEN " dbms_output.put_line('|*Error*|%RED%The nickname value for ' || " p_username || " ' user is missing/incorrect.%ENDCOLOR%|'); " FOR i IN l_dns.FIRST..l_dns.LAST " LOOP " dbms_output.put_line(l_dns(i) || ':'); " dbms_output.put_line( " '- ' || " wwsec_oid.get_user_nickname_attr || " '=>' || " wwsec_oid.get_attr_value(l_dns(i),wwsec_oid.get_user_nickname_attr)); " END LOOP; " ELSE " dbms_output.put_line('|*Error*|%RED%There is no ' || " p_username || " ' user%ENDCOLOR%|'); " END IF; " WHEN OTHERS THEN " dbms_output.put_line('|*Error*|%RED%Could not get the ' || " p_username || " ' user%ENDCOLOR%|'); " END check_user; " " PROCEDURE check_group(p_group_id IN NUMBER, " p_group_name IN VARCHAR2) " IS " l_group_id NUMBER := p_group_id; " l_group_name wwsec_group$.name%type := p_group_name; " l_group_dn wwsec_group$.dn%type; " BEGIN " BEGIN " l_group_dn := wwsec_api_private.get_local_group_dn(l_group_id); " EXCEPTION " WHEN wwsec_api.group_not_found_exception THEN " dbms_output.put_line('|*Error*|%RED%There is no local entry for ' || " l_group_name || " '%ENDCOLOR%|'); " wwsec_oid.unbind; " RAISE error_oiddiag_continue; " RETURN; " WHEN OTHERS THEN " dbms_output.put_line('|*Error*|%RED%Could not access the local entry for ' " || l_group_name || " '%ENDCOLOR%|'); " wwsec_oid.unbind; " RAISE error_oiddiag_continue; " RETURN; " END; " IF p_group_id = wwsec_api.GROUP_AUTHENTICATED_USERS " THEN " BEGIN " l_vals := wwsec_oid.get_group_attr_vals(p_groupname => p_group_name, " p_attr => 'owner', " p_base => " wwsec_oid.get_group_install_base); " l_found := FALSE; " l_app_dn := wwsec_oid.get_normalized_dn(l_app_dn); " FOR i IN l_vals.first..l_vals.last " LOOP " IF wwsec_oid.get_normalized_dn(l_vals(i)) = l_app_dn " THEN " l_found := TRUE; " EXIT; " END IF; " END LOOP; " IF l_found " THEN " dbms_output.put_line( " '|*Application Owner*|Application is an owner of ' || " l_group_name || '|'); " ELSE " dbms_output.put_line('|*Error*|%RED%Application is NOT an owner of ' || " l_group_name || " '%ENDCOLOR%|'); " wwsec_oid.unbind; " RAISE error_oiddiag_continue; " RETURN; " END IF; " EXCEPTION " WHEN OTHERS THEN " dbms_output.put_line('[[['); " dbms_output.put_line( " '|*Error*|%RED%Accessing ' || " l_group_name || " ' group in OID.' || " REPLACE(REPLACE(REPLACE(LTRIM(wwsec_oid.get_last_error), " '|','|'), " '<','<'), " '>','>') || " '%ENDCOLOR%|'); " dbms_output.put_line(']]]'); " wwsec_oid.unbind; " RAISE error_oiddiag_continue; " RETURN; " END; " END IF; " BEGIN " l_vals := wwsec_oid.get_group_attr_vals( " p_groupname => p_group_name, " p_attr => 'objectclass', " p_base => wwsec_oid.get_group_install_base); " l_found := FALSE; " FOR i IN l_vals.first..l_vals.last " LOOP " IF LOWER(l_vals(i)) = 'orclprivilegegroup' " THEN " l_found := TRUE; " EXIT; " END IF; " END LOOP; " IF l_found " THEN " dbms_output.put_line('|*Privileged Group*|' || l_group_name || " ' is a privileged group in OID.' || '|'); " ELSE " dbms_output.put_line('|*Error*|%RED%' || " l_group_name || " ' is NOT a privileged group in OID.%ENDCOLOR%|'); " wwsec_oid.unbind; " RAISE error_oiddiag_continue; " RETURN; " END IF; " EXCEPTION " WHEN OTHERS THEN " dbms_output.put_line('|*Error*|%RED%Accessing ' || " l_group_name || " ' group in OID.%ENDCOLOR%|'); " wwsec_oid.unbind; " RAISE error_oiddiag_continue; " RETURN; " END; " EXCEPTION " WHEN error_oiddiag_continue THEN " RETURN; " END check_group; "BEGIN " dbms_output.put_line('|*Timestamp*|' || " TO_CHAR(sysdate,'DD-Mon-YYYY HH24:MI:SS') || '|'); " BEGIN " SELECT version INTO l_version FROM wwc_version$; " dbms_output.put_line('|*Version*|' || l_version || '|'); " EXCEPTION " WHEN OTHERS THEN " dbms_output.put_line('|*Version*|Unknown|'); " dbms_output.put_line('|*Error*|%RED%' || sqlerrm || '%ENDCOLOR%|'); " END; " BEGIN " l_id := wwsec_api.id(user); " IF NOT l_is_sso AND l_id <> 0 " THEN " dbms_output.put_line('|*Error*|%RED%Schema owner ID is incorrect: ' || " l_id || '%ENDCOLOR%|'); " END IF; " EXCEPTION " WHEN wwsec_api.user_not_found_exception THEN " dbms_output.put_line( " '|*Error*|%RED%User profile for schema owner is missing' || " '%ENDCOLOR%|'); " WHEN OTHERS THEN " dbms_output.put_line( " '|*Error*|%RED%Accessing user profile for schema owner' || " sqlerrm || '%ENDCOLOR%|'); " END; " BEGIN " l_id := wwsec_api.id('PUBLIC'); " IF NOT l_is_sso AND l_id <> 2 " THEN " dbms_output.put_line('|*Error*|%RED%PUBLIC user ID is incorrect: ' || " l_id || '%ENDCOLOR%|'); " END IF; " EXCEPTION " WHEN wwsec_api.user_not_found_exception THEN " dbms_output.put_line( " '|*Error*|%RED%User profile for PUBLIC user is missing%ENDCOLOR%|'); " WHEN OTHERS THEN " dbms_output.put_line( " '|*Error*|%RED%Accessing user profile for PUBLIC user' || " sqlerrm || '%ENDCOLOR%|'); " END; " BEGIN " l_host := wwsec_oid.get_preference_value('ldap_host'); " l_port := wwsec_oid.get_preference_value('ldap_port'); " dbms_output.put_line('|*Host*|' || l_host || '|'); " dbms_output.put_line('|*Port*|' || l_port || '|'); " EXCEPTION " WHEN OTHERS THEN " dbms_output.put_line( " '|*Error*|%RED%Could not get OID host and/or port%ENDCOLOR%|'); " RAISE error_oiddiag_discontinue; " RETURN; " END; " BEGIN " l_session := dbms_ldap.init(l_host, l_port); " l_status := dbms_ldap.simple_bind_s(l_session,null,null); " IF l_status = 0 " THEN " dbms_output.put_line('|*Status*|OID server is up|'); " ELSE " dbms_output.put_line('[[['); " dbms_output.put_line( " '|*Error*|%RED%Could not connect to OID server.' || " REPLACE(REPLACE(REPLACE(LTRIM(dbms_ldap.err2string(l_status)), " '|','|'), " '<','<'), " '>','>') || " '%ENDCOLOR%|'); " dbms_output.put_line(']]]'); " RAISE error_oiddiag_discontinue; " RETURN; " END IF; " l_status := dbms_ldap.unbind_s(l_session); " EXCEPTION " WHEN OTHERS THEN " dbms_output.put_line('[[['); " dbms_output.put_line( " '|*Error*|%RED%Could not access the OID server.' || " REPLACE(REPLACE(REPLACE(LTRIM(wwsec_oid.get_last_error), " '|','|'), " '<','<'), " '>','>') || " '%ENDCOLOR%|'); " dbms_output.put_line(']]]'); " END; " BEGIN " l_app_dn := wwsec_oid.get_application_dn; " l_session := wwsec_oid.bind_application; " dbms_output.put_line( " '|*Application*|Application DN and password are correct|'); " wwsec_oid.unbind; " EXCEPTION " WHEN OTHERS THEN " dbms_output.put_line('[[['); " dbms_output.put_line( " '|*Error*|%RED%Could not connect to the application.' || " REPLACE(REPLACE(REPLACE(LTRIM(wwsec_oid.get_last_error), " '|','|'), " '<','<'), " '>','>')); " dbms_output.put_line('Application DN: ' || " l_app_dn || " 'Application password: ' || " wwsec_oid.get_application_pwd || " '%ENDCOLOR%|'); " dbms_output.put_line(']]]'); " wwsec_oid.unbind; " RAISE error_oiddiag_discontinue; " RETURN; " END; " BEGIN " dbms_output.put_line('|*Default Subscriber*|' || " wwsec_oid.get_default_subscriber || '|'); " EXCEPTION " WHEN OTHERS THEN " dbms_output.put_line('[[['); " dbms_output.put_line( " '|*Error*|%RED%Could not get the default subscriber DN' || " REPLACE(REPLACE(REPLACE(LTRIM(wwsec_oid.get_last_error),'|','|'), " '<','<'), " '>','>') || " '%ENDCOLOR%|'); " dbms_output.put_line(']]]'); " END; " BEGIN " l_found := wwsec_oid.is_group_member(p_user => l_app_dn, " p_group => 'cn=authenticationServices, " cn=Groups, " cn=OracleContext'); " IF l_found " THEN " dbms_output.put_line('|*Application Group*|' || " 'Application is a member of AuthenticationServices|'); " ELSE " l_found := wwsec_oid.is_group_member(p_user => l_app_dn, " p_group =>'cn=OracleUserSecurityAdmins, " cn=Groups, " cn=OracleContext'); " IF l_found " THEN " dbms_output.put_line('|*Application Group*|Application is a member of ' || " 'OracleUserSecurityAdmins|'); " ELSE " dbms_output.put_line('|*Error*|%RED%Application is NOT a member of ' || " 'AuthenticationServices%ENDCOLOR%|'); " END IF; " END IF; " IF NOT l_is_sso AND l_version LIKE '9.0.2%' " THEN " l_found := wwsec_oid.is_group_member(p_user => l_app_dn, " p_group => 'cn=userProxyPrivilege, " cn=Groups, " cn=OracleContext'); " IF l_found " THEN " dbms_output.put_line('|*Application Group*|' || " 'Application is a member of userProxyPrivilege|'); " ELSE " dbms_output.put_line('|*Error*|%RED%Application is NOT a member of ' || " 'userProxyPrivilege%ENDCOLOR%|'); " END IF; " END IF; " EXCEPTION " WHEN OTHERS THEN " dbms_output.put_line('[[['); " dbms_output.put_line( " '|*Error*|%RED%Could not check Application privileges.' || " REPLACE(REPLACE(REPLACE(LTRIM(wwsec_oid.get_last_error), " '|','|'), " '<','<'), " '>','>') || " '%ENDCOLOR%|'); " dbms_output.put_line(']]]'); " wwsec_oid.unbind; " RAISE error_oiddiag_discontinue; " RETURN; " END; " check_user('ORCLADMIN'); " check_user('PUBLIC'); " IF NOT l_is_sso " THEN " check_group(wwsec_api.group_authenticated_users,'AUTHENTICATED_USERS'); " check_group(wwsec_api.group_dba,'DBA'); " check_group(wwsec_api.group_webdb_administrators,'PORTAL_ADMINISTRATORS'); " END IF; " wwsec_oid.unbind; "EXCEPTION " WHEN error_oiddiag_continue THEN " RETURN; " WHEN error_oiddiag_discontinue THEN " NULL; "END; "/ } call writeSql($sql) if isCreated(true) toc '2:[[',getFile(),'][rda_report][OID Diagnostics]]' =head2 Configuration Files Gathers Portal-related configuration files. =cut debug ' Inside PDA module, collecting the configuration files' pretoc '2:Configuration Files' call sort_files(3,$TAIL,\ catFile($ORACLE_HOME,'Apache','Apache','setupinfo.txt'),\ catFile($ORACLE_HOME,'portal','conf','cache.xml'),\ catFile($ORACLE_HOME,'portal','conf','iasconfig.xml'),\ catFile($ORACLE_HOME,'webcache','webcache.xml'),\ catFile($ORACLE_HOME,'webcache','internal.xml'),\ catFile($ORACLE_HOME,'webcache','internal_admin.xml'),\ catFile($ORACLE_HOME,'ultrasearch','webapp','config',\ 'ultrasearch.properties'),\ catFile($ORACLE_HOME,'ldap','das','das.properties'),\ catFile($ORACLE_HOME,'ldap','das','oiddas.properties'),\ catFile($ORACLE_HOME,'xdk','admin','xml.properties')) unpretoc =head2 Log Files Gathers Portal-related log files. =cut debug ' Inside PDA module, collecting the log files' pretoc '2:Log Files' call sort_files(3,$TAIL,\ catFile($ORACLE_HOME,'assistants','opca','install.log'),\ catFile($ORACLE_HOME,'opmn','logs','midtier.log'),\ catFile($ORACLE_HOME,'webcache','logs','event_log')) unpretoc =head2 OC4J_Portal Files Gathers OC4J_Portal configuration and log files. =cut debug ' Inside PDA module, linking portal configuration files' var $dir = catDir($J2EE_TOP,'OC4J_Portal') pretoc '2:OC4J_Portal Config Dir Structure' call group_shares(3,'J2EE_CFG','J2EE',$dir,true) unpretoc debug ' Inside PDA module, linking portal log files' pretoc '2:OC4J_Portal Log Dir Structure' call group_shares(3,'J2EE_LOG','J2EE',$dir,true) unpretoc debug ' Inside PDA module, linking portal application configuration files' var $dir = catDir($J2EE_TOP,'OC4J_Portal','applications') pretoc '2:OC4J_Portal Applications Dir' pretoc '3:Configuration Files' call group_shares(4,'J2EE_ACFG','J2EE',$dir,true) unpretoc debug ' Inside PDA module, linking portal application log files' pretoc '3:Application.log Files' call group_shares(4,'J2EE_ALOG','J2EE',$dir,false) unpretoc 2 debug ' Inside PDA module, linking portal deployment configuration files' var $dir = catDir($J2EE_TOP,'OC4J_Portal','application-deployments') pretoc '2:OC4J_Portal Application-deployments Dir' pretoc '3:Configuration Files' call group_shares(4,'J2EE_DCFG','J2EE',$dir,true) unpretoc debug ' Inside PDA module, linking portal deployment log files' pretoc '3:Application.log Files' call group_shares(4,'J2EE_DLOG','J2EE',$dir,false) unpretoc 2 # Restore the previous context if !$PDA_LOCAL {if defined($env) call setSqlSid($env) call setSqlLogin($old) call setSqlFailure($try) } =head1 SEE ALSO See also L, L, L. =begin credits =over 10 =item RDA 4.5: Ersan Eser, Krishna Kumar, Rishi Mehrotra, Meraj Mohammed. =item RDA 4.6: Ersan Eser. =back =end credits =head1 COPYRIGHT NOTICE Copyright (c) 2002-2008, 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