LogMiner 归档日志挖掘深度解析
一、问题背景与技术痛点
某医疗客户的核心业务系统中，运维人员执行 `CREATE OR REPLACE PROCEDURE` 语句时，误将业务用户的关键存储过程覆盖，且操作时间点距发现时已超过 10 小时。客户急需找回原始存储过程逻辑，否则将影响后续业务流程。
为何常规方案行不通？
1. DDL 操作的特殊性： `CREATE OR REPLACE` 属于 DDL 语句 ，执行时会隐式提交事务， Oracle 闪回查询（ Flashback Query ）仅适用于 DML 操作的数据恢复，无法直接恢复被 DDL 覆盖的数据库对象；
2. 时间窗口失效：由于 操作已过去十多个小时 ，基表数据的闪回查询窗口已超出限制，通过查询数据字典历史版本的方式也无法获取原始内容；
3. 场景创新性：团队 以往使用 LogMiner 工具多为追溯数据变更（如误删数据、异常更新），未处理过存储过程等 PL/SQL 对象的版本找回 ，无成熟经验可直接复用。
二、核心原理与猜想推导
1. 底层存储逻辑拆解
Oracle 中，存储过程的元数据（如对象 ID 、审计信息）存储在 `SYS.PROCEDURE$` 表，而源码则按行存储在 `SYS.SOURCE$` 表（通过 `OBJ#` 关联）。基于这一特性，我提出核心猜想：
`CREATE OR REPLACE` 覆盖存储过程时 ，底层逻辑应为「删除旧源码 → 插入新源码」 —— 即先删除 `SYS.SOURCE$` 中该存储过程对应的旧行记录，再插入新版本的源码行 。若猜想成立，归档日志中必然会记录这一系列删除操作，其 `SQL_UNDO` 字段就是恢复原始源码的关键。
2. 测试环境模拟验证
为验证猜想，我搭建了与客户环境一致的测试环境，完整复现存储过程创建 → 覆盖的全流程：
（ 1 ）创建原始存储过程
CREATE OR REPLACE PROCEDURE mary.insert_test_data
IS
v_ind INTEGER; -- 声明循环变量
BEGIN
v_ind := 0; -- 初始化变量
LOOP
-- 插入数据到mary.tt 表
INSERT INTO mary.tt(id, time)
VALUES(v_ind, SYSDATE);
-- 每插入1 条提交（原逻辑保留，若想批量提交可调整）
COMMIT;
v_ind := v_ind + 1; -- 原始自增步长：1
-- 循环终止条件：插入10 万条后退出
IF v_ind = 100000 THEN
EXIT; -- 替代return ，PL/SQL 循环中用EXIT 更规范
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
-- 异常时回滚并打印错误信息（便于测试排错）
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(' 错误码：' || SQLCODE || ' ，错误信息：' || SQLERRM);
RAISE; -- 可选：重新抛出异常，便于外层捕获
END;
/
（ 2 ）模拟误操作覆盖存储过程
修改两个核心常量：将 `v_ind` 自增步长由 1 改为 2 ，循环终止条件由 10 万条改为 30 万条：
CREATE OR REPLACE PROCEDURE mary.insert_test_data
IS
v_ind INTEGER; -- 声明循环变量
BEGIN
v_ind := 0; -- 初始化变量
LOOP
-- 插入数据到 mary.tt 表
INSERT INTO mary.tt(id, time)
VALUES(v_ind, SYSDATE);
-- 每插入 1 条提交（原逻辑保留，若想批量提交可调整）
COMMIT;
v_ind := v_ind + 2; -- 修改后自增步长： 2
-- 循环终止条件：插入 30 万条后退出
IF v_ind = 300000 THEN
EXIT; -- 替代 return ， PL/SQL 循环中用 EXIT 更规范
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
-- 异常时回滚并打印错误信息（便于测试排错）
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(' 错误码： ' || SQLCODE || ' ，错误信息： ' || SQLERRM);
RAISE; -- 可选：重新抛出异常，便于外层捕获
END;
/
（ 3 ）手工切换归档日志
确保误操作记录写入目标归档日志，便于后续挖掘：
ALTER SYSTEM ARCHIVE LOG CURRENT;
通过 `ARCHIVE LOG LIST` 确认当前归档号为 179 ，目标归档日志文件为 `/oradata/archivelog/1_179_1203889318.dbf` 。
三、 LogMiner 挖掘实施：从猜想验证到源码提取
1. 工具准备：匿名块设计思路
为高效挖掘日志，我设计了两个匿名块，分别解决「快速定位操作」和「详细数据分析」的需求：
- 检查操作类型版：快速统计日志中各对象的操作类型和次数，验证「删除旧源码 → 插入新源码」的猜想；
- 建表版：将日志数据持久化到物理表，便于后续筛选、排序和细节分析，避免重复解析日志。
（ 1 ）检查操作类型版匿名块
set linesize 5000
SET SERVEROUTPUT ON SIZE UNLIMITED;
BEGIN
DBMS_LOGMNR.ADD_LOGFILE('/oradata/archivelog/1_179_1203889318.dbf');
DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
FOR rec IN (
SELECT SEG_OWNER, SEG_NAME, OPERATION ,count(*) as op_count
FROM V$LOGMNR_CONTENTS
WHERE 1=1 group by SEG_OWNER, SEG_NAME, OPERATION order by SEG_OWNER, SEG_NAME, OPERATION,op_count
) LOOP
DBMS_OUTPUT.PUT_LINE('SEG_OWNER: ' || RPAD(rec.SEG_OWNER, 10) ||
' | SEG_NAME: ' || RPAD(rec.SEG_NAME, 40) ||
' | OPERATION: ' || RPAD(rec.OPERATION, 10) ||
' | op_count: ' || RPAD(rec.op_count, 10));
END LOOP;
DBMS_LOGMNR.END_LOGMNR;
END;
/
（ 2 ）建表版匿名块
set linesize 5000
BEGIN
DBMS_LOGMNR.ADD_LOGFILE('/oradata/archivelog/1_179_1203889318.dbf');
DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
EXECUTE IMMEDIATE '
CREATE TABLE log202512 AS
SELECT * FROM V$LOGMNR_CONTENTS
WHERE 1=1
';
DBMS_LOGMNR.END_LOGMNR;
DBMS_OUTPUT.PUT_LINE('Table created with data from V$LOGMNR_CONTENTS');
EXCEPTION
WHEN OTHERS THEN
BEGIN
DBMS_LOGMNR.END_LOGMNR;
EXCEPTION
WHEN OTHERS THEN
NULL; -- 忽略 LogMiner 已关闭的情况
END;
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
RAISE;
END;
/
2. 第一步：猜想验证 —— 操作类型统计分析
执行「检查操作类型版」匿名块后，得到关键统计结果（筛选核心信息如下）：
|
SEG_OWNER |
SEG_NAME |
OPERATION |
op_count |
分析结论 |
|
MARY |
INSERT_TEST_DATA |
DDL |
2 |
对应存储过程的「新建」和「覆盖修改」两次 DDL 操作 |
|
SYS |
PROCEDURE$ |
INSERT |
1 |
新建存储过程时，向元数据表插入元数据 |
|
SYS |
PROCEDURE$ |
UPDATE |
1 |
覆盖存储过程时，更新元数据表的元数据（如审计信息） |
|
SYS |
SOURCE$ |
DELETE |
28 |
覆盖时删除原始源码，共 28 行（与存储过程源码行数一致） |
|
SYS |
SOURCE$ |
INSERT |
56 |
新建插入 28 行源码 + 覆盖时插入 28 行新源码，总数 56 行，与 DELETE 次数呈 2:1 关系 |
核心发现： `SYS.SOURCE$` 表的「 DELETE 28 次 + INSERT 56 次」完全印证了最初的猜想 —— 存储过程覆盖的底层逻辑是「删除旧源码 → 插入新源码」 ，这为后续提取原始源码提供了关键依据。
3. 第二步：源码提取 —— 精准筛选关键记录
执行「建表版」匿名块后，日志数据已持久化到 `log202512` 表。接下来通过多轮查询，逐步定位原始源码：
（ 1 ）第一轮查询：直接查询业务用户对象
最初尝试直接筛选 `SEG_OWNER='MARY'` 的记录，期望获取存储过程的创建语句：
SELECT
SCN, TIMESTAMP, OPERATION, SQL_REDO, SQL_UNDO
FROM
sys.log202512
WHERE SEG_OWNER='MARY';
结果分析： `SQL_REDO` 字段确实包含两次 `CREATE OR REPLACE PROCEDURE` 语句，但 `SQL_UNDO` 均为空。 真实环境中归档日志通常不会保留存储过程新建的完整记录，仅靠业务用户对象查询无法获取旧版本。
（ 2 ）第二轮查询：挖掘系统表关键操作
既然业务用户对象查询无果， 转而聚焦系统表 `SYS.SOURCE$` 和 `SYS.PROCEDURE$` ，筛选有有效 `SQL_UNDO` 的记录：
SELECT
SCN, TIMESTAMP, XIDUSN, XIDSQN, OPERATION,
SEG_OWNER, SEG_NAME, SQL_REDO, SQL_UNDO
FROM
sys.log202512
WHERE
SQL_UNDO IS NOT NULL
AND SQL_UNDO <> 'Unsupported'
AND SEG_OWNER = 'SYS'
AND SEG_NAME IN ('SOURCE$', 'PROCEDURE$');
（ 3 ）关键记录解析：还原原始源码
查询结果中，两条核心事务记录揭示了完整流程：
事务 1 ： 新建存储过程（ SCN=5179831 ， TIMESTAMP=2025-12-14 00:46:58 ）
- 首先向 `SYS.PROCEDURE$` 插入元数据：
INSERT INTO "SYS"."PROCEDURE$"("OBJ#","AUDIT$","STORAGESIZE","OPTIONS")
VALUES ('88608','--------------------------------------',NULL,'0');
- 随后向 `SYS.SOURCE$` 插入 28 行源码（按行号排序），例如：
INSERT INTO "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") VALUES ('88608','1','PROCEDURE insert_test_data¶');
INSERT INTO "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") VALUES ('88608','2','IS¶');
INSERT INTO "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") VALUES ('88608','3',' v_ind INTEGER; -- 声明循环变量 ¶');
...
INSERT INTO "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") VALUES ('88608','14',' v_ind := v_ind + 1;¶');
这部分记录完整呈现了原始存储过程的源码结构， `OBJ#=88608` 是该存储过程的唯一标识。
事务 2 ： 存储过程覆盖修改（ SCN=5179853 ， TIMESTAMP=2025-12-14 00:47:16 ）
- 首先更新 `SYS.PROCEDURE$` 的元数据：
UPDATE "SYS"."PROCEDURE$"
SET "AUDIT$" = '--------------------------------------', "OPTIONS" = '0'
WHERE "OBJ#" = '88608' AND ROWID = 'AAAADdAABAAAVH8AAi';
- 随后执行 28 条 `DELETE` 语句，删除 `SYS.SOURCE$` 中 `OBJ#=88608` 的旧源码（即原始存储过程），例如：
DELETE FROM "SYS"."SOURCE$"
WHERE "OBJ#" = '88608' AND "LINE" = '14' AND "SOURCE" = ' v_ind := v_ind + 1;¶' AND ROWID = 'AAAADgAABAAAWZjAAY';
关键亮点：这些 DELETE 语句的 `SQL_UNDO` 字段，正是恢复原始源码的核心 —— 每一条 DELETE 的 SQL_UNDO 都是一条 INSERT 语句，对应原始源码的一行。
- 最后执行 28 条 `INSERT` 语句，插入修改后的新源码（例如 `v_ind := v_ind + 2;` ）。
4. 最终提取：原始源码还原
将事务 2 中 `SYS.SOURCE$` 表的 28 条 `DELETE` 操作对应的 `SQL_UNDO` 字段提取出来，按 `LINE` 行号排序，去除换行符 `¶` 并格式化，即可完整还原原始存储过程的逻辑：
CREATE OR REPLACE PROCEDURE mary.insert_test_data
IS
v_ind INTEGER; -- 声明循环变量
BEGIN
v_ind := 0; -- 初始化变量
LOOP
-- 插入数据到 mary.tt 表
INSERT INTO mary.tt(id, time)
VALUES(v_ind, SYSDATE);
-- 每插入 1 条提交（原逻辑保留，若想批量提交可调整）
COMMIT;
v_ind := v_ind + 1;
-- 循环终止条件：插入 10 万条后退出
IF v_ind = 100000 THEN
EXIT; -- 替代 return ， PL/SQL 循环中用 EXIT 更规范
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
-- 异常时回滚并打印错误信息（便于测试排错）
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(' 错误码： ' || SQLCODE || ' ，错误信息： ' || SQLERRM);
RAISE; -- 可选：重新抛出异常，便于外层捕获
END;
/
三、关键分析与经验总结
- 猜想验证闭环：从「存储过程覆盖 = 删除旧源码 + 插入新源码」的猜想，到 LogMiner 统计结果的印证，再到系统表记录的精准提取，形成完整的技术逻辑闭环；
- 系统表的关键作用： `SYS.SOURCE$` 和 `SYS.PROCEDURE$` 作为 Oracle 存储 PL/SQL 对象的核心字典表，是本次恢复的关键突破口，其操作记录完整保留了对象的版本变更轨迹；
- SQL_UNDO 的价值： DDL 操作的 `SQL_UNDO` 虽在业务用户对象中为空，但在系统表的底层操作中依然有效，这是本次恢复成功的核心技术点。
四、总结
本次案例通过「猜想 → 验证 → 提取」的全流程，成功利用 LogMiner 工具从归档日志中找回了被覆盖的 Oracle 存储过程原始源码，解决了 DDL 操作无法通过常规闪回恢复的技术难题。核心价值不仅在于成功恢复数据，更在于形成了一套可复用的 PL/SQL 对象版本找回方案 —— 关键在于理解 Oracle 底层存储逻辑，聚焦系统字典表的操作轨迹，善用 LogMiner 的 `SQL_UNDO` 字段。
