建站
咨詢
售后
建站咨詢
新聞中心
[oracle@db12c ~]$ mkdir utl_file_dir
創(chuàng)新互聯(lián)建站是一家專注于網(wǎng)站建設(shè)、做網(wǎng)站與策劃設(shè)計(jì),白城網(wǎng)站建設(shè)哪家好?創(chuàng)新互聯(lián)建站做網(wǎng)站,專注于網(wǎng)站建設(shè)十年,網(wǎng)設(shè)計(jì)領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:白城等地區(qū)。白城做網(wǎng)站價(jià)格咨詢: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; --打開補(bǔ)充日志
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'); --創(chuàng)建數(shù)據(jù)字典
PL/SQL procedure successfully completed.
模擬數(shù)據(jù)恢復(fù)：（首先把scott用戶表T給truncate了，后續(xù)在hr用戶做了很多操作，需要把這些數(shù)據(jù)都恢復(fù)過來）
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
當(dāng)前日志序列號是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;只在當(dāng)前會話有效，
USERNAME
---------------
HR
SCOTT
UNKNOWN
SYS
SYS@ORA12C> create table logmnr as select * from v$logmnr_contents; --創(chuàng)建正式表方便查詢。
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表恢復(fù)，然后通過sql_redo把HR用戶的數(shù)據(jù)恢復(fù)：
新聞標(biāo)題：Logminer的使用
標(biāo)題URL：http://fisionsoft.com.cn/article/pdcpsd.html
