Basics Oracle 12c Diagnostic Utilities

By Chris Ruel, Michael Wessler

As a Database Administrator (DBA), you should be grounded in the fundamentals of how your Oracle 12c database works and where specific files are located.

Sometimes you’ll have only a command-line interface into your database server and have to manually review log files. However, you also should know several easier, faster methods:

  • Oracle Enterprise Manager and Database Express 12c

  • Remote Diagnostic Assistant (RDA)

  • Database diagnostic scripts

Oracle Enterprise Manager and Database Express 12c

Graphical tools, such as Oracle Enterprise Manager and Database Express 12c, let you review alert messages and view incidents. These easy methods of checking for critical errors let you avoid manually sifting through text files.

Depending on the graphical tools used, you can upload files to Oracle Support in the form of a Service Request (SR). Some versions of Oracle GUI software also combine log files in a central location for easier viewing and management.

The capabilities of each GUI tool and the location of each utility change from release to release. Although these changes are a little frustrating at first, Enterprise Manager and Database Express 12c are very intuitive, so you can find what you need very quickly. For detailed information on these tools.

Remote Diagnostic Agent and Oracle 12c

Oracle Remote Diagnostic Agent (RDA) is an Oracle Support utility that captures Oracle-related information on an entire server and stores the results in a series of HTML files. RDA can be integrated with Oracle Configuration Manager (OCM) and can query a myriad of Oracle components outside the database, such as Oracle WebLogic or Enterprise Manager, in addition to your database.

The utility asks you a series of simple questions about your environment and what components you want to investigate. Don’t worry if you don’t know the answer to a question; you can accept the provided default answer.

The intent is to capture data about the operating system, hardware, Oracle software versions, database instances, listeners, and activities within the database environment in an automated manner. You can view that information as a series of static HTML pages to get fast graphical access to all Oracle-related information for a given server.

Not only is graphical information useful to DBAs, but when you provide an RDA to Oracle Support it gives an accurate picture of your database environment. In fact, one of the first things many Oracle Support analysts request is an RDA of your server.

Oracle RDA comes as a tar or zip file available for download via the my Oracle Support network. When downloaded, it executes on the server as either a Perl or shell script. The output is a zip file that you can

  • Upload to Oracle Support as part of a Service Request

  • Unzip into a series of HTML files that you can navigate to find key information

Here you see the main index page of the RDA output where you can drill down into multiple useful areas.

image0.jpg

RDAs are a great way to get a quick snapshot of a system even when there are no problems (per se). For example, if you are consulting and need a quick overview of a client’s system, the RDA is very handy. Or if you’re taking over an existing database server, the RDA tells you exactly what’s on that server and how it’s configured.

Oracle 12c database diagnostic scripts

Most old-school DBAs from the Oracle 7 days lived and died by their toolbox of database scripts — and for good reason. Database scripts based on internal database views and tables provided the raw information for what was going on in a database.

That raw data, coupled with a real understanding of how the database and application worked, often made for a very skilled administrator who could solve most problems. Toward that end, entire books are dedicated to database scripts, and many websites make scripts available for download.

Oracle provides a set of database scripts in every $ORACLE_HOME/rdbms/admin directory. In it are core scripts necessary to create a database, build the data dictionary, and other maintenance operations that aren’t very useful for troubleshooting.

However, the $ORACLE_HOME/rdbms/admin directory also has useful scripts such as utllockt.sql and utlrp.sql. Use them to search for database locks and compile invalid database objects. It is encouraged that you to become familiar with the scripts in this directory and identify the ones that fit into your toolbox.

Many good third-party scripts exist both from books and Internet downloads. Some good scripts are available, so don’t be afraid to seek out good sources and test them first on your development database.

And if all else fails, write your own. Here’s one of our favorites we’ve used many times to see what’s happening on a database and who is doing it:

SQL>get show_session_short.sql
 1 set linesize 180
 2 set pagesize 20
 3 col "Logon Time" format a11
 4 col "UNIX Proc" format a9
 5 col username format a15
 6 col osuser format a13
 7 col "Program Running" format a20
 8 col sid format 9999
 9 col "Connect Type" format a12
 10 col serial# format 9999999
 11 select s.username, osuser, status,
 12 to_char(logon_time,'fmHH:MI:SS AM') as "Logon Time",
 13 sid, s.serial#, p.spid as "UNIX Proc"
 14 from v$session s, v$process p
 15 where s.paddr = p.addr
 16 and s.username is not null
 17* order by status, s.username, logon_time
SQL>@show_session_short
USERNAME    OSUSER    STATUS  Logon Time  SID SERIAL# UNIX Proc
--------------- ------------- -------- ----------- ----- -------- ---------
SYSTEM     oracle    ACTIVE  11:39:11 PM  125    12 29062
SYS       oracle    ACTIVE  12:18:25 AM  119   829 31376
MWESSLER     oracle    ACTIVE  11:40:39 PM  124    54 29264
CRUEL     oracle    ACTIVE  11:41:19 PM  138    35 29359
MPYLE     oracle    INACTIVE 11:37:51 PM  135    13 28749
MWESSLER     oracle    INACTIVE 11:40:26 PM  129    24 29258
DWILSON     oracle    INACTIVE 11:40:49 PM  155    32 29273
CSARJENT     oracle    INACTIVE 11:40:59 PM  126    54 29275
DBSNMP     oracle    INACTIVE 11:41:29 PM  132    19 29357
APEX_WEB     oracle    INACTIVE 11:41:39 PM  131    55 29355
RMAN     oracle    INACTIVE 11:41:49 PM  127   323 29361
11 rows selected.

Although the script is useful, the actual point is to show you the power of a simple script and what it can provide quickly.

Despite all the wiz-bang GUI tools and wonderful database advisors, many folks still use database scripts for some, if not all, of their administrative work.

A toolbox of useful scripts coupled with modern Enterprise Manager tools provides DBAs with the best capability to manage their databases.