Logminer的使用
[oracle@db12c ~]$ mkdir utl_file_dir
创新互联建站是一家专注于网站建设、做网站与策划设计,白城网站建设哪家好?创新互联建站做网站,专注于网站建设十年,网设计领域的专业建站公司;建站业务涵盖:白城等地区。白城做网站价格咨询:028-86922220
[oracle@db12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sun Sep 20 15:55:20 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SYS@ORA12C> alter system set utl_file_dir='/home/oracle/utl_file_dir' scope=spfile;
System altered.
SYS@ORA12C> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ORA12C> startup
ORACLE instance started.
Total System Global Area 729808896 bytes
Fixed Size 2928680 bytes
Variable Size 553652184 bytes
Database Buffers 167772160 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SYS@ORA12C> @?/rdbms/admin/dbmslms.sql;
Session altered.
Package created.
No errors.
Grant succeeded.
Session altered.
_user'@'_connect_identifier> @?/rdbms/admin/dbmslm.sql;
Session altered.
Package created.
Grant succeeded.
Synonym created.
Session altered.
_user'@'_connect_identifier> @?/rdbms/admin/dbmslmd.sql;
Session altered.
Package created.
Synonym created.
Session altered.
SYS@ORA12C> alter database add supplemental log data; --打开补充日志
Database altered.
SYS@ORA12C> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
YES
SYS@ORA12C> exec dbms_logmnr_d.build(dictionary_filename=>'LGM_v1201dict.ora',dictionary_location=>'/home/oracle/utl_file_dir'); --创建数据字典
PL/SQL procedure successfully completed.
模拟数据恢复：（首先把scott用户表T给truncate了，后续在hr用户做了很多操作，需要把这些数据都恢复过来）
COTT@ORA12C> create table t as select * from dba_objects;
Table created.
SCOTT@ORA12C> select sysdate from dual;
SYSDATE
-------------------
2015-09-20 16:32:58
1 row selected.
SCOTT@ORA12C> conn hr/hr;
Connected.
HR@ORA12C> create table hr(id int,name varchar2(100);
create table hr(id int,name varchar2(100)
HR@ORA12C> create table hr(id int,name varchar2(100));
Table created.
HR@ORA12C> insert into hr values(1,'1gsge');
1 row created.
HR@ORA12C> insert into hr values(2,'1gsdfdge');
1 row created.
HR@ORA12C> insert into hr values(3,'1gsdfddfdge');
1 row created.
HR@ORA12C> insert into hr values(5,'1gsdfddfdgdfde');
1 row created.
HR@ORA12C> insert into hr values(4,'1gsdfddfdfdfdgdfde');
1 row created.
HR@ORA12C> commit;
Commit complete.
HR@ORA12C> select * from hr;
ID NAME
----------------- ------------------------------
1 1gsge
2 1gsdfdge
3 1gsdfddfdge
5 1gsdfddfdgdfde
4 1gsdfddfdfdfdgdfde
5 rows selected.
HR@ORA12C> conn / as sysdba
Connected.
SYS@ORA12C> conn scott/scott;
Connected.
SCOTT@ORA12C> truncate table t;
Table truncated.
SCOTT@ORA12C> conn / as sysdba
Connected.
SYS@ORA12C> alter system switch logfile;
System altered.
SYS@ORA12C> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
当前日志序列号是5，那么我以前操作的就是4，
添加日志
SYS@ORA12C> exec dbms_logmnr.add_logfile(logfilename=>'/tmp/o1_mf_1_4_bzww1nfk_.arc',options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
开始挖掘：
SYS@ORA12C> exec dbms_logmnr.start_logmnr(dictfilename=>'/tmp/LGM_v1201dict.ora',options=>dbms_logmnr.ddl_dict_tracking);
PL/SQL procedure successfully completed.
SYS@ORA12C> select distinct username from v$logmnr_contents;只在当前会话有效，
USERNAME
---------------
HR
SCOTT
UNKNOWN
SYS
SYS@ORA12C> create table logmnr as select * from v$logmnr_contents; --创建正式表方便查询。
Table created.
create index logmnr_idx on logmnr(username,table_name,operation);--建索引
SELECT username, sql_redo, TIMESTAMP
FROM logmnr a
WHERE a.timestamp >
to_timestamp('2015-09-20 16:32:58', 'yyyy-mm-dd hh34:mi:ss')
AND a.username NOT IN ('SYS', 'SYSMAN')
这样就可用闪回或者rman把scott用户T表恢复，然后通过sql_redo把HR用户的数据恢复：
新闻名称：Logminer的使用
文章出自：http://ybzwz.com/article/pdcpsd.html
