DBA survival BLOG

DBA stuff and Oracle Data Guard

How Adaptive Plans work with SQL Plan Baselines?

Posted on November 18, 2016 by Ludovico

Disclaimer: after writing this post (but before publishing it) I have seen that other people already blogged about it, so I am ashamed of publishing it anyway… but that’s blogger’s life 🙂

Wednesday I have got a nice question after my presentation about Adaptive Features at the DOAG16 conference:

What happens when you load an adaptive plan in a SQL Plan Baseline?
Does it load only the final plan or does it load the whole plan including the inactive operations? Will the plan be evaluated again using the inflection point?

I have decided to do some tests in order to give the best possible answer. I did not spend the time to rethink about producing an adaptive plan. Tim Hall already did an excellent test case to create and alter an adaptive plan in his blog, so I have reused massively most of its code. Thanks Tim :-).

I will not post all the code (please find it in Tim’s post), I will go straight to the plans.

First: I have an adaptive plan that resolves to NESTED LOOPS:

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ 
  2    a.data AS tab1_data,
  4    b.data AS tab2_data
  5  FROM   tab1 a
  6         JOIN tab2 b ON b.tab1_id = a.id
  7  WHERE  a.code = 'ONE';

...
 
30 rows selected.

SQL> SET LINESIZE 200 PAGESIZE 100
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
SQL_ID  4r3harjun4dvz, child number 0
-------------------------------------
SELECT a.data AS tab1_data,        b.data AS tab2_data FROM   tab1 a
    JOIN tab2 b ON b.tab1_id = a.id WHERE  a.code = 'ONE'

Plan hash value: 2672205743

-----------------------------------------------------------------------------------------------------------
|   Id  | Operation                               | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                        |               |       |       |     3 (100)|          |
|- *  1 |  HASH JOIN                              |               |    25 |   425 |     3   (0)| 00:00:01 |
|     2 |   NESTED LOOPS                          |               |    25 |   425 |     3   (0)| 00:00:01 |
|     3 |    NESTED LOOPS                         |               |    25 |   425 |     3   (0)| 00:00:01 |
|-    4 |     STATISTICS COLLECTOR                |               |       |       |            |          |
|     5 |      TABLE ACCESS BY INDEX ROWID BATCHED| TAB1          |     1 |    11 |     2   (0)| 00:00:01 |
|  *  6 |       INDEX RANGE SCAN                  | TAB1_CODE     |     1 |       |     1   (0)| 00:00:01 |
|  *  7 |     INDEX RANGE SCAN                    | TAB2_TAB1_FKI |    25 |       |     0   (0)|          |
|     8 |    TABLE ACCESS BY INDEX ROWID          | TAB2          |    25 |   150 |     1   (0)| 00:00:01 |
|-    9 |   TABLE ACCESS FULL                     | TAB2          |    25 |   150 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   1 - access("B"."TAB1_ID"="A"."ID")
   6 - access("A"."CODE"='ONE')
   7 - access("B"."TAB1_ID"="A"."ID")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)


33 rows selected.

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */

2 a.data AS tab1_data,

4 b.data AS tab2_data

5 FROM tab1 a

6 JOIN tab2 b ON b.tab1_id = a.id

7 WHERE a.code = 'ONE';

...

30 rows selected.

SQL> SET LINESIZE 200 PAGESIZE 100

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive'));

PLAN_TABLE_OUTPUT

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

SQL_ID 4r3harjun4dvz, child number 0

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

SELECT a.data AS tab1_data, b.data AS tab2_data FROM tab1 a

JOIN tab2 b ON b.tab1_id = a.id WHERE a.code = 'ONE'

Plan hash value: 2672205743

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

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

| 0 | SELECT STATEMENT | | | | 3 (100)| |

|- * 1 | HASH JOIN | | 25 | 425 | 3 (0)| 00:00:01 |

| 2 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 |

| 3 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 |

|- 4 | STATISTICS COLLECTOR | | | | | |

| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 11 | 2 (0)| 00:00:01 |

| * 6 | INDEX RANGE SCAN | TAB1_CODE | 1 | | 1 (0)| 00:00:01 |

| * 7 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 25 | | 0 (0)| |

| 8 | TABLE ACCESS BY INDEX ROWID | TAB2 | 25 | 150 | 1 (0)| 00:00:01 |

|- 9 | TABLE ACCESS FULL | TAB2 | 25 | 150 | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("B"."TAB1_ID"="A"."ID")

6 - access("A"."CODE"='ONE')

7 - access("B"."TAB1_ID"="A"."ID")

Note

-----

- this is an adaptive plan (rows marked '-' are inactive)

33 rows selected.

Second: I load the plan (lazy way: using baseline capture at session level)

SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;

Session altered.

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */
  2         a.data AS tab1_data,
  3         b.data AS tab2_data
  4  FROM   tab1 a
  5         JOIN tab2 b ON b.tab1_id = a.id
  6  WHERE  a.code = 'ONE';

 TAB1_DATA  TAB2_DATA
---------- ----------
...

30 rows selected.

SQL> r 
  1  SELECT /*+ GATHER_PLAN_STATISTICS */
  2         a.data AS tab1_data,
  3         b.data AS tab2_data
  4  FROM   tab1 a
  5         JOIN tab2 b ON b.tab1_id = a.id
  6* WHERE  a.code = 'ONE'

 TAB1_DATA  TAB2_DATA
---------- ----------
...

30 rows selected.

SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;

Session altered.

SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE           PLAN_NAME                          SQL_TEXT                                   ENA ACC FIX
-------------------- ---------------------------------- ------------------------------------------ --- --- ---
SQL_6c4c6680810dd01a SQL_PLAN_6sm36h20hvn0u55a25f73     SELECT /*+ GATHER_PLAN_STATISTICS */       YES YES NO
                                                                       a.data AS tab1_data,
                                                                       b.data A

SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;

Session altered.

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */

2 a.data AS tab1_data,

3 b.data AS tab2_data

4 FROM tab1 a

5 JOIN tab2 b ON b.tab1_id = a.id

6 WHERE a.code = 'ONE';

TAB1_DATA TAB2_DATA

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

...

30 rows selected.

SQL> r

1 SELECT /*+ GATHER_PLAN_STATISTICS */

2 a.data AS tab1_data,

3 b.data AS tab2_data

4 FROM tab1 a

5 JOIN tab2 b ON b.tab1_id = a.id

6* WHERE a.code = 'ONE'

TAB1_DATA TAB2_DATA

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

...

30 rows selected.

SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;

Session altered.

SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME SQL_TEXT ENA ACC FIX

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

SQL_6c4c6680810dd01a SQL_PLAN_6sm36h20hvn0u55a25f73 SELECT /*+ GATHER_PLAN_STATISTICS */ YES YES NO

a.data AS tab1_data,

b.data A

Third: re-run the statement and check the plan

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */
  2         a.data AS tab1_data,
  3         b.data AS tab2_data
  4  FROM   tab1 a
  5         JOIN tab2 b ON b.tab1_id = a.id
  6  WHERE  a.code = 'ONE';

 TAB1_DATA  TAB2_DATA
---------- ----------
...

30 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(format=>'+adaptive'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
SQL_ID  1km5kczcgr0fr, child number 3
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */        a.data AS tab1_data,
b.data AS tab2_data FROM   tab1 a        JOIN tab2 b ON b.tab1_id =
a.id WHERE  a.code = 'ONE'

Plan hash value: 2672205743

-------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |               |       |       |     3 (100)|          |
|   1 |  NESTED LOOPS                         |               |    25 |   425 |     3   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                        |               |    25 |   425 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TAB1          |     1 |    11 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | TAB1_CODE     |     1 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                   | TAB2_TAB1_FKI |    25 |       |     0   (0)|          |
|   6 |   TABLE ACCESS BY INDEX ROWID         | TAB2          |    25 |   150 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

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

   4 - access("A"."CODE"='ONE')
   5 - access("B"."TAB1_ID"="A"."ID")

Note
-----
   - SQL plan baseline SQL_PLAN_6sm36h20hvn0u55a25f73 used for this statement


30 rows selected.

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */

2 a.data AS tab1_data,

3 b.data AS tab2_data

4 FROM tab1 a

5 JOIN tab2 b ON b.tab1_id = a.id

6 WHERE a.code = 'ONE';

TAB1_DATA TAB2_DATA

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

...

30 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(format=>'+adaptive'));

PLAN_TABLE_OUTPUT

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

SQL_ID 1km5kczcgr0fr, child number 3

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

SELECT /*+ GATHER_PLAN_STATISTICS */ a.data AS tab1_data,

b.data AS tab2_data FROM tab1 a JOIN tab2 b ON b.tab1_id =

a.id WHERE a.code = 'ONE'

Plan hash value: 2672205743

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

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

| 0 | SELECT STATEMENT | | | | 3 (100)| |

| 1 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 |

| 2 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 11 | 2 (0)| 00:00:01 |

|* 4 | INDEX RANGE SCAN | TAB1_CODE | 1 | | 1 (0)| 00:00:01 |

|* 5 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 25 | | 0 (0)| |

| 6 | TABLE ACCESS BY INDEX ROWID | TAB2 | 25 | 150 | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - access("A"."CODE"='ONE')

5 - access("B"."TAB1_ID"="A"."ID")

Note

-----

- SQL plan baseline SQL_PLAN_6sm36h20hvn0u55a25f73 used for this statement

30 rows selected.

It does not look adaptive, but I can also check from the function DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE:

SQL> select * from table (DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SQL_6c4c6680810dd01a', format=>'+adaptive'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_6c4c6680810dd01a
SQL text: SELECT /*+ GATHER_PLAN_STATISTICS */        a.data AS tab1_data,
            b.data AS tab2_data FROM   tab1 a        JOIN tab2 b ON b.tab1_id =
          a.id WHERE  a.code = 'ONE'
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_6sm36h20hvn0u55a25f73         Plan id: 1436704627
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 2672205743

---------------------------------------------------------------------------------------------------------
|   Id  | Operation                             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                      |               |    25 |   425 |     3   (0)| 00:00:01 |
|     1 |  NESTED LOOPS                         |               |    25 |   425 |     3   (0)| 00:00:01 |
|     2 |   NESTED LOOPS                        |               |    25 |   425 |     3   (0)| 00:00:01 |
|     3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TAB1          |     1 |    11 |     2   (0)| 00:00:01 |
|  *  4 |     INDEX RANGE SCAN                  | TAB1_CODE     |     1 |       |     1   (0)| 00:00:01 |
|  *  5 |    INDEX RANGE SCAN                   | TAB2_TAB1_FKI |    25 |       |     0   (0)| 00:00:01 |
|     6 |   TABLE ACCESS BY INDEX ROWID         | TAB2          |    25 |   150 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

   4 - access("A"."CODE"='ONE')
   5 - access("B"."TAB1_ID"="A"."ID")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

37 rows selected.

SQL> select * from table (DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SQL_6c4c6680810dd01a', format=>'+adaptive'));

PLAN_TABLE_OUTPUT

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

SQL handle: SQL_6c4c6680810dd01a

SQL text: SELECT /*+ GATHER_PLAN_STATISTICS */ a.data AS tab1_data,

b.data AS tab2_data FROM tab1 a JOIN tab2 b ON b.tab1_id =

a.id WHERE a.code = 'ONE'

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

Plan name: SQL_PLAN_6sm36h20hvn0u55a25f73 Plan id: 1436704627

Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE

Plan rows: From dictionary

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

Plan hash value: 2672205743

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

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

| 0 | SELECT STATEMENT | | 25 | 425 | 3 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 |

| 2 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 11 | 2 (0)| 00:00:01 |

| * 4 | INDEX RANGE SCAN | TAB1_CODE | 1 | | 1 (0)| 00:00:01 |

| * 5 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 25 | | 0 (0)| 00:00:01 |

| 6 | TABLE ACCESS BY INDEX ROWID | TAB2 | 25 | 150 | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - access("A"."CODE"='ONE')

5 - access("B"."TAB1_ID"="A"."ID")

Note

-----

- this is an adaptive plan (rows marked '-' are inactive)

37 rows selected.

Again, despite in the Note section it says it is adaptive, it does not look like an adaptive plan.

Can I trust this information? Of course I did not and tried to check the plan with and without baseline after changing the rows to force a plan switch to HJ (again taking Tim’s example):

SQL> INSERT /*+ APPEND */ INTO tab1
  2   SELECT tab1_seq.nextval,
  2         'ONE',
  4         level
  5  FROM   dual
  6  CONNECT BY level <= 10000;

  10000 rows created.

SQL> COMMIT;

Commit complete.

SQL> alter session set optimizer_use_sql_plan_baselines=false;

Session altered.

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */
  2         a.data AS tab1_data,
  3         b.data AS tab2_data
  4  FROM   tab1 a
  5         JOIN tab2 b ON b.tab1_id = a.id
  6  WHERE  a.code = 'ONE';

 TAB1_DATA  TAB2_DATA
---------- ----------
...

30 rows selected.


SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1km5kczcgr0fr, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */        a.data AS tab1_data,
b.data AS tab2_data FROM   tab1 a        JOIN tab2 b ON b.tab1_id =
a.id WHERE  a.code = 'ONE'

Plan hash value: 1599395313

------------------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                               | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                        |               |      1 |        |     30 |00:00:00.01 |     142 |       |       |          |
|  *  1 |  HASH JOIN                              |               |      1 |     25 |     30 |00:00:00.01 |     142 |  2261K|  2261K| 2180K (0)|
|-    2 |   NESTED LOOPS                          |               |      1 |     25 |  20001 |00:00:00.01 |     124 |       |       |          |
|-    3 |    NESTED LOOPS                         |               |      1 |     25 |  20001 |00:00:00.01 |     124 |       |       |          |
|-    4 |     STATISTICS COLLECTOR                |               |      1 |        |  20001 |00:00:00.01 |     124 |       |       |          |
|     5 |      TABLE ACCESS BY INDEX ROWID BATCHED| TAB1          |      1 |      1 |  20001 |00:00:00.01 |     124 |       |       |          |
|  *  6 |       INDEX RANGE SCAN                  | TAB1_CODE     |      1 |      1 |  20001 |00:00:00.01 |      74 |       |       |          |
|- *  7 |     INDEX RANGE SCAN                    | TAB2_TAB1_FKI |      0 |     25 |      0 |00:00:00.01 |       0 |       |       |          |
|-    8 |    TABLE ACCESS BY INDEX ROWID          | TAB2          |      0 |     25 |      0 |00:00:00.01 |       0 |       |       |          |
|     9 |   TABLE ACCESS FULL                     | TAB2          |      1 |     25 |    100 |00:00:00.01 |      18 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - access("B"."TAB1_ID"="A"."ID")
   6 - access("A"."CODE"='ONE')
   7 - access("B"."TAB1_ID"="A"."ID")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)


34 rows selected.

SQL> alter session set optimizer_use_sql_plan_baselines=true;

Session altered.

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */
  2         a.data AS tab1_data,
  3         b.data AS tab2_data
  4  FROM   tab1 a
  5         JOIN tab2 b ON b.tab1_id = a.id
  6  WHERE  a.code = 'ONE';

 TAB1_DATA  TAB2_DATA
---------- ----------
...

30 rows selected.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1km5kczcgr0fr, child number 2
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */        a.data AS tab1_data,
b.data AS tab2_data FROM   tab1 a        JOIN tab2 b ON b.tab1_id =
a.id WHERE  a.code = 'ONE'

Plan hash value: 2672205743

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |               |      1 |        |     30 |00:00:00.01 |   30889 |
|   1 |  NESTED LOOPS                         |               |      1 |     25 |     30 |00:00:00.01 |   30889 |
|   2 |   NESTED LOOPS                        |               |      1 |     25 |     30 |00:00:00.01 |   30886 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TAB1          |      1 |      1 |  20001 |00:00:00.01 |     125 |
|*  4 |     INDEX RANGE SCAN                  | TAB1_CODE     |      1 |      1 |  20001 |00:00:00.01 |      75 |
|*  5 |    INDEX RANGE SCAN                   | TAB2_TAB1_FKI |  20001 |     25 |     30 |00:00:00.02 |   30761 |
|   6 |   TABLE ACCESS BY INDEX ROWID         | TAB2          |     30 |     25 |     30 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------------------------

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

   4 - access("A"."CODE"='ONE')
   5 - access("B"."TAB1_ID"="A"."ID")

Note
-----
   - SQL plan baseline SQL_PLAN_6sm36h20hvn0u55a25f73 used for this statement


30 rows selected.

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

SQL> INSERT /*+ APPEND */ INTO tab1

2 SELECT tab1_seq.nextval,

2 'ONE',

4 level

5 FROM dual

6 CONNECT BY level <= 10000;

10000 rows created.

SQL> COMMIT;

Commit complete.

SQL> alter session set optimizer_use_sql_plan_baselines=false;

Session altered.

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */

2 a.data AS tab1_data,

3 b.data AS tab2_data

4 FROM tab1 a

5 JOIN tab2 b ON b.tab1_id = a.id

6 WHERE a.code = 'ONE';

TAB1_DATA TAB2_DATA

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

...

30 rows selected.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));

PLAN_TABLE_OUTPUT

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

SQL_ID 1km5kczcgr0fr, child number 0

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

SELECT /*+ GATHER_PLAN_STATISTICS */ a.data AS tab1_data,

b.data AS tab2_data FROM tab1 a JOIN tab2 b ON b.tab1_id =

a.id WHERE a.code = 'ONE'

Plan hash value: 1599395313

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

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

| 0 | SELECT STATEMENT | | 1 | | 30 |00:00:00.01 | 142 | | | |

| * 1 | HASH JOIN | | 1 | 25 | 30 |00:00:00.01 | 142 | 2261K| 2261K| 2180K (0)|

|- 2 | NESTED LOOPS | | 1 | 25 | 20001 |00:00:00.01 | 124 | | | |

|- 3 | NESTED LOOPS | | 1 | 25 | 20001 |00:00:00.01 | 124 | | | |

|- 4 | STATISTICS COLLECTOR | | 1 | | 20001 |00:00:00.01 | 124 | | | |

| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 1 | 20001 |00:00:00.01 | 124 | | | |

| * 6 | INDEX RANGE SCAN | TAB1_CODE | 1 | 1 | 20001 |00:00:00.01 | 74 | | | |

|- * 7 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 0 | 25 | 0 |00:00:00.01 | 0 | | | |

|- 8 | TABLE ACCESS BY INDEX ROWID | TAB2 | 0 | 25 | 0 |00:00:00.01 | 0 | | | |

| 9 | TABLE ACCESS FULL | TAB2 | 1 | 25 | 100 |00:00:00.01 | 18 | | | |

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

Predicate Information (identified by operation id):

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

1 - access("B"."TAB1_ID"="A"."ID")

6 - access("A"."CODE"='ONE')

7 - access("B"."TAB1_ID"="A"."ID")

Note

-----

- this is an adaptive plan (rows marked '-' are inactive)

34 rows selected.

SQL> alter session set optimizer_use_sql_plan_baselines=true;

Session altered.

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */

2 a.data AS tab1_data,

3 b.data AS tab2_data

4 FROM tab1 a

5 JOIN tab2 b ON b.tab1_id = a.id

6 WHERE a.code = 'ONE';

TAB1_DATA TAB2_DATA

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

...

30 rows selected.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));

PLAN_TABLE_OUTPUT

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

SQL_ID 1km5kczcgr0fr, child number 2

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

SELECT /*+ GATHER_PLAN_STATISTICS */ a.data AS tab1_data,

b.data AS tab2_data FROM tab1 a JOIN tab2 b ON b.tab1_id =

a.id WHERE a.code = 'ONE'

Plan hash value: 2672205743

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

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

| 0 | SELECT STATEMENT | | 1 | | 30 |00:00:00.01 | 30889 |

| 1 | NESTED LOOPS | | 1 | 25 | 30 |00:00:00.01 | 30889 |

| 2 | NESTED LOOPS | | 1 | 25 | 30 |00:00:00.01 | 30886 |

| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 1 | 20001 |00:00:00.01 | 125 |

|* 4 | INDEX RANGE SCAN | TAB1_CODE | 1 | 1 | 20001 |00:00:00.01 | 75 |

|* 5 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 20001 | 25 | 30 |00:00:00.02 | 30761 |

| 6 | TABLE ACCESS BY INDEX ROWID | TAB2 | 30 | 25 | 30 |00:00:00.01 | 3 |

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

Predicate Information (identified by operation id):

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

4 - access("A"."CODE"='ONE')

5 - access("B"."TAB1_ID"="A"."ID")

Note

-----

- SQL plan baseline SQL_PLAN_6sm36h20hvn0u55a25f73 used for this statement

30 rows selected.

After changing the rows:

when I do not use the baseline, the plan resolves to HASH JOIN
when I use it, the baseline forces to NESTED LOOPS.

So the plan in the baseline is not adaptive and it forces to what has been loaded. Is it the final plan or the original one? I have to capture it again to see if a new baseline appears:

SQL>  ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;

Session altered.

SELECT /*+ GATHER_PLAN_STATISTICS */
  2         a.data AS tab1_data,
  3         b.data AS tab2_data
  4  FROM   tab1 a
  5         JOIN tab2 b ON b.tab1_id = a.id
  6  WHERE  a.code = 'ONE';

 TAB1_DATA  TAB2_DATA
---------- ----------
...

30 rows selected.

SQL> r
  1  SELECT /*+ GATHER_PLAN_STATISTICS */
  2         a.data AS tab1_data,
  3         b.data AS tab2_data
  4  FROM   tab1 a
  5         JOIN tab2 b ON b.tab1_id = a.id
  6* WHERE  a.code = 'ONE'

 TAB1_DATA  TAB2_DATA
---------- ----------
...

30 rows selected.

SQL>  ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;

Session altered.

SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE           PLAN_NAME                      SQL_TEXT                                                                         ENABLED   ACCEPTED  FIXED
-------------------- ------------------------------ -------------------------------------------------------------------------------- --------- --------- ---------
SQL_6c4c6680810dd01a SQL_PLAN_6sm36h20hvn0u55a25f73 SELECT /*+ GATHER_PLAN_STATISTICS */                                             YES       YES       NO
                                                           a.data AS tab1_data,
                                                           b.data A

SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;

Session altered.

SELECT /*+ GATHER_PLAN_STATISTICS */

2 a.data AS tab1_data,

3 b.data AS tab2_data

4 FROM tab1 a

5 JOIN tab2 b ON b.tab1_id = a.id

6 WHERE a.code = 'ONE';

TAB1_DATA TAB2_DATA

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

...

30 rows selected.

SQL> r

1 SELECT /*+ GATHER_PLAN_STATISTICS */

2 a.data AS tab1_data,

3 b.data AS tab2_data

4 FROM tab1 a

5 JOIN tab2 b ON b.tab1_id = a.id

6* WHERE a.code = 'ONE'

TAB1_DATA TAB2_DATA

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

...

30 rows selected.

SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;

Session altered.

SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME SQL_TEXT ENABLED ACCEPTED FIXED

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

SQL_6c4c6680810dd01a SQL_PLAN_6sm36h20hvn0u55a25f73 SELECT /*+ GATHER_PLAN_STATISTICS */ YES YES NO

a.data AS tab1_data,

b.data A

A new baseline does not appear, so it looks that the original plan is considered by the capture process and not the resolved one! To be 100% sure, let’s try to drop the existing one and redo the test:

SQL> connect / as sysdba
Connected
SQL> DECLARE
  2   v_dropped_plans number;
  3 BEGIN
  4   v_dropped_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE (
  5      sql_handle => 'SQL_6c4c6680810dd01a'
  6 );
  7 END;
  8 /
PL/SQL procedure successfully completed.

SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

no rows selected

SQL> conn ludo/ludo
Connected.
SQL>  ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;

Session altered.

SELECT /*+ GATHER_PLAN_STATISTICS */
  2         a.data AS tab1_data,
  3         b.data AS tab2_data
  4  FROM   tab1 a
  5         JOIN tab2 b ON b.tab1_id = a.id
  6  WHERE  a.code = 'ONE';

 TAB1_DATA  TAB2_DATA
---------- ----------
...

30 rows selected.

SQL> r
  1  SELECT /*+ GATHER_PLAN_STATISTICS */
  2         a.data AS tab1_data,
  3         b.data AS tab2_data
  4  FROM   tab1 a
  5         JOIN tab2 b ON b.tab1_id = a.id
  6* WHERE  a.code = 'ONE'

 TAB1_DATA  TAB2_DATA
---------- ----------
...

30 rows selected.

SQL>  ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;

Session altered.

SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE           PLAN_NAME                      SQL_TEXT                                                                         ENABLED   ACCEPTED  FIXED
-------------------- ------------------------------ -------------------------------------------------------------------------------- --------- --------- ---------
SQL_6c4c6680810dd01a SQL_PLAN_6sm36h20hvn0u55a25f73 SELECT /*+ GATHER_PLAN_STATISTICS */                                             YES       YES       NO
                                                           a.data AS tab1_data,
                                                           b.data A

SQL> select * from table (DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SQL_6c4c6680810dd01a', format=>'+adaptive'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_6c4c6680810dd01a
SQL text: SELECT /*+ GATHER_PLAN_STATISTICS */        a.data AS tab1_data,
            b.data AS tab2_data FROM   tab1 a        JOIN tab2 b ON b.tab1_id =
          a.id WHERE  a.code = 'ONE'
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_6sm36h20hvn0u55a25f73         Plan id: 1436704627
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 2672205743

---------------------------------------------------------------------------------------------------------
|   Id  | Operation                             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                      |               |    25 |   425 |     3   (0)| 00:00:01 |
|     1 |  NESTED LOOPS                         |               |    25 |   425 |     3   (0)| 00:00:01 |
|     2 |   NESTED LOOPS                        |               |    25 |   425 |     3   (0)| 00:00:01 |
|     3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TAB1          |     1 |    11 |     2   (0)| 00:00:01 |
|  *  4 |     INDEX RANGE SCAN                  | TAB1_CODE     |     1 |       |     1   (0)| 00:00:01 |
|  *  5 |    INDEX RANGE SCAN                   | TAB2_TAB1_FKI |    25 |       |     0   (0)| 00:00:01 |
|     6 |   TABLE ACCESS BY INDEX ROWID         | TAB2          |    25 |   150 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

   4 - access("A"."CODE"='ONE')
   5 - access("B"."TAB1_ID"="A"."ID")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

37 rows selected.

100

101

102

103

SQL> connect / as sysdba

Connected

SQL> DECLARE

2 v_dropped_plans number;

3 BEGIN

4 v_dropped_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE (

5 sql_handle => 'SQL_6c4c6680810dd01a'

6 );

7 END;

8 /

PL/SQL procedure successfully completed.

SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

no rows selected

SQL> conn ludo/ludo

Connected.

SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;

Session altered.

SELECT /*+ GATHER_PLAN_STATISTICS */

2 a.data AS tab1_data,

3 b.data AS tab2_data

4 FROM tab1 a

5 JOIN tab2 b ON b.tab1_id = a.id

6 WHERE a.code = 'ONE';

TAB1_DATA TAB2_DATA

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

...

30 rows selected.

SQL> r

1 SELECT /*+ GATHER_PLAN_STATISTICS */

2 a.data AS tab1_data,

3 b.data AS tab2_data

4 FROM tab1 a

5 JOIN tab2 b ON b.tab1_id = a.id

6* WHERE a.code = 'ONE'

TAB1_DATA TAB2_DATA

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

...

30 rows selected.

SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;

Session altered.

SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME SQL_TEXT ENABLED ACCEPTED FIXED

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

SQL_6c4c6680810dd01a SQL_PLAN_6sm36h20hvn0u55a25f73 SELECT /*+ GATHER_PLAN_STATISTICS */ YES YES NO

a.data AS tab1_data,

b.data A

SQL> select * from table (DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SQL_6c4c6680810dd01a', format=>'+adaptive'));

PLAN_TABLE_OUTPUT

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

SQL handle: SQL_6c4c6680810dd01a

SQL text: SELECT /*+ GATHER_PLAN_STATISTICS */ a.data AS tab1_data,

b.data AS tab2_data FROM tab1 a JOIN tab2 b ON b.tab1_id =

a.id WHERE a.code = 'ONE'

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

Plan name: SQL_PLAN_6sm36h20hvn0u55a25f73 Plan id: 1436704627

Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE

Plan rows: From dictionary

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

Plan hash value: 2672205743

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

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

| 0 | SELECT STATEMENT | | 25 | 425 | 3 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 |

| 2 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 11 | 2 (0)| 00:00:01 |

| * 4 | INDEX RANGE SCAN | TAB1_CODE | 1 | | 1 (0)| 00:00:01 |

| * 5 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 25 | | 0 (0)| 00:00:01 |

| 6 | TABLE ACCESS BY INDEX ROWID | TAB2 | 25 | 150 | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - access("A"."CODE"='ONE')

5 - access("B"."TAB1_ID"="A"."ID")

Note

-----

- this is an adaptive plan (rows marked '-' are inactive)

37 rows selected.

So, despite the fact that I have an adaptive plan that switches from NL to HJ, only the NESTED LOOPS operations are captured in the baseline, I can infer the only the original plan is loaded as SQL Plan Baseline.

References:

How to fix CPU usage problem in 12c due to DBMS_FEATURE_AWR

Posted on June 6, 2016 by Ludovico

I love my job because I always have suprises. This week’s surprise has been another problem related to SQL Plan Directives in 12c. Because it is a common problem that potentially affects ALL the customers, I am glad to share the solution on my blog 😀

Symptom of the problem: High CPU usage on the server

My customer’s DBA team has spotted a consistent high CPU utilisation on its servers:

Everyday, at the same time, and for 20-40 minutes, the servers hosting the Oracle databases run literally out of CPU.

Troubleshooting

Ok, it would be too easy to give the solution now. If you cannot wait, jump at the end of this post. But what I like more is to explain how I came to it.

First, I gave a look at the processes consuming CPU. Most of the servers have many consolidated databases on them. Surprisingly, this is what I have found:

It seems that the source of the problem is not a single database, but all of them. Isn’t it? And I see another pattern here: the CPU usage comes always from the [m001] process, so it is not related to a user process.

My customer has Diagnostic Pack so it is easy to go deeper, but you can get the same result with other free tools like s-ash, statspack and snapper. However, this is what I have found in the Instance Top Activity:

Ok, everything comes from a single query with sql_id auyf8px9ywc6j. This is the full sql_text:

(SELECT /*+ FULL(ST) */ SN.DBID ,SN.INSTANCE_NUMBER ,SN.STARTUP_TIME ,ST.STAT_ID ,ST.STAT_NAME ,MIN(SN.SNAP_ID) AS MIN_SNAP ,MAX(SN.SNAP_ID) AS MAX_SNAP ,MIN(CAST(BEGIN_INTERVAL_TIME AS DATE)) AS MIN_DATE ,MAX(CAST(END_INTERVAL_TIME AS DATE)) AS MAX_DATE
FROM DBA_HIST_SNAPSHOT SN ,WRH$_STAT_NAME ST
WHERE SN.BEGIN_INTERVAL_TIME > TRUNC(SYSDATE) - 7 AND SN.END_INTERVAL_TIME < TRUNC(SYSDATE) AND SN.DBID = ST.DBID AND ST.STAT_NAME IN ('DB time', 'DB CPU') GROUP BY SN.DBID,SN.INSTANCE_NUMBER,SN.STARTUP_TIME,ST.STAT_ID,ST.STAT_NAME ) ,DELTA_DATA AS
(SELECT SR.DBID ,SR.INSTANCE_NUMBER ,SR.STAT_NAME ,CASE WHEN SR.STARTUP_TIME BETWEEN SR.MIN_DATE AND SR.MAX_DATE THEN TM1.VALUE + (TM2.VALUE - TM1.VALUE) ELSE (TM2.VALUE - TM1.VALUE) END AS DELTA_TIME
FROM WRH$_SYS_TIME_MODEL TM1 ,WRH$_SYS_TIME_MODEL TM2 ,SNAP_RANGES SR
WHERE TM1.DBID = SR.DBID AND TM1.INSTANCE_NUMBER = SR.INSTANCE_NUMBER AND TM1.SNAP_ID = SR.MIN_SNAP AND TM1.STAT_ID = SR.STAT_ID AND TM2.DBID = SR.DBID AND TM2.INSTANCE_NUMBER = SR.INSTANCE_NUMBER AND TM2.SNAP_ID = SR.MAX_SNAP AND TM2.STAT_ID = SR.STAT_ID )
SELECT STAT_NAME ,ROUND(SUM(DELTA_TIME/1000000),2) AS SECS
FROM DELTA_DATA GROUP BY STAT_NAME

(SELECT /*+ FULL(ST) */ SN.DBID ,SN.INSTANCE_NUMBER ,SN.STARTUP_TIME ,ST.STAT_ID ,ST.STAT_NAME ,MIN(SN.SNAP_ID) AS MIN_SNAP ,MAX(SN.SNAP_ID) AS MAX_SNAP ,MIN(CAST(BEGIN_INTERVAL_TIME AS DATE)) AS MIN_DATE ,MAX(CAST(END_INTERVAL_TIME AS DATE)) AS MAX_DATE

FROM DBA_HIST_SNAPSHOT SN ,WRH$_STAT_NAME ST

WHERE SN.BEGIN_INTERVAL_TIME > TRUNC(SYSDATE) - 7 AND SN.END_INTERVAL_TIME < TRUNC(SYSDATE) AND SN.DBID = ST.DBID AND ST.STAT_NAME IN ('DB time', 'DB CPU') GROUP BY SN.DBID,SN.INSTANCE_NUMBER,SN.STARTUP_TIME,ST.STAT_ID,ST.STAT_NAME ) ,DELTA_DATA AS

(SELECT SR.DBID ,SR.INSTANCE_NUMBER ,SR.STAT_NAME ,CASE WHEN SR.STARTUP_TIME BETWEEN SR.MIN_DATE AND SR.MAX_DATE THEN TM1.VALUE + (TM2.VALUE - TM1.VALUE) ELSE (TM2.VALUE - TM1.VALUE) END AS DELTA_TIME

FROM WRH$_SYS_TIME_MODEL TM1 ,WRH$_SYS_TIME_MODEL TM2 ,SNAP_RANGES SR

WHERE TM1.DBID = SR.DBID AND TM1.INSTANCE_NUMBER = SR.INSTANCE_NUMBER AND TM1.SNAP_ID = SR.MIN_SNAP AND TM1.STAT_ID = SR.STAT_ID AND TM2.DBID = SR.DBID AND TM2.INSTANCE_NUMBER = SR.INSTANCE_NUMBER AND TM2.SNAP_ID = SR.MAX_SNAP AND TM2.STAT_ID = SR.STAT_ID )

SELECT STAT_NAME ,ROUND(SUM(DELTA_TIME/1000000),2) AS SECS

FROM DELTA_DATA GROUP BY STAT_NAME

It looks like something made by a DBA, but it comes from the MMON.

Looking around, it seems closely related to two PL/SQL calls that I could find in the SQL Monitor and that systematically fail every day:

DBMS_FEATURE_AWR function calls internally the SQL auyf8px9ywc6j.

The MOS does not know anything about that query, but the internet does:

Oh no, not Franck again! He always discovers new stuff and blogs about it before I do 🙂

In his blog post, he points out that the query fails because of error ORA-12751 (resource plan limiting CPU usage) and that it is a problem of Adaptive Dynamic Sampling. Is it true?

What I like to do when I have a problematic sql_id, is to run sqld360 from Mauro Pagano, but the resulting zip file does not contain anything useful, because actually there are no executions and no plans.

SQL> select sql_id,  executions, loads, cpu_time from v$sqlstats where sql_id='auyf8px9ywc6j';

SQL_ID        EXECUTIONS      LOADS   CPU_TIME
------------- ---------- ---------- ----------
auyf8px9ywc6j          0         11          0

SQL> select sql_id,  child_number from v$sql where sql_id='auyf8px9ywc6j';

no rows selected

SQL>

SQL> select sql_id, executions, loads, cpu_time from v$sqlstats where sql_id='auyf8px9ywc6j';

SQL_ID EXECUTIONS LOADS CPU_TIME

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

auyf8px9ywc6j 0 11 0

SQL> select sql_id, child_number from v$sql where sql_id='auyf8px9ywc6j';

no rows selected

SQL>

During the execution of the statement (or better, during the period with high CPU usage), there is an entry in v$sql, but no plans associated:

SQL> select sql_id, child_number from v$sql where sql_id='auyf8px9ywc6j';

SQL_ID        CHILD_NUMBER
------------- ------------
auyf8px9ywc6j            0

SQL> select * from table (dbms_xplan.display_cursor('auyf8px9ywc6j',0, 'ALL +NOTE'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auyf8px9ywc6j, child number 0

WITH SNAP_RANGES AS (SELECT /*+ FULL(ST) */ SN.DBID ,SN.INSTANCE_NUMBER
,SN.STARTUP_TIME ,ST.STAT_ID ,ST.STAT_NAME ,MIN(SN.SNAP_ID) AS MIN_SNAP
,MAX(SN.SNAP_ID) AS MAX_SNAP ,MIN(CAST(BEGIN_INTERVAL_TIME AS DATE)) AS
MIN_DATE ,MAX(CAST(END_INTERVAL_TIME AS DATE)) AS MAX_DATE FROM
DBA_HIST_SNAPSHOT SN ,WRH$_STAT_NAME ST WHERE SN.BEGIN_INTERVAL_TIME >
TRUNC(SYSDATE) - 7 AND SN.END_INTERVAL_TIME < TRUNC(SYSDATE) AND
SN.DBID = ST.DBID AND ST.STAT_NAME IN ('DB time', 'DB CPU') GROUP BY
SN.DBID,SN.INSTANCE_NUMBER,SN.STARTUP_TIME,ST.STAT_ID,ST.STAT_NAME )
,DELTA_DATA AS (SELECT SR.DBID ,SR.INSTANCE_NUMBER ,SR.STAT_NAME ,CASE
WHEN SR.STARTUP_TIME BETWEEN SR.MIN_DATE AND SR.MAX_DATE THEN TM1.VALUE
+ (TM2.VALUE - TM1.VALUE) ELSE (TM2.VALUE - TM1.VALUE) END AS
DELTA_TIME FROM WRH$_SYS_TIME_MODEL TM1 ,WRH$_SYS_TIME_MODEL TM2
,SNAP_RANGES SR WHERE TM1.DBID = SR.DBID AND TM1.INSTANCE_NUMBER =
SR.INSTANCE_NUMBER AND TM1.SNAP_ID = SR.MIN_SNAP AND TM1.STAT_ID =
SR.STAT_ID AND TM2.DBID = SR.DBID AND TM2.

NOTE: cannot fetch plan for SQL_ID: auyf8px9ywc6j, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)


22 rows selected.

SQL> select sql_id, child_number from v$sql where sql_id='auyf8px9ywc6j';

SQL_ID CHILD_NUMBER

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

auyf8px9ywc6j 0

SQL> select * from table (dbms_xplan.display_cursor('auyf8px9ywc6j',0, 'ALL +NOTE'));

PLAN_TABLE_OUTPUT

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

SQL_ID auyf8px9ywc6j, child number 0

WITH SNAP_RANGES AS (SELECT /*+ FULL(ST) */ SN.DBID ,SN.INSTANCE_NUMBER

,SN.STARTUP_TIME ,ST.STAT_ID ,ST.STAT_NAME ,MIN(SN.SNAP_ID) AS MIN_SNAP

,MAX(SN.SNAP_ID) AS MAX_SNAP ,MIN(CAST(BEGIN_INTERVAL_TIME AS DATE)) AS

MIN_DATE ,MAX(CAST(END_INTERVAL_TIME AS DATE)) AS MAX_DATE FROM

DBA_HIST_SNAPSHOT SN ,WRH$_STAT_NAME ST WHERE SN.BEGIN_INTERVAL_TIME >

TRUNC(SYSDATE) - 7 AND SN.END_INTERVAL_TIME < TRUNC(SYSDATE) AND

SN.DBID = ST.DBID AND ST.STAT_NAME IN ('DB time', 'DB CPU') GROUP BY

SN.DBID,SN.INSTANCE_NUMBER,SN.STARTUP_TIME,ST.STAT_ID,ST.STAT_NAME )

,DELTA_DATA AS (SELECT SR.DBID ,SR.INSTANCE_NUMBER ,SR.STAT_NAME ,CASE

WHEN SR.STARTUP_TIME BETWEEN SR.MIN_DATE AND SR.MAX_DATE THEN TM1.VALUE

+ (TM2.VALUE - TM1.VALUE) ELSE (TM2.VALUE - TM1.VALUE) END AS

DELTA_TIME FROM WRH$_SYS_TIME_MODEL TM1 ,WRH$_SYS_TIME_MODEL TM2

,SNAP_RANGES SR WHERE TM1.DBID = SR.DBID AND TM1.INSTANCE_NUMBER =

SR.INSTANCE_NUMBER AND TM1.SNAP_ID = SR.MIN_SNAP AND TM1.STAT_ID =

SR.STAT_ID AND TM2.DBID = SR.DBID AND TM2.

NOTE: cannot fetch plan for SQL_ID: auyf8px9ywc6j, CHILD_NUMBER: 0

Please verify value of SQL_ID and CHILD_NUMBER;

It could also be that the plan is no longer in cursor cache (check v$sql_plan)

22 rows selected.

And this is very likely because the statement is still parsing, and all the time is due to the Dynamic Sampling. But because the plan is not there yet, I cannot check it in the DBMS_XPLAN.DISPLAY_CURSOR.

I decided then to trace it with those two statements:

SQL> alter system set events 'sql_trace [sql:auyf8px9ywc6j]';

SQL> alter system set events 'trace[rdbms.SQL_Optimizer.*][sql:auyf8px9ywc6j]';

SQL> alter system set events 'sql_trace [sql:auyf8px9ywc6j]';

SQL> alter system set events 'trace[rdbms.SQL_Optimizer.*][sql:auyf8px9ywc6j]';

At the next execution I see indeed the Adaptive Dynamic Sampling in the trace file, the errror due to the exhausted CPU in the resource plan, and the directives that caused the Adaptive Dynamic Sampling:

=======================================
SPD: BEGIN context at query block level
=======================================
Query Block SEL$3877D5D0 (#3)
Applicable DS directives:
   dirid = 17707367266596005344, state = 5, flags = 1, loc = 1 {CJ(8694)[1, 2]}
   dirid = 17748238338555778238, state = 5, flags = 1, loc = 4 {(8694)[2, 3, 4]; (8460)[2, 3]}
   dirid = 10027833930063681981, state = 1, flags = 5, loc = 4 {(8694)[2, 3, 4]; (8460)[2, 3]; (8436)[1, 5]; (8436)[1, 5]}
Checking valid directives for the query block
  SPD: Directive valid: dirid = 17748238338555778238, state = 5, flags = 1, loc = 4 {(8694)[2, 3, 4]; (8460)[2, 3]}
  SPD: Return code in qosdDSDirSetup: EXISTS, estType = GROUP_BY
  SPD: Return code in qosdDSDirSetup: NODIR, estType = HAVING
  SPD: Return code in qosdDSDirSetup: NODIR, estType = QUERY_BLOCK

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

SPD: BEGIN context at query block level

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

Query Block SEL$3877D5D0 (#3)

Applicable DS directives:

dirid = 17707367266596005344, state = 5, flags = 1, loc = 1 {CJ(8694)[1, 2]}

dirid = 17748238338555778238, state = 5, flags = 1, loc = 4 {(8694)[2, 3, 4]; (8460)[2, 3]}

dirid = 10027833930063681981, state = 1, flags = 5, loc = 4 {(8694)[2, 3, 4]; (8460)[2, 3]; (8436)[1, 5]; (8436)[1, 5]}

Checking valid directives for the query block

SPD: Directive valid: dirid = 17748238338555778238, state = 5, flags = 1, loc = 4 {(8694)[2, 3, 4]; (8460)[2, 3]}

SPD: Return code in qosdDSDirSetup: EXISTS, estType = GROUP_BY

SPD: Return code in qosdDSDirSetup: NODIR, estType = HAVING

SPD: Return code in qosdDSDirSetup: NODIR, estType = QUERY_BLOCK

PARSING IN CURSOR #139834781881608 len=1106 dep=4 uid=0 oct=3 lid=0 tim=3349661181783 hv=4280474888 ad='95770310' sqlid='8w3h8fvzk5r88'
SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) */ SUM(C1) FROM (SELECT /*+ qb_name("innerQuery")  */ 1 AS C1 FROM (SELECT /*+ FULL ("ST") */ "WRM$_SNAPSHOT"."DBID" "DBID","WRM$_SNAPSHOT"."INSTANCE_NUMBER" "INSTANCE_NUMBER","WRM$_SNAPSHOT"."STARTUP_TIME" "STARTUP_TIME","ST"."STAT_ID" "STAT_ID","ST"."STAT_NAME" "STAT_NAME",MIN("WRM$_SNAPSHOT"."SNAP_ID") "MIN_SNAP",MAX("WRM$_SNAPSHOT"."SNAP_ID") "MAX_SNAP",MIN(CAST("WRM$_SNAPSHOT"."BEGIN_INTERVAL_TIME" AS DATE)) "MIN_DATE",MAX(CAST("WRM$_SNAPSHOT"."END_INTERVAL_TIME" AS DATE)) "MAX_DATE" FROM SYS."WRM$_SNAPSHOT" "WRM$_SNAPSHOT","WRH$_STAT_NAME" "ST" WHERE "WRM$_SNAPSHOT"."DBID"="ST"."DBID" AND ("ST"."STAT_NAME"='DB CPU' OR "ST"."STAT_NAME"='DB time') AND "WRM$_SNAPSHOT"."STATUS"=0 AND "WRM$_SNAPSHOT"."BEGIN_INTERVAL_TIME">TRUNC(SYSDATE@!)-7 AND "WRM$_SNAPSHOT"."END_INTERVAL_TIME"<TRUNC(SYSDATE@!) GROUP BY "WRM$_SNAPSHOT"."DBID","WRM$_SNAPSHOT"."INSTANCE_NUMBER","WRM$_SNAPSHOT"."STARTUP_TIME","ST"."STAT_ID","ST"."STAT_NAME") "VW_DIS_1") innerQuery
END OF STMT
...
>> Query Blk Card adjusted from 3.000000 to 2.000000 due to adaptive dynamic sampling

PARSING IN CURSOR #139834781881608 len=1106 dep=4 uid=0 oct=3 lid=0 tim=3349661181783 hv=4280474888 ad='95770310' sqlid='8w3h8fvzk5r88'

SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) */ SUM(C1) FROM (SELECT /*+ qb_name("innerQuery") */ 1 AS C1 FROM (SELECT /*+ FULL ("ST") */ "WRM$_SNAPSHOT"."DBID" "DBID","WRM$_SNAPSHOT"."INSTANCE_NUMBER" "INSTANCE_NUMBER","WRM$_SNAPSHOT"."STARTUP_TIME" "STARTUP_TIME","ST"."STAT_ID" "STAT_ID","ST"."STAT_NAME" "STAT_NAME",MIN("WRM$_SNAPSHOT"."SNAP_ID") "MIN_SNAP",MAX("WRM$_SNAPSHOT"."SNAP_ID") "MAX_SNAP",MIN(CAST("WRM$_SNAPSHOT"."BEGIN_INTERVAL_TIME" AS DATE)) "MIN_DATE",MAX(CAST("WRM$_SNAPSHOT"."END_INTERVAL_TIME" AS DATE)) "MAX_DATE" FROM SYS."WRM$_SNAPSHOT" "WRM$_SNAPSHOT","WRH$_STAT_NAME" "ST" WHERE "WRM$_SNAPSHOT"."DBID"="ST"."DBID" AND ("ST"."STAT_NAME"='DB CPU' OR "ST"."STAT_NAME"='DB time') AND "WRM$_SNAPSHOT"."STATUS"=0 AND "WRM$_SNAPSHOT"."BEGIN_INTERVAL_TIME">TRUNC(SYSDATE@!)-7 AND "WRM$_SNAPSHOT"."END_INTERVAL_TIME"<TRUNC(SYSDATE@!) GROUP BY "WRM$_SNAPSHOT"."DBID","WRM$_SNAPSHOT"."INSTANCE_NUMBER","WRM$_SNAPSHOT"."STARTUP_TIME","ST"."STAT_ID","ST"."STAT_NAME") "VW_DIS_1") innerQuery

END OF STMT

...

>> Query Blk Card adjusted from 3.000000 to 2.000000 due to adaptive dynamic sampling

*** KEWUXS - encountered error: (ORA-12751: violation du temps UC ou des règles relatives au temps d'exécution
ORA-06512: à "SYS.DBMS_FEATURE_AWR", ligne 14
ORA-06512: à "SYS.DBMS_FEATURE_AWR", ligne 92
ORA-06512: à ligne 1
ORA-06512: à "SYS.DBMS_SQL", ligne 1707
ORA-06512: à "SYS.DBMS_FEATURE_USAGE_INTERNAL", ligne 312
ORA-06512: à "SYS.DBMS_FEATURE_USAGE_INTERNAL", ligne 522
ORA-06512: à "SYS.DBMS_FEATURE_USAGE_INTERNAL", ligne 694
ORA-06512: à "SYS.DBMS_FEATURE_USAGE_INTERNAL", ligne 791
ORA-06512: à ligne 1
)

*** KEWUXS - encountered error: (ORA-12751: violation du temps UC ou des règles relatives au temps d'exécution

ORA-06512: à "SYS.DBMS_FEATURE_AWR", ligne 14

ORA-06512: à "SYS.DBMS_FEATURE_AWR", ligne 92

ORA-06512: à ligne 1

ORA-06512: à "SYS.DBMS_SQL", ligne 1707

ORA-06512: à "SYS.DBMS_FEATURE_USAGE_INTERNAL", ligne 312

ORA-06512: à "SYS.DBMS_FEATURE_USAGE_INTERNAL", ligne 522

ORA-06512: à "SYS.DBMS_FEATURE_USAGE_INTERNAL", ligne 694

ORA-06512: à "SYS.DBMS_FEATURE_USAGE_INTERNAL", ligne 791

ORA-06512: à ligne 1

)

So, there are some SQL Plan Directives that force the CBO to run ADS for this query.

SQL> select TYPE, ENABLED, STATE, AUTO_DROP, REASON, CREATED, LAST_MODIFIED, LAST_USED from dba_sql_plan_directives where directive_id in (10027833930063681981, 17707367266596005344, 17748238338555778238);

TYPE             ENA STATE      AUT REASON                               CREATED
---------------- --- ---------- --- ------------------------------------ ---------------------------------------------------------------------------
LAST_MODIFIED                                                               LAST_USED
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
DYNAMIC_SAMPLING YES USABLE     YES GROUP BY CARDINALITY MISESTIMATE     03-JUN-16 02.10.41.000000 PM
03-JUN-16 04.14.32.000000 PM

DYNAMIC_SAMPLING YES USABLE     YES SINGLE TABLE CARDINALITY MISESTIMATE 27-MAR-16 09.01.20.000000 AM
17-APR-16 09.13.01.000000 AM                                                17-APR-16 09.13.01.000000000 AM

DYNAMIC_SAMPLING YES USABLE     YES GROUP BY CARDINALITY MISESTIMATE     13-FEB-16 06.07.36.000000 AM
27-FEB-16 06.03.09.000000 AM                                                03-JUN-16 02.10.41.000000000 PM

SQL> select TYPE, ENABLED, STATE, AUTO_DROP, REASON, CREATED, LAST_MODIFIED, LAST_USED from dba_sql_plan_directives where directive_id in (10027833930063681981, 17707367266596005344, 17748238338555778238);

TYPE ENA STATE AUT REASON CREATED

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

LAST_MODIFIED LAST_USED

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

DYNAMIC_SAMPLING YES USABLE YES GROUP BY CARDINALITY MISESTIMATE 03-JUN-16 02.10.41.000000 PM

03-JUN-16 04.14.32.000000 PM

DYNAMIC_SAMPLING YES USABLE YES SINGLE TABLE CARDINALITY MISESTIMATE 27-MAR-16 09.01.20.000000 AM

17-APR-16 09.13.01.000000 AM 17-APR-16 09.13.01.000000000 AM

DYNAMIC_SAMPLING YES USABLE YES GROUP BY CARDINALITY MISESTIMATE 13-FEB-16 06.07.36.000000 AM

27-FEB-16 06.03.09.000000 AM 03-JUN-16 02.10.41.000000000 PM

This query touches three tables, so instead of relying on the DIRECTIVE_IDs, it’s better to get the directives by object name:

SQL> r
  1  select distinct d.directive_id, TYPE, ENABLED, STATE, AUTO_DROP, REASON, CREATED, LAST_MODIFIED
  2  from dba_sql_plan_directives d join dba_sql_plan_dir_objects o on
  3*     (d.directive_id=o.directive_id) where o.owner='SYS' and o.object_name in ('WRH$_SYS_TIME_MODEL','WRH$_STAT_NAME','WRM$_SNAPSHOT')

DIRECTIVE_ID TYPE             ENA STATE      AUT REASON                               CREATED
------------ ---------------- --- ---------- --- ------------------------------------ ---------------------------------------------------------------------------
LAST_MODIFIED
---------------------------------------------------------------------------
  8.8578E+18 DYNAMIC_SAMPLING YES USABLE     YES JOIN CARDINALITY MISESTIMATE         14-FEB-16 08.11.29.000000 AM
06-JUN-16 01.57.35.000000 PM

  1.7748E+19 DYNAMIC_SAMPLING YES USABLE     YES GROUP BY CARDINALITY MISESTIMATE     19-MAR-16 02.15.17.000000 AM
06-JUN-16 01.57.35.000000 PM

  1.7170E+19 DYNAMIC_SAMPLING YES USABLE     YES JOIN CARDINALITY MISESTIMATE         14-FEB-16 08.11.29.000000 AM
06-JUN-16 01.57.35.000000 PM

  1.7707E+19 DYNAMIC_SAMPLING YES USABLE     YES SINGLE TABLE CARDINALITY MISESTIMATE 13-MAR-16 08.04.38.000000 AM
06-JUN-16 01.57.35.000000 PM

SQL> r

1 select distinct d.directive_id, TYPE, ENABLED, STATE, AUTO_DROP, REASON, CREATED, LAST_MODIFIED

2 from dba_sql_plan_directives d join dba_sql_plan_dir_objects o on

3* (d.directive_id=o.directive_id) where o.owner='SYS' and o.object_name in ('WRH$_SYS_TIME_MODEL','WRH$_STAT_NAME','WRM$_SNAPSHOT')

DIRECTIVE_ID TYPE ENA STATE AUT REASON CREATED

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

LAST_MODIFIED

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

8.8578E+18 DYNAMIC_SAMPLING YES USABLE YES JOIN CARDINALITY MISESTIMATE 14-FEB-16 08.11.29.000000 AM

06-JUN-16 01.57.35.000000 PM

1.7748E+19 DYNAMIC_SAMPLING YES USABLE YES GROUP BY CARDINALITY MISESTIMATE 19-MAR-16 02.15.17.000000 AM

06-JUN-16 01.57.35.000000 PM

1.7170E+19 DYNAMIC_SAMPLING YES USABLE YES JOIN CARDINALITY MISESTIMATE 14-FEB-16 08.11.29.000000 AM

06-JUN-16 01.57.35.000000 PM

1.7707E+19 DYNAMIC_SAMPLING YES USABLE YES SINGLE TABLE CARDINALITY MISESTIMATE 13-MAR-16 08.04.38.000000 AM

06-JUN-16 01.57.35.000000 PM

Solution

At this point, the solution is the same already pointed out in one of my previous blog posts: disable the directives individually!

BEGIN
  FOR rec in (select d.directive_id as did 
    from dba_sql_plan_directives d join dba_sql_plan_dir_objects o on
    (d.directive_id=o.directive_id) where o.owner='SYS'
      and o.object_name in ('WRH$_SYS_TIME_MODEL','WRH$_STAT_NAME','WRM$_SNAPSHOT'))
  LOOP
    DBMS_SPD.ALTER_SQL_PLAN_DIRECTIVE ( rec.did, 'ENABLED','NO');
    DBMS_SPD.ALTER_SQL_PLAN_DIRECTIVE ( rec.did, 'AUTO_DROP','NO');
  END LOOP;
END;
/

BEGIN

FOR rec in (select d.directive_id as did

from dba_sql_plan_directives d join dba_sql_plan_dir_objects o on

(d.directive_id=o.directive_id) where o.owner='SYS'

and o.object_name in ('WRH$_SYS_TIME_MODEL','WRH$_STAT_NAME','WRM$_SNAPSHOT'))

LOOP

DBMS_SPD.ALTER_SQL_PLAN_DIRECTIVE ( rec.did, 'ENABLED','NO');

DBMS_SPD.ALTER_SQL_PLAN_DIRECTIVE ( rec.did, 'AUTO_DROP','NO');

END LOOP;

END;

This very same PL/SQL block must be run on ALL the 12c databases affected by this Adaptive Dynamic Sampling problem on the sql_id auyf8px9ywc6j.

If you have just migrated the database to 12c, it would make even more sense to programmatically “inject” the disabled SQL Plan Directives into every freshly created or upgraded 12c database (until Oracle releases a patch for this non-bug).

-- export from a source where the directives exist and have been disabled
SET SERVEROUTPUT ON
DECLARE
  my_list  DBMS_SPD.OBJECTTAB := DBMS_SPD.ObjectTab();
  dir_cnt  NUMBER;
BEGIN
  DBMS_SPD.CREATE_STGTAB_DIRECTIVE  (table_name => 'AUYF8PX9YWC6J_DIRECTIVES', table_owner=> 'SYSTEM' );
  my_list.extend(3);
 
  -- TAB table
  my_list(1).owner := 'SYS';
  my_list(1).object_name := 'WRH$_SYS_TIME_MODEL';
  my_list(1).object_type := 'TABLE';
  my_list(2).owner := 'SYS';
  my_list(2).object_name := 'WRH$_STAT_NAME';
  my_list(2).object_type := 'TABLE';
  my_list(3).owner := 'SYS';
  my_list(3).object_name := 'WRM$_SNAPSHOT';
  my_list(3).object_type := 'TABLE';

  dir_cnt := DBMS_SPD.PACK_STGTAB_DIRECTIVE(table_name => 'AUYF8PX9YWC6J_DIRECTIVES', table_owner=> 'SYSTEM', obj_list => my_list);
   DBMS_OUTPUT.PUT_LINE('dir_cnt = ' || dir_cnt);
END;
/

expdp directory=data_pump_dir dumpfile=AUYF8PX9YWC6J_DIRECTIVES.dmp logfile=expdp_AUYF8PX9YWC6J_DIRECTIVES.log tables=system.AUYF8PX9YWC6J_DIRECTIVES

-- import into the freshly upgraded/created 12c database
impdp directory=data_pump_dir dumpfile=AUYF8PX9YWC6J_DIRECTIVES.dmp logfile=impdp_AUYF8PX9YWC6J_DIRECTIVES.log

SELECT DBMS_SPD.UNPACK_STGTAB_DIRECTIVE(table_name => 'AUYF8PX9YWC6J_DIRECTIVES', table_owner=> 'SYSTEM') FROM DUAL;

-- export from a source where the directives exist and have been disabled

SET SERVEROUTPUT ON

DECLARE

my_list DBMS_SPD.OBJECTTAB := DBMS_SPD.ObjectTab();

dir_cnt NUMBER;

BEGIN

DBMS_SPD.CREATE_STGTAB_DIRECTIVE (table_name => 'AUYF8PX9YWC6J_DIRECTIVES', table_owner=> 'SYSTEM' );

my_list.extend(3);

-- TAB table

my_list(1).owner := 'SYS';

my_list(1).object_name := 'WRH$_SYS_TIME_MODEL';

my_list(1).object_type := 'TABLE';

my_list(2).owner := 'SYS';

my_list(2).object_name := 'WRH$_STAT_NAME';

my_list(2).object_type := 'TABLE';

my_list(3).owner := 'SYS';

my_list(3).object_name := 'WRM$_SNAPSHOT';

my_list(3).object_type := 'TABLE';

dir_cnt := DBMS_SPD.PACK_STGTAB_DIRECTIVE(table_name => 'AUYF8PX9YWC6J_DIRECTIVES', table_owner=> 'SYSTEM', obj_list => my_list);

DBMS_OUTPUT.PUT_LINE('dir_cnt = ' || dir_cnt);

END;

expdp directory=data_pump_dir dumpfile=AUYF8PX9YWC6J_DIRECTIVES.dmp logfile=expdp_AUYF8PX9YWC6J_DIRECTIVES.log tables=system.AUYF8PX9YWC6J_DIRECTIVES

-- import into the freshly upgraded/created 12c database

impdp directory=data_pump_dir dumpfile=AUYF8PX9YWC6J_DIRECTIVES.dmp logfile=impdp_AUYF8PX9YWC6J_DIRECTIVES.log

SELECT DBMS_SPD.UNPACK_STGTAB_DIRECTIVE(table_name => 'AUYF8PX9YWC6J_DIRECTIVES', table_owner=> 'SYSTEM') FROM DUAL;

It comes without saying that the next execution has been very quick, consuming almost no CPU and without using ADS.

HTH

—

Ludovico

How cold incremental recovery saved me once

Posted on March 15, 2016 by Ludovico

UPDATE: In the original version I was missing a few keywords: “incremental level 0” for the base backup and “resetlogs” at the database open. Thanks Gregorz for your comments.

Sorry for this “memories” post, but the technical solution at the end is worth the read, I hope 😉

Back in 2010, I was in charge of a quite complex project and faced some difficulties that led me to recover a database in a different manner. A few years have passed, but I used again the same procedure many times with full satisfaction… I think it’s worth to publish it now.

But first, let me introduce the project details and the problem.

Scope of the project

Transport a >1TB RAC database from AIX 5 on P6 to AIX 6 on P7, from a third-party datacenter in southern Italy to our main datacenter in northern Italy.
The Database featured >1000 datafiles and a huge table (800GB) partitioned by range and sub-partitioned by list (or the opposite, can’t remember).

Challenges

For budget containement, the project owner asked to avoid the use of HACMP (and thus, avoid the use of shared JFS2). I decided then to take the risk and migrate from JFS2 to ASM.

In order to avoid a few platform-related ASM bugs, I also had to upgrade from Oracle 10.2.0.3 to Oracle 10.2.0.4.

Constraints

I had no access to the source database that was 800km far from our datacenter, and I was granted only to ask for RMAN backups.

The total time of service disruption accepted was quite short (<30 minutes) considering the size and the distance of the database, and there was no direct connectivity between the sites (for political reasons).

Globally, the network throughput for sharing files over ftp was very poor.

First solution

This kind of move was very common to me, and because I was not grated to ask for a temporary Data Guard configuration, the easy solution for me was to ask:

1 – one RMAN ONLINE full backup physically sent on disk

2 – many RMAN archive backups sent over network (via ftp)

Then, on my side, restore the full backup, recover the archives sent over time and, at the date X, ask a final archive backup, ask to close the db and send the online redo logs to do a complete recovery on my side, then startup open upgrade.

Problem

I did a first “dry run” open resetlogs in order to test the procedure and make it faster, and also asked to test the application pointing to the destination database.

The very bad surprise was that the source database was doing a huge amount of nologging inserts leading to monster index corruptions after the recovery on the destination database.

ORA-26040: Data block was loaded using the NOLOGGING option

1	ORA-26040: Data block was loaded using the NOLOGGING option

According to the current database maintainer, setting the force logging on the source database was NOT an option because the SAN was not able to cope with the high redo rates.

Solution

By knowing the Oracle recovery mechanisms, I have proposed to the remote maintainer to change the recovery strategy, despite this solution was not clearly stated in the Oracle documentation:

1 – Take a first online incremental backup from the begin scn of the base full backup (thank God block change tracking was in place) and send it physically over disk

2 – Take other smaller online incremental backups, send them over ftp and apply them on the destination with “noredo”

3 – At the date X, shutdown the source, mount it and take a last incremental in mount state

4 – recover noredo the last incremental and open resetlogs the database.

According to the documentation, the “cold incremental strategy” applies if you take “cold full backups”. But from a technical point of view, taking a cold incremental and recovering it on top of a fuzzy online backup this is 100% equivalent of taking a full consistent backup in mount state.
Because all the blocks are consistent to a specific SCN, there are no fuzzy datafiles: they are recovered from incremental taken from a mounted database! This allows to do incremental recovery and open the databases without applying any single archived log and by shutting down the database only once.

Technical steps

First, take a full ONLINE backup on the source:

-- SOURCE
SQL> alter database backup controlfile to '/tmp/source/ludo.cf' reuse;

Database altered.

SQL> exit
$ rman target /
RMAN> backup incremental level 0 database as compressed backupset format '/tmp/source/%U';

-- SOURCE

SQL> alter database backup controlfile to '/tmp/source/ludo.cf' reuse;

Database altered.

SQL> exit

$ rman target /

RMAN> backup incremental level 0 database as compressed backupset format '/tmp/source/%U';

# SOURCE
scp -rp /tmp/source/ destsrv:/tmp/dest/
ludo.cf              100% |*************************************| 40944 KB    00:00
...

# SOURCE

scp -rp /tmp/source/ destsrv:/tmp/dest/

ludo.cf 100% |*************************************| 40944 KB 00:00

...

Then restore it on the destination (with no recovery):

# DEST
RMAN> restore controlfile from '/tmp/ludo.cf';

Starting restore at 11-AUG-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1058 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=/.../control01.ctl
output file name=/.../control02.ctl
Finished restore at 11-AUG-15

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

RMAN> catalog start with '/tmp/dest/';
...
RMAN> run
2> {
3> set newname for database to '+DATA';
4>
5> restore database;
6> }
...
Finished restore at 11-AUG-15
RMAN>

# DEST

RMAN> restore controlfile from '/tmp/ludo.cf';

Starting restore at 11-AUG-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1058 device type=DISK

channel ORA_DISK_1: copied control file copy

output file name=/.../control01.ctl

output file name=/.../control02.ctl

Finished restore at 11-AUG-15

RMAN> alter database mount;

Statement processed

released channel: ORA_DISK_1

RMAN> catalog start with '/tmp/dest/';

...

RMAN> run

2> {

3> set newname for database to '+DATA';

5> restore database;

6> }

...

Finished restore at 11-AUG-15

RMAN>

Then, run a COLD incremental backup on the source:

-- SOURCE
SQL> shutdown immediate;
...
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
...
Database mounted.
SQL> exit
$ rman target /
RMAN>  BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 
2> CUMULATIVE DATABASE format '/tmp/source/incr%U';
...
Finished backup at 11-AUG-15
RMAN> exit
$ scp -rp /tmp/source/incr* destsrv:/tmp/dest/

-- SOURCE

SQL> shutdown immediate;

...

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

...

Database mounted.

SQL> exit

$ rman target /

RMAN> BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1

2> CUMULATIVE DATABASE format '/tmp/source/incr%U';

...

Finished backup at 11-AUG-15

RMAN> exit

$ scp -rp /tmp/source/incr* destsrv:/tmp/dest/

And run the incremental recovery on the source (without redo):

# DEST
RMAN> catalog start with '/tmp/dest/incr';
...
RMAN> run {
2> recover database noredo;
3> }
...
channel ORA_DISK_1: starting incremental datafile backup set restore
...
Finished recover at 11-AUG-15
RMAN> exit
$ sqlplus / as sysdba
...
SQL> alter database disable block change tracking;
Database altered.
SQL> alter database flashback off;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> create restore point PREUPG guarantee flashback database;
Restore point created.
SQL> -- open resetlogs can be avoided if I copy the online redo logs
SQL> alter database open resetlogs upgrade;
Database altered.
...
-- run catupgrd here

# DEST

RMAN> catalog start with '/tmp/dest/incr';

...

RMAN> run {

2> recover database noredo;

3> }

...

channel ORA_DISK_1: starting incremental datafile backup set restore

...

Finished recover at 11-AUG-15

RMAN> exit

$ sqlplus / as sysdba

...

SQL> alter database disable block change tracking;

Database altered.

SQL> alter database flashback off;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> create restore point PREUPG guarantee flashback database;

Restore point created.

SQL> -- open resetlogs can be avoided if I copy the online redo logs

SQL> alter database open resetlogs upgrade;

Database altered.

...

-- run catupgrd here

That’s all!

This solution gave me the opportunity to move physically the whole >1TB nologging database from one region to another one with a minimal service disruption and without touching at all the source database.

I used it many times later on, even for bigger databases and on several platforms (yes, also Windows, sigh), it works like a charm.

HTH

—

Ludovico

Getting the DBID and Incarnation from the RMAN Catalog

Posted on February 15, 2016 by Ludovico

Using the RMAN catalog is an option. There is a long discussion between DBAs on whether should you use the catalog or not.

But because I like (a lot) the RMAN catalog and I generally use it, I assume that most of you do it 😉

When you want to restore from the RMAN catalog, you need to get the DBID of the database you want to restore and, sometimes, also the incarnation key.

The DBID is used to identify the database you want to restore. The DBID is different for every newly created / duplicated database, but beware that if you duplicate your database manually (using restore/recover), you actually need to change your DBID using the nid tool, otherwise you will end up by having more than one database registered in the catalog with the very same DBID. This is evil! The DB_NAME is also something that you may want to make sure is unique within your database farm.

The Incarnation Key changes whenever you do an “open resetlogs”, following for example a flashback database, an incomplete recovery, or just a “open resetlogs” without any specific need.

In the image, you can see that you may want to restore to a point in time after the open resetlogs (blue incarnation) or before it (red incarnation). Depending on which one you need to restore, you may need to use the command RESET DATABASE TO INCARNATION.

https://docs.oracle.com/database/121/RCMRF/rcmsynta2007.htm#RCMRF148

If you have a dynamic and big environment, you probably script your restores procedures, that’s why getting the DBID and incarnation key using the RMAN commands may be more complex than just querying the catalog using sqlplus.

How do I get the history of my database incarnations?

You can get it easily for all your databases using the handy hierarchical queries on the RMAN catalog (db names and ids are obfuscated for obvious reasons):

SQL> SELECT lpad(' ',2*(level-1))
  || TO_CHAR(DBINC_KEY) AS DBINC_KEY,
  db_key,
  db_name,
  TO_CHAR(reset_time,'YYYY-MM-DD HH24:MI:SS'),
  dbinc_status
FROM rman.dbinc
  START WITH PARENT_DBINC_KEY IS NULL
  CONNECT BY prior DBINC_KEY   = PARENT_DBINC_KEY ;

DBINC_KEY                     DB_KEY DB_NAME    TO_CHAR(RESET_TIME, DBINC_ST
------------------------- ---------- ---------- ------------------- --------
356247416                  356247380 A9EE272A   2011-09-24 18:22:58 PARENT
  356247387                356247380 A9EE272A   2012-10-24 08:41:41 PARENT
    1149458631             356247380 A9EE272A   2014-10-10 08:30:57 CURRENT
360319357                  360319322 F5FD787F   2011-10-14 15:39:19 PARENT
  360319323                360319322 F5FD787F   2012-11-08 18:57:26 PARENT
    547928008              360319322 F5FD787F   2013-09-10 10:57:44 PARENT
      576592237            360319322 F5FD787F   2013-11-20 14:54:05 ORPHAN
      576613820            360319322 F5FD787F   2013-11-20 15:57:03 ORPHAN
      584503796            360319322 F5FD787F   2013-11-27 13:57:53 CURRENT
364099232                  364099231 25E64A7F   2012-11-20 08:01:49 PARENT
  415031968                364099231 25E64A7F   2013-02-15 12:16:15 PARENT
    456099512              364099231 25E64A7F   2013-05-03 12:19:52 CURRENT
366065362                  366065336 3AE45141   2011-09-24 18:22:58 PARENT
  366065337                366065336 3AE45141   2012-11-26 17:14:14 CURRENT
394067322                  394067321 C34FFA7E   2013-01-10 17:18:11 CURRENT
402469086                  402469073 D164DDB8   2011-09-24 18:22:58 PARENT
  402469074                402469073 D164DDB8   2013-01-29 11:20:19 CURRENT
410147332                  410147283 27984513   2011-09-24 18:22:58 PARENT
  410147284                410147283 27984513   2013-02-08 11:12:38 CURRENT
...
...

SQL> SELECT lpad(' ',2*(level-1))

|| TO_CHAR(DBINC_KEY) AS DBINC_KEY,

db_key,

db_name,

TO_CHAR(reset_time,'YYYY-MM-DD HH24:MI:SS'),

dbinc_status

FROM rman.dbinc

START WITH PARENT_DBINC_KEY IS NULL

CONNECT BY prior DBINC_KEY = PARENT_DBINC_KEY ;

DBINC_KEY DB_KEY DB_NAME TO_CHAR(RESET_TIME, DBINC_ST

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

356247416 356247380 A9EE272A 2011-09-24 18:22:58 PARENT

356247387 356247380 A9EE272A 2012-10-24 08:41:41 PARENT

1149458631 356247380 A9EE272A 2014-10-10 08:30:57 CURRENT

360319357 360319322 F5FD787F 2011-10-14 15:39:19 PARENT

360319323 360319322 F5FD787F 2012-11-08 18:57:26 PARENT

547928008 360319322 F5FD787F 2013-09-10 10:57:44 PARENT

576592237 360319322 F5FD787F 2013-11-20 14:54:05 ORPHAN

576613820 360319322 F5FD787F 2013-11-20 15:57:03 ORPHAN

584503796 360319322 F5FD787F 2013-11-27 13:57:53 CURRENT

364099232 364099231 25E64A7F 2012-11-20 08:01:49 PARENT

415031968 364099231 25E64A7F 2013-02-15 12:16:15 PARENT

456099512 364099231 25E64A7F 2013-05-03 12:19:52 CURRENT

366065362 366065336 3AE45141 2011-09-24 18:22:58 PARENT

366065337 366065336 3AE45141 2012-11-26 17:14:14 CURRENT

394067322 394067321 C34FFA7E 2013-01-10 17:18:11 CURRENT

402469086 402469073 D164DDB8 2011-09-24 18:22:58 PARENT

402469074 402469073 D164DDB8 2013-01-29 11:20:19 CURRENT

410147332 410147283 27984513 2011-09-24 18:22:58 PARENT

410147284 410147283 27984513 2013-02-08 11:12:38 CURRENT

...

What about getting the correct DBID/DBINC_KEY pair for a specific database/time?

You can get the time windows for each incarnation using the lead() analytical function:

SQL> WITH dbids AS
  (SELECT TO_CHAR(dbinc.DBINC_KEY) AS DBINC_KEY,
    dbinc.db_key,
    dbinc.db_name,
    dbinc.reset_time,
    dbinc.dbinc_status,
    db.db_id
  FROM rman.dbinc dbinc
  JOIN rman.db db
  ON ( 
  dbinc.db_key   =db.db_key)
  )
select * from (
SELECT DBINC_KEY,
  db_name,
  db_id,
  reset_time,
  nvl(lead (reset_time) over (partition BY db_name order by reset_time),sysdate) AS next_reset
FROM dbids
)
ORDER BY db_name ,
  reset_time ;  

DBINC_KEY                 DBNAME          DB_ID RESET_TIME          NEXTRESET
------------------------- ---------- ---------- ------------------- -------------------
1173852671                1DF63C30   2507085371 2014-07-07 05:38:47 2015-01-16 07:29:01
1173852635                1DF63C30   2507085371 2015-01-16 07:29:01 2015-02-27 16:25:13
1244346785                1DF63C30   2531796824 2015-02-27 16:25:13 2015-02-27 16:25:13
1281775847                1DF63C30   2541221473 2015-02-27 16:25:13 2015-02-27 16:25:13
1233975755                1DF63C30   2528008262 2015-02-27 16:25:13 2015-02-27 16:25:13
1220896058                1DF63C30   2523244390 2015-02-27 16:25:13 2015-03-16 16:06:00
1188550385                1DF63C30   2507085371 2015-03-16 16:06:00 2015-07-17 08:06:00
1220896028                1DF63C30   2523244390 2015-07-17 08:06:00 2015-09-10 11:23:53
1233975725                1DF63C30   2528008262 2015-09-10 11:23:53 2015-10-23 07:46:34
1244346755                1DF63C30   2531796824 2015-10-23 07:46:34 2016-02-08 09:44:03
1281775817                1DF63C30   2541221473 2016-02-08 09:44:03 2016-02-15 10:13:49
1201139592                1D0776F6   2025503263 2014-07-07 05:38:47 2015-05-04 17:08:50
1201139578                1D0776F6   2025503263 2015-05-04 17:08:50 2015-06-02 08:48:07
1213295265                1D0776F6   2029287211 2015-06-02 08:48:07 2015-06-02 08:48:07
1256000477                1D0776F6   2044568865 2015-06-02 08:48:07 2015-06-02 08:48:07
1235940868                1D0776F6   2037421528 2015-06-02 08:48:07 2015-06-17 12:14:38
1213295230                1D0776F6   2029287211 2015-06-17 12:14:38 2015-09-18 15:46:34
1235940852                1D0776F6   2037421528 2015-09-18 15:46:34 2015-12-08 09:08:52
1256000461                1D0776F6   2044568865 2015-12-08 09:08:52 2016-02-15 10:13:49
1173653066                2D828C2C   1656607497 2014-07-07 05:38:47 2015-01-15 14:06:04
1173653052                2D828C2C   1656607497 2015-01-15 14:06:04 2015-06-02 08:48:07
1247872446                2D828C2C   1682603029 2015-06-02 08:48:07 2015-06-02 08:48:07
1218354231                2D828C2C   1671898993 2015-06-02 08:48:07 2015-06-02 08:48:07
1278227063                2D828C2C   1690479985 2015-06-02 08:48:07 2015-06-02 08:48:07
1219084145                2D828C2C   1672155073 2015-06-02 08:48:07 2015-06-02 08:48:07
1228714578                2D828C2C   1675699280 2015-06-02 08:48:07 2015-06-02 08:48:07
1211451469                2D828C2C   1669565762 2015-06-02 08:48:07 2015-06-02 08:48:07
1235422982                2D828C2C   1678113471 2015-06-02 08:48:07 2015-06-02 08:48:07
1228713810                2D828C2C   1675697673 2015-06-02 08:48:07 2015-06-02 08:48:07
1240749487                2D828C2C   1680107003 2015-06-02 08:48:07 2015-06-02 08:48:07
1255743496                2D828C2C   1685361979 2015-06-02 08:48:07 2015-06-10 13:37:08
1211451453                2D828C2C   1669565762 2015-06-10 13:37:08 2015-07-06 13:44:20
1218354215                2D828C2C   1671898993 2015-07-06 13:44:20 2015-07-09 12:52:19
1219084129                2D828C2C   1672155073 2015-07-09 12:52:19 2015-08-19 12:55:40
1228713794                2D828C2C   1675697673 2015-08-19 12:55:40 2015-08-19 13:22:27
1228714562                2D828C2C   1675699280 2015-08-19 13:22:27 2015-09-16 11:58:58
1235422966                2D828C2C   1678113471 2015-09-16 11:58:58 2015-10-08 13:44:29
1240749471                2D828C2C   1680107003 2015-10-08 13:44:29 2015-11-06 11:04:55
1247872430                2D828C2C   1682603029 2015-11-06 11:04:55 2015-12-07 09:27:27
1255743480                2D828C2C   1685361979 2015-12-07 09:27:27 2016-02-04 15:07:29
1278227047                2D828C2C   1690479985 2016-02-04 15:07:29 2016-02-15 10:13:49

SQL> WITH dbids AS

(SELECT TO_CHAR(dbinc.DBINC_KEY) AS DBINC_KEY,

dbinc.db_key,

dbinc.db_name,

dbinc.reset_time,

dbinc.dbinc_status,

db.db_id

FROM rman.dbinc dbinc

JOIN rman.db db

ON (

dbinc.db_key =db.db_key)

)

select * from (

SELECT DBINC_KEY,

db_name,

db_id,

reset_time,

nvl(lead (reset_time) over (partition BY db_name order by reset_time),sysdate) AS next_reset

FROM dbids

)

ORDER BY db_name ,

reset_time ;

DBINC_KEY DBNAME DB_ID RESET_TIME NEXTRESET

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

1173852671 1DF63C30 2507085371 2014-07-07 05:38:47 2015-01-16 07:29:01

1173852635 1DF63C30 2507085371 2015-01-16 07:29:01 2015-02-27 16:25:13

1244346785 1DF63C30 2531796824 2015-02-27 16:25:13 2015-02-27 16:25:13

1281775847 1DF63C30 2541221473 2015-02-27 16:25:13 2015-02-27 16:25:13

1233975755 1DF63C30 2528008262 2015-02-27 16:25:13 2015-02-27 16:25:13

1220896058 1DF63C30 2523244390 2015-02-27 16:25:13 2015-03-16 16:06:00

1188550385 1DF63C30 2507085371 2015-03-16 16:06:00 2015-07-17 08:06:00

1220896028 1DF63C30 2523244390 2015-07-17 08:06:00 2015-09-10 11:23:53

1233975725 1DF63C30 2528008262 2015-09-10 11:23:53 2015-10-23 07:46:34

1244346755 1DF63C30 2531796824 2015-10-23 07:46:34 2016-02-08 09:44:03

1281775817 1DF63C30 2541221473 2016-02-08 09:44:03 2016-02-15 10:13:49

1201139592 1D0776F6 2025503263 2014-07-07 05:38:47 2015-05-04 17:08:50

1201139578 1D0776F6 2025503263 2015-05-04 17:08:50 2015-06-02 08:48:07

1213295265 1D0776F6 2029287211 2015-06-02 08:48:07 2015-06-02 08:48:07

1256000477 1D0776F6 2044568865 2015-06-02 08:48:07 2015-06-02 08:48:07

1235940868 1D0776F6 2037421528 2015-06-02 08:48:07 2015-06-17 12:14:38

1213295230 1D0776F6 2029287211 2015-06-17 12:14:38 2015-09-18 15:46:34

1235940852 1D0776F6 2037421528 2015-09-18 15:46:34 2015-12-08 09:08:52

1256000461 1D0776F6 2044568865 2015-12-08 09:08:52 2016-02-15 10:13:49

1173653066 2D828C2C 1656607497 2014-07-07 05:38:47 2015-01-15 14:06:04

1173653052 2D828C2C 1656607497 2015-01-15 14:06:04 2015-06-02 08:48:07

1247872446 2D828C2C 1682603029 2015-06-02 08:48:07 2015-06-02 08:48:07

1218354231 2D828C2C 1671898993 2015-06-02 08:48:07 2015-06-02 08:48:07

1278227063 2D828C2C 1690479985 2015-06-02 08:48:07 2015-06-02 08:48:07

1219084145 2D828C2C 1672155073 2015-06-02 08:48:07 2015-06-02 08:48:07

1228714578 2D828C2C 1675699280 2015-06-02 08:48:07 2015-06-02 08:48:07

1211451469 2D828C2C 1669565762 2015-06-02 08:48:07 2015-06-02 08:48:07

1235422982 2D828C2C 1678113471 2015-06-02 08:48:07 2015-06-02 08:48:07

1228713810 2D828C2C 1675697673 2015-06-02 08:48:07 2015-06-02 08:48:07

1240749487 2D828C2C 1680107003 2015-06-02 08:48:07 2015-06-02 08:48:07

1255743496 2D828C2C 1685361979 2015-06-02 08:48:07 2015-06-10 13:37:08

1211451453 2D828C2C 1669565762 2015-06-10 13:37:08 2015-07-06 13:44:20

1218354215 2D828C2C 1671898993 2015-07-06 13:44:20 2015-07-09 12:52:19

1219084129 2D828C2C 1672155073 2015-07-09 12:52:19 2015-08-19 12:55:40

1228713794 2D828C2C 1675697673 2015-08-19 12:55:40 2015-08-19 13:22:27

1228714562 2D828C2C 1675699280 2015-08-19 13:22:27 2015-09-16 11:58:58

1235422966 2D828C2C 1678113471 2015-09-16 11:58:58 2015-10-08 13:44:29

1240749471 2D828C2C 1680107003 2015-10-08 13:44:29 2015-11-06 11:04:55

1247872430 2D828C2C 1682603029 2015-11-06 11:04:55 2015-12-07 09:27:27

1255743480 2D828C2C 1685361979 2015-12-07 09:27:27 2016-02-04 15:07:29

1278227047 2D828C2C 1690479985 2016-02-04 15:07:29 2016-02-15 10:13:49

With this query, you can see that every incarnation has a reset time and a “next reset time”.

It’s easy then to get exactly what you need by adding a couple of where clauses:

SQL> WITH dbids AS
  (SELECT TO_CHAR(dbinc.DBINC_KEY) AS DBINC_KEY,
    dbinc.db_key,
    dbinc.db_name,
    dbinc.reset_time,
    dbinc.dbinc_status,
    db.db_id
  FROM rman.dbinc dbinc
  JOIN rman.db db
  ON ( --dbinc.dbinc_key=db.CURR_DBINC_KEY
    --AND
    dbinc.db_key =db.db_key)
  )
SELECT *
FROM
  (SELECT DBINC_KEY,
    db_name,
    db_id,
    reset_time,
    NVL(lead (reset_time) over (partition BY db_name order by reset_time),sysdate) AS next_reset
  FROM dbids
  )
WHERE TO_DATE ('2016-01-20 00:00:00','YYYY-MM-DD HH24:MI;SS') BETWEEN reset_time AND next_reset
AND db_name='1465419F'
ORDER BY db_name ,
  reset_time ; 

DBINC_KEY                 DB_NAME         DB_ID RESET_TIME          NEXT_RESET
------------------------- ---------- ---------- ------------------- -------------------
1256014297                1465419F   1048383773 2015-12-08 11:03:55 2016-02-08 07:55:05

SQL> WITH dbids AS

(SELECT TO_CHAR(dbinc.DBINC_KEY) AS DBINC_KEY,

dbinc.db_key,

dbinc.db_name,

dbinc.reset_time,

dbinc.dbinc_status,

db.db_id

FROM rman.dbinc dbinc

JOIN rman.db db

ON ( --dbinc.dbinc_key=db.CURR_DBINC_KEY

--AND

dbinc.db_key =db.db_key)

)

SELECT *

FROM

(SELECT DBINC_KEY,

db_name,

db_id,

reset_time,

NVL(lead (reset_time) over (partition BY db_name order by reset_time),sysdate) AS next_reset

FROM dbids

)

WHERE TO_DATE ('2016-01-20 00:00:00','YYYY-MM-DD HH24:MI;SS') BETWEEN reset_time AND next_reset

AND db_name='1465419F'

ORDER BY db_name ,

reset_time ;

DBINC_KEY DB_NAME DB_ID RESET_TIME NEXT_RESET

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

1256014297 1465419F 1048383773 2015-12-08 11:03:55 2016-02-08 07:55:05

So, if I need to restore the database 1465419F until time 2016-01-20 00:00:00, i need to set DBID=1048383773 and reset the database to incarnation 1256014297.

Cheers

—

Ludo

Recording of “Rapid Home Provisioning” webinar for the RAC SIG

Posted on January 14, 2016 by Ludovico

Yesterday I have presented the Oracle Rapid Home Provisioning technology for the RAC SIG, you can find the recording on YouTube:

Cheers

—

Ludo

Rapid Home Provisioning

Posted on December 4, 2015 by Ludovico

In a few days I will give a presentation at UKOUG Tech15 about Rapid Home Provisioning, it will be the first time that I present this session in public.

I usually like to give the link to the material to my audience, so here we go:

Slides:

Demo:

Enjoy
—
Ludovico

Oracle Active Data Guard and Global Data Services in Action!

Posted on December 4, 2015 by Ludovico

In a few days I will give a presentation at UKOUG Tech15 about Global Data Services, it will be the first time that I present this session.

I usually like to give the link to the material to my audience, so here we go:

Credits

I have to give special credits to my colleague Robert Bialek. I’ve got a late confirmation for this session and my slide deck was not ready at all, so I have used a big part of his original work. Most of the content included in the slides has been created by Robert, not me. (Thank you for your help! :-))

Slides

Demo recording

Demo script

clear

function db {
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin
}

function gsm {
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/gsmhome_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin
}

db

echo "#### CURRENT CONFIGURATION: CLASSIC DATA GUARD, 3 DATABASES ####"
dgmgrl -echo sys/password1@oltp_de <<EOF
show configuration
EOF
echo "next: GSM config"
read -p ""

gsm
echo "#### GSM CONFIGURATION ####"
echo "GDS COMMAND:
config"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
config
exit
EOF
echo "next: ADD GDSPOOL"
read -p ""


echo "#### ADD GDSPOOL ####"
echo "GDS COMMAND:
add gdspool -gdspool sales"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
add gdspool -gdspool sales
exit
EOF
echo "next: ADD BROKERCONFIG"
read -p ""


echo "#### ADD BROKERCONFIG ####"
echo "GDS COMMAND:
add brokerconfig -connect gsm02.trivadistraining.com:1521/oltp_de -pwd password1 -gdspool sales -region germany"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
add brokerconfig -connect gsm02.trivadistraining.com:1521/oltp_de -pwd password1 -gdspool sales -region germany
exit
EOF
echo "next: config databases"
read -p ""


echo "#### CONFIG DATABASES ####"
echo "GDS COMMAND:
config database"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
config database
exit
EOF
echo "next: modify databases"
read -p ""

echo "#### MODIFY DATABASES ####"
echo "GDS COMMAND: 
modify database -database oltp_ch1 -region switzerland
modify database -database oltp_ch2 -region switzerland
"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
modify database -database oltp_ch1 -region switzerland
modify database -database oltp_ch2 -region switzerland
config database
exit
EOF
echo "next: add service read/write"
read -p ""


echo "#### ADD SERVICE R/W ####"
echo "GDS COMMAND: 
add service -gdspool sales -service gsales_rw -role primary -preferred_all -failovertype SELECT -failovermethod BASIC -failoverretry 5 -failoverdelay 3 -locality LOCAL_ONLY -region_failover
start service -service gsales_rw
services"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
add service -gdspool sales -service gsales_rw -role primary -preferred_all -failovertype SELECT -failovermethod BASIC -failoverretry 5 -failoverdelay 3 -locality LOCAL_ONLY -region_failover
start service -service gsales_rw
services
exit
EOF
echo "next: ADD SERVICE R/O"
read -p ""

echo "#### ADD SERVICE R/O ####"
echo "GDS COMMAND: 
add service -gdspool sales -service gsales_ro -role PHYSICAL_STANDBY -failover_primary -lag 20 -preferred_all -failovertype SELECT -failovermethod BASIC -failoverretry 5 -failoverdelay 3 -locality LOCAL_ONLY -region_failover
start service -service gsales_ro
services
"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
add service -gdspool sales -service gsales_ro -role PHYSICAL_STANDBY -failover_primary -lag 20 -preferred_all -failovertype SELECT -failovermethod BASIC -failoverretry 5 -failoverdelay 3 -locality LOCAL_ONLY -region_failover
start service -service gsales_ro
services
exit
EOF
echo "next: stop apply ch1 (run cli_ro_short.sh first)"
read -p ""

db
echo "#### STOP APPLY DATA GUARD ON OLTP_CH1 ####"
dgmgrl -echo sys/password1@oltp_de <<EOF
edit database oltp_ch1 set state='apply-off';
EOF
echo "next: gds services"
read -p ""


gsm
echo "#### GDS SERVICES ####"
echo "GDS COMMAND: 
services
"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
services
exit
EOF
echo "next: stop apply ch2 (run cli_ro_short.sh first)"
read -p ""

db
echo "#### STOP APPLY DATA GUARD ON OLTP_CH2 ####"
dgmgrl -echo sys/password1@oltp_de <<EOF
edit database oltp_ch2 set state='apply-off';
EOF
echo "next: gds services"
read -p ""

gsm
echo "#### GDS SERVICES ####"
echo "GDS COMMAND: 
services
"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
services
exit
EOF
echo "next: gds services"
read -p ""

gsm
echo "#### GDS SERVICES ####"
echo "GDS COMMAND: 
services
"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
services
exit
EOF
echo "next: start apply ch1  and ch2"
read -p ""

db
echo "#### START APPLY DATA GUARD ON OLTP_CH1 and OLTP_CH2 ####"
dgmgrl -echo sys/password1@oltp_de <<EOF
edit database oltp_ch1 set state='apply-on';
EOF
echo "sleeping 5"
sleep 5
dgmgrl -echo sys/password1@oltp_de <<EOF
edit database oltp_ch2 set state='apply-on';
EOF
echo "next: gds services"
read -p ""

gsm
echo "#### GDS SERVICES ####"
echo "GDS COMMAND: 
services
"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
services
exit
EOF
echo "next: gds services"
read -p ""

gsm
echo "#### GDS SERVICES ####"
echo "GDS COMMAND: 
services
"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
services
exit
EOF
echo "next: switchover to CH1 (run cli_ro_long.sh and cli_rw_long.sh first)"
read -p ""


db
echo "#### VALIDATE DATABASE OLTP_CH1 ####"
dgmgrl -echo sys/password1@oltp_de <<EOF
validate database oltp_ch1;
EOF
echo "next: switchover"
read -p ""
echo "#### SWITCHOVER TO OLTP_CH1 ####"
dgmgrl -echo sys/password1@oltp_de <<EOF
switchover to oltp_ch1;
EOF
echo "next: gds services"
read -p ""

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

clear

function db {

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin

}

function gsm {

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/gsmhome_1

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin

}

echo "#### CURRENT CONFIGURATION: CLASSIC DATA GUARD, 3 DATABASES ####"

dgmgrl -echo sys/password1@oltp_de <<EOF

show configuration

EOF

echo "next: GSM config"

read -p ""

gsm

echo "#### GSM CONFIGURATION ####"

echo "GDS COMMAND:

config"

gdsctl <<EOF

connect gsm_admin/password1@gsm1

config

exit

EOF

echo "next: ADD GDSPOOL"

read -p ""

echo "#### ADD GDSPOOL ####"

echo "GDS COMMAND:

add gdspool -gdspool sales"

gdsctl <<EOF

connect gsm_admin/password1@gsm1

add gdspool -gdspool sales

exit

EOF

echo "next: ADD BROKERCONFIG"

read -p ""

echo "#### ADD BROKERCONFIG ####"

echo "GDS COMMAND:

add brokerconfig -connect gsm02.trivadistraining.com:1521/oltp_de -pwd password1 -gdspool sales -region germany"

gdsctl <<EOF

connect gsm_admin/password1@gsm1

add brokerconfig -connect gsm02.trivadistraining.com:1521/oltp_de -pwd password1 -gdspool sales -region germany

exit

EOF

echo "next: config databases"

read -p ""

echo "#### CONFIG DATABASES ####"

echo "GDS COMMAND:

config database"

gdsctl <<EOF

connect gsm_admin/password1@gsm1

config database

exit

EOF

echo "next: modify databases"

read -p ""

echo "#### MODIFY DATABASES ####"

echo "GDS COMMAND:

modify database -database oltp_ch1 -region switzerland

modify database -database oltp_ch2 -region switzerland

gdsctl <<EOF

connect gsm_admin/password1@gsm1

modify database -database oltp_ch1 -region switzerland

modify database -database oltp_ch2 -region switzerland

config database

exit

EOF

echo "next: add service read/write"

read -p ""

echo "#### ADD SERVICE R/W ####"

echo "GDS COMMAND:

add service -gdspool sales -service gsales_rw -role primary -preferred_all -failovertype SELECT -failovermethod BASIC -failoverretry 5 -failoverdelay 3 -locality LOCAL_ONLY -region_failover

start service -service gsales_rw

services"

gdsctl <<EOF

connect gsm_admin/password1@gsm1

add service -gdspool sales -service gsales_rw -role primary -preferred_all -failovertype SELECT -failovermethod BASIC -failoverretry 5 -failoverdelay 3 -locality LOCAL_ONLY -region_failover

start service -service gsales_rw

services

exit

EOF

echo "next: ADD SERVICE R/O"

read -p ""

echo "#### ADD SERVICE R/O ####"

echo "GDS COMMAND:

add service -gdspool sales -service gsales_ro -role PHYSICAL_STANDBY -failover_primary -lag 20 -preferred_all -failovertype SELECT -failovermethod BASIC -failoverretry 5 -failoverdelay 3 -locality LOCAL_ONLY -region_failover

start service -service gsales_ro

services

gdsctl <<EOF

connect gsm_admin/password1@gsm1

start service -service gsales_ro

services

exit

EOF

echo "next: stop apply ch1 (run cli_ro_short.sh first)"

read -p ""

echo "#### STOP APPLY DATA GUARD ON OLTP_CH1 ####"

dgmgrl -echo sys/password1@oltp_de <<EOF

edit database oltp_ch1 set state='apply-off';

EOF

echo "next: gds services"

read -p ""

gsm

echo "#### GDS SERVICES ####"

echo "GDS COMMAND:

services

gdsctl <<EOF

connect gsm_admin/password1@gsm1

services

exit

EOF

echo "next: stop apply ch2 (run cli_ro_short.sh first)"

read -p ""

echo "#### STOP APPLY DATA GUARD ON OLTP_CH2 ####"

dgmgrl -echo sys/password1@oltp_de <<EOF

edit database oltp_ch2 set state='apply-off';

EOF

echo "next: gds services"

read -p ""

gsm

echo "#### GDS SERVICES ####"

echo "GDS COMMAND:

services

gdsctl <<EOF

connect gsm_admin/password1@gsm1

services

exit

EOF

echo "next: gds services"

read -p ""

gsm

echo "#### GDS SERVICES ####"

echo "GDS COMMAND:

services

gdsctl <<EOF

connect gsm_admin/password1@gsm1

services

exit

EOF

echo "next: start apply ch1 and ch2"

read -p ""

echo "#### START APPLY DATA GUARD ON OLTP_CH1 and OLTP_CH2 ####"

dgmgrl -echo sys/password1@oltp_de <<EOF

edit database oltp_ch1 set state='apply-on';

EOF

echo "sleeping 5"

sleep 5

dgmgrl -echo sys/password1@oltp_de <<EOF

edit database oltp_ch2 set state='apply-on';

EOF

echo "next: gds services"

read -p ""

gsm

echo "#### GDS SERVICES ####"

echo "GDS COMMAND:

services

gdsctl <<EOF

connect gsm_admin/password1@gsm1

services

exit

EOF

echo "next: gds services"

read -p ""

gsm

echo "#### GDS SERVICES ####"

echo "GDS COMMAND:

services

gdsctl <<EOF

connect gsm_admin/password1@gsm1

services

exit

EOF

echo "next: switchover to CH1 (run cli_ro_long.sh and cli_rw_long.sh first)"

read -p ""

echo "#### VALIDATE DATABASE OLTP_CH1 ####"

dgmgrl -echo sys/password1@oltp_de <<EOF

validate database oltp_ch1;

EOF

echo "next: switchover"

read -p ""

echo "#### SWITCHOVER TO OLTP_CH1 ####"

dgmgrl -echo sys/password1@oltp_de <<EOF

switchover to oltp_ch1;

EOF

echo "next: gds services"

read -p ""

And the script to revert the demo:

clear

function db {
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin
}

function gsm {
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/gsmhome_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin
}

db
dgmgrl -echo sys/password1@oltp_de <<EOF
switchover to oltp_de;
EOF

gsm
echo "#### STOP and DELETE SERVICE, REMOVE BROKERCONFIG, REMOVE POOL ####"
 
gdsctl <<EOF
connect gsm_admin/password1@gsm1
stop service -service gsales_ro
stop service -service gsales_rw
remove service -service gsales_ro
remove service -service gsales_rw
remove brokerconfig
remove gdspool -gdspool sales
config
exit
EOF

db
dgmgrl -echo sys/password1@oltp_de <<EOF
show configuration
EOF

echo "DEMO reverted."
read -p ""

clear

function db {

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin

}

function gsm {

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/gsmhome_1

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin

}

dgmgrl -echo sys/password1@oltp_de <<EOF

switchover to oltp_de;

EOF

gsm

echo "#### STOP and DELETE SERVICE, REMOVE BROKERCONFIG, REMOVE POOL ####"

gdsctl <<EOF

connect gsm_admin/password1@gsm1

stop service -service gsales_ro

stop service -service gsales_rw

remove service -service gsales_ro

remove service -service gsales_rw

remove brokerconfig

remove gdspool -gdspool sales

config

exit

EOF

dgmgrl -echo sys/password1@oltp_de <<EOF

show configuration

EOF

echo "DEMO reverted."

read -p ""

Cheers

—

Ludovico

Oracle Database on ACFS: a perfect marriage?

Posted on November 26, 2015 by Ludovico

Update: I will give this presentation at UKOUG Tech15, Wed 9 December at 14:30.

This presentation has had a very poor score in selections for conferences (no OOW, no DOAG) but people liked it very much at Paris Oracle Meetup. The Database on ACFS is mainstream now, thanks to the new ODA releases. Having some knowledge about why and how you should run (not) Databases on ACFS is definitely worth a read.

Slides

Demo 1 recording

Demo 2 recording

Demo script (DB ACFS clone from Standby Database)

#!/bin/bash
####################
# create the clone #
####################

set -x

NUM=`echo $$ | cut -c 1-4`
export NEWNAME=${1:-SN$NUM}

cat <<EOF 
################################################
################################################
##
## CLONING DATABASE USING NEW SID: $NEWNAME
##
################################################
################################################

EOF

export ORACLE_SID=ACFSDB_1

dgmgrl <<EOF
connect sys/racattack
edit database ACFSDB set state="APPLY-OFF";
exit
EOF

acfsutil snap create -w $NEWNAME /u02

dgmgrl <<EOF
connect sys/racattack
edit database ACFSDB set state="APPLY-ON";
exit
EOF

cd /u02/.ACFS/snaps/$NEWNAME/ACFSDB

sqlplus / as sysdba <<EOF
alter database backup controlfile to trace as '/u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc' reuse resetlogs;
create pfile='/u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora' from spfile;
exit
EOF

sed -i -e "s/u02\/ACFSDB\//u02\/.ACFS\/snaps\/$NEWNAME\/ACFSDB\//g" /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc
sed -i -e "s/CREATE CONTROLFILE.*$/CREATE CONTROLFILE REUSE SET DATABASE \"$NEWNAME\" RESETLOGS FORCE LOGGING NOARCHIVELOG/" /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc
rm /u02/.ACFS/snaps/$NEWNAME/ACFSDB/ACFSDB/fast_recovery_area/ACFSDB/controlfile/*
rm /u02/.ACFS/snaps/$NEWNAME/ACFSDB/ACFSDB/controlfile/*
sed -i '/^ACFS.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i -e "s/u02\/ACFSDB\//u02\/.ACFS\/snaps\/$NEWNAME\/ACFSDB\//g" /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.db_name.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.db_unique_name.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.dispatchers.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.audit_file_dest.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.fal_server.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.log_archive_config.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.log_archive_dest_1.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.memory_target.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.service_names.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.cluster_database.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora


#find /u02/.ACFS/snaps/SNAP1/ACFSDB/ACFSDB/fast_recovery_area/ACFSDB/archivelog/ -type f -exec mv {} /u02/.ACFS/snaps/SNAP1/ACFSDB/archivelog/ \;

mkdir -p $ORACLE_BASE/admin/$NEWNAME/adump

cat  >>/u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora <<EOF
*.audit_file_dest='$ORACLE_BASE/admin/$NEWNAME/adump'
*.db_name='$NEWNAME'
*.db_unique_name='$NEWNAME'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=${NEWNAME}XDB)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST'
*.sga_target=1900M
$NEWNAME.instance_number=1
$NEWNAME.undo_tablespace=UNDOTBS1
*.service_names='$NEWNAME'
*.cluster_database=false
EOF

export ORACLE_SID=$NEWNAME

head -n $((`grep -n ^RECOVER /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc | awk -F: '{print $1}'`-2)) /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc > /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control1.trc

 
sqlplus / as sysdba <<EOF
create spfile from pfile='/u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora';
@/u02/.ACFS/snaps/$NEWNAME/ACFSDB/control1.trc
--recover automatic database using backup controlfile until cancel;
--CANCEL
alter database open resetlogs;
alter tablespace temp add tempfile size 50M ;
EOF

#!/bin/bash

####################

# create the clone #

####################

set -x

NUM=`echo $$ | cut -c 1-4`

export NEWNAME=${1:-SN$NUM}

cat <<EOF

################################################

## CLONING DATABASE USING NEW SID: $NEWNAME

################################################

EOF

export ORACLE_SID=ACFSDB_1

dgmgrl <<EOF

connect sys/racattack

edit database ACFSDB set state="APPLY-OFF";

exit

EOF

acfsutil snap create -w $NEWNAME /u02

dgmgrl <<EOF

connect sys/racattack

edit database ACFSDB set state="APPLY-ON";

exit

EOF

cd /u02/.ACFS/snaps/$NEWNAME/ACFSDB

sqlplus / as sysdba <<EOF

alter database backup controlfile to trace as '/u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc' reuse resetlogs;

create pfile='/u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora' from spfile;

exit

EOF

sed -i -e "s/u02\/ACFSDB\//u02\/.ACFS\/snaps\/$NEWNAME\/ACFSDB\//g" /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc

sed -i -e "s/CREATE CONTROLFILE.*$/CREATE CONTROLFILE REUSE SET DATABASE \"$NEWNAME\" RESETLOGS FORCE LOGGING NOARCHIVELOG/" /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc

rm /u02/.ACFS/snaps/$NEWNAME/ACFSDB/ACFSDB/fast_recovery_area/ACFSDB/controlfile/*

rm /u02/.ACFS/snaps/$NEWNAME/ACFSDB/ACFSDB/controlfile/*

sed -i '/^ACFS.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i -e "s/u02\/ACFSDB\//u02\/.ACFS\/snaps\/$NEWNAME\/ACFSDB\//g" /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.db_name.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.db_unique_name.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.dispatchers.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.audit_file_dest.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.fal_server.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.log_archive_config.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.log_archive_dest_1.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.memory_target.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.service_names.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.cluster_database.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

#find /u02/.ACFS/snaps/SNAP1/ACFSDB/ACFSDB/fast_recovery_area/ACFSDB/archivelog/ -type f -exec mv {} /u02/.ACFS/snaps/SNAP1/ACFSDB/archivelog/ \;

mkdir -p $ORACLE_BASE/admin/$NEWNAME/adump

cat >>/u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora <<EOF

*.audit_file_dest='$ORACLE_BASE/admin/$NEWNAME/adump'

*.db_name='$NEWNAME'

*.db_unique_name='$NEWNAME'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=${NEWNAME}XDB)'

*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST'

*.sga_target=1900M

$NEWNAME.instance_number=1

$NEWNAME.undo_tablespace=UNDOTBS1

*.service_names='$NEWNAME'

*.cluster_database=false

EOF

export ORACLE_SID=$NEWNAME

head -n $((`grep -n ^RECOVER /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc | awk -F: '{print $1}'`-2)) /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc > /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control1.trc

sqlplus / as sysdba <<EOF

create spfile from pfile='/u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora';

@/u02/.ACFS/snaps/$NEWNAME/ACFSDB/control1.trc

--recover automatic database using backup controlfile until cancel;

--CANCEL

alter database open resetlogs;

alter tablespace temp add tempfile size 50M ;

EOF

Comments are, as always, very appreciated 🙂

—

Ludo

Migrating Oracle RAC from SuSE to OEL (or RHEL) live

Posted on November 10, 2015 by Ludovico

I have a customer that needs to migrate its Oracle RAC cluster from SuSE to OEL.

I know, I know, there is a paper from Dell and Oracle named:

How Dell Migrated from SUSE Linux to Oracle Linux

That explains how Dell migrated its many RAC clusters from SuSE to OEL. The problem is that they used a different strategy:

– backup the configuration of the nodes
– then for each node, one at time
– stop the node
– reinstall the OS
– restore the configuration and the Oracle binaries
– relink
– restart

What I want to achieve instead is:
– add one OEL node to the SuSE cluster as new node
– remove one SuSE node from the now-mixed cluster
– install/restore/relink the RDBMS software (RAC) on the new node
– move the RAC instances to the new node (taking care to NOT run more than the number of licensed nodes/CPUs at any time)
– repeat (for the remaining nodes)

because the customer will also migrate to new hardware.

In order to test this migration path, I’ve set up a SINGLE NODE cluster (if it works for one node, it will for two or more).

oracle@sles01:~> crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       sles01                   STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       sles01                   STABLE
ora.asm
               ONLINE  ONLINE       sles01                   Started,STABLE
ora.net1.network
               ONLINE  ONLINE       sles01                   STABLE
ora.ons
               ONLINE  ONLINE       sles01                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       sles01                   STABLE
ora.cvu
      1        ONLINE  ONLINE       sles01                   STABLE
ora.oc4j
      1        OFFLINE OFFLINE                               STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       sles01                   STABLE
ora.sles01.vip
      1        ONLINE  ONLINE       sles01                   STABLE
--------------------------------------------------------------------------------
oracle@sles01:~> cat /etc/issue

Welcome to SUSE Linux Enterprise Server 11 SP4  (x86_64) - Kernel \r (\l).

oracle@sles01:~> crsctl stat res -t

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

Name Target State Server State details

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

Local Resources

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

ora.DATA.dg

ONLINE ONLINE sles01 STABLE

ora.LISTENER.lsnr

ONLINE ONLINE sles01 STABLE

ora.asm

ONLINE ONLINE sles01 Started,STABLE

ora.net1.network

ONLINE ONLINE sles01 STABLE

ora.ons

ONLINE ONLINE sles01 STABLE

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

Cluster Resources

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

ora.LISTENER_SCAN1.lsnr

1 ONLINE ONLINE sles01 STABLE

ora.cvu

1 ONLINE ONLINE sles01 STABLE

ora.oc4j

1 OFFLINE OFFLINE STABLE

ora.scan1.vip

1 ONLINE ONLINE sles01 STABLE

ora.sles01.vip

1 ONLINE ONLINE sles01 STABLE

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

oracle@sles01:~> cat /etc/issue

Welcome to SUSE Linux Enterprise Server 11 SP4 (x86_64) - Kernel \r (\l).

I have to setup the new node addition carefully, mainly as I would do with a traditional node addition:

Add new ip addresses (public, private, vip) to the DNS/hosts
Install the new OEL server
Keep the same user and groups (uid, gid, etc)
Verify the network connectivity and setup SSH equivalence
Check that the multicast connection is ok
Add the storage, configure persistent naming (udev) and verify that the disks (major, minor, names) are the very same
The network cards also must be the very same

Once the new host ready, the cluvfy stage -pre nodeadd will likely fail due to

Kernel release mismatch
Package mismatch

Here’s an example of output:

oracle@sles01:~> cluvfy stage -pre nodeadd -n rhel01

Performing pre-checks for node addition

Checking node reachability...
Node reachability check passed from node "sles01"


Checking user equivalence...
User equivalence check passed for user "oracle"
Package existence check passed for "cvuqdisk"

Checking CRS integrity...

CRS integrity check passed

Clusterware version consistency passed.

Checking shared resources...

Checking CRS home location...
Location check passed for: "/u01/app/12.1.0/grid"
Shared resources check for node addition passed


Checking node connectivity...

Checking hosts config file...

Verification of the hosts config file successful

Check: Node connectivity using interfaces on subnet "192.168.56.0"
Node connectivity passed for subnet "192.168.56.0" with node(s) sles01,rhel01
TCP connectivity check passed for subnet "192.168.56.0"


Check: Node connectivity using interfaces on subnet "172.16.100.0"
Node connectivity passed for subnet "172.16.100.0" with node(s) rhel01,sles01
TCP connectivity check passed for subnet "172.16.100.0"

Checking subnet mask consistency...
Subnet mask consistency check passed for subnet "192.168.56.0".
Subnet mask consistency check passed for subnet "172.16.100.0".
Subnet mask consistency check passed.

Node connectivity check passed

Checking multicast communication...

Checking subnet "172.16.100.0" for multicast communication with multicast group "224.0.0.251"...
Check of subnet "172.16.100.0" for multicast communication with multicast group "224.0.0.251" passed.

Check of multicast communication passed.
Total memory check passed
Available memory check passed
Swap space check passed
Free disk space check passed for "sles01:/usr,sles01:/var,sles01:/etc,sles01:/u01/app/12.1.0/grid,sles01:/sbin,sles01:/tmp"
Free disk space check passed for "rhel01:/usr,rhel01:/var,rhel01:/etc,rhel01:/u01/app/12.1.0/grid,rhel01:/sbin,rhel01:/tmp"
Check for multiple users with UID value 1101 passed
User existence check passed for "oracle"
Run level check passed
Hard limits check passed for "maximum open file descriptors"
Soft limits check passed for "maximum open file descriptors"
Hard limits check passed for "maximum user processes"
Soft limits check passed for "maximum user processes"
System architecture check passed

WARNING:
PRVF-7524 : Kernel version is not consistent across all the nodes.
Kernel version = "3.0.101-63-default" found on nodes: sles01.
Kernel version = "3.8.13-16.2.1.el6uek.x86_64" found on nodes: rhel01.
Kernel version check passed
Kernel parameter check passed for "semmsl"
Kernel parameter check passed for "semmns"
Kernel parameter check passed for "semopm"
Kernel parameter check passed for "semmni"
Kernel parameter check passed for "shmmax"
Kernel parameter check passed for "shmmni"
Kernel parameter check passed for "shmall"
Kernel parameter check passed for "file-max"
Kernel parameter check passed for "ip_local_port_range"
Kernel parameter check passed for "rmem_default"
Kernel parameter check passed for "rmem_max"
Kernel parameter check passed for "wmem_default"
Kernel parameter check passed for "wmem_max"
Kernel parameter check passed for "aio-max-nr"
Package existence check passed for "make"
Package existence check passed for "libaio"
Package existence check passed for "binutils"
Package existence check passed for "gcc(x86_64)"
Package existence check passed for "gcc-c++(x86_64)"
Package existence check passed for "glibc"
Package existence check passed for "glibc-devel"
Package existence check passed for "ksh"
Package existence check passed for "libaio-devel"
Package existence check failed for "libstdc++33"
Check failed on nodes:
        rhel01
Package existence check failed for "libstdc++43-devel"
Check failed on nodes:
        rhel01
Package existence check passed for "libstdc++-devel(x86_64)"
Package existence check failed for "libstdc++46"
Check failed on nodes:
        rhel01
Package existence check failed for "libgcc46"
Check failed on nodes:
        rhel01
Package existence check passed for "sysstat"
Package existence check failed for "libcap1"
Check failed on nodes:
        rhel01
Package existence check failed for "nfs-kernel-server"
Check failed on nodes:
        rhel01
Check for multiple users with UID value 0 passed
Current group ID check passed

Starting check for consistency of primary group of root user

Check for consistency of root user's primary group passed
Group existence check passed for "asmadmin"
Group existence check passed for "asmoper"
Group existence check passed for "asmdba"

Checking ASMLib configuration.
Check for ASMLib configuration passed.

Checking OCR integrity...

OCR integrity check passed

Checking Oracle Cluster Voting Disk configuration...

Oracle Cluster Voting Disk configuration check passed
Time zone consistency check passed

Starting Clock synchronization checks using Network Time Protocol(NTP)...

NTP Configuration file check started...
No NTP Daemons or Services were found to be running

Clock synchronization check using Network Time Protocol(NTP) passed


User "oracle" is not part of "root" group. Check passed
Checking integrity of file "/etc/resolv.conf" across nodes

"domain" and "search" entries do not coexist in any  "/etc/resolv.conf" file
All nodes have same "search" order defined in file "/etc/resolv.conf"
PRVF-5636 : The DNS response time for an unreachable node exceeded "15000" ms on following nodes: sles01,rhel01

Check for integrity of file "/etc/resolv.conf" failed


Checking integrity of name service switch configuration file "/etc/nsswitch.conf" ...
Check for integrity of name service switch configuration file "/etc/nsswitch.conf" passed


Pre-check for node addition was unsuccessful on all the nodes.

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

oracle@sles01:~> cluvfy stage -pre nodeadd -n rhel01

Performing pre-checks for node addition

Checking node reachability...

Node reachability check passed from node "sles01"

Checking user equivalence...

User equivalence check passed for user "oracle"

Package existence check passed for "cvuqdisk"

Checking CRS integrity...

CRS integrity check passed

Clusterware version consistency passed.

Checking shared resources...

Checking CRS home location...

Location check passed for: "/u01/app/12.1.0/grid"

Shared resources check for node addition passed

Checking node connectivity...

Checking hosts config file...

Verification of the hosts config file successful

Check: Node connectivity using interfaces on subnet "192.168.56.0"

Node connectivity passed for subnet "192.168.56.0" with node(s) sles01,rhel01

TCP connectivity check passed for subnet "192.168.56.0"

Check: Node connectivity using interfaces on subnet "172.16.100.0"

Node connectivity passed for subnet "172.16.100.0" with node(s) rhel01,sles01

TCP connectivity check passed for subnet "172.16.100.0"

Checking subnet mask consistency...

Subnet mask consistency check passed for subnet "192.168.56.0".

Subnet mask consistency check passed for subnet "172.16.100.0".

Subnet mask consistency check passed.

Node connectivity check passed

Checking multicast communication...

Checking subnet "172.16.100.0" for multicast communication with multicast group "224.0.0.251"...

Check of subnet "172.16.100.0" for multicast communication with multicast group "224.0.0.251" passed.

Check of multicast communication passed.

Total memory check passed

Available memory check passed

Swap space check passed

Free disk space check passed for "sles01:/usr,sles01:/var,sles01:/etc,sles01:/u01/app/12.1.0/grid,sles01:/sbin,sles01:/tmp"

Free disk space check passed for "rhel01:/usr,rhel01:/var,rhel01:/etc,rhel01:/u01/app/12.1.0/grid,rhel01:/sbin,rhel01:/tmp"

Check for multiple users with UID value 1101 passed

User existence check passed for "oracle"

Run level check passed

Hard limits check passed for "maximum open file descriptors"

Soft limits check passed for "maximum open file descriptors"

Hard limits check passed for "maximum user processes"

Soft limits check passed for "maximum user processes"

System architecture check passed

WARNING:

PRVF-7524 : Kernel version is not consistent across all the nodes.

Kernel version = "3.0.101-63-default" found on nodes: sles01.

Kernel version = "3.8.13-16.2.1.el6uek.x86_64" found on nodes: rhel01.

Kernel version check passed

Kernel parameter check passed for "semmsl"

Kernel parameter check passed for "semmns"

Kernel parameter check passed for "semopm"

Kernel parameter check passed for "semmni"

Kernel parameter check passed for "shmmax"

Kernel parameter check passed for "shmmni"

Kernel parameter check passed for "shmall"

Kernel parameter check passed for "file-max"

Kernel parameter check passed for "ip_local_port_range"

Kernel parameter check passed for "rmem_default"

Kernel parameter check passed for "rmem_max"

Kernel parameter check passed for "wmem_default"

Kernel parameter check passed for "wmem_max"

Kernel parameter check passed for "aio-max-nr"

Package existence check passed for "make"

Package existence check passed for "libaio"

Package existence check passed for "binutils"

Package existence check passed for "gcc(x86_64)"

Package existence check passed for "gcc-c++(x86_64)"

Package existence check passed for "glibc"

Package existence check passed for "glibc-devel"

Package existence check passed for "ksh"

Package existence check passed for "libaio-devel"

Package existence check failed for "libstdc++33"

Check failed on nodes:

rhel01

Package existence check failed for "libstdc++43-devel"

Check failed on nodes:

rhel01

Package existence check passed for "libstdc++-devel(x86_64)"

Package existence check failed for "libstdc++46"

Check failed on nodes:

rhel01

Package existence check failed for "libgcc46"

Check failed on nodes:

rhel01

Package existence check passed for "sysstat"

Package existence check failed for "libcap1"

Check failed on nodes:

rhel01

Package existence check failed for "nfs-kernel-server"

Check failed on nodes:

rhel01

Check for multiple users with UID value 0 passed

Current group ID check passed

Starting check for consistency of primary group of root user

Check for consistency of root user's primary group passed

Group existence check passed for "asmadmin"

Group existence check passed for "asmoper"

Group existence check passed for "asmdba"

Checking ASMLib configuration.

Check for ASMLib configuration passed.

Checking OCR integrity...

OCR integrity check passed

Checking Oracle Cluster Voting Disk configuration...

Oracle Cluster Voting Disk configuration check passed

Time zone consistency check passed

Starting Clock synchronization checks using Network Time Protocol(NTP)...

NTP Configuration file check started...

No NTP Daemons or Services were found to be running

Clock synchronization check using Network Time Protocol(NTP) passed

User "oracle" is not part of "root" group. Check passed

Checking integrity of file "/etc/resolv.conf" across nodes

"domain" and "search" entries do not coexist in any "/etc/resolv.conf" file

All nodes have same "search" order defined in file "/etc/resolv.conf"

PRVF-5636 : The DNS response time for an unreachable node exceeded "15000" ms on following nodes: sles01,rhel01

Check for integrity of file "/etc/resolv.conf" failed

Checking integrity of name service switch configuration file "/etc/nsswitch.conf" ...

Check for integrity of name service switch configuration file "/etc/nsswitch.conf" passed

Pre-check for node addition was unsuccessful on all the nodes.

So the problem is not if the check succeed or not (it will not), but what fails.

Solving all the problems not related to the difference SuSE-OEL is crucial, because the addNode.sh will fail with the same errors. I need to run it using -ignorePrereqs and -ignoreSysPrereqs switches. Let’s see how it works:

oracle@sles01:/u01/app/12.1.0/grid/addnode> ./addnode.sh -silent "CLUSTER_NEW_NODES={rhel01}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={rhel01-vip}" -ignorePrereq -ignoreSysPrereqs
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 27479 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 2032 MB    Passed

Prepare Configuration in progress.

Prepare Configuration successful.
..................................................   9% Done.
You can find the log of this install session at:
 /u01/app/oraInventory/logs/addNodeActions2015-11-09_09-57-16PM.log

Instantiate files in progress.

Instantiate files successful.
..................................................   15% Done.

Copying files to node in progress.

Copying files to node successful.
..................................................   79% Done.

Saving cluster inventory in progress.
..................................................   87% Done.

Saving cluster inventory successful.
The Cluster Node Addition of /u01/app/12.1.0/grid was successful.
Please check '/tmp/silentInstall.log' for more details.

As a root user, execute the following script(s):
        1. /u01/app/oraInventory/orainstRoot.sh
        2. /u01/app/12.1.0/grid/root.sh

Execute /u01/app/oraInventory/orainstRoot.sh on the following nodes:
[rhel01]
Execute /u01/app/12.1.0/grid/root.sh on the following nodes:
[rhel01]

The scripts can be executed in parallel on all the nodes. If there are any policy managed databases managed by cluster, proceed with the addnode procedure without executing the root.sh script. Ensure that root.sh script is executed after all the policy managed databases managed by clusterware are extended to the new nodes.
..........
Update Inventory in progress.
..................................................   100% Done.

Update Inventory successful.
Successfully Setup Software.

oracle@sles01:/u01/app/12.1.0/grid/addnode> ./addnode.sh -silent "CLUSTER_NEW_NODES={rhel01}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={rhel01-vip}" -ignorePrereq -ignoreSysPrereqs

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB. Actual 27479 MB Passed

Checking swap space: must be greater than 150 MB. Actual 2032 MB Passed

Prepare Configuration in progress.

Prepare Configuration successful.

.................................................. 9% Done.

You can find the log of this install session at:

/u01/app/oraInventory/logs/addNodeActions2015-11-09_09-57-16PM.log

Instantiate files in progress.

Instantiate files successful.

.................................................. 15% Done.

Copying files to node in progress.

Copying files to node successful.

.................................................. 79% Done.

Saving cluster inventory in progress.

.................................................. 87% Done.

Saving cluster inventory successful.

The Cluster Node Addition of /u01/app/12.1.0/grid was successful.

Please check '/tmp/silentInstall.log' for more details.

As a root user, execute the following script(s):

1. /u01/app/oraInventory/orainstRoot.sh

2. /u01/app/12.1.0/grid/root.sh

Execute /u01/app/oraInventory/orainstRoot.sh on the following nodes:

[rhel01]

Execute /u01/app/12.1.0/grid/root.sh on the following nodes:

[rhel01]

The scripts can be executed in parallel on all the nodes. If there are any policy managed databases managed by cluster, proceed with the addnode procedure without executing the root.sh script. Ensure that root.sh script is executed after all the policy managed databases managed by clusterware are extended to the new nodes.

..........

Update Inventory in progress.

.................................................. 100% Done.

Update Inventory successful.

Successfully Setup Software.

Then, as stated by the addNode.sh, I run the root.sh and I expect it to work:

[oracle@rhel01 install]$ sudo /u01/app/12.1.0/grid/root.sh
Performing root user operation for Oracle 12c

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/12.1.0/grid
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Relinking oracle with rac_on option
Using configuration parameter file: /u01/app/12.1.0/grid/crs/install/crsconfig_params
2015/11/09 23:18:42 CLSRSC-363: User ignored prerequisites during installation

OLR initialization - successful
2015/11/09 23:19:08 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.conf'

CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Starting Oracle High Availability Services-managed resources
CRS-2672: Attempting to start 'ora.mdnsd' on 'rhel01'
CRS-2672: Attempting to start 'ora.evmd' on 'rhel01'
CRS-2676: Start of 'ora.mdnsd' on 'rhel01' succeeded
CRS-2676: Start of 'ora.evmd' on 'rhel01' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'rhel01'
CRS-2676: Start of 'ora.gpnpd' on 'rhel01' succeeded
CRS-2672: Attempting to start 'ora.gipcd' on 'rhel01'
CRS-2676: Start of 'ora.gipcd' on 'rhel01' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'rhel01'
CRS-2676: Start of 'ora.cssdmonitor' on 'rhel01' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'rhel01'
CRS-2672: Attempting to start 'ora.diskmon' on 'rhel01'
CRS-2676: Start of 'ora.diskmon' on 'rhel01' succeeded
CRS-2789: Cannot stop resource 'ora.diskmon' as it is not running on server 'rhel01'
CRS-2676: Start of 'ora.cssd' on 'rhel01' succeeded
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'rhel01'
CRS-2672: Attempting to start 'ora.ctssd' on 'rhel01'
CRS-2676: Start of 'ora.ctssd' on 'rhel01' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'rhel01' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'rhel01'
CRS-2676: Start of 'ora.asm' on 'rhel01' succeeded
CRS-2672: Attempting to start 'ora.storage' on 'rhel01'
CRS-2676: Start of 'ora.storage' on 'rhel01' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'rhel01'
CRS-2676: Start of 'ora.crsd' on 'rhel01' succeeded
CRS-6017: Processing resource auto-start for servers: rhel01
CRS-2672: Attempting to start 'ora.ons' on 'rhel01'
CRS-2676: Start of 'ora.ons' on 'rhel01' succeeded
CRS-6016: Resource auto-start has completed for server rhel01
CRS-6024: Completed start of Oracle Cluster Ready Services-managed resources
CRS-4123: Oracle High Availability Services has been started.
2015/11/09 23:22:06 CLSRSC-343: Successfully started Oracle clusterware stack

clscfg: EXISTING configuration version 5 detected.
clscfg: version 5 is 12c Release 1.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Preparing packages for installation...
cvuqdisk-1.0.9-1
2015/11/09 23:22:23 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster ... succeeded

[oracle@rhel01 install]$ sudo /u01/app/12.1.0/grid/root.sh

Performing root user operation for Oracle 12c

The following environment variables are set as:

ORACLE_OWNER= oracle

ORACLE_HOME= /u01/app/12.1.0/grid

Copying dbhome to /usr/local/bin ...

Copying oraenv to /usr/local/bin ...

Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root script.

Now product-specific root actions will be performed.

Relinking oracle with rac_on option

Using configuration parameter file: /u01/app/12.1.0/grid/crs/install/crsconfig_params

2015/11/09 23:18:42 CLSRSC-363: User ignored prerequisites during installation

OLR initialization - successful

2015/11/09 23:19:08 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.conf'

CRS-4133: Oracle High Availability Services has been stopped.

CRS-4123: Oracle High Availability Services has been started.

CRS-4133: Oracle High Availability Services has been stopped.

CRS-4123: Oracle High Availability Services has been started.

CRS-4133: Oracle High Availability Services has been stopped.

CRS-4123: Starting Oracle High Availability Services-managed resources

CRS-2672: Attempting to start 'ora.mdnsd' on 'rhel01'

CRS-2672: Attempting to start 'ora.evmd' on 'rhel01'

CRS-2676: Start of 'ora.mdnsd' on 'rhel01' succeeded

CRS-2676: Start of 'ora.evmd' on 'rhel01' succeeded

CRS-2672: Attempting to start 'ora.gpnpd' on 'rhel01'

CRS-2676: Start of 'ora.gpnpd' on 'rhel01' succeeded

CRS-2672: Attempting to start 'ora.gipcd' on 'rhel01'

CRS-2676: Start of 'ora.gipcd' on 'rhel01' succeeded

CRS-2672: Attempting to start 'ora.cssdmonitor' on 'rhel01'

CRS-2676: Start of 'ora.cssdmonitor' on 'rhel01' succeeded

CRS-2672: Attempting to start 'ora.cssd' on 'rhel01'

CRS-2672: Attempting to start 'ora.diskmon' on 'rhel01'

CRS-2676: Start of 'ora.diskmon' on 'rhel01' succeeded

CRS-2789: Cannot stop resource 'ora.diskmon' as it is not running on server 'rhel01'

CRS-2676: Start of 'ora.cssd' on 'rhel01' succeeded

CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'rhel01'

CRS-2672: Attempting to start 'ora.ctssd' on 'rhel01'

CRS-2676: Start of 'ora.ctssd' on 'rhel01' succeeded

CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'rhel01' succeeded

CRS-2672: Attempting to start 'ora.asm' on 'rhel01'

CRS-2676: Start of 'ora.asm' on 'rhel01' succeeded

CRS-2672: Attempting to start 'ora.storage' on 'rhel01'

CRS-2676: Start of 'ora.storage' on 'rhel01' succeeded

CRS-2672: Attempting to start 'ora.crsd' on 'rhel01'

CRS-2676: Start of 'ora.crsd' on 'rhel01' succeeded

CRS-6017: Processing resource auto-start for servers: rhel01

CRS-2672: Attempting to start 'ora.ons' on 'rhel01'

CRS-2676: Start of 'ora.ons' on 'rhel01' succeeded

CRS-6016: Resource auto-start has completed for server rhel01

CRS-6024: Completed start of Oracle Cluster Ready Services-managed resources

CRS-4123: Oracle High Availability Services has been started.

2015/11/09 23:22:06 CLSRSC-343: Successfully started Oracle clusterware stack

clscfg: EXISTING configuration version 5 detected.

clscfg: version 5 is 12c Release 1.

Successfully accumulated necessary OCR keys.

Creating OCR keys for user 'root', privgrp 'root'..

Operation successful.

Preparing packages for installation...

cvuqdisk-1.0.9-1

2015/11/09 23:22:23 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster ... succeeded

Bingo! Let’s check if everything is up and running:

[oracle@rhel01 ~]$ /u01/app/12.1.0/grid/bin/crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       rhel01                   STABLE
               ONLINE  ONLINE       sles01                   STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       rhel01                   STABLE
               ONLINE  ONLINE       sles01                   STABLE
ora.asm
               ONLINE  ONLINE       rhel01                   Started,STABLE
               ONLINE  ONLINE       sles01                   Started,STABLE
ora.net1.network
               ONLINE  ONLINE       rhel01                   STABLE
               ONLINE  ONLINE       sles01                   STABLE
ora.ons
               ONLINE  ONLINE       rhel01                   STABLE
               ONLINE  ONLINE       sles01                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       sles01                   STABLE
ora.cvu
      1        ONLINE  ONLINE       sles01                   STABLE
ora.oc4j
      1        OFFLINE OFFLINE                               STABLE
ora.rhel01.vip
      1        ONLINE  ONLINE       rhel01                   STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       sles01                   STABLE
ora.sles01.vip
      1        ONLINE  ONLINE       sles01                   STABLE
--------------------------------------------------------------------------------

[oracle@rhel01 ~]$ /u01/app/12.1.0/grid/bin/crsctl stat res -t

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

Name Target State Server State details

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

Local Resources

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

ora.DATA.dg

ONLINE ONLINE rhel01 STABLE

ONLINE ONLINE sles01 STABLE

ora.LISTENER.lsnr

ONLINE ONLINE rhel01 STABLE

ONLINE ONLINE sles01 STABLE

ora.asm

ONLINE ONLINE rhel01 Started,STABLE

ONLINE ONLINE sles01 Started,STABLE

ora.net1.network

ONLINE ONLINE rhel01 STABLE

ONLINE ONLINE sles01 STABLE

ora.ons

ONLINE ONLINE rhel01 STABLE

ONLINE ONLINE sles01 STABLE

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

Cluster Resources

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

ora.LISTENER_SCAN1.lsnr

1 ONLINE ONLINE sles01 STABLE

ora.cvu

1 ONLINE ONLINE sles01 STABLE

ora.oc4j

1 OFFLINE OFFLINE STABLE

ora.rhel01.vip

1 ONLINE ONLINE rhel01 STABLE

ora.scan1.vip

1 ONLINE ONLINE sles01 STABLE

ora.sles01.vip

1 ONLINE ONLINE sles01 STABLE

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

[oracle@rhel01 ~]$ olsnodes -s
sles01  Active
rhel01  Active

[oracle@rhel01 ~]$ ssh rhel01 uname -r
3.8.13-16.2.1.el6uek.x86_64
[oracle@rhel01 ~]$ ssh sles01 uname -r
3.0.101-63-default

[oracle@rhel01 ~]$ ssh rhel01 cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.5 (Santiago)
[oracle@rhel01 ~]$ ssh sles01 cat /etc/issue
Welcome to SUSE Linux Enterprise Server 11 SP4  (x86_64) - Kernel \r (\l).

[oracle@rhel01 ~]$ olsnodes -s

sles01 Active

rhel01 Active

[oracle@rhel01 ~]$ ssh rhel01 uname -r

3.8.13-16.2.1.el6uek.x86_64

[oracle@rhel01 ~]$ ssh sles01 uname -r

3.0.101-63-default

[oracle@rhel01 ~]$ ssh rhel01 cat /etc/redhat-release

Red Hat Enterprise Linux Server release 6.5 (Santiago)

[oracle@rhel01 ~]$ ssh sles01 cat /etc/issue

Welcome to SUSE Linux Enterprise Server 11 SP4 (x86_64) - Kernel \r (\l).

So yes, it works, but remember that it’s not a supported long-term configuration.

In my case I expect to migrate the whole cluster from SLES to OEL in one day.

NOTE: using OEL6 as new target is easy because the interface names do not change. The new OEL7 interface naming changes, if you need to migrate without cluster downtime you need to setup the new OEL7 nodes following this post: http://ask.xmodulo.com/change-network-interface-name-centos7.html

Otherwise, you need to configure a new interface name for the cluster with oifcfg.

HTH

—

Ludovico

Get information about Cursor Sharing for a SQL_ID

Posted on October 29, 2015 by Ludovico

Yesterday I’ve got a weird problem with Adaptive Cursor Sharing. I’m not sure yet about the issue, but it seems to be related to cursor sharing histograms. Hopefully one day I will blog about what I’ve learnt from this experience.

To better monitor the problem on that specific query, I’ve prepared this script (tested on 12.1.0.2):

COLUMN Shareable HEADING 'S|H|A|R|E|A|B|L|E'
COLUMN "Bind-Aware" HEADING 'B|I|N|D| |A|W|A|R|E'
COLUMN Sensitive HEADING 'S|E|N|S|I|T|I|V|E'
COLUMN Reoptimizable HEADING 'R|E|O|P|T|I|M|I|Z|A|B|L|E'
BREAK on child_number on Execs on "Gets/Exec" on "Ela/Exec" on "Sensitive" on "Shareable" on "Bind-Aware" on bucket0 on bucket1 on bucket2 on cnt on "Reoptimizable" on is_resolved_adaptive_plan

select * from (select *
  from (
select 
s.child_number,
  s.plan_hash_value,
  executions as Execs, 
  round(buffer_gets/executions) as "Gets/Exec",
  round(elapsed_time/executions) as "Ela/Exec",
  is_bind_sensitive as "Sensitive",
  is_shareable as "Shareable",
  is_bind_aware as "Bind-Aware",
  to_char(h.bucket_id) as bucket, h.count as cnt,
  is_reoptimizable as "Reoptimizable",
  is_resolved_adaptive_plan,
  "UNBOUND_CURSOR",  "SQL_TYPE_MISMATCH",  "OPTIMIZER_MISMATCH",
  "OUTLINE_MISMATCH", "STATS_ROW_MISMATCH", "LITERAL_MISMATCH",
  "FORCE_HARD_PARSE", "EXPLAIN_PLAN_CURSOR", "BUFFERED_DML_MISMATCH",
  "PDML_ENV_MISMATCH", "INST_DRTLD_MISMATCH", "SLAVE_QC_MISMATCH",
  "TYPECHECK_MISMATCH", "AUTH_CHECK_MISMATCH", "BIND_MISMATCH",
  "DESCRIBE_MISMATCH", "LANGUAGE_MISMATCH", "TRANSLATION_MISMATCH",
  "BIND_EQUIV_FAILURE", "INSUFF_PRIVS", "INSUFF_PRIVS_REM",
  "REMOTE_TRANS_MISMATCH", "LOGMINER_SESSION_MISMATCH", "INCOMP_LTRL_MISMATCH",
  "OVERLAP_TIME_MISMATCH", "EDITION_MISMATCH", "MV_QUERY_GEN_MISMATCH",
  "USER_BIND_PEEK_MISMATCH", "TYPCHK_DEP_MISMATCH", "NO_TRIGGER_MISMATCH",
  "FLASHBACK_CURSOR", "ANYDATA_TRANSFORMATION", "PDDL_ENV_MISMATCH",
  "TOP_LEVEL_RPI_CURSOR", "DIFFERENT_LONG_LENGTH", "LOGICAL_STANDBY_APPLY",
  "DIFF_CALL_DURN", "BIND_UACS_DIFF", "PLSQL_CMP_SWITCHS_DIFF",
  "CURSOR_PARTS_MISMATCH", "STB_OBJECT_MISMATCH", "CROSSEDITION_TRIGGER_MISMATCH",
  "PQ_SLAVE_MISMATCH", "TOP_LEVEL_DDL_MISMATCH", "MULTI_PX_MISMATCH",
  "BIND_PEEKED_PQ_MISMATCH", "MV_REWRITE_MISMATCH", "ROLL_INVALID_MISMATCH",
  "OPTIMIZER_MODE_MISMATCH", "PX_MISMATCH", "MV_STALEOBJ_MISMATCH",
  "FLASHBACK_TABLE_MISMATCH", "LITREP_COMP_MISMATCH", "PLSQL_DEBUG",
  "LOAD_OPTIMIZER_STATS", "ACL_MISMATCH", "FLASHBACK_ARCHIVE_MISMATCH",
  "LOCK_USER_SCHEMA_FAILED", "REMOTE_MAPPING_MISMATCH", "LOAD_RUNTIME_HEAP_FAILED",
  "HASH_MATCH_FAILED", "PURGED_CURSOR", "BIND_LENGTH_UPGRADEABLE",
  "USE_FEEDBACK_STATS"
from v$sql s
  join v$sql_cs_histogram h
    on (s.sql_id=h.sql_id and
	s.child_number=h.child_number and
	s.con_id=h.con_id
	)
  join v$sql_shared_cursor shc
    on (shc.sql_id=h.sql_id and 
	shc.child_number=h.child_number and
	s.con_id=shc.con_id
	)
	where s.sql_id='&sql_id'
)
pivot (sum(cnt) for (bucket) IN ('0' AS Bucket0,'1' AS Bucket1,'2' AS Bucket2))
)
unpivot (result FOR reason_type IN ("UNBOUND_CURSOR",
  "SQL_TYPE_MISMATCH", "OPTIMIZER_MISMATCH",
  "OUTLINE_MISMATCH", "STATS_ROW_MISMATCH", "LITERAL_MISMATCH",
  "FORCE_HARD_PARSE", "EXPLAIN_PLAN_CURSOR", "BUFFERED_DML_MISMATCH",
  "PDML_ENV_MISMATCH", "INST_DRTLD_MISMATCH", "SLAVE_QC_MISMATCH",
  "TYPECHECK_MISMATCH", "AUTH_CHECK_MISMATCH", "BIND_MISMATCH",
  "DESCRIBE_MISMATCH", "LANGUAGE_MISMATCH", "TRANSLATION_MISMATCH",
  "BIND_EQUIV_FAILURE", "INSUFF_PRIVS", "INSUFF_PRIVS_REM",
  "REMOTE_TRANS_MISMATCH", "LOGMINER_SESSION_MISMATCH", "INCOMP_LTRL_MISMATCH",
  "OVERLAP_TIME_MISMATCH", "EDITION_MISMATCH", "MV_QUERY_GEN_MISMATCH",
  "USER_BIND_PEEK_MISMATCH", "TYPCHK_DEP_MISMATCH", "NO_TRIGGER_MISMATCH",
  "FLASHBACK_CURSOR", "ANYDATA_TRANSFORMATION", "PDDL_ENV_MISMATCH",
  "TOP_LEVEL_RPI_CURSOR", "DIFFERENT_LONG_LENGTH", "LOGICAL_STANDBY_APPLY",
  "DIFF_CALL_DURN", "BIND_UACS_DIFF", "PLSQL_CMP_SWITCHS_DIFF",
  "CURSOR_PARTS_MISMATCH", "STB_OBJECT_MISMATCH", "CROSSEDITION_TRIGGER_MISMATCH",
  "PQ_SLAVE_MISMATCH", "TOP_LEVEL_DDL_MISMATCH", "MULTI_PX_MISMATCH",
  "BIND_PEEKED_PQ_MISMATCH", "MV_REWRITE_MISMATCH", "ROLL_INVALID_MISMATCH",
  "OPTIMIZER_MODE_MISMATCH", "PX_MISMATCH", "MV_STALEOBJ_MISMATCH",
  "FLASHBACK_TABLE_MISMATCH", "LITREP_COMP_MISMATCH", "PLSQL_DEBUG",
  "LOAD_OPTIMIZER_STATS", "ACL_MISMATCH", "FLASHBACK_ARCHIVE_MISMATCH",
  "LOCK_USER_SCHEMA_FAILED", "REMOTE_MAPPING_MISMATCH", "LOAD_RUNTIME_HEAP_FAILED",
  "HASH_MATCH_FAILED", "PURGED_CURSOR", "BIND_LENGTH_UPGRADEABLE",
  "USE_FEEDBACK_STATS"))
where result='Y'
order by child_number;

COLUMN Shareable HEADING 'S|H|A|R|E|A|B|L|E'

COLUMN "Bind-Aware" HEADING 'B|I|N|D| |A|W|A|R|E'

COLUMN Sensitive HEADING 'S|E|N|S|I|T|I|V|E'

COLUMN Reoptimizable HEADING 'R|E|O|P|T|I|M|I|Z|A|B|L|E'

BREAK on child_number on Execs on "Gets/Exec" on "Ela/Exec" on "Sensitive" on "Shareable" on "Bind-Aware" on bucket0 on bucket1 on bucket2 on cnt on "Reoptimizable" on is_resolved_adaptive_plan

select * from (select *

from (

select

s.child_number,

s.plan_hash_value,

executions as Execs,

round(buffer_gets/executions) as "Gets/Exec",

round(elapsed_time/executions) as "Ela/Exec",

is_bind_sensitive as "Sensitive",

is_shareable as "Shareable",

is_bind_aware as "Bind-Aware",

to_char(h.bucket_id) as bucket, h.count as cnt,

is_reoptimizable as "Reoptimizable",

is_resolved_adaptive_plan,

"UNBOUND_CURSOR", "SQL_TYPE_MISMATCH", "OPTIMIZER_MISMATCH",

"OUTLINE_MISMATCH", "STATS_ROW_MISMATCH", "LITERAL_MISMATCH",

"FORCE_HARD_PARSE", "EXPLAIN_PLAN_CURSOR", "BUFFERED_DML_MISMATCH",

"PDML_ENV_MISMATCH", "INST_DRTLD_MISMATCH", "SLAVE_QC_MISMATCH",

"TYPECHECK_MISMATCH", "AUTH_CHECK_MISMATCH", "BIND_MISMATCH",

"DESCRIBE_MISMATCH", "LANGUAGE_MISMATCH", "TRANSLATION_MISMATCH",

"BIND_EQUIV_FAILURE", "INSUFF_PRIVS", "INSUFF_PRIVS_REM",

"REMOTE_TRANS_MISMATCH", "LOGMINER_SESSION_MISMATCH", "INCOMP_LTRL_MISMATCH",

"OVERLAP_TIME_MISMATCH", "EDITION_MISMATCH", "MV_QUERY_GEN_MISMATCH",

"USER_BIND_PEEK_MISMATCH", "TYPCHK_DEP_MISMATCH", "NO_TRIGGER_MISMATCH",

"FLASHBACK_CURSOR", "ANYDATA_TRANSFORMATION", "PDDL_ENV_MISMATCH",

"TOP_LEVEL_RPI_CURSOR", "DIFFERENT_LONG_LENGTH", "LOGICAL_STANDBY_APPLY",

"DIFF_CALL_DURN", "BIND_UACS_DIFF", "PLSQL_CMP_SWITCHS_DIFF",

"CURSOR_PARTS_MISMATCH", "STB_OBJECT_MISMATCH", "CROSSEDITION_TRIGGER_MISMATCH",

"PQ_SLAVE_MISMATCH", "TOP_LEVEL_DDL_MISMATCH", "MULTI_PX_MISMATCH",

"BIND_PEEKED_PQ_MISMATCH", "MV_REWRITE_MISMATCH", "ROLL_INVALID_MISMATCH",

"OPTIMIZER_MODE_MISMATCH", "PX_MISMATCH", "MV_STALEOBJ_MISMATCH",

"FLASHBACK_TABLE_MISMATCH", "LITREP_COMP_MISMATCH", "PLSQL_DEBUG",

"LOAD_OPTIMIZER_STATS", "ACL_MISMATCH", "FLASHBACK_ARCHIVE_MISMATCH",

"LOCK_USER_SCHEMA_FAILED", "REMOTE_MAPPING_MISMATCH", "LOAD_RUNTIME_HEAP_FAILED",

"HASH_MATCH_FAILED", "PURGED_CURSOR", "BIND_LENGTH_UPGRADEABLE",

"USE_FEEDBACK_STATS"

from v$sql s

join v$sql_cs_histogram h

on (s.sql_id=h.sql_id and

s.child_number=h.child_number and

s.con_id=h.con_id

)

join v$sql_shared_cursor shc

on (shc.sql_id=h.sql_id and

shc.child_number=h.child_number and

s.con_id=shc.con_id

)

where s.sql_id='&sql_id'

)

pivot (sum(cnt) for (bucket) IN ('0' AS Bucket0,'1' AS Bucket1,'2' AS Bucket2))

)

unpivot (result FOR reason_type IN ("UNBOUND_CURSOR",

"SQL_TYPE_MISMATCH", "OPTIMIZER_MISMATCH",