Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Shell script to connect

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Shell script to connect
# 1  
Old 02-15-2016
Shell script to connect
hello
i try to made a report to conect all database to diferent OS
HP-UX/LINUX/SOLARIS/AIX
this is my example
i have 5 db
OS are HP-UX
Code:
ps -fea | grep pmon
    root  1120     1  0  Nov 29  ?         5:14 ipmon -sD
  oracle 10286     1  0  Nov 29  ?        27:19 ora_pmon_BD1
  oracle 10431     1  0  Nov 29  ?        20:25 ora_pmon_BD2
  oracle 11728     1  0  Nov 29  ?        24:40 ora_pmon_BD3
  oracle 11882     1  0  Nov 29  ?        46:59 ora_pmon_BD4
  oracle 12068     1  0  Nov 29  ?        25:42 ora_pmon_BD5

my script

Code:
ps -fea | grep '[_]pmon[_]' | while read -r line
do	printf '%s\n' 
# Assign the ORACLE_SID
ORACLE_SID="${line##*_pmon_}"
export ORACLE_SID
echo  "My db :" $ORACLE_SID
done

pgrep -lf ora_pmon |
  while read p a; do

#Find ORACLE_HOME info for current instance
     ORATABLINE=`printf '%s\n'  \
     "$( dbhome "${a#ora_pmon_}" )"`
    ORACLE_HOME=`echo $ORATABLINE`
    export ORACLE_HOME
echo "my Home:" $ORACLE_HOME

  # Put $ORACLE_HOME/bin into PATH and export.
    PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/etc ; export PATH

done

sqlplus -s <<EOF
connect / as sysdba
select instance_name,status,database_status from v\$instance;
select * from v\$version;
EOF
done

when im run the script

My db: BD1
My db : BD2
My db :BD3
My db : BD4
My db: BD5
my Home: /oracle/10.2

try to connect to sqlplus but
only coneect BD5
and execute the 5 times

can help me
to the result look like this:
Code:
My db: BD1
my Home: /oracle/10.2
My db : BD2
my Home: /oracle/10.2
My db : BD3
my Home: /oracle/10.2
My db : BD4
my Home: /oracle/10.2
My db: BD5
my Home: /oracle/10.2


Moderator's Comments:
Mod Comment Please use code tags for all your data and code next time, thanks
Last edited by vbe; 02-15-2016 at 03:37 PM.. Reason: code tags
# 2  
Old 02-16-2016
Hi,
to connect to all 5 databases you have to put the sqlplus command inside the loop and have the variables ORACLE_SID and ORACLE_HOME assigned. Your script assigns ORACLE_SID 5 times, overwriting the previous value with each iteration of the loop.
Then the same happens to your ORACLE_HOME variable and finally you connect to the last database found. This is a starting point how I'd rewrite your script:
Code:
OLD_PATH=$PATH
ps -fea | grep '[_]pmon[_]' | while read -r line; do
   # Assign the ORACLE_SID
   ORACLE_SID="${line##*_pmon_}"; export ORACLE_SID
   echo  "My db :" $ORACLE_SID
   #Find ORACLE_HOME info for current instance
   ORACLE_HOME=$(dbhome $ORACLE_SID); export ORACLE_HOME
   echo "my Home:" $ORACLE_HOME
   # Put $ORACLE_HOME/bin into PATH and export
   PATH=$ORACLE_HOME/bin:$OLD_PATH; export PATH
   sqlplus -s <<EOF
      connect / as sysdba
      select instance_name,status,database_status from v\$instance;
      select * from v\$version;
EOF
done

This User Gave Thanks to cero For This Post:
ceciaide
# 3  
Old 02-16-2016
hello Cero

thanks a lot.

it works.

SmilieSmilie
# 4  
Old 02-17-2016
Hello Cero


I will try another server and it works.
but in some the oracle_home short puts the address
example:

Code:
My db : DB2
my Home: /opt/oracle

another database
correct
Code:
My db : DB1
my Home: /opt/oracle/app/oracle/product/10.2.0.2

can you help me
# 5  
Old 02-18-2016
Do you see incorrect ORACLE_HOMEs? This may be caused by the way the dbhome script works: it reads the oratab file, but in case it is not found it shows the home directory of the user oracle.
You can fix that by reading oratab directly:
Code:
#!/bin/bash
case "$(uname)" in
   *SunOS*) ORATAB=/var/opt/oracle/oratab
            ;;
   *)       ORATAB=/etc/oratab
            ;;
esac
[ ! -f $ORATAB ] && echo "File $ORATAB not found!" >&2 && exit 1

OLD_PATH=$PATH
while IFS=":" read ORACLE_SID ORACLE_HOME AUTOSTART; do
   if ps -eaf |grep pmon[_]$ORACLE_SID >/dev/null; then
      echo "My db  : $ORACLE_SID"
      echo "My home: $ORACLE_HOME"
      PATH=$ORACLE_HOME/bin:$OLD_PATH
      sqlplus -s / as sysdba <<EOF
         select instance_name,status,database_status from v\$instance;
         select * from v\$version;
EOF
   fi
done <$ORATAB

# 6  
Old 02-18-2016
hello Cero.


the oratab is not configured almost all servers.

i try to execute but its not work only find one

Code:
My db  :
My home:
Error 6 initializing SQL*Plus
Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
My db  :
My home:
Error 6 initializing SQL*Plus
Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
My db  :
My home:
Error 6 initializing SQL*Plus
Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
grep: can't open This
grep: can't open file
grep: can't open is
grep: can't open used
grep: can't open by
grep: can't open ORACLE
grep: can't open utilities.
grep: can't open It
grep: can't open is
grep: can't open created
grep: can't open by
grep: can't open root.sh
grep: can't open and
grep: can't open updated
grep: can't open by
grep: can't open the
grep: can't open Database
grep: can't open Configuration
grep: can't open Assistant
grep: can't open when
grep: can't open creating
grep: can't open a
grep: can't open database.
My db  :
My home:
Error 6 initializing SQL*Plus
Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
grep: can't open A
grep: can't open colon,
grep: can't open '
grep: can't open the
grep: can't open entry.
grep: can't open Lines
grep: can't open beginning
grep: can't open with
grep: can't open a
grep: can't open pound
grep: can't open sign,
grep: can't open '#',
grep: can't open are
grep: can't open comments.
grep: can't open Entries
grep: can't open are
grep: can't open of
grep: can't open the
grep: can't open form
grep: can't open $ORACLE_SID
grep: can't open The
grep: can't open first
grep: can't open and
grep: can't open second
grep: can't open fields
grep: can't open are
grep: can't open the
grep: can't open system
grep: can't open identifier
grep: can't open and
grep: can't open home
grep: can't open directory
grep: can't open of
grep: can't open the
grep: can't open database
grep: can't open respectively.
grep: can't open The
grep: can't open third
grep: can't open filed
grep: can't open indicates
grep: can't open to
grep: can't open the
grep: can't open dbstart
grep: can't open utility
grep: can't open that
grep: can't open the
grep: can't open database
grep: can't open should
grep: can't open ,
grep: can't open "Y",
grep: can't open or
grep: can't open should
grep: can't open not,
grep: can't open "N",
grep: can't open be
grep: can't open brought
grep: can't open up
grep: can't open at
grep: can't open system
grep: can't open boot
grep: can't open time.
grep: can't open Multiple
grep: can't open entries
grep: can't open with
grep: can't open the
grep: can't open same
grep: can't open $ORACLE_SID
grep: can't open are
grep: can't open not
grep: can't open allowed.
My db  :db
My home: /opt/oracle/app/oracle/product/10.2.0.2

INSTANCE_NAME    STATUS       DATABASE_STATUS
---------------- ------------ -----------------
db         OPEN         ACTIVE


BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production


this Server is SunoS
# 7  
Old 02-19-2016
Sorry, I forgot to address the comments present in oratab for some installations. This version takes care of them an is a bit more robust:
Code:
#!/bin/bash
case "$(uname)" in
   *SunOS*) ORATAB=/var/opt/oracle/oratab
            ;;
   *)       ORATAB=/etc/oratab
            ;;
esac
[ ! -f $ORATAB ] && echo "File $ORATAB not found!" >&2 && exit 1

OLD_PATH=$PATH
grep -v "^#" $ORATAB |while IFS=":" read ORACLE_SID ORACLE_HOME AUTOSTART; do
   if  ps -eaf |grep "pmon[_]$ORACLE_SID" >/dev/null; then
      echo "My db  : $ORACLE_SID"
      echo "My home: $ORACLE_HOME"
      PATH=$ORACLE_HOME/bin:$OLD_PATH
      sqlplus -s / as sysdba <<EOF
         select instance_name,status,database_status from v\$instance;
         select * from v\$version;
EOF
   fi
done

The oratab file is the standard way to find oracle homes, and it is used by scripts that come with a standard installation. I don't know any other OS independent way to find them on a server.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Not able to connect to Oracle server from shell script

Hi, I am facing an issue in connecting to database from Linux shell script. Here is my code export ORACLE_BASE=xxx export ORACLE_SID=xxx export ORACLE_HOME=xxxx DB_PATH=xxx/'yyy@yy' UserId=`sqlplus -s $DB_PATH <<EOF set head off feedback off select max(USER_ID)+1 from USERS;... (6 Replies)
Discussion started by: yuvi
6 Replies

2. Shell Programming and Scripting

How to Connect to Teradata and Run in Shell Script?

Hi Master, I need to create dan run script in linux to get data from teradata I did this ---------- Post updated at 02:28 AM ---------- Previous update was at 02:25 AM ---------- LOG_DIR="/home/"; OUTDIR="/home/"; SCRIPT_NAME=“test"; DUMPFILE="${OUTDIR}${SCRIPT_NAME}"_"$(date... (2 Replies)
Discussion started by: radius
2 Replies

3. Shell Programming and Scripting

Shell script to connect from one server to other

Dear Experts, I am new to the shell scripting. I am looking for a shell script to connect to one Unix/Linux server1 to other Unix/Linux server2 and trigger a SAP Event in that server2 (Which will trigger a job in SAP). Is this possible to connect from one server to the other server securely... (7 Replies)
Discussion started by: Venu V Reddy
7 Replies

4. Shell Programming and Scripting

How to connect to Oracle database using shell script?

Hi All, I am newbie to unix shell scripting. I want to connect to oracle database using shell script. I know how to connect DB using user name and password but my question is if my password is having '@' sign then how to connect. I have tried like below, cnt=`sqlplus -s /nolog << EOFSQL ... (3 Replies)
Discussion started by: pmotewar
3 Replies

5. Shell Programming and Scripting

Shell Script to connect to the oracle database

Hi Unix Gurus, I have a requirement to write a script, Oracle DB gets referesh every 6hrs, hence we need write a script to connect to the database , if it connects it should generate a file to proceed the next job application or when an error when connectiong to DB it should not generate any... (8 Replies)
Discussion started by: bshivali
8 Replies

6. Shell Programming and Scripting

connect to db2 using shell script

Guys, I am trying to write a shell script that connect to wcsprod database and read the query #!/bin/ksh sqlplus -s < connect to wcsprod user wcsadm using pwd > select * from catentry fetch first 1 row only with ur; databse: wcsprod user: wcsadm pwd: pwd thanks (1 Reply)
Discussion started by: skatpally
1 Replies

7. Shell Programming and Scripting

Connect to oracle db using shell script

Hi, I want to connect to oracle db using unix shell script. And i want to retrieve data from db through shell script. Please help me as soon as possible. Thanks, J.P. Das (1 Reply)
Discussion started by: jyotidas
1 Replies

8. Shell Programming and Scripting

vpn connect/disconnect shell script

Hi I am not so good in scripting..trying ot learn it...need guidance of the experts in shell scripting.. Let me explain the scenario first.. a server MX1 is connected to another server MX2 through vpn..every 5 minute a script runs to test vpn connectivity between the 2 servers.when the vpn... (12 Replies)
Discussion started by: renuka
12 Replies

9. Shell Programming and Scripting

Connect two servers in shell script

Hello all, I know that is a question which has made too many times, and I have been looking into the threads but all posted was not sucessfully for me so... I need a shell script which connect two unix servers, but NOT using ssh... Is there another way to do it? I've tried ssh but it didn't... (1 Reply)
Discussion started by: Geller
1 Replies

10. Shell Programming and Scripting

How to connect DB2 table using shell script

Hi All, I want to connect two tables in DB2 using shell script and then compare the contents of two tables field by field. Could any one please help me in connecting database tables using Unix and retriving data from the same. Thanks, Durwas (0 Replies)
Discussion started by: dtidke
0 Replies
Login or Register to Ask a Question