How to Repair a Corrupt Database Block
INTRODUCTION:
=============
This Knowledge Base entry describes how it is possible to repair a
corrupt database block without scanning or dumping entire file(s).
The approach is to dump all records in a given block (if possible),
then patch out the block, rebuild all affected indices, and then
load in the dumped data.
REMEMBER ALWAYS TO HAVE A CURRENT BACKUP OF YOUR DATABASE(S).
WHY YOU NEED TO KNOW THIS:
===========================
Database corruption can cause serious problems for both applications
and the Progress Relational Database Management System. This entry
describes yet another way to correct database corruption at a block
level.
PROCEDURAL APPROACH:
====================
This entry takes advantage of the way Progress RDMS stores the
data in the database, which applies to version 2 thru 7. Progress
can contain up to 32 records per block, independent of platform.
However, physical block sizes are dependent upon the platform.
Examples:
Platform Block Size/Bytes
DOS, OS/2, NLM, VMS 512
Most unix including Sun, HP, IBM, Unix V.4 1024
Pyramid, Sequent 2048
Progress uses 6 different block formats:
* Master Block
* Index Anchor Block
* Index Block
* Data Block
* RM Block
* Free Block.
Dependent upon where the database corruption is, there are different
options:
A. If corruption is in Master or Index Anchor Blocks, BACKUP is
recommended (or Binary Dump if schema is available).
B. If Index Blocks are corrupted, a full re-index is recommended:
proutil db -B 256 -TM 32 -TB 31 -C idxbuild all
Worst case senario requires 75 per cent of original database
size to be available on disk.
C. If a SINGLE Data Block is corrupted, this entry might be able
to fix the problem.
This solution requires at least a 4GL or higher license.
Description of our approach:
1. A corruption has been found in a database block, either thru
database log with system error such as 450 or a database
scan. We know the RECID of this "bad" record.
(Note that 'wrong dbkey (1124)' errors cannot be fixed with
this approach)
2. Because we know that a block contains a maximum of 32 records,
we can try to find each record in this block, and dump the
record if it is not corrupted.
3. Once step 2 is done, we don't need the block any more, and
we will make it a free block.
4. Once it is made a free block, we need to re-build all affected
indices, and re-build the RM & free chains in order to keep
database integrity.
5. The dumped records can now be load back into the database!
This approach is particularly fast, if the records in the block all
come from the same table (or a few table - worst case is 32 different
tables) - This means we will only have to re-build the indices for
this table.
ONLINE PROCEDURES OR UTILITIES:
===============================
This procedure can be downloaded from the BBS. It is called
"dmpblk.p" and can be found in "9: PROGRESS Source Code Area."
Please read the Note information carefully in the header.
How to use the "dmpblk.p" program:
A. Create a new directory, because many files will be created,
and it is up to the user to clean up!
B. Enter the RECID/DBKEY of the record.
C. Press "Y" to delete all current .d data files (a way to escape
if you want to move the previous .d elsewhere....)
D. Next, the program will dump all records in the block to .d's -
Each table/file affected will have a <table>.d in the current
directory. This is none destructive...
E. If Progress errors out, it might not be possible to recover
any data from the corrupted block!
F. If the program completes, it will create two files:
- "iscript.txt" - contains which indices to be rebuild.
- "load.p" can be used to load the records back in.
And will display the following messages:
Files are dumped in .d - To complete deletion of block
do the following:
1. proutil <db> -C truncate bi
2. proutil <db> -C dbrpr
Select option 8. Reformat Block to a Free Block
Enter dbkey: (number entered will be displayed)
Quit
3. proutil <db> -C idxbuild < ./iscript.txt
4. pro <db> -p load.p
These messages are also written out to the "read.me" file.
/*************************************************************/
/* DATE: July 1994 */
/* COMPANY: Progress Software Technical Support */
/* NOTE: Dumps all records in a database block */
/* INFO: */
/* This program dumps all records in a given block */
/* There is a maximum of 32 records per block */
/* This program was written to be used on Unix systems */
/* Special actions are needed, if */
/* -g is used (before image on different device) */
/* -ai is used (after imaging) */
/* Be very careful when working with dbrpr utility */
/* To run this program, create a new sub-directory */
/* mkdir repair */
/* cd repair */
/* because it will create many small files, which */
/* can be deleted when procedure is done. */
/* Run program in repair-directory with: */
/* pro <full-path-db> -p dmpblk.p */
/* WARNING: REMEMBER, BACKUP IS ONLY SOLUTION IF THIS FAILS */
/*************************************************************/
DEF VAR i AS INTEGER NO-UNDO.
DEF VAR x AS INTEGER NO-UNDO.
DEF VAR ok AS LOGICAL NO-UNDO.
DEF NEW SHARED VAR n AS INTEGER NO-UNDO.
DEF VAR c AS CHAR NO-UNDO.
DEF VAR reccount AS INT NO-UNDO.
DEF STREAM S1.
DEF STREAM S2.
DEF NEW SHARED WORKFILE tables NO-UNDO
FIELD num AS INTEGER
FIELD name LIKE _file._file-name
FIELD num-rec AS INTEGER.
FORM
tables.name AT 1
tables.num-rec LABEL "Records"
reccount LABEL "Total"
WITH 10 DOWN FRAME STAT.
DISPLAY "Remember always to have a CURRENT BACKUP...."
WITH CENTERED FRAME A0.
SET i LABEL
"Enter RECID/DBKEY within the bad block" WITH FRAME A1.
IF i < 64 THEN QUIT.
SET ok LABEL
"All .d will be deleted. Press y to continue." WITH FRAME A2.
IF NOT ok THEN QUIT.
UNIX SILENT rm *.d .
HIDE FRAME A1.
HIDE FRAME A2.
ASSIGN x = i - (i MOD 32).
FOR EACH _file WHERE _file-number > 0:
c = "rec" + STRING(n, "99999") + ".p" .
CREATE tables.
ASSIGN
num = n
name = _file-name
num-rec = 0.
OUTPUT TO VALUE(c).
PUT UNFORMATTED
"DEF VAR rec AS INTEGER NO-UNDO." SKIP
"DEF SHARED VAR n AS INTEGER NO-UNDO." SKIP
"DEF SHARED WORKFILE tables NO-UNDO" SKIP
" FIELD num AS INTEGER" SKIP
" FIELD name LIKE _file._file-name" SKIP
" FIELD num-rec AS INTEGER." SKIP
"DO rec = " STRING(x) " TO " STRING(x + 31) ":" SKIP
" FOR EACH " _file-name "
WHERE RECID(" _file-name ")=rec NO-LOCK:" SKIP
" FIND FIRST tables WHERE num = n." SKIP
" OUTPUT TO " STRING(_dump-name + '.d') " APPEND." SKIP
" EXPORT " _file-name "." SKIP
" OUTPUT CLOSE." SKIP
" tables.num-rec = tables.num-rec + 1." SKIP
" END." SKIP
"END." .
OUTPUT CLOSE.
n = n + 1.
END.
OUTPUT TO TERMINAL.
DISPLAY
"There are " + STRING(n) +
" tables in your database. " FORMAT "X(60)" SKIP
"Dumping all records in block with RECID of " +
STRING(i) FORMAT "X(60)" WITH FRAME B.
PAUSE 0 BEFORE-HIDE.
DO WHILE n > 0:
n = n - 1.
FIND FIRST tables WHERE num = n.
c = "rec" + STRING(n, "99999") + ".p" .
RUN VALUE(c).
reccount = reccount + tables.num-rec .
DISPLAY tables.name tables.num-rec reccount WITH FRAME STAT.
DOWN WITH FRAME STAT.
END.
HIDE FRAME A1.
HIDE FRAME B.
HIDE FRAME STAT.
IF reccount > 0 THEN DO:
OUTPUT STREAM S1 TO iscript.txt .
OUTPUT STREAM S2 TO load.p .
PUT STREAM S1 UNFORMATTED "some" SKIP.
FOR EACH TABLES WHERE num-rec > 0:
FIND FIRST _file WHERE _file-name = tables.name NO-LOCK.
PUT STREAM S2 UNFORMATTED
"INPUT FROM " STRING(_dump-name + ".d") "." SKIP
"REPEAT: " SKIP
" CREATE " _file-name "." SKIP
" IMPORT " _file-name "." SKIP
"END." SKIP
"INPUT CLOSE." SKIP(1).
FOR EACH _index OF _file NO-LOCK:
PUT STREAM S1 UNFORMATTED _file-name SKIP _index-name SKIP.
END.
END.
PUT STREAM S1 UNFORMATTED "!" SKIP "y" SKIP "y" SKIP.
OUTPUT STREAM S1 CLOSE.
OUTPUT STREAM S2 CLOSE.
DISPLAY
"Files are dumped in .d - To complete deletion of block"
FORMAT "x(70)"
"do the following: " FORMAT "x(70)"
"1. proutil " + PDBNAME(1) + " -C truncate bi" FORMAT "x(70)"
"2. proutil " + PDBNAME(1) + " -C dbrpr" FORMAT "x(70)"
" Select option 8. Reformat Block to a Free Block"
FORMAT "x(70)"
" Enter dbkey: " + STRING(i) FORMAT "x(70)"
" Quit" FORMAT "x(70)"
"3. proutil " + PDBNAME(1) + " -C idxbuild < ./iscript.txt "
FORMAT "x(70)"
"4. pro " + PDBNAME(1) + " -p load.p" FORMAT "x(70)"
"The steps can also be found in read.me " FORMAT "x(70)"
WITH CENTERED FRAME GOODBYE.
OUTPUT TO read.me .
PUT UNFORMATTED
"1. proutil " PDBNAME(1) " -C truncate bi" SKIP
"2. proutil " PDBNAME(1) " -C dbrpr" SKIP
" Select option 8. Reformat Block to a Free Block" SKIP
" Enter dbkey: " STRING(i) SKIP
" Quit" SKIP
"3. proutil " PDBNAME(1)
" -B 256 -TB 31 -TM 31 -C idxbuild<./iscript.txt "
SKIP
"4. pro " PDBNAME(1) " -p load.p".
OUTPUT CLOSE.
PAUSE.
END.
Progress Software Technical Support Note # 13338