Feedback
Did this article resolve your question/issue?

   

Article

How to dump around corrupted records?

« Go Back

Information

 
TitleHow to dump around corrupted records?
URL NameP26318
Article Number000137049
EnvironmentProduct: OpenEdge
Version: 10.x, 11x, 12.x
OS: All supported platforms
Question/Problem Description
How to retrieve as many records from a Table as possible after corruption?
How to dump around corrupted recid?
How to dump around corrupted rowid?
How to ASCII dump a table contents with 4GL?
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution

To 'dump around' the corruption, dump the records into a text file using 4GL/ABL, before and after the trouble record.

  • This method relies on using ROWID and tracing which ROWID has failed
  • Introducing a sort by ROWID means the code needs to read all of the records in the range in order to sort them.  
  • Reading a bad block for the sort will cause the session and database to crash (1124) and no data is displayed via BY ROWID(Table_Name) or BY ROWID(Table_Name) DESCENDING
  • Before running this solution, all Bad Blocks need to deleted using the DBRPR utility:   Possible method to fix an overlapped record: Bad record size Records overlap  

The following ABL/4GL example programs demonstrate the method.

"Table_Name" needs to be replaced by the Table in question.

/* BEFORE: dump out all data that can be read ascending up to the first corrupted record*/

OUTPUT TO "before.d".
  FOR EACH Table_Name BY ROWID(Table_Name):
    EXPORT Table_Name. 
  END.
OUTPUT CLOSE.

Errors relating to the corrupt record will appear again and the dump will stop at this point, but all the records before the bad record will be saved into "before.d" file. Rename this file and record the error reported.

/* AFTER: dump out all data that can be read descending up to the first corrupted record found*/

OUTPUT TO "after.d". 
  FOR EACH Table_Name BY ROWID(Table_Name) DESCENDING :
   EXPORT Table_Name.
  END.
OUTPUT CLOSE.

Errors relating to the corrupt record will appear again, but all the records after the bad record are saved into "after.d" file. Rename this file and record the error reported.

If there are more than one bad records, the record identifier reported in the 'before' export operation error message, will not be the same as the record reported in the 'after' export operation. If this is the case, the "before" code then needs to be re-run only in ASCENDING order to recover the maximum possible records.

The following method records the last ROWID in a text file: "befoid.txt", to keep track of where the export has succeeded up to by using:

STRING ( ROWID(Table_Name) )

/* before_skip1.p */

DEFINE STREAM data1.
DEFINE STREAM data2.
OUTPUT STREAM data1 TO "before.d".
OUTPUT STREAM data2 TO "befoid.txt".

  FOR EACH Table_Name BY ROWID(Table_Name):
    EXPORT STREAM data1 Table_Name.

  /* the following line is un-commented if you want to see all the rowids on the screen: */
  /*   DISPLAY STRING(ROWID(Table_Name)) FORMAT "x(20)". */

  /* the SEEK STREAM, resets the data2 stream back to zero each iteration,
     so that you don't end up with a really large befoid.txt file,
     we're only interested in the last ROWID that is exported */
   
       SEEK STREAM data2 TO 0.
       EXPORT STREAM data2 STRING(ROWID(Table_Name)) FORMAT "x(20)".
  END.
OUTPUT STREAM data1 CLOSE.
OUTPUT STREAM data2 CLOSE.


In following iterations each time we're skipping over the corruption point previously reported until we eventually get to the endpoint where no more data can be dumped. This export method (before_skip2.p) needs to be repeated until the errors no longer initiate and all the records that can be saved, are saved.

To skip the bad record add a "WHERE" clause to start from the last ROWID that was exported:  

"ROWID(Table_Name) > int_val_rowid".
"int_val_rowid" needs to be replaced with the last rowid written in HEX(), from the "befoid.txt" file

/* before_skip2.p */

DEFINE STREAM data1.
DEFINE STREAM data2.

/* The “APPEND” clause is placed at the end of the OUTPUT statement to make a unique "before.d" file to be used to load data back. */
OUTPUT STREAM data1 TO "before.d" APPEND.
OUTPUT STREAM data2 TO "befoid.txt".

  FOR EACH Table_Name
    WHERE ROWID(Table_Name) > TO-ROWID("int_val_rowid")
         BY ROWID(Table_Name):
         EXPORT STREAM data1 Table_Name.
             SEEK STREAM data2 TO 0.
             EXPORT STREAM data2 STRING(ROWID(Table_Name)) FORMAT "x(20)".
  END.
OUTPUT STREAM data1 CLOSE.
OUTPUT STREAM data2 CLOSE.


Another method is to dump records from each table specifically using every DBKEY in the area:

  • No index is used to find the record
  • Reading a bad block will cause the session and database to crash (1124) 
  • The next EXPORT is then started by replacing the "DO" value to start dumping from 1 block after the 1124 reported (add the number of records per block)
DEFINE VARIABLE i AS INT64 NO-UNDO.

FIND _file WHERE _file-name = "Table_Name" NO-LOCK.

OUTPUT TO Table_Name.d APPEND.

DO i = 96 TO 5346816.

FIND Table_Name NO-LOCK WHERE RECID(Table_Name) = i NO-ERROR.
        IF AVAILABLE Table_Name AND i <> INTEGER(_file._Template) THEN
            EXPORT Table_Name.
END.
OUTPUT CLOSE.

Example:
SYSTEM ERROR: Wrong dbkey in block. Found 1878151854, should be 1819200 in area 7. (1124)
  • run this code again, replacing the "DO" values to dump from 1 block after 1,819,200 (or block 56850), by adding the records per block for that area
// 1819200 + 32 = 1819232
DO i = 1819232 TO 5346816 
  • When records for this table are found by the RECID, they will be appended to the Table_Name.d output
  • Repeat until the last key is reached
  • Edit the Table_Name.d output before loading this data to a new database area structure.
  • This technique is described with an ABL code example in Article:
Workaround
With the implementation of Table Partitioning in OpenEdge 11.4:
  • RECID can no longer be used to uniquely reference data in a partitioned table as they do not contain a partition-Id component and always pertain to partition 0.
  • The exitsting ROWID information plus the table number is no longer sufficient to uniquely identify a specific record in the database.  
Uniqueness is obtained by also including the partition id associated with the record.  The internal format of a ROWID has been extended to include the partition information.  The information associated with a RECID only contains the encoded block and record within block information. For further information refer to Article:  How to recover records in a damaged partitioned table    
Notes
Last Modified Date1/13/2021 11:37 AM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.