Oracle LogMiner 提示和技巧

太叔炎彬

2023-12-01

LOGMINER：-

Logminer 是 Oracle 数据库服务器提供的一个实用程序，它挖掘 redologs 或 Archivelogs 和数据字典信息以构建 SQL 语句，并将 redolog 文件的内容保存在名为 “V$logmnr_contents”的固定视图中。
LogMiner 工具可以帮助 DBA 通过使用一组 PL/SQL 过程和函数来查找重做日志文件中的更改记录。

在内部，Oracle 将 Log Miner 技术用于其他几个功能，例如 Flashback Transaction Backout、Streams 和逻辑备用数据库。当数据仅包含几个表或单个代码更改时，LogMiner 最常用于恢复目的。

配置 Logminer 的步骤：-

在这个场景中，我们正在使用 LogMiner 实用程序检查删除表的用户名。

设置参数 UTL_FILE_DIR ：

通常我们在需要创建字典文件的地方设置 UTL_FILE_DIR 参数。从 12.2 开始，我们需要创建目录对象。

[oracle@orcl:~ orcldemo] mkdir -p /oradb/logminer

SQL> alter system set utl_file_dir='/u01/logminer' scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size 8621232 bytes
Variable Size 1006633808 bytes
Database Buffers 587202560 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.

SQL> show parameter UTL_FILE_DIR;

NAME          TYPE        VALUE
------------- ----------- -------------
utl_file_dir  string      /u01/logminer

创建并授予对目录的访问权限：

SQL> CREATE OR REPLACE DIRECTORY LOG_DIR AS '/oradb/logminer';

Directory created.

SQL> GRANT READ, WRITE ON DIRECTORY LOG_DIR TO hari;

Grant succeeded.

启用补充日志记录：

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME
--------
NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; 

Database altered.

构建 Logminer：

创建字典文件：

字典文件用于将此数据转换为更有意义的格式。指定字典的文件名和文件的目录路径名。此过程创建字典文件。

字典文件是使用DBMS_LOGMNR_D包中的BUILD过程创建的。

SQL> BEGIN
sys.DBMS_LOGMNR_D.build (
dictionary_filename => 'lgmnrdict.ora',
dictionary_location => 'LOG_DIR');
END;
/

PL/SQL procedure successfully completed.

SQL> select member from v$logfile;

MEMBER
------------------------------------------------------------------
/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_3_fm2fw8lr_.log
/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_2_fm2fvl50_.log
/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_1_fm2fvl47_.log

3 rows selected.

添加日志文件以进行分析：

首先使用NEW程序添加日志文件，然后使用ADD_LOGFILE程序添加所有日志文件。

SQL> BEGIN
DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.new,
logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_3_fm2fw8lr_.log');

DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.addfile,
logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_2_fm2fvl50_.log');

DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.addfile,
logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_1_fm2fvl47_.log');

END;
/ 

PL/SQL procedure successfully completed.

启动 logminer 进程：-

SQL> BEGIN
-- Start using all logs
DBMS_LOGMNR.start_logmnr (
dictfilename => '/oradb/logminer/lgmnrdict.ora');
END;
/ 

PL/SQL procedure successfully completed.

如果使用联机数据字典，则不需要之前的创建字典文件步骤，直接执行

BEGIN
    DBMS_LOGMNR.START_LOGMNR(
           OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
END;

查询 v$logmnr_contents :-

当 LogMiner 会话结束时， v$logmnr_contents 将不再可访问。将 v$logmnr_contents 的内容复制到用户表然后执行分析总是更好，因为查询 v$logmnr_contents 非常昂贵。

现在我们可以捕获删除表的用户，用户是“HARI”，我们还可以检查谁创建了表。

SQL> select username,table_name,sql_redo from v$logmnr_contents where seg_name='SAN';

USERNAME   TABLE_NAME  SQL_REDO
---------- ----------- -------------------------------------
HARI       SAN     create table san(num number,name varchar2(10));

SYS        SAN    ALTER TABLE "HARI"."SAN" RENAME TO                                     "BIN$dsifZpmIOhHgU4NWqMCSjA==$0" ;                                                                                                      

HARI       SAN    drop table hari.san AS  
                  "BIN$dsifZpmIOhHgU4NWqMCSjA==$0" ;  

3 rows selected.

按 SCN 过滤数据：

要按 SCN（系统更改号）过滤数据，请使用 STARTSCN 和 ENDSCN 参数，

演示：-

在执行 DML 事务之前检查 current_scn

SQL> select to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN)
--------------------
2619410

执行 DML 语句

SQL> conn hari/hari;
Connected.
SQL> insert into emp values(1,100);

1 row created.

SQL> insert into emp values(2,200);

1 row created.

SQL> insert into emp values(3,300);

1 row created.

SQL> commit;

Commit complete.

在执行 DML 事务之前检查 current_scn

SQL> select to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN)
--------------------

 2619497

添加日志文件列表以进行分析

SQL> BEGIN
DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.new,
logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_3_fm2fw8lr_.log');

DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.addfile,
logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_2_fm2fvl50_.log');

DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.addfile,
logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_1_fm2fvl47_.log');

END;
/

PL/SQL procedure successfully completed.

指定 SCN 范围以启动 logminer 进程

SQL> begin
DBMS_LOGMNR.start_logmnr (
dictfilename => '/oradb/logminer/lgmnrdict.ora',
startscn => 2619410,
endscn => 2619497);
END;
/ 

PL/SQL procedure successfully completed.

查看日志信息 v$logmnr_contents

SQL> SELECT OPERATION, STATUS, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER = 'HARI' AND TABLE_NAME = 'EMP';

OPERATION   STATUS   SQL_REDO
----------  ------   -----------------------------------------------
INSERT      0        insert into "HARI"."EMP"("EMPNO","SAL") values                         ('1','100');

INSERT      0        insert into "HARI"."EMP"("EMPNO","SAL") values                                               
                     ('2','200');
INSERT      0        insert into "HARI"."EMP"("EMPNO","SAL") values
                     ('3','300');

按时间过滤数据：

要按时间过滤数据，请在 DBMS_LOGMNR.START_LOGMNR 过程中设置 STARTTIME 和 ENDTIME 参数。

演示：-

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------
27-SEP-2018 03:13:13

执行 DML 操作

SQL> conn hari/hari;
Connected.
SQL> insert into emp values(5,500);

1 row created.

SQL> delete emp where empno=1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------
27-SEP-2018 03:15:00

添加日志文件列表以进行分析

SQL> BEGIN
DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.new,
logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_3_fm2fw8lr_.log');

DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.addfile,
logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_2_fm2fvl50_.log');

DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.addfile,
logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_1_fm2fvl47_.log');

END;
/ 

PL/SQL procedure successfully completed.

指定启动logminer进程的时间范围

begin
dbms_logmnr.start_logmnr (
dictfilename => '/oradb/logminer/lgmnrdict.ora',
starttime => TO_DATE('27-SEP-2018 03:13:13', 'DD-MON-YYYY HH:MI:SS'),
endtime => TO_DATE('27-SEP-2018 03:15:00', 'DD-MON-YYYY HH:MI:SS'));
end;
/

PL/SQL procedure successfully completed.

检查 v$logmnr_contents

SQL> SELECT OPERATION, STATUS, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER = 'HARI' AND TABLE_NAME = 'EMP';

OPERATION  STATUS  SQL_REDO
---------- ------  --------------------------------------------------
INSERT     0       insert into "HARI"."EMP"("EMPNO","SAL") values   
                   ('5','500');                   

DELETE     0       delete from "HARI"."EMP" where "EMPNO" = '1' and
                   "SAL" = '100' and ROWID = 'AAASWiAACAAAAqYAAA';

访问 LogMiner 信息：

LogMiner 信息包含在以下视图中。我们可以像查询任何其他视图一样使用 SQL 来查询它们。

V$LOGMNR_CONTENTS ： 显示对用户和表信息所做的更改。

V$LOGMNR_DICTIONARY ： 显示有关 LogMiner 字典文件的信息，前提是字典是使用 STORE_IN_FLAT_FILE 选项创建的。显示的信息包括数据库名称和状态信息。

V$LOGMNR_LOGS ： 显示有关指定重做日志的信息。每个重做日志有一行。

V$LOGMNR_PARAMETERS ： 显示有关可选 LogMiner 参数的信息，包括开始和结束系统更改编号 (SCN) 以及开始和结束时间。

Oracle LogMiner 提示和技巧

相关阅读

相关文章

相关问答

相关文档