Hi. We’ve got a 12c Data Guard environment with primary and physical standby. Although real time apply is enabled, it’s not working and transactions are only being applied after a log switch
SQL> select DEST_NAME,STATUS,DATABASE_MODE,RECOVERY_MODE
from V$ARCHIVE_DEST_STATUS
where status!='INACTIVE';
Primary:
DEST_NAME STATUS DATABASE_MODE RECOVERY_MODE
LOG_ARCHIVE_DEST_1 VALID OPEN IDLE
LOG_ARCHIVE_DEST_2 VALID MOUNTED-STANDBY MANAGED REAL TIME APPLY
In Data Guard Manager, SHOW DATABASE for the standby shows:
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 6 hours 57 minutes 7 seconds (computed 41 seconds ago)
Apply Lag: 6 hours 57 minutes 7 seconds (computed 41 seconds ago)
Average Apply Rate: 65.00 KByte/s
Real Time Query: OFF
Instance(s):
hrp
Database Warning(s):
ORA-16853: apply lag has exceeded specified threshold
ORA-16855: transport lag has exceeded specified threshold
ORA-16857: member disconnected from redo source for longer than specified threshold
Database Status:
WARNING
The transport and apply lags reset to 0 when there is a logfile switch, but then continue to increase until the next switch.
After reading the Oracle docs and previous answers to similar problems, I checked that the log_mode on the standby is ARCHIVELOG, and checked that the log files are the same size and that the number of standby redo logs is one greater than the redo logs:
SQL> select group#, sequence#, bytes, members, status from v$log;
Primary:
GROUP# SEQUENCE# BYTES MEMBERS STATUS
11 71442 104857600 2 INACTIVE
12 71443 104857600 2 INACTIVE
13 71444 104857600 2 CURRENT
14 71440 104857600 2 INACTIVE
15 71441 104857600 2 INACTIVE
Standby:
GROUP# SEQUENCE# BYTES MEMBERS STATUS
11 70087 104857600 2 ACTIVE
12 70083 104857600 2 INACTIVE
15 70086 104857600 2 INACTIVE
14 70085 104857600 2 INACTIVE
13 70084 104857600 2 INACTIVE
SQL> select group#,sequence#,bytes,used,status from v$standby_log;
Primary:
GROUP# SEQUENCE# BYTES MEMBERS STATUS
31 0 104857600 0 UNASSIGNED
32 0 104857600 0 UNASSIGNED
33 0 104857600 0 UNASSIGNED
34 0 104857600 0 UNASSIGNED
35 0 104857600 0 UNASSIGNED
36 0 104857600 0 UNASSIGNED
Standby:
GROUP# SEQUENCE# BYTES MEMBERS STATUS
31 0 104857600 0 UNASSIGNED
32 0 104857600 0 UNASSIGNED
33 0 104857600 0 UNASSIGNED
34 0 104857600 0 UNASSIGNED
35 0 104857600 0 UNASSIGNED
36 0 104857600 0 UNASSIGNED
Background: Everything was running as intended until a couple of weeks ago when a new datafile was created on the primary where the parameter standby_file_management was set to AUTO; unfortunately it was set to MANUAL on the standby. Logs weren’t applied and the fast recovery area on the standby filled up. We’ve managed to apply the logs to the standby so there’s no gap, but just can’t get real time apply to kick back in.
Your help would be much appreciated – thanks.