12c之后dataguarad进程监控
trace dataguard进程需要对sqlnet.ora log_archive_dest_state_3 、log_archive_trace做以下配置
alter system set log_archive_dest_state_3=defer; alter system switch logfile; alter system set log_archive_trace=4095;
arch进程需要kill掉,后台进程tmon发现没有arch进程后会重启启动arch进程
pkill -9 ora_arc 2022-07-27T11:21:46.264725+08:00 TMON (PID:3268): Detected ARCH process failure TMON (PID:3268): Detected ARCH process failure TMON (PID:3268): Detected ARCH process failure TMON (PID:3268): Detected ARCH process failure TMON (PID:3268): Detected ARCH process failure TMON (PID:3268): Detected ARCH process failure TMON (PID:3268): Detected ARCH process failure TMON (PID:3268): Detected ARCH process failure TMON (PID:3268): STARTING ARCH PROCESSES Starting background process ARC0 2022-07-27T11:21:46.272621+08:00 ARC0 started with pid=36, OS id=4265 Starting background process ARC1 2022-07-27T11:21:46.280291+08:00 ARC1 started with pid=39, OS id=4267 Starting background process ARC2 2022-07-27T11:21:46.287048+08:00 ARC2 started with pid=40, OS id=4269 Starting background process ARC3 2022-07-27T11:21:46.293630+08:00 ARC3 started with pid=41, OS id=4271 Starting background process ARC4 2022-07-27T11:21:46.299956+08:00 ARC4 started with pid=42, OS id=4273 Starting background process ARC5 2022-07-27T11:21:46.311148+08:00 ARC5 started with pid=43, OS id=4275 Starting background process ARC6 2022-07-27T11:21:46.317994+08:00 ARC6 started with pid=44, OS id=4277 Starting background process ARC7 2022-07-27T11:21:46.325009+08:00 ARC7 started with pid=45, OS id=4279 TMON (PID:3268): ARC0: Archival started TMON (PID:3268): ARC1: Archival started TMON (PID:3268): ARC2: Archival started TMON (PID:3268): ARC3: Archival started TMON (PID:3268): ARC4: Archival started TMON (PID:3268): ARC5: Archival started TMON (PID:3268): ARC6: Archival started 2022-07-27T11:21:46.326229+08:00 ARC0 (PID:4265): Becoming a 'no FAL' ARCH ARC0 (PID:4265): Becoming the 'no SRL' ARCH 2022-07-27T11:21:46.336463+08:00 TMON (PID:3268): ARC7: Archival started TMON (PID:3268): STARTING ARCH PROCESSES COMPLETE
归档3设置为enable能继续传送归档日志
alter system set log_archive_dest_state_3=enable; alter system switch logfile;
查看v$manage_standby视图,查看具体进程号
THREAD# PROCESS PID STATUS CLIENT_P SEQUENCE# CLIENT_P CLIENT_PID ---------- --------- ------------------------ ------------ -------- ---------- -------- ---------------------------------------- 0 ARCH 3177 CONNECTED ARCH 0 ARCH 3177 0 DGRD 3179 ALLOCATED N/A 0 N/A N/A 0 DGRD 3181 ALLOCATED N/A 0 N/A N/A 0 ARCH 3183 CONNECTED ARCH 0 ARCH 3183 1 ARCH 3185 CLOSING ARCH 29 ARCH 3185 1 ARCH 3187 CLOSING ARCH 33 ARCH 3187 0 ARCH 3189 CONNECTED ARCH 0 ARCH 3189 1 ARCH 3191 CLOSING ARCH 35 ARCH 3191 0 ARCH 3193 CONNECTED ARCH 0 ARCH 3193 0 ARCH 3195 CONNECTED ARCH 0 ARCH 3195 1 RFS 4310 IDLE Archival 0 Archival 3284 THREAD# PROCESS PID STATUS CLIENT_P SEQUENCE# CLIENT_P CLIENT_PID ---------- --------- ------------------------ ------------ -------- ---------- -------- ---------------------------------------- 1 RFS 4312 IDLE LGWR 36 LGWR 4744 0 RFS 4322 IDLE UNKNOWN 0 UNKNOWN 4267 1 MRP0 3301 APPLYING_LOG N/A 36 N/A N/A
开启追踪进程
--primary oradebug setospid 3284 oradebug dump sqlnet_server_trace 16 oradebug tracefile_name --standby oradebug setospid 4310 oradebug dump sqlnet_server_trace 16 oradebug tracefile_name
临时关闭追踪
--primary and standby close trace oradebug dump sqlnet_server_trace 0
查看日志分析错误
sed -n '/2022-07-27 16:25/,/2022-07-27 16:29/p' /u01/app/db/diag/rdbms/qdtais1adg/qdtais1/trace/qdtais1_tt00_3284.trc > /tmp/3284.trc sed -n '/2022-07-27 16:25/,/2022-07-27 16:29/p' /u01/app/db/diag/rdbms/qdtais2adg/qdtais1/trace/qdtais1_rfs_4310.trc > /tmp/4310.trc
问题日志抓取以后,关闭追踪设置
alter system set log_archive_dest_state_3=defer; alter system switch logfile; alter system set log_archive_trace=0; pkill -9 ora_arc alter system set log_archive_dest_state_3=enable; sqlnet.ora 删除: TRACE_DIRECTORY_SERVER = /TMP
参考mos Step by Step Method to Enable SQLNET(Server Tracing) Dynamically on Data Guard (Doc ID 2010476.1)