Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Pierre.

Asked: February 25, 2021 - 11:12 am UTC

Last updated: March 04, 2021 - 2:17 am UTC

Version: 11.2.0.4 to 19.10

Viewed 100+ times

You Asked

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.


and we said...

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.

Rating

  (1 rating)

Comments

Pierre, March 04, 2021 - 4:27 pm UTC

We have following rules for Oracle backups:
1. we keep 35 days of backups.
2. backups are written to NAS
3. After 15 days backups are removed from NAS and moved to another system with NetBackup.

This means:
archived logs older than 35 days don't exist any more.
archived logs older than 15 days still exist in another system.
archived logs which are less than 15 days old are on disk.

Thanks.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database