[Oracle] Running SAP with Oracle Data Guard 11.2.0...

Introduction

You are allowed to run your Oracle database with Oracle Data Guard (physical standby database) by SAP. Oracle itself is publishing "Data Guard Physical Recommended Patch Bundles", but there is no patch bundle available for 11.2 until now. The following blog will describe one ciritcal issue, that we hit after we have upgraded to Oracle 11.2.0.2 (SBP 112022_201105) and running a physical standby database. We also hit a few other issues and fixed them in our test environment before Go-Live, but these issues depend on your configuration. I will focus on one general critical issue only, that you can't easily "simulate" in a non productive-environment.

The configuration

We are running a physical standby database in LGWR SYNC mode without Active Data Guard. The average shipped log volume is round about 3-4 MB per second.

DGMGRL> show configuration;

Configuration - SAP_ERP_P01_DG

Protection Mode: MaxAvailability
Databases:
p01 - Primary database
s01 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

The issue

After we upgraded our primary database, rebuilt and recovered our standby database - we note a huge CPU load on our standby database server.

The following CPU load graph illustrates a Power 6 CPU based environment in snapshot average of 5 mins. If you are taking some live monitored data the CPU usage is much higher in peak - up to round about 5 to 6 CPUs for round about 2 minutes by running in recovery mode only.

After some researches we found out, that the hight CPU load was caused by the DBWR. If the CPU load increases - you can see "latch free" events for the DBWR processes only.

SQL> select SID, SERIAL#, STATUS, PROCESS, PROGRAM, EVENT from v$session where PROGRAM like '%DBW%';
       SID    SERIAL# STATUS   PROCESS                  PROGRAM                                          EVENT
---------- ---------- -------- ------------------------ ------------------------------------------------ ----------------------------------------------------------------
            1          1 ACTIVE   491670                   oracle@<hostname> (DBWA)                           latch free
          89          1 ACTIVE   417928                   oracle@<hostname> (DBWB)                           latch free
        881          1 ACTIVE   311336                   oracle@<hostname> (DBW0)                           latch free
        969          1 ACTIVE   217102                   oracle@<hostname> (DBW1)                           latch free
      1057          1 ACTIVE   209030                   oracle@<hostname> (DBW2)                           latch free
      1145          1 ACTIVE   471058                   oracle@<hostname> (DBW3)                           latch free
      1233          1 ACTIVE   164014                   oracle@<hostname> (DBW4)                           latch free
      1321          1 ACTIVE   368716                   oracle@<hostname> (DBW5)                           latch free
      1409          1 ACTIVE   532540                   oracle@<hostname> (DBW6)                           latch free
      1497          1 ACTIVE   606256                   oracle@<hostname> (DBW7)                           latch free
      1585          1 ACTIVE   516224                   oracle@<hostname> (DBW8)                           latch free

The fix

As we hit the bug #11866952 - Oracle already know about this issue and provided a patch for Solaris and Linux. Unfortunately we were running on AIX - so we need to request a backport, which was provided within a few hours.

Today Oracle provides this patch for AIX, Linux and Solars on 11.2.0.2 and for Linux on 11.2.0.1. If you are running Oracle on another platform you should request a backport before using Data Guard in your productive environment.

Fortunately this patch is fully compatible with the SBP 112022_201105.