Saturday, May 5, 2012

Oracle DBA Tracking Center

For the code or questions: Please contact us at After-Hours Coders

When having to track multiple Oracle databases spread across multiple servers and tying the database to an application, it is difficult to follow all the instances.  This tool, is a comprehensive solution to have one common location for DBAs and other personnel in an IT department to know the status of their Oracle database instances.

The tool uses a SQL Server database, the SQL Server Integration Services ETL tool and the SQL Server Reporting Services reporting tool to compile and present the data.  You will need at least SQL Server Standard Edition to run the SSIS component.  

The design of the solution is meant to answer the following questions:

·         What are my Oracle databases?
·         On what server are they running?
·         What platform is the server?
·         What Oracle version are they?
·         When did the database last startup?

Through a minimum amount of data entry, you can add data to help find out:

·         What application is tied to this database?
·         Is it Production or Development?

This ETL can be scheduled to run as often as every couple of minutes to keep information up to date. 
What do You Need?:

·         SQL Server 2005 Standard Edition or Higher.  This is built in SQL Server 2008 R2.  Install with the Database Engine, Integration Services and Reporting Services.
·         Oracle 10 Client or higher on the same server or computer you will run the ETL package.

There is a database in SQL Server to store information about the Oracle databases. The database name is “DBTRACK” on a SQL Server database instance. When the ETL Runs to completion, updates can be viewed by a running a collection of SQL Server Reporting Services Reports.

Once installed, all you have to do is enter the Oracle instance or database. This tool does the rest!

Here is the ETL in SSIS:

There are two reports available:

The first is the “Applications” report that shows all applications and the related database and type, development or production as well as server.

The second report shows the detailed database information.  The parameters asks for application and type, but defaults to all.

For the code or questions:
Please contact us at After-Hours Coders