database Integration
oracle (en)


ServicePilot database-oracle


# Oracle Database

Overview

This package monitors an Oracle Database.

Description

This package automatically configures the ServicePilot Agent in order to monitor an Oracle Database by using SQL requests. This package provides several kind of indicators and ratios.

  • Process indicators: Availability for each critical oracle process. (SMON, PMON, LGWR , DBWR, CKPT, ...)
  • System: Raw data system indicators and Performance Management Ratio as defined by Oracle
  • TableSpace: Size and usage of the tablespaces instance

Requirements

  • Network Flows - It is is necessary to ensure network connectivity between ServicePilot and the monitored device. In case of a network infrastructure using a firewall, all the ports below must be opened:

    • TCP/1521: Between ServicePilot Agent and Oracle Database
    • ServicePilot Manager Web server access (by default TCP/80 when using HTTP or TCP/443 when using HTTPS although this port is configurable): Between ServicePilot Agent and ServicePilot Manager
  • Third Party Configuration

    • Login Account on the oracle database. See the end of the page for instructions on how to create a Login Account

    • For Oracle Data Provider for .NET 4 connections, the following procedure must be performed on the server where the ServicePilot Agent is installed (by default, on the same server as ServicePilot Manager):

      1. Install the Oracle 64-bit ODAC prerequisite if not already present. Microsoft Visual C++ 2010 SP1 Redistributable x64 Package for ODAC 12c Release 4, Microsoft Visual C++ 2005 SP1 Redistributable x64 Package for ODAC 11.2 Release 6
      2. Download an Oracle 64-bit ODAC (Oracle Data Access Components) Xcopy for Windows x64 installation archive appropriate for the version of Oracle database to which you are to connect: ODAC 12c Release 4 (12.1.0.2.4) for Oracle 10g Release 2 or later, ODAC 11.2 Release 6 (11.2.0.4.0) for Oracle 9i Release 2 to 11g Release 2
      3. Create a folder named "C:\oracle"
      4. Extract the contents of the download ZIP in to the folder "C:\oracle\ODAC121024Xcopy_x64"
      5. Open a command prompt using Run as administrator
      6. Execute the following commands from the command prompt:

         cd C:\oracle\ODAC121024Xcopy_x64
        
         install.bat odp.net4 C:\oracle\odac odac true
        
      7. Edit the Environment Variable Path to add the location of the ODAC that was just installed. To do so, follow these steps:

        1. Go to Control Panel. In the Search box (upper right corner), type "Environment Variable". Click on Edit the system environment variables
        2. In the System Properties window, click on Environment Variables
        3. In the Environment Variables window, look for the variable Path in the System Variables section. Select the variable and click on Edit
        4. In the Edit System Variable window, add the following line at the end of the variable value

          ;C:\oracle\odac;C:\oracle\odac\bin
          
        5. Click OK on all the open windows to exit them.

    • For Oracle Provider for OLE DB connections, the following procedure must be performed on the server where the ServicePilot Agent is installed (by default, on the same server as ServicePilot Manager):

      1. Install the Oracle 64-bit ODAC prerequisite if not already present. Microsoft Visual C++ 2010 SP1 Redistributable x64 Package for ODAC 12c Release 4, Microsoft Visual C++ 2005 SP1 Redistributable x64 Package for ODAC 11.2 Release 6
      2. Download an Oracle 64-bit ODAC (Oracle Data Access Components) Xcopy for Windows x64 installation archive appropriate for the version of Oracle database to which you are to connect: ODAC 12c Release 4 (12.1.0.2.4) for Oracle 10g Release 2 or later, ODAC 11.2 Release 6 (11.2.0.4.0) for Oracle 9i Release 2 to 11g Release 2, ODAC 10g Release 2 (10.2.0.3) for Oracle 8i Release 3 to 10g Release 2 (supported on XP, Server 2003)
      3. Create a folder named "C:\oracle"
      4. Extract the contents of the download ZIP in to the folder "C:\oracle\ODAC121024Xcopy_x64"
      5. Open a command prompt using Run as administrator
      6. Execute the following commands from the command prompt:

         cd C:\oracle\ODAC121024Xcopy_x64
        
         install.bat oledb C:\oracle\odac odac true
        
      7. Add a correctly configured tnsnames.ora file in the C:\oracle\odac\network\admin directory. Note that there is a sample file in the C:\oracle\odac\network\admin\sample directory.

      8. Edit the Environment Variable Path to add the location of the ODAC that was just installed. To do so, follow these steps:

        1. Go to Control Panel. In the Search box (upper right corner), type "Environment Variable". Click on Edit the system environment variables
        2. In the System Properties window, click on Environment Variables
        3. In the Environment Variables window, look for the variable Path in the System Variables section. Select the variable and click on Edit
        4. In the Edit System Variable window, add the following line at the end of the variable value

          ;C:\oracle\odac;C:\oracle\odac\bin
          
        5. Click OK on all the open windows to exit them.

  • ServicePilot Requirements

    • ServicePilot Manager minimum version: 8.5
    • ServicePilot Agent minimum version: 8.5 installed and configured

Installation

Before adding a resource to monitor, make certain that all pre-requisites are in place and if a ServicePilot Agent is required, that it is communicating correctly with the ServicePilot Manager. Resources can be added to ServicePilot configuration in a number of ways:

Add resource using Views Configuration web interface

  1. As an administrative user of ServicePilot, open the ServicePilot web interface.
  2. Navigate to Administration. The Configuration > Views web page will open.
  3. Click on the view in which to place the new resource in the Views hierarchy on the left of the interface. The View editor section will show the existing view contents.
  4. From the Packages list on the right of the interface, click and drag the database-oracle package into the View editor and let go.
  5. The Package properties dialog box will open to allow resource configuration.
  6. Click OK to close the Package properties dialog box. Note that the dialog box will not close if required parameters are not set.
  7. Click Save to apply the new resource to ServicePilot configuration.

Add resource by changing servicepilot.conf configuration file

Resources can be added to ServicePilot configuration by directly editing the servicepilot.conf or other included YAML configuration files. The ServicePilot web interface can be used to make these changes and apply them to the running configuration.

  1. As an administrative user of ServicePilot, open the ServicePilot web interface.
  2. Navigate to Administration.
  3. Navigate to Configuration > Edit configuration.
  4. Expand the configuration to find the provisioning: and then packages: section of the view in which the new resource will be placed.
  5. Add the example package configuration line below.
  6. Click on the green - package: word to open then Package properties dialog box to allow resource configuration.
  7. Click OK to close the Package properties dialog box. Note that the dialog box will not close if required parameters are not set.
  8. Click Save to apply the new resource to ServicePilot configuration.

Example:

- package: "database-oracle;;;;;;;;Oracle Database;;60;Y;Y;Y;Y;Y;*;;Oracle Data Provider for .NET 4;;;;ORCL;1;127.0.0.1;1521;;;;"

Key field notes

  1. In the Monitoring Policies tab, specify the policy or policies to apply to the package

  2. Monitoring Options tab:

    1. Polling Interval (sec): Specify the time interval (in seconds) at which ServicePilot will poll the Oracle Database
  3. Tablespaces tab:

    1. Tablespaces Included Filter: Specify the tablespaces to be included in the monitoring, separated by | e.g. "SYSAUX|SYS*|SYS?UX"
    2. Tablespaces Excluded Filter: Specify the tablespaces to exclude from the monitoring, separated by | e.g. "SYSAUX|SYS*|SYS?UX"

      Note: Each element of the Tablespaces Filter list is a regular expression as defined by Like Operator (Visual Basic)

  4. Connection tab:

    1. Database Connection Type: Select either Oracle data Access Components (ODAC) or Microsoft OLEDB Provider for Oracle depending on the type of connection to be used by ServicePilot to connect the Oracle Database
    2. Database User: Specify the username of the Oracle Login Account to be used by ServicePilot
    3. Database Password: Specify the password of the Oracle Login Account to be used by ServicePilot
  5. OLE DB tab:

    1. Data Source (TNS Name Entry or EZCONNECT string): Specify an alias in the tnsnames.ora file or use an EZCONNECT definition in the form host[:port][/service_name]
  6. ODP .NET 4 tab:

    1. Database Service Name or ID Specify the service name or ID of the Oracle Database e.g. XE or ORCL
    2. Number of connections Specify the number (up to 3) connections to the host server(s) of the Oracle Database.
    3. Database Host IP Address/FQDN: Specify the IP address, host name or FQDN for the Oracle Database server, as resolvable by the machine on which the ServicePilot Agent is running
    4. Database Listener Port specify the listener of the Oracle Database on the host server (default: 1521)

Notes

This package uses SELECT statements specifying the following sources for monitored information:

gv$sysstat gv$process dba_tablespaces dba_data_files dba_freespace sys.v$TEMP_SPACE_HEADER sys.dbatablespaces sys.v$Temp_extent_pool dba_temp_files V$ARCHIVE_DEST_STATUS V$ARCHIVE_DEST

Depending on access permissions, the following user should be sufficient to obtain this data:

CREATE USER "SPUSER" IDENTIFIED BY "sppassword" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP"; GRANT "SELECT_CATALOG_ROLE" TO "SPUSER"; ALTER USER SPUSER DEFAULT ROLE "SELECT_CATALOG_ROLE"; GRANT CREATE SESSION TO "SPUSER";

Note: All characters in the username must be capitalized.

database oracle 0

database oracle 1

database oracle 2