Oracle Hang while shuting down immedaite

[vienpb]

Hi,
Now I am using:
- Oracle version: 8.1.7.4
- OS: HP-UX 11i
- Server: HP 9000/800
- Middle ware: BTuxedo 8.1
- Database size: 400GB
- Backing up by HP device and OmniBack Software.
- Execute Script to shutdown Database Pre-Backing up.
- contents of script:
###### Oracle Shutdown Script for Application ##################
lsnrctl stop
sqlplus internal/password << OraShutdown
alter system set log_archive_max_processes=10;
alter system archive log stop;
alter system set log_archive_max_processes=1;
alter system archive log start;
shutdown immediate
exit
OraShutdown
##################################################

Our backup policy: We backup full offline database at 1 o'clock PM every Sunday, It last 3 years and all offbackup offline sessions is very good.
But three weeks ago untill now, Backup offline can not shutdown Database.
The alert file as follow:
----------------------------------------------------------------------------------------
ALTER SYSTEM SET log_archive_max_processes=10;
Sat Oct 1 01:08:58 2005
archiving is disabled
ARC2 started with pid=37
ARC2: Archival started
ARC3 started with pid=38
ARC3: Archival started
ARC4 started with pid=40
ARC4: Archival started
ARC5 started with pid=42
ARC5: Archival started
ARC6 started with pid=43
ARC6: Archival started
ARC7 started with pid=44
ARC7: Archival started
ARC8 started with pid=45
ARC8: Archival started
ARC9 started with pid=46
Sat Oct 1 01:08:58 2005
ARC0: STARTING ARCH PROCESSES COMPLETE
Sat Oct 1 01:08:58 2005
ARC9: Archival started
Sat Oct 1 01:08:59 2005
ARCH shutting down
Sat Oct 1 01:08:59 2005
ARCH shutting down
Sat Oct 1 01:08:59 2005
ARC0: Archival stopped
Sat Oct 1 01:08:59 2005
ARC1: Archival stopped
Sat Oct 1 01:08:59 2005
ARCH shutting down
Sat Oct 1 01:08:59 2005
ARCH shutting down
Sat Oct 1 01:08:59 2005
ARC2: Archival stopped
Sat Oct 1 01:08:59 2005
ARC6: Archival stopped
Sat Oct 1 01:08:59 2005
ARCH shutting down
Sat Oct 1 01:08:59 2005
ARCH shutting down
Sat Oct 1 01:08:59 2005
ARC7: Archival stopped
Sat Oct 1 01:08:59 2005
ARC5: Archival stopped
Sat Oct 1 01:08:59 2005
ARCH shutting down
Sat Oct 1 01:08:59 2005
ARCH shutting down
Sat Oct 1 01:08:59 2005
ARC8: Archival stopped
Sat Oct 1 01:08:59 2005
ARCH shutting down
Sat Oct 1 01:08:59 2005
ARC9: Archival stopped
Sat Oct 1 01:08:59 2005
ARC3: Archival stopped
Sat Oct 1 01:08:59 2005
ARCH shutting down
Sat Oct 1 01:08:59 2005
ARC4: Archival stopped
Sat Oct 1 01:08:59 2005
ALTER SYSTEM SET log_archive_max_processes=1;
archiving is enabled
ARCH: STARTING ARCH PROCESSES
ARCH: STARTING ARCH PROCESSES COMPLETE
Sat Oct 1 01:09:00 2005
ARC0: Shutdown aborted (current state is 3)
Sat Oct 1 01:10:04 2005
Restarting dead background process EMN0
EMN0 started with pid=37
Sat Oct 1 01:10:08 2005
Shutting down instance (immediate)
License high water mark = 396
SHUTDOWN: waiting for active calls to complete.
------------------------------------------------------------------------------------------

It hang 4 hours, can not shutdown database. I had shutdown abort


Please, Help me solve this problem.

Thanhks!

[Ingo Duschl]

hi, have a look a the disk space for archiving may be there is no.i would reconfigure the archiving. and what is this
Restarting dead background process EMN0
EMN0 started with pid=37

[Julio Yamawaki]

Hi,

There´s a document in Metalink that talks about shutdown immediate hanging.
I´m sending you attached.
Hope this help you.

Regards,

[Ariel Cary]

Hi,

You can see in the alert log file that there are some processes still runing
SHUTDOWN: waiting for active calls to complete
This ussually happend when you have lengthy jobs running at the time of the shutdown. The database will wait for them to complete.
Please verify if you indeed have jobs running.
If this is the case, to minimize the chances of happening, you may want to put the database in restricted mode or set job_queue_processes=0 ahead of time before shutting down the database.

Ariel

[Yogeeraj_1]

hi,

i would seriously look into the RMAN option for backup and never shutdown your database just for backup purposes.

Anyway, you can troubleshoot further by doing the following:
1. Before doing the shutdown immediate, check the sessions currently active:

select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
substr(b.osuser,1,8) os_user, decode(substr(b.program,1,15),null,substr(b.client_info,1,15),substr(b.program,1,15)) program, substr(to_char(logon_time,'dd/mm/yy hh24:mi'),1,15) LogonTime
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by b.username, logonTime;


2. Before you attempt to do the shutdown abort, check at the OS level, the top Oracle processes that are running. Then, check the corresponding Oracle process at the database level:

select b.sid SID,b.serial# "Serial#", c.spid "srvPID", b.osuser, b.username, b.status, b.client_info from v$session b, v$process c where b.paddr = c.addr and c.sPID = '&OSPID'

try these and let us know.

kind regards
yogeeraj

[Patti Johnson]

Has anything in your workload changed recently? Possibly a long running batch job that does not complete before the backup starts? When you do a shutdown immediate it must cancel and rollback all active transactions - if there was a long running transaction active in the db when you issued the shutdown it must roll the entire transaction back.

Checking the number of active connections from the OS as Yogeeraj suggested will help you determine if there is activity in the db.

If you do not have any active transactions, check if you have any connections to the database
ps -ef | grep LOCAL=NO
These OS processes should begin to complete as soon as you issue the shutdown immediate command. If there are some that do not complete, and they are not using resources you can kill them from the OS. I have had to do this on occassion to get a database to come down clean.

If you have to abort the db you need to do a startup and another shutdown cleanly before completing the backup or your backup may not be valid.

Patti

[TwoProc]

There is a known bug in 8.1.7.4 in which the TEMP space gets cut up really badly, leaving SMON running around like crazy at shutdown trying to clean up TEMP segments - and never seeming to finish. We experienced this problem when new programs made extensive use of temp tables for long running queries. The resolution was to do a shutdown abort in our scripts, followed immediately by a startup, and a normal shutdown prior to backups. This problem went away when we upgraded to 9i, and I recommend you do the same.