Partitions/Shell Script – Page 3

Protected: Oracle : Script for Sending email for Blocking Sessions

5 years ago by Vinod SiramOracle DB Admin

There is no excerpt because this is a protected post.

How to move or rename Oracle Home

6 years ago by Binnary Ajay KumarOracle DB Admin

How to move or rename Oracle Home If you want to rename Oracle Home you usually need to: 1. uninstall existing Oracle Home 2. reinstall Oracle software to the right directory that will be the new Oracle Home. You cannot just move Oracle Home because… Read More

Oracle : Scheduling crontab to send EMAIL in Unix

6 years ago by Vinod SiramOracle DB Admin

Oracle : Scheduling crontab to send EMAIL in Unix $ vi test.bash

#!/bin/bash
#Script to perform testing
## Environment variables
TODAY=`date +%d-%b-%Y-%H:%M:%S`
export ORACLE_HOME='/u01/app/oracle/product/12102'
export ORACLE_SID='ORCL'
export ORACLE_BASE='/u01/app/oracle'
export PATH=$PATH:${ORACLE_HOME}/bin
export MAILIST='ktexperts@gmail.com'
export LOG='/tmp/test_mig.log'

#Function to trigger email
function mailog
{
cat ${LOG} | mailx -s "${TODAY} - TEST Mail" ${MAILIST}
}

#Function step1
function step1
{
echo " " >> $LOG
echo "Started Step 1" >> $LOG
echo " " >> $LOG
echo "Test email " >> $LOG
sqlplus -s /nolog <<START >>$LOG
Connect / as sysdba
set serveroutput on
set timing on
prompt Todays date
select sysdate from dual;
START
echo " " >> $LOG
echo "----- END OF Step1 ------" >> $LOG
}

#Script execution is here
> $LOG
if [ "$#" -eq 0 ]
then
step1
mailog
else
$1
mailog
fi

#!/bin/bash

#Script to perform testing

## Environment variables

TODAY=`date +%d-%b-%Y-%H:%M:%S`

export ORACLE_HOME='/u01/app/oracle/product/12102'

export ORACLE_SID='ORCL'

export ORACLE_BASE='/u01/app/oracle'

export PATH=$PATH:${ORACLE_HOME}/bin

export MAILIST='ktexperts@gmail.com'

export LOG='/tmp/test_mig.log'

#Function to trigger email

function mailog

{

cat ${LOG} | mailx -s "${TODAY} - TEST Mail" ${MAILIST}

}

#Function step1

function step1

{

echo " " >> $LOG

echo "Started Step 1" >> $LOG

echo " " >> $LOG

echo "Test email " >> $LOG

sqlplus -s /nolog <<START >>$LOG

Connect / as sysdba

set serveroutput on

set timing on

prompt Todays date

select sysdate from dual;

START

echo " " >> $LOG

echo "----- END OF Step1 ------" >> $LOG

}

#Script execution is here

> $LOG

if [ "$#" -eq 0 ]

then

step1

mailog

else

mailog

Crontab entry

00 02 * * * /local/home/oracle/test.bash 1>/local/home/oracle/test.cronlog 2>/local/home/oracle/test.cronerr

1	00 02 * * * /local/home/oracle/test.bash 1>/local/home/oracle/test.cronlog 2>/local/home/oracle/test.cronerr

Updated Script.

#!/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 to trigger email
function mailog
{
cat ${LOG} | mailx -s "${TODAY} - TEST Mail" ${MAILIST}
}

#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
}


#Script execution is here
> $LOG
if [ "$#" -eq 0 ]
then
step1
mailog
else
$1
mailog
fi

#!/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 to trigger email

function mailog

{

cat ${LOG} | mailx -s "${TODAY} - TEST Mail" ${MAILIST}

}

#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

}

#Script execution is here

> $LOG

if [ "$#" -eq 0 ]

then

step1

mailog

else

mailog

Crontab entry

02 * * * * /home/oracle/test.bash  1>/home/oracle/test.cronlog 2>/home/oracle/test.cronerr

1	02 * * * * /home/oracle/test.bash 1>/home/oracle/test.cronlog 2>/home/oracle/test.cronerr

Oracle : Procedure to capture the Tables row count and Actual table size

7 years ago by Vinod SiramOracle DB Admin

Follow below procedure to capture following information : 1) Tables row count. 2) Table Actual Size. 3) DBA_Tab_Modifications information. Pre requisite: This Procedure needs to be implemented if we are gathering stats daily. #############On any Dev server : (Create DB link to Standby DB from… Read More

Oracle : How to Purge CURSORS related to a single SQL_ID from library cache

7 years ago by Vinod SiramOracle DB Admin

********************************************************************* How to Purge CURSORS related to a single SQL_ID from library cache ********************************************************************* SQL> select ADDRESS, HASH_VALUE from V$SQL where SQL_ID like ‘8zdchyshg4d3g’; select ADDRESS, HASH_VALUE,plan_hash_value from V$SQL where SQL_ID like ‘8zdchyshg4d3g’; SQL> exec DBMS_SHARED_POOL.PURGE (‘000000180DC67320,552744047′,’C’); PL/SQL procedure successfully completed. SQL> select ADDRESS, HASH_VALUE… Read More

Oracle : List all the hidden parameter and their values – &&&&&& – Simulate ORA error in Alert log

7 years ago by Vinod SiramOracle DB Admin

************************************************************ List all the hidden parameter and their values ************************************************************ SET PAUSE ON SET PAUSE ‘Press Return to Continue’ SET PAGESIZE 60 SET LINESIZE 300 COLUMN ksppinm FORMAT A50 COLUMN ksppstvl FORMAT A50 SELECT ksppinm, ksppstvl FROM x$ksppi a, x$ksppsv b WHERE a.indx=b.indx AND substr(ksppinm,1,1)… Read More

Oracle : HOW TO TRACE SESSIONS

7 years ago by Vinod SiramOracle DB Admin

Oracle : HOW TO TRACE SESSIONS ========================================================== For TRACING your own session , use the following query to find details on your own session: ========================================================== set linesize 132 set pagesize 1000 set echo on col rbs format a6 col oracle_user format a12 heading “Oracle|Username” col… Read More

Executing job in Background for one sql

8 years ago by Vinod SiramOracle DB Admin

cat g_stats_1_test.sh sqlplus / as sysdba <<EOF set head off conn / as sysdba set serveroutput on set timing on set echo on spool dict_stats_test.log exec DBMS_STATS.gather_table_stats (ownname => ‘SCOTT’,tabname => ‘EMP’,method_opt=>’FOR COLUMNS SIZE AUTO STATUS’); spool off set timing off set serveroutput off exit;… Read More

Generate output in HTML

8 years ago by Vinod SiramOracle DB Admin

set linesize 200 set pagesize 500 spool /tmp/Oct29.html set markup html on set numwidth 40 select * from emp; set long 100000 select sql_id,sql_text from DBA_HIST_SQLTEXT where sql_id in (select sql_id from v$session); spool off

Generate Service and DB Details with one command for N DB’s on one cluster

8 years ago by Vinod SiramOracle DB Admin

for i in srvctl config; do echo $i; srvctl status database -d $i; done; for i in srvctl config; do echo $i; srvctl status service -d $i; done;