Login or Register to Ask a Question and Join Our Community


UNIX for Advanced & Expert Users

Capturing Oracle Shutdown error

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Advanced & Expert Users Capturing Oracle Shutdown error
# 1  
Old 04-17-2007
Lightbulb Capturing Oracle Shutdown error
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

(c) Copyright 2000 Oracle Corporation. All rights reserved.

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. Smilie
Last edited by ganapati; 04-17-2007 at 03:10 AM..
# 2  
Old 04-17-2007
capture the output in a logfile/variable
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.
# 3  
Old 04-17-2007
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.
# 4  
Old 04-17-2007
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! Smilie
# 5  
Old 04-17-2007
Hammer & Screwdriver
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.
# 6  
Old 04-17-2007
Quote:
Then there is no use of testing "$?" in these scenarios!!!
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.
# 7  
Old 04-17-2007
Quote:
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)!!!
You need to handle the sqlplus exit status with whenever sqlerror/oserror exit [...], like:

Code:
$ 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
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Solaris

Shutdown Oracle DB on SunOS Cluster

Hi, I've this Oracle database version: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production Oracle is install in a SunOS DBSERVER1 5.11 11.2 sun4v sparc sun4v cluster (Not RAC environment) DBSERVER1 is node active and DBSERVER2 is node inactive ... (4 Replies)
Discussion started by: db_senior
4 Replies

2. Shell Programming and Scripting

Capturing Oracle SQL Error Codes

My issue pertains to earlier posts like exit unix script after sqlerror in procedure - dBforums SQLPLUS Error Capturing | Unix Linux Forums | Shell Programming and Scripting We are executing PL/SQL blocks from shell scripts using SQLPLUS. The error code has to be captured into a logfile that... (3 Replies)
Discussion started by: jerome_rajan
3 Replies

3. Shell Programming and Scripting

Help required for Oracle database shutdown script conversion from shell to perl

Please tell me how to convert below program from shell script to perl. Same commands need to use in shutdown, just need program help for startup. export ORACLE_BASE=/home/oracle1 lsnrctl start lndb1 sqlplus '/ as sysdba' startup; (2 Replies)
Discussion started by: learnbash
2 Replies

4. Programming

Problem on capturing system Shutdown

I am having exactly the same problem with https://www.unix.com/programming/129264-application-cleanup-during-linux-shutdown.html but the thread is old and closed. The only difference is that I use sigaction() instead of signal(), which is recommended, as far as I know. This is my code: ... (9 Replies)
Discussion started by: hakermania
9 Replies

5. Shell Programming and Scripting

SQLPLUS Error Capturing

This is my .sh file sqlplus -s my_user/my_pswd@mydb @my_sql.sql ret_code=$? if then echo "return code is $ret_code " echo "Failed" else echo "return code is $ret_code " echo "Success" fi ===================================================== and my_sql.sql is whenever... (4 Replies)
Discussion started by: emailsafath2011
4 Replies

6. Web Development

Tomcat shutdown error

not sure if this is the right forum but im having problem with tomcat restart. i have a regular user that when he login it will go directly to a menu options (a. stop tomcat, start tomcat,). if he selects start it would should show that tomcat has been restarted. if the user press X, he will... (2 Replies)
Discussion started by: lhareigh890
2 Replies

7. Shell Programming and Scripting

Oracle DB Start shutdown scripts

Hi, We have a requirement wherein we do not want to share the Oracle DB sys and system passwords to be shared with the support desk. But they will be responsible for starting/shuting down the Database. Is it possible to write a shell script which will read the sys and system passwords from a... (0 Replies)
Discussion started by: narayanv
0 Replies

8. UNIX for Dummies Questions & Answers

Script to force Oracle database shutdown when shutdown immediate does not work

I have Oracle 9i R2 on AIX 5.2. My Database is running in shared server mode (MTS). Sometimes when I shutdown the database it shutsdown cleanly in 4-5 mints and sometimes it takes good 15-20 minutes and then I get some ora-600 errors and only way to shutdown is by opening another session and... (7 Replies)
Discussion started by: aixhp
7 Replies

9. UNIX for Advanced & Expert Users

Capturing failed FTP error

Hi All, Please check the below ftp related job, which is deleting the files from remote host. Problem is it is not capturing the ftp failure error, and the exit status is still '0' eventhough the deletable files are not present in remote location OR ftp credential are incorrect. ... (5 Replies)
Discussion started by: ganapati
5 Replies

10. UNIX for Dummies Questions & Answers

capturing oracle procedure out param value

I have a procedure with an out parameter, I want to use this value in a shell script, I've done this in perl before but they want this to be a ksh script. what is the syntax to do this. this was my first thought; #!/usr/bin/ksh sqlplus -s scott/tiger@db << EOF ... (1 Reply)
Discussion started by: edog
1 Replies
Login or Register to Ask a Question