Oracle 使用logmnr檢視日誌

[@more@]

C:>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 12月 12 10:25:44 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn as sysdba
已連線。
SQL> show parameter utl_file_dir;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string d:oraclelogmnr
SQL> exec dbms_logmnr_d.build('dic.ora','d:oraclelogmnr');
PL/SQL 過程已成功完成。
SQL> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版權所有 1985-2001 Microsoft Corp.
C:>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 12月 12 10:33:05 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL>
SQL> conn as sysdba;
已連線。
SQL> select group#,status,sequence#,first_change#,first_time from v$log;

GROUP# STATUS SEQUENCE# FIRST_CHANGE# FIRST_TIME
---------- ---------------- ---------- ------------- --------------
1 INACTIVE 104 3636473 11-12月-10
2 CURRENT 105 3659554 12-12月-10
3 INACTIVE 103 3632151 11-12月-10

SQL> conn
已連線。
SQL> create table tangyun_test(id number,pname varchar2(10),detail varchar2(200)) tablespace TBS_CDCPUB;
表已建立。
SQL> alter table tangyun_test add constraint pk_tangyun_test primary key(id) using index tablespace TBS_CDCPUB;
表已更改。
SQL> insert into tangyun_test(id,pname,detail) values(1,'ty','tangyun');
已建立 1 行。
SQL> commit;
提交完成。
SQL> insert into tangyun_test(id,pname,detail) values(2,'test','testlogmnr');
已建立 1 行。
SQL> commit;
提交完成。
SQL> insert into tangyun_test(id,pname,detail) values(3,'oracle','oracletest');
已建立 1 行。
SQL> insert into tangyun_test(id,pname,detail) values(4,'hf','recovery');
已建立 1 行。
SQL> commit;
提交完成。
SQL> select * from tangyun_test;

ID PNAME DETAIL
---------- ---------- -----------------------------
1 ty tangyun
2 test testlogmnr
3 oracle oracletest
4 hf recovery
SQL> update tangyun_test set detail='huifu' where id=4;
已更新 1 行。
SQL> commit;
提交完成。
SQL>
SQL> delete from tangyun_test where id=1;
已刪除 1 行。
SQL> commit;
提交完成。
SQL>
SQL> drop table tangyun_test;
表已刪除。
SQL>
SQL> conn as sysdba
已連線。
SQL>
SQL> select group#,status,sequence#,first_change#,first_time from v$log;

GROUP# STATUS SEQUENCE# FIRST_CHANGE# FIRST_TIME
---------- ---------------- ---------- ------------- --------------
1 INACTIVE 104 3636473 11-12月-10
2 CURRENT 105 3659554 12-12月-10
3 INACTIVE 103 3632151 11-12月-10

SQL> alter system switch logfile;

系統已更改。

SQL> select group#,status,sequence#,first_change#,first_time from v$log;

GROUP# STATUS SEQUENCE# FIRST_CHANGE# FIRST_TIME
---------- ---------------- ---------- ------------- --------------
1 INACTIVE 104 3636473 11-12月-10
2 ACTIVE 105 3659554 12-12月-10
3 CURRENT 106 3664247 12-12月-10

SQL> select sequence#, first_change#,first_time from v$log where sequence# in(105,106);

SEQUENCE# FIRST_CHANGE# FIRST_TIME
---------- ------------- --------------
105 3659554 12-12月-10
106 3664247 12-12月-10
SQL> select t.member from v$logfile t,v$log s where t.group#=s.group# and s.sequence# in (105);

MEMBER
--------------------------------------------------------------------------------
D:ORACLEPRODUCT10.2.0ORADATAORCLREDO02.LOG
SQL> show user
USER 為 "SYS"
SQL> exec dbms_logmnr.add_logfile('D:ORACLEPRODUCT10.2.0ORADATAORCLREDO02.LOG',dbms_logmnr.new);
PL/SQL 過程已成功完成。
SQL> select filename from v$logmnr_logs;
FILENAME
--------------------------------------------------------------------------------
D:ORACLEPRODUCT10.2.0ORADATAORCLREDO02.LOG
SQL> select log_id,low_scn,low_time,next_scn,high_time from v$logmnr_logs;

LOG_ID LOW_SCN LOW_TIME NEXT_SCN HIGH_TIME
---------- ---------- -------------- ---------- --------------
105 3659554 12-12月-10 3664247 12-12月-10

SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'D:oraclelogmnrdic.ora',startscn=>3659554,endscn=>3664247);
PL/SQL 過程已成功完成。
SQL> select scn,timestamp , sql_redo from v$logmnr_contents where seg_owner='ZYK_CDC' or seg_name='TANGYUN_TEST';
SCN TIMESTAMP
---------- --------------
SQL_REDO
--------------------------------------------------------------------------------
3662330 12-12月-10
create table tangyun_test(id number,pname varchar2(10),detail varchar2(200)) tab
lespace TBS_CDCPUB
;

3662354 12-12月-10
CREATE UNIQUE INDEX "ZYK_CDC"."PK_TANGYUN_TEST" on "ZYK_CDC"."TANGYUN_TEST"("ID"
)tablespace TBS_CDCPUB
NOPARALLEL;

SCN TIMESTAMP
---------- --------------
SQL_REDO
--------------------------------------------------------------------------------

3662357 12-12月-10
alter table tangyun_test add constraint pk_tangyun_test primary key(id) using in
dex tablespace TBS_CDCPUB
;
3662516 12-12月-10
drop table tangyun_test purge
;
SCN TIMESTAMP
---------- --------------
SQL_REDO
--------------------------------------------------------------------------------
3662623 12-12月-10
create table tangyun_test(id number,pname varchar2(10),detail varchar2(200)) tab
lespace TBS_CDCPUB;

3662654 12-12月-10
ALTER TABLE "ZYK_CDC"."TANGYUN_TEST" RENAME TO "BIN$UIQvLUrJQ7m9n+v/DwU7Xg==$0"
;

SCN TIMESTAMP
---------- --------------
SQL_REDO
--------------------------------------------------------------------------------
3662657 12-12月-10
drop table tangyun_test AS "BIN$UIQvLUrJQ7m9n+v/DwU7Xg==$0" ;

3662856 12-12月-10
drop table "ZYK_CDC"."BIN$UIQvLUrJQ7m9n+v/DwU7Xg==$0" purge;

3662861 12-12月-10
purge table "BIN$UIQvLUrJQ7m9n+v/DwU7Xg==$0"
;

SCN TIMESTAMP
---------- --------------
SQL_REDO
--------------------------------------------------------------------------------

3662897 12-12月-10
create table tangyun_test(id number,pname varchar2(10),detail varchar2(200)) tab
lespace TBS_CDCPUB;

3662910 12-12月-10
CREATE UNIQUE INDEX "ZYK_CDC"."PK_TANGYUN_TEST" on "ZYK_CDC"."TANGYUN_TEST"("ID"
)tablespace TBS_CDCPUB NOPARALLEL;

SCN TIMESTAMP
---------- --------------
SQL_REDO
--------------------------------------------------------------------------------
3662913 12-12月-10
alter table tangyun_test add constraint pk_tangyun_test primary key(id) using i
ndex tablespace TBS_CDCPUB;

3664154 12-12月-10
ALTER TABLE "ZYK_CDC"."TANGYUN_TEST" RENAME CONSTRAINT "PK_TANGYUN_TEST" TO "BIN
$aMuJ9K/NTjm2rYoUstjsWg==$0" ;

3664157 12-12月-10

SCN TIMESTAMP
---------- --------------
SQL_REDO
--------------------------------------------------------------------------------
ALTER INDEX "ZYK_CDC"."PK_TANGYUN_TEST" RENAME TO "BIN$WVqDx+MxTg6LQ2rd5tydew==$
0" ;

3664161 12-12月-10
ALTER TABLE "ZYK_CDC"."TANGYUN_TEST" RENAME TO "BIN$BXAHQsMhRyWLWbRoMbUCQg==$0"
;

3664164 12-12月-10
drop table tangyun_test AS "BIN$BXAHQsMhRyWLWbRoMbUCQg==$0" ;

SCN TIMESTAMP
---------- --------------
SQL_REDO
--------------------------------------------------------------------------------
已選擇16行。
SQL>