DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Thursday, September 29, 2016

Script to unlock locked users



# ###########
# Description:
# ###########
echo
echo "================================="
echo "This script LOCKS database users."
echo "================================="
echo
sleep 1

# ###########################
# Listing Available Databases:
# ###########################

# Count Instance Numbers:
INS_COUNT=$( ps -ef|grep pmon|grep -v grep|grep -v ASM|wc -l )

# Exit if No DBs are running:
if [ $INS_COUNT -eq 0 ]
 then
   echo No Database Running !
   exit
fi

# If there is ONLY one DB set it as default without prompt for selection:
if [ $INS_COUNT -eq 1 ]
 then
   export ORACLE_SID=$( ps -ef|grep pmon|grep -v grep|grep -v ASM|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )

# If there is more than one DB ASK the user to select:
elif [ $INS_COUNT -gt 1 ]
 then
    echo
    echo "Select the ORACLE_SID:[Enter the number]"
    echo ---------------------
    select DB_ID in $( ps -ef|grep pmon|grep -v grep|grep -v ASM|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
     do
if [ -z "${REPLY##[0-9]*}" ]
then
          export ORACLE_SID=$DB_ID
          echo Selected Instance:
          echo
          echo "********"
          echo $DB_ID
          echo "********"
          echo
 break
else
 export ORACLE_SID=${REPLY}
 break
fi
     done

fi
# Exit if the user selected a Non Listed Number:
if [ -z "${ORACLE_SID}" ]
then
 echo "You've Entered An INVALID ORACLE_SID"
 exit
fi

# #########################
# Getting ORACLE_HOME
# #########################
  ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|grep -v ASM|awk '{print $1}'|tail -1`
  USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ':'|tail -1`

## If OS is Linux:
if [ -f /etc/oratab ]
  then
  ORATAB=/etc/oratab
  ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
  export ORACLE_HOME

## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
  then
  ORATAB=/var/opt/oracle/oratab
  ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
  export ORACLE_HOME
fi

## If oratab is not exist, or ORACLE_SID not added to oratab, find ORACLE_HOME in user's profile:
if [ -z "${ORACLE_HOME}" ]
 then
  ORACLE_HOME=`grep -h 'ORACLE_HOME=\/' $USR_ORA_HOME/.bash* $USR_ORA_HOME/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
  export ORACLE_HOME
fi

# ########################################
# Exit if the user is not the Oracle Owner:
# ########################################
CURR_USER=`whoami`
if [ ${ORA_USER} != ${CURR_USER} ]; then
 echo ""
 echo "You're Running This Sctipt with User: \"${CURR_USER}\" !!!"
 echo "Please Run This Script With The Right OS User: \"${ORA_USER}\""
 echo "Script Terminated!"
 exit
fi

# #############################
# SQLPLUS: Lock An Oracle User:
# #############################
# Variables
echo
echo "Please enter the USERID:"
echo "======================="
while read USERNAME2
 do
VAL11=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
SELECT COUNT(*) FROM DBA_USERS WHERE USERNAME=upper('$USERNAME2');
EOF
)
VAL22=`echo $VAL11| awk '{print $NF}'`
                        case ${VAL22} in
# If the provided value match an exist username in the DB:
1)
${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
SELECT USERNAME,ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME=upper('$USERNAME2');
PROMPT
PROMPT Locking user [${USERNAME2}] ...
PROMPT
EOF
VAL1=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
ALTER user "$USERNAME2" ACCOUNT LOCK;
EOF
)
VAL2=`echo $VAL1| grep "User altered"`
               if [ -z "${VAL2}" ]
                then
                 echo "Failed to lock User \"${USERNAME2}\" !"
   echo
                   exit
                else
                   echo
                   echo User ${USERNAME2} locked Successfully.
                   echo
                fi; break;;
# If no value provided or the value doesn't match any user in the DB try to search for matching:
                        *) echo; echo "INFO: USER [${USERNAME2}] IS NOT EXIST ON DATABASE [$ORACLE_SID] !"
                           echo; echo "Searching ..."; sleep 1
${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
set linesize 143
set pagesize 1000
set feedback off
set trim on
set echo off
col USERNAME for a30
col account_status for a23
select username,account_status,profile,LOCK_DATE,EXPIRY_DATE from dba_users where username like upper ('%$USERNAME2%');
EOF
                           echo; echo "Please Enter the FULL USERID:"
echo "=============================" ;;
                        esac
 done

2 comments:

Praveen Kumar said...

Valuable information thanks for sharing Oracle APPS DBA Online Training

Sridevi K said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.