ORA-16789: standby redo logs configured incorrectly

1. Identify Issue - Broker Configuration reports WARNINGS in standby database

 DGMGRL> show configuration
 Configuration - xxxprd_dgconfig
 Protection Mode: MaxPerformance
 Members:
      dtxxxprof - Primary database
      agxxxprof - Physical standby database
          Warning: ORA-16809: multiple warnings detected for the database

Fast-Start Failover: DISABLED Configuration Status: WARNING (status updated 29 seconds ago)


2. Identify Issue - Drill down deeper for database reporting WARNINGS and we see issue with standby redo logs being reported.

 DGMGRL> show database agxxxprof

 Database - agxxxprof
 Role: PHYSICAL STANDBY
 Intended State: APPLY-ON
 Transport Lag: 0 seconds (computed 1 second ago)
 Apply Lag: 0 seconds (computed 1 second ago)
 Average Apply Rate: 2.12 MByte/s
 Real Time Query: OFF
 Instance(s):
      xxxprof1
            Database Warning(s): ORA-16789: standby redo logs configured incorrectly
 Database Status: WARNING

3. Check v$log to check the current redo logs, number of them and their sizes

 select group#, thread#, status, bytes/1024/1024 from v$log ;
 5 1 UNUSED 4096
 2 1 UNUSED 4096
 1 1 UNUSED 4096
 4 2 UNUSED 4096
 6 2 UNUSED 4096
 3 2 UNUSED 4096

4. Check the v$standby_log for standby redo logs, number of them and their sizes

select group#, thread#, status, bytes/1024/1024 from v$standby_log ; 

GROUP# THREAD# STATUS     BYTES/1024/1024 
------ ------- ---------- --------------- 
 20    1       UNASSIGNED 32768 
 21    1       ACTIVE     32768 
 22    1       UNASSIGNED 32768 
 23    1       UNASSIGNED 32768 
 25    2       ACTIVE     32768 
 26    2       UNASSIGNED 32768 
 27    2       UNASSIGNED 32768 
 28    2       UNASSIGNED 32768

5. Compare the outputs, Note size does not match redo log file sizes so these are not correct also note the number of standby redo logs is not matching the redo logs

6. Now we can fix the issue, first Stop the managed recovery of standby database

 alter database recover managed standby database cancel ;

7. create a new set of standby redo logs that match the redo logs, number of them per thread and their size

alter database add standby logfile thread 1 group 31 size 4294967296 ;
alter database add standby logfile thread 1 group 32 size 4294967296 ;
alter database add standby logfile thread 2 group 33 size 4294967296 ;
alter database add standby logfile thread 2 group 34 size 4294967296 ;
alter database add standby logfile thread 1 group 35 size 4294967296 ;
alter database add standby logfile thread 2 group 36 size 4294967296 ;


8. Now we can Remove the old standby redo logs not active, this is so we can e in a position to switch to the new logs and not stay in old logs

alter database drop standby logfile group 20 ;
alter database drop standby logfile group 22 ;
alter database drop standby logfile group 23 ;
alter database drop standby logfile group 26 ;
alter database drop standby logfile group 27 ;
alter database drop standby logfile group 28 ;


9. Restart the standby managed recovery again, so we can get out of the old standby redo logs that are still used.

 alter database recover managed standby database using current logfile disconnect ;


10. Check recovery catches up.

DGMGRL> show database agxxxprof

 Database - agxxxprof
 Role: PHYSICAL STANDBY
 Intended State: APPLY-ON
 Transport Lag: 0 seconds (computed 1 second ago)
 Apply Lag: 0 seconds (computed 1 second ago)
 Average Apply Rate: 3.14 MByte/s
 Real Time Query: OFF
 Instance(s):
      xxxprof1 Database Warning(s): ORA-16789: standby redo logs configured incorrectly
 Database Status: WARNING


11. Switch logfile in primary database instances to switch into new standby redo for each thread, In RAC must do in each instance so we cover all threads alter system switch logfile ; 12. recheck standby logs on standby note that the old ones are switched out of so we see the old standby redo logs are no longer ACTIVE.

select group#, thread#, status, bytes/1024/1024 from v$standby_log ; 
 GROUP# THREAD# STATUS     BYTES/1024/1024 
------- ------- ---------- --------------- 
21      1       UNASSIGNED 32768 
25      2       UNASSIGNED 32768 
31      1       ACTIVE     4096 
32      1       UNASSIGNED 4096 
33      2       ACTIVE     4096 
34      2       UNASSIGNED 4096 
35      1       UNASSIGNED 4096 
36      2       UNASSIGNED 4096


13. Now we Stop managed recovery of standby database so we can finish removing the old standby redo logs.

 alter database recover managed standby database cancel ;


14. Drop the remaining old standby redo logs

 alter database drop standby logfile group 21 ;
 alter database drop standby logfile group 25 ;


15. Restart managed recovery of standby database

 alter database recover managed standby database using current logfile disconnect ;


16. Check standby redo logs in standby database, we can see only the new standby redo logs we created.

select group#, thread#, status, bytes/1024/1024 from v$standby_log ;
 GROUP# THREAD# STATUS     BYTES/1024/1024 
------- ------- ---------- --------------- 
 31     1       ACTIVE     4096 
 32     1       UNASSIGNED 4096
 33     2       ACTIVE     4096 
 34     2       UNASSIGNED 4096 
 35     1       UNASSIGNED 4096 
 36     2       UNASSIGNED 4096 


17. Recheck Broker Configuration and see that we have addressed the issue reported

DGMGRL> show configuration

 Configuration - xxxprd_dgconfig
 Protection Mode: MaxPerformance
 Members:
       dtxxxprof - Primary database
       agxxxprof - Physical standby database

 Fast-Start Failover: DISABLED
 Configuration Status: SUCCESS (status updated 55 seconds ago)

 DGMGRL> show database agxxxprof

 Database - agxxxprof
 Role: PHYSICAL STANDBY
 Intended State: APPLY-ON
 Transport Lag: 0 seconds (computed 0 seconds ago)
 Apply Lag: 0 seconds (computed 0 seconds ago)
 Average Apply Rate: 1.57 MByte/s
 Real Time Query: OFF
 Instance(s):
       xxxprof1 Database Status: SUCCESS