As part of the Oracle Solaris 11.4 release we introduced the new StatsStore and Dashboard functionality allowing you to collect and analyze stats from all over the system that Oracle Solaris is running on. Some of these stats come from the Oracle Solaris kernel and some from userland applications. One of the interesting things about the design of the StatsStore and Dashboard however is that it also allows you to collect data from other sources like applications running on Oracle Solaris.
As part of our Continuous Delivery strategy we're taking new functionality from our development gate of the next Oracle Solaris update and release it as part of the Oracle Solaris 11.4 SRUs. An example of this is the release of the new Database Stats Sheet as part of Oracle Solaris 11.4 SRU6. It uses this ability of the StatsStore to collect in stats from userland applications and show this in a new sheet of the Oracle Solaris Dashboard.
To make this as easy as possible we've introduced new commands statcfg(1) and rdbms-stat(1) that combine to do all the configuration for you. This configures and starts a new SMF service that connects with the Oracle Database and pulls key performance data from the Database and stores it in the StatsStore.
To do this it creates a new set of stats in the StatsStore and defines what they are. Additionally they create a new Database sheet that gives a default view of these Database stats together with certain stats coming from Oracle Solaris, giving the administrator a unique graphical representation of this combined data.
This blog will give an example on how this is all configured and show what the default Database sheet looks like.
One of the key steps in setting this up is to configure the Oracle Database Wallet so the SMF service can access the V$ tables in the database. The key thing is that the database user you configure in the wallet must have the SYSDBA connect privilege for the database SID you want to monitor. This is something the database admin will have to do for you if it doesn't already allow this.
For this example I'll use a database setup that we used for our Hands on Lab at Oracle OpenWorld last year, where we have a container database (CDB) and a pluggable database (PDB). We'll be configuring the wallet for this PDB.
Before we add anything to the wallet we'll need to ensure that the tnsnames.ora file has the appropriate alias and connection string to connect to the database SID. By default it's located in $ORACLE_HOME/network/admin/, but you could also put is somewhere else and have a symbolic link in this default directory that points to it. Here's our tnsnames.ora file:
-bash-4.4$ cat $ORACLE_HOME/network/admin/tnsnames.ora mypdb = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1) ) )
So the SID for the PDB is pdb1 but the alias we've created is called mypdb.
We'll also need to amend the sqlnet.ora file to make sure it points to where we want to locate our wallet file when something connects to the database. It too by default is located in $ORACLE_HOME/network/admin/, but can be located somewhere else if you want. Here's our sqlnet.ora:
-bash-4.4$ cat $ORACLE_HOME/network/admin/sqlnet.ora # sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora # Generated by Oracle configuration tools. NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/export/home/oracle/wallet))) SQLNET.WALLET_OVERRIDE = TRUE
Now we can create the wallet:
-bash-4.4$ mkstore -wrl $HOME/wallet -create Oracle Secret Store Tool : Version 126.96.36.199.0 Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved. Enter password: Enter password again:
Now we have the wallet we can add keys to it. Here we add the key to our mypdb instance and check if it's stored correctly:
-bash-4.4$ mkstore -wrl $HOME/wallet -createCredential mypdb sys "<my_database_password>" Oracle Secret Store Tool : Version 188.8.131.52.0 Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved. Enter wallet password: -bash-4.4$ mkstore -wrl $HOME/wallet -listCredential Oracle Secret Store Tool : Version 184.108.40.206.0 Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved. Enter wallet password: List credential (index: connect_string username) 1: mypdb sys
Note that you would put your own database password instead of "<my_database_password>" and that this password is now stored behind entry #1.
We can now check if all works correctly:
-bash-4.4$ sqlplus /@mypdb as SYSDBA SQL*Plus: Release 220.127.116.11.0 Production on Thu Feb 28 05:15:34 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 18.104.22.168.0 - 64bit Production SQL>
It works! We can connect through the wallet. This is essentially what the SMF process will do.
Now to install the packages for the new Database sheet. The main new package that pulls data into the StatsStore is pkg:/service/system/statcfg however there's an additional package that configures statcfg and understand how to pull data in from an Oracle Database. This package is called pkg://solaris/service/oracle-rdbms-stats or oracle-rdbms-stats for short. And because it's dependent upon the statcfg package we only need to add the oracle-rdbms-stats package and it will pull statcfg in. Become root and install the package:
-bash-4.4$ su - Password: Oracle Corporation SunOS 5.11 11.4 January 2019 root@HOL3797-19:~# pkg install oracle-rdbms-stats Packages to install: 6 Mediators to change: 1 Services to change: 1 Create boot environment: No Create backup boot environment: No DOWNLOAD PKGS FILES XFER (MB) SPEED Completed 6/6 92/92 146.3/146.3 82.8M/s PHASE ITEMS Installing new actions 208/208 Updating package state database Done Updating package cache 0/0 Updating image state Done Creating fast lookup database Done Updating package cache 1/1
Note it pulled in a total of 6 packages, so 4 more besides the statcfg package. At this point we're ready to configure the connection.
Now we're ready for the final steps. First, still as the root role, give the user oracle the authorizations to write into and update the StatsStore:
root@HOL3797-19:~# usermod -A +solaris.sstore.update.res,solaris.sstore.write oracle UX: usermod: oracle is currently logged in, some changes may not take effect until next login.
We can ignore this last message as the thing we care about is the new oracle-database-stats service and it's process running as user oracle, and when the service is started it will now automatically pick up these authorizations.
Now we configure the new service. Because we're still running the root role the environment settings needed to find the database aren't set, and so the database connection string and the wallet wouldn't be found either. So we set ORACLE_HOME:
root@HOL3797-19:~# export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
Then we use statcfg(1) to configure the new service, plus we make sure the new service also has ORACLE_HOME set:
root@HOL3797-19:~# /usr/bin/statcfg oracle-rdbms -u oracle -g oinstall -s mypdb -c mypdb Creating a new service instance for 'mypdb' root@HOL3797-19:~# svccfg -s oracle-database-stats:mypdb setenv ORACLE_HOME /u01/app/oracle/product/12.2.0/dbhome_1
At this point you can check if the service has successfully started by either checking if there are any issues with svcs -x or svcs -l oracle-database-stats:mypdb. The first will show if any services are having issues and the second will show you the current status of the new oracle-database-stats:mypdb service.
Note: There was an early glitch where after installing the new packages the sstored wasn't restarted resulting in the a problem with the new //:class.app/oracle/rdbms class not being added to StatsStore, this is easy to fix by restarting the sstore service. If the oracle-database-stats:mypdb has gone into maintenance because of this you can easily clear it once the sstore service is restarted. This should be addressed soon if not already when you read this.
Running the statcfg command not only created and configured a new service that pulls data from the Oracle Database, it also creates a new sheet for the Web Dashboard and to look at this connect to the Dashboard on port 6787. Once there navigate to the page that shows you the overview of all the sheets you can choose from and you should see these two added:
The one on the right is dedicated to the new oracle-database-stats:mypdb service and if you click on it, it should look something like this:
This is a screenshot of my database running a swingbench workload to show what type of information you get to see. The top two graphs are coming from the Oracle Database (v$system_event and v$sysstat tables) and they give an insight on what the activity is inside the database and the next is a graph showing all the Oracle processes and their memory size (these are mainly the database shadow processes sharing memory). The other graphs are showing general system stats. All of these are coming out of the StatsStore. (I've zoomed in on the top graph quite a lot to get a nice picture, otherwise you only see the one or two peaks and hardly the small stuff in-between.)
This sheet is a basic example that is provided to get the initial isights in what is happening inside and outside the database. The nice thing about the Dashboard is that you can create your own new sheet, either by copying this sheet and editing it or by creating a brand new one, and this way you can combine these database stats with other stats to fit your needs. But that's for another blog.
Alternatively you can also use the REST interface to pull the stats out into the central monitoring tools of your choice. This too is something for another blog.
Note 1: It is good to know that this functionality focused on local databases. Even though when configuring the tnsnames.ora you can set the connection string to connect to a database on a remote system and the configured oracle-database-stats service will then pull in stats from this remote database, but the StatsStore won't have any OS-based stats to compliment this. Plus the Web Dashboard sheet that is automatically created also won't show correlated data.
Note 2: There's no additional license required to gather this data, so if you don't have the Diagnostics Pack enabled this will still work. Of course, the goal of this functionality is not built to replace the Diagnostics Pack and things it gives you (like AWR reports), it's to give you current and historic data from the database and the OS as a starting point to better investigate where possible issues are.