Oracle's New Features

Top New Features As Used by Me

Subquery Factoring aka WITH clause

What a surprise such the useful clause has been ignored by me until now. And it is here since 9i. I found it in a book "Cost Based Oracle: Fundamentals" by Jonathan Lewis. There is an in-depth article at Oracle-Base about the feature.

Virtual Columns

The virtual column is an extra column in a table which can be a result of calculation based on existing real columns. It came with Oracle 11g and simplified queries very much. It can be used for table partitioning,

Corkscrew

   I work in a place where I have no direct access to the outside world in a way other than HTTP (through an authenticated proxy) and email. However sometimes I need to access the external world, usually through SSH to pick some external resources, push GIT repository or so. And this is not a problem with PuTTY for it has a variety proxies build-in:

   But while working on my Linux running on Oracle VirtualBox I have to rely on pure OpenSSH only. And there is no HTTP proxy support in OpenSSH just out of the box. And there is a solution for OpenSSH as well - this is Corkscrew, a small utility but it does the work and supports HTTP authentication even!

For github.com purpose I use following config in the file ~/.ssh/config:

Host github.com
  ProxyCommand corkscrew proxy.acme.com 80 %h %p ~/.ssh/proxyauth
  ServerAliveInterval 60

In the ssh config above the clause "Host github.com" means the configuration applies only for the target like ssh git@github.com. And this is exactly what git is doing when pushing to a github repository. It pushes through ssh. Following clause "ProxyCommand corkscrew [...]" means ssh has to run corkscrew commands with the following arguments. Here you have the simplest corkscrew syntax:

$ corkscrew -h
corkscrew 2.0 (agroman@agroman.net)
usage: corkscrew <proxyhost> <proxyport> <desthost> <destport> [authfile]
$

First two arguments identify the proxy and its port Proxy.acme.com 80. The target hostname and the port is delivered by ssh itself through variables %h and %p. The last argument is the auth file - if you need credentials for your proxy, create a file, insert the username:password inside (just one line) and your connection through proxy will be authenticated. Usually you don't need it.
The extra line with ServerAliveInterval is useful when I have an idle interactive ssh connection. You can safely remove it from github.com configuration.

That's it! Try it out.

Links

1. Corkscrew homepage.
2. Corkscrew, how to setup.

FGA Notes

FGA Notes @11G

To create a fine-grained audit policy, you must have EXECUTE privileges on the DBMS_FGA PL/SQL package. The package is owned by the SYS user.

To create a fine-grained audit policy, use the DBMS_FGA.ADD_POLICY procedure. This procedure creates an audit policy using the supplied predicate as the audit condition. Oracle Database executes the policy predicate with the privileges of the user who created the policy. The maximum number of fine-grained policies on any table or view object is 256. Oracle Database stores the policy in the data dictionary table, but you can create the policy on any table or view that is not in the SYS schema.
After you create the fine-grained audit policy, it does not reside in any specific schema, although the definition for the policy is stored in the SYS.FGA$ data dictionary table.
You cannot modify a fine-grained audit policy after you have created it. If you need to modify the policy, drop it and then recreate it.

DBMS_FGA.ADD_POLICY(
   object_schema      VARCHAR2, 
   object_name        VARCHAR2, 
   policy_name        VARCHAR2, 
   audit_condition    VARCHAR2, 
   audit_column       VARCHAR2, 
   handler_schema     VARCHAR2, 
   handler_module     VARCHAR2, 
   enable             BOOLEAN, 
   statement_types    VARCHAR2,
   audit_trail        BINARY_INTEGER IN DEFAULT,
   audit_column_opts  BINARY_INTEGER IN DEFAULT);

In this specification:

• object_schema: Specifies the schema of the object to be audited. (If NULL, the current log-on user schema is assumed.)
  
• object_name: Specifies the name of the object to be audited.
  
• policy_name: Specifies the name of the policy to be created. Ensure that this name is unique.
  
• audit_condition: Specifies a Boolean condition in a row. NULL is allowed and acts as TRUE. See "Auditing Specific Columns and Rows" for more information. If you specify NULL or no audit condition, then any action on a table with that policy creates an audit record, whether or not rows are returned
  
• audit_column: Specifies one or more columns to audit, including hidden columns. If set to NULL or omitted, all columns are audited. These can include Oracle Label Security hidden columns or object type columns. The default, NULL, causes audit if any column is accessed or affected.
  
• handler_schema: If an alert is used to trigger a response when the policy is violated, specifies the name of the schema that contains the event handler. The default, NULL, uses the current schema. See also "Tutorial: Adding an Email Alert to a Fine-Grained Audit Policy".
  
• handler_module: Specifies the name of the event handler. Include the package the event handler is in. This function is invoked only after the first row that matches the audit condition in the query is processed.
  Follow these guidelines:
  
  • Do not create recursive fine-grained audit handlers. For example, suppose you create a handler that executes an INSERT statement on the HR.EMPLOYEES table. The policy that is associated with this handler is for INSERT statements (as set by the statement_types parameter). When the policy is used, the handler executes recursively until the system has run out of memory. This can raise the error ORA-1000: maximum open cursors exceeded or ORA-00036: maximum number of recursive SQL levels (50) exceeded.
    
  • Do not issue the DBMS_FGA.ENABLE_POLICY or DBMS_FGA.DISABLE_POLICY statement from a policy handler. Doing so can raise the ORA-28144: Failed to execute fine-grained audit handler error.
    
• enable: Enables or disables the policy using true or false. If omitted, the policy is enabled. The default is TRUE.
  
• statement_types: Specifies the SQL statements to be audited: INSERT, UPDATE, DELETE, or SELECT only.
  
• audit_trail: Specifies the destination (DB or XML) of fine-grained audit records. Also specifies whether to populate LSQLTEXT and LSQLBIND in FGA_LOG$. However, be aware that sensitive data, such as credit card information, can be recorded in clear text. See "Auditing Sensitive Information" for how you can handle this scenario.
  If you set the audit_trail parameter to XML, then the XML files are written to the directory specified by the AUDIT_FILE_DEST initialization parameter.
  For read-only databases, Oracle Database writes the fine-grained audit trail to XML files, regardless of the audit_trail setting.
  
• audit_column_opts: If you specify more than one column in the audit_column parameter, then this parameter determines whether to audit all or specific columns. See "Auditing Specific Columns and Rows" for more information.
  

BEGIN
  DBMS_FGA.ADD_POLICY(
   object_schema      => 'HR',
   object_name        => 'EMPLOYEES',
   policy_name        => 'chk_hr_employees',
   enable             =>  TRUE,
   statement_types    => 'INSERT, UPDATE, SELECT, DELETE',
   audit_trail        =>  DBMS_FGA.DB+DBMS_FGA.EXTENDED);
END;
/

DBMS_FGA.DISABLE_POLICY(
  object_schema        => 'HR',
  object_name          => 'EMPLOYEES',
  policy_name          => 'chk_hr_employees');

DBMS_FGA.DROP_POLICY(
  object_schema      => 'HR',
  object_name        => 'EMPLOYEES',
  policy_name        => 'chk_hr_employees'); 

SELECT * FROM DBA_AUDIT_POLICIES; 

SYS@DEV1> show parameter audit_trail;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB 

SYS@DEV1> SET SERVEROUTPUT ON
BEGIN
IF
DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(
           DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD)
  THEN
     DBMS_OUTPUT.PUT_LINE('AUD$ is initialized for cleanup');
  ELSE
     DBMS_OUTPUT.PUT_LINE('AUD$ is not initialized for cleanup.');
  END IF;
END;
SYS@DEV1>
AUD$ is not initialized for cleanup.
PL/SQL procedure successfully completed.
SYS@DEV1>

Here is an excerpt form the documentation: DEFAULT_CLEANUP_INTERVAL: Specify the desired default hourly purge interval (for example, 12 for every 12 hours). The DBMS_AUDIT_MGMT procedures use this value to determine how to purge audit records. The timing begins when you run the DBMS_AUDIT_MGMT.INIT_CLEANUP procedure. To update this value later, set the DBMS_AUDIT_MGMT.CLEAN_UP_INTERVAL property of the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY procedure. The DEFAULT_CLEANUP_INTERVAL setting must indicate the frequency in which DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL is called. If you are uncertain about the frequency, set it to an approximate value. You can change this value later on by using the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY procedure.

BEGIN
 DBMS_AUDIT_MGMT.INIT_CLEANUP(
  AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
  DEFAULT_CLEANUP_INTERVAL    => 48 );
END;
/

You can cancel the DBMS_AUDIT_MGMT.INIT_CLEANUP settings, that is, the default cleanup interval, by invoking the DBMS_AUDIT_MGMT.DEINIT_CLEANUP procedure. For example, to cancel all purge settings for the standard audit trail:

BEGIN
 DBMS_AUDIT_MGMT.DEINIT_CLEANUP(
  AUDIT_TRAIL_TYPE  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD);
END;
/

Views:

SELECT * FROM DBA_AUDIT_POLICIES;

SELECT * FROM DBA_FGA_AUDIT_TRAIL;
SELECT * FROM DBA_AUDIT_MGMT_CLEAN_EVENTS;
SELECT * FROM DBA_AUDIT_MGMT_CLEANUP_JOBS;
SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;
SELECT * FROM DBA_AUDIT_MGMT_LAST_ARCH_TS;

Documentation Pointers

1. Oracle® Database Security Guide 11g Release 2 (11.2)
• Verifying Security Access with Auditing
•  Auditing Specific Activities with Fine-Grained Auditing
•  Managing the Database Audit Trail

Oracle 11G Auditing

Password change for the application user is the main reason I want auditing now. I have an user called PASGEN, password has been changed but still some application processes exit which try to connect using the wrong password. Existing profiles make the account immediately locked so I had to (temporarily) alter the profile and make failed_login_attempts unlimited:

ALTER PROFILE nonexpiring LIMIT failed_login_attempts UNLIMITED;

After that I setup an audit to find out whats the issue:

AUDIT SESSION BY PASGEN BY ACCESS WHENEVER NOT SUCCESSFUL;

Then I had an hour or longer wasted trying to find out why I see wrong records from SYS.AUD$. I tried to filter only new entries using ntimestamp# - for unknown reason timestamp# (type DATE) has not been populated. Anway - ntimestamp# appears to be in UTC timezone and it took me some time to figure it out. Finally I found DBA_AUDIT_TRAIL and this is where I should start.

So query which work best for me is:

SELECT os_username,username,userhost,os_process,terminal, action_name,comment_text, timestamp
    FROM dba_audit_trail
WHERE returncode = 1017
    AND timestamp > localtimestamp - 1/24/60 * 5 -- last 5 minutes
    AND username='PASGEN'
ORDER BY timestamp, os_username,userhost,os_process;

And sometimes - to see more details - I put asterisk instead of list of columns for SELECT above.

Another interesting issue with the database based audit trail (standard audit trail) is this log grows quickly if there is a lot of entries to be stored. That's particularly true statement when you have an application trying to connect with the wrong password over and over, sometimes a couple of times per second. So the SYSAUX tablespace may be overflowed quickly. This is the reason I implemented automatic cleanup for this tablespace:



SYS@DEV1> show parameter audit_trail;
NAME TYPE   VALUE
----------- ------------------------------
audit_trail string DB
SYS@DEV1>
SET SERVEROUTPUT ON
BEGIN
  IF
    DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(
    DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD)
  THEN
    DBMS_OUTPUT.PUT_LINE('AUD$ is initialized for cleanup');
  ELSE
    DBMS_OUTPUT.PUT_LINE('AUD$ is not initialized for cleanup.');
  END IF;
END;
/
SYS@DEV1>
AUD$ is not initialized for cleanup.
PL/SQL procedure successfully completed.
SYS@DEV1>

Here is an excerpt form the documentation:

DEFAULT_CLEANUP_INTERVAL: Specify the desired default hourly purge interval (for example, 12 for every 12 hours). The DBMS_AUDIT_MGMT procedures use this value to determine how to purge audit records. The timing begins when you run the DBMS_AUDIT_MGMT.INIT_CLEANUP procedure. To update this value later, set the DBMS_AUDIT_MGMT.CLEAN_UP_INTERVAL property of the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY procedure.

The DEFAULT_CLEANUP_INTERVAL setting must indicate the frequency in which DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL is called. If you are uncertain about the frequency, set it to an approximate value. You can change this value later on by using the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY procedure.

BEGIN
 DBMS_AUDIT_MGMT.INIT_CLEANUP(
  AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
  DEFAULT_CLEANUP_INTERVAL    => 48 );
END;
/

You can cancel the DBMS_AUDIT_MGMT.INIT_CLEANUP settings, that is, the default cleanup interval, by invoking the DBMS_AUDIT_MGMT.DEINIT_CLEANUP procedure.
For example, to cancel all purge settings for the standard audit trail:

BEGIN
 DBMS_AUDIT_MGMT.DEINIT_CLEANUP(
  AUDIT_TRAIL_TYPE  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD);
END;
/

Views

SELECT * FROM DBA_FGA_AUDIT_TRAIL;
SELECT * FROM DBA_AUDIT_MGMT_CLEAN_EVENTS;
SELECT * FROM DBA_AUDIT_MGMT_CLEANUP_JOBS;
SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;
SELECT * FROM DBA_AUDIT_MGMT_LAST_ARCH_TS;

 

Documentation Pointers

1. Oracle® Database Security Guide 11g Release 2 (11.2)
• Verifying Security Access with Auditing
•  Auditing Specific Activities with Fine-Grained Auditing
•  Managing the Database Audit Trail

Statistics in 11.2

Collecting Stats Faster

The problem is collecting optimizer statistics with DBMS_STATS.GATHER_SCHEMA_STATS happens serially and takes incredibly long time for huge databases. Particularly when we have LOB tables. Usually I need rebuild statistics after a schema is imported from previous version of the database to the latest one. So I don't care what load it brings to the database - it might be very intense operation but I want it to finish as soon as possible. And usually the load incurred by the usual GATHER_SCHEMA_STATS procedure is minimal so making it running in parallel would be an excellent idea.

One night, when statistics collection already taken a long time, I decided to terminated and go this way:

BEGIN

   DBMS_STATS.GATHER_SYSTEM_STATS('INTERVAL', 180); 

   DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','TRUE');

   DBMS_STATS.GATHER_SCHEMA_STATS( 'MYUSER',
        degree  => DBMS_STATS.AUTO_DEGREE,
        options => 'GATHER',
        cascade => TRUE,
        no_invalidate => FALSE
);

END;

/

The effect was terrific. Mainly because it depends on the parameter job_queue_processes and it was set to 1000. So the database was really under a load for some time but the process finished in 30 minutes. I don't think the job_queue_processed is set to a reasonable parameter here for daily operation but at least it worked for this case!
I specified options=>'GATHER' is required when you want to collects statistics for all objects in the schema. With the default 'GATHER AUTO' Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When GATHER AUTO is specified, the only additional valid parameters are ownname, stattab, statid, objlist and statown; all other parameter settings are ignored. Returns a list of processed objects.

Checking the current status of concurrent statistics collection:

SELECT DBMS_STATS.GET_PREFS('CONCURRENT') pref FROM dual;

Resources

1. GATHER_SCHEMA_STATS in 11.2
2. Concurrent Statistics Gathering.
3. Understanding Optimizer Statistics (PDF).

Oracle XDB

What XDB database is? I didn't use it until recently when I had to assign some network privileges (see my other post on Fine Grained Access). And today I found a database where DBMS_NETWORK_ACL package is not installed. Google redirects me to information about XDB database missing. My first try failed, unfortunately I didn't spool the output to a file. It was because a dedicated XDB tablespace was missing - the script doesn't handle that automatically. Fortunately there is a lot of resources on Internet how to drop and recreate XDB:

@?/rdbms/admin/catnoqm.sql
drop trigger sys.xdb_installation_trigger;
drop trigger sys.dropped_xdb_instll_trigger;
drop table dropped_xdb_instll_tab;


CREATE tablespace XDB datafile '+DATA' size 10M autoextend on next 10m maxsize unlimited;

SPOOL /tmp/xdb.log REPLACE
@?/rdbms/admin/catqm.sql xdbpasswd XDB TEMPSPACE NO
SPOOL OFF


So far I have no idea what less for XDB is actually used.

External Links

http://www.dbatools.net/experience/oracle_xmldb_install.html
http://www.oracle-wiki.net/startdocshowtoinstallxmldb

Oracle 11g Statistics Collection (AUTOTASK)

      I noticed this subject comes back over and over: no statistics gathering job seems to be activated. Well, usually it is enabled until something unexpected has been done in the database. At 11g gathering optimizer stats it is enabled by default. It doesn’t exist (as in 10g) as a dedicated job anymore. Right now it is maintained using DBMS_AUTOTASK_ADMIN_PACKAGE. When I run a query checking the state of “auto optimizer stats collection” I can see it running every day.

SELECT * FROM DBA_AUTOTASK_JOB_HISTORY
WHERE client_name = 'auto optimizer stats collection'
ORDER BY job_start_time DESC;