Dataguard中一次奇葩的ORA-16191

dataguard不发送日志,通过v$archive_dest和alter日志发现有报错:

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
 

根据经验是一般是oracle 密码文件不对,尝试重新拷贝,还是一直不行,使用chksum和md5比对发现密码文件也是一致..上面还提到是否是参数不对,检查remote_login_passwordfile参数发现也是一致的.

询问度娘,一遍文章说是密码大小写参数的问题,参数sec_case_sensitive_logon检查发现确实主库和备库不一致.修改一致后,按照方法重新生成密码文件,并且指定ignorecase=y 忽略密码验证时区分大小写：
orapwd file=orapsid password=password#123 entries=10 force=y ignorecase=y

发现还是不行.

最后通过MOS查找到相关问题,最终得以解决:

ORA-16191 ORA-01017 and ORA-16000 shipping the logs to Standby database (文档 ID 2129339.1)

转到底部

---

In this Document   Symptoms   Changes   Cause   Solution   References   APPLIES TO: Oracle Database - Enterprise Edition - Version 11.2.0.4 and later Oracle Database Cloud Schema Service - Version N/A and later Oracle Database Exadata Cloud Machine - Version N/A and later Oracle Database Exadata Express Cloud Service - Version N/A and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. SYMPTOMS Primary database errors out when shipping the archived logs to the Physical Standby:    PING[ARC2]: Heartbeat failed to connect to standby 'test_dg'. Error is 16191.   Alert log shows: 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 ------------------------------------------------------------ PING[ARC2]: Heartbeat failed to connect to standby 'test_dg'.     After tracing is enabled, trace log shows ORA-16000: OCISessionBegin failed -1 .. Detailed OCI error val is 604 and errmsg is 'ORA-00604: error occurred at recursive SQL level 1 ORA-16000: database open for read-only access ORA-06512: at line 5 ORA-16000: database open for read-only access       CHANGES   CAUSE Standby database is using a logon trigger   SOLUTION Disable the trigger. or Set the below parameter in the Standby database and restart:    _system_trig_enabled=false   Alternatively:   ALTER SYSTEM SET "_system_trig_enabled"=FALSE;   A value of FALSE stops system triggers from firing (i.e. triggers on various DDL or database events are disabled).

注意这里看到备库端alter日志报错ORA-16000,当时很奇怪,怎么会有会话连到备库执行sql语句呢?直到看到mos这篇文章才知道

主库和备库都有一个ddl登录触发器.当主库连接备库的时候,ddl触发,执行一些sql,但是由于备库为只读模式,所以报错ORA-16000,根据官网提示使用上面的语句将隐藏参数disable此触发器即可以了.(注意,这里需要和客户确认能否禁用)