I thought it would be useful to have  a consolidated list of issues we have run into, some of which I have already blogged about, some of which I haven’t. I will try to keep this page up-to-date moving forwards. Note we are not really using 12cR2 extensively, we have just upgraded OEM Repository and AWR Warehouse instances so far.

Recovery Manager

Upgrade Catalog Fails with RMAN-06444: error creating init_grsp_pdb_key

Oracle have now published a document about this issue:
UPGRADE CATALOG command from 12.1 to 12.2 Fails With RMAN-6004 and ORA-1422 (Doc ID 2252894.1)

Image Copies never marked as obsolete after datafile is removed

Bug 26115103 – REPORT OBSOLETE NOT SHOWING OLD DATAFILE COPY IN 12.2

Control File autobackups are never marked as obsolete

Bug 25943271: RMAN REPORT OBSOLETE DOES NOT REPORT CONTROLFILE BACKUP AS OBSOLETE

After upgrading catalog to 12.2,  resync failing with “ORA-20110: set stamp set count conflict”

Bug 26385473 : REGISTER TARGET DATABASE WITH 12.2 CATALOG FAILS WITH RMAN-03008 ORA-20110

This seems to be a problem that is specific to 12.1 multi-tenant instance on 12.2 catalog. It appears that rman is attempting to catalog the same backupset once for each PDB

Alain Fuhrer has run into this and has more details

Trace on the target system reveals problem seems to be caused by SQL like the following, which seem to ‘forget’ to provide the join conditions between the two tables queried:

SELECT BS.RECID , BS.STAMP , BS.SET_STAMP , BS.SET_COUNT , BS.BACKUP_TYPE , BS.INCREMENTAL_LEVEL , BS.PIECES , START_TIME , COMPLETION_TIME , CONTROLFILE_INCLUDED , BS.INPUT_FILE_SCAN_ONLY , KEEP_UNTIL , DECODE(BS.KEEP_OPTIONS , ‘LOGS’ , :b1 , ‘NOLOGS’ , :b2 , ‘BACKUP_LOGS’ , :b3 , 0 ) KEEP_OPTIONS , BS.BLOCK_SIZE , BS.MULTI_SECTION , BS.GUID , DECODE(BS.CON_ID , :b4 , 1 , 0 ) DROPPED_PDB FROM V$BACKUP_SET BS , V$CONTAINERS PDB WHERE BS.RECID BETWEEN :b5 AND :b6 AND ( BS.STAMP >= :b7 OR BS.RECID = :b6 ) AND BS.STAMP >= :b8 AND BS.FOR_XTTS != ‘YES’ ORDER BY BS.RECID

AWR and AWR Warehouse

AWR Warehouse load process failing with ORA-600 after upgrading repository

The error message is [kewrspbr_2: wrong last partition]. Doc ID 2020227.1 describes the fix for a similar issue. I found partitioning was wrong on some on some new 12.2 AWR tables so I recreated them, added a partition for each database contributing to AWR Warehouse. I have been unable to reproduce this issue on a test system.

WRH$_SGASTAT_U becoming unusable

Only affects database upgraded from 12.1.0.2, workaround is to re-create the index.
BUG 25954054 – WRH$_SGASTAT_U BECOMING UNUSABLE STATE IN UPGRADED DB
A patch is now available for this issue.

AWR Transfer Task Failing with ORA-28040

Set SQLNET.ALLOWED_LOGON_VERSION_SERVER to 11

Enterprise Manager

TNS-12599: TNS:cryptographic checksum mismatch in alert log of 12.2 database from connections from OEM

No functional impact. Bug number 25915038 created. Setting the following in sqlnet.ora of target database suppresses the message:

• SQLNET.CRYPTO_CHECKSUM_SERVER=rejected
• SQLNET.ENCRYPTION_SERVER=rejected

No Such Metadata in Enterprise Manager after upgrading database

This is a bug on calculation of database version from db plugin.  Simplest solution is to upgrade db plugin to 13.2.2 or later on database host.

Miscellaneous

ORA-20001: Statistics Advisor: Invalid task name for the current user

Seems to be on fresh created database. Solution per Doc ID 2127675.1 is to run  dbms_stats.init_package.

READ ANY TABLE audit records

Bug 26035911 : AUDIT RECORDS GENERATED EVEN WHEN THE SYSTEM PRIVILEGE IS NOT EXERCISED IN 12.2

Update 2016-04-26: this is new bug 25954054 – WRH$_SGASTAT_U BECOMING UNUSABLE STATE IN UPGRADED DB.

After upgrading our OMR and AWRW instances from 12.1 to 12.2, our monitoring revealed that index SYS.WRH$_SGASTAT_U was becoming unusable at regular intervals.
The first couple of times, we just rebuilt it in the hope that the problem would just go away(!); of course it didn’t, so I had to do a little bit of investigation.

Reviewing the alert log identified the exact time that the problem had occurred:

2017-04-12T08:11:43.674485+08:00
Some indexes or index [sub]partitions of table SYS.WRH$_SGASTAT have been marked unusable

Grepping for “SGASTAT” in the trace directory, also revealed that the index being unusable was causing inserts into AWR had been failing:

*** 2017-04-12T12:00:18.338061+08:00
*** SESSION ID:(289.8864) 2017-04-12T12:00:18.338092+08:00
*** CLIENT ID:() 2017-04-12T12:00:18.338103+08:00
*** SERVICE NAME:(SYS$BACKGROUND) 2017-04-12T12:00:18.338108+08:00
*** MODULE NAME:(MMON_SLAVE) 2017-04-12T12:00:18.338119+08:00
*** ACTION NAME:(Auto-Flush Slave Action) 2017-04-12T12:00:18.338125+08:00
*** CLIENT DRIVER:() 2017-04-12T12:00:18.338132+08:00

*** KEUUXS - encountered error: (ORA-01502: index 'SYS.WRH$_SGASTAT_U' or partition of such index is in unusable state
)
  *** SQLSTR: total-len=464, dump-len=464,
      STR={insert into  wrh$_sgastat  (dbid, per_pdb, con_dbid, snap_id, instance_number, pool, name, bytes)  select distinct  :dbid, 0 per_pdb,  :srcdbid  con_dbid,  :snap_id, :instance_number, pool, name, bytes FROM     (select con_id, pool, name, bytes,             100*(bytes) / (sum(bytes) over (partition by pool)) part_pct        from  v$sgastat      ) sgastat  WHERE (1 = 1)   and part_pct >= 1      or pool is null      or name = 'free memory' or name = 'row cache' }
*** KEWRAFM1: Error=13509 encountered by kewrfteh

As we still had the redo logs for the period of time identified by the alert log, I fired up logminer, and right after a bunch of inserts into WRH$_SGASTAT, I found the following statement:

alter table WRH$_SGASTAT drop partition WRH$_SGASTA_1521710113_17057;

I am guessing this is part of the standard mechanism for purging AWR data based on configured retention settings.
A quick check reveals that of the partitioned AWR base tables, 3 have indexes that are not partitioned:

SQL> SELECT                                                                                                                                                                                                          
  2     partitioned,                                                                                                                                                                                                 
  3     COUNT(*)                                                                                                                                                                                                     
  4  FROM                                                                                                                                                                                                            
  5     dba_indexes                                                                                                                                                                                                  
  6  WHERE                                                                                                                                                                                                           
  7     index_type = 'NORMAL'                                                                                                                                                                                        
  8  AND                                                                                                                                                                                                             
  9     table_name IN (                                                                                                                                                                                              
 10        SELECT                                                                                                                                                                                                    
 11           table_name                                                                                                                                                                                             
 12        FROM                                                                                                                                                                                                      
 13           dba_tables                                                                                                                                                                                             
 14        WHERE                                                                                                                                                                                                     
 15           table_name LIKE 'WRH$\_%' ESCAPE '\'                                                                                                                                                                   
 16        AND                                                                                                                                                                                                       
 17           partitioned = 'YES'                                                                                                                                                                                    
 18     )                                                                                                                                                                                                            
 19  GROUP BY                                                                                                                                                                                                        
 20     partitioned;                                                                                                                                                                                                 
                                                                                                                                                                                                                     
PAR   COUNT(*)                                                                                                                                                                                                       
--- ----------                                                                                                                                                                                                       
NO           3                                                                                                                                                                                                       
YES         35                                                                                                                                                                                                       

SQL> SELECT                                                                                                                                                                                                          
  2     table_owner,                                                                                                                                                                                                 
  3     table_name,                                                                                                                                                                                                  
  4     index_name                                                                                                                                                                                                   
  5  FROM                                                                                                                                                                                                            
  6     dba_indexes                                                                                                                                                                                                  
  7  WHERE                                                                                                                                                                                                           
  8     index_type = 'NORMAL'                                                                                                                                                                                        
  9  AND                                                                                                                                                                                                             
 10     table_name IN (                                                                                                                                                                                              
 11        SELECT                                                                                                                                                                                                    
 12           table_name                                                                                                                                                                                             
 13        FROM                                                                                                                                                                                                      
 14           dba_tables                                                                                                                                                                                             
 15        WHERE                                                                                                                                                                                                     
 16           table_name LIKE 'WRH$\_%' ESCAPE '\'                                                                                                                                                                   
 17        AND                                                                                                                                                                                                       
 18           partitioned = 'YES'                                                                                                                                                                                    
 19     )                                                                                                                                                                                                            
 20  AND partitioned = 'NO';                                                                                                                                                                                         
                                                                                                                                                                                                                     
TABLE_OWNER                    TABLE_NAME                     INDEX_NAME                                                                                                                                             
------------------------------ ------------------------------ ------------------------------                                                                                                                         
SYS                            WRH$_SGASTAT                   WRH$_SGASTAT_U                                                                                                                                         
SYS                            WRH$_CON_SYSMETRIC_HISTORY     WRH$_CON_SYSMET_HISTORY_INDEX                                                                                                                          
SYS                            WRH$_SYSMETRIC_HISTORY         WRH$_SYSMETRIC_HISTORY_INDEX                                                                                                                           

Running the same query on both 11.2 and 12.1 instances reveals no such mis-configured indexes We are following up with Oracle Support, but I would be interested to know if anyone else can check my findings on their 12.2 instances.

Working with Oracle Support we have worked around the issue using the following script:

ALTER TABLE wrh$_sgastat DROP CONSTRAINT wrh$_sgastat_u;

ALTER TABLE wrh$_sgastat ADD CONSTRAINT wrh$_sgastat_u UNIQUE ( dbid,snap_id,instance_number,name,pool,con_dbid )
   USING INDEX
      LOCAL
   TABLESPACE sysaux;

In addition, I believe I have found the bug in file $ORACLE_HOME/rdbms/admin/c1201000.sql at line 3036:

alter table wrh$_sgastat drop constraint wrh$_sgastat_u;

alter table wrh$_sgastat add constraint wrh$_sgastat_u
        unique (dbid, snap_id, instance_number, name, pool, con_dbid, stattype);