When I was working on a heterogeneous replication between SQL Server as publisher and Oracle as subscriber, one day I seethe replication monitor throwing an error as below.
- ORA-00001: unique constraint (Schema.TabbleName) violated (Source: MSSQL_REPL_ORACLE, Error number: 1) Get help: http://help/
I realized that the above error as the similar error (Data Inconsistency error message) that we usually get with SQL to SQL replication, Which we can fix by switching the Subscriber agent profile from Default agent profile to Continue on data consistency errors. BUT that fix didn't really helped me to fix this inconsistency error on SQL to Oracle replication. When I tried to force that fix it said " Replication Monitor could not set default agent profile for this type of agents." .
My fellow DBA helped me in fixing this issue. we created a subscriber User profile by taking the Continue on data consistency errors profile as template and changed the error code to 1 on Skip Errors parameter as shown below. Here we set the error_code to 1 as the error_code is 1 from the table MSrepl_Errors on distributor for the above error (Image 4). And give some new name to the newly create profile and force the Oracle subscriber to use that profile.
Note: Please be sure to switch the profile back to Default profile after the replication passes the problem records.
Image 4 is MS_Replerrors output showing the error_Code info.
Hope this helps!!!
Got It!!: As the error codes are different for Oracle subscription for inconsistency that is the reason why replication monitor doesn't allowed us to use the existing Continue on data consistency errors profile for this error on SQL to Oracle replication...And Same procedure (FIX) will apply for any other heterogeneous replications with SQL Server as publisher...
Hope this helps!!!
No comments:
Post a Comment