SWITCHOVER VERIFY WARNING: no standby redo logfiles configured.

PROD Server and PROD Database

oraclelab1.localdomain.com - DEVDB (PROD)

DR Server and DR Database

oraclelab3.localdomain.com- DRDB (DR)

Issue:

Dataguard switch over failing with Error Message "SWITCHOVER VERIFY WARNING: no standby redo logfiles configured. Standby redo logfiles are recommended configuration for physical standby database."

Error Message:

SWITCHOVER VERIFY WARNING: no standby redo logfiles configured. Standby redo logfiles are recommended configuration for physical standby database.

Cause:

No standby redo logs were created on the standby database

Fix:

Create standby redo logs to fix this issue

Error Logs and commands output:

1. Switchover verify command failed at primary database or PROD database.

[oracle@oraclelab1 2025_05_07]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 7 10:18:33 2025

Version 19.17.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

SQL> alter database switchover to DRDB verify;

alter database switchover to DRDB verify

ERROR at line 1:

ORA-16475: succeeded with warnings, check alert log for more details

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

[oracle@oraclelab1 2025_05_07]$

2. Error Message reported on the PROD database alert log

[oracle@oraclelab1 2025_05_07]$ locate alert_DEVDB.log

/u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/alert_DEVDB.log

[oracle@oraclelab1 2025_05_07]$ tail -100f /u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/alert_DEVDB.log

2025-05-07T10:19:36.656480+05:30

alter database switchover to DRDB verify

2025-05-07T10:19:36.705809+05:30

SWITCHOVER VERIFY WARNING: no standby redo logfiles configured. Standby redo logfiles are recommended configuration for physical standby database.

SWITCHOVER VERIFY: Send VERIFY request to switchover target DRDB

SWITCHOVER VERIFY WARNING: switchover target's apply lag is more than 300 seconds. It takes time for the recovery to catch up. This may slow down switchover process.

3. Connected primary database and verified the standby redo logs on the primary side and created standby redo log on primary database.

[oracle@oraclelab1 2025_05_07]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 7 10:21:24 2025

Version 19.17.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 ('/u01/app/oracle/oradata/DEVDB/onlinelog/standby_redo01.log','/u01/app/oracle/fast_recovery_area/DEVDB/onlinelog/standby_redo01_1.log') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 ('/u01/app/oracle/oradata/DEVDB/onlinelog/standby_redo02.log','/u01/app/oracle/fast_recovery_area/DEVDB/onlinelog/standby_redo02_2.log') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 6 ('/u01/app/oracle/oradata/DEVDB/onlinelog/standby_redo03.log','/u01/app/oracle/fast_recovery_area/DEVDB/onlinelog/standby_redo03_3.log') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7 ('/u01/app/oracle/oradata/DEVDB/onlinelog/standby_redo04.log','/u01/app/oracle/fast_recovery_area/DEVDB/onlinelog/standby_redo04_4.log') SIZE 200M;

Database altered.

SQL>

SQL> set pages 1000 lines 1000

SQL> col member for a50

SQL> col INSTANCE for a10

SQL> SELECT t.INSTANCE, l.THREAD#, l.GROUP#, l.SEQUENCE#, l.bytes, l.archived, l.status, lf.MEMBER

FROM v$log l, v$logfile lf, v$thread t

WHERE t.THREAD# = l.THREAD#

AND l.GROUP# = lf.GROUP#

ORDER BY l.THREAD#, GROUP#;

INSTANCE THREAD# GROUP# SEQUENCE# BYTES ARC STATUS MEMBER

---------- ---------- ---------- ---------- ---------- --- ---------------- --------------------------------------------------

DEVDB 1 1 91 209715200 YES INACTIVE /u01/app/oracle/oradata/DEVDB/onlinelog/o1_mf_1_my1qjolz_.log

DEVDB 1 1 91 209715200 YES INACTIVE /u01/app/oracle/fast_recovery_area/DEVDB/onlinelog/o1_mf_1_my1qjosy_.log

DEVDB 1 2 92 209715200 NO CURRENT /u01/app/oracle/oradata/DEVDB/onlinelog/o1_mf_2_my1qjomo_.log

DEVDB 1 2 92 209715200 NO CURRENT /u01/app/oracle/fast_recovery_area/DEVDB/onlinelog/o1_mf_2_my1qjoyf_.log

DEVDB 1 3 90 209715200 YES INACTIVE /u01/app/oracle/oradata/DEVDB/onlinelog/o1_mf_3_my1qjonr_.log

DEVDB 1 3 90 209715200 YES INACTIVE /u01/app/oracle/fast_recovery_area/DEVDB/onlinelog/o1_mf_3_my1qjpft_.log

6 rows selected.

SQL>

SQL> set pages 1000

SQL> set lines 1000

SQL> col DBID for a10

SQL> select * from v$standby_log;

GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME CON_ID

---------- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ --------- ----------

4 UNASSIGNED 1 0 209715200 512 0 YES UNASSIGNED 0

5 UNASSIGNED 1 0 209715200 512 0 YES UNASSIGNED 0

6 UNASSIGNED 1 0 209715200 512 0 YES UNASSIGNED 0

7 UNASSIGNED 1 0 209715200 512 0 YES UNASSIGNED 0

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

[oracle@oraclelab1 2025_05_07]$

4. Connected Standby database and created the standby redo logs.

[oracle@oraclelab3 2025_05_07]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 7 10:25:58 2025

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> set pages 1000

SQL> set lines 1000

SQL> col DBID for a10

SQL> select * from v$standby_log;

no rows selected

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo01.log','/u01/app/oracle/fast_re covery_area/DRDB/onlinelog/standby_redo01_1.log') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo02.log','/u01/app/oracle/fast_re covery_area/DRDB/onlinelog/standby_redo02_2.log') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 6 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo03.log','/u01/app/oracle/fast_recover y_area/DRDB/onlinelog/standby_redo03_3.log') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo04.log','/u01/app/oracle/fast_recover y_area/DRDB/onlinelog/standby_redo04_4.log') SIZE 200M;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> set pages 1000

SQL> set lines 1000

SQL> col DBID for a10

SQL> select * from v$standby_log;

GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAS T_CHANGE# LAST_TIME CON_ID

---------- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- --- --------- --------- ----------

4 UNASSIGNED 1 0 209715200 512 0 YES UNASSIGNED 0

5 UNASSIGNED 1 0 209715200 512 0 YES UNASSIGNED 0

6 UNASSIGNED 1 0 209715200 512 0 YES UNASSIGNED 0

7 UNASSIGNED 1 0 209715200 512 0 YES UNASSIGNED 0

SQL>

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@oraclelab3 2025_05_07]$

5. Rerun the switchover verify command which is complete successfully

[oracle@oraclelab1 2025_05_07]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 7 10:29:00 2025

Version 19.17.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

SQL> alter database switchover to DRDB verify;

Database altered.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

[oracle@oraclelab1 2025_05_07]$

Mallik034 (Vismo Technologies)

Wednesday, May 7, 2025