Tuesday, December 22, 2015

Automate Database OS Audit File Cleanup

Using EM12c corrective action of type SQL Script you can automate the cleanup of the database OS audit file that are created in the adump directory.

If you want details on how to create and deploy corrective action use my blog post here


Below is the script that can be used in the SQL Script corrective action to delete anything older then sysdate-1 in the adump directory

WHENEVER SQLERROR EXIT FAILURE;
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   last_archive_time => SYSDATE-1);
END;
/

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS);
END;
/


The metric that monitors the adump size of audit file is below so you can add the corrective action to the metric.

Monday, December 21, 2015

Monitoring SAP MAXDB Database with Cloud Control 12c


Oracle DBAs

In an ever changing world where Oracle DBAs are asked to do more every day there may come a time where you are asked to monitor a database that Cloud Control does not monitor or have no plugin available.

The Problem

In our case we were asked to take over the support and monitoring of the SAP MAXDB database o a Windows server. If you want to learn more about MAXDB go here.

Out of the box there is currently no monitoring for MAXDB in Cloud Control 12c nor does there exist a plugin. Not sure if we will ever see this plugin as we all know the history with Oracle and SAP.

As we put our head together to try to find the right solution to monitor MAXDB we wanted to use are existing monitor tools  so we can continue to monitor all of our databases from a central location. We also want to be able to use existing programs that we already know to setup any scripting for this monitoring Ex bash, python.

What we were asked to monitor:
Disk space
CPU and Memory usage
Server down
MAXDB status

First 3 metrics are supported out of the box by the OEM agent. The last which is the MAXDB status is what we setup to use a metric extension for this metric.

Solution

The solution we came up with was using CYGWIN, Cloud Control and Metric Extensions. In this blogger I will discuss how you can implement the same solution on you side.

Per-Steps

The following will not be discussed in the blogged
OEM Agent installed and configured on MAXDB server
Cygwin installed and configured
MAXDB Database configured and running

Steps

1. Create a script like the one below to monitor the state of the MAXDB database
DB_STATE=`/cygdrive/c/DatabaseStudio/pgm/dbmcli -d C1D -u control,<pw> db_state |grep LINE`;

if [ $DB_STATE = OFFLINE ] ; then
em_result=OFFLINE
else
em_result=ONLINE
fi

echo "em_result=$em_result"
2. Save this on the MAXDB server as something like check_maxdb_state.sh
3. Now let create a metric extension that will call this script
4. Login to OEM and go to Enterprise>Monitoring>Metric Extension
5. Give it a name, set adapter and enable collection as needed
Note: the adapter type is OS Command
 6. Set the command to call the script that was created then set the starts with em_result= then click next
7. Set metric columns as noted in the screenshot click next


8. Set credentials as needed


9.  Run a test as needed
10. Review the summary and click finished

Make sure to add this metric to you incident rules to you can be alerted when MAXDB goes OFFLINE




My webpages
http://db12c.blogspot.com/
http://cloudcontrol12c.blogspot.com/

http://www.youtube.com/user/jfruiz11375

Follow me on Twitter

About Me

My photo

Senior DBA with over 16 years experience, specializing in "Database Performance Tuning" and High Availability (RAC, Data Guard & Oracle Golden Gate).