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)
Redo log switches in every 3 mins [message #567751] Fri, 05 October 2012 01:20 Go to next message
AnilKampiri
Messages: 69
Registered: September 2012
Location: Chennai
Member

Sad Sad Sad 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
Re: Redo log switches in every 3 mins [message #567752 is a reply to message #567751] Fri, 05 October 2012 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: Redo log switches in every 3 mins [message #567767 is a reply to message #567752] Fri, 05 October 2012 03:26 Go to previous messageGo to next message
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





Re: Redo log switches in every 3 mins [message #567768 is a reply to message #567767] Fri, 05 October 2012 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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

Re: Redo log switches in every 3 mins [message #567800 is a reply to message #567797] Fri, 05 October 2012 08:19 Go to previous message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: TNS:listener does not currently know of service
Next Topic: ARC file size too small
Goto Forum:
  


Current Time: Tue Mar 19 00:28:10 CDT 2024