Wednesday, March 11, 2020

ORA-16416: No viable Physical Standby switchover targets available

You will see this error when you are trying to switchover from Primary to Standby, you will see the error when log_archive_dest_n not ready at standby or there is some issue with REDO log.

Also note that you will see this error if f there is an archive gap, that prevents switchover because of which the operation fails

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY
*
ERROR at line 1:
ORA-16416: No viable Physical Standby switchover targets available

Error from standby alert.log file

.... (PID:24072): No viable Physical Standby switchover targets available
ORA-16416 signalled during: ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY...
2020-02-27T19:36:42.425842-08:00
rfs (PID:36824): Database mount ID mismatch [0x5a5dee6f:0x5a5d7a7b] (1516105327:1516075643)
rfs (PID:36824): Not using real application clusters
2020-02-27T19:38:17.144104-08:00
.... (PID:24072): Using STANDBY_ARCHIVE_DEST parameter default value as /oraarch/ORCLP krsd.c:18141]
2020-02-27T19:38:17.154204-08:00
ALTER SYSTEM SET log_archive_dest_2='SERVICE=ORCLP AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLP' SCOPE=BOTH;
2020-02-27T19:38:21.552799-08:00
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY
2020-02-27T19:38:21.552940-08:00
TMI: adbdrv kcv_primary_switchover BEGIN 2020-02-27 19:38:21.552864
2020-02-27T19:38:21.553038-08:00
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 24072] (ORCLP)
.... (PID:24072): Waiting for target standby to receive all redo
.... (PID:24072): Waiting for target standby to receive all redo
2020-02-27T19:38:21.553572-08:00
.... (PID:24072): Waiting for all non-current ORLs to be archived
2020-02-27T19:38:21.553661-08:00
.... (PID:24072): All non-current ORLs have been archived
2020-02-27T19:38:21.553741-08:00
.... (PID:24072): Waiting for all FAL entries to be archived
2020-02-27T19:38:21.553820-08:00
.... (PID:24072): All FAL entries have been archived
2020-02-27T19:38:21.553924-08:00
.... (PID:24072): Waiting for potential Physical Standby switchover target to become synchronized
2020-02-27T19:38:21.598598-08:00
Errors in file /home/oracle/diag/rdbms/ORCLP_stdby/ORCLP/trace/ORCLP_tt00_16934.trc:
ORA-16009: invalid redo transport destination
2020-02-27T19:38:21.598892-08:00
TT00 (PID:16934): krsg_check_connection: Error 16009 connecting to standby 'ORCLP'
2020-02-27T19:38:22.554301-08:00


Run below command to find out the Archive Destination status errors..

SQL> select DEST_ID, STATUS, DESTINATION from V$ARCHIVE_DEST_STATUS;
SQL> select DEST_ID,DEST_NAME,STATUS,TARGET,DESTINATION,ERROR,ALTERNATE from v$archive_dest

SQL> select GROUP#,THREAD#,BYTES/1024/1024/1024,ARCHIVED,STATUS from v$standby_log;

Also run below command to identify the gap

SQL> SELECT THREAD#, SEQUENCE# FROM V$THREAD;
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

After running the above commands analyzing the logs I see that the error due to invalid REDO LOG destination. I have created the directories as same as Primary on Standby site.

I also come across issue with LOG_ARCHIVE_DEST_2, after fixing the parameter it worked fine. Also, make sure LOG_ARCHIVE_DEST_STATE_2 enabled.

ORA-16474: target_db_name not found in the LOG_ARCHIVE_DEST_n parameter

I would strongly advise to use Data Guard Broker, it minimizes the errors, and switchover is very easy.

Refer below Data Guard presentation
Webinar: Data Guard Physical Standby Setup in Oracle?

Regards
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com

2 comments:

  1. Packers And Movers Bangalore Local Household Shifting Service, Get Free Best Price Quotes Local Packers and Movers in Bangalore List , Compare Charges, Save Money And Time.@
    Packers And Movers in Bangalore

    ReplyDelete
  2. You'll give new inspiration to their workouts, moving them to get fit and remain fit. Dynamic loved ones from amateurs to enthusiasts will thank you for quite a long time to come! fitnessbond

    ReplyDelete