Vinod Siram – Page 9

#!/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

Ease of DB Administration Via SecureCRT

6 years ago by Vinod SiramSecure CRT

Hello Readers I Standardized and customized the SecureCRT to group and link the frequently used commands. It will make the Oracle DBA job easy and fast. Download SecureCRT Software binaries using below link. https://www.vandyke.com/cgi-bin/account_login.php?pid=scrt_x64_870 Or Download SecureCRT binaries from Google Drive https://drive.google.com/file/d/1jfGtmKKwPwmOcjO1t5BJoN_b77V09a_q/view?usp=sharing Let me know… Read More

Oracle : Generate Clear output for crs_stat -t

6 years ago by Vinod SiramOracle DB Admin

Oracle : Generate Clear output for crs_stat -t

 cat crsstat.sh 
#!/bin/bash

RSC_KEY=$1
QSTAT=-u
AWK=/bin/awk    # if not available use /usr/bin/awk

# Table header:echo ""
$AWK \
  'BEGIN {printf "%-55s %-10s %-18s\n", "HA Resource", "Target", "State";
          printf "%-55s %-10s %-18s\n", "-----------", "------", "-----";}'

# Table body:
ORA_CRS_HOME=/u01/app/grid/product/12102
$ORA_CRS_HOME/bin/crs_stat $QSTAT | $AWK \
 'BEGIN { FS="="; state = 0; }
  $1~/NAME/ && $2~/'$RSC_KEY'/ {appname = $2; state=1};
  state == 0 {next;}
  $1~/TARGET/ && state == 1 {apptarget = $2; state=2;}
  $1~/STATE/ && state == 2 {appstate = $2; state=3;}
  state == 3 {printf "%-55s %-10s %-18s\n", appname, apptarget, appstate; state=0;}'

cat crsstat.sh

#!/bin/bash

RSC_KEY=$1

QSTAT=-u

AWK=/bin/awk # if not available use /usr/bin/awk

# Table header:echo ""

$AWK \

'BEGIN {printf "%-55s %-10s %-18s\n", "HA Resource", "Target", "State";

printf "%-55s %-10s %-18s\n", "-----------", "------", "-----";}'

# Table body:

ORA_CRS_HOME=/u01/app/grid/product/12102

$ORA_CRS_HOME/bin/crs_stat $QSTAT | $AWK \

'BEGIN { FS="="; state = 0; }

$1~/NAME/ && $2~/'$RSC_KEY'/ {appname = $2; state=1};

state == 0 {next;}

$1~/TARGET/ && state == 1 {apptarget = $2; state=2;}

$1~/STATE/ && state == 2 {appstate = $2; state=3;}

state == 3 {printf "%-55s %-10s %-18s\n", appname, apptarget, appstate; state=0;}'

chmod 755 crsstat.sh Output :

./crsstat.sh 
HA Resource                                             Target     State             
-----------                                             ------     -----             
ora.CRS01.dg                                            ONLINE     ONLINE on server11
ora.DATA01.dg                                           ONLINE     ONLINE on server11
ora.FRA01.dg                                            ONLINE     ONLINE on server11
ora.LISTENER.lsnr                                       ONLINE     ONLINE on server11
ora.LISTENER_SCAN1.lsnr                                 ONLINE     ONLINE on server12
ora.LISTENER_SCAN2.lsnr                                 ONLINE     ONLINE on server11
ora.LISTENER_SCAN3.lsnr                                 ONLINE     ONLINE on server11
ora.MGMTLSNR                                            ONLINE     ONLINE on server11
ora.REDO01.dg                                           ONLINE     ONLINE on server11
ora.REDO02.dg                                           ONLINE     ONLINE on server11
ora.asm                                                 ONLINE     ONLINE on server11
ora.cvu                                                 ONLINE     ONLINE on server11
ora.mgmtdb                                              ONLINE     ONLINE on server11
ora.net1.network                                        ONLINE     ONLINE on server11
ora.oc4j                                                ONLINE     ONLINE on server11
ora.ons                                                 ONLINE     ONLINE on server11
ora.orcl2.vin_svc_node1.svc                       	ONLINE     ONLINE on server11
ora.orcl2.vin_svc_node2.svc                       	ONLINE     ONLINE on server11
ora.scan1.vip                                           ONLINE     ONLINE on server12
ora.scan2.vip                                           ONLINE     ONLINE on server11
ora.scan3.vip                                           ONLINE     ONLINE on server11
ora.server11.ASM1.asm                               	ONLINE     ONLINE on server11
ora.server11.LISTENER_server11.lsnr             	ONLINE     ONLINE on server11
ora.server11.ons                                    	ONLINE     ONLINE on server11
ora.server11.vip                                    	ONLINE     ONLINE on server11
ora.server12.ASM2.asm                               	ONLINE     ONLINE on server12
ora.server12.LISTENER_server12.lsnr             	ONLINE     ONLINE on server12
ora.server12.ons                                    	ONLINE     ONLINE on server12
ora.server12.vip                                    	ONLINE     ONLINE on server12

./crsstat.sh

HA Resource Target State

----------- ------ -----

ora.CRS01.dg ONLINE ONLINE on server11

ora.DATA01.dg ONLINE ONLINE on server11

ora.FRA01.dg ONLINE ONLINE on server11

ora.LISTENER.lsnr ONLINE ONLINE on server11

ora.LISTENER_SCAN1.lsnr ONLINE ONLINE on server12

ora.LISTENER_SCAN2.lsnr ONLINE ONLINE on server11

ora.LISTENER_SCAN3.lsnr ONLINE ONLINE on server11

ora.MGMTLSNR ONLINE ONLINE on server11

ora.REDO01.dg ONLINE ONLINE on server11

ora.REDO02.dg ONLINE ONLINE on server11

ora.asm ONLINE ONLINE on server11

ora.cvu ONLINE ONLINE on server11

ora.mgmtdb ONLINE ONLINE on server11

ora.net1.network ONLINE ONLINE on server11

ora.oc4j ONLINE ONLINE on server11

ora.ons ONLINE ONLINE on server11

ora.orcl2.vin_svc_node1.svc ONLINE ONLINE on server11

ora.orcl2.vin_svc_node2.svc ONLINE ONLINE on server11

ora.scan1.vip ONLINE ONLINE on server12

ora.scan2.vip ONLINE ONLINE on server11

ora.scan3.vip ONLINE ONLINE on server11

ora.server11.ASM1.asm ONLINE ONLINE on server11

ora.server11.LISTENER_server11.lsnr ONLINE ONLINE on server11

ora.server11.ons ONLINE ONLINE on server11

ora.server11.vip ONLINE ONLINE on server11

ora.server12.ASM2.asm ONLINE ONLINE on server12

ora.server12.LISTENER_server12.lsnr ONLINE ONLINE on server12

ora.server12.ons ONLINE ONLINE on server12

ora.server12.vip ONLINE ONLINE on server12