Solved

Errors connecting to database when running sqlplus from ksh script

Posted on 2013-06-14
6
808 Views
Last Modified: 2013-06-18
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
0
Comment
Question by:YZlat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39249080
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
 
LVL 23

Expert Comment

by:David
ID: 39251970
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
 
LVL 35

Author Comment

by:YZlat
ID: 39253731
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:David
ID: 39254102
Can you please provide enough of your output log to show all this?
0
 
LVL 35

Author Comment

by:YZlat
ID: 39254209
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
 
LVL 68

Accepted Solution

by:
woolmilkporc earned 500 total points
ID: 39255473
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question