[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Errors connecting to database when running sqlplus from ksh script

Posted on 2013-06-14
6
Medium Priority
?
818 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 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

650 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