Shell Script to connect to the oracle database

Shell Programming and Scripting
1

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 file so my next jobs will not be triggered.

Thanks in Advance !!!

Shiv

2

Hello what have you tried so far and where are you stuck?

3

Also,

Please search this forum with the keyword "sqlplus oracle"

Google Search Results for sqlplus oracle | The UNIX and Linux Forums

4

Thanks Scrutinizer for the reply, Sorry i am new to scripting i did not even start with head , please help me out

5

We are all volunteering our time and expertise, we can give you the direction you need, but you have to put some effort into solving your own problem. This is not a contracted support hotline.

6

Please explain "Oracle DB gets referesh every 6hrs" it means refresh the entire db as like all objects in db?

7 
#!/bin/bash

LogDirectory='/var/tmp/logs'
DataDirectory='/var/tmp/data'

DBUSER='scott'
DBUSERPASSWORD='tiger'
DB='oracle'

sqlplus -s <<EOF  > ${LogDirectory}/query.log
${DBUSER}/${DBUSERPASSWORD}@${MYDB}
set linesize 32767
set feedback off
set heading off

select * from dual;

EOF
8

@bshivali
It would seem to be time to attend a Shell Scripting course to match whatever computer Operating System and Shell you are using, and to then attend and Oracle Administration course for whatever version of Oracle you are running.

We are not prepared to propose code blind when we know nothing about your environment and when you have no training.

@susant4u
I'm not sure about this post in the context of Post #1 . The important point is to detect whether the initial connection to the Oracle database fails.

@bshivali
At a system design level I think that you need to design a schedule which avoids submitting any job which cannot run because the Oracle database has a scheduled maintenance window. Whether you can connect to the database becomes irrelevant if a few seconds later it is going down.
Once you have the schedule, consider using unix cron to create/remove the flag file you desire which tells the applications when the Oracle database is in a maintenance window. Obviously that maintenance window would start before the database actually goes down to allow a normal length job to complete.

9

Thanks for the code..!! really :)helpfull..