Re: Help - shutdown immedate take 10 mins to complete

"adachi" <adachi_2003_at_yahoo.com.hk> wrote in message news:<cj42tb$22g2$1_at_news.hgc.com.hk>...
> Hi all,
>
> Recently I had upgraded my client database from Oracle 7.3.3 on NT to
> 9.2.0.5.0 EE on HP-UX 64bit platform.
>
> They had created a lot of jobs running in Oracle and the job_queue_processes
> is set to 5. Some jobs required around 5 minutes to complete and some jobs
> can be finished within 10 seconds.
>
> When I shutdown the database using "shutdown immediate", it required a least
> 10 minutes to finish. But if I killed all ora_j00x processes before shutting
> down the database, the "shutdown immediate" completed within 10 seconds.
>
> I had tried to change the job_queue_processes to zero before "shutdown
> immediate" but it doesn't work too.
> I had tried to kill the job sessions using "alter system kill session..."
> but it took almost 5 to 10 minutes to complete.
>
> Can somebody give me a hand on that? Thank you so much in advance.
>
> Adachi

Simply script your shutdown and write some PL/SQL to kill any user sessions prior to issuing the shutdown immediate, as shown in the example below:

declare

   cursor get_reg_users is
   select sid, serial#
   from v$session
   where trunc(logon_time) > (select trunc(startup_time) from v$instance);

   sqltxt varchar2(200);
   curr_sess exception;
   pragma exception_init(curr_sess, -27); begin

   for urec in get_reg_users loop

           sqltxt:='alter system kill session '''||urec.sid||','||urec.serial#||'''';

           execute immediate sqltxt;
   end loop;
exception

   when curr_sess then

        null;
end;
/

shutdown immediate

This should kill any user sessions, including jobs scheduled with dbms_job, preserving your SYS session running this script, ensuring your database will close in short order. The WHERE clause eliiminates any sessions with a NULL username (all of the necessary processes to run your database) since these started at roughly the same time listed in v$instance.startup_time. The trunc() function is nscessary since the time portion for each non-user process is greater than that in the startup_time field. And, since trying to kill your session from your session results in an ORA-00027 an exception has been declared and initialised to not report that error.

I feel this is better than trying to use a kill -9 from the operating system against any SQL*Plus sessions listed as it won't leave behind any orphan processes; also, any jobs scheduled with dbms_job won't have a visible SQL*Plus session in a ps -ef list (as far as I know) so using kill -9 would leave such sessions running.

I hope this helps.

David Fitzjarrell Received on Mon Oct 04 2004 - 11:22:21 CDT