Command-Line DBA Scripts

Topic: A review of the command-line monitoring scripts that I currently use for Oracle.

Note: last updated in June 2015. Download links: http://canali.web.cern.ch/canali/resources.htm and https://github.com/lucacanali

Command-line is still very useful for Oracle database administration, troubleshooting and performance monitoring. One of the first thins that I do when I want to work with a given Oracle database is to connect as a DBA user with Sql*plus and get information on the active sessions with a script. I will run the script a few times to get an idea of what's happening 'right now' and get a possible starting point for more systematic tuning/troubleshooting if needed.
I liken this approach to examining a few pictures from a remote webcam and trying to find out what's happening out there!.. The method can be powerful, although with some limitations. One of its advantages is that it is easy to implement in Oracle! One of the main source of information for this type of monitoring is GV$SESSION, which is available in Oracle since ages. More recently GV$ACTIVE_SESSION_HISTORY has proven very useful and I quite like the additional improvements introduced there in11gR2.

Examples: One of my favorites is a script I have called top.sql (not very much an original name I know :) which queries gv$session (RAC-aware) and prints out what I currently find the most interesting details (for my environment) on active sessions that I can fit in roughly 200 characters. Another script that does a similar job taking data from v$active_session_history is ash.sql. the scripts ash2.sql, ash3.sql and ash4.sql are the RAC versions for 2, 3 and 4 node RACs respectively.

One of the typical pain points when writing CLI monitoring scripts is deciding which information to display and how to pack it in a relatively short line. I have also found that with each major Oracle versions I want to refresh my scripts with a few changes.

Scripts details: I have packaged some of the scripts that I have written and that I find useful in a zip file which is available at this link (unzip locally and run using sqlplus).  Note that the scripts are tested on 11gR2, many of them will also work on 10g and 12c.

Monitoring Class (Active Session Monitoring)

Script Name	Description
top.sql	List with selected details of the active sessions, from gv$session (RAC aware)
top_10g.sql	Active sessions details, from gv$session, 10g version  (RAC aware)
ash.sql	List with selected details of the active sessions, from v$active_session_history (one instance only)
ash_h.sql	Usage @ash_h <n#_sec>. Active sessions details for the last n#_sec seconds, from v$active_session_history (current instance only)
ash2.sql, ash3.sql ash4.sql	Active sessions details, from gv$active_session_history (for RAC 2, 3 and 4 nodes).

SQL Monitoring

Script Name	Description
monitor.sql	Display information form gv$sql_monitor for running statements statements. See also monitor_details.sql
monitor_plan.sql	Usage: @monitor_plan <key>. A drill-down script from monitor.sql. Prints details of the execution plan from gv$sql_plan_monitor
monitor_report_sid.sql, monitor_report_sid_active.sql	Usage: @monitor_report_sid_active <sid>. Generates an active/html sql monior report for the given sid. See also @monitor_report_sid <sid> for the simpler html version.
monitor_report_sqli_d.sql, monitor_report_sql_id_active.sql	Usage: @monitor_report_sql_id_active <sql_id>. Generates an active/html sql monior report for the given sql_id. See also @monitor_report_sql_id <sql)id> for the simpler html version.

Drill-down Class

Script Name	Description
ash_sess.sql	Usage: @ash_sess <inst_id> <sid> <n#_sec>. Prints details from gv$active_session_history for a given session (RAC aware)
explain.sql	Usage: @explain <sql_id>. Prints detailed explain plan info for given sql_id from library cache (current instance only)
explain_awr.sql	Usage: @explain_awr sql_id. Prints detailed explain plan info for given sql_id from AWR (rac aware)
obj.sql	Usage: @obj <object_id>. Find details for a given object id, or data_object_id
sessinfo.sql	Usage example: @sessinfo sid=<num>. Prints info on sessions that match the search pattern (RAC aware)
sql.sql	Usage: @sql <sql_id>. Prints sql text for given sql_id (RAC aware)

Workload Characterization Class

Script Name	Description
sysmetric.sql, sysmetric_details.sql	Display values of selected system metrics (RAC aware)
iometric.sql, iometric_details.sql	IO utilization metrics and details (RAC aware)
ehm.sql, ehm_local.sql ehm_micro, ehm_micro_local	Usage: @ehm 10 "db file sequential read". Event history metric data, helps finding skew in wait times, for example to study random reads latency skew (RAC aware). ehm_micro -> 12c version, latency drill down at microsecond.
eventmetric.sql	Wait event metric details for top wait events (RAC aware)
filemetric.sql	File metric details for top-usage files (RAC aware)
ash_top.sql	Usage @ash_top <n#_sec>. Aggregates ash data over n#_sec seconds (current instance only)
jobs_running.sql	List running jobs details (RAC aware)
load.sql	Displays OS-load values (RAC aware)
longops.sql, longops_10g.sql, longops_details.sql	Info on long running operations from gv$sessoin_longops (RAC aware)
transactions.sql	List of open transactions and crash recovery (RAC aware)

ASH and AWR Reports

Script Name	Description
ash_report.sql	Usage example: @ash_report sysdate-1/24 sysdate Generates an ASH report for the current instance and displays it in a browser.
awr_report.sql	Usage: @awr_report <snap_begin> <snap_end>. Generates an AWR report for the current instance and displays it in a browser.
awr_global_report.sql	Usage: @awr_global_report <snap_begin> <snap_end>. Generates a global AWR report for the current database and displays it in a browser.
awr_snapshots.sql	Lists AWR snapshots for the last 24 hours.

Miscellaneous

Script Name	Description
kill.sql	Usage @kill <sid> <serial#> kill the given session (current instance only)
kill_select.sql	Usage example: @kill_select "sql_id='xxxx'". Helps in preparing scripts for killing large number of sessions (current instance only)
locks.sql, locks_10g.sql, locks_details.sql	Scripts to help investigating locks and blocking enqueues issues (RACaware)
binds.sql	Usage: @binds <sql_id>. Helps in finding bind values for a given sql_id (RAC aware)
login.sql	Sql*plus startup/config script for my environment (windows client version 11.2.0.3)

Conclusions
Command-line scripts are still of great help for monitoring and  performance troubleshooting of Oracle databases. Of particular interest are queries to print the details of active sessions, the SQL they execute and their wait events details. This makes for a quick and easy entry point to troubleshooting and to get an idea of what's happening on the DB server. More systematic troubleshooting may be needed beyond that. Command-line scripts are easy to modify and customize, many DBAs indeed have their own tool-set. A selection of my own DBA scripts can be downloaded from this link.

A few links to end this post: First of all the excellent session snapper and the rest of the collection of scripts by Tanel Poder. In addition a mention to a few cool tools that are a sort of bridge between command-line and graphical-interface approaches: MOATS by Tanel Poder and Adrian Billington , SQL Dashboard by Jagjeet Singh and topaas by Marcin Przepiorowski.

Listener.ora and Oraagent in RAC 11gR2

Topic: An investigation of a few details of the implementation of listeners in 11gR2, including the configuration of listener.ora in RAC and the role of the cluster process 'oraagent'.

11gR2 comes with several important changes and improvements to the clusterware in general and in particular the way listeners are managed. While the listener process is still the 'good old' process  tnslsnr (Linux and Unix), it is now started from the grid home (as opposed to database oracle home). Moreover listeners are divided in two classes: node listeners and scan listeners, although they use the same binary for both functions. There are many more details and instead of covering them here I'd rather reference this excellent review: Markus Michalewicz's presentation at TCOUG.

Oraagent takes good care of our listeners

• Node listeners and scan listeners are configured at the clusterware level, for example with srvctl and the configuration is propagated to the listeners accordingly
• this integration is more consistently enforced in 11gR2 than in previous versions
• The oraagent process spawned by crsd takes care of our listeners in terms of configuration and monitoring
• note that there is normally a second oraagent on the system which is spawned by ohasd and does not seem to come into play here
• Notably in the listener.ora file we can see which configuration lines are being taken care of automatically by oraagent, as they are marked with the following comment: # line added by Agent
• experiment on a test DB: delete one or more of the listener.ora configuration lines and restart the listener (for example with srvctl stop listener; srvctl start listener). The original configuration will reappear in listener.ora and the manually modified listener.ora will be renamed (with a timestamp suffix)
• The agent creates and maintains the file: endpoints_listener.ora
• this file is there for backward compatibility, see docs and/or support site for more info. 
• experiment on test: delete the file and restart the listerner, oraagent will recreate the file.
• Oraagent log can be found at: $GRID_HOME/log/{nodename}/agent/crsd/oraagent_oracle/oraagent_oracle.log
• Oraagent monitors the functioning of each listener 
• from the log file entries (see above about the location of the oraagent log file) we can see that each listener is monitored with a frequency of 60 seconds
• Oraagent comes into action also at instance startup, when the instance is started with srvctl (as opposed to 'manually' started instance from sqlplus) and sets LOCAL_LISTENER parameter, dynamically (this is done with an alter system command and only if the parameter has not been set on spfile).

Dynamic listening endpoints 

• Where are the TCP/IP settings of my listeners in listener.ora? 
• Only IPC endpoints are listed in listener.ora, this is at first puzzling, where are the TCP settings that in previous versions were listed in listener.ora? ..read on!
• Notes: 
• endpoint=the address or connection point to the listener. The most known endpoint in the oracle DB world being TCP, port 1521
• dynamic listening endpoint and dynamic service registration are both concepts related to listener activities, but they are distinct.
• Oraagent connects to the listener via IPC and activates the TCP (TCPS, etc) endpoints as specified in the clusterware configuration
• experiment on test: $GRID_HOME/bin/lsnrctl stop listener; $GRID_HOME/bin/lsnrctl start listener; Note that the latter command starts only the IPC endpoint. However oraagent is posted at listener startup and makes active the rest of the endpoints (notably listening on the TCP  port), this can be seen for example by running the following a few seconds after listener restart: $GRID_HOME/bin/lsnrctl status listener (which will list all the active endpoints)
• Another way to say that is that the endpoints for the listener in RAC 11gR2 are configured in a  dynamic way: TCP (TCPS, etc) endpoints are activated at runtime by oraagent
• this is indicated in the listener.ora by the new (undocumented) parameters ENABLE_GLOBAL_DYNAMIC_ENDPOINT_{LISTENER_NAME}=ON
• Experiment on test on disabling dynamic listening endpoint:  
• stop the listener: $GRID_HOME/bin/lsnrctl stop listener
• edit listener.ora and set  ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=OFF
• start the listener: $GRID_HOME/bin/lsnrctl start listener
• check listener.ora and check that the parameter edited above has not changed, that is  ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=OFF 
• in this case the TCP endpoint will not be started, that is the listener will be listening only on IPC. Check with: $GRID_HOME/bin/lsnrctl status listener 
• note: if we try do the same exercise by stopping and starting the listener with srvctl, as it would be the typical way to do it, we will see that the parameter ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER in listener.ora will be set again to ON. This will activate again dynamic listening endpoints, which is something we want in production, but not for this exercise!
• Note: the examples have been tested on 11.2.0.3 RAC for Linux. ORACLE_HOME needs to be set to point to the grid home (and TNS_ADMIN if used at all, needs to be set to $GRID_HOME/network/admin)

I don't need to edit listener.ora any more, or do I?

• We have seen above that the most important configurations related to listener.ora are automated via oraagent and the clusterware in general
• There are additional listener.ora parameters that are not managed by the clusterware in11gR2 and need to be configured in listener.ora in case we want to use them
• notably parameters for COST (Class of Secure Transports) parameters needed to secure against a security vulnerability, detailed at Oracle Security Alert for CVE-2012-1675
• The steps for the configuration are very well described in support note 1340831.1. Here we just mention that for a 2-node RAC the following parameters will be needed in listener.ora (note parameters taken from listener.ora on 11.2.0.3 RAC for Linux):

SECURE_REGISTER_LISTENER = (IPC,TCP)

SECURE_REGISTER_LISTENER_SCAN1 = (IPC,TCPS)

SECURE_REGISTER_LISTENER_SCAN2 = (IPC,TCPS)

WALLET_LOCATION =  (SOURCE =(METHOD = FILE)(METHOD_DATA =(DIRECTORY = ..put here directory of wallet..)))

Conclusions

The handling of listeners in 11gR2 RAC has become much more integrated with the clusterware and for most RAC configurations there is not much need to play with listener.ora any more. At a closer look of however, new parameters and overall some new rules of the game in the 11gR2 implementation are revealed, most notably the use of dynamic endpoint registration by the clusterware.

Kerberos Authentication and Proxy Users

Topic: a discussion on how to use Kerberos authentication  and of proxy users in Oracle. This aimed at providing an alternative to the classical use of username and password credential and to help ease the burden of DB credentials management.

Introduction: Kerberos authentication allows to connect to Oracle without specifying the username/password credentials. The authentication is done externally. Kerberos has been used by a large user community since many years (notably Windows Active Directory uses use Kerberos authentication).
Proxy authentication in Oracle, allows connection to a target DB username via another DB user (the proxy user). For example you can authorize a user with a development account to connect to the application owner account using his/her credentials (thus no need to expose the application user's password).

This post is about combining Kerberos and proxy authentication: you can provide a mean to connect to any given DB user via a Kerberos-authenticated user, i.e. without specifying the password at connection time.

Example of client-side configuration (Linux) 

• These tests have been performed using Oracle server and client 11.2.0.3 for Linux  
• See below for details of server-side configuration
• Create oracle user and grant connection privileges: 

create user "LUCA@MYDOMAIN.COM" identified externally;

grant connect to  "LUCA@MYDOMAIN.COM" ;

...add tablespace config and other privileges if needed..

• configure sqlnet.ora for the oracle client. This can be in $ORACLE_HOME/network/admin, $TNS_ADMIN or in $HOME/.sqlnet.ora depending on the scope of the configuration.

SQLNET.AUTHENTICATION_SERVICES=(ALL)            # easy way to includes KERBEROS5
SQLNET.KERBEROS5_CONF=/etc/krb5.conf            # need to have kerberos client configured
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=orasrv  #this needs to match DB server config
SQLNET.KERBEROS5_CONF_MIT=TRUE

• Request kerberos pricipal for your user if not already present: kinit luca
  • At this point you'll need to put the password on the command line as asked
  • It's also possible to use the oracle utility okinit, in this case for our config we need to run okinit -e 23 luca. 
  • When using db link one need forwardable tickets (kinit -f)
• Connect to the DB with kerberos credentials. In sqlplus use this syntax sqlplus /@MYDB
• Show user will show LUCA@MYDOMAIN.COM
• If you get instead ORA-12638: Credential retrieval failed means you need to troubleshoot. Unfortunately there are many causes for the error, staring with bugs and version incompatibilities (see also notes below).

Proxy users and Kerberos used together 

Suppose that we need to authorize the user luca@mydomain.com  to connect to production application. I would normally use: sqlplus myappuser/mysecretpass@MYDB. With Kerberos and proxy authentication, used together, we can do the following:

• Authorize the user to use proxy authentication: 
• alter user myappuser grant connect through "LUCA@MYDOMAIN.COM";
• Check the configuration with select * from PROXY_USERS; 
• Setup and test Kerberos authentication for MYDB, see steps above 
• Connect to the production user with this method (Kerberos+proxy user]. 
• The syntax in sqlplus is: sqlplus [myappuser]/@MYDB 
• show user from sqlplus will show myappuser as result
• With this method the password for myappuser has never been used. Only the password for the kerberos user  LUCA@MYDOMAIN.COM has been used when requesting the ticket with kinit
• One can still connect to myappuser with the orginal username/pass credentials too, this is just an additional path to it

Notes on client-side configuration (Windows)

• Kerberos authentication will work with the ticket granted by the domain at users logon
  • okinit, oklist etc are missing from instant client but are also not needed to make this work
  • do not use client 11.2.0.1 if possible. 
  • you need to logon as domain user
  • in alternative if you want to use this from a local account or usea different kerberos user, just run cmd.exe with run as and specify a domain user's credentials 
  • check with klist that you have the ticket for the principal "LUCA" in this example
• Create or copy over krb5.conf from a Linux machine (for example from DB server)
• Edit sqlnet.ora in %ORACLE_HOME%\network\admin (or %TNS_ADMIN%) adding the following:

SQLNET.AUTHENTICATION_SERVICES=(KERBEROS5)
SQLNET.KERBEROS5_CONF= ....put path here....\krb5.conf
SQLNET.KERBEROS5_CONF_MIT=TRUE
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=orasrv # match value on server

SQLNET.KERBEROS5_CC_NAME=OSMSFT:    # only needed in Windows

• connect to the DB with kerberos credentials
• same as the case of Linux above. 
• In sqlplus use this syntax sqlplus /@MYDB 
• This has been tested with a Windows 7 client and Oracle 11.2.0.3 instant client 32 bit

Notes on DB server configuration for kerberos authentication

This is described in Oracle documentation so I'll just put a few notes here.

• Ask the kerberos administrator to add a new service principal. Best to use a short name, for example orasrv, as used in the examples above
• Create the keytab file and on each DB server node(s) with correct permissions for the oracle user
• for CERN installations the tool cern-get-keytab can be used:
• sudo cern-get-keytab --service orasrv -k /etc/krb5.keytab.orasrv
• sudo chown oracle:dba /etc/krb5.keytab.orasrv
• Configure vi $ORACLE_HOME/network/admin/sqlnet.ora and add on all nodes 
• note there are a few differences with client-only configuration discussed above
• for RAC: edit sqlnet.ora in $ORACLE_HOME/network/admin, as opposed to $CRS_HOME/network/admin 

SQLNET.AUTHENTICATION_SERVICES=(ALL)

SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=orasrv
SQLNET.KERBEROS5_KEYTAB=/etc/krb5.keytab.orasrv # see keytab generation step above
SQLNET.KERBEROS5_CONF=/etc/krb5.conf
SQLNET.KERBEROS5_CONF_MIT=TRUE

Notes:

Setting up Kerberos authentication requires just a few steps and is often quite simple. However there are many possible sources of issues that may need to be troubleshooted for a new installation. That ranges from bug in certain Oracle versions, incompatibilities with encryption levels, etc. I have tested with Oracle client 11.2.0.3 both on Linux and Windows 7 again Oracle server server on 11.2.0.3 (Linux 64 bit). This support note can be a useful starting point for troubleshooting if needed: Master Note For Kerberos Authentication [ID 1375853.1]
A (quite) different technique also aiming at reducing the need of hard-coded credentials is Secure External Password Store. This is described in oracle documentation and in the following support note: Using The Secure External Password Store [ID 340559.1]

Note added on licensing: a recent update to the Oracle licensing information states that "strong authentication services (Kerberos, PKI, and RADIUS) are no longer part of Oracle Advanced Security and are available in all licensed editions of all supported releases of the Oracle database."

Conclusions: 

We have discussed how to set up Kerberos authentication for Oracle users both as a standalone method and in conjunction with Proxy Authentication. These techniques can be of help to reduce the need of hard-coded credentials when accessing Oracle database services.