前言
之前碰到DG环境中，redo不能同步的大部分原因是密码文件的问题，解决办法也很简单，就是生成一份密码文件，然后拷贝到其它节点即可。
最近碰到一种情况是由于配置了登陆触发器导致redo不能同步。
本文就这种情况，我们来做实验验证。
快速部署DG环境
使用docker快速部署：
1nohup docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/dg_phy_11.2.0.4:1.0 &
2nohup docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/dg_pri_11.2.0.4:1.0 &
3
4docker tag registry.cn-hangzhou.aliyuncs.com/lhrbest/dg_phy_11.2.0.4:1.0 lhrbest/dg_phy_11.2.0.4:1.0
5docker tag registry.cn-hangzhou.aliyuncs.com/lhrbest/dg_pri_11.2.0.4:1.0 lhrbest/dg_pri_11.2.0.4:1.0
6
7
8-- 创建DG的网络
9docker network create --subnet=192.168.68.0/16 mhalhr
10docker network inspect mhalhr
11
12-- 分别初始化主库和备库
13docker run -itd --name LHR11G -h LHR11G \
14 -p 1528:1521 -p 1128:1158 -p 228:22 -p 13398:3389 \
15 --network mhalhr --ip 192.168.68.68 \
16 --privileged=true \
17 lhrbest/dg_pri_11.2.0.4:1.0 init
18
19docker run -itd --name LHR11GDG -h LHR11GDG \
20 -p 1529:1521 -p 1129:1158 -p 229:22 -p 13399:3389 \
21 --network mhalhr --ip 192.168.68.69 \
22 --privileged=true \
23 lhrbest/dg_phy_11.2.0.4:1.0 init
24
25 -- 添加网卡
26docker network connect bridge LHR11G
27docker network connect bridge LHR11GDG
28
29-- 进入容器
30docker exec -it LHR11G bash
31docker exec -it LHR11GDG bash
32
33-- 分别启动主库、备库和监听
34su - oracle
35lsnrctl start
36sas
37startup
检查同步情况
主库：
1SYS@LHR11G> COL NAME FOR A80
2SET LINESIZE 9999 PAGESIZE 9999
3SYS@LHR11G> SELECT A.THREAD#, A.NAME, A.SEQUENCE#, A.APPLIED, A.FIRST_TIME
4 2 FROM V$ARCHIVED_LOG A,
5 (SELECT NB.THREAD#,NB.RESETLOGS_ID,(MAX(NB.SEQUENCE#) - 3) MAX_SEQUENCE#
6 4 FROM V$ARCHIVED_LOG NB
7 5 WHERE NB.APPLIED = 'YES'
8 6 and RESETLOGS_ID=(select max(nbb.RESETLOGS_ID) from V$ARCHIVED_LOG nbb)
9 7 GROUP BY NB.THREAD#,NB.RESETLOGS_ID) B
10 8 WHERE B.THREAD# = A.THREAD#
11 9 AND B.RESETLOGS_ID = A.RESETLOGS_ID
12 10 AND A.SEQUENCE# >= MAX_SEQUENCE#
13 11 and a.RESETLOGS_ID=(select max(nb.RESETLOGS_ID) from V$ARCHIVED_LOG nb)
14 12 AND A.STANDBY_DEST = (SELECT CASE WHEN NB.DATABASE_ROLE LIKE '%STANDBY%' THEN 'NO' ELSE 'YES' END FROM V$DATABASE NB)
15 13 ORDER BY A.THREAD#, A.SEQUENCE#;
16
17
18 THREAD# NAME SEQUENCE# APPLIED FIRST_TIME
19---------- -------------------------------------------------------------------------------- ---------- ------------------ -------------------
20 1 lhr11gdg 125 YES 2023-09-20 06:27:33
21 1 lhr11gdg 126 YES 2023-09-20 12:04:33
22 1 lhr11gdg 127 YES 2023-09-20 19:50:15
23 1 lhr11gdg 128 YES 2024-08-19 14:20:18
24 1 lhr11gdg 129 NO 2024-08-19 14:20:24
备库：
1SYS@LHR11GDG> -- 查询应用日志序列号
2COL NAME FOR A80
3SET LINESIZE 9999 PAGESIZE 9999
4SELECT A.THREAD#, A.NAME, A.SEQUENCE#, A.APPLIED, A.FIRST_TIME
5 FROM V$ARCHIVED_LOG A,
6 3 (SELECT NB.THREAD#,NB.RESETLOGS_ID,(MAX(NB.SEQUENCE#) - 3) MAX_SEQUENCE#
7 4 FROM V$ARCHIVED_LOG NB
8 WHERE NB.APPLIED = 'YES'
9 6 and RESETLOGS_ID=(select max(nbb.RESETLOGS_ID) from V$ARCHIVED_LOG nbb)
10 7 GROUP BY NB.THREAD#,NB.RESETLOGS_ID) B
11 8 WHERE B.THREAD# = A.THREAD#
12 9 AND B.RESETLOGS_ID = A.RESETLOGS_ID
13 10 AND A.SEQUENCE# >= MAX_SEQUENCE#
14 and a.RESETLOGS_ID=(select max(nb.RESETLOGS_ID) from V$ARCHIVED_LOG nb)
15 12 AND A.STANDBY_DEST = (SELECT CASE WHEN NB.DATABASE_ROLE LIKE '%STANDBY%' THEN 'NO' ELSE 'YES' END FROM V$DATABASE NB)
16 13 ORDER BY A.THREAD#, A.SEQUENCE#;
17
18 THREAD# NAME SEQUENCE# APPLIED FIRST_TIME
19---------- -------------------------------------------------------------------------------- ---------- ------------------ -------------------
20 1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2023_09_20/o1_mf_1_125_l 125 YES 2023-09-20 06:27:33
21 jnvtkc2_.arc
22
23 1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2023_09_20/o1_mf_1_126_l 126 YES 2023-09-20 12:04:33
24 joq3qgy_.arc
25
26 1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2024_08_19/o1_mf_1_127_m 127 YES 2023-09-20 19:50:15
27 d5s2ymk_.arc
28
29 1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2024_08_19/o1_mf_1_128_m 128 YES 2024-08-19 14:20:18
30 d5s2yfb_.arc
31
32 1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2024_08_19/o1_mf_1_129_m 129 IN-MEMORY 2024-08-19 14:20:24
33 d5s31df_.arc
可看到，是同步的。
主库建表测试：
1create table t1(id int);
备库查询：
1select * from t1;
确认是实时同步的。
主库创建登陆触发器
1CREATE SEQUENCE logon_audit_log_seq
2START WITH 1
3INCREMENT BY 1
4NOCACHE;
5
6drop table logon_audit_log;
7CREATE TABLE logon_audit_log (
8 id NUMBER PRIMARY KEY,
9 username VARCHAR2(30) NOT NULL,
10 logon_time date NOT NULL,
11 terminal VARCHAR2(100),
12 osuser VARCHAR2(30),
13 session_id VARCHAR2(30),
14 host VARCHAR2(100),
15 ip_address VARCHAR2(45),
16 module VARCHAR2(48),
17 actions VARCHAR2(32)
18);
19
20
21CREATE OR REPLACE TRIGGER logon_trigger_name
22AFTER LOGON ON DATABASE
23BEGIN
24 INSERT INTO logon_audit_log (
25 id,username, logon_time, terminal, osuser, session_id, host, ip_address, module, actions
26 )
27 VALUES (
28 logon_audit_log_seq.nextval,
29 USER,
30 sysdate,
31 SYS_CONTEXT('USERENV', 'TERMINAL'),
32 SYS_CONTEXT('USERENV', 'OS_USER'),
33 SYS_CONTEXT('USERENV', 'SESSIONID'),
34 SYS_CONTEXT('USERENV', 'HOST'),
35 SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
36 SYS_CONTEXT('USERENV', 'MODULE'),
37 SYS_CONTEXT('USERENV', 'ACTION')
38 );
39END;
创建完成后，在备库也可以查询到该触发器，此时，主备依然是实时同步的。
建表删表测试同步正常！！！
触发故障
主备库都做重启动作。
重启后，发现告警日志开始报错，主库：
1******************************************************************
2LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
3******************************************************************
4******************************************************************
5Error 1017 received logging on to the standby
6------------------------------------------------------------
7Check that the primary and standby are using a password file
8and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
9and that the SYS password is same in the password files.
10 returning error ORA-16191
11------------------------------------------------------------
12FAL[server, ARC3]: Error 16191 creating remote archivelog file 'lhr11gdg'
13FAL[server, ARC3]: FAL archive failed, see trace file.
14ARCH: FAL archive failed. Archiver continuing
15ORACLE Instance LHR11G - Archival Error. Archiver continuing.
16Error 1017 received logging on to the standby
17------------------------------------------------------------
18Check that the primary and standby are using a password file
19and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
20and that the SYS password is same in the password files.
21 returning error ORA-16191
22------------------------------------------------------------
23Error 16191 for archive log file 2 to 'lhr11gdg'
24Errors in file /u01/app/oracle/diag/rdbms/lhr11g/LHR11G/trace/LHR11G_nsa2_3230.trc:
25ORA-16191: Primary log shipping client not logged on standby
26Mon Aug 19 17:06:22 2024
27Error 1017 received logging on to the standby
28------------------------------------------------------------
29Check that the primary and standby are using a password file
30and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
31and that the SYS password is same in the password files.
32 returning error ORA-16191
33------------------------------------------------------------
34FAL[server, ARC3]: Error 16191 creating remote archivelog file 'lhr11gdg'
35FAL[server, ARC3]: FAL archive failed, see trace file.
36ARCH: FAL archive failed. Archiver continuing
37ORACLE Instance LHR11G - Archival Error. Archiver continuing.
38
39
40
41
42
43Mon Aug 19 17:07:11 2024
44Error 1017 received logging on to the standby
45------------------------------------------------------------
46Check that the primary and standby are using a password file
47and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
48and that the SYS password is same in the password files.
49 returning error ORA-16191
50------------------------------------------------------------
51PING[ARC1]: Heartbeat failed to connect to standby 'lhr11gdg'. Error is 16191.
备库：
1Mon Aug 19 17:06:10 2024
2SMON: enabling cache recovery
3Dictionary check beginning
4Dictionary check complete
5Database Characterset is AL32UTF8
6No Resource Manager plan active
7replication_dependency_tracking turned off (no async multimaster replication found)
8Physical standby database opened for read only access.
9Errors in file /u01/app/oracle/diag/rdbms/lhr11gdg/LHR11GDG/trace/LHR11GDG_ora_3796.trc:
10ORA-00604: error occurred at recursive SQL level 1
11ORA-44311: service dg_taf_lhr not running
12ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
13ORA-06512: at "SYS.DBMS_SERVICE", line 466
14ORA-06512: at "SYS.DBMS_SERVICE", line 400
15ORA-06512: at "SYS.DG_TAF_PROC_LHR", line 8
16ORA-06512: at line 2
17Completed: ALTER DATABASE OPEN
主库查询：
1SYS@LHR11G> select dest_id,error,status,log_sequence,applied_scn from v$archive_dest a where a.status<>'INACTIVE';
2
3 DEST_ID ERROR STATUS LOG_SEQUENCE APPLIED_SCN
4---------- ----------------------------------------------------------------- ------------------ ------------ -----------
5 1 VALID 151 0
6 2 ORA-16191: Primary log shipping client not logged on standby ERROR 152 4974779
7
8SYS@LHR11G>
备库查询：
1SYS@LHR11GDG> SET LINESIZE 9999 PAGESIZE 9999
2SELECT A.THREAD#, A.NAME, A.SEQUENCE#, A.APPLIED, A.FIRST_TIME
3 2 FROM V$ARCHIVED_LOG A,
4 3 (SELECT NB.THREAD#,NB.RESETLOGS_ID,(MAX(NB.SEQUENCE#) - 3) MAX_SEQUENCE#
5 FROM V$ARCHIVED_LOG NB
6 WHERE NB.APPLIED = 'YES'
7 6 and RESETLOGS_ID=(select max(nbb.RESETLOGS_ID) from V$ARCHIVED_LOG nbb)
8 7 GROUP BY NB.THREAD#,NB.RESETLOGS_ID) B
9 8 WHERE B.THREAD# = A.THREAD#
10 AND B.RESETLOGS_ID = A.RESETLOGS_ID
11 AND A.SEQUENCE# >= MAX_SEQUENCE#
12 11 and a.RESETLOGS_ID=(select max(nb.RESETLOGS_ID) from V$ARCHIVED_LOG nb)
13 12 AND A.STANDBY_DEST = (SELECT CASE WHEN NB.DATABASE_ROLE LIKE '%STANDBY%' THEN 'NO' ELSE 'YES' END FROM V$DATABASE NB)
14 13 ORDER BY A.THREAD#, A.SEQUENCE#;
15
16 THREAD# NAME SEQUENCE# APPLIED FIRST_TIME
17---------- -------------------------------------------------------------------------------- ---------- ------------------ -------------------
18 1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2024_08_19/o1_mf_1_147_m 147 YES 2024-08-19 16:34:26
19 d61odw4_.arc
20
21 1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2024_08_19/o1_mf_1_148_m 148 YES 2024-08-19 16:34:32
22 d61opg1_.arc
23
24 1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2024_08_19/o1_mf_1_149_m 149 YES 2024-08-19 16:47:41
25 d61os8s_.arc
26
27 1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2024_08_19/o1_mf_1_150_m 150 YES 2024-08-19 16:47:53
28 d62r272_.arc
29
30
31SYS@LHR11GDG>
日志已经不同步了。
此时，做实时验证，发现不能同步了（主库建表，备库查询即可）。
排查故障
生成trace来检查。
主库检查
在主库都做如下操作：
1alter system set events '16191 trace name errorstack level 10';
2
3alter system set log_archive_dest_state_2='defer' sid='*';
4alter system archive log current;
5alter system set log_archive_dest_state_2='ENABLE' sid='*';
6alter system archive log current;
主库告警日志：
1Mon Aug 19 17:15:57 2024
2ALTER SYSTEM SET log_archive_dest_state_2='defer' SCOPE=BOTH SID='*';
3ALTER SYSTEM ARCHIVE LOG
4Mon Aug 19 17:15:57 2024
5Thread 1 advanced to log sequence 153 (LGWR switch)
6 Current log# 3 seq# 153 mem# 0: /u01/app/oracle/oradata/LHR11G/redo03.log
7Archived Log entry 330 added for thread 1 sequence 152 ID 0x782dc04f dest 1:
8ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH SID='*';
9ALTER SYSTEM ARCHIVE LOG
10Thread 1 advanced to log sequence 154 (LGWR switch)
11 Current log# 1 seq# 154 mem# 0: /u01/app/oracle/oradata/LHR11G/redo01.log
12Archived Log entry 331 added for thread 1 sequence 153 ID 0x782dc04f dest 1:
13Mon Aug 19 17:15:57 2024
14******************************************************************
15LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
16LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
17******************************************************************
18Mon Aug 19 17:15:58 2024
19Error 1017 received logging on to the standby
20------------------------------------------------------------
21Check that the primary and standby are using a password file
22and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
23and that the SYS password is same in the password files.
24 returning error ORA-16191
25------------------------------------------------------------
26PING[ARC1]: Heartbeat failed to connect to standby 'lhr11gdg'. Error is 16191.
27Error 1017 received logging on to the standby
28------------------------------------------------------------
29Check that the primary and standby are using a password file
30and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
31and that the SYS password is same in the password files.
32 returning error ORA-16191
33------------------------------------------------------------
34Errors in file /u01/app/oracle/diag/rdbms/lhr11g/LHR11G/trace/LHR11G_nsa2_3230.trc:
35ORA-16191: Primary log shipping client not logged on standby
36Error 16191 for archive log file 1 to 'lhr11gdg'
37Errors in file /u01/app/oracle/diag/rdbms/lhr11g/LHR11G/trace/LHR11G_nsa2_3230.trc:
38ORA-16191: Primary log shipping client not logged on standby
39Mon Aug 19 17:15:59 2024
40Dumping diagnostic data in directory=[cdmp_20240819171559], requested by (instance=1, osid=3230 (NSA2)), summary=[abnormal process termination].
41Mon Aug 19 17:15:59 2024
42Error 1017 received logging on to the standby
43------------------------------------------------------------
44------------------------------------------------------------
45Check that the primary and standby are using a password file
46and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
47and that the SYS password is same in the password files.
48 returning error ORA-16191
49------------------------------------------------------------
50Errors in file /u01/app/oracle/diag/rdbms/lhr11g/LHR11G/trace/LHR11G_arc3_3228.trc:
51ORA-16191: Primary log shipping client not logged on standby
52FAL[server, ARC3]: Error 16191 creating remote archivelog file 'lhr11gdg'
53FAL[server, ARC3]: FAL archive failed, see trace file.
54ARCH: FAL archive failed. Archiver continuing
55ORACLE Instance LHR11G - Archival Error. Archiver continuing.
56Dumping diagnostic data in directory=[cdmp_20240819171600], requested by (instance=1, osid=3228 (ARC3)), summary=[abnormal process termination].
查看trace文件内容：
1/u01/app/oracle/diag/rdbms/lhr11g/LHR11G/trace/LHR11G_nsa2_3230.trc
2
3
4*** 2024-08-19 17:06:13.077 1279 krsw.c
5NSA2: initializing for LGWR communication
6NSA2: connecting to KSR channel
7Success
8NSA2: subscribing to KSR channel
9Success
10*** 2024-08-19 17:06:13.078 1324 krsw.c
11NSA2: initialized successfully ASYNC=1
12
13*** 2024-08-19 17:06:16.074
14Destination is specified with ASYNC=61440
15OCISessionBegin failed -1
16.. Detailed OCI error val is 604 and errmsg is 'ORA-00604: error occurred at recursive SQL level 1
17ORA-16000: database open for read-only access
18ORA-06512: at line 2
19'
20OCISessionBegin failed. Error -1
21.. Detailed OCI error val is 1017 and errmsg is 'ORA-01017: invalid username/password; logon denied
22'
23
24*** 2024-08-19 17:06:17.220
25OCISessionBegin failed. Error -1
26.. Detailed OCI error val is 1017 and errmsg is 'ORA-01017: invalid username/password; logon denied
27'
28OCISessionBegin failed. Error -1
29.. Detailed OCI error val is 1017 and errmsg is 'ORA-01017: invalid username/password; logon denied
30'
31*** 2024-08-19 17:06:17.234 4329 krsh.c
32Error 1017 received logging on to the standby
33------------------------------------------------------------
34Check that the primary and standby are using a password file
35and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
36and that the SYS password is same in the password files.
37 returning error ORA-16191
38------------------------------------------------------------
39*** 2024-08-19 17:06:17.234 939 krsu.c
40Error 16191 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'lhr11gdg'
41Error 16191 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'lhr11gdg'
42*** 2024-08-19 17:06:17.234 4329 krsh.c
43Error 16191 for archive log file 2 to 'lhr11gdg'
44*** 2024-08-19 17:06:17.234 2917 krsi.c
45krsi_dst_fail: dest:2 err:16191 force:0 blast:1
46ORA-16191: Primary log shipping client not logged on standby
主要错误是 .. 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 2
备库检查
在备库都做如下操作：
1alter system set events '16191 trace name errorstack level 10';
2
3
4alter database recover managed standby database cancel;
5alter database recover managed standby database using current logfile disconnect from session;
备库告警日志：
1Mon Aug 19 17:20:01 2024
2alter database recover managed standby database cancel
3Mon Aug 19 17:20:02 2024
4MRP0: Background Media Recovery cancelled with status 16037
5Errors in file /u01/app/oracle/diag/rdbms/lhr11gdg/LHR11GDG/trace/LHR11GDG_pr00_3836.trc:
6ORA-16037: user requested cancel of managed recovery operation
7Managed Standby Recovery not using Real Time Apply
8Recovery interrupted!
9Mon Aug 19 17:20:02 2024
10MRP0: Background Media Recovery process shutdown (LHR11GDG)
11Managed Standby Recovery Canceled (LHR11GDG)
12Completed: alter database recover managed standby database cancel
13alter database recover managed standby database using current logfile disconnect from session
14Attempt to start background Managed Standby Recovery process (LHR11GDG)
15Mon Aug 19 17:20:02 2024
16MRP0 started with pid=32, OS id=4154
17MRP0: Background Managed Standby Recovery process started (LHR11GDG)
18 started logmerger process
19Mon Aug 19 17:20:07 2024
20Managed Standby Recovery starting Real Time Apply
21Parallel Media Recovery started with 32 slaves
22Waiting for all non-current ORLs to be archived...
23All non-current ORLs have been archived.
24Media Recovery Waiting for thread 1 sequence 151
25Completed: alter database recover managed standby database using current logfile disconnect from session
无明显的错误，但我之前在生产环境的时候，有一个明显的报错：
1Errors in file /u01/app/oracle/diag/rdbms/dgorcl/dgorcl/trace/dgorcl_ora_7229.trc:
2ORA-00604: error occurred at recursive SQL level 1
3ORA-16000: database open for read-only access
4ORA-06512: at line 2
5
6
7-- 查看trace发现是触发器的问题
8Error in executing triggers on connect internal
9*** 2024-08-17 21:50:27.744
10dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=0, mask=0x0)
11----- Error Stack Dump -----
12ORA-00604: error occurred at recursive SQL level 1
13ORA-16000: database o
故障解决
搜索mos找到2篇相关的内容：
参考：Error 1017 / ORA-16191 In Standby Alertlog (Doc ID 2225190.1)
ORA-16191 ORA-01017 and ORA-16000 shipping the logs to Standby database (Doc ID 2129339.1)
解决：主备库都需要运行，然后重启数据库问题解决：
1ALTER SYSTEM SET "_system_trig_enabled"=FALSE sid='*';
2
3
4SET PAGESIZE 9999
5SET LINE 9999
6COL NAME FORMAT A40
7COL KSPPDESC FORMAT A50
8COL KSPPSTVL FORMAT A20
9SELECT A.INDX,
10 A.KSPPINM NAME,
11 A.KSPPDESC,
12 B.KSPPSTVL
13FROM X$KSPPI A,
14 X$KSPPCV B
15WHERE A.INDX = B.INDX
16AND A.KSPPINM LIKE '/_%' ESCAPE '/'
17AND LOWER(A.KSPPINM) LIKE LOWER('_system_trig_enabled');
A value of FALSE stops system triggers from firing (i.e. triggers on various DDL or database events are disabled).
主库：
1SYS@LHR11G> select dest_id,error,status,log_sequence,applied_scn from v$archive_dest a where a.status<>'INACTIVE';
2
3 DEST_ID ERROR STATUS LOG_SEQUENCE APPLIED_SCN
4---------- ----------------------------------------------------------------- ------------------ ------------ -----------
5 1 VALID 157 0
6 2 VALID 158 4996820
备库：
1SYS@LHR11GDG> COL NAME FOR A80
2SET LINESIZE 9999 PAGESIZE 9999
3SELECT A.THREAD#, A.NAME, A.SEQUENCE#, A.APPLIED, A.FIRST_TIME
4 FROM V$ARCHIVED_LOG A,
5 (SELECT NB.THREAD#,NB.RESETLOGS_ID,(MAX(NB.SEQUENCE#) - 3) MAX_SEQUENCE#
6 WHERE NB.APPLIED = 'YES'
7 FROM V$ARCHIVED_LOG NB
8and RESETLOGS_ID=(select max(nbb.RESETLOGS_ID) from V$ARCHIVED_LOG nbb)
9 WHERE NB.APPLIED = 'YES'
10 6 and RESETLOGS_ID=(select max(nbb.RESETLOGS_ID) from V$ARCHIVED_LOG nbb)
11 GROUP BY NB.THREAD#,NB.RESETLOGS_ID) B
12 WHERE B.THREAD# = A.THREAD#
13 9 AND B.RESETLOGS_ID = A.RESETLOGS_ID
14 10 AND A.SEQUENCE# >= MAX_SEQUENCE#
15 11 and a.RESETLOGS_ID=(select max(nb.RESETLOGS_ID) from V$ARCHIVED_LOG nb)
16 12 AND A.STANDBY_DEST = (SELECT CASE WHEN NB.DATABASE_ROLE LIKE '%STANDBY%' THEN 'NO' ELSE 'YES' END FROM V$DATABASE NB)
17 13 ORDER BY A.THREAD#, A.SEQUENCE#;
18
19 THREAD# NAME SEQUENCE# APPLIED FIRST_TIME
20---------- -------------------------------------------------------------------------------- ---------- ------------------ -------------------
21 1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2024_08_19/o1_mf_1_154_m 154 YES 2024-08-19 17:15:57
22 d63wrn9_.arc
23
24 1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2024_08_19/o1_mf_1_155_m 155 YES 2024-08-19 17:21:25
25 d63wrnc_.arc
26
27 1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2024_08_19/o1_mf_1_156_m 156 YES 2024-08-19 17:21:34
28 d63wr1z_.arc
29
30 1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2024_08_19/o1_mf_1_157_m 157 YES 2024-08-19 17:25:42
31 d63wx0k_.arc
此时，做实时验证，发现已经OK了（主库建表，备库查询即可）。
总结
1、可配置16191的events进行跟踪。
2、ora-16191常见原因有密码文件不同步、有登录触发器，不常见原因有传输的用户缺少sysoper角色。
3、若有系统登陆触发器，则需要禁掉该功能（在11.2.0.4中已复现）。
4、DG中不能同步的时候，可选择重启数据库，让某些配置生效（虽然参数已生效，但只有重启DB才能触发）。
