PROTECTION_LEVEL being RESYNCHRONIZATION in a DATA GUARD environment

[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
---------- ---------- ----------------
1 5474 INACTIVE
1 5473 INACTIVE
1 5475 CURRENT
2 5391 INACTIVE
2 5392 CURRENT
2 5390 INACTIVE
3 5470 INACTIVE
3 5471 INACTIVE
3 5472 CURRENT

9 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. 
请使用浏览器的分享功能分享到微信等