Change all Oracle Databases Password on server using Shell scripts

Basically Shell script with PLSQL Block which changes database user passwords for both standolne and Multitenant databases, it is not easy to use PLSQL logic to change passwords for PDB databases in CDB, since we either need to use OEM scheduler trigger the Job or concat perl script to run custom script.

chg_dbpasswd.sh 
#!/bin/sh
## Date       : 11/19/2020
## Version    : v1
## Author     : Sandeep R Narani
## Purpose    : To Check & Change Oracle Database user password in RAC & Standalone Env
##Check the oracle users profile
hostn=`hostname`
if [ -f /etc/oratab ]
then
   ORATAB=/etc/oratab; export ORATAB
fi
#for SID in `cat /etc/oratab |  grep -v "^-" | grep -v "^#" | grep -v "^ORA" | grep -v "^+ASM"| grep -v ':Y'|awk -F: '{print $1}'`
for SID in `ps -e -o command | grep pmon | grep -v grep | grep -v '+ASM'| grep -v '+APX1' | cut -d '_' -f3`
do
ORACLE_SID=${SID}
export ORACLE_SID
ORACLE_HOME=`grep ${ORACLE_SID}: ${ORATAB}|awk -F: '{print $2}'`
export ORACLE_HOME
PATH=$PATH:$ORACLE_HOME/bin
export PATH
echo ${SID}
i=1;
for user in "$@"
do
    echo "Checking Username - $i: $user in ${SID} database";
    i=$((i + 1));
sqlplus -S "/as sysdba"<<EOF |tee -a set_user_${hostn}.log
set serveroutput on
DECLARE
   v_count       INTEGER        := 0;
BEGIN
   SELECT COUNT (1)
     INTO v_count
     FROM dba_users
    WHERE username = UPPER ('${user}');
   IF v_count != 0
   THEN
      EXECUTE IMMEDIATE ('alter user ${user} identified by "{{ db_passwd }}" account unlock');
      DBMS_OUTPUT.put_line ('     ');
      DBMS_OUTPUT.put_line ('User ${user} password changed successful in ${SID} database');
      DBMS_OUTPUT.put_line ('     ');
   ELSE
                DBMS_OUTPUT.put_line ('     ');
                DBMS_OUTPUT.put_line ('User ${user} username not exist in ${SID} database');
                DBMS_OUTPUT.put_line ('     ');
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
      DBMS_OUTPUT.put_line ('     ');
END;
/
exit
EOF
done
done
echo 'Script Completed'

Execution

dbadeeds[oracle:orcl]/home/oracle$ ./chg_dbpasswd.sh system dbsnmp
# orcl
# Checking Username – 1: system in orcl database
# User system password changed successful in orcl database

# PL/SQL procedure successfully completed.

dbadeeds[oracle:orcl]/home/oracle$ ./chg_dbpasswd.sh system dbsnmp
# orcl
# Checking Username – 2: dbsnmp in orcl database
# User dbsnmp password changed successful in orcl database

# PL/SQL procedure successfully completed.

For Oracle Multitentant Databases.

#!/bin/sh
#-----------------------------------------
## Date       : 11/19/2020
## Version    : v1
## Author     : Sandeep R Narani
## Purpose    : To Check & Change Oracle Database user password in CDB
##Check the oracle users profile
hostn=`hostname`
DATE=`date +"%m%d%Y"`
#WORKING_DIR="{{ pdb_working_folder }}"
#LOG_DIR="{{ pdb_working_folder }}"

if [ -f /etc/oratab ]
then
   ORATAB=/etc/oratab; export ORATAB
fi
{% raw %}
for SID in `ps -e -o command | grep pmon | grep -v grep | grep -v '+ASM' | grep -v '+APX1' | cut -d '_' -f3`
do 
length=${#SID}
endindex=$(expr $length - 1)
OH=`echo ${SID:0:$endindex}`
echo $OH
ORACLE_SID=${SID}
export ORACLE_SID
ORACLE_HOME=`grep ${OH}: ${ORATAB}|awk -F: '{print $2}'`
export ORACLE_HOME
PATH=$PATH:$ORACLE_HOME/bin
export PATH
echo ${SID}
getpdbs=$(sqlplus -s "/ as sysdba"  << EOF
set pagesize 0 feedback off verify off heading off echo off;
SELECT name FROM v\$containers WHERE con_id > 2 AND open_mode = 'READ WRITE' ORDER BY 1
/
exit;
EOF
)
echo "List of PDB Databases $getpdbs in this ${OH} Container Database" 
echo "Deleting the output logfiles"
rm -rf $LOG_DIR/chg_dbpasswd_output*.log
i=1;
for user in "$@"
do
 echo "Checking Username - $i: $user in ${SID} database";
    i=$((i + 1));
echo "
{% endraw %}
set serveroutput on
DECLARE
   v_count       INTEGER        := 0;
BEGIN
   SELECT COUNT (1)
     INTO v_count
     FROM dba_users
    WHERE username = UPPER ('${user}');

   IF v_count != 0
   THEN
      EXECUTE IMMEDIATE ('alter user ${user} identified by "{{ db_passwd }}" account unlock');
      DBMS_OUTPUT.put_line ('     ');
      DBMS_OUTPUT.put_line ('User ${user} password changed successful in ${SID} database');
      DBMS_OUTPUT.put_line ('     ');
   ELSE
                DBMS_OUTPUT.put_line ('     ');
                DBMS_OUTPUT.put_line ('User ${user} username not exist in ${SID} database');
                DBMS_OUTPUT.put_line ('     ');
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
      DBMS_OUTPUT.put_line ('     ');
END;
/
" > $WORKING_DIR/chg_dbpasswd_${user}.sql
for run_pdbs in $getpdbs
do
rm -rf $LOG_DIR/chg_dbpasswd_output*.log
echo "Running scripts on this pdb database - $run_pdbs"
echo "$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -d $WORKING_DIR -c '$run_pdbs' -l $LOG_DIR -b chg_dbpasswd_output chg_dbpasswd_${user}.sql" >runcmd
chmod 755 $WORKING_DIR/runcmd
$WORKING_DIR/runcmd
sleep 2s
rm -rf $LOG_DIR/chg_dbpasswd_output*.log
done
done
echo "script Completed"
done

DBADEEDS