1 、故障描述

某次某用户，误操作执行 `CREATE OR REPLACE PROCEDURE` 语句时，误将业务用户的关键存储过程覆盖，且操作时间点距发现时已超过数小时。用户急需找回原始存储过程逻辑，否则将影响后续业务流程。

2 、根因分析

（ 1 ）、创建原始存储过程CREATE OR REPLACE PROCEDURE mary.insert_test_dataIS    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` 。

（ 4 ）、创建两个匿名块

检查匿名块：快速统计日志中各对象的操作类型和次数，验证「删除旧源码 → 插入新源码」的猜想；

建表匿名块：将日志数据持久化到物理表，便于后续筛选、排序和细节分析，避免重复解析日志。

检查匿名块 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; /

建表匿名块 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; /

3 、解决方案

原存储过程 DDL 语句提取：

执行「建表」匿名块后，日志数据已持久化到 `log202512` 表。接下来通过多轮查询，逐步定位原始源码：

  （ 1 ）第一轮查询：直接查询业务用户对象

最初尝试直接筛选 `SEG_OWNER='MARY'` 的记录，期望获取存储过程的创建语句：SELECT    SCN, TIMESTAMP, OPERATION, SQL_REDO, SQL_UNDOFROM    sys.log202512WHERE 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 ）最终提取：原始 DDL 语句还原

将事务 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; /