Category Archives: 11gR2

Posts related to Oracle 11g Release 2

Pivot query on Automatic Workload Repository

Leave a reply

I’ve just tried to get a few information from the Automatic Workload Repository (AWR). I actually wanted to put together an overview of various system metrics from DBA_HIST_SYSMETRIC_SUMMARY to create a chart. Unfortunately the data is stored as name/value pairs and not in columns. So it’s time again to convert rows to columns. There are several more or less difficult solutions for any kind of oracle release. Enclosed I’ll show a simple variant for Oracle 11g with PIVOT.

In my query I’m interested in the metrics Current Logons Count, Process Limit Usage (%) and Session Limit Usage (%). A description of these and other metrics can be found in the EM 12c online documentation Database-Related Metrics Reference Manual. The AWR view DBA_HIST_SYSMETRIC_SUMMARY is explained in the Oracle Database Reference.

Simple query with name / value pairs.

col  METRIC_NAME FOR a25
ALTER SESSION SET nls_date_format='DD.MM.YYYY HH24:MI:SS';
SELECT
    SNAP_ID,
    END_TIME,
    METRIC_NAME,
    MAXVAL
FROM
    DBA_HIST_SYSMETRIC_SUMMARY
WHERE
    METRIC_NAME IN ('Current Logons Count','Process Limit %','Session Limit %')
ORDER BY SNAP_ID;
   SNAP_ID END_TIME            METRIC_NAME                   MAXVAL
---------- ------------------- ------------------------- ----------
       589 03.06.2013 00:21:44 Process Limit %           31,7142857
       589 03.06.2013 00:21:44 Current Logons Count             110
       589 03.06.2013 00:21:44 Session Limit %           22,2222222
       590 03.06.2013 01:20:44 Process Limit %           14,8571429
       590 03.06.2013 01:20:44 Current Logons Count              51
       590 03.06.2013 01:20:44 Session Limit %           11,8055556
       591 03.06.2013 02:21:44 Session Limit %           11,6319444
       591 03.06.2013 02:21:44 Process Limit %           14,2857143
       591 03.06.2013 02:21:44 Current Logons Count              49
       592 03.06.2013 03:20:44 Current Logons Count              47
       592 03.06.2013 03:20:44 Process Limit %           13,7142857
       592 03.06.2013 03:20:44 Session Limit %           11,1111111
...

Lets query the same data with a PIVOT query.

SELECT
    *
FROM
    (SELECT
        SNAP_ID,
        END_TIME,
        METRIC_NAME,
        MAXVAL
    FROM
        DBA_HIST_SYSMETRIC_SUMMARY)
pivot
   ( MAX(MAXVAL)
   FOR METRIC_NAME IN ('Current Logons Count' ,'Process Limit %','Session Limit %'))
ORDER BY SNAP_ID;
   SNAP_ID END_TIME            'Current Logons Count' 'Process Limit %' 'Session Limit %'
---------- ------------------- ---------------------- ----------------- -----------------
       589 03.06.2013 00:21:44                    110        31,7142857        22,2222222
       590 03.06.2013 01:20:44                     51        14,8571429        11,8055556
       591 03.06.2013 02:21:44                     49        14,2857143        11,6319444
       592 03.06.2013 03:20:44                     47        13,7142857        11,1111111
       593 03.06.2013 04:21:44                     47        13,7142857           10,9375
       594 03.06.2013 05:21:44                     49        14,2857143        11,2847222
       595 03.06.2013 06:21:44                     48                14        11,2847222
       596 03.06.2013 07:21:44                     49        14,2857143        11,2847222
       597 03.06.2013 08:21:44                     48                14        11,2847222
       598 03.06.2013 09:21:45                     49        14,2857143        11,2847222
       599 03.06.2013 10:21:45                     47        13,7142857           10,9375
...

Et voilà, the result can now be used to create a fancy excel chart.

Other solutions with DECODE, CASE or SYS_CONNECT_BY_PATH are also possible. These would even run on older Oracle releases.

Fast lane to ORA-00600 service requests

1 Reply

There are several way’s to find information on internal oracle errors like ORA-00600 and ORA-07445. Looking up google is one of them, but not in any case the best one. If you are using Oracle Metalink you may just search for the ORA-00600 and the error code of first argument or use the ORA-600/ORA-7445/ORA-700 Error Look-up Tool [153788.1], which is available since several years. But sometimes you do not get the expected results and you have to open an Oracle service request to get more information, bug fix, workaround, justification that you really haft this issue, etc. It is then a bit cumbersome to add all kind of information and upload files to create the SR. Since a while Oracle provides simple troubleshooting tools in My Oracle Support. Rather than searching for the error you just have to upload the IPS package created with Oracle adrci. Based on the information in the IPS package, Oracle suggests possible causes / solutions. Optional one has the possibility to open an SR based directly on the IPS package. The troubleshooting tool can directly be accessed ORA-00600 or will be opened if you add ORA-00600 in the error message field when creating an SR. At the end your problem and you SR is probably not solved faster, but at least it is a bit easier to open an SR. :-)

Similar troubleshooting tools are also available for ORA-07445, ORA-04030 and ORA-04031

Build the Package

Start ADRCI and set the proper ADR home

oracle@urania:~/[TDB01] adrci

ADRCI: Release 11.2.0.3.0 - Production on Mon Jun 3 10:54:39 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

ADR base = "/u00/app/oracle"
adrci> show home
ADR Homes:
diag/rdbms/tdb01/TDB01
diag/tnslsnr/urania/listener
adrci> set home diag/rdbms/tdb01/TDB01

List the last 5 incidents

adrci> show incident -last 5

ADR Home = /u00/app/oracle/diag/rdbms/tdb01/TDB01:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
----------- --------------------------------------- ----------------------------------------
2995358 ORA 700 [dbghmo_read_msgobj_mt_bygrpid] 2013-06-01 23:32:03.985000 +02:00
2932162 ORA 600 [3619] 2013-06-01 21:05:10.287000 +02:00
2911681 ORA 600 [3619] 2013-06-01 20:27:38.752000 +02:00
2891200 ORA 600 [3619] 2013-06-01 20:25:49.517000 +02:00
2851271 ORA 700 [dbghmo_read_msgobj_mt_bygrpid] 2013-06-01 18:23:27.147000 +02:00
First 5 rows fetched (*** more available ***)

For an Oracle SR we need a physical package of the incident. This can be generated with just a simple command.

adrci> IPS PACK INCIDENT 2932162 IN /tmp
Generated package 6 in file /tmp/ORA600361_20130603113930_COM_1.zip, mode complete

References

More information on ADR, Incidents and ORA-00600 troubleshooting can be found in the following MOS notes:

ADR

  • 11g Understanding Automatic Diagnostic Repository [ID 422893.1]
  • Database 11g: Quick Steps to Package and Send Critical Error Diagnostic Information to Support [ID 443529.1]
  • ADR Different Methods to Create IPS Package [ID 411.1]
  • 11g How to Unpack a Package in to ADR [ID 745960.1]
  • How to use the ADRCI interface to generate a SQL test case for an incident [ID 1330207.1]
  • Retention Policy for ADR [ID 564269.1]
  • 11g How To Find Out Trace Files Relevant To A Critical Error [ID 443536.1]

ORA-600/7445 Internal Errors

Use of DEFAULT_CLEANUP_INTERVAL

2 Replies

Following a question to the blog post Database Audit and Audit trail purging, I noticed something interesting about the DEFAULT_CLEANUP_INTERVAL parameter. On one hand, it is mandatory to initialize the audit trail and to define a DEFAULT_CLEANUP_INTERVAL, on the other hand, the parameter is not used at all. Oracle explains this in the MOS note Parameter DEFAULT_CLEANUP_INTERVAL of DBMS_AUDIT_MGMT.INIT_CLEANUP procedure [1243324.1]

Quote Oracle Support (MOS Note 1243324.1):

The dbms_audit_mgmt.init_cleanup parameter DEFAULT_CLEANUP_INTERVAL is not intended to be used to control the frequency of execution of audit management automatic cleanup. This parameter, although assigned a value during initialisation of audit infrastructure, is unused in current releases. However, in future releases it is intended to be used to control functionality which automatically partitions audit tables based on their archive frequency. This functionality already exists in the DBMS_AUDIT_MGMT package but is disabled in current releases. This is not a classified product bug, but expected behaviour.

According to the MOS Note DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL Not Clearing FGA Audit Trail When Using Last Archive Timestamp [1532676.1] it could be a no go for audit purging if DEFAULT_CLEANUP_INTERVAL has not or never been. Conclusion initialize the audit trail and define a value for the default cleanup interval but manualy setup a purge job.

I’m curious what Oracle plans for the future. Unified and self purging audit trail :-)

Reference

A few Metalink Notes related to Audit and Audit Management.

  • Master Note For Oracle Database Auditing
  • Known Issues When Using: DBMS_AUDIT_MGMT
  • How to Truncate, Delete, or Purge Rows from the Audit Trail Table AUD$
  • DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL Not Clearing FGA Audit Trail When Using Last Archive Timestamp
  • Parameter DEFAULT_CLEANUP_INTERVAL of DBMS_AUDIT_MGMT.INIT_CLEANUP procedure

Oracle CPU / PSU Pre-Release Announcement January 2013

1 Reply

Today Oracle has published the Pre-Release Announcement for the first CPU Patch in 2013. This Critical Patch Update contains 86 new security vulnerability fixes for several Oracle products. From the Oracle database point of view it is quite a small update. There is only one security fix for the Oracle Database Server and no for client-only installations.

Although the CVSS rating of this vulnerability is 9.0, it looks that there is no hurry to install this security fix on most of the database environments. This is because only the spatial is affected. If this is true, we’ll see next Tuesday when Oracle is officially releasing CPU / PSU January 2013. Next week I’ll have a closer look.

More details about the patch will follow soon on the Oracle Security Pages.

Oracle CPU / PSU Pre-Release Announcement October 2012

Today Oracle has published the Pre-Release Announcement for the october CPU Patch. This Critical Patch Update contains 109 new security vulnerability fixes for several Oracle products. 5 of these fixes are just for the Oracle Database Server including 2 fixes for client-only installations. What frighten me a bit, is the CVSS Base Score of 10 for the core RDBMS. Oracle apparently has to close another big security issue. The core RDBMS is by the way the only component which has to be patched by this CPU. In combination with this severity everybody will have to patch. SCN flaw, TNS poisoning, Oracle Password Hashing Algorithm Weaknesses, etc obviously it’s the oracle-year of critical issues. Any way we will see it next week in detailed. As mentioned just the following Database Server Products are affected.

  • Core RDBMS

So far the Database Server Patch’s are planned for Oracle Database 11g Release 2 (11.2.0.2,11.2.0.3), Oracle Database 11g Release 1 (11.2.0.7) and Oracle Database 10g Release 2 (10.2.0.3, 10.2.0.4, 10.2.0.5).

The official release for the CPU / PSU is planned for next week 16 October 2012. More details about the patch will follow soon on the Oracle Security Pages.

DOAG / SOUG Security-Lounge at Basel

I haven’t found time to provide any blog post in the past weeks. Never the less I would like to inform about the upcoming security lounge in Basel at which I’ll give two lectures about Oracle Security. It’s a small even with just one speaker ;-) Ok it was planned to have a second one but it did not work. The event is organized by the DOAG regional group Freiburg and SOUG. It will start at 17:30 on the 24th of April.

Have a look at the DOAG Webpage for a detailed Agenda of the Event and the location. Looking forward to see you there.

I’ll post the slides for both presentations shortly after the event on this page.

Oracle hidden init.ora parameter

This post focuses on init.ora parameters. It is not really new topic, but rather a personal reference to some practical queries and scripts. If you are the customer, it’s always handy when you can easily access your own queries.

It is quite simple to get some information on init.ora parameters from SQLPlus. Using a tool like TOAD or SQL Developer make it even easier. Unfortunately I work often at the customer without my own tools and scripts. So commandline and SQLPlus is the only “tools” available to work on the database. It is not an issue to dig through the data dictionary to get any kind of information as long as there is 1-2 view involved. But for querying multiple View’s, X$ views etc it is easier to have something on hand.

OK, what’s different with my queries? Not much, they just fit my needs :-) Instead of just querying v$parameter I’ll query as well the X$ views to see as well the hidden parameter and simple description for each parameter.

The first query does a select on X$KSPPI, X$KSPPCV, X$KSPPSV and V$PARAMETER to display all init.ora parameter including the hidden parameters. The result can be limited by adding a part of the parameter name or specify % to see all which then would be a little over 2500 parameters. S stands for it is session modifiable, I stands for it is system modifiable and D show if the parameter does still have the default value or not. I’ve added the query as hip.sql (stands somehow for hidden init parameter) to my small script collection which can be downloaded in the script section.

SET linesize 235
col Parameter FOR a50
col SESSION FOR a28
col Instance FOR a55
col S FOR a1
col I FOR a1
col D FOR a1
col Description FOR a90

SELECT  
  a.ksppinm  "Parameter",
  decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session",
  c.ksppstvl "Instance",
  decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",
  decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",
  decode(p.isdefault,'FALSE','F','TRUE','T') "D",
  a.ksppdesc "Description"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
  AND p.name(+) = a.ksppinm
  AND UPPER(a.ksppinm) LIKE UPPER('%&1%')
ORDER BY a.ksppinm;

The second script does the same as the first one exempt that it limit the result to the list of parameter which are not default. I’ve added the query as hipf.sql (stands somehow for hidden init parameter false) to my small script collection which can be downloaded in the script section.

SET linesize 235 pagesize 200
col Parameter FOR a50
col SESSION FOR a28
col Instance FOR a55
col S FOR a1
col I FOR a1
col D FOR a1
col Description FOR a90

SELECT * FROM (SELECT  
  a.ksppinm  "Parameter",
  decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session",
  c.ksppstvl "Instance",
  decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",
  decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",
  decode(p.isdefault,'FALSE','F','TRUE','T') "D",
  a.ksppdesc "Description"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
  AND p.name(+) = a.ksppinm
  AND UPPER(a.ksppinm) LIKE UPPER('%&1%')
ORDER BY a.ksppinm) WHERE d='F';

A few information on hidden init.ora parameter can be found in the Metalink Note How To Query And Change The Oracle Hidden Parameters In Oracle 10g [315631.1]