Documentation forDatabase Performance Analyzer

Register a single Oracle database instance

You must register all of the database instances that you plan to monitor with DPA. You can register an individual instance using the Register Instance wizard, or you can register multiple instances.

DPA can monitor many database types, including SQL Server, Sybase, Db2, PostgreSQL, MySQL, AWS, and Azure. This example in the DPA Getting Started Guide shows you how to register an Oracle database with a manually created monitoring user. For information about registering other types of database instances, see Register a database instance for monitoring in the DPA Administrator Guide.

Before you start

Before you run the Register Instance wizard, make sure:

  • The database instance is a version that DPA can monitor.

  • You know the password a user with the SYSDBA role (such as SYS). You will connect to the Oracle database as that user to run a script, but you will not enter the password into DPA.

Register the instance

Run a script to create the DPA monitoring user, which is used to collect information for DPA. Then run the wizard to complete registration.

Task 1: Create the monitoring user

  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.
  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.

View performance data for the database instance

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.

When DPA is monitoring an instance, the home page shows summary information about the instance.

For the first two hours after you register the instance, data is shown only on the home page. After two hours, all DPA pages are populated.

Click the instance name to view detailed information about that instance. For more information, see Investigate a SQL statement causing long waits.