投稿
  1. 云图网首页
  2. 技术专区
  3. 大数据

Oracle数据库日志挖掘恢复数据笔记

• 大数据 
-- 创建学生表
CREATE TABLE STUDENT (
    STUDENTID INT PRIMARY KEY,
    NAME VARCHAR(100),
    AGE INT,
    GENDER VARCHAR(10),
    EMAIL VARCHAR(100)
);

-- 插入100条数据
BEGIN
    FOR i IN 1..100 LOOP
        INSERT INTO STUDENT (STUDENTID, NAME, AGE, GENDER, EMAIL)
        VALUES (i, 'Student' || i, 20 + MOD(i, 5), CASE WHEN MOD(i, 2) = 0 THEN 'Female' ELSE 'Male' END, 'student' || i || '@example.com');
    END LOOP;
    COMMIT;
END;

6913e9cb9677b.png

模拟误操作删除数据

--删除学号大于20的数据，模拟用户失误删除数据
delete from STUDENT where STUDENTID>20;
commit;

1. 前置条件检查

1.1 确认数据库归档模式

-- 检查数据库是否处于归档模式
SELECT log_mode FROM v$database;

-- 检查归档日志信息
SELECT name, log_mode, database_role FROM v$database;

1.2 确认补充日志已开启

-- 检查补充日志状态
SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui 
FROM v$database;

6913ea384b68d.png

-- 如果未开启，需要启用补充日志
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

-- 其他方式的开启（后续三个脚本语句）
-- 为特定表开启最小补充日志（记录主键）
ALTER TABLE your_schema.your_critical_table ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
-- 或者开启更全面的日志（记录所有列）
ALTER TABLE your_schema.your_critical_table ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
-- 检查开启状态
SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_all
FROM user_tables WHERE table_name = 'YOUR_CRITICAL_TABLE';

我这里执行的是前三个语句

-- 如果未开启，需要启用补充日志
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

执行完再检查补充日志是否开启

6913ef0bb0b74.png

1.3 安装LogMiner

LogMiner是Oracle数据库的一个组件，通常默认安装。

--检查组件是否安装
SELECT DISTINCT name FROM DBA_SOURCE WHERE TYPE = 'PACKAGE' AND NAME IN ('DBMS_LOGMNR', 'DBMS_LOGMNR_D');

6913f0425b7be.png

如果没有，你需要运行两个脚本：


--这两个脚本会创建LogMiner所需的包。
$ORACLE_HOME/rdbms/admin/dbmslm.sql
$ORACLE_HOME/rdbms/admin/dbmslmd.sql

2. 日志挖掘环境准备

2.1 创建日志挖掘目录

-- 创建专用目录
CREATE DIRECTORY logmnr_dir AS '/u01/app/oracle/logmnr';
GRANT READ, WRITE ON DIRECTORY logmnr_dir TO system;

-- 或者使用现有目录
SELECT * FROM dba_directories WHERE directory_name = 'DATA_PUMP_DIR';

6913f92a15abb.png

2.2 创建数据字典文件（可选但推荐）

-- 提取数据字典到文件
EXEC DBMS_LOGMNR_D.BUILD(dictionary_filename => 'logmnr_dict.ora', -
                        dictionary_location => 'LOGINR_DIR', -
                        options => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

3. 确定恢复时间范围

3.1 确认删除操作时间

-- 查询最近归档日志信息
SELECT sequence#, first_time, next_time, name 
FROM v$archived_log 
WHERE first_time > SYSDATE - 1 
ORDER BY first_time DESC;

-- 查看当前在线日志
SELECT group#, sequence#, bytes, members, status 
FROM v$log;

3.2 确定包含删除操作的日志文件

-- 根据时间范围查找相关日志文件
SELECT name, first_time, next_time
FROM v$archived_log
WHERE first_time BETWEEN TO_DATE('2024-01-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS')
                     AND TO_DATE('2024-01-01 11:00:00', 'YYYY-MM-DD HH24:MI:SS')
ORDER BY first_time;

4. 执行日志挖掘

4.1 添加日志文件进行分析

-- 开始LogMiner会话
BEGIN
    DBMS_LOGMNR.ADD_LOGFILE(
        LogFileName => '/u01/app/oracle/arch/arch_1_1234.arc',
        Options => DBMS_LOGMNR.NEW
    );
    
    -- 添加更多日志文件
    DBMS_LOGMNR.ADD_LOGFILE(
        LogFileName => '/u01/app/oracle/arch/arch_1_1235.arc',
        Options => DBMS_LOGMNR.ADDFILE
    );
END;

4.2 启动LogMiner

-- 使用数据字典启动（推荐）
BEGIN
    DBMS_LOGMNR.START_LOGMNR(
        Options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG +
                   DBMS_LOGMNR.COMMITTED_DATA_ONLY +
                   DBMS_LOGMNR.SKIP_CORRUPTION
    );
END;


-- 或者使用外部数据字典文件
BEGIN
    DBMS_LOGMNR.START_LOGMNR(
        StartTime => TO_DATE('2024-01-01 10:30:00', 'YYYY-MM-DD HH24:MI:SS'),
        EndTime => TO_DATE('2024-01-01 10:45:00', 'YYYY-MM-DD HH24:MI:SS'),
        DictFileName => 'LOGINR_DIR:logmnr_dict.ora'
    );
END;

5. 分析挖掘结果

5.1 查询删除操作记录

-- 查询特定表的删除操作
SELECT scn, timestamp, sql_redo, sql_undo, session_info, seg_owner, seg_name
FROM v$logmnr_contents
WHERE seg_owner = 'WUSHAN' 
  AND seg_name = 'STUDENT'
  AND operation = 'DELETE'
  AND timestamp BETWEEN TO_DATE('2025-11-12 08:30:00', 'YYYY-MM-DD HH24:MI:SS')
                    AND TO_DATE('2025-11-12 11:30:00', 'YYYY-MM-DD HH24:MI:SS')
ORDER BY scn;
--或者
SELECT operation, sql_redo, sql_undo, timestamp, seg_owner, seg_name
FROM v$logmnr_contents
WHERE operation IN ( 'DELETE') and seg_owner='WUSHAN' and timestamp>=TO_DATE('2025-11-12 08:30:00', 'YYYY-MM-DD HH24:MI:SS') 
ORDER BY scn;


-- 查询所有DML操作
SELECT operation, sql_redo, sql_undo, timestamp, seg_owner, seg_name
FROM v$logmnr_contents
WHERE operation IN ('INSERT', 'UPDATE', 'DELETE')
ORDER BY scn;

6913f4f90c7ad.png

此时可以直接复制sql_undo列的语句进行执行恢复

6913f5249e97b.png

数据验证

6913f54e35b6b.png

此时数据已经恢复过来到100条数据了。

当数据量非常大时候，可以执行以下示例的脚本（对应的对象改成你自己的数据库对象）进行获取恢复语句

5.2 生成恢复SQL脚本

-- 创建恢复脚本
SET PAGESIZE 0
SET LINESIZE 1000
SET FEEDBACK OFF
SET HEADING OFF
SPOOL /u01/app/oracle/scripts/recover_data.sql

SELECT 'INSERT INTO ' || seg_owner || '.' || seg_name || 
       ' VALUES (' || 
       LISTAGG(
           CASE 
               WHEN column_name IS NULL THEN 'NULL'
               ELSE '''' || REPLACE(column_value, '''', '''''') || ''''
           END, ','
       ) WITHIN GROUP (ORDER BY column_position) || 
       ');' AS recovery_sql
FROM (
    SELECT lc.seg_owner, lc.seg_name, lc.operation,
           REGEXP_SUBSTR(lc.sql_undo, '([^,]+)', 1, LEVEL) AS column_value,
           LEVEL AS column_position,
           NULL AS column_name  -- 实际应用中可能需要关联dba_tab_columns获取列名
    FROM v$logmnr_contents lc
    WHERE lc.seg_owner = 'SCOTT'
      AND lc.seg_name = 'EMP'
      AND lc.operation = 'DELETE'
      AND lc.sql_undo IS NOT NULL
    CONNECT BY LEVEL <= REGEXP_COUNT(lc.sql_undo, ',') + 1
       AND PRIOR lc.scn = lc.scn
       AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
)
GROUP BY seg_owner, seg_name, scn
ORDER BY scn;

SPOOL OFF

6. 执行数据恢复

6.1 验证恢复脚本

-- 首先验证恢复数据
SELECT COUNT(*) FROM WUSHAN.STUDENT;  -- 当前记录数

-- 检查恢复脚本中的记录数
SELECT COUNT(*) FROM (
    -- 这里放置从日志挖掘中提取的INSERT语句对应的查询
);

6913f54e35b6b.png

6.2 执行恢复操作

-- 方法1：直接执行生成的INSERT语句
@/u01/app/oracle/scripts/recover_data.sql

-- 方法2：创建临时表存储恢复数据（以scott.emp为例）
CREATE TABLE scott.emp_recover AS
SELECT * FROM scott.emp WHERE 1=0;

-- 插入恢复数据到临时表
-- 修改生成的INSERT语句指向临时表

-- 验证数据完整性后合并到原表
INSERT INTO scott.emp 
SELECT * FROM scott.emp_recover;

COMMIT;

7. 清理工作

7.1 结束LogMiner会话

-- 结束日志挖掘会话
BEGIN
    DBMS_LOGMNR.END_LOGMNR();
END;

7.2 清理临时对象

-- 删除临时表（如果创建了）
DROP TABLE scott.emp_recover PURGE;

-- 清理脚本文件
-- 在操作系统级别删除生成的脚本文件

8. 高级技巧和注意事项

8.1 处理大量数据的优化

-- 使用更精确的时间范围
BEGIN
    DBMS_LOGMNR.START_LOGMNR(
        StartScn => 12345678,  -- 指定开始SCN
        EndScn => 12345890,    -- 指定结束SCN  
        Options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG +
                   DBMS_LOGMNR.COMMITTED_DATA_ONLY
    );
END;

8.2 使用SCN进行精确恢复

-- 基于SCN的恢复更精确
SELECT min_scn, next_scn, name 
FROM v$archived_log 
WHERE 12345678 BETWEEN min_scn AND next_scn;

8.3 监控日志挖掘进度

-- 查看当前LogMiner会话
SELECT session_name, db_name, logminer_id, status 
FROM v$logmnr_session;

9. 故障排除

9.1 常见错误及解决

-- 错误：ORA-01306 需要在启动LogMiner前指定DB_ID
SELECT dbid FROM v$database;

BEGIN
    DBMS_LOGMNR.START_LOGMNR(
        StartTime => SYSDATE - 1,
        EndTime => SYSDATE,
        DictFileName => 'LOGINR_DIR:logmnr_dict.ora',
        Dbid => 1234567890  -- 替换为实际DBID
    );
END;


-- 错误：ORA-01291 缺少日志文件
-- 确保添加了所有相关的日志文件

10. 预防措施

10.1 定期备份数据字典

-- 定期更新数据字典文件
EXEC DBMS_LOGMNR_D.BUILD(dictionary_filename => 'logmnr_dict_'||TO_CHAR(SYSDATE,'YYYYMMDD')||'.ora', -
                        dictionary_location => 'LOGINR_DIR', -
                        options => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

10.2 实施删除保护策略

-- 使用闪回查询测试删除前数据
SELECT * FROM scott.emp AS OF TIMESTAMP SYSDATE - 5/1440;  -- 5分钟前

-- 启用闪回数据归档（需要额外许可）
ALTER TABLE scott.emp FLASHBACK ARCHIVE fba1;

重要提示：

  • 在生产环境执行前，务必在测试环境验证整个过程
  • 确保有足够的磁盘空间存储日志文件和数据字典
  • 恢复操作建议在业务低峰期进行
  • 始终在执行前备份当前数据状态

原创文章，作者：kirin，如若转载，请注明出处：https://blog.ytso.com/tech/bigdata/318098.html

赞 (0)
0 0
上一篇 1天前
下一篇 1天前

相关推荐

发表回复

登录后才能评论