ORA-16191: Primary log shipping client not logged on standby

This error usually is because of password file mismatch: someone updated in primary and forgot to pass across to standby nodes, missed a node… but what if password file in primary and standby is the same?

In this case, password files are correct so PRIMARY should not have issues sending logs,right? But no, instead it keeps receiving ORA-16191:

SQL> select inst_id, DEST_ID,DEST_NAME,STATUS,DESTINATION,error from gv$archive_dest_status where status <> 'INACTIVE' order by 1,2;

   INST_ID    DEST_ID DEST_NAME            STATUS    DESTINATION    ERROR
---------- ---------- -------------------- --------- -------------- ------------------------------------------------------------
         1          1 LOG_ARCHIVE_DEST_1   VALID
         1          2 LOG_ARCHIVE_DEST_2   ERROR     STANDBY_DB     ORA-16191: Primary log shipping client not logged on standby
         2          1 LOG_ARCHIVE_DEST_1   VALID
         2          2 LOG_ARCHIVE_DEST_2   ERROR     STANDBY_DB     ORA-16191: Primary log shipping client not logged on standby
SQL>

How do you keep digging? Well… it is time to do some tracing.
Error is reported on primary DB, so lets set trace in primary (not standby)

Primary: SQL>  alter system set events '16191 trace name errorstack level 10';
Primary: SQL > alter system set log_archive_dest_state_2 = defer  scope=memory;
Primary: SQL > alter system set log_archive_dest_state_2 = enable scope=both;
Primary: SQL > alter system archive log current;

**REMEMBER TO DISABLE ONCE ALL FIXED:
SQL> alter system set events '16191 trace name context off';

The error will be reported on the ARC traces, so lets check the last one generated:

/u01/app/oracle/diag/rdbms/primary_DB/primary_DB/trace/primary_DB_arc2_150506.trc 

*** 2019-10-26 12:28:21.325579 4929 krsh.c
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
      returning error ORA-16191
------------------------------------------------------------
*** 2019-10-26 12:28:21.326642 2968 krsu.c
Error 16191 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'STANDBY_DB'
krsi_verify_network: Error 16191 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'STANDBY_DB'
krsd_reopen_chk: Reactivation of destination LOG_ARCHIVE_DEST_2 has failed
krsd_reopen_chk: failcnt = 704

*** 2019-10-26 12:29:21.745
OCISessionBegin failed -1
.. Detailed OCI error val is 604 and errmsg is 'ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database or pluggable database open for read-only access
ORA-06512: at line 63
'

*** 2019-10-26 12:29:22.784
OCISessionBegin failed. Error -1
.. Detailed OCI error val is 1017 and errmsg is 'ORA-01017: invalid username/password; logon denied

So here it is: “ORA-16000: database or plugable database open for read-only access”
It seems DB has a logon trigger but since it is open in READ-ONLY, it is having a misleading ORA-01017.

How to fix this and start MRP? Please remember when DB is facing this error, transport can not start and standby is not in sync.

Here the fix:

==== ON STANDBY:

	SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
	Database altered.
	
	*check _system_trig_enabled value: 
	
	Parameter           Instance_Value    Default_v SESSM SYSMOD    Description
	---------------     ----------------- --------- ----- --------- --------------------------------------------------
	_system_trig_enabled TRUE              TRUE      False IMMEDIATE are system triggers enabled
	
	
	**disable system logon trigger while we start MRP/transport:
	
	SQL> alter system set "_system_trig_enabled"=FALSE;
	System altered.
	SQL> 
	
	SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
	Database altered.
	SQL> 
	

==== ON PRIMARY:
	SQL> alter system set log_archive_dest_state_2 = enable scope=both;
	System altered.


==== ON STANDBY:
	SQL> alter system set "_system_trig_enabled"=TRUE;
	System altered.
	SQL> 



NOW PRIMARY DOES NOT HAVE ERRORS:

SQL>  select inst_id, DEST_ID,DEST_NAME,STATUS,DESTINATION,error from gv$archive_dest_status where status <> 'INACTIVE' order by 1,2;

   INST_ID    DEST_ID DEST_NAME            STATUS    DESTINATION          ERROR                 
---------- ---------- -------------------- --------- -------------------- ----------------------
         1          1 LOG_ARCHIVE_DEST_1   VALID
         1          2 LOG_ARCHIVE_DEST_2   VALID     STANDBY_DB
         2          1 LOG_ARCHIVE_DEST_1   VALID
         2          2 LOG_ARCHIVE_DEST_2   VALID     STANDBY_DB

SQL>