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.indxSQL> 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_reclaimableselect 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$SESSIONGV$SQL_SHARED_CURSORGV$STREAMS_CAPTUREV$RECOVERY_AREA_USAGEGV$ACTIVE_SESSION_HISTORYGV$WLM_PCMETRICV$RMAN_BACKUP_SUBJOB_DETAILSV$BACKUP_DATAFILE_SUMMARYV$BACKUP_CONTROLFILE_SUMMARYGV$IOSTAT_FILEGV$GOLDENGATE_CAPTURE11 rows selected.
联系客服
