Shell Script to Change Sys and System Password in Oracle Database with Video Explanation

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