Sessions and Events in the Database

Active Sessions 

When you want to do a verification of current activity in the database, the following sos_sessions_active.sql script will list information on active sessions, blockers, associated events, impacted objects, users, program and services that are currently running across all instance(s) in the database (i.e. this script can run against a single instance or RAC database).

An example of this report can be found here:
(Click on image to zoom in)

Historical Sessions

At times, identifying performance issues in real time can be challenging. By the time the DBA is called upon to investigate the issue disappears. Rather then waiting for the issue to resurface, with Active Session History (ASH) introduced in 10g, the feature has become invaluable source to allow DBAs to perform post analysis and of sessions captured in the past.

Using the sos_sessions_history.sql script, by specifying the time period you wish to review, the report will return details on sessions and associated events.

NOTE: The columns “machine” and “sql_opname” is not available to be reported against DBA_HIST_ACTIVE_SESS_HISTORY in version 10g and 11.1.  So the sos_sessions_history.sql script will be required to edited to remove these columns from the query is executing against versions earlier then 11.2. 

A snippet from a sample output can be found here: (Click on image to zoom in)

ASMCMD Wrapper ... For the Frequent ASM Administrator

In the pre-11g days of ASM, I was hesitant to use ASMCMD (command line utility to perform ASM tasks)... perhaps in its earlier days, it was a combination of limited functionality and my comfort in connecting to the ASM instance via sqlplus to perform administrative tasks.

But now in 11g, I have grown the desire to use ASMCMD and have found it to be more useful then I have first envisaged. In 11g, some of the new functionality now offered through ASMCMD includes the ability to perform: startup and shutdown of ASM instance backup (md_backup) and restore (md_restore) of asm metadata list open files (lsof) verify disk usage (du) change/alter (chdg) disks in disk groups.   For a comprehensive list of ASMCMD commands, you can refer to Oracle 11.2 Database Storage Administrator's Guide.

With the more frequent use of ASMCMD, I got tired of switching between the ASM environment and DB environment settings, as a result, I have created an ASM wrapper script which calls asmcmd (ie. works when ASM is mounted or running) with the ability to preserve your DB environment setting. 

Example of using this script are provided below:

NOTE: Change to the directory to where the script sos_asm.sh was downloaded to and set executable permission accordingly

[oracle@lnx01] cd /home/oracle/scripts
[oracle@lnx01] chmod +x sos_asm.sh

Example 1:
Using the sos_asm.sh script, I performed an ASM metadata backup using asmcmd but also was able to preserve database environment settings for the database called BLUE.

[oracle@lnx01] export ORACLE_SID=BLUE; . oraenv
[oracle@lnx01] cd /home/oracle/scripts
[oracle@lnx01] ./sos_asm.sh md_backup /home/oracle/asm_backup/dgbk_DG_DATA -G DG_DATA
[oracle@lnx01] sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Jul 20 23:09:31 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select sys_context('USERENV','DB_NAME') DB from dual;
DB
-----------
BLUE

Viewing Realtime Alert Log via ADRCI

ADR (Automatic Diagnostic Repository) was introduced in 11g and is a datastore for diagnostic information for oracle components such as database, asm, listener and scan listeners. It stores trace files, logs in both clear text and xml format. ADR does allow the capability to see alert (and listener) logs in realtime.

For example if I wanted to view in realtime the alert log for Database called RED, I would do the following:

[oracle@lnx01] adrci
adrci> set home RED
adrci> show alert -tail -f

If you have multiple ADR homes and wish to quickly switch between alert or listener logs, I have created a script which can be downloaded called sos_alert.sh. Example of using this script are provided below, but first change to directory to where sos_alert.sh was downloaded to and set executable permission accordingly:

[oracle@lnx01] cd /home/oracle/scripts
[oracle@lnx01] chmod +x sos_alert.sh

Example 1: To view alert log for the database named RED

[oracle@lnx01] ./sos_alert.sh RED

Example 2: To view alert log for ASM

[oracle@lnx01] ./sos_alert.sh +ASM

Example 3: To view listener log for listener named “LISTENER”

[oracle@lnx01] ./sos_alert.sh LISTENER

Example 4: To view listener log for scan listener named “LISTENER_SCAN1″

[oracle@lnx01] ./sos_alert.sh LISTENER_SCAN1