 Oracle 8.1.6 Solaris 7 - redo log group switch [message #131856] |
Tue, 09 August 2005 15:38  |
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 #132501 is a reply to message #131856] |
Sat, 13 August 2005 09:33  |
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 |
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 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
