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!

[Michael Fairclough]

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.

[Yogeeraj_1]

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

[Jean-Luc Oudart]

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

[Rick Garland]

Many thanks for the extra info.

[Rick Garland]

Close up