本文主要介紹，在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]發生，例項崩潰了

在撰寫本檔案時，上述錯誤目前尚未解決，正在由開發部門調查。</error>

【BUG】ORA-00600 [4506] [ktubko_1] CDB模式，rac下不懂sga會觸發相關bug

相關文章