Fixing ERROR: index "pg_class_relname_nsp_index" contains unexpected zero page at block 64 (seg46 sdw8:40004 pid=15677)

Post date: Feb 24, 2015 1:11:1 PM

DCA was down due to segment failure. DBA's recovered the segments. After DCA is brought up, developers starts getting following error when creating tables.

Sequence of events

1. DBA's saw performance issue

2. Found Segment Failure

3. Informed Developers for quick maintenance - DCA downtime of 2 hours

4. Fixed segment failure (recovered segment)

5. database brought up for developers

6. Developers starts complaining ERROR

ERROR: index "pg_class_relname_nsp_index" contains unexpected zero page at block 64 (seg46 sdw8:40004 pid=15377)

ERROR: index "pg_class_relname_nsp_index" contains unexpected zero page at block 64 (seg47 sdw8:40005 pid=16495)

Fix:

There is an issue with the index. Please execute the following commands,

reindex index pg_catalog.pg_class_relname_nsp_index;