-- logminer 使用 online dict and continuous mine
execute sys.DBMS_LOGMNR.START_LOGMNR(STARTSCN =>648270993 , ENDSCN =>648270993, OPTIONS =>dbms_logmnr.dict_from_online_catalog+sys.DBMS_LOGMNR.CONTINUOUS_MINE); --V$LOGMNR_DICTIONARY-------查询使用的数据字典文件 select filename from v$logmnr_dictionary; --V$LOGMNR_PARAMETERS-------查询当前LogMiner设定的参数 select * from v$logmnr_parameters; --V$LOGMNR_LOGS-------查询分析的日志文件 select filename from v$logmnr_logs; CREATE TABLE tzm.ttt18 AS select ROW_ID, SCN, XIDUSN,XIDSLT,XIDSQN, XID, TABLE_NAME, SEG_OWNER, OPERATION_CODE, CSF, SQL_REDO from v$logmnr_contents
-- logminer 指定 dict
alter system set utl_file_dir='/home/oracle/logminer' scope=spfile; shutdown immediate startup show parameter utl_file_dir execute sys.DBMS_LOGMNR.START_LOGMNR(dictfilename=>'/home/oracle/logminer/dictionary.ora',STARTSCN => 624342312, ENDSCN =>624342384, OPTIONS =>sys.DBMS_LOGMNR.CONTINUOUS_MINE);
/*
* redologfile,sequence,scn关系
*/
SELECT * FROM ( select 'onlinelog',l.GROUP#,sequence#,FIRST_CHANGE# as startscn ,NEXT_CHANGE# as endscn,l.STATUS,l.bytes fsize, to_char(FIRST_TIME,'yyyy/mm/dd:hh24:mi:ss') firsttime, to_char(NEXT_TIME,'yyyy/mm/dd:hh24:mi:ss') nexttime,MEMBER from V$log l, v$logfile lf WHERE l.GROUP#=lf.GROUP# UNION ALL select 'archivelog',recid,SEQUENCE#,FIRST_CHANGE# as startscn ,NEXT_CHANGE# AS endscn,STATUS,BLOCKS*BLOCK_SIZE AS fsize , to_char(FIRST_TIME,'yyyy/mm/dd:hh24:mi:ss') firsttime, to_char(NEXT_TIME,'yyyy/mm/dd:hh24:mi:ss') nexttime, name from v$archived_log WHERE name IS NOT NULL ) WHERE STARTscn <= 622116275 AND 622116275 <= endscn
-- show session and sql
SELECT s.SID, s.SERIAL#, s.USERNAME, s.STATUS,s.OSUSER, s.SCHEMANAME , s.LOGON_TIME , s.machine , s.program , s.seq# , s.event , sq.SQL_TEXT FROM V$SESSION s LEFT OUTER JOIN V$SQL sq ON sq.SQL_ID=s.SQL_ID WHERE s.TYPE='USER' ORDER BY 3,4,8,9,7
-- query object name by objectid
select object_name,OBJECT_ID, data_object_id, dba_objects.* from dba_objects where object_id in (5644153);
-- archiveed log size
SELECT logtime,thread#,sum(logsize)/1024/1024/1024||'GB',count(name) FROM ( SELECT thread#, TRUNC(first_time, 'dd') AS logtime, a.blocks*a.block_size AS logsize, name FROM v$archived_log a WHERE a.STATUS = 'A' AND a.dest_id = 1) li GROUP BY logtime, thread# ORDER BY logtime DESC, thread#
-- alter dump redolog
oradebug setmypid; alter system dump logfile '/opt/oracle/archivelog/archive_1_5_989852304.log' scn min 648270877 scn max 648270878; oradebug tracefile_name;
-- redolog
select lf.TYPE, l.GROUP#,sequence#,FIRST_CHANGE# as startscn ,NEXT_CHANGE# as endscn,l.STATUS,l.bytes fsize, to_char(FIRST_TIME,'yyyy/mm/dd:hh24:mi:ss') firsttime, to_char(NEXT_TIME,'yyyy/mm/dd:hh24:mi:ss') nexttime,MEMBER from V$log l, v$logfile lf WHERE l.GROUP#=lf.GROUP# ALTER DATABASE ADD logfile GROUP 12 '/opt/oracle/oradata/orcl/redo12.log' SIZE 5120m ; ALTER DATABASE ADD logfile GROUP 13 '/opt/oracle/oradata/orcl/redo13.log' SIZE 5120m ; ALTER DATABASE ADD logfile GROUP 14 '/opt/oracle/oradata/orcl/redo14.log' SIZE 5120m ; ALTER DATABASE ADD logfile GROUP 15 '/opt/oracle/oradata/orcl/redo15.log' SIZE 5120m ; ALTER DATABASE ADD logfile GROUP 16 '/opt/oracle/oradata/orcl/redo16.log' SIZE 5120m ; ALTER DATABASE ADD logfile GROUP 17 '/opt/oracle/oradata/orcl/redo17.log' SIZE 5120m ; ALTER DATABASE ADD logfile GROUP 18 '/opt/oracle/oradata/orcl/redo18.log' SIZE 5120m ; ALTER DATABASE ADD logfile GROUP 19 '/opt/oracle/oradata/orcl/redo19.log' SIZE 5120m ; ALTER DATABASE ADD logfile GROUP 20 '/opt/oracle/oradata/orcl/redo20.log' SIZE 5120m ; ALTER database DROP logfile group 8; ALTER database DROP logfile group 9; ALTER database DROP logfile group 10; ALTER database DROP logfile group 11; ALTER system switch logfile ; alter system checkpoint; alter system archive log SEQUENCE 4979;
相关推荐
EXECUTE dbms_logmnr.add_logfile(LogFileName=>'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ABC\REDO03.LOG',Options=>dbms_logmnr.new); 添加其它文件 EXECUTE dbms_logmnr.add_logfile(LogFileName=>'D:\ORACLE\...
ODI如何通过logminer技术从oracle_数据库中抽取增量数据
基于LogMiner的Oracle数据库日志分析.pdf
第1章 建立和配置数据库 1.1 数据库创建规划 1.1.1 规划以及提出正确的问题 1.1.2 怎样确定恰当的数据块尺寸 1.2 组织文件系统 1.2.1 怎样命名数据库文件 1.2.2 使用最佳灵活结构 1.2.3 怎样配置符合 OFA 的 ...
oracle日志分析工具LogMiner使用.
利用LogMiner分析Oracle日志文件.pdf
利用LogMiner进行Oracle数据恢复.pdf
Transglobe-logminer-kafka 表数据复制 先决条件 安装Kafka服务器(kafka_2.13-2.7.0)1.1将ojdbc8-12.2.0.1.jar复制到kafka的libs目录中 建立并获取mylogminer-1.0-jar-with-dependencies.jar 构建并获取ods-load...
LogMiner工具使用说明.doc
Logminer依赖于2个包:DBMS_LOGMNR和DBMS_LOGMNR_D,Oracle 11g默认已安装 Logminer 基本使用步骤 ... Start LogMiner. 开始日志挖掘 <4>. Request the redo data of interest. 查询V$LOGMNR_CONTENTS获
var Log = require ( 'logminer' ) ; var log = new Log ( 'Parent' ) ; log . message ( 'hello' ) ; log . warn ( 'world' ) ; log . info ( 'in 2015' ) ; var child = log . child ( 'Child' ) ; child ....
Oracle中使用LogMiner进行日志分析.pdf
当我们不小心误操作致使数据库数据丢失、改变时, 需要对数据库对象做基于时间点的恢复,找到我们需要的数据,这个时间点不能...SQL> select name from v$archived_log; NAME ----------------------------------------
Oracle_10g_LogMiner
Oracle异机logminer日志分析 日志分析 挖掘日志 分析
LogMiner提供了一个处理重做日志文件并将其内容翻译成代表对数据库的逻辑操作的SQL语句的过程。LogMiner运行在Oracle版本8.1或者更高版本中。
7 使用LOGMINER恢复archive log 文件,视图$logmnr_contents中不包含() a. archive logfile路径 b. table_name c. SCN型 d. Sql_redo 8 Which statement about locally managed table spaces is true? a. Tables in ...
Logminer是oracle从8i开始提供的用于分析重做日志信息的工具,它包括DBMS_LOGMNR和DBMS_LOGMNR_D两个package,后边的D是字典的意思。它既能分析redo log file,也能分析归档后的archive log file。在分析日志的过程...
Oracle 10g中LogMiner的使用.pdf