首先开启归档:
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.
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.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29135257/viewspace-2123337/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29135257/viewspace-2123337/