Capturing Oracle Shutdown error

ganapati · April 17, 2007, 1:34am

Hi,

Iam calling 3 sql scripts through one shell script 'rmia.sh'.
Till now this was working fine, but last time while calling the SQL scripts,
Oracle was down. But amazingly the exit status was '0' (success)!!!

Below is the shell code:

#!/usr/bin/ksh -x

assign_file asql a.sql 1
assign_file rmia_upd rmia0001.up1 1
assign_file rmia_pop rmia0001.pop 1
assign_file rmia_tru rmia0001.tru 1

sqlplus -s $USER_NAME/$PASS_WORD <<!
@$rmia_upd
!

if [ $? -ne 0 ]
then
echo JOB Errored
exit 1
fi

sqlplus -s $USER_NAME/$PASS_WORD <<!
@$rmia_pop
!

if [ $? -ne 0 ]
then
echo JOB Errored
exit 2
fi

sqlplus -s $USER_NAME/$PASS_WORD <<!
@$rmia_tru
!

if [ $? -ne 0 ]
then
echo JOB Errored
exit 3
fi

echo "Job RMIA0001 is successful"
exit 0

Last few lines of the log file are:

+ sqlplus -s / @/opt/tuxedo/lokesha/rmia0001.up1
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress

+ [ 0 -ne 0 ]
+ sqlplus -s / @/opt/tuxedo/lokesha/rmia0001.pop
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress

+ [ 0 -ne 0 ]
+ sqlplus / @/opt/tuxedo/lokesha/rmia0001.tru

SQL*Plus: Release 8.1.7.0.0 - Production on Sun Apr 1 00:01:04 2007

ERROR:
ORA-01033: ORACLE initialization or shutdown in progress

Enter user-name: + [ 0 -ne 0 ]
+ date
+ echo Job RMIA0001 is successful
+ exit 0

What could be the possible reason for not capturing this "Oracle shutdown Error" as per the log file this time?

Any clue would be much appreciated.

Many Thanks and Regards,
Mysore Ganapati.

ranj1 · April 17, 2007, 5:27am

Why not capture the output in a logfile or a variable and then check in that. All the error handling for sql should be done in the sqlplus session.

ganapati · April 17, 2007, 5:37am

ranj, sorry you didn't get my question.

If oracle is down and then sql files will not get executed. Then how to capture SQL error in SQLPLUS session.
Shell treats entire SQL calling lines as one command and "$?" will give the exit status for its previous command. In my case eventhough Oracle was down and SQL files were not get executed, but "$?" returned with "0", which indicates successful of the SQLPLUS calling.

With Regards,
Ganapati.

matrixmadhan · April 17, 2007, 6:11am

Yes,

that would return $? as 0, as the sqlplus session was successfully terminated, the return status of the sql plus session overrides the return status whether oracle server is available or not.

For that, I could suggest to parse the logs, where information like 'Oracle', 'Shut down' should be identified to really know that there had been such shutdowns!

ganapati · April 17, 2007, 6:48am

Matrixmadan,

Then there is no use of testing "$?" in these scenarios!!!

Consider the below example:

#!/usr/bin/ksh -x

assign_file asql a.sql 1

sqlplus -s sys/ibct736 <<!
@$asql
!

if [ $? -ne 0 ]
then
echo JOB Errored >> a.log 2>&1
exit 1
fi

echo "Job is successful" > a.log 2>&1
exit 0

OUTPUT is:

+ assign_file asql a.sql 1
+ sqlplus -s sys/ibct736
+ 0<<
@/opt/tuxedo/lokesha/a.sql
ERROR:
ORA-01090: shutdown in progress - connection is not permitted

ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}] | [INTERNAL]
where <logon> ::= <username>[/<password>][@<connect_string>] | /
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
+ [ 1 -ne 0 ]
+ echo JOB Errored
+ 1>> a.log 2>& 1
+ exit 1

This is what lead me towards confusion!

I want to find the reason for the first case, in which shell script failed to capture the Oracle shutdown error.

Any how thanks for your friendly help.

Regards,
Mysore Ganapati.

matrixmadhan · April 17, 2007, 7:06am

I never said to ...

Sorry if there was a confusion.

For these scenarios it would be easier and appropriate to parse the logs and detect failure patterns.

radoulov · April 17, 2007, 7:52am

You need to handle the sqlplus exit status with whenever sqlerror/oserror exit [...], like:

$ printf "select null from dba_tables;\n" \
> | sqlplus -s / as sysdba; \
> printf "Exit status is: $?\n"
select null from dba_tables
                 *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only


Exit status is: 0
$ printf "whenever sqlerror exit 42\n\
> select null from dba_tables;\n" \
> | sqlplus -s / as sysdba; \
> printf "Exit status is: $?\n"
select null from dba_tables
                 *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only


Exit status is: 42

ganapati · April 18, 2007, 5:33am

radoulov,

"whenever sqlerror exit" will get executed only if the oracle is up.
But whenever Oracle is in shutdown status, that time the SQL command "whenever sqlerror exit" will not get executed.

Also please see the prior two example and compare the difference.
At one instance it worked fine and gave the exit status "1" and another time the status was "0".

Please guide/correct me if Iam wrong.

Mysore Ganapati.

radoulov · April 18, 2007, 5:45am

Incomplete post! See addendum below!

ganapati:

radoulov,

"whenever sqlerror exit" will get executed only if the oracle is up.
But whenever Oracle is in shutdown status, that time the SQL command "whenever sqlerror exit" will not get executed.

Incorrect! It's SQL*Plus (client) feature, see the docs for details. For example:
$ printf "select null from dba_tables;\n" \
> | sqlplus -s / as sysdba; \
> printf "Exit status is: $?\n"
select null from dba_tables
*
ERROR at line 1:
ORA-01034: ORACLE not available


Exit status is: 0
$ printf "whenever sqlerror exit 42\n\
> select null from dba_tables;\n" \
> | sqlplus -s / as sysdba; \
> printf "Exit status is: $?\n"
select null from dba_tables
*
ERROR at line 1:
ORA-01034: ORACLE not available


Exit status is: 42
ganapati:

Also please see the prior two example and compare the difference.
At one instance it worked fine and gave the exit status "1" and another time the status was "0".
[...]

Exit 1 is because of the "SP2-0306: Invalid option.".

radoulov · April 18, 2007, 6:13am

Going' OT, so I suppose the thread has to be moved ...

OK,
it works as long as you succeed to connect (even to an idle instance): tested with a "shutting down" oracle instance and a regular user connection and it doesn't work (note that if you're sysdba/sysoper it will work, they are able to connect anyway):

as sysdba

$ printf "whenever sqlerror exit 42\n\
>  select null from dba_tables;\n" \
>  | sqlplus -s / as sysdba ; \
>  printf "Exit status is: $?\n"
 select null from dba_tables
*
ERROR at line 1:
ORA-01012: not logged on


Exit status is: 42

as regular user

$ printf "whenever sqlerror exit 42\n\
>  select null from dba_tables;\n" \
>  | sqlplus -s test/test; \
>  printf "Exit status is: $?\n"
ERROR:
ORA-01090: shutdown in progress - connection is not permitted


SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon>  ::= <username>[/<password>][@<connect_identifier>] | /
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
Exit status is: 1

Anyway, you get an exit status != 0, so you can handle it via script.
Of course, as far as you don't use it in this way:

[oracle@ecq007: ~]$ printf "conn test/test\nwhenever sqlerror exit 42\n\
>  select null from dba_tables;\n" \
>  | sqlplus -s /nolog; \
>  printf "Exit status is: $?\n"
ERROR:
ORA-01090: shutdown in progress - connection is not permitted


SP2-0640: Not connected
Exit status is: 0

Regards
Dimitre

ganapati · April 19, 2007, 7:08am

Thanks a lot for your sincere support radoulov,

My main question was, what could be the exact reason for not getting exit status '1' in my first example and why did I get exit '1' for my second example.
I only need to know the differences between those two.

In this regard, any help would be great help for me.

With Regards and Cheers~~
Mysore Ganapati.

radoulov · April 19, 2007, 7:39am

Don't know, I cannot reproduce your first case (I'm getting exit status 1).

Could you post the content of "$rmia_upd"?

ganapati · April 19, 2007, 7:46am

rmia0001.up1 is as below:

WHENEVER SQLERROR EXIT FAILURE

update emp set ename='ganap'
where enum=10001 ;
/
EXIT SUCCESS
Regards,
Ganapati.

radoulov · April 19, 2007, 8:08am

Cannot reproduce it:

$ cat script 
#!/bin/ksh -x

sqlplus -s test/test <<!
@rmia_upd.sql
!

if [ $? -ne 0 ]
then
echo JOB Errored
exit 1
fi

$ cat rmia_upd.sql 
WHENEVER SQLERROR EXIT FAILURE

select null from dual;

EXIT SUCCESS
$ ./script 
+ sqlplus -s test/test
+ << ! 
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress


SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon>  ::= <username>[/<password>][@<connect_identifier>] | /
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
+ [ 1 -ne 0 ]
+ echo JOB Errored
JOB Errored
+ exit 1

Are you sure it's the right log? It's strange that the command appears as

+ sqlplus -s / @/opt/tuxedo/lokesha/rmia0001.up1

and not as

+ sqlplus -s your_user/your_pass
+ << ! 
...

ganapati · April 19, 2007, 8:18am

Thanks radoulov,

One last question please.
Any chances of finding the possiblities of not getting exit status '0' in this scenarios? (which is depicted in my first mail)

Regards,
Ganapati. :rolleyes:

radoulov · April 19, 2007, 8:26am

I'm not sure what exactly you mean,
I cannot find how can you get 0 exit status in the first case ...

Other than this (that I already mentioned):

$ cat script 
#!/bin/ksh -x

sqlplus -s /nolog <<!
conn test/test
@rmia_upd
!

if [ $? -ne 0 ]
then
echo JOB Errored
exit 1
fi

$ ./script 
+ sqlplus -s /nolog
+ << ! 
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress


SP2-0640: Not connected
+ [ 0 -ne 0 ]