Got an environment with 15 instances (15 – databases) on a host, ya this is an ideal environment for consolidations, but pre-12c which will introduce many ‘background processes’ and many overheads.
In term of adminitration and monitoring I created a bash function to simplify startup, shutdown, check status – colored whether db is up (green) or down (red), run common sql, check alertlog based on instance name supplied.
Beware the help is in Indonesian, please call google translate to get help :)
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=`hostname`; export ORACLE_HOSTNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0.3/dbhome_1; export ORACLE_HOME
ORACLE_SID=db1; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=$PATH:$HOME/bin:/usr/sbin:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
alias sq=’sqlplus / as sysdba’
function alert {
SID=$1
export ORACLE_SID=$SID
unset testvar
testvar=$(sqlplus -s / as sysdba <<!
set pagesize 0 feedback off verify off heading off echo off;
select value from v\$parameter where name=’background_dump_dest’;
exit;
!
echo $testvar
)
tail -200f ${testvar}/alert_${SID}.log
}
function dbstatus {
SID=$1
OPT=$2
export ORACLE_SID=$SID
unset testvar2
testvar2=$(sqlplus -s / as sysdba <<!
set pagesize 0 feedback off verify off heading off echo off;
select status from v\$instance;
exit;
!
)
echo -e “$SID \t=> ${testvar2}”
}
function dbup {
SID=$1
OPT=$2
export ORACLE_SID=$SID
unset testvar8
testvar8=$(sqlplus -s / as sysdba <<!
set pagesize 0 feedback off verify off heading off echo off;
startup $OPT;
exit;
!
)
dbstatus $SID
}
function dbdown {
SID=$1
export ORACLE_SID=$SID
unset testvar3
testvar3=$(sqlplus -s / as sysdba <<!
shutdown immediate;
exit;
!
)
CK1=$(ps -ef|grep ora_smon_${SID} | grep -v grep | wc -l)
if [ ${CK1} -eq 0 ]; then echo “$SID was down”; else echo “$SID still running”; fi
}
function dbora {
CMD=$1
SIDS=$2
OPTS=$3
declare -a DBS=(‘db1’ ‘db2’ ‘db3’ ‘db4’
‘db5’ ‘db6’ ‘db7’ ‘db8’
‘db9’ ‘db10’ ‘db11’ ‘db12’
‘db13’ ‘db14’ ‘db15’);
if [ ${CMD} == “start” ]; then
if [ ${SIDS} == “all” ]; then
for I in ${DBS[@]}
do
dbup $I $OPTS
done
else
dbup $SIDS $OPTS
fi
fi
if [ ${CMD} == “down” ]; then
if [ ${SIDS} == “all” ]; then
for I in ${DBS[@]}
do
dbdown $I
done
else
dbdown $SIDS
fi
fi
if [ ${CMD} == “list” ]; then
for I in ${DBS[@]}
do
CK2=$(ps -ef|grep ora_pmon_${I} | grep -v grep|wc -l)
if [ ${CK2} -ne 0 ]; then
echo -e “\e[38;5;82m${I} \t: Up”
else
echo -e “\e[38;5;196m${I} \t: Down”
fi
done
echo -e “\e[38;5;247m”
fi
if [ ${CMD} == “status” ]; then
for I in ${DBS[@]}
do
CK2=$(ps -ef|grep ora_pmon_${I} | grep -v grep|wc -l)
if [ ${CK2} -ne 0 ]; then
dbstatus $I
else
echo -e “\e[38;5;196m${I} \t: Down”
fi
echo -n -e “\e[38;5;247m”
done
fi
if [ ${CMD} == “alert” ] && [ -n “${SIDS}” ]; then
CK8=$(ps -ef|grep ora_pmon_${SIDS} | grep -v grep|wc -l)
if [ ${CK8} -ne 0 ]; then
alert ${SIDS}
else
echo -e “\e[38;5;196m${SIDS} \t: Down”
echo -e “\e[38;5;247m”
fi
fi
if [ ${CMD} == “sql” ] && [ -n “${SIDS}” ]; then
CK9=$(ps -ef|grep ora_pmon_${SIDS} | grep -v grep|wc -l)
if [ ${CK9} -ne 0 ]; then
export ORACLE_SID=${SIDS}
if [ -n “${OPTS}” ]; then
sqlplus / as sysdba @${OPTS}
else
sqlplus / as sysdba
fi
else
echo -e “\e[38;5;196m${SIDS} \t: Down”
echo -e “\e[38;5;247m”
fi
fi
if [ ${CMD} == “help” ]; then
echo “cara pemakaian:”
echo “dbora list ==> list + up/down database”
echo “dbora status ==> list + status database”
echo “dbora start all [OPTION] ==> startup semua database berdasarkan OPTION [mount/open/restrict/nomount]”
echo “dbora start [SID] [OPTION] ==> startup satu database berdasarkan nama instance OPTION [mount/open/restrict/nomount]”
echo “dbora down all ==> shutdown semua database”
echo “dbora down [SID] ==> shutdown satu database berdasarkan nama instancenya”
echo “dbora alert [SID] ==> melihat alertlog file berdasarkan nama instancenya”
echo “dbora sql [SID] ==> sqlplus / as sysdba berdasarkan nama instancenya”
echo “dbora sql [SID] [file.sql] ==> sqlplus / as sysdba berdasarkan nama instancenya dan eksekusi file.sql”
fi
}
otak-otak | favorite evening food 