Hello,
We would like to purge old entries in V$ARCHIVED_LOG in primary and standby databases because we have noted that some queries using V$DATABASE, V$BACKUP_SET and V$ARCHIVED_LOG especially on standby are sometimes slow.
We have control_file_record_keep_time set to 25 but on same databases we have a sometimes thousands of entries in V$ARCHIVED_LOG which are older: sometimes 2 month old and sometimes older than 1 year.
Example:
> show parameter control_file_record_keep_time
control_file_record_keep_time integer 25
> select sysdate - min(first_time) from v$archived_log;
SYSDATE-MIN(FIRST_TIME)
-----------------------
705.846887
Is there a way to run a selective purging in V$ARCHIVED_LOG in primary databases ? I know MOS Note 845361.1 but this a full purge.
Thanks.
Do those old archive logs still exist? If so, it would be worth some investigation as to why they are floating around, because that might mean some issue where your backups think they need an old archive (which might in turn mean a backup problem).
If you're confident in the backup/archive integrity, then you can first ensure the controlfile reflects reality via
RUN
{
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
DELETE NOPROMPT OBSOLETE;
}but remember, this means RMAN no longer has any record of those archivelogs. If it turns out that you DID need one of them, you're gonna be in trouble when it comes to restoring. "crosscheck" is *not* a regular command you should be running.
If that does not work, it means those archives must still be present on disk
And just to add, if your v$archive_log expanded at some stage due to a high volume of archive logs, then the number of archive logs in there wont impact the performance, but the total size (think of it like a full table scan).
If you *really* wanted a clean slate, you could recreate your controlfiles and then re-catalog a subset of archivelogs, but that seems a drastic step.