Страницы

среда, 30 декабря 2015 г.

ORA-8102 on ALTER TABLE SHRINK SPACE with default column optimization

We have been using centralized AWR repository since 2013.
I developed some PL/SQL procedures for that using original awrextr.sql/awrload.sql scripts as a template.
That solution have some drawbacks, for example, an AWR retention for foreign databases always set to 40150 years after each load:
SQL> select dbid, retention from dba_hist_wr_control;

      DBID RETENTION
---------- --------------------
1110059808 +00366 00:00:00.0
1917063347 +40150 00:00:00.0
1996649024 +40150 00:00:00.0
We could change the retention but that would always be reset on the next AWR load.
To prevent the excessive AWR data growth, I executed DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE procedure once a year.
The 2015 year was no exception.
I also executed ALTER TABLE SHRINK SPACE command after I had executed DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE procedures.
Usually that command takes a while to execute, because the some of AWR tables are more than 50G in size, for example, WRH$_ACTIVE_SESSION_HISTORY.
It's a base table for DBA_HIST_ACTIVE_SESS_HISTORY view and contains an ASH data that's huge.
This year things went wrong:
SQL> alter table SYS.WRH$_ACTIVE_SESSION_HISTORY shrink space;
-- AFTER 4-8 hours
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 635
Session ID: 281 Serial number: 54363
alert.log was not very helpful:
Mon Dec 21 14:37:31 2015
Process 0x3cd728e40 appears to be hung while dumping
Current time = 1689592362, process death time = 1689530965 interval = 60000
Called from location UNKNOWN:UNKNOWN
Attempting to kill process 0x3cd728e40 with OS pid = 17740
OSD kill succeeded for process 3cd728e40
Mon Dec 21 14:39:46 2015
Errors in file /oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_25447.trc:
But a relevant trace file (which was more than 4G in size) spotted a light on a problem:
*** ACTION NAME:() 2015-12-21 14:39:46.890
 
oer 8102.2 - obj# 1021709, rdba: 0x086ad43c(afn 33, blk# 2806844)
kdk key 8102.2:
  ncol: 6, len: 26
  key: (26): 
 06 c5 0c 0b 06 63 09 04 c3 08 0e 3e 02 c1 02 05 c4 62 46 05 3b 03 c2 02 3d
 ff
  mask: (4096): 
 81 90 20 02 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
Well, string "oer 8102.2" it's a sign of famous ORA-8102 error:
[oracle@localhost ~]$ oerr ora 8102
08102, 00000, "index key not found, obj# %s, file %s, block %s (%s)"
// *Cause:  Internal error: possible inconsistency in index
// *Action:  Send trace file to your customer support representative, along
//      with information on reproducing the error
obj# 1021709 in the trace file easily shows to us where the problem is:
SQL> select xmltype(cursor(select * from dba_objects where object_id=1021709)) xml_rec from dual;

XML_REC
----------------------------------------------------------------------------------------------------------

<ROWSET>
  <ROW>
    <OWNER>SYS
    <OBJECT_NAME>WRH$_ACTIVE_SESSION_HISTORY_PK
    <SUBOBJECT_NAME>WRH$_ACTIVE_1110059808_66853
    <OBJECT_ID>1021709
    <DATA_OBJECT_ID>1080082
    <OBJECT_TYPE>INDEX PARTITION
    <CREATED>27.10.2015 01:28:05
    <LAST_DDL_TIME>22.12.2015 09:11:22
    <TIMESTAMP>2015-10-27:01:28:05
    <STATUS>VALID
    <TEMPORARY>N
    <GENERATED>N
    <SECONDARY>N
    <NAMESPACE>4
    <SHARING>NONE
    <ORACLE_MAINTAINED>Y
  </ROW>
</ROWSET>
The index WRH$_ACTIVE_SESSION_HISTORY_PK is local partitioned on next columns:
CREATE UNIQUE INDEX "SYS"."WRH$_ACTIVE_SESSION_HISTORY_PK" ON "SYS"."WRH$_ACTIVE_SESSION_HISTORY" ("DBID", "SNAP_ID", "INSTANCE_NUMBER", "SAMPLE_ID", "SESSION_ID", "CON_DBID")
The all of the above columns are numbers.
The index key:
  key: (26): 
 06 c5 0c 0b 06 63 09 04 c3 08 0e 3e 02 c1 02 05 c4 62 46 05 3b 03 c2 02 3d
 ff
can be decrypted easily:
SQL> with input as (
  2    -- index key
  3    select '06 c5 0c 0b 06 63 09 04 c3 08 0e 3e 02 c1 02 05 c4 62 46 05 3b 03 c2 02 3d ff' c from dual),
  4    t(pos, num,c) as (
  5    -- traverse the index key recursively
  6    select 0 pos, 0, replace(c, ' ') c from input union all
  7    select pos+1,
  8           utl_raw.cast_to_number(hextoraw(substr(c, 3, to_number(substr(c,1,2), 'xx')*2))),
  9           substr(c, (to_number(substr(c, 1, 2), 'xx')+1)*2+1)
 10      from t
 11     where c<>'ff')
 12  select pos, num
 13    from t
 14   where pos>0
 15   order by pos
 16  /

       POS        NUM
---------- ----------
         1 1110059808
         2      71361
         3          1
         4   97690458
         5        160
Ok, now we've known that: dbid=1110059808, snap_id=71361, instance_number=1, sample_id=97690458, session_id=160.
But the index was built on six columns when the problem key contains only five.
What is the CON_DBID for the problem key? That's where the trouble lies!
CON_DBID was introduced in Oracle 12.1 and added as a "DEFAULT NOT NULL" column, i.e. that column addition was a metadata-only operation without updating the table blocks (unless _add_col_optim_enabled was not set to FALSE).
SQL> select to_char(property, 'fm0xxxxxxx')
  2    from sys.col$
  3   where obj# = 22800
  4     and name = 'CON_DBID';

TO_CHAR(PROPERTY,'FM0XXXXXX
---------------------------
40000000
Notice that property was 0x40000000 - I speculated that such property was set to columns which were added with default column optimization.
Some MOS notes prove that, i.e. Table Columns Have Wrong Default Value After Transporting Tablespaces (Doc ID 1602994.1).
I dumped the relevant table block and found that CON_DBID column was not present in it.
The pieces of that puzzle started to fit together.
I had constructed a simple test case which reproduced an ORA-8102 error:
SQL> create table t(x int, pad varchar2(100)) enable row movement;
SQL> insert /*+ append*/
  2    into t
  3  select level, lpad('x', 100, 'x')
  4    from dual
  5    connect by level<=1e5;
SQL> commit;
SQL> 
SQL> alter table t add y int default 10 not null;
SQL> 
SQL> create index t_xy_i on t(x,y);
SQL> 
SQL> delete t where x<=1e5/2;
SQL> commit;
SQL> 
SQL> alter table t shrink space;
alter table t shrink space
*
ERROR at line 1:
ORA-08102: index key not found, obj# 91957, file 10, block 3990 (2)
The problem is present in Oracle 11.2.0.4 and more newest versions. I didn't test in earlier 11g patchsets.
Oracle Support raised a new bug: ORA-8102 ON ALTER TABLE SHRINK SPACE WITH ADD COL OPTIMIZATION (unpublished), which is still under investigation.
The tables suspected to an ORA-8102 error can be identified easily by bitand(col$.property, 1073741824)=1073741824 (0x40000000 in hex):
SQL> select o.object_name
  2    from sys.col$ c,
  3         dba_objects o
  4   where bitand(c.property, 1073741824)=1073741824
  5     and o.object_id=c.obj#
  6     and o.owner='SYS'
  7   order by o.object_name;
I restricted above query to SYS schema. Below is the output of that query in one of database which was upgraded from 11.2.0.4 to 12.1.0.2 in the 2015:
SQL> select o.object_name
  2    from sys.col$ c,
  3         dba_objects o
  4   where bitand(c.property, 1073741824)=1073741824
  5     and o.object_id=c.obj#
  6     and o.owner='SYS'
  7   order by o.object_name;

OBJECT_NAME
-------------------------------------------------------------------
CDB_LOCAL_ADMINAUTH$
HISTGRM$
PROFNAME$
WRH$_ACTIVE_SESSION_HISTORY
WRH$_BG_EVENT_SUMMARY
WRH$_BUFFERED_QUEUES
WRH$_BUFFERED_SUBSCRIBERS
WRH$_BUFFER_POOL_STATISTICS
WRH$_CLUSTER_INTERCON
WRH$_COMP_IOSTAT
WRH$_CR_BLOCK_SERVER
WRH$_CURRENT_BLOCK_SERVER
WRH$_DATAFILE
WRH$_DB_CACHE_ADVICE
WRH$_DISPATCHER
WRH$_DLM_MISC
WRH$_DYN_REMASTER_STATS
WRH$_DYN_REMASTER_STATS
WRH$_ENQUEUE_STAT
WRH$_EVENT_HISTOGRAM
WRH$_EVENT_NAME
WRH$_FILEMETRIC_HISTORY
WRH$_FILESTATXS
WRH$_IC_CLIENT_STATS
WRH$_IC_DEVICE_STATS
WRH$_INSTANCE_RECOVERY
WRH$_INST_CACHE_TRANSFER
WRH$_INTERCONNECT_PINGS
WRH$_IOSTAT_DETAIL
WRH$_IOSTAT_FILETYPE
WRH$_IOSTAT_FILETYPE_NAME
WRH$_IOSTAT_FUNCTION
WRH$_IOSTAT_FUNCTION_NAME
WRH$_JAVA_POOL_ADVICE
WRH$_LATCH
WRH$_LATCH_CHILDREN
WRH$_LATCH_MISSES_SUMMARY
WRH$_LATCH_NAME
WRH$_LATCH_PARENT
WRH$_LIBRARYCACHE
WRH$_LOG
WRH$_MEMORY_RESIZE_OPS
WRH$_MEMORY_TARGET_ADVICE
WRH$_MEM_DYNAMIC_COMP
WRH$_METRIC_NAME
WRH$_MTTR_TARGET_ADVICE
WRH$_MUTEX_SLEEP
WRH$_MVPARAMETER
WRH$_OPTIMIZER_ENV
WRH$_OSSTAT
WRH$_OSSTAT_NAME
WRH$_PARAMETER
WRH$_PARAMETER_NAME
WRH$_PERSISTENT_QMN_CACHE
WRH$_PERSISTENT_QUEUES
WRH$_PERSISTENT_SUBSCRIBERS
WRH$_PGASTAT
WRH$_PGA_TARGET_ADVICE
WRH$_PLAN_OPERATION_NAME
WRH$_PLAN_OPTION_NAME
WRH$_PROCESS_MEMORY_SUMMARY
WRH$_RESOURCE_LIMIT
WRH$_ROWCACHE_SUMMARY
WRH$_RSRC_CONSUMER_GROUP
WRH$_RSRC_PLAN
WRH$_RULE_SET
WRH$_SEG_STAT
WRH$_SEG_STAT_OBJ
WRH$_SERVICE_NAME
WRH$_SERVICE_STAT
WRH$_SERVICE_WAIT_CLASS
WRH$_SESSMETRIC_HISTORY
WRH$_SESS_TIME_STATS
WRH$_SESS_TIME_STATS
WRH$_SGA
WRH$_SGASTAT
WRH$_SGA_TARGET_ADVICE
WRH$_SHARED_POOL_ADVICE
WRH$_SHARED_SERVER_SUMMARY
WRH$_SQLCOMMAND_NAME
WRH$_SQLSTAT
WRH$_SQLTEXT
WRH$_SQL_BIND_METADATA
WRH$_SQL_PLAN
WRH$_SQL_SUMMARY
WRH$_SQL_WORKAREA_HISTOGRAM
WRH$_STAT_NAME
WRH$_STREAMS_APPLY_SUM
WRH$_STREAMS_APPLY_SUM
WRH$_STREAMS_CAPTURE
WRH$_STREAMS_CAPTURE
WRH$_STREAMS_POOL_ADVICE
WRH$_SYSMETRIC_HISTORY
WRH$_SYSMETRIC_SUMMARY
WRH$_SYSSTAT
WRH$_SYSTEM_EVENT
WRH$_SYS_TIME_MODEL
WRH$_TABLESPACE
WRH$_TABLESPACE_SPACE_USAGE
WRH$_TABLESPACE_STAT
WRH$_TEMPFILE
WRH$_TEMPSTATXS
WRH$_THREAD
WRH$_TOPLEVELCALL_NAME
WRH$_UNDOSTAT
WRH$_WAITCLASSMETRIC_HISTORY
WRH$_WAITSTAT
WRI$_ADV_SQLT_PLAN_HASH
WRI$_OPTSTAT_HISTGRM_HISTORY
WRI$_SQLSET_BINDS
WRI$_SQLSET_DEFINITIONS
WRI$_SQLSET_MASK
WRI$_SQLSET_PLANS
WRI$_SQLSET_PLAN_LINES
WRI$_SQLSET_STATEMENTS
WRI$_SQLSET_STATISTICS
WRI$_SQLTEXT_REFCOUNT

117 rows selected.
You could see that mostly AWR tables are affected (WRH$%, WRI%). The brand new 12c database's AWR tables won't have property set to 1073741824 (0x40000000), unless you wouldn't create some.
If you set the _add_col_optim_enabled parameter to FALSE, you will be safe.
If you will face a same issue one day, the possible workaround for the issue is to rebuild table in anyway to update the problem column (MOVE table, redefine table online, or set column value equals to self).

понедельник, 30 ноября 2015 г.

V$SQL_PLAN_MONITOR.STARTS is higher than expected due to NLJ batching/Prefetching

Recently one of the developers asked me to explain why V$SQL_PLAN_MONITOR.STARTS is higher than expected for one particular query.
Here is a problem query (some columns are hidden to preserve readability):
SQL Plan Monitoring Details (Plan Hash Value=40624586)
================================================================================================================================================
| Id |                Operation                |        Name        | Execs |   Rows   | Read  | Read  | Activity |      Activity Detail       |
|    |                                         |                    |       | (Actual) | Reqs  | Bytes |   (%)    |        (# samples)         |
================================================================================================================================================
|  0 | SELECT STATEMENT                        |                    |     1 |        0 |       |       |          |                            |
|  1 |   NESTED LOOPS                          |                    |     1 |        0 |       |       |          |                            |
|  2 |    NESTED LOOPS                         |                    |     1 |    62594 |       |       |          |                            |
|  3 |     TABLE ACCESS BY INDEX ROWID BATCHED | MAIN_TABLE         |     1 |    62594 | 22777 | 178MB |    25.89 | Cpu (1)                    |
|    |                                         |                    |       |          |       |       |          | db file parallel read (28) |
|  4 |      INDEX RANGE SCAN                   | MAIN_TABLE_I       |     1 |    62594 |    25 | 200KB |          |                            |
|  5 |     INDEX UNIQUE SCAN                   | CHILD_TABLE_PK     |  109K |    62594 | 63798 | 498MB |    71.43 | Cpu (2)                    |
|    |                                         |                    |       |          |       |       |          | db file parallel read (78) |
|  6 |    TABLE ACCESS BY INDEX ROWID          | CHILD_TABLE        |  105K |        0 |  1402 |  11MB |     1.79 | db file parallel read (2)  |
================================================================================================================================================
You can see that we've obtained 62K rows at step 3, but notice a number of "Execs" at step 5: 109K. It's almost twice as higher than 62K.
Number of rows ("Rows (Actual)") is correct, though.
I checked relevant columns in V$SQL_PLAN_MONITOR view (STARTS/OUTPUT_ROWS) and verified that there's no contradiction between V$SQL_PLAN_MONITOR view and DBMS_SQLTUNE.REPORT_SQL_MONITOR output.
I investigated this issue further and constructed a simple test case which can be used to reproduce the issue.
SQL> create table fact
  2  as
  3  select date '2015-08-01' + trunc(level/4)/86400 fact_date,
  4         lpad('x', 240, 'x') padding,
  5         mod(level, 100000) dim_id
  6    from dual
  7    connect by level<=4*86400;

Table created.

SQL> 
SQL> exec dbms_stats.gather_table_stats( '', 'fact')

PL/SQL procedure successfully completed.

SQL> 
SQL> create table dim
  2  as
  3  select trunc(dbms_random.value(1,100000)) id,
  4         lpad('x', 340, 'x') padding
  5    from dual
  6    connect by level<=2*86400;

Table created.

SQL> 
SQL> create index dim_i on dim(id);

Index created.

SQL> 
SQL> exec dbms_stats.gather_table_stats( '', 'dim')

PL/SQL procedure successfully completed.
I created 2 tables: FACT and DIM, populated them with data and gathered statistics.
Now, I will flush the buffer cache and execute a test query:
SQL> alter system flush buffer_cache;

System altered.

SQL> 
SQL> select /*+ monitor leading(f) use_nl(d) full(f)*/
  2         count(f.padding),
  3         count(d.padding)
  4    from fact f,
  5         dim d
  6   where f.fact_date between to_date('01.08.2015 12:00', 'dd.mm.yyyy hh24:mi') and to_date('01.08.2015 12:10', 'dd.mm.yyyy hh24:mi')
  7     and d.id = f.dim_id;


COUNT(F.PADDING) COUNT(D.PADDING)
---------------- ----------------
            4214             4214

1 row selected.

SQL> 
Let's see the DBMS_SQLTUNE report for the last query:
SQL> select dbms_sqltune.report_sql_monitor from dual;


REPORT_SQL_MONITOR
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report

SQL Text
------------------------------
select /*+ monitor leading(f) use_nl(d) full(f)*/ count(f.padding), count(d.padding) from fact f, dim d where f.fact_date between to_date('01.08.2015 12:00

.. skip..

SQL Plan Monitoring Details (Plan Hash Value=85884857)
===========================================================================================================================================================
| Id |            Operation            | Name  |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity |   Activity Detail    |
|    |                                 |       | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |     (# samples)      |
===========================================================================================================================================================
|  0 | SELECT STATEMENT                |       |         |       |        10 |    +14 |     1 |        1 |      |       |          |                      |
|  1 |   SORT AGGREGATE                |       |       1 |       |        10 |    +14 |     1 |        1 |      |       |          |                      |
|  2 |    NESTED LOOPS                 |       |         |       |        10 |    +14 |     1 |     4214 |      |       |          |                      |
|  3 |     NESTED LOOPS                |       |    5025 | 11890 |        10 |    +14 |     1 |     4214 |      |       |          |                      |
|  4 |      TABLE ACCESS FULL          | FACT  |    2408 |  3535 |        23 |     +1 |     1 |     2404 |  109 | 100MB |     8.70 | Cpu (1)              |
|    |                                 |       |         |       |           |        |       |          |      |       |          | direct path read (1) |
|  5 |      INDEX RANGE SCAN           | DIM_I |       2 |     1 |        10 |    +14 |  2539 |     4214 |  562 |   4MB |          |                      |
|  6 |     TABLE ACCESS BY INDEX ROWID | DIM   |       2 |     4 |        22 |     +2 |  7582 |     4214 | 2810 |  22MB |    91.30 | Cpu (21)             |
===========================================================================================================================================================
Ok, we've just reproduced the issue: row source 4 of the plan generated 2404 rows, but the number of "Execs" (2539) at line 5 is equals to 2539, which is slightly greater.
It was not by accident that I flushed the buffer cache before I execute a query.
If I'll execute a query once again, then dbms_sqltune.report_sql_monitor report has number of "Execs" in line 5 equals to number of rows in line 4:
SQL Plan Monitoring Details (Plan Hash Value=85884857)
======================================================================================================================================================
| Id |            Operation            | Name  |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity | Activity Detail |
|    |                                 |       | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |   (# samples)   |
======================================================================================================================================================
|  0 | SELECT STATEMENT                |       |         |       |         1 |     +0 |     1 |        1 |      |       |          |                 |
|  1 |   SORT AGGREGATE                |       |       1 |       |         1 |     +0 |     1 |        1 |      |       |          |                 |
|  2 |    NESTED LOOPS                 |       |         |       |         1 |     +0 |     1 |     4214 |      |       |          |                 |
|  3 |     NESTED LOOPS                |       |    5025 | 11890 |         1 |     +0 |     1 |     4214 |      |       |          |                 |
|  4 |      TABLE ACCESS FULL          | FACT  |    2408 |  3535 |         1 |     +0 |     1 |     2404 |  108 | 100MB |          |                 |
|  5 |      INDEX RANGE SCAN           | DIM_I |       2 |     1 |         1 |     +0 |  2404 |     4214 |      |       |          |                 |
|  6 |     TABLE ACCESS BY INDEX ROWID | DIM   |       2 |     4 |         1 |     +0 |  4214 |     4214 |      |       |          |                 |
======================================================================================================================================================
I believe, that differences in execs/rows were caused by NLJ batching/Table prefetching.
How can we prove it?
First, I'll show how changes "physical reads cache prefetch" before/after query execution.
SQL> select n.name, s.value
  2    from v$statname n, v$mystat s
  3   where n.name like '%prefetch%'
  4     and s.statistic#=n.statistic#
  5     and s.value>0
  6   order by n.name;


NAME                                VALUE
------------------------------ ----------
physical reads cache prefetch      127987

1 row selected.

SQL> 
SQL> select /*+ monitor leading(f) use_nl(d) full(f)*/
  2         count(f.padding),
  3         count(d.padding)
  4    from fact f,
  5         dim d
  6   where f.fact_date between to_date('01.08.2015 12:00', 'dd.mm.yyyy hh24:mi') and to_date('01.08.2015 12:10', 'dd.mm.yyyy hh24:mi')
  7     and d.id = f.dim_id;


COUNT(F.PADDING) COUNT(D.PADDING)
---------------- ----------------
            4214             4214

1 row selected.

SQL> 
SQL> select n.name, s.value
  2    from v$statname n, v$mystat s
  3   where n.name like '%prefetch%'
  4     and s.statistic#=n.statistic#
  5     and s.value>0
  6   order by n.name;


NAME                                VALUE
------------------------------ ----------
physical reads cache prefetch      131321

1 row selected.
Statistic increased by the 131321-127987=3334 blocks. Second, when I disable nlj_batching, there are no differences in execs/rows:
SQL> alter system flush buffer_cache;

System altered.

SQL> 
SQL> select n.name, s.value
  2    from v$statname n, v$mystat s
  3   where n.name like '%prefetch%'
  4     and s.statistic#=n.statistic#
  5     and s.value>0
  6   order by n.name;


NAME                                VALUE
------------------------------ ----------
physical reads cache prefetch      137989

1 row selected.

SQL> 
SQL> select /*+ monitor leading(f) use_nl(d) full(f) opt_param('_nlj_batching_enabled' 0)*/
  2         count(f.padding),
  3         count(d.padding)
  4    from fact f,
  5         dim d
  6   where f.fact_date between to_date('01.08.2015 12:00', 'dd.mm.yyyy hh24:mi') and to_date('01.08.2015 12:10', 'dd.mm.yyyy hh24:mi')
  7     and d.id = f.dim_id;


COUNT(F.PADDING) COUNT(D.PADDING)
---------------- ----------------
            4214             4214

1 row selected.

SQL> 
SQL> select n.name, s.value
  2    from v$statname n, v$mystat s
  3   where n.name like '%prefetch%'
  4     and s.statistic#=n.statistic#
  5     and s.value>0
  6   order by n.name;


NAME                                VALUE
------------------------------ ----------
physical reads cache prefetch      137989

1 row selected.

SQL> 
SQL> select dbms_sqltune.report_sql_monitor from dual;


REPORT_SQL_MONITOR
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report

SQL Text
------------------------------
select /*+ monitor leading(f) use_nl(d) full(f) opt_param('_nlj_batching_enabled' 0)*/ count(f.padding), count(d.padding) from fact f, dim d where f.fact_date be
 and d.id = f.dim_id

.. skip ..

SQL Plan Monitoring Details (Plan Hash Value=1381503666)
=================================================================================================================================================================
| Id |           Operation            | Name  |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity |       Activity Detail       |
|    |                                |       | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |         (# samples)         |
=================================================================================================================================================================
|  0 | SELECT STATEMENT               |       |         |       |         1 |     +2 |     1 |        1 |      |       |          |                             |
|  1 |   SORT AGGREGATE               |       |       1 |       |         1 |     +2 |     1 |        1 |      |       |          |                             |
|  2 |    TABLE ACCESS BY INDEX ROWID | DIM   |       2 |     4 |         2 |     +1 |     1 |     4214 | 3360 |  26MB |   100.00 | db file sequential read (2) |
|  3 |     NESTED LOOPS               |       |    5025 | 11890 |         1 |     +2 |     1 |     6619 |      |       |          |                             |
|  4 |      TABLE ACCESS FULL         | FACT  |    2408 |  3535 |         1 |     +2 |     1 |     2404 |  109 | 100MB |          |                             |
|  5 |      INDEX RANGE SCAN          | DIM_I |       2 |     1 |         1 |     +2 |  2404 |     4214 |   12 | 98304 |          |                             |
=================================================================================================================================================================


1 row selected.
Or, if we want to obtain "more-classic" NLJ plan shape (I added no_nlj_prefetch(d) hint):
SQL> alter system flush buffer_cache;

System altered.

SQL> 
SQL> select n.name, s.value
  2    from v$statname n, v$mystat s
  3   where n.name like '%prefetch%'
  4     and s.statistic#=n.statistic#
  5     and s.value>0
  6   order by n.name;


NAME                                VALUE
------------------------------ ----------
physical reads cache prefetch      137989

1 row selected.

SQL> 
SQL> select /*+ monitor leading(f) use_nl(d) full(f) opt_param('_nlj_batching_enabled' 0) no_nlj_prefetch(d)*/
  2         count(f.padding),
  3         count(d.padding)
  4    from fact f,
  5         dim d
  6   where f.fact_date between to_date('01.08.2015 12:00', 'dd.mm.yyyy hh24:mi') and to_date('01.08.2015 12:10', 'dd.mm.yyyy hh24:mi')
  7     and d.id = f.dim_id;


COUNT(F.PADDING) COUNT(D.PADDING)
---------------- ----------------
            4214             4214

1 row selected.

SQL> 
SQL> select n.name, s.value
  2    from v$statname n, v$mystat s
  3   where n.name like '%prefetch%'
  4     and s.statistic#=n.statistic#
  5     and s.value>0
  6   order by n.name;


NAME                                VALUE
------------------------------ ----------
physical reads cache prefetch      137989

1 row selected.

SQL> 
SQL> select dbms_sqltune.report_sql_monitor from dual;


REPORT_SQL_MONITOR
------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report

SQL Text
------------------------------
select /*+ monitor leading(f) use_nl(d) full(f) opt_param('_nlj_batching_enabled' 0) no_nlj_prefetch(d)*/ count(f.padding), count(d.padding) from fact f, dim d wh
d.mm.yyyy hh24:mi') and d.id = f.dim_id

.. skip ..

SQL Plan Monitoring Details (Plan Hash Value=676372893)
==================================================================================================================================================================
| Id |            Operation            | Name  |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity |       Activity Detail       |
|    |                                 |       | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |         (# samples)         |
==================================================================================================================================================================
|  0 | SELECT STATEMENT                |       |         |       |         1 |     +2 |     1 |        1 |      |       |          |                             |
|  1 |   SORT AGGREGATE                |       |       1 |       |         1 |     +2 |     1 |        1 |      |       |          |                             |
|  2 |    NESTED LOOPS                 |       |    5025 | 11890 |         1 |     +2 |     1 |     4214 |      |       |          |                             |
|  3 |     TABLE ACCESS FULL           | FACT  |    2408 |  3535 |         1 |     +2 |     1 |     2404 |  109 | 100MB |          |                             |
|  4 |     TABLE ACCESS BY INDEX ROWID | DIM   |       2 |     4 |         2 |     +2 |  2404 |     4214 | 3360 |  26MB |    66.67 | Cpu (1)                     |
|    |                                 |       |         |       |           |        |       |          |      |       |          | db file sequential read (1) |
|  5 |      INDEX RANGE SCAN           | DIM_I |       2 |     1 |         1 |     +2 |  2404 |     4214 |   12 | 98304 |          |                             |
==================================================================================================================================================================
I have searched for a similar issues through MOS and found a Bug 13634445 : V$SQL_PLAN_MONITOR AND NL BATCHING IN 11G, which has status 92 "Closed, Not a Bug".
Another interesting observation is that by adding a "gather_plan_statistics" hint, I'll disable prefetching for that case and fix V$SQL_PLAN_MONITOR "discrepancy"
SQL> alter system flush buffer_cache;

System altered.

SQL> 
SQL> select /*+ monitor leading(f) use_nl(d) full(f) gather_plan_statistics*/
  2         count(f.padding),
  3         count(d.padding)
  4    from fact f,
  5         dim d
  6   where f.fact_date between to_date('01.08.2015 12:00', 'dd.mm.yyyy hh24:mi') and to_date('01.08.2015 12:10', 'dd.mm.yyyy hh24:mi')
  7     and d.id = f.dim_id;


COUNT(F.PADDING) COUNT(D.PADDING)
---------------- ----------------
            4214             4214

1 row selected.

SQL> 
SQL> select dbms_sqltune.report_sql_monitor from dual;


REPORT_SQL_MONITOR
------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report

SQL Text
------------------------------
select /*+ monitor leading(f) use_nl(d) full(f) gather_plan_statistics*/ count(f.padding), count(d.padding) from fact f, dim d where f.fact_date between to_date('
dim_id

..skip..

SQL Plan Monitoring Details (Plan Hash Value=85884857)
==================================================================================================================================================================
| Id |            Operation            | Name  |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity |       Activity Detail       |
|    |                                 |       | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |         (# samples)         |
==================================================================================================================================================================
|  0 | SELECT STATEMENT                |       |         |       |         8 |     +2 |     1 |        1 |      |       |          |                             |
|  1 |   SORT AGGREGATE                |       |       1 |       |         8 |     +2 |     1 |        1 |      |       |          |                             |
|  2 |    NESTED LOOPS                 |       |         |       |         8 |     +2 |     1 |     4214 |      |       |          |                             |
|  3 |     NESTED LOOPS                |       |    5025 | 11890 |         8 |     +2 |     1 |     4214 |      |       |          |                             |
|  4 |      TABLE ACCESS FULL          | FACT  |    2408 |  3535 |         9 |     +1 |     1 |     2404 |  109 | 100MB |    11.11 | direct path read (1)        |
|  5 |      INDEX RANGE SCAN           | DIM_I |       2 |     1 |         8 |     +2 |  2404 |     4214 |   12 | 98304 |          |                             |
|  6 |     TABLE ACCESS BY INDEX ROWID | DIM   |       2 |     4 |         8 |     +2 |  4214 |     4214 | 3360 |  26MB |    88.89 | db file sequential read (8) |
==================================================================================================================================================================

четверг, 29 октября 2015 г.

12c: PX Auto DOP without IO Calibration

I have upgraded one of data warehouse database this week and noticed changes in behaviour of PX Auto DOP feature.
According to the documentation of the parallel_degree_policy parameter Oracle Database Reference 11g:
  • MANUAL
    Disables automatic degree of parallelism, statement queuing, and in-memory parallel execution. This reverts the behavior of parallel execution to what it was prior to Oracle Database 11g Release 2 (11.2). This is the default.
  • LIMITED
    Enables automatic degree of parallelism for some statements but statement queuing and in-memory Parallel Execution are disabled. Automatic degree of parallelism is only applied to those statements that access tables or indexes decorated explicitly with the DEFAULT degree of parallelism using the PARALLEL clause. Statements that do not access any tables or indexes decorated with the DEFAULT degree of parallelism will retain the MANUAL behavior.
  • AUTO
    Enables automatic degree of parallelism, statement queuing, and in-memory parallel execution.
In short:
MANUAL - disables Auto DOP, parallel statement queuing, and in-memory parallel execution.
AUTO - enables all of the above.
What if you need parallel statement queuing but dont need Auto DOP?
MOS note suggests to add hint STATEMENT_QUEUING in your statements manually: How to Achieve Parallel Statement Queuing for an SQL When PARALLEL_DEGREE_POLICY=MANUAL (Doc ID 1902069.1)
In 11g there was an clever automatic way to achieve this:
VLDB and Partitioning Guide 11g
When PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database determines whether the statement should run in parallel based on the cost of the operations in the execution plan and the hardware characteristics.
The hardware characteristics include I/O calibration statistics so these statistics must be gathered otherwise Oracle Database does not use the automatic degree policy feature.

So I simply installed PARALLEL_DEGREE_POLICY=AUTO and didn't gather IO calibration statistics.
This prevents Auto DOP but parallel statement queuing and in-memory parallel execution still enabled.
Now in 12c things got changed:
VLDB and Partitioning Guide 12c
When PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database determines whether the statement should run in parallel based on the cost of the operations in the execution plan and the hardware characteristics.
The hardware characteristics include I/O calibration statistics so these statistics should be gathered.
If I/O calibration is not run to gather the required statistics, a default calibration value is used to calculate the cost of operations and the degree of parallelism.

Have you seen the difference?
It means that now Auto DOP works without IO calibration statistics.
I dont see any announcement in the New Features Guide or VLDB and Partitioning Guide about this change.
Yes, I agree that configuration with PARALLEL_DEGREE_POLICY=AUTO without IO calibration statistics looks an unusual thing.
But I would prefer that Oracle mentions such things at least in the "Changes in this release" section in the relevant book.
I.e. here.

среда, 30 сентября 2015 г.

Bug 20214168 - Wrong Results using aggregations of CASE expression with fix of bug 20003240 present

I started to use Oracle 12c in the early 2014 in non-production environments.
At now I am extensively testing the ADO, In-Memory functionalities and have couple of database in production.
They already use DBBP (Database Bundle Patch) 10 for engineering systems and database In-Memory and have several underscope parameters in effect.
Unfortunately, a few days ago we have faced with the wrong results caused by the Bug 20214168 - Wrong Results using aggregations of CASE expression with fix of bug 20003240 present.
This problem was already discussed on the freelist oracle-l thread Strange Behaviour (with Test Case).
Original thread was Exadata specific.
We dont have an Exadata yet but we use DBIM (Database In-Memory) and so we install DBBP patches.
I found out that solution from the thread with event 10055 don't help to resolve wrong results issue in our environment.
Let's create test table and data from the original bug:
SQL> create table test_fact(chrtype varchar2(3), rate number);
SQL> insert into test_fact values('R03', 1.3);
SQL> insert into test_fact values('LDU', 0.21);
SQL> 
SQL> select *
  2    from test_fact;

CHRTYPE         RATE
--------- ----------
R03              1.3
LDU              .21
Now execute below query:
SQL> select sum(
  2           case
  3             when chrtype in ('R03', 'LDU')
  4             then rate/10
  5           end) result
  6    from test_fact;

    RESULT
----------
      .041
Notice wrong results. Above query should return 0.151 and not 0.041.
Now we will execute same query with the event 10055 set:
SQL> alter session set events '10055 trace name context forever, level 0x200';
SQL> 
SQL> select sum(
  2           case
  3             when chrtype in ('R03', 'LDU')
  4             then rate/10
  5           end) result
  6    from test_fact;

    RESULT
----------
      .041
Notice that results are the same (they are wrong) and the event 10055 set to level 0x200 dont help.
I observed these results in the environment with Patch 21188742 - Database Patch for Engineered Systems and DB In-Memory 12.1.0.2.10 (Jul2015) applied.
May be other levels of event 10055 could help?
[oracle@localhost ~]$ oerr ora 10055
10055, 00000, "Rowsets: turn off rowsets for various operations"
// *Document: NO
// *Cause:    N/A
// *Action:   Turns off rowsets for various operations
//            Level:
//            0x00000001 - turn off for table scan
//            0x00000002 - turn off for hash join consume
//            0x00000004 - turn off for hash join produce
//            0x00000008 - turn off for group by
//            0x00000010 - turn off for sort
//            0x00000020 - turn off for table-queue out
//            0x00000040 - turn off for table-queue in
//            0x00000080 - turn off for identity
//            0x00000100 - turn off for granule iterator
//            0x00000200 - turn off for EVA functions
//            0x00000400 - turn off for PL/SQL
//            0x00000800 - turn off for upgrade
//            0x00001000 - turn off for database startup
//            0x00002000 - turn off for blobs and clobs
//            0x00004000 - turn off for tracing row source
//            0x00008000 - turn off rowset information in explain plan
//            0x00010000 - disable hash join rowsets fast path
//            0x00020000 - turn off for bloom create
//            0x00040000 - turn off for bloom use
//            0x00080000 - disable prefetch for hash join
//            0x00100000 - disable prefetch for bloom
//            0x00200000 - disable semi blocking hash join
//            0x00400000 - turn off rowset for fixed table
//
Unfortunately, other levels also have not helped.
I found 2 possible solutions for this problem case.
1. set _rowsets_enabled to false:
SQL> select sum(
  2           case
  3             when chrtype in ('R03', 'LDU')
  4             then rate/10
  5           end) result
  6    from test_fact;

    RESULT
----------
      .041
SQL> 
SQL> alter session set "_rowsets_enabled"=false;
SQL> 
SQL> select sum(
  2           case
  3             when chrtype in ('R03', 'LDU')
  4             then rate/10
  5           end) result
  6    from test_fact;

    RESULT
----------
      .151
2. set _rowsets_max_rows to 1:
SQL> select sum(
  2           case
  3             when chrtype in ('R03', 'LDU')
  4             then rate/10
  5           end) result
  6    from test_fact;

    RESULT
----------
      .041
SQL> 
SQL> alter session set "_rowsets_max_rows"=1;
SQL> 
SQL> select sum(
  2           case
  3             when chrtype in ('R03', 'LDU')
  4             then rate/10
  5           end) result
  6    from test_fact;

    RESULT
----------
      .151
Off course, we shouldn't use underscope parameters without Oracle Support agreement.
Instead we should apply the fix for bug 20214168 ASAP.
I tried to apply the patch 20214168 to this Oracle Home but OPatch informed me that patch already applied!
Recommended actions: The fixes by this patch are currently in the Oracle Home. There is no need to apply this patch.
Patch : 20214168

        Bug SubSet of 21125181
        Subset bugs are:
        20214168
From the opatch lsinv I saw that definitely fix for bug 20214168 already installed:
Local Machine Information::
Hostname: localhost
ARU platform id: 23
ARU platform description:: Solaris Operating System (SPARC 64-bit)
                
Installed Top-level Products (1): 
Oracle Database 12c                                                  12.1.0.2.0There are 1 products installed in this Oracle Home.
Interim patches (5) :
Patch  20831113     : applied on Wed Aug 05 15:01:24 NOVT 2015
Unique Patch ID:  18927529
Patch description:  "OCW Patch Set Update : 12.1.0.2.4 (20831113)"
   Created on 23 Jun 2015, 06:58:08 hrs UTC
   Bugs fixed:
     18589889, 19139608, 19280860, 19061429, 19133945, 19341538, 20011424
...skip...
Patch  21125181     : applied on Wed Aug 05 14:50:35 NOVT 2015
Unique Patch ID:  19005983
Patch description:  "DATABASE BUNDLE PATCH: 12.1.0.2.10 (21125181)"
   Created on 1 Jul 2015, 22:01:24 hrs PST8PDT
Sub-patch  20594149; "DATABASE BUNDLE PATCH: 12.1.0.2.7 (20594149)"
Sub-patch  20415006; "DATABASE BUNDLE PATCH: 12.1.0.2.6 (20415006)"
Sub-patch  20243804; "DATABASE BUNDLE PATCH: 12.1.0.2.5 (20243804)"
   Bugs fixed:
...skip...
     19990543, 19012044, 20214168, 20209481, 18885870, 13640676, 13498243
...skip...
Patch  19396455     : applied on Mon Jul 13 17:43:01 NOVT 2015
Unique Patch ID:  18154832
   Created on 15 Oct 2014, 20:19:28 hrs PST8PDT
   Bugs fixed:
     19396455
Patch  19567916     : applied on Mon Jul 13 17:41:37 NOVT 2015
Unique Patch ID:  18878751
   Created on 1 May 2015, 01:31:51 hrs PST8PDT
   Bugs fixed:
     19567916
Patch  20879889     : applied on Mon Jul 13 17:33:26 NOVT 2015
Unique Patch ID:  18969474
   Created on 27 May 2015, 10:30:29 hrs PST8PDT
   Bugs fixed:
     20879889
I have found that bug 21214168 incorrectly informed as fixed but actually it's not fixed: Bug 21553476 - Wrong Results using aggregations of CASE expression with fix of bug 20003240 present in Exadata (Doc ID 21553476.8).
So instead of install patch 21214168, we should install Patch 21553476: EXADATA X5-2 RESULTS WRONG NUMERIC CALCULATION.
I had installed patch 21553476 and problem was resolved after that!
SQL> select sum(
  2           case
  3             when chrtype in ('R03', 'LDU')
  4             then rate/10
  5           end) result
  6    from test_fact
  7  /

    RESULT
----------
      .151

понедельник, 17 августа 2015 г.

EM 12.1.0.4 Installing recommended Plug-Ins and Patches

In one of the project we will use DBaaS (Database as a Service) functionality of EM Cloud Control 12c.
We faced with a multiple issue and decided to upgrade EM and install Oracle recommended patches from note:
Enterprise Manager Cloud Control 12c Recommended Plug-Ins and Patches for Database as a Service (DBaaS) (Doc ID 1549855.1)
Specifically:
Patch 20870437: ENTERPRISE MANAGER BASE PLATFORM - OMS 12.1.0.4.4 PSU (GENERIC)
and
Patch 21415432: enterprise manager for oms plugins 12.1.0.4.14
We had vanilla EM 12.1.0.4 installation without any PSU or patches (except Cloud Framework Plug-In version 12.1.0.2.0).

ENTERPRISE MANAGER BASE PLATFORM - OMS 12.1.0.4.4 PSU
At the step of installation:
2.9 Run the following command on each OMS instance home to ensure that you do not have any issues with configuration and binary prerequisite checks,
I encountered an error:
[oracle@mgmt 20870437]$ 
/u01/app/oracle/oms12/oms/OPatch/opatchauto apply -analyze 
-property_file /u01/app/oracle/oms12/oms/OPatch/wlskeys/property_file 
-invPtrLoc /u01/app/oracle/oms12/oms/oraInst.loc  
OPatchAuto.OMS_DISABLE_HOST_CHECK=true
OPatch Automation Tool
Copyright (c) 2014, Oracle Corporation.  All rights reserved.
  
  
OPatchauto version : 11.1.0.10.4
OUI version        : 11.1.0.12.0
Running from       : /u01/app/oracle/oms12/oms
Log file location  : /u01/app/oracle/oms12/oms/cfgtoollogs/opatch/opatch2015-08-07_17-00-41PM_1.log
  
OPatchauto log file: /u01/app/oracle/oms12/oms/cfgtoollogs/opatchauto/20870437/opatch_oms_2015-08-07_17-00-43PM_analyze.log
  
  
  
OPatchauto
 failed to establish JMX connection to weblogic server. This could be 
because of one (or) more of the following reasons:
1. Weblogic admin server URL that manages OMS application may not be right.
2. Weblogic admin server credentials (username, password) may not be right.
3.
 Virtual host configuration. If OMS, weblogic server are on virtual host
 configuration, Please make sure to add 
OPatchAuto.OMS_DISABLE_HOST_CHECK=true to command line and run again. 
(example: /u01/app/oracle/oms12/oms/OPatch/opatchauto apply -analyze 
-property_file /u01/app/oracle/oms12/oms/OPatch/wlskeys/property_file 
-invPtrLoc /u01/app/oracle/oms12/oms/oraInst.loc 
OPatchAuto.OMS_DISABLE_HOST_CHECK=true 
OPatchAuto.OMS_DISABLE_HOST_CHECK=true)
  
Please check above conditions and if error(s) still persist, Please contact Oracle support.
  
  
[ Error during Get weblogic Admin Server information Phase]. Detail: OPatchauto was not able to find right interview inputs.
OPatchauto failed:
OPatchauto
 failed to establish JMX connection to weblogic server. This could be 
because of one (or) more of the following reasons:
1. Weblogic admin server URL that manages OMS application may not be right.
2. Weblogic admin server credentials (username, password) may not be right.
3.
 Virtual host configuration. If OMS, weblogic server are on virtual host
 configuration, Please make sure to add 
OPatchAuto.OMS_DISABLE_HOST_CHECK=true to command line and run again. 
(example: /u01/app/oracle/oms12/oms/OPatch/opatchauto apply -analyze 
-property_file /u01/app/oracle/oms12/oms/OPatch/wlskeys/property_file 
-invPtrLoc /u01/app/oracle/oms12/oms/oraInst.loc 
OPatchAuto.OMS_DISABLE_HOST_CHECK=true 
OPatchAuto.OMS_DISABLE_HOST_CHECK=true)
  
Please check above conditions and if error(s) still persist, Please contact Oracle support.
  
Log file location: /u01/app/oracle/oms12/oms/cfgtoollogs/opatchauto/20870437/opatch_oms_2015-08-07_17-00-43PM_analyze.log
  
Recommended actions: Please correct the interview inputs and run opatchauto again.
  
OPatchauto failed with error code 231
[oracle@mgmt 20870437]$
I searched through MOS knowledge base and have decided that this error due to the old OPatch version.
I found a note with a same error:
EM12c : OPatchauto fails with : opatchauto finds that weblogic credentials may not be right or admin server is down (Doc ID 1644317.1)
From which I concluded that issue should be resolved after OPatch 11.1.0.11 install.
As I seen from the logs, we have used OPatch version 11.1.0.10.4.
I installed latest OPatch version 11.1.0.x and installation of PSU was ended without any issues.

Enterprise Manager for OMS Plug-Ins 12.1.0.4.14
opatchauto apply -analyze command showed that from 18 sub-patches I could install only 1.
[oracle@mgmt 21415432]$ /u01/app/oracle/oms12/oms/OPatch/opatchauto apply -analyze -invPtrLoc /u01/app/oracle/oms12/oms/oraInst.loc  OPatchAuto.OMS_DISABLE_HOST_CHECK=true
OPatch Automation Tool
Copyright (c) 2014, Oracle Corporation.  All rights reserved.
 
OPatchauto version : 11.1.0.12.3
OUI version        : 11.1.0.12.0
Running from       : /u01/app/oracle/oms12/oms
Log file location  : /u01/app/oracle/oms12/oms/cfgtoollogs/opatch/opatch2015-08-10_15-52-13PM_1.log
 
OPatchauto log file: /u01/app/oracle/oms12/oms/cfgtoollogs/opatchauto/21415432/opatch_oms_2015-08-10_15-52-16PM_analyze.log
 
Please enter OMS weblogic admin server URL(t3s://mgmt.localdomain:7102):>
Please enter OMS weblogic admin server username:> weblogic
Please enter OMS weblogic admin server password:>
 
WARNING: Sub-patch "19060193" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "19513319" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "19512418" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "20466650" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "20623942" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "20950018" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "20950277" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21167953" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21167965" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21167991" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21174690" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21185095" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21324604" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21324646" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21324654" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21324861" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21324890" skipped for incompatibility with components installed in the OMS system.
 
Configuration Validation: Success
 
Running apply prerequisite checks for sub-patch(es) "21167573" and Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.cfw.oms.plugin_12.1.0.2.0"...
Please monitor OPatch log file: /u01/app/oracle/oms12/plugins/oracle.sysman.cfw.oms.plugin_12.1.0.2.0/cfgtoollogs/opatch/21167573_Aug_10_2015_15_52_13/ApplyPrereq2015-08-10_15-53-08PM_2.log
Sub-patch(es) "21167573" are successfully analyzed for Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.cfw.oms.plugin_12.1.0.2.0"
 
Complete Summary
================
 
All log file names referenced below can be accessed from the directory "/u01/app/oracle/oms12/oms/cfgtoollogs/opatch/2015-08-10_15-52-13PM_SystemPatch_21415432_1"
 
Prerequisites analysis summary:
-------------------------------
  
The following sub-patch(es) are applicable:
 
  Oracle Home Name   Sub-patches                                          Log file
  ----------------   -----------                                          --------
         OraHome18      21167573   21167573_ApplyPrereq2015-08-10_15-53-08PM_2.log
 
The following sub-patches are incompatible with components installed in the OMS system:
19060193,19513319,19512418,20466650,20623942,20950018,20950277,21167953,21167965,21167991,21174690,21185095,21324604,21324646,21324654,21324861,21324890
 
--------------------------------------------------------------------------------
 
The following warnings have occurred during OPatchauto execution:
1)  Sub-patch "19060193" skipped for incompatibility with components installed in the OMS system.
2)  Sub-patch "19513319" skipped for incompatibility with components installed in the OMS system.
3)  Sub-patch "19512418" skipped for incompatibility with components installed in the OMS system.
4)  Sub-patch "20466650" skipped for incompatibility with components installed in the OMS system.
5)  Sub-patch "20623942" skipped for incompatibility with components installed in the OMS system.
6)  Sub-patch "20950018" skipped for incompatibility with components installed in the OMS system.
7)  Sub-patch "20950277" skipped for incompatibility with components installed in the OMS system.
8)  Sub-patch "21167953" skipped for incompatibility with components installed in the OMS system.
9)  Sub-patch "21167965" skipped for incompatibility with components installed in the OMS system.
10)  Sub-patch "21167991" skipped for incompatibility with components installed in the OMS system.
11)  Sub-patch "21174690" skipped for incompatibility with components installed in the OMS system.
12)  Sub-patch "21185095" skipped for incompatibility with components installed in the OMS system.
13)  Sub-patch "21324604" skipped for incompatibility with components installed in the OMS system.
14)  Sub-patch "21324646" skipped for incompatibility with components installed in the OMS system.
15)  Sub-patch "21324654" skipped for incompatibility with components installed in the OMS system.
16)  Sub-patch "21324861" skipped for incompatibility with components installed in the OMS system.
17)  Sub-patch "21324890" skipped for incompatibility with components installed in the OMS system.
--------------------------------------------------------------------------------
 
OPatchauto Session completed with warnings.
 
Log file location: /u01/app/oracle/oms12/oms/cfgtoollogs/opatchauto/21415432/opatch_oms_2015-08-10_15-52-16PM_analyze.log
 
OPatchauto completed with warnings.
 
[oracle@mgmt 21415432]$
Most of the patches was skipped due to incompatibility checks:
WARNING: Sub-patch "21324890" skipped for incompatibility with components installed in the OMS system.
I searched through MOS knowledge base and found a note:
EM 12c: Applying a System Patch to the Enterprise Manager 12.1.0.4 Cloud Control OMS Oracle Home Fails at Analyze with Error: Sub-patch skipped for incompatibility with components installed in the OMS system (Doc ID 1921337.1) which tell us that:

Such incompatibility errors result from two scenarios:
Case 1:The Bundle Patch is comprised of patches for the OMS Oracle Home as well as Plug-in Homes. If one of these Plugins is not present in the environment, then these patches would be skipped automatically as they are not needed.
Case 2: If the plug-in is present but is of a lower version than the plug-in versions mentioned above, then such errors can also result.


Patch 21415432 (Enterprise Manager for OMS Plug-Ins 12.1.0.4.14) contains patches for the below list of plugins:
  • Enterprise Manager for Cloud: 12.1.0.8.0
  • Enterprise Manager for Fusion Middleware: 12.1.0.6.0
  • Enterprise Manager for Oracle Database: 12.1.0.6.0
  • Enterprise Manager for Fusion Applications: 12.1.0.6.0
  • Enterprise Manager for Storage Management Framework: 12.1.0.4.0
  • Enterprise Manager for Siebel: 12.1.0.5.0
  • Enterprise Manager for Exadata : 12.1.0.6.0
  • Enterprise Manager for Oracle Virtual Infrastructure: 12.1.0.1.0
  • Enterprise Manager for Virtualization: 12.1.0.6.0
  • Enterprise Manager for My Oracle Support: 12.1.0.6.0
  • Enterprise Manager for Chargeback and Capacity Planning: 12.1.0.6.0
  • Enterprise Manager for Apache Tomcat: 12.1.0.3.0
  • Enterprise Manager for Oracle Database: 12.1.0.7.0
  • Enterprise Manager for Storage Management Framework: 12.1.0.5.0
  • Enterprise Manager for Cloud: 12.1.0.9.0
  • Enterprise Manager for Fusion Middleware: 12.1.0.7.0
Reading further the note
EM 12c: Applying a System Patch to the Enterprise Manager 12.1.0.4 Cloud Control OMS Oracle Home Fails at Analyze with Error: Sub-patch skipped for incompatibility with components installed in the OMS system (Doc ID 1921337.1), I have found the emcli commands which can be used to verify versions of plugins installed on the OMS:
./emcli login -username=sysman
./emcli sync
./emcli list_plugins_on_server
I run above commands and found that I have plugin versions which should be patched by the Patch 21415432 (I highlight DB and SMF plugins for simplicity):
OMS name is mgmt.localdomain:4889_Management_Service
Plug-in Name                        Plugin-id                   Version [revision]
Oracle Cloud Framework              oracle.sysman.cfw           12.1.0.2.0
Oracle Database                     oracle.sysman.db            12.1.0.6.0
Oracle Fusion Middleware            oracle.sysman.emas          12.1.0.6.0
Oracle MOS (My Oracle Support)      oracle.sysman.mos           12.1.0.6.0
Oracle Storage Management Framework oracle.sysman.smf           12.1.0.4.0
Oracle Cloud Application            oracle.sysman.ssa           12.1.0.8.0
Oracle Virtual Infrastructure       oracle.sysman.vi            12.1.0.1.0
Oracle Virtualization               oracle.sysman.vt            12.1.0.6.0
Oracle Exadata                      oracle.sysman.xa            12.1.0.6.0
I continued searching through MOS and found the Bug 21287619 : OPATCHAUTO UNABLE TO IDENTIFY INSTALLED PLUGIN_HOME which was without the resolution but provided some ideas for further investigations.
I started to check them.
At first, I verified that I have correct plugin versions in PLUGIN_HOME:
[oracle@mgmt 21415432]$ ls -la /u01/app/oracle/oms12/plugins/
drwxr-xr-x 14 oracle oinstall 4096 Aug  4 15:05 .
drwxr-x--- 16 oracle oinstall 4096 Jul 11  2014 ..
drwxr-xr-x  2 oracle oinstall 4096 Aug  4 15:31 backup
drwxr-xr-x 12 oracle oinstall 4096 Jul 11  2014 oracle.sysman.cfw.oms.plugin_12.1.0.1.0
drwxr----- 14 oracle oinstall 4096 Aug 10 14:58 oracle.sysman.cfw.oms.plugin_12.1.0.2.0
drwxr-xr-x 18 oracle oinstall 4096 Aug  4 15:03 oracle.sysman.db.oms.plugin_12.1.0.6.0
drwxr-xr-x 18 oracle oinstall 4096 Jul 11  2014 oracle.sysman.emas.oms.plugin_12.1.0.6.0
drwxr-xr-x 13 oracle oinstall 4096 Jul 11  2014 oracle.sysman.mos.oms.plugin_12.1.0.6.0
drwxr-xr-x 12 oracle oinstall 4096 Jul 11  2014 oracle.sysman.smf.oms.plugin_12.1.0.4.0
drwxr-xr-x 14 oracle oinstall 4096 Jul 11  2014 oracle.sysman.ssa.oms.plugin_12.1.0.8.0
drwxr-xr-x 12 oracle oinstall 4096 Jul 11  2014 oracle.sysman.vi.oms.plugin_12.1.0.1.0
drwxr-xr-x 15 oracle oinstall 4096 Jul 11  2014 oracle.sysman.vt.oms.plugin_12.1.0.6.0
drwxr-xr-x 13 oracle oinstall 4096 Jul 11  2014 oracle.sysman.xa.oms.plugin_12.1.0.6.0
drwxr-xr-x  2 oracle oinstall 4096 Jul 11  2014 tmp
I reviewed opatch/opatchauto lspatches output:
[oracle@mgmt 21415432]$ opatch lspatches
20870437;ENTERPRISE MANAGER BASE PLATFORM - OMS 12.1.0.4.4 PSU (GENERIC)
[oracle@mgmt 21415432]$ opatchauto lspatches
OPatch Automation Tool
Copyright (c) 2014, Oracle Corporation.  All rights reserved.
Oracle Home:/u01/app/oracle/oms12/oms
20870437;ENTERPRISE MANAGER BASE PLATFORM - OMS 12.1.0.4.4 PSU (GENERIC)
The following groups of patch(es) are applied as System Patch bundle(s):
20870437
For more details on installed patch(es) in platform, plugin homes, Please do "/u01/app/oracle/oms12/oms/OPatch/opatch lsinventory -details -oh <desired home path>".
opatch lsinv output:
[oracle@mgmt 21415432]$ opatch lsinv
Oracle Interim Patch Installer version 11.1.0.12.7
Copyright (c) 2015, Oracle Corporation.  All rights reserved.
Oracle Home       : /u01/app/oracle/oms12/oms
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/oms12/oms/oraInst.loc
OPatch version    : 11.1.0.12.7
OUI version       : 11.1.0.12.0
Log file location : /u01/app/oracle/oms12/oms/cfgtoollogs/opatch/opatch2015-08-11_08-41-48AM_1.log
OPatch detects the Middleware Home as "/u01/app/oracle/oms12"
Lsinventory Output file location : /u01/app/oracle/oms12/oms/cfgtoollogs/opatch/lsinv/lsinventory2015-08-11_08-41-48AM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):
EM Platform (OMS)                                                    12.1.0.4.0
There are 1 products installed in this Oracle Home.
Interim patches (1) :
Patch  20870437     : applied on Mon Aug 10 15:19:39 MSK 2015
Unique Patch ID:  18978384
Patch description:  "ENTERPRISE MANAGER BASE PLATFORM - OMS 12.1.0.4.4 PSU (GENERIC)"
   Created on 2 Jul 2015, 03:07:25 hrs PST8PDT
   Bugs fixed:
     20010360, 20346629, 18599481, 19459173, 20646440, 18830050, 18244680
     19614772, 20492788, 18932882, 19355307, 20263906, 20556489, 20106145
     14301574, 18721562, 19328391, 19535114, 19861538, 19794113, 19604817
     20596437, 19892848, 19218456, 19978077, 18505219, 17843191, 20345612
     20748748, 20024779, 18256983, 19329809, 19300103, 17987955, 18351725
     18903783, 19430853, 19845101, 20870437, 20223681, 20019184, 20263188
     19069940, 19861412, 19136663, 18446798, 19474685, 18765545, 18038059
     17783148, 18041873, 19509780, 19030447, 20933667, 19574756, 19258481
     18938561, 17812648, 20377474, 19572261, 18945007, 20307428, 18388324
     19500609, 17991694, 19137411, 19474929, 19592446, 18710124, 20536095
     19352118, 19557054, 18726708, 19595813, 19170861, 19685443, 18353162
     19289270, 19367214, 18906451, 19458672, 20284029, 18490444, 19766881
     18509136, 20739088, 19511544, 18695961, 19812966, 20576148, 20623258
     19055251, 20260177, 19323634, 19941819, 18725891, 19824587, 19780847
     18359621, 19393211, 19211048, 20505155, 18476364, 18490468, 18247849
     19071602, 19068674, 18490455, 19524568, 19061168, 19077861, 20031287
     18756725, 19281252, 18977955, 18521731, 18485889, 19775544, 18900197
     17834366, 20460507, 19545505, 19823996, 20645335, 20633770, 17407918
     18706824, 20568225, 18805935, 19376082, 19146514, 18490432, 19694744
     18230284, 18726208, 20044313, 18516298, 20642633, 18999400, 20692416
     20392036, 19362386, 19452280, 18356966, 20617058, 19055979, 19830994
     20030493, 19823761, 18834216, 19196979, 19785223, 18389542
  
--------------------------------------------------------------------------------
OPatch succeeded.
I found an answer in an inventory.xml from Central Inventory:
<HOME NAME="oms12c1" LOC="/u01/app/oracle/oms12/oms" TYPE="O" IDX="3">
   <REFHOMELIST>
      <REFHOME LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.db.oms.plugin_12.1.0.4.0"/>
      <REFHOME LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.xa.oms.plugin_12.1.0.4.0"/>
      <REFHOME LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.emas.oms.plugin_12.1.0.4.0"/>
      <REFHOME LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.mos.oms.plugin_12.1.0.5.0"/>
      <REFHOME LOC="/u01/app/oracle/oms12/jdk16"/>
      <REFHOME LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.vt.oms.plugin_12.1.0.5.0"/>
      <REFHOME LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.ssa.oms.plugin_12.1.0.6.0"/>
      <REFHOME LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.smf.oms.plugin_12.1.0.2.0"/>
      <REFHOME LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.ssa.oms.plugin_12.1.0.7.0"/>
      <REFHOME LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.db.oms.plugin_12.1.0.5.0"/>
      <REFHOME LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.smf.oms.plugin_12.1.0.3.0"/>
      <REFHOME LOC="/u01/app/oracle/oms12/plugins/oracle.sun.oss7.oms.plugin_12.1.0.4.0"/>
      <REFHOME LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.emas.oms.plugin_12.1.0.5.0"/>
      <REFHOME LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.cfw.oms.plugin_12.1.0.2.0"/>
   </REFHOMELIST>
</HOME>
Notice, that inventory.xml contains old plugin versions! It's hard to say what exactly leads to a such behaviour and I didn't have enough time for further investigations.
It was enough for me, that inventory.xml dont contain actual plugin versions.

Example 1: oracle.sysman.smf.oms.plugin (Storage Management Framework).
emcli list_plugins_on_server shows version 12.1.0.4:
Oracle Storage Management Framework          oracle.sysman.smf             12.1.0.4.0
inventory.xml contains information about versions 12.1.0.2/12.1.0.3:
<HOME NAME="OraHome13" LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.smf.oms.plugin_12.1.0.2.0" TYPE="O" IDX="24" PLUGIN="T">
<HOME NAME="OraHome16" LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.smf.oms.plugin_12.1.0.3.0" TYPE="O" IDX="29" PLUGIN="T">
Patch 21415432 contains fixes for SMF plugin versions 12.1.0.4/12.1.0.5.

Example 2: oracle.sysman.db.oms.plugin (Database Plugin).
emcli list_plugins_on_server shows version 12.1.0.6:
Oracle Database                               oracle.sysman.db                12.1.0.6.0
inventory.xml contains information about versions 12.1.0.4/12.1.0.5:
<HOME NAME="oracle_sysman_db11" LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.db.oms.plugin_12.1.0.4.0" TYPE="O" IDX="4" PLUGIN="T">
<HOME NAME="OraHome15" LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.db.oms.plugin_12.1.0.5.0" TYPE="O" IDX="28" PLUGIN="T">
Patch 21415432 contains fixes for Database Plugin versions 12.1.0.6/12.1.0.7.
Due to such inconsistencies opatch reported a message like below:
WARNING: Sub-patch "19060193" skipped for incompatibility with components installed in the OMS system.
I decided to recover lost inventory entities by note:
EM 12c: How to Recover a Lost Inventory Record for the Enterprise Manager 12.1.0.1 Cloud Control OMS and Management Agent (Doc ID 1480237.1)

GOAL

In the case that recovering a lost inventory record may be necessary, this note describes how to accomplish this for the Enterprise Manager 12.1.0.1 Cloud Control OMS and Management Agent.


First of all, I had take backup of Central Inventory, local inventory, ORACLE_HOME of OMS.
8 plugins from 9 total didn't have an actual version in the inventory.xml.
For each of them, I executed steps from the note:
EM 12c: How to Recover a Lost Inventory Record for the Enterprise Manager 12.1.0.1 Cloud Control OMS and Management Agent (Doc ID 1480237.1)
I remind, that before my actions only 1 sub-patch (from 18) could be applied to the OMS.
And after that, opatchauto analyze showed that 9 sub-patches from 18 could be applied to the OMS. Bingo!
OMS Plug-Ins patches contains fixes for 2 versions of each plugin. And only one of them was installed in my environment.
[oracle@mgmt 21415432]$ /u01/app/oracle/oms12/oms/OPatch/opatchauto apply -analyze -invPtrLoc /u01/app/oracle/oms12/oms/oraInst.loc  OPatchAuto.OMS_DISABLE_HOST_CHECK=true
OPatch Automation Tool
Copyright (c) 2014, Oracle Corporation.  All rights reserved.
 
OPatchauto version : 11.1.0.12.3
OUI version        : 11.1.0.12.0
Running from       : /u01/app/oracle/oms12/oms
Log file location  : /u01/app/oracle/oms12/oms/cfgtoollogs/opatch/opatch2015-08-11_17-13-05PM_1.log
OPatchauto log file: /u01/app/oracle/oms12/oms/cfgtoollogs/opatchauto/21415432/opatch_oms_2015-08-11_17-13-08PM_analyze.log
 
Please enter OMS weblogic admin server URL(t3s://mgmt.localdomain:7102):>
Please enter OMS weblogic admin server username:> weblogic
Please enter OMS weblogic admin server password:>
 
WARNING: Sub-patch "19512418" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "20466650" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "20950277" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21167965" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21185095" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21324604" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21324646" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21324654" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21324861" skipped for incompatibility with components installed in the OMS system.
  
Configuration Validation: Success
 
Running apply prerequisite checks for sub-patch(es) "21324890" and Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.vi.oms.plugin_12.1.0.1.0"...
Please monitor OPatch log file: /u01/app/oracle/oms12/plugins/oracle.sysman.vi.oms.plugin_12.1.0.1.0/cfgtoollogs/opatch/21324890_Aug_11_2015_17_13_05/ApplyPrereq2015-08-11_17-13-58PM_2.log
Sub-patch(es) "21324890" are successfully analyzed for Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.vi.oms.plugin_12.1.0.1.0"
 
Running apply prerequisite checks for sub-patch(es) "21167573" and Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.cfw.oms.plugin_12.1.0.2.0"...
Please monitor OPatch log file: /u01/app/oracle/oms12/plugins/oracle.sysman.cfw.oms.plugin_12.1.0.2.0/cfgtoollogs/opatch/21167573_Aug_11_2015_17_13_05/ApplyPrereq2015-08-11_17-14-01PM_2.log
Sub-patch(es) "21167573" are successfully analyzed for Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.cfw.oms.plugin_12.1.0.2.0"
 
Running apply prerequisite checks for sub-patch(es) "21174690" and Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.db.oms.plugin_12.1.0.6.0"...
Please monitor OPatch log file: /u01/app/oracle/oms12/plugins/oracle.sysman.db.oms.plugin_12.1.0.6.0/cfgtoollogs/opatch/21174690_Aug_11_2015_17_13_05/ApplyPrereq2015-08-11_17-14-03PM_2.log
Sub-patch(es) "21174690" are successfully analyzed for Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.db.oms.plugin_12.1.0.6.0"
 
Running apply prerequisite checks for sub-patch(es) "21167953" and Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.xa.oms.plugin_12.1.0.6.0"...
Please monitor OPatch log file: /u01/app/oracle/oms12/plugins/oracle.sysman.xa.oms.plugin_12.1.0.6.0/cfgtoollogs/opatch/21167953_Aug_11_2015_17_13_05/ApplyPrereq2015-08-11_17-14-06PM_2.log
Sub-patch(es) "21167953" are successfully analyzed for Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.xa.oms.plugin_12.1.0.6.0"
 
Running apply prerequisite checks for sub-patch(es) "19060193" and Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.vt.oms.plugin_12.1.0.6.0"...
Please monitor OPatch log file: /u01/app/oracle/oms12/plugins/oracle.sysman.vt.oms.plugin_12.1.0.6.0/cfgtoollogs/opatch/19060193_Aug_11_2015_17_13_05/ApplyPrereq2015-08-11_17-14-09PM_2.log
Sub-patch(es) "19060193" are successfully analyzed for Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.vt.oms.plugin_12.1.0.6.0"
 
Running apply prerequisite checks for sub-patch(es) "20950018" and Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.emas.oms.plugin_12.1.0.6.0"...
Please monitor OPatch log file: /u01/app/oracle/oms12/plugins/oracle.sysman.emas.oms.plugin_12.1.0.6.0/cfgtoollogs/opatch/20950018_Aug_11_2015_17_13_05/ApplyPrereq2015-08-11_17-14-11PM_2.log
Sub-patch(es) "20950018" are successfully analyzed for Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.emas.oms.plugin_12.1.0.6.0"
 
Running apply prerequisite checks for sub-patch(es) "20623942" and Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.ssa.oms.plugin_12.1.0.8.0"...
Please monitor OPatch log file: /u01/app/oracle/oms12/plugins/oracle.sysman.ssa.oms.plugin_12.1.0.8.0/cfgtoollogs/opatch/20623942_Aug_11_2015_17_13_05/ApplyPrereq2015-08-11_17-14-14PM_2.log
Sub-patch(es) "20623942" are successfully analyzed for Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.ssa.oms.plugin_12.1.0.8.0"
 
Running apply prerequisite checks for sub-patch(es) "19513319" and Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.smf.oms.plugin_12.1.0.4.0"...
Please monitor OPatch log file: /u01/app/oracle/oms12/plugins/oracle.sysman.smf.oms.plugin_12.1.0.4.0/cfgtoollogs/opatch/19513319_Aug_11_2015_17_13_05/ApplyPrereq2015-08-11_17-14-17PM_2.log
Sub-patch(es) "19513319" are successfully analyzed for Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.smf.oms.plugin_12.1.0.4.0"
 
Running apply prerequisite checks for sub-patch(es) "21167991" and Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.mos.oms.plugin_12.1.0.6.0"...
Please monitor OPatch log file: /u01/app/oracle/oms12/plugins/oracle.sysman.mos.oms.plugin_12.1.0.6.0/cfgtoollogs/opatch/21167991_Aug_11_2015_17_13_05/ApplyPrereq2015-08-11_17-14-19PM_2.log
Sub-patch(es) "21167991" are successfully analyzed for Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.mos.oms.plugin_12.1.0.6.0"
 
Complete Summary
================
 
All log file names referenced below can be accessed from the directory "/u01/app/oracle/oms12/oms/cfgtoollogs/opatch/2015-08-11_17-13-05PM_SystemPatch_21415432_1"
 
Prerequisites analysis summary:
-------------------------------
 
The following sub-patch(es) are applicable:
 
                                 Oracle Home Name   Sub-patches                                          Log file
                                 ----------------   -----------                                          --------
                       oracle_home_MOS_12_1_0_6_0      21167991   21167991_ApplyPrereq2015-08-11_17-14-19PM_2.log
  oracle_home_Oracle_Fusion_Middleware_12_1_0_6_0      20950018   20950018_ApplyPrereq2015-08-11_17-14-11PM_2.log
                        oracle_home_OV_12_1_0_6_0      19060193   19060193_ApplyPrereq2015-08-11_17-14-09PM_2.log
                                        OraHome18      21167573   21167573_ApplyPrereq2015-08-11_17-14-01PM_2.log
                       oracle_home_OVI_12_1_0_1_0      21324890   21324890_ApplyPrereq2015-08-11_17-13-58PM_2.log
                        oracle_home_OE_12_1_0_6_0      21167953   21167953_ApplyPrereq2015-08-11_17-14-06PM_2.log
                       oracle_home_OCA_12_1_0_8_0      20623942   20623942_ApplyPrereq2015-08-11_17-14-14PM_2.log
           oracle_home_Oracle_Database_12_1_0_6_0      21174690   21174690_ApplyPrereq2015-08-11_17-14-03PM_2.log
                       oracle_home_SMF_12_1_0_4_0      19513319   19513319_ApplyPrereq2015-08-11_17-14-17PM_2.log
  
The following sub-patches are incompatible with components installed in the OMS system:
19512418,20466650,20950277,21167965,21185095,21324604,21324646,21324654,21324861
 
--------------------------------------------------------------------------------
 
The following warnings have occurred during OPatchauto execution:
1)  Sub-patch "19512418" skipped for incompatibility with components installed in the OMS system.
2)  Sub-patch "20466650" skipped for incompatibility with components installed in the OMS system.
3)  Sub-patch "20950277" skipped for incompatibility with components installed in the OMS system.
4)  Sub-patch "21167965" skipped for incompatibility with components installed in the OMS system.
5)  Sub-patch "21185095" skipped for incompatibility with components installed in the OMS system.
6)  Sub-patch "21324604" skipped for incompatibility with components installed in the OMS system.
7)  Sub-patch "21324646" skipped for incompatibility with components installed in the OMS system.
8)  Sub-patch "21324654" skipped for incompatibility with components installed in the OMS system.
9)  Sub-patch "21324861" skipped for incompatibility with components installed in the OMS system.
--------------------------------------------------------------------------------
OPatchauto Session completed with warnings.
 
Log file location: /u01/app/oracle/oms12/oms/cfgtoollogs/opatchauto/21415432/opatch_oms_2015-08-11_17-13-08PM_analyze.log
 
OPatchauto completed with warnings.
Further installation was ended without any issues.

понедельник, 3 августа 2015 г.

Reclaim unused space from MLOG$ segments after MVIEW Refresh

We are using fast-refreshable MVIEWs in some of our database for reporting purposes.
They are works fine most of the time.
However, periodically MLOG size is increased due to different reasons.
Such as: bulk data loading, other abnormal application activities, or delays with MVIEW refresh.
After that, even if you refresh MVIEW, HWM doesn't reset.
You need to reset it manually, using commands such as ALTER TABLE SHRINK/MOVE and so on.
Without resetting the HWM MVIEW refresh performance can be poor.

Good news: there are Patch 11072728 available for some platforms that can reset the HWM without manual intervention.
This patch was described in MOS note Space Not Reclaimed from MLOG$ Segments After MVIEW Refresh (Doc ID 1941137.1)
According to the note, the fix for bug 11072728 will be provided in upcoming 12.2 release.
I have already requested that patch for Solaris SPARC64 on top of 11.2.0.4.4/11.2.0.4.6.

Today I have decided to look closer on Patch 11072728 in one of non-production database.
I want to be sure that patch didn't harm production instance.

Reading the bug readme, I have found that fix for bug is not enabled by default.
You need to set underscope parameter _bug11072728_mv_refresh_truncate_log to 1 to enable it.
Worth to be noted, is that we can change this parameter on the session and on the system level:
SQL> select isses_modifiable,
  2         issys_modifiable
  3    from v$parameter
  4   where name = '_bug11072728_mv_refresh_truncate_log';

ISSES_MODIFIABLE ISSYS_MODIFIABLE
---------------- ----------------
TRUE             IMMEDIATE
Next script I have used to setup test schema:
SQL> grant connect to tc identified by tc;

Grant succeeded.

SQL> grant alter session to tc;

Grant succeeded.

SQL> grant create materialized view to tc;

Grant succeeded.

SQL> grant create table to tc;

Grant succeeded.

SQL> grant unlimited tablespace to tc;

Grant succeeded.

SQL> 
SQL> conn tc/tc
Connected.
SQL> 
SQL> create table t
  2  as
  3  select *
  4    from all_users;

Table created.

SQL> 
SQL> alter table t add constraint t_pk primary key(username);

Table altered.

SQL> 
SQL> create materialized view log on t with primary key;

Materialized view log created.

SQL> 
SQL> create materialized view mv_t
  2  refresh fast
  3  as
  4  select *
  5    from t;

Materialized view created.
    
In the above script I have created table, mat view log on it, and materialized view.
We can see that mat view log is empty, segment has 8 blocks allocated:
SQL> select count(*) from mlog$_t;

  COUNT(*)
----------
         0

SQL> 
SQL> select blocks
  2    from user_segments
  3   where segment_name = 'MLOG$_T';

    BLOCKS
----------
         8
I update table in loop, 100 times in total:
SQL> begin
  2    for i in 1..100
  3    loop
  4      update t
  5         set created = created
  6       where username <> 'SYS';
  7      commit;
  8    end loop;
  9  end;
 10  /
Check that mat view log is not empty, segment was extended:
SQL> select count(*) from mlog$_t;

  COUNT(*)
----------
     19400

SQL> 
SQL> select blocks
  2    from user_segments
  3   where segment_name = 'MLOG$_T';

    BLOCKS
----------
       104
Let's refresh materialized view now:
SQL> exec dbms_mview.refresh( 'mv_t', method=>'f')

PL/SQL procedure successfully completed.
If the patch 11072728 was not applied or the parameter "_bug11072728_mv_refresh_truncate_log" not set to 1, then the HWM is not reset and the segment space allocated is the same:
SQL> select count(*) from mlog$_t;

  COUNT(*)
----------
         0

SQL> 
SQL> select blocks
  2    from user_segments
  3   where segment_name = 'MLOG$_T';

    BLOCKS
----------
       104
In the production system with a high DML activity materialized view logs can grows to a much higher size.
And the materialized view refresh performance degrades.
As I said previously, before the patch 11072728 you need to reset the HWM manually.
With the patch 11072829 you dont need to do it anymore.
Just the set parameter "_bug11072728_mv_refresh_truncate_log" to 1:
SQL> exec dbms_mview.refresh( 'mv_t', method=>'f')

PL/SQL procedure successfully completed.

SQL> 
SQL> select count(*) from mlog$_t;

  COUNT(*)
----------
         0

SQL> 
SQL> select blocks
  2    from user_segments
  3   where segment_name = 'MLOG$_T';

    BLOCKS
----------
         8
You can see that segment allocated space was decreased.
How does it works under the hood?
I have setup sql tracing and event 10704 (enqueue trace) to investigate it:
SQL> alter session set events 'sql_trace bind=true:10704 level 10';

Session altered.

SQL> 
SQL> exec dbms_mview.refresh( 'mv_t', method=>'f')

PL/SQL procedure successfully completed.
I used below command to filter the irrelevant lines from output:
egrep "ksqgtl \*|ksqrcl: [^r]|^truncate|select count\(\*\) from .*MLOG" orcl_ora_18632.trc
  • ksqgtl - get lock function
  • ksqrcl - release lock function
  • We want to show the truncate command and the select count(*) from MLOG.
Below is the output of egrep command, in which some lines was skipped for brevity:
select count(*) from "TC"."MLOG$_T"
ksqgtl *** TM-001854e4-00000000 mode=6 flags=0x401 timeout=0 ***
select count(*) from "TC"."MLOG$_T"
ksqgtl *** TM-001854e6-00000000 mode=6 flags=0x401 timeout=0 ***
truncate table "TC"."MLOG$_T"
These commands was executed when the materialized view update already done.
Looks like that new algorithm with MLOG truncate works in following way:
  1. execute old refresh code
  2. check count of rows in MLOG
  3. if zero, lock master table in exclusive mode nowait (TM lock with timeout=0)
  4. check count of rows in MLOG again (because there are can be DML between step 2 and 3)
  5. lock MLOG in exclusive mode nowait (TM lock with timeout=0)
  6. truncate MLOG
At least, an open transaction will prevent the truncation of MLOG.
And that indirectly confirms my assumptions about internal workings of new refresh algorithm. Let's update the 1 row in table in session 1:
SQL> -- session 1
SQL> update t
  2     set created=created
  3   where username='SYS';

1 row updated.
Now we will check space of MLOG before refresh, perform actual refresh and check the space again (in other session):
SQL> -- session 2
SQL> select blocks
  2    from user_segments
  3   where segment_name = 'MLOG$_T';

    BLOCKS
----------
       104

SQL> exec dbms_mview.refresh( 'mv_t', method=>'f')

PL/SQL procedure successfully completed.
SQL> 
SQL> select count(*) from mlog$_t;

  COUNT(*)
----------
         0

SQL> 
SQL> select blocks
  2    from user_segments
  3   where segment_name = 'MLOG$_T';

    BLOCKS
----------
       104
And the truncate of MLOG was not executed.
Looks like, it's safe to use a patch 11072728 in the production environment.
If I will face any issue with that patch in the production, I will update this blog post.

среда, 22 июля 2015 г.

Edition-Based redefinition: adjunct schemas

EBR (Edition-Based Redefinition) - one of the killer feature of Oracle Database 11g R2, as said Tom Kyte at the 2010.
But I don't think that this feature widely used by Oracle community.
Why? Probably, because application must be "edition-aware", as described by Tom Kyte in his excellent articles.
I have been using EBR (Edition-Based Redefinition) since 2012.
In this blog post I would like to describe one of the basic component of EBR: adjunct schemas.
All of this information is a pure speculations based on blog posts and Oracle whitepapers, and my experience with EBR feature.
All of the tests are run in Oracle Database version 12.1.0.2 under Solaris SPARC.
Here is a code that I have used for this demo:
def tns_alias=orcl

doc
  connect as DBA user
#
conn /@&tns_alias.

set echo on timi off ti off sqlp "SQL> "

drop edition e1 cascade;
drop edition e2 cascade;
drop user tc cascade;

doc
  Create editions-enabled schema
#
grant create procedure, create session to tc identified by tc;

alter user tc enable editions;

doc
  Enable SQL tracing
#

alter session set events 'sql_trace bind=true';

doc
  Create 2 editions
#

create edition e1;
create edition e2;

alter session set events 'sql_trace off';

select value from v$diag_info where name='Default Trace File';
You can see that I have created edition-enabled schema TC and 2 editions: E1 and E2.
I would to point your attention to the below recursive SQL executed during "CREATE EDITION" command:
PARSING IN CURSOR #18446744071422692152 len=288 dep=1 uid=0 oct=2 lid=0 tim=9324454212634 hv=556673006 ad='430c59cc0' sqlid='fws71mhhkw9zf'
insert into user$(user#, name, password, ctime, ptime,                      datats#, tempts#, type#, defrole, resource$, ltime,                      astatus, lcount, spare1, spare2, ext_user
name)    values(:1, :2, NULL, sysdate, null, :3, :4, 2, :5, :6, null, :7, 0, 16,           :8, :9)
END OF STMT
PARSE #18446744071422692152:c=1199,e=1198,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=9324454212629
BINDS #18446744071422692152:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=ffffffff77e77150  bln=22  avl=03  flg=05
  value=859
 Bind#1
  oacdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=01 csi=171 siz=32 off=0
  kxsbbbfp=ffffffff7fff8688  bln=32  avl=30  flg=09
  value="SYS_DZAYDZXFEF1OQRKZUBTDF4UHRF"
 Bind#2
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=144 off=0
  kxsbbbfp=ffffffff77e770a8  bln=22  avl=02  flg=05
  value=3
 Bind#3
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=ffffffff77e770c0  bln=22  avl=02  flg=01
  value=2
 Bind#4
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=48
  kxsbbbfp=ffffffff77e770d8  bln=22  avl=01  flg=01
  value=0
 Bind#5
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=72
  kxsbbbfp=ffffffff77e770f0  bln=22  avl=02  flg=01
  value=1
 Bind#6
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=96
  kxsbbbfp=ffffffff77e77108  bln=22  avl=01  flg=01
  value=0
 Bind#7
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=120
  kxsbbbfp=ffffffff77e77120  bln=22  avl=04  flg=01
  value=512690
 Bind#8
  oacdty=01 mxl=32(02) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=01 csi=171 siz=32 off=0
  kxsbbbfp=ffffffff7fff86c4  bln=32  avl=02  flg=09
  value="TC"
Using Tom Kyte print_table procedure I retrieve this row in more readable format:
SQL> exec print_table(q'#select * from sys.user$ where name='SYS_DZAYDZXFEF1OQRKZUBTDF4UHRF'#')
USER#                         : 859
NAME                          : SYS_DZAYDZXFEF1OQRKZUBTDF4UHRF
TYPE#                         : 2
PASSWORD                      :
DATATS#                       : 3
TEMPTS#                       : 2
CTIME                         : 22.07.2015 15:44:08
PTIME                         :
EXPTIME                       :
LTIME                         :
RESOURCE$                     : 1
AUDIT$                        :
DEFROLE                       : 0
DEFGRP#                       :
DEFGRP_SEQ#                   :
ASTATUS                       : 0
LCOUNT                        : 0
DEFSCHCLASS                   :
EXT_USERNAME                  : TC
SPARE1                        : 16
SPARE2                        : 512690
SPARE3                        :
SPARE4                        :
SPARE5                        :
SPARE6                        :
SPARE7                        :
SPARE8                        :
SPARE9                        :
SPARE10                       :
SPARE11                       :
-----------------
We can see that USER$ was populated with new row with obscure "NAME" SYS_%. New row has "TYPE#"=2.
According to a definition of SYS.USER$ table from @?/rdbms/admin/dcore.bsq this's "adjunct" schema:
create table user$                                             /* user table */
( user#         number not null,                   /* user identifier number */
  name          varchar2("M_IDEN") not null,                 /* name of user */
               /* 0 = role, 1 = user, 2 = adjunct schema, 3 = schema synonym */
Row has EXT_USERNAME same as original schema:
EXT_USERNAME                  : TC
SPARE2 - references to edition object:
SPARE2                        : 512690

SQL> exec print_table('select * from dba_objects where object_id=512690')
OWNER                         : SYS
OBJECT_NAME                   : E1
SUBOBJECT_NAME                :
OBJECT_ID                     : 512690
DATA_OBJECT_ID                :
OBJECT_TYPE                   : EDITION
CREATED                       : 22.07.2015 15:44:08
LAST_DDL_TIME                 : 22.07.2015 15:44:08
TIMESTAMP                     : 2015-07-22:15:44:08
STATUS                        : VALID
TEMPORARY                     : N
GENERATED                     : N
SECONDARY                     : N
NAMESPACE                     : 64
EDITION_NAME                  :
SHARING                       : NONE
EDITIONABLE                   :
ORACLE_MAINTAINED             : N
-----------------
Second adjunct schema points to E2 edition (some rows skipped for readability):
SQL> exec print_table(q'#select * from sys.user$ where name='SYS_D$8SPB$NEB3PDFCNYRJHBAV7MK'#')
USER#                         : 861
NAME                          : SYS_D$8SPB$NEB3PDFCNYRJHBAV7MK
TYPE#                         : 2
...
EXT_USERNAME                  : TC
SPARE1                        : 16
SPARE2                        : 512691
...

SQL> exec print_table('select * from dba_objects where object_id=512691')
OWNER                         : SYS
OBJECT_NAME                   : E2
SUBOBJECT_NAME                :
OBJECT_ID                     : 512691
DATA_OBJECT_ID                :
OBJECT_TYPE                   : EDITION
CREATED                       : 22.07.2015 15:44:08
LAST_DDL_TIME                 : 22.07.2015 15:44:08
TIMESTAMP                     : 2015-07-22:15:44:08
STATUS                        : VALID
TEMPORARY                     : N
GENERATED                     : N
SECONDARY                     : N
NAMESPACE                     : 64
EDITION_NAME                  :
SHARING                       : NONE
EDITIONABLE                   :
ORACLE_MAINTAINED             : N
-----------------
Below is the listing of adjunct schemas in one of production database with one edition (we purge old editions periodically).
SQL> select name from sys.user$ where type#=2;

NAME
------------------------------
SYS_CTO$G3UFDV01YXT$0I2IIAM1FW
SYS_#65J#JRLAO7834YSNCQ09MJB0#
SYS_CFV3AQP2WPEDH4WG#BC_OY_529
SYS_MAEDFDRVL4UF6O0_K#2IKFG332
SYS_ZTM78VJZ1NRBT0OZ7CX3QQYWO#
SYS_PBOZ#4GB#AZAEGM2DQ62ODWO1Q
SYS_4OYT_40J6MSQ9HK4L$R5K8ZD8G
SYS_7DT8CD#ARM8HF8LE5K6T7#G91K
SYS_DAL9_OKM$3EL#MH_IA4IYDJ6V8
SYS_7W7SDDSF#H6QO#D8F_CFH6HL5A
SYS_WW9CZFBWQZH45YCIQW9Z8QXDPG
SYS_R0CCNSCA$$67F4F3JASWEJXZMS
SYS___IC1EVBW5MA9VL6BUY958MFEQ
SYS_JY31UD09PBTXAUV#YLDG8ND1X$
SYS_CMD9YMUP#4W3IRHEA99$OA_2$H
SYS_375#5FX937$H68GJ_ZSX5ROSP3
SYS_U5$O#5LBHUX78D5M6C421CM_F#
SYS_FFQSGVBBM97GZCOVA7ZZCXOE6N
SYS_DX#I9P0FLCROTW5_FUQ0TG4L$M
SYS_7CFP28DK1RX$CV#ES#WEKAJ1WR
Sometimes we have 5-6 editions in place. That results in 100-120 additional schemas used by EBR!
Now you know, if you will find a row in user$ with type#=2 and with a obscure name like 'SYS_%' - it's a normal.
At least if you are using Editions.

четверг, 18 июня 2015 г.

Function-Based index changed its definition after a rebuild

One of our developers have discovered a problem when index column data type changed after index had been rebuild.
They asked me for help with that issue. Here's a demo script:
SQL> create table t(
  2    x int,
  3    y int);

Table created.

SQL> create index t_i on t(
  2    decode(x, 1, to_number(null), y));

Index created.

SQL> exec dbms_stats.gather_table_stats( '', 't')

PL/SQL procedure successfully completed.
I create a function-based index with one column.
The index column is defined to show "Y" column (with type int) if the "X" column isn't equal to 1 (or null).
According to a documentation:
http://docs.oracle.com/database/121/SQLRF/functions056.htm#SQLRF00631
Oracle automatically converts the return value to the same data type as the first result.
If the first result has the data type CHAR or if the first result is null, 
then Oracle converts the return value to the data type VARCHAR2.
Notice that index column data type is a NUMBER:
SQL> select column_name
  2    from user_ind_columns
  3   where index_name = 'T_I';

COLUMN_NAME
------------------------------
SYS_NC00003$

SQL> select data_type
  2    from user_tab_cols
  3   where table_name = 'T'
  4     and column_name = 'SYS_NC00003$';

DATA_TYPE
------------------------------
NUMBER
The developer have found that his query doesn't use the index:
SQL> explain plan for
  2  select *
  3    from t
  4   where decode(x, 1, to_number(null), y) = to_number(:1);

Explained.

SQL> 
SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 2153619298

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    26 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    26 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter(DECODE("X",1,TO_NUMBER(NULL),"Y")=TO_NUMBER(:1))
Index will be used when he changed his query to following:
SQL> explain plan for
  2  select *
  3    from t
  4   where decode(x, 1, null, y) = to_number(:1);

Explained.

SQL> 
SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 2858887366

--------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      |     1 |    39 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T    |     1 |    39 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | T_I  |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access(DECODE("X",1,NULL,TO_CHAR("Y"))=TO_NUMBER(:1))
Why is this?
The index expression is not what I passed in the CREATE INDEX statement:
SQL> select column_expression
  2    from user_ind_expressions
  3   where index_name = 'T_I';

COLUMN_EXPRESSION
------------------------------
DECODE("X",1,NULL,"Y")
It looks like Oracle is "clever enough" to change our index expression from:
DECODE(X, 1, TO_NUMBER(NULL), Y)
to:
DECODE("X", 1, NULL, "Y")
This leads to unexpected results when index was rebuild:
alter index t_i rebuild;
The index column data type is VARCHAR2 after that!
SQL> select data_type
  2    from user_tab_cols
  3   where table_name = 'T'
  4     and column_name = 'SYS_NC00003$';

DATA_TYPE
------------------------------
VARCHAR2
Our query now suddenly has started using FTS where previously it was using an index:
SQL> explain plan for
  2  select *
  3    from t
  4   where decode(x, 1, null, y) = to_number(:1);

Explained.

SQL> 
SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 2153619298

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    48 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    48 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter(TO_NUMBER(DECODE("X",1,NULL,TO_CHAR("Y")))=TO_NUMBER(:1))

Index hint did not help either:
SQL> explain plan for
  2  select /*+ index(t t_i)*/*
  3    from t
  4   where decode(x, 1, null, y) = to_number(:1);

Explained.

SQL> 
SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 2153619298

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    48 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    48 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter(TO_NUMBER(DECODE("X",1,NULL,TO_CHAR("Y")))=TO_NUMBER(:1))
Notice TO_NUMBER conversion at line 1:
   1 - filter(TO_NUMBER(DECODE("X",1,NULL,TO_CHAR("Y")))=TO_NUMBER(:1))
We should rewrite our query as follows:
SQL> explain plan for
  2  select *
  3    from t
  4   where decode(x, 1, null, y) = to_char(:1);

Explained.

SQL> 
SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 2858887366

--------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      |     1 |    48 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T    |     1 |    48 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | T_I  |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access(DECODE("X",1,NULL,TO_CHAR("Y"))=:1)
To workaround this issue, we could use an index on virtual columns.
We could prevent TO_NUMBER(NULL) to NULL conversion by changing TO_NUMBER(NULL) to CAST(NULL as NUMBER), or other such expression.
I have opened the SR with Oracle and they told me that this's due to:
Bug 17871767: FUNCTION BASE INDEX DEFINITION CHANGED ON 11.2, ADDED A TO_CHAR FUNCTION This bug is still under work and hasn't been resolved yet.
Actually, this issue with function-based indexes has been present for a long time.
I have reproduced it on 9.2.0.6 at least.