101 lines
3.6 KiB
SQL
101 lines
3.6 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display ASM Disks
|
|
* Parameter : 1 - DiskGroup Name (Default % , use / as escape character)
|
|
* 2 - FailGroup (Default % , use / as escape character)
|
|
* 3 - DiskName (Default % , use / as escape character)
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 21-Apr-13 Vishal Gupta Created
|
|
*
|
|
*
|
|
*/
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE DISKGROUP_NAME
|
|
UNDEFINE FAILGROUP
|
|
UNDEFINE DISK_NAME
|
|
|
|
DEFINE DISKGROUP_NAME="&&1"
|
|
DEFINE FAILGROUP="&&2"
|
|
DEFINE DISK_NAME="&&3"
|
|
|
|
set term off
|
|
COLUMN _DISKGROUP_NAME NEW_VALUE DISKGROUP_NAME NOPRINT
|
|
COLUMN _FAILGROUP NEW_VALUE FAILGROUP NOPRINT
|
|
COLUMN _DISK_NAME NEW_VALUE DISK_NAME NOPRINT
|
|
|
|
SELECT UPPER(DECODE('&&DISKGROUP_NAME','','%','&&DISKGROUP_NAME')) "_DISKGROUP_NAME"
|
|
, UPPER(DECODE('&&FAILGROUP','','%','&&FAILGROUP')) "_FAILGROUP"
|
|
, UPPER(DECODE('&&DISK_NAME','','%','&&DISK_NAME')) "_DISK_NAME"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
PROMPT *******************************************
|
|
PROMPT A S M D I S K S
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - DiskGroup Name = '&&DISKGROUP_NAME'
|
|
PROMPT * - FailGroup = '&&FAILGROUP'
|
|
PROMPT * - DiskName = '&&DISK_NAME'
|
|
PROMPT *******************************************
|
|
|
|
COLUMN DiskGroup HEAD "DiskGroup" FORMAT A15
|
|
COLUMN failgroup HEAD "FailGroup" FORMAT A10
|
|
COLUMN name HEAD "ASM Disk Name" FORMAT A30
|
|
COLUMN preferred_read HEAD "Pref|Read" FORMAT A4
|
|
COLUMN mount_status HEAD "Mount|Status" FORMAT A8
|
|
COLUMN header_status HEAD "Header|Status" FORMAT A8
|
|
COLUMN mode_status HEAD "Mode|Status" FORMAT A8
|
|
COLUMN repair_timer HEAD "Repair|Timer|(sec)" FORMAT 9,999
|
|
COLUMN library HEAD "Library" FORMAT A7
|
|
COLUMN label HEAD "Label" FORMAT A30
|
|
COLUMN path HEAD "Path" FORMAT A50
|
|
COLUMN total_gb HEAD "Total|(GB)" FORMAT 9,999
|
|
COLUMN free_gb HEAD "Free|(GB)" FORMAT 9,999
|
|
COLUMN free_gb HEAD "Free|(GB)" FORMAT 9,999
|
|
COLUMN hot_used_gb HEAD "Hot|Used|(GB)" FORMAT 9,999
|
|
COLUMN cold_used_gb HEAD "Cold|Used|(GB)" FORMAT 9,999
|
|
|
|
|
|
SELECT dg.NAME DiskGroup
|
|
, d.failgroup
|
|
, d.name
|
|
, d.preferred_read
|
|
, d.voting_file
|
|
, d.mount_status
|
|
, d.header_status
|
|
, d.mode_status
|
|
, d.state
|
|
, d.repair_timer
|
|
, d.library
|
|
--, d.label
|
|
, d.total_mb /1024 total_gb
|
|
, d.free_mb/1024 free_gb
|
|
, d.hot_used_mb/1024 hot_used_gb
|
|
, d.cold_used_mb/1024 cold_used_gb
|
|
--, d.path
|
|
-- V$ASM_DISK_STAT does not perform disk discovery like V$ASM_DISK view.
|
|
FROM v$asm_disk_stat d
|
|
, v$asm_diskgroup_stat dg
|
|
WHERE d.group_number = dg.group_number
|
|
AND dg.name LIKE '&&DISKGROUP_NAME'
|
|
AND NVL(d.failgroup,'%') LIKE '&&FAILGROUP'
|
|
AND NVL(d.name,'%') LIKE '&&DISK_NAME'
|
|
ORDER BY dg.NAME
|
|
, d.FAILGROUP
|
|
, d.name
|
|
;
|
|
|
|
|
|
@@footer
|