8/18/2020 10:04 PM

6/21/2023 4:05 PM

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. If DPA creates or configures the monitoring user, you must give DPA temporary access to the credentials of a user with the DBA role. 
• A utility package is added, and execute permissions for that package are granted to the DPA monitoring user. If DPA creates the utility package, you must give DPA temporary access to the credentials of a user with the SYSDBA role (such as SYS).

To register an Oracle database without entering the credentials of a privileged user, you can create the monitoring user and add the utility package yourself.

• If you have DPA 2021.1 or earlier, use the instructions in this article to register an Oracle database instance without entering the credentials of a privileged user into DPA. You can use mass registration or the DPA management API to register the instance.
  Note: If you have DPA 2020.2.1 and later versions, you can also (optionally) choose to register the instance without creating database objects in the SYS schema.
• If you have DPA 2021.3 and later, you can use mass registration, the API, or a UI wizard to register an Oracle database without a privileged user:
  • To use a wizard, see Register an Oracle database instance in the DPA Administrator Guide.
  • To use mass registration or the API, see the following sections.

Note: You must still know the password of a user with the SYSDBA role (such as SYS) in order to run the provided scripts. But you do not have to enter it into DPA.

Database Performance Analyzer

n/a

How to register an Oracle database instance without entering the SYS password and (optionally) without creating objects in the SYS schema

Step 1: Create the monitoring user with the necessary permissions and the utility package

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

   • CreateMonUserOracleOptimized.sql

     This is the performance-optimized option. You can use this option with all supported versions of DPA. Using this option creates objects under the SYS schema. (See the "Requirements overview" section below for more information about the objects created.)  

   • CreateMonUserOracleReducedPermissions.sql

     You can use this option with DPA 2020.2.1 and later versions. This option does not create any objects under the SYS schema. (See the "Requirements overview" section below for more information about the objects created.) 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.

Step 2: Register the database instance without specifying a privileged user

If you use 2021.1 or earlier, use mass registration or the DPA management API as described below.

If you use DPA 2021.3 and later, you can use mass registration or the API as described below. Or you can use a wizard. To use a wizard, see Register an Oracle database instance in the DPA Administrator Guide.

When you use mass registration or the DPA API to register the database instance, omit the privileged user in the registration details.  

• The following example is a segment of a JSON script that calls the DPA API to register an Oracle database instance without specifying a privileged user. Change the values to reflect your monitored database and the credentials of the monitoring user created in the previous step.

  {
    "databaseType": "ORACLE",
    "serverName": "127.0.0.1",
    "serviceNameOrSID": "SID",
    "port": 1521,
    "monitoringUser": "dpa_mon",
    "monitoringUserPassword": "Pa$$word1",
    "monitoringUserTableSpace": "USERS",
    "monitoringUserTempTableSpace": "TEMP",
    "displayName": "DatabaseName"
  }

• The following shows an example of an Oracle mass registration spreadsheet used to register a database instance without specifying a privileged user. If you use the Oracle mass registration spreadsheet:

  • Leave the DBA Username and DBA Password columns empty.
  • In the Monitoring User and Monitoring User Password columns, enter the credentials of the monitoring user created in the previous step.
  • In the Create Monitoring User(Y/N) column, enter N.
  • Leave the SYS Password column empty.

  

• The following shows an example of a spreadsheet for multiple database types used to register a SQL Server database instance without specifying a privileged user. If you use the spreadsheet for multiple database types:

  • Leave the Privileged User and Privileged User Password columns empty.
  • In the Monitoring User and Monitoring User Password columns, enter the credentials of the monitoring user created in the previous step.
  • In the Create Monitoring User (Y/N) column, enter N.
  • Leave the SYS Password column empty.

  

Monitoring user validation

During the registration, DPA validates that the monitoring user has the required permissions. By default, the registration fails if any mandatory permission is missing and displays a warning if optional permissions are missing. The same validations are also done when database connection details are updated.

This behavior can be overridden by setting the advanced option MONITOR_VALIDATION_OVERRIDE to TRUE. This allows the registration to pass even if the X$KSUSE view is missing. Some mandatory requirements cannot be overridden because they are needed to perform the validations. See the "Requirements overview" section for details.

Requirements overview

The following sections describe the privileges and objects required for DPA monitoring. 

System privileges granted to the DPA monitoring user

These privileges are the same for both monitoring user creation methods.

Privilege	Mandatory	Effect on DPA monitoring if missing
CREATE SESSION	Yes (cannot be overridden)	DPA cannot connect to the instance. Registration fails.
SELECT ANY DICTIONARY	Yes (cannot be overridden)	Registration fails with the message "Instance licensing information could not be retrieved." If this privilege is removed on an already registered instance, polling fails.
CREATE TABLE and UNLIMITED TABLESPACE	No	The 'Oracle Stale Statistics' alert does not work. Live execution plans for queries are not available.
ANALYZE ANY	No	Required for table tuning when the advanced option INDEX_ANALYSIS_FLUSH_STATS (support option) is set to TRUE.

The following privilege is needed only when the performance-optimized creation method is used.

Privilege	Mandatory	Effect on DPA monitoring if missing
	No	Required to recreate the monitoring object during an upgrade. When an instance is registered using the performance-optimized creation method, objects are created in the SYS schema. During an upgrade, these objects might be dropped and will need to be recreated.

Object privileges granted to the DPA monitoring user

These privileges are the same for both monitoring user creation methods.

Privilege	Objects	Mandatory	Effect on DPA monitoring if missing
SELECT	V_$INSTANCE V_$PARAMETER	Yes (cannot be overridden)	Registration fails.
EXECUTE	UTL_CON package (see details below)	No	The Kill Session functionality does not work. Plans for queries with low execution cost might not be available.

The UTL_CON package

This package is required to kill sessions. Without it, the Kill Session option in the Real Time Sessions view does not work. It is also required to turn session tracing on or off to get plans for queries with zero or near-zero cost.

It is created under the SYS schema in both monitoring user creation methods, but it is not mandatory. For more detailed information, see What is UTL CON?

General database objects used for DPA monitoring

The following table describes the general objects used by the DPA monitoring user. How these objects are created (view or synonym in the SYS or monitoring user schema) depends on which method is used to create the monitoring user.

View name	Mandatory	Effect on DPA monitoring if missing
X$KSUSE	Yes (can be overridden)	Wait time for queries is not available.
X$KGLNA1	No	SQL text for queries is not available.
X$KCCCF	No	Control file names are not available.
X$KGLCURSOR	No	Statistics for queries are not available.
X$KCFIO	No	The storage I/O data is not available.
X$DBGALERTEXT	No	The Oracle Alert Log Entries alert does not work.

Database objects when using the reduced-permission method

With the reduced-permission option, the views are created only in the monitoring user schema.

Warning: The X$KCCCF view, which DPA needs to retrieve the names of Oracle control file names, cannot be created using the reduced-permission option. 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)".

View name	Object type	Schema	Used for
X$KSUSE	View	Monitoring user	Wait time
X$KGLNA1	View	Monitoring user	SQL texts
X$KCCCF	View	Monitoring user	Control files
X$KGLCURSOR	View	Monitoring user	SQL statistics
X$KCFIO	View	Monitoring user	Storage I/O
X$DBGALERTEXT	View	Monitoring user	Oracle Alert Log Entries alert

Database objects when using the performance-optimized method

With the performance-optimized option, the views are created in the SYS schema with corresponding synonyms in the monitoring user schema.

View name	Object type	Schema	Used for
X_$KSUSE	View	SYS	Wait time
X_$KGLNA1	View	SYS	SQL texts
X_$KCCCF	View	SYS	Control files
X_$KGLCURSOR	View	SYS	SQL statistics
X_$KCFIO	View	SYS	Storage I/O
X_$DBGALERTEXT	View	SYS	Oracle Alert Log Entries alert
X$KSUSE	Synonym to sys.X_$KSUSE	Monitoring user	Wait time
X$KGLNA1	Synonym to sys.X_$KGLNA1	Monitoring user	SQL texts
X$KCCCF	Synonym to sys.X_$KCCCF	Monitoring user	Control files
X$KGLCURSOR	Synonym to sys.X_$KGLCURSOR	Monitoring user	SQL statistics
X$KCFIO	Synonym to sys.X_$KCFIO	Monitoring user	Storage I/O
X$DBGALERTEXT	Synonym to sys.X_$DBGALERTEXT	Monitoring user	Oracle Alert Log Entries alert

-- Scripts are not supported under any SolarWinds support program or service.
-- 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.