44.2 Collecting Data for Database Usage Tracking

Prior to producing the Database Usage Tracking Report, corresponding Metric Collections must be configured and enabled. This includes the following steps:

1. Setting Database Usage Tracking Credentials

2. Enabling or disabling (when the collection is finished) the Metric Collection. Depending on the preferences and available licensing, this can be done:

   • Using Monitoring Templates, from the OEM console, for the database targets which are licensed with Diagnostics Pack.

   • Using EM Command Line Interface (EM CLI), for any database target, regardless of the licensing.

There are two types of metric collections:

• Weekly metrics - to be collected once in 7 days: lms_wk_ci and lms_wk_ci_cdb

• Hourly metrics - to be collected every hour: lms_hr_ci and lms_hr_ci_cdb

  This collection must me enabled only when session information is needed, and should be carefully monitored because of the amount of data that can be generated.

For each of these two types, there are two different metric collections:

• For standard traditional database targets: lms_wk_ci and lms_hr_ci

• For Container Database (CDB) targets: lms_wk_ci_cdb and lms_hr_ci_cdb, which collect data from CDB$ROOT container and also from all the Pluggable Databases (PDBs)

44.2.1 Setting Database Usage Tracking Credentials

1. Log in to Enterprise Manager. From the Setup menu, select Security and then Monitoring Credentials.
2. Choose the desired database target from the list and click Manage Monitoring Credentials. The target Credential page displays.
3. Choose the target name from the list and click Set Credentials. The Enter Monitoring Credentials dialog displays.
4. Enter the requisite monitoring credentials and click Save.

44.2.2 Enabling/Disabling the Metric Collection using Monitoring Templates

This method uses Monitoring Templates, a Diagnostics Pack feature, therefore can be used only on the database targets licensed with Diagnostics Pack.

Note:

The use of monitoring templates for database targets is licensed under the Oracle Diagnostics Pack. You can also use the Enterprise Manager command line interface (EM CLI) to enable/disable metric collections which do not require an extra license.

Enabling the weekly metric collection:

1. From the Enterprise menu, select Monitoring, and then Monitoring Templates.

   

2. Choose Database Instance as target type, check Display Oracle Certified Templates and then click Go.

   

3. Chose Oracle Certified - Enable Database Usage Tracking Weekly Metrics, then click Apply.

   

4. From the new page, click Add. Choose the desired targets using check boxes and then click Select.

   

5. Click OK to finalize the changes.

   

6. Verify the confirmation message and Pending Apply Operations column that shows the number of targets that have not yet been updated. Make sure there are no ("0") pending apply operations.

   

Enabling the hourly metric collection:

1. From the Enterprise menu, select Monitoring, and then Monitoring Templates.

   

2. Choose Database Instance as the target type, check Display Oracle Certified Templates and click Go.

   

3. Choose Oracle Certified - Enable Database Usage Tracking Hourly Metrics, then click Apply.

   

4. Click Add and then choose the desired targets.

   

5. Click OK to finalize the changes.

   

6. A confirmation message displays at the top of the page.

   

Disabling Usage Tracking Metric Collection:

1. Follow the steps 1 and 2 as show in the previous section.
2. In Step 3, choose Oracle Certified - Disable Database Usage Tracking Metrics, which disables both hourly and weekly collections.
3. Follow steps 4 to 6 from the previous section.

44.2.3 Enabling/Disabling the Metric Collection using the Command Line Interface

In the previous section, "Enabling/Disabling the Metric Collection using Monitoring Templates," you performed these actions using the Enterprise Manager Cloud Control console for database targets licensed with Diagnostics Pack. However, you can also use the Enterprise Manager command line interface (EM CLI) to enable/disable metric collection from the operating system command line, in which case no extra licensing is required.

The following topics are covered in this section:

• Setting up EM CLI login

• Enabling/disabling the metric collection

• Using EM CLI to list all the database targets

• Using SQL to verify collection status

44.2.3.1 Setting up EM CLI login

Before running the EM CLI commands to enable/disable metric collection, the EM CLI login must be configured.This is typically done by specifying the URL, username, and password as shown in the following example:

emcli setup -url="https://jupiter.solarsystem.com:7799/em" -username=sysman -password=manager -trustall

44.2.3.2 Enabling/disabling the metric collection

Metric collection is enabled/disabled using the EM CLI modify_collection_schedule verb. This verb is fully documented in the Oracle Enterprise Manager Command Line Interface Guide.

The following syntax must be use for Database Usage Tracking purposes:

emcli modify_collection_schedule
        -targetType="oracle_database"
        -targetNames="tname1;tname2;tname3;..."
        -collectionName="lms_wk_ci_cdb|lms_hr_ci_cdb|lms_wk_ci|lms_hr_ci"
        -freqType="HOUR|DAY|WEEKLY"
        -freqValue="1|7| MON|TUE|WED|THU|FRI|SAT|SUN"
        -collectionStatus="ENABLED|DISABLED"
        -preview="N"

Parameters

• targetNames

  The target name should be the same as exists in the repository. All of the targets should be the same target type you specified in the targetType parameter. Use a semicolon ( ; ) to separate the names. Changes to the collection schedule will be executed for only valid target name and target type combinations. For example:tname1;tname2;tname3

• collectionName

  Name of one of the four metric collections predefined for Database Usage Tracking. "wk" indicates weekly collection while "hr" indicates the hourly collection. "_cdb" suffix indicates that the collection is to be applied only to CDB database targets.

  • lms_hr_ci_cdb and lms_wk_ci_cdb - must be applied to all CDB database targets (with PDBs)

  • lms_hr_ci and lms_wk_ci - must be applied to all the rest of database targets

• freqType and freqValue

  Indicate the frequency. These two parameters are not needed or ignored (if provided) in the case of collectionStatus="DISABLED".

  These parameters can be one of the following:

  • freqType=HOUR freqValue=1

  • freqType= DAYS freqValue=7

  • freqType=WEEKLY freqValue=MON (or any other weekday)

• collectionStatus

  Enables or disables the collection. The default is Enabled. If Disabled, freqType and freqValue are ignored.

Usage Examples

• Enabling weekly metrics on a CDB database target.

  emcli modify_collection_schedule \
          -targetType="oracle_database" \
          -targetNames="targetdb3" \
          -collectionName="lms_wk_ci_cdb" \
          -freqType="DAY" \
          -freqValue="7" \
          -collectionStatus="ENABLED" \
          -preview="N"
  

  Note: On MS Windows, replace "\" with the Windows-specific command line continuation character: "^".

• Enabling weekly metrics on a multiple non-CDB database targets.

  emcli modify_collection_schedule \
          -targetType="oracle_database" \
          -targetNames="targetdb1;targetdb2" \
          -collectionName="lms_wk_ci" \
          -freqType="WEEKLY" \
          -freqValue="SUN" \
          -collectionStatus="ENABLED" \
          -preview="N"
  

• Disabling weekly metrics on a CDB database target.

  emcli modify_collection_schedule \
          -targetType="oracle_database" \
          -targetNames="targetdb3" \
          -collectionName="lms_wk_ci_cdb" \
          -collectionStatus="DISABLED" \
          -preview="N"
  

• Disabling weekly metrics on multiple non-CDB database targets.

  emcli modify_collection_schedule \
          -targetType="oracle_database" \
          -targetNames="targetdb1;targetdb2" \
          -collectionName="lms_wk_ci" \
          -collectionStatus="DISABLED" \
          -preview="N"
  

• Enabling hourly metrics on a CDB database target.

  emcli modify_collection_schedule \
          -targetType="oracle_database" \
          -targetNames="targetdb3" \
          -collectionName="lms_hr_ci_cdb" \
          -freqType="DAY" \
          -freqValue="7" \
          -collectionStatus="ENABLED" \
          -preview="N"
  

• Enabling hourly metrics on a multiple non-CDB database targets.

  emcli modify_collection_schedule \
          -targetType="oracle_database" \
          -targetNames="targetdb1;targetdb2" \
          -collectionName="lms_hr_ci" \
          -freqType="WEEKLY" \
          -freqValue="SUN" \
          -collectionStatus="ENABLED" \
          -preview="N"
  

• Disabling hourly metrics on a CDB database target.

  emcli modify_collection_schedule \
          -targetType="oracle_database" \
          -targetNames="targetdb3" \
          -collectionName="lms_hr_ci_cdb" \
          -collectionStatus="DISABLED" \
          -preview="N"
  

• Disabling hourly metrics on multiple non-CDB database targets.

  emcli modify_collection_schedule \
          -targetType="oracle_database" \
          -targetNames="targetdb1;targetdb2" \
          -collectionName="lms_hr_ci" \
          -collectionStatus="DISABLED" \
          -preview="N"
  

44.2.3.3 Using EM CLI to list all the database targets

During the configuration process, it might be useful to list all the database targets in order to make sure that none are missed.

To list all database targets, run the following EM CLI command:

emcli get_targets -targets="oracle_database"

44.2.3.4 Using SQL to verify collection status

The following SQL query can be run on OEM Repository to list the collection status and schedules assigned to the database targets.

select
       t.TARGET_NAME,
       c.COLL_NAME,
       c.IS_ENABLED,
       c.SCHEDULE_EX
  from      SYSMAN.MGMT_TARGETS          t
  left join SYSMAN.MGMT_COLLECTIONS c on t.TARGET_GUID = c.OBJECT_GUID and c.COLL_NAME like 'lms_%_ci%'
  where t.target_type = 'oracle_database'
  order by t.TARGET_NAME, c.COLL_NAME;

44.2.4 Creating a Database Usage Tracking Report

1. Log in to Enterprise Manager. From the Setup menu, select Security and then Monitoring Credentials.
   
2. Choose the Database Instance target type and click Manage Monitoring Credentials.
   
3. Select Database Usage Tracking Credentials entry in the Credential Set list and click Search.
   

   Click on the row for the desired target and then click Set Credentials.

   Note:

   This operation needs to be performed for all the Database Instances.

4. Enter the username and password for a database user with SYSDBA privilege.
   
5. Alternate method: Use the Enterprise Manager command line utility (EM CLI) to make the above settings as shown in the following examples.

Example 44-1 Multiple Targets

emcli set_monitoring_credential -target_names="testdb1;testdb2" -target_type=oracle_database -set_name=DBCredsLMSMonitoring -cred_type=DBCreds -attributes="DBUserName:<USERNAME>;DBPassword:<PASSWORD>;DBRole:SYSDBA"

Example 44-2 Single Target

emcli set_monitoring_credential -target_name=Oemrep_Database -target_type=oracle_database -set_name=DBCredsLMSMonitoring-cred_type=DBCreds -attributes="DBUserName:<USERNAME>;DBPassword:<PASSWORD>;DBRole:SYSDBA"