11.2.0.3 Issues with Materialized Views post April PSU?

From: Ryan January <rjjanuary_at_multiservice.com>
Date: Thu, 29 Aug 2013 14:15:18 -0500
Message-ID: <521F9DC6.4000102_at_multiservice.com>

Has anyone experienced ORA-600's using materialized views on the April 2013 PSU? If so, did you find a work around? Were there any relevant MOS docs you may point me to?
11.2.0.3 RAC (3 node cluster / ASM storage) on RHEL 5 using the 2.6.39-400.109.5.el5uek kernel.

After the April PSU we began having issues with ORA-600's with a multitude of differing first arguments. Oracle's lookup tool has found similar issues (while not exactly the same) and has listed them as being fixed in prior releases. Attached you'll find a very simple test case. One sample run is included below. I should also note that this first surfaced using fast refresh on commit, however it has also been verified to affect both full and fast refreshes using dbms_snapshot as well. This has been proven to be easily repeatable and surfaces when a refresh occurs after any uncommitted transaction on a table referenced within the view. After the error, and subsequent rollback, you'll notice I issue a second refresh that does complete successfully. Rolling back the patches also yields proper refresh behavior with uncommitted transactions.

Thanks,
Ryan

SQL> CREATE TABLE RJT1
    ( "T1_ID" NUMBER(10,0),
"ROW_VERSION" NUMBER(10,0) ,

      CONSTRAINT "T1_PK" PRIMARY KEY ("T1_ID") ); 2 3 4 5

Table created.

SQL> CREATE TABLE RJT2
    ( "T2_ID" NUMBER(10,0),
"ROW_VERSION" NUMBER(10,0),
"T1_ID" NUMBER(10,0),

   "T2_VALUE" NUMBER(10,0),
      CONSTRAINT "T2_PK" PRIMARY KEY ("T2_ID") );

   2 3 4 5 6 7
Table created.

SQL>
SQL> INSERT INTO RJT1 ("T1_ID","ROW_VERSION")    VALUES (1,0); INSERT INTO RJT2 ("T2_ID","ROW_VERSION","T1_ID","T2_VALUE")    VALUES (1,0,1,1); COMMIT; 2
1 row created.

SQL> SQL> 2
1 row created.

SQL> SQL> Commit complete.

SQL> CREATE MATERIALIZED VIEW LOG ON RJT1 WITH ROWID, SEQUENCE ("T1_ID")
INCLUDING NEW VALUES; 2 3 Materialized view log created.

SQL> CREATE MATERIALIZED VIEW LOG ON RJT2 WITH ROWID, SEQUENCE ("T2_ID","T1_ID","T2_VALUE") INCLUDING NEW VALUES; 2 3 Materialized view log created.

SQL> CREATE MATERIALIZED VIEW SIMPLE_MV
REFRESH FAST ON COMMIT
AS

   SELECT RJT1.T1_ID, SUM(RJT2.t2_value) T2_VALUES    FROM RJT1
   JOIN RJT2 ON RJT1.T1_ID = RJT2.T1_ID
   GROUP BY RJT1.T1_ID
; 2 3 4 5 6 7 8

Materialized view created.

SQL> INSERT INTO RJT2 ("T2_ID","ROW_VERSION","T1_ID","T2_VALUE")    VALUES (2,0,1,2); 2 1 row created.

SQL> COMMIT;
COMMIT
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path ORA-00600: internal error code, arguments: [kkzgajdl_dltgen-0], [], [], [], [],
[], [], [], [], [], [], []

SQL>
SQL> INSERT INTO RJT2 ("T2_ID","ROW_VERSION","T1_ID","T2_VALUE")    VALUES (2,0,1,2); 2 1 row created.

SQL> exec dbms_snapshot.refresh('SIMPLE_MV'); BEGIN dbms_snapshot.refresh('SIMPLE_MV'); END;

*
ERROR at line 1:
ORA-12008: error in materialized view refresh path ORA-00600: internal error code, arguments: [kkzgajdl_dltgen-0], [], [], [], [],
[], [], [], [], [], [], []

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2765
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2745
ORA-06512: at line 1

SQL> exec dbms_snapshot.refresh('SIMPLE_MV');

PL/SQL procedure successfully completed.

-- 


------------------------------------------------------------------
This email is intended solely for the use of the addressee and may
contain information that is confidential, proprietary, or both.
If you receive this email in error please immediately notify the
sender and delete the email..
------------------------------------------------------------------



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

Received on Thu Aug 29 2013 - 21:15:18 CEST