shall script oracle R12

schedule scripts:

1 ) Rman daily backup;


0 04 * * * sh /home/oracle/scripts/rman_script/rman__db.sh



====


. ~/.bash_profile

find /backup/PROD_DB_BACKUP/DB -mtime +2 -type f -exec rm {} \;

export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'
LOG=/backup/WEPPROD_DB_BACKUP/LOGS/rmanPROD`date +%d.%m.%Y_%T`.log
rman  target / LOG = $LOG <<EOF
run {
crosscheck backup;
delete force  noprompt obsolete;
allocate channel t1 device type disk;
allocate channel t2 device type disk;
allocate channel t3 device type disk;
allocate channel t4 device type disk;
allocate channel t5 device type disk;
allocate channel t6 device type disk;
allocate channel t7 device type disk;
allocate channel t8 device type disk;
backup as COMPRESSED BACKUPSET
     format '/backup/_DB_BACKUP/DB/%d_%t_%s_%p'
        tag 'dbmain_full_backup' database;
SQL 'alter system archive log current';
crosscheck archivelog all;
backup as compressed backupset archivelog all format '/backup/DB_BACKUP/DB/%d_Arch_Log%t_%s_%p';
    backup
    tag = cf1
   format '/backup/_BACKUP/DB/%d_CONTROL_%t_%s_%p' current controlfile;
release channel t1;
release channel t2;
release channel t3;
release channel t4;
release channel t5;
release channel t6;
release channel t7;
release channel t8;
}
EOF

CAP=`echo $ORACLE_SID|tr '[a-z]' '[A-Z]'`
#MAIL_ID="gopinath.k@jin,beniston.thangaraj@jsw.in,venkat.p@wepsol.in,wep.dba@jsw.in"
MAIL_ID="Oracle.Alert@wepinam.s@.in,ajaykumar.ch@.in,Hari.Krishna@@"

#tail -10 $LOG | grep ^"RMAN-"

#cat $LOG | grep ^"RMAN-"|grep ^"ORA-"
tail -30 $LOG | grep ^"RMAN-"

if [ $? -ne 0 ]
then
mail -s "Rman Backup Successful of $CAP on `date`"   $MAIL_ID < $LOG
else
mail -s "Rman Backup Failed of $CAP on `date`" $MAIL_ID <$LOG
fi



==========================================



2 ) Per day how much archive log files will generating in Production instance:
--------------------------------------------------------------------------

0 07 * * * sh /home/oracle/scripts/cron_scripts/sql/log_per_day.sh



[oracle@dbwep ~]$ more /home/oracle/scripts/cron_scripts/sql/log_per_day.sh

. /oracle/WEPPROD/db/tech_st/10.2.0/_connect.env
#rm /tmp/archive.html
sqlplus system/manager @/home/oracle/scripts/cron_scripts/sql/log_per_day.sql << EOF
exit
EOF
mail -s "WEPPROD Archives Generated Report"Oracle.Alert@gmail.com, <

/tmp/archive.lst


Base on "log_per_day.sh" will generating and given the out put.-----


set pagesize 200
set linesize 100

col d_dt for a10
col DATE new_value ye_date
col db_name new_value SID
!rm /tmp/archive.lst
--spool /tmp/archive.lst
--select sysdate-1 as yest_date from dual;
select name db_name from v$database;
--!rm /tmp/archive.lst
---set markup html on
spool /tmp/archive.lst
--TTITLE CENTER 'ye_date'
--TTITLE LEFT 'Archives Generated for 'SID ' on ' ye_date
TTITLE LEFT 'Database Name - ' SID


SELECT Start_Date,
       Start_Time,
       Num_Logs
----   ROUND(Num_Logs * (Vl.Bytes / (1024 * 1024)),
    -- 2) AS Mbytes
      -- Vdb.NAME AS Dbname
  FROM (SELECT TO_CHAR(Vlh.First_Time,
                       'YYYY-MM-DD') AS Start_Date,
               TO_CHAR(Vlh.First_Time,
                       'HH24') || ':00' AS Start_Time,
               COUNT(Vlh.THREAD#) Num_Logs
          FROM V$log_History Vlh WHERE TRUNC(vlh.first_time) =  TRUNC(SYSDATE-1)
         GROUP BY TO_CHAR(Vlh.First_Time,
                          'YYYY-MM-DD'),
                  TO_CHAR(Vlh.First_Time,
         'HH24') || ':00') Log_Hist,
       V$log Vl,
       V$database Vdb
 WHERE Vl.GROUP# = 1
  ORDER BY Log_Hist.Start_Date,
        Log_Hist.Start_Time;

TTITLE off
clear col
undefine SID
undefine ye_date
--set markup html off
spool off

You have new mail in /var/spool/mail/oracle


===================================================================================


3) :- Inactive session sechdule-program;
----------------------------------------

0 9,14,18 * * * sh /home/oracle/scripts/cron_scripts/shell/inactwep.sh




#!/bin/ksh
. /oracle/WEPPROD/db/tech_st/12.1.0/WEPPROD_dbwep.env
home_dir=`echo /home/oracle/scripts/cron_scripts` ; export home_dir
shell_dir=`echo $home_dir/shell/` ; export shell_dir
sql_dir=`echo $home_dir/sql/` ; export sql_dir
out_dir=`echo $home_dir/Logs/` ; export out_dir
v_date=`date +"%Y%m%d%H:%M"`
LOGFILE=/tmp/inactive_$1.log.$v_date; export LOGFILE
FILE=/tmp/inactive.lst
dba_list="Oracle.Alert@gmail.com,

ol.in,"
#dba_list="valliappan.ramanathan@gmail,afsal.mohammed@gmail.cpm"

apps_user=apps
apps_pwd=wep2dba
db_connect="$apps_user/$apps_pwd@prod"

if [ -f $FILE ]
then
        echo "File exists"
        rm $FILE
fi

run_spr=`sqlplus -s $db_connect @$sql_dir/inactwep.sql << EOF
exit
EOF`
echo $?
echo $run_spr
if [ $? -ne 0 ]
  then
     echo "problems with sqlplus" >> $LOGFILE
  else
     echo ' '
if [ -f $FILE ]
        then
                mail -s "Inactive session .......${v_date}" $dba_list < /tmp/inactive.lst
        else
                echo "" > /dev/null
        fi
fi



ii) scripts:

spool /tmp/inactive.lst


 SELECT
 s.sid,
 s.serial#,
 s.machine,
 to_char(s.logon_time,'mm-dd-yy hh24:mi:ss') Logon_Time,
 s.last_call_et/7200 Last_Call_ET,
 s.action,
 s.module
 FROM
        V$SESSION s,v$PROCESS p
        WHERE s.paddr = p.addr AND
        s.username IS NOT NULL AND
        s.last_call_et/7200 > 1 and
        s.status='INACTIVE' and
        s.module not like 'JDBC%'
        order by logon_time
/
spool off




===============================================================

4: -

All concurrent Program schedule crontab -l:
-----------------------------------------

0 07 * * * sh /home/oracle/scripts/cron_scripts/shell/run_it.sh Prod 2>&1 >/dev/null





#!/bin/ksh
#set -x
#dba_list="wep.dba@gmailcom"
dba_list="Oracle.Alert@gmail.com"
ORACLE_HOME=/oracle//db/tech_st/12.1.0;export ORACLE_HOME
PATH=$PATH:/usr/openwin/bin:/usr/local/bin:$ORACLE_HOME/bin:.;export PATH

ORACLE_SID=PROD;export ORACLE_SID
ORAENV_ASK=NO; export ORAENV_ASK
#. oraenv

home_dir=`echo /home/oracle/scripts/cron_scripts` ; export home_dir
shell_dir=`echo $home_dir/shell/` ; export shell_dir
sql_dir=`echo $home_dir/sql/` ; export sql_dir
out_dir=`echo $home_dir/Logs/` ; export out_dir
v_date=`date +"%Y%m%d%H:%M"`
LOGFILE=/tmp/run_it_$1.log.$v_date; export LOGFILE

. /oracle/PROD/db/tech_st/12.1.0/PROD_dbwep.env

   apps_user=apps
   apps_pwd=apps
db_connect="$apps_user/$apps_pwd@$1"

run_spr=`sqlplus -s $db_connect @/home/oracle/scripts/cron_scripts/sql/daily.sql $1 << EOF
exit
EOF`

  if [ $? -ne 0 ]
  then
     echo "problems with sqlplus"
  else
     echo ' '
     echo $out_dir
     if [ -s  $out_dir\Daily_run_$1.txt ]
     then
               echo "Sending E-mail for $1 Production report ...."
               mail -s "$1 Production Status Report" $dba_list < $out_dir/Daily_run_$1.txt
     else
       echo "no report exists"
     fi
  fi






ii) Base above shall scripts call this sql scripts =" daily.sql".


set trimspool on
set feedback off
set term off
set verify off

spool /home/oracle/scripts/cron_scripts/Logs/Daily_run_&&1..txt

rem start /home/oracle/scripts/cron_scripts/sql/cnt_requests.sql
start /home/oracle/scripts/cron_scripts/sql/cnt_req_users.sql
start /home/oracle/scripts/cron_scripts/sql/max_run_programs.sql
start /home/oracle/scripts/cron_scripts/sql/top_running_jobs.sql
start /home/oracle/scripts/cron_scripts/sql/conc_req_error_rpt.sql
start /home/oracle/scripts/cron_scripts/sql/pend_dtl.sql &&1

exit


iii)

 more /home/oracle/scripts/cron_scripts/sql/cnt_requests.sql    --> scripts


set lines 120
set pages 0
col cnt format 999999 heading "Total No of Requests"
--- ttitle 'Number of Concurrent Requests ' skip 2
select '                         Number of Concurrent Requests for ', sysdate - 1 from dual ;

set pages 900
 select  count(*) cnt
 from fnd_concurrent_requests
 where trunc(actual_start_date) = trunc(sysdate) -1
/






iv) start /home/oracle/scripts/cron_scripts/sql/cnt_req_users.sql

select fcr.cnt "Conc Reqs", icxs.self_serv_user_count "SelfServ Users", icxs.self_serv_session_count "SelfServ

Sessions", fl.forms_user_count "Forms Users", fl.forms_se
ssion_count "Forms Sessions"
from icx.icx_sessions icxs, fnd_concurrent_requests fl
(select count(distinct user_id) self_serv_user_count, count(*) self_serv_session_count from icx.icx_sessions where

trunc(creation_date) = trunc(sysdate-1)) icxs,
(select count(distinct user_id) forms_user_count,count(*) forms_session_count from applsys.fnd_logins where trunc

(start_time)=trunc(sysdate-1)) fl,
(select count(*) cnt from fnd_concurrent_requests where trunc(actual_start_date) = trunc(sysdate-1) ) fcr
/

v) /home/oracle/scripts/cron_scripts/sql/max_run_programs.sql


set lines 120
set pages 900
col program format a70
col cnt format 999999 heading "Number of Runs"
ttitle 'Programs that ran for more than 20 times ' skip 2
 select  substr(user_concurrent_program_name,1,70) program, count(*) cnt
 from fnd_conc_req_summary_v
 where trunc(actual_start_date) = trunc(sysdate) -1
 group by substr(user_concurrent_program_name,1,70)
having count(*) > 20
order by 2
/


vi) start /home/oracle/scripts/cron_scripts/sql/top_running_jobs.sql

ttitle 'Concurrent Requests which has more than 30 minutes of execution time' skip 2

SELECT a.request_id,
       substr(user_concurrent_program_name,1,50) name,
       to_char(actual_start_date,'DD-MON-YY HH24:MI') st_dt,
       to_char(actual_completion_date,'HH24:MI') end_tm,
trunc(((actual_completion_date-actual_start_date)*24*60*60)/60)+(((actual_completion_date-actual_start_date)

*24*60*60)-(trunc(((actual_completion_date-actual_start_date
)*24*60*60)/60)*60))/100 exe_time,
       requestor,
       decode(a.status_code,'E','Error','X','Terminated','Normal') status_code
FROM apps.fnd_conc_req_summary_v a
WHERE actual_start_date >= decode(to_char(sysdate,'DAY'),'MONDAY',trunc(sysdate)-3 ,
                                        'SUNDAY',trunc(sysdate)-2,
                                        trunc(sysdate-1))
AND nvl(actual_completion_date,sysdate) - actual_start_date >= 30/24/60
ORDER BY actual_start_date, name
;


vii) /home/oracle/scripts/cron_scripts/sql/conc_req_error_rpt.sql => what concurrent program completed with Error:


ttitle 'Concurrent Requests that Completed in Error ' skip 2
SELECT a.request_id request_id,
       substr(a.user_concurrent_program_name,1,50) name,
       to_char(a.actual_start_date,'HH24:MI') st_time,
       to_char(a.actual_completion_date,'HH24:MI') end_time,
       requestor ,
       decode(a.phase_code,'R','Running','P','Inactive','C','Completed',
              a.phase_code) phase_code,
       decode(a.status_code,'E','Error',   'C','Normal','X','Terminated', 'Q','On Hold', 'D','Cancelled',

'G','Warning',
        'R','Normal',  'W', 'Paused', a.status_code) status_code
FROM apps.fnd_conc_req_summary_v a
WHERE  trunc(actual_completion_date) = trunc(sysdate - 1)
AND a.status_code in ('E','X','D')
ORDER BY actual_start_date;



viii) /home/oracle/scripts/cron_scripts/sql/pend_dtl.sql

ttitle center 'Scheduled Requests in &&1 Production' skip 2

select fcr.request_id,
       substr(decode(fcp.user_concurrent_program_name,'Report Set',fcp.user_concurrent_program_name || ' ' ||

fcr.description,fcp.user_concurrent_program_name),1,47) co
nc_prog_name,
       fu.user_name requestor,
       decode(fcr.phase_code,'R','Running','P',decode(fcr.hold_flag,'Y','Inactive','Pending'),'C','Completed',

fcr.phase_code) phase_code,
       decode(fcr.status_code,'E','Error',   'C','Normal',    'X','Terminated',
                            'Q',decode(fcr.hold_flag,'Y','On Hold',decode(sign(fcr.requested_start_date-

sysdate),1,'Scheduled','Standby')), 'D','Cancelled', 'G','Warnin
g',
                            'R','Normal',  'W', 'Paused','T','Terminating',
                            'R','Normal',  'W', 'Paused','T','Terminating',
                            'I','Scheduled',
                            fcr.status_code) status_code,
       to_char(fcr.requested_start_date,'DD-MON-YYYY HH24:MI:SS') req_start_dt
from
     apps.fnd_user fu,
     apps.fnd_concurrent_programs_vl fcp,
     apps.fnd_concurrent_requests fcr
where fcp.concurrent_program_id = fcr.concurrent_program_id
 and fcr.status_code in ('Q', 'I')
 and fcr.phase_code = 'P'
and fcr.requested_by = fu.user_id
order by 1
;
ttitle off


============================

5> alertlog.Error monitoringS:
------------------------------

*/2 * * * * sh /home/oracle/ora_alert.sh  


#!/bin/sh

set -x
. ~/.bash_profile
SID="`echo $ORACLE_SID`"
MAIL_ID="Oracle.Alert@wepindia.com"

#/orahome/oracle/product/TJTG/db/tech_st/10.2.0/admin/TJTG_tjtgb001/bdump
cd /oracle/PROD/db/tech_st/12.1.0/admin/PROD_dbwep/diag/rdbms/wepprod/PROD/trace

LAST="`cat /tmp/last`"
CUR="`cat alert_$SID.log | wc -l`"

DIFF="`expr $CUR - $LAST`"

sed -ne "${LAST},${CUR}p"  alert_$SID.log | grep '^ORA'|grep -v '^ORACLE'  > /tmp/file
#grep -A$DIFF alert_$SID.log | grep ^ORA  > /tmp/file
#sed -n '`cat /tmp/last`,`cat alert_$SID.log` p' alert_$SID.log | grep '^ORA'  > /tmp/file
#awk 'NR >= "`echo $LAST`" && NR <= "`echo $CUR`"' alert_$SID.log | grep '^ORA' > /tmp/file

if [ -f /tmp/file ] && [ `ls -lrt /tmp/file  | awk '{print $5}'` -gt 0 ]
then
echo "ORA Error exists in alert log of $SID" > /tmp/report
echo "------------------------------------------------------------------" >> /tmp/report
cat /tmp/file  >> /tmp/report

mail -s "ORA Error Alert on $SID at `date`" $MAIL_ID < /tmp/report

#echo $CUR > /tmp/last

fi
echo $CUR > /tmp/last


==========================================