<< 解決oracle 11g ORA-16191 | 回blog首頁 | 11g DGMGRL測試 >>

2009年06月29日

11g logical standby的一個bug

在測11g的 logical standby 遇到一個 ORA-600 ，沒想到最終的解決還是要先重做一次 logical standby了....
不過oracle也太不厚道了，從9i就知道這個問題，到11g了還有這個問題="=

metalink_link

Subject: GETTING ORA-00600: [KRVXBPX20], [1], [33244], [56], [16] IN SQL APPLY
  Doc ID: 761661.1 Type: PROBLEM
  Modified Date : 14-MAY-2009 Status: PUBLISHED

In this Document
  Symptoms
  Changes
  Cause
  Solution
  References

 

 

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.2 to 11.1.0.7
This problem can occur on any platform.

Symptoms

Customer has dataguard environment including a primary database, a physical standby database and a logical standby database. Customer did a switchover between the primary and the physical standby database. After that, the logical standby starts getting ORA-00600 [KRVXBPX20] when starting the sql apply.

Changes

Switchover or failover from primary to physical standby, then logical standby sql apply errors out with ORA-00600 [KRVXBPX20].


Cause

The ORA-00600: [KRVXBPX20] indicates that logical standby builder detects IMU (In Memory Undo) in the redo streams. Logical standby does not support IMU and enabling supplemental logging disables the IMU.

In customer's case, supplemental logging is enabled in the original primary database, but it is not enabled in the original physical standby database. Prior to 11.2 (which has not been released yet at the time of writing), supplemental logging DDLs are not propagated to the physical standby database. Thus they had a situation where the primary has supplemental logging set, but the physical standby did not.

Once they switched over or failover to the physical standby, it started generating redo without supplemental logging (including IMU), and hence the logical standby ran into the problem.

The same problem can also happen in an environment with only primary and logical standby. Consider the following scenario:

1). Start with a primary database A with supplemental logging enabled and physical standby B without supplemental logging enabled.
2). Switchover, which brings A to physical standby (with supplemental logging enabled) and B to primary (without supplemental logging enabled).
3). Convert the new physical standby A to logical standby.

Because now primary B does not have supplemental logging enabled, the logical standby A is exposed.

So whenever logical standby is involved, we need to make sure supplemental logging is enabled on  primary before it is open for business. Use the queries in next section to help achieve this.


Solution

If customer has run into ORA-00600 [KRVXBPX20], they have to rebuild the logical standby. Setting _in_memory_undo=false or enabling supplemental logging after running into this error will not help to get rid of this error, because the redo stream has already had the IMU. Using skip_transaction will not help either.


Solution:

Rebuild logical standby. In the future, make sure the supplemental logging is enabled on new primary after the switchover. Follow the steps below to make sure supplemental logging is enabled on new primary:


Instead of opening the original physical standby database following a switchover or a failover (once you have converted the physical standby to the primary by issuing "ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY" and you have issued a shutdown of the instance), do the following:

SQL> STARTUP MOUNT;
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN AS SUPP_LOG, SUPPLEMENTAL_LOG_DATA_PK as SUPP_PK,
SUPPLEMENTAL_LOG_DATA_UI as SUPP_UI FROM V$DATABASE;

SUPP_LOG SUPP_PK SUPP_UI
-------- ------- --------
NO       NO      NO

If any of the column returns NO, enable minimal and primary key/unique index supplemental logging at the database level:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

Now open the database

SQL> ALTER DATABASE OPEN;

At this point, the new primary database is ready for business.


Posted by my_work at 樂多Roodo! │22:33 │回應(0) │引用(0) │DB
樂多分類：網路/3C 共同主題：Oracle 工具：編輯本文
標籤：oracle
Ads by Roodo! 

引用URL

http://cgi.blog.roodo.com/trackback/9337627


回最上方