Data guard log shipping problem

Problem: Log shipping stopped after data guard switchover. The error is "ORA-16191: Primary log shipping client not logged on standby".

Finding:

The database password file is doubled check to be correct. Sqlplus as sysdba to the primary from standby or to standby from primary works fine.

select thread#, sequence#, block# from v$managed_standby where process='MRP0';
on the standby ("new" one, the same hereinafter) shows the values are not moving.

select thread#, sequence#, first_time from v$archived_log where first_time>sysdate-1/2 order by 3;
on the standby shows missing redo logs from one thread compared to the output on the primary.

select * from v$archive_dest where dest_id=2;
on the primary shows good ('VALID') status on one instance and bad ('ERROR') on the other. The error column shows "ORA-16191: Primary log shipping client not logged on standby". Shutting down the bad instance causes the good instance to start to show bad status.

Solution:

Accoring to

ORA-16191 ORA-01017 and ORA-16000 shipping the logs to Standby database (Doc ID 2129339.1)
Error 1017 / ORA-16191 In Standby Alertlog (Doc ID 2225190.1)

one reason for ORA-16191 is a logon trigger for the log shipping user, i.e. sys unless changed. We didn't create such a trigger. But new versions of Oracle (18c? and above) creates a logon trigger in sys so that a shell session in which environment variable DBMS_SET_PDB is set to the name of a PDB will directly (through bequeath protocol) login to the PDB without running the command "alter session set container=<pdb>" if the logon user is sys or system.

select * from dba_triggers where owner='SYS' and triggering_event='LOGON '; <-- the trigger is DBMS_SET_PDB

If log shipping were running fine, we could disable the trigger on the primary:
alter trigger DBMS_SET_PDB disable;
and the trigger would be disabled on the standby.

But logs are not shipped now so such a change won't be propagated to the standby. And on the standby, we cannot alter a trigger to disable it because the database is open read-only (or mounted). In this dilemma, the only solution is to tell the standby to not honor a system trigger by setting this database parameter:
alter system "_system_trig_enabled"=false;
run on the standby. Although the parameter appears to be changed in sqlplus without error, the standby database must be bounced for the setting to take effect. After the bounce, log shipping starts fine and the problem is solved.

Since logs can be shipped now, once we verify the trigger is disabled as shown in xxx_triggers, we can disable the trigger on the primary, and remove the parameter from the standby:
alter system reset "_system_trig_enabled";

In the future, we won't be able to take advantage of the new environment variable ORACLE_PDB_SID, and have to alter session set container to enter a PDB if you initially are in CDB.

By the way, to quickly check if log shipping is working instead of waiting for up to 5 minutes, you can set log_archive_dest_state_2 to defer and then enable.



Parameter sec_case_sensitive_logon in this database is set to false. Maybe relevant?