OraFAQ Forum: Server Administration » Disk group used by database

Home » RDBMS Server » Server Administration » Disk group used by database (Oracle 11.2.0.4.0, RHL Server release 6.5)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Disk group used by database [message #620361]

Thu, 31 July 2014 12:45

srikanth02
Messages: 49
Registered: June 2014
Location: India

Member

Hi,

I am using ASM disk group to store database files but its not a RAC database.

Can you tell how to find out disk groups and their path used by particular database

Regards,
Srikanth

[Updated on: Thu, 31 July 2014 12:45]

Report message to a moderator

Re: Disk group used by database [message #620362 is a reply to message #620361]

Thu, 31 July 2014 12:49

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.lmgtfy.com/?q=oracle+asm+diskgroup

Report message to a moderator

Re: Disk group used by database [message #620364 is a reply to message #620361]

Thu, 31 July 2014 13:09

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Dumby, it is in the documentation, the one we already pointed more than one hundred times.

Report message to a moderator

Re: Disk group used by database [message #620381 is a reply to message #620364]

Thu, 31 July 2014 15:31

srikanth02
Messages: 49
Registered: June 2014
Location: India

Member

Hi Blackswan,

I used following select query to find the path where the databases files for a particular database is stored,
but the output it has selected is 148 rows selected. say for example i want to find the path of database files for database 'A'
and i did select query from 'A' database.Does it mean database files of 'A' are stored in the following path ?

select name, path, header_status from v$asm_disk;
NAME                           PATH                           HEADER_STATUS
------------------------------ ------------------------------ ------------------------------------
HDS_K1_439A_39                 /dev/raw/raw39                 MEMBER
HDS_K2_4399_38                 /dev/raw/raw38                 MEMBER
HDS_K1_4399_37                 /dev/raw/raw37                 MEMBER
HDS_K2_4397_34                 /dev/raw/raw34                 MEMBER
HDS_K1_4397_33                 /dev/raw/raw33                 MEMBER
HDS_K2_4396_32                 /dev/raw/raw32                 MEMBER
HDS_K1_4396_31                 /dev/raw/raw31                 MEMBER
HDS_K2_4395_30                 /dev/raw/raw30                 MEMBER
HDS_K1_4395_29                 /dev/raw/raw29                 MEMBER
HDS_K2_4394_28                 /dev/raw/raw28                 MEMBER
HDS_K1_4394_27                 /dev/raw/raw27                 MEMBER
HDS_K2_4393_26                 /dev/raw/raw26                 MEMBER
HDS_K1_4393_25                 /dev/raw/raw25                 MEMBER
HDS_K2_4392_24                 /dev/raw/raw24                 MEMBER
HDS_K1_4392_23                 /dev/raw/raw23                 MEMBER
HDS_K2_4391_22                 /dev/raw/raw22                 MEMBER
HDS_K1_4391_21                 /dev/raw/raw21                 MEMBER
HDS_K2_438F_18_ODMMES          /dev/raw/raw18                 MEMBER
HDS_K1_438F_17_ODMMES          /dev/raw/raw17                 MEMBER

Could you please clarify it?

Regards,
Srikanth

[Updated on: Thu, 31 July 2014 15:33]

Report message to a moderator

Re: Disk group used by database [message #620382 is a reply to message #620381]

Thu, 31 July 2014 15:48

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

  1* select view_name from dba_views where view_name like '%ASM%'
SQL> /

VIEW_NAME
------------------------------
V_$ASM_ACFSSNAPSHOTS
V_$ASM_ACFSVOLUMES
V_$ASM_ACFS_ENCRYPTION_INFO
V_$ASM_ACFS_SECURITY_INFO
V_$ASM_ALIAS
V_$ASM_ATTRIBUTE
V_$ASM_CLIENT
V_$ASM_DISK
V_$ASM_DISKGROUP
V_$ASM_DISKGROUP_STAT
V_$ASM_DISK_IOSTAT

VIEW_NAME
------------------------------
V_$ASM_DISK_STAT
V_$ASM_FILE
V_$ASM_FILESYSTEM
V_$ASM_OPERATION
V_$ASM_TEMPLATE
V_$ASM_USER
V_$ASM_USERGROUP
V_$ASM_USERGROUP_MEMBER
V_$ASM_VOLUME
V_$ASM_VOLUME_STAT
GV_$ASM_ACFSSNAPSHOTS

VIEW_NAME
------------------------------
GV_$ASM_ACFSVOLUMES
GV_$ASM_ACFS_ENCRYPTION_INFO
GV_$ASM_ACFS_SECURITY_INFO
GV_$ASM_ALIAS
GV_$ASM_ATTRIBUTE
GV_$ASM_CLIENT
GV_$ASM_DISK
GV_$ASM_DISKGROUP
GV_$ASM_DISKGROUP_STAT
GV_$ASM_DISK_IOSTAT
GV_$ASM_DISK_STAT

VIEW_NAME
------------------------------
GV_$ASM_FILE
GV_$ASM_FILESYSTEM
GV_$ASM_OPERATION
GV_$ASM_TEMPLATE
GV_$ASM_USER
GV_$ASM_USERGROUP
GV_$ASM_USERGROUP_MEMBER
GV_$ASM_VOLUME
GV_$ASM_VOLUME_STAT
MGMT_V_ASM_DB_DG_USAGE
MGMT_V_ASM_DG_USAGE

44 rows selected.

SQL>

Report message to a moderator

Re: Disk group used by database [message #620384 is a reply to message #620381]

Thu, 31 July 2014 15:56

srikanth02
Messages: 49
Registered: June 2014
Location: India

Member

I am tired of finding the query Sad

. I hope the below one is the correct query to find out the path of database files .

SQL> select name from v$datafile;

[Updated on: Thu, 31 July 2014 15:58]

Report message to a moderator

Re: Disk group used by database [message #620393 is a reply to message #620384]

Thu, 31 July 2014 19:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

how many concurrent databases are open on this system?

Report message to a moderator

Re: Disk group used by database [message #620396 is a reply to message #620393]

Thu, 31 July 2014 19:28

srikanth02
Messages: 49
Registered: June 2014
Location: India

Member

<how many concurrent databases are open on this system?>

There are 10 databases hosted on this server.

Regards,
Srikanth

[Updated on: Thu, 31 July 2014 19:29]

Report message to a moderator

Re: Disk group used by database [message #620399 is a reply to message #620396]

Thu, 31 July 2014 20:56

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

As its 10 database in one ASM diskgroup. You should know which database stored in which disk group [ I believe there should some naming configuration with respective of database / asm diskgroup ]

v$asm_diskgroup

And

SELECT SUBSTR(d.name,1,16) AS asmdisk, d.mount_status, d.state, 
     dg.name AS diskgroup FROM V$ASM_DISKGROUP dg, V$ASM_DISK d 
     WHERE dg.group_number = d.group_number;

http://docs.oracle.com/cd/E16338_01/server.112/e10500/asmviews.htm

Report message to a moderator

Re: Disk group used by database [message #620404 is a reply to message #620384]

Fri, 01 August 2014 00:20

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

srikanth02 wrote on Thu, 31 July 2014 22:56

I am tired of finding the query . I hope the below one is the correct query to find out the path of database files .
SQL> select name from v$datafile;

And we are tired of you, your laziness and your stupidity. Sad

Report message to a moderator

Re: Disk group used by database [message #620469 is a reply to message #620399]

Fri, 01 August 2014 13:54

srikanth02
Messages: 49
Registered: June 2014
Location: India

Member

Hi Babu,

Welcome back ! Nice to see you in my thread.

With help of the query you provided i could find which diskgroup the database files are writing to as below

SELECT SUBSTR(d.name,1,16) AS asmdisk, d.mount_status, d.state, 
     dg.name AS diskgroup FROM V$ASM_DISKGROUP dg, V$ASM_DISK d 
     WHERE dg.group_number = d.group_number;

ASMDISK                        MOUNT_STATUS          STATE                    DISKGROUP
------------------------------ --------------------- ------------------------ ------------------------------
HDS_K2_43B5_94                 CACHED                NORMAL                   MDAS_DATA01
HDS_K1_43B5_93                 CACHED                NORMAL                   MDAS_DATA01
HDS_K2_43B4_92                 CACHED                NORMAL                   MDAS_DATA01
HDS_K1_43B4_91                 CACHED                NORMAL                   MDAS_DATA01

And to find the path of disk groups i used the following query.

select group_number,name,path,total_mb,free_mb from v$asm_disk
where group_number in (select group_number from v$asm_diskgroup
where name in ('MDAS_DATA01'));
GROUP_NUMBER NAME                           PATH                             TOTAL_MB    FREE_MB
------------ ------------------------------ ------------------------------ ---------- ----------
           3 HDS_K1_43C2_119                /dev/raw/raw119                    102396      17500
           3 HDS_K2_43C1_118                /dev/raw/raw118                    102396      17472
           3 HDS_K1_43C1_117                /dev/raw/raw117                    102396      17456
           3 HDS_K2_43C0_116                /dev/raw/raw116                    102396      17348
           3 HDS_K1_43C0_115                /dev/raw/raw115                    102396      17528

Can you check the above output and confirm the query i used is correct ?

Regards,
Srikanth

Report message to a moderator

Re: Disk group used by database [message #620471 is a reply to message #620469]

Fri, 01 August 2014 14:08

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

Quote:

Can you tell how to find out disk groups and their path used by particular database

You own your database / environment.. So we don't know how do you setup disk group with database.. Since you knows how to figure out disk group and disk's with respective of disk groups.. Please find out and post your response if any more question.

Report message to a moderator

Re: Disk group used by database [message #620473 is a reply to message #620361]

Fri, 01 August 2014 15:27

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

Quote:

Can you tell how to find out disk groups and their path used by particular database

You need to connect to the ASM instance (not an RDBMS instance) and query v$asm_client. That will tell you which instances are using which disk groups.

Report message to a moderator

Re: Disk group used by database [message #620475 is a reply to message #620473]

Fri, 01 August 2014 16:38

srikanth02
Messages: 49
Registered: June 2014
Location: India

Member

Hi all,

Here is my findings.

I altered the query and some how manage to get the output i wanted.But the below sql query returned 510 rows.
I suspect the query is wrong.

Can you check and confirm whether the query i used is correct ?

SQL> l
  1  SELECT f.name as diskgroup,dg.path AS location, SUBSTR(c.instance_name,1,12) AS instance,
  2  SUBSTR(c.db_name,1,12) AS dbname, SUBSTR(c.SOFTWARE_VERSION,1,12) AS software
  3  FROM v$asm_disk dg, V$ASM_CLIENT c,v$asm_diskgroup f
  4  WHERE dg.group_number = c.group_number
  5* and DB_NAME='DMRS'
DISKGROUP                      LOCATION                       INSTANCE             DBNAME                   SOFTWARE
---------------------- ------------------------------ -------------------- ------------------------ -------------------------
MCPMES_DATA01                  /dev/raw/raw130                DMRS                 DMRS                     11.2.0.4.0
ISPMES_FRA                     /dev/raw/raw130                DMRS                 DMRS                     11.2.0.4.0
ISPMES_DATA01                  /dev/raw/raw130                DMRS                 DMRS                     11.2.0.4.0
ODMMES_DATA01                  /dev/raw/raw130                DMRS                 DMRS                     11.2.0.4.0
-
-
-
-
and so on.

Regards,
Srikanth

[Updated on: Fri, 01 August 2014 19:52]

Report message to a moderator

Re: Disk group used by database [message #620480 is a reply to message #620475]

Sat, 02 August 2014 01:17

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

You are making a cross join to v$asm_client. Re-write the query to use ANSI syntax, which is much less prone to error.

Report message to a moderator

Re: Disk group used by database [message #620601 is a reply to message #620480]

Mon, 04 August 2014 09:52

srikanth02
Messages: 49
Registered: June 2014
Location: India

Member

Hi all,

I found answer i wanted .

First need to find the diskgroup name related to database using asmcmd and then lsdg

Then using below command find the path it belongs to

select path,name,os_mb,failgroup from v$asm_disk where group_number =(select group_number from v$asm_diskgroup where name= 'DG1'

Thanks for those who tried to help me on this thread.

Regards,
Srikanth.

Report message to a moderator

Re: Disk group used by database [message #620608 is a reply to message #620601]

Mon, 04 August 2014 10:04

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This does not answer your question.

Report message to a moderator

Previous Topic:	12c creating user from Sql Dev client machine
Next Topic:	ORA-01410 INVAILD ROWID for standby database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jan 22 00:59:05 CST 2025