OraFAQ Forum: Server Administration » Redo log switches in every 3 mins

Home » RDBMS Server » Server Administration » Redo log switches in every 3 mins (Oracle Database 10g Release 10.2.0.3.0 - Production,Microsoft Windows 2003)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Redo log switches in every 3 mins [message #567751]

Fri, 05 October 2012 01:20

AnilKampiri
Messages: 69
Registered: September 2012
Location: Chennai

Member

Dear All,

Today i noticed one problem with my database,my redologs switches in every 3mins,i also noticed there is no more transaction changes happening in database but still redo switches.

Fri Oct 05 06:10:05 2012
Thread 1 advanced to log sequence 79244
Current log# 2 seq# 79244 mem# 0: D:\ORADATA\ORACI\REDO02.LOG
Fri Oct 05 06:12:16 2012
Thread 1 advanced to log sequence 79245
Current log# 1 seq# 79245 mem# 0: D:\ORADATA\ORACI\REDO01.LOG
Fri Oct 05 06:14:28 2012
Thread 1 advanced to log sequence 79246
Current log# 3 seq# 79246 mem# 0: D:\ORADATA\ORACI\REDO03.LOG
Fri Oct 05 06:15:51 2012
Thread 1 advanced to log sequence 79247
Current log# 2 seq# 79247 mem# 0: D:\ORADATA\ORACI\REDO02.LOG
Fri Oct 05 06:16:59 2012
Thread 1 advanced to log sequence 79248
Current log# 1 seq# 79248 mem# 0: D:\ORADATA\ORACI\REDO01.LOG
Fri Oct 05 06:19:08 2012
Thread 1 advanced to log sequence 79249
Current log# 3 seq# 79249 mem# 0: D:\ORADATA\ORACI\REDO03.LOG
Fri Oct 05 06:21:22 2012

Can any one advice me why redo switch happening,any internal problem causes redo to switch please advice.

Regards,
Anil

Report message to a moderator

Re: Redo log switches in every 3 mins [message #567752 is a reply to message #567751]

Fri, 05 October 2012 01:37

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

i also noticed there is no more transaction changes happening in databas

How? Post what you did.
Before, Please read How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

What is your archive_lag_target parameter value?
Check jobs (dba_jobs, dba_scheduler% views).
Oracle does not switch if it does not need it (that is there are changes) or if you do not ask it to do it.

Regards
Michel

Report message to a moderator

Re: Redo log switches in every 3 mins [message #567767 is a reply to message #567752]

Fri, 05 October 2012 03:26

AnilKampiri
Messages: 69
Registered: September 2012
Location: Chennai

Member

Dear Michel,

"Quote"

i also noticed there is no more transaction changes happening in database

Kindly find what i did,

SQL> SELECT ADDR,XID AS "txn id", XIDUSN AS "undo seg", XIDSLOT AS "slot",
  2      XIDSQN AS "seq", STATUS AS "txn status"
  3      FROM V$TRANSACTION;

ADDR     txn id             undo seg       slot        seq txn status
-------- ---------------- ---------- ---------- ---------- ----------------
329DCBC8 02001700CB282400          2         23    2369739 ACTIVE

SQL> select sid,serial#,taddr,osuser from v$session where taddr='329DCBC8';

       SID    SERIAL# TADDR    OSUSER
---------- ---------- -------- ------------------------------
       380       8139 329DCBC8 atiacoh

SQL> select sql_text||' # '||hash_value
  2   from v$sqltext
  3  where (hash_value,address) in
  4  (select sql_hash_value,sql_ADDRESS from
  5  v$session where sid in (380))
  6  order by piece;

no rows selected

there is only one transaction happening,i dont think this will cause my redo to full.


please find archive_lag_taget parameter.

SQL> show parameter archive_lag_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
SQL>

Regards,
Anil

Report message to a moderator

Re: Redo log switches in every 3 mins [message #567768 is a reply to message #567767]

Fri, 05 October 2012 03:34

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

there is only one transaction happening

Which does not mean it does not generate GB of redo/undo.
Check USED_UBLK of v$transaction.
In addition, you then see only the current on-going transactions and the history of them.

Regards
Michel

[Updated on: Fri, 05 October 2012 03:34]

Report message to a moderator

Re: Redo log switches in every 3 mins [message #567797 is a reply to message #567768]

Fri, 05 October 2012 07:30

AnilKampiri
Messages: 69
Registered: September 2012
Location: Chennai

Member

Dear Michel,

Sorry for little delay in respose,i just monitored current trasaction can u please adivce below transaction really impact more redo switch based on USED_UBLK.


SQL> 1
  1* select ADDR,STATUS,START_SCN,PRV_XID,recursive,USED_UBLK,XIDSQN from gv$transaction
SQL> /

ADDR     STATUS            START_SCN PRV_XID          REC  USED_UBLK     XIDSQN
-------- ---------------- ---------- ---------------- --- ---------- ----------
329B6DD8 ACTIVE           5195055740 0000000000000000 NO           1     318666
329B7D7C ACTIVE           5195353662 0000000000000000 NO           1     284890
329DD104 ACTIVE           5193047179 0000000000000000 NO           9    1627239
329EE5F0 ACTIVE           5194799420 0000000000000000 NO           1    1456477
32A00A80 ACTIVE           5195368652 0000000000000000 NO           1     889994
32A24E74 ACTIVE           5195197115 0000000000000000 NO           2    2287717
32A26DAC ACTIVE           5192099121 0000000000000000 NO           4    1584660
32A6CBE4 ACTIVE           5195251441 0000000000000000 NO           1      54249

8 rows selected.

Also please note still redo switches in every 3 mins

Fri Oct 05 10:20:53 2012
Thread 1 advanced to log sequence 79357
  Current log# 3 seq# 79357 mem# 0: D:\ORADATA\ORACI\REDO03.LOG
Fri Oct 05 10:23:14 2012
Thread 1 advanced to log sequence 79358
  Current log# 2 seq# 79358 mem# 0: D:\ORADATA\ORACI\REDO02.LOG
Fri Oct 05 10:25:43 2012
Thread 1 advanced to log sequence 79359
  Current log# 1 seq# 79359 mem# 0: D:\ORADATA\ORACI\REDO01.LOG
Fri Oct 05 10:28:10 2012
Thread 1 advanced to log sequence 79360
  Current log# 3 seq# 79360 mem# 0: D:\ORADATA\ORACI\REDO03.LOG
Fri Oct 05 10:30:37 2012
Thread 1 advanced to log sequence 79361
  Current log# 2 seq# 79361 mem# 0: D:\ORADATA\ORACI\REDO02.LOG
Fri Oct 05 10:33:08 2012
Thread 1 advanced to log sequence 79362
  Current log# 1 seq# 79362 mem# 0: D:\ORADATA\ORACI\REDO01.LOG

Regards,
Anil

Report message to a moderator

Re: Redo log switches in every 3 mins [message #567800 is a reply to message #567797]

Fri, 05 October 2012 08:19

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

There you see you have many transactions and not just one.
I bet you have many and many small transactions.
You can use Log Miner and sum redo_length grouping by transaction.

Regards
Michel

Report message to a moderator

Previous Topic:	TNS:listener does not currently know of service
Next Topic:	ARC file size too small

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 02 23:09:19 CST 2024