| DATABASE DOWN WHEN MANY USERS LOG IN [message #233979] |
Sat, 28 April 2007 12:33  |
MELAKU
Messages: 32
Registered: April 2007
Location: ETHIOPIA
|
Member |
|
|
Our database cease to work particularly during peak hour or when there are many transactions and many users log in. During this time clients can not connect to the database. It is only possible to connect only internally from the server. Please any help on this.
When I see the alert log file there is an error message as:
replication_dependency_tracking turned off (no async multimaster replication found).
See few lines of the alert file attached with this log
|
|
|
|
|
|
| Re: DATABASE DOWN WHEN MANY USERS LOG IN [message #233985 is a reply to message #233984] |
Sat, 28 April 2007 13:29  |
MELAKU
Messages: 32
Registered: April 2007
Location: ETHIOPIA
|
Member |
|
|
|
We have replicated databse at a separate server. Formerly we were able to use the second databse when the server 1 of the primary database down. We do have server clustering/ redundacy. But now when we down server 1 the clustering for server 2 databse do not works. That we will explore it further in the future. But my worry now is on the primary server. We need to restart the databse when the problem occurs. Sometimes restarting the databse will not solve, hence we need to reboot the server. You see since we are working on Banking industry having a break for about 30 to 45 minutes is so terrible for branches.
|
|
|
|
|
|
|
|
|
|
| Re: DATABASE DOWN WHEN MANY USERS LOG IN [message #234071 is a reply to message #234069] |
Sun, 29 April 2007 06:52 |
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
|
Senior Member |
|
|
I understand what you are saying. And ... to be honest I think you are right.
However the tone of your reply, at least to me, is not very friendly.
I think/hope you have a lot of experience in the database field, so you probably have seen this problem a hundreth times and allready know that it could be anything. So you advice/ask to give some more details.
Me... on the other hand, I have not so much experience. I have had the same problem once, and found that the processes parameter was to low (I found this after some guidance from a more experienced DBA). So...today i read the thread and think ... I give a solution which have worked for me. I just like to help people.
The answer from your side however discourages me to answer at all, since I don't like being talked to like that.
So ... in short....:
Yes you are totally right. Some more details on the problem are in order. But maybe you can be a bit more friendly.
Best Regards,
Martijn
|
|
|
|
| Re: DATABASE DOWN WHEN MANY USERS LOG IN [message #234074 is a reply to message #234071] |
Sun, 29 April 2007 08:40 |
 |
Michel Cadot
Messages: 68651
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
You are right, give answers, don't be afraid or feel throw out by mines.
I have a direct style and a zero degree in diplomacy, I'm afraid. I don't say things personnaly just on a technical point of view, so always takes it as this not as an attack.
Concerning this post, I saw many problems and many "gurus" giving a "silver bullet" at first glance, just wasting our time and sometimes getting things worst trying their successives "solutions" when a good measure and analyze take more effort (at first sight) but lead to a good solution.
I repeat, sometimes theses "solutions" really hurted, so I now have some epidermal reaction when I find in the same case again.
Really sorry if I offended you.
Regards
Michel
|
|
|
|
| Re: DATABASE DOWN WHEN MANY USERS LOG IN [message #234084 is a reply to message #233979] |
Sun, 29 April 2007 14:02 |
MELAKU
Messages: 32
Registered: April 2007
Location: ETHIOPIA
|
Member |
|
|
Dear Michel and Martin,
I am very thank you for your concern and advices. I am greatly sorry that you two were misunderstood each other. But I hope this is professional discussion that both of you raised. As you said once I faced similar problem and I increase sessions, transactions and processes almost double just before a month. Problem was solved and we were doing fine. When simialr problem with slight scenario change happens, I raised these three a little bit. But this time this do not solve the problem. That is why I logged the problem. Today is Sunday, tomorrow I will try to check the statspack.
Thank you both of you. I will wait also your unwavering support.
Melaku K.
|
|
|
|
|
|
|
|
|
|
| Re: DATABASE DOWN WHEN MANY USERS LOG IN [message #234268 is a reply to message #233979] |
Mon, 30 April 2007 11:55 |
MELAKU
Messages: 32
Registered: April 2007
Location: ETHIOPIA
|
Member |
|
|
I have tried to see the statspack report as to where the problem lies am not capable of identifying it. Simialr problem is ocuuring every peak hour. I usually pass this period by restarting the databse. My customers are many that during restarting the database they are shouting. PLEASE I NEED YOUR STRONG SUPPORT.
Thank U
|
|
|
|
| Re: DATABASE DOWN WHEN MANY USERS LOG IN [message #234326 is a reply to message #233979] |
Mon, 30 April 2007 18:24 |
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
|
Senior Member |
|
|
hi melaku,
Your alert log file is pointing frequent log switches, which is perhaps thing affecting your database performance..
i.e log switches at
Thu Apr 26 18:53:25 2007
Thu Apr 26 18:54:02 2007
Thu Apr 26 18:54:38 2007
Thu Apr 26 18:54:54 2007
Thu Apr 26 18:57:12 2007
Thu Apr 26 18:57:49 2007
Thu Apr 26 18:59:45 2007
Thu Apr 26 19:00:42 2007
Thu Apr 26 19:00:58 2007
Thu Apr 26 19:01:58 2007
Each time when log switche occurs, oracle has do perform some tasks which may lead the sessions wait..
So, i think your problem area might be the frequent log switches.
Am i at right way, Seniors?
But before making any changes, get the proper advice from the senior DBAs of this great helpful forum..
|
|
|
|
|
|
|
|
| Re: DATABASE DOWN WHEN MANY USERS LOG IN [message #234423 is a reply to message #233979] |
Tue, 01 May 2007 09:48 |
MELAKU
Messages: 32
Registered: April 2007
Location: ETHIOPIA
|
Member |
|
|
Thank U all
Michel I am looking what you suggested me. In my alert log file I have seen two case: 1) db_recovery_file_dest_ size has 2GB space but it is 0% used
2. At some place of the alert log, it says DEADLOCK DETECTED. Do you think my problem related with either of these two?
My problem is still not solved. I manged by using the secondary server database, then restart primary db and halt package of database of the secondary server. It is very inconvenient. Here are below those points that I mention earlier;
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
//
Fri Apr 27 16:26:09 2007
Private_strands 1 at log switch
Thread 1 advanced to log sequence 43136
Current log# 1 seq# 43136 mem# 0: /u03/HIBRETDB/redo01.log
Fri Apr 27 16:27:23 2007
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/HIBRETDB/udump/hibretdb_ora_25674.trc.
Fri Apr 27 16:33:30 2007
Private_strands 1 at log switch
|
|
|
|
|
|
| Re: DATABASE DOWN WHEN MANY USERS LOG IN [message #235514 is a reply to message #233979] |
Mon, 07 May 2007 00:40 |
MELAKU
Messages: 32
Registered: April 2007
Location: ETHIOPIA
|
Member |
|
|
Hi all,
As per suggestions given by you, I checked on the existence of applications/ queries that make the database busy. We do have queries/ reports to be refreshed by client. When these users refresh reports, queries passed to the databse. These queries are not tuned and sometimes took more than 10 minutes to retrieve data. Hence I have created a query that kill sessions that waiting for more than 5 minutes where the object is business object (i.e. report). I managed to do with that, but last Friday, the sluggish nature of the databse happens once. Though my attempt in killing these sessions seem ok, still there is a problem with the databse. Is killing session free up those occupied cash memory? so that physical memory reading of the database minimizes? I need your support. If there is queries that I need to use inorder to free up cash memory and RAM
|
|
|
|
| Re: DATABASE DOWN WHEN MANY USERS LOG IN [message #235518 is a reply to message #235514] |
Mon, 07 May 2007 00:54 |
|
Michel Cadot
Messages: 68651
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Sorry but you need someone in site to help you.
We can't determine what points in your application or planning you have to change as we don't have and can't gave all the required informations.
If you can't do it yourself, you have to hire a consultant to make an audit.
Btw, if you can kill sessions at any time because it takes too long, then they do not useful job and so why do you start them? Declare a priori they are too big and then just don't start them.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
