oracle oc2,测试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；