Shell Script to Change Sys and System Password in Oracle Database


I am running the script from a centralized DB server to connect to all DB's and changing the password. Make sure the DB is able to tnsping to all remote server
I created a TEST_DB/PROD_DB file with the DB/TNS names which will be used for connecting.





cat TEST_DB

TEST1
TEST2
TEST3
TEST4


Script:

#!/bin/bash
#################################################################################
#PURPOSE: TEST DB SYS and SYSTEM password change script
# Usage : sys_system_pass_change.sh [PROD]/[TEST]
#Author Version Date
#Himanshu Singh 1.0 15-AUG-2020
#
##################################################################################

#######################Before change system password prefix#######################
prefx=dundb
##############Change the below value before canging system password##################

newprefx=ebs123
. $HOME/testdb.env
checkfile=TNS_DB_CHECK.log
passch=PASS_CHANGE.log
RECEIPENTS=support@funoracleapps.com
RUN_USER=`who am i |awk '{print $1}'`
if [ $# -ne 1 ]
then
echo "No Parameter passed!! ==\>Either Pass TEST or PROD\<== Exiting"
exit 1;
fi

if [ "$1" == "TEST" ]
then
inst_file=TEST_DB
elif [ "$1" == "PROD" ]
then
inst_file=PROD_DB
else
echo "Wrong Parameter passed!! Exiting"
exit 1;
fi

pass_checker ()
{
for db_name in `cat ${inst_file}|grep -v '^#'`
do
tnsping ${db_name} > /tmp/tnsping_check
count=`grep -i "ok" /tmp/tnsping_check|wc -l`
if [ $count -ne 1 ]
then
echo "Tnsping to ${db_name} failed..Please check"
#else
#echo "TNSPING working on ${db_name}"
fi
dataname=`echo ${db_name}|tr [:upper:] [:lower:]`
echo "======================================================================="
echo " Checking the database ${db_name}" 
echo "======================================================================="
#echo "connect system/${prefx}${dataname}@${db_name}"
sqlplus -s "/as  sysdba" <<EOF
connect system/${prefx}${dataname}@${db_name}
select name from v\$database;
exit;
EOF

done
}

change_pass ()
{
for db_name in `cat ${inst_file}|grep -v '^#'`
do
dataname=`echo ${db_name}|tr [:upper:] [:lower:]`
echo "======================================================================="
echo " Working on the database ${db_name}"
echo "======================================================================="
sqlplus -s "/as  sysdba" <<EOF
connect system/${prefx}${dataname}@${db_name}
select name from v\$database;
alter user sys identified by ${newprefx}${dataname};
alter user system identified by ${newprefx}${dataname};
exit;
EOF

done
}


#################################################################
##### MAIN PROG################################################
################################################################
echo " FUNORACLEAPPS -- SYS and SYSTEM PASSWORD CHANGE UTILITY "
export PS3="Please make a selection [1-3][Press 3 for Exit]  => " 
select fun in Pass_Check Change_Pass Exit
do
case $fun in 

"Pass_Check") pass_checker > ${checkfile}
echo "Completed"
mailx -s "SYS and SYSTEM Password Connection Check in  $1 Instances on `date` executed by ${RUN_USER}" ${RECEIPENTS} < ${checkfile}
;;
"Change_Pass") change_pass > ${passch}
echo "Completed"
mailx -s "SYS and SYSTEM Password Changed Completed in $1 Instances  on `date` executed by ${RUN_USER}" ${RECEIPENTS} <  ${passch}
;;
"Exit") echo "Exiting" 
exit 0;
;;
 *)echo "Invalid option. Program will exit now."
            break
            ;;
esac
done










If you like please follow and comment