第十章：日志管理

文档信息

适用版本：Oracle 12c / 18c / 19c / 21c
阅读时间：约 45 分钟
前置知识：第九章性能优化

10.1 重做日志 (Redo Log)

10.1.1 重做日志结构

┌─────────────────────────────────────────────────────────────────┐
│                      Oracle 重做日志结构                          │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  内存 → Redo Log Buffer → LGWR → 在线日志文件                      │
│                                                                 │
│  ┌─────────────────────────────────────────────────────────────┐ │
│  │  在线日志组 (LG1)                                           │ │
│  │  ┌─────────────┐  ┌─────────────┐  ┌─────────────┐         │ │
│  │  │ Member 1    │  │ Member 2    │  │ Member 3    │         │ │
│  │  │ redo01a.log │  │ redo01b.log │  │ redo01c.log │         │ │
│  │  └─────────────┘  └─────────────┘  └─────────────┘         │ │
│  └─────────────────────────────────────────────────────────────┘ │
│                           │                                      │
│                           ▼ 日志切换                             │
│  ┌─────────────────────────────────────────────────────────────┐ │
│  │  在线日志组 (LG2)                                           │ │
│  │  ┌─────────────┐  ┌─────────────┐                          │ │
│  │  │ redo02a.log │  │ redo02b.log │                          │ │
│  │  └─────────────┘  └─────────────┘                          │ │
│  └─────────────────────────────────────────────────────────────┘ │
│                           │                                      │
│                           ▼ 归档                                 │
│  ┌─────────────────────────────────────────────────────────────┐ │
│  │  归档日志                                                   │ │
│  │  ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐            │ │
│  │  │ ARC001  │ │ ARC002  │ │ ARC003  │ │ ARC004  │            │ │
│  │  └─────────┘ └─────────┘ └─────────┘ └─────────┘            │ │
│  └─────────────────────────────────────────────────────────────┘ │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

10.1.2 查看重做日志

-- 查看日志组
SELECT 
    group#,
    thread#,
    sequence#,
    bytes/1024/1024 AS size_mb,
    members AS member_count,
    status,
    archived
FROM v$log
ORDER BY group#;

-- 查看日志组成员
SELECT 
    group#,
    member,
    type,
    status
FROM v$logfile
ORDER BY group#;

-- 查看当前日志
SELECT 
    sequence#,
    status,
    bytes,
    first_change#,
    next_change#
FROM v$log
WHERE status = 'CURRENT';

-- 日志序列号历史
SELECT 
    sequence#,
    first_time,
    next_time,
    ROUND((next_time - first_time) * 24 * 60, 2) AS minutes,
    archived,
    status
FROM v$log_history
ORDER BY sequence# DESC
FETCH FIRST 20 ROWS ONLY;

10.1.3 管理重做日志

-- 添加日志组
ALTER DATABASE ADD LOGFILE GROUP 4 (
    '/u01/app/oracle/oradata/ORCL/redo04a.log',
    '/u01/app/oracle/oradata/ORCL/redo04b.log'
) SIZE 100M;

-- 添加日志成员
ALTER DATABASE ADD LOGFILE MEMBER 
'/u01/app/oracle/oradata/ORCL/redo01c.log' TO GROUP 1;

-- 删除日志成员
ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/ORCL/redo01c.log';

-- 删除日志组（必须 inactive）
ALTER DATABASE DROP LOGFILE GROUP 4;

-- 调整日志大小（需重建）
ALTER DATABASE ADD LOGFILE GROUP 5 (
    '/u01/app/oracle/oradata/ORCL/redo05a.log'
) SIZE 200M;

-- 强制日志切换
ALTER SYSTEM SWITCH LOGFILE;

-- 清除日志组
ALTER DATABASE CLEAR LOGFILE GROUP 3;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;  -- 未归档也可清除

-- 查看日志使用率
SELECT 
    group#,
    status,
    (members * bytes) / 1024 / 1024 AS total_mb,
    archived
FROM v$log;

10.2 归档日志管理

10.2.1 归档配置

-- 查看归档配置
SHOW PARAMETER LOG_ARCHIVE_FORMAT;
SHOW PARAMETER LOG_ARCHIVE_DEST_1;
SHOW PARAMETER LOG_ARCHIVE_DEST_STATE_1;

-- 查看归档状态
ARCHIVE LOG LIST;
SELECT log_mode FROM v$database;

-- 配置本地归档
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 
    'LOCATION=/u01/app/oracle/arch/ORCL MANDATORY REOPEN';

-- 配置闪回恢复区归档
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 
    'LOCATION=USE_DB_RECOVERY_FILE_DEST MANDATORY';

-- 配置远程归档（DataGuard）
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 
    'SERVICE=STANDBY_DB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY';

-- 归档格式
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT = 'arch_%t_%s_%r.arc' SCOPE=SPFILE;
-- %t: 线程号
-- %s: 序列号
-- %r: Resetlogs ID
-- %d: DBID

-- 启用归档
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

-- 禁用归档
ALTER DATABASE NOARCHIVELOG;

10.2.2 归档空间管理

-- 查看闪回恢复区
SELECT 
    name,
    space_limit/1024/1024/1024 AS limit_gb,
    space_used/1024/1024/1024 AS used_gb,
    space_reclaimable/1024/1024/1024 AS reclaimable_gb,
    ROUND((space_used/space_limit)*100, 2) AS usage_pct,
    number_of_files
FROM v$recovery_file_dest;

-- 配置闪回恢复区大小
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 100G SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u01/app/oracle/fra' SCOPE=BOTH;

-- 查看归档日志大小
SELECT 
    ROUND(SUM(blocks * block_size)/1024/1024/1024, 2) AS total_gb
FROM v$archived_log
WHERE deleted = 'NO'
AND completion_time >= SYSDATE - 30;

-- 查看每天归档量
SELECT 
    TRUNC(completion_time) AS arch_date,
    COUNT(*) AS file_count,
    ROUND(SUM(blocks * block_size)/1024/1024/1024, 2) AS size_gb
FROM v$archived_log
GROUP BY TRUNC(completion_time)
ORDER BY arch_date DESC;

10.2.3 归档清理

-- RMAN 删除过期归档
RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';

-- 删除指定时间之前
RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE "TO_DATE('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";

-- 删除指定序列号之前
RMAN> DELETE ARCHIVELOG UNTIL SEQUENCE 12345;

-- 强制删除
RMAN> DELETE FORCE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1';

-- 设置保留策略
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;

-- 交叉验证归档
RMAN> CROSSCHECK ARCHIVELOG ALL;
RMAN> DELETE EXPIRED ARCHIVELOG ALL;

10.3 审计日志

10.3.1 审计配置

-- 查看审计配置
SHOW PARAMETER AUDIT_TRAIL;

-- 启用审计
ALTER SYSTEM SET AUDIT_TRAIL = DB EXTENDED SCOPE=SPFILE;
-- 参数值：
-- NONE: 禁用
-- DB: 写入 SYS.AUD$
-- DB,EXTENDED: 包含 SQL 语句和绑定变量
-- OS: 写入操作系统文件
-- XML: 写入 XML 格式
-- XML,EXTENDED: 包含 SQL 语句

-- 重启数据库使审计设置生效
SHUTDOWN IMMEDIATE;
STARTUP;

-- 配置审计选项
-- 审计登录
AUDIT SESSION;
AUDIT SESSION BY USER hr, oe;

-- 审计 DDL
AUDIT TABLE;  -- 审计 CREATE, ALTER, DROP TABLE
AUDIT VIEW;
AUDIT SEQUENCE;
AUDIT SYNONYM;
AUDIT PROCEDURE;
AUDIT TRIGGER;
AUDIT FUNCTION;
AUDIT PACKAGE;

-- 审计 DML
AUDIT INSERT TABLE BY USER hr;
AUDIT UPDATE TABLE, DELETE TABLE BY USER hr;
AUDIT SELECT TABLE, INSERT TABLE, UPDATE TABLE, DELETE TABLE;

-- 审计权限
AUDIT PRIVILEGE CREATE ANY TABLE;
AUDIT PRIVILEGE DROP ANY TABLE;
AUDIT ALL PRIVILEGES;

10.3.2 审计查询

-- 查看审计记录
SELECT 
    username,
    timestamp,
    action_name,
    obj_name,
    sql_text,
    returncode
FROM dba_audit_trail
WHERE username = 'HR'
ORDER BY timestamp DESC;

-- 查看审计会话
SELECT 
    username,
    terminal,
    timestamp,
    action_name,
    returncode,
    priv_used
FROM dba_audit_session
WHERE username = 'HR'
ORDER BY timestamp DESC;

-- 查看审计对象访问
SELECT 
    username,
    timestamp,
    obj_name,
    action_name,
    sql_text
FROM dba_audit_object
WHERE obj_name = 'EMPLOYEES'
ORDER BY timestamp DESC;

-- 查看失败登录
SELECT 
    username,
    terminal,
    timestamp,
    returncode,
    priv_used
FROM dba_audit_session
WHERE returncode != 0
ORDER BY timestamp DESC;

-- 查看谁使用了 SYSDBA 权限
SELECT 
    username,
    userhost,
    timestamp,
    action_name
FROM dba_audit_trail
WHERE priv_used = 'SYSDBA'
OR action_name IN ('LOGON', 'LOGOFF');

10.3.3 精细化审计 (FGA)

-- 创建细粒度审计策略
BEGIN
    DBMS_FGA.ADD_POLICY(
        object_schema   => 'HR',
        object_name     => 'EMPLOYEES',
        policy_name     => 'AUDIT_SALARY_ACCESS',
        audit_condition => 'SALARY > 5000',
        audit_column    => 'SALARY,COMMISSION_PCT',
        handler_schema  => NULL,
        handler_module  => NULL,
        enable          => TRUE,
        statement_types => 'SELECT,UPDATE',
        audit_trail     => DBMS_FGA.XML + DBMS_FGA.EXTENDED
    );
END;
/

-- 查看 FGA 审计
SELECT 
    timestamp,
    db_user,
    object_name,
    policy_name,
    sql_text,
    bind_variables
FROM dba_fga_audit_trail
WHERE policy_name = 'AUDIT_SALARY_ACCESS';

-- 删除审计策略
BEGIN
    DBMS_FGA.DROP_POLICY(
        object_schema => 'HR',
        object_name   => 'EMPLOYEES',
        policy_name   => 'AUDIT_SALARY_ACCESS'
    );
END;
/

10.4 日志分析

10.4.1 Alert Log

-- 查看 Alert Log 位置
SHOW PARAMETER BACKGROUND_DUMP_DEST;

-- 查询告警日志中的错误
SELECT 
    TO_CHAR(originating_timestamp, 'YYYY-MM-DD HH24:MI:SS') AS timestamp,
    message_text
FROM X$DBGALERTEXT
WHERE message_text LIKE '%ORA-%'
AND originating_timestamp > SYSDATE - 7
ORDER BY originating_timestamp DESC;

-- 查询特定错误
SELECT * FROM X$DBGALERTEXT
WHERE message_text LIKE '%ORA-00600%'
OR message_text LIKE '%ORA-07445%';

-- 统计错误类型
SELECT 
    SUBSTR(message_text, 1, INSTR(message_text, ':') - 1) AS error_type,
    COUNT(*) AS error_count
FROM X$DBGALERTEXT
WHERE message_text LIKE '%ORA-%'
AND originating_timestamp > SYSDATE - 7
GROUP BY SUBSTR(message_text, 1, INSTR(message_text, ':') - 1)
ORDER BY COUNT(*) DESC;

10.4.2 Trace 文件

-- 查看 Trace 文件位置
SHOW PARAMETER USER_DUMP_DEST;    -- 用户进程
SHOW PARAMETER BACKGROUND_DUMP_DEST;  -- 后台进程

-- 生成会话跟踪
ALTER SESSION SET SQL_TRACE = TRUE;
ALTER SESSION SET EVENTS 'TRACE[RDBMS.SQL_Optimizer.*]';

-- 使用 DBMS_MONITOR
BEGIN
    -- 启用会话跟踪
    DBMS_MONITOR.SESSION_TRACE_ENABLE(
        waits  => TRUE,
        binds => TRUE
    );
END;
/

-- 关闭跟踪
BEGIN
    DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => :sid, serial_num => :serial);
END;
/

-- 启用服务跟踪
BEGIN
    DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(
        service_name  => 'ORCL',
        module_name   => 'PAYROLL_APP',
        action_name   => NULL,
        waits         => TRUE,
        binds         => TRUE
    );
END;
/

-- 查找 Trace 文件
SELECT 
    p.spid AS os_pid,
    s.username,
    s.program,
    p.trace_filename
FROM v$process p
JOIN v$session s ON p.addr = s.paddr
WHERE s.sid = :sid AND s.serial# = :serial;

10.4.3 日志挖掘入门

-- LogMiner 配置（详见第十五章）
-- 1. 创建数据字典
EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', '/u01/app/oracle/oradata/ORCL/', OPTIONS => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

-- 2. 添加日志文件
EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/arch/ORCL/arch_001.arc', DBMS_LOGMNR.NEW);

-- 3. 启动 LogMiner
EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME => '/u01/app/oracle/oradata/ORCL/dictionary.ora');

-- 4. 查询日志内容
SELECT 
    username,
    sql_redo,
    sql_undo,
    timestamp
FROM V$LOGMNR_CONTENTS
WHERE table_name = 'EMPLOYEES';

-- 5. 结束 LogMiner
EXECUTE DBMS_LOGMNR.END_LOGMNR();

10.5 本章小结

✅ 重做日志：事务持久性保证，日志切换与组成员管理
✅ 归档日志：DataGuard 基础，空间清理与保留策略
✅ 审计日志：记录登录、DDL、DML 操作，FGA 细粒度审计
✅ Alert Log：系统错误与警告
✅ Trace 文件：SQL 跟踪与诊断
✅ 日志挖掘：LogMiner 分析重做日志内容