Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Getachew.

Asked: September 28, 2016 - 10:03 pm UTC

Last updated: September 29, 2016 - 2:44 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

My question is about the records kept in the controlfile. Here is a general backgroud for this question:
I have a Primary database and a physical standby database both in 12C version. The redo log files on the
primary database were so undersized (512M), the primary was generating on average, 400 archives per hour.
This caused the Standby to lag behind the primary sometimes by more than 3 hours. We discussed this with
Oracle support and we were told part of the issue is that the number of archive log entries in the
controlfile (around 708 thousand). My question is: Since the records keep time is seven days, why do those
entries persist for more than 7 days? How can we lower the number of entiries without creating a controlfile?

SQL> select type, records_used, records_total from v$controlfile_record_section where type like '%LOG%';

TYPE RECORDS_USED RECORDS_TOTAL
---------------------------- ------------ -------------
REDO LOG 190 192
LOG HISTORY 293888 293888
ARCHIVED LOG 707520 707520
BACKUP REDOLOG 0 430
FLASHBACK LOG 920 2048
FOREIGN ARCHIVED LOG 0 1002


NAME TYPE VALUE
------------------------------------ ----------- ------------
control_file_record_keep_time integer 7


Thank you for taking my question.

and Connor said...

control_file_record_keep_time is the *minimum* time we must keep things, not the maximum time.

The maximum number is defined by the 'maxloghistory' from when the database was created or controlfile was created.

I think you'll need to recreate the control file at the some stage using backup to trace, but it would be worth contacting Support to see if there are any other possible strategies

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.