topic Re: oracle not shuting down as expected in Operating System - HP-UX

oracle not shuting down as expected

Rick Garland — Tue, 21 Oct 2003 08:50:01 GMT

Hi all:

Got Oracle 8.1.6 running on HPUX 11.00.
Have a DEV instance that is not in archive mode so the backups are done cold. Every so often during the pre-exec phase of shutting down the database it will hang, making the backup fail and the database to be in an unusable state. Need to shutdown abort to get it to startup again.

For the pre-exec it is doing a shutdown immediate. Seems that a connection is still established and the database will not complete it shutdown.

Any ideas on what may be the actual cause and possible solutions?

Many thanks!

Re: oracle not shuting down as expected

T G Manikandan — Tue, 21 Oct 2003 08:53:03 GMT

check the alertlog of the instance and check what is it doing?

Is the SMON cleaning up the temporary segments.
How much time does it take for the shutdown.

What is the size of the database and log files.

Revert

Re: oracle not shuting down as expected

Steven E. Protter — Tue, 21 Oct 2003 08:58:53 GMT

Even on a shutdown immediate, oracle's dbshut command can sometimes hang because of commits and users. ITs not really hanging, its jutt not working the way its supposed to.

It go so annoying to me I developed a shutdown script that reads the /etc/oratab file and more efficiently shuts down the database.

#!/sbin/sh
if [ "$LOGNAME" = "root" ]
then
echo "User root detected...shelling to oracle"
/usr/bin/su - oracle -c "/usr/contrib/bin/shut.oracle"
exit 1
fi

if [ "$LOGNAME" = "oracle" ]
then
oktorun="Y"
echo "User oracle detected"
fi

if [ "$LOGNAME" = "oraoper" ]
then
oktorun="Y"
echo "User oraoper detected"
fi

if [ "$oktorun" = "Y" ]
then
echo "user is authorized...$LOGNAME"
else
echo "user must be oracle or oraoper: $LOGNAME"
exit 1
fi

SetOptions=$-
set +u

export ORATAB=/etc/oratab; [ -f $ORATAB ] ||
export ORATAB=/var/opt/oracle/oratab

sidfile=/utmp/sids.file

# awk -F: '/^[^#*]+:/ {print $1 " " $3}' $ORATAB > $sidfile
# awk -F: '/^[^#*]+:/ {print $1}' $ORATAB > $sidfile

cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;;#comment-line in oratab
*)

if [ "`echo $LINE | awk -F: '/^[^#*]+:/ {print $3}' -`" = "Y" ] ; then
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
echo "checking oracle sid: $ORACLE_SID "
/usr/contrib/bin/shutsid.sh $ORACLE_SID
fi

esac

done

shutsid.sh

#!/sbin/sh

ORACLE_SID=$1
. /usr/contrib/bin/setOraProfile

sqlplus internal << EOFTOP1
select * from v\$database;
shutdown immediate;
exit;
EOFTOP1

exit;

Why does it work better? Because it doesn't use dbshut. I've used it for years but Oracle has griped about it when told.

Your choice.

SEP

Re: oracle not shuting down as expected

Graham Cameron_1 — Tue, 21 Oct 2003 09:00:53 GMT

It is standard practice here to do:

shutdown abort
startup restrict
shutdown

I was horrified when I first saw this but 3 years on I'm getting used to seeing it.

Oracle are aware of what we do.

Historically this was due to latch problems with OPS, but although OPS is long gone, we still use it.

-- Graham

Re: oracle not shuting down as expected

Rick Garland — Tue, 21 Oct 2003 09:04:04 GMT

Here is the snipet of the alert log as requested.

Shutting down instance (immediate)
License high water mark = 25
Tue Oct 21 02:07:51 2003
SHUTDOWN: waiting for active calls to complete.
Tue Oct 21 07:53:07 2003
Shutting down instance (abort)
License high water mark = 25
Instance terminated by USER, pid = 2598
Tue Oct 21 07:53:11 2003
Starting ORACLE instance (normal)

Re: oracle not shuting down as expected

Rick Garland — Tue, 21 Oct 2003 09:06:58 GMT

You are right, I am frightened to use shutdown abort all of the time.

Possible modifying the pre-exec to shutdown abort after so much time on shutdown immediate.

Any other ideas?

Re: oracle not shuting down as expected

Volker Borowski — Tue, 21 Oct 2003 09:33:23 GMT

Hi,

is the agent for Enterprise Manager running ?
If yes, stop it first.

agtctl shutdown

Hope this helps
Volker

Re: oracle not shuting down as expected

Stan_17 — Tue, 21 Oct 2003 12:34:53 GMT

Hi,

Usually this happens if smon cleaning up temp segments or any active connections rolling back huge transactions.

check used_ublk in v$transaction for any transaction being rollbacked. That gives you an approximate timing as to when it would finish rollback.

possible solution would be, shutdown abort, followed by startup restrict then shutdown immediate.

-
Stan

Re: oracle not shuting down as expected

Stan_17 — Tue, 21 Oct 2003 12:40:51 GMT

Forgot to mention, if its smon cleaning up temp segments, use tempfiles (lmt) for the temporary tbs, to avoid ST enqueue contention on the dictionary tables.

-
Stan

Re: oracle not shuting down as expected

Rick Garland — Tue, 21 Oct 2003 13:12:31 GMT

Right now I am going with a shutdown abort, startup restrict, shutdown.

My goal is to modify the script so that it will time the amount of time it takes shutdown immediate. If too long the script will automatically revert to a shutdown abort.

Many thanks for the help!

Re: oracle not shuting down as expected

Michael Fairclough — Tue, 21 Sep 2004 16:39:04 GMT

The key is "SHUTDOWN: waiting for active calls to complete." There should be a process id listed in the alert log just after this message. That's the process that needs to be killed. Here's one of mine:

Fri Jul 23 00:10:14 2004
Active call for process 24050 user 'oraprd' program 'oracle@drerpdb01 (TNS V1-V3)'
SHUTDOWN: waiting for active calls to complete.

In this case the process 24050 needs to be killed. Once it's killed the database will shutdown normally.

Re: oracle not shuting down as expected

Yogeeraj_1 — Wed, 22 Sep 2004 00:29:57 GMT

hi Rick,

The database is waiting for pmon to clean up processes, but pmon is unable to clean them. The client connections to the server are causing the shutdown immediate or normal to hang. Killing them allows pmon to clean up and release the associated Oracle processes and resources.

You can write a script to locate and kill any client connections to the database at the Unix level, as follows:

a. Locate any client connections to the database using ps, and grep for any processes belonging to this .

Example: ps -ef | grep YD

b. Look for processes that include a 'LOCAL=NO' designation.

Example: ora816 235 1 0 Sep 22 1:01 oracleYD (LOCAL=NO)

c. Kill the Unix process(es) with the 'LOCAL=NO' designation.

Example: kill -9 235

hope this helps!

regards
Yogeeraj

Re: oracle not shuting down as expected

Jean-Luc Oudart — Wed, 22 Sep 2004 04:06:46 GMT

Rick,

The normal procedure would be to shutdown or shutdown immeiate, but as mentioned it could hang if you have session still running (lon op ?) or a (very) long backout transaction in progress.

shutdown abort will avoid that but the database would be in a status I don't think you can use for cloning.
shutdown abort + startup + shutdown
You may still have to replay (rollback) the aborted transaction and this too can take sometimes.

The alternative is to use a watchdog for your shutdown immediate.
The watchdog process will check for the shutdown script still running after a while (!) , kill it and run a shutdown abort instead.
Not the nicest solution I agree. but the abort means you could start your backup.

Regards,
Jean-Luc

PS : if this is just a DEV database, you may just stick to shutdown abort + startup + shutdown as mentioned in an other post

Re: oracle not shuting down as expected

Rick Garland — Wed, 22 Sep 2004 08:58:18 GMT

Many thanks for the extra info.

Re: oracle not shuting down as expected

Rick Garland — Wed, 22 Sep 2004 09:00:13 GMT

Close up