# OWBr11g.def: Collects Oracle Warehouse Builder Information (11g) # $Id: OWBr11g.def,v 1.9 2009/02/06 18:22:53 mschenke Exp $ # ARCS: $Header: /u02/webapps/workbench/cvs/RDA_4/src/scripting/lib/modules/OWBr11g.def,v 1.9 2009/02/06 18:22:53 mschenke Exp $ # # Change History # 20090114 LDE Change the copyright notice. =head1 NAME OWBr11g - Collects Oracle Warehouse Builder Information (11g) =head1 DESCRIPTION This module collects Oracle Warehouse Builder-related information (11g). =cut # Initialisation import $TOC,$TOP # Load the common macros run DBinfo() # Check 11g repository presence' debug ' Inside OWB module, checking 11g repository objects' set $sql {SELECT DECODE(COUNT(*),0,'NOTFOUND','PRESENT') " FROM all_objects " WHERE object_name IN ('ALL_IV_INSTALLATIONS','ALL_IV_WORKSPACES') " AND OWNER = 'OWBSYS'; } if grepSql($sql,'NOTFOUND') {echo 'No 11g repositories found' return } =head2 report11g - Repository Information (11g) Gathers release-related information. =cut debug ' Inside OWB module, getting 11g repository information' report report11g var $TTL = '---+!! Repository Information (11g)' var @TTL = ('',\ "---+ OWBSYS Repository Version\012\ ---## From View owbsys.all_iv_installations",\ "---+ Workspaces and Owners\012\ ---## From View owbsys.all_iv_workspaces",\ "---+ Workspaces Users\012\ ---## From Views owbsys.workspace_assignment, \ owbsys.all_iv_workspaces",\ "---+ Control Center in Current Instance\012\ ---## From View owbsys.wb_rtv_service_nodes",\ "---+ OWBSYS Size",\ "---+ OWBSYS WB_RT_Platform_Repository\012\ ---## From Table owbsys.wb_rt_platform_properties",\ "---+ OWB WB_RT_Service_Job_Logs\012\ ---## Last 10 Entries from Table owbsys.wb_rt_service_job_logs") var @HDR = ('',\ '|*Version*|*Release*|*Creation Date*|*Update Date*|*Created By*|\ *Updated By*|',\ '|*Name*|*Owner*|*Creation Date*|*Update Date*|*Created By*|\ *Updated By*|',\ '|*Name*|*Owner*|*User*|*Creation Timestamp*|*Update Timestamp*|\ *Created By*|*Updated By*|',\ undef,\ '|*Table*| *Rows*|*Platform Properties Seeded Correctly*|') set $sql {SET serveroutput on "ALTER session SET current_schema = OWBSYS; "SELECT '|' || " installed_version || '|' || " release || '|' || " TO_CHAR(created_on,'DD-Mon-YYYY') || '|' || " TO_CHAR(updated_on,'DD-Mon-YYYY') || '|' || " created_by || '|' || " updated_by || '|' " FROM all_iv_installations; "PROMPT ___Macro_separator(2)___ "SELECT '|' || " workspace_name || '|' || " workspace_owner || '|' || " TO_CHAR(created_on,'DD-Mon-YYYY') || '|' || " TO_CHAR(updated_on,'DD-Mon-YYYY') || '|' || " created_by || '|' || " updated_by || '|' " FROM all_iv_workspaces; "PROMPT ___Macro_separator(3)___ "SELECT '|' || " w.workspace_name || '|' || " a.workspace_owner || '|' || " w.user_name || '|' || " w.creationtimestamp || '|' || " w.updatetimestamp || '|' || " w.createdby || '|' || " w.updatedby || '|' " FROM owbsys.workspace_assignment w, owbsys.all_iv_workspaces a " WHERE w.workspace_id = a.workspace_id " AND isworkspaceowner = 0 " ORDER BY w.workspace_name,a.workspace_owner; "PROMPT ___Macro_separator(4)___ "DECLARE " l_act NUMBER(2); " l_ena NUMBER(2); " l_flg VARCHAR2(1); " l_hom VARCHAR2(80); " l_hst VARCHAR2(30); " l_ins NUMBER(2); " l_lck VARCHAR2(128); " l_lim INTEGER := 0; " l_nam VARCHAR2(50); " l_nod NUMBER(2); " l_prt VARCHAR2(5); " l_sql VARCHAR2(255) := NULL; " l_sta NUMBER; " l_svc VARCHAR2(30); " l_ver VARCHAR2(11); " " TYPE t_cursor IS REF CURSOR; " c_dyn t_cursor; "BEGIN " l_sql := 'SELECT node_id, " instance_number, " host, " port, " service_name, " enabled, " runtime_version, " server_side_home, " instance_name, " currently_active " FROM wb_rtv_service_nodes'; " OPEN c_dyn FOR l_sql; " FETCH c_dyn " INTO l_nod,l_ins,l_hst,l_prt,l_svc,l_ena,l_ver,l_hom,l_nam,l_act; " IF c_dyn%FOUND " THEN " dbms_output.enable(100000); " dbms_output.put_line('| *Available* | *Node*| *Inst*|*Host*| ' || " '*Port*|*Service Name*| *Enabled*|*RT Version*|' || " '*Server Side Home*|*Instance Name*| ' || " '*Currently Active*|'); " LOOP " EXIT WHEN c_dyn%NOTFOUND; " dbms_lock.allocate_unique('WBLK_OWBSYS',l_lck); " l_sta := dbms_lock.request(l_lck,dbms_lock.x_mode,l_lim,TRUE); " IF l_sta = 0 " THEN " l_sta := dbms_lock.release(l_lck); " l_flg := 'N'; " ELSE " l_flg := 'Y'; " END IF; " dbms_output.put_line('|' || " l_flg || ' | ' || " l_nod || '| ' || " l_ins || '|' || " l_hst || ' | ' || " l_prt || '|' || " l_svc || ' | ' || " l_ena || '|' || " l_ver || ' |' || " l_hom || ' |' || " l_nam || ' | ' || " l_act || '|'); " FETCH c_dyn " INTO l_nod,l_ins,l_hst,l_prt,l_svc,l_ena,l_ver,l_hom,l_nam,l_act; " END LOOP; " END IF; " CLOSE c_dyn; "END; "/ "PROMPT ___Macro_separator(5)___ "DECLARE " l_cnt NUMBER; "BEGIN " SELECT COUNT(*) " INTO l_cnt " FROM owbsys.cmpmmmstorage; " dbms_output.put_line('|owbsys.cmpmmmstorage| ' || l_cnt || '|N/A|'); " SELECT COUNT(*) " INTO l_cnt " FROM owbsys.cmpmmmclasses; " dbms_output.put_line('|owbsys.cmpmmmclasses| ' || l_cnt || '|N/A|'); " SELECT COUNT(*) " INTO l_cnt " FROM owbsys.allinterlink; " dbms_output.put_line('|owbsys.allinterlink| ' || l_cnt || '|N/A|'); " SELECT COUNT(*) " INTO l_cnt " FROM owbsys.allpctree; " dbms_output.put_line('|owbsys.allpctree| ' || l_cnt || '|N/A|'); " SELECT COUNT(*) " INTO l_cnt " FROM owbsys.wb_rt_platform_properties; " IF l_cnt > 250 " THEN " dbms_output.put_line('|owbsys.wb_rt_platform_properties| '||l_cnt||'|Y|'); " ELSE " dbms_output.put_line('|owbsys.wb_rt_platform_properties| '||l_cnt||'|N|'); " END IF; "END; "/ "PROMPT ___Macro_separator(6)___ "DECLARE " l_flg BOOLEAN := TRUE; " l_sql VARCHAR2(1500) := NULL; " TYPE t_cursor IS REF CURSOR; " c_dyn t_cursor; " TYPE rec_sql IS RECORD(l_pth VARCHAR2(75),l_val VARCHAR2(75)); " TYPE tab_sql IS TABLE OF rec_sql INDEX BY BINARY_INTEGER; " dst_sql TAB_SQL; "BEGIN " dbms_output.enable(100000); " l_sql := 'SELECT SUBSTR(property_path,1,75),SUBSTR(property_value,1,75) " FROM owbsys.wb_rt_platform_properties " WHERE property_path IN ( " ''property.RuntimePlatform.0.delete_all_job_on_complete'', " ''property.RuntimePlatform.0.exceptions'', " ''property.RuntimePlatform.0.logfile_max_size'', " ''property.RuntimePlatform.0.max_number_logfiles'', " ''property.RuntimePlatform.0.messages'', " ''property.RuntimePlatform.0.platform'', " ''property.RuntimePlatform.0.platform_net_host_name'', " ''property.RuntimePlatform.0.platform_net_service_name'', " ''property.RuntimePlatform.0.purge_delay_hours'', " ''property.RuntimePlatform.0.purge_minimum_minutes'', " ''property.RuntimePlatform.0.recovery'', " ''property.RuntimePlatform.0.runtime_home'', " ''property.RuntimePlatform.0.service_command'', " ''property.RuntimePlatform.0.service_parameters'', " ''property.RuntimePlatform.0.temp_dir_name'', " ''property.RuntimePlatform.0.trace'', " ''property.RuntimePlatform.0.trace_control_on'', " ''property.RuntimePlatform.0.uoid'', " ''property.RuntimePlatform.0.version'', " ''property.RuntimePlatform.0.min_service_version'')'; " OPEN c_dyn FOR l_sql; " FETCH c_dyn BULK COLLECT INTO dst_sql; " IF (dst_sql.COUNT > 0) " THEN " dbms_output.put_line('|*Property Path*|*Property Value*|'); " FOR i IN dst_sql.FIRST..dst_sql.LAST " LOOP " dbms_output.put_line('|' || " dst_sql(i).l_pth || ' |' || " dst_sql(i).l_val || ' |'); " END LOOP; " l_flg := TRUE; " END IF; " CLOSE c_dyn; " l_sql := 'SELECT SUBSTR(property_path,1,75),SUBSTR(property_value,1,75) " FROM owbsys.wb_rt_platform_properties " WHERE property_path LIKE ''%11g'' " AND property_path NOT LIKE ''%OEM%'''; " OPEN c_dyn FOR l_sql; " FETCH c_dyn BULK COLLECT INTO dst_sql; " IF (dst_sql.COUNT > 0) " THEN " IF l_flg " THEN " dbms_output.put_line('[[#Top][Back to top]]'); " END IF; " dbms_output.put_line( " '---+ Runtime Platform Properties for SQLPLUS and SQL*Loader '); " dbms_output.put_line('|*Property Path*|*Property Value*|'); " FOR i IN dst_sql.FIRST..dst_sql.LAST " LOOP " dbms_output.put_line('|' || " dst_sql(i).l_pth || ' |' || " dst_sql(i).l_val || ' |'); " END LOOP; " END IF; " CLOSE c_dyn; "END; "/ "PROMPT ___Macro_separator(7)___ "DECLARE " l_cnt NUMBER; " l_max NUMBER; " l_msg VARCHAR2(2000); " l_off NUMBER; " l_sql VARCHAR2(400) := NULL; " l_tim VARCHAR2(30); " " TYPE t_cursor IS REF CURSOR; " c_dyn t_cursor; "BEGIN " dbms_output.enable(300000); " l_sql := 'SELECT COUNT(*) " FROM owbsys.wb_rt_service_job_logs'; " EXECUTE IMMEDIATE l_sql " INTO l_max; " IF l_max > 0 " THEN " dbms_output.put_line('|*Time Stamp*|*File Path*|*Message*|'); " l_sql := 'SELECT TO_CHAR(time_stamp,''DD-Mon-YYYY HH24:MI:SS''), " REPLACE(SUBSTR(message,9+instr(message,''command''),500), " CHR(10),''%BR%'') " FROM owbsys.wb_rt_service_job_logs " ORDER BY time_stamp'; " OPEN c_dyn FOR l_sql; " l_cnt := 0; " LOOP " FETCH c_dyn " INTO l_tim,l_msg; " EXIT WHEN c_dyn%NOTFOUND; " IF l_cnt >= l_max - 10 " THEN " -- display last 10 entries " l_off := INSTR(l_msg,'run_service'); " IF l_off > 0 " THEN " dbms_output.put_line('|' || " l_tim || ' |' || " SUBSTR(l_msg,1,14 + l_off) || ' |\'); " dbms_output.put_line( " SUBSTR(l_msg,15 + l_off,200) || ' |'); " ELSE " dbms_output.put_line('|' || l_tim || ' |\'); " dbms_output.put_line(SUBSTR(l_msg,1,240) || ' ||'); " END IF; " END IF; " l_cnt := l_cnt + 1; " END LOOP; " CLOSE c_dyn; " END IF; "END; "/ } call separator(1) call writeSql($sql) call separator(0,'Repository Information (11g)') # RUN_SERVICE.SH / RUN_SERVICE.BAT set $sql {SET serveroutput on "ALTER session SET current_schema = OWBSYS; "DECLARE " l_hom VARCHAR2(100); " l_pth VARCHAR2(100); " l_sql VARCHAR2(200) := NULL; " l_val VARCHAR2(512); " TYPE t_cursor IS REF CURSOR; " c_dyn t_cursor; "BEGIN " l_sql := 'SELECT server_side_home " FROM owbsys.wb_rt_service_nodes'; " OPEN c_dyn FOR l_sql; " LOOP " FETCH c_dyn " INTO l_hom; " EXIT WHEN c_dyn%NOTFOUND; " l_sql := 'SELECT property_value " FROM owbsys.wb_rt_platform_properties " WHERE property_path = " ''property.RuntimePlatform.0.service_command'''; " EXECUTE IMMEDIATE l_sql " INTO l_pth; " dbms_output.put_line( l_hom || '|' || l_pth); " END LOOP; " CLOSE c_dyn; "END; "/ } prefix {write '---+ RUN_SERVICE.SH / RUN_SERVICE.BAT' write '|*Runtime Repository*|*Run Service*|' } call loadSql($sql) var ($cnt,$err) = (0,0) loop $lin (getSqlLines()) {break match($lin,'^\s*(ORA-|ERROR)') var ($hom,$pth) = split('\|',$lin,2) if or($hom,$pth) {if match($pth,'run_service\.sh') var $fil = catFile($hom,'owb','bin','unix','run_service.sh') else var $fil = catFile($hom,'owb','bin','win32','run_service.bat') if testFile('r',$fil) {suspend report data run_service call writeData($fil) var $url = getFile('.') resume report incr $cnt write '|run_service|',concat('[[',$url,'][_blank][',encode($fil),']]'),'|' } else {incr $err write '|run_service|',encode($fil),'|' } } else write '|run_service| |' } if hasOutput(true) {if $cnt write ' * Links point to files that have been collected in their original \ format. Opening them directly in your browser can present \ security risks. To prevent them, access the file outside the \ browser or use the link to save them and use an adequate viewer.' if $err {if $OWB_LOCAL write ' * Files are not accessible for RDA. Permission problems or \ incorrect ``SERVER_SIDE_HOME`` in table ``WB_RT_SERVICE_NODES`` \ are possible causes.%BR%' else write ' * Files are not accessible for RDA. If not located on a remote \ system, permission problems or incorrect ``SERVER_SIDE_HOME`` \ in table ``WB_RT_SERVICE_NODES`` are possible causes.%BR%' } write $TOP } =begin credits =over 10 =item RDA 4.10: Sarath Babu, Jean-Philippe Peelman. =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