8

I'd like to know if its possible to get sqlplus output in some way to discover if my database is up.

I want to run a list of scripts on a database, but before I do that, I want to know if the database is up and running with my script.

Here is what I tried:

 sqlplus /@DB1 << EOF
 > select 1 from dual;
 > EOF

It cannot connect, but the return code of sqlplus still says "everything OK"!

SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 28 10:06:41 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-12505: TNS:listener does not currently know of SID given in connect
descriptor


Enter user-name: SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where  ::= [/][@]
       ::= [][/][@]
Enter user-name: ju@srv:/tmp/jcho $ echo $?
0

I know I could grep the result of my test query, like that:

a.sh

sqlplus /@DB1 << EOF
  select 'ALL_GOOD_BOY' from dual;
EOF

call:

gives 1 line if connection works, 0 otherwise:

$ a.sh |grep ALL_GOOD_BOY|wc -l

... This seems many steps to me. Any other way to set sqlplus in a mode where "unable to connect" gives an "error" return code?

Community
  • 1
  • 1
J. Chomel
  • 8,193
  • 15
  • 41
  • 69
  • 1
    To be honest I think the way with select from dual is best way of checking it. Here is another thread with very similar topic: http://dba.stackexchange.com/questions/4718/how-check-that-the-oracle-database-is-up – Kacper Nov 28 '16 at 09:35
  • That's what I'm afraid of. If I could actually connect, I could change SQLPLUS return code like [here](http://stackoverflow.com/a/18111656/6019417). But I don't know if I can connect. – J. Chomel Nov 28 '16 at 09:58
  • I had to look a little deeper, it was there: https://stackoverflow.com/questions/2254761/sqlplus-force-it-to-return-an-error-code – J. Chomel Dec 28 '17 at 14:13

2 Answers2

6

Thanks to the reference given by @Kacper, I could adapt this sqlplus /nolog to my case; here's the idea:

  1. open sqlplus only without connecting
  2. set a specific return code on SQLERROR - this is what happens when connect fails
  3. return code can be collected as usual in the caller script:

a.sh

sqlplus /nolog << EOF
 WHENEVER SQLERROR EXIT 50
 WHENEVER OSERROR EXIT 66
 connect /@${MISTERY_DB}
 exit;
EOF

Then the call:

/ju $ export MISTERY_DB="eg_NON_EXISTING_DB"
/ju $ a.sh
SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 29 08:43:44 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
SQL> SQL> SQL> ERROR:
  ORA-12154: TNS:could not resolve the connect identifier specified
/ju $ echo $?
50

Also related: Connect to sqlplus in a shell script and run SQL scripts

J. Chomel
  • 8,193
  • 15
  • 41
  • 69
1

Here is another solution you could use: WHENEVER SQLERROR sql.sqlcode works for me (on Oracle 11g):

# try a simple SELECT FROM DUAL on previously defined database in var MY_DB
sqlplus -s /@${MY_DB} << EOF
  whenever sqlerror exit sql.sqlcode;
  select 1 from dual;
  exit;
EOF
ERR_CODE=$? # then $? is loaded with error received
if [[ 0 != "${ERR_CODE}" ]] ; then
  echo could not connect :\(
else 
  echo connection succeeded
fi
Jika
  • 412
  • 5
  • 14