asmd.sql

SET LINES 1000 FEED OFF

COL PATH      FORMAT A30
COL LABEL     FORMAT A20

COL GROUP_NUMBER FORMAT 99 HEAD "G#"
COL VOLUME_NUMBER FORMAT 99 HEAD "V#"
COL NUM_VOL FORMAT 99 HEAD "V#"
COL DISK_NUMBER  FORMAT 99 HEAD "D#"
COL FILE_NUMBER  FORMAT 999 HEAD "F#"

COL FS_NAME       FORMAT A30
COL MOUNTPATH     FORMAT A30
COL VOLUME_DEVICE FORMAT A30

COL FAILGROUP        FORMAT A14 HEAD FGROUP
COL COMPATIBILITY    FORMAT A12
COL DATABASE_COMPATIBILITY    FORMAT A12

COL FILE_NAME    FORMAT A34
COL FILE_TYPE    FORMAT A20 
COL GROUP_NAME   FORMAT A17
COL DISK_NAME    FORMAT A28 HEAD DISK_NAME
COL LABEL_NAME    FORMAT A28 HEAD LABEL_NAME
COL VOLUME_NAME  FORMAT A20 HEAD VOLUME_NAME
COL VOTING       FORMAT A6

COL INSTANCE     FORMAT A12
COL DB_CLIENT    FORMAT A12
COL SOFTWARE     FORMAT A12
COL COMPATIBLE   FORMAT A12 

COL COMPATIBLE_ASM   FORMAT A11 HEAD "COMPATIBLE|ASM"
COL COMPATIBLE_RDBMS FORMAT A11 HEAD "COMPATIBLE|RDBMS"
COL COMPATIBLE_ADVM  FORMAT A11 HEAD "COMPATIBLE|ADVM"
COL REPAIR_TIME      FORMAT A7 HEAD "REPAIR|TIME"

PROMPT
PROMPT ===============> ASM DISKGROUPS

-- sql original
SELECT
  GROUP_NUMBER                   
 ,NAME GROUP_NAME
 ,STATE                          
 ,TYPE                           
 ,ROUND(TOTAL_MB/1024) TOTAL_GB
 ,ROUND(FREE_MB/1024) FREE_GB
 ,ROUND(COLD_USED_MB/1024) COLD_USED_GB
 ,ROUND(USABLE_FILE_MB/1024) USABLE_GB
 ,SECTOR_SIZE                    
 ,BLOCK_SIZE                     
 ,ALLOCATION_UNIT_SIZE           
 ,REQUIRED_MIRROR_FREE_MB        
 ,OFFLINE_DISKS                  
 ,COMPATIBILITY                  
 ,DATABASE_COMPATIBILITY         
 ,VOTING_FILES                   
FROM V$ASM_DISKGROUP
ORDER BY 1
.

-- nova visão - flavio
SELECT
  D.GROUP_NUMBER                   
 ,D.NAME GROUP_NAME
 ,CASE D.STATE WHEN 'CONNECTED' THEN 'MOUNTED' ELSE D.STATE END STATE
 ,D.TYPE                           
 ,ROUND(D.TOTAL_MB/1024) OS_TOTAL_GB
 ,ROUND(D.TOTAL_MB/1024/(CASE TYPE WHEN 'EXTERN' THEN 1 WHEN 'NORMAL' THEN 2 ELSE 3 END)) TOTAL_GB
 ,ROUND(D.COLD_USED_MB/1024/(CASE TYPE WHEN 'EXTERN' THEN 1 WHEN 'NORMAL' THEN 2 ELSE 3 END)) USED_GB
 ,ROUND(D.FREE_MB/1024/(CASE TYPE WHEN 'EXTERN' THEN 1 WHEN 'NORMAL' THEN 2 ELSE 3 END)) FREE_GB
 ,ROUND(D.REQUIRED_MIRROR_FREE_MB/1024/(CASE TYPE WHEN 'EXTERN' THEN 1 WHEN 'NORMAL' THEN 2 ELSE 3 END)) MIRROR_GB
 ,ROUND(D.USABLE_FILE_MB/1024) USABLE_GB
 ,(SELECT VALUE FROM V$ASM_ATTRIBUTE A WHERE D.GROUP_NUMBER = A.GROUP_NUMBER AND NAME =  'disk_repair_time' ) REPAIR_TIME
 ,(SELECT VALUE FROM V$ASM_ATTRIBUTE A WHERE D.GROUP_NUMBER = A.GROUP_NUMBER AND NAME =  'compatible.asm'   ) COMPATIBLE_ASM
 ,(SELECT VALUE FROM V$ASM_ATTRIBUTE A WHERE D.GROUP_NUMBER = A.GROUP_NUMBER AND NAME =  'compatible.rdbms' ) COMPATIBLE_RDBMS
 ,(SELECT VALUE FROM V$ASM_ATTRIBUTE A WHERE D.GROUP_NUMBER = A.GROUP_NUMBER AND NAME =  'compatible.advm'  ) COMPATIBLE_ADVM
FROM V$ASM_DISKGROUP D
ORDER BY 1
/


PROMPT
PROMPT ===============> ASM VOLUMES

SELECT 
 GROUP_NUMBER                                   
,VOLUME_NUMBER
,VOLUME_NAME                                    
,ROUND(SIZE_MB/1024) SIZE_GB
,VOLUME_DEVICE                                  
,MOUNTPATH                                      
,STATE                                          
,REDUNDANCY                                     
,STRIPE_COLUMNS                                 
,STRIPE_WIDTH_K                                 
,COMPOUND_INDEX                                 
,FILE_NUMBER                                    
,INCARNATION                                    
,DRL_FILE_NUMBER                                
,RESIZE_UNIT_MB                                 
,USAGE                                          
FROM V$ASM_VOLUME
/

PROMPT
PROMPT ===============> ASM FILESYSTEMS

SELECT 
  NUM_VOL                                                        
 ,FS_NAME                                                        
 ,AVAILABLE_TIME                                                 
 ,BLOCK_SIZE                                                     
 ,STATE                                                          
 ,CORRUPT                                                        
 ,ROUND(TOTAL_SIZE/1024) SIZE_GB
 ,ROUND(TOTAL_FREE/1024) FREE_GB
 ,TOTAL_SNAP_SPACE_USAGE                                         
FROM V$ASM_FILESYSTEM
/

PROMPT
PROMPT ===============> ASM CLIENTS

SELECT 
  dg.name GROUP_NAME
 ,SUBSTR(c.instance_name,1,12) AS instance
 ,rpad(c.db_name,12,' ')  DB_CLIENT
 ,SUBSTR(c.SOFTWARE_VERSION,1,12) AS software
 ,SUBSTR(c.COMPATIBLE_VERSION,1,12) AS compatible 
FROM V$ASM_DISKGROUP dg
JOIN V$ASM_CLIENT c ON (dg.group_number = c.group_number)
/

PROMPT 
PROMPT ===============> ASM DISKS
SELECT
  D.GROUP_NUMBER 
 ,D.HEADER_STATUS ST_HEADER
 ,( SELECT G.NAME FROM V$ASM_DISKGROUP G WHERE G.GROUP_NUMBER = D.GROUP_NUMBER ) GROUP_NAME
 ,D.DISK_NUMBER  
 ,D.NAME DISK_NAME
 --,D.LABEL LABEL_NAME
 ,D.PATH
 ,D.FAILGROUP
 ,ROUND(D.OS_MB/1024) OS_GB
 ,ROUND(D.FREE_MB/1024) USABLE_GB
 ,D.MODE_STATUS ST_MODE
 ,D.FAILGROUP_TYPE
 ,RPAD(CASE WHEN D.VOTING_FILE = 'Y' THEN 'YES' ELSE 'no' END,6, ' ' ) VOTING
 ,D.STATE
 ,D.READS
 ,D.WRITES
 ,D.READ_TIME
 ,D.WRITE_TIME
 ,D.SECTOR_SIZE
 ,D.REDUNDANCY
 ,D.CREATE_DATE
 ,D.MOUNT_DATE
FROM V$ASM_DISK D
ORDER BY GROUP_NUMBER, DISK_NUMBER
/


PROMPT 
PROMPT ===============> ASM FILES
SELECT
  A.GROUP_NUMBER 
 ,( SELECT G.NAME FROM V$ASM_DISKGROUP G WHERE G.GROUP_NUMBER = A.GROUP_NUMBER ) GROUP_NAME
 ,A.FILE_NUMBER
 ,F.TYPE FILE_TYPE
 ,A.NAME FILE_NAME
 ,ROUND(F.BYTES/1024/1024) SIZE_MB
 ,ROUND(F.SPACE/1024/1024) SPACE_MB
 ,F.PERMISSIONS
 ,F.BLOCK_SIZE 
 ,F.BLOCKS 
 ,F.REDUNDANCY 
 ,F.STRIPED
 ,F.CREATION_DATE
 ,F.MODIFICATION_DATE
 ,F.PRIMARY_REGION 
 ,F.MIRROR_REGION
 ,A.ALIAS_INDEX
 ,A.ALIAS_INCARNATION
 ,A.SYSTEM_CREATED
 ,A.ALIAS_DIRECTORY
 ,A.FILE_INCARNATION 
FROM V$ASM_FILE F
JOIN V$ASM_ALIAS A 
ON ( A.GROUP_NUMBER = F.GROUP_NUMBER AND A.FILE_NUMBER = F.FILE_NUMBER AND A.FILE_INCARNATION = F.INCARNATION )
WHERE A.SYSTEM_CREATED = 'Y'
ORDER BY 1, 3
.

PROMPT
PROMPT ===============> ASM OPERATIONS

SELECT * FROM GV$ASM_OPERATION;

PROMPT



SET LINES 300 PAGES 300
COL PATH FORMAT A30
COL LABEL FORMAT A30
SELECT GROUP_NUMBER, NAME, STATE FROM V$ASM_DISKGROUP
ORDER BY GROUP_NUMBER
.

SET LINES 300 PAGES 300
COL LABEL_PATH FORMAT A40
SELECT NVl(LABEL, 'PATH:' || PATH ) LABEL_PATH, CREATE_DATE, MOUNT_DATE, DISK_NUMBER, MOUNT_STATUS
, HEADER_STATUS, MODE_STATUS, STATE, FAILGROUP_TYPE
FROM V$ASM_DISK
--WHERE GROUP_NUMBER = 0
ORDER BY GROUP_NUMBER, HEADER_STATUS
.