Home » RDBMS Server » Performance Tuning » DATABASE DOWN WHEN MANY USERS LOG IN
DATABASE DOWN WHEN MANY USERS LOG IN [message #233979] Sat, 28 April 2007 12:33 Go to next message
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 #233984 is a reply to message #233979] Sat, 28 April 2007 13:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
This is not an error message, it is just for information.
If you don't use replication, you can ignore it.

Use Statspack during these peak hours to find what's going on.

Regards
Michel

Re: DATABASE DOWN WHEN MANY USERS LOG IN [message #233985 is a reply to message #233984] Sat, 28 April 2007 13:29 Go to previous messageGo to next message
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 #233988 is a reply to message #233985] Sat, 28 April 2007 13:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
So do StatsPack.
Use v$session_wait to see what your sessions are waiting on.

Regards
Michel
Re: DATABASE DOWN WHEN MANY USERS LOG IN [message #234067 is a reply to message #233979] Sun, 29 April 2007 05:44 Go to previous messageGo to next message
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
Senior Member
Maybe you should increase your processes and/or sessions parameter to a higher value.

Maybe there are to many people logging into the database at peakhours.
Re: DATABASE DOWN WHEN MANY USERS LOG IN [message #234069 is a reply to message #234067] Sun, 29 April 2007 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Why not advice to increase memory, increase number of CPU, increase number of disks, increase...
With no clue any advice is WRONG.

Regards
Michel
Re: DATABASE DOWN WHEN MANY USERS LOG IN [message #234071 is a reply to message #234069] Sun, 29 April 2007 06:52 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
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 Go to previous messageGo to next message
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 #234086 is a reply to message #234074] Sun, 29 April 2007 14:42 Go to previous messageGo to next message
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
Senior Member
Hee Michel,

No problem. You just passed your exam in diplomacy with your friendly and kind response Smile

Thanks for taking the time to explain your point of view.

Most posts I see from you are very informative to me, so I'm glad it's not personal Smile

And indeed the answer of Melaku indicates that my solution would not work. This probably is the proof of your "silver-bullet" theory. Next time I hope I have the patience to first ask some more details if not supplied.

Best regards,

Martijn
Re: DATABASE DOWN WHEN MANY USERS LOG IN [message #234142 is a reply to message #233979] Mon, 30 April 2007 02:46 Go to previous messageGo to next message
MELAKU
Messages: 32
Registered: April 2007
Location: ETHIOPIA
Member
Hi

I just statspack report when we just face similar problem today. See the attachment. Any support please
Re: DATABASE DOWN WHEN MANY USERS LOG IN [message #234148 is a reply to message #233979] Mon, 30 April 2007 03:30 Go to previous messageGo to next message
colly_liu
Messages: 6
Registered: April 2007
Junior Member
you can check you system resource,if the memory not enough .
Re: DATABASE DOWN WHEN MANY USERS LOG IN [message #234268 is a reply to message #233979] Mon, 30 April 2007 11:55 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #234338 is a reply to message #234326] Mon, 30 April 2007 23:53 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
So, i think your problem area might be the frequent log switches.

No,
OP database is not in ARCHIVE LOG MODE and database running in NO ARCHIVE LOG mode.

Regards
Taj
Re: DATABASE DOWN WHEN MANY USERS LOG IN [message #234346 is a reply to message #234338] Tue, 01 May 2007 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Taj, even in NOARCHIVELOG mode you can have log switches problem, if DBWR is not able to checkpoint during the time all logs are reused.

Melaku, I think you should have a look at:
- Top 5 Timed Events: "enq: TX - row lock contention": 25.19% of DB Time which shows that 25% of its time a session is waiting for a row held by another session
- 27.11 parses per second this is too much

Regards
Michel





Re: DATABASE DOWN WHEN MANY USERS LOG IN [message #234423 is a reply to message #233979] Tue, 01 May 2007 09:48 Go to previous messageGo to next message
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 #234431 is a reply to message #234423] Tue, 01 May 2007 10:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1) Just an information message saying you reserved some space and don't use it.
2) YES, definitively.

Now search in the trace file the origin of the deadlock and correct your application.

Regards
Michel
Re: DATABASE DOWN WHEN MANY USERS LOG IN [message #235514 is a reply to message #233979] Mon, 07 May 2007 00:40 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
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
Previous Topic: Index Issues
Next Topic: insufficient privileges when generating explain plan for a synonym
Goto Forum:
  


Current Time: Sat Dec 14 16:45:13 CST 2024