DBAdminisaurus: Open Source Monitoring Application for Oracle
Posted at Wednesday, October 15, 2008
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
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
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.
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.
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.
Update httpd.conf
Next, update the Apache configuration for the directory which will serve DBAdminisaurus
DirectoryIndex index.php
#
# AllowOverride controls what directives may be placed in .htaccess files.
# It can be "All", "None", or any combination of the keywords:
# Options FileInfo AuthConfig Limit
#
AllowOverride None
#
# Controls who can get stuff from this server.
#
Order deny,allow
Deny from all
Allow from 192.168.1.104
Allow from 127.0.0.1
Allow from localhost
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.
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'
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
[mysql]
mysql.username = dbsaurus
mysql.hostname = localhost:3307
mysql.password = dbsaurus
mysql.default_db = dbsaurus
[security]
aes.salt = babalugats
mysql.username = dbsaurus
mysql.hostname = localhost:3307
mysql.password = dbsaurus
mysql.default_db = dbsaurus
[security]
aes.salt = babalugats
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
DirectoryIndex index.php
#
# AllowOverride controls what directives may be placed in .htaccess files.
# It can be "All", "None", or any combination of the keywords:
# Options FileInfo AuthConfig Limit
#
AllowOverride None
#
# Controls who can get stuff from this server.
#
Order deny,allow
Deny from all
Allow from 192.168.1.104
Allow from 127.0.0.1
Allow from localhost
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
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Oct 16 10:58:56 2008
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
User: dbsaurus
Password:
User created.
Grant succeeded.
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
User: dbsaurus
Password:
User created.
Grant succeeded.
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
Installing Apache/PHP/11g Instant Client on Windows
Posted at Tuesday, October 14, 2008
This post covers the steps necessary to set-up open source development tools on Windows. Specifically, PHP, Apache, and Oracle's 11g Instant Client. This will allow you to deploy applications locally to a Windows server or laptop should you not have other resources.
Here is the software I used:
Apache 2.2.9 with OpenSSL (x86) on Windows
php-5.2.6-win32-installer.msi
instantclient-basic-win32-11.1.0.6.0.zip
mysql-5.0.67-win32.zip
Install Apache
Normally I shutdown IIS before installing Apache on Windows as not to disturb the installation routine (port conflicts):
Alternatively, you could shut this down via the GUI.
Run the Apache Installer
Double-click or enter on the command-line:
Enter the information relevant to your Apache instance
Accept the default directory or enter your desired path
Test connectivity to your Apache instance
Now we will change the port so that the Apache instance doesn't conflict with IIS. We will do this by editing httpd.conf
Change Listen 80 to reflect a different port (in my case 81) and save the file.
Bounce Apache to test
Afterwards, you should be able to contact Apache on the new port
Restart IIS and its dependent services
Install PHP
Run the PHP Installer
Double-click or enter on the command-line:
Accept the default directory or browse to the desired location
Choose 'Apache 2.2.x' since that is the version of Apache we just installed
Point the installer to the conf directory of the Apache installation you performed previously
Install the PHP options that are needed for the applications you wish to create or run. In my case, I installed: Oracle 8, GD, MySQL, and OpenSSL. I also selected the 'Register php' option.
Bounce Apache. This time I used the Apache Service Monitor from the toolbar.
Test your PHP installation by placing the following file in your Apache instance's htdocs directory:
test.php
Access the file from your web browser to verify that PHP is integrated with Apache properly. Also, review any options that you had the PHP installer configure.
Install 11g Instant Client
In order to connect to Oracle databases using the OCI component of PHP, you will need a client local to the machine that contains the necessary networking libraries. In most cases, the easiest, lightest way to do this is with the Oracle Instant Client.
Simply unzip the Instant Client to the location of your choice
Afterwards, my Instant Client is located at C:\instantclient_11_1
Create a tnsnames.ora file in the Instant Client software directory and add any services you wish to connect to.
tnsnames.ora (example)
You will need to update your system's environment variables so that Apache can find the necessary libraries. Create or Update TNS_ADMIN, PATH, and ORACLE_HOME variables with the values from your Instant Client via Control Panel > System > Advanced > Environment Variables. You may also have to consider altering any NLS_LANG settings of ORACLE_HOMEs in the registry.
Reboot your system
Create and modify the following script to test Oracle connectivity
oratest.php
Test the script from your browser
If the test succeeded, then you have a working configuration.
Optionally, update OCI settings in php.ini. The settings I use are below:
php.ini
Optionally, Install and Configure MySQL
Run the Installer and follow the Screens
Test Integration with Apache and PHP, by creating the following script
testmysql.php
Test the script from your browser
If the test succeeded, then you have a working configuration.
Here is the software I used:
Apache 2.2.9 with OpenSSL (x86) on Windows
php-5.2.6-win32-installer.msi
instantclient-basic-win32-11.1.0.6.0.zip
mysql-5.0.67-win32.zip
Install Apache
Normally I shutdown IIS before installing Apache on Windows as not to disturb the installation routine (port conflicts):
net stop "IIS Admin"
The following services are dependent on the IIS Admin service.
Stopping the IIS Admin service will also stop these services.
World Wide Web Publishing
Simple Mail Transfer Protocol (SMTP)
Do you want to continue this operation? (Y/N) [N]: Y
The World Wide Web Publishing service is stopping....
The World Wide Web Publishing service was stopped successfully.
The Simple Mail Transfer Protocol (SMTP) service is stopping.
The Simple Mail Transfer Protocol (SMTP) service was stopped successfully.
....
The IIS Admin service was stopped successfully.
Stopping the IIS Admin service will also stop these services.
World Wide Web Publishing
Simple Mail Transfer Protocol (SMTP)
Do you want to continue this operation? (Y/N) [N]: Y
The World Wide Web Publishing service is stopping....
The World Wide Web Publishing service was stopped successfully.
The Simple Mail Transfer Protocol (SMTP) service is stopping.
The Simple Mail Transfer Protocol (SMTP) service was stopped successfully.
....
The IIS Admin service was stopped successfully.
Alternatively, you could shut this down via the GUI.
Run the Apache Installer
Double-click or enter on the command-line:
apache_2.2.9-win32-x86-openssl-0.9.8h-r2.msi
Enter the information relevant to your Apache instance
Accept the default directory or enter your desired path
Test connectivity to your Apache instance
Now we will change the port so that the Apache instance doesn't conflict with IIS. We will do this by editing httpd.conf
C:\Program Files\Apache\Apache229\conf> notepad httpd.conf
Change Listen 80 to reflect a different port (in my case 81) and save the file.
Bounce Apache to test
net stop "Apache2.2"
The Apache2.2 service is stopping.
The Apache2.2 service was stopped successfully.
The Apache2.2 service was stopped successfully.
net start "Apache2.2"
The Apache2.2 service is starting.
The Apache2.2 service was started successfully.
The Apache2.2 service was started successfully.
Afterwards, you should be able to contact Apache on the new port
Restart IIS and its dependent services
net start "IIS Admin"
The IIS Admin service is starting.
The IIS Admin service was started successfully.
The IIS Admin service was started successfully.
net start "World Wide Web Publishing"
The World Wide Web Publishing service is starting.
The World Wide Web Publishing service was started successfully.
The World Wide Web Publishing service was started successfully.
net start "FTP Publishing"
The FTP Publishing service is starting.
The FTP Publishing service was started successfully.
The FTP Publishing service was started successfully.
Install PHP
Run the PHP Installer
Double-click or enter on the command-line:
php-5.2.6-win32-installer.msi
Accept the default directory or browse to the desired location
Choose 'Apache 2.2.x' since that is the version of Apache we just installed
Point the installer to the conf directory of the Apache installation you performed previously
Install the PHP options that are needed for the applications you wish to create or run. In my case, I installed: Oracle 8, GD, MySQL, and OpenSSL. I also selected the 'Register php' option.
Bounce Apache. This time I used the Apache Service Monitor from the toolbar.
Test your PHP installation by placing the following file in your Apache instance's htdocs directory:
test.php
<?php phpinfo() ?>
Access the file from your web browser to verify that PHP is integrated with Apache properly. Also, review any options that you had the PHP installer configure.
Install 11g Instant Client
In order to connect to Oracle databases using the OCI component of PHP, you will need a client local to the machine that contains the necessary networking libraries. In most cases, the easiest, lightest way to do this is with the Oracle Instant Client.
Simply unzip the Instant Client to the location of your choice
Afterwards, my Instant Client is located at C:\instantclient_11_1
Create a tnsnames.ora file in the Instant Client software directory and add any services you wish to connect to.
tnsnames.ora (example)
EMREP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1.colestock.test)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = emrep.colestock.test)
(INSTANCE_NAME = emrep)
)
)
CUBS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1.colestock.test)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cubs.colestock.test)
)
)
JLC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1525))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1525))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jlc.colestock.test)
)
)
JLC2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jlc.colestock.test)
(INSTANCE_NAME = jlc2)
)
)
JLC1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jlc.colestock.test)
(INSTANCE_NAME = jlc1)
)
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1.colestock.test)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = emrep.colestock.test)
(INSTANCE_NAME = emrep)
)
)
CUBS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1.colestock.test)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cubs.colestock.test)
)
)
JLC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1525))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1525))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jlc.colestock.test)
)
)
JLC2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jlc.colestock.test)
(INSTANCE_NAME = jlc2)
)
)
JLC1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jlc.colestock.test)
(INSTANCE_NAME = jlc1)
)
)
You will need to update your system's environment variables so that Apache can find the necessary libraries. Create or Update TNS_ADMIN, PATH, and ORACLE_HOME variables with the values from your Instant Client via Control Panel > System > Advanced > Environment Variables. You may also have to consider altering any NLS_LANG settings of ORACLE_HOMEs in the registry.
Reboot your system
Create and modify the following script to test Oracle connectivity
oratest.php
<?php
$db_conn = ocilogon('username', 'password', 'db');
$cmdstr = "select 'It Works' as \"Message\" from dual";
$parsed = ociparse($db_conn, $cmdstr);
ociexecute($parsed);
$nrows = ocifetchstatement($parsed, $results);
for ($i = 0; $i < $nrows; $i++ ) {
echo $results["Message"][$i] ;
}
?>
$db_conn = ocilogon('username', 'password', 'db');
$cmdstr = "select 'It Works' as \"Message\" from dual";
$parsed = ociparse($db_conn, $cmdstr);
ociexecute($parsed);
$nrows = ocifetchstatement($parsed, $results);
for ($i = 0; $i < $nrows; $i++ ) {
echo $results["Message"][$i] ;
}
?>
Test the script from your browser
If the test succeeded, then you have a working configuration.
Optionally, update OCI settings in php.ini. The settings I use are below:
php.ini
oci8.privileged_connect = Off
oci8.max_persistent = -1
oci8.persistent_timeout = 60
oci8.ping_interval = 60
oci8.statement_cache_size = 50
oci8.default_prefetch = 100
oci8.old_oci_close_semantics = On
oci8.events = On
oci8.max_persistent = -1
oci8.persistent_timeout = 60
oci8.ping_interval = 60
oci8.statement_cache_size = 50
oci8.default_prefetch = 100
oci8.old_oci_close_semantics = On
oci8.events = On
Optionally, Install and Configure MySQL
Run the Installer and follow the Screens
Test Integration with Apache and PHP, by creating the following script
testmysql.php
<?php
$conn = mysql_connect("localhost:3307", "root", "password");
mysql_select_db("mysql",$conn);
$results = mysql_query("select 'It works!'", $conn);
mysql_data_seek($results,0);
$x = mysql_fetch_array($results,MYSQL_NUM);
echo var_dump($x);
?>
$conn = mysql_connect("localhost:3307", "root", "password");
mysql_select_db("mysql",$conn);
$results = mysql_query("select 'It works!'", $conn);
mysql_data_seek($results,0);
$x = mysql_fetch_array($results,MYSQL_NUM);
echo var_dump($x);
?>
Test the script from your browser
If the test succeeded, then you have a working configuration.
Labels: Installation, Instant Client, OCI, PHP
_507.png)
Subscribe
