ORACLE自动添加白名单脚本

#!/bin/bash
# Automatically add whitelist
# author:chenlin
# Execute the script using the grid user
# Please fill in the IP address at the position of the first variable (IP),such as:" 192.168.0.113", the IP address is not followed by a comma, paying attention to adding a space before the IP address
# If there are multiple IP addresses, please write them in the format, such as:" 192.168.0.113,\n 192.168.0.114,\n 192.168.0.115", the last IP address is not followed by a comma, and pay attention to the spaces
# If there is no prompt of "The listening program has successfully registered the service" after 3 minutes, it indicates that the service registration has failed. Please go back and check the IP filling format before adding again

IP=" 192.168.0.113,\n 192.168.0.114,\n 192.168.0.115"

source ~/.bash_profile
DATE=$(date +"%Y%m%d")
RMFILE=rm
cd $ORACLE_HOME/network/admin

cat sqlnet.ora |sed '/^$/d' > sqlnet_bak.ora

FIR_COUNT=`cat sqlnet_bak.ora|sed -e 's/[ ]*$//g' |wc -l`

sed -i '$d' sqlnet_bak.ora
END_COUNT=`tail -1 sqlnet_bak.ora`
sed -i '$d' sqlnet_bak.ora
echo "$END_COUNT," >> sqlnet_bak.ora

echo -e "$IP" >>sqlnet_bak.ora
echo ")" >>sqlnet_bak.ora

COM_COUNT=`cat sqlnet_bak.ora|sed -e 's/[ ]*$//g' |wc -l`

ps -ef|grep lsn |grep -v -E "grep|ASM"|grep grid |awk -F ' ' '{print $9}' > listener_file.txt
LISTENER_COUNT=`cat listener_file.txt|sed -e 's/[ ]*$//g' |wc -l`
LISTENER1=`cat listener_file.txt | awk 'NR==1'`
LISTENER2=`cat listener_file.txt | awk 'NR==2'`
LISTENER3=`cat listener_file.txt | awk 'NR==3'`
LISTENER4=`cat listener_file.txt | awk 'NR==4'`
LISTENER5=`cat listener_file.txt | awk 'NR==5'`
LISTENER6=`cat listener_file.txt | awk 'NR==6'`

${RMFILE} listener_file.txt

if [[ $COM_COUNT -gt $FIR_COUNT ]]; then
        cd $ORACLE_HOME/network/admin
        mv sqlnet.ora sqlnet.ora_$DATE.bak
        mv sqlnet_bak.ora sqlnet.ora
        chmod 640 sqlnet.ora
        echo "The sqlnet file successfully added"
  else 
        echo "The sqlnet file was not successfully added"
        ${RMFILE} sqlnet_bak.ora
fi 

## reload listener
if [ $COM_COUNT -gt $FIR_COUNT ] && [ $LISTENER_COUNT -eq 1 ]; then 
        lsnrctl reload $LISTENER1 > /dev/null 2>&1
        echo "One listeners were successfully reloaded!"
  elif [ $COM_COUNT -gt $FIR_COUNT ] && [ $LISTENER_COUNT -eq 2 ]; then
        lsnrctl reload $LISTENER1 > /dev/null 2>&1
        lsnrctl reload $LISTENER2 > /dev/null 2>&1
        echo "Tow listeners were successfully reloaded!"
  elif [ $COM_COUNT -gt $FIR_COUNT ] && [ $LISTENER_COUNT -eq 3 ]; then
        lsnrctl reload $LISTENER1 > /dev/null 2>&1
        lsnrctl reload $LISTENER2 > /dev/null 2>&1
        lsnrctl reload $LISTENER3 > /dev/null 2>&1
        echo "three listeners were successfully reloaded!"
  elif [ $COM_COUNT -gt $FIR_COUNT ] && [ $LISTENER_COUNT -eq 4 ]; then
        lsnrctl reload $LISTENER1 > /dev/null 2>&1
        lsnrctl reload $LISTENER2 > /dev/null 2>&1
        lsnrctl reload $LISTENER3 > /dev/null 2>&1
        lsnrctl reload $LISTENER4 > /dev/null 2>&1
        echo "Four listeners were successfully reloaded!"
  elif [ $COM_COUNT -gt $FIR_COUNT ] && [ $LISTENER_COUNT -eq 5 ]; then
        lsnrctl reload $LISTENER1 > /dev/null 2>&1
        lsnrctl reload $LISTENER2 > /dev/null 2>&1
        lsnrctl reload $LISTENER3 > /dev/null 2>&1
        lsnrctl reload $LISTENER4 > /dev/null 2>&1
        lsnrctl reload $LISTENER5 > /dev/null 2>&1
        echo "Five listeners were successfully reloaded!"
  elif [ $COM_COUNT -gt $FIR_COUNT ] && [ $LISTENER_COUNT -eq 6 ]; then
        lsnrctl reload $LISTENER1 > /dev/null 2>&1
        lsnrctl reload $LISTENER2 > /dev/null 2>&1
        lsnrctl reload $LISTENER3 > /dev/null 2>&1
        lsnrctl reload $LISTENER4 > /dev/null 2>&1
        lsnrctl reload $LISTENER5 > /dev/null 2>&1
        lsnrctl reload $LISTENER6 > /dev/null 2>&1
        echo "six listeners were successfully reloaded!"
  else 
        echo "The listener was not successfully reload!"
fi

## check service
while lsncount=`lsnrctl stat $LISTENER1 |grep Instance |wc -l`;
do
if [ $lsncount -gt 0 ]; then
        echo "The listening program has successfully registered the service"
        break
fi
sleep 10
done

##--------------------------------------------------------------------------------##
##----------------The following script is a rollback operation--------------------##
##--------------------------------------------------------------------------------##

# cd $ORACLE_HOME/network/admin
# mv sqlnet.ora_$DATE.bak sqlnet.ora
# ps -ef|grep lsn |grep -v -E "grep|ASM"|grep grid |awk -F ' ' '{print $9}' > listener_file.txt
# LISTENER_COUNT=`cat listener_file.txt|sed -e 's/[ ]*$//g' |wc -l`
# LISTENER1=`cat listener_file.txt | awk 'NR==1'`
# LISTENER2=`cat listener_file.txt | awk 'NR==2'`
# LISTENER3=`cat listener_file.txt | awk 'NR==3'`
# LISTENER4=`cat listener_file.txt | awk 'NR==4'`
# LISTENER5=`cat listener_file.txt | awk 'NR==5'`
# LISTENER6=`cat listener_file.txt | awk 'NR==6'`
# 
# if  [ $LISTENER_COUNT -eq 1 ]; then 
#         lsnrctl reload $LISTENER1 > /dev/null 2>&1
#         echo "One listeners were successfully reloaded!"
#   elif [ $LISTENER_COUNT -eq 2 ]; then
#         lsnrctl reload $LISTENER1 > /dev/null 2>&1
#         lsnrctl reload $LISTENER2 > /dev/null 2>&1
#         echo "Tow listeners were successfully reloaded!"
#   elif [ $LISTENER_COUNT -eq 3 ]; then
#         lsnrctl reload $LISTENER1 > /dev/null 2>&1
#         lsnrctl reload $LISTENER2 > /dev/null 2>&1
#         lsnrctl reload $LISTENER3 > /dev/null 2>&1
#         echo "three listeners were successfully reloaded!"
#   elif [ $LISTENER_COUNT -eq 4 ]; then
#         lsnrctl reload $LISTENER1 > /dev/null 2>&1
#         lsnrctl reload $LISTENER2 > /dev/null 2>&1
#         lsnrctl reload $LISTENER3 > /dev/null 2>&1
#         lsnrctl reload $LISTENER4 > /dev/null 2>&1
#         echo "Four listeners were successfully reloaded!"
#   elif [ $LISTENER_COUNT -eq 5 ]; then
#         lsnrctl reload $LISTENER1 > /dev/null 2>&1
#         lsnrctl reload $LISTENER2 > /dev/null 2>&1
#         lsnrctl reload $LISTENER3 > /dev/null 2>&1
#         lsnrctl reload $LISTENER4 > /dev/null 2>&1
#         lsnrctl reload $LISTENER5 > /dev/null 2>&1
#         echo "Five listeners were successfully reloaded!"
#   elif [ $LISTENER_COUNT -eq 6 ]; then
#         lsnrctl reload $LISTENER1 > /dev/null 2>&1
#         lsnrctl reload $LISTENER2 > /dev/null 2>&1
#         lsnrctl reload $LISTENER3 > /dev/null 2>&1
#         lsnrctl reload $LISTENER4 > /dev/null 2>&1
#         lsnrctl reload $LISTENER5 > /dev/null 2>&1
#         lsnrctl reload $LISTENER6 > /dev/null 2>&1
#         echo "six listeners were successfully reloaded!"
#   else 
#         echo "The listener was not successfully reload!"
# fi
# 
# while lsncount=`lsnrctl stat $LISTENER1 |grep Instance |wc -l`;
# do
# if [ $lsncount -gt 0 ]; then
#         echo "The listening program has successfully registered the service"
#         break
# fi
# sleep 10
# done