| Database hangs during the night ...... [message #147869] |
Mon, 21 November 2005 07:59  |
bes2005
Messages: 33
Registered: September 2005
Location: UK
|
Member |
|
|
We have an Oracle9i development server on Win 2003 server which is used for development purposes. It works fine during the day then it stops or hangs during the night. When we try to access it from SQL Plus it takes forever with no response and doesn't display any errors.
We restart the Win 2003 server next morning and it works fine again. Sounds funny but it is getting annoying to reboot the server every morning.
Any suggestions how to solve this problem?
Thanx in advance.
|
|
|
|
| Re: Database hangs during the night ...... [message #147886 is a reply to message #147869] |
Mon, 21 November 2005 09:28  |
sunil_v_mishra
Messages: 506
Registered: March 2005
|
Senior Member |
|
|
hi,
Here are few Question in my mind
[1] It is your development database or live production database?
[2] Is your Database running in 24x7 ?
[3] How many user are using that database?
[4] At what time & day you are taking backup & is it cold backup or hot backup?
[5] How much RAM you have?
[6] What is the size of your SGA?
[7] Find in night which query is running in your database and using maximum temperory space.
note:- your issue is related to performance tunning and the limited information that you had given in your quetion had very wide answer... which required to check many things other than what i had asked...
Most of time in issue like yours .. many ideal user is connected to your database ....because of this also you must be having problem to connect to the database.....
Regards
Always Friend Sunilkumar
|
|
|
|
| Re: Database hangs during the night ...... [message #148024 is a reply to message #147869] |
Tue, 22 November 2005 05:44 |
bes2005
Messages: 33
Registered: September 2005
Location: UK
|
Member |
|
|
Thanks for your reply.
Here are my answers for your questions:
[1] It is your development database or live production database?
-- it is a development database
[2] Is your Database running in 24x7 ?
-- need to run it 24x7 in order to avoid restart next morning. Nobody is using it during the night. maybe any developers from home in the evening.
[3] How many user are using that database?
-- about 20 developers
[4] At what time & day you are taking backup & is it cold backup or hot backup?
-- before we live later afternoon every day
[5] How much RAM you have?
-- over 1GB
[6] What is the size of your SGA?
-- about 200 MB
[7] Find in night which query is running in your database and using maximum temperory space.
-- trying to find it out after restart the server
Any further suggestions about what and where should i pay attention for possible problems. Aso if it is is related to any parameter settings?
Thank you all
|
|
|
|
| Re: Database hangs during the night ...... [message #148035 is a reply to message #148024] |
Tue, 22 November 2005 07:25 |
sunil_v_mishra
Messages: 506
Registered: March 2005
|
Senior Member |
|
|
hi,
As per your answer to my question ... i think you should increase the size of SGA ... size of SGA is very less for 20 user ...only 200MB is not sufficient. so increase it to 600MB.
Note:- 60-65% of ram should be used by oracle for better performance.
One more thing i want to know ... are you running any big process in evening against DB? Are that process is going to release the SGA after completion of their work?
Regards
Sunilkumar
[Updated on: Tue, 22 November 2005 07:46] Report message to a moderator |
|
|
|
| Re: Database hangs during the night ...... [message #148078 is a reply to message #148035] |
Tue, 22 November 2005 10:06 |
sunil_v_mishra
Messages: 506
Registered: March 2005
|
Senior Member |
|
|
hi,
Note:- 60-65% of ram should be used by oracle for better performance
the above statement was in context with 1 GB ram... earlier in my first reply i asked
5] How much RAM you have? to bes2005 his reply was 1gb
And there is 20 user for his Database.
so 600 MB of SGA will more or less solve this problem ,,, its a development database and .. and frankly speaking windows o/s need time to time rebooting to perform nicely campare to unix based o/s ....
Regards
sunilkumar
|
|
|
|
|
|
|
|
| Re: Database hangs during the night ...... [message #148429 is a reply to message #147869] |
Thu, 24 November 2005 03:54 |
bes2005
Messages: 33
Registered: September 2005
Location: UK
|
Member |
|
|
Thank you all of you for your suggestions. Problem already solved. I made the following changes:
1. Increased SGA_MAX_SIZE = 500MB
2. set SQL.EXPIRE_TIME = 5
3. increased SESSION parameter
4. increased ENQUE_RESOURCES and transactions parameter
5. set timed_statistics = FALSE
It is working fine and is not stopping during the night for the tine being.
Thanx
|
|
|
|
| Re: Database hangs during the night ...... [message #148493 is a reply to message #147869] |
Thu, 24 November 2005 09:04 |
bes2005
Messages: 33
Registered: September 2005
Location: UK
|
Member |
|
|
Hi again:
In addition to the above steps I also performed the following:
On the database server console, the message is displayed:
Erro in apps event appears:
ORACLE Instance XXXXXXXX - Cannot allocate log. Archival required
This error means that Oracle wanted to perform an archive log switch, but was unable to because the next online redo log has not yet been archived. The database will continued to function (no data will be lost), but all transactions were blocked (database appeared to hang) until the current archive log is written out and a redo log can be allocated.
Automatic archiving was disabled and database hanged. This can be verified by issuing the command:
SQL> ARCHIVE LOG LIST;
If this displays ‘Automatic archival Disabled’ then automatic archiving has been disabled. Archive the redo logs manually by issuing the command:
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
iN long term solution you need to modify the init<SID>.ora file and set the parameter LOG_ARCHIVE_START to True.
HAPPY ORACLEing.........
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
