Refreshing read only Materialized Views

[sjwales]

Been trying to set up a database that is a copy of a new production database that has just gone live. The application owner wants data the production database left alone and anyone who is doing anything like ad hoc queries should be going against the replicated database.

On the source database I've created the materialized view logs for the tables in question.

On the target we went with the following structure for the refresh:

BUILD IMMEDIATE REFRESH SYSDATE+2/24

This should be refreshing automatically every 2 hours, I had thought.

The init paramter job_process_queues, which appears to be relevant, is set to 5.

However, after all of this, the data is not refreshing.

Any help here would be appreciated.

Source:
HP Superdome / HPUX 11.11 / Oracle 9.2.0.2

Target:
Dell 2650 / Windows Advanced Server 2000 / Oracle 9.2.0.2.1

Thanks
Steve

Steve Wales
stephen.wales@riotinto.com

 

[SantaMufasa]

Steve,

Check to see that the parameter "AQ_TM_PROCESSES" has a value of at least "1". If not, then that could be your problem. Please advise.

Dave

 

[sjwales]

Was set to 0. I'll up it to 5 and monitor and see what happens. Thanks.

Steve Wales
stephen.wales@riotinto.com

 

[sjwales]

The materialized views are still not auto refreshing - I made the changes to the parameter as requested late last night. The LAST_REFRESH_DATE was 01-AUG-03 last night. I left it till this morning, hoping that the 2 hour refresh window would kick in. It hasn't....

Any further help would be appreciated.

Steve Wales
stephen.wales@riotinto.com

 

[SantaMufasa]

Steve,

Could you please post the results of:

SELECT * from DBA_JOBS;

Thanks,

Dave

 

[sjwales]

There are 1111 Materialized Views in the database and 1111 rows in DBA_JOBS that all look a little something like this:

JOB LOG_USER PRIV_USER
---------- ------------------------------ ------------------------------
SCHEMA_USER LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE
------------------------------ --------- -------- --------- -------- ---------
NEXT_SEC TOTAL_TIME B
-------- ---------- -
INTERVAL
--------------------------------------------------------------------------------
FAILURES
----------
WHAT
--------------------------------------------------------------------------------
NLS_ENV
--------------------------------------------------------------------------------
MISC_ENV INSTANCE
---------------------------------------------------------------- ----------
2322 ELLIPSE ELLIPSE
ELLIPSE 01-AUG-03 14:14:28 02-AUG-03
14:14:28 3 N
sysdate +1
0
dbms_refresh.refresh('"ELLIPSE"."MSF001_DC0037"');
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC
Y='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LAN
GUAGE='AMERICAN' NLS_SORT='BINARY'
0102000200000000 0


I don't know why the next us showing as sysdate+1 and it is showing next_date as Aug-02 which was 4 days ago.

Thanks for your continues help!

stephen.wales@riotinto.com

 

[jee]

Just some random thoughts here...

You mentioned the job_queue_processes parameter being set to 5. What about the job_queue_interval parameter (controls how often jobs are checked to see if any are due to run)? I suspect if this is set to zero, it never happens. I think it defaults to 60 seconds or something like that.

Your job output indicates that the job is scheduled to run once every 24 hours (SYSDATE + 1 - that's the interval). It last ran on Aug 1 and was scheduled to run again on Aug 2.

Don't know if this makes a difference, but doesn't the refresh clause need a NEXT in it?

REFRESH NEXT SYSDATE+2/24

Since the Failures column on your job is set to zero, it appears that the job is just not running - not that it runs a fails.

 

[sem]

JEE: NEXT clause has been already processed and transformed to this job entry.

Regards, Dima