Rem
Rem $Header: storage_common_views.sql 08-dec-2005.11:54:38 ajdsouza Exp $
Rem
Rem storage_report_views.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.
Rem
Rem NAME
Rem common_views.sql -
Rem views common to backend and ui code for the storage module.
Rem
Rem DESCRIPTION
Rem Views for storage reporting - only those views that
Rem are common to the storage backend code and storage UI
Rem should exist in this file.
Rem
Rem NOTES
Rem
Rem MODIFIED (MM/DD/YY)
Rem ajdsouza 12/08/05 - Backport ajdsouza_stg_bug4609392_4651120_fix
Rem from main
Rem ajdsouza 11/22/05 - fixed bug# 4609392 by joiing to
Rem view mgmt$ecm_current_snapshots
Rem instead of mgmt_ecm_gen_snapshots
Rem sreddy 07/23/05 - Modify mgmt_v_storage_report_data to have
Rem all snapshots [not just current snapshots]
Rem gsbhatia 06/26/05 - New repmgr header impl
Rem ajdsouza 05/02/05 - added snapshot_type to predicate to ensure
Rem that index MGMT_ECM_SNAP_IDX(2) can get used
Rem rmenon 03/10/05 - removing mgmt_v_db_cntrlfiles_size_ecm
Rem THe underlying metric is going to be
Rem removed. Database size now just shows
Rem tablespace size.
Rem ajdsouza 03/03/05 - Modified mgmt$storage_report_disk, NVL a3 with name
Rem gsbhatia 02/13/05 - updating repmgr header
Rem gsbhatia 02/09/05 - updating repmgr header
Rem gsbhatia 02/07/05 - updating repmgr header
Rem rmenon 02/04/05 - added is_virtual_layer column to mgmt_v_storage_report_data
Rem ajdsouza 01/31/05 - remove NVL fro global_unique_id in mgmt_v_storage_report_data
Rem ktlaw 01/11/05 - add repmgr header
Rem ajdsouza 10/24/04 - Changed mgmt$storage_report_issues to have count
Rem rmenon 09/28/04 - added target_guid, host name columns
Rem to mgmt_v_storage_report_data
Rem ajdsouza 08/19/04 - moved base private views
Rem rmenon 08/16/04 - formatting cleanup and some additional
Rem comments.
Rem ajdsouza 08/10/04 - ajdsouza_ecm_processing
Rem ajdsouza 08/09/04 -
Rem ajdsouza 07/30/04 - ajdsouza_fix_storage_metrics
Rem ajdsouza 07/28/04 - Moved to new storage component in
Rem common functional unit
Rem ajdsouza 06/25/04 - storage reporting sources
Rem ajdsouza 05/12/04 - Created
Rem
--------------------------------------------------------
-- Dummy db views in emcore. These views are recreated
-- in the file storage/storage_database_views.sql
-- in the emdb vob. This way we can "compile"
-- the sql files in storage module that depend on
-- core and db files.
--------------------------------------------------------
---------------------------------------------------------
-- storage private views begin - these views are ------
-- private to sysman and start with mgmt_v. ------
---------------------------------------------------------
---------------------------------------------------------
-- NAME: mgmt_v_db_datafiles_ecm
--
-- PURPOSE:
-- dummy view that represents the table mgmt_db_datafiles_ecm.
--
-- IMPLMENTATION NOTES:
---------------------------------------------------------
CREATE OR REPLACE VIEW mgmt_v_db_datafiles_ecm
(
ecm_snapshot_id,
file_name,
file_size,
os_storage_entity
)
AS
SELECT
NULL,
NULL,
NULL,
NULL
FROM dual
WHERE 1 = 2
/
---------------------------------------------------------
-- NAME: mgmt_v_db_redologs_ecm
--
-- PURPOSE:
-- dummy view that represents the table mgmt_db_redologs_ecm.
--
-- IMPLMENTATION NOTES:
---------------------------------------------------------
CREATE OR REPLACE VIEW mgmt_v_db_redologs_ecm
(
ecm_snapshot_id,
file_name,
logsize,
os_storage_entity
)
AS
SELECT
NULL,
NULL,
NULL,
NULL
FROM dual
WHERE 1 = 2
/
---------------------------------------------------------
-- NAME: mgmt_v_db_controlfiles_ecm
--
-- PURPOSE:
-- dummy view that represents the table mgmt_db_controlfiles_ecm.
--
-- IMPLMENTATION NOTES:
---------------------------------------------------------
CREATE OR REPLACE VIEW mgmt_v_db_controlfiles_ecm
(
ecm_snapshot_id,
file_name,
os_storage_entity
)
AS
SELECT
NULL,
NULL,
NULL
FROM dual
WHERE 1 = 2
/
---------------------------------------------------------
-- NAME: mgmt_v_db_dbninstanceinfo_ecm
--
-- PURPOSE:
-- dummy view that represents the table
-- mgmt_db_dbninstanceinfo_ecm.
--
-- IMPLMENTATION NOTES:
---------------------------------------------------------
CREATE OR REPLACE VIEW mgmt_v_db_dbninstanceinfo_ecm
(
ecm_snapshot_id
)
AS
SELECT NULL
FROM dual
WHERE 1 = 2
/
---------------------------------------------------------
-- NAME: mgmt_v_db_tablespaces_ecm
--
-- PURPOSE:
-- dummy view that represents the table
-- mgmt_db_tablespaces_ecm.
--
-- IMPLMENTATION NOTES:
---------------------------------------------------------
CREATE OR REPLACE VIEW mgmt_v_db_tablespaces_ecm
(
ecm_snapshot_id,
tablespace_size,
tablespace_used_size
)
AS
SELECT
NULL,
NULL,
NULL
FROM dual
WHERE 1 = 2
/
---------------------------------------------------------
-- NAME: mgmt_v_storage_report_data
--
-- PURPOSE:
-- This view gives meaningful names to the
-- generic columns a1, a2 etc. Other views are
-- built on top of this view (instead of the
-- table mgmt_storage_reports_data.) Thus any changes
-- to the meaning of these generic columns is
-- isolated.
--
-- IMPLMENTATION NOTES:
---------------------------------------------------------
CREATE OR REPLACE VIEW mgmt_v_storage_report_data
(
ecm_snapshot_id,
target_name,
target_type,
target_guid,
key_value,
global_unique_id,
name,
storage_layer,
em_query_flag,
is_top_layer,
is_virtual_layer,
is_bottom_layer,
is_intermediate_layer,
is_container_layer,
is_spare,
is_allocated,
entity_type,
rawsizeb,
sizeb,
usedb,
freeb,
local_fs_type,
local_fs,
local_fs_mountpoint,
nfs_vendor,
nfs_server,
nfs,
nfs_mountpoint,
nfs_server_ip_address,
nfs_mount_privilege,
nfs_server_mac_address,
volume_manager_vendor,
volume_manager_product,
volume_manager_disk_group,
volume_manager_used_path,
volume_manager_configuration,
disk_used_path,
disk_vendor,
disk_product,
file_type
)
AS
SELECT
a.ecm_snapshot_id,
b.target_name,
b.target_type,
b.target_guid,
a.key_value,
a.global_unique_id,
a.name,
a.storage_layer,
a.em_query_flag,
DECODE(INSTRB(a.em_query_flag,'TOP'),0,'N','Y'),
DECODE(INSTRB(a.em_query_flag,'VIRTUAL'),0,'N','Y'),
DECODE(INSTRB(a.em_query_flag,'BOTTOM'),0,'N','Y'),
DECODE(INSTRB(a.em_query_flag,'INTERMEDIATE'),0,'N','Y'),
DECODE(INSTRB(a.em_query_flag,'CONTAINER'),0,'N','Y'),
DECODE(INSTRB(a.em_query_flag,'SPARE'),0,'N','Y'),
DECODE(INSTRB(a.em_query_flag,'UNALLOCATED'),0,'Y','N'),
SUBSTR(a.entity_type,1,32),
a.rawsizeb,
a.sizeb,
a.usedb,
a.freeb,
DECODE( a.storage_layer, 'LOCAL_FILESYSTEM', a.a1, NULL ) ,
DECODE( a.storage_layer, 'LOCAL_FILESYSTEM', a.a2, NULL ) ,
DECODE( a.storage_layer, 'LOCAL_FILESYSTEM', a.a3, NULL ) ,
DECODE( a.storage_layer, 'NFS', a.a1, NULL ) ,
DECODE( a.storage_layer, 'NFS', a.a2, NULL ) ,
DECODE( a.storage_layer, 'NFS', a.a3, NULL ) ,
DECODE( a.storage_layer, 'NFS', a.a4, NULL ) ,
DECODE( a.storage_layer, 'NFS', a.a5, NULL ) ,
DECODE( a.storage_layer, 'NFS', a.a6, NULL ) ,
DECODE( a.storage_layer, 'NFS', a.a7, NULL ) ,
DECODE( a.storage_layer, 'VOLUME_MANAGER', a.a1, 50, NULL ) ,
DECODE( a.storage_layer, 'VOLUME_MANAGER', a.a2, NULL ) ,
DECODE( a.storage_layer, 'VOLUME_MANAGER', a.a4, NULL ) ,
DECODE( a.storage_layer, 'VOLUME_MANAGER', a.a3, NULL ) ,
DECODE( a.storage_layer, 'VOLUME_MANAGER', a.a5, NULL ) ,
DECODE( a.storage_layer, 'OS_DISK', a.a3, NULL ) ,
DECODE( a.storage_layer, 'OS_DISK', a.a1, NULL ) ,
DECODE( a.storage_layer, 'OS_DISK', a.a2, NULL ) ,
DECODE( a.storage_layer, 'OS_DISK', a.a4,
'VOLUME_MANAGER', a.a6, NULL )
FROM mgmt_storage_report_data a,
mgmt$ecm_current_snapshots b
WHERE a.ecm_snapshot_id = b.ecm_snapshot_id
AND b.snapshot_type = 'host_storage'
/
-----------------------------------------------------
-- mgmt_v_storage_report_unique
-----------------------------------------------------
CREATE OR REPLACE VIEW mgmt_v_storage_report_unique
(
ecm_snapshot_id,
key_value,
global_unique_id,
name,
storage_layer,
em_query_flag,
is_top_layer,
is_virtual_layer,
is_bottom_layer,
is_intermediate_layer,
is_container_layer,
is_spare,
is_allocated,
entity_type,
rawsizeb,
sizeb,
usedb,
freeb,
local_fs_type,
local_fs,
local_fs_mountpoint,
nfs_vendor,
nfs_server,
nfs,
nfs_mountpoint,
nfs_server_ip_address,
nfs_mount_privilege,
nfs_server_mac_address,
volume_manager_vendor,
volume_manager_product,
volume_manager_disk_group,
volume_manager_used_path,
volume_manager_configuration,
disk_used_path,
disk_vendor,
disk_product,
file_type
)
AS
SELECT ecm_snapshot_id,
key_value,
global_unique_id,
name,
storage_layer,
em_query_flag,
is_top_layer,
is_virtual_layer,
is_bottom_layer,
is_intermediate_layer,
is_container_layer,
is_spare,
is_allocated,
entity_type,
rawsizeb,
sizeb,
usedb,
freeb,
local_fs_type,
local_fs,
local_fs_mountpoint,
nfs_vendor,
nfs_server,
nfs,
nfs_mountpoint,
nfs_server_ip_address,
nfs_mount_privilege,
nfs_server_mac_address,
volume_manager_vendor,
volume_manager_product,
volume_manager_disk_group,
volume_manager_used_path,
volume_manager_configuration,
disk_used_path,
disk_vendor,
disk_product,
file_type
FROM
(
SELECT ecm_snapshot_id,
target_name,
target_type,
key_value,
global_unique_id,
name,
storage_layer,
em_query_flag,
is_top_layer,
is_virtual_layer,
is_bottom_layer,
is_intermediate_layer,
is_container_layer,
is_spare,
is_allocated,
entity_type,
rawsizeb,
sizeb,
usedb,
freeb,
local_fs_type,
local_fs,
local_fs_mountpoint,
nfs_vendor,
nfs_server,
nfs,
nfs_mountpoint,
nfs_server_ip_address,
nfs_mount_privilege,
nfs_server_mac_address,
volume_manager_vendor,
volume_manager_product,
volume_manager_disk_group,
volume_manager_used_path,
volume_manager_configuration,
disk_used_path,
disk_vendor,
disk_product,
file_type,
ROW_NUMBER() OVER ( PARTITION BY global_unique_id ORDER BY target_type, target_name ) AS global_unique_id_rownumber
FROM mgmt_v_storage_report_data data
WHERE target_name = SYS_CONTEXT('storage_context', 'host_name' )
)
WHERE global_unique_id_rownumber = 1
/
-----------------------------------------------
-- mgmt_v_sl_size
----------------------------------------------
CREATE OR REPLACE VIEW mgmt_v_sl_size
(
storage_layer,
entity_type,
name,
top_allocatedb,
top_unallocatedb,
total_freeb,
bottom_sizeb
)
AS
SELECT DECODE(a.storage_layer,'NFS',
DECODE(a.nfs_mount_privilege,'WRITE',
'NFS-WRITE','NFS-READ'),
a.storage_layer),
a.entity_type,
a.name,
DECODE(a.is_top_layer||'-'||a.is_allocated,'Y-Y',
a.sizeb,0),
DECODE(a.is_top_layer||'-'||a.is_allocated,'Y-N',
a.sizeb,0),
a.freeb,
DECODE(a.is_bottom_layer,'Y',
a.sizeb,0)
FROM mgmt_v_storage_report_unique a
/
-----------------------------------------------
-- mgmt_v_sl_size_summary
----------------------------------------------
CREATE OR REPLACE VIEW mgmt_v_sl_size_summary
(
storage_layer,
top_allocatedb,
top_unallocatedb,
total_freeb,
bottom_sizeb
)
AS
SELECT storage_layer,
SUM(top_allocatedb),
SUM(top_unallocatedb),
SUM(total_freeb),
SUM(bottom_sizeb)
FROM mgmt_v_sl_size a
GROUP BY
storage_layer
/
-----------------------------------------------
-- mgmt_v_sl_sz_sm_layers
----------------------------------------------
CREATE OR REPLACE VIEW mgmt_v_sl_sz_sm_layers
AS
SELECT SUM(DECODE( a.storage_layer, 'OS_DISK',
a.bottom_sizeb, 0)) total_disks_sizeb,
SUM(DECODE( a.storage_layer, 'VOLUME_MANAGER',
a.bottom_sizeb, 0)) total_volumes_sizeb,
SUM(DECODE( a.storage_layer, 'NFS-WRITE',
a.bottom_sizeb, 0)) total_writeable_nfs_sizeb,
SUM(DECODE( a.storage_layer, 'LOCAL_FILESYSTEM',
a.bottom_sizeb, 0)) total_fs_sizeb,
SUM(DECODE( a.storage_layer, 'OS_DISK',
a.total_freeb, 0)) disks_total_freeb,
SUM(DECODE( a.storage_layer, 'VOLUME_MANAGER',
a.total_freeb, 0)) volumes_total_freeb,
SUM(DECODE( a.storage_layer, 'NFS-WRITE',
a.total_freeb, 0)) nfswriteable_total_freeb,
SUM(DECODE( a.storage_layer, 'LOCAL_FILESYSTEM',
a.total_freeb, 0)) fs_total_freeb,
SUM(DECODE( a.storage_layer, 'VOLUME_MANAGER',
a.top_allocatedb, 0)) volumes_total_allocated,
SUM(DECODE( a.storage_layer, 'OS_DISKS',
a.top_allocatedb, 0)) disks_total_allocated,
SUM(DECODE( a.storage_layer, 'VOLUME_MANAGER',
a.top_unallocatedb, 0)) volumes_total_unallocated,
SUM(DECODE( a.storage_layer, 'OS_DISKS',
a.top_unallocatedb, 0)) disks_total_unallocated
FROM mgmt_v_sl_size_summary a
/
-----------------------------------------------
-- mgmt_v_sl_src
----------------------------------------------
CREATE OR REPLACE VIEW mgmt_v_sl_src
AS
SELECT a.parent_storage_layer AS parent_storage_layer,
SUM( DECODE( a.child_storage_layer,'OS_DISK',
a.parent_sizeb, 0) ) AS on_local_disks,
SUM( DECODE( a.child_storage_layer,'VOLUME_MANAGER',
a.parent_sizeb, 0) ) on_volumes,
SUM( DECODE( a.child_storage_layer,'NFS',
a.parent_sizeb, 0) ) AS on_nfs_read,
SUM( DECODE( a.child_storage_layer,'LOCAL_FILESYSTEM',
a.parent_sizeb, 0) ) AS on_local_fs
FROM
(
SELECT p.storage_layer AS parent_storage_layer,
c.storage_layer AS child_storage_layer,
p.global_unique_id,
p.sizeb AS parent_sizeb,
p.usedb AS parent_usedb,
c.sizeb AS child_sizeb,
c.freeb AS child_usedb,
ROW_NUMBER() OVER ( PARTITION BY p.storage_layer, p.global_unique_id ORDER BY p.sizeb, c.sizeb ) AS global_unique_id_rownumber
FROM mgmt_v_storage_report_data p,
mgmt_storage_report_keys k,
mgmt_v_storage_report_data c,
mgmt$ecm_current_snapshots s
WHERE s.ecm_snapshot_id = k.ecm_snapshot_id
AND s.snapshot_type = 'host_storage'
AND s.target_name = p.target_name
AND s.target_type = p.target_type
AND s.target_name = c.target_name
AND s.target_type = c.target_type
AND s.target_type = 'host'
AND s.target_name = SYS_CONTEXT('storage_context', 'host_name' )
AND k.key_value = c.key_value
AND k.parent_key_value = p.key_value
) a
WHERE a.parent_storage_layer != a.child_storage_layer
AND a.global_unique_id_rownumber = 1
GROUP BY
a.parent_storage_layer,
a.child_storage_layer
/
-----------------------------------------------
-- PUBLIC VIEWS
--
-- these views are published to the end user
-----------------------------------------------
-----------------------------------------------
-- mgmt$storage_report_localfs
----------------------------------------------
CREATE OR REPLACE VIEW mgmt$storage_report_localfs
(
target_name,
target_type,
filesystem_type,
filesystem,
mountpoint,
sizeb,
usedb,
freeb
)
AS
SELECT
b.target_name,
b.target_type,
a1,
a2,
a3,
sizeb,
usedb,
freeb
FROM mgmt_storage_report_data a,
mgmt$ecm_current_snapshots b
WHERE storage_layer = 'LOCAL_FILESYSTEM'
AND entity_type = 'Mountpoint'
AND a.ecm_snapshot_id = b.ecm_snapshot_id
AND b.snapshot_type = 'host_storage'
/
----------------------------------------------
-- mgmt$storage_report_nfs
----------------------------------------------
CREATE OR REPLACE VIEW mgmt$storage_report_nfs
(
target_name,
target_type,
filesystem,
mountpoint,
sizeb,
usedb,
freeb,
nfs_server,
nfs_server_ip_address,
nfs_vendor,
mount_privilege
)
AS
SELECT b.target_name,
b.target_type,
a3,
a4,
sizeb,
usedb,
freeb,
a2,
a5,
a1,
a6
FROM mgmt_storage_report_data a,
mgmt$ecm_current_snapshots b
WHERE storage_layer = 'NFS'
AND entity_type = 'Mountpoint'
AND a.ecm_snapshot_id = b.ecm_snapshot_id
AND b.snapshot_type = 'host_storage'
/
----------------------------------------------
-- mgmt$storage_report_volume
----------------------------------------------
CREATE OR REPLACE VIEW mgmt$storage_report_volume
(
target_name,
target_type,
vendor,
product,
type,
disk_group,
name,
used_path,
file_type,
rawsizeb,
sizeb,
usedb,
freeb,
configuration
)
AS
SELECT
b.target_name,
b.target_type,
a1,
a2,
entity_type,
a4,
name,
a3,
a4,
rawsizeb,
sizeb,
usedb,
freeb,
a5
FROM mgmt_storage_report_data a,
mgmt$ecm_current_snapshots b
WHERE storage_layer = 'VOLUME_MANAGER'
AND a.ecm_snapshot_id = b.ecm_snapshot_id
AND b.snapshot_type = 'host_storage'
/
----------------------------------------------
-- mgmt$storage_report_disk
----------------------------------------------
CREATE OR REPLACE VIEW mgmt$storage_report_disk
(
target_name,
target_type,
entity_type,
used_path,
file_type,
sizeb,
usedb,
freeb,
vendor,
product
)
AS
SELECT
b.target_name,
b.target_type,
entity_type,
NVL(a3,name),
a4,
sizeb,
usedb,
freeb,
a1,
a2
FROM mgmt_storage_report_data a,
mgmt$ecm_current_snapshots b
WHERE storage_layer = 'OS_DISK'
AND a.ecm_snapshot_id = b.ecm_snapshot_id
AND b.snapshot_type = 'host_storage'
/
----------------------------------------------
-- mgmt$storage_report_data
----------------------------------------------
CREATE OR REPLACE VIEW mgmt$storage_report_data
(
target_name,
target_type,
key_value,
global_unique_id,
name,
storage_layer,
entity_type,
rawsizeb,
sizeb,
usedb,
freeb
)
AS
SELECT
b.target_name,
b.target_type,
a.key_value,
a.global_unique_id,
a.name,
a.storage_layer,
a.entity_type,
a.rawsizeb,
a.sizeb,
a.usedb,
a.freeb
FROM mgmt_storage_report_data a,
mgmt$ecm_current_snapshots b
WHERE a.ecm_snapshot_id = b.ecm_snapshot_id
AND b.snapshot_type = 'host_storage'
/
----------------------------------------------
-- mgmt$storage_report_keys
----------------------------------------------
CREATE OR REPLACE VIEW mgmt$storage_report_keys
(
target_name,
target_type,
key_value,
parent_key_value
)
AS
SELECT
b.target_name,
b.target_type,
a.key_value,
a.parent_key_value
FROM mgmt_storage_report_keys a,
mgmt$ecm_current_snapshots b
WHERE a.ecm_snapshot_id = b.ecm_snapshot_id
AND b.snapshot_type = 'host_storage'
/
----------------------------------------------
-- mgmt$storage_report_issues
----------------------------------------------
CREATE OR REPLACE VIEW mgmt$storage_report_issues
(
target_name,
target_type,
type,
message_count
)
AS
SELECT
b.target_name,
b.target_type,
a.type,
COUNT(*)
FROM mgmt_storage_report_issues a,
mgmt$ecm_current_snapshots b
WHERE a.ecm_snapshot_id = b.ecm_snapshot_id
AND b.snapshot_type = 'host_storage'
GROUP BY
b.target_name,
b.target_type,
a.type
/
----------------------------------------------
-- mgmt$storage_report_paths
----------------------------------------------
CREATE OR REPLACE VIEW mgmt$storage_report_paths
(
target_name,
target_type,
key_value,
name,
path,
file_type,
storage_layer,
entity_type
)
AS
SELECT b.target_name,
b.target_type,
a.key_value,
c.name,
a.value,
a.file_type,
c.storage_layer,
c.entity_type
FROM mgmt_storage_report_alias a,
mgmt$ecm_current_snapshots b,
mgmt_storage_report_data c
WHERE a.ecm_snapshot_id = b.ecm_snapshot_id
AND c.ecm_snapshot_id = b.ecm_snapshot_id
AND c.key_value = a.key_value
AND b.snapshot_type = 'host_storage'
/