I have an Oracle 8.1.6 database running on HP-UX 11.0 and every evening before I begin the backup, I shutdown Oracle. After the backup, I start it back up. My problem is that I have several external applications that use ODBC connections to the Oracle database, and several users now running terminal services from their homes connecting to the database. The problem is that if even one process still shows active in Oracle, dbshut hangs until that process is killed. If that process is not killed prior to the scheduled startup time, the startup also fails. Is there an easy way to check for and kill open processes prior to executing dbshut?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
dbshut hangs waiting for logins to complete.
- Thread starter tman138
- Start date
If there is something common about the process of the connections, you can do a ps -e | grep <process common> and then kill the process.
I am assuming (but am not sure) that an ODBC connection would have a process that has the letters LOC in the process.
Here is a script I use when I have to kill a lot of processes with a common thread. Be careful with this. I don't know what the implications are for an Oracle database if you kill ODBC connections like this:
PID=`ps -e | grep $1 | awk '{ print $1 }' > /home/utility/bin/pid.txt`
do kill -9 $LINE
done
syntax for the script would be scriptname.sh LOC
It will kill ALL process that have the letters LOC in it.
Not elegant, but it works.
I am assuming (but am not sure) that an ODBC connection would have a process that has the letters LOC in the process.
Here is a script I use when I have to kill a lot of processes with a common thread. Be careful with this. I don't know what the implications are for an Oracle database if you kill ODBC connections like this:
PID=`ps -e | grep $1 | awk '{ print $1 }' > /home/utility/bin/pid.txt`
do kill -9 $LINE
done
syntax for the script would be scriptname.sh LOC
It will kill ALL process that have the letters LOC in it.
Not elegant, but it works.
- Thread starter
- #3
Thanks. That's exactly what I was thinking to do since I can do that manually before shutdown with sucess. I already developed my ps statement yesterday and I'll work on the process kill portion today. Using LOC won't work for me since the Oracle listener connection reports as a local connection, but it reports as LOCAL=YES while my problem children report as LOCAL=NO. Hopefully with a few mods to your code I'll be in business. I am new to Unix and this parsing syntax was going to be a challenge. Thanks again!
- Thread starter
- #5
OK - I just got around to trying this and I can't even seem to execute even just the ps portion. Shouldn't ps -e | grep $1 return just the PID? I get a shell error:
sh: 1: Parameter not set.
I think I my syntax is incorrect. I am also trying to understand your line that syntax for the script would be scriptname.sh LOC do you mean that I should name the script in the syntax "scriptname.sh" and that LOC is passsed to the script as a variable? Where is the variable handled in the actual script?
Sorry to be such a pest.
sh: 1: Parameter not set.
I think I my syntax is incorrect. I am also trying to understand your line that syntax for the script would be scriptname.sh LOC do you mean that I should name the script in the syntax "scriptname.sh" and that LOC is passsed to the script as a variable? Where is the variable handled in the actual script?
Sorry to be such a pest.
Oops, I forgot a line! I'm very sorry. This is what the script should be:
PID=`ps -e | grep $1 | awk '{ print $1 }' > /home/utility/bin/pid.txt`
# This line cats the file created in the line above and #says while
# catting the file, read each line.
cat /export/home/utility/bin/pid.txt | while read LINE
# This line tells the system to do a kill -9 on each PID #that is
# listed in the pid.txt file. There will alwasy be an error #message
# because the grep for the process is also listed but by the time
# the kill command is executed, the grep has already died.
do kill -9 $LINE
# This line closes up the do line.
done
Syntax:
Let's say you want to kill all processes that have LOCAL=NO.
The syntax for invoking the script would be:
Let's say the script is named killit.sh.
killit.sh LOCAL=NO.
This will kill all processes that LOCAL=NO in them.
PID=`ps -e | grep $1 | awk '{ print $1 }' > /home/utility/bin/pid.txt`
# This line cats the file created in the line above and #says while
# catting the file, read each line.
cat /export/home/utility/bin/pid.txt | while read LINE
# This line tells the system to do a kill -9 on each PID #that is
# listed in the pid.txt file. There will alwasy be an error #message
# because the grep for the process is also listed but by the time
# the kill command is executed, the grep has already died.
do kill -9 $LINE
# This line closes up the do line.
done
Syntax:
Let's say you want to kill all processes that have LOCAL=NO.
The syntax for invoking the script would be:
Let's say the script is named killit.sh.
killit.sh LOCAL=NO.
This will kill all processes that LOCAL=NO in them.
- Thread starter
- #7
Getting closer...Once I worked out all of the permissions, the script now executes without error.. that's the good news. The bad news is that the ps statement doesn't return anything and as a result the text file is empty. Just ps -e | grep {LOCAL) returns only the grep process. When I use ps -ef | grep oracleptmn (the sender of the processes I wish to kill) I get all processes (both LOCAL=NO and LOCAL=YES) so I think I need to use CUT to filter out all but the PID using ps -ef. I'm on the right track now though.
Once I get this going I plan to create a script file with the parameter hard coded and run it from my shutdown script which runs as a cron job.
Once I get this going I plan to create a script file with the parameter hard coded and run it from my shutdown script which runs as a cron job.
- Thread starter
- #8
Stuck again!
OK this is what I have:
I modified the script & ps statement (& the resulting pid.txt file)as follows:
##!/usr/bin/sh -x
psPID=`ps -ef | grep oracleptmn | cut -c 11-14,45,60-69 | awk '{print $1}' > /users/home/oracle/admin/pid.txt`
cat /users/home/oracle/admin/pid.txt | while read LINE
do
# proceed only if the 13th character is 'N'
if [ "`echo $LINE | awk -F: '{print $13}' -`" = "N" ] ; then
PID=`echo $LINE | awk -F: '{print $1}' -`
#cat PID > /users/home/oracle/admin/pid2.txt
kill -9 PID
done
The output into pid.txt is:
7212
LOCAL=NO)
7454tmn
7204LOCAL=NO)
but ever since I added the if statement, I get a syntax error at line 12: Done is not expected.
What's wrong with my syntax?
OK this is what I have:
I modified the script & ps statement (& the resulting pid.txt file)as follows:
##!/usr/bin/sh -x
psPID=`ps -ef | grep oracleptmn | cut -c 11-14,45,60-69 | awk '{print $1}' > /users/home/oracle/admin/pid.txt`
cat /users/home/oracle/admin/pid.txt | while read LINE
do
# proceed only if the 13th character is 'N'
if [ "`echo $LINE | awk -F: '{print $13}' -`" = "N" ] ; then
PID=`echo $LINE | awk -F: '{print $1}' -`
#cat PID > /users/home/oracle/admin/pid2.txt
kill -9 PID
done
The output into pid.txt is:
7212
LOCAL=NO)7454
tmn7204
LOCAL=NO)but ever since I added the if statement, I get a syntax error at line 12: Done is not expected.
What's wrong with my syntax?
Hi,
last three psotings are the right ones from Oracle Point of View.
Oracle could only shut down gracefully if no connection is on. You need to shutdown immediate or if this won't work shutdown abort. But abort "crashes" the Db and it needs recovery after startup.
Did you think about online-backup ???
You need some more space for the archived logs, but its better recoverable in time.
regards
Uwe
last three psotings are the right ones from Oracle Point of View.
Oracle could only shut down gracefully if no connection is on. You need to shutdown immediate or if this won't work shutdown abort. But abort "crashes" the Db and it needs recovery after startup.
Did you think about online-backup ???
You need some more space for the archived logs, but its better recoverable in time.
regards
Uwe
- Thread starter
- #14
Thanks to all who continue to post, but problem is resolved. I took a two pronged approach and kill all non-local user processors still attached at shutdown time, and changed dbshut to shutdown Oracle immediately. I haven't had a single problem in over 2 weeks now.
...T
...T
- Status
Similar threads
- Locked
- Question
- Locked
- Question