This was first published on https://blog.dbi-services.com/drilling-down-vrecoveryareausage (2014-07-18)
Republishing here for new followers. The content is related to the the versions available at the publication date
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.
Hi Franck,
you extracted the view definition from a 12 database right? In 11g, the last block in UNION doesn’t exist because it concerns auxiliary datafile copy and the base x$ view doesn’t exist.
Regards,
Nicolas
Hi Nicolas, Yes that’s right it’s from 12c.