Mani's Blog

August 5, 2011

Troubleshooting Materialized View Replication in Oracle

Filed under: Oracle — mani @ 10:31 pm

Materialized gets broken for many reasons.  Here are the simple steps to diagnose and trouble Materialized view and its replication.

  1. Verify the MV replication job is broken.
    SQL> select job from dba_jobs where broken='Y';
           JOB
    ----------
        63
  1. Check how much MV Log left to be refreshed.
    SQL> SELECT COUNT(*) FROM    MLOG$_PROFILE;
      COUNT(*)
    ----------
           235
  2. Connect as MV owner and run the job.
    SQL> begin
      2  dbms_job.run(63);
      3  end;
      4  /
    PL/SQL procedure successfully completed.
  3. Verify the broken job is fixed. It should return no rows.
    SQL> select job from dba_jobs where broken='Y';
  4. Verify that the mlog is cleared
    SQL> SELECT COUNT(*) FROM    MLOG$_PROFILE;
      COUNT(*)
    ----------
         0
  5. Sometime the replication is broken due unique violation
    Errors in file /local/opt/oracle/diag/rdbms/xnb3cont/XNB3CONT/trace/XNB3CONT_ora_10946.trc:
    ORA-12012: error on auto execute of job 63
    ORA-12008: error in materialized view refresh path
    ORA-00001: unique constraint (XN_CONTACTS.PROFILE_EMAIL_L_P) violated
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2545
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2751
    ORA-06512: at "SYS.DBMS_IREFRESH", line 685
    ORA-06512: at "SYS.DBMS_REFRESH", line 195
    ORA-06512: at line 1
    Mon Jul 11 22:06:18 2011
  6. Drop the unique index
    SQL> DROP INDEX "XN_CONTACTS"."PROFILE_EMAIL_L_P";
    Index dropped.
  7. Create non-unique index.
    SQL> CREATE INDEX "XN_CONTACTS"."PROFILE_EMAIL_L_P" ON "XN_CONTACTS"."PROFILE" (LOWER("EMAIL")) TABLESPACE "XN_CONTACT_IDX" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) parallel 8 nologging;
    Index created.
    SQL> alter INDEX "XN_CONTACTS"."PROFILE_EMAIL_L_P" noparallel;
    Index altered.
  8. Connect to MV owner and run the job that was broken.
    SQL> connect xn_contacts
    Enter password:
    Connected.
    SQL> begin
      2  dbms_job.run(63);
      3  end;
      4  /
    PL/SQL procedure successfully completed.
    SQL>
  9. In case if you need to disable dbms_job schedule.
    exec dbms_ijob.broken(63,true);
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: