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:
Section 8.3, "Understanding Diagnostic Log and Log Configuration Files"
Section 8.6, "Using ODBC/JDBC Procedures to Obtain Oracle BI Server Diagnostics"
Oracle Business Intelligence provides various diagnostic tools to assist you in finding the causes and solutions to issues, as described in Table 8-1.
Tool | Description | Reference |
---|---|---|
Overview page in Fusion Middleware Control |
Enables you to view recent issues with the system. |
|
Performance metrics |
Enables you to view metrics that affect performance. |
|
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" |
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. |
|
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" |
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:
Section 8.2.1, "Using Fusion Middleware Control to View Log Information, Error Messages, and Alerts"
Section 8.2.2, "Configuring Log File Rotation Policy and Specifying Log Levels"
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:
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."
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.
View lists of the following:
Recent errors under the Most Recent Errors region
recent warnings under the Most Recent Warnings region
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.
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.
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.
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."
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:
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."
Display the Log Configuration tab of the Diagnostics page.
Click Lock and Edit Configuration to enable changes to be made.
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
Click Apply, then click Activate Changes.
Return to the Business Intelligence Overview page and click Restart.
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:
Open the component log configuration file as described in Section 8.3.2, "What Are Diagnostic Log Configuration Files and Where Are They Located?"
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.
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.
Save your changes and close the file.
Restart Oracle Business Intelligence.
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:
Display Fusion Middleware Control.
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.
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.
To filter for the messages for Oracle Business Intelligence
Click Add Fields, then select Module, and click Add.
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.
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.
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.
This section discusses diagnostic log files and diagnostic log configuration files, and contains the following topics:
Section 8.3.1, "What Are Diagnostic Log Files and Where Are They Located?"
Section 8.3.2, "What Are Diagnostic Log Configuration Files and Where Are They Located?"
Section 8.3.3, "What Are Log File Message Categories and Levels?"
Section 8.3.5, "What Messages Are Included in the System Log?"
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.
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.
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."
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."
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.
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:
The SQL statement in the initialization string. The Initialization string field is in the Initialization Block dialog, in the General tab.
The LOGGING column references stored values for the log level.
Set the logging level to 0 (zero) for each production user. The Logging level field is in the User dialog, in the User tab. In the Administration Tool, select Identity from the Manage option on the main toolbar. In the Identity Manager dialog, double-click a user and select the User tab.
This section contains the following topics:
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.
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:
|
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. |
To set the query logging level for a user:
In the Oracle BI Administration Tool, select Manage, then Identity.
The Identity Manager dialog is displayed.
Double-click the name of the user for which you want to set the query logging level.
The User dialog is displayed.
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.
Click OK.
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.
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.
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.
This section describes logging specifically in Presentation Services and contains the following topics:
Section 8.5.1, "Using the Oracle BI Presentation Services Logging Facility"
Section 8.5.2, "Setting the Logging Levels for Oracle BI Presentation Services"
Section 8.5.3, "Structure for the Oracle BI Presentation Services Configuration File"
Section 8.5.5, "Oracle BI Presentation Services Message Structure"
Section 8.5.6, "Oracle BI Presentation Services Log Filters"
For general information about logging in Oracle Business Intelligence, see Section 8.3, "Understanding Diagnostic Log and Log Configuration Files."
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.
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:
In the global header, click Administration.
In the Maintenance and Troubleshooting area, select the logging level to use under Reload Log Configuration.
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.
Click the Manage Sessions link to display the Manage Sessions page.
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.
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:
|
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:
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 |
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. |
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.
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.
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.
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>
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. |
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"/>
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.
This section describes how to use ODBC/JDBC procedures to obtain diagnostic information for the Oracle BI Server. It contains the following topics:
Section 8.6.1, "About the Oracle BI Server ODBC/JDBC Procedures"
Section 8.6.2, "Obtaining a List of Available Diagnostic Categories"
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.
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.
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 |
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. |