How to verify if archived log deletion policy is correctly applied? | Databases at CERN

What is the best way to handle archived logs deletion in environments with standby and downstream capture databases? One could use own scripts, to delete for example all backed up archived logs, older than n days. But better way, will be to set RMAN archived log deletion policy, because then, additional options could be specified, to delete archived logs which are not only backed up n times, but also applied or shipped to other databases in the environment. Then, with proper settings, we should not end up with standby database which needs already deleted archived log... Of course unless there are some bugs causing problems with correct handling of archived logs deletion, so it’s good idea to double-check your configuration, before real deletion occurs, which usually happens when there is space pressure in FRA.

One way to do it, is to analyse the results of query:

 

SQL> select * from v$recovery_area_usage;

 

It shows the percentage of FRA used space by several types of files, including archived logs. Also, there is a column showing percentage of space which can be freed. By comparing these results with the list of already backed up archived logs, as well as archived logs applied/sent to standby/downstream capture databases, we could see if our policy is properly applied.

Our example archived log deletion policy looks like:

 

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO 'SBT_TAPE';

 

In our test database (11.2.0.4) we could see:

 

SQL> select * from v$recovery_area_usage where file_type = 'ARCHIVED LOG';

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
ARCHIVED LOG                       1.63                      1.63             462

 

In this case, archived logs backup runs every hour and thanks to real-time apply, our standby database is only about 1 second behind the primary. So with archived log deletion policy set as above, only archived logs already backed up should be eligible for deletion. I’ve checked, that there are some not backed up, but because result of this query shows only 2 digits after decimal point, we cannot be sure what Oracle thinks about them. What we can do is to look at definition of v$recovery_area_usage view, modify defining query to change the scale and get more detailed percentages. Definition of this view could be checked using following query:

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

 

And here is the interesting part - definition of this view is rather long and it cannot be displayed by this query. After checking the definition of v$fixed_view_definition we could see, that it won’t show us more than 4000 characters:

SQL> select view_definition
     from v$fixed_view_definition
     where view_name = 'GV$FIXED_VIEW_DEFINITION';

VIEW_DEFINITION
----------------------------------------------------------------------------------
select i.inst_id,kqfvinam,kqftpsel from x$kqfvi i, x$kqfvt t where i.indx = t.indx

SQL> select max(length(kqftpsel)) maxsize from x$kqfvt;

       MAXSIZE
--------------
          4000

 

So, the question is if we can find full definition of v$recovery_area_usage view? And where?

Maybe in Oracle libraries? Let's have a look… I’ve chosen a string, which looks to be unique from the part of v$recovery_area_usage definition which is visible in v$fixed_view_definition and…voilà!

$ cd $ORACLE_HOME/lib
$ strings libserver11.a | grep fusg.space_reclaimable
select fusg.file_type,           decode(nvl2(ra.name, ra.space_limit, 0), 0, 0,
(…)

 

Indeed, the definition is very long, but now we could modify it as we want. After removing all but archived logs part, modyfing the scale for percentages and adding part with count of reclaimable files, we could end up with more detailed results:

SQL> SELECT fusg.file_type,
       DECODE(nvl2(ra.name, ra.space_limit, 0), 0, 0, ROUND(NVL(fusg.space_used, 0)       /ra.space_limit, 6) * 100) percent_space_used,
       DECODE(nvl2(ra.name, ra.space_limit, 0), 0, 0, ROUND(NVL(fusg.space_reclaimable, 0)/ra.space_limit, 6) * 100) percent_space_reclaimable,
       nvl2(ra.name, fusg.number_of_files, 0) number_of_files, number_of_reclaimable_files
     FROM v$recovery_file_dest ra,
       (SELECT 'ARCHIVED LOG' file_type,
         SUM(al.file_size) space_used,
         SUM(
         CASE
           WHEN dl.rectype = 11
           THEN al.file_size
           ELSE 0
         END) space_reclaimable,
         COUNT(
         CASE
           WHEN dl.rectype = 11
           THEN 1
           ELSE null
         END) number_of_reclaimable_files,
         COUNT(*) number_of_files
       FROM
         (SELECT recid,
           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
       ) fusg;

FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES NUMBER_OF_RECLAIMABLE_FILES
------------ ------------------ ------------------------- --------------- ---------------------------
ARCHIVED LOG             1.6308                    1.6294             462                         459

 

Of course we could further modify the query, to get exactly which archived logs are still not eligible for deletion, to check if these are the ones, which are not backed up, not applied on standby or not shipped to downstream capture, but I wanted to show you the way, how to check the definition of fixed view, which exceeds capabilities of v$fixed_view_definition, especially that v$recovery_area_usage is not the only one longer than 4000 characters:

SQL> select view_name from v$fixed_view_definition where length(view_definition) = 4000;

VIEW_NAME
------------------------------
GV$SESSION
GV$SQL_SHARED_CURSOR
GV$STREAMS_CAPTURE
V$RECOVERY_AREA_USAGE
GV$ACTIVE_SESSION_HISTORY
GV$WLM_PCMETRIC
V$RMAN_BACKUP_SUBJOB_DETAILS
V$BACKUP_DATAFILE_SUMMARY
V$BACKUP_CONTROLFILE_SUMMARY
GV$IOSTAT_FILE
GV$GOLDENGATE_CAPTURE

11 rows selected.