Replay of Log Miner SQL_UNDO fails with ORA-00001

From: Rich J <rjoralist3_at_society.servebeer.com>
Date: Fri, 20 Oct 2017 08:41:09 -0500
Message-ID: <1ee050a7bb346a2e71c2d6b41f3fb025_at_society.servebeer.com>



Hey all,

In a test 11.2.0.3 NOARCHIVELOG database, a business analyst wants to know what a particular table looked like last week. Flashback query is not enabled, but I do have enough redo logs to contain the transactions since then. So, after verifying via audit trail that there has been no DDL on this table, I fire up Log Miner via Toad and add the inactive redo logs. I then run:

SELECT
    scn,
    sql_undo
FROM v$logmnr_contents
WHERE seg_name = 'MYTABLE';

Since it's >1.2M rows (eep!), I pull the 160MB of results into (the excellent!) EM Editor, sort SCN descending, drop the SCN column and save the text file. Executing those SQLs in SQL*Plus (without COMMITs), it fails after 530K statements with ORA-00001 unique constraint error on an INSERT. FORCE LOGGING is not on, but supplemental logging is. Assuming there has been no DML on this table between the log mining and the SQL replay, am I missing something here? My pre-caffeinated sinus-pressured brain thinking...not...morning...this.......

Thanks!
Rich

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Oct 20 2017 - 15:41:09 CEST

Original text of this message