The archive_lag_target is a little more complicated than just "switch logs
after this interval" - although that's usually a good first-order
approximation. Oracle tries to anticipate the time it will take to archive the
log file as well, so the time you're setting means "switch logs so that when
the archiving is COMPLETE the database is no more than this many seconds ahead
of where the newly archived log will take you". This is particularly relevant
for archiving across a wide area network.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________________
From: De DBA [dedba@xxxxxxxxxx]
Sent: 12 May 2016 09:47
To: Jonathan Lewis; oracle-l@xxxxxxxxxxxxx
Subject: Re: What is filling the logs
Thanks, yes! That's what it was: archive_lag_target is set to 900.
The production is running in archive log mode, and has active dataguard. It is
a very busy OLTP environment and has a large amount of small log files (to
avoid "checkpoint not complete"), so the lag time is never reached. My copy is
for data-masking, which is why I replaced the many small logfiles with a couple
large ones. Remains to ponder why when archive_lag_target is set to 900 sec,
Oracle initiates log switches at 450 sec or less..
I should at this point also clarify that I inherited this environment months
after my predecessor fell ill and left without leaving much documentation. I'd
like to break a lance (again) for documenting the idiosyncrasies of the
environment so that when the inevitable happens, the replacement has some idea
what to expect..
Cheers,
Tony
On 12/05/16 17:38, Jonathan Lewis wrote:
archive_lag_target ?
cron with alter system switch logfile ?
Are you running in archivelog mode ? How much redo is actually generated
between switches ?
Can you find any SQL in v$sql with a large number of executions that might be
generating redo - e.g. from auditing a series of failed logins that never
produce sessions and therefore never appear in snapper output ?
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of De DBA [dedba@xxxxxxxxxx]
Sent: 12 May 2016 05:40
To: oracle-l@xxxxxxxxxxxxx
Subject: What is filling the logs
G'day!
I think I'm going mad.. I have a database which is completely idle. No jobs,
no job engines, no users connecting, just background processes.
log_checkpoint_timeout and log_checkpoint_interval are left default.
Yet the 1GB logfiles switch every 7 minutes on average.
I must be overlooking the obvious.. What could cause this rediculous amount
of log switches? It is a copy of the production database. I am now suspecting
that whatever causes it may also happen on the (very busy) production itself.
Any thoughts welcome...
Cheers,
Tony
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
