Database Check using Perl and Shell script

I ran into a host that did not support Oracle Grid Control Agent 12c since it is running Red Hat 3 OS so I needed to resort to old shell script for alert.

I use a Perl script so that I can eventually put this into a Oracle User Defined Metric ( UDM ) for Grid Control.

Here is the perl script and the shell script.
The PERL script calls the Shell Script.

#!/usr/local/bin/perl
system ("/home/oracle/script/chkdbup.sh");
$infile="/tmp/dbup.tmp";
open(IN,"$infile") || die "cannot open $infile for reading: $!";
$alert=0; # 0 = down, 1 = up
while ( ) {
if (/PRODB/) {
print "1\n";
$alert=1;
}
}
if ( $alert == 0 ) {
system ("/home/oracle/script/alert.sh");
}
close (IN);

Shell Script

#!/bin/bash
export ORACLE_SID=EMGC
export db=PRODDB
export ORAENV_ASK=NO
. oraenv
$ORACLE_HOME/bin/sqlplus /nolog

<< EOF
set pagesize 0
set echo off
set feedback off
conn dbsnmp/password@$db
spool /tmp/dbup.tmp
select global_name from global_name;
spool off
exit
EOF

Migrating from EM Grid Control 11 to 12c Linux 64 bit

Oracle released Enterprise Manager ( EM ) 12c. Here is the procedure I used to migrate from EM 11g on Red Hat Linux 5 x64 to EM 12c.

The oracle online doc for EM 12c is available here: http://download.oracle.com/docs/cd/E24628_01/index.htm

There are a few different upgrade options discussed in this section of the documentation. None of the options met my needs so I followed the path of fresh install on new server and migrated the targets over from 11g to 12c over a week.

My reasons for approaching the upgrade in this fashion were:
- I had a spare server available that was not being used
- I wanted to get a feel for the 12c changes from 11 using my test/dev targets
- I wanted to move production systems over 1 RAC cluster at a time so I could get the correct monitoring in place.

If you are going to attempt upgrade, Oracle has documented a few methods found in link below.
Below is the matrix of the upgrade paths.
http://download.oracle.com/docs/cd/E24628_01/upgrade.121/e22625/overview_upg_approaches.htm#CJAHJGJI

Preparation: Check for required packages for Oracle Management Service ( OMS ).
make-3.81
binutils-2.17.50.0.6
gcc -4.1.1
libaio-0.3.106
glibc-common-2.3.4
libstdc++ -4.1.1
setarch-1.6
sysstat-5.0.5
rng-utils-2.0
glibc-devel-2.5-49 ( 32bit and 64 bit )

rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep glibc
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep make
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep binutils
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep gcc
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep libstdc++
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep glibc-common
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep sysstat
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep setarch
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep rng-utils
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep glibc-devel

Preparation: Check for required packages for Oracle Management Agent ( OMA ).
make-3.81
binutils-2.17.50.0.6
gcc -4.1.1
libaio-0.3.106
glibc-common-2.3.4
libstdc++ -4.1.1
setarch-1.6
sysstat-5.0.5
rng-utils-2

rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep make
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep binutils
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep gcc
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep libstdc++
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep glibc-common
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep sysstat
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep setarch
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep rng-utils
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep libaio-0.3.106

Set kernel.shmmax parameter to a value less than 4 GB
cat /proc/sys/kernel/shmmax
Example: To set shmmax to 3.9 gb
sysctl -w kernel.shmmax=33500744908
cat /etc/sysctl.conf | grep kernel.shmmax

Demo of a simple install from oraclelearning.
http://www.youtube.com/watch?v=i7Zci4eNxW4&NR=1

I performed an advanced install and found the installation process very smooth. I placed the oms and database on same server.

My start, stop, and status scripts for stopping oms just need the OMS path and agent path updated.

When deploying the agents, I used the manual method. Since I had 11g agents on my targets, I removed all the targets in 11g EM, stopped the agent, removed the host target from 11g EM and then un-installed the 11g agent using the below silent install method.

This is documented in: How to Remove the 11.1.0.1.0 Grid Control Management Agent with the Silent Method ID 1068036.1
cd /opt/oracle/agent11g/oui/bin
./runInstaller -silent -deinstall "REMOVE_HOMES={/opt/oracle/agent11g}" -removeallfiles

Before installing the 12c agent using the manual method, I need to make 2 changes to the sudoers file, hashing out requiretty and setting visiblepw

/etc/sudoers
#Defaults requiretty
Defaults visiblepw

After those changes I re-added my database most of which are 10gR2 RAC where I just need to support the cluster name and the CRS HOME path.

So far I am liking the interface changes in EM 12c and have not run into any bugs.

Database Health Check List

Daily Checks
Alert Log
Jobs ( DBMS_JOBS, Scheduler, CronJobs )
Invalid_Objects
Grid Control Alerts
AWR Report
DashBoard
Backup successful
Standby lag is ok

Weekly Check
All the above daily
Dashboards Enhancement
Bad Queries evaluated with explain plan
Daily checks are automated
Job logs are being monitored for success/failurs
Dashboard updated and email is sent and enhanced

Monthly Check
All daily/weekly checks
Password file up to date
Any Oracle issues have updated SR

Quarterly Check
Oracle patches are reviewed
Database DB and APP diagrams are up to date