I've a two node rac setup with physical standby database. Primary remote log shipping failing with ORA-16057. Could you please go through the below settings and suggest me where I'm missing.
Alert Log of Primary Node 1:
====================
Error 16057 for archive log file 31 to 'KD0315S'
Errors in file /u00/app/kd0315p/diag/rdbms/kd0315p/KD0315P1/trace/KD0315P1_nsa2_5727.trc:
ORA-16057: server not in Data Guard configuration
Tue Jul 21 16:09:56 2020
Archived Log entry 234 added for thread 1 sequence 105 ID 0x89699a35 dest 1:
Tue Jul 21 16:09:57 2020
FAL[server, ARCn]: Error 16057 creating remote archivelog file 'KD0315S'
FAL[server, ARCn]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance KD0315P1 - Archival Error. Archiver continuing.
Tue Jul 21 16:14:44 2020
Completed checkpoint up to RBA [0x6a.2.10], SCN: 1704667
Tue Jul 21 16:15:06 2020
PING[ARC1]: Heartbeat failed to connect to standby 'KD0315S'. Error is 16057.
+
+
PING[ARC1]: Heartbeat failed to connect to standby 'KD0315S'. Error is 12514.
Tue Jul 21 16:46:43 2020
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
Tue Jul 21 16:46:43 2020
PING[ARC1]: Heartbeat failed to connect to standby 'KD0315S'. Error is 16057.
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='*';
Tue Jul 21 16:46:45 2020
Beginning log switch checkpoint up to RBA [0x6b.2.10], SCN: 1713341
Thread 1 advanced to log sequence 107 (LGWR switch)
Current log# 32 seq# 107 mem# 0: +DATA/kd0315p/onlinelog/group_32.273.1046183905
Current log# 32 seq# 107 mem# 1: +RECO/kd0315p/onlinelog/group_32.308.1046183905
Tue Jul 21 16:46:48 2020
ARC2: Archivelog destination LOG_ARCHIVE_DEST_2 disabled: destination Data Guard configuration error
Archived Log entry 235 added for thread 1 sequence 106 ID 0x89699a35 dest 1:
Tue Jul 21 16:50:40 2020
Alert log of Primary Node 2:
========================
Error 16057 for archive log file 41 to 'KD0315S'
Errors in file /u00/app/kd0315p/diag/rdbms/kd0315p/KD0315P2/trace/KD0315P2_nsa2_4832.trc:
ORA-16057: server not in Data Guard configuration
Tue Jul 21 16:09:56 2020
Archived Log entry 233 added for thread 2 sequence 116 ID 0x89699a35 dest 1:
Tue Jul 21 16:09:56 2020
FAL[server, ARCa]: Error 16057 creating remote archivelog file 'KD0315S'
FAL[server, ARCa]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance KD0315P2 - Archival Error. Archiver continuing.
Tue Jul 21 16:13:41 2020
Completed checkpoint up to RBA [0x75.2.10], SCN: 1704670
Tue Jul 21 16:15:55 2020
PING[ARC1]: Heartbeat failed to connect to standby 'KD0315S'. Error is 16057.
+
+
Beginning log switch checkpoint up to RBA [0x76.2.10], SCN: 1713345
Thread 2 advanced to log sequence 118 (LGWR switch)
Current log# 42 seq# 118 mem# 0: +DATA/kd0315p/onlinelog/group_42.276.1046183957
Current log# 42 seq# 118 mem# 1: +RECO/kd0315p/onlinelog/group_42.310.1046183957
Tue Jul 21 16:46:48 2020
ARC2: Archivelog destination LOG_ARCHIVE_DEST_2 disabled: destination Data Guard configuration error
======================================================================
Parameters set on Primary Database:
SQL> select INST_ID,name,open_mode,db_unique_name,database_role from gv$database;
INST_ID NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE
---------- --------- -------------------- ------------------------------ ----------------
1 KD0315P READ WRITE KD0315P PRIMARY
2 KD0315P READ WRITE KD0315P PRIMARY
SQL> col error for a50
SQL> select dest_id,error,status,log_sequence,applied_scn from v$archive_dest where dest_id=2;
DEST_ID ERROR STATUS LOG_SEQUENCE APPLIED_SCN
---------- -------------------------------------------------- --------- ------------ -----------
2 ORA-16057: server not in Data Guard configuration ERROR 106 0
SQL> sho parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string dg_config=(KD0315P,KD0315S)
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=KD0315S ASYNC AFFIRM V
ALID_FOR=(ONLINE_LOGFILES,PRIM
ARY_ROLE) DB_UNIQUE_NAME=KD031
5S
SQL> show parameter log_archive_dest_state_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string ENABLE
======================================================================
Parameters Set on Standby Database:
SQL> show parameter uniq
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string KD0315S
SQL> show parameter db_n
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string KD0315P
SQL> show parameter ve_c
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string DG_CONFIG=(KD0315S,KD0315SP)
SQL> show parameter ve_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=KD0315P LGWR ASYNC VAL
ID_FOR=(ONLINE_LOGFILES,PRIMAR
Y_ROLE) DB_UNIQUE_NAME=KD0315P
==============================================================================
TNSPING:
From Primary Node 1:
==============
[oracle@pmyws01 trace]$ tnsping KD0315S
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 21-JUL-2020 16:59:51
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/u00/app/kd0315p/product/11.2.0.4/KD0315P/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = crsprod-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = KD0315S.oracle.local) (UR = A) (CID=(PROGRAM=oracle)(HOST=pmyws01.oracle.local)(USER=soracle))))
OK (20 msec)
From Primary Node 2:
==============
[oracle@pmyws02 ~]$ tnsping KD0315S
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 21-JUL-2020 17:00:46
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/u00/app/kd0315p/product/11.2.0.4/KD0315P/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = crsprod-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = KD0315S.oracle.local) (UR = A) (CID=(PROGRAM=oracle)(HOST=pmyws01.oracle.local)(USER=soracle))))
OK (0 msec)
From Standby:
==============
[soracle@pmyws02 ~]$ tnsping KD0315P
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 21-JUL-2020 17:02:31
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/u00/app/kd0315s/product/11.2.0.4/KD0315S/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = crsprod-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = KD0315P.oracle.local) (UR = A)))
OK (10 msec)
[soracle@pmyws02 ~]$ tnsping KD0315P1
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 21-JUL-2020 17:02:33
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/u00/app/kd0315s/product/11.2.0.4/KD0315S/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = pmyws01-vip)(PORT = 1531)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = KD0315P.oracle.local) (SID = KD0315P1) (UR = A)))
OK (10 msec)
[soracle@pmyws02 ~]$ tnsping KD0315P2
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 21-JUL-2020 17:02:35
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/u00/app/kd0315s/product/11.2.0.4/KD0315S/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = pmyws02-vip)(PORT = 1531)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = KD0315P.oracle.local) (SID = KD0315P2) (UR = A)))
OK (50 msec)
============================================================================================
SQL*=PLUS Connectivity
From Primary Node 1:
==================
[oracle@pmyws01 ~]$ sqlplus sys/oracle@KD0315S as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 21 17:06:00 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options
SQL> show parameter uniq
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string KD0315S
SQL> show parameter db_n
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string KD0315P
SQL> set lines 300
SQL> select INST_ID,name,open_mode,db_unique_name,database_role from gv$database;
INST_ID NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE
---------- --------- -------------------- ------------------------------ ----------------
2 KD0315P MOUNTED KD0315S PHYSICAL STANDBY
From Node 2:
===============
[oracle@pmyws02 ~]$ sqlplus sys/oracle@KD0315S as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 21 17:08:31 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options
SQL> show parameter uniq
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string KD0315S
SQL> exit
From Standby:
=============
[soracle@pmyws02 ~]$ sqlplus sys/oracle@KD0315P as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 21 17:10:22 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options
SQL> conn sys/oracle@KD0315P1 as sysdba
Connected.
SQL> conn sys/oracle@KD0315P2 as sysdba
Connected.
SQL> exit