oracle9i logMiner的使用

在學習RMAN恢復時用到logmnr,做個筆記.[@more@]

-- 基於更改的恢復

--查詢日誌歷史
col nc for a16
col fc for a16
select recid,stamp,sequence# sq#,to_char(first_change#) fc,
to_char(next_change#) nc from v$log_history
/
RECID STAMP SQ# FC NC
----- ---------- ---------- ---------------- ---------------
1 653309452 1 8495792983529 8495792983763
2 653309454 2 8495792983763 8495792983766
3 653309654 3 8495792983766 8495792983850
4 653309683 4 8495792983850 8495792983860
5 653309692 5 8495792983860 8495792983866
6 653309735 6 8495792983866 8495792983882
7 653309751 7 8495792983882 8495792983889
8 653309772 8 8495792983889 8495792983899
9 653310435 9 8495792983899 8495792984192
10 653310436 10 8495792984192 8495792984195

select group#,sequence#,archived,status,to_char(first_change#)
from v$log;

GROUP# SEQUENCE# ARC STATUS TO_CHAR(FIRST_CHANGE#)
------ ---------- --- ---------------- ------------------------
1 11 NO CURRENT 8495792984195
2 10 YES INACTIVE 8495792984192
3 9 YES INACTIVE 8495792983899

---- logmnr的使用方法
1 SHUTDOWN
2 create pfile from spfile;
3 edit the pfile to add
*.utl_file_dir='c:oracleoradatatestdb'
4 startup
5 exec dbms_logmnr_d.build('logdict.ora','c:oracleoradatatestdb');
6 exec dbms_logmnr.add_logfile(logfilename=> 'c:oracleoradatatestdbredo01.log',-
options=> dbms_logmnr.new);
exec dbms_logmnr.add_logfile(logfilename=>'c:oracleoradatatestdbredo02.log',-
options=> dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>'c:oracleoradatatestdbredo03.log',-
options=> dbms_logmnr.addfile);

7 exec dbms_logmnr.start_logmnr(dictfilename=> 'c:oracleoradatatestdblogdict.ora', -
startscn=> 8495793082990, endscn=> 8495793083304);
----scn透過查詢v$log_history得到

8 select * from v$logmnr_contents where seg_name='...';

9 查詢當前SCN號.
select to_char(dbms_flashback.get_system_change_number) from dual;

8495792990416

exec dbms_logmnr.start_logmnr(dictfilename=> 'c:oracleoradatatestdblogdict.ora', -
startscn=> 8495792983899, endscn=> 8495792990416);

select distinct seg_name from v$logmnr_contents;

8495792990600

exec dbms_logmnr.start_logmnr(dictfilename=> 'c:oracleoradatatestdblogdict.ora', -
startscn=> 8495792990416, endscn=> 8495792990600);

SQL> select distinct seg_name from v$logmnr_contents;

SEG_NAME
-------------------------------------------------------

EMP
SMON_SCN_TIME

----看看SQL REDO
SQL> select scn,sql_redo from v$logmnr_contents where seg_name='EMP'

8495792990581
delete from "SCOTT"."EMP" where "EMP_ID" = '1810' and "EMPNAME" =
TO_DATE('14-OCT-07', 'DD-MON-RR') and ROWID = 'AAACEJAAGAAACM0AAC'

8495792990581
delete from "SCOTT"."EMP" where "EMP_ID" = '1767' and "EMPNAME" =

O_DATE('14-OCT-07', 'DD-MON-RR') and ROWID = 'AAACEJAAGAAACM0AAK';

8495792990581
delete from "SCOTT"."EMP" where "EMP_ID" = '2083' and "EMPNAME" =
= TO_DATE('14-OCT-07', 'DD-MON-RR') and ROWID = 'AAACEJAAGAAACM0AA

8495792990581
delete from "SCOTT"."EMP" where "EMP_ID" = '1667' and "EMPNAME" =

DATE('14-OCT-07', 'DD-MON-RR') and ROWID = 'AAACEJAAGAAACM0AAS';

1942 rows selected.

-- 8495792990581 這個SCN是下面這個刪除產生的.
SQL> delete from emp;
1942 rows deleted.

SQL> commit;
Commit complete.

SQL> select count(1) from emp;
COUNT(1)
----------
0

----結束logmnr會話
exec dbms_logmnr.end_logmnr;

-- 如果需要再次分析,重複上面的操作,除了第3條utl_file_dir的設定不需重複做之外.

-- 如何恢復呢?
RMAN> restore database;
RMAN> run {
2> set until scn=8495792990581;
3> recover database;
4> }
...
RMAN> alter database open resetlogs;

database opened

SQL> select count(1) from emp;

COUNT(1)
----------
1942
-- 這1942條又回來了.

重點還在於透過查詢V$LOG_HISTORY確定合適的SCN，然後start_logmnr.