[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Errors connecting to database when running sqlplus from ksh script

Posted on 2013-06-14
6
Medium Priority
?
821 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
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Background Still having to process all these year-end "csv" files received from all these sources (including Government entities), sometimes we have the need to examine the contents due to data error, etc... As a "Unix" shop, our only readily …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
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
Suggested Courses
Course of the Month19 days, 2 hours left to enroll

834 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