Home » Infrastructure » Linux » Oracle Process freezes at 4 A.M. on GNU/Linux
Oracle Process freezes at 4 A.M. on GNU/Linux [message #274444] Tue, 16 October 2007 01:45 Go to next message
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 #274457 is a reply to message #274444] Tue, 16 October 2007 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Archived logs file system full?
Quote:

never completes till manual intervention

Which one?

Regards
Michel
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 Go to previous messageGo to next message
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 #274465 is a reply to message #274460] Tue, 16 October 2007 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I wanted to say "which manual intervention"?
This will help us to guess what was the problem.

Regards
Michel
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 Go to previous messageGo to next message
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 #274520 is a reply to message #274500] Tue, 16 October 2007 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When you have a process that seems to hang, activate a trace on it to see what it is doing or waiting for.
See How to set trace for others sessions

Regards
Michel
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 Go to previous message
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

Previous Topic: Oracle server & client connection problem
Next Topic: Importing dump to oracle database
Goto Forum:
  


Current Time: Thu Mar 28 05:48:34 CDT 2024