# OWBinfo.ctl: Collects generic Oracle Warehouse Builder Information
# $Id: OWBinfo.ctl,v 1.3 2013/10/30 07:18:34 RDA Exp $
# ARCS: $Header: /home/cvs/cvs/RDA_8/src/scripting/lib/collect/OFM/OWBinfo.ctl,v 1.3 2013/10/30 07:18:34 RDA Exp $
#
# Change History
# 20130528  JGS  Enhance module.

=head1 NAME

OFM:OWBinfo - Collects Generic Oracle Warehouse Builder Information

=head1 DESCRIPTION

This module collects generic Oracle Warehouse Builder-related information.

=cut

# Load the common macros
run DB:DBinfo()

=head2 generic - Generic Information

Gathers generic information.

=cut

debug ' Inside OWB module, getting generic information'
report generic

var $TTL = '---+!! Generic Information'
var @TTL = ('',\
            '---+ Database Version',\
            '---+ Real Application Cluster',\
            '---+ Database Registry',\
            '---+ Database Registry Version',\
            '---+ Version',\
            '---+ Java Virtual Machine',\
            '---+ Validity of Java Virtual Machine',\
            '---+ Number of Java Objects per Schema',\
            '---+ Total Java Objects',\
            '---+ Invalid Java Objects',\
            '---+ Validity of Java Objects',\
            '---+ Invalid Objects (Non Java Objects)',\
            '---+ Total Objects',\
            '---+ Instance Parameters',\
            '---+ Resources',\
            '---+ Database Parameters',\
            '---+ Jobs Scheduled in Relation to job_queue_processes',\
            '---+ List of Jobs Scheduled',\
            '---+ Jobs Running in Relation to job_queue_processes',\
            '---+ List of DBA Jobs Running',\
            '---+ Oracle Workflow',\
            '---+ Oracle Exadata',\
            '---+ Scheduler Packages Status',\
            '---+ DBMS_SCHEDULER Package Status')
var @HDR = ('',\
            '|*Version*|',\
            undef,\
            '|*Name*|*Status*|*Version*|',\
            '|*Description*|*Version*|',\
            '|*Name*|*Version*|',\
            '|*Name*|*Status*|*Version*|',\
            '|*Description*|',\
            '|*Owner*| *Count*|',\
            '|*Description*|',\
            '|*Owner*|*Object Name*|*Object Type*|',\
            '|*Description*|',\
            '|*Owner*|*Object Name*|*Object Type*|',\
            '|*Description*|',\
            '|*Name*|*Value*|',\
            '|*Description*| *Value*| *Recommended Value*|',\
            '|*Parameter*|*Value*|',\
            undef,\
            '|*Name*|*Value*|*Last Date*|*Next Date*|',\
            undef,\
            '|*Name*|*Value*|*Last Date*|*Next Date*|',\
            undef,\
            undef,\
            '|*Owner*|*Object Name*|*Object Type*|*Status*|')

set $sql
{SET serveroutput on
"PROMPT ___Macro_separator(1)___
"SELECT '|' ||
"       banner || ' |'
" FROM v$version;
"PROMPT ___Macro_separator(2)___
"DECLARE
" l_act gv$active_instances.inst_name%TYPE;
" l_cls v$parameter.value%TYPE;
" l_cnt NUMBER;
" l_dly v$parameter.value%TYPE;
" l_hst gv$instance.host_name%TYPE;
" l_id  NUMBER;
" l_ins NUMBER;
" l_nam gv$instance.instance_name%TYPE;
" l_num NUMBER;
" l_sql VARCHAR2(800) := NULL;
" l_sta gv$instance.database_status%TYPE;
" l_svc v$parameter.value%TYPE;
"
" TYPE t_cursor IS ref cursor;
" c_dyn t_cursor;
"BEGIN
" SELECT value
"  INTO l_cls
"  FROM v$parameter
"  WHERE name = 'cluster_database';
" SELECT value
"  INTO l_ins
"  FROM v$parameter
"  WHERE name = 'cluster_database_instances';
" SELECT COUNT(*)
"  INTO l_cnt
"  FROM v$parameter
"  WHERE name = 'max_commit_propagation_delay';
" IF l_cnt > 0
" THEN
"  SELECT value
"   INTO l_dly
"   FROM v$parameter
"   WHERE name = 'max_commit_propagation_delay';
" END IF;
" SELECT value
"  INTO l_svc
"  FROM v$parameter
"  WHERE name = 'service_names';
" IF l_cls = 'TRUE'
" THEN
"  dbms_output.put_line('This is a Real Application Cluster, having ' ||
"                       TRIM(l_ins) || ' instances.%BR%');
"  dbms_output.put_line('Instances (gv$instance)%BR%');
"  dbms_output.put_line('| *ID*| *Instance Number*|*Instance Name*|' ||
"                       '*Host Name*|*Database Status*|');
"  l_sql := 'SELECT inst_id,
"                   instance_number,
"                   instance_name,
"                   host_name,
"                   database_status
"                FROM gv$instance';
"  OPEN c_dyn FOR l_sql;
"  LOOP
"   FETCH c_dyn
"    INTO l_id,l_num,l_nam,l_hst,l_sta;
"   EXIT WHEN c_dyn%NOTFOUND;
"   dbms_output.put_line('| ' ||
"                        l_id || '| ' ||
"                        l_num || '|' ||
"                        l_nam || ' |' ||
"                        l_hst || ' |' ||
"                        l_sta || ' |');
"  END LOOP;
"  CLOSE c_dyn;
"  dbms_output.put_line('%BR%');
"  dbms_output.put_line('Active Instances (gv$active_instances)%BR%');
"  dbms_output.put_line('| *ID*| *Instance Number*|*Instance Name*|');
"  l_sql := 'SELECT inst_id,inst_number,inst_name
"             FROM gv$active_instances';
"  OPEN c_dyn FOR l_sql;
"  LOOP
"   FETCH c_dyn
"    INTO l_id,l_num,l_act;
"   EXIT WHEN c_dyn%NOTFOUND;
"   dbms_output.put_line('| ' ||
"                        l_id || '| ' ||
"                        l_num || '|' ||
"                        l_act || ' |');
"  END LOOP;
"  CLOSE c_dyn;
"  dbms_output.put_line('%BR%');
"  dbms_output.put_line('Instance Parameters%BR%');
"  dbms_output.put_line('|*Service Names*| ' || l_svc || '|');
"  IF l_cnt > 0
"  THEN
"   dbms_output.put_line('|*Max Commit Propagation Delay*| ' || l_dly || '|');
"  END IF;
" ELSE
"  dbms_output.put_line('|*Description*|');
"  dbms_output.put_line('|This is not a Real Application Cluster|');
" END IF;
"END;
"/
"PROMPT ___Macro_separator(3)___
"SELECT '|' ||
"       comp_name || ' |' ||
"       status || ' |' ||
"       version || ' |'
" FROM dba_registry;
"PROMPT ___Macro_separator(4)___
"DECLARE
" l_cnt NUMBER;
" l_ver v$instance.version%TYPE;
"BEGIN
" SELECT version
"  INTO l_ver
"  FROM v$instance;
" SELECT COUNT(*)
"  INTO l_cnt
"  FROM dba_registry
"  WHERE version <> l_ver;
" IF l_cnt = 0
" THEN
"  dbms_output.put_line('|All components have version|' || l_ver || '|');
" ELSE
"  dbms_output.put_line(
"   '|Some components do not have the same version as the instance|' ||
"   l_ver || '|');
" END IF;
"END;
"/
"PROMPT ___Macro_separator(5)___
"SELECT '|' ||
"       comp_name || ' |' ||
"       version || ' |'
" FROM dba_registry
" WHERE version NOT IN (SELECT version
"                        FROM v$instance);
"PROMPT ___Macro_separator(6)___
"SELECT '|' ||
"       comp_name || ' |' ||
"       status || ' |' ||
"       version || ' |'
" FROM dba_registry
" WHERE UPPER(comp_name) LIKE '%JAVA VIRTUAL%' OR UPPER(comp_name) LIKE '%XDK%';
"PROMPT ___Macro_separator(7)___
"DECLARE
" l_cnt NUMBER;
"BEGIN
" SELECT COUNT(*)
"  INTO l_cnt
"  FROM dba_registry
"  WHERE comp_name LIKE '%JServer%'
"    AND STATUS = 'VALID';
" IF l_cnt = 1
" THEN
"  dbms_output.put_line(
"   '|JServer Java Virtual Machine is AVAILABLE and VALID|');
" ELSE
"  dbms_output.put_line(
"   '|JServer Java Virtual Machine is NOT available or NOT valid|');
" END IF;
" SELECT COUNT(*)
"  INTO l_cnt
"  FROM dba_registry
"  WHERE comp_name LIKE '%XDK%'
"    AND status = 'VALID';
" IF l_cnt = 1
" THEN
"  dbms_output.put_line('|Oracle XDK is AVAILABLE and VALID|');
" ELSE
"  dbms_output.put_line('|Oracle XDK is NOT available or NOT valid|');
" END IF;
" SELECT COUNT(*)
"  INTO l_cnt
"  FROM dba_registry
"  WHERE comp_name LIKE '%Java Packages%'
"    AND status = 'VALID';
" IF l_cnt = 1
" THEN
"  dbms_output.put_line(
"   '|Oracle Database Java Packages are AVAILABLE and VALID|');
" ELSE
"  dbms_output.put_line(
"   '|Oracle Database Java Packages are NOT available or NOT valid|');
" END IF;
"END;
"/
"PROMPT ___Macro_separator(8)___
"SELECT '|' ||
"       owner || ' | ' ||
"       COUNT(*) || '|'
" FROM dba_objects
" WHERE object_type LIKE '%JAVA%'
" GROUP BY owner;
"PROMPT ___Macro_separator(9)___
"DECLARE
" l_cnt NUMBER;
"BEGIN
" SELECT COUNT(*)
"  INTO l_cnt
"  FROM dba_objects
"  WHERE object_type LIKE '%JAVA%';
" IF l_cnt = 0
" THEN
"  dbms_output.put_line('|No Java objects installed|');
" ELSE
"  dbms_output.put_line('|' || l_cnt || ' objects|');
" END IF;
"END;
"/
"PROMPT ___Macro_separator(10)___
"SELECT '|' ||
"       SUBSTR(owner,1,10) || ' |' ||
"       SUBSTR(object_name,1,40) || ' |' ||
"       object_type || ' |'
" FROM dba_objects
" WHERE status = 'INVALID'
"   AND object_name LIKE '%JAVA%';
"PROMPT ___Macro_separator(11)___
"DECLARE
" l_cnt NUMBER;
"BEGIN
" SELECT COUNT(*)
"  INTO l_cnt
"  FROM dba_objects
"  WHERE object_type LIKE '%JAVA%'
"    AND status = 'INVALID';
" IF l_cnt = 0
" THEN
"  dbms_output.put_line('|All Java objects in the instance are valid|');
" ELSE
"  dbms_output.put_line('|' || l_cnt || ' objects|');
" END IF;
"END;
"/
"PROMPT ___Macro_separator(12)___
"SELECT '|' ||
"       SUBSTR(owner,1,10) || ' |' ||
"       SUBSTR(object_name,1,40) || ' |' ||
"       object_type || ' |'
" FROM dba_objects
" WHERE status = 'INVALID'
"   AND owner = 'SYS'
"   AND object_type NOT LIKE '%JAVA%';
"PROMPT ___Macro_separator(13)___
"DECLARE
" l_cnt NUMBER;
"BEGIN
" SELECT COUNT(*)
"  INTO l_cnt
"  FROM dba_objects
"  WHERE object_type NOT LIKE '%JAVA%'
"    AND status = 'INVALID';
" IF l_cnt = 0
" THEN
"  dbms_output.put_line('|All non-Java objects in the instance are valid|');
" ELSE
"  dbms_output.put_line('|' || l_cnt || ' objects|');
" END IF;
"END;
"/
"PROMPT ___Macro_separator(14)___
"SELECT '|' ||
"       LOWER(name) || ' |' ||
"       value || ' |'
" FROM v$parameter
" WHERE LOWER(name) IN ('service_names','compatible','sga_target',
"                       'java_pool_size','enqueue_resources','open_cursors',
"                       'remote_login_passwordfile',
"                       'o7_dictionary_accessibility','aq_tm_processes',
"                       'job_queue_processes','open_cursors','db_block_size',
"                       'db_cache_size','compatible','lock_sga',
"                       'optimizer_mode','log_buffer','shared_pool_size',
"                       'large_pool_size','db_file_multiple_block_read_count',
"                       'disk_asynch_io','db_writer_processes','dbwr_io_slaves',
"                       'parallel_automatic_tuning','pga_aggregate_target',
"                       'parallel_adaptive_multi_user','plsql_optimizer_level',
"                       'log_checkpoint_timeout','statistics_level',
"                       'undo_management','enqueue_resources',
"                       'query_rewrite_enabled','user_indirect_data_buffers',
"                       'utl_file_dir','workarea_size_policy',
"                       'resource_manager_plan','cluster_database',
"                       'cluster_database_instances','memory_max_target',
"                       'memory_target','parallel_max_servers')
" ORDER BY name;
"PROMPT ___Macro_separator(15)___
"DECLARE
" l_cnt NUMBER;
" l_val NUMBER;
"BEGIN
" SELECT value
"  INTO l_val
"  FROM v$parameter
"  WHERE name = 'aq_tm_processes';
" IF l_val = 0
" THEN
"  dbms_output.put_line('|AQ_TM_PROCESSES| ' || l_val || '|Should be >= 1|');
" END IF;
" SELECT value
"  INTO l_val
"  FROM v$parameter
"  WHERE name = 'job_queue_processes';
" IF l_val < 10
" THEN
"  dbms_output.put_line('|JOB_QUEUE_PROCESSES| ' || l_val ||
"                       '|Should be >= 10|');
" END IF;
" SELECT value
"  INTO l_val
"  FROM v$parameter
"  WHERE name = 'open_cursors';
" IF l_val < 300
" THEN
"  dbms_output.put_line('|OPEN_CURSORS| ' || l_val || '|Should be >= 300|');
" END IF;
"END;
"/
"PROMPT ___Macro_separator(16)___
"SELECT '|' ||
"       parameter || ' |' ||
"       value || ' |'
" FROM nls_database_parameters;
"PROMPT ___Macro_separator(17)___
"DECLARE
" l_cnt NUMBER := 0;
" l_max NUMBER;
"BEGIN
" SELECT COUNT(*)
"  INTO l_cnt
"  FROM dba_jobs;
" IF l_cnt <> 0
" THEN
"  dbms_output.put_line('|*Number of jobs in DBA Jobs*| ' || l_cnt || '|');
" END IF;
" SELECT value
"  INTO l_max
"  FROM v$parameter
"  WHERE name = 'job_queue_processes';
" dbms_output.put_line('|*Value of job_queue_processes*| ' || l_max || '|');
" IF l_max < l_cnt
" THEN
"  dbms_output.put_line('%BR%');
"  dbms_output.put_line('|*Description*|');
"  dbms_output.put_line('|Number of jobs in DBA Jobs is higher than the ' ||
"                       'value of parameter Job Queue Processes|');
" END IF;
"END;
"/
"PROMPT ___Macro_separator(18)___
"SELECT '|' ||
"       schema_user || ' |' ||
"       REPLACE(REPLACE(what,'|','&#124;'),CHR(10),'%BR%') || ' |' ||
"       TO_CHAR(last_date,'DD-Mon-YYYY HH24:MI:SS') || ' |' ||
"       TO_CHAR(next_date,'DD-Mon-YYYY HH24:MI:SS') || ' |'
" FROM dba_jobs
" ORDER BY schema_user;
"PROMPT ___Macro_separator(19)___
"DECLARE
" l_cnt NUMBER := 0;
" l_max NUMBER;
"BEGIN
" SELECT COUNT(*)
"  INTO l_cnt
"  FROM dba_jobs_running;
" IF l_cnt <> 0
" THEN
"  dbms_output.put_line('|*Number of in DBA Jobs running at '||
"                         LOWER(TO_CHAR(SYSDATE, 'DD-Mon-YYYY HH24:MI:SS'))||
"                         '*| ' || l_cnt || '|');
" END IF;
" SELECT value
"  INTO l_max
"  FROM v$parameter
"  WHERE name = 'job_queue_processes';
" dbms_output.put_line('|*Value of job_queue_processes*| ' || l_max || '|');
" IF l_max < l_cnt
" THEN
"  dbms_output.put_line('%BR%');
"  dbms_output.put_line('|*Description*|');
"  dbms_output.put_line('|Number of jobs running in DBA Jobs Running is ' ||
"                       'higher than the value of parameter Job Queue '||
"                       'Processes|');
" END IF;
"END;
"/
"PROMPT ___Macro_separator(20)___
"SELECT '|' ||
"       j.schema_user || ' |' ||
"       REPLACE(REPLACE(j.what,'|','&#124;'),CHR(10),'%BR%') || ' |' ||
"       TO_CHAR(j.last_date,'DD-Mon-YYYY HH24:MI:SS') || ' |' ||
"       TO_CHAR(j.next_date,'DD-Mon-YYYY HH24:MI:SS') || ' |'
" FROM dba_jobs j,dba_jobs_running r
" WHERE r.job=j.job
" ORDER BY schema_user;
"PROMPT ___Macro_separator(21)___
"DECLARE
" l_own dba_objects.owner%TYPE;
" l_sql VARCHAR2(800) := NULL;
" l_ver VARCHAR2(64);
"
" CURSOR c_own IS
"  SELECT owner
"   FROM dba_objects
"   WHERE object_name = 'WF_RESOURCES';
"BEGIN
" OPEN c_own;
" FETCH c_own
"  INTO l_own;
" IF c_own%NOTFOUND
" THEN
"  dbms_output.put_line('|*Description*|');
"  dbms_output.put_line('|Workflow not installed|');
" ELSE
"  dbms_output.put_line('|*Owner*|*Version*|');
"  LOOP
"   l_sql := 'SELECT DISTINCT SUBSTR(TRIM(text),1,64)
             " FROM ' || l_own || '.wf_resources
             " WHERE name = ''WF_VERSION''';
"   EXECUTE IMMEDIATE l_sql
"    INTO l_ver;
"   dbms_output.put_line('|' || l_own ||' |' || l_ver || '|');
"   FETCH c_own
"    INTO l_own;
"   EXIT WHEN c_own%NOTFOUND;
"  END LOOP;
"  IF c_own%ROWCOUNT > 1
"  THEN
"   dbms_output.put_line('Warning: Multiple Workflow servers installed.%BR%');
"  END IF;
" END IF;
" CLOSE c_own;
"END;
"/
"PROMPT ___Macro_separator(22)___
"DECLARE
" l_cnt NUMBER := 0;
"BEGIN
" SELECT COUNT(*)
"  INTO l_cnt
"  FROM v$cell;
" dbms_output.put_line('|*Description*|');
" IF l_cnt > 0
" THEN
"  dbms_output.put_line('|This is an Exadata Database Machine, having ' ||
"                       TRIM(l_cnt) || ' cells|');
" ELSE
"  dbms_output.put_line('|This is not an Exadata Database Machine|');
" END IF;
"END;
"/
"PROMPT ___Macro_separator(23)___
"SELECT '|' ||
"       owner || ' |' ||
"       object_name || ' |' ||
"       object_type || ' |' ||
"       status || ' |'
" FROM dba_objects
" WHERE object_name IN ('DBMS_JOB','DBMS_SCHEDULER');
"PROMPT ___Macro_separator(24)___
"DECLARE
" l_disabled VARCHAR2(5);
"BEGIN
" dbms_scheduler.get_scheduler_attribute('SCHEDULER_DISABLED', l_disabled);
" dbms_output.put_line('|*Description*|');
" IF l_disabled = 'TRUE'
" THEN
"  dbms_output.put_line('|Warning: DBMS_SCHEDULER is disabled. This prevents
                        " the runtime service to start.|');
" ELSE
"  dbms_output.put_line('|DBMS_SCHEDULER is enabled in the Database|');
" END IF;
"END;
"/
}

call separator(1)
call writeSql($sql)
call separator(0,'Generic Information')

=begin credits

=over 10

=item RDA 4.10: Sarath Babu, Jean-Philippe Peelman.

=item RDA 4.20: Mark Rovers.

=item RDA 4.26: Mark Rovers.

=back

=end credits

=head1 COPYRIGHT NOTICE

Copyright (c) 2002, 2016, Oracle and/or its affiliates. All rights reserved.

=head1 TRADEMARK NOTICE

Oracle and Java are registered trademarks of Oracle and/or its
affiliates. Other names may be trademarks of their respective owners.

=cut