Important Apps DBA Scripts

Finding Apps Version :

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

#!/bin/ksh
sqlplus apps/ezdtst321 << EOF
spool find_apps_version.log
SELECT substr(a.application_short_name, 1, 5) code,
       substr(t.application_name, 1, 50) application_name,
       p.product_version version
FROM   fnd_application a,
       fnd_application_tl t,
       fnd_product_installations p
WHERE  a.application_id = p.application_id
AND    a.application_id = t.application_id
AND    t.language = USERENV('LANG');
spool off;
exit
EOF

Checking Patch Number :
-----------------------------------

#!/bin/ksh
echo " "
echo "Starting the Database" $TWO_TASK
echo " "
echo "Enter the patch number:"
read number
sqlplus apps/ezdtst321 << EOF
spool patch_checking.log
select '-------------------------------------' from dual;
select '--------------AD_BUGS----------------' from dual;
select '-------------------------------------' from dual;
SELECT BUG_ID,BUG_NUMBER,CREATION_DATE,LAST_UPDATE_DATE FROM AD_BUGS WHERE BUG_NUMBER LIKE '$number';
select '-------------------------------------' from dual;
select '------------AD_APPLIED_PATCHES-------' from dual;
select '-------------------------------------' from dual;
SELECT APPLIED_PATCH_ID,PATCH_NAME,CREATION_DATE,LAST_UPDATE_DATE FROM AD_APPLIED_PATCHES WHERE PATCH_NAME = '$number';
spool off;
exit
EOF

Reducing Patching Time :
-----------------------------------


#!/bin/sh
#
# adpatch_auto a.k.a aa
#
# Ver Date Author Description
# --- --------- ---------- ---------------------------
# 1.0 30-OCT-00 G. Roberts Created
# 1.1 09-MAY-07 G. Roberts Extended for zip files
#
# Only use for patches you've already done manually, or in non-PROD environments
# Notes: You must have your environment set correctly
# You must have precreated defaults.txt
# Patch must be in PT (see config below)
# Patch driver number must be same as patch number

if [ "$#" != "2" -a "$#" != "3" -a "$#" != "4" -a "$#" != "5" ]; then
echo "Usage: aa PATCH_NUM DRIVERFILE_PREFIX [RESTART=YN [INTERACTIVE=YN]]"
echo " Simple e.g: aa 123456 u"
echo " Restart e.g: aa 123456 u Y Y"
exit 1
fi

# CONFIGURATION ITEMS
# Set PT to where your base patch directory is
PT=/patch; export PT
# LOGFILE is a file keeping track of aa run history
LOGFILE=$APPL_TOP/admin/p.log; export LOGFILE
# DEFFILE is your defaults.txt probably defaults.txt or adalldefaults.txt
DEFFILE=$APPL_TOP/admin/${TWO_TASK}/adalldefaults.txt
# Set DOUNZIP to Y if you want aa to search for and unzip p*.zip
DOUNZIP=Y; export DOUNZIP
# Set SETMAINT to Y to automatically switch on and off maintenance mode
# Only needed for late'ish versions 11.5.10 or higher
SETMAINT=Y; export SETMAINT
# Date format for logging
DF="+%y%m%d %H:%M:%S"

# Check CONTEXT_NAME is set
if [ -z "${CONTEXT_NAME}" ]; then
echo "CONTEXT_NAME is not set!"
exit 1
else
CTXT=${CONTEXT_NAME}; export CTXT
fi

ORIGDIR="`pwd`"

PNUM=$1; export PNUM
DRIV=$2; export DRIV
PATCH=$PNUM; export PATCH
if [ ! -z "$3" ]; then
if [ "$3" = "N" ]; then
REST="restart=no"; export REST
else
REST="restart=yes"; export REST
fi
fi
if [ "$#" = "4" ]; then
if [ "$4" = "Y" ]; then
INTERACT=yes; export INTERACT
else
INTERACT=no; export INTERACT
fi
else
INTERACT=no; export INTERACT
fi

echo "Patch number set to $PNUM"
echo "Driver set to $DRIV"
echo "Restart set to $REST"
echo "Interactive set to $INTERACT"
echo
echo `date "$DF"`" Starting $DRIV $PNUM $REST $INTERACT"
echo `date "$DF"`" Starting $DRIV $PNUM $REST $INTERACT" >> $LOGFILE

cd $PT
if [ "$DOUNZIP" = "Y" ]; then
if [ ! -d "$PNUM" ]; then
echo `date "$DF"`" Looking for zip files to unzip"
ZFILE=`ls -tr *$PNUM*.zip awk '{print $1}'`; export ZFILE
if [ -n "$ZFILE" ]; then
if [ -f "$ZFILE" ]; then
echo `date "$DF"`" Unzipping $ZFILE"
echo `date "$DF"`" Unzipping $ZFILE" >> $LOGFILE
unzip "$ZFILE"
else
echo `date "$DF"`" ERROR: File $PT/*$PNUM*.zip does not exist."
echo `date "$DF"`" ERROR: File $PT/*$PNUM*.zip does not exist." >> $LOGFILE
exit 1
fi
else
echo `date "$DF"`" ERROR: File $PT/*$PNUM*.zip does not exist."
echo `date "$DF"`" ERROR: File $PT/*$PNUM*.zip does not exist." >> $LOGFILE
exit 1
fi
fi
fi

if [ ! -d $PNUM ]; then
echo `date "$DF"`" ERROR: Directory $PT/$PNUM does not exist."
echo `date "$DF"`" ERROR: Directory $PT/$PNUM does not exist." >> $LOGFILE
exit 1
fi

if [ "$SETMAINT" = "Y" ]; then
echo `date "$DF"`" Enable maintenance mode"
echo `date "$DF"`" Enable maintenance mode" >> $LOGFILE
sqlplus -s apps/`get_pw db apps` @$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE
fi

echo `date "$DF"`" Calling adpatch"
echo `date "$DF"`" Calling adpatch" >> $LOGFILE
adpatch \
defaultsfile=$DEFFILE \
logfile=${DRIV}${PNUM}_$CTXT.log \
patchtop=$PT/$PATCH \
driver=${DRIV}${PNUM}.drv \
workers=6 interactive=$INTERACT options=novalidate $REST
#workers=6 interactive=$INTERACT options=novalidate,nocheckfile $REST

if [ -d $PT/$PATCH/backup ]; then
echo `date "$DF"`" mv $PT/$PATCH/backup to $PT/$PATCH/backup_$CTXT"
echo `date "$DF"`" mv $PT/$PATCH/backup to $PT/$PATCH/backup_$CTXT" >>$LOGFILE
mv $PT/$PATCH/backup $PT/$PATCH/backup_$CTXT
fi

if [ "$SETMAINT" = "Y" ]; then
echo `date "$DF"`" Disable maintenance mode"
echo `date "$DF"`" Disable maintenance mode" >> $LOGFILE
sqlplus -s apps/`get_pw db apps` @$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE
fi

cd "$ORIGDIR"

echo `date "$DF"`" Finished applying $DRIV $PNUM $INTERACT"
echo `date "$DF"`" Finished applying $DRIV $PNUM $INTERACT" >> $LOGFILE

Checking How many times driver file gets applied in bug :
------------------------------------------------------------------------------

echo "-----------------To get information related to how many time driver file is applied for bugs---------------"
#---------------------script Executing Syntax-------------------------------
#---------------------<script_file_name>.sh <patch_number>------------------
sqlplus apps/<apps_passwd> << EOF
select * from AD_PATCH_RUN_BUGS where BUG_ID in (select BUG_ID from AD_BUGS where BUG_NUMBER = '$1';
exit
EOF

Finding applied patches and drivers :
--------------------------------------------------

--------Script Running Syntax-----
#--------<script filename>.sh <patch name>-------
echo "Query to find out if any patch except localisation patch is applied or not, if applied, that what all drivers it contain and time of it's application"
sqlplus apps/ezdtst321 << EOF
select A.APPLIED_PATCH_ID, A.PATCH_NAME, A.PATCH_TYPE, B.PATCH_DRIVER_ID, B.DRIVER_FILE_NAME, B.ORIG_PATCH_NAME, B.CREATION_DATE, B.PLATFORM, B.SOURCE_CODE, B.CREATION_DATE, B.FILE_SIZE, B.MERGED_DRIVER_FLAG, B.MERGE_DATE from AD_APPLIED_PATCHES A, AD_PATCH_DRIVERS B where A.APPLIED_PATCH_ID = B.APPLIED_PATCH_ID and A.PATCH_NAME = '$1';
exit
EOF

Finding Patching History :
------------------------------------

echo " To know that if the patch is applied successfully, applied on both node or not, start time of patch application and end time of patch application, patch top location , session id ... patch run id"
#----------------------Syntax for this Script--------------------
#---------------------<Script_name>.sh patch_name---------------
sqlplus apps/ezdtst321 << EOF
select D.PATCH_NAME, B.APPLICATIONS_SYSTEM_NAME, B.NAME, C.DRIVER_FILE_NAME, A.PATCH_DRIVER_ID, A.PATCH_RUN_ID, A.SESSION_ID, A.PATCH_TOP, A.START_DATE, A.END_DATE, A.SUCCESS_FLAG, A.FAILURE_COMMENTS from AD_PATCH_RUNS A, AD_APPL_TOPS B, AD_PATCH_DRIVERS C, AD_APPLIED_PATCHES D where A.APPL_TOP_ID = B.APPL_TOP_ID AND A.PATCH_DRIVER_ID = C.PATCH_DRIVER_ID and C.APPLIED_PATCH_ID = D.APPLIED_PATCH_ID and A.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = '$1')) ORDER BY 3;
exit
EOF

Finding Merged Patch Information :
--------------------------------------------------

echo "-------------------------------find Merged patch Information---------------------------------------"
#---------------- To find Merged patch Information from database in Oracle Applications------------------
#-----------------Script Syntax <Script_file_name>.sh <applied_patch_id>---------------------------------
sqlplus apps/<apps_passwd> << EOF
select bug_number from ad_bugs where bug_id in ( select bug_id from ad_comprising_patches where patch_driver_id =(select patch_driver_id from ad_patch_drivers where applied_patch_id =$1) );
exit
EOF

Finding Base Application Version :
------------------------------------------------

echo " find the base application version"
sqlplus apps/ezdtst321 << EOF
select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE "when updated", ROW_SOURCE_COMMENTS "how it is done" from AD_RELEASES where BASE_RELEASE_FLAG = 'Y';
exit
EOF

Finding Latest Application Version :
-------------------------------------------------


echo "----------- To find the latest application version-----------"
sqlplus apps/ezdtst321 << EOF
select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE updated,ROW_SOURCE_COMMENTS "how it is done",BASE_RELEASE_FLAG "Base version" FROM AD_RELEASES where END_DATE_ACTIVE IS NULL;
exit
EOF


Finding What is being done by the patch :
---------------------------------------------------------


echo "------------------------To find what is being done by the patch--------------------------------------------"
sqlplus apps/<apps_passwd> << EOF
select A.BUG_NUMBER "Patch Number", B. PATCh_RUN_BUG_ID "Run Id",D.APP_SHORT_NAME appl_top, D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B, AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F where A.BUG_ID = B.BUG_ID and B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID and D.FILE_ID = F.FILE_ID and A.BUG_NUMBER = '$1' and B.PATCH_RUN_BUG_ID = ' < > ' and C.EXECUTED_FLAG = 'Y' GROUP BY A.BUG_NUMBER, B.PATCH_RUN_BUG_ID, D. APP_SHORT_NAME, D>SUBDIR, D.FILENAME, E.ACTION_CODE;
exit
EOF



Finding What all has been done during application of patch :
----------------------------------------------------------------------------------

#--------------------------Query to know, what all has been done during application of PATCH-------------------
echo "-------------ScRIPT PROVIDES WHAT ALL HAS BEEN DONE DURING APPLICATION OF PATCH--------------------------"
sqlplus apps/<apps_passwd> << EOF
Select J.PATCh_NAME, H.APPLICATIONS_SYSTEM_NAME Instance_Name, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME appl_top,D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B,AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F, AD_PATCH_RUNS G,
AD_APPL_TOPS H, AD_PATCH_DRIVERS I, AD_APPLIED_PATCHES J where A.BUG_ID = B.BUG_ID and
B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID
and D.FILE_ID = F.FILE_ID and G.APPL_TOP_ID = H.APPL_TOP_ID and G.PATCH_DRIVER_ID = I.PATCH_DRIVER_ID and
I.APPLIED_PATCH_ID = J.APPLIED_PATCH_ID and B.PATCH_RUN_ID = G.PATCH_RUN_ID and C.EXECUTED_FLAG = 'Y' and
G.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID
in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = 'merged'))
GROUP BY J.PATCH_NAME, H.APPLICATIONS_SYSTEM_NAME, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME, D.SUBDIR,
D.FILENAME, E.ACTION_CODE;
exit
EOF