Re: Corrupt indexes on a system catalog

Hi Family. This is a detailed summary of what I did to get out of a very serious problem. As with the original, it is not reading for the faint of heart. But since some of the replys I got indicated this has happened to others, it may serve as a template for others in this situation, with ideas on how to peice back together a corrupted OnLine system. Yes, it was certainly a traumatic experience. In case you are unfamiliar with this thread, please refer to the original plaint at the end of this article. In a nutshell, the OnLine system was corrupt at the partition page level, with the symptoms showing up as tables with no columns defined. The result was that I could get no info on one set of tables (18 to be exact), with the error message to the effect that the entry is not found in syscolumns. Thus, the following query: select tabname from systables where tabid not in (select tabid from syscolumns) actually returned 18 table names. I tried running dbexport on the database but it too ran up against the corruption and inconsistent behavior in the catalogs. This is consistent with some of the more sympathetic responses I got from my original post. With the help of Informix tech support to get me started, as well as a utility from iiug, I was able to unload the database (800 tables or so) with their schemas, destroy the OnLine instance, rebuild it, and reload the database. All in one (very long) evening of work. Overall, I was fortunate (other things considered) that the OnLine system here was rather small and that there was enough file-system space to do everything I needed to do. 1. For starters, there was another uncorrupted database in the instance. How uncorrupted? I could run dbexport against it with no problems. I did this. 2. Next, I ran the following SQL script against the corrupted database: select tabname from systables where tabid not in (select tabid from syscolumns) Yes, it's the above query, identifying 18 tables I would need to reconstruct from other sources, which were available on another machine. 3. In order to save all schemas that I could save, I ran the following script against the corrupted database: output to unload_schema.sh without headings select "dbschema -q -ss -d garpacv2 -t " || trim(tabname) || " create_" || trim(tabname) || ".sql;" from systables where tabid in (select tabid from syscolumns where tabid > 99) This produced a shell script in the form of: dbschema -q -ss -d garpacv2 -t stxactnr create_stxactnr.sql; dbschema -q -ss -d garpacv2 -t stxaddld create_stxaddld.sql; dbschema -q -ss -d garpacv2 -t stxaddlr create_stxaddlr.sql; . . . etc. Of course, I had to remember to chmod +x on the resulting shell script file. 4. Similarly, I needed to save all the data I could save, hence the following SQL script: output to onload_tables.sql without headers select "unload to " || trim(tabname) || ".unl" || " select * from " || trim(tabname) || ";" from systables where tabid in (select tabid from syscolumns where tabid > 99) This produced an SQL script looking like: unload to stxactnr.unl select * from stxactnr; unload to stxaddld.unl select * from stxaddld; unload to stxaddlr.unl select * from stxaddlr; unload to stxerord.unl select * from stxerord;. . . etc. 5. I ran the shell script created in step 3 and the SQL script created in step 4. Hence, a crude approximation of a dbexport. 6. Next, I needed to modify each schema script (created in step 3) to add a load command. Hence the following shell command: for SQ in create_*.sql -- For each schqma do tname=`echo $SQ | sed -e s/create_// -e s/\\.sql//` unlname=${tname}.unl load_cmd="load from $unlname insert into $tname \\;" echo $load_name >>$SQ done Accomplishment: At the end of each schema file, after the create table and create index commands, I have appended a load command. If I had not been so under the gun, I might have come up with a more clever script, with more sed, to insert the load command before the create index. (Sigh.. Dilbert's dilemma.. ;-) 7. Obtained the copy_spaces script from the iiug archives. Thanks very much to author Pete Stiglich. I *did* have to edit it to work the way I needed with my (korn) shell but these were minor - < 10 minutes. Ran copy_spaces to produce a shell-script of onspaces commands that would recreate all of my dbspaces should the need arise. ***** The need has arisen! ***** 8. Modified the ONCONFIG file (yea, I saved a copy first) for the location of the PHYSLOG and number of logical logs. 9. Moment of truth! I took the instance off line and ran oninit -i to reinitialize the root Dbspace. 10. Ran the script produced by copy_spaces, getting back all my dbspaces. 11. Ran the necessary onparams commands to get all my logs back into the logdbs, followed by the necessary ontape to activate them. Ran onmode -l a few times followed by onmode -c to make these new logs the active ones and, since I was logging (initially) to /dev/null, allowing me to drop the original 3 logs in rootdbs. My system was now configured as before. 12. Ran dbimport on the other database that had been uncorrupted. 13. Recreated the formerly corrupted database in its intended dbspace. 14. Ran the shell command loop: for SQ in create_*.sql do dbaccess garpacv2 $SQ done 15. Manually created the 18 tables that could not be unloaded. 16. Re-enabled logging on both databases. 17. Scream in triumph! Howl at the moon (except it was a dark and stormy night). Came up with a quick evasion when the building security guys came it with a butterfly net. Those 18 tables were, of course, empty. This skewed the application at first but (another furtunate accident) the data was not essential and could be regenerated by our admin folks. Permissions were also screwed up but this was an easy, if tedious, task to fix up. Thanks to those who tried to offer help but it was really to no avail and this horrid task really was the only way to get my users back in business. -- =========================== Original Message =========================== AIX 3.1.4 OnLine 7.11.UC1 Level: Not for the faint of heart. Hi Family. I have a biiig problem with a client system down. I am bcc'ing the tech support person but I think he needs some help. Several times a day, the alarm program kicks in with a complaint about a corrupt index and the af file tells me to run the appropriate oncheck command. When this succeeds, it looks OK. But it fails alot. One of the af messages got me suspicious and decided to run oncheck against systables, syscolumns and sysindexes. Here is a sample session: 43p-v3$ oncheck -cDI garpacv2:systables Validating indexes for garpacv2:informix.systables... Index tabname Index tabid ERROR:Key valu