Home » RDBMS Server » Server Administration » Disk group used by database (Oracle 11.2.0.4.0, RHL Server release 6.5)
Disk group used by database [message #620361] Thu, 31 July 2014 12:45 Go to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 23155
Registered: January 2009
Senior Member
http://www.lmgtfy.com/?q=oracle+asm+diskgroup
Re: Disk group used by database [message #620364 is a reply to message #620361] Thu, 31 July 2014 13:09 Go to previous messageGo to next message
Michel Cadot
Messages: 60006
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Disk group used by database [message #620381 is a reply to message #620364] Thu, 31 July 2014 15:31 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 23155
Registered: January 2009
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> 


Re: Disk group used by database [message #620384 is a reply to message #620381] Thu, 31 July 2014 15:56 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 23155
Registered: January 2009
Senior Member
how many concurrent databases are open on this system?
Re: Disk group used by database [message #620396 is a reply to message #620393] Thu, 31 July 2014 19:28 Go to previous messageGo to next message
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 Go to previous messageGo to next message
babuknb
Messages: 1734
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

Re: Disk group used by database [message #620404 is a reply to message #620384] Fri, 01 August 2014 00:20 Go to previous messageGo to next message
Michel Cadot
Messages: 60006
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
srikanth02 wrote on Thu, 31 July 2014 22:56
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;


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

Re: Disk group used by database [message #620469 is a reply to message #620399] Fri, 01 August 2014 13:54 Go to previous messageGo to next message
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
Re: Disk group used by database [message #620471 is a reply to message #620469] Fri, 01 August 2014 14:08 Go to previous messageGo to next message
babuknb
Messages: 1734
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.


Re: Disk group used by database [message #620473 is a reply to message #620361] Fri, 01 August 2014 15:27 Go to previous messageGo to next message
John Watson
Messages: 4862
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.
Re: Disk group used by database [message #620475 is a reply to message #620473] Fri, 01 August 2014 16:38 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 4862
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.
Re: Disk group used by database [message #620601 is a reply to message #620480] Mon, 04 August 2014 09:52 Go to previous messageGo to next message
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.
Re: Disk group used by database [message #620608 is a reply to message #620601] Mon, 04 August 2014 10:04 Go to previous message
Michel Cadot
Messages: 60006
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This does not answer your question.

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


Current Time: Sat Dec 20 13:31:20 CST 2014

Total time taken to generate the page: 0.07030 seconds