ITPub博客

首页 > 数据库 > Oracle > Oracle DBMS_LOGMNR

Oracle DBMS_LOGMNR

原创 Oracle 作者:mfm088 时间:2006-10-10 14:20:55 0 删除 编辑

Oracle DBMS_LOGMNR

[@more@]
Oracle DBMS_LOGMNR
Version 10.2
General
Source{ORACLE_HOME}/rdbms/admin/dbmslm.sql

Constants
Add archive log option flags
NameData TypeValue
ADDFILEBINARY_INTEGER3
NEWBINARY_INTEGER1
REMOVEFILEBINARY_INTEGER2
Start LOGMNR option flags
NameData TypeValueDescription
COMMITTED_DATA_ONLYBINARY_INTEGER2If set, DML statements corresponding to committed transactions are returned. DML statements corresponding to a committed transaction are grouped together. Transactions are returned in their commit order. Transactions that are rolled back or in-progress are filtered out, as are internal redo records (those related to index operations, management, and so on).

If this option is not set, all rows for all transactions (committed, rolled back, and in-progress) are returned in the order in which they are found in the redo logs (in order of SCN values).
CONTINUOUS_MINEBINARY_INTEGER1024Directs LogMiner to automatically add redo log files, as needed, to find the data of interest. You only need to specify the first log to start mining, or just the starting SCN or date to indicate to LogMiner where to begin mining logs. You are not required to specify any redo log files explicitly. LogMiner automatically adds and mines the (archived and online) redo log files for the data of interest. This option requires that LogMiner is connected to the same database instance that is generating the redo log files. It also requires that the database be mounted and that archiving be enabled.

Beginning with Oracle Database release 10.1, the CONTINUOUS_MINE options is supported for use in an Oracle Real Application Clusters environment.
DDL_DICT_TRACKINGBINARY_INTEGER

8

If the LogMiner dictionary in use is a flat file or in the redo log files, LogMiner updates its internal dictionary if a DDL event occurs. This ensures that correct SQL_REDO and SQL_UNDO information is maintained for objects that are modified after the LogMiner internal dictionary is built. The database to which LogMiner is connected must be open.

This option cannot be used in conjunction with the DICT_FROM_ONLINE_CATALOG option and cannot be used when the LogMiner dictionary being used is one that was extracted to a flat file prior to Oracle9i.
DICT_FROM_ONLINE_CATALOGBINARY_INTEGER

16

Directs LogMiner to use the current online database dictionary rather than a LogMiner dictionary contained in a flat file or in the redo log files being analyzed.

This option cannot be used in conjunction with the DDL_DICT_TRACKING option. The database to which LogMiner is connected must be the same one that generated the redo log files.

Expect to see a value of 2 in the STATUS column of the V$LOGMNR_CONTENTS view if the table definition in the database does not match the table definition in the redo log file.
DICT_FROM_REDO_LOGSBINARY_INTEGER

32

If set, LogMiner expects to find a LogMiner dictionary in the redo log files that were specified. The redo log files are specified with the DBMS_LOGMNR.ADD_LOGFILE procedure or with the DBMS_LOGMNR.START_LOGMNR procedure with the CONTINUOUS_MINE option.
NO_DICT_RESET_ONSELECTBINARY_INTEGER

1

Will be deprecated soon
NO_ROWID_IN_STMTBINARY_INTEGER

2048

If set, the ROWID clause is not included in the reconstructed SQL statements. The redo log file may already contain logically unique identifiers for modified rows if supplemental logging is enabled.

When using this option, you must be sure that supplemental logging was enabled in the source database at the appropriate level and that no duplicate rows exist in the tables of interest. LogMiner does not make any guarantee regarding the uniqueness of logical row identifiers.
NO_SQL_DELIMITERBINARY_INTEGER

64

If set, the SQL delimiter (a semicolon) is not placed at the end of reconstructed SQL statements. This is helpful for applications that open a cursor and then execute the reconstructed statements.
PRINT_PRETTY_SQLBINARY_INTEGER

512

If set, LogMiner formats the reconstructed SQL statements for ease of reading. These reconstructed SQL statements are not executable.
SKIP_CORRUPTIONBINARY_INTEGER

4

Directs a select operation on the V$LOGMNR_CONTENTS view to skip any corruptions in the redo log file being analyzed and continue processing. This option works only when a block in the redo log file (and not the header of the redo log file) is corrupt. You should check the INFO column in the V$LOGMNR_CONTENTS view to determine the corrupt blocks skipped by LogMiner. When a corruption in the redo log file is skipped, the OPERATION column contains the value CORRUPTED_BLOCKS, and the STATUS column contains the value 1343.

Status column option flags

NameData TypeValue
CORRUPTED_BLK_IN_REDOBINARY_INTEGER32
HOLE_IN_LOGSTREAMBINARY_INTEGER13
INVALID_SQLBINARY_INTEGER2
UNGUARANTEED_SQLBINARY_INTEGER3
VALID_SQLBINARY_INTEGER0
Workarounds for the lack of constrained subtypes
LogFileNameTemplate VARCHAR2(256);
SUBTYPE LogFileName IS LogFileNameTemplate%TYPE;
LogFileDescTemplate VARCHAR2(256);
SUBTYPE LogFileDescription IS LogFileDescTemplate%TYPE;
Data TypesSUBTYPE Length IS BINARY_INTEGER;

SUBTYPE ThreadId IS BINARY_INTEGER;
Dependencies
DBA_LOGMNR_LOGGV_$LOGMNR_STATS
DBA_LOGMNR_PURGED_LOGGV_$LOGMNR_TRANSACTION
DBA_LOGMNR_SESSIONV_$LOGMNR_CALLBACK
DBMS_LOGMNR_INTERNALV_$LOGMNR_CONTENTS
DBMS_STREAMS_ADM_UTLV_$LOGMNR_DICTIONARY
GV_$LOGMNR_CALLBACKV_$LOGMNR_DICTIONARY_LOAD
GV_$LOGMNR_CONTENTSV_$LOGMNR_LATCH
GV_$LOGMNR_DICTIONARYV_$LOGMNR_LOGFILE
GV_$LOGMNR_DICTIONARY_LOADV_$LOGMNR_LOGS
GV_$LOGMNR_LATCHV_$LOGMNR_PARAMETERS
GV_$LOGMNR_LOGFILEV_$LOGMNR_PROCESS
GV_$LOGMNR_LOGSV_$LOGMNR_REGION
GV_$LOGMNR_PARAMETERSV_$LOGMNR_SESSION
GV_$LOGMNR_PROCESSV_$LOGMNR_STATS
GV_$LOGMNR_REGIONV_$LOGMNR_TRANSACTION
GV_$LOGMNR_SESSION

Exceptions
z
NumberDescription
ORA-00904Value specified for the column_name parameter is not a fully qualified column name.
ORA-01281startScn or endSCN parameter specified is not a valid SCN or endScn is greater then startScn
ORA-01282startTime parameter not between years 1988 and 2110 or endTime parameter is greater than year 2110
ORA-01283The value specified in the Options parameter is not a NUMBER or is not a known LogMiner Adhoc option
ORA-01284The dictionary file specified in DictFileName parameter has a length greater then 256 or cannot be opened
ORA-01285DictFileName parameter is not a valid VARCHAR2
ORA-01286Options specified require start time or start SCN
ORA-01287Specified file is from a different database incarnation
ORA-01289Specified file has already been added to the list. Duplicate redo log files cannot be added.
ORA-01290Specified file is not in the current list and therefore cannot be removed from the list.
ORA-01291Redo files are missing which are needed to satisfy the user's requested SCN/time range. The user can specify ALLOW_MISSING_LOGS option. Missing logs are not allowed when DDL tracking is in use
ORA-01292No log file has been registered with LogMiner
ORA-01293Mounted database required for options specified (CONTINIOUS_MINE)
ORA-01294Error while processing the data dictionary extract
ORA-01295DB_ID of the data dictionary does not match that of the redo logs
ORA-01296Character set specified in the data dictionary does not match (is incompatible with) that of the database
ORA-01297Redo version mismatch between the dictionary and the registered redo logs
ORA-01298More than one dictionary source was specified or DDL_DICT_TRACKING was requested with DICT_FROM_ONLINE_CATALOG
ORA-01299Dictionary is from a different database incarnation
ORA-01300Writable database required for options specified (DDL_DICT_TRACKING, DICT_FROM_REDO_LOGS,
DICT_FROM_ONLINE_CATALOG)
ORA-01323A LogMiner dictionary is not associated with the LogMiner session
ORA-01324Specified file cannot be added to the list because there is a DB_ID mismatch.
ORA-01371A logfile containing the dictionary dump to redo logs is missing
Pragmapragma TIMESTAMP('1998-05-05:11:25:00');
Security ModelExecute granted to execute_catalog_role
-- either
GRANT execute catalog role TO uwclass;
- or
GRANT select ON v_$logmnr_contents TO uwclass;
ADD_LOGFILE
Register logfiles to be analyzeddbms_logmnr.add_logfile(
logfilename VARCHAR2,
options BINARY_INTEGER DEFAULT ADDFILE);
See MINE_VALUE Demo
COLUMN_PRESENT
Designed to be used in conjunction with the MINE_VALUE function. If the MINE_VALUE function returns a NULL value, it can mean either the specified column is not present in the redo or undo portion of the data or the specified column is present and is NULL. dbms_logmnr.column_present(
sql_redo_undo RAW,
column_name VARCHAR2 DEFAULT '') RETURN NUMBER;
See MINE_VALUE Demo
END_LOGMNR
Completes a log miner sessiondbms_logmnr.end_logmnr;
See MINE_VALUE Demo
MINE_VALUE

Facilitates queries based on a column's data value.
dbms_logmnr.mine_value(
sql_redo_undo RAW,
column_name VARCHAR2 DEFAULT '') RETURN VARCHAR2;
conn / as sysdba

shutdown immediate;

startup mount exclusive;

alter database archivelog;

alter database open;

grant select on v_$logmnr_contents to uwclass;

alter system switch logfile;

desc gv$logmnr_contents

SELECT COUNT(*)
FROM gv$logmnr_contents;

GRANT EXECUTE ON dbms_flashback TO uwclass;

conn uwclass/uwclass

-- capture starting SCN
SELECT dbms_flashback.get_system_change_number
FROM dual;
-- 3845224

UPDATE airplanes
SET customer_id = 'FIND'
WHERE line_number = 13396;

COMMIT;

alter system switch logfile;

-- capture ending SCN
SELECT dbms_flashback.get_system_change_number
FROM dual;
-- 3845288
DECLARE
bscn NUMBER := 3845224;
escn NUMBER := 3845288;
BEGIN
sys.dbms_logmnr.add_logfile('c:tempO1_MF_1_140_27XNFMRW_.ARC');

sys.dbms_logmnr.start_logmnr(bscn, escn);
--EXCEPTION
-- WHEN OTHERS THEN
-- sys.dbms_logmnr.end_logmnr;
END;
/

/*
sys.dbms_logmnr.start_logmnr(bscn, escn, options=> dbms_logmnr.committed_data_only);
*/


col object_name format a30

desc gv$logmnr_contents;
-- note abs_file#, rel_file#, data_blk#

SELECT v.scn, v.commit_timestamp, v.table_name, o.object_name, v.operation
FROM sys.v_$logmnr_contents v, dba_objects o
WHERE SUBSTR(v.table_name,6) = o.object_id;

SELECT info
FROM gv$logmnr_contents;

SELECT sql_redo, sql_undo
FROM gv$logmnr_contents
WHERE username = 'UWCLASS';

SELECT utl_raw.cast_to_varchar2(HEXTORAW('53414c')) FROM dual;
NWO HEXTORAW('4e574f')
USAF HEXTORAW('55534146')
DAL HEXTORAW('44414c')
SAL 53414c

SELECT sql_redo
FROM gv$logmnr_contents
WHERE seg_name = 'AIRPLANES'
AND seg_owner = 'UWCLASS'
AND operation = 'UPDATE'
AND dbms_logmnr.mine_value(REDO_VALUE) <> dbms_logmnr.mine_value(UNDO_VALUE);

-- need query with COLUMN_PRESENT (finds it and doesn't find it)

exec sys.dbms_logmnr.end_logmnr;
REMOVE_LOGFILE
Removes a redo log file from an existing list of redo log files for LogMiner to processdbms_logmnr.remove_logfile(LogFileName VARCHAR2);
TBD
START_LOGMNR
Begin a log miner sessiondbms_logmnr.start_logmnr(
startscn NUMBER DEFAULT 0,
endscn NUMBER DEFAULT 0,
starttime DATE DEFAULT '01-JAN-1988',
endtime DATE DEFAULT '31-DEC-2110',
dictfilename VARCHAR2 DEFAULT '',
options BINARY_INTEGER DEFAULT 0);
See MINE_VALUE Demo
Related Topics
DBMS_FLASHBACK
Contact Us ? Legal Notices and Terms of Use ? Privacy Statement

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

上一篇: Oracle control files
请登录后发表评论 登录
全部评论

注册时间:2012-01-14

  • 博文量
    98
  • 访问量
    1025267