ORA-16014: log 1 sequence# not archived, no available dest

Error Description:

There was a request from one of my client to restart an Oracle Instance hosted on Windows Server. I restarted the instance successfully without any issue. But while executing "alter system switch logfile" command, archive process got stuck and it was taking long time to switch logfile. Then I tried to restart the instance once more, but after mount stage it got terminated and unable to start.

As per error message "ORA-16014", what do you think? Either Archive log destination is full or it is not available. Right? Here is the different case. Archivelog destination was set properly and also, sufficient free space was available in archivelog destination. What can be the issue?

SQL> alter system switch logfile;

Snap from Alert Log File: Ping without log force is disabled:   instance mounted in exclusive mode. Endian type of dictionary set to little ARCH: LGWR is scheduled to archive to LAD:3 after log switch 2018-04-21T16:50:40.551256+05:30 Errors in file D:\APP\ORACLE\diag\rdbms\test_pry\test\trace\test_ora_6124.trc: ORA-16014: log 1 sequence# 6379 not archived, no available destinations ORA-00312: online log 1 thread 1: 'D:\APP\ORACLE\ORADATA\TEST\REDO01.LOG' USER (ospid: 6124): terminating the instance due to error 16014 ERROR: Unable to normalize symbol name for the following short stack (at offset 125): dbgeGetAsyncActivForED()+939<-dbkif_record_dde_actions()+421<-ksuitm_opt()+2482<-ksuitm() +14<-kcfopd()+10758<-adbdrv_options..0()+38247<-adbdrv()+161 <-opiexe()+29912<-opiosq0()+6886<-kpooprx()+464<-kpoal8()+1173<-opiodr()+1378<-ttcpip()+ 1368<-opitsk()+2474<-opiino()+1265<-opiodr()+1378<-opidrv()+1833<-sou2o()+112<-opimai_real()+ 499<-opimai()+231<-OracleThreadStart()+745<-00007FF8B91A13D2<-00007FF8BAC454E4 2018-04-21T16:50:40.629375+05:30 System state dump requested by (instance=1, osid=6124), summary=[abnormal instance termination]. System State dumped to trace file D:\APP\ORACLE\diag\rdbms\test_pry\test\trace\test_diag_4196_20180421165040.trc 2018-04-21T16:50:44.600298+05:30 Instance terminated by USER, pid = 6124 2018-04-21T16:51:28.893259+05:30 Starting ORACLE instance (normal) (OS id: 3824) 2018-04-21T16:51:28.908883+05:30 CLI notifier numLatches:19 maxDescs:1610 2018-04-21T16:51:28.924509+05:30

I started the instance in mount stage and checked both primary and standby dataguard configuration and I identified the issue.

On Primary: SQL> select dest_name,error status from v$archive_dest where dest_id in(1,3); DEST_NAME            ERROR  STATUS -------------------- ------ ----------- log_archive_dest_1          BAD_PARAM log_archive_dest_3          VALID On Standby: SQL> select dest_name,error status from v$archive_dest where dest_id in(1,3); DEST_NAME           ERROR  STATUS -------------------------- --------- log_archive_dest_1         VALID log_archive_dest_3         BAD_PARAM Primary: Database Name: TEST db_unique_name: TEST_PRY Standby: Database Name: TEST db_unique_name: TEST_STD

Note: Here standby database destination is set to dest_3.

On Primary: SQL> show parameter log_archive_dest_1 NAME                 TYPE    VALUE -------------------- ------- --------------------------------------------------------------------------- log_archive_dest_1   string  LOCATION=E:\Archives  DB_UNIQUE_NAME=TEST VALID_FOR(ALL_LOGFILES,ALL_ROLES)                                                        SQL> show parameter dest_3 NAME                TYPE    VALUE ------------------- ------- -------------------------------------------------------------------------------------- log_archive_dest_3  string  service=TEST LGWR ASYNC valid_for(ONLINE_LOGFILE,STANDBY_ROLE) db_unique_name=TEST_STD On Standby: SQL> show parameter log_archive_dest_1 NAME                TYPE   VALUE ------------------- ------ ------------------------------------------------------------------------------ log_archive_dest_1  string LOCATION=E:\ARCHIVES DB_UNIQUE_NAME=TEST_STD VALID_FOR=ALL_LOGFILES,ALL_ROLES)                                                               SQL> show parameter dest_3 NAME                 TYPE    VALUE -------------------- ------- ----------------------------------------------------------------------------------- log_archive_dest_3   string  service=TEST LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=TEST The db_unique_name marked in red colour on both primary and standby database is the issue which was wrongly specified. The correct db_unique_name in the database was TEST_PRY.

I took the backup of these parameters before resetting them. I changed below parameters on primary and standby databases and restarted the instance, but again faced below different error message.

On Primary: SQL> alter system set log_archive_dest_1='LOCATION=E:\Archives DB_UNIQUE_NAME=TEST_PRY VALID_FOR= (ALL_LOGFILES,ALL_ROLES)' scope=spfle; On Standby: SQL> alter system set log_archive_dest_3 ='service=TEST LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=TEST_PRY' ORA-16057 : server not in Data Guard configuration On Primary: SQL> select dest_name,error status from v$archive_dest where dest_id in(1,3); DEST_NAME                 ERROR      STATUS -------------------- --------------------------------------------------- ------ log_archive_dest_1                        VALID log_archive_dest_3    ORA-16057 : server not in Data Guard configuration    ERROR I checked below parameter settings in both primary and standby database which was wrongly set.  On Primary and Standby: SQL> show parameter log_archive_config NAME                 TYPE   VALUE -------------------- ------ ------------------------- log_archive_config   string dg_config=(TEST,TEST_STD) Again I changed these parameters in both primary and standby databases and restarted the instance. On Primary and Standby: SQL> alter system set log_archive_config='dg_config=(TEST_PRY,TEST_STD)' scope=spfile;

Now I am able to switch logfile without any issue and also all primary and standby destinations are valid without any error.

On Primary: SQL> select dest_name,error status from v$archive_dest where dest_id in(1,3); DEST_NAME           ERROR  STATUS -------------------------- -------- log_archive_dest_1         VALID log_archive_dest_3     VALID On Standby: SQL> select dest_name,error status from v$archive_dest where dest_id in(1,3); DEST_NAME          ERROR  STATUS ------------------------- ------ log_archive_dest_1        VALID log_archive_dest_3        VALID SQL> alter system switch logfile; System altered  SQL> / System altered  SQL> / System altered  SQL> select dest_id,max(sequence#) from v$archived_log group by dest_id; DEST_ID       MAX(SEQUENCE#) -----------      ------------      1                6389      3                6389

Thanks for reading this post ! Please comment if you like this post ! Click on FOLLOW to get future blog updates !