2015-11-26

handling disks for ASM - when DB, Linux and Storage admins work together

A proper management of ASM Disks can be a complicated task.

On DOAG2015 I discussed with Martin Bach about the concept in my current company, where we implemented a setting which is consistent, robust and enables Storage, Linux and DB admins to work together easily.

As we started to think about ASM when 10.1 was out we tried to evaluate our possibility. asmlib was discarded quite early as it only increased complexity without additional value: We have a SAN (fibre channel) infrastructure with 2 separated fabrics. So a multipath configuration is needed. ASM (or asmlib)  can not handle this, so a proper multipath configuration is needed at all. Also asmlib hides storage details from DBAs/ASM-Admins, where we wanted to enable every person involved know as many details as possible easily.

We also saw ASM sometimes takes a long time to scan for new disks (every time you access v$asm_disks - so use v$asm_disk_stat as this view does not rescan but only shows infos about devices it has in SGA already) if there are many "files" (devices) in asm_diskstring.

asm_diskstring

We set asm_diskstring to a dedicated directory. In our case it's called /appl/oracle/asm_disks/* This speeds up a rescan of all "disks", it's also a clear indicator of all disks ASM uses. There we have symlinks to devices in /dev/mapper/

symlinks

The symlink has this format:
/appl/oracle/asm_disks/360060e80167bd70000017bd700000007p1_p9500_b52_MONIQP01_000 -> /dev/mapper/360060e80167bd70000017bd700000007p1

Some informations about all the values we stored there:
360060e80167bd70000017bd700000007p1 is the WWN of the disk, together with it's partition (p1).  The WWN is very useful in every discussion with Storage Admins, as it identifies the LUN from their perspective. We decided to partition the disks. It's shown in our records that Linux Admins touches un-formatted devices more often than devices which are formatted already. There were also some cases in early tests when the first block of a disk was cached by the kernel. Both issues are addressed when we format every disk. If required partitioning can help do adapt alignments.
p9500 is a shortname which identifies the Storage box with a name we can use during discussions. It's somewhere within the WWN as well. So it's a pure redundancy. But it makes discussions much easier.
b52 is a shortname to identify the datacenter. As pur fabrics are spawned across several datacenters, sometimes it's nice to have a fast knowledge about the topology.
MONIQP01_000 is the label used in some Storage boxes. It contains the Diskgroup name and some number.  At the moment it's NOT the NAME of an ASM-disk, but this can be introduced easily.

As the name of a diskgroup is coded into our naming schema, it's not accepted to reuse a disk for some other diskgroup. (Technically it's still possible, we just agreed not to do so). Even it seems this limits the DBAs flexibility, there are good reasons to do so. Disks are sometimes created with dedicated settings/parameters for a special purpose. Reusing such disks in other DGs would cause strange and hard to find performance symptoms. So If disks are not needed anymore we always "destroy" them and re-create new if needed.

udev rules

Our udev ruleset on RedHat6 is quite simple:
the file /etc/udev/rules.d/41-multipath.rules contains such lines:
ACTION=="add|change", ENV{DM_NAME}=="360060e80167bd70000017bd700000007p1", OWNER:="oracle", MODE:="0660", GROUP:="asmadmin"
We do not do any mapping of names here - it's only there to set permissions.

multipath

The config in /etc/multipath.conf is quite simple, only parameters required for every specific storage vendor / product.


I can not say a lot about configurations outside if the Linux server, so both SAN fabrics and the storage system are "just working".




2015-11-21

anatomy of DBA_FEATURE_USAGE_STATISTICS

In Oracle database the vie DBA_FEATURE_USAGE_STATISTICS is given to
display information about database feature usage statistics.
That's all I can find in The (12.1) Docu.

Just in case anyone asks - DBA_FEATURE_USAGE_STATISTICS is not mentioned in the license guide at all:

Still for me it's interesting what's behind the view, and how it's content is populated.

So I started to analyze it's anatomy.
Starting with the view definition (slightly rewritten for better readability):
SELECT samp.dbid,      
       fu.name,  
       samp.version,   
       fu.detected_usages, 
       samp.total_samples, 
       Decode(To_char( last_usage_date,  'MM/DD/YYYY, HH:MI:SS'), 
              NULL, 'FALSE', 
              To_char( last_sample_date, 'MM/DD/YYYY, HH:MI:SS'), 
              'TRUE', 'FALSE') currently_used, 
       fu.first_usage_date, 
       fu.last_usage_date, 
       fu.aux_count, 
       fu.feature_info, 
       samp.last_sample_date, 
       lsamp.ast_sample_period, 
       samp.sample_interval, 
       mt.description 
FROM   wri$_dbu_usage_sample samp, 
       wri$_dbu_feature_usage fu, 
       wri$_dbu_feature_metadata mt 
WHERE  samp.dbid = fu.dbid 
       AND samp.version = fu.version 
       AND fu.name = mt.name 
       AND fu.name NOT LIKE '_DBFUS_TEST%'    /* filter out test features */ 
       AND Bitand(mt.usg_det_method, 4) != 4  /* filter out disabled features */

This view is quite simple, and all important columns of wri$_dbu_usage_sample and wri$_dbu_feature_usage are shown in the projection.
The filters are only to hide some rows used for internal testing.

So the interesting part are those columns of wri$_dbu_feature_meatadata which are not shown.
In this table are 2 groups of columns:
The first group are the columns INST_CHK_METHOD and INST_CHK_LOGIC.
They describe if and how to check, if a detailed detection is needed at all.
INST_CHECK_METHOD has 3 possible values right now:
DBU_INST_ALWAYS_INSTALLED1/* no test required */
DBU_INST_OBJECT/* object name in LOGIC */
DBU_INST_TEST4/* only test */
DBU_INST_ALWAYS_INSTALLED means the test is run always. If DBU_INST_OBJECT it's only tested, if the object in INST_CHK_LOGIC exists.

The detection itself is described in the columns USG_DET_METHOD and USG_DET_LOGIC.
USG_DET_METHOD has 3 possible values:
DBU_DETECT_BY_SQL1/* SQL specified in USG_DET_LOGIC */
DBU_DETECT_BY_PROCEDURE/* Procedure specified in USG_DET_LOGIC */
DBU_DETECT_NULL4/* only test for _DBFUS_TEST_SQL_7 */

If it's set to DBU_DETECT_BY_SQL, the SQL stored in USG_DET_LOGIC is executed and it's result stored. In case it's DBU_DETECT_BY_PROCEDURE, the procedure is called. It has 3 parameters (with different names, but the type is always the same):
Argument Name Type   In/Out Default 
------------- ------ ------ ------- 
IS_USED       NUMBER OUT            
DATA_RATIO    NUMBER OUT            
CLOB_REST     CLOB   OUT    

DATA_RATIO will be stored in AUX_COUNT, CLOB_REST in FEATURE_INFO.

These infos should be sufficient to analyze everything inside and related to DBA_FEATURE_USAGE_STATISTICS.



During the research I found 2 ways to do a immediate run of all tests. Instead of the default weekly run. Those update wri$_dbu_feature_usage - so the results are stored.

The first one is to kick mmon to run the usage sampling out of schedule:
alter session set events 'immediate trace name mmon_test level 6';

The second method is to run the sampling in the current session. This is quite nice for any kind of tracing:
alter session set "_SWRF_TEST_ACTION"=53;


But be aware both methods can create a higher DETECTED_USAGES than weeks between FIRST_USAGE_DATE and LAST_USAGE_DATE.



For a proper mapping between entries in DBA_FEATURE_USAGE_STATISTICS and options/packs there is a script options_packs_usage_statistics.sql provided by My Oracle Support in Database Options/Management Packs Usage Reporting for Oracle Databases 11gR2 and 12c (Doc ID 1317265.1)

2015-10-10

12c datapatch can fail with manual out of place patching

datapatch can fail if doing manual out of place patching, when the new ORACLE_HOME is not cloned from previous one but a fresh installation. The problem is a one-of patch in the old ORACLE_HOME, which is included in something else (like a PSU) in the new ORACLE_HOME.
In my specific situation the old O_H had PSU 12.1.0.2.3 + some one-off + Patch:21355879.
This Patch is not needed anymore in my new O_H (12.1.0.2.4 + some other one-off) - I checked on My Oracle Support - Patches & Updates:

So no need (and no simple way) to add this patch to the new O_H at all.

But when running datapatch within the new O_H, it throws this error and refuses to continue:
Error: prereq checks failed!
  patch 21355879: rollback script /appl/oracle/product/rdbms_121024Jc/sqlpatch/21355879/19095143/21355879_rollback.sql does not exist
Prereq check failed, exiting without installing any patches.

It's obvious the patch  21355879 is not installed within the new O_H. But somehow ( I did not check the exact internal function) datapatch checks the view DBA_REGISTRY_SQLPATCH and finds PATCH_ID:21355879. With a statement similar to
SELECT XMLSerialize(CONTENT dbms_sqlpatch.opatch_registry_state INDENT) from dual;
it finds the patchuid 19095143. The base table seems to be OPATCH_XINV_TAB.  (the other patches within 21355879 are not in this XML and doesn't require a rollback script).

So the database repository says a rollback script should be present, but it's not installed in the new O_H as Oracle calls it not required anymore.

The clean solution would be to patch every new O_H in the exact sequence of all it predecessors - this can be time consuming and also challenging when different branches with conflicting one-offs needs to be merged - even all these conflicting one-offs are merged in a later PSU and not needed anymore.

We decided to do the easier way and just copy all patch directories from old onto new O_H.

12c datapatch - take care of parallel patching

datapatch is a nice new feature in recent Oracle database installations. It helps to ensure the databases objects match the binaries after any kind of patching and so avoid situations which can be an operational and support nightmare - and very hard to identify.

Problem


Unfortunately it has some drawbacks as well.
One of those I hit recently when running datapatch on 2 instances which uses the same ORACLE_HOME.
At some time in it's progress datapatch uses dbms_qopatch.get_opatch_lsinventory, which uses the external table SYS.OPATCH_XML_INV. This has the preprocessor $ORACLE_HOME/QOPatch/qopiprep.bat. This script executes
$ORACLE_HOME/OPatch/opatch lsinventory -xml  $ORACLE_HOME/QOpatch/xml_file.xml -retry 0 -invPtrLoc $ORACLE_HOME/oraInst.loc >> $ORACLE_HOME/QOpatch/stout.txt
`echo "UIJSVTBOEIZBEFFQBL" >> $ORACLE_HOME/QOpatch/xml_file.xml`
echo `cat $ORACLE_HOME/QOpatch/xml_file.xml`
rm $ORACLE_HOME/QOpatch/xml_file.xml
rm $ORACLE_HOME/QOpatch/stout.txt
Maybe you see already what will happen when 2 different instances execute this step at the same time:
2 different opatch lsinventory -xml instances are writing into the same file. A very efficient was to corrupt the xml file, as they write to the same file $ORACLEHOME/QPatch/xml_file.xml

in one db I got this error:
Determining current state...DBD::Oracle::st execute failed: ORA-20001: Latest xml inventory is not loaded into table
ORA-06512: at "SYS.DBMS_QOPATCH", line 1937
ORA-06512: at "SYS.DBMS_QOPATCH", line 1259 (DBD ERROR: error possibly near <*> indicator at char 143 in 'SELECT description, startup_mode
               FROM XMLTable('/InventoryInstance/patches/patch[patchID=21573304]'
                    PASSING <*>dbms_qopatch.get_opatch_lsinventory
                    COLUMNS description VARCHAR2(100) PATH 'patchDescription',
                            startup_mode VARCHAR2(7) PATH 'sqlPatchDatabaseStartupMode')') [for Statement "SELECT description, startup_mode
               FROM XMLTable('/InventoryInstance/patches/patch[patchID=21573304]'
                    PASSING dbms_qopatch.get_opatch_lsinventory
                    COLUMNS description VARCHAR2(100) PATH 'patchDescription',
                            startup_mode VARCHAR2(7) PATH 'sqlPatchDatabaseStartupMode')"] at /appl/oracle/product/rdbms_121024Jc/sqlpatch/sqlpatch.pm line 1368, <LOGFILE> line 73.

in the other
verify_queryable_inventory returned ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00229: input source is empty
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1

Queryable inventory could not determine the current opatch status.
Execute 'select dbms_sqlpatch.verify_queryable_inventory from dual'

But there might be different errors possible as well - all based on the concurrency issue here.

Workaround

In our environment the it's quite simple to workaround such issues: We jave scripts which do all the steps during patching, so the "apply datapatch" module just needs a small extension which ckecks for a "latch" (existence of a file in our case) and only continues if it can grab this file. Otherwise it sleeps for a short time.

Solution

Oracle could easily use a filename like  $ORACLEHOME/QPatch/xml_file.$$.xml instead. I'm not sure if it's worth the effort to fight through the perimeters in MOS.

Update

(2015-12-28)
I found 12.1:Parallel Execution Of Datapatch during Patching or Manual upgrade fails with error " Queryable inventory could not determine the current opatch status " (Doc ID 2054286.1) today which describes exactly this behavior.
Patch:19215058 solves the issue by implementing
DBSID=$ORACLE_SID
...
$ORACLE_HOME/QOpatch/xml_file_$DBSID.xml
...

2015-06-23

SQL Plan Directives and result cache affects performance

In my current company we are preparing a migration of our billing application to a new version. During this migration there are the ordinary changes of infrastructure as well. Application servers from HPUX to Linux, database servers from RH5 to RH6, different storage subsystem, Oracle from 10.2 to 12c, different application partitioning, and so on ...
At least from the management perspective the expectation is clear: everything is shiny new and costed a lot of money. So it must be faster than before.

In this case the first test of a billrun was not faster. Our application mate contacted the DBA team as it seems to be significantly slower than on the [old env]

As you can see in the graph most sessions spent their time in wait class Other. It's the wait latch free - so it's a serialization issue. But the latch was Result Cache: RC Latch and it was (at least for some sessions) in exclusive mode, as a shared mode only would not cause the sessions to wait.
The DB has set RESULT_CACHE_MODE = MANUAL and the statements affected where like (slightly simplified)
select distinct ID 
from instance_history 
where base__id = 123456 
  and status_code = 3 
  and start_date >= to_date('20150515', 'yyyymmdd')     
  and start_date <= to_date('20150614', 'yyyymmdd')
As there is no RESULT_CACHE hint MODE is MANUAL and nothing is set in the sessions as well, it was not obvious why and where the latch is required at all. A quick check in ASH showed all sessions with this wait had IN_PARSE=Y.  It seems to be strange to have the result cache involved in parsing of a schema in exclusive mode, as this is only required to enter values to the result cache, and the statement is not executed yet.
But sometimes there is a statement executed in PARSE state: There are recursive SQLs fired by the optimizer to get more informations and deliver better plans. In this case it's dynamic sampling.
In the DB the parameter optimizer_dynamic_sampling is 2. And the table had statistics. The optimizer should not do any dynamic sampling based on this parameter.
But a new 12c feature initiates dynamic sampling regardless the DB settings: SQL Plan Directives. Here the SPD told the optimizer the stats on table instance_history might not be enough and it should do some dynamic sampling there as well.
By some luck I attended a presentation from Christian Antognini about Adaptive Dynamic Sampling some days ago. There he mentioned in 12.1.0.2 the optimizer uses optimizer_dynamic_sampling level 11 regardless th spfile settings.  Even worse, it lies about this in the Note section of explain plan.

With all these informations at hand the quick summary at that time was:
A lot of parse creates a huge amount of (identical) dynamic sampling statements. Those try to utilize the result cache by the hint RESULT_CACHE(SNAPSHOT=3600). As the result cache was configured very small, there were few hits but many misses - and all those misses tried to add their results to the cache. Other sampling statements had to wait for that. And as totally different statements are run at the same time, those wiped out the cache again and again.

As a first reaction we increased the result cache in the database.It looks much better now. latch free is down to 16% (CPU:50%, db file sequential read: 25%). But still sometimes the throughput drops due to serialization.  Of course we could again increase the result cache. But we decided another approach.

Back to the first issue, we have many statements in PARSE. As all of these statements had the same execution plan, there is no need for parsing the statement again and again. But it has literals instead of bind variables.
One suggestion was to set cursor_sharing=force. We could do this on instance-level or via login-trigger for a specific user/host/program. But still I prefer to have a most specific solution with least side effects.
So we asked the colleague responsible for the application tests if the statement could be changed to use bind variables. And to my big surprise it was possible without a big issue!

Now all the latch free waits are gone. The topmost "latch"-event is latch: cache buffer chains.

During these investigations I learned a lot about SQL Plan Directives, dynamic sampling and even result cache latches. It shows it's still true in 12c to avoid unnecessary parses - for one more reason now.

I have to thank Chris Antognini, Franck Pachot, Martin Klier and Stefan Koehler for their blogs, presentations and fast support via twitter.


Update (2016-07-21): In the meantime Oracle created a Note about this behavior: High "Latch Free" Waits with Contention on 'Result Cache: RC Latch' when RESULT_CACHE_MODE = MANUAL on Oracle 12c (Doc ID 2002089.1). There it is called an expected behavior, but the Solution is to disable Automatic Dynamic Statistics using
alter system set "_optimizer_ads_use_result_cache" = FALSE;
Thank you Laurent Leturgez for sharing:

2015-05-11

Poor mans getTracefile

As you might know already, I'm a big fan of Oracle Traces. Consequently I'm a fan of Method-R tools to work with these files as well.
A very important part in the chain of tools is Method R Trace - an extension to SQL Developer - as it speeds up developers a lot: When they generate a tracefile, they can access it immediately and do not need to ask and wait for a DBA to get and provide the file. On the other side, I as a lazy dba want developers to serve themselves.
Therein Method R Trace specializes.
Beside the functionality to trace a current statement/script, in Version 2.0.0.43 of Method R Trace There was another functionality (called feature 2) to list all tracefiles

 and download them.

Unfortunately this functionality disappeared in Version 3.0.0.1 as I mentioned earlier.



As I really like that feature I implemented a poor mans getTracefile functionality in SQL Developer 4.1 with a mixture of Method R Trace Installation Script from version 2.0.0.43 and 3.0.0.1(+patch).



The outcome is not so nice looking or integrated, but it provides the general functionality.
You can list all the tracefiles in a User Defined Report:



When you click a Tracefile the child report gets populated with a bfile locator associated with the specific physical LOB file.


Here SQL Developer is very kind to provide a context menue for this bfile locator.




And you can decide to store the file locally or view it directly. There is no fancy integration into Method R Trace like tagging, filter or whatever, so you must process the file manually.



To get this functionality an extension to the MRTRACE schema is required, and of course the user defined Report must be installed in SQL Developer.

For a proper MRTRACE schema you need both installation scripts from Method R Trace 3.0.0.1 and 2.0.0.43. First install the 3.0.0.1 script (+patch). Afterwards install only the level two feature set objects from 2.0.0.43.

Additional grants are required as well:
grant select on dba_directories to mrtrace;

And as these objects, grants and synonyms needs to be created. I create them for user MRTRACE.

/* 0.3 */
/* 20150511 - bergerma - add full schema reference (MRTRACE)                */
/*                       as MRTRACE lacks create session and has NOPASSWORD */
/*                       add JAVA_GRANTS as trace directories changed       */
/* requires MrTrace 2.0.0.43 objects! */
/* http://berxblog.blogspot.com/2015/05/poor-mans-gettracefile.html for details */

connect SYS
grant select on dba_directories to mrtrace; -- <- make sure this user is correct

connect SYSTEM/:system_pwd

create or replace TYPE MRTRACE.TRACEFILE AS OBJECT (
  trace_path varchar2(200),
  trace_name varchar2(200),
  filesize  NUMBER(15,0),
  filedate  date
);
/

create or replace TYPE MRTRACE.TRACEFILES IS TABLE OF  MRTRACE.TRACEFILE;
/

CREATE OR REPLACE 
PACKAGE MRTRACE.MRTRACE_BX AS 

/* ***************************************************************************
* berx - 20150507 - initial release - for testers only
* see http://berxblog.blogspot.com/2015/05/poor-mans-gettracefile.html 
*   for details
*************************************************************************** */

function get_version return varchar2;

function get_tracefiles_java  return TRACEFILES pipelined;

function get_bfile(file_name IN varchar2, directory_name IN varchar2)
return bfile;

END MRTRACE_BX;
/

CREATE OR REPLACE
PACKAGE BODY MRTRACE.MRTRACE_BX AS

/* ***************************************************************************
* berx - 20150507 - initial release - for testers only
* berx - 20150508 - 0.2 - minor adaptions
* berx - 20150511 - 0.3 - add full schema reference
*
* see http://berxblog.blogspot.com/2015/05/poor-mans-gettracefile.html 
*   for details
* requirements
*   GRANT SELECT ON DBMS_DIRECTORIES TO :MRTRACE_USER
*************************************************************************** */

FUNCTION get_version
  RETURN VARCHAR2
AS
 v_myversion varchar2(10) := '0.3';
 v_required_mrtrace  varchar2(20) := '2.0.0.43';
 v_actual_mrtrace    varchar2(20);
 v_warning_string1   varchar2(60) := ' - MRTrace_bx requires MRTRACE level two feature set ';
 v_warning_string2   varchar2(30) := ' but the version is: ';
BEGIN
   v_actual_mrtrace := MRTRACE.get_version;
  if v_actual_mrtrace = v_required_mrtrace then RETURN v_myversion;
  else RETURN v_myversion || v_warning_string1 || v_required_mrtrace || 
              v_warning_string2 || v_actual_mrtrace;
  END IF;
END get_version;


FUNCTION get_tracefiles_java
  RETURN TRACEFILES pipelined
AS
  VRETURNARRAY SimpleStringArrayType;
  v_cnt NUMBER := 0;
BEGIN
  MRTRACE.GET_TRACEFILES_JAVA(vReturnArray => VRETURNARRAY);
  v_cnt:=VRETURNARRAY.count;
  FOR i IN VRETURNARRAY.first..VRETURNARRAY.last
  LOOP
    pipe row( TRACEFILE( SUBSTR(VRETURNARRAY(i),1,instr(VRETURNARRAY(i), '|', 1, 1)-1)  -- PATH
            , SUBSTR(VRETURNARRAY(i),
                     instr(VRETURNARRAY(i), '|', 1, 1) + 1, 
                     instr(VRETURNARRAY(i), '|', 1, 2)-instr(VRETURNARRAY(i), '|', 1, 1)-1) -- FILENAME
            , to_number( SUBSTR(VRETURNARRAY(i), 
                                instr(VRETURNARRAY(i), '|', 1, 2) +1, 
                                instr(VRETURNARRAY(i), '|', 1, 3)-instr(VRETURNARRAY(i), '|', 1, 2)-1) 
                       ) -- SIZE
            , (TO_DATE('19700101000000', 'YYYYMMDDHH24MISS') + 
               to_number( SUBSTR(VRETURNARRAY(i), 
                                 instr(VRETURNARRAY(i), '|', 1, 3)+1, 
                                 LENGTH(VRETURNARRAY(i))-instr(VRETURNARRAY(i), '|', 1, 3))) / 86400000
              ) 
          ) ); -- pipe row ... 
  END LOOP;
  RETURN;
END get_tracefiles_java;

FUNCTION get_bfile(file_name IN varchar2, directory_name IN varchar2)
  RETURN bfile
AS
 v_path    VARCHAR2(200);
  v_dirname  VARCHAR2(100) := 'METHODR_UDUMP_1';
  v_bfile BFILE;
BEGIN
  v_path := directory_name;
  BEGIN
   select min(dir.DIRECTORY_NAME) into v_dirname 
   from dba_directories dir 
   where dir.directory_path = v_path 
     and dir.directory_name like 'METHODR_UDUMP%';
  EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line (
         'Error in get_bfile where raised:');
      DBMS_OUTPUT.put_line (
         DBMS_UTILITY.format_error_backtrace);
      RAISE;
  END;
  
  v_bfile := bfilename(v_dirname,file_name);
  RETURN v_bfile;
END get_bfile;

END MRTRACE_BX;
/

DECLARE -- JAVA_GRANTS
  v_path   varchar2(200); 
  c_path sys_refcursor; 
BEGIN
open c_path for 'select DIRECTORY_PATH from dba_directories where directory_name like ''METHODR_%''';
      loop
        fetch c_path into v_path;
        exit when c_path%notfound;
        if ( v_path is null )then
          raise_application_error(-20009, 'Could not get the value of the "METHODR_" directory from dba_directories.', false);
        end if;
        execute immediate 'BEGIN DBMS_JAVA.GRANT_PERMISSION(''MRTRACE'',' || '''SYS:java.io.FilePermission'',''' || v_path || ''', ''read'' ); END;';
        execute immediate 'BEGIN DBMS_JAVA.GRANT_PERMISSION(''MRTRACE'',' || '''SYS:java.io.FilePermission'',''' || v_path || '/-'', ''read'' ); END;';        
      end loop;
close c_path;
END; 

grant execute on MRTRACE.MRTRACE_BX to PUBLIC;
create public synonym mrtrace_bx for mrtrace.mrtrace_bx;

At last this report is required in SQL Developer:

<?xml version="1.0" encoding="UTF-8" ?>
<displays>
<display id="04e5e018-014a-1000-8001-ac193119805b" type="" style="Table" enable="true">
<name><![CDATA[get tracefiles]]></name>
<description><![CDATA[create a list of tracefiles in Instances UDUMP directory and makes specific files available
it's based on Method-R Method R Trace functionality (version 2.0.0.43 needed) with an extension MrTrace_bx ]]></description>
<tooltip><![CDATA[create a list of tracefiles in Instances UDUMP directory and makes specific files available ]]></tooltip>
<drillclass><![CDATA[null]]></drillclass>
<CustomValues>
<TYPE>horizontal</TYPE>
</CustomValues>
<query>
<sql><![CDATA[select TRACE_NAME ,
FILESIZE ,
FILEDATE ,
TRACE_PATH 
from table(mrtrace_bx.get_tracefiles_java)]]></sql>
</query>
<pdf version="VERSION_1_7" compression="CONTENT">
<docproperty title="-" author="-" subject="-" keywords="-" />
<cell toppadding="2" bottompadding="2" leftpadding="2" rightpadding="2" horizontalalign="LEFT" verticalalign="TOP" wrap="true" />
<column>
<heading font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="FIRST_PAGE" />
<footing font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="NONE" />
<blob blob="EMBED" zip="false" />
</column>
<table font="Agency FB" size="10" style="NORMAL" color="-16777216" userowshading="false" oddrowshading="-1" evenrowshading="-1" showborders="true" spacingbefore="12" spacingafter="12" horizontalalign="LEFT" />
<header enable="false" generatedate="false">
<data>

null                                                               </data>
</header>
<footer enable="false" generatedate="false">
<data value="null" />
</footer>
<security enable="false" useopenpassword="false" openpassword="null" encryption="EXCLUDE_METADATA">
<permission enable="false" permissionpassword="null" allowcopying="true" allowprinting="true" allowupdating="false" allowaccessdevices="true" />
</security>
<pagesetup papersize="LETTER" orientation="1" measurement="in" margintop="1.0" marginbottom="1.0" marginleft="1.0" marginright="1.0" />
</pdf>
<display id="null" type="" style="Table" enable="true">
<name><![CDATA[get tracefile]]></name>
<description><![CDATA[access the specific tracefile ]]></description>
<tooltip><![CDATA[Specific tracefile from list above ]]></tooltip>
<drillclass><![CDATA[null]]></drillclass>
<CustomValues>
<TYPE>horizontal</TYPE>
</CustomValues>
<query>
<sql><![CDATA[select mrtrace_bx.get_bfile(:TRACE_NAME, :TRACE_PATH) as " Tracefile "
from dual]]></sql>
<binds>
<bind id="TRACE_NAME">
<prompt><![CDATA[TRACE_NAME]]></prompt>
<tooltip><![CDATA[TRACE_NAME]]></tooltip>
<value><![CDATA[NULL_VALUE]]></value>
<bracket><![CDATA[null]]></bracket>
</bind>
<bind id="TRACE_PATH">
<prompt><![CDATA[TRACE_PATH]]></prompt>
<tooltip><![CDATA[TRACE_PATH]]></tooltip>
<value><![CDATA[NULL_VALUE]]></value>
<bracket><![CDATA[null]]></bracket>
</bind>
</binds>
</query>
<pdf version="VERSION_1_7" compression="CONTENT">
<docproperty title="-" author="-" subject="-" keywords="-" />
<cell toppadding="2" bottompadding="2" leftpadding="2" rightpadding="2" horizontalalign="LEFT" verticalalign="TOP" wrap="true" />
<column>
<heading font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="FIRST_PAGE" />
<footing font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="NONE" />
<blob blob="EMBED" zip="false" />
</column>
<table font="null" size="10" style="NORMAL" color="-16777216" userowshading="false" oddrowshading="-1" evenrowshading="-1" showborders="true" spacingbefore="12" spacingafter="12" horizontalalign="LEFT" />
<header enable="false" generatedate="false">
<data>

null          </data>
</header>
<footer enable="false" generatedate="false">
<data value="null" />
</footer>
<security enable="false" useopenpassword="false" openpassword="null" encryption="EXCLUDE_METADATA">
<permission enable="false" permissionpassword="null" allowcopying="true" allowprinting="true" allowupdating="false" allowaccessdevices="false" />
</security>
<pagesetup papersize="LETTER" orientation="1" measurement="in" margintop="1.0" marginbottom="1.0" marginleft="1.0" marginright="1.0" />
</pdf>
</display>
</display>
</displays>


If you see any issues with these script and report please tell me, I only had limited possibilities to test them.

2015-05-06

SQL Developer 4.1 with Method R Trace 3

These days SQL Developer 4.1 GA was released. If you use SQL Developer, I'd recommend an update.

Nearly at the same time Method R Trace 3.0.0.1 extension for SQL Developer was released as well. Especially for developers that's a very important news - at least if they concern about performance. Method R Trace
A new Version of this extension is needed as SQL Developer changed it's extension framework at the upgrade from version 3 to 4. So I'm very glad Method R took the effort to upgrade Method R Trace. Because of this upgrade, the feature of the current release is limited, as they described in the announcement.
I follow their argumentation it's better to release Method R Trace with feature 1 right now and not wait some more months until feature 1 and 2 are released.

But this decision leads to some consequences: Feature 1 and 2 need different objects installed in the database. And as with version 3.0.0.1 only feature 1 is released, all the objects for feature 2 (it's called level two feature set object in the 2.0.0.43 installation script) is not released. As I did not find the old extension anymore available at Method R website, I just can advise to preserve a copy in case you need some of the level two feature set objects for any purpose.

One more little thing in case you use Method R Trace 3.0.0.1 on a RAC: in the installation script replace
open c_gvparameter for 'select i.inst_id, v.value 
from gv$instance i, v$diag_info v 
where v.name = ''Diag Trace''';
with
open c_gvparameter for 'select i.inst_id, v.value 
from gv$instance i, gv$diag_info v 
where i.inst_id = v.inst_id 
  and v.name = ''Diag Trace''';

to ensure you can access the local tracefiles on all instances.

2015-01-08

ORADEBUG DOC 12.1.0.2

this is just an online docu of ORADEBUG DOC in 12.1.0.2.
The general comments from Tanel Poder apply to this version as well.




SQL> oradebug doc

Internal Documentation
**********************

  EVENT                           Help on events (syntax, event list, ...)
  COMPONENT       [<comp_name>]   List all components or describe <comp_name>

ORADEBUG DOC EVENT

SQL> oradebug doc event

Event Help:
***********

  Formal Event Syntax
  --------------------
    <event_spec>   ::= '<event_id> [<event_scope>]
                                   [<event_filter_list>]
                                   [<event_parameters>]
                                   [<action_list>]
                                   [off]'

    <event_id>     ::= <event_name | number>[<target_parameters>]

    <event_scope>  ::= [<scope_name>: scope_parameters]

    <event_filter> ::= {<filter_name>: filter_parameters}

    <action>       ::= <action_name>(action_parameters)

    <action_parameters> ::= <parameter_name> = [<value>|<action>][, ]

    <*_parameters> ::= <parameter_name> = <value>[, ]


  Some Examples
  -------------
    * Set event 10235 level 1:
      alter session set events '10235';

    * Set events SQL_TRACE (a.k.a. 10046) level 1:
      alter session set events 'sql_trace';

    * Turn off event SQL_TRACE:
      alter session set events 'sql_trace off';

    * Set events SQL_TRACE with parameter <plan_stat> set to 'never'
      and parameter <wait> set to 'true':
      alter session set events 'sql_trace wait=true, plan_stat=never';

    * Trace in-memory the SQL_MONITOR component (the target) and all its
      sub-components at level high. Get high resolution time for each
      trace:
      alter session set events 'trace[sql_mon.*] memory=high,
                                                 get_time=highres';

    * On-disk trace PX servers p000 and p005 for components 'sql_mon'
      and 'sql_optimizer' (including sub-components) at level highest:
      alter system set events 'trace[sql_mon | sql_optimizer.*]
                            {process: pname = p000 | process: pname=p005}';

    * Same as above but only when SQL id '7ujay4u33g337' is executed:
      alter system set events 'trace[sql_mon | sql_optimizer.*]
                                    [sql: 7ujay4u33g337]
                            {process: pname = p000 | process: pname=p005}';

    * Execute an action immediatly by using 'immediate' for the event
      name:
      alter session set events 'immediate eventdump(system)'

    * Create an incident labeled 'table_missing' when external error
      942 is signaled by process id 14534:
      alter session set events '942 {process: 14534}
                                    incident(table_missing)';


  Notes
  -----
    * Implicit parameter level is 1 by default
      e.g. '10053' is same as '10053 level 1'

    * Event target (see [<target_parameters>] construct) is only
      supported by specific events like the TRACE[] event

    * <event_scope> and/or <event_filter> are constructs
      that can be used for any event

    * Same event can be set simultaneously for a different scope or
      target but not for different filters.

    * '|' character can be used to select multiple targets, scope or
      filters.

      E.g. 'sql_trace [sql: sql_id=g3yc1js3g2689 | sql_id=7ujay4u33g337]'

    * '=' sign is optional in <*_parameters>

      E.g. 'sql_trace level 12';

    * Like PL/SQL, no need to specify the parameter name for target,
      scope, filters and action. Resolution is done by position in
      that case:

      E.g. 'sql_trace [sql: g3yc1js3g2689 | 7ujay4u33g337]'


  Help sub-topics
  ---------------

    NAME    [<event_name>]      List all events or describe <event_name>
    SCOPE   [<scope_name>]      List all scopes or describe <scope_name>
    FILTER  [<filter_name>]     List all filters or describe <filter_name>
    ACTION  [<action_name>]     List all actions or describe <action_name>


SQL> spool off


ORADEBUG DOC EVENT NAME

SQL> oradebug doc event name

Events in library DIAG:
------------------------------
trace[]              Main event to control UTS tracing
disable_dde_action[] Event used by DDE to disable actions
ams_trace[]          Event to dump ams performance trace records
ams_rowsrc_trace[]   Event to dump ams row source tracing
sweep_verification   Event to enable sweep file verification
enable_xml_inc_staging Event to enable xml incident staging format
dbg[]                Event to hook dbgtDbg logging statements

Events in library RDBMS:
------------------------------
wait_event[]         event to control wait event post-wakeup actions
alert_text           event for textual alerts
trace_recursive      event to force tracing recursive SQL statements
clientid_overwrite   event to overwrite client_identifier when client_info is set
sql_monitor          event to force monitoring SQL statements
sql_monitor_test     event to test SQL monitoring
eventsync_tac        Event posted from events syncing tac
sql_trace            event for sql trace
pmon_startup         startup of pmon process
background_startup   startup of background processes
db_open_begin        start of db open operation
test_gvtf            test GV$() Table Tunction
fault                Event used to inject fault in RDBMS kernel
gcr_systest          gcr_systest
em_express           EM Express debug event
emx_control          event to control em express
emx_test_control     event to control em express testing
awrdiag[]            AWR Diagnostic Event
msgq_trace           event to control msgq tracing
ipclw_trace          event to control ipclw tracing
kbc_fault            event to control container fault injection
asm_corruption_trace event to control ASM corruption tracing
kxdrs_sim            debug event to simulate certain conditions in kxdrs layer

kcfio_debug          debug event to debug kcfio based on event level

krbabrstat_fault     event to control krbabrstat fault injection
periodic_dump[]      event for periodically dumping

Events in library GENERIC:
------------------------------
kg_event[]           Support old error number events (use err# for short)

Events in library CLIENT:
------------------------------
oci_trace            event for oci trace

Events in library LIBCELL:
------------------------------
libcell_stat         libcell statistics level specification
cellclnt_skgxp_trc_ops Controls to trace SKGXP operations
cellclnt_ossnet_trc  Controls to trace IP affinity in ossnet
cellclnt_high_lat_ops Control to trace High-latency I/O operations
diskmon_sim_ops[]    Diskmon simulation events
cellclnt_read_outlier_limit Control to trace read I/O outliers
cellclnt_write_outlier_limit Control to trace write I/O outliers
cellclnt_lgwrite_outlier_limit Control to trace log write I/O outliers
cellclnt_sparse_mode Mode of how to handle sparse buffers

Events in library ADVCMP:
------------------------------
arch_comp_level[]    arch_comp_level[<ulevel, 1-7>]
ccmp_debug           columnar compression debug event
inmemory_nobasic     disable KDZCF_IMC_BASIC implementation
inmemory_nohybrid    disable KDZCF_IMC_HYBRID implementation
ccmp_align           columnar compression enable alignment
ccmp_countstar       columnar compression enable count(*) optimization
ccmp_dumpunaligned   columnar compression dump dbas of unaligned CUs
ccmp_rbtree          columnar compression switch back to rb tree
inmemory_force_ccl   inmemory force column compression levels
inmemory_imcu[]      inmemory_imcu[<ulevel=  nocomp|dml|query_low|query_high|capacity_low|capacity_high>]

Events in library PLSQL:
------------------------------
plsql_event[]        Support PL/SQL error number events


SQL> spool off


ORADEBUG DOC EVENT NAME <event_name>
SQL> ORADEBUG DOC EVENT NAME trace

trace: Main event to control UTS tracing

Usage
-------
trace [ component       <string>[0] ]
   disk            < default | lowest | low | medium | high | highest | disable >,
   memory          < default | lowest | low | medium | high | highest | disable >,
   get_time        < disable | default | seq | highres | seq_highres >,
   get_stack       < disable | default | force >,
   operation       <string>[32],
   function        <string>[32],
   file            <string>[32],
   line            <ub4>


SQL> spool off


SQL> ORADEBUG DOC EVENT NAME disable_dde_action

disable_dde_action: Event used by DDE to disable actions

Usage
-------
disable_dde_action [ action_name     <string>[100] ]
   facility        <string>[20],
   error           <ub4>


SQL> spool off


SQL> ORADEBUG DOC EVENT NAME ams_trace

ams_trace: Event to dump ams performance trace records

Usage
-------
ams_trace [ relation        <string>[30] ]

SQL> spool off


SQL> ORADEBUG DOC EVENT NAME ams_rowsrc_trace

ams_rowsrc_trace: Event to dump ams row source tracing

Usage
-------
ams_rowsrc_trace [ relation        <string>[30] ]
   level           <ub4>


SQL> spool off


SQL> ORADEBUG DOC EVENT NAME dbg

dbg: Event to hook dbgtDbg logging statements

Usage
-------
dbg [ component       <string>[0] ]
   operation       <string>[32],
   function        <string>[32],
   file            <string>[32],
   line            <ub4>


SQL> spool off


SQL> ORADEBUG DOC EVENT NAME wait_event

wait_event: event to control wait event post-wakeup actions

Usage
-------
wait_event [ name            <string>[64] ]


SQL> ORADEBUG DOC EVENT NAME awrdiag

awrdiag: AWR Diagnostic Event

Usage
-------
awrdiag [ name            <string>[64] ]
   level           <ub4>,
   str1            <string>[256],
   str2            <string>[256],
   num1            <ub8>,
   num2            <ub8>


SQL> spool off


SQL> ORADEBUG DOC EVENT NAME periodic_dump

periodic_dump: event for periodically dumping

Usage
-------
periodic_dump [ name            <string>[64] ]
   level           <ub4>,
   seconds         <ub4>,
   lifetime        <ub4>


SQL> spool off


SQL> ORADEBUG DOC EVENT NAME kg_event

kg_event: Support old error number events (use err# for short)

Usage
-------
kg_event [ errno           <ub4> ]
   level           <ub4>,
   lifetime        <ub4>,
   armcount        <ub4>,
   traceinc        <ub4>,
   forever         <ub4>


SQL> spool off


SQL> ORADEBUG DOC EVENT NAME diskmon_sim_ops
Error: "diskmon_sim_ops" not a known event/library name
Use <event_name>, <library_name> or <library_name>.<event_name>

SQL> spool off


SQL> ORADEBUG DOC EVENT NAME arch_comp_level

arch_comp_level: arch_comp_level[<ulevel, 1-7>]

Usage
-------
arch_comp_level [ ulevel          <ub4> ]
   ilevel          <ub8>,
   sortcols        <ub4>,
   cusize          <ub4>,
   analyze_amt     <ub4>,
   analyze_rows    <ub4>,
   analyze_minrows <ub4>,
   mincusize       <ub4>,
   maxcusize       <ub4>,
   mincurows       <ub4>,
   align           <ub4>,
   rowlocks        <ub4>,
   maxcuhpctfree   <ub4>,
   guarantee_rll   <ub4>,
   cla_stride      <ub4>,
   dict_cla_stride <ub4>


SQL> spool off


SQL> ORADEBUG DOC EVENT NAME inmemory_imcu

inmemory_imcu: inmemory_imcu[<ulevel=  nocomp|dml|query_low|query_high|capacity_low|capacity_high>]

Usage
-------
inmemory_imcu [ ulevel       < invalid | nocomp | dml | query_low | query_high | capacity_low | capacity_high > ]
   target_rows     <ub4>,
   source_maxbytes <ub4>


SQL> spool off


SQL> ORADEBUG DOC EVENT NAME plsql_event

plsql_event: Support PL/SQL error number events

Usage
-------
plsql_event [ errno           <ub4> ]

SQL> spool off



ORADEBUG DOC EVENT SCOPE

SQL> oradebug doc event scope

Event scopes in library RDBMS:
------------------------------
SQL[]                sql scope for RDBMS


SQL> spool off


ORADEBUG DOC EVENT SCOPE SQL
SQL> oradebug doc event scope sql

SQL: sql scope for RDBMS

Usage
-------
[SQL:  sql_id          <string>[20] ]


SQL> spool off



ORADEBUG DOC EVENT FILTER

SQL> ORADEBUG DOC EVENT FILTER

Event filters in library DIAG:
------------------------------
occurence            filter to implement counting for event checks
callstack            filter to only fire an event when a function is on the stack
eq                   filter to only fire an event when a == b
ne                   filter to only fire an event when a != b
gt                   filter to only fire an event when a > b
lt                   filter to only fire an event when a < b
ge                   filter to only fire an event when a >= b
le                   filter to only fire an event when a <= b
anybit               filter to only fire an event when (a & b) != 0
allbit               filter to only fire an event when (a & b) == b
nobit                filter to only fire an event when (a & b) == 0
bet                  filter to only fire an event when b <= a <= c
nbet                 filter to only fire an event when a < b or a > c
in                   filter to only fire an event when a is equal to any b .. p
nin                  filter to only fire an event when a is not equal to any b .. p
streq                filter to only fire an event when string s1 = s2 (up to <len> characters)
strne                filter to only fire an event when string s1 != s2 (up to <len> characters)
tag                  filter to only fire an event when a tag is set

Event filters in library RDBMS:
------------------------------
wait                 filter for specific wait parameters and wait duration
process              filter to set events only for a specific process
px                   filter to check identity of the process for fault injection

Event filters in library GENERIC:
------------------------------
errarg               filter to set error events only for a specific error argument


SQL> spool off



ORADEBUG DOC EVENT FILTER <filter_name>
SQL> ORADEBUG DOC EVENT FILTER occurence

occurence: filter to implement counting for event checks

Usage
-------
{occurence:  start_after     <ub4>,
             end_after       <ub4> }


SQL> spool off


SQL> ORADEBUG DOC EVENT FILTER callstack

callstack: filter to only fire an event when a function is on the stack

Usage
-------
{callstack:  fname           <string>[64],
             fprefix         <string>[64],
             maxdepth        <ub4> }


SQL> spool off


SQL> ORADEBUG DOC EVENT FILTER eq

eq: filter to only fire an event when a == b

Usage
-------
{eq:  a               <ub8>,
      b               <ub8> }


SQL>

SQL> ORADEBUG DOC EVENT FILTER ne

ne: filter to only fire an event when a != b

Usage
-------
{ne:  a               <ub8>,
      b               <ub8> }


SQL> spool off


SQL> ORADEBUG DOC EVENT FILTER gt

gt: filter to only fire an event when a > b

Usage
-------
{gt:  a               <ub8>,
      b               <ub8> }


SQL> spool off


SQL> ORADEBUG DOC EVENT FILTER lt

lt: filter to only fire an event when a < b

Usage
-------
{lt:  a               <ub8>,
      b               <ub8> }


SQL> spool off


SQL> ORADEBUG DOC EVENT FILTER ge

ge: filter to only fire an event when a >= b

Usage
-------
{ge:  a               <ub8>,
      b               <ub8> }


SQL> spool off


SQL> ORADEBUG DOC EVENT FILTER le

le: filter to only fire an event when a <= b

Usage
-------
{le:  a               <ub8>,
      b               <ub8> }


SQL> spool off


SQL> ORADEBUG DOC EVENT FILTER anybit

anybit: filter to only fire an event when (a & b) != 0

Usage
-------
{anybit:  a               <ub8>,
          b               <ub8> }


SQL> spool off


SQL> ORADEBUG DOC EVENT FILTER allbit

allbit: filter to only fire an event when (a & b) == b

Usage
-------
{allbit:  a               <ub8>,
          b               <ub8> }


SQL> spool off


SQL> ORADEBUG DOC EVENT FILTER nobit

nobit: filter to only fire an event when (a & b) == 0

Usage
-------
{nobit:  a               <ub8>,
         b               <ub8> }


SQL> spool off


SQL> ORADEBUG DOC EVENT FILTER bet

bet: filter to only fire an event when b <= a <= c

Usage
-------
{bet:  a               <ub8>,
       b               <ub8>,
       c               <ub8> }


SQL> spool off


SQL> ORADEBUG DOC EVENT FILTER nbet

nbet: filter to only fire an event when a < b or a > c

Usage
-------
{nbet:  a               <ub8>,
        b               <ub8>,
        c               <ub8> }


SQL> spool off


SQL> ORADEBUG DOC EVENT FILTER in

in: filter to only fire an event when a is equal to any b .. p

Usage
-------
{in:  a               <ub8>,
      b               <ub8>,
      c               <ub8>,
      d               <ub8>,
      e               <ub8>,
      f               <ub8>,
      g               <ub8>,
      h               <ub8>,
      i               <ub8>,
      j               <ub8>,
      k               <ub8>,
      l               <ub8>,
      m               <ub8>,
      n               <ub8>,
      o               <ub8>,
      p               <ub8> }


SQL> spool off


SQL> ORADEBUG DOC EVENT FILTER nin

nin: filter to only fire an event when a is not equal to any b .. p

Usage
-------
{nin:  a               <ub8>,
       b               <ub8>,
       c               <ub8>,
       d               <ub8>,
       e               <ub8>,
       f               <ub8>,
       g               <ub8>,
       h               <ub8>,
       i               <ub8>,
       j               <ub8>,
       k               <ub8>,
       l               <ub8>,
       m               <ub8>,
       n               <ub8>,
       o               <ub8>,
       p               <ub8> }


SQL> spool off


SQL> ORADEBUG DOC EVENT FILTER streq

streq: filter to only fire an event when string s1 = s2 (up to <len> characters)

Usage
-------
{streq:  s1              <string>[256],
         s2              <string>[256],
         len             <ub4> }


SQL> spool off


SQL> ORADEBUG DOC EVENT FILTER strne

strne: filter to only fire an event when string s1 != s2 (up to <len> characters)

Usage
-------
{strne:  s1              <string>[256],
         s2              <string>[256],
         len             <ub4> }


SQL> spool off


SQL> ORADEBUG DOC EVENT FILTER tag

tag: filter to only fire an event when a tag is set

Usage
-------
{tag:  tname           <string>[64] }


SQL> spool off


SQL> ORADEBUG DOC EVENT FILTER wait

wait: filter for specific wait parameters and wait duration

Usage
-------
{wait:  minwait         <ub8>,
        p1              <ub8>,
        p2              <ub8>,
        p3              <ub8>,
        _actual_wait_time <ub8> default 'evargn(pos=1)',
        _actual_wait_p1 <ub8> default 'evargn(pos=2)',
        _actual_wait_p2 <ub8> default 'evargn(pos=3)',
        _actual_wait_p3 <ub8> default 'evargn(pos=4)' }


SQL> spool off


SQL> ORADEBUG DOC EVENT FILTER process

process: filter to set events only for a specific process

Usage
-------
{process:  ospid           <string>[20],
           orapid          <ub4>,
           pname           <string>[20],
           con_id          <ub8> }


SQL> spool off


SQL> ORADEBUG DOC EVENT FILTER px

px: filter to check identity of the process for fault injection

Usage
-------
{px:  slave_set       <ub4>,
      slave_num       <ub4>,
      local_slave_num <ub4>,
      instance_id     <ub4>,
      dfo_number      <ub4>,
      oct             <ub4>,
      pxid            <ub4> }


SQL> spool off


SQL> ORADEBUG DOC EVENT FILTER errarg

errarg: filter to set error events only for a specific error argument

Usage
-------
{errarg:  arg1            <string>[50],
          arg2            <string>[50],
          arg3            <string>[50],
          arg4            <string>[50],
          arg5            <string>[50],
          arg6            <string>[50],
          arg7            <string>[50],
          arg8            <string>[50] }


SQL> spool off




ORADEBUG DOC EVENT ACTION

SQL> ORADEBUG DOC EVENT ACTION

Actions in library DIAG:
---------------------------
evfunc                - Get posting function name
evfile                - Get posting file name
evline                - Get posting file line number as ub8
evfmt                 - Get trace / log format string
evargc                - Get count of event check arguments as a ub8
evargn                - Get event check argument value as ub8
evargp                - Get event check argument value as void *
evargs               
  - Get event check argument as string, with optional format
errargs               - Get error argument as string
errargn               - Get error argument as ub8
errargp               - Get error argument as pointer
errargc               - Get count of error arguments as a ub8
sum                  
  - Compute a1 + a2 + ... + a15 as ub8 (zero if all NULL)
trace                
  - trace to disk; apply format to string arguments
 % is an argument placeholder
 \n and \t are supported. Use double \ as escape
sub                   - Compute a1 - a2 as ub8
add                   - Compute a1 + a2 as ub8
mod                   - Compute a1 modulo a2 as ub8
div                   - Compute a1 / a2 as ub8
mul                   - Compute a1 * a2 as ub8
incr                  - Increment ptr by offset
decr                  - Decrement ptr by offset
refn                 
  - Dereference ptr-to-number: *(ub<numsize>*)(((ub1*)<ptr>)) + <offset>)
refp                 
  - Dereference ptr-to-ptr: *(ub1**)(((ub1*)<ptr>)) + <offset>)
refs                 
  - Dereference ptr-to-string: *(oratext **)(((ub1*)<ptr>) + <offset>)
 Length is optional; NULL-terminated string is assumed
refsl                
  - Dereference ptr-to-string: *(oratext **)(((ub1*)<ptr>) + <offset>)
 with ptr-to-length: *(ub<lensize>*)(((ub1*)<ptr>) + <lenoffset>)
dumpFrameContext      - Dump Frame Context contents
dumpBuckets          
kgsfdmp              
dumpDiagCtx          
dumpDbgecPopLoc      
dumpDbgecMarks       
dumpGeneralConfiguration 
dumpADRLockTable     
shortstack           
  - get short stack (up to 256 characters)
showoffsets controls display of code offsets
skipframes can be used to overcome 256 char limit
dbgvci_action_signal_crash 

Actions in library RDBMS:
---------------------------
incident              - Create an Incident
sqlmon_dump           - SQL Monitor Dump SGA Action
varaddr               - Return address of a fixed PGA/SGA/UGA variable
username              - Return user log-in name
sqlid                 - Return current SQL Id in character format
flashfreeze          
oradebug              - debug process using ORADEBUG
debugger              - debug process using System Debugger
debug                
  - alias for 'debugger' - debug process using System Debugger
crash                 - crash process
kill_instance         - killing RDBMS instance
controlc_signal       - received 1013 signal
eventdump             - list events that are set in the group
kdlut_bucketdump_action 
kzxt_dump_action     
dumpKernelDiagState  
HMCHECK              (async)
DATA_BLOCK_INTEGRITY_CHECK (async)
CF_BLOCK_INTEGRITY_CHECK (async)
DB_STRUCTURE_INTEGRITY_CHECK (async)
REDO_INTEGRITY_CHECK (async)
TRANSACTION_INTEGRITY_CHECK (async)
SQL_TESTCASE_REC     (async)
SQL_TESTCASE_REC_DATA (async)
ORA_12751_DUMP       
sqladv_dump_dumpctx  
ORA_4030_DUMP        
  - dump summary of PGA memory usage, largest allocations
ORA_4036_DUMP         - dump summary of PGA memory usage
HNGDET_MEM_USAGE_DUMP_NOARGS  - dump hang detection memory usage
kcfis_action          - kcfis actions
exadata_dump_modvers  - Exadata dump module versions
QUERY_BLOCK_DUMP      - Debug action for dumping a qbcdef tree
dumpADVMState         - Dump contents of ADVM state
dumpASMState          - Dump contents of ASM state
ASM_CHECK_DG         (async) - Run check diskgroup
ASM_DUMP_KSTSS        - Dump KST Trace and System State
ASM_MOUNT_FAIL_CHECK (async)
ASM_DGFDM_CHECK_NO_DG_NAME (async)
ASM_SYNC_IO_FAIL_CHECK (async)
ASM_DG_FORCE_DISMOUNT_CHECK (async)
ASM_ALLOC_FAIL_CHECK (async)
ASM_ADD_DISK_CHECK   (async)
ASM_FILE_BUSY_CHECK  (async)
ASM_TOOMANYOFF_FAIL_CHECK (async)
ASM_INSUFFICIENT_DISKS_CHECK (async)
ASM_INSUFFICIENT_MEM_CHECK (async)
KJZN_ASYNC_SYSTEM_STATE (async)
KSI_GET_TRACE         - Get lmd0 traces for ksi issues
TRACE_BUFFER_ON       - Allocate trace output buffer for ksdwrf()
TRACE_BUFFER_OFF     
  - Flush and deallocate trace output buffer for ksdwrf()
LATCHES               - Dump Latches
XS_SESSION_STATE      - Dump XS session state
PROCESSSTATE          - Dump process state
SYSTEMSTATE           - Dump system state
INSTANTIATIONSTATE    - Dump instantiation state
CONTEXTAREA           - Dump cursor context area
HEAPDUMP             
  - Dump memory heap (1-PGA, 2-SGA, 4-UGA, +1024-Content)
POKE_LENGTH           - Set length before poking value
POKE_VALUE            - Poke a value into memory
POKE_VALUE0           - Poke 0 value into memory
GLOBAL_AREA          
  - Dump fixed global area(s) (1=PGA/2=SGA/3=UGA, add +8 for pointer content)
REALFREEDUMP          - Dump PGA real free memory allocator state
FLUSH_JAVA_POOL       - Flush Java pool
PGA_DETAIL_GET       
  - Ask process to publish PGA detail info (level is pid)
PGA_DETAIL_DUMP      
  - Dump PGA detail information for process (level is pid) 
PGA_DETAIL_CANCEL     - Free PGA detail request (level is pid)
PGA_SUMMARY           - Summary of PGA memory usage, largest allocations
MODIFIED_PARAMETERS   - Dump parameters modifed by session (level unused)
ERRORSTACK           
  - Dump state (ksedmp). Use INCIDENT action to create incident 
CALLSTACK             - Dump call stack (level > 1 to dump args)
RECORD_CALLSTACK     
  - Record or dump call stack, level = #frames (level += 1000000 go to trc)
BG_MESSAGES           - Dump routine for background messages
ENQUEUES             
  - Dump enqueues (level >=2 adds resources, >= 3 adds locks)
KSTDUMPCURPROC       
  - Dump current process trace buffer (1 for all events)
KSTDUMPALLPROCS      
  - Dump all processes trace buffers (1 for all events)
KSTDUMPALLPROCS_CLUSTER 
  - Dump all processes (cluster wide) trace buffers (1 for all events)
KSKDUMPTRACE          - Dumping KSK KST tracing (no level)
DBSCHEDULER           - Dump ressource manager state
LDAP_USER_DUMP        - Dump LDAP user mode
LDAP_KERNEL_DUMP      - Dump LDAP kernel mode
DUMP_ALL_OBJSTATS     - Dump database objects statistics
DUMPGLOBALDATA        - Rolling migration DUMP GLOBAL DATA
HANGANALYZE           - Hang analyze
HANGANALYZE_PROC      - Hang analyze current process
HANGANALYZE_GLOBAL    - Hang analyze system
HNGDET_MEM_USAGE_DUMP  - dump hang detection memory usage
GES_STATE             - Dump DML state
RACDUMP               - Dump RAC state
OCR                   - OCR client side tracing
CSS                   - CSS client side tracing
CRS                   - CRS client side tracing
SYSTEMSTATE_GLOBAL    - Perform cluster wide system state dump (via DIAG)
DUMP_ALL_COMP_GRANULE_ADDRS 
  - MMAN dump all granule addresses of all components (no level)
DUMP_ALL_COMP_GRANULES 
  - MMAN dump all granules of all components (1 for partial list)
DUMP_ALL_REQS        
  - MMAN dump all pending memory requests to alert log
DUMP_TRANSFER_OPS     - MMAN dump transfer and resize operations history
DUMP_ADV_SNAPSHOTS   
  - MMAN dump all snapshots of advisories (level unused)
CONTROLF              - DuMP control file info
FLUSH_CACHE          
  - Flush buffer cache without shuting down the instance
SET_AFN               - Set afn # for buffer flush (level = afn# )
SET_ISTEMPFILE       
  - Set istempfile for buffer flush (level = istempfile )
FLUSH_BUFFER          - Reuse block range without flushing entire cache
BUFFERS               - Dump all buffers in the buffer cache at level l
SET_TSN_P1           
  - Set tablespace # for buffer dump (level = ts# + 1)
BUFFER               
  - Dump all buffers for full relative dba <level> at lvl 10
BC_SANITY_CHECK      
  - Run buffer cache sanity check (level = 0xFF for full)
SET_NBLOCKS           - Set number of blocks for range reuse checks
CHECK_ROREUSE_SANITY  - Check range/object reuse sanity (level = ts#)
DUMP_PINNED_BUFFER_HISTORY 
  - kcb Dump pinned buffers history (level = # buffers)
REDOLOGS              - Dump all online logs according to the level
LOGHIST              
  - Dump the log history (1: dump earliest/latest entries, >1: dump most recent 2**level entries)
REDOHDR               - Dump redo log headers
LOCKS                 - Dump every lock element to the trace file
GC_ELEMENTS           - Dump every lock element to the trace file
FILE_HDRS             - Dump database file headers
FBINC                
  - Dump flashback logs of the current incarnation and all its ancestors.
FBHDR                 - Dump all the flashback logfile headers
FLASHBACK_GEN         - Dump flashback generation state
KTPR_DEBUG           
  - Parallel txn recovery (1: cleanup check, 2: dump ptr reco ctx, 3: dump recent smon runs)
DUMP_TEMP             - Dump temp space management state (no level)
DROP_SEGMENTS         - Drop unused temporary segments
TREEDUMP             
  - Dump an index tree rooted at dba BLOCKDBA (<level>)
KDLIDMP               - Dump 11glob inodes states (level = what to dump)
ROW_CACHE             - Dump all cache objects
LIBRARY_CACHE        
  - Dump the library cache (level > 65535 => level = obj @)
CURSORDUMP            - Dump session cursors
CURSOR_STATS          - Dump all statistics information for cursors
SHARED_SERVER_STATE   - Dump shared server state
LISTENER_REGISTRATION - Dump listener registration state
JAVAINFO              - Dump Oracle Java VM
KXFPCLEARSTATS        - Clear all Parallel Query messaging statistics
KXFPDUMPTRACE         - Dump Parallel Query in-memory traces
KXFXSLAVESTATE        - Dump PX slave state (1: uga; 2: current cursor state; 3: all cursors)
KXFXCURSORSTATE       - Dump PX slave cursor state
WORKAREATAB_DUMP      - Dump SQL Memory Manager workarea table
OBJECT_CACHE          - Dump the object cache
SAVEPOINTS            - Dump savepoints
RULESETDUMP           - Dump rule set
FAILOVER              - Set condition failover immediate
OLAP_DUMP             - Dump OLAP state
AWR_FLUSH_TABLE_ON    - Enable flush of table id <level> (ids in X$KEWRTB)
AWR_FLUSH_TABLE_OFF  
  - Disable flush of table id <level> (ids in X$KEWRTB)
ASHDUMP               - Dump ASH data (level = # of minutes)
ASHDUMPSECONDS        - Dump ASH data (level = # of seconds)
HM_FW_TRACE           - DIAG health monitor set tracing level
IR_FW_TRACE           - DIAG intelligent repair set/clear trace
GWM_TRACE             - Global Services Management set/clear trace
GWM_TEST              - Global Services Management set/clear GDS test
GLOBAL_BUFFER_DUMP    - Request global buffer dump (level 1 = TRUE)
DEAD_CLEANUP_STATE    - Dump dead processes and killed sessions
IMDB_PINNED_BUFFER_HISTORY 
  - Dump IMDB pinned buffer history (level = (dump_level << 16 | num_buffers))
HEAPDUMP_ADDR         - Heap dump by address routine (level > 1 dump content)
POKE_ADDRESS          - Poke specified address (level = value)
CURSORTRACE           - Trace cursor by hash value (hash value is address)
RULESETDUMP_ADDR      - Dump rule set by address
kewmdump              - Dump Metrics Metadata and Memory
con_id                - Return Container Id as UB8
DBGT_SPLIT_CSTSTRING 
DUMP_SWAP             - dump system memory and swap information
ALERT_SWAP            - issue alert message about system swap percentage
DUMP_PATCH            - dump patch information
dumpBucketsRdbms     

Actions in library GENERIC:
---------------------------
xdb_dump_buckets     
dumpKGERing           - Dump contents of KGE ring buffer
dumpKGEState          - Dump KGE state information for debugging

Actions in library CLIENT:
---------------------------
kpuActionDefault      - dump OCI data
kpuActionSignalCrash 
  - crash and produce a core dump (if supported and possible)
kpudpaActionDpapi     - DataPump dump action


SQL> spool off
ORADEBUG DOC EVENT ACTION <action_name>
You can get more details about some actions by running the doc command for the library.action:

SQL> ORADEBUG DOC EVENT ACTION RDBMS.query_block_dump

ORADEBUG DOC COMPONENT


SQL> ORADEBUG DOC COMPONENT


Components in library DIAG:
--------------------------
  diag_uts                     Unified Tracing Service (dbgt, dbga)
    uts_vw                     UTS viewer toolkit (dbgtp, dbgtn)
  diag_adr                     Automatic Diagnostic Repository (dbgr)
    ams_comp                   ADR Meta-data Repository (dbgrm)
    ame_comp                   ADR Export/Import Services (dbgre)
    ami_comp                   ADR Incident Meta-data Services (dbgri)
    diag_ads                   Diagnostic Directory and File Services (dbgrf, sdbgrf, sdbgrfu, sdbgrfb)
  diag_hm                      Diagnostic Health Monitor ((null))
  diag_ips                     Diagnostic Incident Packaging System ((null))
  diag_dde                     Diagnostic Data Extractor (dbge)
  diag_fmwk                    Diagnostic Framework (dbgc)
    diag_ilcts                 Diagnostic Inter-Library Compile-time Service (dbgf)
    diag_attr                  Diagnostic Attributes Management ((null))
    diag_comp                  Diagnostic Components Management ((null))
  diag_testp                   Diagnostic component test parent (dbgt)
    diag_testc1                Diagnostic component test child 1 ((null))
    diag_testc2                Diagnostic component test child 2 ((null))
  KGSD                         Kernel Generic Service Debugging (kgsd)
  diag_events                  Diagnostic Events (dbgd)
  diag_adl                     Diagnostic ARB Alert Log (dbgrl, dbgrlr)
  diag_vwk                     Diagnostic viewer toolkit (dbgv)
    diag_vwk_parser            Diagnostic viewer parser (dbgvp, dbgvl)
    diag_vwk_uts               Diagnostic viewer for UTS traces and files (dbgvf)
    diag_vwk_ams               Diagnostic viewer for AMS metadata (dbgvm)
    diag_vwk_ci                Diagnostic viewer for command line (dbgvci)
  kghsc                        KGHSC Compact Stream (kghsc)
  dbgxtk                       DBGXTK xml toolkit (dbgxtk)

Components in library RDBMS:
--------------------------
  SQL_Compiler                 SQL Compiler ((null))
    SQL_Parser                 SQL Parser (qcs)
    SQL_Semantic               SQL Semantic Analysis (kkm)
    SQL_Optimizer              SQL Optimizer ((null))
      SQL_Transform            SQL Transformation (kkq, vop, nso)
        SQL_MVRW               SQL Materialized View Rewrite ((null))
        SQL_VMerge             SQL View Merging (kkqvm)
        SQL_Virtual            SQL Virtual Column (qksvc, kkfi)
      SQL_APA                  SQL Access Path Analysis (apa)
      SQL_Costing              SQL Cost-based Analysis (kko, kke)
        SQL_Parallel_Optimization SQL Parallel Optimization (kkopq)
      SQL_Plan_Management      SQL Plan Managment (kkopm)
      SQL_Plan_Directive       SQL Plan Directive (qosd)
    SQL_Code_Generator         SQL Code Generator (qka, qkn, qke, kkfd, qkx)
      SQL_Parallel_Compilation SQL Parallel Compilation (kkfd)
      SQL_Expression_Analysis  SQL Expression Analysis (qke)
    MPGE                       MPGE (qksctx)
    ADS                        ADS (kkoads)
  SQL_Execution                SQL Execution (qer, qes, kx, qee)
    Parallel_Execution         Parallel Execution (qerpx, qertq, kxfr, kxfx, kxfq, kxfp)
      PX_Messaging             Parallel Execution Messaging (kxfp)
      PX_Group                 Parallel Execution Slave Group (kxfp)
      PX_Affinity              Parallel Affinity (ksxa)
      PX_Buffer                Parallel Execution Buffers (kxfpb)
      PX_Granule               Parallel Execution Granules (kxfr)
      PX_Control               Parallel Execution Control (kxfx)
      PX_Table_Queue           Parallel Execution Table Queues (kxfq)
      PX_Scheduler             Parallel Execution Scheduler (qerpx)
      PX_Queuing               Parallel Execution Queuing (kxfxq)
      PX_Blackbox              Parallel Execution Blackbox (kxf)
      PX_PTL                   Parallel Execution PTL (kxft)
      PX_Expr_Eval             Parallel Execution Expression Evaluation ((null))
      PX_Selector              Parallel Execution PX Selector (qerpsel)
      PX_Overhead              Parallel Execution Overhead (qerpx, kxfr, kxfx, kxfp)
    Bloom_Filter               Bloom Filter (qerbl, qesbl)
    Vector_Processing          Vector Processing ((null))
      Vector_Translate         Vector Translate (qkaxl, qerxl, qesxl, qesxlp, qerrc)
      Vector_Aggregate         Vector Aggregate (qergv, qesgv)
      Vector_PX                Vector PX (qesxlp, qerxl)
    Time_Limit                 Query Execution Time Limit (opiexe, qerst)
  PGA_Manage                   PGA Memory Management ((null))
    PGA_Compile                PGA Memory Compilation ((null))
    PGA_IMM                    PGA Memory Instance Manage ((null))
    PGA_CMM                    PGA Memory Cursor Manage ((null))
    PGA_ADV                    PGA Memory Advisor ((null))
  rdbms_dde                    RDBMS Diagnostic Data Extractor (dbke)
  VOS                          VOS (ks)
    hang_analysis              Hang Analysis (ksdhng)
    background_proc            Background Processes (ksb, ksbt)
    system_param               System Parameters (ksp, kspt)
    ksu                        Kernel Service User (ksu)
      ksutac                   KSU Timeout Actions ((null))
    ksv_trace                  Kernel Services Slave Management (ksv)
    file                       File I/O (ksfd, ksfdaf)
  sql_mon                      SQL Monitor (keswx)
    sql_mon_deamon             SQL Monitor Deamon ((null))
    sql_mon_query              SQL Monitor Query ((null))
  CACHE_RCV                    Cache Recovery (kcv, kct, kcra, kcrp, kcb)
    DLF                        Delayed Log Force ((null))
  DIRPATH_LOAD                 Direct Path Load (kl, kdbl, kpodp)
    DIRPATH_LOAD_BIS           Direct Path Kpodpbis Routine (kpodp)
  RAC                          Real Application Clusters ((null))
    GES                        Global Enqueue Service ((null))
      KSI                      Kernel Service Instance locking (ksi)
      RAC_ENQ                  Enqueue Operations ((null))
      DD                       GES Deadlock Detection ((null))
      RAC_BCAST                Enqueue Broadcast Operations ((null))
      RAC_FRZ                  DLM-Client Freeze/Unfreeze (kjfz)
      KJOE                     DLM Omni Enqueue service (kjoe)
    GCS                        Global Cache Service (kjb)
      GCS_BSCN                 Broadcast SCN (kjb, kcrfw)
      GCS_READMOSTLY           GCS Read-mostly (kjb)
      GCS_READER_BYPASS        GCS Reader Bypass (kjb)
      GCS_DELTAPUSH            GCS Delta Push (kjb)
    GSIPC                      Global Enqueue/Cache Service IPC ((null))
    RAC_RCFG                   Reconfiguration ((null))
    RAC_DRM                    Dynamic Remastering ((null))
    RAC_MRDOM                  Multiple Recovery Domains ((null))
    CGS                        Cluster Group Services (kjxg)
    CGSIMR                     Instance Membership Recovery (kjxgr)
    RAC_WLM                    Work Load Management (wlm)
    RAC_MLMDS                  RAC Multiple LMS (kjm)
    RAC_KA                     Kernel Accelerator (kjk)
    RAC_LT                     RAC Latch Usage ((null))
  db_trace                     RDBMS server only tracing ((null))
  kst                          server trace layer tracing (kst)
  ddedmp                       RDBMS Diagnostic Data Extractor Dumper (dbked)
  cursor                       Shared Cursor (kxs, kks)
    Bind_Capture               Bind Capture Tracing ((null))
  KSM                          Kernel Service Memory (ksm)
  KSE                          Kernel Service Error Manager (kse)
  explain                      SQL Explain Plan (xpl)
  rdbms_event                  RDBMS Events (dbkd)
  LOB_INODE                    Lob Inode (kdli)
  rdbms_adr                    RDBMS ADR (dbkr)
  ASM                          Automatic Storage Management (kf)
    KFK                        KFK (kfk)
      KFKIO                    KFK IO (kfkio)
      KFKSB                    KFK subs (kfksubs)
    KFN                        ASM Networking subsystem (kfn)
      KFNU                     ASM Umbillicus (kfnm, kfns, kfnb)
      KFNS                     ASM Server networking (kfns)
      KFNC                     ASM Client networking (kfnc)
      KFNOR                    KFN orion (kfnor)
    KFIS                       ASM Intelligent Storage interfaces (kfis)
    KFM                        ASM Node Monitor Interface Implementation (kfm)
      KFMD                     ASM Node Monitor Layer for Diskgroup Registration (kfmd)
      KFMS                     ASM Node Monitor Layers Support Function Interface (kfms)
    KFFB                       ASM Metadata Block (kffb)
    KFFD                       ASM Metadata Directory (kffd)
    KFZ                        ASM Zecurity subsystem (kfz)
    KFC                        ASM Cache (kfc)
    KFR                        ASM Recovery (kfr)
    KFE                        ASM attributes (kfe)
    KFDP                       ASM PST (kfdp)
    KFG                        ASM diskgroups (kfg)
    KFDS                       ASM staleness registry and resync (kfds)
    KFIA                       ASM Remote (kfia)
      KFIAS                    ASM IOServer (kfias)
      KFIAC                    ASM IOServer client (kfiac)
    KFFSCRUB                   ASM Scrubbing (kffscrub)
    KFIO                       ASM translation I/O layer (kfio)
    KFIOER                     ASM translation I/O layer (kfioer)
    KFV                        ASM Volume subsystem (kfv)
      KFVSU                    ASM Volume Umbillicus (kfvsu)
      KFVSD                    ASM Volume Background (kfvsd)
    KFDX                       ASM Exadata interface (kfdx)
    KFZP                       ASM Password File Layer (kfzp)
    KFA                        ASM Alias Operations (kfa)
    KFF                        KFF (kff)
    KFD                        ASM Disk (kfd)
      KFDVA                    ASM Virtual ATB (kfdva)
    KFTHA                      ASM Transparent High Availability (kftha)
  DML                          DML Drivers (ins, del, upd)
  Health_Monitor               Health Monitor ((null))
  DRA                          Data Repair Advisor ((null))
  DIRACC                       Direct access to fixed tables (kqfd)
  PART                         Partitioning (kkpo, qespc, qesma, kkpa, qergi)
    PART_IntPart               Interval Partitioning ((null))
    PART_Dictionary            Partitioning Dictionary (kkpod)
  LOB_KDLW                     Lob kdlw (kdlw)
  LOB_KDLX                     Lob xfm (kdlx)
  LOB_KDLXDUP                  Lob dedup (kdlxdup)
  LOB_KDLRCI                   Lob rci (kdlrci)
  LOB_KDLA                     SecureFile Archive (kdla)
  SQL_Manage                   SQL Manageability (kes)
    SQL_Manage_Infra           Other SQL Manageability Infrastructure (kesai, kesqs, kesatm, kesutl, kessi, keswat, keswts, keswsq)
    SQL_Tune                   SQL Tuning Advisor (kest)
      SQL_Tune_Auto            SQL Tuning Advisor (auto-tune) (kestsa)
        Auto_Tune_Opt          Auto Tuning Optimizer (kkoat)
      SQL_Tune_Index           SQL Tuning Advisor (index-tune) (kestsi)
      SQL_Tune_Plan            SQL Tuning Advisor (plan node analysis) (kestsp)
      SQL_Tune_Px              SQL Tuning Advisor (parallel execution) (kestsa)
      SQL_Tune_Fr              SQL Tuning Advisor (fix regression) (kestsa)
    SQL_Test_Exec              SQL Test-Execute Service (kestse)
    SQL_Perf                   SQL Performance Analyzer (kesp, keswpi)
    SQL_Repair                 SQL Repair Advisor (kesds)
    SQL_trace_parser           SQL trace parser (kesstp)
  SQL_Analyze                  SQL Analyze (qksan)
  SQL_DS                       SQL Dynamic Sampling Services (qksds)
  SQL_DDL                      SQL DDL (atb, ctc, dtb)
  RAT_WCR                      Real Application Test: Workload Capture and Replay (kec)
  Spatial                      Spatial (md)
    Spatial_IND                Spatial Indexing (mdr)
    Spatial_GR                 Spatial GeoRaster (mdgr)
  Text                         Text (dr)
  rdbms_gc                     RDBMS Diagnostic Generic Configuration (dbkgc)
  XS                           XS Fusion Security (kzx)
    XSSESSION                  XS Session (kzxs)
    XSPRINCIPAL                XS Principal (kzxu)
    XSSECCLASS                 XS Security Class (kzxc, kzxsp)
    XSXDS                      XS Data Security (kzxd)
    XSVPD                      XS VPD ((null))
    XSXDB_DEFAULT              XS XDB ((null))
    XS_MIDTIER                 XS Midtier (kpuzxs)
    XSNSTEMPLATE               XS Namespace template (kzxnt)
    XSACL                      XS ACL (kzxa)
    XSADM                      XS Administrative operation (kzxm, kzxi)
  AQ                           Streams Advanced Queuing (kwq, kkcn, kpon, kpoaq, kpce, kpcm, kpun, kpuaq, kws)
    AQ_DEQ                     Streams Advanced Queuing Dequeue (kwqid, kwqdl)
    AQ_BACK                    Streams Advanced Queueing Background (kwsbg, kwsbsm)
      AQ_TM                    Streams Advanced Queuing Time Manager (kwqit, kwqmn)
      AQ_CP                    Streams Advanced Queuing Cross Process (kwscp, kwsipc)
      AQ_LB                    Streams Advanced Queuing Load Balancer (kwslb, kwslbbg)
      AQ_NTFN                  Streams Advanced Queuing Notification (kpond, kkcne)
        AQ_NTFNP12C            Streams Advanced Queuing pre-12c Notification (kwqic)
      AQ_TMSQ                  Streams Advanced Queuing Time Manager for Sharded Queue (kwsbtm, kwsbjc, kwsbit)
    AQ_MC                      Streams Advanced Queuing Message Cache (kwsmc, kwssh, kwsmb, kwsmsg, kwssb, kwschnk, kwscb, kwsdqwm, kwssbsh)
    AQ_PT                      Streams Advanced Queuing Partitioning (kwspt)
    AQ_SUB                     Streams Advanced Queuing Subscription (kwssi, kwssa, kwsnsm, kwsnsme)
  KSFM                         Kernel Service File Mapping (ksfm)
  KXD                          Exadata specific Kernel modules (kxd)
    KXDAM                      Exadata Disk Auto Manage (kxdam)
    KCFIS                      Exadata Predicate Push (kcfis)
    NSMTIO                     Trace Non Smart I/O (nsmtio)
    KXDBIO                     Exadata Block level Intelligent Operations (kxdbio)
    KXDRS                      Exadata Resilvering Layer (kxdrs)
    KXDOFL                     Exadata Offload (kxdofl)
    KXDMISC                    Exadata Misc (kxdmisc)
    KXDCM                      Exadata Metrics Fixed Table Callbacks (kxdcm)
    KXDBC                      Exadata Backup Compression for Backup Appliance (kxdbc)
  DV                           Database Vault (kzv)
  ASO                          Advanced Security Option ((null))
    RADM                       Real-time Application-controlled Data Masking (kzradm)
  SVRMAN                       Server Manageability (ke)
    AWR                        Automatic Workload Repository (kew)
      ASH                      Active Session History (kewa)
      METRICS                  AWR metrics (kewm)
      REPOSITORY               AWR Repository (kewr)
        FLUSH                  AWR Snapshot Flush (kewrf)
        PURGE                  AWR Snapshot Purge (kewrps)
      AWRUTL                   AWR Utilities (kewu)
    AUTOTASK                   Automated Maintenance Tasks (ket)
    MMON                       MMON/MMNL Infrastructure (keb)
    SVRALRT                    Server Generated Alert Infrastructure (kel)
  OLS                          Oracle Label Security (zll)
  AUDITNG                      Database Audit Next Generation (aud, kza, kzft, aus, aop, ttp)
    Configuration              ANG Configuration (aud, kza, kzft, aus, aop, ttp)
    QueueWrite                 ANG Queue Write (aud, kza, kzft, aus, aop, ttp)
    FileWrite                  ANG File Write (aud, kza, kzft, aus, aop, ttp)
    RecordCompose              ANG Record Compose (aud, kza, kzft, aus, aop, ttp)
    DBConsolidation            ANG Database Consolidation (aud, kza, kzft, aus, aop, ttp)
    SYS_Auditing               ANG SYS Auditing (aud, kza, kzft, aus, aop, ttp)
  KJCI                         KJCI Cross Instance Call (kjci)
  KJZ                          KJZ - DIAG (kjz)
    KJZC                       KJZC - DIAG Communication Layer (kjzc)
    KJZD                       KJZD - DIAG Main Layer (kjzd)
    KJZF                       KJZF - DIAG Flow Control Layer (kjzf)
    KJZG                       KJZG - DIAG Group Services Layer (kjzg)
    KJZH                       KJZH - DIAG API Layer (kjzh)
    KJZM                       KJZM - DIAG Membership Layer (kjzm)
  SEC                          Security (kz)
    CBAC                       Code-Based Access Control (kzc)
  dbop                         DBOP monitoring (keomn)
    dbop_gen                   DBOP generic service (keomg)
      dbop_deamon              DBOP monitoring Deamon (keomg)
    dbop_comp                  DBOP composite type (keomm)
  em_express                   EM Express (kex)
  orarep                       orarep (ker)
  Data                         Data Layer (kd, ka)
    KDS                        Kernel Data Scan (kds)
      KDSRID                   Fetch By Rowid (kdsgrp, kdsgnp)
      KDSFTS                   Full Table Scan (kdsttgr, kdstgr)
      KDSCLU                   Cluster Table Scan (kdsics, kdscgr)
    KDI                        Index Layer (kdi)
      KDIZOLTP                 OLTP HIGH Index (kdizoltp)
      KDXOKCMP                 Auto Prefix Compressed Index (kdxokcmp)
      KDIL                     Index Load (kdil)
  RAT                          Real Application Testing (kec)
    RAT_MASK                   Real Application Testing: Masking (kesm, kecprm)
  BA                           Backup Appliance (kbrs)
  KBC                          BA Containers (kbc)
  connection_broker            Connection Broker (kmp)
  KRA                          Kernel Recovery Area Function (kra)
  KRA_SQL                      KRA SQL Tracing ((null))
  KRB                          Kernel Backup Restore (krb)
    KRB_THREAD                 KRBBPC Thread Switches ((null))
    KRB_IO                     KRB I/O ((null))
    KRB_INCR                   KRB Incremental Restore ((null))
    KRB_PERF                   KRB Performance Tracing ((null))
    KRB_BPOUTPUT               Detailed Backup Piece Output ((null))
    KRB_BPVAL                  Detailed Block List During Restore Validate ((null))
    KRB_FLWRES                 Details on Restore Flow ((null))
    KRB_FLWCPY                 Details on krbydd Flow ((null))
    KRB_FLWBCK                 Details on Backup Flow ((null))
    KRB_FLWUSAGE               RMAN Feature Usage ((null))
    KRB_OPTIM                  Unused Space Compression ((null))
  KRBABR                       Auto Block Media Recovery (krbabr)
  KRC                          Recovery Block Change Tracking (krc)
  KRC_CHANGES                  Recovery Block Change Tracking CHANGES ((null))
  IM                           in-memory ((null))
    IM_transaction             IM transaction layer ((null))
      IM_Txn_PJ                IM Txn Private Journal (ktmpj)
      IM_Txn_SJ                IM Txn Shared Journal (ktmsj)
      IM_Txn_JS                IM Txn Journal Scan (ktmjs)
      IM_Txn_Conc              IM Txn Concurrency (ktmc)
      IM_Txn_Blk               IM Txn Block (ktmb)
      IM_Txn_Read              IM Txn Read (ktmr)
    IM_space                   IM space layer ((null))
    IM_data                    IM data layer (kdm)
      IM_populate              IM populating (kdml)
      IM_background            IM background (kdmr)
      IM_scan                  IM scans ((null))
      IM_journal               IM journal ((null))
      IM_dump                  IM dump ((null))
      IM_FS                    IM faststart ((null))
      IM_optimizer             IM optimizer (kdmo)
  xdb_wallet                   XDB Wallet (kzs)
  PROGINT                      Programmatic Interfaces (kp)
    OCI                        OCI (oci, kpk, kpn)
    OPI                        OPI (opi)
    RPI                        RPI (rpi, kpr)
    NPI                        NPI (npi, nco, kpfs)
    Two_Task                   Two Task (osn, ksn)
    PROGINT_PLSQL              Programmatic Interfaces to/from PL/SQL (kkx, psd, pckl, plswa)
    Two_Phase                  Two-phase commit (k2)
    Conn_Pool                  Connection Pool (kppl)
    TSM                        Transparent Session Migration (kpm, kps)
    PROGINT_MISC               Progint Miscellaneous (kpo, kpbf, kpin)

Components in library GENERIC:
--------------------------
  Generic_VOS                  Generic VOS ((null))
    VOS_Heap_Manager           VOS Heap Manager ((null))
    VOS_Latches                VOS Latches ((null))
    VOS_GST                    VOS Generic Stack Trace (kgds)
  XML                          XML (qmxt, qmxq)
  Generic_XDB                  Generic XDB ((null))
    XDB_Repository             XDB Repository (qme)
    XDB_Protocols              XDB Protocols (qmh, qmf, qmn)
    XDB_Query                  XDB Query (qmxtra, qerxp)
    XDB_XMLIndex               XDB XMLIndex (qmxtri, qmixq)
    XDB_Schema                 XDB Schema (qmxtri, qmixq)
    XDB_XOB                    XDB XOB (qmx)
    XDB_CSX                    XDB CSX (qmcx)
      XDB_CSX_ENCODING         XDB CSX ENCODING (qmcxe, qmcxm)
      XDB_CSX_DECODING         XDB CSX DECODING (qmcxd)
      XDB_CSX_SELFCON          XDB CSX SELFCON (qmcxe)
    XDB_Default                XDB Default ((null))
  LOB                          LOB (koll, kola)
    LOB_Refcount               LOB Refcount (kolr)
    LOB_Default                LOB Default (kole,  kokl, koxs, kokla, koklm, koklv)
  KGH                          KGH Memory Allocator (kgh)
  KGF                          ASM Automatic Storage Management (kgf)
  LIBCACHE                     LIBCACHE (kgl, kql)
  OBJECTS                      OBJECTS ((null))
    OBJECTS_DDL                OBJECTS DDL (kokt)
    OBJECTS_Types              OBJECTS Types (kot, ko, ort)
    OBJECTS_Images             OBJECTS Images (koke, kot, kad)
    OBJECTS_Anydata            OBJECTS Anydata (kokla, kolo, kot, kad)
    OBJECTS_Streams            OBJECTS Streams (koxs)
    OBJECTS_Dict               OBJECTS Dictionary (kkdo, qcdo)
    OBJECTS_Semanal            OBJECTS Semantic Analysis (koks, qcso, qcto)
    OBJECTS_Default            OBJECTS Default ((null))
  KGFPM                        PATCH repository (kgfpm)
  KGFDVF                       Voting File Interface (kgfdvf)
  Shared_Objects               Shared Object Manager (pes)
    SO_Loader                  Native Code Loader (pesld)

Components in library CLIENT:
--------------------------
  Client_KPU                   Client KPU ((null))
    KPU_Memory                 KPU Memory ((null))
    KPU_TTC                    KPU TTC ((null))
    KPU_Relational             KPU Relational ((null))
    KPU_Objects                KPU Objects ((null))
    KPU_LOBS                   KPU LOBS ((null))
  progint_appcont              Prog Interfaces Application Continuity ((null))
    progint_appcont_rdbms      Prog Interfaces Application Continuity RDBMS-side ((null))
  SQLLDR_Load                  SQLLDR Load (ul)
  DPAPI_Load                   DPAPI Load (kpudp)

Components in library LIBCELL:
--------------------------
  Client_Library               Client Library ((null))
    Disk_Layer                 Disk Layer ((null))
    Network_Layer              Network Layer ((null))
    IPC_Layer                  IPC Layer ((null))

Components in library ORANET:
--------------------------
  TNSLSNR                      OraNet Listener ((null))
    NET_NSGR                   Network Service Generic Registration ((null))
    NET_NSGI                   TNI Network Service Generic Listener User-defined class ((null))
  CMAN                         OraNet Connection Manager ((null))
  NET                          OraNet Services ((null))
    NET_NI                     Network Interface Layer ((null))
    NET_NS                     Network Session Layer ((null))
    NET_NT                     Network Transport Layer ((null))
    NET_NTM                    Network Transport Mailbox Layer ((null))
    NET_NTP                    Network Transport IPC Layer ((null))
    NET_NTT                    Network Transport TCP/IP Layer ((null))
    NET_NTUS                   Network Transport Unix Domain Sockets Layer ((null))
    NET_NL                     Network Library ((null))
    NET_NA                     Network Authentication ((null))
    NET_NZ                     Network Zecurity ((null))
    NET_NTZ                    Network SSL ((null))
    NET_NU                     Network Trace Route Utility ((null))
    NET_NN                     Network Names ((null))

Components in library ADVCMP:
--------------------------
  ADVCMP_MAIN                  Archive Compression (kdz)
    ADVCMP_COMP                Archive Compression: Compression (kdzc, kdzh, kdza)
    ADVCMP_DECOMP              Archive Compression: Decompression (kdzd, kdzs)
      ADVCMP_DECOMP_HPK        Archive Compression: HPK (kdzk)
      ADVCMP_DECOMP_PCODE      Archive Compression: Pcode (kdp)

Components in library PLSQL:
--------------------------
  PLSQL_Apps                   PL/SQL Apps (di, pi, plitblm, scm, std, textio, wpiutil)
  PLSQL_Codegen                PL/SQL Codegen ((null))
    PLSQL_COG_IDL_Gen          PL/SQL Codegen IDL Gen (pdw)
    PLSQL_COG_Infrastructure   PL/SQL Codegen Infrastructure (pdz)
    PLSQL_COG_Native           PL/SQL Codegen Native (pdn)
    PLSQL_COG_Optimizer        PL/SQL Codegen Optimizer (pdx)
    PLSQL_COG_MCode_Gen        PL/SQL Codegen MCode Gen (pdy)
  PLSQL_Code_Execution         PL/SQL Code Execution (pb, pd, pe, pf, plst, pri)
  PLSQL_External_Proc          PL/SQL External Proc (pef, ociextp)
  PLSQL_IDL                    PL/SQL IDL (pdt, pt)
  PLSQL_ILMS                   PL/SQL ILMS (pgm)
  PLSQL_KNLDE                  PL/SQL KNLDE (pbbicd, pbp3g, pbs, pbt3g, peibp)
  PLSQL_KG_Interface           PL/SQL KG Interface (bam, hep, hsh, lis, par, phdr, pk)
  PLSQL_Infrastructure         PL/SQL Infrastructure (pci, pcm, ph, pl, pncutil, pp, ps, pu, tre)
  PLSQL_PSD                    PL/SQL PSD ((null))
    PLSQL_PSD_Generic          PL/SQL PSD Generic (psd, pso, psu)
    PLSQL_PSD_Standalones      PL/SQL PSD Standalones (p2c, pls, psx)
  PLSQL_Semantics              PL/SQL Semantics (ph2, pha, phn)
  PLSQL_Syntax                 PL/SQL Syntax (ph1)

SQL> spool off