System Center Opalis : Query an Oracle DB

Hi,

For one of my customer, I had to retrieve some information from an Oracle DB with System Center Opalis. The DB Query object included in Opalis supports MS SQL, Oracle, Odbc connection ….

clip_image001

For querying an Oracle DB with Opalis, there is some requirements, that I will explain step by step below :

1. Install the Oracle client on your Opalis server. Opalis requires the x86 release of the Oracle Client even if the Opalis server is in x64.

2. Oracle client is available at the following page : http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html

3. Download this version : Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (32-bit).

win32_11gR2_client.zip (684,581,290 bytes)

4. When downloaded, and uncompressed, just execute the setup.exe file.

clip_image003

5. During the installation process, select Runtime as installation type, and continue the next /next setup.

clip_image005

6. Once the setup finished, we need new to edit the tnsnames.ora configuration file.

7. Go to InstallationFolderproduct11.2.0client_1networkadmin and edit the tnsnames.ora file.

8. ARSDVL is the name of the Oracle DB on which we want to connect, this name is used at two places in the file. line 4  as service name and also in the Connect_Data field. Host contqins is the name of the Oracle DB server.

clip_image006

9. Once the file edited and saved, for testing the connection, open a CMD prompt and type : 
SQLPLUS LOGIN@TNSNAME

clip_image008

10. If the SQL> prompt appears, we now than everything is ok and we could go back to our Opalis policy.

11. Here, a simple Opalis policy which will query the Oracle DB and return the result in an event.
clip_image009

12. Query Database object – Connection configuration : Add the name used in the tnsnames.ora file in the Service Name field, set the connection credentials.

clip_image010

13. Query Database object – Query : Here a simple Select query (Tips : Don’t add the ‘;’ at the end of the query or you will get an error message).

clip_image011

14. Send Platform Event – Configuration :

image

15. Run the policy.

clip_image012

16. Check the result Smile

clip_image013

Christopher KEYAERT

Tweet about this on TwitterShare on FacebookShare on LinkedInShare on Google+Email this to someoneShare on TumblrPin on PinterestDigg thisShare on RedditFlattr the authorBuffer this pageShare on StumbleUpon

About Christopher Keyaert

Christopher Keyaert is a Consultant, focused on helping partners to leverage the System Center and Microsoft Azure cloud platform. He is also a Microsoft Most Valuable Professional (MVP) for Cloud and Data Center Management and a Microsoft Certified Trainer (MCT).
This entry was posted in Uncategorized. Bookmark the permalink.

One Response to System Center Opalis : Query an Oracle DB

  1. Abdul says:

    This is a great blog !!
    After following your steps described above, when i try to execute my runbook i get the below error:

    Failed, Oracle failure Database error has occurred. ORA-12154: TNS:could not resolve the connect identifier specified.

    My TnsName are configured properly. Upon opening SQL Plus, I can connect and also execute queries.

    I am not sure if I need to configure something extra from the orchestrator side.
    Please advise! Any feedback will be appreciated.

    Thank You!

    Regards,
    Abdul

Leave a Reply

Your email address will not be published. Required fields are marked *