Monitoring Oracle

Topics: Developer Forum, User Forum
May 28, 2007 at 6:09 AM
I try using SQL Monitor for monitoring my Oracle Database, but the result is always:

Status: Fail
Status Message: ORA-12154: TNS:could not resolve service name

The XML is :

<SQLMonitor>
<ConnectionString>Provider=OraOLEDB.oracle;User ID=myUser;Password=myPassword;Data Source=myDB;</ConnectionString>
<SP>
<Name>TESF</Name>
<Parameters>
</Parameters>
</SP>
</SQLMonitor>

I have instal Oracle Client in my PC and It's running well, no problem with TNSNAME. Why?

Btw, thank's for TCP port update, that work perfectly!
Coordinator
May 29, 2007 at 1:03 PM
I would guess that you need to provide the machine name running your Oracle database in the ConnectionString argument. I can see the provider, User ID, Password and database name, but I do not see a machine name anywhere. I'm not familiar with Oracle but this is most likely your problem.

Glad to hear the TCP monitor is now working correctly!

Thanks,
Fred.
Jul 24, 2007 at 4:25 PM
@dwigung:
did you succeed monitoring Oracle DB with OLEDB provider? What's your running XML definition for the SQL monitor? Thanks
Aug 1, 2007 at 9:26 AM
Edited Aug 1, 2007 at 3:06 PM
The SQL monitor needs a Stored procedure defined on the MS SQL Server.
Try this PowerShell monitor instead (thanks to http://heywood.id.au/blog/):

$connectionString = “Data Source=YOUR SID;User Id=YOURUSER;Password=YOUR_PASSWORD;Integrated Security=no”

System.Reflection.Assembly::LoadWithPartialName(”System.Data.OracleClient”)
$connection = New-Object System.Data.OracleClient.OracleConnection($connectionString)
$queryString = “SELECT COUNT(YOURTABLEFIELD) FROM YOURSCHEMA.YOURTABLE”

$command = new-Object System.Data.OracleClient.OracleCommand($queryString, $connection)
$connection.Open()
$CountYourField = $command.ExecuteScalar()
$connection.Close()

if ($CountYourField -eq $null)
{
$Status.StatusID=3
$Status.StatusText="FAIL: no records found."
}
else
{
$Status.StatusID=1
$Status.StatusText="Connection done: found " + $CountYourField + " records."
}

There is no error check for connection errors, but it works...
You don't need to set system ODBC DSNs, only install the Oracle client and set TNSNAMES resolution.
Aug 13, 2007 at 10:01 AM
I did not suceed monitoring oracle using sql monitor in RC3 because my oracle server ver 7 did not support for OLEDB conection, may be it was not fully installed. Thanks
Oct 13, 2007 at 4:33 AM
You were close. I found the secret (OLEDB.NET=true) buried in the Oracle Ole documentation.

Try this...

Provider=OraOLEDB.Oracle;OLEDB.NET=true;Data Source=TNSNameetc;User Id=User;Password=Password

I have also used the following through PowerShell:


System.Reflection.Assembly::LoadWithPartialName("Oracle.DataAccess")
$connectionString = "Data Source=TNSNameetc;User Id=User;Password=Password;"
$connection = New-Object Oracle.DataAccess.Client.OracleConnection($connectionString)
$queryString = "SELECT COUNT(*) FROM SomeTable"
$command = new-Object Oracle.DataAccess.Client.OracleCommand($queryString, $connection)
$command.CommandTimeout = 600;
$connection.Open()
$CountYourField = $command.ExecuteScalar()
$connection.Close()