Go to main content

Using Oracle® Solaris 11.4 StatsStore and System Web Interface

Exit Print View

Updated: November 2020
 
 

Oracle Database Sheet

If the system is running the Oracle Database, you can use the Oracle Solaris System Web Interface to view a high-level overview of Oracle Database performance and problems on that system. The information provided enables an Oracle Solaris administrator to diagnose whether the source of a problem is in the operating system, network, or storage, or whether a database administrator is required to further diagnose the problem.

Use the Oracle Database sheet to examine data such as the following:

  • Number of times per second that the database performs block changes, data retrievals, or user commits.

  • Amount of time the database spends waiting due to system events, user application code, and system configuration.

    See V$SYSTEM_EVENT and V$SYSSTAT in “Instance Tuning Using Performance Views” in the database performance tuning guide. For example, see Oracle Database Performance Tuning Guide 20c. In particular, see “Table of Wait Events and Potential Causes.”

  • Memory consumption of running database processes. Note that memory consumption is in kilobytes. In the graph in the following figure, memory consumption is shown in millions of kilobytes so that the data fits on the graph. Thus “4M” on the y-axis means 4 million kilobytes, or 4 GB.

The following figure shows an example of an Oracle Database sheet. The Oracle Database instance in this example is mypdb.

Figure 10  Sample Oracle Database Sheet

image:Figure shows an example Oracle Database sheet.

The Oracle Database sheet also includes system performance visualizations that are not specific to Oracle Database but are included for convenience. For example, you could correlate a drop in database work or an increase in database wait times with an increase in disk reads and writes from other sources or with a CPU going offline.

Configuring an Oracle Database Sheet

To use the Oracle Database sheet, install the service/oracle-rdbms-stats package. The service/oracle-rdbms-stats package delivers the following:

  • The Oracle Database sheet

  • Oracle Instant Client

  • The application/stats/oracle-database-stats SMF service

  • The statcfg utility

  • The statcfg(1) and rdbms-stat(1) man pages

Using the statcfg Utility

The statcfg utility enables the administrator to configure an Oracle Database sheet to report statistics for a specific Oracle Database instance. Until you run the statcfg utility, the oracle-database-stats SMF service is disabled, and you will not see an Oracle Database sheet.

The statcfg utility must be run as the root user or by assuming the root role.

Before you use the statcfg utility, export ORACLE_HOME as shown in the following example:

# export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

The following syntax shows how to use the statcfg utility with an Oracle Database instance:

# /usr/bin/statcfg [add] oracle-rdbms -u user -g group -s sid -c connectstring
# /usr/bin/statcfg remove oracle-rdbms -u user -g group -s sid

The add subcommand adds an Oracle Database sheet for the sid database instance as described in Adding an Oracle Database Sheet. The remove subcommand removes the Oracle Database sheet for the sid database instance as described in Removing an Oracle Database Sheet.

user, group

The Oracle Solaris user and group that this oracle-database-stats service instance will run as. See Additional User Configuration for Oracle Database Instances for additional configuration required for the user specified by user.

sid

The name of the Oracle Database instance that this oracle-database-stats:sid service instance will monitor.

connectstring

The alias for access credentials for the sid database instance. See “Oracle Wallet” in Additional User Configuration for Oracle Database Instances for more information.

Use the following command to show a help message about using the oracle-rdbms service value:

# /usr/bin/statcfg oracle-rdbms -h

Additional User Configuration for Oracle Database Instances

Perform the following configuration for the user specified by user:

Authorizations

Ensure that user has the following authorizations assigned:

  • solaris.sstore.update.res

  • solaris.sstore.write

The following example shows how to add these authorizations for user:

# usermod -A +solaris.sstore.update.res,solaris.sstore.write user
SYSDBA

Ensure that user has the SYSDBA connect privilege for the specified sid database instance.

ORACLE_HOME

The shell startup script for user must specify ORACLE_HOME, or specify ORACLE_HOME in the service as shown in the following example:

# svccfg -s application/stats/oracle-database-stats:sid \
setenv ORACLE_HOME /u01/app/oracle/product/19.0.0.0/dbhome_1
Oracle Wallet

The sqlnet.ora parameter file must specify the Oracle Wallet location for user.

The tnsnames.ora file must specify the Oracle Wallet db_connect_string alias for the sid database instance.

By default, both sqlnet.ora and tnsnames.ora are located in the $ORACLE_HOME/network/admin directory.

Follow the instructions in “Managing the Secure External Password Store for Password Credentials” in the database security guide to configure the user’s Oracle Wallet. For example, see Oracle Database Security Guide 20c.

Adding an Oracle Database Sheet

The following command adds an Oracle Database sheet that reports statistics related to the sid Oracle Database instance.

# /usr/bin/statcfg add oracle-rdbms -u user -g group -s sid -c connectstring

Run this command again with a different value for sid to create an additional Database sheet that reports statistics for that sid.

Removing an Oracle Database Sheet

Use the remove subcommand to remove the database sheet for the specified database instance.

# /usr/bin/statcfg remove oracle-rdbms -u user -g group -s sid

Troubleshooting Configuring an Oracle Database Sheet

Make sure the database instance is up and running.

Make sure the user has sufficient privilege to run the statcfg command. Run the statcfg command as the root user or assume the root role.

Make sure ORACLE_HOME is set in the environment where you run statcfg.

Check the state of the oracle-database-stats:sid service.

$ svcs oracle-database-stats:sid

If any of the Oracle RDBMS connections fail to communicate with their respective databases, the service will enter the degraded state.

If the service is in maintenance or is otherwise not online, check the service log as shown in the following example:

$ svcs -xL oracle-database-stats:sid

To check the settings of the service instance, use the svcprop command to show the values of the following properties:

  • user

  • group

  • db/sid

  • db/db_connect_string

For more information about service commands, see Managing System Services in Oracle Solaris 11.4.