| Oracle Process freezes at 4 A.M. on GNU/Linux [message #274444] |
Tue, 16 October 2007 01:45  |
neelabh_SE
Messages: 20
Registered: October 2007
|
Junior Member |
|
|
Hello,
i have GNU/Linux on which Oracle 10g Release 10.2.0.1.0 Database server is running.
I have a process scheduled for Saturday 0010 hours.(this whole process takes at least 10 hours to complete successfully).
the problem is that Oracle process just freezes at around 4'o clock and never completes till manual intervention.
Also i tested to run the same Oracle process at different time( Sunday 1800 hours) and it finished successfully.
i did some research to check if any other job is running at that time by checking log. I found out that there are few jobs in cron.daily file ("00-logwatch", "00-makewhatis.cron", "00webalizer", "0anacron", "certwatch", "logrotate", "prelink", "rpm", "slocate.cron"," tmpwatch") which are scheduled at 0402 hours clock.
I found these timing awfully coincidental so tried to dig around this but I'm not able to figure out exact usage of these jobs and most importantly it's effect on ORACLE PROCESS.
I would appreciate if someone could help me to find out exact reason of process freezing and whether this could be attributed to Linux scheduled cron jobs.
below is the crontab file ( if it helps)
01 * * * * root run-parts /etc/cron.hourly
02 4 * * * root run-parts /etc/cron.daily
22 4 * * 0 root run-parts /etc/cron.weekly
42 4 1 * * root run-parts /etc/cron.monthly
thanks
neel
|
|
|
|
|
|
| Re: Oracle Process freezes at 4 A.M. on GNU/Linux [message #274460 is a reply to message #274457] |
Tue, 16 October 2007 02:34  |
neelabh_SE
Messages: 20
Registered: October 2007
|
Junior Member |
|
|
I'm sorry i didn't get your question.
but if I'm not mistaking you are asking about job which needs "manual intervention".
The Oracle process which is facing problem is a Data warehouse ETL process written in PL/SQL .
The jobs i had mentioned above are the Linux OS jobs.
I hope this answers your question.
if you could elaborate the question further then I'll be better able to answer your query.
thanks
neel
|
|
|
|
|
|
| Re: Oracle Process freezes at 4 A.M. on GNU/Linux [message #274500 is a reply to message #274465] |
Tue, 16 October 2007 04:33 |
neelabh_SE
Messages: 20
Registered: October 2007
|
Junior Member |
|
|
By manual intervention i mean that i have to kill the frozen process manually (i use 'kill -9 ' for stopping the script) and restart from the last point where it froze.
I'll elaborate the whole process to have a clear picture
This process is for loading data warehouse. So it takes data from transactional system and loads the tables in warehouse.The transactional data is delivered to our server as dump on Friday every evening. The system uses this dump to load warehouse.
The process has been scripted in PL/SQL stored procedures.
There is a shell script which executes SQL scripts ( the SQL script in turn calls the stored procedure from DB). So there are few points defined in the script from where the process can be restarted in case of failure. I kill the process and then explicitly execute the remaining part of the script.
One thing i forgot in my previous post, this manual activity is done only on Monday morning once I'm in office as there is no provision of monitoring of this activity over the weekend( the process starts at 0010 Saturday).
Thanks a lot for showing keen interest.
Please revert back if you have more concerns/queries.
|
|
|
|
|
|
| Re: Oracle Process freezes at 4 A.M. on GNU/Linux [message #274544 is a reply to message #274520] |
Tue, 16 October 2007 06:44 |
neelabh_SE
Messages: 20
Registered: October 2007
|
Junior Member |
|
|
Thanks Michel.
As of now the time of this activity had been changed to Sunday evening and process is completing successfully.
Since this is a production environment i can't change the time of this activity.
I'll try to search the old dump and see if i could get my hands on some useful information looking into trace files.
If i get something informative or interesting on this matter i'll definitely forward you the same.
thanks again.
regards,
neel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
