?
Solved

Errors connecting to database when running sqlplus from ksh script

Posted on 2013-06-14
6
Medium Priority
?
815 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
Technology Partners: 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 1500 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
FreeBSD on EC2 FreeBSD (https://www.freebsd.org) is a robust Unix-like operating system that has been around for many years. FreeBSD is available on Amazon EC2 through Amazon Machine Images (AMIs) provided by FreeBSD developer and security office…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses
Course of the Month8 days, 16 hours left to enroll

765 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