Oracle-L: RE: ORA-01089

From: <John.Hallas_at_vodafone.co.uk>
Date: Wed, 06 Nov 2002 07:56:29 -0800
Message-ID: <F001.004FCB83.20021106075629@fatcity.com>

Don't know the exact circumstances but the following note from Metalink (1014091.102) may be appropriate
However the best way to learn anything is to ask the senior DBA what he meant. An explanation from him may include looking at the alert log and the scripts that were used. That is more than most people on this list can offer

Good luck

John

Problem Description:

You issue a shutdown immediate and receive the following error: ORA-01089: immediate shutdown in progress - no operations are permitted Cause: The SHUTDOWN IMMEDIATE command was used to shut down a running Oracle instance, terminating any active operations. Action: Wait for the instance to be restarted or contact the database administrator.
You may be running a shutdown script that performs a shutdown abort, then a startup, followed by a shutdown immediate. The error is recorded in the alert
log, and a trace file is generated by one or more snp processes. Solution Description:

The error is actually expected behavior based on the conditions present during
the shutdown immediate. The following workarounds are available: Workaround 1:

Modify your shutdown script to issue a shutdown normal instead of a shutdown

immediate.
Issuing a shutdown normal allows the snp processes to complete their work prior to shutting down, thus avoiding the error. The implication of this workaround is that the database will not shut down until all user and snp processes have completed.
Workaround 2:

Increase the JOB_QUEUE_INTERVAL parameter in the INIT.ORA file. *NOTE: Increase by increments and test to determine what interval is necessary to avoid the error.
This increases the time before the snp processes wake up after starting up the database, allowing the shutdown immediate to be issued before the snp processes wake up.
Workaround 3:

Start the database with a different INIT.ORA file. Modify the startup script to use a separate INIT.ORA file that does not include the JOB_QUEUE_PROCESSES or JOB_QUEUE_INTERVAL parameters. For Oracle 8i versions you can set the JOB_QUEUE_PROCESSES to 0, this will ensure that no snp processes startup.
Since no snp processes will start during the startup, no error will occur during the shutdown immediate.
Explanation:

During the startup portion of the shutdown script, Oracle starts a number of

snp processes based on the "init.ora" parameter "JOB_QUEUE_PROCESSES". Depending on how long Oracle takes to process the shutdown immediate portion of
the shutdown script and the value specified in the "init.ora" parameter "JOB_QUEUE_INTERVAL", one or more of the snp processes may wake up to check the
jobs queue. The smaller the value of "JOB_QUEUE_INTERVAL", the sooner the snp
processes will wake up. If these processes wake up between the time the database was started and the shutdown immediate was issued, the ORA-1089 error
will occur. The snp process cannot continue because a shutdown is in process.
References:

[BUG:775116] ORA-604, ORA-1089, AND ORA-7445 DURING SHUTDOWN IMMEDIATE -----Original Message-----
Sent: 06 November 2002 14:59
To: Multiple recipients of list ORACLE-L

Hello Gurus,

This morning our database locked us out with the error code ORA-01089. Circumstances surrounding this occurrence were that a cold backup by Tivoli is taken performed every night at 4AM.

When I spoke with our senior DBA, he confirmed that the database never shutdown properly before the backup and sure enough, I could see the processes this morning from yesterday. This is the second occurrence in 5 days.

So my first question is, why would this happen? And secondly, how do I deal with it?

Further to my conversation with the senior DBA, I was informed that indeed a shutdown abort command was issued but only after it was confirmed that "everything was ok". What does he mean by that statement? What is he checking for before issuing the abort command?

Thanks in advance and I would like to apologize if this question has been posed earlier at some point in time. I did search the archives but didn't find a satisfactory explanation.

Saira Somani
IT Support/Analyst
Hospital Logistics Inc.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: John.Hallas_at_vodafone.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Nov 06 2002 - 09:56:29 CST