ITPub博客

首页 > Linux操作系统 > Linux操作系统 > logminer

logminer

原创 Linux操作系统 作者:flyingslr 时间:2009-03-10 11:19:11 0 删除 编辑

1 使用logminer分析DML操作:
SQL> insert into scott.dept values(51,'admins',default);
已创建 1 行。
SQL> commit;
提交完成。
SQL> exec dbms_logmnr.add_logfile(logfilename=>'d:\ORACLE\PRODUCT\10.2.0\ORADAT
\orcl\redo01.log',options=>dbms_logmnr.new)
PL/SQL 过程已成功完成。
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalo
)
PL/SQL 过程已成功完成。
SQL> select username,timestamp,sql_redo from v$logmnr_contents where seg_name='D
EPT';
USERNAME TIMESTAMP
-------- --------------
SQL_REDO
------------------------------------------------------------
SYS      10-3月 -09
insert into "SCOTT"."DEPT"("COL 1","COL 2") values (HEXTORAW
('c133'),HEXTORAW('61646d696e'));
SYS      10-3月 -09
update "SCOTT"."DEPT" set "COL 3" = HEXTORAW('6265696a696e67
') where "COL 3" IS NULL and ROWID = 'AAAMfNAAEAAAAAMAAB';
SYS      10-3月 -09
USERNAME TIMESTAMP
-------- --------------
SQL_REDO
------------------------------------------------------------
ALTER TABLE "SCOTT"."DEPT" RENAME CONSTRAINT "PK_DEPT" TO "B
IN$yK9S5MOxSweCxHxX7PXx6g==$0" ;
SYS      10-3月 -09
ALTER TABLE "SCOTT"."DEPT" RENAME TO "BIN$CXM0utMaTqKZ4fh2Zg
np9A==$0" ;
SYS      10-3月 -09
drop table scott.dept AS "BIN$CXM0utMaTqKZ4fh2Zgnp9A==$0" ;
USERNAME TIMESTAMP
-------- --------------
SQL_REDO
------------------------------------------------------------
SQL> EXEC DBMS_LOGMNR.END_LOGMNR;
PL/SQL 过程已成功完成。
2 使用logminer 分析DDL操作:
SQL> alter system set utl_file_dir='d:\backup' scope=spfile;
系统已更改。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area  603979776 bytes
Fixed Size                  1250380 bytes
Variable Size             285215668 bytes
Database Buffers          310378496 bytes
Redo Buffers                7135232 bytes
数据库装载完毕。
数据库已经打开。
SQL> exec dbms_logmnr_d.build('dict.ora','d:\backup',dbms_logmnr_d.store_in_flat
_file)
PL/SQL 过程已成功完成。
SQL> exec dbms_logmnr.add_logfile(logfilename=>'d:\ORACLE\PRODUCT\10.2.0\ORADATA
\ORCL\REDO01.LOG',options=>dbms_logmnr.new)
PL/SQL 过程已成功完成。
SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'d:\backup\dict.ora',options=>d
bms_logmnr.ddl_dict_tracking)
PL/SQL 过程已成功完成。
SQL> col sql_redo format a40
SQL> select timestamp,sql_redo from v$logmnr_contents where lower(sql_redo) like
 '%drop table%';
TIMESTAMP      SQL_REDO
-------------- ----------------------------------------
10-3月 -09     drop table scott.emp AS "BIN$y2AltRlkQ4q
               SEiYns6tKdA==$0" ;
10-3月 -09     drop table scott.dept AS "BIN$CXM0utMaTq
               KZ4fh2Zgnp9A==$0" ;
SQL> exec dbms_logmnr.end_logmnr;
PL/SQL 过程已成功完成。
3 显示logminer信息:
SQL> select to_char(timestamp,'yyyy-mm-dd:hh24:mi:ss') time,sql_redo from v$logm
nr_contents where sql_redo like '%create%' or sql_redo like '%create%';

TIME                SQL_REDO
------------------- ----------------------------------------
2009-03-10:09:00:57 insert into "SYS"."WRH$_LATCH_MISSES_SUM
                    MARY"("SNAP_ID","DBID","INSTANCE_NUMBER"
                    ,"PARENT_NAME","WHERE_IN_CODE","NWFAIL_C
                    OUNT","SLEEP_COUNT","WTR_SLP_COUNT") val
                    ues ('55','1207717150','1','slave class
                    create','ksvcreate','0','3','0');
2009-03-10:10:00:14 insert into "SYS"."WRH$_LATCH_MISSES_SUM
                    MARY"("SNAP_ID","DBID","INSTANCE_NUMBER"
                    ,"PARENT_NAME","WHERE_IN_CODE","NWFAIL_C
                    OUNT","SLEEP_COUNT","WTR_SLP_COUNT") val
TIME                SQL_REDO
------------------- ----------------------------------------
                    ues ('56','1207717150','1','slave class
                    create','ksvcreate','0','4','0');
SQL> select db_name,filename from v$logmnr_dictionary;
DB_NAME
---------
FILENAME
--------------------------------------------------------------------------------
ORCL
d:\backup\dict.ora
SQL> select filename,low_scn,next_scn from v$logmnr_logs;
FILENAME
--------------------------------------------------------------------------------
   LOW_SCN   NEXT_SCN
--------- ----------
d:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
    884573     911694
SQL> select start_date,end_data from v$logmnr_parameters;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10225874/viewspace-566529/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 使用flashback
请登录后发表评论 登录
全部评论

注册时间:2008-07-15

  • 博文量
    44
  • 访问量
    80140