Executing SQL from Shell Script

AskDBA.org Forum » Real Application Cluster

Executing SQL from Shell Script

(3 posts)

Average Rating For This Topic:

Rate This Topic Yourself:

Started 8 months ago by deepika.dhamija@gmail.com
Latest reply from deepika.dhamija@gmail.com
This topic is closed
This topic is resolved

No tags yet.

deepika.dhamija@gmail.com

junior member
Joined: Dec '09
Posts: 9
offline
Reputation:12

Hi,

I have a two node RAC Cluster Setup (10R2) and I want to execute sql queries from a shell script, to be run as root.
I have the foll test.sh script

#! /bin/bash
echo "In BASH"
sqlplus / <<+ENDOFSQL+
select name, path from v$asm_disk;
exit
+ENDOFSQL+
echo "Back to Bash"
exit

and I get the foll O/P
In BASH

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 23 07:25:32 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:
ORA-12546: TNS:permission denied

Enter user-name: SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon> ::= <username>[/<password>][@<connect_identifier>] | /
Enter user-name: Enter password:
ERROR:
ORA-12546: TNS:permission denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
Back to Bash

What permissions should for this?

2) Also I need to know the node names of the nodes that form the clustre. I add the foll lines to my script
NODE_NAMES=olsnodes
echo $NODE_NAMES

ad I get O/P
linux-1 linux-2
Is it possible to store these space separated node names in diff variables that can then be used. (the number of nodes are variable)

Any suggestion/advise is appreciated. Thanks

Posted 8 months ago #
Amit Bansal

Oracle DBA
Joined: Jun '08
Posts: 48
offline
Reputation:63

Deepika,

Please find response below

1) Your first script has mistake. Should be like

export ORACLE_SID=+ASM1
sqlplus / as sysdba <<EOF
select name, path from v$asm_disk;
exit
EOF

2)

node_list=olsnodes
node1=echo $node_list|awk '{print $1}'
node2=echo $node_list|awk '{print $2}'
echo "Node 1 is " $node1
echo "Node 2 is " $node2

Hope this helps

Cheers
Amit
http://askdba.org/weblog/

Posted 8 months ago #
deepika.dhamija@gmail.com

junior member
Joined: Dec '09
Posts: 9
offline
Reputation:12

Thanks Amit.

I was able to execute the SQL using the command
sqlplus <username>/<password>@<orcl_sid>

Posted 8 months ago #

RSS feed for this topic

Topic Closed

This topic has been closed to new replies.

AskDBA.org Forum

Subscribe to aSkDba.org Forum Posts

AskDBA.org Forum » Real Application Cluster

Executing SQL from Shell Script

Topic Closed