Sqlplus在Debian上的日志分析

Debian上 sqlplus 日志分析实用指南

一、日志来源与定位

• 系统层面：sqlplus 是客户端进程，其启动、参数、崩溃等通常写入 /var/log/（如 /var/log/syslog、/var/log/auth.log）。使用 journalctl 可按服务、时间、实时跟踪日志，例如：journalctl -u -f。
• Oracle 层面：数据库告警与监听日志常位于 $ORACLE_HOME/log/，常见文件有 alert.log 与 listener.log；监听配置在 $ORACLE_HOME/network/admin/listener.ora。
• 会话层面：sqlplus 自身输出需显式记录，常用 SPOOL 将查询结果导出到文件，或用 shell 重定向保存执行日志。
• 补充：Red Hat 系常见 /var/log/messages，Debian 常用 /var/log/syslog，两者都可用于检索 sqlplus 相关系统日志。

二、快速排查路径

• 连接缓慢或卡顿：先用系统跟踪定位阻塞点，再回到数据库查会话与等待事件。
  1. 跟踪 sqlplus 登录/执行：strace -T -t -f -o sqlplus_strace.log sqlplus -S /nolog <<EOF connect $DB_USER/$DB_PASS as sysdba; exit; EOF
  2. 在输出中查找耗时最大的系统调用（如 read/poll/recvmsg），定位是 DNS、审计目录、口令校验还是网络往返问题。
  3. 进入数据库复核活跃会话与等待：
     • 活跃 SQL：
       select ses.inst_id||‘:’||ses.sid as inst_sid, username, (sysdate - sql_exec_start) day to second, ses.sql_id, substr(sql.sql_text,1,60) sql_text,
       substr(case when time_since_last_wait_micro=0 then ‘IDLE: ‘||event else event end,1,40) event,
       (case when time_since_last_wait_micro=0 then wait_time_micro else time_since_last_wait_micro end)/1e6 wait_sec
       from gv$session ses, gv$sqlstats sql
       where ses.inst_id||’:’||ses.sid <> sys_context(‘USERENV’,‘INSTANCE’)||‘:’||sys_context(‘USERENV’,‘SID’)
       and username is not null and status=‘ACTIVE’ and ses.sql_id=sql.sql_id(+);
     • 登录风暴/阻塞：
       select event, count(*), round(sum(time_waited)/1e6,2) sec
       from v$session_event where wait_class!=‘Idle’ group by event order by sec desc;
• 归档与备库延迟（ADG）：
  1. 最近已应用的归档时间：
     LAST_APPLIED_TIME=$(sqlplus -s “$DB_USER/$DB_PASS as sysdba” <<‘EOF’
     SET HEADING OFF FEEDBACK OFF
     SELECT TO_CHAR(next_time,‘YYYY-MM-DD HH24:MI:SS’)
     FROM v$archived_log
     WHERE sequence# = (SELECT MAX(sequence#) FROM v$archived_log WHERE applied=‘YES’);
     EXIT;
     EOF
     )
  2. 与当前时间比较，超过阈值则告警（脚本化邮件/钉钉/企业微信通知）。
• 日志切换频繁（潜在 I/O 瓶颈）：
  1. 查看在线日志组与大小：
     sqlplus -s $DB_CONN_STR@$SH_DB_SID <<‘EOF’
     set linesize 150 pages 100 feedback off verify off
     col dbname new_value dbname
     col time_stamp new_value time_stamp
     SELECT name dbname, substr(to_char(sysdate,‘YYYY-Mon-DD HH24:MI:SS’),1,20) time_stamp FROM v$database;
     col bytes format 9,999,999,999,999
     col member format a60
     select group#,thread#,sequence#,members,bytes/1024/1024 size_MB,archived,status from v$log order by 1,2;
     EOF
  2. 按小时统计当日日志切换次数，识别高峰时段与异常频繁切换。

三、常用分析场景与命令模板

• 场景 A：审计谁在什么时候执行了哪些语句
  1. 打开审计（示例：登录审计）并确认审计表写入：AUDIT SESSION BY ACCESS;
  2. 查询最近会话与语句：
     select username, os_username, terminal, machine, program, logon_time, action_name, returncode
     from dba_audit_trail order by timestamp desc fetch first 100 rows only;
• 场景 B：还原并分析某时段的 DML 变更（误操作排查）
  1. 确定时间窗口与归档序列：
     alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss’;
     select sequence#, first_time from v$log_history where first_time between to_date(‘2025-03-01 00:00:00’,‘yyyy-mm-dd hh24:mi:ss’)
     and to_date(‘2025-03-01 23:59:59’,‘yyyy-mm-dd hh24:mi:ss’) order by sequence#;
  2. 用 RMAN 将归档日志恢复到临时目录（示例序列 3988–4000）：
     RUN { SET ARCHIVELOG DESTINATION TO ‘/oracle/tmp/arch’; RESTORE ARCHIVELOG SEQUENCE BETWEEN 3988 AND 4000; }
  3. 用 LogMiner 加载并分析：
     exec dbms_logmnr.add_logfile(logfilename=>‘/oracle/tmp/arch/1_3988_.dbf’, options=>dbms_logmnr.new);
     exec dbms_logmnr.add_logfile(logfilename=>'/oracle/tmp/arch/1_3989_.dbf’, options=>dbms_logmnr.addfile);
     exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
     create table logmnr_results tablespace users as select * from v$logmnr_contents;
     – 查询示例：按表、时间、SCN、操作用户过滤
     select scn, timestamp, operation, seg_owner, seg_name, sql_redo from logmnr_results where seg_name=‘EMP’ and operation in (‘INSERT’,‘UPDATE’,‘DELETE’);
     exec dbms_logmnr.end_logmnr;
• 场景 C：将 sqlplus 输出结构化保存，便于后续分析
  sqlplus -S /nolog <<‘EOF’
  SET HEADING OFF FEEDBACK OFF VERIFY OFF PAGESIZE 0 LINESIZE 1000 TRIMSPOOL ON
  SPOOL /var/log/sqlplus/query_$(date +%F_%H%M%S).csv
  SELECT inst_id||‘:’||sid||‘,’||username||‘,’||sql_id||‘,’||substr(sql_text,1,120)
  FROM gv$session ses, gv$sqlstats sql
  WHERE ses.sql_id=sql.sql_id(+) AND status=‘ACTIVE’ AND username is not null
  AND ses.inst_id||‘:’||ses.sid <> sys_context(‘USERENV’,‘INSTANCE’)||‘:’||sys_context(‘USERENV’,‘SID’);
  SPOOL OFF
  EXIT;
  EOF
  后续可用 awk/cut/sort/uniq/grep 做字段提取、去重、频次统计与异常模式识别。

四、自动化与可视化建议

• 日志轮转与保留：为 sqlplus 输出与脚本日志配置 logrotate（按日切分、压缩、保留 N 天），避免磁盘被占满。
• 集中化与告警：将 /var/log/syslog、$ORACLE_HOME/log/alert.log、listener.log 与 sqlplus 结果文件接入 ELK/Graylog，用 Kibana/Grafana 做趋势与异常告警（如登录失败激增、ADG 延迟、日志切换异常）。
• 轻量日报：用 Logwatch 生成系统层面日报，配合自定义脚本对 Oracle 关键指标（活跃会话、等待事件 TopN、日志切换次数）做汇总邮件。

五、排错清单

• 找不到 sqlplus 输出：确认脚本使用了 SPOOL 或 shell 重定向，并检查目录写权限与磁盘空间。
• 登录极慢：用 strace -T -t -f 定位卡在何处（常见为 DNS、审计目录、口令字典、网络往返）；必要时优化解析、迁移/清理审计文件或调整 DNS。
• 归档/ADG 延迟：对比 v$archived_log.applied 时间与当前时间，结合 RMAN 与 LogMiner 复核是否存在归档缺口或备库应用瓶颈。
• 日志切换频繁：检查 v$log 大小与数量、I/O 性能、批量作业与统计信息收集窗口，必要时增大日志组或错峰跑批。