上一篇
在Oracle的DG环境中，由于配置了登陆触发器导致日志不能同步，报错ORA-16191的实验验证总结
在上篇中，我们实验了一下，在DG环境中，若存在登陆触发器，则会导致Redo日志不能传递到备库从而报ora-16191的错误，解决办法也很简单，禁用相关触发器或者配置隐含参数
1ALTER SYSTEM SET "_system_trig_enabled"=FALSE sid='*';
然后重启库，或重新激活同步都可以。
但，在DG场景下，若领导要求必须有登陆日志的话，怎么办呢？
我们继续往下看。。。
若一定需要使用系统触发器呢？
若因为安全审计等原因，领导要求一定需要有该程序，那应该怎么办呢？
其实报错的主要原因是因为触发器中有dml操作，但物理备库是只读的，所以会报错，我们需要修改一下我们的程序。增加判断，若当前是主库，则直接插入表；若当前是备库，则需要通过dbalink插入到主库中或舍弃日志记录，具体根据领导要求来就行。
修改后的简单示例，如下，在主库执行即可：
1drop table logon_audit_log;
2CREATE TABLE logon_audit_log (
3 id NUMBER PRIMARY KEY,
4 username VARCHAR2(30),
5 logon_time date,
6 terminal VARCHAR2(100),
7 osuser VARCHAR2(30),
8 session_id VARCHAR2(30),
9 host VARCHAR2(100),
10 ip_address VARCHAR2(45),
11 module VARCHAR2(48),
12 actions VARCHAR2(32),
13 db_role VARCHAR2(32)
14);
15
16insert into logon_audit_log (id) values(1);
17commit;
18
19grant all on sys.logon_audit_log to public;
20
21
22CREATE PUBLIC DATABASE LINK db_pri
23CONNECT TO system IDENTIFIED BY lhr
24USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.68.68)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = LHR11G)))';
25
26
27
28CREATE OR REPLACE TRIGGER logon_trigger
29AFTER LOGON ON DATABASE
30DECLARE
31 PRAGMA AUTONOMOUS_TRANSACTION;
32BEGIN
33
34
35
36 PRO_tri_logon();
37
38
39
40 EXCEPTION
41 WHEN OTHERS THEN
42 DBMS_SYSTEM.KSDWRT(2,SQLERRM);
43
44END;
45
46
47
48
49create or replace procedure PRO_tri_logon AUTHID CURRENT_USER AS
50
51 v_TERMINAL varchar2(100);
52 v_OS_USER varchar2(100);
53 v_SESSIONID varchar2(100);
54 v_HOST varchar2(100);
55 v_IP_ADDRESS varchar2(100);
56 v_MODULE varchar2(100);
57 v_ACTION varchar2(100);
58
59
60 v_db_role VARCHAR2(30);
61 v_seq number;
62
63 begin
64
65
66 SELECT DATABASE_ROLE INTO v_db_role FROM V$DATABASE;
67 select max(id)+1 into v_seq from sys.logon_audit_log;
68
69
70 IF v_db_role = 'PRIMARY' and USER not in ('SYS','PUBLIC','SYSTEM') THEN
71
72 INSERT INTO sys.logon_audit_log (id,username, logon_time, terminal, osuser, session_id, host, ip_address, module, actions,db_role)
73 VALUES (
74 v_seq,
75 USER,
76 sysdate,
77 SYS_CONTEXT('USERENV', 'TERMINAL'),
78 SYS_CONTEXT('USERENV', 'OS_USER'),
79 SYS_CONTEXT('USERENV', 'SESSIONID'),
80 SYS_CONTEXT('USERENV', 'HOST'),
81 SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
82 SYS_CONTEXT('USERENV', 'MODULE'),
83 SYS_CONTEXT('USERENV', 'ACTION'),
84 v_db_role
85 );
86 commit;
87
88 elsif USER not in ('SYS','PUBLIC','SYSTEM') THEN
89
90 select SYS_CONTEXT('USERENV', 'TERMINAL'),
91 SYS_CONTEXT('USERENV', 'OS_USER'),
92 SYS_CONTEXT('USERENV', 'SESSIONID'),
93 SYS_CONTEXT('USERENV', 'HOST'),
94 SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
95 SYS_CONTEXT('USERENV', 'MODULE'),
96 SYS_CONTEXT('USERENV', 'ACTION')
97 into v_TERMINAL,v_OS_USER,v_SESSIONID,v_HOST,v_IP_ADDRESS,v_MODULE,v_ACTION
98 from dual;
99
100
101 EXECUTE IMMEDIATE 'INSERT INTO sys.logon_audit_log@db_pri (id,username, logon_time, terminal, osuser, session_id, host, ip_address, module, actions,db_role)
102 VALUES (
103 :v_seq,
104 USER, sysdate,
105 :v_TERMINAL,:v_OS_USER,:v_SESSIONID,:v_HOST,:v_IP_ADDRESS,:v_MODULE,:v_ACTION,
106 :v_db_role
107 )' USING v_seq,v_TERMINAL,v_OS_USER,v_SESSIONID,v_HOST,v_IP_ADDRESS,v_MODULE,v_ACTION, v_db_role ;
108
109 commit;
110
111 END IF;
112
113 commit;
114
115EXCEPTION
116 WHEN OTHERS THEN
117 DBMS_SYSTEM.KSDWRT(2,SQLERRM);
118
119end ;
主库做，启用触发器：
1ALTER SYSTEM SET "_system_trig_enabled"=true sid='*';
2
3
4alter system set log_archive_dest_state_2='defer' sid='*';
5alter system archive log current;
6alter system set log_archive_dest_state_2='ENABLE' sid='*';
7alter system archive log current;
8
9
10
11SYS@LHR11G> SET PAGESIZE 9999
12SET LINE 9999
13COL NAME FORMAT A40
14COL KSPPDESC FORMAT A50
15COL KSPPSTVL FORMAT A20
16SELECT A.INDX,
17 A.KSPPINM NAME,
18 A.KSPPDESC,
19 B.KSPPSTVL
20
21FROM X$KSPPI A,
22 X$KSPPCV B
23WHERE A.INDX = B.INDX
24AND A.KSPPINM LIKE '/_%' ESCAPE '/'
25AND LOWER(A.KSPPINM) LIKE LOWER('_system_trig_enabled');
26
27
28 INDX NAME KSPPDESC KSPPSTVL
29---------- ---------------------------------------- -------------------------------------------------- --------------------
30 1876 _system_trig_enabled are system triggers enabled TRUE
备库做：
1ALTER SYSTEM SET "_system_trig_enabled"=true sid='*';
2
3
4alter database recover managed standby database cancel;
5alter database recover managed standby database using current logfile disconnect from session;
然后使用lhr用户进行测试，日志可以正常记录：
做switchover验证
1[oracle@lhr11gdg ~]$ dgmgrl sys/lhr@lhr11g
2DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
3
4Copyright (c) 2000, 2009, Oracle. All rights reserved.
5
6Welcome to DGMGRL, type "help" for information.
7Connected.
8DGMGRL> show configuration
9
10Configuration - LHR11G
11
12 Protection Mode: MaxPerformance
13 Databases:
14 LHR11G - Primary database
15 LHR11GDG - Physical standby database
16
17Fast-Start Failover: DISABLED
18
19Configuration Status:
20SUCCESS
21DGMGRL> switchover to 'LHR11GDG';
22Performing switchover NOW, please wait...
23Operation requires a connection to instance "LHR11GDG" on database "LHR11GDG"
24Connecting to instance "LHR11GDG"...
25Connected.
26New primary database "LHR11GDG" is opening...
27Operation requires startup of instance "LHR11G" on database "LHR11G"
28Starting instance "LHR11G"...
29ORACLE instance started.
30Database mounted.
31Database opened.
32Switchover succeeded, new primary is "LHR11GDG"
33DGMGRL> show configuration
34
35Configuration - LHR11G
36
37 Protection Mode: MaxPerformance
38 Databases:
39 LHR11GDG - Primary database
40 LHR11G - Physical standby database
41
42Fast-Start Failover: DISABLED
43
44Configuration Status:
45SUCCESS
46
47DGMGRL>
48
switchover正常，但是在做了switchover后，备库到主库的dblink就得修改了，否则备库的登陆日志不能被记录的：
1DROP PUBLIC DATABASE LINK db_pri;
2CREATE PUBLIC DATABASE LINK db_pri
3CONNECT TO system IDENTIFIED BY lhr
4USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.68.69)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = LHR11GDG)))';
继续切换一次：
1DGMGRL> switchover to 'LHR11G';
2Performing switchover NOW, please wait...
3Operation requires a connection to instance "LHR11G" on database "LHR11G"
4Connecting to instance "LHR11G"...
5Connected.
6New primary database "LHR11G" is opening...
7Operation requires startup of instance "LHR11GDG" on database "LHR11GDG"
8Starting instance "LHR11GDG"...
9ORACLE instance started.
10Database mounted.
11Database opened.
12Switchover succeeded, new primary is "LHR11G"
13DGMGRL>
14DGMGRL>
15DGMGRL>
16DGMGRL>
17DGMGRL> show configuration
18
19Configuration - LHR11G
20
21 Protection Mode: MaxPerformance
22 Databases:
23 LHR11G - Primary database
24 LHR11GDG - Physical standby database
25
26Fast-Start Failover: DISABLED
27
28Configuration Status:
29SUCCESS
30
31DGMGRL>
总结
1、可配置16191的events进行跟踪。
2、ora-16191常见原因有密码文件不同步、有登录触发器，不常见原因有传输的用户缺少sysoper角色。
3、若有系统登陆触发器，则需要禁掉该功能（在11.2.0.4中已复现）。
4、DG中不能同步的时候，可选择重启数据库，让某些配置生效（虽然参数已生效，但只有重启DB才能触发）。
5、使用系统触发器时，一定要慎重。在测试环境充分测试之后，再上生产环境。
6、11g环境中，不能在备库使用主库的序列；从12c开始，ADG可以使用主库创建的序列。
7、在触发器或存储过程中，可以使用DBMS_SYSTEM.KSDWRT(2,SQLERRM);将相关错误信息打印到告警日志中从而进行排错。
