ORA-01555 error when refreshing materialized views in DBMS_JOB

[Maven4Champ]

All,

With this project needing to be finished soon and an issue occuring on the local database, I am hopefuly one of you will have the answer or resolution so that I may complete this project soon....

Here is the setup..
10g database (remote)
9i database (local
DB Link from local to remote database

103 materialized views in local database that are refreshed by pulling data from dblink to remote database.

A PL/SQL procedure has been created which sets the v_failures variable = 0 and then performs a check to see if the current job has a failure and if so, inserts that value into the v_failures variable. When that reaches "1", then the procedure does nothing and closes out. If the failures are equal to "0" then it performs a DBMS_MVIEW.REFRESH procedure for each materialized view.

This worked the first time but its continually failing now with the ORA-01555 error (snapshot too old). From what I can tell, the dbms_job duration is 4 seconds and the Last_Exec is 2m 7s after it starts (8:30 PM). With that said, our DBAs working o nthe project have increased the Undo_Retention settings and assure us that shouldn't be the problem. Odd thing is, this never happened in the dev environment when we were developing/testing - only in the production environment once it got migrated.

I am looking for possible causes and possible solutions to the ORA-01555 error. A sample of the code in my procedure is below:

CREATE OR REPLACE PROCEDURE Ar_Mviews IS
V_FAILURES NUMBER := 0;
BEGIN
BEGIN
SELECT FAILURES INTO V_FAILURES FROM USER_JOBS WHERE SCHEMA_USER = 'CATEBS' AND WHAT LIKE '%DISCO_MVIEWS%';
IF V_FAILURES = 1 THEN NULL; 
ELSE 
DBMS_MVIEW.REFRESH ('AR_BATCH_RECEIPTS_V', 'C');
DBMS_OUTPUT.PUT_LINE(V_FAILURES); END IF;
END;

BEGIN
SELECT FAILURES INTO V_FAILURES FROM USER_JOBS WHERE SCHEMA_USER = 'CATEBS' AND WHAT LIKE '%DISCO_MVIEWS%';
IF V_FAILURES = 1 THEN NULL; 
ELSE 
DBMS_MVIEW.REFRESH ('AR_BATCHES_ALL_V', 'C');
DBMS_OUTPUT.PUT_LINE(V_FAILURES); END IF;
END;
END Ar_Mviews;
/

 

[SantaMufasa]

Maven,

There is nothing directly in your code that either causes what is happening, nor can you do anything directly in your code to prevent it.

But, there are a couple of things that can occur to prevent it from happening. One of the resolutions is long, tedious, and complex. You can read about the cause and the tedious solution at faq759-4436.

The quickest solution, and just as effective as Option 1 is simply to have your DBA (significantly) increase the sizes of your rollback segments. (If you take the time to read the portion of the FAQ that explains the cause of ORA-01555, then you can see that increasing the size of all rollback segments (not just one), reduces the risk or susceptibility to this error.)

So, throw some disk space at your rollback segments, and it should help prevent the problem.

BTW, what are the sizes of your rollback segments presently? I set mine to about 200 MB each (with matching OPTIMAL settings), and I don't see the problem occurring in our environment.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Maven4Champ]

Mufasa,

This is what we suspected. We have looked into the undo_retention time alloted which is 10800 in our environment we are hitting (remote database). Getting the DBA to check on the size alloted now.

 

[SantaMufasa]

Maven,

Frankly, the undo_retention is Oracle's method of achieving the effect that I mention in my FAQ. Your 10800 setting (3 hours) seems like it should be plenty to avoid ORA-01555. Also, the UNDO_RETENTION=<high number> and UNDO_MANAGEMENT=AUTO theoretically should avoid the error altogether. (Your DBA should not need to manually increase your rollback-segment sizes with these settings.

Bottom-line, with your settings, I don't know why your are seeing ORA-01555.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Maven4Champ]

Something odd I am experiencing...

I dropped the job and re-created it and it ran fine on Tuesday night in just under 3 hours.

Its interval was set for the next night (Wed) at 8:30pm and kicked off and subsequently completed but without executing/refreshing any materialized views.

Same error it appears which is coming from the remote database. So why would the job work once but then fail the second time if everything was the same?

 

[jimirvine]

If I remember correctly (I can't seem to find the exact source at the moment - I'm pretty sure it was on AskTom) Oracle 9i does not guarantee the undo_retention value. It will adhere if it can, but if it needs the block in the rbs, it's going to use it.