Access redo generation After migrating from 9.2.0 to 10.2.0.4 [message #475366] |
Tue, 14 September 2010 11:03 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
abhi_sri
Messages: 20 Registered: September 2010 Location: India
|
Junior Member |
|
|
We had our production database hosted on Oracle 9.2.0. Few months back we have migrated it to Oracle 10.2.0.4.0.
After Migration I have noticed that redo generation has become very very high. In earlier case no. of log files generating in production hours were around 30 where as after migration it become around 200 files per day. I have run statspack report on this database. Report is saying that db block change & disk write is become very high. Parameter timed_statistics has also been set to FALSE. Even then there is not any reduction on no. of log file generation. I had used import export for upgrading the databases. Please provide a solution for this problem.
Thanks In advance for all your favours....
|
|
|
|
Re: Access redo generation After migrating from 9.2.0 to 10.2.0.4 [message #475371 is a reply to message #475367] |
Tue, 14 September 2010 12:05 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
abhi_sri
Messages: 20 Registered: September 2010 Location: India
|
Junior Member |
|
|
Thanks Michel for such an early reply...
just to let you know that all the parameters & memory structure of instance is same as of the earlier one (before upgrade) & later one(after upgrade). So baseline is almost same. Now redo generation is become so high that it is hard to manage. Some times database suddenly stops working because redo log space become full.
|
|
|
|
Re: Access redo generation After migrating from 9.2.0 to 10.2.0.4 [message #475373 is a reply to message #475371] |
Tue, 14 September 2010 12:09 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote: So baseline is almost same
Baseline is not just parameters it is about the workload and how it is handles by Oracle. All statistics, all waits, and so on.
Quote:Some times database suddenly stops working because redo log space become full.
So enlarge the space allocated to logs.
Also enlarge your redo logs.
There are much more things that happen in the background in 10g and even more in 11g.
Regards
Michel
[Updated on: Tue, 14 September 2010 12:10] Report message to a moderator
|
|
|
Re: Access redo generation After migrating from 9.2.0 to 10.2.0.4 [message #475454 is a reply to message #475373] |
Wed, 15 September 2010 04:20 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
abhi_sri
Messages: 20 Registered: September 2010 Location: India
|
Junior Member |
|
|
Thank for the quick responces...
The migration activity for this particular database is completed in one weekend. So the no of users are same. Workload is also same because this database is for a particular application. And the number of uses using it are same. Yes we can add the space in archredo directory & that is what we have done. But I want to know the rootcause that why it is generating huge redo logs.
|
|
|
|
Re: Access redo generation After migrating from 9.2.0 to 10.2.0.4 [message #475487 is a reply to message #475459] |
Wed, 15 September 2010 05:19 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
abhi_sri
Messages: 20 Registered: September 2010 Location: India
|
Junior Member |
|
|
Hi,
Could you be more specific about the statistics you are reffering, so that I can provide some more information. As I have earlier told that the parameters & memory structure for the instance is same before upgrade & after upgrade. Workload is also same. Queries running in the session is also same before & after upgrade. So for me redo generation sould be somehow same after I have upgraded but it is not same. One more thing I want to add that after doing the analysis on statspack report, I found that after upgrade log file switch waits is become very high. Its value is 143 millisecond for a perticular time.
|
|
|
Re: Access redo generation After migrating from 9.2.0 to 10.2.0.4 [message #475492 is a reply to message #475487] |
Wed, 15 September 2010 05:32 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:Could you be more specific about the statistics you are reffering, so that I can provide some more information.
Statements, number of executions, number of blocks read, modified, generated logs, wait events, cpu, elapsed time...
You cannot provide them if you didn't record them before for instance in Statspack tables.
Quote:As I have earlier told that the parameters & memory structure for the instance is same before upgrade & after upgrade. Workload is also same. Queries running in the session is also same before & after upgrade. So for me redo generation sould be somehow same after I have upgraded but it is not same.
But Oracle binaries are not so there is no reason it should be the same and I already explained it in a previous answer.
Quote:I found that after upgrade log file switch waits is become very high. Its value is 143 millisecond for a perticular time.
Depends on your IO subsystem and its load.
Regards
Michel
|
|
|
|
Re: Access redo generation After migrating from 9.2.0 to 10.2.0.4 [message #475554 is a reply to message #475525] |
Wed, 15 September 2010 10:08 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
abhi_sri
Messages: 20 Registered: September 2010 Location: India
|
Junior Member |
|
|
Hello Mahesh,
Its just spreadout through out the day.
On very first glance the cause of the problem is looks like transaction is become high, that is why redo generation become high. But It is not the case, even it cant be possible that transactions become so high in one day & that too on the day when I have upgraded when the application & the number of user are same.
|
|
|
|
|
|