• Script permettant de propager un paramètre SQLPLUS sur tous les SID up

#!/bin/bash
############################### BANCO SANTANDER CH ##############################
#
# Script Name : sqlplus_parameter.sh
# Author      : Mikaël DE LAGRANDRIE
# Role        : UNIX script to push SQLPLUS parameter
# Usage       : none
#
# -------------------------------------------------------------------------------
# REVISIONS
# -------------------------------------------------------------------------------
# 1.0 => Initial version (15/04/2024)
#
################################################################################
 
 
# Checking if current user is Oracle
if [ "$USER" != "oracle" ]; then
        echo "ERROR - this script must be run under ORACLE"
    exit
fi
 
# Oracle sids
oracle_sids=( $(ps -ef | grep  oracle | grep ora_pmon  | sed 's/ora_pmon_//'| awk '{print $8}' |grep -v grep | grep -v sed) )
 
# compter membre array
oracle_sids_nmr=${#oracle_sids[@]}
 
# DG Status and FRA on SID list
for i in "${oracle_sids[@]}"
do
:
echo "#########################################"
echo "######### $i ####################"
 
ORACLE_SID=$i
export ORACLE_HOME=`grep -w ${ORACLE_SID} /etc/oratab | cut -d ":" -f 2`
 
 
echo "==== SQLPLUS ===="
sqlplus / as sysdba <<EOF
ALTER system SET DB_RECOVERY_FILE_DEST_SIZE=300G scope=both;
SET linesize 500
col NAME FOR a50
SELECT name, ROUND(SPACE_LIMIT/1024/1024/1024,2) "Allocated Space(GB)",
ROUND(SPACE_USED/1024/1024/1024,2) "Used Space(GB)",
ROUND(SPACE_RECLAIMABLE/1024/1024/1024,2) "SPACE_RECLAIMABLE (GB)" ,
(SELECT ROUND(ESTIMATED_FLASHBACK_SIZE/1024/1024/1024,2)
FROM V\$FLASHBACK_DATABASE_LOG) "Estimated Space (GB)"
FROM V\$RECOVERY_FILE_DEST;
exit;
EOF
done