Oracle 12c logminer測試

首先開啟歸檔：
SQL> archive log list   
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/archive
Oldest online log sequence     35
Next log sequence to archive   37
Current log sequence           37


檢查資料庫是否suppplemental logging
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEME
-------
NO
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEME
--------
YES
逆向操作為：
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;


方法一：
建立測試表，在每次commit後執行切換一次日誌：
SQL> create table t5 (name varchar2(10));
Table created.
SQL> insert into t5 values('abc');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t5 values('ycr');
1 row created.
SQL> commit;
Commit complete.
SQL> delete from t5 where name='abc';
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from t5;
NAME
----------
ycr


使用sysdba執行,注意此執行過程只能在一個session中執行：
execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/archive/1_37_915332259.dbf',options=>dbms_logmnr.new);
execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/archive/1_38_915332259.dbf',options=>dbms_logmnr.addfile); 
execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/archive/1_39_915332259.dbf',options=>dbms_logmnr.addfile); 
如需移除日誌使用
execute dbms_logmnr.remove_logfile(logfilename=>'日誌檔案');
檢視要分析的日誌：
select * from v$logmnr_logs; 
啟動logminer
execute dbms_logmnr.start_logmnr(Options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)
啟動分析時還可以指定時間或者scn
execute dbms_logmnr.start_logmnr(Options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG,starttime => to_date(’02-Apr-2010 09:30:00’,’DD-MON-YYYY HH:MI:SS’),endtime => to_date(’02-Apr-2010 19:30:00’,’DD-MON-YYYY HH:MI:SS’)); 
execute dbms_logmnr.start_logmnr(Options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG ,startscn => 3231808,endscn => 3231813);
檢視分析結果
select username,scn,timestamp,sql_redo,sql_undo from v$logmnr_contents t where t.USERNAME='TEST';
關閉logminer
execute dbms_logmnr.end_logmnr;


方法二：
SQL>  show parameter utl;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines               string
utl_file_dir                         string


alter system set utl_file_dir='/u01/app/oracle/utl' scope=spfile;
重啟資料庫並啟動所有pdb
alter pluggable database pdb2,pdbycr open;
建立字典檔案：
execute dbms_logmnr_d.build('dic.ora','/u01/app/oracle/utl',options => dbms_logmnr_d.store_in_flat_file);
同方法一相同新增要分析的日誌後，開始分析,剩餘步驟均與方法一相同，其中開始分析的方法略不同：
exec dbms_logmnr.start_logmnr(dictfilename =>'/u01/app/oracle/utl/dic.ora');
使用此種方法無法查詢出普通使用者，比如TEST中的相關資料，只能查詢出sys中資料




同一種方法還可以分析線上日誌：
建立測試環境：
SQL> create table t6 (name varchar2(10));
Table created.
SQL> insert into t6 values ('abce');
1 row created.
SQL> insert into t6 values ('ycr1');
1 row created.
SQL> insert into t6 values ('ycr2');
1 row created.
SQL> commit;
Commit complete.
SQL> 
SQL> delete from t6 where name='ycr2';
1 row deleted.
SQL> select * from t6;
NAME
----------
abce
ycr1
SQL> commit;
Commit complete.
檢視當前日誌：
col group# for 99
col status for a20
col member for a50
select l.group#,l.status,f.member from v$log l,v$logfile f where l.GROUP#=f.GROUP#;
新增檔案
execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/ycr/redo01.log',options=>dbms_logmnr.new);
execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/ycr/redo02.log',options=>dbms_logmnr.addfile); 
開始分析
execute dbms_logmnr.start_logmnr(Options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)
檢視分析結果
select username,scn,timestamp,sql_redo,sql_undo from v$logmnr_contents t where t.USERNAME='TEST'
關閉logminer
execute dbms_logmnr.end_logmnr;


以上均已再12.1.0.2版本測試透過


其中使用的選項選項解釋如下：
DICT_FROM_ONLINE_CATALOG：
Directs LogMiner to use the current online database dictionary rather than a LogMiner dictionary contained in a flat file or in the redo log files being analyzed.
This option cannot be used in conjunction with the DDL_DICT_TRACKING option. The database to which LogMiner is connected must be the same one that generated the redo log files.
Expect to see a value of 2 in the STATUS column of the V$LOGMNR_CONTENTS view if the table definition in the database does not match the table definition in the redo log file.


DictFileName：
Specifies the flat file that contains the LogMiner dictionary. It is used to reconstruct SQL_REDO and SQL_UNDO columns in V$LOGMNR_CONTENTS, as well as to fully translate SEG_NAME, SEG_OWNER, SEG_TYPE_NAME, TABLE_NAME, and TABLE_SPACE columns. The fully qualified path name for the LogMiner dictionary file must be specified. (This file must have been created previously through the DBMS_LOGMNR_D.BUILD procedure.)
You need to specify this parameter only if neither DICT_FROM_REDO_LOGS nor DICT_FROM_ONLINE_CATALOG is specified.