Protected: Oracle : Shell Script to Send email if RAC services not running
There is no excerpt because this is a protected post.
Author: Rakesh Sahu
Oracle : Shell Script to gather stats for Stale tables
Dear Readers, In this article, we wrote an automation job for Shell Script to gather stats for Stale tables. Note : 1) Please test the Shell script in Non-Prod before implementing it in Production. Script for gather stats for Stale tables
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
#!/bin/bash function PreChks { ORACLE_HOME=/u01/app/oracle/product/12.1.0.2 if [[ ! -e $ORACLE_HOME ]]; then echo " " echo "Error ::: Given ORACLE_HOME :: $ORACLE_HOME does not exist!!! " echo " " exit; fi ORACLE_SID=mvmrp1 export PATH=$ORACLE_HOME/bin:$PATH:. export ORACLE_BASE ORACLE_HOME PATH ORACLE_SID LOGFILE=/tmp/main.log echo "Task Start at `date` " > $LOGFILE export MAIL_RECIPIENT_INFO='ktexperts@admin.com' } function get_objects { echo "#CHECK FOR DATABASE and Tables STATUS##">>"$LOGFILE" DB_OPEN_MODE=`sqlplus -s / as sysdba <<EOF set pagesize 0 feedback off verify off heading off echo off select open_mode from v\\$database; EOF` echo "$DB_OPEN_MODE">>"$LOGFILE" if [[ $DB_OPEN_MODE = "READ WRITE" ]] then echo "Fetch Stale_Stats Data" sqlplus -s / as sysdba <<EOF > /tmp/a.log set pagesize 0 feedback off verify off heading off echo off set linesize 300 select table_name, owner from dba_tab_statistics where stale_stats='YES' and owner in ('MVM1') order by table_name; EOF else ##DATABASE IS NOT OPEN## mail_subject="Database $INSTANCE is not open hence Gather Stats cant not Start...." echo " " echo "Database $INSTANCE is not open hence Gather Stats cant not Start...." echo " " mailx -s "${mail_subject}" ${MAIL_RECIPIENT_INFO}<<EOF " Database $INSTANCE is not open hence Gather Stats cant not Start...." EOF exit 0 fi cat /tmp/a.log >> /tmp/main.log } function gather_stats { cat /tmp/a.log | while read p do tbl=`echo $p | awk '{ print $1 }'` owner=`echo $p | awk '{ print $2 }'` SQL=`echo "Exec dbms_stats.gather_table_stats('$owner','$tbl',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4);"` echo $SQL >> /tmp/main.log sqlplus -s / as sysdba <<EOF >> /tmp/main.log Exec dbms_stats.gather_table_stats('$owner','$tbl',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4); EOF done } PreChks get_objects gather_stats get_objects mail_subject="mvmrp1 Stale Stats Report" cat "$LOGFILE" | mailx -s "${mail_subject}" ${MAIL_RECIPIENT_INFO} |
Output :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
################## Task Start at Mon Jun 14 21:00:01 PDT 2021 #CHECK FOR DATABASE and Tables STATUS## READ WRITE READ WRITE #CHECK FOR DATABASE and Tables STATUS## READ WRITE READ WRITE EMP MVM1 DEPT MVM1 SALGRADE MVM1 Exec dbms_stats.gather_table_stats('MVM1','EMP',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4); PL/SQL procedure successfully completed. Exec dbms_stats.gather_table_stats('MVM1','DEPT',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4); PL/SQL procedure successfully completed. Exec dbms_stats.gather_table_stats('MVM1','SALGRADE',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4); PL/SQL procedure successfully completed. #CHECK FOR DATABASE and Tables STATUS## READ WRITE |
Oracle Sample Unix script to monitor select query during Load Test
Hi Dears, In this article, we will see Oracle Sample Unix script to monitor select query during Load Test
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
#!/bin/bash ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1 ORACLE_SID=ORCL while : do sqlplus -s / as sysdba <<EOF set pagesize 0 set linesize 300 select B.USERNAME ||' ('||B.SID||','||B.SERIAL#||',@'||B.INST_ID||') is Currently '||B.STATUS||' for last '||B.LAST_CALL_ET||' Sec and it''s BLOCKING user '|| W.USERNAME|| ' ('| |W.SID||','||W.SERIAL#||',@'||W.INST_ID||')' from (select INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET from gv\$session where BLOCKING_SESSION >0) W, (select INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET from gv\$session ) B where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID; EOF date echo "Hit [CTRL+C] to stop!" sleep 5 done |
Output :
|
1 2 3 4 5 6 7 8 9 |
Wed Mar 24 11:54:39 PDT 2021 Hit [CTRL+C] to stop! no rows selected Wed Mar 24 11:54:44 PDT 2021 Hit [CTRL+C] to stop! no rows selected |
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates KTEXPERTS is always… Read More
Oracle : Check Partition Summary More than N Days (60 Days)
Dear Readers, In this article, we will see the following Check Partition Summary More than N Days (60 Days). Script :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
cat parti_last_60days.sh #!/bin/bash #Script to Check Partition In last 60 Days #./Partition Report.sh DEMO ## Environment variables TODAY=`date +%d-%b-%Y-%H:%M:%S` rm /tmp/part1.log /tmp/part.log touch /tmp/part1.log export ORACLE_HOME='/u01/app/oracle/product/12.1.0.2/db_1' export ORACLE_SID='ORCL11' export ORACLE_BASE='/u01/app/oracle' export PATH=$PATH:${ORACLE_HOME}/bin export MAILIST='ktexpert@ktexperts.com' export LOG='/tmp' _OWNER=$1 function sql_script { echo " " echo "Started To Run Partition Check Sql Query" echo " " sqlplus vinod/vinod <<EOF > /tmp/t.log SET HEADING OFF FEEDBACK OFF ECHO OFF PAGESIZE 0 spool /tmp/schema.log select table_name from dba_tables where owner='$_OWNER'; spool off; EOF sed -i '1d' /tmp/schema.log sed -i '$d' /tmp/schema.log wc=`wc -l /tmp/schema.log |awk '{ print $1 }'` for (( c=1; c<=$wc; c++ )) do _tbls=`sed ''$c'!d' /tmp/schema.log` _tbls1=` echo -n "${_tbls//[[:space:]]/}"` if [ "$_tbls1" ]; then echo "Checking Partition details on $_tbls1@$_OWNER" sqlplus vinod/vinod <<EOF > /tmp/t.log set line 2000 pages 2000 SET HEADING ON FEEDBACK OFF ECHO OFF PAGESIZE 0 trimspool on col OBJECT_NAME for a40 col SUBOBJECT_NAME for a40 col OWNER for a30 col CREATED for a30 spool /tmp/part.log; SELECT object_name,subobject_name,owner,created FROM dba_objects where owner='$_OWNER' and OBJECT_NAME='$_tbls1' and object_type='TABLE PARTITION' and trunc(created) <= trunc(sysdate-60) order by 4; spool off EOF fi sed -i '1d' /tmp/part.log sed -i '$d' /tmp/part.log #echo "################ Partition Check on $_tbls1 table of $_OWNER schema 60 Days Back ################# " >> /tmp/part1.log if [ ! -s /tmp/part.log ]; then #echo " " >> /tmp/part1.log #echo "Checked and No Partition Found 60 Days Back for $_tbls1 table " >> /tmp/part1.log #echo " " >> /tmp/part1.log _var='FALSE' else echo "################ Partition Check on $_tbls1 table of $_OWNER schema 60 Days Back ################# " >> /tmp/part1.log echo " " >> /tmp/part1.log echo "-------------------------------------------------------------------------------------------------------------------------------------------" >> /tmp/part1.log echo "TABLE_NAME Partition_Name OWNER PARTITION_CREATED_DATE" >> /tmp/part1.log echo "-------------------------------------------------------------------------------------------------------------------------------------------" >> /tmp/part1.log cat /tmp/part.log >> /tmp/part1.log fi done echo " " echo "----- End Of Query Execution logs can be check at /tmp/part1.log ------" } function send_mail { cat /tmp/part1.log | mailx -s "${TODAY} - Partition Check Summary" ${MAILIST} } #MAIN sql_script send_mail |
Out Put :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
################ Partition Check on DEPART table of DEMO schema 60 Days Back ################# ------------------------------------------------------------------------------------------------------------------------------------------- TABLE_NAME Partition_Name OWNER PARTITION_CREATED_DATE ------------------------------------------------------------------------------------------------------------------------------------------- DEPART SYS_P21641 DEMO 11-JUN-20 DEPART SYS_P13521 DEMO 27-JUL-20 DEPART SYS_P13530 DEMO 28-JUL-20 DEPART SYS_P13587 DEMO 29-JUL-20 DEPART SYS_P13631 DEMO 04-AUG-20 DEPART SYS_P13668 DEMO 05-AUG-20 DEPART SYS_P13768 DEMO 06-AUG-20 DEPART SYS_P13856 DEMO 19-AUG-20 DEPART SYS_P13894 DEMO 20-AUG-20 DEPART SYS_P13928 DEMO 11-SEP-20 DEPART SYS_P14004 DEMO 12-SEP-20 DEPART SYS_P14067 DEMO 14-SEP-20 DEPART SYS_P14072 DEMO 15-SEP-20 ################ Partition Check on EMPBKP table of DEMO schema 60 Days Back ################# ------------------------------------------------------------------------------------------------------------------------------------------- TABLE_NAME Partition_Name OWNER PARTITION_CREATED_DATE ------------------------------------------------------------------------------------------------------------------------------------------- EMPBKP SYS_P21661 DEMO 11-JUN-20 EMPBKP SYS_P13561 DEMO 28-JUL-20 EMPBKP SYS_P13632 DEMO 04-AUG-20 EMPBKP SYS_P13672 DEMO 05-AUG-20 EMPBKP SYS_P13772 DEMO 06-AUG-20 EMPBKP SYS_P13857 DEMO 19-AUG-20 EMPBKP SYS_P13903 DEMO 20-AUG-20 EMPBKP SYS_P13949 DEMO 11-SEP-20 EMPBKP SYS_P14009 DEMO 12-SEP-20 |
Thank you for giving your valuable time to read the above information. Follow us on Website … Read More
Oracle: Comparison between Python and Shell Script for sending email for the set of SQL commands
Dear Readers, In this article, we will see the following Comparison between Python and Shell Script for sending email for the set of SQL commands. Python and Shell Script for sending email for the set of SQL commands: Shell Script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
#!/bin/bash #Script to perform testing ## Environment variables TODAY=`date +%d-%b-%Y-%H:%M:%S` export ORACLE_HOME='/u01/app/oracle/product/12.1.0.2' export ORACLE_SID='prod' export ORACLE_BASE='/u01/app/oracle' export PATH=$PATH:${ORACLE_HOME}/bin export MAILIST='ktexperts@gmail.com' export LOG='/tmp/test_mig.log' #Function step1 function step1 { echo " " >> $LOG echo "Started Step 1" >> $LOG echo " " >> $LOG echo "Test email " >> $LOG sqlplus vinod/vinod <<START >>$LOG prompt updating records in EMP table select * from emp where deptno=20; prompt update emp set sal=sal+500 WHERE deptno=20; update emp set sal=sal+500 WHERE deptno=20; prompt select * from emp where deptno=20; select * from emp; START echo " " >> $LOG echo "----- END OF Step1 ------" >> $LOG } #Function to trigger email function mailog { cat ${LOG} | mailx -s "${TODAY} - TEST Mail" ${MAILIST} } #Script execution is here > $LOG if [ "$#" -eq 0 ] then step1 mailog else $1 mailog fi |
Output:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
updating records in EMP table SQL> EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 4800 20 7566 JONES MANAGER 7839 02-APR-81 6975 20 7788 SCOTT ANALYST 7566 19-APR-87 7000 20 7876 ADAMS CLERK 7788 23-MAY-87 5100 20 7902 FORD ANALYST 7566 03-DEC-81 7000 20 SQL> update emp set sal=sal+500 WHERE deptno=20 SQL> 5 rows updated. SQL> select * from emp where deptno=20 SQL> EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 5300 20 7566 JONES MANAGER 7839 02-APR-81 7475 20 7788 SCOTT ANALYST 7566 19-APR-87 7500 20 7876 ADAMS CLERK 7788 23-MAY-87 5600 20 7902 FORD ANALYST 7566 03-DEC-81 7500 20 |
Python Script:… Read More
