• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 12352
  • Last Modified:

Shutdown Immediate hanging.

Hello Experts:

I am running Oracle 8.1.7 on Windows 2000 Server.  We do cold backups every Friday morning (hots the rest of the days) and I use the following script to shutdown the database:

connect internal/<password>@<instance> as sysdba
shutdown immediate
startup restrict
shutdown normal
exit

However, every once in a while the shutdown immediate hangs and I need to do a shutdown abort and startup manually and restart the database (and the backup fails).  This is a snippet from the alert.log for the shutdown:

Fri Nov 26 02:03:41 2004
Shutting down instance (immediate)
License high water mark = 18
Fri Nov 26 02:04:48 2004
ALTER DATABASE CLOSE NORMAL
Fri Nov 26 02:04:48 2004
SMON: disabling tx recovery
SMON: disabling cache recovery
Fri Nov 26 02:04:49 2004
Thread 1 closed at log sequence 3521
Fri Nov 26 02:04:49 2004
Completed: ALTER DATABASE CLOSE NORMAL
Fri Nov 26 02:04:49 2004
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
archiving is disabled
Fri Nov 26 02:04:52 2004
ARCH shutting down
Fri Nov 26 02:04:52 2004
ARCH shutting down
Fri Nov 26 02:04:52 2004
ARC1: Archival stopped
Fri Nov 26 02:04:52 2004
ARC0: Archival stopped
Fri Nov 26 02:09:59 2004
SHUTDOWN: waiting for detached processes to terminate.

The waiting for the detached process is that last message I get.

The hang is seeming to get more frequent and I am also seeing more activity in the application this database supports too so I am suspecting its only going to get worse (i.e. hangs every week).

Any pointers would be appreciated.

Simon.


0
simonk_klammer
Asked:
simonk_klammer
  • 2
  • 2
  • 2
  • +5
2 Solutions
 
compcrazyCommented:
after connect and before the shutdown command, check for any active session, if there are any, you can kill them if u are sure...

if that also doesnt work, start killing the inactive sessions also. then try the shutdown command.

Of course, i hope u will not kill your session... otherwise you will again be out ;-)

hope this helps.
0
 
alexfrlCommented:
USE

SHUTDOWN ABORT
0
 
schwertnerCommented:
The reason for this is
shutdown normal

Use
shutdown immediate

instead.

In fact shutdown doesn't hang. You choose (and Oracle executes your will)
to shutdown after every user sessions quits (an nearly impossible situation)!
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
alexfrlCommented:
connect internal/<password>@<instance> as sysdba
shutdown ABORT
startup restrict
shutdown normal
exit
0
 
boriskalavskyCommented:
You can try following:

alter system checkpoint;
alter system switch logfile;
-- If running Oracle version before 8.1.7.1
alter system set log_archive_max_processes=5;
alter system archive log stop;
alter system set log_archive_max_processes=1;
alter system archive log start
-- end If running Oracle version before 8.1.7.1
shutdown immediate

Oracle had some problems in 8.1.7 with archiving and checkpointing on shutdown.
0
 
kumaran100Commented:
Hi,
As told by compcrazy  It is waiting for the sessions to be closed.
If  users are connected, it won't shutdown immediately. Ask them to end the session or u can explicitly close the sessions. This is the only possible way.
If still it is continuing, then shutdown the server once and try it. Hope this will help u.

regards,
kums

0
 
boriskalavskyCommented:
You can try following:

alter system checkpoint;
alter system switch logfile;
-- If running Oracle version before 8.1.7.1
alter system set log_archive_max_processes=5;
alter system archive log stop;
alter system set log_archive_max_processes=1;
alter system archive log start
-- end If running Oracle version before 8.1.7.1
shutdown immediate

0
 
Mark GeerlingsDatabase AdministratorCommented:
We also had that problem occasionally with Oracle8.1.7 on Windows.  I opened a TAR on it with Oracle, but they were not able to solve it.  I modified our shutdown/backup/restart process slightly to include a second, independent process that checks if the "shutdown immediate" succeeded, or not.  If not, it then does a "shutdown abort" followed by a restart.  Would you like more details on how we do that?
0
 
simonk_klammerAuthor Commented:
Hi Markgeer:

I would like more information on that please.

Also, boriskalavsky and compcrazy:  I'm checking out your suggestions too.

Thanks!

Simon.
0
 
Mark GeerlingsDatabase AdministratorCommented:
I use two separate batch files, that call two corresponding *.sql files.  The batch files are scheduled with the Windows Task Scheduler ("at" or WinAt).  We are actually on WindowsServer2003 now, and the Task Scheduler is much improved over older version of Windows, but this same approach worked for us with WindowsNT4.0.  We never actually ran Oracle on Windows2000, we went from Oracle8.1.7 on WindowsNT4.0 to Oracle9.2.0 on WindowsServer2003.

The first batch file (named "weekend.bat") first creates a file to indicate that it started, then it attempts to shut Oracle down and do a cold backup (disk-to-disk copy).  If those steps succeed, it deletes the file created in the first step.  This process normally takes 35-40 minutes on our server.

The second batch file (named "week_ck.bat") is scheduled to run one hour later than "weekend.bat".  It checks for the existance of the file that indicates "weekend.bat" started, but did not finish, usually because the shutdown hung.  If it finds that file, it does a shutdown abort, then a reboot.  If this happens, we do not get a cold backup.  This could be modified to try a shutdown after the reboot, then do a cold backup and another restart, but i have not written those extra steps in.

Batch File #1, weekend.bat
@echo off
H:
cd \programs\oracle
echo Started at > H:\programs\oracle\weekend.log
time /t >> H:\programs\oracle\weekend.log
rem Delete the old "marker" file(s)
if exist G:\weekend.bak del G:\weekend.bak
if exist G:\hung.txt del G:\hung.txt
if exist G:\weekend.txt goto reboot
if exist G:\hung.txt goto reboot
rem (else... and this is expected)
  goto normal
:reboot
  rem The normal weekend process hung, probably during the Oracle shutdown, so this is being called
  rem  from a batch file in the startup process after a server shutdown and reboot
  copy G:\keep_me.txt G:\reboot.txt /Y
  rem Clear screen and warn console user (if any) so database shutdown and backup can be skipped, if desired.
  cls
  echo * * * * * * * * * * * *
  echo *  W A R N I N G !    *
  echo * * * * * * * * * * * *
  echo *
  echo * The production Oracle database will be shut down in 30 seconds!
  echo *
  echo * Press "N", or close this DOS windows *NOW* to avoid that!
  echo *
  rem "choice parameters: /t = time (in seconds); /c = choices "y" or "n" in this case;
  rem       /n = don't display choices; /d = default ("y" in this case); /m = message text ("Shutdown database...")
  choice /t 30 /c yn /n /d y /m "Shutdown database now? Y = yes, N = no"
if errorlevel = 2 goto nopicked
  goto notfound
:nopicked
    echo No
  goto end
:notfound
if errorlevel = 1 echo Shutting down and backing up Oracle now...
  goto stop_svc
:normal
rem Create a new file to show that the weekend process has started.
echo Copying "keep_me.txt" to "weekend.txt" now... >> H:\programs\oracle\weekend.log
copy G:\keep_me.txt G:\weekend.txt >> H:\programs\oracle\weekend.log
rem This does a full shutdown of Oracle (including the services) and copies the data, log and control files to
rem  an alternate location so they can be backed up to tape from there.
rem The SQL*Net listener service is also shut down so its log file can be renamed.
set ORACLE_SID=PROD
echo Calling database shutdown script now... >> H:\programs\oracle\weekend.log
sqlplus /nolog @H:\Programs\Oracle\week_end.sql
:stop_svc
rem net stop OracleSystemAgent
net stop OracleServicePROD
lsnrctl stop
time /t >> H:\programs\oracle\weekend.log
echo Database and listener shut down, moving archived redo logs... >> H:\programs\oracle\weekend.log
rem * * * Changed to: "lsnrctl stop" from "net stop..." on Nov 28, 2000 per Oracle Tech Support
rem net stop OracleOra817TNSListener
del G:\backup\dt6*.ora /s > G:\backup\del_big.log
del H:\backup2\ix6*.ora /s >> G:\backup\del_big.log
del G:\backup\dt5*.ora /s >> G:\backup\del_big.log
del H:\backup2\ix5*.ora /s >> G:\backup\del_big.log
del G:\backup\dt4*.ora /s >> G:\backup\del_big.log
del H:\backup2\ix4*.ora /s >> G:\backup\del_big.log
time /t >> H:\programs\oracle\weekend.log
echo Copying database files to G:\backup and H:\backup2 ... >> H:\programs\oracle\weekend.log
xcopy F:\ora_data\*.ora G:\backup /Y > G:\backup\copy_fw.log
xcopy F:\ora_data\*.ctl G:\backup /Y >> G:\backup\copy_fw.log
xcopy G:\ora_data\*.ora G:\backup /Y > G:\backup\copy_gw.log
xcopy G:\ora_data\*.ctl G:\backup /Y >> G:\backup\copy_gw.log
xcopy H:\ora_data\*.ora G:\backup /Y > G:\backup\copy_hw.log
xcopy H:\ora_data\*.ctl G:\backup /Y >> G:\backup\copy_hw.log
xcopy J:\ora_data\*.ora H:\backup2 /Y > G:\backup\copy_jw.log
xcopy K:\ora_data\*.ora G:\backup /Y > G:\backup\copy_kw.log
echo Renaming listener.ora... >> H:\programs\oracle\weekend.log
call H:\Programs\misc\ren_lstn.bat
xcopy I:\ora_data\tem*.tmp G:\RdBackup\Ora_data /Y > G:\backup\copy_gw.log
rem  Rename the "flag" file to show that the weekend database shutdown and backup finished.
rename G:\weekend.txt weekend.bak
time /t >> H:\programs\oracle\weekend.log
echo Reached end-of-process, O/S shutdown and reboot follow  >> H:\programs\oracle\weekend.log
shutdown.exe /R /F
:end
-- (end of "weekend.bat")

Here are the files called from weekend.bat: ("week_end.sql",  and "ren_alrt.bat")

connect sys/[password]@prod as sysdba
--To get more information, (to debug the indefinite: "waiting for active calls to complete")
-- before issuing the shutdown command, issue:
--
--alter session set events '10400 trace name context forever, level 1';
--
--This will produce a trace file in your "user_dump_dest" directory (G:\trace\user)
--The trace file will be a systemstate dump. From this you can tell what processes are still active.
--
shutdown immediate;
startup restrict;
alter database create standby controlfile as 'H:\ora_data\standby.ctl' reuse;
alter database backup controlfile to trace;
shutdown;
exit;
-- (end of file: "week_end.sql")

@echo off
rem PLEASE DO NOT MODIFY THIS FILE!
rem This batch file was produced by the Oracle job, "count_rows",
rem  on Mon. Nov 29, 2004 at 05:37:44 am.
rem  * * * * * * * * * * * * * * * * * * * * * *
G:
cd\trace\system
rename listener.log list1204.log
-- (end of file: "ren_alrt.bat")

Batch file #2 "week_ck.bat"
@echo off
rem This file is intended to be run by the job scheduler on the production DB server at 2:00am on Sundays.
rem  It looks for a file that only exists after the normal weekend DB shutdown starts, but before it finishes.
rem  If this "weekend.txt" file exists, the weekend shutdown did not finish - usually because it hung on the DB
rem  shutdown.  So this forces a server shutdown and reboot.  The startup then calls the
rem  weekend.bat job.
rem
if exist G:\weekend.txt goto hung
  rem Else, This is what we expect to happen: "G:\weekend.txt" does not exist because it was renamed
  rem  successfully by "weekend.bat" at the end of the DB shutdown, backup, restart cycle.
  rem  
  rem Copy the backed-up Oracle data files to other directories where they can stay for the week
  rem  and not get overwritten by the daily hot backups.
  xcopy G:\backup\*.ora F:\ora_data\spare /d /y > G:\trace\backcopy.log
  xcopy G:\backup\*.ctl F:\ora_data\spare /d /y >> G:\trace\backcopy.log
  xcopy H:\backup2\*.ora K:\backup /d /y >> G:\trace\backcopy.log
  xcopy H:\backup2\*.ctl F:\ora_data\spare /d /y >> G:\trace\backcopy.log
  call H:\programs\oracle\copy_to_stdb.bat
  goto end
:hung
copy G:\keep_me.txt G:\hung.txt >> H:\programs\oracle\hung.log
H:\programs\oracle\down_now.bat
:end
-- (end of file: "week_ck.bat")

Here is the file "down_now.bat" that is called from "week_ck.bat"
rem This is intended to be run ONLY when the regular weekend job hangs on the Oracle shutdown
shutdown.exe /R /F
-- (end of file: "down_now.bat")
0
 
ahmedalamCommented:
We occasionally have a shutdown immediate issue in production before taking backup from snapshot media. Our production comes down once a week for cold backup. Is there a process we can make sure if the shutdown immediate does not work in 5 mins issue a shutdown abort command automatically.
Thanks
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
  • 2
  • 2
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now