Tag Archives: Trivadis Content

Blog posts also posted on the Trivadis Blog (TriBlog)

Oracle 12c New Security Features

I’ve just uploaded the slides for my lecture Oracle 12c new security features, as I had promised this in my previous posts. (See also DOAG 2013 Datenbank or DOAG SIG Security). The slides is a consolidation of my presentations on the New Security Features in latest generation of Oracle Database and does no reflect 1:1 the slides at the different events.

Yet a short summary of new security features

  • Oracle Data Redaction, Advanced Security feature to prevent display of sensitive data.
  • Support for Secure Hash Algorithm SHA-2 for DBMS_CRYPTO and the password hash.
  • New unified auditing and audit policies.
  • Privilege Analysis, to analyse who is using which privileges and clean up authorization.
  • New administration privileges like SYSBACKUP, SYSDG and SYSKM to reduce the dependence on SYSDBA and improve separation of duty.
  • Database Vault persistent protections, DB Vault does not longer depend on executables.

There is much more just on security. The full list of new features is available in the New Features Guide 12c Release 1 (12.1). Oracle 12c is a release with so many security innovations since long time. So let’s discuss the good, the bad and the mad….

If you plan to take a training have a look at the Trivadis Oracle Database 12c Techno Circle.

Oracle Database 12c

Oracle still hasn’t officially announce the new Oracle Database 12c release. But since OTN database overview has been changed to 12c, I guess it is now somehow official. As rumors hinted, one of the main innovations are pluggable databases now named multitenant. Other important new features and products are:

  • Adaptive Execution Plans
  • Application Continuity
  • Automatic Data Optimization (ADO)
  • Data Guard Far Sync
  • Data Redaction
  • Global Data Services
  • Heat Map
  • Multitenant (Pluggable Databases)
  • Pattern Matching
  • SQL Translation Framework

The full list of new features is available in the New Features Guide 12c Release 1 (12.1)

I’ve already had the chance to look into the New Security Features. (See also DOAG 2013 Datenbank or DOAG SIG Security) Therefore I’ll post more details on New Security Features in the next day’s and weeks. If you already plan to take a training have a look at the Trivadis Oracle Database 12c Techno Circle. Or are you already planning an upgrade? Then it is worth to have a look at Mike Dietrich’s Upgrade, Migrate and Consolidate to Oracle Database 12c slides.

References and Links

Some of the links are currently just partially available. It looks like Oracle is updating different links respectively references.

Pivot query on Automatic Workload Repository

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

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

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

DOAG 2013 Datenbank

As I announced a while ago in SOUG Special Interest Group Baden March 21st I’ll speak again about some improvements in the latest generation of Oracle Database. The content of the presentation is a mixture of the presentations I’ve lectured at SOUG SIG Baden and DOAG SIG Security Munich. It covers the following possible new features. The features will explained by several practical examples.

  • Data Redaction
  • Unified Database Auditing
  • Role and Privilege Analysis

More Information on the Event is available on the DOAG website.

Due to the fact that this presentation contains preliminary information, the slides will not be available for download yet. But I will make the download link available once the dust settles on the latest Generation of Database Technology…

DOAG SIG Security

Just a couple of hours ago I’ve lecture a presentation about the latest Generation of Database Technology at the DOAG SIG Security in München. It is a sneak preview on a few upcoming security improvements. Unfortunately I do not yet have the permission to provide the presentation for download. But I will make the download link available once the dust settles on the latest Generation of Database Technology…

so stay tuned.

SOUG Special Intrest Group Baden March 21st

In about two weeks I will participate at the SOUG special interest group at Baden. I will present a paper entitled “New Security Features in latest generation of Oracle Database“. Where latest generation of Oracle Database does not stand for an other Oracle 11g release. But that’s an other story…

The aim of the presentation is to provide a range of information on new security features as they could be released in with latest generation of Oracle Database. It covers the following possible new features.

  • Data Redaction
  • Unified Datenbank Auditing
  • Role and Privilege Analysis
  • Improved Database Vault
  • Database Application Security Architecture
  • Improved Key Management
  • New OS Roles

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

Due to the fact that this presentation contains preliminary information, the slides will not be available for download. It is a must to personally attend the SIG SOUG 🙂 If you do not have time to participate at the SOUG event, you have a second chance later this year. I’ve planned a similar presentation for the DOAG Event in Düsseldorf. More on that later.

Oracle released CPU / PSU January 2013

As announced in my post about Oracle’s pre-release announcement of last week, Oracle has now released the first Critical Patch Updates for 2013. Overall this CPU contains 86 new security fixes across several Oracle products like Database Server, MySQL Server, Sun Product Suite, WebLogic Server etc. For products like Oracle Database Mobile it does contain quite some critical security fixes with a CVSS Rating of 10. On the other hand there’s just one security fix for regular oracle database servers. This security fix relates merely to the SPATIAL option. For a variety of Oracle database server, which do not use the spatial option, this CPU is not so critical. It’s probably worth waiting for the CPU april 2013.

CPU Release Dates

The next four Critical Patch Updates will be released at the following dates:

  • 16 April 2013
  • 16 July 2013
  • 15 October 2013
  • 14 January 2014

References

Links all around Critical Patch Update: