4 Replies Latest reply: Feb 15, 2013 4:21 AM by Osama_Mustafa RSS

    In RAC, check archive mount point size from sqlplus prompt

    989730
      hi Expert,

      I am in need to find archive log mount point space detail usage through sqlplus on multiple instance. Is there any view in oracle which can give me the detail.

      I know one method using external table but that is cumbersome for RAC.

      can i get some command like this.

      select inst_id,mountpoint,space_usage,space_available,total from GV$table --> returns

      or any other easy method.

      Thankyou.
        • 1. Re: In RAC, check archive mount point size from sqlplus prompt
          CKPT
          I am in need to find archive log mount point space detail usage through sqlplus on multiple instance. Is there any view in oracle which can give me the detail.

          I know one method using external table but that is cumbersome for RAC.

          can i get some command like this.

          select inst_id,mountpoint,space_usage,space_available,total from GV$table --> returns

          or any other easy method.
          As i understood your question.....

          You will see same results from all the instances, It shows based on the Disk group, you can use "v$asm_diskgroup" if ASM
          SQL> select name,total_mb,free_mb from v$asm_diskgroup;

          And you cannot see the archives occupied by each database if there are multiple databases are using same disk group for archives. Because one database wont care of other database structure, usages so on..

          And if you talk about normal mount point which is completely OS part, here you cannot get the info from RDBMS instance. Of course you can calculate size from v$archived_log by mentioning mount point in where clause of "name" column.
          • 2. Re: In RAC, check archive mount point size from sqlplus prompt
            Justin_Mungal
            986727 wrote:
            hi Expert,

            I am in need to find archive log mount point space detail usage through sqlplus on multiple instance. Is there any view in oracle which can give me the detail.

            I know one method using external table but that is cumbersome for RAC.

            can i get some command like this.

            select inst_id,mountpoint,space_usage,space_available,total from GV$table --> returns

            or any other easy method.

            Thankyou.
            If you're using a Fast Recovery Area, the database tracks usage details for it, and those details are viewable.
            SQL> set linesize 150;
            SQL> select * from v$flash_recovery_area_usage;
            
            FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
            -------------------- ------------------ ------------------------- ---------------
            CONTROL FILE                        .23                         0               1
            REDO LOG                           3.64                         0               3
            ARCHIVED LOG                          0                         0               0
            BACKUP PIECE                          0                         0               0
            IMAGE COPY                            0                         0               0
            FLASHBACK LOG                         0                         0               0
            FOREIGN ARCHIVED LOG                  0                         0               0
            
            7 rows selected.
            -Justin
            • 3. Re: In RAC, check archive mount point size from sqlplus prompt
              Step_Into_Oracle_DBA
              First check whether database is using Recovery File Dest or log_archive_dest using archive log list
              SQL> archive log list
              Database log mode              Archive Mode
              Automatic archival             Enabled
              Archive destination            USE_DB_RECOVERY_FILE_DEST
              Oldest online log sequence     381
              Next log sequence to archive   383
              Current log sequence           383
              SQL>
              SQL> sho parameter recovery
              
              NAME                                 TYPE        VALUE
              ------------------------------------ ----------- ------------------------------
              db_recovery_file_dest                string      +FRA1
              db_recovery_file_dest_size           big integer 8G
              recovery_parallelism                 integer     0
              
              SQL>  Select name,total_mb,free_mb from v$asm_diskgroup where name='FRA1';
              
              NAME                             TOTAL_MB    FREE_MB
              ------------------------------ ---------- ----------
              FRA1                         81978      76733
              
              SQL> set lines 200
              SQL> select * from v$flash_recovery_area_usage;
              
              FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
              -------------------- ------------------ ------------------------- ---------------
              CONTROL FILE                        .22                         0               1
              REDO LOG                            7.4                         0               6
              ARCHIVED LOG                       1.57                         0               2
              BACKUP PIECE                          0                         0               0
              IMAGE COPY                            0                         0               0
              FLASHBACK LOG                         0                         0               0
              FOREIGN ARCHIVED LOG                  0                         0               0
              
              7 rows selected.
              Types of files which uses FRA will be shown in v$flash_recovery_area_usage.

              Please keep forum clean by Marking your Post as Answered or Helpful if Your question is answered.

              Thanks & Regards,
              SID

              (StepIntoOracleDBA)

              Email : stepintooracledba@gmail.com

              http://stepintooracledba.blogspot.in/

              http://www.stepintooracledba.com/
              • 4. Re: In RAC, check archive mount point size from sqlplus prompt
                Osama_Mustafa
                Check this Oracle Document : "Viewing Information About the Archived Redo Log"
                http://docs.oracle.com/cd/B28359_01/server.111/b28310/archredo008.htm