Oracle ADG同步延时ORA-1017和 ORA-16191

背景描述：
某客户CV备份软件无法识别设备，计划在窗口内重启Oracle数据库服务器节点1服务器。重启主库后，ADG备库同步延时。
问题原因：
备库数据库中触发器审计触发导致
解决方法：
禁用备库触发器
基础环境：

分析过程：

1、数据库日志
主库节点1日志：

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

FAL[server, ARC3]: Error 16191 creating remote archivelog file 'xfdbdg'
FAL[server, ARC3]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance xfdb1 - Archival Error. Archiver continuing.
Mon Jun 17 21:23:06 2024
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[ARC1]: Heartbeat failed to connect to standby 'xfdbdg'. Error is 16191.
Mon Jun 17 21:24:07 2024
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

复制

【说明】主库节点1日志中有Heartbeat failed to connect to standby ‘xfdbdg’. Error is 16191和Error 1017等报错。
主库节点2：

Mon Jun 17 21:14:12 2024
Archived Log entry 20178 added for thread 2 sequence 4503 ID 0x885095b7 dest 1:
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
Error 16191 for archive log file 16 to 'xfdbdg'
Errors in file /u01/app/oracle/diag/rdbms/xfdb/xfdb2/trace/xfdb2_nsa2_143682.trc:
ORA-16191: Primary log shipping client not logged on standby
Mon Jun 17 21:14:56 2024
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[ARC1]: Heartbeat failed to connect to standby 'xfdbdg'. Error is 16191.

复制

【说明】主库节点12日志中报错是 Heartbeat failed to connect to standby ‘xfdbdg’. Error is 16191和Error 1017。
备库日志：

alter database recover managed standby database cancel
Mon Jun 17 21:24:13 2024
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/xfdbdg/xfdbdg/trace/xfdbdg_pr00_370139.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 13960488814850
Mon Jun 17 21:24:13 2024
MRP0: Background Media Recovery process shutdown (xfdbdg)
Managed Standby Recovery Canceled (xfdbdg)

复制

【说明】MRP0: Background Media Recovery cancelled with status 16037备库MRP进程终止
2、检查主备库密码文件
主备库执行md5校验

[oracle@pri dbs]$ md5sum orapwxfdb
90f199187064b1437c092a4a54a2fffb  orapwxfdb
[oracle@adg dbs]$ md5sum orapwxfdbdg
90f199187064b1437c092a4a54a2fffb  orapwxfdbdg

复制

【说明】主备库密码文件md5值一致。
3、检查连接

sqlplus sys/password@xfdb as  sysdba
sqlplus sys/password@xfdbdg as  sysdba

复制

【说明】主备库验证连接正常。
4、检查备库trace文件

Trace file /u01/app/oracle/diag/rdbms/xfdbdg/xfdbdg/trace/xfdbdg_pr00_370139.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name:    Linux
Node name:      zpipadg
Release:        2.6.32-642.el6.x86_64
Version:        #1 SMP Wed Apr 13 00:51:26 EDT 2016
Machine:        x86_64
Instance name: xfdbdg
Redo thread mounted by this instance
Error in executing triggers on connect internal

复制

【说明】trc中有trigger触发器的error
5、检查触发器

create or replace TRIGGER SYS.login_log
after logon on database
DECLARE
 ipaddr   VARCHAR2 (15);
begin
ipaddr := SYS_CONTEXT ('USERENV', 'IP_ADDRESS');
IF ipaddr is not null then
insert into login_history
select username, machine, sysdate, sys_context('userenv', 'ip_address')
from v$session
where audsid = userenv('sessionid');
commit;
end if;
end;
复制

【说明】数据库中有通过触发器记录ip的审计功能。因为备库是只读状态，无法执行insert语句。
处理建议
1、在备库中禁用触发器

SQL>ALTER SYSTEM SET "_system_trig_enabled"=FALSE;
SQL> SELECT I.KSPPINM NAME,
     I.KSPPDESC DESCRIPTION,
      CV.KSPPSTVL VALUE,
   CV.KSPPSTDF ISDEFAULT
 FROM SYS.X$KSPPI I, SYS.X$KSPPCV CV
WHERE I.INST_ID = USERENV('Instance')
  AND CV.INST_ID = USERENV('Instance')
  AND I.INDX = CV.INDX
  AND I.KSPPINM LIKE '_system_trig_enabled'
ORDER BY REPLACE(I.KSPPINM, '_', '');  4    5    6    7    8    9   10  

NAME                 DESCRIPTION                    VALUE           ISDEFAULT
-------------------- ------------------------------ --------------- ---------
_system_trig_enabled are system triggers enabled    FALSE            TRUE
复制

2、调整触发器
create or replace TRIGGER SYS.login_log
after logon on database
DECLARE
ipaddr VARCHAR2 (15);
begin
select database_role
into db_role
from v$database; If db_role <> 'PHYSICAL STANDBY' then ipaddr := SYS_CONTEXT ('USERENV', 'IP_ADDRESS'); IF ipaddr is not null then insert into login_history select username, machine, sysdate, sys_context('userenv', 'ip_address') from v$session
where audsid = userenv(‘sessionid’);
commit;
end if;
end if;
end;
在触发器中增加数据库角色的判断，当数据库不是物理备库时执行数据库登录审计。

【小结】在ADG环境中数据库中类似审计的触发器作为巡检检查项；对于ORA-1017、ORA-16191报错分析除了密码文件还可以检查触发器；数据库增加审计功能产品或触发器、存储过程等对象时应该充分考虑ADG特点，经过测试后再上线。
-the end-