We have three physical standby databases. Two of them always show zero outstanding standby logs and the lag time is 00:00:00. One of the standby databases always shows one remaining standby log and the lag time continues to climb. We had to set the archive_lag_target parameter on the primary to prevent the lag from climbing above 10 minutes. The archive_lag_target is not set on the other primary databases.
We have been scratching our heads trying to figure out why this one physical standby seems to hold on to one log in the standby logs and doesn’t flush it out immediately like the other standby logs.
When we display the v$applied query there is a difference in the way oracle reports the current applied log.
Here is the display for the databases with zero remaining standby logs:
SYS@SBUAAPI_1 > select thread#, sequence#,applied from v$archived_log where applied <> ‘YES’;
THREAD# SEQUENCE# APPLIED
1 15573 IN-MEMORY
SYS@SBUAAPI_1 >
Here is the display for the database with the single remaining standby log
SYS@SBPROD_1 > select thread#, sequence#,applied from v$archived_log where applied <> ‘YES’;
THREAD# SEQUENCE# APPLIED
2 219129 NO
SYS@SBPROD_1 >
We have queried the Oracle Support Knowledge base but have not found anything related to this issue.
We would like to fix the one standby database so it behaves like the other two.