Help procedure keeps giving login errors

One of our site is having some peculiar problem. When they are
running a procedure they are getting ORA-01089 immediate shutdown in progress
error. Same time they were able to login to the database using SQLPLUS
and do some operation. Point is nobody has issued shutdown command. It 's
10g database.
Need some help.  Below is the procedure and the error messages:

Procedure being run

${ORACLE_HOME}/bin/sqlplus ${OUSER}/${OPW} <<EOC

SET SERVEROUTPUT ON SIZE 10240;

BEGIN lang_updtab.update_table(dtab=>'bib_holdings_temp', stab=>'bib_holdings_compare', exclcmp=>'ID', exclupd=>'ID', flags=>'EIUC', commitpoint=>100);END;

Result of procedure

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jan 24 15:33:24 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

ERROR:

ORA-01089: immediate shutdown in progress - no operations are permitted

Enter user-name: SP2-0306: Invalid option.

Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]

where <logon> ::= <username>[/<password>][@<connect_identifier>] | /

Enter user-name: SP2-0306: Invalid option.

Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]

where <logon> ::= <username>[/<password>][@<connect_identifier>] | /

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
sikyalaSenior Database AdministratorAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
bummerlordConnect With a Mentor Commented:
this Q would be better suited in the Oracle section I think :-)

Rule of thumb: Review the alert log from the time (and just before) it started to happen.

You are 100% sure no one issued a shutdown immediate?
No cold backups running at night that might have failed, and never shut down properly?
If running in a HA cluster, and this procedure eats away on resources enough for the monitoring to think it's time to attempt fail over, but fails? (any sane HA implementation would do shutdown abort though, but there are strange setups sometimes)

I would proceed like this;
shut down cellphone ;-)
get cup of coffee
shutdown abort, or kill processes from shell (reboot if you will)
perform full cold backup of the database file system.
startup and proceed with instance recovery.
Try the same procedure again to see if it will reproduce the problem 'every time' and on a fresh instance.
If it does, submit a case with oracle and have them return some value for that insane support invoice.
If it doesn't, submit a case to oracle support anyway :-)

0
 
TintinConnect With a Mentor Commented:
Are you sure your ORACLE_SID points to the right database?  I assume you left that section out of your post?
0
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
can you try to be more explicit in saying that in which database the script should be run. This is not mandatory if oracle sid is set properly but we can try here.

${ORACLE_HOME}/bin/sqlplus ${OUSER}/${OPW}@TESTDB <<EOC
...

Change the TESTDB in the above to your database name and retry.

Thanks



0
 
sikyalaSenior Database AdministratorAuthor Commented:
I am sorry it has been a couple of days since I responded.  I am honestly unsure of what is going on now. I am the DBA and my team lead contacted me regarding this problem. It seems to occur when he runs a Unix script that is logging into the database. At first he said he thought this problem is a result of me applying the October Oracle patch while the script was running from a cron job. Applying the patch requires me to shutdown the database. Now he is saying it may have had something to do with the server being low on disk space. He hasn't confirmed it. Nor has he come to any other conclusions.  I did recommend that he add the ORACLE SID in the script and he said he didn't need to because the script gets the ORACLE SID from the .profile file.
0
All Courses

From novice to tech pro — start learning today.