Home » RDBMS Server » Server Administration » ASM Diskgroup State (Oracle 10.1.0.5, Solaris 10)
ASM Diskgroup State [message #347440] Thu, 11 September 2008 11:53 Go to next message
deepmachine
Messages: 80
Registered: August 2008
Location: United States
Member
I ran the following SQL:
SELECT 
  V.GROUP_NUMBER, V.NAME, V.SECTOR_SIZE, 
     V.BLOCK_SIZE, V.ALLOCATION_UNIT_SIZE, V.STATE, 
     V.TYPE, V.TOTAL_MB, V.FREE_MB
  FROM SYS.V_$ASM_DISKGROUP V


...and got these results:
GROUP_NUMBER	NAME		SECTOR_SIZE	BLOCK_SIZE	ALLOCATION_UNIT_SIZE	STATE		TYPE	TOTAL_MB	FREE_MB
1		DATA		512		4096		1048576			CONNECTED	EXTERN	204186		0
2		LOGS		512		4096		1048576			MOUNTED		EXTERN		


What can be the reasons that a diskgroup should run in MOUNTED state?

Also, why is not showing me any data against FREE_MB, and no data against second diskgroup?

Thanks!

[Updated on: Thu, 11 September 2008 11:55]

Report message to a moderator

Re: ASM Diskgroup State [message #347446 is a reply to message #347440] Thu, 11 September 2008 12:16 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You must be running this query against the database instead of ASM instance.
It is one of those quirky things that drove me nuts and I never got a chance to look into further.
Long story short, connected means the database is actively using it.
You must be worried if the state is 'dismounted' or the other states.
try with AMS instance (using v$ instead of v_$) or
just try with
export ORACLE_SID=+ASM --+ASM1 OR ASM2 OR whatever in case of RAC
asmcmd lsdg 
 

[Updated on: Thu, 11 September 2008 12:17]

Report message to a moderator

Re: ASM Diskgroup State [message #347459 is a reply to message #347446] Thu, 11 September 2008 13:42 Go to previous messageGo to next message
deepmachine
Messages: 80
Registered: August 2008
Location: United States
Member
Oh great.. it worked!

Here are the results.
$ export ORACLE_SID=+ASM1
$ sqlplus / as sysdba
SQL> set linesize 2000
SQL> SELECT
V.GROUP_NUMBER, V.NAME, V.SECTOR_SIZE,
V.BLOCK_SIZE, V.ALLOCATION_UNIT_SIZE, V.STATE,
V.TYPE, V.TOTAL_MB, V.FREE_MB
FROM SYS.V$ASM_DISKGROUP V;

GROUP_NUMBER NAME                           SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE       TYPE     TOTAL_MB    FREE_MB
------------ ------------------------------ ----------- ---------- -------------------- ----------- ------ ---------- ----------
           1 DATA                           512       	4096              	1048576 MOUNTED     EXTERN     204186     137965
           2 LOGS                           512       	4096              	1048576 MOUNTED     EXTERN     204186     203715

SQL>


Questions:
So, what's the difference between the ALLOCATION_UNIT_SIZE and TOTAL_MB?

My DB size (checked from EM) is 69G, and FREE_MB=137965 shows that I have alsmost 134G free. That means the max. size for my DISKGROUP=1 is 200G. If yes, then what's Allocation Unit Size?

Does this also mean that the total capacity of my ASM DISKGROUPS is 400G?

Also, why didn't the V$ASM_DISKGROUP show the information until the SID was set as +ASM1? I assume that information in views should remain the same regardless of the SID.

awaiting your reply...
Thanks again..

[Updated on: Thu, 11 September 2008 13:45]

Report message to a moderator

Re: ASM Diskgroup State [message #347461 is a reply to message #347459] Thu, 11 September 2008 13:50 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
All the question are answered in docs.
http://www.acs.ilstu.edu/docs/Oracle/server.101/b10755/dynviews_1019.htm

Re: ASM Diskgroup State [message #347639 is a reply to message #347461] Fri, 12 September 2008 08:52 Go to previous messageGo to next message
deepmachine
Messages: 80
Registered: August 2008
Location: United States
Member
Mahesh,
I had already gone through those links, and they don't have any significant information about the questions I had.

Any other material?

Thanks
Re: ASM Diskgroup State [message #347651 is a reply to message #347639] Fri, 12 September 2008 10:09 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> So, what's the difference between the ALLOCATION_UNIT_SIZE and TOTAL_MB?
allocation unit is just the extent size. If you are interested,
investigate about _asm_ausize and _asm_stripesize.
By default allocation unit is 1mb (I believe).
Quoting docs.
ALLOCATION_UNIT_SIZE  -Size of the allocation unit (in bytes)
FREE_MB               - Unused capacity of the disk group (in megabytes)

>>Also, why didn't the V$ASM_DISKGROUP show the information until the SID was set as +ASM1? I assume that information in views should remain the same regardless of the SID.
Already answered.
Before you queried the database instance.
with +ASM1 you query the local ASM instance.
Both are different
Quoting docs
In an Automatic Storage Management instance, V$ASM_DISKGROUP displays one row for every disk group discovered by the Automatic Storage Management instance. 
In a database instance, V$ASM_DISKGROUP displays one row for every Automatic Storage Management disk group 
mounted by the local Automatic Storage Management instance.

>>Does this also mean that the total capacity of my ASM DISKGROUPS is 400G?
quoting docs
TOTAL_MB -Total capacity of the disk group (in megabytes)

do the math.

Re: ASM Diskgroup State [message #347816 is a reply to message #347651] Sun, 14 September 2008 04:12 Go to previous messageGo to next message
deepmachine
Messages: 80
Registered: August 2008
Location: United States
Member
I have a staging environment where I was trying to see the ASM disk space. I followed the same process of exporting the ORACLE_SID=+ASM1 and then running the SQL as mentioned in the above posts, and I get this error:
oracle@ora1-pstag:~$ export ORACLE_SID=+ASM1
oracle@ora1-pstag:~$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Sep 14 05:05:16 2008

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> set linesize 2000

SELECT
V.GROUP_NUMBER, V.NAME, V.SECTOR_SIZE,
V.BLOCK_SIZE, V.ALLOCATION_UNIT_SIZE, V.STATE,
V.TYPE, V.TOTAL_MB, V.FREE_MB
FROM SYS.V$ASM_DISKGROUP V;
SELECT
*
ERROR at line 1:
ORA-01034: ORACLE not available


But when I do it through ORACLE_SID=stage1, it gives me following results:
SELECT
V.GROUP_NUMBER, V.NAME, V.SECTOR_SIZE,
V.BLOCK_SIZE, V.ALLOCATION_UNIT_SIZE, V.STATE,
V.TYPE, V.TOTAL_MB, V.FREE_MB
FROM SYS.V$ASM_DISKGROUP V;

GROUP_NUMBER NAME                           SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE       TYPE     TOTAL_MB    FREE_MB
------------ ------------------------------ ----------- ---------- -------------------- ----------- ------ ---------- ----------
           1 DATA                                   512       4096              1048576 CONNECTED   EXTERN     102352      32255
           2 LOGS                                   512       4096              1048576 CONNECTED   EXTERN     102352       2734

SQL>


What's the difference, what's going on?

Thanks.
Re: ASM Diskgroup State [message #347835 is a reply to message #347816] Sun, 14 September 2008 08:57 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
as already said in the previous post,
export ORACLE_SID=+ASM --+ASM1 OR ASM2 OR whatever in case of RAC

If you have a rac, +ASM1 or +ASM2 or whatever you have defined will work.
For a single instacne +ASM would work.
Depends on your environment.
Re: ASM Diskgroup State [message #348728 is a reply to message #347440] Wed, 17 September 2008 16:01 Go to previous message
mjshank
Messages: 6
Registered: August 2007
Junior Member
If you like, send me the ASM entry in your oratab and I will send you a script to provide the results below:

$ asmreview.ksh
Reporting ASM stats...

Date
----------------------------------------
Report Date: 17-SEP-2008 04:54:28


Instance Version SGA MG PGA MG Total RAM MB
------------------------ --------------- ---------- ---------- ------------
@ +ASM 10.2.0.3.0 500 25 525


NAME PATH TOTAL_MB FREE_MB READS WRITES MOUNT_STATUS
--------------- -------------------------------- ------------ ------------ ---------- ---------- ------------
DATA_0003 /u01/crs/oracle/ora_links/asm200 153,588 70,915 1312927 1356888 CACHED
DATA_0004 /u01/crs/oracle/ora_links/asm201 153,588 70,915 744480 789337 CACHED
DATA_ETL_0000 /u01/crs/oracle/ora_links/asm100 102,386 45,757 152138 406310 CACHED
DATA_ETL_0001 /u01/crs/oracle/ora_links/asm101 102,386 45,771 120320 214067 CACHED
DATA_ETL_0002 /u01/crs/oracle/ora_links/asm102 102,386 45,769 128436 204394 CACHED
DATA_ETL_0003 /u01/crs/oracle/ora_links/asm103 102,386 45,772 92163 226359 CACHED
FRA_0001 /u01/crs/oracle/ora_links/asm300 51,187 50,600 132665 1093950 CACHED
------------ ------------
sum 767,907 375,499

7 rows selected.


michael dot shank at mpiresearch.com
Previous Topic: scrollbar creatin
Next Topic: Log minor
Goto Forum:
  


Current Time: Tue Dec 06 00:28:22 CST 2016

Total time taken to generate the page: 0.13640 seconds