OraFAQ Forum: Replication » SNAPSHOT Refresh Problem

Home » Server Options » Replication » SNAPSHOT Refresh Problem (Oracle 10.2.0.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SNAPSHOT Refresh Problem [message #507979]

Wed, 18 May 2011 12:52

lkukkalarf
Messages: 3
Registered: April 2010
Location: Richmond, VA

Junior Member

Need urgent help for the following SNAPSHOT Replication

1. Created the snapshot
2. Deployed the snapshot
3. Manual / Auto Snapshot refresh - ON (3-5 times daily)
4. There are 35 tables with Materialized views
5. Every thing works fine until 1 week ago since then one table not refreshing either manual / auto

There is no deferror
While running manual snapshot refresh i am getting the following error
dbms_snapshot.refresh('tablename',refresh_after_errors ==> TRUE);
SQL> select * from rep_error;

SQL> set pages 55
SQL> /

TABLE_NAME
------------------------------------------------------------
ERROR_DAT
---------
ERROR_MSG
------------------------------------------------------------
CCF_CIF
16-MAY-11
ORA-12008: error in materialized view refresh path
ORA-01407: cannot update ("Schema"."CCF_CIF_CHECK"."Column") to NULL
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2460
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2429

TABLE_BY_TABLE_REFRESH in progress.... Done but still have the same issue with CCF_CIF

My question ei there a way to fix this problem and How?

Please help me. I am new DBA in Advanced replication world.

Thank you in advance,

Lucky

Report message to a moderator

Re: SNAPSHOT Refresh Problem [message #507981 is a reply to message #507979]

Wed, 18 May 2011 13:08

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Quote:

ORA-01407: cannot update ("Schema"."CCF_CIF_CHECK"."Column") to NULL

Is this not clear?
Either do not insert a null or remove the not null constraint.

Regards
Michel

[Updated on: Wed, 18 May 2011 13:08]

Report message to a moderator

Re: SNAPSHOT Refresh Problem [message #507983 is a reply to message #507981]

Wed, 18 May 2011 14:02

lkukkalarf
Messages: 3
Registered: April 2010
Location: Richmond, VA

Junior Member

Mike,

Thank you for your reply.

Yes that is correct. The ORA-01407 null value

Please review the following:

Master DB1 Table1/Table2/table3/table4/table5

Snapshot SB1 table1/table2/table3/table4/table5

DBMS_SNAPSHOT.refresh -- works fine until couple
of days ago.
out of these 5 tables one table is ex. sb1.table3 (suppose)

Master - DB1.table3 has 10 rows
Snapshot SB1.table3 has 9 rows (Missing one)
When refresh from the SNAPSHOT SB1 - missing one is referenced

Now can i drop the materialized view and
recreat that one alone?
Or i have to recreate the snapshot (all the tables)???

Thank you in advance,

Lucky

Report message to a moderator

Re: SNAPSHOT Refresh Problem [message #507990 is a reply to message #507983]

Wed, 18 May 2011 14:35

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Now can i drop the materialized view and
recreat that one alone?

Yes but if you have the same not null constraint you will have the same error.
The error comes from the data not from the materialized view.

Regards
Michel

Report message to a moderator

Re: SNAPSHOT Refresh Problem [message #507991 is a reply to message #507990]

Wed, 18 May 2011 14:45

lkukkalarf
Messages: 3
Registered: April 2010
Location: Richmond, VA

Junior Member

Can i recreate only one table (problematic one)???
If Yes,
Can you give me the direction to recreate a table and refresh.
Server - Oracle 10.2.0.3 Adv replication

In this case, the system is still in sync right?

Thank you,
Lucky

Report message to a moderator

Re: SNAPSHOT Refresh Problem [message #508019 is a reply to message #507991]

Thu, 19 May 2011 01:43

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Recreate it like you first created it and if you want to be in sync then refresh all of them.

Regards
Michel

Report message to a moderator

Previous Topic:	Advanced Replication (ASR) Got Stuck
Next Topic:	Oracle streams or MVs or database triggers

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 12:11:18 CST 2025