ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
firstname.lastname@example.org. Request a Workshop for
your organization today.
Enables online query online of archived redo log files through a SQL interface and provides most of the tools required to start and stop log mining.
Add archive log option flags
Status column option flags
Start LOGMNR option flags
If 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).
Directs 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.
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.
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 GV$LOGMNR_CONTENTS view if the table definition in the database does not match the table definition in the redo log file.
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.
Will be deprecated soon
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.
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.
If set, LogMiner formats the reconstructed SQL statements for ease of reading. These reconstructed SQL statements are not executable.
Directs a select operation on the GV$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 GV$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.
SUBTYPE Length IS BINARY_INTEGER;
SUBTYPE ThreadId IS BINARY_INTEGER;
-- workarounds for the lack of constrained subtypes
SUBTYPE LogFileName IS LogFileNameTemplate%TYPE;
SUBTYPE LogFileDescription IS LogFileDescTemplate%TYPE;
SELECT v.scn, v.commit_timestamp, v.table_name, o.object_name, v.operation
FROM sys.v_$logmnr_contents v, dba_objects_ae o
WHERE SUBSTR(v.table_name,6) = o.object_id;
conn / as sysdba
STARTUP MOUNT EXCLUSIVE;
ALTER DATABASE NOARCHIVELOG;
ALTER DATABASE OPEN;
SELECT sql_redo, sql_undo
WHERE username = 'UWCLASS';
SELECT utl_raw.cast_to_varchar2(HEXTORAW('53414c')) FROM dual;
WHERE seg_name = 'AIRPLANES'
AND seg_owner = 'UWCLASS'
AND operation = 'UPDATE'
AND sys.dbms_logmnr.mine_value(REDO_VALUE, 'CUSTOMER_ID') <> sys.dbms_logmnr.mine_value(UNDO_VALUE, 'CUSTOMER_ID');
options IN BINARY_INTEGER DEFAULT 0,
schema IN VARCHAR2 DEFAULT '',
startSCN IN NUMBER DEFAULT 0,
endSCN IN NUMBER DEFAULT 0,
startTime IN DATE DEFAULT '',
endTime IN DATE DEFAULT '',
threads IN VARCHAR2 DEFAULT '',
logLocation IN VARCHAR2 DEFAULT '',
logNameSpecifier IN VARCHAR2 DEFAULT '');
startscn IN NUMBER DEFAULT 0,
endscn IN NUMBER DEFAULT 0,
starttime IN DATE DEFAULT '',
endtime IN DATE DEFAULT '',
dictfilename IN VARCHAR2 DEFAULT '',
options IN BINARY_INTEGER DEFAULT 0);