ORA-00600 [krccckp_scn_low] – from CTWR Process

Since Oracle RDBMS 11g it is possible to use block change tracking on a standby database.
After using a snapshot standby database we got instnace crashes with:

ORA-00600 [krccckp_scn_low] - from CTWR Process

not exactly shure if this is a bug or missing dokumentation, but it seems that standby block change tracking is not handled correctly.
so if a 12.1 snapshot standby database is reverted to a physical standby, I recomment do disable and enable block change traking.
So this ORA-00600 [krccckp_scn_low] can be avoided.

Indexing Oracle table columns of collection type (nested table)

With Oracle’s Object-Relational you can store collection of user defined datatypes (UDT) in a table column. This column can be stored a IOT or heap table.

If stored as heap table (normal table) you may want to index the nested table so that there are not FTS on every access.

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                               |     9 |  1818 | 32469   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL                  | TABLE                         |     1 |    81 |   309   (1)| 00:00:01 |

The nested table has a hidden column “NESTED_TABLE_ID” which can be indexed:

create index <my_index>on <my_nested_table> (NESTED_TABLE_ID)
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                               |     9 |  1818 |    56   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| NESTED_TABXX_TAB2             |     1 |    27 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | NESTED_TAB_IX1                |     1 |       |     1   (0)| 00:00:01 |

* Don’t forget to gather statistics after create the indexes

Howto Purge a OLS Group from the library cache with dbms_shared_pool (Oracle Label Security)

With the dbms_shared_pool.purge() function specific object can be purged out of the library cache.
But according oracle documentation of dbms_shared_pool just a few types of objects can purged.

Here I give a short example how other objects can be purged. I my case a oracle OLS Group, casue of a curruption with OLS

ERROR:
ORA-12403: invalid internal label

The function to use is:
exec dbms_shared_pool.purge(,,<heap, mostly 1>);

  1. Lookup the hash:
    select SHARABLE_MEM,NAMESPACE,FULL_HASH_VALUE  from v$db_object_cache where namespace like '%LABEL%' and ;
    SHARABLE_MEM NAMESPACE
    ------------ ----------------------------------------------------------------
            4096 LABEL SECURITY GROUP NO->SHORT NAME
    

    You have identify the needed object’s hash.

  2. Identify the namespace:
    elect kglsttyp,kglstdsc,kglstidn from x$kglst where kglstdsc like '%LABEL%' order by 3,1 ;
    
    KGLSTTYP            KGLSTDSC                                         KGLSTIDN
    ------------------- ---------------------------------------------- ----------
    NAMESPACE           LABEL SECURITY ILABELS                                 96
    NAMESPACE           LABEL SECURITY GROUP NO->SHORT NAME                    97
    NAMESPACE           LABEL SECURITY GROUP SHORT NAME->NO                    98
    NAMESPACE           LABEL SECURITY COMPARTMENT NO->SHORT NAME              99
    NAMESPACE           LABEL SECURITY COMPARTMENT SHORT NAME->NO             100
    NAMESPACE           LABEL SECURITY LEVEL NO->SHORT NAME                   101
    ....
    

    In my case I need to purge a “LABEL SECURITY GROUP NO->SHORT NAME” with the id 97.

  3. Purge the object:
    SQL>  exec dbms_shared_pool.purge('2d86c4246f3c0eb516628bf324d6b9a3',97,1);
    
    PL/SQL procedure successfully completed.
    
    SQL> select SHARABLE_MEM,NAMESPACE  from v$db_object_cache where namespace like '%LABEL%' and FULL_HASH_VALUE='2d86c4246f3c0eb516628bf324d6b9a3';
    
    SHARABLE_MEM NAMESPACE
    ------------ ----------------------------------------------------------------
               0 LABEL SECURITY GROUP NO->SHORT NAME
    

    Here we see that the object occupies 0 memory, so it’s gone.

If this method works for all library cache object is not clear, but for most it does. Neverthenless this this method need to be usesd with care, since it’s not documentet and it may not be clear if the rdbms reloads the purged object correctly if needed.