Skip to Main Content
  • Questions

  • Log File Switches Twice as Often as ARCHIVE_LAG_TARGET

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Connor McDonald

Thanks for the question, Andy.

Asked: February 17, 2017 - 5:51 pm UTC

Last updated: February 18, 2017 - 5:22 am UTC

Version: 11.2.0.3 (PSU 11.2.0.3.7)

Viewed 1000+ times

You Asked

I have an odd issue occurring on my primary pre-production database server. In both my production and pre-production environments, we have the ARCHIVE_LAG_TARGET parameter set to force a log switch at regular intervals. I know this is not a modern-day best practice and we intend to zero out that parameter soon, but for now it is what it is. In production, the parameter is set to 300 (five minutes) and works as expected:


SQL> show parameter archive_lag_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 300

SQL> SELECT first_time
2 FROM v$log_history
3 WHERE first_time > SYSDATE - 1/4
4 ORDER BY first_time DESC;

FIRST_TIME
------------------------
2/10/2017 5:18:06 PM
2/10/2017 5:13:05 PM
2/10/2017 5:08:05 PM
2/10/2017 5:03:04 PM
2/10/2017 4:58:04 PM
2/10/2017 4:53:04 PM
2/10/2017 4:48:04 PM
2/10/2017 4:43:06 PM
2/10/2017 4:38:06 PM
2/10/2017 4:33:06 PM
2/10/2017 4:28:05 PM
2/10/2017 4:23:05 PM
2/10/2017 4:18:05 PM
2/10/2017 4:13:04 PM
2/10/2017 4:08:04 PM
2/10/2017 4:03:03 PM
2/10/2017 3:58:03 PM
2/10/2017 3:53:06 PM
2/10/2017 3:48:06 PM
2/10/2017 3:43:05 PM

FIRST_TIME
------------------------
2/10/2017 3:38:05 PM
2/10/2017 3:33:04 PM
2/10/2017 3:28:04 PM
2/10/2017 3:23:04 PM
2/10/2017 3:18:03 PM
2/10/2017 3:13:03 PM
2/10/2017 3:08:05 PM
2/10/2017 3:03:05 PM
2/10/2017 2:58:05 PM
2/10/2017 2:53:04 PM
2/10/2017 2:48:04 PM
2/10/2017 2:43:04 PM
2/10/2017 2:38:03 PM
2/10/2017 2:33:03 PM
2/10/2017 2:28:02 PM
2/10/2017 2:23:02 PM
2/10/2017 2:18:05 PM
2/10/2017 2:13:04 PM
2/10/2017 2:08:04 PM
2/10/2017 2:03:04 PM
2/10/2017 1:58:03 PM

FIRST_TIME
------------------------
2/10/2017 1:53:03 PM
2/10/2017 1:48:03 PM
2/10/2017 1:43:03 PM
2/10/2017 1:38:02 PM
2/10/2017 1:33:02 PM
2/10/2017 1:28:02 PM
2/10/2017 1:23:04 PM
2/10/2017 1:18:04 PM
2/10/2017 1:13:04 PM
2/10/2017 1:08:03 PM
2/10/2017 1:03:03 PM
2/10/2017 12:58:03 PM
2/10/2017 12:53:02 PM
2/10/2017 12:48:02 PM
2/10/2017 12:43:02 PM
2/10/2017 12:38:02 PM
2/10/2017 12:33:01 PM
2/10/2017 12:28:01 PM
2/10/2017 12:23:04 PM
2/10/2017 12:18:03 PM
2/10/2017 12:13:03 PM

FIRST_TIME
------------------------
2/10/2017 12:08:02 PM
2/10/2017 12:03:02 PM
2/10/2017 11:58:02 AM
2/10/2017 11:53:02 AM
2/10/2017 11:48:01 AM
2/10/2017 11:43:01 AM
2/10/2017 11:38:01 AM
2/10/2017 11:33:00 AM
2/10/2017 11:28:00 AM


In pre-production, the parameter is set to 7200 (120 minutes). However, the log file switches are occurring hourly. The actual log switch always happens at half the duration defined in the parameter on this server, no matter what value is set (e.g. when I set it to 1800, the switch occurs every 15 minutes):


SQL> show parameter archive_lag_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 7200

SQL> SELECT first_time
2 FROM v$log_history
3 WHERE first_time > SYSDATE - 1/4
4 ORDER BY first_time DESC;

FIRST_TIME
------------------------
2/10/2017 4:02:16 PM
2/10/2017 3:02:13 PM
2/10/2017 2:02:14 PM
2/10/2017 1:02:14 PM
2/10/2017 12:02:12 PM


I have searched the DBMS_SCHEDULER, cron, and DBA_SOURCE and verified that there are no procs/jobs doing any log file switches, and I have tried setting the ARCHIVE_LAG_TARGET parameter to 0 and back to 7200 with the same results. All other date/time-related functionality (DBMS_SCHEDULER, etc.) works as expected. This is true for every database on the pre-production server, so I am beginning to suspect there is something unusual going on at the server level, but I’m not sure where to start looking (both servers are on RHEL 5.11). Any ideas as to what might be happening?

Thank you in advance for your assistance!

and Connor said...

You can have a look in your alert.log.

If someone/something is running: alter system archive log current

then you'll see it.

Unfortunately, if someone runs: alter system switch logfile

then you dont see the command in the alert log (just the effects).

But to double check, you could run:

audit alter system

just to double check that no script etc is doing it, but the fact that the frequency does indeed alter when you change the setting suggests its not anything external.

If you disable archive_lag_target, does the switching disappear altogether ? (I'm just thinking in terms of a workaround you could do it manually via cron/scheduler etc).

It could be worth having a chat to Support about.



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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.