Documentation forDatabase Performance Analyzer

Register an Oracle database instance

The following sections provide instructions for using a wizard to register a self-managed Oracle database instance for monitoring with DPA.

Alternatively, you can use mass registration to register multiple database instances, or you can register database instances using scripts that call the DPA API.

For an Oracle RAC (Real Application Cluster), SolarWinds recommends registering every physical instance in the cluster. Do not register the virtual IP that distributes load across the RAC instances.

If you choose to register the virtual IP load balancing listener, or to monitor only a subset of instances in the cluster, DPA will not have complete and consistent data. This will affect DPA's tuning and resource analysis.

For more information, see DPA registration and licensing options for clustered environments.

If you register a database instance within the 14-day trial period, DPA begins monitoring the instance immediately. After the trial period, you must activate a license to monitor the database instance.

Options for creating the monitoring user

When you register an Oracle database instance for monitoring, the following changes are made to that instance:

  • The DPA monitoring user is created or configured to enable DPA to monitor the database instance.
  • A utility package is added, and execute permissions for that package are granted to the DPA monitoring user.

For detailed information about the utility package and the privileges granted to the monitoring user, see the Requirements Overview section of this article.

If DPA is not integrated with CyberArk, the following options in the registration wizard determine how the monitoring user and utility package are created or configured.

If DPA is integrated with CyberArk, these options are not displayed. You must create the monitoring user.

  • I will create the monitoring user manually.

    With this option, you connect to the Oracle database as a user with the SYSDBA role (such as SYS) and run a script to create the monitoring user and utility package. Then you use the wizard to complete the registration.

    You must know the password of a user with the SYSDBA role to run the script, but you are not prompted to enter the SYS password or any database user credentials into DPA.

    To use this option, see Register an Oracle database instance and create the monitoring user yourself below.

  • DPA will create/configure the monitoring user.

    With this option:

    • You must provide the credentials of a user with the DBA role so that DPA can create or configure the monitoring user.
    • If you want DPA to add the utility package, you must provide the SYS password. Otherwise, you can connect as a user with the SYSDBA role (such as SYS) and run a script to add the package.

    DPA does not store the user credentials or the SYS password. They are used only during registration and then forgotten.

    To use this option, see Register an Oracle database instance and let DPA create the monitoring user below.

Monitoring user name requirement for Oracle CDBs

If you are registering an Oracle multitenant container database (CDB), the monitoring user name must start with C## (for example, C##username).

If the monitoring user name for a CDB does not start with C##, the registration wizard displays the following message:

User Creation Test Error: ORA-65096: invalid common user or role name

Register an Oracle database instance and create the monitoring user yourself

Task 1: Create the monitoring user and utility package

  1. Copy one of the following scripts to a file:

    • CreateMonUserOracleOptimized.sql

      This is the performance-optimized option. This script creates objects under the SYS schema. (See the Requirements Overview section of this article for more information about the objects created.)

    • CreateMonUserOracleReducedPermissions.sql

      This is the reduced-permission option. This script does not create any objects under the SYS schema. With this option, DPA cannot retrieve the names of Oracle control files. If a wait event is associated with a control file, the Top Files trends chart cannot display the control file name. Instead, the chart displays the placeholder "Control File(s)". All other DPA functionality is available.

  2. Edit the script to update the user name and password values.

    If you are registering a CDB, the monitoring user name must start with C##. Therefore, when you edit the script, replace the name in the first line (create user DPA_MON) with create user C##DPA_MON.

  3. Connect to the Oracle database as a user with the SYSDBA role (such as SYS), and run the script.

Task 2: Complete the registration wizard

  1. In the upper-left corner of the DPA home page, click Register DB Instance for Monitoring.

  2. Under Self-managed, select Oracle.

  3. If the monitoring user prompt is shown, select I will create the monitoring user manually. Then click Next.

  4. Complete the Connection information panel:

    1. Select the connection method, and then complete the fields required for the selected method:

      Connection method Required fields
      Direct connect
      • Enter the host name or IP address of the server that hosts the database instance.
      • Verify or update the port used for the connection. The default port is 1521.
      • Specify the SID (System Identifier) or Service name of the database instance.
      TNS connect descriptor

      In the TNS descriptor box, enter everything after NAME= in the tnsnames.ora file. The beginning (DESCRIPTION= is necessary. For example:

      (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.mycompany.com)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = myserver)))

      TNS name

      In the TNS name box, enter the SERVICE_NAME value from the tnsnames.ora file.

      To use this option, Oracle Name Resolution must be configured. For instructions, see Connect to Oracle using name resolution.
      LDAP

      In the LDAP box, enter the LDAP distinguished name.

      To use this option, Oracle Name Resolution must be configured. For instructions, see Connect to Oracle using name resolution.

    2. Under SSL mode (if SSL mode is enabled), specify the type of secure socket layer (SSL) connections established between the instance and the DPA server.

      SSL connections are not available if LDAP is selected as the Connection method.

      SSL mode Description
      Disable SSL encryption is not used.
      Validate server certificate SSL is enabled. The client verifies that the server is trustworthy by checking the certificate chain up to a trusted certificate authority (CA).
      Validate server certificate and match hostname SSL is enabled. The client verifies the certificate chain and also verifies that the server hostname matches its certificate's Subject Alternative Name or Common Name (CN).

    3. Enter the user name and password of the monitoring user created previously. Or, if DPA is configured to use CyberArk, enter the CyberArk credentials query for the monitoring user.

    4. Click Next.

    5. DPA validates the connection information and the privileges of the monitoring user. If the validation is successful, the Instance options pane opens.

  5. Specify the following Instance options.

    The instance name and group membership can be changed after registration.

    1. If your repository database is Oracle, choose the tablespace in the repository database to store DPA performance data for this monitored instance.

      By default, the performance data is stored in the default tablespace of the repository user. However, data for monitored instances can be stored in separate tablespaces.

    2. If the monitored instance contains the Oracle E-Business Suite, specify whether you want DPA to collect additional information about the suite.

      DPA can capture Oracle E-Business data to identify the screens, modules, and users generating the database requests. This gives you increased visibility into the causes of performance problems in the Oracle E-Business Suite, Oracle Enterprise Resource Planning (ERP), and Oracle Applications environments.

    3. Enter the name that DPA will display to identify this database instance.

      The Display name field defaults to the name retrieved from the database instance.

    4. (Optional) If you have manually created instance groups, you can assign this database instance to one of the groups.

      If no manual groups exist, this option is not shown.

    5. (Optional) If you have existing alert groups, you can assign this database instance to one or more groups.

      If no alert groups exist, or the existing groups do not match this instance's database type, this option is not shown.

    6. Click Next.

  6. Review the information on the Summary page. Click Back if you need to make changes. When the information is correct, click Register.

Register an Oracle database instance and let DPA create the monitoring user

Task 1: Identify the privileged user

When you register a database instance using this option, you must provide the credentials of a privileged user. During registration, the privileged user either creates the monitoring user or grants the required privileges to an existing user that you designate as the monitoring user. DPA does not store the credentials of the privileged user. 

For self-managed Oracle database instances, the privileged user must be assigned the DBA role. It cannot be the repository database user.

Task 2: Complete the registration wizard

  1. In the upper-left corner of the DPA home page, click Register DB Instance for Monitoring.
  2. Under Self-managed, select Oracle.
  3. At the monitoring user prompt, select DPA will create/configure the monitoring user. Then click Next.
  4. Enter the following connection information:

    1. Select the connection method, and then complete the fields required for the selected method:

      Connection methodRequired fields
      Direct connect
      • Enter the host name or IP address of the server that hosts the database instance.
      • Verify or update the port used for the connection. The default port is 1521.
      • Specify the SID (System Identifier) or Service name of the database instance.
      TNS connect descriptor

      In the TNS descriptor box, enter everything after NAME= in the tnsnames.ora file. The beginning (DESCRIPTION= is necessary. For example:

      (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.mycompany.com)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = myserver)))

      LDAP or TNS name

      In the LDAP/TNS name box, enter the LDAP distinguished name or the SERVICE_NAME value from the tnsnames.ora file.

      To use this option, Oracle Name Resolution must be configured. For instructions, see Connect to Oracle using name resolution.

    2. In the DBA Username and DBA Password fields, enter the name and password of the privileged user you identified previously.

    3. Click Next.

      DPA validates the connection information and the privileges of the privileged user. If the validation is successful, the Monitoring User pane opens.

  5. Create or specify the account that DPA will use to gather information (the monitoring user).

    To ensure that the account has the required permissions, SolarWinds recommends creating a new account.

    • To create a new account:

      1. Next to Create Monitoring User, select Yes.

      2. Enter the user name and password. If you are registering a CDB, the monitoring user name must start with C##.

      3. Select a Tablespace and Temp Tablespace on the monitored database. This is primarily used for gathering Explain Plan data for monitored queries.

      4. Click Next.

    • To specify an existing account:

      1. Next to Create Monitoring User, select No.

      2. Enter the user name and password. If you are registering a CDB, the monitoring user name must start with C##.

        DPA uses the default Tablespaces for that user.

        DPA ignores data on the monitored database instance from the monitoring user. Make sure the monitoring user will not cause load on the monitored instance.

      3. Click Next to open the Oracle Monitoring Information pane.

      If you are registering multiple Oracle Real Application Clusters (RAC) nodes, you may receive an error that the user already exists. You can create a different monitoring user or clear the Create a New Monitoring User checkbox and continue.

  6. On the Oracle Monitoring Information pane, complete the following steps:

    1. Click Yes if the monitored instance contains the Oracle E-Business Suite and you want DPA to collect additional information about the suite.

      DPA can capture Oracle E-Business data to identify the screens, modules, and users generating the database requests. This gives you increased visibility into the causes of performance problems in the Oracle E-Business Suite, Oracle Enterprise Resource Planning (ERP), and Oracle Applications environments.

    2. Use one of the following options to install a utility package and grant execute permissions for that package to the DPA monitoring user:

      • Enter the SYS password to allow DPA to install the package and grant permissions. DPA does not store the SYS password.

        The SYS Password field is available only if remote login as SYS is enabled on the monitored Oracle instance.

      • If you do not want to provide the SYS password, or the field is not available, complete the following steps: 

        1. Click the link to open the Manual Steps for Monitored Database Instance Registration panel.
        2. Click Select All, copy the script, and paste it into a text file.
        3. As an Oracle Administrator, log in as SYS to the database instance to be monitored.
        4. Access the text file.
        5. Execute the script.

    3. Click Next.

      If your repository database is Oracle, the Oracle Repository Tablespace panel opens. If not, continue with step 8.

  7. If your repository database is Oracle, choose the tablespace in the repository database to store DPA performance data for this monitored instance. Then click Next.

    By default, the performance data is stored in the default tablespace of the repository user. However, data for monitored instances can be stored in separate tablespaces.

    If your repository database is not Oracle, the wizard skips this step.

  8. (Optional) Select the alert groups you want the new database instance to join. Then click Next.

    • If no alert groups exist, or the existing groups do not match the database type of this instance, the wizard skips this step.

    • Group membership can be changed after registration

  9. Review the information, and click Register Database Instance.

  10. When the registration is complete, click Finish to return to the DPA home page.

The scripts are not supported under any SolarWinds support program or service. The scripts are provided AS IS without warranty of any kind. SolarWinds further disclaims all warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The risk arising out of the use or performance of the scripts and documentation stays with you. In no event shall SolarWinds or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the scripts or documentation.