本文主要介绍，在rac环境下， 设置不同的sga，可能导致cdb模式数据库出现相关bug。

适用版本范围：Oracle Database - Enterprise Edition - Version 12.2.0.1 to 18.14.0.0.0 [Release 12.2 to 18]

问题描述：
在多租户CDB/PDB RAC数据库环境中，如果实例有不同的SGA大小，并且至少有一个实例的SGA大于128GB，那么可能会发生ORA-00600 [4506] / ORA-00600 [ktubko_1] 表现的错误或损坏。

这个问题没有出现在以下环境或版本：
-   Oracle 12.1.0.2, 19c and above
-    [or] Non-CDB database
-    [or] Single instance database                                
-    [or] same SGA size across instances of the same RAC database

现象：
如果任何一个RAC实例的SGA大小大于128GB，而另一个实例的SGA大小不同，那么可能导致实例之间的某些同步不完整或不正确。如：
```
Thread 1

sga_max_size          = 80G
 sga_target               = 80G

Thread 2

sga_target               = 242G

Thread 3

sga_max_size          = 80G
 sga_target               = 80G
```

症状、报错：
ORA-600 [4506] errors, mixed with ORA-600 [ktubko_1]

Different SGA Size greater than 128GB on Each Instance on RAC Database
 
 解决办法：
 在RAC数据库的所有实例中使用相同的sga_max_size。这个参数的改变需要重新启动实例。

只要没有手动设置"_kgl_message_locks "和 "target_pdbs "这两个参数，"sga_max_size "参数就能发挥作用。

如果遇到以上错误，也可以通过以下办法解决：
```
Please Perform Step 1 and 2
Step 1 -

Optionally and temporarily disable transaction recovery to keep the database open to perform further investigation

Since the database may crash because SMON is failing, then to execute the steps 1 and 2 it may be needed to temporarily disable transaction recovery

by either setting one of the next parameters in the init.ora:

_smu_debug_mode=1024

or

event="10513 trace name context forever, level 2"

Then open the instance.

Step 2 - Identify the affected segments

Object Id for the affected segments are printed in the alert log with next message:

Error <error number> encountered while recovering transaction (usn, slot) on object nnnnnn

then use object_id=nnnnnn in dba_objects (dictionary object id):

select * from dba_objects where object_id = nnnnnn;

If the above query does not return a row, it means that the object was dropped and may remain in the recycle bin; query recyclebin$ using obj# = object id.

If recyclebin$ does not return a row, check obj$.  If none of them return rows, then the segment is already dropped for good and the issue should have been resolved for that segment.

A grep can be performed to identify all the segments: grep 'Error.*encountered while recovering transaction' alert.log

Solution 1 -
     Based on Step 1 and 2 identify the affected object and object_type.

1.1 If affected object_type is index please drop and re-create Index

1.2 If affected object_type as Table/Table Partition

1.2.1. please take export backup of Table/Table Partition, truncate the table and import the table.

or

1.2.2 please move the Table/Table Partition online with in tablespace.

If a logical inconsistency can be afforded, repair the problem by recreating the segments.

Solution 2 -  
    Point-In-Time-Recover or Flashback can perform if we know exact time of issue or corruption happened
     There might be situations where applications cannot afford having such corruption errors.

For those cases the options are:

Option 1: Flashback database before the issue (if flashback is enabled).
          Option 2: Point In time Recovery (PITR)
          Option 3: Failover to a standby before the time for the corruption

Final Steps -

Once the error is resolved please execute the below steps to re-enable transaction recovery

1. Set _smu_debug_mode to its original value 
           alter system reset "_smu_debug_mode" scope=both sid='*';

Or

2.remove the event 10513 from the parameters file.

This is only needed if transaction recovery was disabled using event 10513.

Note that if tx recovery was disabled through "_smu_debug_mode"=1024, then unsetting event 10513 will not re-enable tx recovery

After enabling transaction recovery monitor to check if there are still dead transactions:

SELECT KTUXESTA "STATUS" ,KTUXEUSN||'.'||KTUXESLT||'.'||KTUXESQN XID ,KTUXERDBF "FILE" ,KTUXERDBB "BLOCK"

FROM SYS.X$KTUXE

WHERE KTUXECFL = 'DEAD'

ORDER BY KTUXESIZ;
```

相关bug补丁：
这个问题已被报告为。 
    Bug 32347014。ora-600[4506], ora-600[ktubko_1]发生，实例崩溃了

在撰写本文件时，上述错误目前尚未解决，正在由开发部门调查。

ITPub博客

【BUG】ORA-00600 [4506] [ktubko_1] CDB模式，rac下不同的sga值会触发相关bug

最新文章