By Franck Pachot

.
In a previous post I used X$KCCAGF to get more information about reclaimable archived logs in FRA, because there is a bug in standby (not opened) databases where archivelog deletion policy is ignored. I explained that the view V$RECOVERY_AREA_USAGE has only aggregated information about space reclaimable without the details about which files are reclaimable or not. Here I’ll explain how I came to X$KCCAGF and I’ll give the query to get all the detailed information that is hidden behind V$RECOVERY_AREA_USAGE.

Here is what is exposed by V$RECOVERY_AREA_USAGE:

SQL> set linesize 200 pagesize 1000
SQL> select * from v$recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
----------------------- ------------------ ------------------------- ---------------
CONTROL FILE                           .21                         0               1
REDO LOG                                 0                         0               0
ARCHIVED LOG                           .44                       .37              15
BACKUP PIECE                           .37                         0               1
IMAGE COPY                               0                         0               0
FLASHBACK LOG                            0                         0               0
FOREIGN ARCHIVED LOG                     0                         0               0
AUXILIARY DATAFILE COPY                  0                         0               0

8 rows selected.

and this is a good overview about space usage. But now I want to see which are those archived logs that are reclaimable and which are not. Because I want to compare with backups, standby shipping, retention, etc.

Here is the information that I want:

FILE_TYPE    FILE_NAME                                                     BYTES REC COMPLETIO
------------ ------------------------------------------------------------------- --- ---------
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_9_9v5cn1kv_.arc       81408 YES 01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_7_9v59wnsb_.arc      599552 YES 01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_4_9v57fdtn_.arc    10725376 YES 01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_8_9v5cd035_.arc      112640 YES 01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_11_9v5ffd57_.arc    2515968 YES 01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_12_9v5fqd0k_.arc     112640 YES 01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_13_9v5fz8z1_.arc     529408 YES 01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_14_9v5hgkqr_.arc      94208 YES 01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_5_9v598zsz_.arc     2663424 YES 01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_10_9v5cxtr4_.arc     312832 YES 01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_6_9v59lvv7_.arc      127488 YES 01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_17_9v5jcds8_.arc     126464 NO  01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_16_9v5j2968_.arc     634368 NO  01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_15_9v5hrgtv_.arc    2430976 NO  01-JUL-14
ARCHIVED LOG /fra/demo/archivelog/2014_07_01/o1_mf_1_18_9v5kny3b_.arc     190976 NO  01-JUL-14
BACKUP PIECE /fra/demo/backupset/2014_07_18/o1_mf_annnn_TAG20140718T... 17882624 NO  18-JUL-14
CONTROL FILE /fra/demo/controlfile/o1_mf_9v506z2f_.ctl                  10027008 NO

17 rows selected.

I’ll show the query to get that, at the end of this post. But I’m also going to show you how I came to the right query, using undocumented information. The first idea was to get the V$RECOVERY_AREA_USAGE definition and see if there is any ‘group by’ that we can get rid of. V$ views definition is exposed in V$FIXED_VIEW_DEFINITION:

SQL> select * from v$fixed_view_definition where view_name='V$RECOVERY_AREA_USAGE';

VIEW_NAME
------------------------------
VIEW_DEFINITION
--------------------------------------------------------------------------------
    CON_ID
----------
V$RECOVERY_AREA_USAGE
select fusg.file_type,
 decode(nvl2(ra.name, ra.space_limit, 0), 0, 0,
  round(nvl(fusg.space_used, 0)/ra.space_limit, 4)                          * 10
0),                                                            decode(nvl2(ra.na
...
                                    from v$archived_log,
                                   (select /*+ no_merge */ ceilasm from x$krasga
)                      where is_recovery_dest_file = 'YES'
                   and name is not null) al,
         0

but this is not enough because the view definition is limited to 4000 characters and the query is much larger than that. Note that I’ve not reproduced all the 4000 characters here.

SQL> desc v$fixed_view_definition
 Name               Null?    Type
 ------------------ -------- ----------------
 VIEW_NAME                   VARCHAR2(30)
 VIEW_DEFINITION             VARCHAR2(4000)

So how to get the whole query? The fixed view definition is hardcoded in the oracle code. So let’s try to get it from the oracle binary. I have the beginning and I will try to find the full line from that pattern:

$ strings $ORACLE_HOME/bin/oracle | grep "select fusg.file_type,"

And bingo, I have the full query. Once again, I reproduce only the first and last lines:

select fusg.file_type,                                                           decode(nvl2(ra.name, ra.space_limit, 0), 0, 0,                                   
...
union all                                                                select 'AUXILIARY DATAFILE COPY'        file_type,                               sum(adc.file_size)               space_used,                              sum(case when adc.purgable = 1 then adc.file_size                                  else 0 end)             space_reclaimable,                       count(*)                         number_of_files                     from (select case when ceilasm = 1 and adfcnam like '+%'                               then                                                                         ceil(((adfcnblks*adfcbsz)+1)/1048576)*1048576                          else                                                                         adfcnblks*adfcbsz                                                      end file_size,                                                            adfcrecl purgable                                                    from x$kccadfc,                                                                (select /*+ no_merge */ ceilasm from x$krasga)                      where bitand(adfcflg, 1) = 1                                                and adfcnam is not null)adc)fusg

Here we get the whole query in one line. Then I’ve just to put it in the SQLDeveloper formatter, replace the aggregate functions for PERCENT_SPACE_USED, PERCENT_SPACE_RECLAIMABLE and NUMBER_OF_FILES by the file name and size, and the ‘reclaimable’ flag. Here is the whole query I used to get the result above:

set linesize 200 pagesize 1000
column file_name format a100
SELECT file_type,
  name file_name,
  space_used bytes,
  CASE
    WHEN space_reclaimable>=space_used
    THEN 'YES'
    ELSE 'NO'
  END reclaimable,
  completion_time
FROM
  (SELECT 'CONTROL FILE' file_type,
    name,
    CAST(NULL AS DATE) completion_time,
    (
    CASE
      WHEN ceilasm = 1
      AND name LIKE '+%'
      THEN ceil(((block_size*file_size_blks)+1)/1048576)*1048576
      ELSE block_size       *file_size_blks
    END) space_used,
    0 space_reclaimable,
    1 number_of_files
  FROM v$controlfile,
    (SELECT /*+ no_merge */
      ceilasm FROM x$krasga
    )
  WHERE is_recovery_dest_file = 'YES'
  UNION ALL
  SELECT 'REDO LOG' file_type,
    member,
    CAST(NULL AS DATE),
    (
    CASE
      WHEN ceilasm = 1
      AND member LIKE '+%'
      THEN ceil((l.bytes+1)/1048576)*1048576
      ELSE l.bytes
    END) space_used,
    0 space_reclaimable,
    1 number_of_files
  FROM
    (SELECT group#, bytes FROM v$log
    UNION
    SELECT group#, bytes FROM v$standby_log
    ) l,
    v$logfile lf,
    (SELECT /*+ no_merge */
      ceilasm FROM x$krasga
    )
  WHERE l.group#               = lf.group#
  AND lf.is_recovery_dest_file = 'YES'
  UNION ALL
  SELECT 'ARCHIVED LOG' file_type,
    name,
    completion_time,
    (al.file_size) space_used,
    (
    CASE
      WHEN dl.rectype = 11
      THEN al.file_size
      ELSE 0
    END) space_reclaimable,
    1 number_of_files
  FROM
    (SELECT recid,
      name,
      completion_time,
      CASE
        WHEN ceilasm = 1
        AND name LIKE '+%'
        THEN ceil(((blocks*block_size)+1)/1048576)*1048576
        ELSE blocks       * block_size
      END file_size
    FROM v$archived_log,
      (SELECT /*+ no_merge */
        ceilasm FROM x$krasga
      )
    WHERE is_recovery_dest_file = 'YES'
    AND name                   IS NOT NULL
    ) al,
    x$kccagf dl
  WHERE al.recid    = dl.recid(+)
  AND dl.rectype(+) = 11
  UNION ALL
  SELECT 'BACKUP PIECE' file_type,
    handle,
    completion_time,
    (bp.file_size) space_used,
    (
    CASE
      WHEN dl.rectype = 13
      THEN bp.file_size
      ELSE 0
    END) space_reclaimable,
    1 number_of_files
  FROM
    (SELECT recid,
      handle,
      completion_time,
      CASE
        WHEN ceilasm = 1
        AND handle LIKE '+%'
        THEN ceil((bytes+1)/1048576)*1048576
        ELSE bytes
      END file_size
    FROM v$backup_piece,
      (SELECT /*+ no_merge */
        ceilasm FROM x$krasga
      )
    WHERE is_recovery_dest_file = 'YES'
    AND handle                 IS NOT NULL
    ) bp,
    x$kccagf dl
  WHERE bp.recid    = dl.recid(+)
  AND dl.rectype(+) = 13
  UNION ALL
  SELECT 'IMAGE COPY' file_type,
    name,
    completion_time,
    (dc.file_size) space_used,
    (
    CASE
      WHEN dl.rectype = 16
      THEN dc.file_size
      ELSE 0
    END) space_reclaimable,
    1 number_of_files
  FROM
    (SELECT recid,
      name,
      completion_time,
      CASE
        WHEN ceilasm = 1
        AND name LIKE '+%'
        THEN ceil(((blocks*block_size)+1)/1048576)*1048576
        ELSE blocks       * block_size
      END file_size
    FROM v$datafile_copy,
      (SELECT /*+ no_merge */
        ceilasm FROM x$krasga
      )
    WHERE is_recovery_dest_file = 'YES'
    AND name                   IS NOT NULL
    ) dc,
    x$kccagf dl
  WHERE dc.recid    = dl.recid(+)
  AND dl.rectype(+) = 16
  UNION ALL
  SELECT 'FLASHBACK LOG' file_type,
    name,
    first_time,
    NVL(fl.space_used, 0) space_used,
    NVL(fb.reclsiz, 0) space_reclaimable,
    NVL(fl.number_of_files, 0) number_of_files
  FROM
    (SELECT name,
      first_time,
      (
      CASE
        WHEN ceilasm = 1
        AND name LIKE '+%'
        THEN ceil((fl.bytes+1)/1048576)*1048576
        ELSE bytes
      END)space_used,
      1 number_of_files
    FROM v$flashback_database_logfile fl,
      (SELECT /*+ no_merge */
        ceilasm FROM x$krasga
      )
    ) fl,
    (SELECT SUM(to_number(fblogreclsiz)) reclsiz FROM x$krfblog
    )fb
  UNION ALL
  SELECT 'FOREIGN ARCHIVED LOG' file_type,
    rlnam,
    CAST(NULL AS DATE),
    (rlr.file_size) space_used,
    (
    CASE
      WHEN rlr.purgable = 1
      THEN rlr.file_size
      ELSE 0
    END) space_reclaimable,
    1 number_of_files
  FROM
    (SELECT rlnam,
      CASE
        WHEN ceilasm = 1
        AND rlnam LIKE '+%'
        THEN ceil(((rlbct*rlbsz)+1)/1048576)*1048576
        ELSE rlbct       *rlbsz
      END file_size,
      CASE
        WHEN bitand(rlfl2, 4096) = 4096
        THEN 1
        WHEN bitand(rlfl2, 8192) = 8192
        THEN 1
        ELSE 0
      END purgable
    FROM x$kccrl,
      (SELECT /*+ no_merge */
        ceilasm FROM x$krasga
      )
    WHERE bitand(rlfl2, 64) = 64
    AND rlnam              IS NOT NULL
    )rlr
  UNION ALL
  SELECT 'AUXILIARY DATAFILE COPY' file_type,
    adfcnam,
    CAST(NULL AS DATE),
    (adc.file_size) space_used,
    (
    CASE
      WHEN adc.purgable = 1
      THEN adc.file_size
      ELSE 0
    END) space_reclaimable,
    1 number_of_files
  FROM
    (SELECT adfcnam,
      CASE
        WHEN ceilasm = 1
        AND adfcnam LIKE '+%'
        THEN ceil(((adfcnblks*adfcbsz)+1)/1048576)*1048576
        ELSE adfcnblks       *adfcbsz
      END file_size,
      adfcrecl purgable
    FROM x$kccadfc,
      (SELECT /*+ no_merge */
        ceilasm FROM x$krasga
      )
    WHERE bitand(adfcflg, 1) = 1
    AND adfcnam             IS NOT NULL
    )adc
  )fusg
ORDER BY completion_time nulls last;

Don’t hesitate to add more information as I did when investigating the archivelog deletion policy.