Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Using the OPatch Utility in Oracle
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

Using the OPatch Utility

The Opatch utility is used to install Oracle patches.  Most Oracle DBAs are familiar with this utility.  Every Oracle CPU (Critical Patch Update) is installed using this utility, as well as all other patches that Oracle produces to fix bugs and update their product. Opatch lsinventory detail is the command used to query opatch in order to find out what patches are installed.

 

OPatch creates a hidden dotted directory called .patch_storage in the $ORACLE_HOME. In .patch_storage are directories created by OPatch which have a name identical to the number of the patch being installed and also contain time and date of the patch installation:

 

/export/home/u04/app/oracle/product/10.1.0/db_2/.patch_storage/4193293 

 

Inside each hidden Patch directory are log files describing the patch processes that have occurred. The instructions for installing each Oracle patch are included with the patch.  Normally, the method is to unpack the patch, move to the directory named the same as the patch number, then type opatch  apply.  In versions prior to 10.2.0.4, the command is opatch apply.

Utilities for Analyzing Oracle Trace Files

There are several utilities for analyzing Oracle trace files. These include trace assist (trcasst), session tracer (trcsess), trace analyzer (trcanlzr.sql) and tkprof.  Many DBAs are very familiar with the Oracle trace facility, but just in case, here are some brief instructions for using this powerful Oracle utility. Before tracing can be enabled, the environment must first be configured by performing the following steps:

  1. Enable Timed Statistics: This parameter enables the collection of certain vital statistics such as CPU execution time, wait events, and elapsed times. The resulting trace output is more meaningful with these statistics. The command to enable timed statistics is:
    ALTER SYSTEM SET TIMED_STATISTICS = TRUE;

  2. Check the User Dump Destination Directory: The trace files generated by Oracle can be numerous and large. These files are placed by Oracle in the user_dump_dest directory as specified in the init.ora. The user dump destination can also be specified for a single session using the alter session command. Make sure that enough space exists on the device to support the number of trace files that you expect to generate.

  3. Turn Tracing On: The next step in the process is to enable tracing. By default, tracing is disabled due to the burden (5-10%) it places on the database. Tracing can be defined at the session level:

ALTER SESSION SET SQL_TRACE = TRUE;
DBMS_SESSION.SET_SQL_TRACE(TRUE);

 

A DBA may enable tracing for another user’s session by using the following statement:

 

DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (sid,serial#,true);

 

The sid (Session ID) and serial# can be obtained from the v$session view. Once tracing with Oracle tkprof is enabled, Oracle generates and stores the statistics in the trace file. The trace file name is version specific.

  1. Enable Oracle tkprof tracing only on those sessions that are having problems. Explain Plan is not as useful when used in conjunction with tkprof since the trace file contains the actual execution path of the SQL statement. Use Explain Plan when anticipated execution statistics are desired without actually executing the statement.

  2. When tracing a session, remember that nothing in v$session indicates that a session is being traced. Therefore, trace with caution and remember to disable tracing after an adequate amount of trace data has been generated.

Tkprof does not control the contents of a trace file, it simply formats them. Oracle provides multiple ways to actually generate the trace file. Tkprof is valuable for detailed trace file analysis. For those DBAs that prefer a simpler tracing mechanism with instant feedback, the autotrace utility should be used.

 

The trace assist (trcasst) utility is used to analyze Oracle trace files generated by most Oracle error messages.  This utility will analyze the trace file and put it into a readable format.



For more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.


 

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.