Streams Capture/LogMiner Builder Crashed with ORA-1280/1341 when it was mining redologs for "Alter table.."

From: krishna sarabu <ksarabu_at_yahoo.com>
Date: Thu, 19 Apr 2007 15:50:30 -0700 (PDT)
Message-ID: <971963.31589.qm@web56205.mail.re3.yahoo.com>

Hi,

Streams Capture/LogMiner builder crashed with the following errors when it was mining through redo logs for "alter table.." stmt. We have added a column to this table with "DEFAULT SYSDATE" and this table has 1 million rows.

Please note that this table is not part of the Streams. (db version: 10.2.0.2, Solaris 2.8)

We have increased the "_sga_size" parameter to 400MB for this capture process and restarted and ran into same issue again. The 10046 trace on LogMiner builder process showing repeated insert/select/update statements on "system.logmnr_spill$", might be related to LogMiner-builder spilling.  

 Anyone experienced this issue before? will appreciate your help.

---

Errors from alert.log

---

Thu Apr 19 05:11:05 2007
LOGMINER: Begin mining logfile: /export/oracle/pdsdata1/CNRT/logCNRT4.ora Thu Apr 19 08:27:17 2007
krvxerpt: Errors detected in process 30, role builder. Thu Apr 19 08:27:17 2007
krvxmrs: Leaving by exception: 1341
Thu Apr 19 08:27:17 2007
Errors in file /opt/sw/oracle/admin/CNRT/bdump/cnrt_p001_10071.trc: ORA-01341: LogMiner out-of-memory

---

From cnrt_p001_10071.trc trace:

---

• 2007-04-19 08:27:04.043
• SERVICE NAME:(SYS$USERS) 2007-04-19 08:27:04.021
• SESSION ID:(132.1) 2007-04-19 08:27:04.021 Spill: can not find enough to spill. amountNeeded: 3876

  ---

  ---

To reproduce this problem:

---

In any existing Streams env on Oracle 10.2.0.2:

1. As any database user, run the following stmts to create a test table with 1 million rows ..

    Create or replace Type t_RowT as Object (ID Number(28), Tdate Date, Tval Number(38,2), Tprice NUMBER);     /

    Create or replace Type t_TabT is table of t_RowT;

    create or replace Function pipeme(p_Start In Number, v_End Number) return t_TabT     PIPELINED IS
      v_Start Number(28) := p_Start;
    begin

     while v_Start <= v_End
     loop
         pipe row( t_RowT(v_Start, (sysdate-v_Start), dbms_random.value(1,v_End), dbms_random.value() ));
        v_Start := v_Start + 1;
     end loop;
     return;

    end;
    /

• Create test table STRMS_TEST_1M

    create table strms_test_1m as select * from table(pipeme(1,1000000));

• Now, add a column with datatype TIMESTAMP default SYSDATE

    alter table strms_test_1m add (EDate TIMESTAMP default SYSDATE);

• Monitor the Capture/LogMiner sessions... ---------------End Of Test----------------------------

Thanks,
Krishna        

---

Ahhh...imagining that irresistible "new car" smell?  Check outnew cars at Yahoo! Autos.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 19 2007 - 17:50:30 CDT