Basics Oracle 12c Diagnostic Utilities
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.
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.