9 Replies Latest reply: May 25, 2013 7:06 AM by user8930326 RSS

    DiskGroup Size for a Databas

    user8930326
      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
          Mahir M. Quluzade
          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
            Catch-22
            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
              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
                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
                  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
                    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
                      Mahir M. Quluzade
                      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
                        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
                          Folks,

                          Can someone please help me.

                          Appreciate your help.