[ID 969002.1]

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.0. - Release: 10.2 to 11.2
Information in this document applies to any platform.
This applies to 10.2 to 11.2 physical standby database with maximum availability protection mode.

Symptoms

The primary database runs in MAXIMUM AVAILABILITY protection mode. After loosing connectivity to the physical standby database, the protection level goes to RESYNCHRONIZATION, and remains at RESYNCHRONIZATION even after the connectivity to the standby is reestablished. You wonder when the protection level will go back to MAXIMUM AVAILABILITY.

Changes

The network connection between the primary and the physical standby database is disconnected for some reason.

Cause

It is normal for you to see the protection_level goes to RESYNCHRONIZATION from MAXIMUM AVAILABILITY when the network connection disconnects between the primary and the standby.

The reason the protection_level remains RESYNCHRONIZATION is because the standby database falls behind the primary. When the standby is in-sync with the primary, the protection_level will be changed back to MAXIMUM AVAILABILITY from RESYNCHRONIZATION.

You could check the protection_level from v$database from either the primary or the standby database.

SQL> select protection_mode, protection_level from v$database;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY RESYNCHRONIZATION

You could check if the physical standby database falls behind the primary database by the following.

On primary

SQL> select thread#, sequence#, status from v$log;

THREAD# SEQUENCE# STATUS
---------- ---------- ----------------
5474 INACTIVE
5473 INACTIVE
5475 CURRENT
5391 INACTIVE
5392 CURRENT
5390 INACTIVE
5470 INACTIVE
5471 INACTIVE
5472 CURRENT

rows selected.

On standby

SQL> select thread#, max(sequence#) from v$archived_log where applied='YES' group by thread#;

THREAD# MAX(SEQUENCE#)
---------- --------------
1 5472
2 5389
3 5469

As you can see the last applied sequence# on the standby is smaller than the current sequence# on the primary for each thread.

Solution

Switch logfile on each primary instance.

Please don't switch logfile too frequent on each instance at a time, otherwise, when all online redo log files are not archived, that could cause the primary database hangs.

SQL>alter system switch logfile;

You could use the queries in the CAUSE section to determine if the physical standby database is in-sync with the primary database.

ITPub博客

PROTECTION_LEVEL being RESYNCHRONIZATION in a DATA GUARD environment