Rem
Rem $Header: storage_common_db_utils_pkgbody.sql 17-may-2005.09:54:35 ajdsouza Exp $
Rem
Rem storage_common_db_utils_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      storage_common_db_utils_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      Utilities used by storage for accessing db/asm/rac target information
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    ajdsouza    04/25/05 - convert to bulk queries
Rem    ajdsouza    11/23/04 - 
Rem    ajdsouza    09/20/04 - ajdsouza_storage_fix1
Rem    ajdsouza    09/07/04 - Created
Rem

Rem SET ECHO ON
Rem SET FEEDBACK 1
Rem SET NUMWIDTH 10
Rem SET LINESIZE 80
Rem SET TRIMSPOOL ON
Rem SET TAB OFF
Rem SET PAGESIZE 100

CREATE OR REPLACE PACKAGE BODY storage_common_db_utils AS

c_db_target_type            CONSTANT mgmt_targets.target_type%TYPE := MGMT_GLOBAL.G_DATABASE_TARGET_TYPE;
c_osm_target_type           CONSTANT mgmt_targets.target_type%TYPE := 'osm_instance';
c_rac_target_type           CONSTANT mgmt_targets.target_type%TYPE := MGMT_GLOBAL.G_RAC_DATABASE_TARGET_TYPE;

c_metric_osm_diskpath       CONSTANT mgmt_metrics.metric_name%TYPE   := 'Disk_Path';
c_osm_diskpath             CONSTANT mgmt_metrics.metric_column%TYPE := 'disk_path';
l_osm_diskpath_guid        MGMT_METRICS.METRIC_GUID%TYPE := NULL;

------------------------------------------------------------
--  get_oracle_db_targets
------------------------------------------------------------
FUNCTION get_oracle_db_targets 
 ( 
   v_host_name mgmt_targets.host_name%TYPE 
 )
 RETURN  MGMT_METRIC_INSTANCE_ARRAY
IS
 l_target_list   MGMT_METRIC_INSTANCE_ARRAY;
BEGIN

  SELECT MGMT_METRIC_INSTANCE
         (
          target_name,
          target_type,
          NULL,
          NULL,
          NULL
         )
  BULK COLLECT INTO l_target_list
  FROM   mgmt_targets
  WHERE  target_type IN 
  (
    STORAGE_COMMON_DB_UTILS.c_osm_target_type,
    STORAGE_COMMON_DB_UTILS.c_db_target_type,
    STORAGE_COMMON_DB_UTILS.c_rac_target_type
  )
  AND   host_name = v_host_name;
 
  RETURN l_target_list;

END get_oracle_db_targets;


------------------------------------------------------------
--  get_os_entities_for_orcl_tgts
------------------------------------------------------------
FUNCTION get_os_entities_for_orcl_tgts
 (
   v_target_name   mgmt_targets.target_name%TYPE,
   v_target_type   mgmt_targets.target_type%TYPE
 )
  RETURN MGMT_STORAGE_STRING_TABLE
IS
  l_target_guid           mgmt_targets.target_guid%TYPE;
  l_db_ecm_snapshotid     mgmt_ecm_snapshot.snapshot_guid%TYPE;
  l_ose_list              MGMT_STORAGE_STRING_TABLE;
BEGIN
 
   -- Rac or DB target 
   IF v_target_type IN 
      (
        STORAGE_COMMON_DB_UTILS.c_db_target_type,
        STORAGE_COMMON_DB_UTILS.c_rac_target_type 
      ) 
   THEN
  
     BEGIN

       SELECT a.ecm_snapshot_id
       INTO   l_db_ecm_snapshotid
       FROM   mgmt$ecm_current_snapshots a,
              mgmt_v_db_datafiles_ecm b
       WHERE  a.target_name = v_target_name
       AND    a.target_type = v_target_type
       AND    a.ecm_snapshot_id = b.ecm_snapshot_id
       AND    ROWNUM = 1;

     EXCEPTION

      WHEN NO_DATA_FOUND THEN
        RETURN NULL;

      WHEN OTHERS THEN
        RAISE;

     END;

     -- Get all the os_storage_entities for db and rac targets 
     SELECT os_storage_entity
     BULK COLLECT INTO l_ose_list
     FROM   mgmt_v_db_datafiles_ecm
     WHERE  ecm_snapshot_id = l_db_ecm_snapshotid
     UNION
     SELECT os_storage_entity
     FROM   mgmt_v_db_redologs_ecm
     WHERE  ecm_snapshot_id = l_db_ecm_snapshotid
     UNION
     SELECT os_storage_entity
     FROM   mgmt_v_db_controlfiles_ecm
     WHERE  ecm_snapshot_id = l_db_ecm_snapshotid;
   
   -- OSM target
   ELSIF v_target_type = STORAGE_COMMON_DB_UTILS.c_osm_target_type THEN
  
     IF STORAGE_COMMON_DB_UTILS.l_osm_diskpath_guid IS NULL THEN
       STORAGE_COMMON_DB_UTILS.l_osm_diskpath_guid        := 
        MGMT_METRIC.GET_METRIC_GUID
        (
          STORAGE_COMMON_DB_UTILS.c_osm_target_type, 
          STORAGE_COMMON_DB_UTILS.c_metric_osm_diskpath, 
          STORAGE_COMMON_DB_UTILS.c_osm_diskpath
        );
     END IF;
     
     l_target_guid := MGMT_TARGET.GET_TARGET_GUID(v_target_name,v_target_type);

     -- Get all the disk paths for the osm target 
     SELECT string_value
     BULK COLLECT INTO l_ose_list
     FROM   mgmt_current_metrics
     WHERE  target_guid = l_target_guid
     AND    metric_guid = STORAGE_COMMON_DB_UTILS.l_osm_diskpath_guid;
   
   END IF;
     
  RETURN l_ose_list;

END get_os_entities_for_orcl_tgts;

BEGIN

  NULL;

END storage_common_db_utils;
/