Home » RDBMS Server » Performance Tuning » Oracle 8.1.6 Solaris 7 - redo log group switch
icon9.gif  Oracle 8.1.6 Solaris 7 - redo log group switch [message #131856] Tue, 09 August 2005 15:38 Go to next message
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
Senior Member
Oracle 8.1.6 on Solaris 7 with 12 disks and 4 cpus' (I know, old stuff)
Why does Enterprise Manager show the status of our log groups as:
group 1 - active size 102400K
group 2 - current size 102400K
group 3 - active size 1048756K
group 4 - active size 524288K
group 5 - active size 524288K
The groups 3, 4, and 5 were onlined to perform a 1/2 billion row update in 350 tables with commits that occur every 1/2 million rows updated.

I thought only one can be current and usually only another one active?? (being archived? although I set noarchivelog mode for this task)

Do I need to bring more checkpoint processes or log writter processes online? Is this why a log switch stalls the database?
Re: Oracle 8.1.6 Solaris 7 - redo log group switch [message #131862 is a reply to message #131856] Tue, 09 August 2005 16:42 Go to previous messageGo to next message
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
Senior Member
Now it shows;

group 1 active
group 2 active
group 3 current
group 4 inactive
group 5 inactive
Re: Oracle 8.1.6 Solaris 7 - redo log group switch [message #132501 is a reply to message #131856] Sat, 13 August 2005 09:33 Go to previous messageGo to next message
skempins
Messages: 16
Registered: June 2005
Location: Florida
Junior Member
When we were on Sun and investigating a particular performance problem I noticed that log switches could take a long time which slowed our transaction. We ended up making our redo logs bigger to minimize the number of log switches.

About the redo log status; (I may be way off base here) in long running transactions that span log files, previous log files may be needed in the event of a rollback. So, this status prevents the DBA from removing a log file that may be needed.
Re: Oracle 8.1.6 Solaris 7 - redo log group switch [message #132609 is a reply to message #132501] Mon, 15 August 2005 03:12 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
You are right, in that you can only have 1 redo log group 'current' (as this is the rlg beig written to) however there can be multiple 'active' redo logs i.e. cannot be overwritten yet due to being archived / still writing to disk. You only need to deal with this is you are getting lots of 'checkpoint not complete' warnings.
Jim
Re: Oracle 8.1.6 Solaris 7 - redo log group switch [message #132658 is a reply to message #131856] Mon, 15 August 2005 09:41 Go to previous messageGo to next message
alliejane
Messages: 59
Registered: July 2005
Location: Glasgow
Member
Hi,

Just checking a few things..

Why have logfile groups 4,5 got 500m members when 1,2 have 100m and group 3 is much bigger. Not saying it's wrong, just not sure why you would do this.

If a number of log groups are showing as active then I'm guessing your switching frequently, can you tell me on avarage how often that is?

Are you seeing "checkpoint not complete" in your alert log, if you are and it's frequent then your database is going into mini stalls and this will slow performance down.

Quote:

About the redo log status; (I may be way off base here) in long running transactions that span log files, previous log files may be needed in the event of a rollback. So, this status prevents the DBA from removing a log file that may be needed.


Kind of.. the redo log files are used to protect oracle blocks that haven't been written to disk yet, periodically DBWR will pick up dirty blocks from the buffer cache (blocks who's image in memory is different from their image on disk) and write them to the datafiles. Once that has happened then the protection in the redo logfile is not required, however an active status of a redo logfile group means that not all the blocks protected by that file have been written to disk yet, so Oracle will still need it just in case the database aborts and the transactions need to be replayed.

Just a last point - Redo Log Files have very little to do with rollback, when a user initiates a rollback statement then the "rollback/undo" segments are used, redo logs are used to protect commited tranactions just in case of an abort - or for longer term physical recovery if Archive Logging is truned on.


Allie
Re: Oracle 8.1.6 Solaris 7 - redo log group switch [message #132691 is a reply to message #131856] Mon, 15 August 2005 13:39 Go to previous message
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
Senior Member
Thanks!
Previous Topic: Row chaining and Local Tablespace
Next Topic: Solaris V880 W/32 G ram Oracle 10.1.2 - parameters?
Goto Forum:
  


Current Time: Fri May 27 18:09:52 CDT 2022