Skip Headers
Oracle® Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition
11g Release 1 (11.1.1)

Part Number E10541-07
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

8 Diagnosing and Resolving Issues in Oracle Business Intelligence

This chapter describes how to diagnose and resolve issues in Oracle Business Intelligence using tools such as Fusion Middleware Control and log files.

This chapter includes the following sections:

8.1 What Diagnostic Tools Are Available?

Oracle Business Intelligence provides various diagnostic tools to assist you in finding the causes and solutions to issues, as described in Table 8-1.

Table 8-1 Diagnostic Tools

Tool Description Reference

Overview page in Fusion Middleware Control

Enables you to view recent issues with the system.

Section 2.2.3, "Using Fusion Middleware Control to Manage Oracle Business Intelligence System Components"

Performance metrics

Enables you to view metrics that affect performance.

Section 7.1, "Monitoring Service Levels"

Diagnostic pages in Fusion Middleware Control

Enables you to drill into problems and view and configure log files.

Section 8.2.1, "Using Fusion Middleware Control to View Log Information, Error Messages, and Alerts"

Section 8.2.2.1, "Using Fusion Middleware Control to Configure Log File Rotation Policy and Specify Log Levels"

Usage tracking

Enables you to generate usage tracking statistics that can be used in a variety of ways such as database optimization, aggregation strategies, or billing users or departments based on the resources that they consume.

Section 9.1, "About Usage Tracking"

Reports of Catalog objects

Enables you to learn details of objects in the Oracle BI Presentation Catalog.

Section 17.9, "Creating Reports to Display Catalog Data Using Catalog Manager"

Consistency Check Manager

Enables you to check the validity of the repository.

"Checking the Consistency of a Repository or a Business Model" in Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition

Model Check Manager

Enables you to check for modeling problems that might affect Oracle BI Summary Advisor and the aggregate persistence engine.

"Using Model Check Manager to Check for Modeling Problems" in Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition

ODBC/JDBC procedures

Enables you to obtain diagnostic information for the Oracle BI Server.

Section 8.6, "Using ODBC/JDBC Procedures to Obtain Oracle BI Server Diagnostics"


8.2 Viewing and Configuring Diagnostic Log Files

You can view diagnostic log files and configure settings that affect diagnostic log files and the information that they contain, as described in the following sections:

8.2.1 Using Fusion Middleware Control to View Log Information, Error Messages, and Alerts

You can search for and view the log entries for Oracle Business Intelligence components using Fusion Middleware Control Log Viewer. The log files can be searched for log messages, and you can apply filters that can, for example, target a certain date range, user, user transaction, or level of message (error, warning, notification, and so on). You can also view log files in their entirety from the Fusion Middleware Control Log Viewer.

When log entries for error messages and warnings are generated across multiple log files, they can be difficult to trace. However, it is possible to view logging information for specific user transactions across multiple log files. Transaction level logging associates a unique transaction ID, which is called the Execution Context ID (ECID), with every log and error message that is generated in response to a user request. This logging enables rapid diagnosis of the cause of underlying issues. However, some messages in the log (for example system messages for server startup or shutdown) do not have a transactional attribute. All log messages that are related to client requests do have a transactional attribute.

Before you begin this procedure, ensure that you are familiar with the information in Section 3.2, "Using Fusion Middleware Control to Update Oracle Business Intelligence Configuration Settings."

To use Fusion Middleware Control to view log information, error messages, and alerts:

  1. Go to the Business Intelligence Overview page, as described in Section 2.2.3, "Using Fusion Middleware Control to Manage Oracle Business Intelligence System Components."

  2. Display the Log Messages tab of the Diagnostics page.

    Click the Help button on the page to access the page-level help for its elements.

  3. View lists of the following:

    • Recent errors under the Most Recent Errors region

    • recent warnings under the Most Recent Warnings region

  4. Select a link under View/Search All Log Files and View/Search Log Files By Component to display messages for all log files, or for the messages for the log files of a specified component. Click the Help button on the page to access the page-level help for the following links:

    • Search the log files using the Log Viewer

    • Presentation Services Log

    • Server Log

    • Scheduler Log

    • JavaHost Log

    • Cluster Controller Log

    • Action Services Log

    • Security Services Log

    • Administrator Services Log

    Fusion Middleware Control displays messages in the Log Messages page that correspond to your selection.

  5. Enter appropriate search criteria to display corresponding error messages.

    To view messages by ECID, click View Related Messages and select the by ECID (Execution Context ID) menu option.

  6. Select one or more rows to view the log file entry details for the selected messages.

    For more information about the elements that are displayed in the Log Viewer, see the Fusion Middleware help.

8.2.2 Configuring Log File Rotation Policy and Specifying Log Levels

You can configure criteria that determine when a new log file must be created, based on the size of the log file and the age of the log file. You can also specify log levels to determine what level of message the log files contain.

This section contains the following topics:

For information about using methods in the Oracle BI Systems Management API to change configuration settings, see Chapter 23, "Introducing the Oracle BI Systems Management API."

8.2.2.1 Using Fusion Middleware Control to Configure Log File Rotation Policy and Specify Log Levels

Before you begin this procedure, ensure that you are familiar with the information in Section 3.2, "Using Fusion Middleware Control to Update Oracle Business Intelligence Configuration Settings."

To use Fusion Middleware Control to configure log file rotation policy and specify log levels:

  1. Go to the Business Intelligence Overview page, as described in Section 2.2.3, "Using Fusion Middleware Control to Manage Oracle Business Intelligence System Components."

  2. Display the Log Configuration tab of the Diagnostics page.

  3. Click Lock and Edit Configuration to enable changes to be made.

  4. Complete the elements using the descriptions in the help topic for the page.

    For example, you can specify which log levels to use, and for some you can set their granularity.

    Click the Help button on the page to access the page-level help for the following options:

    Log Configuration

    • Maximum File Size option

    • Maximum Log Age option

    Query Logs

    • Maximum File Size option

    • Maximum Log Age option

    Default Log Level

    Component Specific Log Levels

  5. Click Apply, then click Activate Changes.

  6. Return to the Business Intelligence Overview page and click Restart.

8.2.2.2 Manually Changing Additional Log File Settings

In addition to the log file settings that you can change in Fusion Middleware Control, you can change other settings manually. Use various elements in the log configuration file for a component to change these settings.

Note:

Editing a diagnostic log configuration file for a single component is not advised, because changes might subsequently be overwritten. For information, see Section 3.4, "Using a Text Editor to Update Configuration Settings."

Before you begin this procedure, ensure that you are familiar with the information in Section 3.4, "Using a Text Editor to Update Configuration Settings."

To manually change the settings that configure the log file format:

  1. Open the component log configuration file as described in Section 8.3.2, "What Are Diagnostic Log Configuration Files and Where Are They Located?"

  2. Locate the section in which you must add the Format element, which specifies the log file format. The default is ODL-TEXT.

    To use the Fusion Middleware Control Log Viewer to view and search through the log files for Oracle Business Intelligence, then the files must be in either ODL-Text or ODL-XML format.

  3. Include the element and its ancestor elements as appropriate, as shown in the following example:

    <server>
       <ServerInstance>
          <Log>
             <Format>ODL-TEXT</Format>
          </Log>
       </ServerInstance>
    </server>
    

    For an example of a JavaHost Server diagnostic log configuration file, see Example 8-2.

  4. Save your changes and close the file.

  5. Restart Oracle Business Intelligence.

8.2.3 Diagnosing Issues Using the Log Viewer

You can use the Log Viewer in Fusion Middleware Control to find messages that can assist you in resolving issues with the Oracle Business Intelligence system.

To diagnose issues using the Log Viewer:

  1. Display Fusion Middleware Control.

  2. Right-click the bifoundation domain and select Logs, then View Log Messages.

    The Log Messages page is displayed. The Log Viewer collects lines from all log files and displays them on this page. You can filter the lines to view the ones in which you are interested.

  3. To start filtering the list, enter search criteria to locate the messages in which you are interested:

    • If you know that an error occurred around a certain date, then set the Date Range to Time Interval. Select the start and end dates for filtering.

    • If the error happens continually, then set the Date Range to Most Recent. Select Days and specify a number such as 1 or 3.

    • For Message Types, select the following: Incident Error, Error, Warning, and Notification. If the number of messages that is returned is too large, then deselect Notification to see only errors and warnings.

      The advantage of selecting Notification is that you can see what the Oracle Business Intelligence system was doing, which can assist you in determining where something went wrong.

  4. To filter for the messages for Oracle Business Intelligence

    1. Click Add Fields, then select Module, and click Add.

    2. Ensure that Module is set to contains, then enter the following value:

      oracle.bi.management

      That value specifies the name of the Java package from which all log entries for systems management for Oracle Business Intelligence originate.

  5. Click Search.

    The page lists all log messages that meet the criteria, including the errors and warnings that lead up to the problem that you are diagnosing.

  6. To save a copy of the log messages, click Export Messages to File, then As Oracle Diagnostic Log Text (.txt) or other format appropriate to your needs.

As you view the log messages, you can see that the Message column explains what operations happened at what times. You can learn important information such as when servers were restarted or a configuration change occurred. You can use the values in the Log File column to learn which files were written to, which gives a clue as to what Oracle Business Intelligence was doing. For example, a value of nqserver.log indicates an interaction with the Oracle BI Server and a value of sawlog5.log indicates an interaction with Presentation Services.

You can view the log messages to see what might have contributed to a particular situation. For example, suppose that you make changes in Fusion Middleware Control to specify a different repository, but you cannot see the repository in Presentation Services. When you view the log messages, you find an error message that indicates that the computer that hosts the Managed Server and to which the new repository was copied has run out of memory. An earlier error message indicates that the Administration Server had reported the change to the repository and had tried to synchronize the change to the Managed Server.

8.3 Understanding Diagnostic Log and Log Configuration Files

This section discusses diagnostic log files and diagnostic log configuration files, and contains the following topics:

8.3.1 What Are Diagnostic Log Files and Where Are They Located?

Diagnostic log files are files used to store message information that is generated by Oracle Business Intelligence servers. These log files are stored in the following location:

ORACLE_INSTANCE\diagnostics\logs\component_type\coreapplication

The following diagnostic log files are used in Oracle Business Intelligence:

  • Presentation Services

    • \CatalogCrawler\sawcatalogcrawlerlogsysn.log — The catalog crawler log file, which is not searchable in the Fusion Middleware Control Log Viewer.

    • sawlogn.log — The Presentation Services log file that represents the latest part of diagnostic log output and is searchable in the Fusion Middleware Control Log Viewer.

    For more information specifically about Presentation Services logging, see Section 8.5, "Logging in Oracle BI Presentation Services."

  • Oracle BI Server

    • nqquery<n>.log — The Oracle BI Server query log, which is not searchable in the Fusion Middleware Control Log Viewer.

      <n> = date and timestamp, for example nqquery-20101209-2135.log

    • nqserver<n>.log — The Oracle BI Server main diagnostic log, which is searchable in the Fusion Middleware Control Log Viewer.

      <n> = date and timestamp, for example nqserver-20101209-2135.log

    • nqsadmintool.log — The log for the Oracle BI Administration Tool.

    • Oracle BI Server utilities — For example, biserverxmlexec and equalizerpds, also generate their own logs when they are run.

  • JavaHost

    • jh-n.log — The JavaHost Server main diagnostic log, which is searchable in the Fusion Middleware Control Log Viewer.

      <n> = date and timestamp, for example jh-20100909-2135.log

  • Oracle BI Scheduler

    • nqscheduler-<n>.log — The Oracle BI Scheduler log file, which is searchable in the Fusion Middleware Control Log Viewer.

      <n> = date and timestamp, for example nqscheduler-20100909-2135.log

  • Cluster Controller

    • nqcluster-yyyyMMdd-hhmm.log — The Oracle BI Cluster Controller diagnostic file, which is searchable in the Fusion Middleware Control Log Viewer.

      <n> = date and timestamp, for example nqcluster-20100909-2135.log

  • BI JEE log (Action Services and Security Services), both of the following log files are searchable in the Fusion Middleware Control Log Viewer:

    • AdminServer-diagnostic.log

    • bi_server1-diagnostic.log

  • Upgrade

    Log files for the upgrade of Oracle Business Intelligence are created in the following location:

    ORACLE_HOME\upgrade\logs

    For information about upgrade log files, see Oracle Fusion Middleware Upgrade Guide for Oracle Business Intelligence. These files are not searchable in the Fusion Middleware Control Log Viewer.

Note:

For the following log files, you cannot set the time zone in which messages are logged in the files: nqcluster-yyyyMMdd-hhmm.log, nqscheduler-<n>.log, and nqserver<n>.log. The messages are logged in the files in Greenwich Mean Time (GMT). When you view the messages in the Fusion Middleware Control Log Viewer, you see the messages in the local time zone.

8.3.2 What Are Diagnostic Log Configuration Files and Where Are They Located?

Diagnostic log configuration files control output to diagnostic log files for Oracle Business Intelligence.

Note:

Editing a diagnostic log configuration file for a single component is not advised, because changes might subsequently be overwritten. For information, see Section 3.4, "Using a Text Editor to Update Configuration Settings."

Log configuration files for Oracle Business Intelligence are stored in the following locations:

ORACLE_INSTANCE\config\component_type\bi_component_name

For example:

  • \OPMN\opmn\opmn.xml

  • \OracleBIClusterControllerComponent\coreapplication_obiccs1\ccslogconfig.xml

  • \OracleBIJavaHostComponent\coreapplication_obijh1\logging_config.xml

  • \OracleBIPresentationServicesComponent\coreapplication_obips1\instanceconfig.xml

  • \OracleBISchedulerComponent\coreapplication_obisch1\instanceconfig.xml

  • \OracleBIServerComponent\coreapplication_obis1\logconfig.xml

About Formats in Diagnostic Log Configuration Files

Diagnostic log configuration files conform to the Oracle Diagnostic Log (ODL) standard, although they can differ slightly in appearance.

Example 8-1 and Example 8-2 illustrate two of the log configuration files for Oracle Business Intelligence.

Example 8-1 BI Server Diagnostic Log Configuration File Format

<server>
   <ServerInstance>
      <Log>
         <MaximumFileSizeKb>10000</MaximumFileSizeKb>
         <MaximumLogAgeDay>60</MaximumLogAgeDay>
         <Format>ODL-TEXT</Format>
            <Level>
               <IncidentError>1</IncidentError>
               <Error>1</Error>
               <Warning>16</Warning>
               <Notification>1</Notification>
               <Trace>16</Trace>
            </Level>
      </Log>
      <UserLog>
         <MaximumFileSizeKb>10000</MaximumFileSizeKb>
         <MaximumLogAgeDay>10</MaximumLogAgeDay>
         <Format>ODL-TEXT</Format>
      </UserLog>
   </ServerInstance>
</server>

Example 8-2 JavaHost Server Diagnostic Log Configuration File Format

<?xml version = '1.0' encoding = 'utf-8'?>
<logging_configuration>
   <log_handlers>
      <log_handler name='odl-handler' class='oracle.core.ojdl.logging.ODLHandlerFactory'>
      <property name='path' value='C:\oracle_bi_ee_BIFNDNPTPSNT0911060426S-Release\jhlogs\javahost.log'/>
      <property name='maxFileSize' value='1000000'/>
      <property name='maxLogSize' value='5000000'/>
      </log_handler>
   </log_handlers>
   <loggers>
      <logger name='saw' level='NOTIFICATION:1' useParentHandlers='false'>         <handler name='odl-handler'/>
      </logger>
   </loggers>
</logging_configuration>

Oracle Business Intelligence components control their diagnostic log files by using server-specific settings in their log configuration files, for example:

  • Oracle BI Presentation Services log configuration file:

    - writerClassId settings configure messages that the system writes to the sawlog.log file.

  • Oracle BI Server log configuration file:

    - Log settings configure messages that the system writes to the nqserver.log file.

    For more information, see Section 8.3.5, "What Messages Are Included in the System Log?"

    - UserLog settings configure messages that the system writes to the nqquery.log file.

    For more information, see Section 8.4, "Managing the Query Log."

  • Oracle BI Scheduler log configuration file:

    - Log settings configure messages that the system writes to the nqscheduler.log file.

  • JavaHost Server log configuration file:

    - log_handlers elements and subelements enable configuration of the log file rotation policy and the specification of the log file name and its location.

    - loggers elements and subelements enable appropriate handling of Java component (JavaHost Server) log levels, by mapping the JavaHost Server log levels to the standard Oracle Diagnostic Log (ODL) log levels.

8.3.3 What Are Log File Message Categories and Levels?

Categories and levels for log file messages define the detail and level of importance with which the system writes messages to a log file. Fusion Middleware Control enables you to control these settings in the logconfig.xml file.

Each message category in a log file for Oracle Business Intelligence is set to a specific default value between 1 and 32, and only messages with a level less than or equal to the log level is logged.

Log file message categories are described in Table 8-2.

Table 8-2 Log File Message Category Levels

Category:Level Description

IncidentError:1

A serious problem caused by unknown reasons has occurred. You can fix the problem only by contacting Oracle Support Services.

No performance impact.

Error:1

A problem that requires attention from the system administrator has occurred.

No performance impact.

Warning:1

An action occurred or a condition was discovered that must be reviewed and might require action before an error occurs.

No performance impact.

Notification:1

A report of a normal action or event has occurred. This could be a user operation, such as "login completed" or an automatic operation such as a log file rotation.

No performance impact.

Notification:16

A configuration-related message or problem has occurred.

Low performance impact. You can enable this level broadly in a production environment without having a significant performance impact in the software.

Trace:1

A trace or debug message that is used for debugging or performance monitoring has been written. Typically this message contains detailed event data that is clear enough to be understood by someone who does not know internal implementation details.

Small performance impact. This level might be enabled broadly occasionally on a production environment to debug issues with the software. Enabling logging at this level might have a small performance impact, but not to the point of making the software unusable.

Trace:16

A fairly detailed trace or debug message has been written. The message is clear enough to be understood by Oracle Support Services engineers who have a deep knowledge of the product but might not know full details of the internal implementation.

High performance impact. This level must not be enabled on a production environment, except on special situations to debug issues with the software.

Trace:32

A highly detailed trace or debug message has been written. The message is intended for an Oracle developer working on the software who knows enough details about the implementation of the subsystem that generates the message.

Very high performance impact. This level is not expected to be enabled in a production environment and developers use it only to debug the software on a test or development environment.


In the following log configuration file example, in the Notification message category, only level 1 messages are logged. If the log level is set to 0, then nothing is logged for that message category.

<Level>
      <IncidentError>1</IncidentError>
      <Error>1</Error>
      <Warning>1</Warning>
      <Notification>1</Notification>
      <Trace>1</Trace>
    </Level>

Avoid manually changing the default settings in the log file. Use Fusion Middleware Control to make changes. For more information, see Section 8.2.2.1, "Using Fusion Middleware Control to Configure Log File Rotation Policy and Specify Log Levels."

8.3.4 What is Log File Rotation?

Log file rotation is the creation of new log files, when the file exceeds a specified threshold or date. Take the MaximumFileSizeKb setting for the component log configuration file for the Oracle BI Scheduler as an example. Whenever a log file exceeds the size that is specified by this setting, then the existing Scheduler log file is renamed, and a new log file is created. Additionally, a log file date that is older than the MaximumLogAgeDay setting is deleted.

Different Oracle BI components have different log file names, and different settings within their log configuration files. For example, the file naming convention for the Scheduler is as follows:

  • nqscheduler.log — The latest log file.

  • nqscheduler-<n>.log — The renamed previous log file.

    where <n> = date and timestamp, for example nqscheduler-20100909-2135.log

For more information, see Section 8.2.2.1, "Using Fusion Middleware Control to Configure Log File Rotation Policy and Specify Log Levels."

8.3.5 What Messages Are Included in the System Log?

The Oracle BI Server writes messages to the nqserver.log file, based on configuration settings. In addition to writing messages to this log file, the BI Server writes certain severe messages to the system log file for UNIX systems. The following list includes the kinds of messages that the BI Server writes to the system log file:

  • When the BI Server cannot start (for example, because another server has previously started), then the system log file includes a message such as the following one:

    Another server is already running on : @1%ls and port: @2%ls.

  • When memory problems occur, the system log file includes a message such as the following one:

    Could not enable the Low-Fragmentation Heap.

  • When the hard disk on the computer is full, the system log file includes a message such as the following one:

    Out of disk space.

8.4 Managing the Query Log

The Oracle BI Server provides a facility for logging query activity at the individual user level. Use logging for quality assurance testing, debugging, and troubleshooting by Oracle Support Services. In production mode, query logging is typically disabled.

The query log file is named nqquery.log, and is located in:

ORACLE_INSTANCE\diagnostics\logs\component_type\bi_component_name

Oracle BI Server query logging is tracked at a user level. It is a resource-intensive process if you track the entire user community.

Note:

For production systems, it is recommended that query logging be enabled only for a very targeted user community. In production systems, you can use usage tracking as the production-level logging facility. See Chapter 9, "Managing Usage Tracking" for more information.

It is recommended that you test users only when the user name clearly indicates it is a test user and have verified that query logging is enabled. If logging is enabled for such users, then it is recommended that they be given names such as sales_admin_with_logging, sales_dev_with_logging, or sales_test_with_logging, so that you can readily identify them. Even production administrator logins should not have query logging enabled, because it strains the available resources.

Also disable query logging for the following:

This section contains the following topics:

8.4.1 Configuring Query Logging

This section includes information about setting the size of the query log, choosing a logging level, and enabling query logging for a user.

Because query logging can produce very large log files, the logging system is turned off by default. You can enable logging to test that the repository is configured properly, to monitor activity on the system, to help solve performance problems, or to assist Oracle Support Services. You must enable query logging on the system for each user whose queries you want logged. You do this using the Oracle BI Administration Tool.

8.4.1.1 Setting the Query Logging Level

You can enable query logging levels for individual users, as described in Section 8.4.1.2, "Setting the Query Logging Level for a User." You cannot configure a logging level for a group.

A session variable overrides the logging level for a particular user. For example, if the administrator has a logging level of 4 and the session variable logging level is defined as the default 0 (zero) in the repository, then the logging level for the administrator is 0.

Set the logging level based on the amount of logging that is appropriate for your organization. In normal operations, logging is generally disabled (that is, the logging level is set to 0). If you decide to enable logging, then select a logging level of 1 or 2. These two levels are designed for use by administrators.

You might want to diagnose performance or data issues by setting a temporary log level for a query. You can enable query logging for a select statement by adding a prefix clause in the Advanced SQL Clauses section of the Advanced tab in Oracle BI Presentation Services. For example, for the select statement:

SELECT year, product, sum(revenue) FROM time, products, facts; 

You can specify the logging level of 5 in the Prefix field as follows:

Set Variable LOGLEVEL=5;

For this query, the logging level of 5 is used regardless of the value of the underlying LOGLEVEL variable.

Note:

Use logging levels greater than 2 only with the assistance of Oracle Support Services.

The query logging levels are described in Table 8-3.

Table 8-3 Query Logging Levels

Logging Level Information That Is Logged

Level 0

No logging.

Level 1

Logs the SQL statement issued from the client application. Also logs the following:

  • Physical query response time — The time for a query to be processed in the back-end database.

  • Number of physical queries — The number of queries that are processed by the back-end database.

  • Cumulative time — The sum of time for all physical queries for a request (that is, the sum of all back-end database processing times and DB-connect times).

  • DB-Connect time — The time taken to connect to the back-end database.

  • Query cache processing — The time taken to process the logical query from the cache.

  • Elapsed time — The time that has elapsed from when the logical query is presented to the BI Server until the result is returned to the user. Elapsed time can never be less than response time, because elapsed time takes into account the small extra time between the logical query being presented to the BI Server to the start of preparation of the query. In cases where this difference in time is negligible, the elapsed time equals the response time.

  • Response time — The time taken for the logical query to prepare, execute, and fetch the last record. This matches the TOTAL_TIME_SEC that is logged in usage tracking, as described in Section 9.3, "Description of the Usage Tracking Data."

  • Compilation time — The time taken to compile the logical query.

  • For each query, logs the query status (success, failure, termination, or timeout), and the user ID, session ID, and request ID.

Level 2

Logs everything logged in Level 1.

Additionally, for each query, logs the repository name, business model name, subject area name, SQL statement issued against the physical database, queries issued against the cache, number of rows returned from each query against a physical database and from queries issued against the cache, and the number of rows returned to the client application.

Level 3

Logs everything logged in Level 2.

Additionally, adds a log entry for the logical query plan, when a query that was supposed to seed the cache was not inserted into the cache, when existing cache entries are purged to make room for the current query, and when the attempt to update the exact match hit detector fails.

Do not select this level without the assistance of Oracle Support Services.

Level 4

Logs everything logged in Level 3.

Additionally, logs the query execution plan. Do not select this level without the assistance of Oracle Support Services.

Level 5

Logs everything logged in Level 4.

Additionally, logs intermediate row counts at various points in the execution plan. Do not select this level without the assistance of Oracle Support Services.

Level 6 and 7

Not used.


8.4.1.2 Setting the Query Logging Level for a User

To set the query logging level for a user:

  1. In the Oracle BI Administration Tool, select Manage, then Identity.

    The Identity Manager dialog is displayed.

  2. Double-click the name of the user for which you want to set the query logging level.

    The User dialog is displayed.

  3. Set the logging level by clicking the Up or Down arrows next to the Logging Level field.

    To disable query logging for a user, set the logging level to 0.

  4. Click OK.

8.4.2 Using the Log Viewer

Use the Oracle Business Intelligence Log Viewer utility (or a text editor) to view the query log. Each entry in the query log is tagged with the name of the user who issued the query, the session ID of the session in which the query was initiated, and the request ID of the individual query.

8.4.2.1 Running the Log Viewer Utility

To run the Log Viewer utility (which is located on Windows in \MW_HOME\ORACLE_HOME\bifoundation\server\bin\nqlogviewer.exe), open a command prompt, and enter nqlogviewer with any combination of its arguments. The syntax is as follows:

nqlogviewer [-u user_name] [-f log_input_filename]
          [-o output_result_filename]
          [-s session_ID] [-r request_ID]

In this syntax:

  • user_name is the name of a user in the Oracle Business Intelligence repository. This parameter limits the scope to entries for a particular user. If not specified, all users for whom query logging is enabled are displayed.

  • log_input_filename is the name of an existing log file from where the content is taken. This parameter is required.

  • output_result_filename is the name of a file in which to store the output of the log. If the file exists, then the results are appended to the file. If the file does not exist, then a new file is created. If this argument is not specified, then output is sent to the monitor screen.

  • session_ID is the session ID of the user session. The BI Server assigns each session a unique ID when the session is initiated. This parameter limits the scope of the log entries to the specified session ID. If not specified, then all session IDs are displayed.

  • request_ID is the request ID of an individual query. The BI Server assigns each query a unique ID when the query is initiated. This parameter limits the scope of the log entries to the specified request ID. If not specified, then all request IDs are displayed.

    The request ID is unique among the active requests, but not necessarily unique during the session. Request IDs are generated in a circular manner, and if a request is closed or if the session is long enough, then a request ID is reused.

You can also locate user names, session IDs, and request IDs through the Session Manager. See Oracle Fusion Middleware Security Guide for Oracle Business Intelligence Enterprise Edition for information.

Administrators can view the query log using the Manage Sessions option in the Presentation Services Administration page.

8.4.2.2 Interpreting the Log Records

After you have logged some query information and started the log viewer, you can analyze the log. Log entries for levels 1 and 2 are generally self-explanatory. The log entries can provide insights to help database administrators (DBAs) in charge of the underlying databases tune them for optimum query performance. The query log can also help you check the accuracy of applications that use the BI Server.

The log is divided into the following sections:

  • SQL Request — This section lists the SQL statement that is issued from the client application. You can use this information to rerun the query from the same application, or from a different application.

  • General Query Information — This section lists the repository, the business model, and the subject area from which the query was run. You can use this information to provide statistics on query usage that you can use to set priorities for future application development and system management.

  • Database Query — This section begins with an entry that reads "Sending query to the database named <data_source_name>," where <data_source_name> is the name of the data source to which the BI Server is connecting. Multiple database queries can be sent to one or more data sources. Each query has an entry in the log.

    The database query section has several uses, such as recording the SQL statement that was sent to the underlying databases. You can use this logged SQL statement to run queries directly against the database for performance tuning, results verification, or other testing purposes. You can also use this information to examine the tables that are being queried to verify that aggregate navigation is working as you expect. If you understand the structure of the underlying database, then it might also provide some insights into potential performance improvements, such as useful aggregate tables or indexes to build.

  • Query Status — The query success entry in the log indicates whether the query completed successfully, or failed. You can search through the log for failed queries to determine why they failed. For example, all the queries during a particular time period might have failed due to database downtime.

8.5 Logging in Oracle BI Presentation Services

This section describes logging specifically in Presentation Services and contains the following topics:

For general information about logging in Oracle Business Intelligence, see Section 8.3, "Understanding Diagnostic Log and Log Configuration Files."

8.5.1 Using the Oracle BI Presentation Services Logging Facility

By default, Oracle BI Presentation Services is configured to log all error events and informational and warning events of sufficient importance. An example of an important informational event is a server starting up or a server shutting down. Log files are named sawlogxx.log, where the xx is replaced by an incremented number.

To debug specific issues that a user might be encountering, the logging level can be increased to log more information than the default configuration. For example, while debugging a particular Oracle BI Presentation Services connectivity issue, you can increase the maximum logging on the saw.odbc log source only. This adds detailed logging for that component, without cluttering the log with detailed logging from other events. All Oracle BI Presentation Services configuration information is loaded from the instanceconfig.xml file.

Caution:

Because logging affects performance, do not increase the logging on a production implementation, except to diagnose specific issues.

8.5.2 Setting the Logging Levels for Oracle BI Presentation Services

Note:

The ability to set the logging levels for Oracle BI Presentation Services applies to Oracle BI EE 11.1.1.7.10 and later versions, and might not be available in earlier versions. For more information about Oracle BI EE 11.1.1.7.10, see"New Features for 11.1.1.7.10."

You use options on the Administration page in Presentation Services to affect logging levels.

To set logging levels for Presentation Services:

  1. In the global header, click Administration.

  2. In the Maintenance and Troubleshooting area, select the logging level to use under Reload Log Configuration.

  3. Click Reload Log Configuration to allow the change to take effect without restarting Presentation Services.

    The change remains in effect even when you stop and restart Presentation Services.

  4. Click the Manage Sessions link to display the Manage Sessions page.

  5. For each session, specify the appropriate level in the Log Level column of the table.

    The updated level takes effect immediately for that session. When you select a level, ensure that its severity value is smaller than or equal to the value specified for all messages in Presentation Services.

8.5.3 Structure for the Oracle BI Presentation Services Configuration File

The structure of the configuration file is shown in Example 8-3. The cardinality of each node is shown in brackets.

Example 8-3 Structure of Log Section in instanceconfig.xml File

Logging [1..1]
Writers [0..1]
Writer [0..1]
WriterClassGroups [0..1]
Filters [0..1]
FilterRecord [0..n]

An example of an instanceconfig.xml file that has four writers is shown in Example 8-4.

Example 8-4 instanceconfig.xml File with Four Writers

<?xml version="1.0" ?>
<Server>
. . . . . . .
<Logging>
<Writers>
<Writer implementation="FileLogWriter" name="Global File Logger"
writerClassId="1" dir="{%ORACLE_BIPS_INSTANCE_LOGDIR%}" filePrefix="sawlog"
maxFileSizeKb="10000" filesN="10" fmtName="ODL-Text" ODLLogFilePath="{%ORACLE_BIPS_INSTANCE_LOGDIR%}/diagnostic.log"/>
<Writer implementation="CoutWriter" name="Global Output Logger"
writerClassId="2" />
<Writer implementation="EventLogWriter" name="Event Logger"
writerClassId="3" />
<Writer implementation="CrashWriter" name="CrashWriter"
writerClassId="4"
/>
</Writers>
<WriterClassGroups>
<WriterClassGroup name="All">1,2,3,4</WriterClassGroup>
<WriterClassGroup name="File">1</WriterClassGroup>
<WriterClassGroup name="Console">2</WriterClassGroup>
<WriterClassGroup name="EventLog">3</WriterClassGroup>
<WriterClassGroup name="Crash">4</WriterClassGroup>
</WriterClassGroups>
<Filters>
<FilterRecord writerClassGroup="Console" path = "saw" information="1" warning="31" error="31" trace="0" incident_error="32" />
<FilterRecord writerClassGroup="File" path = "saw" information="1" warning="31" error="31" trace="0" incident_error="32" />
<FilterRecord writerClassGroup="File" path="saw.mktgsqlsubsystem.joblog" information="1" warning="2" error="31" trace="0" incident_error="32"/>
<FilterRecord writerClassGroup="File" path="saw.httpserver.request"
information="16" warning="32" error="32" trace="0" incident_error="32"/>
<FilterRecord writerClassGroup="File" path="saw.httpserver.response"
information="16" warning="32" error="32" trace="0" incident_error="32"/>
</Filters>
</Logging>
</Server>

Table 8-4 contains a description of each node in the configuration hierarchy.

Table 8-4 Oracle BI Presentation Services Log Configuration File Elements

Element Attribute Description

Writers

None

Contains writers configuration.

This configuration is loaded on startup.

Writer

None

Configures a writer.

Writer

disableCentralControl

(Optional) Determines that this entry is not updated by Fusion Middleware Control. Default value is true.

Writer

implementation

The following implementations are defined:

  • FileLogWriter. Writes to a disk file.

  • CoutWriter. Writes to standard output.

  • EventLogWriter. Writes to a Windows event log or UNIX syslog.

  • CrashWriter. A Windows only facility that writes to a crash dump file when Presentation Services attempts to log from a specific source file and line number.

    Used in a production environment for information of some loggable but irrecoverable error (for example, failed NQTEST).

    Note: Use this implementation with care as it might leave the server in an unstable state. Use this implementation in very rare diagnostic-only scenarios on a test system.

    On Windows, CrashWriter requires the appropriate version of dbghelp.dll (at least 6.0.17.0).

    The correct dbghelp.dll can be found in support/windows/system32.

    Put this DLL in the WINNT/system32 or in the main/bin directory.

    No registration is required.

Writer

name

Unique name for the writer.

Writer

writerClassId

Specifies an integer number in the range 1 through 10. This number is used by filters to allow or prohibit logging.

Each distinct writer must have a unique value, which is used later for filter configuration.

Different writers might have the same class ID, but if they do, those writers cannot be distinguished by filters.

Writer

fmtName

(Optional) Specifies the format of logged messages. Valid values are:

  • default - 10g style. Formats messages with identifying headings.

  • ODL-TEXT. Formats messages in Oracle Diagnostic Text format.

  • ODL-XML. Formats messages in Oracle Diagnostic XML format.

If you do not set this attribute, then logged messages are displayed in the default format which for file log writers is 10g style and for console is ODL-TEXT.

See Section 8.5.4, "Examples of the Formats of Logged Messages" for examples.

Writer (FileLogWriter specific attribute)

dir

Specifies the directory where log files are created.

Writer (FileLogWriter specific attribute)

ODLLogFilePath

Specifies the file that Fusion Middleware Control displays in the Log Viewer.

Writer (FileLogWriter specific attribute)

maxFileSizeKb

Specifies the maximum size of the logging file in kilobytes.

When the file size limit is reached, the file is closed and a new logging file is created.

Writer (FileLogWriter specific attribute)

filePrefix

Specifies the prefix for log files.

Writer (FileLogWriter specific attribute)

filesN

Specifies the maximum number of logging files.

When this number is exceeded, the first file is deleted and re-created again. Then the logger starts to write to the beginning of the first file.

Writer (EventLogWriter specific attribute)

winSource

Specifies the event log source for logged events.

Writer (CrashWriter specific attribute)

file

Specifies the dump file path.

On Windows, a dump file is created in bin/coredumps and Presentation Services continues to run.

Writer (CrashWriter specific attribute)

line

Dump file line number.

WriterClassGroups

None

Contains the definition for writer classes. A writer class is a group of writer class IDs.

WriterClassGroup (Contains [as child text] a comma-delimited list of class IDs.)

name

Specifies the name of the WriterClassGroup.

Filters

None

Contains filter configuration.

FilterRecord

writerClassGroup

Specifies the group of writers to which this record is applied. WriterClassGroup is likely defined previously in the WriterClassGroups section.

FilterRecord

disableCentralControl

(Optional) Determines that this entry is not updated by Fusion Middleware Control. Default value is true.

FilterRecord

path

Specifies the log source path. To enable the logging of SOAP information, enter the following value:

saw.httpserver.request.soaprequest

The current filter record is applied to the software component that is identified by that path and all its subcomponents.

FilterRecord

information

Contains an integer that specifies the severity of the corresponding message type.

Only messages with a severity index less than the provided number are logged.

FilterRecord

warning

Contains an integer that specifies the severity of the corresponding message type.

Only messages with a severity index less than the provided number are logged.

FilterRecord

error

Contains an integer that specifies the severity of the corresponding message type.

Only messages with a severity index less than the provided number are logged.

FilterRecord

trace

Contains an integer that specifies the severity of the corresponding message type.

Only messages with a severity index less than the provided number are logged.

FilterRecord

incident_error

Contains an integer that specifies the severity of the corresponding message type.

Only messages with a severity index less than the provided number are logged.


8.5.4 Examples of the Formats of Logged Messages

The fmtName attribute of the Writer element formats logged messages in one of three formats: default (10g style), ODL-TEXT, and ODL-XML. The following entries are examples of these formats.

Example 8-5 shows the default format.

Example 8-5 Default Format

The default format generates messages with identifying headings, such as:

Type: Information
Severity: 30
Time: Wed Jul 26 11:22:20 2006
File: project\sawserver\sawserver.cpp
Line: 399
Properties: ThreadID-2552
Location: 
             saw.sawserver
             saw.sawserver.initializesawserver
             saw.sawserver
Oracle BI Presentation Services has started successfully.

Example 8-6 shows the ODL-TEXT format.

Example 8-6 ODL-TEXT Format

The short format generates messages in a shortened form without identifying headings, such as:

[timestamp] [component id] [messagetype:level] [message-id] [module id] ([field-name: field-value])* message-text [[
supplemental-detail
]]

[2010-05-27T10:51:20.000-07:00] [OBIPS] [NOTIFICATION:1] [] [saw.sawserver] [ecid: 1243446680218334471555761] [tid: 2552] Oracle BI Presentation Services (OBIPS) 11.1.1.2 (Build 0) are starting up.[[
File:sawserver.cpp
Line:432
Location:
   saw.sawserver
   saw.sawserver.initializesawserver
   saw.sawserver
ecid: 1243446680218334471555761 
]]

Example 8-7 shows the ODL-XML format.

Example 8-7 ODL-XML Format

The xml format generates messages in XML format, such as:

<msg time="2010-05-08T18:41:05.000+00:00" 
comp_id="OBIPS" type="NOTIFICATION" level="1" msg_id="" 
module="saw.sawserver" ecid="124180446517874242628761" tid="127c"> 
<txt> Oracle BI Presentation Services has started successfully</txt> 
<suppl_detail /> 
</msg>

8.5.5 Oracle BI Presentation Services Message Structure

Each message that is logged by Presentation Services has several components, as described in Table 8-5.

Table 8-5 Components of Presentation Services Log Message

Message Component Description

Message Text

The text of the log message to the user.

Message Type

One of five types: information, warning, error, incident_error or trace.

For information, see Table 8-2.

Severity

The severity is represented as a positive integer.

The lower the value, the more important the message. A message with severity of 0 is the most important type of message, whereas a message with a severity of 32 is not important at all.

Message Properties

Properties indicate other kinds of information. The kind varies among messages and might include user name, the IP address of the client browser, the thread ID, and so on.


8.5.6 Oracle BI Presentation Services Log Filters

FilterRecords customize logging details. Use FilterRecords to specify the implementation (output type) and logging levels for categories of web logs: Incident Error, Error, Trace, Warnings, and Information.

In the following example, the first two FilterRecords contain the following string:

path="saw"

This string logs the informational events at level 1, the error messages at level 31, and so on:

<FilterRecord writerClassGroup="Console" path="saw" information="1" warning="31" error="31" trace="0" incident_error="32" />
<FilterRecord writerClassGroup="File" path="saw" information="1" warning="31" error="31" trace="0" incident_error="32" />
<FilterRecord writerClassGroup="File" path="saw.mktgsqlsubsystem.joblog" information="1" warning="2" error="31" trace="0" incident_error="32"/>

This high-level path applies to every event.

You can customize FilterRecords by adding new FilterRecords, such as the third one shown in the preceding example, with finer-grain specification of log levels for events of various types. In this example, information is being logged to a disk file from saw.mktgsqlsubsystem.log, which generates Marketing job events.

You can disable logging of job details by changing the information level from 1 to 0, as shown in the following example, or by commenting out the lines:

<FilterRecord writerClassGroup="Console" path="saw" information="1" warning="31" error="31" trace="0" incident_error="32" />
<FilterRecord writerClassGroup="File" path="saw" information="1" warning="31" error="31" trace="0" incident_error="32" />
<FilterRecord writerClassGroup="File" path="saw.mktgsqlsubsystem.joblog" information="1" warning="2" error="31" trace="0" incident_error="32"/>

8.5.7 Diagnosing Issues with Agents

If an agent fails to execute fully or if debugging is turned on in Oracle BI Scheduler, then a log file is generated for the agent.

You manually turn on debugging by setting the Debug element to true in the Oracle BI Scheduler instanceconfig.xml file. (For information, see Section 8.3.2, "What Are Diagnostic Log Configuration Files and Where Are They Located?")

The location for agent log files is specified in the instanceconfig.xml file for the Oracle BI Scheduler. (For information, see Section 20.3.3.3, "Agent Scheduler Configuration Settings.") The default location for log files is the Log directory in the Oracle Business Intelligence installation directory on the computer where the Oracle BI Scheduler is installed.

The log file name has the following format:

Agent-JobID-InstanceID.xxx

In this file name:

  • Agent is the prefix for all agent log files.

  • JobID is the Oracle BI Scheduler job identifier for the agent.

  • InstanceID is the Oracle BI Scheduler instance identifier for the agent.

  • xxx is the file extension:

    • .err for agent error log files.

    • .log for debug log files.

The agent error and debug log files are written as separate files for each agent instance that fails to execute. You can use a text editor to view the files. Entries are generally self-explanatory.

The presence of an error log does not necessarily mean that an agent failed completely. For example, suppose an agent delivers content to multiple email addresses. If some addresses are invalid or the mail server is down, then an error log is generated for the agent.

You can also view error messages and exit codes for job instances in Job Manager. For information, see "Instance Properties in Job Manager" in Oracle Fusion Middleware Scheduling Jobs Guide for Oracle Business Intelligence Enterprise Edition). Exit status shows the number of deliveries successfully completed.

8.6 Using ODBC/JDBC Procedures to Obtain Oracle BI Server Diagnostics

This section describes how to use ODBC/JDBC procedures to obtain diagnostic information for the Oracle BI Server. It contains the following topics:

8.6.1 About the Oracle BI Server ODBC/JDBC Procedures

You can use ODBC/JDBC procedures to obtain diagnostic information for the Oracle BI Server. These procedures are especially useful on non-Windows platforms where you cannot run the Administration Tool.

Use the nqcmd utility to run the procedures using ODBC. See "Using nqcmd to Test and Refine the Repository" in Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition for more information about nqcmd.

You can also run the procedures using JDBC. For more information about using JDBC to connect to the Oracle BI Server, see the README.TXT file contained in the bijdbc.jar file in ORACLE_HOME/bifoundation/jdbc.

8.6.2 Obtaining a List of Available Diagnostic Categories

You can first run OBISAvailableDiagnostics() to get a list and description of the diagnostic categories that are available. For example:

call OBISAvailableDiagnostics()

The results appear similar to the following:

Category Description

General

General overview of the OBIS instance you are connected to.

DBInstance:DBNAME1

All of the statistics related to the DB instance named in DBNAME1

DBInstance:DBNAMEn

All of the statistics related to the DB instance named in DBNAMEn

LDAP:Instance1

All of the statistics related to the LDAP instance named in Instance1

LDAP:Instancen

All of the statistics related to the LDAP instance named in Instancen

DBConnectionPool:Instance1

All of the statistics related to the DB connection pool named in Instance1

DBConnectionPool:Instancen

All of the statistics related to the DB connection pool named in Instancen

ThreadPool:Instance1

All of the statistics related to the Thread pool named in Instance1

ThreadPool:Instancen

All of the statistics related to the Thread pool named in Instancen

Cache:Instance1

All of the statistics related to the Cache named in Instance1

Cache:Instancen

All of the statistics related to the Cache named in Instancen


All categories, except for the General category, are Instance categories. Instance categories are statistics related to a particular instance object (like a specific physical database). If multiple instances of an object are initialized, separate categories exist for each instance, in the format category_name:instance_name. See the preceding table for examples.

Note the following about the ODBC/JDBC categories:

  • The ThreadPool category only displays statistics from threads created and managed by the DbConnection PoolMgr.

  • The Cache category displays statistics from the Compiler Cache and the LDAP Internal Cache.

8.6.3 Running Specific Diagnostics

After you obtain the available diagnostic categories, you can call OBISDiagnostics(string) to obtain diagnostics for individual categories, where string is a category name. For example:

call OBISDiagnostics('ThreadPool:orcldb_pool')

The results appear similar to the following:

Parameter Name Value

CAPACITY

1000

THREAD COUNT

20

BUSY THREAD COUNT

15

ACCUMULATED REQUESTS

5

MAX STACK SIZE

100


The spelling of the category must be correct, or no rows are returned.

Another example might be:

call OBISDiagnostics('General')

The results appear similar to the following:

Parameter Name Value

TOTAL SESSIONS

10

QUERIES PER SEC

5

NEW LOGINS

10

ACTIVE LOGINS

7

NEW REQUESTS

30

DATA CACHE HIT PER SEC

5

NEW INIT BLOCKS

10


8.6.4 About Parameters for ODBC/JDBC Procedures

The following tables provide parameter reference information for each category type:

Table 8-6 General Category Parameters

Parameter Name Description

TOTAL SESSIONS

The total number of sessions connecting clients to the Oracle BI Server.

QUERIES PER SEC

The number of queries completed each second by the Oracle BI Server.

NEW LOGINS

The total number of new login requests received by the Oracle BI Server.

ACTIVE LOGINS

The total number of active logins within the Oracle BI Server.

NEW REQUESTS

The number of new execute requests received by the Oracle BI Server.

DATA CACHE HIT PER SEC

The percentage of data cache hits for each second.

NEW INIT BLOCKS

The total number of new initialization block requests received by the Oracle BI Server.


Table 8-7 DBInstance Category Parameters

Parameter Name Description

QUERIES PER SEC

The number of queries completed each second by the back-end database.

FAILED QUERIES PER SEC

The number of queries that failed each second in the back-end database.

NEW PREPARES

The number of prepares sent to the back-end database.

ROWS PER SEC

The number of rows retrieved each second from the back-end database.

KB PER SEC

The number of kilobytes retrieved each second from the back-end database.


Table 8-8 LDAP Category Parameters

Parameter Name Description

NEW REQUESTS

The total number of new LDAP authentication requests received.

NEW IMPERSONATED REQUESTS

The total number of new impersonated LDAP authentication requests received.

ACTIVE REQUESTS

The number of LDAP authentication requests active within the Oracle BI Server.


Table 8-9 DBConnectionPool Category Parameters

Parameter Name Description

CAPACITY

The maximum number of connections that the database connection pool allows.

CONNECTION COUNT

The current number of open connections in the thread pool.

BUSY CONNECTION COUNT

The number of connections that have been assigned to process a query, or that are currently processing a query, in the database connection pool.

AVG REQUESTS PER SEC

The average number of requests each second that have been submitted to the database connection pool.

AVG OPEN REQUESTS PER SEC

The average number of connections that are opened each second. Connections might be opened for new connections, because other connections timed out, or because of problems with a connection.


Table 8-10 ThreadPool Category Parameters

Parameter Name Description

CAPACITY

The maximum number of threads allowed by the thread pool.

THREAD COUNT

The current number of threads in the thread pool.

BUSY THREAD COUNT

The current number of threads that have been assigned work. The thread might be blocked waiting for a resource or data, or it could be actively running on a CPU.

ACCUMULATED REQUESTS

The total number of requests that have been submitted to the thread pool.

MAX STACK SIZE

The maximum number of stack bytes consumed for all threads in the thread pool.


Table 8-11 Cache Category Parameters

Parameter Name Description

CAPACITY

The total capacity of the specified cache object.

TOTAL REQUESTS

The total number of requests each second against the specified cache object.

AVG REQUESTS

The average number of requests each second against the specified cache object.

AVG HITS

The average number of hits each second for the specified cache object.

AVG MISS

The average number of misses each second for the specified cache object.