Help with Database size script

newdbaxchange · #1 (**permalink**) 10-19-11, 06:47

Hello,

I'm not very good at scripting as my job is an DBA for a small firm, but now I'd like to implement a few cron jobs which access and report on the databases on an automatic basis.

This is my SQL TEXT

# -- Total size of Database Size in GB set echo off feedback off verify off pause off SELECT 'Database Size is --> ' || TO_CHAR(ROUND((select sum(bytes)/1024/1024/1024 from dba_data_files) + (select sum(bytes)/1024/1024/1024 from v$log),0)) || 'GB' FROM dual;

PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++++++++

exit

The SQL part works fine. However I would like to schedule this via cron, so it runs on a weekly basis at say 4am every Monday.

The mail part I believe is as follows - which I can end at the end of the script
mail -s 'Database Size for Database DB1' name@company.com < /tmp/dbsize.log

This will execute on a linux box under the Oracle user

Could someone please help and many thanks in advance

kitaman · #2 (**permalink**) 10-19-11, 09:13

Setting up a cron job has several steps.
First create a simple script like the following.

Code:

!#/bin/ksh
env

Then add an entry to the crontab file for the appropriate user (found in /var/spool/cron/crontabs)
This entry should look like (will run every hour on the hour)

Code:

0 * * * * /home/user/mycronscript.ksh >/home/user/mycronscript.log 2>&1

When you have added this, stop and restart the cron daemon from a terminal session as root.
#/etc/init.d/cron stop
#etc/init.d/cron start

Part two.
Cron jobs almost never have the same environment as a terminal session. Examine the output of this first cron job to see what variables have to be added to your oracle script for it to run.
Append your oracle report script to the above script and add any environment changes.
When you are satisfied that it runs correctly, change the run frequency to:

Code:

0 04 * * 1 /home/user/mycronscript.ksh >/home/user/mycronscript.log 2>&1

If you make changes to the crontab files, you must stop and start cron for the changes to take effect.

newdbaxchange · #3 (**permalink**) 10-19-11, 11:08

Hello thanks for your reply

I did the following:

script:

more uptime.sh
[oracle@mondbop001 {SOCPROD1} ~]$ more uptime.sh
#!/bin/ksh
env

output:

[oracle@mondbop001 {SOCPROD1} ~]$ ./uptime.sh
_=*18327*/bin/env
ADR_HOME=/u01/app/oracle/diag/rdbms/socprodcav/SOCPROD1
CVS_RSH=ssh
G_BROKEN_FILENAMES=1
HISTSIZE=1000
HOME=/home/oracle
HOSTNAME=mondbop001.prod.mon.bbc.co.uk
INPUTRC=/etc/inputrc
LANG=en_US.UTF-8
LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/dbhome_1/lib
LESSOPEN=|/usr/bin/lesspipe.sh %s
LOGNAME=oracle
LS_COLORS=no=00:fi=00:di=00;34:ln=00;36

i=40;33:s o=00;35:bd=40;33;01:cd=40;33;01

r=01;05;37;41:mi= 01;05;37;41:ex=00;32:*.cmd=00;32:*.exe=00;32:*.com =00;32:*.btm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00 ;32:*.tar=00;31:*.tgz=00;31:*.arj=00;31:*.taz=00;3 1:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz =00;31:*.bz2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00; 31:*.cpio=00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;3 5:*.xbm=00;35:*.xpm=00;35:*.png=00;35:*.tif=00;35:
MAIL=/var/spool/mail/oracle
NLS_DATE_FORMAT=DD-MON-YY HH24:MI

S
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
ORACLE_SID=SOCPROD1
ORACLE_UNQNAME=SOCPRODCAV
PATH=/u01/app/oracle/product/11.2.0/dbhome_1/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:.
PS1=[\u@\h {$ORACLE_SID} \W]$
PWD=/home/oracle
SHELL=/bin/bash
SHLVL=1
SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
SSH_CLIENT=10.182.194.23 3662 22
SSH_CONNECTION=10.182.194.23 3662 10.182.213.100 22
SSH_TTY=/dev/pts/2
TERM=xterm
USER=oracle
A__z="*SHLVL

crontab -l (as user Oracle)

# perform a calculation of the database size and mail the output to the Oracle DBA

#00 04 * * * /home/oracle/uptime.sh

OK. So what are the next steps? How do I get the to access the database via shell script and perform a select command?

Many thanks for your assistance

kitaman · #4 (**permalink**) 10-19-11, 11:26

Add ">/home/oracle/uptime.log 2>&1" to the end of the crontab line. This will save the ouptut of the script (both stdout and errout) to uptime.log. You first need to find out what entries you have to add to the script to have the cron environment match the output when you ran that script from a prompt.
Change the frequency of the cron job unless you only want to do one test per day.

newdbaxchange · #5 (**permalink**) 10-19-11, 11:50

Thanks for that - my crontab entry now looks like this:-

#00 04 * * * /home/oracle/uptime.sh >/home/oracle/uptime.log 2>&1

but I cannot get the script to enter the database - here is my script. It only has one select statement - can you tell me what I am doing wrong

set echo off feedback off verify off pause off
SELECT 'Database Size is --> ' || TO_CHAR(ROUND((select sum(bytes)/1024/1024/1024 from dba_data_files) + (select sum(bytes)/1024/1024/1024 from v$log),0)) ||
'GB'
FROM dual;
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++++++++
exit
END)

mail -s 'Database Size for Database DB1' name@company.com < /home/oracle/uptime.log

kitaman · #6 (**permalink**) 10-19-11, 12:01

The script that you show is an Oracle sql script not ksh.
I do not use Oracle but I assume that you need something like:
$oraclesql connect_string <uptime.sql
In other words, if you save your sql script to a file, how would you run it from a command prompt so as not to have to type it each time.

newdbaxchange · #7 (**permalink**) 10-20-11, 03:21

Hi,

If I saved my SQL to a file then every time I enter the database I would simple @filename. I have tested this and it works fine. What I am trying to do is schedule this job via cron on a nightly basis, but unsure how to access the database via shell.

Normally via shell prompt I would enter the database thus:-

[oracle@mondbop303 {SOCPROD1} ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 20 07:19:49 2011

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> set echo off feedback off verify off pause off
SELECT 'Database Size is --> ' || TO_CHAR(ROUND((select sum(bytes)/1024/1024/1024 from dba_data_files) + (select sum(bytes)/1024/1024/1024 from v$log),0)) ||
SQL> 2 'GB'
3 FROM dual;
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++++++++
'DATABASESIZEIS-->'||TO_CHAR(ROUND((SELECTSUM(BYTES)/1024/1024/
---------------------------------------------------------------
Database Size is --> 83GB
SQL>
++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++++++++

Can you assist - as all I'm trying to do is have this run automatically - many thanks

kitaman · #8 (**permalink**) 10-20-11, 08:25

Next step:
add the following lines to uptime.sh

Code:

sqlplus '/ as sysdba' < filename (your sql text)

Run this from a terminal session, probably (./uptime.sh) and see if you get the correct results.
If so:
From the command prompt run
env >myenv.txt
edit uptime.sh and replace the env command with myenv.txt.
Remove lines that are not required (colors, ssh*, TERM )
append "; export VARIABLENAME" to the end of each line.

Your script should now look like:

Code:

#!/bin/ksh
ADR_HOME=/u01/app/oracle/diag/rdbms/socprodcav/SOCPROD1
BROKEN_FILENAMES=1
HOME=/home/oracle
HOSTNAME=mondbop001.prod.mon.bbc.co.uk
INPUTRC=/etc/inputrc
LANG=en_US.UTF-8
LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/dbhome_1/lib
LESSOPEN=|/usr/bin/lesspipe.sh %s
NLS_DATE_FORMAT=DD-MON-YY HH24:MIS
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
ORACLE_SID=SOCPROD1; export ORACLE_SID
ORACLE_UNQNAME=SOCPRODCAV
PATH=/u01/app/oracle/product/11.2.0/dbhome_1/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:.; export PATH
sqlplus '/ as sysdba' <filename

I have only added the export to two of the lines as an example.

Forgot to add that you should use absolute file names as a general rule, and probably you should add
cd $HOME
just before the sqlplus statement.

Jack

newdbaxchange · #9 (**permalink**) 10-21-11, 06:21

sorry but your reply is rather confusing for a novice like me.

Could you just put together a script and I will execute that script and reply back on the output, whether it worked or not

Many thanks

DBFinder · #10 (**permalink**) 10-28-11, 20:11

Quote:

Originally Posted by newdbaxchange

sorry but your reply is rather confusing for a novice like me.

Could you just put together a script and I will execute that script and reply back on the output, whether it worked or not

Let me describe to you with a simpler example ! Will come up soon with an example !

DBFinder · #11 (**permalink**) 10-30-11, 12:24

Hello,

Here is an example to set up a cron job for SQL script.

Suppose following SQL script to be schedulled

Code:

set echo off feedback off verify off pause off

select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Current Time" from dual;
 
quit

For this script to run via cron, you will create a shell script.

When you run sql commands via shell script, you need to set up environment for that database ; for oracle you need to setup ORACLE_SID and ORACLE_HOME along with PATH.

Your script should look like this.

Code:

#!/bin/bash

export ORACLE_SID=orcl
export ORACLE_BASE=/app/oracle
export PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/app/oracle/product/11.2.0/dbhome_1/bin
export ORACLE_HOME=/app/oracle/product/11.2.0/dbhome_1

sqlplus / as sysdba @/home/oracle/my_sql_script.sql

I think upto here you should be fine. However if you cannot write this shell script, you can use following command on the shell prompt to generate it.

ps: make sure you do not cut-paste above script as it is. Your environment will be different than mine. So, either you edit paths as yours, or you run following line to generate it for you.

Code:

env | awk 'BEGIN{print "#!/bin/bash\n"}/^ORACLE|^PATH/ { print "export "$0 } END { print "\nsqlplus / as sysdba < /home/oracle/my_sql_script.sql"}' >/home/oracle/my_sql_script.sh

What this does is that it selects ORACLE and PATH lines to write to the script. Before this it writes shebang as first line of script. and at the end, it writes sqlplus line for running the SQL commands.

Third and final step will be to setup cron job. here is how you do it

Code:

crontab -e

Following line is needed to be added. Make a note of output redirection. I am appending the output file with ">>" instead of overwrite with ">". Following cron entry will run it at every 45 minutes on the clock.

Code:

45 * * * * /home/oracle/my_sql_script.sh >>/home/oracle/my_sql_script_output.txt 2>&1

The output should be like below if you have set the permissions on the shell script as executable.

Code:

SQL*Plus: Release 11.2.0.1.0 Production on Sun Oct 30 11:45:01 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Current Time
------------------------
Sun 30-Oct-2011 11:45:01
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

In above example

Code:

 
           shell script : my_sql_script.sh
           sql script   : my_sql_script.sh
           output file : my_sql_script.output.txt
           location    : /home/oracle

I hope this helps you understand how this works. If you have a further question, please respond back with the line you have difficulty with.

Regards
DBFinder

Internet Services

Web Development

Digital Marketing

Consumer Tech