Return
-
Title
Configuring User-defined Collections for Oracle database agents -
Description
Cannot create User Defined Collections (UDC) because a user is required/missingHow to proceed in Connection Details in order to enable a user for User Defined Collections?User Defined Collections (UDC) fails to run because the user for UDC is disabled -
Cause
User Defined Collections (UDC) are a very flexible and straight forward method to enable the users to create their own collections plus the advantages of the Db cartridge, such as adding a Baseline to the UDC.
UDC requires a user configuration. This user will SQL to the Monitored Instance and will perform the SQL.
This user can be either a UDC dedicated user or the same DB user that the Agent works with.In any case the UDC user needs to be first configured and initialized by the user.
Below is some more infornation regarding the UDC mechanism & limitations:
- Queries that end with ';' (you will receive an Oracle error saying that this sign is unknown)
- We submit up to 100 rows from the query output (ASP parameter)
- The query must retrieve a result set, meaning tables of rows and column
- Statements which customers creates are being executed on the monitored instance using the default or custom credentials.
-
Resolution
Please refer to KB 262094 for a video on adding user defined collections to database agents.
Use the User-defined Collections view in the Databases Administration dashboard to add user-defined collections to all of the currently selected agents, in order to provide for queries not included in Foglight for Oracle.
After collections are added, this view displays all user-defined collections for all of the agents; for example, if a collection was added to 12 agents during its addition, the view will display 12 rows, showing the collection for each agent.This view is also used for configuring the sampling frequency for each collection.The available sampling frequencies are:- Real-Time - When a user is currently focusing on a screen, the sampling frequency for all of the collections associated with this screen switches to Real-Time.
- Online - When at least one user is connected, the sampling frequency for all of the collections that are not currently running at Real-Time frequency switches to Online.
- Offline - when no user is currently connected to the application
Please refer to KB 232102 for details on opening the Databases Administration Dashboard.
To add user-defined collections:
- Select the requested agents.
- Click Add.
If the user-defined collections are enabled for the selected agent, the Add a User-defined Collection dialog box opens. Proceed to Step 3.
If user-defined collections are not enabled, the User-defined Collections Not Enabled dialog box opens.
IMPORTANT: Agents can also enabled for user-defined collections through the Connection Details view. For details, see Enabling and setting credentials for user-defined collections.
To enable and configure the creation of user-defined collections:- Click Edit credentials.
The Edit Credentials for User-defined Collections dialog box opens. - Select whether to perform the collection using the existing agent credentials or by creating dedicated credentials.
- If the user-defined collections are to be enabled using dedicated credentials, type the requested user name and password.
- Select whether to connect as SYSDBA.
- Click OK.
- The Applying modified settings progress bar is displayed. If the modified settings are su cess fully applied, the Add a User-defined Collection dialog box opens.
- Click Edit credentials.
- Click Set details.
The Set Collection Details dialog box opens. - Enter a name in the Collection name field.
- Enter a brief description of the collection in the Collection description field (optional).
- Paste the query’s SQL text in the Query Text field
* IMPORTANT: This field can hold up to 4096 characters. Any PL-SQL Functions (that are not getting input) can be written by wrapping the PL-SQL “Select From dual” statement. - Type a value, in seconds, in the Query Timeout field.
- Use the Start Time field to define the time when the collection starts running (optional).
* IMPORTANT: If this field is left empty, the collection starts working immediately. The start time can only be defined using the wizard. After the collection starts working, at the time defined using the Start Time field, it will continue to run according to its defined frequency and can only be disabled. - Click Verify.
The collection is verified by running the query on each of the currently selected agents.
After the verification process is complete, the Verification Results pop-up appears, indicating whether the collection was verified successfully. In case the collection verification failed, the relevant error message is displayed.
If the verification succeeded on at least one agent, the collection’s details are displayed on the table described below.
- Except for the Column Name and Column Type fields, whose values are retrieved by the query and cannot be changed, all other fields can be edited by clicking any of them.
The Edit Collection Properties dialog box appears, allowing you to edit the values of the following parameters:- Display name
- Type
- Unit of measurement
- Aggregation
In addition, the collection’s sampling frequencies are displayed on the table at the bottom of the screen, and can be edited by clicking any of them.
- Ensure that all settings are appropriate, and click OK to finish the collection creation process.
The newly created collection now appears on the table.
* IMPORTANT: After adding the requested user-defined collections, they can only be deleted or cloned to other agents. If one or more queries need to be modified, delete them and create new ones.