Rem Rem $Header: storage_ui_common_pkgdef.sql 25-jul-2005.22:05:14 sreddy Exp $ Rem Rem storage_ui_common_pkgdef.sql Rem Rem Copyright (c) 2004, 2005, Oracle. All rights reserved. Rem Rem NAME Rem storage_ui_common_pkgdef.sql - Rem Rem DESCRIPTION Rem package containing methods to retrieve host or group Rem storage data. Rem Rem NOTES Rem Only methods that work for both host and group should exist Rem in this package. Rem Rem MODIFIED (MM/DD/YY) Rem sreddy 07/23/05 - Fix bug#4253179, improve readbility Rem sreddy 07/23/05 - Added DELETE_MISSING_SNAPSHOT_HOSTS Rem ajdsouza 07/19/05 - modified get_refresj_job_info Rem ajere 06/15/05 - Fix bug# 4300002 Rem rmenon 05/02/05 - Rem rmenon 03/24/05 - Rem rmenon 03/11/05 - make get_refresh_job_info public Rem rmenon 03/01/05 - added info conveying if any refresh Rem job is running against a group or not. Rem rmenon 02/16/05 - Rem rmenon 02/09/05 - Formatted code to conform to EM coding Rem standards. Did code cleanup. Rem rmenon 01/14/05 - Rem rmenon 12/28/04 - Rem rmenon 11/23/04 - formatting changes. Rem rmenon 11/11/04 - logic for calculating error and config Rem change history for host and group storage Rem history data. Rem rmenon 11/01/04 - moved utilization summary logic to Rem PL/SQL code. Rem rmenon 10/09/04 - rmenon_storage_group_ui_and_sql_code_refactor Rem rmenon 09/28/04 - Created Rem CREATE OR REPLACE PACKAGE STORAGE_UI_COMMON_PKG AS G_GRP_STORAGE_HIST_ENABLED constant varchar2(30) := 'GROUP_STORAGE_HISTORY_ENABLED'; G_THREE_MONTHS CONSTANT VARCHAR2(2) := '3M'; G_ONE_MONTH CONSTANT VARCHAR2(2) := '1M'; G_ONE_YEAR CONSTANT VARCHAR2(3) := '12M'; G_DAYS_PER_MONTH CONSTANT INTEGER := 30; G_OS_DISK CONSTANT VARCHAR2(8) := 'OS_DISK'; G_VOLUME_MANAGER CONSTANT VARCHAR2(15) := 'VOLUME_MANAGER'; G_NFS CONSTANT VARCHAR2(4) := 'NFS'; G_LOCAL_FILESYSTEM CONSTANT VARCHAR2(17) := 'LOCAL_FILESYSTEM'; G_WRITE_PRIVELEGE CONSTANT VARCHAR2(5) := 'WRITE'; G_NFS_WRITE CONSTANT VARCHAR2(8) := 'NFSWRITE'; G_HOST_STORAGE_COLL_NAME CONSTANT VARCHAR2(15) := 'host_storage'; G_DB_CONFIG_COLL_NAME CONSTANT VARCHAR2(15) := 'oracle_dbconfig'; G_RAC_CONFIG_COLL_NAME CONSTANT VARCHAR2(16) := 'oracle_racconfig'; G_ASM_DB_DG_COLL_NAME CONSTANT VARCHAR2(25) := 'Database_DiskGroup_Usage'; G_ASM_DG_USAGE_10_2_COLL CONSTANT VARCHAR2(20) := 'DiskGroup_Usage_10_2'; G_ASM_DB_DG_METRIC CONSTANT VARCHAR2(25) := 'Database_DiskGroup_Usage'; G_ASM_DG_USAGE_METRIC CONSTANT VARCHAR2(20) := 'DiskGroup_Usage'; G_HOST_STORAGE_SNAPSHOT_TYPE constant varchar2(15) := 'host_storage'; -- -- Gets data to render the summary section of UI above the page -- properties in host storage UI pages. -- PROCEDURE GET_STORAGE_SUMMARY(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_target_timezone_region OUT VARCHAR2, p_collection_info OUT SYS_REFCURSOR, p_allocation_summary OUT SYS_REFCURSOR, p_usage_summary OUT SYS_REFCURSOR, p_utilization_summary OUT SYS_REFCURSOR, p_num_of_targets OUT NUMBER, p_targets_having_snapshot OUT NUMBER, p_targets_with_coll_defined OUT NUMBER, p_targets_with_coll_enabled OUT NUMBER, p_hist_data_exists_flag OUT VARCHAR2, p_storage_unit OUT VARCHAR2, p_history_metric_defined_flag OUT VARCHAR2); -- -- Inserts target names into a temporary table MGMT_STORAGE_REPORT_UI_TARGETS. -- In case of host, inserts the host itself. -- In case of group, inserts associated host list for the group. -- This approach allows common code for both host and group targets. -- PROCEDURE INSERT_TARGETS_FOR_UI_QUERIES(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2); PROCEDURE INSERT_TARGETS_FOR_UI_QUERIES(p_target_guid IN RAW); -- -- Deletes hosts from MGMT_STORAGE_REPORT_UI_TARGETS table -- which do not have valid host_storage snapshot. -- PROCEDURE DELETE_MISSING_SNAPSHOT_HOSTS; -- -- Gets target name and type, given target guid of a target. -- PROCEDURE GET_TARGET_NAME_TYPE(p_target_guid IN RAW, p_target_name OUT VARCHAR2, p_target_type OUT VARCHAR2); -- -- Gets storage history data to render the history summary page. -- PROCEDURE GET_STORAGE_HIST_SUMMARY(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_period IN VARCHAR2, p_target_timezone_region OUT VARCHAR2, p_collection_timestamp OUT DATE, p_history_summary OUT SYS_REFCURSOR, p_history_stats OUT SYS_REFCURSOR, p_storage_unit OUT VARCHAR2, p_min_timeseries_date OUT DATE, p_max_timeseries_date OUT DATE, p_storage_hist_enabled_flag OUT VARCHAR2); -- -- Gets storage history data to render the storage history of -- databases on a host. -- PROCEDURE GET_STORAGE_HIST_DB(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_period IN VARCHAR2, p_target_timezone_region OUT VARCHAR2, p_collection_timestamp OUT DATE, p_history_summary OUT SYS_REFCURSOR, p_history_stats OUT SYS_REFCURSOR, p_storage_unit OUT VARCHAR2, p_min_timeseries_date OUT DATE, p_max_timeseries_date OUT DATE, p_storage_hist_enabled_flag OUT VARCHAR2); -- -- Gets storage history data to render the storage history of -- local file systems on a host. -- PROCEDURE GET_STORAGE_HIST_LOCALFS(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_period IN VARCHAR2, p_target_timezone_region OUT VARCHAR2, p_collection_timestamp OUT DATE, p_history_summary OUT SYS_REFCURSOR, p_history_stats OUT SYS_REFCURSOR, p_storage_unit OUT VARCHAR2, p_min_timeseries_date OUT DATE, p_max_timeseries_date OUT DATE, p_storage_hist_enabled_flag OUT VARCHAR2); -- -- Gets storage history data to render the storage history of NFS on a host. -- PROCEDURE GET_STORAGE_HIST_NFS(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_period IN VARCHAR2, p_target_timezone_region OUT VARCHAR2, p_collection_timestamp OUT DATE, p_history_summary OUT SYS_REFCURSOR, p_history_stats OUT SYS_REFCURSOR, p_storage_unit OUT VARCHAR2, p_min_timeseries_date OUT DATE, p_max_timeseries_date OUT DATE, p_storage_hist_enabled_flag OUT VARCHAR2); -- -- Gets storage history data to render the storage history of -- local disks on a host. -- PROCEDURE GET_STORAGE_HIST_DISKS(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_period IN VARCHAR2, p_target_timezone_region OUT VARCHAR2, p_collection_timestamp OUT DATE, p_history_summary OUT SYS_REFCURSOR, p_history_stats OUT SYS_REFCURSOR, p_storage_unit OUT VARCHAR2, p_min_timeseries_date OUT DATE, p_max_timeseries_date OUT DATE, p_storage_hist_enabled_flag OUT VARCHAR2); -- -- Gets storage history data to render the storage history of asm on a host. -- PROCEDURE GET_STORAGE_HIST_ASM(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_period IN VARCHAR2, p_target_timezone_region OUT VARCHAR2, p_collection_timestamp OUT DATE, p_history_summary OUT SYS_REFCURSOR, p_history_stats OUT SYS_REFCURSOR, p_storage_unit OUT VARCHAR2, p_min_timeseries_date OUT DATE, p_max_timeseries_date OUT DATE, p_storage_hist_enabled_flag OUT VARCHAR2); -- -- Gets storage history data to render the storage history of volumes on a host. -- PROCEDURE GET_STORAGE_HIST_VOL(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_period IN VARCHAR2, p_target_timezone_region OUT VARCHAR2, p_collection_timestamp OUT DATE, p_history_summary OUT SYS_REFCURSOR, p_history_stats OUT SYS_REFCURSOR, p_storage_unit OUT VARCHAR2, p_min_timeseries_date OUT DATE, p_max_timeseries_date OUT DATE, p_storage_hist_enabled_flag OUT VARCHAR2); -- -- Runs storage history repository collection for host or group. -- PROCEDURE RUN_HIST_COLLECTION(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2); -- -- This PROCEDURE returns 'Y' if storage history for a group -- is enabled, 'N' otherwise. If the target type is host, -- it always returns 'Y'. -- FUNCTION GET_STORAGE_HIST_ENABLED(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2) RETURN VARCHAR2; -- -- Gets storage vendor disribution for a host or a group -- based on writeable NFS and disks information. -- PROCEDURE GET_STORAGE_VENDOR_DISTRIB(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_storage_vendor_distrib OUT SYS_REFCURSOR); -- -- Gets storage summary values in byte size numbers. -- If p_calc_storage_source_flag is FALSE, then it does not compute -- p_db_on_* values. It is set to FALSE for repository metric collections -- and TRUE for rendeting Storage Details page. -- PROCEDURE GET_STORAGE_SUMMARY_VALUES(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_calc_storage_source_flag IN NUMBER DEFAULT MGMT_GLOBAL.G_TRUE, p_vol_allocated OUT NUMBER, p_vol_overhead OUT NUMBER, p_vol_unallocated OUT NUMBER, p_vol_size OUT NUMBER, p_asm_allocated OUT NUMBER, p_asm_unallocated OUT NUMBER, p_asm_overhead OUT NUMBER, p_disk_allocated OUT NUMBER, p_disk_unallocated OUT NUMBER, p_disk_size OUT NUMBER, p_asm_size OUT NUMBER, p_writeable_nfs_allocated OUT NUMBER, p_db_on_local_disks OUT NUMBER, p_db_on_volumes OUT NUMBER, p_db_on_asm OUT NUMBER, p_db_on_local_fs OUT NUMBER, p_db_on_writeable_nfs OUT NUMBER, p_db_allocated OUT NUMBER, p_db_tablespace_size OUT NUMBER, p_db_tablespace_used OUT NUMBER, p_db_used OUT NUMBER, p_db_free OUT NUMBER, p_local_fs_on_local_disks OUT NUMBER, p_local_fs_on_volumes OUT NUMBER, p_local_fs_used OUT NUMBER, p_local_fs_free OUT NUMBER, p_writeable_nfs_used OUT NUMBER, p_writeable_nfs_free OUT NUMBER, p_total_allocated OUT NUMBER, p_total_unallocated OUT NUMBER, p_total_overhead OUT NUMBER, p_total_used OUT NUMBER, p_total_free OUT NUMBER); -- -- Gets latest collection timestamp of all the relevant snapshots of given -- target. -- FUNCTION GET_COLLECTION_TIMESTAMP(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2) RETURN DATE; -- -- Gets the following types of errors for a host or group -- 1. any storage metric collection errors -- 2. any storage metric mapping issues -- PROCEDURE GET_STORAGE_ERRORS(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_target_timezone_region OUT VARCHAR2, p_collection_timestamp OUT DATE, p_storage_collection_errors OUT SYS_REFCURSOR, p_storage_mapping_issues OUT SYS_REFCURSOR, p_targets_with_missing_snaps OUT SYS_REFCURSOR, p_targets_with_no_collection OUT SYS_REFCURSOR, p_targets_with_coll_disabled OUT SYS_REFCURSOR); -- -- Returns information on storage data availability. -- For a host, it returns -- p_num_of_targets => num of targets, in this case 1. -- p_targets_having_snapshot => 1 if host has storage snapshot, 0 otherwise. -- p_targets_with_coll_defined => 1 if host has storage collection defined, -- 0 otherwise. -- p_targets_with_coll_enabled => 1 if host has storage collection enabled, -- 0 otherwise. -- For a group, it returns -- p_num_of_targets => # of associated host targets for the group. -- p_targets_having_snapshot => # of associated hosts having storage snapshot -- p_targets_with_coll_defined => # of associated hosts with storage snapshot -- collection defined -- p_targets_with_coll_enabled => # of associated hosts with storage snapshot -- collection enabled -- PROCEDURE GET_STORAGE_DATA_AVAIL_INFO(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_num_of_targets OUT NUMBER, p_targets_having_snapshot OUT NUMBER, p_targets_with_coll_defined OUT NUMBER, p_targets_with_coll_enabled OUT NUMBER); -- -- Gets stats related to configuration of the target. Stats are as follows. -- . total database targets deployed across summarized hosts -- . number of database targets with storage data across summarized hosts -- . total ASM targets deployed across summarized hosts -- . number of ASM targets with storage data across summarized hosts -- . Total number of mapping issues related to unmonitored NFS targets -- . Total number of mapping issues other than the unmonitored NFS targets -- . Total storage metric collection errors for host(s). -- . Total storage metric collection errors for associated database target(s). -- . Total storage metric collection errors for associated ASM target(s). -- PROCEDURE GET_STRG_DATA_CFG_AND_ERRORS(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_total_dbs OUT NUMBER, p_summarized_dbs OUT NUMBER, p_total_asm_count OUT NUMBER, p_summarized_asm_count OUT NUMBER, p_host_metric_errors OUT NUMBER, p_db_metric_errors OUT NUMBER, p_asm_metric_errors OUT NUMBER, p_unmonitored_nfs_count OUT NUMBER, p_other_issues_count OUT NUMBER); -- -- Gets timezone region of a target. -- FUNCTION GET_TARGET_TIMEZONE_REGION(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2) RETURN VARCHAR2; -- -- Returns 'Y' if storage history for a group is enabled, 'N' otherwise. -- Returns 'Y' for a host target. -- PROCEDURE GET_HIST_ENABLED_EXISTS_FLAG(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_storage_hist_enabled OUT VARCHAR2, p_hist_data_exists_flag OUT VARCHAR2, p_refresh_job_name OUT MGMT_JOB.job_name%TYPE, p_refresh_job_exec_id OUT MGMT_JOB_EXEC_SUMMARY.execution_id%TYPE, p_refresh_job_status OUT MGMT_JOB_EXEC_SUMMARY.status%TYPE); -- -- Returns refresh job info if it is not yet completed. -- PROCEDURE GET_REFRESH_JOB_INFO(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_refresh_job_name OUT MGMT_JOB.job_name%TYPE, p_refresh_job_exec_id OUT MGMT_JOB_EXEC_SUMMARY.job_id%TYPE, p_refresh_job_status OUT MGMT_JOB_EXEC_SUMMARY.status%TYPE); END storage_ui_common_pkg; / SHOW ERRORS;