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用戶的數據恢復：