一.确认数据库是否开启归档模式
1.通过sqlplus连接oracle(orcl替换为你的oracle实例)
export ORACLE_SID=orcl
sqlplus / as sysdba
2.查看oralce是否开启归档模式
archive log list
输出以下信息为未开启归档
Database log mode No Archive Mode
Automatic archival Disabled
输出以下信息为已开启归档(跳过下面步骤,直接进入挖掘归档日志)
Database log mode Archive Mode
Automatic archival Enabled
3.关闭数据库
shutdown immediate;
4.启动实例并加载数据库,但不打开
startup mount
5.更改数据库为归档模式
alter database archivelog;
6.启动自动归档
alter system archive log start;
二.挖掘归档日志
1.通过sqlplus连接oracle
export ORACLE_SID=orcl
sqlplus / as sysdba
2.创建字典路径,并重启oracle使其生效
!mkdir -p /home/oracle/logmnr
alter system set utl_file_dir='/home/oracle/logmnr' scope=spfile;
startup force
3.查看/开启补充日志,NO为关闭,YES开启(很重要,不然之后的执行日志看不到)
select supplemental_log_data_pk,supplemental_log_data_ui from v$database;
alter database add supplemental log data (primary key, unique index) columns;
4.创建oracle用户
create user steven identified by oracle;
grant dba to steven;
conn steven/oracle;
create table test (id number,serial# number);
5.执行sql
select CURRENT_SCN from v$database;
insert into test values(1,2);
insert into test values(3,4);
update test set id=100 where id=1;
delete from test where id=3;
select * from test;
commit;
6.将redo里记录写到datafile里,切换归档(创建一个新的归档)
alter system checkpoint;
alter system switch logfile;
7.创建字典(用来挖掘redo,归档日志)
exec dbms_logmnr_d.build('dictionary.ora','/home/oracle/logmnr');
8.查询归档日志文件地址
select name from (SELECT ((next_time -TO_DATE('19700101','yyyymmdd'))*86400 - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600) as AUDIT_TIME, name FROM V$ARCHIVED_LOG ) ;
9.查询redo日志文件地址
select member from v$logfile;
10.添加解析的归档日志(也可以添加redo日志,我这里解析归档日志,所以只添加第8步中的归档日志结果)
exec dbms_logmnr.add_logfile(LogFileName=>'/home/suninfo/admdata/vdb20210302111543/datafile/archivelog/1_12_1065373504.dbf',Options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(LogFileName=>'/home/suninfo/admdata/vdb20210302111543/datafile/archivelog/1_13_1065373504.dbf',Options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(LogFileName=>'/home/suninfo/admdata/vdb20210302111543/datafile/archivelog/1_14_1065373504.dbf',Options=>dbms_logmnr.addfile);
11.使用字典对归档日志挖掘
exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/dictionary.ora');
12.查询挖掘日志
select OPERATION,SQL_REDO,SQL_UNDO from v$logmnr_contents
13.V$LOGMNR_CONTENTS视图包含日志历史记录信息。
可以根据自己需要加where条件查询日志记录。