This discussion is archived
9 Replies Latest reply: May 25, 2013 5:06 AM by user8930326 RSS

DiskGroup Size for a Databas

user8930326 Newbie
Currently Being Moderated
Hi Team,

Am looking for a SQL query to get the DiskGroup Size for each Database Size.

Ex: DB11A is a database with instance DB11A1, DB11A2
ASM is ASM instance with ASM1, ASM2
My ASM/DB Version 11.2.0.2
OS - RHEL 5.8

So i need a query to get DG size that is took/occupied by that Database

The output should say - DiskGroup DATA01 is occupying 50GB for Database DB11A

So i request you all to provide me the Query to get DiskGroup (Size in MB/GB) taken by Each DB

Sample Output

****************************************
Database_Name DiskGroup Size(MB)
************* *********** ************
DB11A DATA01 20,648
DB11A FLASH 16,024


Thanks in Advance

Edited by: user8930326 on May 20, 2013 8:21 PM

Edited by: user8930326 on May 20, 2013 8:22 PM

Edited by: user8930326 on May 20, 2013 8:24 PM

Edited by: user8930326 on May 20, 2013 8:25 PM
  • 1. Re: DiskGroup Size for a Databas
    MahirM.Quluzade Guru
    Currently Being Moderated
    Hi,

    Did you want this, please try
    select c.db_name, SUM(f.bytes/1024/1024) SUM_MB 
     from  v$asm_client c, v$asm_file f 
    where c.group_number = f.group_number
    group by  c.db_name;
    Regards
    Mahir M. Quluzade
  • 2. Re: DiskGroup Size for a Databas
    Dude! Guru
    Currently Being Moderated
    You probably mean how much space does a database occupy in a diskgroup?

    A diskgroup can store the data of several databases. If I'm not mistaken than you cannot query information using SQL for databases that are not currently connected and hence not seen in the v$asm_client view. Using the ASMCMD command line utility might be a better option.

    For instance:
    [oracle@ol1 ~]$ . oraenv
    ORACLE_SID = [orclasm] ? +ASM
    The Oracle base remains unchanged with value /u01/app/oracle
    [[oracle@ol1 ~]$ asmcmd
    ASMCMD> ls
    DATA/
    ASMCMD> cd data
    ASMCMD> ls
    ASM/
    ORCL/
    ASMCMD> cd orcl
    ASMCMD> du
    Used_MB      Mirror_used_MB
       3979               11937
    ASMCMD>
  • 3. Re: DiskGroup Size for a Databas
    user8930326 Newbie
    Currently Being Moderated
    No I have the same query to figure out the size taken by Each DB,

    But i am looking a query to see how much each Disk group is using by my Database i.e

    YOur query gave DB11A is totally using 798,234 MB but I am interested in to see [ i have 3 Disk groups Data01, Data02, Flash ]
    So i like to know how much of my each Disk group is taking ...

    like assume the output should show Data01 - 350,234 MB, Data02 300,234 MB and Flash the rest.

    I need a query to get that above info.

    Thanks for your help
  • 4. Re: DiskGroup Size for a Databas
    user8930326 Newbie
    Currently Being Moderated
    No I have the same query to figure out the size taken by Each DB,

    But i am looking a query to see how much each Disk group is using by my Database i.e

    YOur query gave DB11A is totally using 798,234 MB but I am interested in to see [ i have 3 Disk groups Data01, Data02, Flash ]
    So i like to know how much of my each Disk group is taking ...

    like assume the output should show Data01 - 350,234 MB, Data02 300,234 MB and Flash the rest.

    I need a query to get that above info.

    Thanks for your help
  • 5. Re: DiskGroup Size for a Databas
    User328666 Newbie
    Currently Being Moderated
    If you want to use a SQL query then you will need to either write several queries or a really big one with union statements. Oracle tracks the file sizes in a different view for each type of file: dba_data_files, dba_temp_files, v$log, v$archived_log, and v$controlfile. Some of these views only report blocks, and have another column to tell you the block size used by that file. Your SQL statement will use the SUBSTR and INSTR functions to inspect the leading characters between the first + and / symbols to determine the diskgroup name, and you can use an in-line view to group by those chars (optional). Re-run the query in each database.

    ASMCMD has a new du statement. Unfortunately it gives you total space used in a diskgroup and cannot break out the numbers per database. To make it work simply navigate to the diskgroup/database directory and then run du. Repeat for each database and add up the numbers, then repeat for each diskgroup and add up the numbers. This sounds dumb but you will be done faster than the time required to write a really complex SQL statement unless you're a whiz at that stuff.
  • 6. Re: DiskGroup Size for a Databas
    user8930326 Newbie
    Currently Being Moderated
    I agree but the only thing is am not good in development side I.e
    Write SQL coding so requesting your all help

    I tried my best but none worked so finally reached here

    Please someone helpme
  • 7. Re: DiskGroup Size for a Databas
    MahirM.Quluzade Guru
    Currently Being Moderated
    Hi,

    Can you check following tests.
    SQL> select c.db_name,d.name, SUM(BYTES/1024/1024) AS MBSIZE
     from  v$asm_client c, v$asm_diskgroup d, v$asm_file f
     where c.group_number=d.group_number 
       and f.group_number= d.group_number
    group by c.db_name,d.name;  2    3    4    5  
    
    DB_NAME  NAME                        MBSIZE
    -------- ------------------------------ ----------
    racdb      DATA                    2445.57178
    racdb      FRA                    217.642578
    
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
    OLAP, Data Mining, Oracle Database Vault and Real Application Testing options
    [oracle@oel62-rac1 Desktop]$ su - grid
    Password: 
    [grid@oel62-rac1 ~]$ asmcmd -p
    ASMCMD [+] > cd +DATA
    ASMCMD [+DATA] > du 
    Used_MB      Mirror_used_MB
       2469                2469
    ASMCMD [+DATA] > cd +FRA
    ASMCMD [+FRA] > du
    Used_MB      Mirror_used_MB
        228                 228
    Regards
    Mahir M. Quluzade
  • 8. Re: DiskGroup Size for a Databas
    user8930326 Newbie
    Currently Being Moderated
    Mihir,

    Thanks for your reply.

    Your query is not giving the correct size it just pulls the whole diskgroup size rather its not pulling the size occupied by that DB [ My DB size is around 800 GB, but the query show approx 1.5 TB, this size is the size of all my 3 DB's ]

    below is the my attemp.

    SYS@DB11A**>> @db_size

    Database Size Used space Free space
    -------------------- -------------------- --------------------
    789 GB 748 GB 41 GB

    SYS@DB11A**>> select c.db_name,d.name, SUM(BYTES/1024/1024) AS MBSIZE
    from v$asm_client c, v$asm_diskgroup d, v$asm_file f
    where c.group_number=d.group_number
    and f.group_number= d.group_number
    group by c.db_name,d.name;

    DB_NAME NAME MBSIZE
    -------- ------------------------------ ----------
    DB11A DATA01 632612.538
    DB11A DATA02 671676.986
    DB11A FRA 9369.07031
  • 9. Re: DiskGroup Size for a Databas
    user8930326 Newbie
    Currently Being Moderated
    Folks,

    Can someone please help me.

    Appreciate your help.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points