-- 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\...
- 执行命令`ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/oracle11g/archivelog' SCOPE=BOTH;`来设置归档路径。 - 关闭数据库:`SHUTDOWN IMMEDIATE;` - 启动到mount状态:`STARTUP MOUNT;` - 开启归档模式...
SELECT NAME FROM V$ARCHIVED_LOG WHERE FIRST_TIME <='2017-05-25 14:27:16' AND NEXT_TIME >='2017-05-25 14:27:16'; EXECUTE DBMS_LOGMNR.ADD_LOGFILE('D:\ARCHIVE\ARC0000015312_0867335574.0001', OPTIONS=>...
LogMiner 的架构主要包括三个部分:日志挖掘器(LogMiner)、日志分析器(Log Analyzer)和日志报告器(Log Reporter)。日志挖掘器负责从日志文件中提取日志记录,日志分析器对日志记录进行分析和处理,而日志报告...
手动切换当前redo日志的命令是`ALTER SYSTEM ARCHIVE LOG CURRENT`,这有助于确保数据库在发生故障时能够恢复到特定时间点。 接下来,查询归档日志的时间范围至关重要,因为我们需要确定要分析的具体日志文件。可以...
通过`SELECT NAME FROM V$ARCHIVED_LOG;`查询可用的归档日志,然后使用`DBMS_LOGMNR.ADD_LOGFILE`将指定的日志文件添加到分析列表。 **4. 启动 LogMiner** 启动LogMiner分析的命令是`DBMS_LOGMNR.START_LOGMNR`。...
Oracle中的LogMiner是一个强大的日志分析工具,自Oracle 8i版本开始提供,用于解析重做日志文件(归档日志文件),提取其中的DML操作(INSERT、UPDATE、DELETE等)以及相关的回滚SQL语句。LogMiner没有图形用户界面...
### Logminer简单介绍 #### 一、Logminer概述 Oracle Logminer是Oracle数据库的一个实用工具,主要用于捕捉数据库中发生的变更信息。它可以帮助我们追踪数据库表中的数据修改情况,包括INSERT、UPDATE、DELETE等...
SQL> execute dbms_logmnr.add_logfile(LogFileName => 'G:\ORACLE\ORADATA\ORADBSP\REDO04.LOG', Options => dbms_logmnr.new); SQL> execute dbms_logmnr.add_logfile(LogFileName => 'G:\ORACLE\ORADATA\ORADBSP\...
Logminer依赖于2个包:DBMS_LOGMNR和DBMS_LOGMNR_D,Oracle 11g默认已安装 Logminer 基本使用步骤 ... Start LogMiner. 开始日志挖掘 <4>. Request the redo data of interest. 查询V$LOGMNR_CONTENTS获
4. 添加在线重做日志成员:`sql> alter database add logfile member '/disk3/log1b.rdo' to group 1, '/disk4/log2b.rdo' to group 2;` 5. 重命名在线重做日志文件:`sql> alter database rename file 'c:/oracle/...
- 命令:`ALTER DATABASE ADD LOGFILE [GROUP <group_number>] ('<path_to_logfile_a>', '<path_to_logfile_b>') SIZE ;` - 作用:通过指定路径和大小,可以创建新的在线重做日志组。例如: ```sql ALTER ...
3. **启用补充日志记录**:通过执行`ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;`命令来启用。 #### 四、操作步骤 下面通过具体的步骤来演示如何使用LogMiner进行归档日志分析: ##### 1. 添加归档日志文件 首先...
**LogMiner**是Oracle官方提供的一款用于解析重做日志文件(Redo Log Files)的工具。它能够提取并记录下数据库中发生的各种变更事件,包括但不限于插入(INSERT)、更新(UPDATE)、删除(DELETE)以及DDL操作等。...
### Oracle Logminer 使用和安装步骤详解 #### 一、Oracle Logminer 概述 Oracle Logminer 是一个强大的工具,用于挖掘 Oracle 数据库 Redo 日志中的数据更改信息。通过 Logminer,用户可以追踪数据库表的变化情况...
SQL>alter system set utl_file_dir='d:\oracle\logmnr' scope=both; -- 修改数据字典位置 SQL>execute dbms_logmnr_d.build('dictionary.ora', 'd:\oracle\logmnr'); -- 创建数据字典文件 ``` #### 三、LogMiner的...
LogMiner是Oracle数据库提供的一种日志分析工具,它能够解析redo log文件,帮助DBA追踪数据库的逻辑更改、纠正用户误操作、执行事后审计和变化分析。LogMiner通过重构SQL语句和UNDO语句,使DBA能够理解日志中的操作...
Oracle中的日志主要包括重做日志(Redo Logs)和归档日志(Archived Logs)。其中,重做日志用于记录对数据库的所有更改;而归档日志则是在数据库运行在归档模式下时,将已经写入重做日志但尚未被覆盖的数据进行备份...