script

Linux and Unix-Like Linux
1

delete

2

try change to

 if [[ $(grep -E "ORA-|ERROR" /tmp/schema.log ) ]]
then
mailx -s "USER Stats gather succeed on instance xyz on host $a" $PSDBA < /tmp/schema.log
else
mailx -s "USER Stats gather failed on instance xyz on host $a" $PSDBA < /tmp/schema.log
exit 0
fi

or

[ $(grep -E "ORA-|ERROR" /tmp/schema.log ) ] || ( mailx -s "USER Stats gather failed on instance xyz on host $a" $PSDBA ) < /tmp/schema.log
3

It is not tested, but I think it works. If you face problems, let me know.

There are several you can improve on it.

cat scriptDb.sh
############
#!/bin/ksh
ORACLE_SID=$1
a=`hostname`
PSDBA="xxxxxx"

# OS Definition
myPID=$$

# Files
sqlFile="<PathTo>/scriptDb.sql"
sqlplusOutputLog="/tmp/schema_${myPID}.log"

# Sleep time in seconds
maxSleepTime=300
loopSleepTime=5
countLoopSleetTime=0

nohup sqlplus -L @"${sqlFile}" "${sqlplusOutputLog}" 1>/dev/null 2>&1 &

while true
do
	if [ ${countLoopSleetTime} -ge ${maxSleepTime} ]
	then
		pid=`ps -ef | egrep "${sqlFile}" | awk '{print $2}'`
		kill -15 ${pid}
		break
	fi
	countLoopSleetTime=`expr ${countLoopSleetTime} + ${loopSleepTime}`
done

chk_err=`egrep -i 'ORA-|SP2-|TNS-|Usage' "${sqlplusOutputLog}" | wc -l`
if [ $chk_err -ne 0 ]
then
	mailx -s "USER Stats gather failed on instance xyz on host $a" $PSDBA < "${sqlplusOutputLog}"
	exit 0
fi

cat scriptDb.sql
############
SET VERIFY OFF
SET TIMING ON

WHENEVER SQLERROR EXIT SQL.SQLCODE

spool &1

CONNECT system/system

show user
select instance_name from v$instance;
select TO_CHAR(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;

exec dbms_stats.GATHER_SCHEMA_STATS (ownname=>'USER', block_sample=>true, granularity=>'AUTO', options=>'GATHER', estimate_percent=>SYS.DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL COLUMNS SIZE auto', degree=>null, cascade=>SYS.DBMS_STATS.AUTO_CASCADE, no_invalidate=>SYS.DBMS_STATS.AUTO_INVALIDATE,forc e=>true);

spool off

EXIT

I hope it helps!

---------- Post updated at 15:15 ---------- Previous update was at 15:12 ----------

I forgot two things:
- To validate the log file inside the while loop:
chk_err=`egrep -i 'ORA-|SP2-|TNS-|Usage' "${sqlplusOutputLog}" | wc -l`
if [ $chk_err -ne 0 ]
then
break
fi
- Add the sleep inside the loop:
sleep ${loopSleepTime}

---------- Post updated at 15:17 ---------- Previous update was at 15:15 ----------

Also you can change the kill from 15 to 9:

kill -15 ${pid}
kill -9 ${pid}
4

Thanks for your Help

5

Hi felipe.vinturin ,

             I understand your script little bit as i am new to scripting i am unable to understand some part of the script. If possible can you help me in understanding the script.

Thanks in Advance...

6

Hi,

I attached the script with some comments. If you have doubts, let me know.

Regards.

7

Hi,

can u please tell me what these statement mean

pid=`ps -ef | egrep "${sqlFile}" | awk '{print $2}'`
chk_err=`egrep -i 'ORA-|SP2-|TNS-|Usage' "${sqlplusOutputLog}" | wc -l`
and how can we get the myPID=$$ .

    Thanks for your quick reply. Can you help me how to learn shell scripting. If you have any good docs for shell scripting so that starters can easily learn .

Thanks..

8

Check below:

pid=`ps -ef | egrep "${sqlFile}" | awk '{print $2}'`
Returns the SQLPlus PID, filtering the "ps" command output using the SQL file, in this case: scriptDb.sql.

===============================================
chk_err=`egrep -i 'ORA-|SP2-|TNS-|Usage' "${sqlplusOutputLog}" | wc -l`
Returns the number of Oracle errors in the log file pointed by: "${sqlplusOutputLog}".
This part:
egrep -i 'ORA-|SP2-|TNS-|Usage' "${sqlplusOutputLog}"
tries to find in the log file the words: "ORA-", "SP2-", "TNS-", "Usage" and if it finds, it prints to the screen, so the command "wc -l", count the lines returned.

If you want you can execute each part of a pipe, as the next part will always do something with the previous command output.

About learning shell script, the following link seems to be a good start:
Unix shell scripting with ksh/bash

And if you have doubts, you can read the manual pages of each command, like: "man ps", there you will find all the command options and if you could not find the answer you are looking for, you can post a question here in the forum, we have excelent people here! =o)

I hope it helps!

Regards!

9

i have a small question
how we can get pid from dbscript.sql script
pid=`ps -ef | egrep "${sqlFile}" | awk '{print $2}'`

Thanks,

10

As it is a database script, which runs in SQLPlus, there is no easy way.

By the way, why do you need it?

11

I would like to know how can we find the pid of that sql session because we need pid to kill that session if it takes more time

Could you please help me how to find the pid

Thanks a lot for Your help all the time

12

I think the script is not clear for you!

When you execute SQLPlus:

sqlplus <User><Pass>@${ORACLE_SID}

It opens a connection to the database and you can find it in the V$session view and when you kill the sqlplus process, it also closes the database connection, so, when the steps below are executed, the database connection is also closed:

pid=`ps -ef | egrep "${sqlFile}" | awk '{print $2}'`
kill -9 ${pid}
13

hi

I have a question
pid=`ps -ef | egrep "${sqlplusoutputlog}" | awk 'NR==19 {print $0}'`

in the above command i want to get the number from the sqlplusoutputlog and that number is in 19th line. I don't know whether the syntax is correct or not
could you please help me in this

thanks,

14

What number ??

Again, what number is it ?
19th line of what ? Do you mean 19th line of the ${sqlplusoutputlog} file ?

tyler_durden

15

Hi

yes i have process id of a session in ${sqlplusoutputlog} file and it is in 19th line and i need to get that number assigned to a variable in a shell script
please provide me with a correct sysntax

Thanks...

16

It's difficult to figure out a way of doing that unless you show us line number 19 of your log file.

tyler_durden

17

What do you want to achieve?
What will have in the 19th line of ${sqlplusoutputlog}?

This command does nothing with ${sqlplusoutputlog} file, it only uses it to filter the "ps" command result.

pid=`ps -ef | egrep "${sqlplusoutputlog}"`

If you want the 19th line of the ${sqlplusoutputlog}, just:

# Example
# cat TestFile.txt
1
2
3
4
5
6
7
8
9
10

# Direct command
# awk 'NR==5{print $0}'
5

# Value in a variable
# lineValue=`awk 'NR==5{print $0}' TestFile.txt`
# echo ${lineValue}
5

# Your command
# lineValue=`awk 'NR==19 {print $0}' "${sqlplusoutputlog}"`
# echo ${lineValue}
<Your result: ???>

Regards!

---------- Post updated at 08:43 ---------- Previous update was at 08:41 ----------

Quoting my own post!

Sorry! |Too late answer, but I will keep it here.

---------- Post updated at 08:43 ---------- Previous update was at 08:43 ----------

Quoting my own post!

Sorry! Too late answer, but I will keep it here.

18

HI Felipe,

No problem i have to thank you for your help to me

i have done with that script.

I have a question i am writing another shell script in that i am checking some process using ps -ef |grep
and if the process is not there i need to start that process
but the problem is the process has to start from root
how can i go to the root from oracle user in the shell script
it will ask for the password but i dont want to use that
is there any other way please let me know

Thanks..

19

If you add oracle user to the sudoers list, then you can use the sudo command to start your process as root, with or without password.

There's a lot of material on sudo and sudoers out there on the Internet or on this forum. Google is your friend.

tyler_durden

20

Hi,

I dont want oracle user to get access to the root with out having password

I need to run just only one command from oracle which actually has to run as root

how can i keep that command in the sudoers so that when i run that command from oracle wont ask for password

thanks