分析oracle的日志
脚本安装logminer
@$ORACLE_HOME/rdbms/admin/dbmslm.sql
@$ORACLE_HOME/rdbms/admin/dbmslmd.sql
查看是否开启扩充日志
select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
结果应该为yes 如果为no的话,手动开启,
开启方法 alter database add supplemental log data;
创建日志分析用户并授权
创建CREATE USER logminer IDENTIFIED BY logminer;
授权GRANT CONNECT, RESOURCE,DBA TO logminer;
建立logminer的使用目录
cd /opt/oracle/oradata/
mkdir logminer
创建logminer字典文件路径
CREATE DIRECTORY utlfile AS '/opt/oracle/oradata/logminer';
alter system set utl_file_dir='/opt/app/oracle/oradata/logminer' scope=spfile;
(第二步可能会出现ORA-32001: write to SPFILE requested but no SPFILE is in use的错误 重新创建一下spfile 命令create spfile from pfile;重启数据库,即可解决此错误 ,使用show parameter utl_file_dir可以查看字典路径是否设置完成)
开始分析
分析在线日志文件
准备测试数据
conn logminer/logminer
create table student(username varchar2(20),password varchar2(20));
insert into student(username,password) values('qqq','111');
insert into student(username,password) values('www','222');
commit;
创建数字字典文件
EXECUTE dbms_logmnr_d.build(dictionary_filename => 'student.ora', dictionary_location =>'/opt/oracle/oradata/logminer');
查看数据库当前使用的联机日志文件
SELECT group#, sequence#, status, first_change#, first_time FROM V$log ORDER BY first_change#;(status为current的日志文件是正在使用的日志文件)
将要解析的日志文件加入
EXEC dbms_logmnr.add_logfile('/opt/oracle/oradata/orcl/redo02.log', dbms_logmnr.new);(redo02.log为正在使用的日志文件)
启用logminer进行分析
EXEC dbms_logmnr.start_logmnr( dictfilename=>'/opt/oracle/oradata/logminer/student.ora');
查看日志分析结果
SQL> col sql_redo for a50
SQL> col sql_undo for a50
SQL> col seg_owner for a10
SQL> set linesize 5000 pagesize 5000
SQL> SELECT sql_redo, sql_undo, seg_owner FROM v$logmnr_contents WHERE seg_name='STUDENT' AND seg_owner='LOGMINER';
分析归档日志文件-(需要去确认数据库的归档日志是否开启 开启命令alter database archivelog;)
准备测试数据
将当前日志归档
alter system switch logfile;
查看归档日志文件
select sequence#, FIRST_CHANGE#, NEXT_CHANGE#,name from v$archived_log order by sequence# desc;
创建数字字典文件
EXECUTE dbms_logmnr_d.build(dictionary_filename => 'student2.ora', dictionary_location =>'/opt/oracle/oradata/logminer');
将归档日志文件加入
exec dbms_logmnr.add_logfile('/opt/oracle/flash_recovery_area/ORCL/archivelog/2018_03_07/o1_mf_1_13_f9zbdcgz_.arc', dbms_logmnr.new);
启用logminer进行分析
exec dbms_logmnr.start_logmnr(dictfilename=>'/opt/oracle/oradata/logminer/student2.ora');
查看分析结果
SELECT sql_redo, sql_undo, seg_owner FROM v$logmnr_contents WHERE seg_name='STUDENT' AND seg_owner='LOGMINER';
脚本安装logminer
@$ORACLE_HOME/rdbms/admin/dbmslm.sql
@$ORACLE_HOME/rdbms/admin/dbmslmd.sql
查看是否开启扩充日志
select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
结果应该为yes 如果为no的话,手动开启,
开启方法 alter database add supplemental log data;
创建日志分析用户并授权
创建CREATE USER logminer IDENTIFIED BY logminer;
授权GRANT CONNECT, RESOURCE,DBA TO logminer;
建立logminer的使用目录
cd /opt/oracle/oradata/
mkdir logminer
创建logminer字典文件路径
CREATE DIRECTORY utlfile AS '/opt/oracle/oradata/logminer';
alter system set utl_file_dir='/opt/app/oracle/oradata/logminer' scope=spfile;
(第二步可能会出现ORA-32001: write to SPFILE requested but no SPFILE is in use的错误 重新创建一下spfile 命令create spfile from pfile;重启数据库,即可解决此错误 ,使用show parameter utl_file_dir可以查看字典路径是否设置完成)
开始分析
分析在线日志文件
准备测试数据
conn logminer/logminer
create table student(username varchar2(20),password varchar2(20));
insert into student(username,password) values('qqq','111');
insert into student(username,password) values('www','222');
commit;
创建数字字典文件
EXECUTE dbms_logmnr_d.build(dictionary_filename => 'student.ora', dictionary_location =>'/opt/oracle/oradata/logminer');
查看数据库当前使用的联机日志文件
SELECT group#, sequence#, status, first_change#, first_time FROM V$log ORDER BY first_change#;(status为current的日志文件是正在使用的日志文件)
将要解析的日志文件加入
EXEC dbms_logmnr.add_logfile('/opt/oracle/oradata/orcl/redo02.log', dbms_logmnr.new);(redo02.log为正在使用的日志文件)
启用logminer进行分析
EXEC dbms_logmnr.start_logmnr( dictfilename=>'/opt/oracle/oradata/logminer/student.ora');
查看日志分析结果
SQL> col sql_redo for a50
SQL> col sql_undo for a50
SQL> col seg_owner for a10
SQL> set linesize 5000 pagesize 5000
SQL> SELECT sql_redo, sql_undo, seg_owner FROM v$logmnr_contents WHERE seg_name='STUDENT' AND seg_owner='LOGMINER';
分析归档日志文件-(需要去确认数据库的归档日志是否开启 开启命令alter database archivelog;)
准备测试数据
将当前日志归档
alter system switch logfile;
查看归档日志文件
select sequence#, FIRST_CHANGE#, NEXT_CHANGE#,name from v$archived_log order by sequence# desc;
创建数字字典文件
EXECUTE dbms_logmnr_d.build(dictionary_filename => 'student2.ora', dictionary_location =>'/opt/oracle/oradata/logminer');
将归档日志文件加入
exec dbms_logmnr.add_logfile('/opt/oracle/flash_recovery_area/ORCL/archivelog/2018_03_07/o1_mf_1_13_f9zbdcgz_.arc', dbms_logmnr.new);
启用logminer进行分析
exec dbms_logmnr.start_logmnr(dictfilename=>'/opt/oracle/oradata/logminer/student2.ora');
查看分析结果
SELECT sql_redo, sql_undo, seg_owner FROM v$logmnr_contents WHERE seg_name='STUDENT' AND seg_owner='LOGMINER';