当前位置： 首页 > article >正文

【数据库管理】⑥日志挖掘LogMiner

article 2024/12/22 20:55:03

1. LogMiner的作用

LogMiner是Oracle数据库中的一个工具，它可以用于分析数据库的重做日志文件，以了解数据库的操作历史和数据变化情况。LogMiner可以将重做日志文件中的SQL语句提取出来，并将其转换成易于理解的格式，以便用户进行分析和查询。

LogMiner的主要作用包括：

  1. 数据恢复：LogMiner可以用于恢复误删除或误修改的数据，通过分析重做日志文件中的SQL语句，可以找到被删除或修改的数据，并进行恢复操作。

  2. 数据审计：LogMiner可以用于审计数据库的操作，通过分析重做日志文件中的SQL语句，可以了解数据库的操作情况和操作者。

  3. 数据分析：LogMiner可以用于分析数据库的历史数据，通过分析重做日志文件中的SQL语句，可以了解数据库的操作历史和数据变化情况。

  4. 数据备份：LogMiner可以用于备份数据库的数据，通过分析重做日志文件中的SQL语句，可以保证备份数据的完整性和可靠性。

总之，LogMiner是Oracle数据库中非常重要的一个工具，可以用于数据恢复、数据审计、数据分析和数据备份等操作，可以帮助用户更好地管理和维护数据库。

2. 对DML进行日志挖掘

2.1 首先添加database补充日志

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

//注意: 通过PL/SQL包的DML的日志挖掘,这步要先执行,在此之后的DML操作才能从日志里挖到.在OEM中也是要求先做这一步,不同的是以前的DML操作是可以挖到的.

2.2 添加要分析的日志

-- 第一个要加载的日志文件
SQL> execute dbms_logmnr.add_logfile(logfilename=>'日志',options=>dbms_logmnr.new);
-- 可以反复添加补充多个日志文件
SQL> execute dbms_logmnr.add_logfile(logfilename=>'补充日志',options=>dbms_logmnr.addfile);

2.3 执行logmnr 分析

SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

2.4 查询分析结果

-- 可以设置时间格式,也可以在显示方式里再确定格式.
SQL> select username, scn, timestamp, sql_redo from v$logmnr_contents where seg_name='表名';

2.5 关闭日志分析

SQL> execute dbms_logmnr.end_logmnr;

2.6 案例演示

2.6.1 添加database补充日志

[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 2 15:40:49 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL>

2.6.2 DML操作表

SQL> ---scott
SQL> conn scott/tiger@PDB1;
Connected.
SQL> show user;
USER is "SCOTT"
SQL> show con_name;

CON_NAME
------------------------------
PDB1
SQL> set pagesize 200 linesize 200
SQL>
SQL> create table a1 (id int);
create table a1 (id int)
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> drop table a1 cascade;
drop table a1 cascade
                    *
ERROR at line 1:
ORA-00905: missing keyword


SQL> drop table a1;

Table dropped.

SQL> 
SQL> create table a1 (id int);

Table created.

SQL> insert into a1 values(1);

1 row created.

SQL> update a1 set id=5;

1 row updated.

SQL> commit;

Commit complete.

SQL> delete a1;

1 row deleted.

SQL> commit;

Commit complete.

SQL>

2.6.3 查看当前日志组sequence

SQL> 
SQL> show user;
USER is "SYS"
SQL> set pagesize 200 linesize 200
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARCHIVED  STATUS                                           FIRST_CHANGE# FIRST_TIME      NEXT_CHANGE# NEXT_TIME           CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- --------------- ------------ --------------- ----------
         1          1        400   52428800        512          1 NO        CURRENT                                               23765775 02-APR-23         1.8447E+19                   0
         2          1        398   52428800        512          1 YES       INACTIVE                                              23739870 02-APR-23           23753718 02-APR-23         0
         3          1        399   52428800        512          1 YES       INACTIVE                                              23753718 02-APR-23           23765775 02-APR-23         0

SQL> 

-- a1表的DML操作都写进了current组里,记住sequence#是400号.然后手工切换当前日志进archive里.

2.6.4 手工切换当前日志进archive

[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 2 15:48:00 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show user;
USER is "SYS"
SQL> set pagesize 300 linesize 300
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARCHIVED  STATUS                                           FIRST_CHANGE# FIRST_TIME      NEXT_CHANGE# NEXT_TIME           CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- --------------- ------------ --------------- ----------
         1          1        400   52428800        512          1 NO        CURRENT                                               23765775 02-APR-23         1.8447E+19                   0
         2          1        398   52428800        512          1 YES       INACTIVE                                              23739870 02-APR-23           23753718 02-APR-23         0
         3          1        399   52428800        512          1 YES       INACTIVE                                              23753718 02-APR-23           23765775 02-APR-23         0

SQL> alter system switch logfile;

System altered.

SQL> select name from v$archived_log;

NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_172_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_173_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_174_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_175_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_176_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_177_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_178_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_179_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_180_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_181_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_182_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_183_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_184_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_185_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_186_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_187_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_188_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_189_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_190_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_191_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_192_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_193_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_194_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_195_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_196_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_197_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_198_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_199_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_200_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_201_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_202_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_203_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_204_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_205_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_206_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_207_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_208_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_209_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_210_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_211_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_212_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_213_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_214_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_215_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_216_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_217_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_218_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_219_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_220_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_221_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_222_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_223_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_224_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_225_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_226_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_227_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_228_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_229_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_230_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_231_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_232_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_233_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_234_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_235_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_236_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_237_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_238_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_239_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_240_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_241_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_242_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_243_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_244_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_245_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_246_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_247_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_248_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_249_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_250_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_251_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_252_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_253_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_254_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_255_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_256_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_257_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_258_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_259_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_260_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_261_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_262_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_263_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_264_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_265_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_266_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_267_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_268_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_269_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_270_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_271_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_272_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_273_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_274_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_275_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_276_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_277_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_278_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_279_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_280_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_281_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_282_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_283_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_284_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_285_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_286_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_287_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_288_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_289_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_290_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_291_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_292_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_293_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_294_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_295_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_296_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_297_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_298_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_299_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_300_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_301_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_302_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_303_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_304_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_305_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_306_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_307_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_308_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_309_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_310_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_311_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_312_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_313_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_314_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_315_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_316_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_317_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_318_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_319_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_320_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_321_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_322_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_323_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_324_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_325_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_326_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_327_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_328_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_329_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_330_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_331_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_332_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_333_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_334_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_335_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_336_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_337_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_338_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_339_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_340_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_341_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_342_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_343_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_344_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_345_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_346_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_347_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_348_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_349_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_350_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_351_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_352_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_353_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_354_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_355_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_356_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_357_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_358_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_359_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_360_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_361_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_362_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_363_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_364_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_365_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_366_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_367_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_368_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_369_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_370_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_371_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_372_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_373_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_374_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_375_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_376_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_377_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_378_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_379_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_380_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_381_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_382_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_383_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_384_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_385_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_386_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_387_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_388_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_389_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_390_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_391_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_392_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_393_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_394_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_395_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_396_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_397_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_398_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_399_1119711914.dbf
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_400_1119711914.dbf

229 rows selected.

SQL>

2.6.5 添加要分析的日志

SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_400_1119711914.dbf',options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> show user;
USER is "SYS"
SQL>

2.6.6 执行logmnr 分析

SQL> show user;
USER is "SYS"
SQL> 
SQL> 
SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
BEGIN dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog); END;

*
ERROR at line 1:
ORA-16331: container "PDB3" is not open
ORA-06512: at "SYS.DBMS_LOGMNR", line 72
ORA-06512: at line 1


SQL>

SQL> SELECT name, open_mode FROM v$pdbs;

NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OPEN_MODE
------------------------------
PDB$SEED
READ ONLY

PDB1
READ WRITE

PDB2
READ WRITE

PDB3
MOUNTED


SQL>
SQL> ALTER PLUGGABLE DATABASE PDB3 OPEN;

Pluggable database altered.

SQL> SELECT name, open_mode FROM v$database;

NAME                        OPEN_MODE
--------------------------- ------------------------------------------------------------
CDB1                        READ WRITE

SQL> SELECT name, open_mode FROM v$pdbs;

NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OPEN_MODE
------------------------------
PDB$SEED
READ ONLY

PDB1
READ WRITE

PDB2
READ WRITE

PDB3
READ WRITE


SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

SQL>

2.6.7 查询分析结果

SQL> 
SQL> col SQL_REDO format a40
SQL> select scn, to_char(timestamp, 'YYYY-mm-dd hh24:mi:ss') timestamp, sql_redo from v$logmnr_contents where seg_name='A1';

       SCN TIMESTAMP                                                 SQL_REDO
---------- --------------------------------------------------------- ----------------------------------------
  23777473 2023-04-02 15:42:55                                       ALTER TABLE "SCOTT"."A1" RENAME TO "BIN$
                                                                     +FaXLr2b1djgU4oIqMAjrA==$0" ;

  23777477 2023-04-02 15:42:55                                       drop table a1 AS "BIN$+FaXLr2b1djgU4oIqM
                                                                     AjrA==$0" ;

  23777577 2023-04-02 15:43:32                                       create table a1 (id int);
  23777614 2023-04-02 15:43:43                                       insert into "SCOTT"."A1"("ID") values ('
                                                                     1');

  23777642 2023-04-02 15:43:57                                       update "SCOTT"."A1" set "ID" = '5' where
                                                                      "ID" = '1' and ROWID = 'AAASsOAAMAAAM8O
                                                                     AAA';

  23777658 2023-04-02 15:44:04                                       delete from "SCOTT"."A1" where "ID" = '5
                                                                     ' and ROWID = 'AAASsOAAMAAAM8OAAA';


6 rows selected.

SQL> show user
USER is "SYS"
SQL>

2.6.8 关闭日志分析

SQL> show user
USER is "SYS"
SQL> 
SQL> execute dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

SQL>

3 对DDL进行日志挖掘

Oracle的日志文件中,对于表等用户对象(Object),并不是保存名字,而是保存一个ID号.建立字典文件的目的就是使LogMiner在分析时可以将Object、ID翻译成我们熟悉的对象名

3.1 建立logmnr目录

如果是第一次做,先要建好logmnr目录,如下查询value值为空

SQL> 
SQL> show user;
USER is "SYS"
SQL> show parameter utl

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
create_stored_outlines               string
SQL> 
[oracle@oracle-db-19c ~]$ pwd
/home/oracle
[oracle@oracle-db-19c ~]$ mkdir /home/oracle/logmnr

3.2 指定utl_file_dir路径

设置logmnr 参数,存放数据字典文件dict.ora

SQL> alter system set utl_file_dir='/home/oracle/logmnr' scope=spfile;
SQL> startup force
SQL> show parameter utl
NAME					TYPE		VALUE
---------------------	--------	---------------------------------
create_stored_outlines	string
utl_file_dir			string		/home/oracle/logmnr

3.3 建立数据字典文件dict.ora

SQL> execute dbms_logmnr_d.build('dict.ora','/home/oracle/logmnr',dbms_logmnr_d.store_in_flat_file);
PL/SQL procedure successfully completed.

3.4 添加日志

SQL> execute dbms_logmnr.add_logfile(logfilename=>'日志文件',options=>dbms_logmnr.new);
SQL> execute dbms_logmnr.add_logfile(logfilename=>'追加日志',options=>dbms_logmnr.addfile);

3.5 执行分析

SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/dict.ora',options=>dbms_logmnr.ddl_dict_tracking);

3.6 查看分析结果

SQL> select username, scn, to_char(timestamp, 'yyyy-mm-dd hh24:mi:ss'),sql_redo from v$logmnr_contents WHERE USERNAME ='SCOTT' and lower(sql_redo) like '%table%';

3.7 关闭日志分析

SQL> execute dbms_logmnr.end_logmnr;

http://www.kler.cn/a/6611.html

相关文章：

  • 2023年下半年软考信息安全工程师案例分析及答案解析
  • 使用CNN模型训练图片识别（键盘，椅子，眼镜，水杯，鼠标）
  • List深拷贝后,数据还是被串改
  • 网站灰度发布？Tomcat的8005、8009、8080三个端口的作用什么是CDNLVS、Nginx和Haproxy的优缺点服务器无法开机时
  • python数据分析：介绍pandas库的数据类型Series和DataFrame
  • 设计模式--单例模式【创建型模式】
  • 图像镶嵌拼接
  • ToBeWritten之MIPS汇编基础铺垫
  • aspnet030高校学生团体管理系统sqlserver
  • 夜天之书 #80 推特开源算法与开放革命
  • RocketMQ消息ACK机制及消费进度管理
  • Linux——控制启动过程（更改root密码）
  • springcloud整合knike4j聚合微服务接口文档
  • 蓝桥杯 路径
  • 2.11 循环赛日程表
  • 编译与链接相关知识
  • 推荐一款强大的OCR工具
  • Golang电脑上怎么下载-Go安装和环境配置图文教程[超详细]
  • 联想服务器上安装 ffmpeg
  • 照片太大怎么压缩变小一点，分享5个简单方法
  • 组合预测 | Python实现LSTM-XGBoost长短期记忆网络组合极限梯度提升树股票价格预测
  • OJ系统刷题 第一篇
  • leetcode 152 乘积最大子数组
  • 无证驾驶叉车而导致人员死亡的事故！【安装叉车指纹锁、司机权限采集器的重要性】
  • 基于Java+SSM+jsp的教学质量评价系统设计与实现【源码(完整源码请私聊)+论文+演示视频+包运行成功】
  • ToBeWritten之理解嵌入式Web HTTP协议