How to create the PLUSTRACE role in order to use the AUTOTRACE feature in sqlplus

Create the PLAN_TABLE in the schema you want to use it for (in this example, SCOTT):

CONNECT SCOTT 
@$ORACLE_HOME/rdbms/admin/utlxplan.sql 

Create the PLUSTRACE role:

CONNECT / AS SYSDBA 
@$ORACLE_HOME/sqlplus/admin/plustrce.sql 

Grant the PLUSTRACE role to SCOTT:

CONNECT / AS SYSDBA 
grant plustrace to scott;

You are now able to use the autotrace feature of sqlplus for user SCOTT.

Source: Oracle Documentation

How to display the currently used ORACLE_HOME from sqlplus

Thanks to my former colleague Laurent Schneider for pointing out a way to display the currently used ORACLE_HOME from within sqlplus:

set lines 200
col oracle_home format a40
set autoprint on
var oracle_home varchar2(255)
exec dbms_system.get_env('ORACLE_HOME',:ORACLE_HOME);

PL/SQL procedure successfully completed.

ORACLE_HOME
------------------------------

/u01/oracle/product/11204

Note: To automatically display the value of a bind variable created with VARIABLE, use the SET AUTOPRINT command.
ON or OFF controls whether SQL*Plus automatically displays bind variables (referenced in a successful PL/SQL block or used in an EXECUTE command).

Source: Oracle Documentation

How to solve ORA-27125: unable to create shared memory segment on AIX

When starting an Oracle instance on a new AIX server, the following error may occur:

ORA-27125: unable to create shared memory segment
IBM AIX RISC System/6000 Error: 1: Not owner

The situation is resolved by setting "capabilities" on the oracle operating system user, as follows:

[root@server1] lsuser -a capabilities oracle
oracle
[root@tsu0emdb02] chuser capabilities=CAP_BYPASS_RAC_VMM,CAP_PROPAGATE oracle
[root@tsu0emdb02] lsuser -a capabilities oracle
oracle capabilities=CAP_BYPASS_RAC_VMM,CAP_PROPAGATE

You can now start your instance:

server1:testdb1>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 12 12:10:04 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 5344731136 bytes
Fixed Size                  2255784 bytes
Variable Size            1392510040 bytes
Database Buffers         3942645760 bytes
Redo Buffers                7319552 bytes
SQL>

What constitute "default auditing" under traditional auditing?

From the Oracle Documentation:

"When you use Database Configuration Assistant (DBCA) to create a new database, Oracle Database configures the database to audit the most commonly used security-relevant SQL statements and privileges"

and

"If you manually create a database, then you should run the secconf.sql script to apply the default audit settings to your database"

Oracle Database audits the following privileges by default:

ALTER ANY PROCEDURE

CREATE ANY LIBRARY

DROP ANY TABLE

ALTER ANY TABLE

CREATE ANY PROCEDURE

DROP PROFILE

ALTER DATABASE

CREATE ANY TABLE

DROP USER

ALTER PROFILE

CREATE EXTERNAL JOB

EXEMPT ACCESS POLICY

ALTER SYSTEM

CREATE PUBLIC DATABASE LINK

GRANT ANY OBJECT PRIVILEGE

ALTER USER

CREATE SESSION

GRANT ANY PRIVILEGE

AUDIT SYSTEM

CREATE USER

GRANT ANY ROLE

CREATE ANY JOB

DROP ANY PROCEDURE

Oracle Database audits the following SQL shortcuts by default:

ROLE

SYSTEM AUDIT

PUBLIC SYNONYM

DATABASE LINK

PROFILE

SYSTEM GRANT

Remember that secconf.sql turns on audting regardless of your database using Unified Auditing or Traditional Auditing.

How?

When executed manually, the DBA is prompted for which type of auditing style that should be considered:

sqlplus / as sysdba @secconf.sql

Session altered.


Profile altered.

Do you wish to configure 11g style Audit Configuration OR
Do you wish to configure 12c Unified Audit Policies?
Enter RDBMS_11G for former or RDBMS_UNIAUD for latter
Enter value for 1: RDBMS_11G
old   7:   USER_CHOICE := '&1';
new   7:   USER_CHOICE := 'RDBMS_11G';

What I have found, is that if you intend to harden your Traditional Auditing policies by executing the script in a database where the Unified Auditing polices exist but is disabled, or a database running in "Mixed mode" auditing, the script will fail:

DECLARE
*
ERROR at line 1:
ORA-46358: Audit policy ORA_ACCOUNT_MGMT already exists.
ORA-06512: at line 9

A simple workaround in such a case is to simply comment out the code that is irrelevant to your desired type of auditing, and rerun the script.

You can disable default auditing if you wish, see the section "Disabling and Enabling Default Audit Settings"

To check whether or not default auditing has been actived, you can query the view DBA_PRIV_AUDIT_OPTS, which describes current system privileges being audited across the system and by user. If the column USERNAME is NULL, you have introduced system-wide auditing.

How to convert stored outlines to use SQL Plan Baselines

If you have been using stored outlines for plan stability in versions prior to Oracle 11g, you should migrate them to use SQL Plan Baselines instead. Stored outlines is, according to oracle, "a legacy technique for providing plan stability".

My database had a number of stored outlines:

SELECT OWNER, CATEGORY, USED, MIGRATED,COUNT(*) 
FROM   DBA_OUTLINES
GROUP BY OWNER,CATEGORY,USED,MIGRATED
ORDER BY MIGRATED;

OWNER	CATEGORY	USED	MIGRATED	COUNT(*)
USER1	DEFAULT	USED	MIGRATED	3
USER2	DEFAULT	USED	MIGRATED	1
USER3	DEFAULT	USED	NOT-MIGRATED	7
USER1	DEFAULT	USED	NOT-MIGRATED	7
USER4	DEFAULT	USED	NOT-MIGRATED	1
USER2	DEFAULT	USED	NOT-MIGRATED	36

I created the following pl/sql to convert them to SQL Plan Baselines using the package DBMS_SPM.MIGRATE_STORED_OUTLINE:

SET TRIMSPOOL ON
SET LINES 200
SET PAGES 200
ALTER SESSION SET NLS_LANGUAGE='AMERICAN'; <-- to get English messages during execution
SPOOL CONVERT_OUTLINES.LOG
SET SERVEROUTPUT ON
DECLARE
  L_CLOB            CLOB; -- will display the resulting report

  CURSOR C1 IS
    SELECT OWNER,NAME
    FROM DBA_OUTLINES
    WHERE MIGRATED = 'NOT-MIGRATED';

    C1_REC C1%ROWTYPE;

 BEGIN
    DBMS_OUTPUT.ENABLE( 1000000 );

    IF NOT C1%ISOPEN THEN
       OPEN C1;
    END IF;
 
     LOOP
       FETCH C1 INTO C1_REC;
       EXIT WHEN C1%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE('Now converting: ' || C1_REC.OWNER || '.' || C1_REC.NAME);
       L_CLOB := DBMS_SPM.MIGRATE_STORED_OUTLINE( ATTRIBUTE_NAME=>'OUTLINE_NAME', ATTRIBUTE_VALUE=>C1_REC.NAME, FIXED=>'NO');
       DBMS_OUTPUT.PUT_LINE( L_CLOB );
     END LOOP;
    CLOSE C1;
END;
/
EXIT

The resulting log files shows that several of the stored outlines could not be converted:

Now converting: USER2.SYS_OUTLINE_11021513055564321
-------------------------------------------------------------------------------

               Migrate Stored Outline to SQL Plan Baseline

Report
-------------------------------------------------------------------------------

Summary:
--------

Number of stored outlines to be migrated: 1
Stored outlines migrated successfully: 0
Stored outlines failed to be migrated: 1

Summary of Reasons for failure:
-------------------------------

Number of invalid stored outlines: 1

Details on stored outlines not migrated or name changes during migration:
-------------------------------------------------------------------------

* Notes on name change:
* New SQL plan baselines are assigned the same names as their original stored 
* outlines. If a stored outline has the same name as an existing
* SQL plan baseline, a system generated name is used for the new
* SQL plan baseline.

I then checked with the developers. It turned out that the outlines that didn't convert properly were remnants from the database when it was totally different laid out - the schemas had by now diverged and I could simply ignore these obsolete outlines.

So the last step was simply to generate a drop-script for the non-migrated outlines and then execute these:

SELECT 'DROP OUTLINE ' || NAME || ';'
FROM DBA_OUTLINES
WHERE MIGRATED = 'NOT-MIGRATED';

Login storm against database caused exhausted library cache

One of our databases experienced massive contention in the shared pool, in form of wait events alerted as "library cache locks".

The database was very small indeed, so my natural instinct was to throw some more memory at the virtual host, and rearrange the memory parameters.

This turned out to be a misconception; the resources were sufficient for the instance to work properly.

The problem was caused by an incorrect password configuration on the application server.

What we could observe was:

A totally exhausted shared pool, caused by "library cache lock"

The SQL that seemed to be repeatedly executed was

SELECT /*+ connect_by_filtering */
          privilege#, LEVEL
      FROM sysauth$
CONNECT BY grantee# = PRIOR privilege# AND privilege# > 0
START WITH grantee# = :1 AND privilege# > 0;


SELECT privilege#
  FROM sysauth$
WHERE (grantee# = :1 OR grantee# = 1) AND privilege# > 0;

The V$EVENT_NAME view showed that the wait event was accompanied by the additional information found in the columns parameter1 through parameter3, which turned out to be helpful further on:

select  name, wait_class,parameter1,parameter2,parameter3
from v$event_name
where wait_class = 'Concurrency'
and name = 'library cache lock';

NAME	WAIT_CLASS	PARAMETER1	PARAMETER2	PARAMETER3
library cache lock	Concurrency	handle address	lock address	100*mode+namespace

Further research showed that the problem was due to a built-in delay between failed login attempts in Oracle 11g:

"The 'library cache lock' wait is seen due to the fact that the account status gets updated due to incorrect login.
To prevent password guessing attack, there's a sleep() in the code when incorrect login attempts exceed count of 3.
And because of this sleep() you see a wait on library cache, as the process is yet to release the lock."

In release 11.1.0.7, patch 7715339 was released to remove this delay.

In release 11.2.X, the DBA must set an event to remove the delay, as follows:

alter system set events '28401 trace name context forever, level 1'; 

According to Oracle, the purpose of the built-sleep is to make it harder to succeed in a "password guessing attack", particularly in cases where FAILED_LOGIN_ATTEMPTS is set to UNLIMITED. Oracle Development is pointing out that disabling the sleep-function is not recommended. A better solution is to set the FAILED_LOGIN_ATTEMPTS to a reasonable value.
When the number of failed login attempts for a session hits the limit, the account will be locked. Subsequent logon attempts with incorrect password will then be rejected immediately without any contention in the library cache.

See Bug 15882590 : 'LIBRARY CACHE LOCK' DURING WRONG PASSWORD LOGON ATTEMPTS on My Oracle Support (MOS) for further information.

How to use the case statement in a shell script, with case-insensitive input

Use the pipe (|) character for each possible case:

echo "TABLESPACE REPORT FOR $ORACLE_SID"

stty echo
   echo "All tablespaces or specific tablespace? [A|S]"
   read answer
stty echo

case $answer in
A|a) echo "All tablespaces selected...please wait...";
    getFiles A;;
S|s) echo "Which tablespace?";
    read wt;
    getFiles $wt;;
*) echo "Only A or S are supported parameters.";
    exit 1;;
esac