测试oracle11G使用logmnr分析日志

事前准备：

1、设置数据库强制归档：
验证是否开启focelogging
SQL>select force_logging from v$database;

若没有启用，可使用下面语法开启强制归档：

SQL>alter database force logging; #开启force logging

2、开启最小附加日志：最小附加日志（Minimal supplemental logging）：是开启logmnr的最低日志要求。

验证是否开启最小附加日志：
SQL> SELECT supplemental_log_data_min FROM v$database;

若没有启用，可使用下面语法开启最小附加日志：
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

3、设置日期格式，，如果不设置的话，最后显示的结果只有日月年，没有具体的时间点：
SQL> ALTER SESSION SET NLS_DATE_FORMAT = \’dd-mon-yyyy hh24:mi:ss\’;

测试开始：

1、创建测试表test，并进行insert、update、delete操作：

SQL> create table test (id number, name varchar(10) );

Table created.

SQL> insert into test values (1,\’JACK\’);

1 row created.

SQL> insert into test values (2,\’LISA\’);

1 row created.

SQL> select * from test;

ID NAME
———- ——————–
1 JACK
2 LISA

SQL> update test set name =\’ELLA\’ where id=2;

1 row updated.

SQL> select * from test;

ID NAME
———- ——————–
1 JACK
2 ELLA

SQL> delete from test where id=1;

1 row deleted.

SQL> select * from test;

ID NAME
———- ——————–
2 ELLA

2、完成insert、update、delete操作后切换日志或者手工进行归档：

SQL> alter system archive log current；

System altered.

3、进入归档日志文件所在路径：

[oracle@mas 2018_11_06]$ ll -h
total 9.6M
-rw-r—– 1 oracle dba 5.4M Nov 6 09:00 o1_mf_1_33_fy1sww22_.arc
-rw-r—– 1 oracle dba 1.8M Nov 6 09:22 o1_mf_1_34_fy1v6oyj_.arc
-rw-r—– 1 oracle dba 2.5M Nov 6 10:07 o1_mf_1_35_fy1xtp1l_.arc

4、添加需要进行解析的日志文件：

SQL> execute dbms_logmnr.add_logfile(LOGFILENAME =>\’/data/oracle/flash_recovery_area/MCJ/archivelog/2018_11_06/o1_mf_1_33_fy1sww22_.arc\’, OPTIONS => dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile(\’/data/oracle/flash_recovery_area/MCJ/archivelog/2018_11_06/o1_mf_1_34_fy1v6oyj_.arc\’,dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile(\’/data/oracle/flash_recovery_area/MCJ/archivelog/2018_11_06/o1_mf_1_35_fy1xtp1l_.arc\’,dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

5、开始分析：

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

PL/SQL procedure successfully completed.

6、设置显示格式：

SQL> set lines 32565
SQL> set pages 50000
SQL> col sql_redo format a9000

7、用spool命令将oracle所有的操作结果写入到指定的文件中：

SQL> spool /data/oracle/flash_recovery_area/MCJ/archivelog/2018_11_06/test_20181106.txt
SQL> select TIMESTAMP,USERNAME,OS_USERNAME,MACHINE_NAME,OPERATION,sql_redo from v$logmnr_contents where table_name=\’TEST\’ and sql_redo<>\’Unsupported\’ order by timestamp;

06-nov-2018 09:55:48 SYS oracle mas DDL drop table test;
06-nov-2018 09:56:39 SYS oracle mas DDL create table test (id number, name varchar(10) );
06-nov-2018 09:57:10 SYS oracle mas INSERT insert into \”SYS\”.\”TEST\”(\”ID\”,\”NAME\”) values (\’1\’,\’JACK\’);
06-nov-2018 09:58:04 SYS oracle mas INSERT insert into \”SYS\”.\”TEST\”(\”ID\”,\”NAME\”) values (\’2\’,\’LISA\’);
06-nov-2018 10:02:25 SYS oracle mas UPDATE update \”SYS\”.\”TEST\” set \”NAME\” = \’ELLA\’ where \”NAME\” = \’LISA\’ and ROWID = \’AAAR8qAABAAAUf5AAB\’;
06-nov-2018 10:02:58 SYS oracle mas DELETE delete from \”SYS\”.\”TEST\” where \”ID\” = \’1\’ and \”NAME\” = \’JACK\’ and ROWID = \’AAAR8qAABAAAUf5AAA\’;

6 rows selected.

SQL> spool off

8、结束分析：

SQL> exec dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

9、找到spool命令生成的文件：

[oracle@mas 2018_11_06]$ ll -h test_20181106.txt
-rw-r–r– 1 oracle dba 252K Nov 6 10:22 test_20181106.txt

10、此时这个文件里面的格式较乱，需要进行格式调整：

[oracle@mas 2018_11_06]$ sed -i \’s/[ ][ ]*/ /g\’ test_20181106.txt

11、最后查看结果：（结果列分别对应TIMESTAMP,USERNAME,OS_USERNAME,MACHINE_NAME,OPERATION,sql_redo）

SQL> select TIMESTAMP,USERNAME,OS_USERNAME,MACHINE_NAME,OPERATION,sql_redo from v$logmnr_contents where table_name=\’TEST\’ and sql_redo<>\’Unsupported\’ order by timestamp;

TIMESTAMP USERNAME OS_USERNAME MACHINE_NAME OPERATION SQL_REDO
06-nov-2018 09:55:48 SYS oracle mas DDL drop table test;
06-nov-2018 09:56:39 SYS oracle mas DDL create table test (id number, name varchar(10) );
06-nov-2018 09:57:10 SYS oracle mas INSERT insert into \”SYS\”.\”TEST\”(\”ID\”,\”NAME\”) values (\’1\’,\’JACK\’);
06-nov-2018 09:58:04 SYS oracle mas INSERT insert into \”SYS\”.\”TEST\”(\”ID\”,\”NAME\”) values (\’2\’,\’LISA\’);
06-nov-2018 10:02:25 SYS oracle mas UPDATE update \”SYS\”.\”TEST\” set \”NAME\” = \’ELLA\’ where \”NAME\” = \’LISA\’ and ROWID = \’AAAR8qAABAAAUf5AAB\’;
06-nov-2018 10:02:58 SYS oracle mas DELETE delete from \”SYS\”.\”TEST\” where \”ID\” = \’1\’ and \”NAME\” = \’JACK\’ and ROWID = \’AAAR8qAABAAAUf5AAA\’;

注：第7步在v$logmnr_contents查询操作情况时，可根据实际需求添加其他字段，例如操作时间、操作用户、操作类型等等。

12、附上v$logmnr_contents字段：

>SQL desc v$logmnr_contents；