Wednesday, May 23, 2018

Shell Script - Add Entry (TNSNAMES.ORA)

Hi, everyone.

Please find below a shell script that can be used to add an entry in the tnsnames.ora:


  • add_tnsnames_entry.sh:
#!/bin/bash

####################### ADD AN ENTRY IN THE TNSNAMES #######################
### 
### add_tnsnames_entry.sh
### PARAMETERS ###
### $1 RECEIVE ORACLE_UNQNAME
### $2 RECEIVE ORACLE_SID
### $3 RECEIVE NAME OF THE LISTENER REQUIRED TO CREATE THE STATIC SERVICE
### $4 RECEIVE ORACLE_HOME OF THE REQUIRED LISTENER TO BE CREATED THE STATIC SERVICE
### 
####################### ADD AN ENTRY IN THE TNSNAMES #######################

export ORACLE_HOME=/opt/oracle/product/12.1.0/db
export TNSNAMES_FILE=tnsnames.ora
export ENTRY_NAME=$1
export HOST_ADDR=$2
export PORT_NUM=$3
export SRV_NAME=$4
export TNS_ADMIN_DIR=$5


if [ -z $1 ] || [ -z $2 ] || [ -z $3 ] || [ -z $4 ] || [ -z $5 ]; then

  echo "Missing one or more required parameters: [ENTRY_NAME] [HOST_ADDR] [PORT_NUM] [SERVICE_NAME] [TNS_ADMIN_DIR]"
  exit;

fi


chk_entry=`cat $TNS_ADMIN_DIR/$TNSNAMES_FILE | grep "$ENTRY_NAME =" | wc -l`

if [ -n "$chk_entry" ] && [ "$chk_entry" = 0 ]; then

  echo "Creating the required entry..."
  echo ""

echo "
$ENTRY_NAME =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = $HOST_ADDR)(PORT = $PORT_NUM))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = $SRV_NAME)
    )
  )
" >> $TNS_ADMIN_DIR/$TNSNAMES_FILE

   elif [ "$chk_entry" != 0 ]; then

   echo ""
echo "This entry already exists: [$ENTRY_NAME]."
echo "Nothing to do!"
echo ""
   exit;
 
fi 

################################################################################

  • chmod +x add_tnsnames_entry.sh

e.g:

./add_tnsnames_entry.sh CDB12C rac1-vip 1521 CDB12C1 /opt/oracle/product/12.1.0/db/network/admin

Shell Script - Add Static Service (LISTENER.ORA)

Hi, everyone.

Please find below a shell script that can be used to add a static service in the required/desired listener:


  • add_lsnr_static_service.sh:

#!/bin/bash

####################### ADD STATIC SERVICE IN THE REQUIRED LISTENER #######################
### 
### add_lsnr_static_service.sh
### PARAMETERS ###
### $1 RECEIVE ORACLE_UNQNAME
### $2 RECEIVE ORACLE_SID
### $3 RECEIVE NAME OF THE LISTENER REQUIRED TO CREATE THE STATIC SERVICE
### $4 RECEIVE ORACLE_HOME OF THE REQUIRED LISTENER TO BE CREATED THE STATIC SERVICE
### 
####################### ADD STATIC SERVICE IN THE REQUIRED LISTENER #######################


export ORACLE_HOME=/opt/oracle/product/12.1.0/db
export ORACLE_UNQNAME=$1
export ORACLE_SID=$2
export LISTENER_HOME=$4
export LISTENER_FILE=network/admin/listener.ora

if [ -z $1 ] || [ -z $2 ] || [ -z $3 ] || [ -z $4 ]; then
echo "Missing one or more required parameters: [GLOBAL_DBNAME] [SID_NAME] [LISTENER_NAME] [LISTENER_HOME]"
exit;
fi


lnt1=`cat $LISTENER_HOME/$LISTENER_FILE | grep SID_NAME | awk '{print $3}' | sed  's/)//g' | wc -l`
lnt2=`cat $LISTENER_HOME/$LISTENER_FILE | grep GLOBAL_DBNAME | awk '{print $3}' | sed  's/)//g' | wc -l`
ln=0
sid_list_ora=`cat $LISTENER_HOME/$LISTENER_FILE | grep "SID_LIST_$3 ="`
cnt_fnd=0

while [ $ln -le $lnt1 ] || [ $ln -le $lnt2 ]; do

sid_name_ora=`cat $LISTENER_HOME/$LISTENER_FILE | grep SID_NAME | awk '{print $3}' | sed  's/)//g' | awk -v line=$ln 'NR==line'`
gb_name_ora=`cat $LISTENER_HOME/$LISTENER_FILE | grep GLOBAL_DBNAME | awk '{print $3}' | sed  's/)//g' | awk -v line=$ln 'NR==line'`


if [ -n "$sid_list_ora" ] && [ "$sid_name_ora" != "$ORACLE_SID" ] || [ "$gb_name_ora" != "$ORACLE_UNQNAME" ] && [ $ln != 0 ]; then
cnt_fnd=1 
else
cnt_fnd=0
fi

if [ -n "$sid_list_ora" ] && [ "$sid_name_ora" == "$ORACLE_SID" ] && [ "$gb_name_ora" == "$ORACLE_UNQNAME" ]; then
echo ""
echo " *** This entry already exists: $sid_name_ora for listener: $3 ***"
echo ""
exit;
elif [ -z "$sid_list_ora" ] && [ $ln -lt 1 ]; then
echo ""
echo "*** Creating SID_LIST for: $3 and adding service: $1 ***"
echo ""
echo "
SID_LIST_$3 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = $ORACLE_UNQNAME)
      (ORACLE_HOME = $ORACLE_HOME)
      (SID_NAME = $ORACLE_SID)
    )
  )
" >> $LISTENER_HOME/$LISTENER_FILE
fi
ln=$(( ln+1 ))
done


if [ "$cnt_fnd" != 0 ]; then
echo ""
echo "*** Adding service entry: $ORACLE_UNQNAME for listener: $3 ***"
echo ""
sed -i '/SID_LIST =/a \ \ \ \ (SID_DESC = \n \ \ \ \ \ (GLOBAL_DBNAME = '$ORACLE_UNQNAME') \n \ \ \ \ \ (ORACLE_HOME = '$ORACLE_HOME') \n \ \ \ \ \ (SID_NAME = '$ORACLE_SID') \n \ \ \ )' $LISTENER_HOME/$LISTENER_FILE
fi


$LISTENER_HOME/bin/lsnrctl reload $3
$LISTENER_HOME/bin/lsnrctl status $3


echo ""
echo ""


###########################################################################################

  •  Adapt ORACLE_HOME variable to your need.
  •  chmod +x add_lsnr_static_service.sh
e.g:

./add_lsnr_static_service.sh CDB12C_DGMGRL CDB12C LISTENER_PRD /opt/oracle/product/12.1.0/db

Pages