1. LogMiner的作用
LogMiner是Oracle数据库中的一个工具，它可以用于分析数据库的重做日志文件，以了解数据库的操作历史和数据变化情况。LogMiner可以将重做日志文件中的SQL语句提取出来，并将其转换成易于理解的格式，以便用户进行分析和查询。
LogMiner的主要作用包括：
数据恢复：LogMiner可以用于恢复误删除或误修改的数据，通过分析重做日志文件中的SQL语句，可以找到被删除或修改的数据，并进行恢复操作。
数据审计：LogMiner可以用于审计数据库的操作，通过分析重做日志文件中的SQL语句，可以了解数据库的操作情况和操作者。
数据分析：LogMiner可以用于分析数据库的历史数据，通过分析重做日志文件中的SQL语句，可以了解数据库的操作历史和数据变化情况。
数据备份：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 sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 2 15:40:49 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle. All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0SQL> show con_name;CON_NAME------------------------------CDB$ROOTSQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;Database altered.SQL> 2.6.2 DML操作表
SQL> ---scottSQL> conn scott/tiger@PDB1;Connected.SQL> show user;USER is "SCOTT"SQL> show con_name;CON_NAME------------------------------PDB1SQL> set pagesize 200 linesize 200SQL>SQL> create table a1 (id int);create table a1 (id int) *ERROR at line 1:ORA-00955: name is already used by an existing objectSQL> drop table a1 cascade;drop table a1 cascade *ERROR at line 1:ORA-00905: missing keywordSQL> 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 200SQL> 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 0SQL> -- a1表的DML操作都写进了current组里,记住sequence#是400号.然后手工切换当前日志进archive里.2.6.4 手工切换当前日志进archive
[oracle@oracle-db-19c ~]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 2 15:48:00 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle. All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0SQL> show user;USER is "SYS"SQL> set pagesize 300 linesize 300SQL> 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 0SQL> 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.dbf229 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 openORA-06512: at "SYS.DBMS_LOGMNR", line 72ORA-06512: at line 1SQL>SQL> SELECT name, open_mode FROM v$pdbs;NAME------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------OPEN_MODE------------------------------PDB$SEEDREAD ONLYPDB1READ WRITEPDB2READ WRITEPDB3MOUNTEDSQL>SQL> ALTER PLUGGABLE DATABASE PDB3 OPEN;Pluggable database altered.SQL> SELECT name, open_mode FROM v$database;NAME OPEN_MODE--------------------------- ------------------------------------------------------------CDB1 READ WRITESQL> SELECT name, open_mode FROM v$pdbs;NAME------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------OPEN_MODE------------------------------PDB$SEEDREAD ONLYPDB1READ WRITEPDB2READ WRITEPDB3READ WRITESQL> 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 a40SQL> 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 userUSER is "SYS"SQL> 2.6.8 关闭日志分析
SQL> show userUSER 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 utlNAME TYPE VALUE------------------------------------ --------------------------------- ------------------------------create_stored_outlines stringSQL> [oracle@oracle-db-19c ~]$ pwd/home/oracle[oracle@oracle-db-19c ~]$ mkdir /home/oracle/logmnr3.2 指定utl_file_dir路径
设置logmnr 参数,存放数据字典文件dict.ora
SQL> alter system set utl_file_dir='/home/oracle/logmnr' scope=spfile;SQL> startup forceSQL> show parameter utlNAMETYPEVALUE--------------------------------------------------------------create_stored_outlinesstringutl_file_dirstring/home/oracle/logmnr3.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;