Using SQL Server to collect information from your Oracle server

, 2011-06-06

How many DBA's out there have to manage Microsoft SQL Server instances and Oracle database servers? More than you think, and I am one of them. Just over a year ago one of our departments wanted to purchase a very expensive product that only runs on an Oracle database. When the software was purchased; 2 Oracle DBAs were also hired. Then it started, and we were all asked to cross train each other on the two database systems.

On the SQL Server side I had created several reports to provide information on all our SQL Servers via a linked server connection. I decided to do the same and collect basically the same information from the Oracle servers. Here is a list of data I wanted to collect from Oracle.

  • Job Status Data
  • Current Error Information
  • Database ONLINE Status
  • Patch Data
  • Patch Set Data

To perform this task I first had to install the Oracle client (in this case it was for Oracle 10g) on the SQL server that will create the linked connection to the Oracle server. Without the client installed the link server parameter '@provider=' would not work as the Microsoft provided Oracle driver does not work properly. Even Microsoft recommends using the Oracle client. So make sure you install the Oracle client. If you are using a 64bit server just install the 32bit client as the 64 bit client may cause issues. I never did find out why this is the case.

Once installed you can open up the SQL Server Management Studio (SSMS) and create a link connection from your SQL server to your Oracle server. This link can be created by the GUI (see Books On Line) or with the script below.

Note: You must replace 'ORACLESRV1' with the name of your Oracle server. Also you need to change the user name 'REPORTS_USER' and provide its password 'PASSWORD'. Once complete you can execute this code. The user and password are the credentials you have created on the Oracle server. Ensure that the login has the rights to the VIEWs on SYSMAN.MGMT$*

PRINT 'Creating Oracle link connection...'
PRINT ' '
EXEC master.dbo.sp_addlinkedserver @server = N'ORACLESRV1', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'ORACLESP'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORACLESRV1',@useself=N'False',@locallogin=NULL,@rmtuser=N'REPORTS_USER',@rmtpassword='PASSWORD'
EXEC master.dbo.sp_serveroption @server=N'ORACLESRV1', @optname=N'collation compatible', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'ORACLESRV1', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'ORACLESRV1', @optname=N'use remote collation', @optvalue=N'true'

Now that you have created a linked connection to the Oracle server you can test this connection with the following script. It performs a SELECT against a system view on the Oracle server.

SELECT
[JOB_NAME], [JOB_OWNER], [JOB_TYPE], [SCHEDULED_TIME],
[START_TIME], [END_TIME], [STATUS], [TARGET_NAME],
[TARGET_TYPE]
FROM ORACLESRV1..[SYSMAN].[MGMT$JOB_EXECUTION_HISTORY]

The information displayed is the execution history for jobs on your Oracle server. Neat! By querying other system views you can get a lot of information that is also displayed in the Oracle Enterprise Manager Web interface.

Now that your tests are good you should delete the linked connection with the script below.

IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'ORACLESRV1')EXEC master.dbo.sp_dropserver @server=N'ORACLESRV1', @droplogins='droplogins'

This code will detect if the linked connection is there and delete it.

There you have it. You can now see and collect information regarding your Oracle server without using any Oracle tools. You can now collect the related information and display it in SQL Server Report Server (SSRS). The script provided will perform the following:

  1. Creates a database called: ORACLEREPORTS
  2. Creates permanent tables: tblOracleJobStats - Job statistics, tblOracleCurrErrors - Shows errors, tblOracleOnlinStatus - Show if databases are online, tblOraclePatches and tblOraclePatchSets - Show level of patches installed
  3. Creates a stored procedure called: sp_dba_Oracle_Status - This stored procedure will create a new link connection to your Oracle server, perform a TRUNCATE on your tables and then repopulated with several SELECT statements. You can add this stored procedure to a job to collect your data whenever you like. I run this on an hourly basis.

Once the script has executed you can just run the stored procedure to collect your information. Also you can create simple reports in SSRS to display the data. The completed SQL script is located in the Resources section below with additional notes within it.

Thanks,
Rudy

 

 

Resources

Rate

4.36 (11)

Share

Share

Rate

4.36 (11)

Related content

Trace-scrubbing Tools

Andrew Zanevsky shares his trace-scrubbing procedures that make it easy for you to handle large trace files and aggregate transactions by type–even when captured T-SQL code has variations.

SQL Server Profiler is a veritable treasure trove when it comes to helping DBAs optimize their T-SQL code. But, the surfeit of riches (I'm reminded of the Arabian Nights tale of Aladdin) can be overwhelming. I recently had one of those "sinking" feelings when I first tried to make sense of the enormous amount of data collected by traces on a client's servers. At this particular client, the online transactions processing system executes more than 4 million database transactions per hour. That means that even a 30-minute trace that captures "SQL Batch Completed" events results in a table with 2 million rows. Of course, it's simply impractical to process so many records without some automation, and even selecting the longest or most expensive transactions doesn't necessarily help in identifying bottlenecks. After all, short transactions can be the culprits of poor performance when executed thousands of times per minute.

2005-01-11

2,003 reads

Basics of C2 Auditing

Auditing in SQL Server is something that is becoming more and more prevelant, and will likely become more regulated over time. Dinesh Asanka brings us a new article on the basics of SQL Servers most powerful auditing configuration, C2 level auditing.

4.88 (8)

2004-11-03

28,595 reads

Monitoring Drive and Database Free Space

SQL Server will autogrow your databases as they run out of space. But the process doesn't manage space, nor does it check the free space on the drive. Allowing autogrow to grow unchecked and unmanaged will eventually use up all the free disk space and potentially crash your server. New author Mark Nash brings you his system for monitoring space usage and generating a report that eases this process.

4.4 (5)

2006-08-25 (first published: )

43,136 reads