Multiple-opatch-automation

########################SCRIPT TO APPLY MULTIPLE OPATCHES################################

P=/testappl/APRILCPU2020/DB/SR_Patches

export P 

for Patch in 21321429 20123899 28682351 25139545 30216977 21967332 25099339 23604553 31179008 30614876

do 

cd $P/$Patch 

$ORACLE_HOME/OPatch/opatch apply -silent -local 

done 

multiple-dir-rename

for DIR in   xxxx09   xxxx10   xxxx11   xxxx14   

do

cd /$DIR

mv -i PRODUTION DEV

done

INVALID-BACKUP-AUTOMATION

#################SCRIPT FOR AUTOMATIC INVALID TABLE BACKUP###########

#!/usr/bin/bash

##rm pcount.log

##rm qcount.log

t=`date +"%Y%m%d%H%M%S"`

p="invalids$t"

pcount="pcount.$t"

qcount="qcount.$t"

a=`grep -o '[0-9]\+' pcount.log|sed -n 1p`

b=`grep -o '[0-9]\+' qcount.log|sed -n 1p`

sqlplus -s "/ as sysdba"  << EOF

create table $p as select * from dba_objects where status like 'INVALID';

spool pcount.log

select count (*) from $p;

exit;

EOF

sqlplus -s "/ as sysdba" << EOF

spool qcount.log

select count(*) from dba_objects where status like 'INVALID';

exit;

EOF

if [ $a == $b ]

then

echo "#########****INVALID TABLE BACKUP HAS BEEN TAKEN SUCESSFULLY****#######"

echo       "INVALID-BACKUP-TABLENAME:           $p"

echo       "INVALID-BACKUP-TABLECOUNT:          $a"

echo       "ACUTAL-INVALID-TABLE-COUNT:         $b"

else

sqlplus -s "/ as sysdba"  << EOF

spool drp.txt

drop table $p;

spool off;

exit;

EOF

echo "There is an issue with taking automated backup so please check and take the invalid count manually"

fi

automation-healthcheck

Problem for automation

[user@server lk]$ CONCSUB apps/passwordofuser SYSADMIN 'System Administrator' SYSADMIN WAIT=N CONCURRENT FND FNDSCURS > req.txt
[user@server lk]$ cat req.txt
Submitted request 4632124 for CONCURRENT FND FNDSCURS

[user@server lk]$ awk '{print $3}' req.txt
4632124


SQL> SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[user@server lk]$ CONCSUB apps/passwordofuser SYSADMIN 'System Administrator' SYSADMIN WAIT=N CONCURRENT FND FNDSCURS > req.txt
[user@server lk]$ > req.txt
[user@server lk]$ cat req.txt
[user@server lk]$ CONCSUB apps/passwordofuser SYSADMIN 'System Administrator' SYSADMIN WAIT=N CONCURRENT FND FNDSCURS > req.txt
[user@server lk]$ cat req.txt
Submitted request 4632126 for CONCURRENT FND FNDSCURS

[user@server lk]$ sqlplus apps/passwordofuser << EOF
> spool healthcheck.txt
> set lines 300
> select a.user_concurrent_program_name , a.CONCURRENT_PROGRAM_ID , REQUEST_ID
> ,REQUEST_TYPE,PHASE_CODE  , STATUS_CODE , ACTUAL_START_DATE ,COMPLETION_TEXT
> from fnd_concurrent_programs_tl a, fnd_concurrent_requests b
> where a.concurrent_program_id=b.concurrent_program_id
> and program_application_id=0
> and REQUEST_ID=4632126;
> spool off;
> exit;
> EOF

SQL*Plus: Release 8.0.6.0.0 - Production on Wed Apr 1 07:41:10 2020

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SQL> SQL>   2    3    4    5    6
USER_CONCURRENT_PROGRAM_NAME                                                                                                               CONCURRENT_PROGRAM_ID REQUEST_ID R P S ACTUAL_ST
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------- ---------- - - - ---------
COMPLETION_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Active Users                                                                                                                               20641     4632126   C C 01-APR-20
Normal Running


***After few minits--we dont estimate the time***

SQL> SQL> SQL>   2    3    4    5    6
USER_CONCURRENT_PROGRAM_NAME                                                                                                               CONCURRENT_PROGRAM_ID REQUEST_ID R P S ACTUAL_ST
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------- ---------- - - - ---------
COMPLETION_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Active Users                                                                                                                               20641     4632126   C C 01-APR-20
Normal completion


when it get the status like completion then we need to conculde that reqeust has been completed and application is running fine




########################SOLUTION IS #######healthcheck report for oracle apps

#!/bin/sh
username=$1;
password=$2;
> req.txt;
> healthcheck.txt;
sd=`echo $TWO_TASK`
CONCSUB $1/$2 SYSADMIN 'System Administrator' SYSADMIN WAIT=N CONCURRENT FND FNDSCURS > req.txt
requid=`awk '{print $3}' req.txt`
echo $requid;
sqlplus $1/$2 << EOF
spool healthcheck.txt
set lines 300
select a.user_concurrent_program_name , a.CONCURRENT_PROGRAM_ID , REQUEST_ID
,REQUEST_TYPE,PHASE_CODE  , STATUS_CODE , ACTUAL_START_DATE ,COMPLETION_TEXT
from fnd_concurrent_programs_tl a, fnd_concurrent_requests b
where a.concurrent_program_id=b.concurrent_program_id
and program_application_id=0
and REQUEST_ID=$requid;
spool off;
exit;
EOF
####REQSTATUS####
a=`cat healthcheck.txt|grep Normal|wc -l`
while [ $a -le 1 ]
do
> healthcheck.txt
echo "$requid is running"
sleep 2
sqlplus $1/$2 << EOF
spool healthcheck.txt
set lines 300
select a.user_concurrent_program_name , a.CONCURRENT_PROGRAM_ID , REQUEST_ID
,REQUEST_TYPE,PHASE_CODE  , STATUS_CODE , ACTUAL_START_DATE ,COMPLETION_TEXT
from fnd_concurrent_programs_tl a, fnd_concurrent_requests b
where a.concurrent_program_id=b.concurrent_program_id
and program_application_id=0
and REQUEST_ID=$requid;
spool off;
exit;
EOF
a=`cat healthcheck.txt|grep Normal|wc -l`
done
rm -rf healthcheck.txt
>outfile.txt
echo REQUESTID=$requid > outfile.txt
echo PROGRAMNAME="Active users" >>outfile.txt
echo STATUS="COMPLETED" >> outfile.txt
echo PHASE="NORMAL" >> outfile.txt
###DBSTATUS#####
###spool DB.txt
sqlplus $1/$2 << EOF
spool DB.txt
conn $1/$2
select * from global_name;
spool off;
exit;
EOF
b=`cat DB.txt|grep Connected|wc -l`
if [$b -ge 1 ]
then
rm -rf DB.txt
echo DATABASENAME=$sd >> outfile.txt
echo DBSTATUS="UP AND RUNNING" >> outfile.txt
fi
########APACHE STATUS#############
as=`pwd`
rm -rf apache.txt
cd $COMMON_TOP/admin/scripts/$TWO_TASK*
rm -rf apache.txt
sh adapcctl.sh status > apache.txt
chmod -R 777 apache.txt
cp -r apache.txt $as
cd $as
p=`cat apache.txt|grep not|wc -l`
if [$p -ge 1 ]
then
echo APACHESTATUS="APACHE IS NOT RUNNING"  >> outfile.txt
else
echo APACHESTATUS="APACHE IS UP AND  RUNNING" >> outfile.txt
fi

Shell Scripting(sh&ksh)

USEFULL LINK FOR LINUX
http://linuxcommand.org/lc3_writing_shell_scripts.php


$# Stores the number of command-line arguments that were passed to the shell program.
$? Stores the exit value of the last command that was executed.
$* is a string containing all arguments.
$1 is the first argument.


SSH FUNCTION

{
if [ ${DB_OS_TYPE} == "SunOS" ]; then
        COMMAND='cat /etc/release | head -1'
    DB_CPU_TEMP=`exec_over_ssh ${ORA_USER} ${ORA_HOST} ". /export/home/$ORA_USER/.profile > /dev/null;opatch lsinventory| grep -i 'DATABASE PATCH'| head -1"`
else
    DB_CPU_TEMP=`exec_over_ssh ${ORA_USER} ${ORA_HOST} ". /home/$ORA_USER/.bash_profile > /dev/null;opatch lsinventory| grep -i 'DATABASE PATCH'| head -1"`
fi



exec_over_ssh()
{
        USER=$1
        HOST=$2
        COMMAND=$3
        OUTPUT=`ssh ${USER}@${HOST} ${COMMAND}`
        echo ${OUTPUT}
}

###################created script by me#
#!/bin/bash
###set -x
user=$1
HOST=$2
COMMAND=$3
exec_ssh()
{
OUTPUT=`ssh ${user}@${HOST} ${COMMAND}`
echo ${OUTPUT}
}
if [ $# == 3 ]
then
exec_ssh
else
echo "enter correect parameter"
exit
fi

AUTMATION SCRIPT - DBSTRAT and STOP

Step1: create scripts

***********DBSTART SCRIPT********
[oracle@AVIATION ~]$ cat stratdb.sh
export  ORACLE_BASE=/d01/oracle
export ORACLE_HOME=/d01/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=AVIATION
export PATH=$PATH:$ORACLE_HOME/bin
echo $ORACLE_SID
sqlplus '/as sysdba'<<!
startup
show parameter db_name
select open_mode from v\$database ;
exit


*********DBSTOPSCRIPT*********
[oracle@AVIATION ~]$ cat stopdb.sh
export  ORACLE_BASE=/d01/oracle
export ORACLE_HOME=/d01/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=AVIATION
export PATH=$PATH:$ORACLE_HOME/bin
echo $ORACLE_SID
sqlplus 'sys/oracle as sysdba'<<eof
select name, open_mode from v\$database ;
show parameter db_name
shutdown immediate
exit
eof


Step2:create dbora script to

***************dbora-service-script*********
#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database software.

ORA_OWNER=oracle

case "$1" in
    'start')
        # Start the Oracle databases:
        # The following command assumes that the oracle login
        # will not prompt the user for any values
        # Remove "&" if you don't want startup as a background process.
        su $ORA_OWNER -c "/home/oracle/stratdb.sh >> /home/oracle/startup_shutdown.log 2>&1" &

        touch /var/lock/subsys/dbora
        ;;
    'stop')
        # Stop the Oracle databases:
        # The following command assumes that the oracle login
        # will not prompt the user for any values
        su $ORA_OWNER -c "/home/oracle/stopdb.sh >> /home/oracle/startup_shutdown.log 2>&1"
        rm -f /var/lock/subsys/dbora
        ;;
esac

**************



Step3:change entry in oratab
****

[oracle@AVIATION ~]$ cat /etc/oratab
logout
AVIATION:/d01/oracle/product/11.2.0/dbhome_1:Y
[root@AVIATION etc]#


Step4:Add linux service to dbora script:
*********
chmod 750 /etc/init.d/dbora

chkconfig --add dbora


Stpe5:which run level you need to use the script mentioning through chkconfig
*****************
chkconfig --level 356 dbora on
chkconfig --level 0124 dbora off



NOTE:inittab --have the runlevel details. (loc:/etc/inittab)
******************after adding dbora to init.d the below softlinks will be created automatically******
[root@AVIATION init.d]# pwd
/etc/rc.d/init.d
[root@AVIATION init.d]# cd ..
[root@AVIATION rc.d]# pwd
/etc/rc.d
[root@AVIATION rc.d]# ls -ltr
total 112
-rwxr-xr-x 1 root root   220 Jun  7  2011 rc.local
-rwxr-xr-x 1 root root  2255 Jun  7  2011 rc
-rwxr-xr-x 1 root root 26930 Jul 22  2011 rc.sysinit
drwxr-xr-x 2 root root  4096 Mar  2 13:47 init.d
drwxr-xr-x 2 root root  4096 Mar  2 14:33 rc6.d
drwxr-xr-x 2 root root  4096 Mar  2 14:33 rc5.d
drwxr-xr-x 2 root root  4096 Mar  2 14:33 rc3.d
drwxr-xr-x 2 root root  4096 Mar  2 14:34 rc4.d
drwxr-xr-x 2 root root  4096 Mar  2 14:34 rc2.d
drwxr-xr-x 2 root root  4096 Mar  2 14:34 rc1.d
drwxr-xr-x 2 root root  4096 Mar  2 14:34 rc0.d
[root@AVIATION rc.d]# cd rc4.d
[root@AVIATION rc4.d]# ls
lrwxrwxrwx 1 root root 15 Mar  2 14:34 K10dbora -> ../init.d/dbora
[root@AVIATION rc4.d]# pwd
/etc/rc.d/rc4.d