Ouch, this hurts: bug 21923026 – patch Oracle 12c Home before upgrading if you have OLTP Compression in 11g

Actually I’d consider this as a real serious issue which may affect many customers with larger deployments using the Advanced Compression Option’s OLTP Compression.

I came across it as Don Seiler full of anger twittered some bad words about Oracle Database 12.1.0.2.. We’ve exchanged a good number of emails – and I could see the issues Don and his colleagues got while assisting a customer to go live on Oracle Database 12c.

The most interesting one happened on the physical standby after the primary got upgraded. The MRP failed with an ORA-600.

Issue

After upgrading from Oracle 11.2.0.3 to Oracle 12.1.0.2 the MRP (Managed Recovery Process) on the physical standby database is failing with:

ORA-600 [kdBlkCheckError], [5]

Restoring the data file does not solve the issue.

This happens if the source table has OLTP Compression enabled, and this database has been upgraded to Oracle Database 12.1.0.2

This corruption on compressed tables will happen during media recovery while applying redo generated for those tables when the source database was running on the Oracle 11g software version.

This also applies to Oracle 12c upgraded physical standby databases when recovering redo that was generated on 11g.

The latter is actually the issue Don must have had encountered here.

Which versions are affected?

This happens only in Oracle Database 12.1.0.1 and Oracle Database 12.1.0.2. The issue is fixed in the MAIN code line in Oracle 12.2. You can detect tables with OLTP Compression with these queries:

select owner, table_name, compression, compress_for  
from dba_tables
where compress_for in ('ADVANCED','QUERY LOW','QUERY HIGH','ARCHIVE LOW','ARCHIVE HIGH','OLTP');

select table_owner, table_name, compression, compress_for  
from dba_tab_partitions
where compress_for in ('ADVANCED','QUERY LOW','QUERY HIGH','ARCHIVE LOW','ARCHIVE HIGH','OLTP');

select table_owner, table_name, compression, compress_for  
from dba_tab_subpartitions
where compress_for in ('ADVANCED','QUERY LOW','QUERY HIGH','ARCHIVE LOW','ARCHIVE HIGH','OLTP');

Workaround/Solution

Uncompress the table would work – but is not a pleasant idea.

Install the fix for Bug 21923026 in the new Oracle 12c home BEFORE upgrading the database

Issue got filed as bug 21682261 but has been replaced by the patch for bug 21923026. It should be applied to Oracle 12.1.0.2 before upgrade. If you hit the issue on a physical standby then apply the patch to the standby’s home first, then refresh the affected data file by RMAN with a copy of this file from production – and restart MRP again.

Patch 21923026 is available on top of a plain Oracle 12.1.0.2 installation but also on top of several PSUs and BPs. Please check the RELEASE selection of the patch download for bug 21923026

Further Information

• ALERT
  MOS Note: 2058461.1
  ALERT Bug 21923026 Corruption during Recovery after upgrading to 12c for Compressed Tables
• Patch
  Download for bug 21923026
• MOS Note: 21923026.8
  Bug 21923026 – Corruption during Recovery after upgrading to 12c for Compressed Tables
• BUG 21923026
  ORA-600 [OLTP COMPRESSION SANITY CHECK]
  (non published bug – therefore no link added)
  .
• Superseeded — MOS Note: 21682261.8
  Bug 21682261 – Corruption during Recovery after upgrading to 12c for Compressed Tables – Superseded

• Bug 21682261
  ORA-600 [KDBLKCHECKERROR]..[6126] OR [6110] FOR COMPRESS BLOCK DURING RECOVERY
  (non published bug – therefore no link added)

–Mike