1 0 CACHED NORMAL UNKNOWN 2047 1269 CONTROLLER1 VOL1 Result will show Disk VOL4 and VOL5 is Missing.
Add Disk physically, create partition, and scan ASM disk and list ASM disk. You will show delete disk (VOL4 and VOL5)
Again execute bellow Query:
Select GROUP_NUMBER, DISK_NUMBER, MOUNT_STATUS, STATE, REDUNDANCY, TOTAL_MB, FREE_MB, FAILGROUP, NAME from v$asm_disk;
GROUP DISK MOUNT_S STATE REDUNDA TOTAL_MB FREE_MB FAILGROUP NAME
0 0 CLOSED NORMAL UNKNOWN 2047 0 0 1 CLOSED NORMAL UNKNOWN 3067 0 1 4 MISSING HUNG UNKNOWN 0 0 VOL5 1 3 MISSING HUNG UNKNOWN 0 0 VOL4
1 0 CACHED NORMAL UNKNOWN 2047 1269 CONTROLLER1 VOL1
Now delete the ASM Disk, scan ASM Disk and list disk (VOL4 and VOL5) /etc/init.d/oracleasm deletedisk VOL4
/etc/init.d/oracleasm deletedisk VOL5
The list ASM disk will not show Disk (VOL4 and VOL5)
Create ASM Disk, scan and list ASM Disk with Different Disk name. /etc/init.d/oracleasm createdisk VOL 4NEW /dev/sdc1
/etc/init.d/oracleasm createdisk VOL 5NEW /dev/sdd1
The list ASM Disk will show Disk (VOL4NEW and VOL5NEW) Now we add Newelly added ASM Disk to DISKGROUP.
ALTER DISKGROUP <DG_NAME> ADD FAILGROUP <FAILGROUP_NAME> DISK 'ORCL: VOL4NEW‘,‘ORCL: VOL5NEW‘;
Execute following query and check your disk status.
Select GROUP_NUMBER, DISK_NUMBER, MOUNT_STATUS, STATE, REDUNDANCY, TOTAL_MB, FREE_MB, FAILGROUP, NAME from v$asm_disk;
---END---
Case: (One or two Disk but not all Disks in a failgroup are lost after a disk/LUN failure)
Redundancy NORMAL
FAILGROUP DISK
Controller1 VOL1, VOL2
Controller2 VOL4, VOL5
Suppose One Disk (VOL5) in failgroup (Controller2) is lost. Workaround:
Execute following query on ASM instance. (This will show you ASM disk status) Select GROUP_NUMBER, DISK_NUMBER, MOUNT_STATUS, STATE, REDUNDANCY, TOTAL_MB, FREE_MB, FAILGROUP, NAME from v$asm_disk;
GROUP DISK MOUNT_S STATE REDUNDA TOTAL_MB FREE_MB FAILGROUP NAME
1 2 MISSING NORMAL UNKNOWN 0 0 VOL5
1 0 CACHED NORMAL UNKNOWN 2047 1269 CONTROLLER1 VOL1 1 1 CACHED NORMAL UNKNOWN 2047 1732 CONTROLLER2 VOL4
Result will show Disk VOL5 is Missing.
Add Disk physically, create partition, and scan ASM disk and list ASM disk. You will show delete disk (VOL5)
Again execute bellow Query:
Select GROUP_NUMBER, DISK_NUMBER, MOUNT_STATUS, STATE, REDUNDANCY, TOTAL_MB, FREE_MB, FAILGROUP, NAME from v$asm_disk;
GROUP DISK MOUNT_S STATE REDUNDA TOTAL_MB FREE_MB FAILGROUP NAME
0 0 CLOSED NORMAL UNKNOWN 3067 0
1 0 CACHED NORMAL UNKNOWN 2047 1269 CONTROLLER1 VOL1 1 1 CACHED NORMAL UNKNOWN 2047 1269 CONTROLLER2 VOL4NEW
Now delete the ASM Disk, scan ASM Disk and list disk (VOL5) /etc/init.d/oracleasm deletedisk VOL5
The list ASM disk will not show Disk (VOL5)
Create ASM Disk, scan and list ASM Disk with Different Disk name. /etc/init.d/oracleasm createdisk VOL5 /dev/sdd1
The list ASM Disk will show Disk (VOL5)
Now we add Newelly added ASM Disk to DISKGROUP.
ALTER DISKGROUP <DG_NAME> ADD FAILGROUP <FAILGROUP_NAME> DISK 'ORCL: VOL5‘;
Execute following query and check your disk status.
Select GROUP_NUMBER, DISK_NUMBER, MOUNT_STATUS, STATE, REDUNDANCY, TOTAL_MB, FREE_MB, FAILGROUP, NAME from v$asm_disk;
Useful Query for ASM
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
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.
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