Monitoring ASM Instance and Database

Guideline for Shared pool Size in ASM instance-

Increase shared pool size based on the following guidelines:

·         For disk groups using external redundancy: Every 100 GB of space needs 1 MB of extra shared pool plus a fixed amount of 2 MB of shared pool.

·         For disk groups using normal redundancy: Every 50 GB of space needs 1 MB of extra shared pool plus a fixed amount of 4 MB of shared pool.

·         For disk groups using high redundancy: Every 33 GB of space needs 1 MB of extra shared pool plus a fixed amount of 6 MB of shared pool.

How to check database Size? 

To obtain the current database storage size that is either already on ASM or will be stored in ASM:

SELECT d+l+t DB_SPACE

FROM

(SELECT SUM(bytes)/(1024*1024*1024) d FROM v$datafile),

(SELECT SUM(bytes)/(1024*1024*1024) l FROM v$logfile a, v$log b

 WHERE a.group#=b.group#),

(SELECT SUM(bytes)/(1024*1024*1024) t FROM v$tempfile

WHERE status='ONLINE'

 
How to check Disk Group Details?

select group_number, name, total_mb, free_mb, state, type from v$asm_diskgroup;

How to Check ASM Disk Details?

SELECT group_number, disk_number, mount_status, header_status, state, path FROM v$asm_disk

GROUP_NUMBER        DISK_NUMBER          MOUNT_S        HEADER_STATU      STATE               PATH

0                               0                              CLOSED            CANDIDATE           NORMAL     C:\ASMDISKS\_FILE_DISK1

0                              1                              CLOSED            CANDIDATE           NORMAL      C:\ASMDISKS\_FILE_DISK2

0                              2                              CLOSED            CANDIDATE           NORMAL       C:\ASMDISKS\_FILE_DISK3

0                             3                               CLOSED            CANDIDATE           NORMAL        C:\ASMDISKS\_FILE_DISK4

Note:

The value of zero in the GROUP_NUMBER column for all four disks. This indicates that a disk is available but hasn't yet been assigned to a disk group.

Dynamice Performance Views

V$ASM_DISKGROUP

This view provides information about a disk group. In a database instance, this view contains one row for every ASM disk group mounted by the ASM instance.

V$ASM_CLIENT

This view identifies all the client databases using various disk groups. In a Database instance, the view contains one row for the ASM instance if the database has any open ASM files.

V$ASM_DISK

This view contains one row for every disk discovered by the ASM instance. In a database instance, the view will only contain rows for disks in use by that database instance.

V$ASM_FILE

This view contains one row for every ASM file in every disk group mounted by the ASM instance.

V$ASM_TEMPLATE

This view contains one row for every template present in every disk group mounted by the ASM instance.

See Also                                                                                               See Also 

Rename Disk / Delete Disk in ASM                                                        Moving Control Files in ASM