oracle not shuting down as expected

[Rick Garland]

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!

[T G Manikandan · Accepted solution]

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

[Steven E. Protter]

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

[Graham Cameron_1]

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

[Rick Garland]

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)

[Rick Garland]

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?

[Volker Borowski]

Hi,

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

agtctl shutdown

Hope this helps
Volker

[Stan_17]

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

[Stan_17]

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

[Rick Garland]

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!