Oracle Replication Tips by Burleson

Oracle replication Stalled Jobs dbms_job

Broken jobs are easy to find and repair. However, there is another problem with dbms_jobs that is harder to deal with.  This problem is stalled or hung jobs.  For this discussion, a stalled or hung job is an Oracle background jobs process that has stopped, but did not die or record a failure.

The stalled process may or may not be holding a lock on a replicated table.  Stalled jobs are rare, but a replicated database with 180 replicated tables refreshing at intervals between 1 and 3 minutes, 24 hours a day, will eventually have a stalled process.  

When a user process hangs, the user normally exits, and the database recovers resources as needed. When a background process hangs, there is no user to log off and the database will not detect that the process has stopped.  The database will not recover the process resources because it has no indication that the process is in trouble.  Someone must kill the stalled process. 

A hung process usually cannot be killed from within the Oracle database because it is not responding.  As long as the process remains, the job will not complete the refresh and replication of those tables the job supports. In order to remove the process, you must kill it from the operating system level.  Once killed by the OS, all locks are recovered by the database and the unfinished refresh will be re-executed by another process.  The Oracle database will also respawn the background process once it has determined that it is no longer there.  

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.