Oracle Replication Tips by Burleson

Oracle Broken Job Fix

When the NEXT_DATE of a job arrives, the job coordinator process will assign an available background jobs process to execute the commands in the WHAT column.  For replication, that is normally a refresh of a table or refresh group.  Once the job has completed execution, the LAST_DATE and LAST_SEC columns are updated and the NEXT_DATE column is updated to the next execution time.

If the job process fails to execute the job (say the master database is not available) it will increment the FAILURES column and the job will be rescheduled. The retry increment starts at 1 minute, and doubles after each subsequent failure.

In our example, after the first failure, the job will be rescheduled to retry after one minute. If it fails again, the FAILURES field will be incremented and the job will be re-executed after 2 mins, then after four mins, then eight mins, until the FAILURES field reaches 16.  Once FAILURES equals 16, the database will mark the job as broken and will not attempt execution again.

Note that a job will not wait more than the interval time before reattempting to execute the job again after a failure.  So if the interval is set to 1 minute, the job will reattempt to execute every minute until 16 failures are recorded and it is marked broken.  Also, the NEXT_DATE and NEXT_SEC columns are not updated until the job successfully executes, so you will never have more than one process attempting to execute a job at the same time.  Since the database will automatically attempt to retry jobs with failures greater

This is an excerpt from Oracle Replication By Rampant TechPress (only $19.95).  You can click here to order a copy and get instant access to the code depot:

http://www.rampant-books.com/book_2003_2_replication.htm

Get a Personal Oracle Replication Mentor

The author is now offering personal mentors for Oracle DBAs where you can have an Oracle expert right at your fingertips, anytime day or night. We work with hundreds of Oracle databases every year, so we know exactly how to quickly assist you with any Oracle replication question. 

You can get me personally, or any Oracle Certified replication DBA with more than 20 years of full-time IT experience.