Sometimes Dbvisit support will ask for a row history, also known as LogMiner dump. This means running LogMiner on the archive/online redo logs to obtain all changes on a particular row.
Setting up LogMiner
First, guess the date (or directly SCN) range of interest. This is often constrained by archive logs available (only archive logs still known to the database are used by default).
A SYSDBA connection is needed.
begin
DBMS_LOGMNR.START_LOGMNR(
STARTTIME => sysdate-1,
ENDTIME => sysdate,
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE
);
end;
Querying the LogMiner data
After executing START_LOGMNR, the LogMiner data is available via
SELECT *
FROM V$LOGMNR_CONTENTS
Note however, that running this query in full and without any where clause can take a very long time. Also, it's not advisable to insert the full data back into the database, as it would cause lot of archive logs generated and next query to the view would query these new logs, too.
The recommended approach is:
- Find one operation to the row in question; this is often done by using TABLE_NAME, XID (transaction ID, available in Replicate conflict log), timestamp or directly by searching for values in SQL_REDO.
- Obtain rowid of the row in question (in some cases, this is available directly and thus previous step is not needed)
- Query the v$logmnr_contents by this ROW_ID.
- Export the rows found to some usable and human-readable format - usually csv, xls or insert script work fine. Upload this to Dbvisit support.