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

3 comments:

  1. 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
  2. Foreigner Call Girls in Mahipalpur are young, intelligent,Call Girls in Faridabad beautiful body curves and well-behaved that everybody looks forward to in their female partner.Call Girls in Faridabad They know well how to dress and project themselves,Mature Housewife Escorts in Agra depending on the place of the outing.Mature Housewife Escorts in Agra Some people travel in groups while some prefer to travel alone. If you belong to the second category then you may call us and we’ll give you a perfect partner for your trip,Mature Housewife Escorts in Ajmer are ready to accompany you everywhere.

    ReplyDelete
  3. By entering the information from their CBSE 10th admit card, students may obtain the CBSE 10th Important Question Paper 2023 on the website. The details regarding the CBSE Xth Model Question Paper 2023 for all regions and the CBSE 10th exam Important Question Paper 2023 date are provided here. For the academic year 2023, CBSE 10th Model Paper 2023 the board examination for class X will be held in all regions in March by the Central Board of Secondary Education, New Delhi. Class X and XII public exams will be administered by the CBSE board. The CBSE Board also administers admission tests like

    ReplyDelete