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