Script to connect to All 12c PDB inside every CDB on a specific node and perform desired action

Note : You can use same script in Exadata also.

Changes required for Exadata.

DIR == change /home/monitoring with Shared location across all DB nodes. Eg: ZFS mount point

Schedule below command in crontab script

dcli -l oracle -g ~/dbs_group “$shared_location/ALL_PDB_CONNECT.sh”

 

== Script Start ==

## All_PDB_CONNECT.sh

#Script to Connect to ALL PDB amd perform similar action all PDB — Like User Creation,Parameter check etc
# set the Oracle environement to be able to run in crontab
DIR=/home/monitoring/ALL_PDB
PATH=$PATH:/usr/local/bin
date=`date`
PDB_LOG=$DIR/log/All_PDB_`hostname`.log
if [ -f $PDB_LOG ] ;
then
rm $PDB_LOG
fi
ps -eaf | grep ora_smon|grep -v grep |tr -s ” ” | cut -d ” ” -f8 | cut -d _ -f3|grep ‘[1]$’ > $DIR/config/instance_name_`hostname`
cat $DIR/config/instance_name_`hostname` | while read INSTANCE
do
export ORACLE_SID=$INSTANCE
export ORAENV_ASK=NO
. /usr/local/bin/oraenv > /dev/null
(sqlplus -s -l / as sysdba << EOF
set head off
set echo off
set feedback off
select NAME from v\$pdbs where NAME!=’PDB\$SEED’;
exit
EOF
) > $DIR/config/pdb/PDB_$ORACLE_SID.log
done
ps -eaf | grep ora_smon|grep -v grep |tr -s ” ” | cut -d ” ” -f8 | cut -d _ -f3|grep ‘[1]$’ > $DIR/config/instance_name_`hostname`
cat $DIR/config/instance_name_`hostname` | while read INSTANCE
do
export ORACLE_SID=$INSTANCE
export ORAENV_ASK=NO
. /usr/local/bin/oraenv > /dev/null
cat $DIR/config/pdb/PDB_$ORACLE_SID.log | grep -v ^$| while read PDB
do
$DIR/pdb_run.sh $PDB >> $DIR/log/All_PDB_`hostname`.log
done
done
if [ -f $PDB_LOG ] ;
then
cat $DIR/log/All_PDB_`hostname`.log
exit 1
fi

== Script End ==

 
====================
# Sub Script — pdb_run.sh —
sqlplus -s -l / as sysdba <<EOF
set head off
set echo off
set feedback off
alter session set container=$1;
show con_name
–Any SQL command
select name from v\$database;
Create User test identified by Welcome1;
select name,open_mode from gv\$pdbs;
–Any SQL command
EXIT;
EOF
=======================

Script to check FRA usage on all running Database Simultaneously

Note : You can use same script in Exadata also.

Changes required for Exadata.

FRA_LOG == change /home/monitoring with Shared location across all DB nodes. Eg: ZFS mount point

Schedule below command in crontab script

dcli -l oracle -g ~/dbs_group “$shared_location/FRA_CHECK.sh”

 

=====================================

## Script Start — FRA_CHECK.sh##

#Script to check FRA percentage
# set the Oracle environement to be able to run in crontab

PATH=$PATH:/usr/local/bin
date=`date`
FRA_LOG=/home/monitoring/FRA_CHECK/log/db_fra_check_`hostname`.log
if [ -f $FRA_LOG ] ;
then
rm $FRA_LOG
fi
ps -eaf | grep ora_smon|grep -v grep |tr -s ” ” | cut -d ” ” -f8 | cut -d _ -f3|grep ‘[1]$’ > /home/monitoring/FRA_CHECK/config/instance_name_`hostname`
cat /home/monitoring/FRA_CHECK/config/instance_name_`hostname` | while read INSTANCE
do
export ORACLE_SID=$INSTANCE
export ORAENV_ASK=NO
. /usr/local/bin/oraenv > /dev/null
fra_usage=$(sqlplus -s -l / as sysdba << EOF
set head off
select round((SPACE_USED/SPACE_LIMIT)*100) from V\$RECOVERY_FILE_DEST;
exit
EOF
)
#Based on threshold as per environment.Below threshold is 70%
if ((fra_usage > 70))
then
(
echo “”
echo $date Warning: FRA size percentage is $fra_usage went above the warning of 70 for database $INSTANCE >> /home/monitoring/FRA_CHECK/log/db_fra_check_`hostname`.log
echo “”
)
fi
done
if [ -f $FRA_LOG ] ;
then
cat /home/monitoring/FRA_CHECK/log/db_fra_check_`hostname`.log
exit 1
fi

## Script End ##