Logminer的使用

[oracle@db12c ~]$ mkdir utl_file_dir

[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用户的数据恢复：