Automate Oracle Table Space Report using SQL Server


By:   |   Updated: 2016-06-22   |   Comments   |   Related: More > Other Database Platforms

Problem

We work in an environment with both SQL Server and Oracle where we are responsible for monitoring all of the servers.  Due to limitations we don't have third party tools to monitor the Oracle environment. In such cases we need to find alternatives to monitor the Oracle environment, such as table space growth details in order to be able to expand the databases accordingly. How can we automate the monitoring of the Oracle environment table space using some of our SQL Server tools?

Solution

As a SQL DBA I like to automate anything and everything that I can to minimize manual monitoring as much as possible. In our environment, we have a couple of Oracle servers where we faced table space growth issues and were notified by the users or when the queries failed due to table space issues.  To prevent this issue, this first tip of a multi-part series will demonstrate how to automate a table space report. Please follow the below step by step instructions to setup an automated daily table space report for Oracle using SQL Server tools.

Install Oracle Client

Install the Oracle client on your SQL Server monitoring server instance. You need to install the Oracle client on your monitoring server in order to create an Oracle Linked Server which will be used to collect the data. For instructions on how to install the Oracle client, please refer to this Installing Oracle Database Client.

Create SQL Server Linked Server to Oracle

1. Open SQL Server Management Studio (SSMS) and connect to your SQL Server monitoring instance.

2. Go to Server Object > Linked Server and right click select "New Linked Server...".

3. Enter the name of the server and select server type as "Other data source".

4. In the Provider drop down list select "Oracle Provider for OLEDB" from the drop down list.

5. Enter the Product Name, Data source and Provider string. You can find these details from the tnsname.ora file which is typically on the Oracle server in a directory similar to /home/oracle/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora. The file path may be different on your server.  Here is what it looked like on my server.

New SQL Server Linked Server

6. On the Security tab, enter the credentials you use to connect to the Oracle server. I recommend you create a separate account for this report with only the required Oracle permissions.

Setup Security for the New SQL Server Linked Server

7. Once you are done entering the required details, click the OK button. Once the Oracle Linked Server is created, please test the linked server by right clicking on the Linked Server and select the "Test Connection" option to ensure its working and you are able to connect successfully as shown below.

Test the SQL Server Linked Server

Note: Add other linked servers for other Oracle instances if required.

Scripts for Objects to Store Historical Data

SQL Server Tables

Below are the scripts to create tables on the SQL Server monitoring server to store the Oracle table space information, health details and an archive table to store historical information.

CREATE TABLE [dbo].[TableSpace_Oracle]([INSTANCE] [char](20) NULL,
[TABLESPACE_NAME] [varchar](30) NULL,
[TotalMb] [nvarchar](384) NULL,
[TotalUsed] [nvarchar](384) NULL,
[TotalFree] [nvarchar](384) NULL,
[PctFree] [nvarchar](384) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TableSpace_Oracle_archive]( [INSTANCE] [char](20) NULL, [TABLESPACE_NAME] [varchar](30) NULL, [Total Mb] [nvarchar](384) NULL, [Total Used] [nvarchar](384) NULL, [Total Free] [nvarchar](384) NULL, [Pct Free] [nvarchar](384) NULL, [Logdate] [datetime] NULL ) ON [PRIMARY]
GO CREATE TABLE [dbo].[oracle_health]( [NAME] [varchar](9) NULL, [OPEN_MODE] [varchar](10) NULL, [LOG_MODE] [varchar](12) NULL ) ON [PRIMARY] GO

SQL Server Stored Procedure

Create a stored procedure on the SQL Server monitoring server to capture the Oracle table space information.

CREATE procedure [dbo].[Populate_Oracle_Tablespace_Data]
as
declare @Startdate varchar(25) declare @Enddate varchar(25) declare @date varchar(12) select @date=convert(varchar(10),getdate()-1,121) set @startdate = @date + ' 00:00:00' set @Enddate = @date + ' 23:59:59'
-- Code to backup the old table space data insert into TableSpace_oracle_archive select [INSTANCE] ,[TABLESPACE_NAME] ,[TotalMb] ,[TotalUsed] ,[TotalFree] ,[PctFree] , getdate() FROM [DBA].[dbo].[TableSpace_Oracle]
--Code to cleanup space before populating current data Truncate table TableSpace_Oracle Truncate table oracle_health
-- Populate health for ORAINSTANCE instance insert into oracle_health select * FROM OPENQUERY(ORALinkServer, 'select name, open_mode, log_mode from v$database')
--- Populating Tablespace data for MSS instance insert into TableSpace_Oracle select * FROM OPENQUERY(ORALinkServer, 'Select ''ORA_INST1'', B.Tablespace_Name, B.Total/1048576 "Total Mb", round(Nvl((B.Total-A.Total_Free)/1048576, 100),2) "Total Used", round(Nvl(A.Total_Free/1048576, 0),2) "Total Free", round(Nvl((A.Total_Free/B.Total) * 100, 0),2) "Pct Free" From ( Select Tablespace_Name, Sum(Bytes) Total_Free From Sys.Dba_Free_Space Group By Tablespace_Name ) A , ( Select Tablespace_Name, Sum(Bytes) Total From Sys.Dba_Data_Files Group By Tablespace_Name ) B Where A.Tablespace_Name(+) = B.Tablespace_Name')
--Duplicate the above code if you have multiple instances
GO

The oracle_health table is just used to check status.  This helps with troubleshooting if there is an issue connecting.

I also hardcoded the instance name that I am checking "ORA_INST1".  You would need to update this to the name of the Oracle instance you are checking.

If you want to check additional Oracle instances, you would need to duplicate the code above.  You could also alter this stored procedure to make it more dynamic to accommodate multiple instances.

SQL Server Agent Job to Pull Table Space Information from Oracle

Use SSMS to Create SQL Server Agent Job

1. In SSMS navigate to SQL Server Agent > Jobs then right click on the Jobs folder and select "New Job...".

2. Name the job "Daily Oracle Table Space Report" as shown below:

New SQL Server Agent Job to Capture the Oracle Table Space

3. Go to the Steps page and click the "New..." button on the bottom left of the screen and name the step "Populate Data".

4. In the command box type Exec Populate_Oracle_TableSpace_Data as shown below and click the OK button to save the configuration.

SQL Server Agent Job Step for Data Collection

5. Go to the Schedules tab and create a new schedule to run daily every 8 hours as shown below:

SQL Server Agent Job Schedule

T-SQL Script to Create SQL Server Agent Job

Alternatively, you can run the following script to build the SQL Server Agent Job.

USE [msdb]
GO
BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END
DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Daily Oracle Table Space Report', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'No description available.', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @[email protected], @step_name=N'Populate Data', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'exec Populate_Oracle_Tablespace_Data', @database_name=N'Monitoring', --Your Monitoring Database Name where you have created SP. @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @[email protected], @name=N'Schedule', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=8, @freq_subday_interval=8, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20130906, @active_end_date=99991231, @active_start_time=10000, @active_end_time=235959, @schedule_uid=N'70e2a605-c71b-4786-9d32-30e2d77893f0' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Oracle Table Space Collection from SQL Server

Now that you have an automated table space collection populated every 8 hours you are able to query the data and see results like the following.

Oracle Table Space Data in SQL Server
Next Steps
  • If you have needs to monitor both Oracle and SQL Server, consider some of the ideas from this tip.
  • In the next tip I will demonstrate how we can create an SSRS table space report that is delivered to your inbox every 8 hours.


Last Updated: 2016-06-22


get scripts

next tip button



About the author
MSSQLTips author Atul Gaikwad Atul Gaikwad has over 14+ years of experience with SQL Server. He currently works for one of the leading MNCs in Pune as a Delivery Manager.

View all my tips
Related Resources





Comments For This Article





download


Recommended Reading

SQL Server and PostgreSQL Linked Server Configuration - Part 2

Creating a SQL Server 2014 Linked Server for an Oracle 11g Database

SQL Server and PostgreSQL Foreign Data Wrapper Configuration - Part 3

Transferring Data Between SQL Server 2014 and Oracle 11g Databases

Export Data from IBM DB2 iSeries to SQL Server





get free sql tips
agree to terms


Learn more about SQL Server tools