Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2010
    Posts
    81

    Unanswered: Help with Database size script

    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

  2. #2
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    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.

  3. #3
    Join Date
    Jun 2010
    Posts
    81
    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;36i=40;33:so=00;35:bd=40;33;01:cd=40;33;01r=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;31:*.lzh=00;31:*.zip=00;31:*.z=00; 31:*.Z=00;31:*.gz=00;31:*.bz2=00;31:*.bz=00;31:*.t z=00;31:*.rpm=00;31:*.cpio=00;31:*.jpg=00;35:*.gif =00;35:*.bmp=00;35:*.xbm=00;35:*.xpm=00;35:*.png=0 0;35:*.tif=00;35:
    MAIL=/var/spool/mail/oracle
    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
    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

  4. #4
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    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.

  5. #5
    Join Date
    Jun 2010
    Posts
    81
    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

  6. #6
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    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.

  7. #7
    Join Date
    Jun 2010
    Posts
    81
    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

  8. #8
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    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
    Last edited by kitaman; 10-20-11 at 09:52.

  9. #9
    Join Date
    Jun 2010
    Posts
    81
    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

  10. #10
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by newdbaxchange View Post
    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 !

  11. #11
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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
    Last edited by DBFinder; 10-30-11 at 14:54.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •