DBAdminisaurus: Open Source Monitoring Application for Oracle

I have been working on an open source application for monitoring Oracle; for lack of a better name, I am calling it "DBAdminisaurus."

DBAdminisaurus is basically a PHP application which allows you to see real-time as well as historical information about your Oracle databases. It leverages open source tools; specifically: Apache, Oracle 11g Instant Client, PHP, MySQL, and FusionCharts Free. Accordingly, DBAdminisaurus costs nothing!

I highly recommend FusionCharts Free for developers building these kinds of applications.



DBAdminisaurus is fully customizable. For example, all chart metadata is contained in an easy to edit XML file.

DBAdminisaurus uses a local MySQL database for login/authenication information.

---

Prerequisites

• Apache Instance with OCI/PHP/MySQL Modules


• PHP with GD,OCI, and MySQL


• Oracle Client


• MySQL

If you need help configuring your server to meet the prerequisities listed above, reference the following blog posts. In the case of a Linux/Unix-based distribution, many of these services may already be installed and configured.

Windows

Installing Apache/PHP/11g Instant Client/MySQL on Windows

Linux

Installing Apache/Tomcat

Installing PHP/11g Instant Client/OCI8

Installing Multiple Versions of MySQL

---

Download DBAdminisaurus

DBAdminisaurus Version 1.0

Initial Release, 10/15/2008

---

Installation

For the most part, installation should be platform independent. The following shows how to get the application up and running on a Windows platform, however, the same basic steps should apply to other platforms as well.

Unzip DBAdminisaurus to an Apache Directory

---

Install the dbsaurus MySQL Database

The script to do this is found in the setup directory of DBAdminisaurus.

Edit the setup/setup_dbsaurus.sql file to reflect the desired password for the dbsaurus user.

Connect to your local MySQL database and execute this script as root.

C:\Program Files\Apache\Apache229\htdocs\setup>mysql -h localhost -u root -P 330
7 -p < setup_dbsaurus.sql
Enter password: ********

This script creates the dbsaurus user, database, as well as the required tables and indexes.

---

Move and Update /setup/dbsaurus.ini

DBAdminisaurus has a single application configuration file: dbsaurus.ini. Before using DBAdminisaurus, you will want to move this file out of the Apache directory, to a location local to the server, which cannot be accessed by remote users.

C:\Program Files\Apache\Apache229\htdocs\setup> move dbsaurus.ini C:\dbsaurus.ini

Next, edit dbsaurus.ini, changing the following values: 'mysql.hostname,' 'mysql.password,' and 'aes.salt.' The 'mysql' values are for the local MySQL database you just created. The 'aes.salt' value is used during the encryption of your oracle database passwords. Change this value to something that you can remember.

dbsaurus.ini

---

Update /dbsaurus/dbsaurusconfig.php

The /dbsaurus/dbsaurusconfig.php file needs to be updated to reflect the location of your dbsaurus.ini file. Alter the following line to reflect the path to your configuration file.

var $ini = 'C:\dbsaurus.ini';

---

Update httpd.conf

Next, update the Apache configuration for the directory which will serve DBAdminisaurus


In my case, I simply update the 'Directory' directive to restrict access as appropriate and to automatically serve 'index.php' by default.

---

Test Logging In

By default, one user already exists, named 'test' with the initial password of 'test'.



If everything is working you should see the following screen.

---

Create Oracle Users

DBAdminisaurus needs to connect to your Oracle databases in order to run various queries. Therefore, you should create a user against each Oracle database you wish to monitor using the /setup/setup_orauser.sql script.

$ export ORACLE_SID=emrep
$ . oraenv
$ sqlplus "/ as sysdba" @setup_orauser.sql

---

Setup the Oracle User in DBAdminisaurus

Select 'Databases'



Click 'Add' and Enter the Database Information; Click 'Submit' (Remember that the value of 'Instance' should correspond to the net service name in your tnsnames.ora.



You should see an entry for the newly entered database



Select 'Dashboard Rex' from the Navigation Bar. Your new database is now available in the drop down.

Test the Dashboard by choosing your database from the drop-down menu; click 'Submit'

Labels: DBAdminisaurus, Monitoring