Undocumented parameter to DBMS_XPLAN.DISPLAY_CURSOR

The documentation
http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_xplan.htm#i996786

tops out at

format=>'ALL'

DBMS_XPLAN.DISPLAY_CURSOR(
   sql_id           IN  VARCHAR2  DEFAULT  NULL,
   cursor_child_no  IN  NUMBER    DEFAULT  0, 
   format           IN  VARCHAR2  DEFAULT  'TYPICAL');

What if we wanted MORE ... 

First let's see what we get with 'ALL' 

set long 32000
set linesize 32000
set pagesize 0
-- set autotrace on
alter system set result_cache_mode=manual;
--select cust_first_name,cust_last_name from customers where cust_id=103379;
-- set autotrace off
select cust_first_name,cust_last_name from customers where cust_id=103379;
select * from table(dbms_xplan.display_cursor(format=>'ALL'));
quit;

System altered.

Alana                Yoon

SQL_ID  dh88td3czp639, child number 0
-------------------------------------
select cust_first_name,cust_last_name from customers where
cust_id=103379

Plan hash value: 4238351645

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |     1 |    20 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | CUSTOMERS_PK |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / CUSTOMERS@SEL$1
   2 - SEL$1 / CUSTOMERS@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CUST_ID"=103379)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "CUST_FIRST_NAME"[VARCHAR2,20], "CUST_LAST_NAME"[VARCHAR2,40]
   2 - "CUSTOMERS".ROWID[ROWID,10]


32 rows selected.

OK all as expected. 

What if we changed 'ALL' to 'ADVANCED' ?

set long 32000
set linesize 32000
set pagesize 0
-- set autotrace on
alter system set result_cache_mode=manual;
--select cust_first_name,cust_last_name from customers where cust_id=103379;
-- set autotrace off
select cust_first_name,cust_last_name from customers where cust_id=103379;
select * from table(dbms_xplan.display_cursor(format=>'ADVANCED'));
quit;

System altered.

Alana                Yoon

SQL_ID  dh88td3czp639, child number 0
-------------------------------------
select cust_first_name,cust_last_name from customers where
cust_id=103379

Plan hash value: 4238351645

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |     1 |    20 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | CUSTOMERS_PK |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / CUSTOMERS@SEL$1
   2 - SEL$1 / CUSTOMERS@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "CUSTOMERS"@"SEL$1" ("CUSTOMERS"."CUST_ID"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CUST_ID"=103379)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "CUST_FIRST_NAME"[VARCHAR2,20], "CUST_LAST_NAME"[VARCHAR2,40]
   2 - "CUSTOMERS".ROWID[ROWID,10]


46 rows selected.

! Look at the extra information provided.    Have a good one ! 

Finding the actual execution plan for a SQL Statement.

Issuing Explain plan does just that - doesn't actually give you what actually happened.

Can we get better  ?


set long 32000
set linesize 32000
set autotrace on
 select cust_first_name,cust_last_name from customers where cust_id=103379;
quit;

CUST_FIRST_NAME      CUST_LAST_NAME
-------------------- ----------------------------------------
Alana                Yoon


Execution Plan
----------------------------------------------------------
Plan hash value: 4238351645

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    20 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |     1 |    20 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | CUSTOMERS_PK |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CUST_ID"=103379)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        617  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

This is just autotrace.  Lets move up the stack a bit. 

Using DBMS_XPLAN 

... 

If we don't suppply any parameters to DISPLAY_CURSOR then " in which case the plan of the last cursor executed by the session is displayed." 

( see http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_xplan.htm#i996786) 

set long 32000

set linesize 32000

set pagesize 0

-- set autotrace on

select cust_first_name,cust_last_name from customers where cust_id=103379;

-- set autotrace off

select * from table(dbms_xplan.display_cursor);

quit;

Alana                Yoon

SQL_ID  dh88td3czp639, child number 0

-------------------------------------

select cust_first_name,cust_last_name from customers where

cust_id=103379

Plan hash value: 4238351645

--------------------------------------------------------------------------------------------

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |              |       |       |     2 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |     1 |    20 |     2   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | CUSTOMERS_PK |     1 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("CUST_ID"=103379)

20 rows selected.

This is very useful because it takes the work out of guessing with Autotrace etc. 

Finding the location of the Oracle Alert Log 9i/10g/11g - updated for 12c

In this short post I'll try and see if we can provide a sensible way of establishing the location of the alert log for an oracle database, one that works for 9i, 10g, and 11g.

The Oracle Alert log tells us what major events have happened to the Oracle database.

In times gone by this was a nice text formatted file that most Oracle DBAs knew how to read.  In 11g this was changed to an XML formatted file, with the advent of the ADR  which is a first stab at automating the management of the large quantity of detailed trace file that Oracle manages.

We won't go into the detail of the ADR here except to note that this is where the alert log hangs out in recent versions of Oracle.

To cope with the backlash of complaints of people who didn't want to read an XML formatted alert log, Oracle continue to write the traditional form of the log as well simultaneously.  This is perfect for our purposes.

So this post will try and establish some ways that you can find the alert log.

Some initial problems you may run into

1:  The database is down -  if it is down, then obviously Oracle cannot tell you what the location is as SQLPLUS is down.
2. There are multiple alert logs - in a RAC database for example there are several alert logs, one for each instance
3.  you don't have access to the file location -  being a database user is no guarantee of being able to access the alert log file location (even being DBA may not be sufficient ! - depends on the sandbox the sysadmins have you in)

However it breaks down into two basic cases
Database is Up or Database is Down

Database is Up
If the database is Up we can ask it where it is writing the alert Log

• Assumptions
• User has access to SQLPLUS
• User has access to v$parameter 
• user can issue SHOW PARAMETER

The alert log location we want to see can be found in a variety of ways.

*** UPDATED - FOR 12c this doesn't work any more !!! ***

Read to the end for 12c answer..... 

Show Parameter Background_DUMP_DEST 

SQL> show parameter background_dump_dest

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

background_dump_dest                 string      /u01/app/oracle/diag/rdbms/rep

                                                 o/REPO/trace

SQL>

So that is a location of the oracle alert log, works for all recent versions, 8i,9i,10g,11g

Of course this isn't easy to capture into a variable so why not 

SQL> set linesize 300

SQL> column value format a300

SQL> select value from v$parameter where name='background_dump_dest';

VALUE

------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/repo/REPO/trace

SQL>

So that is a location of the oracle alert log, works for all recent versions, 8i,9i,10g,11g

Which is a little easier. 

Another way of getting pretty much the same thing is 

( This one actually probes the ADR for the trace directory rather than looking at the legacy BDD  - but only works in ADR databases  - 11g and later ) 

SQL> select value from v$diag_info where name='Diag Trace';

VALUE

------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/repo/REPO/trace

SQL>

So that is a location of the oracle alert log, works for 11g

These techniques work when the database is started NOMOUNT,MOUNT or OPEN .. but they do not work, of course, when the database is down, as mentioned above. 

Database is Down 

OK if the db is down how do we find the alert log ?  

There are a number of ways to find the alert log. 

Simple :  search the operating system directories for it. 

The alert log will be named alert_SID.log where SID is the SID of the instance ( thus a rac DB may have many alert logs, and a Data Guard pair will have two 

if we assume our SID=REPO then by the above convention the alert log will be named alert_REPO.log 

We will not go into how to find the SID here, we assume this is well known. 

so we know the name of the file how do  we search for it. 

Linux/Unix 

[oracle@oracloud3 /]$ find / -name alert_REPO.log 2>/dev/null

/u01/app/oracle/diag/rdbms/repo/REPO/trace/alert_REPO.log

[oracle@oracloud3 /]$

We redirect STDERR to null because we don't want to read about all the stuff our Oracle account doesn't have access to. 

Windows 

Rather than do command line ( dir alert_repo.log /s )  just use the GUI to find the file, alternatively if that's not an option download and use something like Agent Ransack 

OK so that's the simplistic method 

What about a little more intelligence than that 

If we were using an older database we used to be obliged to enter the BACKGROUND_DUMP_DEST into the initialization parameter file ( PFILE )... so we could go and find that and see if it's there. 

For this we need ORACLE_HOME envar set. 

On Linux/Unix 

cd $ORACLE_HOME/dbs 

on Windows 

cd %ORACLE_HOME%/database

if a pfile is in use we will see it as initSID.ora, so following the above example it will be called initREPO.ora 

we can then edit this using our favourite text editor to find the above variable and then we have the value. 

OK ... but the current 11g, this doesn't work because 

a)  newer databases default to using an SPFILE ( binary version of the spfile ) 

b)  The background dump dest variable is defaulted in 11g by the ADR so it does not need to be specified. 

So in order to find the alert log we can try and put together a "where Oracle thinks it might put it " from analyzing the SPFILE. 

In order to do this we take into account the following assumptions/axioms. 

ADR Top level Directory is usually  ORACLE_BASE/diag 

RDBMS Logs are usually stored in ADR_base/rdbms

This is then broken down  into DB_NAME/sid to take account of the fact that there may be a shared ADR directory tree across multiple instances of the same database ( i.e. RAC in some situations ) 

so we can complete the location of the alert log in 11g (Only )
as

ORACLE_BASE/diag/rdbms/DB_NAME/SID/trace

or
/u01/app/oracle/diag/rdbms/repo/REPO/trace

This assumes a normally put-together database. 

If you haven't bothered following an OFA architecture then you will probably be defaulted as per the manual
"Derived from the value of the $ORACLE_BASE environment variable. If $ORACLE_BASE is not set, then derived fromORACLE_BASE as set by the Oracle Universal Installer. If ORACLE_BASE is not set, then $ORACLE_HOME/rdbms/log is the default location "

PS to find DB_NAME and ORACLE_BASE we may need to look at the spfile

this is difficult to view as it is a binary file so let's look at 'strings spfileREPO.ora' to get what we want.

Hope that helps .... several different ways to get what you need.



12c (12.1.0.2)  answer :   Background_dump_dest no longer useful as they have changed what that parameter is doing

$ORACLE_BASE/diag/rdbms/DB_NAME/SID/trace is still useful to find the alert log

core_dump_dest can  be used to get close

core_dump_dest = /u01/app/oracle/diag/rdbms/repo/REPO/cdump   and then cd ../trace

It is an ongoing mystery why this is made difficult to find.

Here's a SQL script I wrote to find the alert log on Linux  for 11g and 12c databases

set heading off pages 0 trimspool on lines 120 feedback off echo off
--  Chris Slattery  November 2015
--  find the Alert Log Location in recent versions of Oracle 11g onwards
 with  DD (DDV) as (SELECT VALUE DDV FROM V$parameter WHERE NAME='diagnostic_dest'),
       LCD (LCDV)as (select LOWER(value) LCDV FROM V$parameter WHERE NAME='db_name'),
       INST (INSTV) as (select UPPER(value) INSTV FROM V$parameter WHERE NAME='instance_name')
       select * from (select DDV||'/diag/rdbms/'||LCDV||'/'||INSTV||'/trace' from DD,LCD,INST ) where rownum= 1;
quit;

/

to run it

sqlplus -S "/ as sysdba" @get_alert_loc.sql
/u01/app/oracle/diag/rdbms/repo/REPO/trace

ls -lrt /u01/app/oracle/diag/rdbms/repo/REPO/trace/alert_REPO.log

-rw-r-----. 1 oracle dba 164844 Nov 26 10:09 /u01/app/oracle/diag/rdbms/repo/REPO/trace/alert_REPO.log

so that's OK

Note will be different on Windows etc but this should cover the majority of cases.  Yes I know I am making assumptions with Lower and Upper 

Hope this helps people .. 

Upgrading OBIEE 11.1.1.5 to 11.1.1.6

Hi :

Re the  above

basic steps

1 : download OBIEE 11.1.1.6 from edelivery
2:  shutdown everything
3:  run the main installer as software only
4: run PSA to upgrade the database bits  ( both MDS and BIPLATFORM )
5:  now upgradenonj2eeapp.sh for both main OPMN and WT OPMN
6: now run WLST to execute bi-upgrade.py
7: now upgrade catalogs via setting upgradeandexit=true in instanceconfig.xml,restart sawserver, change the flag back and bounce sawserver again
8:edit the BIADMINISTRATOR principle and add the extra permissions

The documentation is incorrect
http://docs.oracle.com/cd/E23943_01/doc.1111/e16793/patch_set_installer.htm#BGBEAFBG

if you are starting from 11.1.1.5 jumps you past the required steps 6,7,8 above ... just something to watch out for ...
Correct Post-Patch for OBIEE is

http://docs.oracle.com/cd/E23943_01/doc.1111/e16793/patch_set_installer.htm#CHDJFHHJ


enjoy !



Happy new year if not blogging before then.

Just got Discoverer Plus 10.1.2.3, Excel 2013 and Windows 8 all working together !  Yes I AM BORED

Very good post on how to do post-mortems in a crash scenario

http://www.slideshare.net/danmil30/how-to-run-a-5-whys-with-humans-not-robots#btnNext

OEL6 and oracle-rdbms-server-11gR2-preinstall

This used to be called oracle-validated but it got changed to oracle-rdbms-server-11gR2-preinstall as it sounded better perhaps ?

This RPM is very useful as it allows you to bypass all the usual timewasting in getting simple Linux basic system setup done to allow the Oracle RDBS ( whoops RDBMS)  database be installed.

1. Oracle Public YUM Repositories are now free access, as is basic OEL itself.

2. OEL6 comes preconfigured with access to the public repositories ( or you can build your own ! )

3.  This is a little more difficult in OEL5 as you have to configure up the public YUM repos yourself  but they ARE available

Therefore if your machine has HTTP access to the internet you can set it  up REALLY easily.

Let's have a look at this.

Assumptions :

1.  Clean install of OEL6
2.  yum list has been done ( i.e. the repo has correctly populated with the updated list of packages etc. )


Finding the rpm 

[root@oel6cleanzero ~]# yum list|grep oracle

oracle-logos.noarch                       60.0.11-9.el6               @anaconda-                                                    OracleLinuxServer-201206261930.x86_64/6.3

oraclelinux-release.x86_64                6:6Server-3.0.2             @anaconda-                                                    OracleLinuxServer-201206261930.x86_64/6.3

oraclelinux-release-notes.x86_64          6Server-7                   @anaconda-                                                    OracleLinuxServer-201206261930.x86_64/6.3

oracle-rdbms-server-11gR2-preinstall.x86_64

oracleasm-support.x86_64                  2.1.5-1.el6                 ol6_latest

[root@oel6cleanzero ~]# yum list|grep oracle

oracle-logos.noarch                       60.0.11-9.el6               @anaconda-OracleLinuxServer-201206261930.x86_64/6.3

oraclelinux-release.x86_64                6:6Server-3.0.2             @anaconda-OracleLinuxServer-201206261930.x86_64/6.3

oraclelinux-release-notes.x86_64          6Server-7                   @anaconda-OracleLinuxServer-201206261930.x86_64/6.3

oracle-rdbms-server-11gR2-preinstall.x86_64

oracleasm-support.x86_64                  2.1.5-1.el6                 ol6_latest

There it is at the end ! 

Let's install it ....  use the -y flag to answer yes to any prompts 

 yum -y install oracle-rdbms-server-11gR2-preinstall.x86_64

Loaded plugins: refresh-packagekit, security

Setting up Install Process

Resolving Dependencies

--> Running transaction check

---> Package oracle-rdbms-server-11gR2-preinstall.x86_64 0:1.0-6.el6 will be installed

--> Processing Dependency: gcc-c++ for package: oracle-rdbms-server-11gR2-preinstall-1.0-6.el6.x86_64

--> Processing Dependency: gcc for package: oracle-rdbms-server-11gR2-preinstall-1.0-6.el6.x86_64

--> Processing Dependency: libaio-devel for package: oracle-rdbms-server-11gR2-preinstall-1.0-6.el6.x86_64

--> Processing Dependency: libstdc++-devel for package: oracle-rdbms-server-11gR2-preinstall-1.0-6.el6.x86_64

--> Processing Dependency: glibc-devel for package: oracle-rdbms-server-11gR2-preinstall-1.0-6.el6.x86_64

--> Processing Dependency: compat-libstdc++-33 for package: oracle-rdbms-server-11gR2-preinstall-1.0-6.el6.x86_64

--> Processing Dependency: ksh for package: oracle-rdbms-server-11gR2-preinstall-1.0-6.el6.x86_64

--> Processing Dependency: compat-libcap1 for package: oracle-rdbms-server-11gR2-preinstall-1.0-6.el6.x86_64

--> Running transaction check

---> Package compat-libcap1.x86_64 0:1.10-1 will be installed

---> Package compat-libstdc++-33.x86_64 0:3.2.3-69.el6 will be installed

---> Package gcc.x86_64 0:4.4.6-4.el6 will be installed

--> Processing Dependency: cpp = 4.4.6-4.el6 for package: gcc-4.4.6-4.el6.x86_64

--> Processing Dependency: cloog-ppl >= 0.15 for package: gcc-4.4.6-4.el6.x86_64

---> Package gcc-c++.x86_64 0:4.4.6-4.el6 will be installed

--> Processing Dependency: libmpfr.so.1()(64bit) for package: gcc-c++-4.4.6-4.el6.x86_64

---> Package glibc-devel.x86_64 0:2.12-1.80.el6_3.6 will be installed

--> Processing Dependency: glibc-headers = 2.12-1.80.el6_3.6 for package: glibc-devel-2.12-1.80.el6_3.6.x86_64

--> Processing Dependency: glibc = 2.12-1.80.el6_3.6 for package: glibc-devel-2.12-1.80.el6_3.6.x86_64

--> Processing Dependency: glibc-headers for package: glibc-devel-2.12-1.80.el6_3.6.x86_64

---> Package ksh.x86_64 0:20100621-16.el6 will be installed

---> Package libaio-devel.x86_64 0:0.3.107-10.el6 will be installed

---> Package libstdc++-devel.x86_64 0:4.4.6-4.el6 will be installed

--> Running transaction check

---> Package cloog-ppl.x86_64 0:0.15.7-1.2.el6 will be installed

--> Processing Dependency: libppl_c.so.2()(64bit) for package: cloog-ppl-0.15.7-1.2.el6.x86_64

--> Processing Dependency: libppl.so.7()(64bit) for package: cloog-ppl-0.15.7-1.2.el6.x86_64

---> Package cpp.x86_64 0:4.4.6-4.el6 will be installed

---> Package glibc.x86_64 0:2.12-1.80.el6 will be updated

--> Processing Dependency: glibc = 2.12-1.80.el6 for package: glibc-common-2.12-1.80.el6.x86_64

---> Package glibc.x86_64 0:2.12-1.80.el6_3.6 will be an update

---> Package glibc-headers.x86_64 0:2.12-1.80.el6_3.6 will be installed

--> Processing Dependency: kernel-headers >= 2.2.1 for package: glibc-headers-2.12-1.80.el6_3.6.x86_64

--> Processing Dependency: kernel-headers for package: glibc-headers-2.12-1.80.el6_3.6.x86_64

---> Package mpfr.x86_64 0:2.4.1-6.el6 will be installed

--> Running transaction check

---> Package glibc-common.x86_64 0:2.12-1.80.el6 will be updated

---> Package glibc-common.x86_64 0:2.12-1.80.el6_3.6 will be an update

---> Package kernel-uek-headers.x86_64 0:2.6.32-300.39.1.el6uek will be installed

---> Package ppl.x86_64 0:0.10.2-11.el6 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================================================

 Package                                         Arch              Version                              Repository             Size

====================================================================================================================================

Installing:

 oracle-rdbms-server-11gR2-preinstall            x86_64            1.0-6.el6                            ol6_latest             15 k

Installing for dependencies:

 cloog-ppl                                       x86_64            0.15.7-1.2.el6                       ol6_latest             93 k

 compat-libcap1                                  x86_64            1.10-1                               ol6_latest             17 k

 compat-libstdc++-33                             x86_64            3.2.3-69.el6                         ol6_latest            183 k

 cpp                                             x86_64            4.4.6-4.el6                          ol6_latest            3.7 M

 gcc                                             x86_64            4.4.6-4.el6                          ol6_latest             10 M

 gcc-c++                                         x86_64            4.4.6-4.el6                          ol6_latest            4.7 M

 glibc-devel                                     x86_64            2.12-1.80.el6_3.6                    ol6_latest            970 k

 glibc-headers                                   x86_64            2.12-1.80.el6_3.6                    ol6_latest            600 k

 kernel-uek-headers                              x86_64            2.6.32-300.39.1.el6uek               ol6_latest            716 k

 ksh                                             x86_64            20100621-16.el6                      ol6_latest            684 k

 libaio-devel                                    x86_64            0.3.107-10.el6                       ol6_latest             13 k

 libstdc++-devel                                 x86_64            4.4.6-4.el6                          ol6_latest            1.5 M

 mpfr                                            x86_64            2.4.1-6.el6                          ol6_latest            156 k

 ppl                                             x86_64            0.10.2-11.el6                        ol6_latest            1.3 M

Updating for dependencies:

 glibc                                           x86_64            2.12-1.80.el6_3.6                    ol6_latest            3.8 M

 glibc-common                                    x86_64            2.12-1.80.el6_3.6                    ol6_latest             14 M

Transaction Summary

====================================================================================================================================

Install      15 Package(s)

Upgrade       2 Package(s)

Total download size: 43 M

Downloading Packages:

(1/17): cloog-ppl-0.15.7-1.2.el6.x86_64.rpm                                                                  |  93 kB     00:00

(2/17): compat-libcap1-1.10-1.x86_64.rpm                                                                     |  17 kB     00:00

(3/17): compat-libstdc++-33-3.2.3-69.el6.x86_64.rpm                                                          | 183 kB     00:01

(4/17): cpp-4.4.6-4.el6.x86_64.rpm                                                                           | 3.7 MB     00:08

(5/17): gcc-4.4.6-4.el6.x86_64.rpm                                                                           |  10 MB     00:29

(6/17): gcc-c++-4.4.6-4.el6.x86_64.rpm                                                                       | 4.7 MB     00:10

(7/17): glibc-2.12-1.80.el6_3.6.x86_64.rpm                                                                   | 3.8 MB     00:09

(8/17): glibc-common-2.12-1.80.el6_3.6.x86_64.rpm                                                            |  14 MB     00:51

(9/17): glibc-devel-2.12-1.80.el6_3.6.x86_64.rpm                                                             | 970 kB     00:02

(10/17): glibc-headers-2.12-1.80.el6_3.6.x86_64.rpm                                                          | 600 kB     00:01

(11/17): kernel-uek-headers-2.6.32-300.39.1.el6uek.x86_64.rpm                                                | 716 kB     00:01

(12/17): ksh-20100621-16.el6.x86_64.rpm                                                                      | 684 kB     00:01

(13/17): libaio-devel-0.3.107-10.el6.x86_64.rpm                                                              |  13 kB     00:00

(14/17): libstdc++-devel-4.4.6-4.el6.x86_64.rpm                                                              | 1.5 MB     00:03

(15/17): mpfr-2.4.1-6.el6.x86_64.rpm                                                                         | 156 kB     00:00

(16/17): oracle-rdbms-server-11gR2-preinstall-1.0-6.el6.x86_64.rpm                                           |  15 kB     00:00

(17/17): ppl-0.10.2-11.el6.x86_64.rpm                                                                        | 1.3 MB     00:02

------------------------------------------------------------------------------------------------------------------------------------

Total                                                                                               320 kB/s |  43 MB     02:16

warning: rpmts_HdrFromFdno: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY

Retrieving key from http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6

Importing GPG key 0xEC551F03:

 Userid: "Oracle OSS group (Open Source Software group) "

 From  : http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

  Installing : libstdc++-devel-4.4.6-4.el6.x86_64                                                                              1/19

  Updating   : glibc-2.12-1.80.el6_3.6.x86_64                                                                                  2/19

  Updating   : glibc-common-2.12-1.80.el6_3.6.x86_64                                                                           3/19

  Installing : mpfr-2.4.1-6.el6.x86_64                                                                                         4/19

  Installing : cpp-4.4.6-4.el6.x86_64                                                                                          5/19

  Installing : compat-libcap1-1.10-1.x86_64                                                                                    6/19

  Installing : ksh-20100621-16.el6.x86_64                                                                                      7/19

  Installing : compat-libstdc++-33-3.2.3-69.el6.x86_64                                                                         8/19

  Installing : ppl-0.10.2-11.el6.x86_64                                                                                        9/19

  Installing : cloog-ppl-0.15.7-1.2.el6.x86_64                                                                                10/19

  Installing : kernel-uek-headers-2.6.32-300.39.1.el6uek.x86_64                                                               11/19

  Installing : glibc-headers-2.12-1.80.el6_3.6.x86_64                                                                         12/19

  Installing : glibc-devel-2.12-1.80.el6_3.6.x86_64                                                                           13/19

  Installing : gcc-4.4.6-4.el6.x86_64                                                                                         14/19

  Installing : gcc-c++-4.4.6-4.el6.x86_64                                                                                     15/19

  Installing : libaio-devel-0.3.107-10.el6.x86_64                                                                             16/19

  Installing : oracle-rdbms-server-11gR2-preinstall-1.0-6.el6.x86_64                                                          17/19

  Cleanup    : glibc-2.12-1.80.el6.x86_64                                                                                     18/19

  Cleanup    : glibc-common-2.12-1.80.el6.x86_64                                                                              19/19

  Verifying  : compat-libcap1-1.10-1.x86_64                                                                                    1/19

  Verifying  : ksh-20100621-16.el6.x86_64                                                                                      2/19

  Verifying  : glibc-common-2.12-1.80.el6_3.6.x86_64                                                                           3/19

  Verifying  : gcc-4.4.6-4.el6.x86_64                                                                                          4/19

  Verifying  : libaio-devel-0.3.107-10.el6.x86_64                                                                              5/19

  Verifying  : oracle-rdbms-server-11gR2-preinstall-1.0-6.el6.x86_64                                                           6/19

  Verifying  : gcc-c++-4.4.6-4.el6.x86_64                                                                                      7/19

  Verifying  : glibc-headers-2.12-1.80.el6_3.6.x86_64                                                                          8/19

  Verifying  : libstdc++-devel-4.4.6-4.el6.x86_64                                                                              9/19

  Verifying  : compat-libstdc++-33-3.2.3-69.el6.x86_64                                                                        10/19

  Verifying  : glibc-2.12-1.80.el6_3.6.x86_64                                                                                 11/19

  Verifying  : mpfr-2.4.1-6.el6.x86_64                                                                                        12/19

  Verifying  : kernel-uek-headers-2.6.32-300.39.1.el6uek.x86_64                                                               13/19

  Verifying  : cpp-4.4.6-4.el6.x86_64                                                                                         14/19

  Verifying  : glibc-devel-2.12-1.80.el6_3.6.x86_64                                                                           15/19

  Verifying  : ppl-0.10.2-11.el6.x86_64                                                                                       16/19

  Verifying  : cloog-ppl-0.15.7-1.2.el6.x86_64                                                                                17/19

  Verifying  : glibc-2.12-1.80.el6.x86_64                                                                                     18/19

  Verifying  : glibc-common-2.12-1.80.el6.x86_64                                                                              19/19

Installed:

  oracle-rdbms-server-11gR2-preinstall.x86_64 0:1.0-6.el6

Dependency Installed:

  cloog-ppl.x86_64 0:0.15.7-1.2.el6                                     compat-libcap1.x86_64 0:1.10-1

  compat-libstdc++-33.x86_64 0:3.2.3-69.el6                             cpp.x86_64 0:4.4.6-4.el6

  gcc.x86_64 0:4.4.6-4.el6                                              gcc-c++.x86_64 0:4.4.6-4.el6

  glibc-devel.x86_64 0:2.12-1.80.el6_3.6                                glibc-headers.x86_64 0:2.12-1.80.el6_3.6

  kernel-uek-headers.x86_64 0:2.6.32-300.39.1.el6uek                    ksh.x86_64 0:20100621-16.el6

  libaio-devel.x86_64 0:0.3.107-10.el6                                  libstdc++-devel.x86_64 0:4.4.6-4.el6

  mpfr.x86_64 0:2.4.1-6.el6                                             ppl.x86_64 0:0.10.2-11.el6

Dependency Updated:

  glibc.x86_64 0:2.12-1.80.el6_3.6                              glibc-common.x86_64 0:2.12-1.80.el6_3.6

As we can see it has installed all the prerequisites and dependencies needed to install Oracle Database on Enterprise Linux 6. 

But it has done other stuff too ... let's look at that ! 

It logs all its work in 

/var/log/oracle-rdbms-server-11gR2-preinstall

-bash: l: command not found

[root@oel6cleanzero oracle-rdbms-server-11gR2-preinstall]# ls -lrt

total 8

drwxr-xr-x. 3 root root 4096 Dec  5 11:11 backup

drwx------. 2 root root 4096 Dec  5 11:11 results

[root@oel6cleanzero oracle-rdbms-server-11gR2-preinstall]#

Every file it amends is backed up in the backup directory and the what it does is logged in the results directory. 

Lets look in the results directory and we see one file orakernel.log 

Adding group oinstall with gid 54321

Adding group dba

Adding user oracle with user id 54321, initial login group oinstall, supplementary group dba and  home directory /home/oracle

Changing ownership of /home/oracle to oracle:oinstall

Please set password for oracle user

uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba)

Creating oracle user passed

Verifying  kernel parameters as per Oracle recommendations...

fs.file-max             6815744

kernel.sem              250 32000 100 128

kernel.shmmni           4096

kernel.shmall           1073741824

kernel.shmmax           4398046511104

net.core.rmem_default           262144

net.core.rmem_max               4194304

net.core.wmem_default           262144

net.core.wmem_max               1048576

fs.aio-max-nr           1048576

net.ipv4.ip_local_port_range            9000 65500

Setting kernel parameters as per oracle recommendations...

Altered file /etc/sysctl.conf

Original file backed up at /etc/sysctl.conf.orabackup

Verifying & setting of kernel parameters passed

Verifying oracle user OS limits as per Oracle recommendations...

oracle  soft    nofile          1024

oracle  hard    nofile          65536

oracle  soft    nproc           2047

oracle  hard    nproc           16384

oracle  soft    stack           10240

oracle  hard    stack           32768

Setting oracle user OS limits as per Oracle recommendations...

Altered file /etc/security/limits.conf

Original file backed up at /etc/security/limits.conf.orabackup

Verifying & setting of user limits passed

Verifying kernel boot parameters as per Oracle recommendations...

old boot params: kernel /vmlinuz-2.6.39-200.24.1.el6uek.x86_64 ro root=/dev/mapper/vg_oel6cleanzero-lv_root rd_NO_LUKS  KEYBOARDTYPE=pc KEYTABLE=uk LANG=en_US.UTF-8 rd_LVM_LV=vg_oel6cleanzero/lv_root rd_NO_MD rd_LVM_LV=vg_oel6cleanzero/lv_swap SYSFONT=latarcyrheb-sun16  rd_NO_DM rhgb quiet, new boot params: kernel /vmlinuz-2.6.39-200.24.1.el6uek.x86_64 ro root=/dev/mapper/vg_oel6cleanzero-lv_root rd_NO_LUKS  KEYBOARDTYPE=pc KEYTABLE=uk LANG=en_US.UTF-8 rd_LVM_LV=vg_oel6cleanzero/lv_root rd_NO_MD rd_LVM_LV=vg_oel6cleanzero/lv_swap SYSFONT=latarcyrheb-sun16  rd_NO_DM rhgb quiet numa=off

old boot params: kernel /vmlinuz-2.6.32-279.el6.x86_64 ro root=/dev/mapper/vg_oel6cleanzero-lv_root rd_NO_LUKS  KEYBOARDTYPE=pc KEYTABLE=uk LANG=en_US.UTF-8 rd_LVM_LV=vg_oel6cleanzero/lv_root rd_NO_MD rd_LVM_LV=vg_oel6cleanzero/lv_swap SYSFONT=latarcyrheb-sun16 crashkernel=auto rd_NO_DM rhgb quiet, new boot params: kernel /vmlinuz-2.6.32-279.el6.x86_64 ro root=/dev/mapper/vg_oel6cleanzero-lv_root rd_NO_LUKS  KEYBOARDTYPE=pc KEYTABLE=uk LANG=en_US.UTF-8 rd_LVM_LV=vg_oel6cleanzero/lv_root rd_NO_MD rd_LVM_LV=vg_oel6cleanzero/lv_swap SYSFONT=latarcyrheb-sun16 crashkernel=auto rd_NO_DM rhgb quiet numa=off

Setting kernel boot parameters as per Oracle recommendations...

Boot parameters will be effected on next reboot

Altered file /boot/grub/grub.conf

Original file backed up at /boot/grub/grub.conf.orabackup

Verifying & setting of boot parameters passed

Taking a backup of old config files under /var/log/oracle-rdbms-server-11gR2-preinstall/backup/Dec-05-2012-11-11-05

So what has it done : 

1. Added required prerequisite Oracle RPMs to allow Oracle database install OK 
2. Added Oracle users and groups ( dba,oinstall etc )  . there is no password set on the Oracle user so no password login can happen 
3. Sets up kernel sysctl and limits.conf ( always fiddly ) 
4. Changes the kernel to boot numa off 
5. also modprobes the kernel for flow control if needed. 

So when we are done we are more or less good to install oracle straight away ! Massive timesaver 

Note this doesn't apply to RAC but you will be doing stringent hardware checks anyway 

Also I don't think this works for RHEL but it's worth a try to mess about with

Hope this helps ( did a little unconference about it at UKOUG 2012 ! )