Oracle DBA – A lifelong learning experience

Posts Tagged ‘v$asm_diskgroup’

ASM disks – lsdg compared with the v$asm_diskgroup view

Posted by John Hallas on July 8, 2016

What is the difference between the summaries of disk space on these two systems? Look at the free_usable_file_MB column and free space

System 1

ASMCMD [+] > lsdg
State    Type    Rebal  Sector  Block       AU   Total_MB   Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  NORMAL  N         512   4096  4194304   72769536   2031032           433152          798940              0             N  DATA_PEX0/
MOUNTED  NORMAL  N         512   4096  4194304  128286720  20004028          2672640         8665694              0             N  DATA_PEX1/
         Group                 Diskgroup     Total  Req'd     Free   Percent Disk Size Percent Percent  Disk
     Group Name       State      Redundancy       GB     GB       GB Imbalance  Variance    Free    Free Count
---------- ---------- ---------- ---------- -------- ------ -------- --------- --------- ------- ------- -----
         1 DATA_PEX0  MOUNTED    NORMAL       71,064    423    1,560       1.4        .0     2.2     3.5   168
         2 DATA_PEX1  MOUNTED    NORMAL      125,280  2,610   16,925        .1        .0    15.6    15.6    48

System 2

ASMCMD [+] > lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576   2621412   595744                0          595744              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576   1048568   700029                0          700029              0             N  FRA/

                                                     Mirror                      Percent Minimum Maximum
           Group                 Diskgroup     Total  Req'd     Free   Percent Disk Size Percent Percent  Disk
     Group Name       State      Redundancy       GB     GB       GB Imbalance  Variance    Free    Free Count
---------- ---------- ---------- ---------- -------- ------ -------- --------- --------- ------- ------- -----
         1 DATA       MOUNTED    EXTERN        2,560      0      582        .2        .0    22.7    22.8    20
         2 FRA        MOUNTED    EXTERN        1,024      0      684        .0        .0    66.8    66.8     8

System 1 is an Exadata stack with NORMAL redundancy whereas System 2 uses EXTERNAL redundancy. All of our systems bar Exadata are configured with dynamic multi-pathing with external redundancy to ensure high availability. – we allow the SAN to manage redundancy

In system 1 when using the second query to interrogate the diskgroups it would appear that we have 1.5Tb of free space in DATA_PEX0  and yet the lsdg command indicates we only have 800Gb free. Quite a significant difference when we have a weekly growth rate of ~250Gb in that diskgroup

SELECT g.group_number  "Group" ,      g.name          "Group Name" ,      g.state         "State" ,      g.type          "Type" ,      g.total_mb/1024 "Total GB" ,      g.free_mb/1024  "Free GB" ,      100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) "Imbalance" ,      100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) "Variance" ,      100*(min(d.free_mb/d.total_mb)) "MinFree" ,      100*(max(d.free_mb/d.total_mb)) "MaxFree" ,      count(*)        "DiskCnt" FROM v$asm_disk d, v$asm_diskgroup g WHERE d.group_number = g.group_number and d.group_number <> 0 and d.state = 'NORMAL' and d.mount_status = 'CACHED' GROUP BY g.group_number, g.name, g.state, g.type, g.total_mb, g.free_mb ORDER BY 1;

PS the code comes from a very good ASM script from this very blog

This blog by Harald van Breederode explains in a much better way than I could why the mirroring in normal redundancy uses additional space .

As we are quite space challenged on the Exadata storage at the moment I have been asked several times to explain the different values of free space that are being reported – now I can just point them to this blog entry which will re-direct them to Harald’s very good explanation.

Job done

 

 

 

 

Advertisements

Posted in ASM, Blogroll, Oracle | Tagged: , , , , | 6 Comments »

The Mother of all ASM scripts

Posted by John Hallas on March 6, 2012

Back in 2009 I posted a script which I found very useful to review ASM disks. I gave that post the low-key title of The ASM script of all ASM scripts. Now that script has been improved I have to go a bit further with the hyperbole and we have the The Mother of all ASM scripts.  If it ever gets improved then the next post will just be called ‘Who’s the Daddy’.

I have been using the current script across all our systems for the last 3 years and I find it very useful, a colleague, Allan Webster, has added a couple of improvements and it is now better than before.

The improvements show current disk I/O statistics and a breakdown of the types of files in each disk group and the total sizes of that filetype. The I/O statistics are useful when you have a lot of databases, many of which are test and development and so you do not look at them as that often. It just gives a quick overview that allows you to get a feel if anything is wrong and to see what the system is actually doing. There are also a few comments at the beginning defining the various ASM views available. Read the rest of this entry »

Posted in ASM, Oracle, scripts | Tagged: , , | 13 Comments »

asm 11g compatibility settings and conversion to H.A.R.D format

Posted by John Hallas on December 12, 2008

When using ASM with 11g and taking the default settings from a DBCA build the following line can be seen in the asm alert_log

Spfile /app/oracle/product/11.1.0/asm/dbs/spfile+ASM.ora is in old pre-11 format and compatible >= 11.0.0; converting to new H.A.R.D. compliant format

Firstly H.A.R.D. refers to Oracle’s Hardware Assisted Resilient Data (H.A.R.D) Initiative which Metalink note 227671.1 summarises as

To use HARD validation, all datafiles and log files are placed on HARD-compliant storage.

The user must also enable the HARD validation feature on the storage, using the vendor-provided interface. When Oracle writes data to the storage, the storage system validates the data. If it appears to be corrupted, then the write is rejected with an error.

The sql commands here are taken from an excellent note on the Pythian site by Alex Gorbachev http://www.pythian.com/blogs/1078/oracle-11g-asm-diskgroup-compatibility.

In short the compatibility settings allow ASM to manage databases from both 10g and 11g. As this was an 11g environment only I felt comfortable in amending the parameters.

The compatible.asm parameter defines the minimum version of the asm software that is using the disk groups. As the format of the metadata on the disk changes from asm 10 to asm 11 then this setting defines that format. This will be non-reversible.

rdbms.compatible defines the minimum version of the rdbms compatible parameter for any databse that uses the asm disk groups.

col COMPATIBILITY form a10

col DATABASE_COMPATIBILITY form a10

col NAME form a20

select group_number, name, compatibility, database_compatibility from v$asm_diskgroup;

GROUP_NUMBER NAME COMPATIBIL DATABASE_C

———— ——————– ———- ———-

1 FRA 10.1.0.0.0 10.1.0.0.0

2 DATA 10.1.0.0.0 10.1.0.0.0

SQL> col value form a10

select group_number, name, value from v$asm_attribute;

no rows selected

SQL> alter diskgroup FRA set attribute ‘compatible.asm’=’11.1’;

Diskgroup altered.

alter diskgroup DATA set attribute ‘compatible.asm’=’11.1’

Diskgroup altered.

select group_number, name, compatibility, database_compatibility from v$asm_diskgroup;

GROUP_NUMBER NAME COMPATIBIL DATABASE_C

———— ——————– ———- ———-

1 FRA 11.1.0.0.0 10.1.0.0.0

2 DATA 11.1.0.0.0 10.1.0.0.0

SQL> select group_number, name, value from v$asm_attribute;

GROUP_NUMBER NAME VALUE

———— ——————– ———-

1 disk_repair_time 3.6h

1 au_size 8388608

1 compatible.asm 11.1.0.0.0

1 compatible.rdbms 10.1.0.0.0

2 disk_repair_time 3.6h

2 au_size 8388608

2 compatible.asm 11.1.0.0.0

2 compatible.rdbms 10.1.0.0.0

8 rows selected.

alter diskgroup data set attribute ‘compatible.rdbms’=’11.1’

alter diskgroup fra set attribute ‘compatible.rdbms’=’11.1’

alter diskgroup data set attribute ‘compatible.rdbms’=’11.1’

alter diskgroup fra set attribute ‘compatible.rdbms’=’11.1’

select group_number, name, value from v$asm_attribute;

GROUP_NUMBER NAME COMPATIBIL DATABASE_C

———— ——————– ———- ———-

1 FRA 11.1.0.0.0 11.1.0.0.0

2 DATA 11.1.0.0.0 11.1.0.0.0

Restart the database and lo and behold, no spfile compatibility error

Posted in ASM, Oracle | Tagged: , , , , , | Leave a Comment »