Monthly Archives: January 2015

How to resolve the text behind v$views?

5 Replies

This is a common problem I have and I never write it down (except now). For example, today I wanted to know what the valid parameters for _serial_direct_read were:

SQL> select * from v$parameter_valid_values where name ='_serial_direct_read';

no rows selected

OK so if Oracle doesn’t tell me then maybe I can work it out? Getting the view_text has worked in the past:

SQL> select view_name, text_vc from dba_views where view_name = '%PARAMETER_VALID_VALUES'

VIEW_NAME                          TEXT_VC
---------------------------------- ----------------------------------------------------------------------------------------------------
V_$PARAMETER_VALID_VALUES          select "NUM","NAME","ORDINAL","VALUE","ISDEFAULT","CON_ID"
                                   from v$parameter_valid_values
GV_$PARAMETER_VALID_VALUES         select "INST_ID","NUM","NAME","ORDINAL","VALUE","ISDEFAULT","CON_ID"
                                   from gv$parameter_valid_values

I’m sure I did the step wrong, but I couldn’t find what the lower case thingie was.

SQL> desc "v$parameter_valid_values"
ERROR:
ORA-04043: object "v$parameter_valid_values" does not exist

SQL> sho user
USER is "SYS"
SQL> desc "gv$parameter_valid_values"
ERROR:
ORA-04043: object "gv$parameter_valid_values" does not exist

SQL> select * from dba_views where view_name = '"gv$parameter_valid_values"';

no rows selected

SQL> select * from dba_objects where object_name = '"gv$parameter_valid_values"';

no rows selected

Yes, I’m pretty sure I got something wrong along the way.

Solutions

One possibility is to use dbms_xplan.display_cursor() – easy!

SQL> select * from v$parameter_valid_values where name = '_serial_direct_read';

no rows selected

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID  9hkygnf02nd8y, child number 0
-------------------------------------
select * from v$parameter_valid_values where name =
'_serial_direct_read'

Plan hash value: 1012408093

-------------------------------------------------------------------------
| Id  | Operation        | Name            | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                 |       |       |     1 (100)|
|*  1 |  FIXED TABLE FULL| X$KSPVLD_VALUES |     1 |    49 |     0   (0)|
-------------------------------------------------------------------------

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

   1 - filter(("NAME_KSPVLD_VALUES"='_serial_direct_read' AND
              TRANSLATE("NAME_KSPVLD_VALUES",'_','#') NOT LIKE '#%' AND
              "INST_ID"=USERENV('INSTANCE')))

Now if I translate this I can write a query that shows me what I need. It also demonstrates that – just like v$parameter – underscore parameters aren’t shown in this view.

SQL> @desc X$KSPVLD_VALUES
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      ADDR                                     RAW(8)
    2      INDX                                     NUMBER
    3      INST_ID                                  NUMBER
    4      CON_ID                                   NUMBER
    5      PARNO_KSPVLD_VALUES                      NUMBER
    6      NAME_KSPVLD_VALUES                       VARCHAR2(64)
    7      ORDINAL_KSPVLD_VALUES                    NUMBER
    8      VALUE_KSPVLD_VALUES                      VARCHAR2(255)
    9      ISDEFAULT_KSPVLD_VALUES                  VARCHAR2(64)

SQL> select PARNO_KSPVLD_VALUES, NAME_KSPVLD_VALUES, ORDINAL_KSPVLD_VALUES, VALUE_KSPVLD_VALUES
  2  from X$KSPVLD_VALUES where NAME_KSPVLD_VALUES ='_serial_direct_read';

PARNO_KSPVLD_VALUES NAME_KSPVLD_VALUES             ORDINAL_KSPVLD_VALUES VALUE_KSPVLD_VALUES
------------------- ------------------------------ --------------------- ------------------------------
               2873 _serial_direct_read                                1 ALWAYS
               2873 _serial_direct_read                                2 AUTO
               2873 _serial_direct_read                                3 NEVER
               2873 _serial_direct_read                                4 TRUE
               2873 _serial_direct_read                                5 FALSE

There you go!

Another way is to use the 12c functionality in DBMS_UTILITY.EXPAND_SQL_TEXT. Reusing the example by Tom Kyte:

SQL> var x clob.

SQL> exec dbms_utility.expand_sql_text( -
  2   input_sql_text => 'select * from V$PARAMETER_VALID_VALUES', -
  3   output_sql_text => :x)

print :x

X
--------------------------------------------------------------------------------------------------------
SELECT "A1"."NUM" "NUM","A1"."NAME" "NAME","A1"."ORDINAL" "ORDINAL","A1"."VALUE" "VALUE",
"A1"."ISDEFAULT" "ISDEFAULT","A1"."CON_ID" "CON_ID" FROM  (SELECT "A2"."NUM" "NUM","A2"."NAME"
"NAME","A2"."ORDINAL" "ORDINAL","A2"."VALUE" "VALUE","A2"."ISDEFAULT" "ISDEFAULT","A2"."CON_ID" "CON_ID"
FROM  (SELECT "A3"."INST_ID" "INST_ID","A3"."PARNO_KSPVLD_VALUES" "NUM","A3"."NAME_KSPVLD_VALUES"
"NAME","A3"."ORDINAL_KSPVLD_VALUES" "ORDINAL","A3"."VALUE_KSPVLD_VALUES"
"VALUE","A3"."ISDEFAULT_KSPVLD_VALUES" "ISDEFAULT","A3"."CON_ID" "CON_ID" FROM SYS."X$KSPVLD_VALUES" "A3"
WHERE TRANSLATE("A3"."NAME_KSPVLD_VALUES",'_','#') NOT LIKE '#%') "A2" WHERE
"A2"."INST_ID"=USERENV('INSTANCE')) "A1"

This seems to have worked in earlier versions too, one example is on Jonathan Lewis’ blog.

Update: the most obvious solution to this was to use v$fixed_view_definition! The view must have dropped at the cold end of my brain’s LRU list. As others have pointed out (thanks everyone for your comments!), this would be the way to query the object:

SQL> select VIEW_DEFINITION from V$FIXED_VIEW_DEFINITION where view_name like 'GV$PARAMETER_VALID_VALUES';

VIEW_DEFINITION
-------------------------------------------------------------------------------------------------------
SELECT INST_ID, PARNO_KSPVLD_VALUES, NAME_KSPVLD_VALUES, ORDINAL_KSPVLD_VALUES, VALUE_KSPVLD_VALUES,
 ISDEFAULT_KSPVLD_VALUES, CON_ID
 FROM X$KSPVLD_VALUES WHERE TRANSLATE(NAME_KSPVLD_VALUES,'_','#') NOT LIKE '#%'

Summary

It’s probably not what Oracle intended but DBMS_UTILITY.EXPAND_SQL_TEXT() worked really well. I came across the DBMS_XPLAN.DISPLAY_CURSOR() output by chance when I ran my diagnostic script at the wrong time but it, too, does the job.

Or, I could have used Tanel Poder’s script I didn’t know about until now:


SQL> @pvalid _serial_direct_read
Display valid values for multioption parameters matching "_serial_direct_read"...

  PAR# PARAMETER                                                 ORD VALUE                          DEFAULT
------ -------------------------------------------------- ---------- ------------------------------ -------
  2873 _serial_direct_read                                         1 ALWAYS
       _serial_direct_read                                         2 AUTO
       _serial_direct_read                                         3 NEVER
       _serial_direct_read                                         4 TRUE
       _serial_direct_read                                         5 FALSE
Advertisements

Adaptive plans and v$sql_plan and related views

Leave a reply

Adaptive plans are one of the coolest new optimiser features in Oracle 12c. If you haven’t seen or heard about them in detail I recommend the following resources:

There is a caveat with this though: if your tuning script relies on pulling information from v$sql_plan and related views, you get more information than you might want. I found out about this while working on our 12c New Features training class. This, and a lot more, will be part of it. Stay tuned :)

Consider the following example. I will use the following query in this article:

SELECT
  /* statement002 */
  /*+ gather_plan_statistics monitor */
  TRUNC(order_date, 'mm'),
  COUNT(TRUNC(order_date, 'mm'))
FROM orders o,
  order_items oi
WHERE oi.ORDER_ID    = o.order_id
AND o.CUSTOMER_CLASS = 'Prime'
AND o.WAREHOUSE_ID   = 501
AND o.ORDER_DATE BETWEEN DATE '2012-01-01' AND DATE '2012-07-01'
GROUP BY TRUNC(order_date, 'mm')
ORDER BY 1;

These tables are part of the Swingbench SOE (order entry) schema. I have inflated the order_items table to twice its size for a total of 171,579,632 rows.

When executing this query on the x4-2 half rack in the lab I get this (sorry for the wide output!):

TRUNC(ORDER_DATE, COUNT(TRUNC(ORDER_DATE,'MM'))
----------------- -----------------------------
20120101 00:00:00                           472
20120201 00:00:00                           580
20120301 00:00:00                           614
20120401 00:00:00                           578

SQL> @x
Display execution plan for last statement for this session from library cache...

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3wb68w2kj6gdd, child number 0
-------------------------------------
SELECT   /* statement002 */   /*+ gather_plan_statistics monitor */
TRUNC(order_date, 'mm'),   COUNT(TRUNC(order_date, 'mm')) FROM orders
o,   order_items oi WHERE oi.ORDER_ID    = o.order_id AND
o.CUSTOMER_CLASS = 'Prime' AND o.WAREHOUSE_ID   = 501 AND o.ORDER_DATE
BETWEEN DATE '2012-01-01' AND DATE '2012-07-01' GROUP BY
TRUNC(order_date, 'mm') ORDER BY 1

Plan hash value: 812470616

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                  |      1 |        |       | 29766 (100)|       |       |      4 |00:00:00.06 |   29354 |     17 |       |       |          |
|   1 |  SORT ORDER BY                                 |                  |      1 |    404 | 14948 | 29766   (1)|       |       |      4 |00:00:00.06 |   29354 |     17 |  2048 |  2048 | 2048  (0)|
|   2 |   HASH GROUP BY                                |                  |      1 |    404 | 14948 | 29766   (1)|       |       |      4 |00:00:00.06 |   29354 |     17 |  1394K|  1394K|  634K (0)|
|*  3 |    FILTER                                      |                  |      1 |        |       |            |       |       |   2244 |00:00:00.05 |   29354 |     17 |       |       |          |
|   4 |     NESTED LOOPS                               |                  |      1 |   2996 |   108K| 29764   (1)|       |       |   2244 |00:00:00.05 |   29354 |     17 |       |       |          |
|*  5 |      TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| ORDERS           |      1 |    434 | 13454 | 28462   (1)| ROWID | ROWID |    362 |00:00:00.03 |   28261 |     17 |       |       |          |
|*  6 |       INDEX RANGE SCAN                         | ORD_WAREHOUSE_IX |      1 |  28624 |       |    90   (0)|       |       |  28441 |00:00:00.02 |      90 |      0 |  1025K|  1025K|          |
|*  7 |      INDEX RANGE SCAN                          | ITEM_ORDER_IX    |    362 |      7 |    42 |     3   (0)|       |       |   2244 |00:00:00.01 |    1093 |      0 |  1025K|  1025K|          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   3 - filter(TIMESTAMP' 2012-07-01 00:00:00'>=TIMESTAMP' 2012-01-01 00:00:00')
   5 - filter(("O"."CUSTOMER_CLASS"='Prime' AND "O"."ORDER_DATE">=TIMESTAMP' 2012-01-01 00:00:00' AND "O"."ORDER_DATE"<=TIMESTAMP' 2012-07-01 00:00:00'))
   6 - access("O"."WAREHOUSE_ID"=501)
   7 - access("OI"."ORDER_ID"="O"."ORDER_ID")

   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan
   - 2 Sql Plan Directives used for this statement

@x is one of the many useful scripts written by Tanel Poder. I recommend you download the tpt_public.zip and get familiar with what they do-they are just great.

The Execution Plan

Please take a note at the output of the execution plan. The query is an adaptive plan (see notes). The join between orders and order_items is performed using a nested loop (step 4 and the following ones). You can also use v$sql to show that this plan is an adaptive plan:

SQL> select sql_id, child_number, executions, is_reoptimizable, is_resolved_adaptive_plan
  2  from v$sql where sql_id = '3wb68w2kj6gdd';

SQL_ID        CHILD_NUMBER EXECUTIONS I I
------------- ------------ ---------- - -
3wb68w2kj6gdd            0          1 Y Y

It is indeed already resolved. Using Jonathan Lewis’s recent notes on reoptimisation I checked what the optimiser worked out:

SQL> select sql_id, child_number, hint_id, hint_text, reparse
  2  from v$sql_reoptimization_hints where sql_id = '3wb68w2kj6gdd' and child_number = 0;

SQL_ID        CHILD_NUMBER    HINT_ID HINT_TEXT                                             REPARSE
------------- ------------ ---------- -------------------------------------------------- ----------
3wb68w2kj6gdd            0          1 OPT_ESTIMATE (@"SEL$1" GROUP_BY ROWS=4.000000 )             1
3wb68w2kj6gdd            0          2 OPT_ESTIMATE (@"SEL$1" JOIN ("OI"@"SEL$1" "O"@"SEL          1
                                      $1") ROWS=1.000000 )

Nice! That also provides some insights that could be useful. You can map the hint_text to v$sql_plan.object_alias, it is the query block name. I have actually executed slight variations of the query in preparation …

Back to my problem

In the past I have used v$sql_plan (indirectly, via scripts written by far more clever people than me) to work each step in the plan. v$sql_plan has an ID, PARENT_ID and DEPTH that make it easier to work out where in the plan you are. Using my old approach I got stuck, consider this:

SQL> r
  1  select
  2    lpad(' ',sp.depth*1,' ')
  3    || sp.operation AS operation,
  4    sp.OPTIONS,
  5    sp.object#,
  6    sp.object_name,
  7    sp.object_alias,
  8    sp.object_type
  9  FROM v$sql_plan sp
 10* where sql_id = '3wb68w2kj6gdd' and child_number = 0

OPERATION                                OPTIONS                                OBJECT# OBJECT_NAME               OBJECT_ALI OBJECT_TYP
---------------------------------------- ----------------------------------- ---------- ------------------------- ---------- ----------
SELECT STATEMENT
 SORT                                    ORDER BY
  HASH                                   GROUP BY
   FILTER
    HASH JOIN
     NESTED LOOPS
      STATISTICS COLLECTOR
       TABLE ACCESS                      BY GLOBAL INDEX ROWID BATCHED            28865 ORDERS                    O@SEL$1    TABLE
        INDEX                            RANGE SCAN                               29329 ORD_WAREHOUSE_IX          O@SEL$1    INDEX
      INDEX                              RANGE SCAN                               29302 ITEM_ORDER_IX             OI@SEL$1   INDEX
     INDEX                               FAST FULL SCAN                           29302 ITEM_ORDER_IX             OI@SEL$1   INDEX

11 rows selected.

If you compare this with the DBMS_XPLAN-output from above then you notice there is a lot more information in the query against v$sql_plan …In fact, that’s the same output as what you get when calling DBMS_XPLAN.DISPLAY_CURSOR(… format => ‘ADAPTIVE’):

SQL> select * from table(dbms_xplan.display_cursor('3wb68w2kj6gdd',0,'+ADAPTIVE'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3wb68w2kj6gdd, child number 0
-------------------------------------
SELECT   /* statement002 */   /*+ gather_plan_statistics monitor */
TRUNC(order_date, 'mm'),   COUNT(TRUNC(order_date, 'mm')) FROM orders
o,   order_items oi WHERE oi.ORDER_ID    = o.order_id AND
o.CUSTOMER_CLASS = 'Prime' AND o.WAREHOUSE_ID   = 501 AND o.ORDER_DATE
BETWEEN DATE '2012-01-01' AND DATE '2012-07-01' GROUP BY
TRUNC(order_date, 'mm') ORDER BY 1

Plan hash value: 812470616

---------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                                        | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                                 |                  |       |       | 29766 (100)|          |       |       |
|     1 |  SORT ORDER BY                                   |                  |   404 | 14948 | 29766   (1)| 00:00:02 |       |       |
|     2 |   HASH GROUP BY                                  |                  |   404 | 14948 | 29766   (1)| 00:00:02 |       |       |
|  *  3 |    FILTER                                        |                  |       |       |            |          |       |       |
|- *  4 |     HASH JOIN                                    |                  |  2996 |   108K| 29764   (1)| 00:00:02 |       |       |
|     5 |      NESTED LOOPS                                |                  |  2996 |   108K| 29764   (1)| 00:00:02 |       |       |
|-    6 |       STATISTICS COLLECTOR                       |                  |       |       |            |          |       |       |
|  *  7 |        TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| ORDERS           |   434 | 13454 | 28462   (1)| 00:00:02 | ROWID | ROWID |
|  *  8 |         INDEX RANGE SCAN                         | ORD_WAREHOUSE_IX | 28624 |       |    90   (0)| 00:00:01 |       |       |
|  *  9 |       INDEX RANGE SCAN                           | ITEM_ORDER_IX    |     7 |    42 |     3   (0)| 00:00:01 |       |       |
|-   10 |      INDEX FAST FULL SCAN                        | ITEM_ORDER_IX    |     7 |    42 |     3   (0)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------------------------------
...
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan (rows marked '-' are inactive)
   - 2 Sql Plan Directives used for this statement

Note the line: this is an adaptive plan (rows marked ‘-‘ are inactive). But how does DBMS_XPLAN know that these lines are hidden? There doesn’t seem to be a view v$sql_plan_hidden_lines. I tried a few things and eventually traced the call to DBMS_XPLAN.DISPLAY_CURSOR. In the trace I found the trick Oracle uses:

=====================
PARSING IN CURSOR #140127730386096 len=298 dep=2 uid=75 oct=3 lid=75 tim=767822822383 hv=580989905 ad='e9e6f7570' sqlid='fg4skgcja2cyj'
SELECT EXTRACTVALUE(VALUE(D), '/row/@op'), EXTRACTVALUE(VALUE(D), '/row/@dis'), EXTRACTVALUE(VALUE(D), '/row/@par'), EXTRACTVALUE(VALUE(D),
'/row/@prt'), EXTRACTVALUE(VALUE(D), '/row/@dep'), EXTRACTVALUE(VALUE(D), '/row/@skp') FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(:B1 ), 
'/*/display_map/row'))) D
END OF STMT
...
STAT #140127732564416 id=1 cnt=11 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=0 pr=0 pw=0 time=691 us cost=1 size=8935 card=1)'
STAT #140127732564416 id=2 cnt=11 pid=1 pos=1 obj=0 op='VIEW  (cr=0 pr=0 pw=0 time=1223 us cost=0 size=8935 card=1)'
STAT #140127732564416 id=3 cnt=11 pid=2 pos=1 obj=0 op='NESTED LOOPS  (cr=0 pr=0 pw=0 time=1116 us cost=0 size=957 card=1)'
STAT #140127732564416 id=4 cnt=11 pid=3 pos=1 obj=0 op='FIXED TABLE FIXED INDEX X$KQLFXPL (ind:4) (cr=0 pr=0 pw=0 time=902 us cost=0 size=853 card=1)'
STAT #140127732564416 id=5 cnt=11 pid=3 pos=2 obj=0 op='FIXED TABLE FIXED INDEX X$KGLCURSOR_CHILD (ind:2) (cr=0 pr=0 pw=0 time=134 us cost=0 size=104 card=1)'

So that’s it! The X$ views map to v$sql and v$sql_plan unless I am very mistaken. V$SQL_PLAN only has 1 column that contains XML-other_xml. Using this information I thought there has to be something in there … and indeed, there is:

SQL> select xmltype(other_xml)
  2  from v$sql_plan
  3  where sql_id = '3wb68w2kj6gdd' and child_number = 0
  4  and other_xml is not null;

XMLTYPE(OTHER_XML)
---------------------------------------------------------------------------------------------------------------------------
<other_xml>
  <info type="db_version">12.1.0.2</info>
  <info type="parse_schema"><![CDATA["SOE"]]></info>
  <info type="dynamic_sampling" note="y">2</info>
  <info type="plan_hash_full">166760258</info>
  <info type="plan_hash">812470616</info>
  <info type="plan_hash_2">3729130925</info>
  <info type="adaptive_plan" note="y">yes</info>
  <spd>
    <cv>0</cv>
    <cu>2</cu>
  </spd>
  <outline_data>
    <hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint>
    <hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('12.1.0.2')]]></hint>
    <hint><![CDATA[DB_VERSION('12.1.0.2')]]></hint>
    <hint><![CDATA[ALL_ROWS]]></hint>
    <hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint>
    <hint><![CDATA[INDEX_RS_ASC(@"SEL$1" "O"@"SEL$1" ("ORDERS"."WAREHOUSE_ID" "ORDERS"."ORDER_STATUS"))]]></hint>
    <hint><![CDATA[BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "O"@"SEL$1")]]></hint>
    <hint><![CDATA[INDEX(@"SEL$1" "OI"@"SEL$1" ("ORDER_ITEMS"."ORDER_ID"))]]></hint>
    <hint><![CDATA[LEADING(@"SEL$1" "O"@"SEL$1" "OI"@"SEL$1")]]></hint>
    <hint><![CDATA[USE_NL(@"SEL$1" "OI"@"SEL$1")]]></hint>
    <hint><![CDATA[USE_HASH_AGGREGATION(@"SEL$1")]]></hint>
  </outline_data>
  <display_map>
    <row op="1" dis="1" par="0" prt="0" dep="1" skp="0"/>
    <row op="2" dis="2" par="1" prt="0" dep="2" skp="0"/>
    <row op="3" dis="3" par="2" prt="0" dep="3" skp="0"/>
    <row op="4" dis="3" par="3" prt="0" dep="3" skp="1"/>
    <row op="5" dis="4" par="3" prt="0" dep="4" skp="0"/>
    <row op="6" dis="4" par="4" prt="0" dep="4" skp="1"/>
    <row op="7" dis="5" par="4" prt="5" dep="5" skp="0"/>
    <row op="8" dis="6" par="5" prt="0" dep="6" skp="0"/>
    <row op="9" dis="7" par="4" prt="0" dep="5" skp="0"/>
    <row op="10" dis="7" par="3" prt="0" dep="3" skp="1"/>
  </display_map>
</other_xml>

The SQL statement from the trace was not much use to me, the following seemed better suited to work out what was happening. I added what I think the abbreviations stand for:

WITH display_map AS
  (SELECT X.*
  FROM v$sql_plan,
    XMLTABLE ( '/other_xml/display_map/row' passing XMLTYPE(other_xml ) COLUMNS 
      op  NUMBER PATH '@op',    -- operation
      dis NUMBER PATH '@dis',   -- display
      par NUMBER PATH '@par',   -- parent
      prt NUMBER PATH '@prt',   -- ?
      dep NUMBER PATH '@dep',   -- depth
      skp NUMBER PATH '@skp' )  -- skip
  AS X
  WHERE sql_id     = '&sql_id'
  AND child_number = &sql_child
  AND other_xml   IS NOT NULL
  )
SELECT * from display_map;

Enter value for sql_id: 3wb68w2kj6gdd
Enter value for sql_child: 0

        OP        DIS        PAR        PRT        DEP        SKP
---------- ---------- ---------- ---------- ---------- ----------
         1          1          0          0          1          0
         2          2          1          0          2          0
         3          3          2          0          3          0
         4          3          3          0          3          1
         5          4          3          0          4          0
         6          4          4          0          4          1
         7          5          4          5          5          0
         8          6          5          0          6          0
         9          7          4          0          5          0
        10          7          3          0          3          1

10 rows selected.

Well-that’s a starting point. Now all I have to do is join the display map to v$sql_plan cleverly. After a little bit of fiddling with the query this seems to work:

WITH display_map AS
  (SELECT X.*
  FROM v$sql_plan,
    XMLTABLE ( '/other_xml/display_map/row' passing XMLTYPE(other_xml ) COLUMNS 
      op  NUMBER PATH '@op',    -- operation
      dis NUMBER PATH '@dis',   -- display
      par NUMBER PATH '@par',   -- parent
      prt NUMBER PATH '@prt',   -- ?
      dep NUMBER PATH '@dep',   -- depth
      skp NUMBER PATH '@skp' )  -- skip
  AS X
  WHERE sql_id     = '&sql_id'
  AND child_number = &sql_child
  AND other_xml   IS NOT NULL
  )
SELECT 
  -- new ID, depth, parent etc from display_map
  NVL(m.dis, 0) AS new_id,
  m.par         AS new_parent,
  m.dep         AS new_depth,
  -- plan formatting, as usual
  lpad(' ',m.dep*1,' ')
  || sp.operation AS operation,
  sp.OPTIONS,
  sp.object#,
  sp.object_name,
  sp.object_alias,
  sp.object_type
FROM v$sql_plan sp
LEFT OUTER JOIN display_map m
ON (sp.id = m.op)
WHERE sp.sql_Id        = '&sql_id'
AND sp.child_number    = &sql_child
AND NVL(m.skp,0)      <> 1
ORDER BY NVL(dis,0);

    NEW_ID NEW_PARENT  NEW_DEPTH OPERATION                      OPTIONS                                OBJECT# OBJECT_NAME               OBJECT_ALI OBJECT_TYP
---------- ---------- ---------- ------------------------------ ----------------------------------- ---------- ------------------------- ---------- ----------
         0                       SELECT STATEMENT
         1          0          1  SORT                          ORDER BY
         2          1          2   HASH                         GROUP BY
         3          2          3    FILTER
         4          3          4     NESTED LOOPS
         5          4          5      TABLE ACCESS              BY GLOBAL INDEX ROWID BATCHED            28865 ORDERS                    O@SEL$1    TABLE
         6          5          6       INDEX                    RANGE SCAN                               29329 ORD_WAREHOUSE_IX          O@SEL$1    INDEX
         7          4          5      INDEX                     RANGE SCAN                               29302 ITEM_ORDER_IX             OI@SEL$1   INDEX

8 rows selected.

This seems to match what Oracle produces:

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3wb68w2kj6gdd, child number 0
-------------------------------------
SELECT   /* statement002 */   /*+ gather_plan_statistics monitor */
TRUNC(order_date, 'mm'),   COUNT(TRUNC(order_date, 'mm')) FROM orders
o,   order_items oi WHERE oi.ORDER_ID    = o.order_id AND
o.CUSTOMER_CLASS = 'Prime' AND o.WAREHOUSE_ID   = 501 AND o.ORDER_DATE
BETWEEN DATE '2012-01-01' AND DATE '2012-07-01' GROUP BY
TRUNC(order_date, 'mm') ORDER BY 1

Plan hash value: 812470616

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                  |       |       | 29766 (100)|          |       |       |
|   1 |  SORT ORDER BY                                 |                  |   404 | 14948 | 29766   (1)| 00:00:02 |       |       |
|   2 |   HASH GROUP BY                                |                  |   404 | 14948 | 29766   (1)| 00:00:02 |       |       |
|*  3 |    FILTER                                      |                  |       |       |            |          |       |       |
|   4 |     NESTED LOOPS                               |                  |  2996 |   108K| 29764   (1)| 00:00:02 |       |       |
|*  5 |      TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| ORDERS           |   434 | 13454 | 28462   (1)| 00:00:02 | ROWID | ROWID |
|*  6 |       INDEX RANGE SCAN                         | ORD_WAREHOUSE_IX | 28624 |       |    90   (0)| 00:00:01 |       |       |
|*  7 |      INDEX RANGE SCAN                          | ITEM_ORDER_IX    |     7 |    42 |     3   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------------------------

With this done it should be possible to add other diagnostic information too, just join the additional views and add the relevant columns. Hope this helps! I haven’t performed extensive testing on the approach but wanted to put it out here for the more clever people to tell me where I’m wrong.

Installing Oracle 12.1.0.2 RAC on Oracle Linux 7-part 2

3 Replies

In the first part of the article series you could read how a kickstart file made the installation of Oracle Linux 7 a lot more bearable. In this part of the series it’s all about configuring the operating system. The installation of Grid Infrastructure and the Oracle database is for another set of posts.

There are quite some differences between Oracle Linux 6 and 7

To me the transition from Oracle Linux 6 to 7 feels like the step from Solaris 9 to 10 at the time. Personally I think that a lot has changed. Although, it’s fair to say that it has been quite some time it has been announced that the network stack commands we know and love are deprecated and might go… Even with Oracle Linux 6 there was a threat that network manager would now be the only tool to modify your network settings (which thankfully was not the case). A lot of efforts of the Linux community have now come to fruition, and it’s time to adjust to the future. Even when it’s painful (and it is, at least a bit).

Configuring the network

The warning has been out there quite a while but now it seems to be true-no more system-config-network-tui to configure the network! No more ifconfig! Oh dear-quite a bit of learning to be done. Luckily someone else has done all the legwork and documented the changes. A good example is this one:

https://dougvitale.wordpress.com/2011/12/21/deprecated-linux-networking-commands-and-their-replacements/

So first of all-don’t fear: although all network interfaces are configured using network manager now, you can still use a command line tool: nmtui. After trying it out I have to say I’m not really convinced about its usability. What appears better is the use of the nmcli, network manager command line tool. It’s use is quite confusing, and it appears to me as if the whole network manager toolset was developed for laptop users, not servers. But I digress. I have a few interfaces in my RAC VM, the first was configured during the installation, eth[1-3] aren’t configured yet.

[root@localhost ~]# nmcli connection show
NAME         UUID                                  TYPE            DEVICE
System eth0  77e3f8a9-76d0-4051-a8f2-cbbe39dab089  802-3-ethernet  eth0
[root@localhost ~]# nmcli device status
DEVICE  TYPE      STATE         CONNECTION
eth0    ethernet  connected     System eth0
eth1    ethernet  disconnected  --
eth2    ethernet  disconnected  --
eth3    ethernet  disconnected  --
lo      loopback  unmanaged     --
[root@localhost ~]#

At this point I have used eth0 as the management network (similar to the way Exadata does) and will use the other networks for the database. eth1 will act as the public network, eth2 and eth3 will be private.

Although the network interfaces can be named differently for device name persistence I stick with the old naming for now. I don’t want to run into trouble with the installer just yet. On physical hardware you are very likely to see very different network interface names, the kernel uses a naming scheme identifying where the cards are (on the main board, or in extension cards for example). I’ll write another post about that soon.

Using dnsmasq (on the host) I configure my hosts for these addresses:

[root@ol62 ~]# grep rac12pri /etc/hosts
192.168.100.107	rac12pri1.example.com		rac12pri1
192.168.100.108	rac12pri1-vip.example.com	rac12pri1-vip
192.168.100.109	rac12pri2.example.com		rac12pri2
192.168.100.110	rac12pri2-vip.example.com	rac12pri2-vip
192.168.100.111	rac12pri-scan.example.com	rac12pri-scan
192.168.100.112	rac12pri-scan.example.com	rac12pri-scan
192.168.100.113	rac12pri-scan.example.com	rac12pri-scan

Configuring the interface is actually not too hard once you got the hang of it. It took me a little while to get it though… It almost appears as if something that was simple and easy to use was made difficult to use.

[root@localhost ~]# nmcli con add con-name eth1 ifname eth1 type ethernet ip4 192.168.100.107/24 gw4 192.168.100.1
[root@localhost ~]# nmcli con add con-name eth2 ifname eth2 type ethernet ip4 192.168.101.107/24
[root@localhost ~]# nmcli con add con-name eth3 ifname eth3 type ethernet ip4 192.168.102.107/24 

[root@localhost ~]# nmcli con show
NAME         UUID                                  TYPE            DEVICE
eth2         ccc7f592-b563-4b9d-a36b-2b45809e4643  802-3-ethernet  eth2
eth1         ae897dee-42ff-4ccd-843b-7c97ba0d5315  802-3-ethernet  eth1
System eth0  77e3f8a9-76d0-4051-a8f2-cbbe39dab089  802-3-ethernet  eth0
eth3         b6074c9a-dcc4-4487-9a8a-052e4c60bbca  802-3-ethernet  eth3

I can now verify the IP addresses using the “ip” tool (ifconfig was not installed, I haven’t yet checked if there was a compatibility package though)

[root@localhost ~]# ip addr show
1: lo: &lt;LOOPBACK,UP,LOWER_UP&gt; mtu 65536 qdisc noqueue state UNKNOWN
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth0: &lt;BROADCAST,MULTICAST,UP,LOWER_UP&gt; mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 52:54:00:6e:6f:67 brd ff:ff:ff:ff:ff:ff
    inet 192.168.150.111/24 brd 192.168.150.255 scope global eth0
    inet6 fe80::5054:ff:fe6e:6f67/64 scope link
       valid_lft forever preferred_lft forever
3: eth1: &lt;BROADCAST,MULTICAST,UP,LOWER_UP&gt; mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 52:54:00:96:ad:88 brd ff:ff:ff:ff:ff:ff
    inet 192.168.100.107/24 brd 192.168.100.255 scope global eth1
    inet6 fe80::5054:ff:fe96:ad88/64 scope link
       valid_lft forever preferred_lft forever
4: eth2: &lt;BROADCAST,MULTICAST,UP,LOWER_UP&gt; mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 52:54:00:c1:cc:8e brd ff:ff:ff:ff:ff:ff
    inet 192.168.101.107/24 brd 192.168.101.255 scope global eth2
    inet6 fe80::5054:ff:fec1:cc8e/64 scope link
       valid_lft forever preferred_lft forever
5: eth3: &lt;BROADCAST,MULTICAST,UP,LOWER_UP&gt; mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 52:54:00:7e:59:45 brd ff:ff:ff:ff:ff:ff
    inet 192.168.102.107/24 brd 192.168.102.255 scope global eth3
    inet6 fe80::5054:ff:fe7e:5945/64 scope link
       valid_lft forever preferred_lft forever

Now what’s left is setting the hostname-which is a simple call to hostnamectl –static set-hostname rac12pri1. nmcli gives you an interface to changing the hostname as well. I repeated the steps for node 2, they are identical except for the network IP addresses of course.

So that concludes the network setup.

Managing linux daemons

If you are curious about setting services at runlevel, then there’ll be another surprise:

[root@rac12pri2 ~]# chkconfig --list

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

iprdump        	0:off	1:off	2:on	3:on	4:on	5:on	6:off
iprinit        	0:off	1:off	2:on	3:on	4:on	5:on	6:off
iprupdate      	0:off	1:off	2:on	3:on	4:on	5:on	6:off
netconsole     	0:off	1:off	2:off	3:off	4:off	5:off	6:off
network        	0:off	1:off	2:on	3:on	4:on	5:on	6:off
pmcd           	0:off	1:off	2:off	3:off	4:off	5:off	6:off
pmie           	0:off	1:off	2:off	3:off	4:off	5:off	6:off
pmlogger       	0:off	1:off	2:off	3:off	4:off	5:off	6:off
pmmgr          	0:off	1:off	2:off	3:off	4:off	5:off	6:off
pmproxy        	0:off	1:off	2:off	3:off	4:off	5:off	6:off
pmwebd         	0:off	1:off	2:off	3:off	4:off	5:off	6:off
rhnsd          	0:off	1:off	2:on	3:on	4:on	5:on	6:off
[root@rac12pri2 ~]#

If you just got familiar with upstart then there are some bad news: upstart is now replaced with systemd… This might be the right time to read up on that if you aren’t familiar with it yet:

https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/7/html/System_Administrators_Guide/chap-Managing_Services_with_systemd.html

Things are a little different with that, so here is an example how to enable and start the NTP service. It has to be installed first if that hasn’t been the case. You also should add the -x flag in /etc/sysconfig/ntpd. First I would like to see if the service is available. You use systemctl for this-so instead of a chkconfig ntpd –list you call systemctl as shown:

[root@rac12pri ~]# systemctl list-units --type service --all | grep ntpd
ntpd.service                                                                              loaded inactive dead    Network Time Service
ntpdate.service                                                                           loaded inactive dead    Set time via NTP

I have to get used to the new syntax: previously you used “service <whatever> status” and then, if you needed, typed backspace a few times and changed status to start. The new syntax is closer to human language but less practical: systemctl status <service>. Changing status to start requires more typing.

The check proved that the service exists (i.e. the NTP package is installed), but it is not started. We can change this:

[root@rac12pri ~]# systemctl enable ntpd.service
[root@rac12pri ~]# systemctl start ntpd.service
[root@rac12pri ~]# systemctl status ntpd.service
ntpd.service - Network Time Service
Loaded: loaded (/usr/lib/systemd/system/ntpd.service; enabled)
Active: active (running) since Tue 2014-12-16 15:38:47 GMT; 1s ago
Process: 5179 ExecStart=/usr/sbin/ntpd -u ntp:ntp $OPTIONS (code=exited, status=0/SUCCESS)
Main PID: 5180 (ntpd)
CGroup: /system.slice/ntpd.service
└─5180 /usr/sbin/ntpd -u ntp:ntp -g -x

Dec 16 15:38:47 rac12pri ntpd[5180]: Listen normally on 8 eth1 fe80::5054:ff:fe96:ad88 UDP 123
Dec 16 15:38:47 rac12pri ntpd[5180]: Listen normally on 9 eth2 fe80::5054:ff:fec1:cc8e UDP 123
Dec 16 15:38:47 rac12pri ntpd[5180]: Listen normally on 10 eth3 fe80::5054:ff:fe7e:5945 UDP 123
Dec 16 15:38:47 rac12pri ntpd[5180]: Listen normally on 11 eth0 fe80::5054:ff:fe6e:6f67 UDP 123
Dec 16 15:38:47 rac12pri ntpd[5180]: Listening on routing socket on fd #28 for interface updates
Dec 16 15:38:47 rac12pri ntpd[5180]: 0.0.0.0 c016 06 restart
Dec 16 15:38:47 rac12pri ntpd[5180]: 0.0.0.0 c012 02 freq_set ntpd 0.000 PPM
Dec 16 15:38:47 rac12pri ntpd[5180]: 0.0.0.0 c011 01 freq_not_set
Dec 16 15:38:47 rac12pri systemd[1]: Started Network Time Service.
Dec 16 15:38:48 rac12pri ntpd[5180]: 0.0.0.0 c614 04 freq_mode
[root@rac12pri ~]#

The call to “systemctl enable” replaces an invocation of chkconfig to automatically start ntpd as a service (chkconfig ntpd on). Starting the service does not produce any output, hence the need to check the status.

There is a slight caveat with the use of NTP: it is not the default time keeping service. Another tool, named chronyd is used instead.

This causes a problem after the next reboot: chronyd will be started, NTPd won’t be. The Red Hat documentation therefore has a section on how to switch:

[root@rac12pri ~]# systemctl stop chronyd
[root@rac12pri ~]# systemctl disable chronyd
[root@rac12pri ~]# systemctl status chronyd

Storage

Shared storage is provided by KVM. I am using my SSDs in the lab from where I create a few “LUNs”. These must explicitly be made “shareable” to be accessible by more than one guest. Since 12.1.0.2.0 Oracle installs a database for the cluster health monitor by default. Currently I use the following setup for my lab 12.1.0.2 clusters:

  1. +CHM (external redundancy) – 1x 15GB
  2. +OCR (normal redundancy) – 3x 2 GB
  3. +DATA (external redundancy) – 1 x 15GB
  4. +RECO (external redundancy) – 1 x 10 GB

If you use the guided installation of Grid Infrastructure the installer will prompt you for a single disk group only. This means that the CHM database as well as the OCR and voting files be installed in that disk group. I prefer to separate them though, which is why I create a second disk group OCR after the installation has completed and move the voting files and OCR out of +CHM.

DATA and RECO are standard Exadata disk groups and I like to keep things consistent for myself.

I use fdisk to partition the future ASM disks with 1 partition spanning the whole LUN.

Other tasks

A lot of the other pre-installation tasks can actually be performed during the kickstart installation. I still like to use SELinux in permissive mode even though-according to Requirements for Installing Oracle Database 12.1 on RHEL6 or OL6 64-bit (x86-64) (Doc ID 1529864.1)-selinux can be in “enforcing”. The directive in the kickstart file is

selinux –permissive

You shouldn’t have to install additional packages-all packages to be installed should go into the %packages section of the file. Simply copy the package names from the official documentation and paste below the last package in the section. There is one exception to the rule: cvuqdisk must be installed from the Oracle installation media.

Settings for /etc/sysctl.conf and /etc/security/limits.conf can also be made in the kickstart file as shown in the first part of this series.

Storage to be made available to RAC must have permissions set. Since there isn’t an ASMLib in Oracle Linux 7 to my knowledge UDEV will have to be used, and my udev configuration file, too, is in the first part.

To make sure my user and group IDs for the oracle and grid account are the same I create the accounts in the kickstart file as well. Passwords are deliberately not set-they may evolve and I can’t possibly remember them all :)

User equivalence can be set up using a technique I have already described in an earlier blog post. Although the user equivalence setup can be deferred to when you install Grid Infrastructure I still perform it before to allow me to run the cluster verification tool with the -fixup option.

Installing Oracle 12.1.0.2 RAC on Oracle Linux 7-part 1

2 Replies

Now that 12.1.0.2 is certified on RedHat Linux 7 and spin-off environments it’s time to test the installation of RAC on such a system.

The installation of the OS is different from Oracle Linux 5 and 6-with these distributions was very straight forward how to install the operating system the method has changed significantly in release 7. I won’t cover the complete installation here, as always Tim Hall was quicker than me, but it makes me wonder who signed off the user interface for the partitioning “wizard”… I personally think that the kickstart partitioning-information is a lot easier to understand.

http://oracle-base.com/articles/linux/oracle-linux-7-installation.php

After walking through the steps in the graphical installer I thought that I’d rather kickstart the whole lot. If you have not yet used kickstart to create Oracle/Red Hat/CentOS hosts then now is the time to start admiring the technology!

My host is Oracle Linux 6.6 and there aren’t any packages that aren’t part of the distribution, with one exception: I have compiled dnsmasq from the current source since it had a bug when specifying the –bind-interfaces command line option if other dnsmasq processes were already running. libvirt uses dnsmasq extensively for its own networking (I since then upgraded the host to Oracle Linux 7 too and the problem is no longer present)

I don’t like repetitive tasks

Anything that is repetitive is not the best use of anyone’s time, especially not mine. I prefer to run a single command, do something different and come back with the OS already installed. For the installation to work I am relying on PXE booting to pass the kernel and initial RAM disk. TFTP is in charge of that. A Kickstart file is used for the silent installation. Let’s start off with the Pre Execution Boot Environment (I am putting a shameless plug in here for my 12c book which covers this subject extensively if you need more background). Here is my default configuration file in pxelinux.cfg:

default menu.c32
prompt 0

menu title PXE Boot Menu

label oracle-linux 6.6 x86-64
    menu label oracle-linux 6.6 x86-64
    kernel /tftpboot/webroot/images/ol/6/6/vmlinuz
    append initrd=/tftpboot/webroot/images/ol/6/6/initrd.img ks=http://192.168.150.1/ol66.ks ksdevice=eth0

label oracle-linux 7.0 x86-64
    menu label oracle-linux 7.0 x86-64
    kernel /tftpboot/webroot/images/ol/7/0/vmlinuz
    append initrd=/tftpboot/webroot/images/ol/7/0/initrd.img inst.ks=http://192.168.150.1/ol70.ks inst.repo=http://192.168.150.1/ol70

The next step is to ensure that you have the images you are pointing to.

[root@ol62 0]# cp -iv /media/ol70/images/pxeboot/* .
`/media/ol70/images/pxeboot/initrd.img' -> `./initrd.img'
`/media/ol70/images/pxeboot/TRANS.TBL' -> `./TRANS.TBL'
`/media/ol70/images/pxeboot/upgrade.img' -> `./upgrade.img'
`/media/ol70/images/pxeboot/vmlinuz' -> `./vmlinuz'
[root@ol62 0]# pwd
/tftpboot/webroot/images/ol/7/0

The Pre Execution Environment (PXE) will supply the KS (kickstart) directive to the boot process. The Initial RAMdisk and kernel will be pulled via tftp-don’t forget to enable xinetd and tftp if you are not using dnsmasq like me or this won’t work. You also need a DHCP server that provides the NIC of the VM with an IP and the reference to the PXE boot configuration. If you are using a lab server then dnsmasq can do this for you. I believe that admins in real life might not do it that way… The official Red Hat documentation has a nice section on how to configure your environment for pxeboot with the ISC DHCP package, too. Here is the pxe.conf file I used successfully (with that newly compiled dnsnasq-again the stock Oracle 6.x version can’t handle the –bind-interface at the time of writing.

[root@ol62 ~]# cat /etc/dnsmasq.d/pxe.conf
tftp-root=/tftpboot/webroot
enable-tftp

dhcp-boot=pxelinux.0
dhcp-option=vendor:PXEClient,6,2b
dhcp-no-override

pxe-prompt="Press F8 for boot menu", 3
pxe-service=X86PC, "Boot from network", pxelinux
pxe-service=X86PC, "Boot from local hard disk", 0

#log-queries
#log-dhcp

domain=example.com
dhcp-range=192.168.150.50,192.168.150.150,12h

This file then went into /etc/dnsmasq.d/ as you can see, and I restarted the daemon to make it read the file. Initially I found the log-queries and log-dhcp output quite useful to work out what was happening, you can see these messages in /var/log/messages. There are many … so you might want to turn that off as soon as you fixed any potential problems.

Kickstarting

My kickstart file assumes that the installation tree is available via http from 192.168.150.1/ol70. It’s just a loopback mounted ISO image so easy to implement in my host.

My kickstart file is not really special, it simply sets the partitioning schema creating 2 volume groups: rootvg for system related things such as swap, boot and root plus oraclevg for the oracle binaries. I based it primarily on the anaconda-ks.cfg file in /root that has been created during the installation. The kickstart documentationis also rather nice. Since there is no preinstall RPM available right now I resorted to a %post script to add the Oracle-specific settings:

UPDATE

Since I wrote this post Oracle Linux 7.1 has been released which features the 11.2 and 12.1 RDBMS preinstall RPMs rendering some of the steps shown here obsolete.

%post --log=/root/ks-post.log

cat >> /etc/sysctl.conf <<EOF
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
kernel.panic_on_oops = 1
EOF

sysctl -f --system

cat >> /etc/security/limits.conf <<EOF
oracle   soft   nofile    1024
oracle   hard   nofile    65536
oracle   soft   nproc     2047
oracle   hard   nproc     16384
oracle   soft   stack     10240
oracle   hard   stack     32768
oracle   soft   memlock   60397977
oracle   hard   memlock   60397977

grid   soft   nofile    1024
grid   hard   nofile    65536
grid   soft   nproc     2047
grid   hard   nproc     16384
grid   soft   stack     10240
grid   hard   stack     32768
grid   soft   memlock   60397977
grid   hard   memlock   60397977
EOF

cat > /etc/udev/rules.d/61-asm.rules <<EOF
KERNEL=="vd[cdefgh]1", OWNER="grid", GROUP="asmdba" MODE="0660"
EOF

gid=10000
for group in asmdba asmadmin kmdba dgdba backupdba dba oinstall; do
  groupadd -g $(( gid += 1 )) $group ;
done

useradd -u 10000 -g oinstall -G asmdba,kmdba,dgdba,backupdba,dba oracle
useradd -u 10001 -g oinstall -G asmdba,asmadmin,dba grid

mkdir -p /u01/app/oracle
mkdir -p /u01/app/grid

chown -R grid:oinstall /u01
chown -R oracle:oinstall /u01/app/oracle

This is of course just an example-you need to adjust this to your environment. I am assuming a separation of duties here as well which not everyone will (and should!) apply in his environment. There are also quite a few individual packages in the %package list right now that I’ll replace with the call to the preinstall RPM once it’s available. Oracle Linux 7 reflects the fact that many system daemons use a conf.d directory to separate customisations from the main configuration file. Oracle 12.1.0.2 does not check for this: when I tried moving my Oracle specific code into /etc/sysctl.d/50-oracle.conf OUI could not find and parse the file. It subsequently complained about an unknown configuration. So therefore we are back to the old days.

Now all I need to do is to call virt-install on the command line and wait for the KVM environment to be created.

References