Re: Log switches every 4 minutes with 17MB Archivelogs despite 500MB log file size

From: Justin Mungal <justin@xxxxxxx>
To: martin.decker@xxxxxxxxxxxxxxxxx
Date: Wed, 1 Feb 2017 18:24:24 -0600

I suppose that's better than nothing, but it's a far cry from a solution. I
hope you kept the SR opened and escalated it so that you can get an actual
answer on this.

On Wed, Feb 1, 2017 at 4:04 AM, Martin Decker <
martin.decker@xxxxxxxxxxxxxxxxx> wrote:

Dear all,

there is no dataguard standby involved. No Redo Shipping. No DG Broker.

Oracle Support finally came up with a solution:
After increasing redo log files from 512M to 1024M, we no longer see redo
log switches every 4 minutes but only after 50 minutes and the archived
redo logs are no longer only 15-17MB in size but around 180MB. Of course
this is documented neither in the official documentation nor on MOS.

Unfortunately, they could not give a plausible reason for the behaviour.

Another colleague mentioned that the huge 512M granule size could also
have an effect. We are planning to reduce KSM granule size to 128M at the
next maintenance window.

We are also discussing to adapt some other RAC underscores as described in
this Note but have not decided yet: Best Practices and Recommendations for
RAC databases with SGA size over 100GB (Doc ID 1619155.1)

Best regards,
Martin

2017-01-31 17:35 GMT+01:00 Matt Adams <MAdams@xxxxxxxxxx>:

That phrase ‘MAXIMUM PERFORMANCE’ in the log file makes my thing that
standby database management might be involved, but I don’t think I’ve ever
seen quite this situation before.

We have standby databases, do not use the Data guard broker.

Is this rac cluster also the primary for a standby site?  Is it possible
that the data guard broker software is doing this somehow to force changes
from a primary to a standby database?

*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@freeli
sts.org] *On Behalf Of *Martin Decker
*Sent:* Tuesday, January 31, 2017 9:23 AM
*To:* Powell, Mark
*Cc:* oracle-l@xxxxxxxxxxxxx
*Subject:* Re: Log switches every 4 minutes with 17MB Archivelogs
despite 500MB log file size

Dear Mark, Bertrand,

log_checkpoint_interval is not net

log_checkpoint_timeout is not set

archive_lag_target is not set

no cron jobs

no dba_jobs

no dba_scheduler_jobs

"alter system" audit enabled, but no audit records generated in neither
audit_file_dest nor dba_audit_trail

fast_start_mttr_target was not set, and is now set to 300s, but does not
a difference.

Redo Log Buffer is not specified and defaults to around 500 MB, we have 4
CPU Cores with hyperthreading, so 8 visible cores per host.

More details (v$log, init.ora contents, x$kcrfstrand) can be found here:
https://drive.google.com/file/d/0B_d8Sdgtmy-rUGlIWkFFaWJhYWc/view

Regards,

Martin

2017-01-31 14:54 GMT+01:00 Powell, Mark <mark.powell2@xxxxxxx>:

What are the values database parameters log_checkpoint_interval and
log_checkpoint_timeout?

------------------------------

*From:* oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Martin Decker <martin.decker@xxxxxxxxxxxxxxxxx>
*Sent:* Tuesday, January 31, 2017 8:05:55 AM
*To:* oracle-l@xxxxxxxxxxxxx
*Subject:* Log switches every 4 minutes with 17MB Archivelogs despite
500MB log file size

Dear List,

in a RAC 2 Node cluster (12.1.0.2 with DBBP October 2016), we see redo
log switches every 4 minutes with Archivelogs filled up to 15-18 MB. Redo
Log File size is 512MB. The Application is using the instances with
singleton services so node1 has much more redo generation compared to node2.

It is unclear what is triggering the redo log switches. We can exclude
the following:

* ALTER SYSTEM SWITCH LOGFILE;

* ALTER SYSTEM ARCHIVE LOG CURRENT;

* RMAN ARCHIVELOG / DB BACKUP

* ARCHIVE_LAG_TARGET

Oracle Support SR is open, but so far no clues.

Any ideas on how to diagnose? LOG_ARCHIVE_TRACE shows that log switch is
exectued, but not who/what is triggering the switch:

*** 2017-01-19 14:13:58.496248 1380 krsl.c
krsl_log_switch_trace: Beginning Log Switch operation for MAXIMUM
PERFORMANCE:
krsk_ler_get: Read LE 16 flag 0xa T-1.S-888 low 0x000c.d4c16698 nxt
0xffff.ffffffff nab 4294967295
  switching from log 16 thread 1 sequence 888
              to log 12 thread 1 sequence 889

Best regards,

Martin

--

--

Martin Decker
Oracle 10g/11g/12c Certified Master
+43 (0) 699 1131 6463 <+43%20699%2011316463>

martin.decker@xxxxxxxxxxxxxxxxx
http://www.ora-solutions.net

--

--

[image: Image removed by sender.]

Martin Decker
Dipl.-Ing. (FH)
Oracle 10g/11g/12c Certified Master

+43 (0) 699 1131 6463 <+43%20699%2011316463>

martin.decker@xxxxxxxxxxxxxxxxx
http://www.ora-solutions.net

[image: Image removed by sender.]

**** This communication may contain privileged and/or confidential
information. If you are not the intended recipient, you are hereby notified
that disclosing, copying, or distributing of the contents is strictly
prohibited. If you have received this message in error, please contact the
sender immediately and destroy any copies of this document. ****

--
--

Martin Decker
Dipl.-Ing. (FH)
Oracle 10g/11g/12c Certified Master

+43 (0) 699 1131 6463 <+43%20699%2011316463>

martin.decker@xxxxxxxxxxxxxxxxx
http://www.ora-solutions.net

Follow-Ups:
- Re: Log switches every 4 minutes with 17MB Archivelogs despite 500MB log file size
  - From: Martin Decker

References:
- Re: Log switches every 4 minutes with 17MB Archivelogs despite 500MB log file size
  - From: Martin Decker

Re: Log switches every 4 minutes with 17MB Archivelogs despite 500MB log file size

Other related posts: