RE: What is filling the logs

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
_at_jloracle

________________________________________
From: De DBA [dedba_at_tpg.com.au]
Sent: 12 May 2016 09:47
To: Jonathan Lewis; oracle-l_at_freelists.org
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
> _at_jloracle
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org [oracle-l-bounce@freelists.org] on behalf of De DBA [dedba@tpg.com.au]
> Sent: 12 May 2016 05:40
> To: oracle-l_at_freelists.org
> 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
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--