No question at this time
DBA Top 10
1 A. Kavsek 8400
2 B. Vroman 5900
3 M. Cadot 5400
4 P. Wisse 4800
5 T. Boles 3700
6 J. Schnackenberg 2100
7 . Lauri 1500
8 R. Pattyn 700
9 T. P 600
9 J. Péran 600
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48407
Total active users1519
Act. users last 24h1
Act. users last hour0
Registered user hits last week239
Registered user hits last month1082
Go up

Materialized View unavailable during refresh
Next thread: What is the future of Oracle?
Prev thread: DB-Links, DB_DOMAIN and packages

Message Score Author Date
Hello, We encounter a problem of data persisten...... Cedric Carbier Jul 17, 2018, 11:21
Cédric Oracle 9.2.0.6 ? pretty old. If you h...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Guy Lambregts Jul 18, 2018, 09:42
Hello Cédric, to add something: be aware of the...... Bruno Vroman Jul 18, 2018, 15:56
Hello Guy and Bruno, Thank you very much for yo...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Cedric Carbier Jul 18, 2018, 16:26

Follow up by mail Click here


Subject: Materialized View unavailable during refresh
Author: Cedric Carbier, France
Date: Jul 17, 2018, 11:21, 396 days ago
Os info: AIX 5.3
Oracle info: 9.2.0.6
Message: Hello,

We encounter a problem of data persistence during the refresh of a materialized view.

This materialized view gets data from a remote database, and has looks like this :


CREATE MATERIALIZED VIEW GH.GAGT_V2
TABLESPACE TS_G
NOLOGGING
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE
START WITH TO_DATE('16-07-2018 15:45:37','dd-mm-yyyy hh24:mi:ss')
NEXT (SYSDATE + 1/720)
WITH PRIMARY KEY
AS
SELECT ...
FROM "GH"."GAGT_V"@AGIRHINT;


(AGIRHINT is the remote database where we get data from)

Note that REFRESH FORCE is mandatory. Using REFRESH FAST instead would lead to ORA-12015 because of query complexity.

Furthermore, atomic refresh has been configured with the following query :


EXEC DBMS_MVIEW.REFRESH(LIST => 'GH.GAGT_V2', METHOD => 'c', ATOMIC_REFRESH => TRUE);


I believe that ATOMIC REFRESH set to true makes the MV be refreshed within a transaction (DELETE/INSERT/COMMIT).

And the problem is, that when we query this MV, we get from time to time (i.e. each time the MV is refreshed)...
- select count(*) from GH.GAGT_V2 -> 0
- select * from GH.GAGT_V2 -> nothing

Is there a way to avoid this ?

Thank you very much for your help, clues, remarks, advices, ...

Cedric
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Materialized View unavailable during refresh
Author: Guy Lambregts, Belgium
Date: Jul 18, 2018, 09:42, 395 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: Cédric

Oracle 9.2.0.6 ? pretty old.

If you have Metalink access would recommend to go through


Complete Refresh Read Consistency Behavior During Refresh and Complete Refresh Performance as Influenced by the ATOMIC_REFRESH Refresh Parameter (Doc ID 553464.1)


Looks that this doc explains your issue and provides you a workaround.

The functionality of the atomic refresh of MV' s depends on the Oracle release, if you are using 9.2 it is expected functionality

Regards
Guy
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Materialized View unavailable during refresh
Author: Bruno Vroman, Belgium
Date: Jul 18, 2018, 15:56, 395 days ago
Message: Hello Cédric,

to add something: be aware of the "cost" of the "ATOMIC REFRESH"... It can be incredibly heavier to use this compared to non-atomic refresh, so this has to be used only if it is really necessary (when there are several MV to refresh and when they have to be "consistent with each other").

Indeed: a standard refresh will use "TRUNCATE" to delete the "old data" (hence it cannot be used if several MVs have to be refreshed as a unit), vs "DELETE" when you demand "atomic_refresh" (and the consequences in UNDO and REDO)

I prefer to use a standard refresh, and to do this when the MV is not used by the application.

Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Materialized View unavailable during refresh
Author: Cedric Carbier, France
Date: Jul 18, 2018, 16:26, 395 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hello Guy and Bruno,

Thank you very much for your answers.

Indeed, the MOS Doc 553464.1 was the key of the solution, that is :
1. Create the MV without any schedule clause
2. Create a dummy MV that return no rows
3. Create a refresh group including these 2 MVs, scheduled as required (in my case, every minute).
(in the future, more MVs will be added to the group, when the developers will know what they need to replicate).

In a few words, I have been asked to find a solution to the following problem : "we need to replicate data from a source db in 11g version to a target db in 9i version. It has to be replicated on the fly, 24/7. The replicated data on target db may respond quickly at any time".

I agree that 9.2.0.6 is a very old Oracle release. Unfortunately I cannot upgrade it : the software that uses this db is not supported on higher versions.

Thank you very much once again...

Best regards,
Cedric

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here