Snapshot too old from materialized view refresh job

Question

This Oracle database has a DBMS_JOB that runs the following materialized view refresh script :

dbms_refresh.refresh(ABCD.V_POSTAL_CODES);

If I run it by hand, it's finebut if the job executes it a snapshot too old occurs. It will also around 1/5 of the time simply succeed for no apparent reasons.

Here is the error in question (Database is installed in french):

ORA-12012: erreur d'exécution automatique du travail 16220
ORA-12008: erreur dans le chemin de régénération de la vue matérialisée
ORA-01555: snapshot too old: rollback segment number 5 with name "_SYSSMU5$" too small
ORA-02063: précédant line de ORA10SRV
ORA-06512: à "SYS.DBMS_SNAPSHOT", ligne 2809
ORA-06512: à "SYS.DBMS_SNAPSHOT", ligne 3025
ORA-06512: à "SYS.DBMS_IREFRESH", ligne 689
ORA-06512: à "SYS.DBMS_REFRESH", ligne 195
ORA-06512: à ligne 1

This is the script of the materialized view in question, it calls a DB_LINK to an oracle 10g database to retrieve the view's data :

CREATE MATERIALIZED VIEW ABCD.V_POSTAL_CODES (CODE_POST)
TABLESPACE TBL_SPC
PCTUSED    0
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          200K
            NEXT             216K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH TO_DATE('09-12-2016 00:01:00','dd-mm-yyyy hh24:mi:ss')
NEXT TRUNC(SYSDATE) + 1.0007    
WITH PRIMARY KEY
AS 
/* Formatted on 2016-12-08 08:39:50 (QP5 v5.256.13226.35510) */
SELECT DISTINCT postal_code code_post
  FROM t_buildings@ora10srv
 WHERE postal_code IS NOT NULL;

You can see in the second last line of the script above, the name of the dblink. Here's the script of the dblink :

CREATE DATABASE LINK ORA10SRV
 CONNECT TO POST_SCHEMA
 IDENTIFIED BY <PWD>
 USING 'ora10srv.domain.com';

Here is the script of the table getting data pulled from by the view's refresh.

CREATE TABLE POST_SCHEMA.T_BUILDINGS
(
  BUILDING_KEY          NUMBER(10)              NOT NULL,
  SEQUENCE              NUMBER(5)               NOT NULL,
  CIVIC_NUMBER          NUMBER(10)              NOT NULL,
  APPART MENT           CHAR(5 CHAR),
  STREET_SERIE          NUMBER(10)              NOT NULL,
  STREET                VARCHAR2(60 CHAR)       NOT NULL,
  POSTAL_CODE           CHAR(7 CHAR),
  STREET_CORNER         CHAR(1 BYTE)            NOT NULL,
  STATUS                CHAR(1 CHAR)            NOT NULL,
  NOTE                  VARCHAR2(100 BYTE),
  SERIAL                NUMBER(10)              DEFAULT 0                     NOT NULL,
  IDE                   NUMBER(10)              NOT NULL
)
TABLESPACE TBL_SPC
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          504K
            NEXT             7776K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

So, now that we have all the implicated tables and objects : What I've done as far as debugging is :

• Raised the UNDO_RETENTION parameter way too high so that's out of way (20000)

• Made the undo tablespace very big.

• Tried to compile the view by hand, it works. If I try to compile it and don't commit it though it will cause a similar error if I try to run it from a different session.

• Got rid of DBMS_REFRESH and used DBMS_MVIEW.REFRESH instead with atomic_refresh set to false.

-Set the undo tablespace to GUARANTEE RETENTION

-Completely deleted and recreated all the indexes on the referenced distant database's table.

Still, I'm receiving the following error messages every nights :

ORA-12012: erreur d'exécution automatique du travail 16220
ORA-12008: erreur dans le chemin de régénération de la vue matérialisée
ORA-01555: snapshot too old: rollback segment number 11 with name "_SYSSMU11$" too small
ORA-02063: précédant line de ORA10P
ORA-06512: à "SYS.DBMS_SNAPSHOT", ligne 2809
ORA-06512: à "SYS.DBMS_SNAPSHOT", ligne 3025
ORA-06512: à "SYS.DBMS_SNAPSHOT", ligne 2994
ORA-06512: à ligne 1

I will try openning an SR with oracle. Here is what you can spot on oracle support website regarding this specific issue :

Until a consistent, reproducible test case can be developed in-house this problem remains unresolved.

The bug has been closed at this point without finding a root issue. The problem is not easy to reproduce consistently.

Answer 1

Hints

Just a quick summary of possible ways to provide you with additional information.

Job History

You might get additional information if you query the job history:

SELECT  /*+ RULE */
    A.JOB JOB#, 
    SCHEMA_USER MVIEW_OWNER,
    substr(DECODE(SUBSTR(WHAT,INSTR(WHAT,'.',1,2)+2,INSTR(WHAT,'”',1,4)-4-INSTR(WHAT,'.',1,2)+2),NULL,SUBSTR(WHAT,1,80), 
    SUBSTR(WHAT,INSTR(WHAT,'.',1,2)+2,INSTR(WHAT,'”',1,4)-4-INSTR(WHAT,'.',1,2)+2)),0,80) JOB_DESCRIPTION,
    to_char(LAST_DATE, 'yyyy-mm-dd hh24:mi:ss') LAST_RUN_DATE, 
    to_char(NEXT_DATE, 'yyyy-mm-dd hh24:mi:ss') NEXT_SCHED_RUN_DATE, 
    DECODE(BROKEN,'Y','YES','N','NO',' ') IS_BROKEN,
    FAILURES,  
    RUNNING IS_RUNNING, 
    B.SID SID 
FROM DBA_JOBS A LEFT OUTER JOIN 
    (SELECT /*+ RULE */ JOB,'YES' RUNNING,SID FROM DBA_JOBS_RUNNING ) B 
ON A.JOB = B.JOB 
ORDER BY SCHEMA_USER, JOB_DESCRIPTION;

Reference: My refresh materialized view is not refreshing – Part 1

Depending on what results you get, you might be able to look for additional information.

Rollback vs. Undo

Is your database running with Rollback Undo Segments? You might want to consider switching to the Undo Segments instead (10g+).

To enable SMU, set the UNDO_MANAGEMENT parameter to TRUE

Why?

Earlier releases of Oracle Database used rollback segments to store undo. Oracle9i introduced automatic undo management, which simplifies undo space management by eliminating the complexities associated with rollback segment management. Oracle strongly recommends (Oracle 9i and on words) to use undo tablespace (automatic undo management) to manage undo rather than rollback segments.

Reference: What is the difference between Rollback and Undo Tablespace?, section Undo vs. Rollback

Advisor Framework

If you are running in Undo Segements mode then you can retrieve advisory information about the Undo configuration from the system with the following script (Advisor Framework):

set serveroutput on

 DECLARE
  v VARCHAR2(300); 
 BEGIN
   v := dbms_undo_adv.undo_advisor(SYSDATE-1/1440, SYSDATE, 1);
   dbms_output.put_line(v);
 END;
 /

Reference: Oracle DBMS_UNDO_ADV

If you find your database doesn't have sufficient Undo Segments, then you might have to resize your UNDO tablespace to accommodate for the large amount of modifications/moves your MV is performing.