Re: What is filling the logs

From: De DBA <dedba_at_tpg.com.au>
Date: Thu, 12 May 2016 18:47:42 +1000
Message-ID: <5734432E.2080601_at_tpg.com.au>

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_at_freelists.org] on behalf of De DBA [dedba_at_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
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu May 12 2016 - 10:47:42 CEST