Logmnr 用法介绍及其注意事项(ORA-01306)
?
Logmnr 用法介绍及其注意事项
转载请注明出处：http://blog.csdn.net/xiaofan23z
Environment：linux +oracle10g two nodes rac
一、???????????安装LogMiner工具，以下两个脚本以SYSDBA身份运行
?
SQL>?@$ORACLE_HOME/rdbms/admin/dbmslm.sql;
Package created.
Grant succeeded.
##创建DBMS_LOGMNR包，该包用来分析日志文件。
SQL>?@$ORACLE_HOME/rdbms/admin/dbmslmd.sql;
Package created.
##创建DBMS_LOGMNR_D包，该包用来创建数据字典文件。
二、???????????使用LogMiner工具??
?下面将详细介绍如何使用LogMiner工具。?
?1、创建数据字典文件（data-dictionary）??
??1).首先在init.ora初始化参数文件中，指定数据字典文件的位置，也就是添加一个参数 UTL_FILE_DIR，该参数值为服务器中放置数据字典文件的目录。如：? UTL_FILE_DIR?=?($ORACLE_HOME\logs)?,重新启动数据库，使新加的参数生效：
SQL>?shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shutdown.
SQL>?startup mount
ORACLE instancestarted.
?
Total System GlobalArea? 922746880 bytes
Fixed Size??? ??????????????1222624 bytes
Variable Size???????????? 209717280 bytes
DatabaseBuffers????????? 704643072 bytes
Redo Buffers??????????????? 7163904 bytes
Database mounted.
SQL>?alter system setutl_file_dir='/u01/app/oracle/product/10.2.0/db_1/log' scope=spfile;
System altered.
SQL>?shutdown immediate
ORA-01109: databasenot open
Database dismounted.
ORACLE instance shutdown.
SQL>??????????????????
SQL>?startup mount
ORACLE instancestarted.
?
Total System GlobalArea? 922746880 bytes
Fixed Size?????????? ???????1222624 bytes
Variable Size???????????? 209717280 bytes
DatabaseBuffers????????? 704643072 bytes
Redo Buffers??????????????? 7163904 bytes
Database mounted.
SQL>
SQL>?show parameter utl
?
NAME???????????????????????????????? TYPE??????? VALUE
----------------------------------------------- ------------------------------
create_stored_outlines?????????????? string
utl_file_dir???????????????????????? string????? /u01/app/oracle/product/10.2.0
????????????????????????????????????????????????/db_1/log
SQL>
SQL>?alter database open;
Database altered.
SQL>
?
??????? Ps: LogMiner uses a dictionary file, which is a special file that
?????????????? indicates the database that created it as well as the time the file was
?????????????? created. The dictionary file is not required, but is recommended. Without a
?????????????? dictionary file, the equivalent SQL statements will use Oracle internal object
?????????????? IDs for the object name and present column values as hex data.
也可以不使用数据字典，但是oracle推荐使用。
From ：How to Setup LogMiner [ID 111886.1]
转载请注明出处：http://blog.csdn.net/xiaofan23z
?
2). 然后创建数据字典文件
SQL>?execute dbms_logmnr_d.build(dictionary_filename =>'dict.ora',dictionary_location =>'/u01/app/oracle/product/10.2.0/db_1/log');
PL/SQL procedure successfully completed.
2. 创建要分析的日志文件列表??
??1).创建分析列表，即所要分析的日志
?SQL>?executedbms_logmnr.add_logfile(LogFileName =>'+DATA/ldbrac/onlinelog/group_3.266.732154759',Options => dbms_logmnr.new); ##根据时间点或者在v$log，v$archived_log中查询出要分析的log
?
PL/SQL procedure successfully completed.
?
###为了验证效果 删除一个表然后切换log
droptable?askey_id；
SQL>?alter system switch logfile;
System altered.
将切换的log 加入到分析列表
SQL>?execute dbms_logmnr.add_logfile(LogFileName =>'+DATA/ldbrac/onlinelog/group_2.262.732154617',Options =>dbms_logmnr.ADDFILE);
?
PL/SQL procedure successfully completed.
?
Alert log中会有如下信息显示
Sat Apr 21 11:35:192012
LOGMINER: Beginmining logfile: +DATA/ldbrac/onlinelog/group_2.262.732154617
Sat Apr 21 11:35:192012
LOGMINER: Begin mining logfile:+DATA/ldbrac/onlinelog/group_3.266.732154759
?
3、使用logMiner进行日志分析?
???1).无限制条件，即用数据字典文件对要分析的日志文件所有内容做分析?
?????SQL>?executedbms_logmnr.start_logmnr(DictFileName =>?????'/u01/app/oracle/product/10.2.0/db_1/log/dict.ora');
PL/SQL procedure successfully completed.
??????
???????? 2).带限制条件，可以用scn号或时间做限制条件,也可组合使用?
???????? ????--分析日志列表中时间从20120420从10:00到13:00的内容?
SQL> execute dbms_logmnr.start_logmnr(startTime => to_date('20120421100000','yyyy-mm-dd hh24:mi:ss'),endTime => to_date('20120421130000','yyyy-mm-dd hh24:mi:ss'),DictFileName => '/u01/app/oracle/product/10.2.0/db_1/log/dict01.ora');
????PL/SQL?procedure?successfully?completed?
??dbms_logmnr.start_logmnr函数的原型为：?
??PROCEDURE?start_logmnr(?
?????startScn???????????IN??NUMBER?default?0?,
?????endScn?IN??NUMBER?default?0,?
?????startTime??????IN??DATE?default?'',?
?????endTime????????IN??DATE?default?'',?
?????DictFileName????IN??VARCHAR2?default?'',?
?????OptionsIN??BINARY_INTEGER?default?0?);?
4.分析数据??
V$LOGMNR_LOGS?是分析日志列表视图
分析结果在GV$LOGMNR_CONTENTS?视图中
根据条件查询分析结果
SQL>? select* from V$LOGMNR_CONTENTSwhere?sql_redolike'drop%';
?
??????SCN?????? CSCN TIMESTAMPCOMMIT_TI??? THREAD#???? LOG_ID????XIDUSN
---------- ---------- --------- ------------------- ---------- ----------
???XIDSLT???? XIDSQN??? PXIDUSN???PXIDSLT??? PXIDSQN???? RBASQN????RBABLK
---------- ---------- ---------- -------------------- ---------- ----------
??RBABYTE???? UBAFIL???? UBABLK????UBAREC???? UBASQN? ABS_FILE#?REL_FILE#
?
?
?
使用logminer查询表v$logmnr_contents必须在同一个会话中进行，因为分析的那些
信息存储在这个session 的PGA中，在别的session里面是查不到的。
如果在别的session中视图查询这个视图得到如下错误
SQL>select * from V$LOGMNR_CONTENTS;
select* from V$LOGMNR_CONTENTS
ERRORat line 1:
ORA-01306: dbms_logmnr.start_logmnr() must be invoked before selectingfrom
v$logmnr_contents
视图v$logmnr_contents中的分析结果仅在我们运行过程'dbms_logmrn.start_logmnr'这个
会话的生命期中存在。这是因为所有的LogMiner存储都在PGA内存中，所有其他的进程是看
不到它的，同时随着进程的结束，分析结果也随之消失。 最后，使用过程DBMS_LOGMNR.END_LOGMNR
终止日志分析事务，此时PGA内存区域被清除，分析结果也随之不再存在。
?
可以创建临时表解决这个问题
SQL>create table logmnr_tab1 as select? *from V$LOGMNR_CONTENTS;
Tablecreated.
SQL>
?
然后在临时表中分析
?
?
?
selectscn,timestamp,log_id,seg_owner,seg_name,table_name,seg_type_name,operation,sql_redo?
fromsys.logmnr_tab1where?sql_redolike'drop%';
根据条件查询特定的DDL/DML操作
?
?
5．分析后释放内存
?SQL>?execute?dbms_logmnr.end_logmnr;?
???PL/SQL?procedure?successfully?completed?
6.其它?
???1).删除日志分析文件?
???exec?dbms_logmnr.add_logfile('+DATA/ldbrac/onlinelog/group_2.262.732154617',dbms_logmnr.removefile);
注意：
?
??? BEGIN
??????? DBMS_LOGMNR.REMOVE_LOGFILE(LOGFILENAME=> '/oracle/logs/log2.f');
??? END;
??? /
更多信息请查看?java进阶网?http://www.javady.com/index.php/category/thread
