Home » RDBMS Server » Server Administration » Oracle REDO log switching - query / help
Oracle REDO log switching - query / help [message #230174] Wed, 11 April 2007 02:35 Go to next message
kumarpk108
Messages: 7
Registered: December 2006
Junior Member
Hi all,

We are running Oracle 9.2.0.7 / SAP 4.6C.
Recently we performed the oracle version
upgrade from 9.2.0.6 -> 9.2.0.7.

Our REDO log grouping as follows:

LOGFILE
GROUP 11 (
'/oracle/C11/origlogA/log_g11m1.dbf',
'/oracle/C11/mirrlogA/log_g11m2.dbf'
) SIZE 400M,
GROUP 12 (
'/oracle/C11/origlogB/log_g12m1.dbf',
'/oracle/C11/mirrlogB/log_g12m2.dbf'
) SIZE 400M,
GROUP 13 (
'/oracle/C11/origlogA/log_g13m1.dbf',
'/oracle/C11/mirrlogA/log_g13m2.dbf'
) SIZE 400M,
GROUP 14 (
'/oracle/C11/origlogB/log_g14m1.dbf',
'/oracle/C11/mirrlogB/log_g14m2.dbf'
) SIZE 400M

Last year we had an issue with Oracle redo log switching
where in the logs were switched in the following way.

Group Thread Sequence Size Nr of Archive
Nr Nr Nr (bytes) Members Status

12 1 84606 419430400 2 YES
14 1 84607 419430400 2 YES
13 1 84608 419430400 2 YES
11 1 84609 419430400 2 NO

12 -> 14 -> 13 -> 11 which is logB -> logB -> logA -> logA

Our understanding is that oracle should write to one redo log,
once filled switch to another redo log on a different file system.
This is not the case for C11. We switch (to write in new)
& read (to archive out) on the same file system as specified above.

Last year we did an experiment and dropped and recreated LOGFILE
groups and the redo log sequence was put in correctly. i.e
12 -> 13 -> 14 -> 11 which is logB -> logA -> logB -> logA

When we examined the redo log sequence recently, it is again
showing the older way - which is
12 -> 14 -> 13 -> 11 which is logB -> logB -> logA -> logA
and that is not the best option.

We would like to know what made this switch happen
even though we manually dropped/recreated the
logfile groups and ensured the switching in an optimal way.
Is there any parameter or v$ table entries to examine whether
this type redo log switching has any issues.

Also what will be implications of this type of
switching - will it adversely affect the log
switching and hence the wait time if we leave
it like this. What kind of performance implication
can this result in?

What would be the recommended steps to effect
the change to logfile groups so that the redo log
switching happens in a more optimal way i.e

12 -> 13 -> 14 -> 11 which is logB -> logA -> logB -> logA

Any help on the subject will be greatly appreiciated.

Thanks
Kumar
Re: Oracle REDO log switching - query / help [message #230189 is a reply to message #230174] Wed, 11 April 2007 03:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Redo log number or file system doesn't matter for Oracle.
It takes the first available one order by their first_change# or switch_change#.
So order may change if there is a problem during archiving.

Regards
Michel
Re: Oracle REDO log switching - query / help [message #231155 is a reply to message #230189] Sun, 15 April 2007 03:30 Go to previous messageGo to next message
kumarpk108
Messages: 7
Registered: December 2006
Junior Member
Thanks Michel for your clarification.
Re: Oracle REDO log switching - query / help [message #231161 is a reply to message #230189] Sun, 15 April 2007 05:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I meant "file system time", sorry for the missing word.
Michel
icon5.gif  Re: Oracle REDO log switching - query / help [message #250244 is a reply to message #230174] Mon, 09 July 2007 05:14 Go to previous messageGo to next message
rupaliss
Messages: 6
Registered: July 2007
Location: Mumbai
Junior Member

Hi

I am new to this forum, I needed some help regarding Redo Log Files...Is there any possibilities to set a parameter tht can occur log switch automatically before it get full other than ALTER SYSTEM SWITCH LOGFILE

Thnx
Rupali S
Re: Oracle REDO log switching - query / help [message #250248 is a reply to message #250244] Mon, 09 July 2007 05:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
archive_lag_target

Regards
Michel
Re: Oracle REDO log switching - query / help [message #250254 is a reply to message #250248] Mon, 09 July 2007 05:46 Go to previous messageGo to next message
rupaliss
Messages: 6
Registered: July 2007
Location: Mumbai
Junior Member

Hi Michel,

Thanks for your fast response, can you tell me in detail how can I use this parameter, I have no time to sit try this parameter, I have to let my boss know about such case as soon as possible...

He wanted to know if we can switch log file before it gets full say every 2 hours or 5 hours

Thnkx
Re: Oracle REDO log switching - query / help [message #250265 is a reply to message #250254] Mon, 09 July 2007 06:09 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, you can, it is the purpose of this parameter.
If you just followed the link, it would take less time to read it than to post your message.

Regards
Michel
Previous Topic: Creating physical Standby database
Next Topic: Preventing programmer changing data
Goto Forum:
  


Current Time: Sat Dec 03 22:10:48 CST 2016

Total time taken to generate the page: 0.11308 seconds