Errors connecting to database when running sqlplus from ksh script

I have the following script to read database names from a file in a loop, login to the sqlplus, run a select statement against the database and write the results to a file.


grep "^[a-zA-Z]" $FILE1 | while read LINE
do
 OUTFILE=/u01/test/logs/output_`date +"%Y%m%d%H%M"`.log
sqlplus -s "/ as sysdba" <<! >> $OUTFILE
        COLUMN field1 format 999999.90
        COLUMN field2 format a20
        COLUMN field3 format 999999.90
       
                set lines 132
SELECT field1, field2, field3 FROM TABLE1;

                 exit;

!

done

I checked the output file and saw that it only returns data for the last database in the list and for all previous ones it returns an error

SELECT field1, field2, field3 FROM Table1
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0


What could be the problem? All of those databases are running if I check

ps -ef | grep pmon
LVL 35
YZlatAsked:
Who is Participating?
 
woolmilkporcConnect With a Mentor Commented:
I'm rather sure that this is an Oracle problem and not a scripting problem.

ORA-1034 is almost always due to Oracle not running.

Are you really sure that the respective DBs are up? I know you wrote about having checked pmon, but anyway, a simple test should help deciding whether ist's your script or Oracle:

Just set ORACLE_SID and ORACLE_HOME on the command line to the values pertaining to one of the failing DBs and try starting sqlplus manually.

export ORACLE_SID=xxx
export ORACLE_HOME=/oracle/xxx
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus / as sysdba

What do you see?

Please note that you must export the variables!
0
 
woolmilkporcCommented:
You're creating a variable LINE at each iteration, but you don't use that variable anywhere later.

If it's a database name you should at least set ORACLE_SID and ORACLE_HOME based on that name,
otherwise you will always connect to the database pointed to by the initial setting of those variables, or you can't connect at all if the variables are empty.

Could it be that you're always examining an old version of $OUTPUTFILE?

Or didn't you post the whole script?
0
 
DavidSenior Oracle Database AdministratorCommented:
If all instances have the same ORACLE_HOME, you could get away with:
...do
export ORACLE_SID=$LINE
or possibly:
.oraenv < $LINE

but I can't connect to test this.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
YZlatAuthor Commented:
woolmilkpork, that's not all my code, actually I grab $LINE and set $ORACLE_SID to the contents of that line. I also set $ORACLE_HOME
0
 
DavidSenior Oracle Database AdministratorCommented:
Can you please provide enough of your output log to show all this?
0
 
YZlatAuthor Commented:
SELECT col1, col2, col3 col4 FROM TABLE1
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

SELECT col1, col2, col3 col4 FROM TABLE1
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

col1	col2	col3	col4
-------------------- --------- -------------------
test	7.343   7.315   .027         
test2	2.034   5.281   .056 

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.