HOW TO AVOID ORA-00235 ERRORS WHEN RUNNING FAST UNLOAD FOR ORACLE

Document ID : KB000028118
Last Modified Date : 14/02/2018
Show Technical Document Details

Introduction: 

HOW TO AVOID ORA-00235 ERRORS WHEN RUNNING FAST UNLOAD FOR ORACLE
ORA-00235, "control file fixed table inconsistent due to concurrent update"

Background:  

With incredible processing power of the latest computer hardware and technology, more people are choosing to run multiple FUO jobs in parallel, usually via a shell script which launches multiple jobs in the background, one after another. While this is certainly a faster way to perform unloads, it is important to understand the effect that parallel processing can have on your system, and on the Oracle server in particular.

Instructions: 

Oracle error ORA-00235 occurs when a process attempts to access shared information that is currently being updated by another process. FUO displays this error in conjunction with a SELECT of "sys.v_$datafile", when it attempts to retrieve the path names of all data files attached to the table being unloaded. This error is equivalent to the message you sometimes get when dialing a number on your telephone: "all circuits are busy now ... try your call again later." FUO waits awhile, and then tries the same select statement again (currently we retry the call every two seconds for up to three minutes before giving up, reporting the error and terminating the unload).

However, on some systems, especially those with very large, very busy database servers, the internal retry logic in FUO may not be enough to get past this error in the allotted time (three minutes). Also realize that in a parallel processing environment where FUO is the main player, the more FUO jobs launched, the busier your Oracle server will be (the problem will be aggravated). This is almost exclusively due to checkpointing:

Each unload job launched eventually performs an "ALTER SYSTEM CHECKPOINT GLOBAL" against the database, which is probably the most processing-intensive task that Oracle performs. Unfortunately, this is also the only method available for FUO to guarantee a consistent view of the table's data from a known point in time. Because Oracle queues all of FUO jobs as they request their checkpoints, and then executes each checkpoint consecutively, each additional FUO job added to the set causes the server to get even busier. This further increases the likelihood that one or more jobs will encounter this error.

If you encounter this error, try reducing the total number of FUO jobs launched at the same time.