Interrupted Load

The data load process can be re-started. Once SQL Loader reached the maximum number of errors allowed for the run, it records the position of the last record in error in the log. The restarted load begins pulling the data from the file after skipping certain number of records.

In order to continue processing interrupted load, perform the following steps:

  • Retrieve the log file. Find statement 'Specify SKIP= n when continuing the load .' in the log file.

  • Examine the log file and determine whether the problems with the data are easily amendable. Retrieve .bad and .rejected files from the same location where SQL Loader log files are found, review the problem and decide whether the entire extract has to be corrected or the upload should continue forward.

  • Re-upload the input data file to the server

  • Submit the batch once again, this time specifying the n as a skip batch parameter

Simillar approach can be utilised to recover from the partial data upload. Note that in order to provide an additional safeguard for your effort and allow recovery it is recommended to create the data extract with records ordered by the primary key. This technique will allow you to determine the portion of the reminder of the un-uploaded data and make decision on the subsequent actions. If you are uploading large volumes of data and the interruption happened toward the end of the upload process it could be beneficial to complete the upload rather than to cleanup and start over.

Consider the following scenarios:

  • Data Upload batch process ended abnormally due to an unrecoverable event such as software malfunction. In this case neither SQL loader nor the batch process are able to end gracefully and report the number of records processed.
  • Data Upload batch process encountered a legitimate error and the batch thread and the entire batch run is now in error status.
  • The SQL Loader process ended with fatal error that prevented it to log the "skipped" option properly and it is not found anywherein the SQL Loader log
If you decied to proceed with recovery, perform the following steps:

  • Query the table and determine the primary key of the last record that was sucessfully uploaded - this would be possible if the data in the input file was ordered

  • Then determine the position of this record in the data file

  • Finaly, submit the batch once again and use the skip batch parameter to resume the load from this position