Corrupt system catalog
Andrew Ford hit a corrupt 'distrib' index on the sysdistrib system catalog in an unlogged database on IDS 11.70 (Ubuntu, VM). oncheck -cDI reported ISAM error 105 (illegal key descriptor); the data unloaded/reloaded fine into a new database, but the old database couldn't be dropped (error 211) and the catalog index couldn't be dropped (error 511). Suggestions included oncheck repair in quiescent mode, deleting sysdistrib rows, UPDATE STATISTICS ... DROP DISTRIBUTIONS, or scrapping/reinitializing the instance; Mark Jalkiewicz said the corrupt index metadata page isn't repairable by oncheck and IBM support would be needed. The poster leaned toward unload/reinit rather than opening a support case; no confirmed fix is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
11.70.UC7IE running on Ubuntu
I've got a corrupt sysdistrib table somehow
$ oncheck -cDI breaker_pnl_old:sysdistrib
Validating indexes for breaker_pnl_old:informix.sysdistrib...
Index distrib
Could not bfget pagenum 0x5be, iserrno 105
ISAM error: illegal key descriptor (too many parts or too long).
ERROR: Index distrib for breaker_pnl_old:informix.sysdistrib is bad.
I am able to successfully unload/reload the database into a new copy of the
db, but any attempt to drop the old database with the corrupt sysdistrib
table is met with
211: Cannot read system catalog (sysdistrib).
105: ISAM error: bad isam file format.
and any attempt to drop the distrib index runs into
511: Cannot modify system catalog (sysdistrib).
Short of unloading all data and an oninit -i is there anything I can do to
drop the db with the corrupt system catalog table?
Thanks,
Andrew
Have you tried to let oncheck repair/rebuild the damaged index? Just for
curiosity sake, is this a logged database?
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Thu, Mar 14, 2013 at 10:46 PM, Andrew Ford <andrew@informix-dba.com>wrote:
> 11.70.UC7IE running on Ubuntu
>
> I've got a corrupt sysdistrib table somehow
>
> $ oncheck -cDI breaker_pnl_old:sysdistrib>
> Validating indexes for breaker_pnl_old:informix.sysdistrib...
>
> Index distrib
>
> Could not bfget pagenum 0x5be, iserrno 105
>
> ISAM error: illegal key descriptor (too many parts or too long).>
> ERROR: Index distrib for breaker_pnl_old:informix.sysdistrib is bad.
>
> I am able to successfully unload/reload the database into a new copy of the
> db, but any attempt to drop the old database with the corrupt sysdistrib
> table is met with
>
> 211: Cannot read system catalog (sysdistrib).>
> 105: ISAM error: bad isam file format.>
> and any attempt to drop the distrib index runs into
>
> 511: Cannot modify system catalog (sysdistrib).>
> Short of unloading all data and an oninit -i is there anything I can do to
> drop the db with the corrupt system catalog table?
>
> Thanks,
>
> Andrew
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8f2346598d9c5404d7ee7b8d
I'm not given that option by oncheck, it just say lets me know there is
corruption but doesn't ask me if I'd like to fix it.
This is NOT a logged database.
Andrew
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
Kagel
Sent: Thursday, March 14, 2013 10:46 PM
To: ids@iiug.org
Subject: Re: Corrupt system catalog [29754]
Have you tried to let oncheck repair/rebuild the damaged index? Just for
curiosity sake, is this a logged database?
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Thu, Mar 14, 2013 at 10:46 PM, Andrew Ford
<andrew@informix-dba.com>wrote:
> 11.70.UC7IE running on Ubuntu
>
> I've got a corrupt sysdistrib table somehow
>
> $ oncheck -cDI breaker_pnl_old:sysdistrib>
> Validating indexes for breaker_pnl_old:informix.sysdistrib...
>
> Index distrib
>
> Could not bfget pagenum 0x5be, iserrno 105
>
> ISAM error: illegal key descriptor (too many parts or too long).>
> ERROR: Index distrib for breaker_pnl_old:informix.sysdistrib is bad.
>
> I am able to successfully unload/reload the database into a new copy
> of the db, but any attempt to drop the old database with the corrupt
> sysdistrib table is met with
>
> 211: Cannot read system catalog (sysdistrib).>
> 105: ISAM error: bad isam file format.>
> and any attempt to drop the distrib index runs into
>
> 511: Cannot modify system catalog (sysdistrib).>
> Short of unloading all data and an oninit -i is there anything I can
> do to drop the db with the corrupt system catalog table?
>
> Thanks,
>
> Andrew
>
>
>
>
****************************************************************************
***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8f2346598d9c5404d7ee7b8d
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
When a corrupt index was discovered by oncheck or a message in the
online.log, I would use dbaccess to drop and recreate the index ... just to
be on the safe side. Doing so also allows u to relocate the index if you
so desire. If course, if it is a PK or a FK, things can be a little
difficult ... so goes the life of a DBA.
If you can perform a select using the index and it doesn't return an error
either via dbaccess or through the online.log, I wouldn't consider it to be
an emergency but something to take care of at the next scheduled downtime.
Take care.
Clifton
On Fri, Mar 15, 2013 at 2:43 PM, Andrew Ford <andrew@informix-dba.com>wrote:
> I'm not given that option by oncheck, it just say lets me know there is
> corruption but doesn't ask me if I'd like to fix it.
>
> This is NOT a logged database.
>
> Andrew
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> Kagel
> Sent: Thursday, March 14, 2013 10:46 PM
> To: ids@iiug.org
> Subject: Re: Corrupt system catalog [29754]
>
> Have you tried to let oncheck repair/rebuild the damaged index? Just for
> curiosity sake, is this a logged database?
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or
> by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Thu, Mar 14, 2013 at 10:46 PM, Andrew Ford
> <andrew@informix-dba.com>wrote:
>
> > 11.70.UC7IE running on Ubuntu
> >
> > I've got a corrupt sysdistrib table somehow
> >
> > $ oncheck -cDI breaker_pnl_old:sysdistrib> >
> > Validating indexes for breaker_pnl_old:informix.sysdistrib...
> >
> > Index distrib
> >
> > Could not bfget pagenum 0x5be, iserrno 105
> >
> > ISAM error: illegal key descriptor (too many parts or too long).> >
> > ERROR: Index distrib for breaker_pnl_old:informix.sysdistrib is bad.
> >
> > I am able to successfully unload/reload the database into a new copy
> > of the db, but any attempt to drop the old database with the corrupt
> > sysdistrib table is met with
> >
> > 211: Cannot read system catalog (sysdistrib).> >
> > 105: ISAM error: bad isam file format.> >
> > and any attempt to drop the distrib index runs into
> >
> > 511: Cannot modify system catalog (sysdistrib).> >
> > Short of unloading all data and an oninit -i is there anything I can
> > do to drop the db with the corrupt system catalog table?
> >
> > Thanks,
> >
> > Andrew
> >
> >
> >
> >
>
> ****************************************************************************
> ***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --e89a8f2346598d9c5404d7ee7b8d
>
>
> ****************************************************************************
> ***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Take care.
Clifton
This communication may contain privileged or other confidential
information. If you have received it in error, please advise the sender by
reply email and immediately delete the message and any attachments without
copying or disclosing the contents. Thank you.
Consider the environment. Please don't print this e-mail unless you need to.
--047d7ba97d2033b4a004d7f7e6d8
Try running it in quiescent mode with the -y option
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Andrew
Ford
Sent: Friday, March 15, 2013 10:43 AM
To: ids@iiug.org
Subject: RE: Corrupt system catalog [29757]
I'm not given that option by oncheck, it just say lets me know there is
corruption but doesn't ask me if I'd like to fix it.
This is NOT a logged database.
Andrew
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel
Sent: Thursday, March 14, 2013 10:46 PM
To: ids@iiug.org
Subject: Re: Corrupt system catalog [29754]
Have you tried to let oncheck repair/rebuild the damaged index? Just for
curiosity sake, is this a logged database?
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Thu, Mar 14, 2013 at 10:46 PM, Andrew Ford
<andrew@informix-dba.com>wrote:
> 11.70.UC7IE running on Ubuntu
>
> I've got a corrupt sysdistrib table somehow
>
> $ oncheck -cDI breaker_pnl_old:sysdistrib>
> Validating indexes for breaker_pnl_old:informix.sysdistrib...
>
> Index distrib
>
> Could not bfget pagenum 0x5be, iserrno 105
>
> ISAM error: illegal key descriptor (too many parts or too long).>
> ERROR: Index distrib for breaker_pnl_old:informix.sysdistrib is bad.
>
> I am able to successfully unload/reload the database into a new copy
> of the db, but any attempt to drop the old database with the corrupt
> sysdistrib table is met with
>
> 211: Cannot read system catalog (sysdistrib).>
> 105: ISAM error: bad isam file format.>
> and any attempt to drop the distrib index runs into
>
> 511: Cannot modify system catalog (sysdistrib).>
> Short of unloading all data and an oninit -i is there anything I can
> do to drop the db with the corrupt system catalog table?
>
> Thanks,
>
> Andrew
>
>
>
>
****************************************************************************
***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8f2346598d9c5404d7ee7b8d
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
When I attempt to drop the corrupt index I get
511: Cannot modify system catalog (sysdistrib).
Because this is a system catalog that is corrupt.
The data has been unloaded and the database has been rebuilt and reloaded so
this isn't an emergency.
I just can't drop the old version of the database that has the corrupt
system catalog table and each time I take a backup it whines about finding
corrupt pages like a little baby.
Andrew
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Clifton Bean
Sent: Friday, March 15, 2013 10:01 AM
To: ids@iiug.org
Subject: Re: Corrupt system catalog [29758]
When a corrupt index was discovered by oncheck or a message in the
online.log, I would use dbaccess to drop and recreate the index ... just to
be on the safe side. Doing so also allows u to relocate the index if you so
desire. If course, if it is a PK or a FK, things can be a little difficult
... so goes the life of a DBA.
If you can perform a select using the index and it doesn't return an error
either via dbaccess or through the online.log, I wouldn't consider it to be
an emergency but something to take care of at the next scheduled downtime.
Take care.
Clifton
On Fri, Mar 15, 2013 at 2:43 PM, Andrew Ford <andrew@informix-dba.com>wrote:
> I'm not given that option by oncheck, it just say lets me know there
> is corruption but doesn't ask me if I'd like to fix it.
>
> This is NOT a logged database.
>
> Andrew
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art Kagel
> Sent: Thursday, March 14, 2013 10:46 PM
> To: ids@iiug.org
> Subject: Re: Corrupt system catalog [29754]
>
> Have you tried to let oncheck repair/rebuild the damaged index? Just
> for curiosity sake, is this a logged database?
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own
> opinions and do not reflect on my employer, Advanced DataTools, the
> IIUG, nor any other organization with which I am associated either
> explicitly, implicitly, or by inference. Neither do those opinions
> reflect those of other individuals affiliated with any entity with
> which I am affiliated nor those of the entities themselves.
>
> On Thu, Mar 14, 2013 at 10:46 PM, Andrew Ford
> <andrew@informix-dba.com>wrote:
>
> > 11.70.UC7IE running on Ubuntu
> >
> > I've got a corrupt sysdistrib table somehow
> >
> > $ oncheck -cDI breaker_pnl_old:sysdistrib> >
> > Validating indexes for breaker_pnl_old:informix.sysdistrib...
> >
> > Index distrib
> >
> > Could not bfget pagenum 0x5be, iserrno 105
> >
> > ISAM error: illegal key descriptor (too many parts or too long).> >
> > ERROR: Index distrib for breaker_pnl_old:informix.sysdistrib is bad.
> >
> > I am able to successfully unload/reload the database into a new copy
> > of the db, but any attempt to drop the old database with the corrupt
> > sysdistrib table is met with
> >
> > 211: Cannot read system catalog (sysdistrib).> >
> > 105: ISAM error: bad isam file format.> >
> > and any attempt to drop the distrib index runs into
> >
> > 511: Cannot modify system catalog (sysdistrib).> >
> > Short of unloading all data and an oninit -i is there anything I can
> > do to drop the db with the corrupt system catalog table?
> >
> > Thanks,
> >
> > Andrew
> >
> >
> >
> >
>
> **********************************************************************
> ******
> ***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --e89a8f2346598d9c5404d7ee7b8d
>
>
> **********************************************************************
> ******
> ***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
****************************************************************************
***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Take care.
Clifton
This communication may contain privileged or other confidential information.
If you have received it in error, please advise the sender by reply email
and immediately delete the message and any attachments without copying or
disclosing the contents. Thank you.
Consider the environment. Please don't print this e-mail unless you need to.
--047d7ba97d2033b4a004d7f7e6d8
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
OK, that fits with my experience. Only unlogged databases experience index
corruption except in extreme circumstances.
One thing you could try is to delete all the rows in the sysdistrib table
and see if that lets the server 'repair' the index structure.
So, if you can unload the data, the only option may be to reload it into a
clean new database. If there aren' t too many other databases I'd scrap
the instance and start over. Alternatively if you have support on this
server, IBM may be able to make the database "go away".
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Fri, Mar 15, 2013 at 10:43 AM, Andrew Ford <andrew@informix-dba.com>wrote:
> I'm not given that option by oncheck, it just say lets me know there is
> corruption but doesn't ask me if I'd like to fix it.
>
> This is NOT a logged database.
>
> Andrew
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> Kagel
> Sent: Thursday, March 14, 2013 10:46 PM
> To: ids@iiug.org
> Subject: Re: Corrupt system catalog [29754]
>
> Have you tried to let oncheck repair/rebuild the damaged index? Just for
> curiosity sake, is this a logged database?
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or
> by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Thu, Mar 14, 2013 at 10:46 PM, Andrew Ford
> <andrew@informix-dba.com>wrote:
>
> > 11.70.UC7IE running on Ubuntu
> >
> > I've got a corrupt sysdistrib table somehow
> >
> > $ oncheck -cDI breaker_pnl_old:sysdistrib> >
> > Validating indexes for breaker_pnl_old:informix.sysdistrib...
> >
> > Index distrib
> >
> > Could not bfget pagenum 0x5be, iserrno 105
> >
> > ISAM error: illegal key descriptor (too many parts or too long).> >
> > ERROR: Index distrib for breaker_pnl_old:informix.sysdistrib is bad.
> >
> > I am able to successfully unload/reload the database into a new copy
> > of the db, but any attempt to drop the old database with the corrupt
> > sysdistrib table is met with
> >
> > 211: Cannot read system catalog (sysdistrib).> >
> > 105: ISAM error: bad isam file format.> >
> > and any attempt to drop the distrib index runs into
> >
> > 511: Cannot modify system catalog (sysdistrib).> >
> > Short of unloading all data and an oninit -i is there anything I can
> > do to drop the db with the corrupt system catalog table?
> >
> > Thanks,
> >
> > Andrew
> >
> >
> >
> >
>
> ****************************************************************************
> ***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --e89a8f2346598d9c5404d7ee7b8d
>
>
> ****************************************************************************
> ***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec54b48d01379e704d7f81c7b
You can't do any of that with a catalog table;
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Fri, Mar 15, 2013 at 11:00 AM, Clifton Bean <clifton.m.bean@gmail.com>wrote:
> When a corrupt index was discovered by oncheck or a message in the
> online.log, I would use dbaccess to drop and recreate the index ... just to
> be on the safe side. Doing so also allows u to relocate the index if you
> so desire. If course, if it is a PK or a FK, things can be a little
> difficult ... so goes the life of a DBA.
>
> If you can perform a select using the index and it doesn't return an error
> either via dbaccess or through the online.log, I wouldn't consider it to be
> an emergency but something to take care of at the next scheduled downtime.
>
> Take care.
> Clifton
>
> On Fri, Mar 15, 2013 at 2:43 PM, Andrew Ford <andrew@informix-dba.com
> >wrote:
>
> > I'm not given that option by oncheck, it just say lets me know there is
> > corruption but doesn't ask me if I'd like to fix it.
> >
> > This is NOT a logged database.
> >
> > Andrew
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art
> > Kagel
> > Sent: Thursday, March 14, 2013 10:46 PM
> > To: ids@iiug.org
> > Subject: Re: Corrupt system catalog [29754]
> >
> > Have you tried to let oncheck repair/rebuild the damaged index? Just for
> > curiosity sake, is this a logged database?
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and
> > do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other
> > organization with which I am associated either explicitly, implicitly, or
> > by
> > inference. Neither do those opinions reflect those of other individuals
> > affiliated with any entity with which I am affiliated nor those of the
> > entities themselves.
> >
> > On Thu, Mar 14, 2013 at 10:46 PM, Andrew Ford
> > <andrew@informix-dba.com>wrote:
> >
> > > 11.70.UC7IE running on Ubuntu
> > >
> > > I've got a corrupt sysdistrib table somehow
> > >
> > > $ oncheck -cDI breaker_pnl_old:sysdistrib> > >
> > > Validating indexes for breaker_pnl_old:informix.sysdistrib...
> > >
> > > Index distrib
> > >
> > > Could not bfget pagenum 0x5be, iserrno 105
> > >
> > > ISAM error: illegal key descriptor (too many parts or too long).> > >
> > > ERROR: Index distrib for breaker_pnl_old:informix.sysdistrib is bad.
> > >
> > > I am able to successfully unload/reload the database into a new copy
> > > of the db, but any attempt to drop the old database with the corrupt
> > > sysdistrib table is met with
> > >
> > > 211: Cannot read system catalog (sysdistrib).> > >
> > > 105: ISAM error: bad isam file format.> > >
> > > and any attempt to drop the distrib index runs into
> > >
> > > 511: Cannot modify system catalog (sysdistrib).> > >
> > > Short of unloading all data and an oninit -i is there anything I can
> > > do to drop the db with the corrupt system catalog table?
> > >
> > > Thanks,
> > >
> > > Andrew
> > >
> > >
> > >
> > >
> >
> >
> ****************************************************************************
> > ***
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --e89a8f2346598d9c5404d7ee7b8d
> >
> >
> >
> ****************************************************************************
> > ***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Take care.
> Clifton
>
> This communication may contain privileged or other confidential
> information. If you have received it in error, please advise the sender by
> reply email and immediately delete the message and any attachments without
> copying or disclosing the contents. Thank you.
>
> Consider the environment. Please don't print this e-mail unless you need
> to.
>
> --047d7ba97d2033b4a004d7f7e6d8
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec5555494470a2504d7f8453a
ISAM error: illegal key descriptor (too many parts or too long).
The index meta data page that contains information for the index keys for the
sysdistrib table is corrupted .. and oncheck repair option cannot repair that
since the corruption is not "data related" like missing rowid etc. Since you
cannot drop the database (corruption is in the sys catalogs) your going to
need tech support to dial in and assist you in dropping the database or
removing the corruption which from my experience is straight forward and quick.
Mark
Thanks everyone for the input.
Something fishy is going on. They had 3 corrupt indexes in one database and
at least one corrupt sysdistrib index in another all showing up at about the
same time and very quickly, this instance has been up for no more than a
week.
The instance is running on a virtual machine that I have no control over,
I'm thinking something happened at the disk level (cooked chunks) while the
engine was online.
I haven't dealt with unlogged databases in a long time. I have experienced
some index corruption using them, but not as much as this and not as quickly
as this.
I'm going to try the delete from sysdistrib trick to see if that will allow
me to drop the database with corruption. I doubt we will call in IBM support
on this Innovator-C instance. I can unload the data/reinit the
instance/recreate the dbs/reload the data in the time it would take to get
the paper work together.
Andrew
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
Kagel
Sent: Friday, March 15, 2013 10:16 AM
To: ids@iiug.org
Subject: Re: Corrupt system catalog [29761]
OK, that fits with my experience. Only unlogged databases experience index
corruption except in extreme circumstances.
One thing you could try is to delete all the rows in the sysdistrib table
and see if that lets the server 'repair' the index structure.
So, if you can unload the data, the only option may be to reload it into a
clean new database. If there aren' t too many other databases I'd scrap the
instance and start over. Alternatively if you have support on this server,
IBM may be able to make the database "go away".
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Fri, Mar 15, 2013 at 10:43 AM, Andrew Ford
<andrew@informix-dba.com>wrote:
> I'm not given that option by oncheck, it just say lets me know there
> is corruption but doesn't ask me if I'd like to fix it.
>
> This is NOT a logged database.
>
> Andrew
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art Kagel
> Sent: Thursday, March 14, 2013 10:46 PM
> To: ids@iiug.org
> Subject: Re: Corrupt system catalog [29754]
>
> Have you tried to let oncheck repair/rebuild the damaged index? Just
> for curiosity sake, is this a logged database?
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own
> opinions and do not reflect on my employer, Advanced DataTools, the
> IIUG, nor any other organization with which I am associated either
> explicitly, implicitly, or by inference. Neither do those opinions
> reflect those of other individuals affiliated with any entity with
> which I am affiliated nor those of the entities themselves.
>
> On Thu, Mar 14, 2013 at 10:46 PM, Andrew Ford
> <andrew@informix-dba.com>wrote:
>
> > 11.70.UC7IE running on Ubuntu
> >
> > I've got a corrupt sysdistrib table somehow
> >
> > $ oncheck -cDI breaker_pnl_old:sysdistrib> >
> > Validating indexes for breaker_pnl_old:informix.sysdistrib...
> >
> > Index distrib
> >
> > Could not bfget pagenum 0x5be, iserrno 105
> >
> > ISAM error: illegal key descriptor (too many parts or too long).> >
> > ERROR: Index distrib for breaker_pnl_old:informix.sysdistrib is bad.
> >
> > I am able to successfully unload/reload the database into a new copy
> > of the db, but any attempt to drop the old database with the corrupt
> > sysdistrib table is met with
> >
> > 211: Cannot read system catalog (sysdistrib).> >
> > 105: ISAM error: bad isam file format.> >
> > and any attempt to drop the distrib index runs into
> >
> > 511: Cannot modify system catalog (sysdistrib).> >
> > Short of unloading all data and an oninit -i is there anything I can
> > do to drop the db with the corrupt system catalog table?
> >
> > Thanks,
> >
> > Andrew
> >
> >
> >
> >
>
> **********************************************************************
> ******
> ***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --e89a8f2346598d9c5404d7ee7b8d
>
>
> **********************************************************************
> ******
> ***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
****************************************************************************
***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec54b48d01379e704d7f81c7b
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi Andrew ,
Just for curiosity , what happen if try run update statistics low drop
distribution ?
2013/3/15 Andrew Ford <andrew@informix-dba.com>
> Thanks everyone for the input.
>
> Something fishy is going on. They had 3 corrupt indexes in one database and
> at least one corrupt sysdistrib index in another all showing up at about
> the
> same time and very quickly, this instance has been up for no more than a
> week.
>
> The instance is running on a virtual machine that I have no control over,
> I'm thinking something happened at the disk level (cooked chunks) while the
> engine was online.
>
> I haven't dealt with unlogged databases in a long time. I have experienced
> some index corruption using them, but not as much as this and not as
> quickly
> as this.
>
> I'm going to try the delete from sysdistrib trick to see if that will allow
> me to drop the database with corruption. I doubt we will call in IBM
> support
> on this Innovator-C instance. I can unload the data/reinit the
> instance/recreate the dbs/reload the data in the time it would take to get
> the paper work together.
>
> Andrew
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> Kagel
> Sent: Friday, March 15, 2013 10:16 AM
> To: ids@iiug.org
> Subject: Re: Corrupt system catalog [29761]
>
> OK, that fits with my experience. Only unlogged databases experience index
> corruption except in extreme circumstances.
>
> One thing you could try is to delete all the rows in the sysdistrib table
> and see if that lets the server 'repair' the index structure.
>
> So, if you can unload the data, the only option may be to reload it into a
> clean new database. If there aren' t too many other databases I'd scrap the
> instance and start over. Alternatively if you have support on this server,
> IBM may be able to make the database "go away".
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or
> by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Fri, Mar 15, 2013 at 10:43 AM, Andrew Ford
> <andrew@informix-dba.com>wrote:
>
> > I'm not given that option by oncheck, it just say lets me know there
> > is corruption but doesn't ask me if I'd like to fix it.
> >
> > This is NOT a logged database.
> >
> > Andrew
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > Art Kagel
> > Sent: Thursday, March 14, 2013 10:46 PM
> > To: ids@iiug.org
> > Subject: Re: Corrupt system catalog [29754]
> >
> > Have you tried to let oncheck repair/rebuild the damaged index? Just
> > for curiosity sake, is this a logged database?
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own
> > opinions and do not reflect on my employer, Advanced DataTools, the
> > IIUG, nor any other organization with which I am associated either
> > explicitly, implicitly, or by inference. Neither do those opinions
> > reflect those of other individuals affiliated with any entity with
> > which I am affiliated nor those of the entities themselves.
> >
> > On Thu, Mar 14, 2013 at 10:46 PM, Andrew Ford
> > <andrew@informix-dba.com>wrote:
> >
> > > 11.70.UC7IE running on Ubuntu
> > >
> > > I've got a corrupt sysdistrib table somehow
> > >
> > > $ oncheck -cDI breaker_pnl_old:sysdistrib> > >
> > > Validating indexes for breaker_pnl_old:informix.sysdistrib...
> > >
> > > Index distrib
> > >
> > > Could not bfget pagenum 0x5be, iserrno 105
> > >
> > > ISAM error: illegal key descriptor (too many parts or too long).> > >
> > > ERROR: Index distrib for breaker_pnl_old:informix.sysdistrib is bad.
> > >
> > > I am able to successfully unload/reload the database into a new copy
> > > of the db, but any attempt to drop the old database with the corrupt
> > > sysdistrib table is met with
> > >
> > > 211: Cannot read system catalog (sysdistrib).> > >
> > > 105: ISAM error: bad isam file format.> > >
> > > and any attempt to drop the distrib index runs into
> > >
> > > 511: Cannot modify system catalog (sysdistrib).> > >
> > > Short of unloading all data and an oninit -i is there anything I can
> > > do to drop the db with the corrupt system catalog table?
> > >
> > > Thanks,
> > >
> > > Andrew
> > >
> > >
> > >
> > >
> >
> > **********************************************************************
> > ******
> > ***
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --e89a8f2346598d9c5404d7ee7b8d
> >
> >
> > **********************************************************************
> > ******
> > ***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
> ****************************************************************************
> ***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --bcaec54b48d01379e704d7f81c7b
>
>
> ****************************************************************************
> ***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--f46d0438eddbf9a9e004d7f9a0a6
While at Bloomberg, we used to have to rebuild an index or two ever time
the servers were bounced or crashed. We lost 6 indexes (2 each) on the
three largest tables in our news database on all three news servers one
weekend. I worked all day that Sunday until 5AM on Monday to get on server
back online in time for the markets in the morning and left to get some
sleep with the last indexes building (one on a second server and two on
the last one). Was awakened at 9AM by a screaming development director
asking "Why the F&%$ are you home when I have only one news server online?
Get your F&%$ing A$$ in here and fix it!" I sleepily explained the
situation slammed the phone down and got dressed. Arrived to an abashed
uber boss apologizing for not knowing that I'd been in the office for
almost 28 hours before going home and by-the-way I was right the second
news server had come back online before I got back into the office. Two
weeks later they finally let me turn logging on for all of our databases on
45 server instances and I only lost one other index in the following ten
years even though data volumes, transaction rates, and numbers of ultimate
users had more than doubled in that time.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Fri, Mar 15, 2013 at 12:34 PM, Andrew Ford <andrew@informix-dba.com>wrote:
> Thanks everyone for the input.
>
> Something fishy is going on. They had 3 corrupt indexes in one database and
> at least one corrupt sysdistrib index in another all showing up at about
> the
> same time and very quickly, this instance has been up for no more than a
> week.
>
> The instance is running on a virtual machine that I have no control over,
> I'm thinking something happened at the disk level (cooked chunks) while the
> engine was online.
>
> I haven't dealt with unlogged databases in a long time. I have experienced
> some index corruption using them, but not as much as this and not as
> quickly
> as this.
>
> I'm going to try the delete from sysdistrib trick to see if that will allow
> me to drop the database with corruption. I doubt we will call in IBM
> support
> on this Innovator-C instance. I can unload the data/reinit the
> instance/recreate the dbs/reload the data in the time it would take to get
> the paper work together.
>
> Andrew
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> Kagel
> Sent: Friday, March 15, 2013 10:16 AM
> To: ids@iiug.org
> Subject: Re: Corrupt system catalog [29761]
>
> OK, that fits with my experience. Only unlogged databases experience index
> corruption except in extreme circumstances.
>
> One thing you could try is to delete all the rows in the sysdistrib table
> and see if that lets the server 'repair' the index structure.
>
> So, if you can unload the data, the only option may be to reload it into a
> clean new database. If there aren' t too many other databases I'd scrap the
> instance and start over. Alternatively if you have support on this server,
> IBM may be able to make the database "go away".
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or
> by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Fri, Mar 15, 2013 at 10:43 AM, Andrew Ford
> <andrew@informix-dba.com>wrote:
>
> > I'm not given that option by oncheck, it just say lets me know there
> > is corruption but doesn't ask me if I'd like to fix it.
> >
> > This is NOT a logged database.
> >
> > Andrew
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > Art Kagel
> > Sent: Thursday, March 14, 2013 10:46 PM
> > To: ids@iiug.org
> > Subject: Re: Corrupt system catalog [29754]
> >
> > Have you tried to let oncheck repair/rebuild the damaged index? Just
> > for curiosity sake, is this a logged database?
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own
> > opinions and do not reflect on my employer, Advanced DataTools, the
> > IIUG, nor any other organization with which I am associated either
> > explicitly, implicitly, or by inference. Neither do those opinions
> > reflect those of other individuals affiliated with any entity with
> > which I am affiliated nor those of the entities themselves.
> >
> > On Thu, Mar 14, 2013 at 10:46 PM, Andrew Ford
> > <andrew@informix-dba.com>wrote:
> >
> > > 11.70.UC7IE running on Ubuntu
> > >
> > > I've got a corrupt sysdistrib table somehow
> > >
> > > $ oncheck -cDI breaker_pnl_old:sysdistrib> > >
> > > Validating indexes for breaker_pnl_old:informix.sysdistrib...
> > >
> > > Index distrib
> > >
> > > Could not bfget pagenum 0x5be, iserrno 105
> > >
> > > ISAM error: illegal key descriptor (too many parts or too long).> > >
> > > ERROR: Index distrib for breaker_pnl_old:informix.sysdistrib is bad.
> > >
> > > I am able to successfully unload/reload the database into a new copy
> > > of the db, but any attempt to drop the old database with the corrupt
> > > sysdistrib table is met with
> > >
> > > 211: Cannot read system catalog (sysdistrib).> > >
> > > 105: ISAM error: bad isam file format.> > >
> > > and any attempt to drop the distrib index runs into
> > >
> > > 511: Cannot modify system catalog (sysdistrib).> > >
> > > Short of unloading all data and an oninit -i is there anything I can
> > > do to drop the db with the corrupt system catalog table?
> > >
> > > Thanks,
> > >
> > > Andrew
> > >
> > >
> > >
> > >
> >
> > **********************************************************************
> > ******
> > ***
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --e89a8f2346598d9c5404d7ee7b8d
> >
> >
> > **********************************************************************
> > ******
> > ***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
> ****************************************************************************
> ***
> > Forum Note: Use @@DQ@
Question for everyone.
How come you use unlogged databases for something that is not scratch space
e.g.
ETL staging area?
David.
On 15 March 2013 at 18:24 Art Kagel <art.kagel@gmail.com> wrote:
> While at Bloomberg, we used to have to rebuild an index or two ever time
> the servers were bounced or crashed. We lost 6 indexes (2 each) on the
> three largest tables in our news database on all three news servers one
> weekend. I worked all day that Sunday until 5AM on Monday to get on server
> back online in time for the markets in the morning and left to get some
> sleep with the last indexes building (one on a second server and two on
> the last one). Was awakened at 9AM by a screaming development director
> asking "Why the F&%$ are you home when I have only one news server online?
> Get your F&%$ing A$$ in here and fix it!" I sleepily explained the
> situation slammed the phone down and got dressed. Arrived to an abashed
> uber boss apologizing for not knowing that I'd been in the office for
> almost 28 hours before going home and by-the-way I was right the second
> news server had come back online before I got back into the office. Two
> weeks later they finally let me turn logging on for all of our databases on
> 45 server instances and I only lost one other index in the following ten
> years even though data volumes, transaction rates, and numbers of ultimate
> users had more than doubled in that time.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated nor
> those of the entities themselves.
>
> On Fri, Mar 15, 2013 at 12:34 PM, Andrew Ford <andrew@informix-dba.com>wrote:
>
> > Thanks everyone for the input.
> >
> > Something fishy is going on. They had 3 corrupt indexes in one database and
> > at least one corrupt sysdistrib index in another all showing up at about
> > the
> > same time and very quickly, this instance has been up for no more than a
> > week.
> >
> > The instance is running on a virtual machine that I have no control over,
> > I'm thinking something happened at the disk level (cooked chunks) while the
> > engine was online.
> >
> > I haven't dealt with unlogged databases in a long time. I have experienced
> > some index corruption using them, but not as much as this and not as
> > quickly
> > as this.
> >
> > I'm going to try the delete from sysdistrib trick to see if that will allow
> > me to drop the database with corruption. I doubt we will call in IBM
> > support
> > on this Innovator-C instance. I can unload the data/reinit the
> > instance/recreate the dbs/reload the data in the time it would take to get
> > the paper work together.
> >
> > Andrew
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> > Kagel
> > Sent: Friday, March 15, 2013 10:16 AM
> > To: ids@iiug.org
> > Subject: Re: Corrupt system catalog [29761]
> >
> > OK, that fits with my experience. Only unlogged databases experience index
> > corruption except in extreme circumstances.
> >
> > One thing you could try is to delete all the rows in the sysdistrib table
> > and see if that lets the server 'repair' the index structure.
> >
> > So, if you can unload the data, the only option may be to reload it into a
> > clean new database. If there aren' t too many other databases I'd scrap the
> > instance and start over. Alternatively if you have support on this server,
> > IBM may be able to make the database "go away".
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and
> > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> > organization with which I am associated either explicitly, implicitly, or
> > by
> > inference. Neither do those opinions reflect those of other individuals
> > affiliated with any entity with which I am affiliated nor those of the
> > entities themselves.
> >
> > On Fri, Mar 15, 2013 at 10:43 AM, Andrew Ford
> > <andrew@informix-dba.com>wrote:
> >
> > > I'm not given that option by oncheck, it just say lets me know there
> > > is corruption but doesn't ask me if I'd like to fix it.
> > >
> > > This is NOT a logged database.
> > >
> > > Andrew
> > >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > > Art Kagel
> > > Sent: Thursday, March 14, 2013 10:46 PM
> > > To: ids@iiug.org
> > > Subject: Re: Corrupt system catalog [29754]
> > >
> > > Have you tried to let oncheck repair/rebuild the damaged index? Just
> > > for curiosity sake, is this a logged database?
> > >
> > > Art
> > >
> > > Art S. Kagel
> > > Advanced DataTools (www.advancedatatools.com)
> > > Blog: http://informix-myview.blogspot.com/
> > >
> > > Disclaimer: Please keep in mind that my own opinions are my own
> > > opinions and do not reflect on my employer, Advanced DataTools, the
> > > IIUG, nor any other organization with which I am associated either
> > > explicitly, implicitly, or by inference. Neither do those opinions
> > > reflect those of other individuals affiliated with any entity with
> > > which I am affiliated nor those of the entities themselves.
> > >
> > > On Thu, Mar 14, 2013 at 10:46 PM, Andrew Ford
> > > <andrew@informix-dba.com>wrote:
> > >
> > > > 11.70.UC7IE running on Ubuntu
> > > >
> > > > I've got a corrupt sysdistrib table somehow
> > > >
> > > > $ oncheck -cDI breaker_pnl_old:sysdistrib> > > >
> > > > Validating indexes for breaker_pnl_old:informix.sysdistrib...
> > > >
> > > > Index distrib
> > > >
> > > > Could not bfget pagenum 0x5be, iserrno 105
> > > >
> > > > ISAM error: illegal key descriptor (too many parts or too long).> > > >
> > > > ERROR: Index distrib for breaker_pnl_old:informix.sysdistrib is bad.
> > > >
> > > > I am able to successfully unload/reload the database into a new copy
> > > > of the db, but any attempt to drop the old database with the corrupt
> > > > sysdistrib table is met with
> > > >
> > > > 211: Cannot read system catalog (sysdistrib).> > > >
> > > > 105: ISAM error: bad isam file format.> > > >
> > > > and any attempt to drop the distrib index runs into
> > > >
> > > > 511: Cannot modify system catalog (sysdistrib).> > > >
> > > > Short of unloading all data and an oninit -i is there anything I can
> > > > do to drop the db with the corrupt system catalog table?
> > > >
> > > > Thanks,
> > > >
> > > > Andrew
> > > >
> > > >
> > > >
> > > >
> > >
> > > **********************************************************************@@NL@
You mean at Bloomberg back in the '90s? Bloomberg had its own home grown
databases. When they POC's RDBMSes the POC team had to prove to doubters
in development that it would be as fast as the BBDB databases. They did
that by cutting corners and using sub-optimal schema like not turning
logging on and making a "timeseries like" record with 31 days of prices and
volumes packed into a single CHAR(129) column. When I got there I said "No
logging is a BAD idea." but it took four years and that incident to change
their minds about logging (never won the schema fight - it's still that way
in some DB2 and Oracle databases they've built since). Fortunately the
consultant who helped them build their earliest apps and middleware had
coded for transaction support detection as did I for the apps and
middleware I built for them, so the transition was painless.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Fri, Mar 15, 2013 at 2:30 PM, david@smooth1.co.uk
<david@smooth1.co.uk>wrote:
> Question for everyone.
>
> How come you use unlogged databases for something that is not scratch space
> e.g.
> ETL staging area?
>
> David.
>
> On 15 March 2013 at 18:24 Art Kagel <art.kagel@gmail.com> wrote:
> > While at Bloomberg, we used to have to rebuild an index or two ever time
> > the servers were bounced or crashed. We lost 6 indexes (2 each) on the
> > three largest tables in our news database on all three news servers one
> > weekend. I worked all day that Sunday until 5AM on Monday to get on
> server
> > back online in time for the markets in the morning and left to get some
> > sleep with the last indexes building (one on a second server and two on
> > the last one). Was awakened at 9AM by a screaming development director
> > asking "Why the F&%$ are you home when I have only one news server
> online?
> > Get your F&%$ing A$$ in here and fix it!" I sleepily explained the
> > situation slammed the phone down and got dressed. Arrived to an abashed
> > uber boss apologizing for not knowing that I'd been in the office for
> > almost 28 hours before going home and by-the-way I was right the second
> > news server had come back online before I got back into the office. Two
> > weeks later they finally let me turn logging on for all of our databases
> on
> > 45 server instances and I only lost one other index in the following ten
> > years even though data volumes, transaction rates, and numbers of
> ultimate
> > users had more than doubled in that time.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> > other organization with which I am associated either explicitly,
> > implicitly, or by inference. Neither do those opinions reflect those of
> > other individuals affiliated with any entity with which I am affiliated
> nor
> > those of the entities themselves.
> >
> > On Fri, Mar 15, 2013 at 12:34 PM, Andrew Ford
> <andrew@informix-dba.com>wrote:
> >
> > > Thanks everyone for the input.
> > >
> > > Something fishy is going on. They had 3 corrupt indexes in one database
> and
> > > at least one corrupt sysdistrib index in another all showing up at
> about
> > > the
> > > same time and very quickly, this instance has been up for no more than
> a
> > > week.
> > >
> > > The instance is running on a virtual machine that I have no control
> over,
> > > I'm thinking something happened at the disk level (cooked chunks) while
> the
> > > engine was online.
> > >
> > > I haven't dealt with unlogged databases in a long time. I have
> experienced
> > > some index corruption using them, but not as much as this and not as
> > > quickly
> > > as this.
> > >
> > > I'm going to try the delete from sysdistrib trick to see if that will
> allow
> > > me to drop the database with corruption. I doubt we will call in IBM
> > > support
> > > on this Innovator-C instance. I can unload the data/reinit the
> > > instance/recreate the dbs/reload the data in the time it would take to
> get
> > > the paper work together.
> > >
> > > Andrew
> > >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art
> > > Kagel
> > > Sent: Friday, March 15, 2013 10:16 AM
> > > To: ids@iiug.org
> > > Subject: Re: Corrupt system catalog [29761]
> > >
> > > OK, that fits with my experience. Only unlogged databases experience
> index
> > > corruption except in extreme circumstances.
> > >
> > > One thing you could try is to delete all the rows in the sysdistrib
> table
> > > and see if that lets the server 'repair' the index structure.
> > >
> > > So, if you can unload the data, the only option may be to reload it
> into a
> > > clean new database. If there aren' t too many other databases I'd scrap
> the
> > > instance and start over. Alternatively if you have support on this
> server,
> > > IBM may be able to make the database "go away".
> > > Art S. Kagel
> > > Advanced DataTools (www.advancedatatools.com)
> > > Blog: http://informix-myview.blogspot.com/
> > >
> > > Disclaimer: Please keep in mind that my own opinions are my own
> opinions
> > > and
> > > do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other
> > > organization with which I am associated either explicitly, implicitly,
> or
> > > by
> > > inference. Neither do those opinions reflect those of other individuals
> > > affiliated with any entity with which I am affiliated nor those of the
> > > entities themselves.
> > >
> > > On Fri, Mar 15, 2013 at 10:43 AM, Andrew Ford
> > > <andrew@informix-dba.com>wrote:
> > >
> > > > I'm not given that option by oncheck, it just say lets me know there
> > > > is corruption but doesn't ask me if I'd like to fix it.
> > > >
> > > > This is NOT a logged database.
> > > >
> > > > Andrew
> > > >
> > > > -----Original Message-----
> > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> Of
> > > > Art Kagel
> > > > Sent: Thursday, March 14, 2013 10:46 PM
> > > > To: ids@iiug.org
> > > > Subject: Re: Corrupt system catalog [29754]
> > > >
> > > > Have you tried to let oncheck repair/rebuild the damaged index? Just
> > > > for curiosity sake, is this a logged database?
> > > >
> > > > Art
> > > >
> > > > Art S. Kagel
> > > > Advanced DataTools (www.advancedatatools.com)
> > > > Blog: http://informix-myview.blogspot.com/
> > > >
> > >
Art,
Any types of reason that logged db can maintain its indexes so well, but
unlogged can not ?
Thanks,
Frank
On Fri, Mar 15, 2013 at 2:24 PM, Art Kagel <art.kagel@gmail.com> wrote:
> While at Bloomberg, we used to have to rebuild an index or two ever time
> the servers were bounced or crashed. We lost 6 indexes (2 each) on the
> three largest tables in our news database on all three news servers one
> weekend. I worked all day that Sunday until 5AM on Monday to get on server
> back online in time for the markets in the morning and left to get some
> sleep with the last indexes building (one on a second server and two on
> the last one). Was awakened at 9AM by a screaming development director
> asking "Why the F&%$ are you home when I have only one news server online?
> Get your F&%$ing A$$ in here and fix it!" I sleepily explained the
> situation slammed the phone down and got dressed. Arrived to an abashed
> uber boss apologizing for not knowing that I'd been in the office for
> almost 28 hours before going home and by-the-way I was right the second
> news server had come back online before I got back into the office. Two
> weeks later they finally let me turn logging on for all of our databases on
> 45 server instances and I only lost one other index in the following ten
> years even though data volumes, transaction rates, and numbers of ultimate
> users had more than doubled in that time.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated nor
> those of the entities themselves.
>
> On Fri, Mar 15, 2013 at 12:34 PM, Andrew Ford <andrew@informix-dba.com
> >wrote:
>
> > Thanks everyone for the input.
> >
> > Something fishy is going on. They had 3 corrupt indexes in one database
> and
> > at least one corrupt sysdistrib index in another all showing up at about
> > the
> > same time and very quickly, this instance has been up for no more than a
> > week.
> >
> > The instance is running on a virtual machine that I have no control over,
> > I'm thinking something happened at the disk level (cooked chunks) while
> the
> > engine was online.
> >
> > I haven't dealt with unlogged databases in a long time. I have
> experienced
> > some index corruption using them, but not as much as this and not as
> > quickly
> > as this.
> >
> > I'm going to try the delete from sysdistrib trick to see if that will
> allow
> > me to drop the database with corruption. I doubt we will call in IBM
> > support
> > on this Innovator-C instance. I can unload the data/reinit the
> > instance/recreate the dbs/reload the data in the time it would take to
> get
> > the paper work together.
> >
> > Andrew
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art
> > Kagel
> > Sent: Friday, March 15, 2013 10:16 AM
> > To: ids@iiug.org
> > Subject: Re: Corrupt system catalog [29761]
> >
> > OK, that fits with my experience. Only unlogged databases experience
> index
> > corruption except in extreme circumstances.
> >
> > One thing you could try is to delete all the rows in the sysdistrib table
> > and see if that lets the server 'repair' the index structure.
> >
> > So, if you can unload the data, the only option may be to reload it into
> a
> > clean new database. If there aren' t too many other databases I'd scrap
> the
> > instance and start over. Alternatively if you have support on this
> server,
> > IBM may be able to make the database "go away".
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and
> > do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other
> > organization with which I am associated either explicitly, implicitly, or
> > by
> > inference. Neither do those opinions reflect those of other individuals
> > affiliated with any entity with which I am affiliated nor those of the
> > entities themselves.
> >
> > On Fri, Mar 15, 2013 at 10:43 AM, Andrew Ford
> > <andrew@informix-dba.com>wrote:
> >
> > > I'm not given that option by oncheck, it just say lets me know there
> > > is corruption but doesn't ask me if I'd like to fix it.
> > >
> > > This is NOT a logged database.
> > >
> > > Andrew
> > >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > > Art Kagel
> > > Sent: Thursday, March 14, 2013 10:46 PM
> > > To: ids@iiug.org
> > > Subject: Re: Corrupt system catalog [29754]
> > >
> > > Have you tried to let oncheck repair/rebuild the damaged index? Just
> > > for curiosity sake, is this a logged database?
> > >
> > > Art
> > >
> > > Art S. Kagel
> > > Advanced DataTools (www.advancedatatools.com)
> > > Blog: http://informix-myview.blogspot.com/
> > >
> > > Disclaimer: Please keep in mind that my own opinions are my own
> > > opinions and do not reflect on my employer, Advanced DataTools, the
> > > IIUG, nor any other organization with which I am associated either
> > > explicitly, implicitly, or by inference. Neither do those opinions
> > > reflect those of other individuals affiliated with any entity with
> > > which I am affiliated nor those of the entities themselves.
> > >
> > > On Thu, Mar 14, 2013 at 10:46 PM, Andrew Ford
> > > <andrew@informix-dba.com>wrote:
> > >
> > > > 11.70.UC7IE running on Ubuntu
> > > >
> > > > I've got a corrupt sysdistrib table somehow
> > > >
> > > > $ oncheck -cDI breaker_pnl_old:sysdistrib> > > >
> > > > Validating indexes for breaker_pnl_old:informix.sysdistrib...
> > > >
> > > > Index distrib
> > > >
> > > > Could not bfget pagenum 0x5be, iserrno 105
> > > >
> > > > ISAM error: illegal key descriptor (too many parts or too long).> > > >
> > > > ERROR: Index distrib for breaker_pnl_old:informix.sysdistrib is bad.
> > > >
> > > > I am able to successfully unload/reload the database into a new copy
> > > > of the db, but any attempt to drop the old database with the corrupt
> > > > sysdistrib table is met with
> > > >
> > > > 211: Cannot read system catalog (sysdistrib).> > > >
> > > > 105: ISAM error: bad isam file format.> > > >
> > > > and any attempt to drop the distrib index runs into
> > > >
> > > > 511: Cannot modify system catalog (sysdistrib).> > > >
> > > > Short of unloading all data and an oninit -i is there anything I can
> > > > do to drop the db with the corrupt system catalog table?
> > > >
> > > > Thanks,
> > > >
> > > > Andrew
> > > >
> > > >
> > > >
> > > >
> > >
>
Yes, a very good reason. Recovery. With logging if a server goes down
without a complete normal shutdown then when it restarts the server enters
a process caled "Fast Recovery". The server will slap down any physical
log records it finds on startup to bring all pages modified since the last
checkpoint back to a known tabula rasa state then roll forward logical logs
until they run out, then finally roll back any incomplete transactions.
This cleans up anything that could make an index page or data page
inconsistent. It's a bit more complicated in 11.10 and later with
non-blocking checkpoints but the basics are the same. Without logging Fast
Recover is truncated and only DDL and internal metadata modifications can
be recovered as these are the only operations that are still logged. So an
extent added to a partition is safe, but your data and index pages may not
be. Indexes are especially vulnerable since there are more tiny updates to
any given index page than to a given data page unless the rows are very
narrow.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Wed, Mar 20, 2013 at 4:57 PM, FRANK <yunyaoqu@gmail.com> wrote:
> Art,
>
> Any types of reason that logged db can maintain its indexes so well, but
> unlogged can not ?
>
> Thanks,
> Frank
>
> On Fri, Mar 15, 2013 at 2:24 PM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > While at Bloomberg, we used to have to rebuild an index or two ever time
> > the servers were bounced or crashed. We lost 6 indexes (2 each) on the
> > three largest tables in our news database on all three news servers one
> > weekend. I worked all day that Sunday until 5AM on Monday to get on
> server
> > back online in time for the markets in the morning and left to get some
> > sleep with the last indexes building (one on a second server and two on
> > the last one). Was awakened at 9AM by a screaming development director
> > asking "Why the F&%$ are you home when I have only one news server
> online?
> > Get your F&%$ing A$$ in here and fix it!" I sleepily explained the
> > situation slammed the phone down and got dressed. Arrived to an abashed
> > uber boss apologizing for not knowing that I'd been in the office for
> > almost 28 hours before going home and by-the-way I was right the second
> > news server had come back online before I got back into the office. Two
> > weeks later they finally let me turn logging on for all of our databases
> on
> > 45 server instances and I only lost one other index in the following ten
> > years even though data volumes, transaction rates, and numbers of
> ultimate
> > users had more than doubled in that time.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> > other organization with which I am associated either explicitly,
> > implicitly, or by inference. Neither do those opinions reflect those of
> > other individuals affiliated with any entity with which I am affiliated
> nor
> > those of the entities themselves.
> >
> > On Fri, Mar 15, 2013 at 12:34 PM, Andrew Ford <andrew@informix-dba.com
> > >wrote:
> >
> > > Thanks everyone for the input.
> > >
> > > Something fishy is going on. They had 3 corrupt indexes in one database
> > and
> > > at least one corrupt sysdistrib index in another all showing up at
> about
> > > the
> > > same time and very quickly, this instance has been up for no more than
> a
> > > week.
> > >
> > > The instance is running on a virtual machine that I have no control
> over,
> > > I'm thinking something happened at the disk level (cooked chunks) while
> > the
> > > engine was online.
> > >
> > > I haven't dealt with unlogged databases in a long time. I have
> > experienced
> > > some index corruption using them, but not as much as this and not as
> > > quickly
> > > as this.
> > >
> > > I'm going to try the delete from sysdistrib trick to see if that will
> > allow
> > > me to drop the database with corruption. I doubt we will call in IBM
> > > support
> > > on this Innovator-C instance. I can unload the data/reinit the
> > > instance/recreate the dbs/reload the data in the time it would take to
> > get
> > > the paper work together.
> > >
> > > Andrew
> > >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > Art
> > > Kagel
> > > Sent: Friday, March 15, 2013 10:16 AM
> > > To: ids@iiug.org
> > > Subject: Re: Corrupt system catalog [29761]
> > >
> > > OK, that fits with my experience. Only unlogged databases experience
> > index
> > > corruption except in extreme circumstances.
> > >
> > > One thing you could try is to delete all the rows in the sysdistrib
> table
> > > and see if that lets the server 'repair' the index structure.
> > >
> > > So, if you can unload the data, the only option may be to reload it
> into
> > a
> > > clean new database. If there aren' t too many other databases I'd scrap
> > the
> > > instance and start over. Alternatively if you have support on this
> > server,
> > > IBM may be able to make the database "go away".
> > > Art S. Kagel
> > > Advanced DataTools (www.advancedatatools.com)
> > > Blog: http://informix-myview.blogspot.com/
> > >
> > > Disclaimer: Please keep in mind that my own opinions are my own
> opinions
> > > and
> > > do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> > other
> > > organization with which I am associated either explicitly, implicitly,
> or
> > > by
> > > inference. Neither do those opinions reflect those of other individuals
> > > affiliated with any entity with which I am affiliated nor those of the
> > > entities themselves.
> > >
> > > On Fri, Mar 15, 2013 at 10:43 AM, Andrew Ford
> > > <andrew@informix-dba.com>wrote:
> > >
> > > > I'm not given that option by oncheck, it just say lets me know there
> > > > is corruption but doesn't ask me if I'd like to fix it.
> > > >
> > > > This is NOT a logged database.
> > > >
> > > > Andrew
> > > >
> > > > -----Original Message-----
> > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> Of
> > > > Art Kagel
> > > > Sent: Thursday, March 14, 2013 10:46 PM
> > > > To: ids@iiug.org
> > > > Subject: Re: Corrupt system catalog [29754]
> > > >
> > > > Have you tried to let oncheck repair/rebuild the damaged index? Just
> > > > for curiosity sake, is this a logged database?
> > > >
> > > > A
OK. Art ! If the integrity of data or index can not be guaranteed, it is just a meaningless db. Or unlogged db should never check these data loss or integrity. Whenever the engine is not gracefully shutdown, the database is almost certain in a inconsistent state unless read-only apps. Frank On Wed, Mar 20, 2013 at 6:44 PM, Art Kagel <art.kagel@gmail.com> wrote: > Yes, a very good reason. Recovery. With logging if a server goes down > without a complete normal shutdown then when it restarts the server enters > a process caled "Fast Recovery". The server will slap down any physical > log records it finds on startup to bring all pages modified since the last > checkpoint back to a known tabula rasa state then roll forward logical logs > until they run out, then finally roll back any incomplete transactions. > This cleans up anything that could make an index page or data page > inconsistent. It's a bit more complicated in 11.10 and later with > non-blocking checkpoints but the basics are the same. Without logging Fast > Recover is truncated and only DDL and internal metadata modifications can > be recovered as these are the only operations that are still logged. So an > extent added to a partition is safe, but your data and index pages may not > be. Indexes are especially vulnerable since there are more tiny updates to > any given index page than to a given data page unless the rows are very > narrow. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > On Wed, Mar 20, 2013 at 4:57 PM, FRANK <yunyaoqu@gmail.com> wrote: > > > Art, > > > > Any types of reason that logged db can maintain its indexes so well, but > > unlogged can not ? > > > > Thanks, > > Frank > > > > On Fri, Mar 15, 2013 at 2:24 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > > > While at Bloomberg, we used to have to rebuild an index or two ever > time > > > the servers were bounced or crashed. We lost 6 indexes (2 each) on the > > > three largest tables in our news database on all three news servers one > > > weekend. I worked all day that Sunday until 5AM on Monday to get on > > server > > > back online in time for the markets in the morning and left to get some > > > sleep with the last indexes building (one on a second server and two on > > > the last one). Was awakened at 9AM by a screaming development director > > > asking "Why the F&%$ are you home when I have only one news server > > online? > > > Get your F&%$ing A$$ in here and fix it!" I sleepily explained the > > > situation slammed the phone down and got dressed. Arrived to an abashed > > > uber boss apologizing for not knowing that I'd been in the office for > > > almost 28 hours before going home and by-the-way I was right the second > > > news server had come back online before I got back into the office. Two > > > weeks later they finally let me turn logging on for all of our > databases > > on > > > 45 server instances and I only lost one other index in the following > ten > > > years even though data volumes, transaction rates, and numbers of > > ultimate > > > users had more than doubled in that time. > > > > > > Art > > > > > > Art S. Kagel > > > Advanced DataTools (www.advancedatatools.com) > > > Blog: http://informix-myview.blogspot.com/ > > > > > > Disclaimer: Please keep in mind that my own opinions are my own > opinions > > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor > any > > > other organization with which I am associated either explicitly, > > > implicitly, or by inference. Neither do those opinions reflect those of > > > other individuals affiliated with any entity with which I am affiliated > > nor > > > those of the entities themselves. > > > > > > On Fri, Mar 15, 2013 at 12:34 PM, Andrew Ford <andrew@informix-dba.com > > > >wrote: > > > > > > > Thanks everyone for the input. > > > > > > > > Something fishy is going on. They had 3 corrupt indexes in one > database > > > and > > > > at least one corrupt sysdistrib index in another all showing up at > > about > > > > the > > > > same time and very quickly, this instance has been up for no more > than > > a > > > > week. > > > > > > > > The instance is running on a virtual machine that I have no control > > over, > > > > I'm thinking something happened at the disk level (cooked chunks) > while > > > the > > > > engine was online. > > > > > > > > I haven't dealt with unlogged databases in a long time. I have > > > experienced > > > > some index corruption using them, but not as much as this and not as > > > > quickly > > > > as this. > > > > > > > > I'm going to try the delete from sysdistrib trick to see if that will > > > allow > > > > me to drop the database with corruption. I doubt we will call in IBM > > > > support > > > > on this Innovator-C instance. I can unload the data/reinit the > > > > instance/recreate the dbs/reload the data in the time it would take > to > > > get > > > > the paper work together. > > > > > > > > Andrew > > > > > > > > -----Original Message----- > > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf > Of > > > Art > > > > Kagel > > > > Sent: Friday, March 15, 2013 10:16 AM > > > > To: ids@iiug.org > > > > Subject: Re: Corrupt system catalog [29761] > > > > > > > > OK, that fits with my experience. Only unlogged databases experience > > > index > > > > corruption except in extreme circumstances. > > > > > > > > One thing you could try is to delete all the rows in the sysdistrib > > table > > > > and see if that lets the server 'repair' the index structure. > > > > > > > > So, if you can unload the data, the only option may be to reload it > > into > > > a > > > > clean new database. If there aren' t too many other databases I'd > scrap > > > the > > > > instance and start over. Alternatively if you have support on this > > > server, > > > > IBM may be able to make the database "go away". > > > > Art S. Kagel > > > > Advanced DataTools (www.advancedatatools.com) > > > > Blog: http://informix-myview.blogspot.com/ > > > > > > > > Disclaimer: Please keep in mind that my own opinions are my own > > opinions > > > > and > > > > do not reflect on my employer, Advanced DataTools, the IIUG, nor any > > > other > > > > organization with which I am associated either explicitly, > implicitly, > > or > > > > by > > > > inference. Neither do those opinions reflect those of other > individuals > > > > affiliated with any entity with which I am affiliated nor those of > the > > > > entities themselves. > > > > > > > > On Fri, Mar 15, 2013
WHOA! Flame on! There are rules and there are trade-offs. You didn't follow the best practices rules and you don't like the trade-off. First, I didn't say that anytime the server crashes your data is hosed, I said that without logging there is a possibility that data or indexes can be damaged. With logging that cannot happen due to a server, system, or storage crash. (Partial disk failure can still cause damaged indexes and there's nothing any RDBMS can do to prevent that, it can only detect it.) Your data and your index integrity CAN be guaranteed. Follow the rules and log your databases and Informix can recover and keep the internal integrity of your database safe from just about anything short of nuclear war. I know you came from the Standard Engine world where logging was truly optional. That was because there was no memory cache in SE. Each client had/has its own database "engine", simple as it was/is, and the developers hadn't thought to use shared memory so caching data in memory wasn't reasonable (not to mention that some early UNIXes didn't even support share memory). Therefore your data was safe with or without logging because each sqlexec process always had to write its modifications directly to the database files on disk. It also had to lock the file or a byte range in the file (depending on the OS support for one or the other) in order to insure that its writes wouldn't clash with other sessions' IO. The trade-off here is performance and scaling. SE cannot scale to hundreds of thousands of users. OnLine could come close because of the central shared memory cache. Those news servers I managed handled over 125,000 users all asking to read the Loraine Bobbit story almost simultaneously! Dynamic server scales even more so because it adds other shared memory structures and reduces the number of processes needed to support <N> users by multithreading and multiprocessing a central engine that replaces the sqlexec and sqlturbo processes! Once Informix Turbo, which became OnLine, came out logging was no longer optional because there was a shared cache of data pages and index nodes. Same for Dynamic Server. If you want transactional and data security you turn logging on. Period. The ONLY databases that CAN have no logging are Data Warehouse databases where you don't care if something gets F'd up because you can just reload it all. Personally I don't buy that argumen and insist on logging all databases with which I am associated, even DW sites, but there are those, including some very knowledgeable people like Lester Knutsen in the Informix world and others in the database in general world who stand by unlogged DW instances. Their arguments are cogent but based on the ability to just recreate the DW database from sources. I'm too lazy to want to have to do that. I'm considered an expert at recovering trashed databases. I would be happy to never have to do that again and work hard to make sure my clients are never in that position. I've only once had to recover data for a client (all other recoveries where not clients until after we got their data back) and that was because the client didn't follow my recommendations - they do now! Yes, turning on logging costs you a smidgen of performance. Nothing earth shattering. Yes, you have to code for transactions and track additional error codes versus an unlogged database - not a big deal - I've done that. But what you gain is well worth the effort. Are there some database engines out there that do not offer a non-transaction/unlogged option? Sure. Does that mean you can't make them unsafe -well actually you can, but for the scope of this diatribe - yes it does. Does that make Informix "just a meaningless db"? Uh, no! So you are angry that Informix let you shoot yourself in the foot with the gun you manufactured and loaded yourself? IIRC several of us warned you long ago that you should be running your databases with logging turned on. And if we didn't specifically warn you, you've been on this SIG for a long time, I can't count on all of my digits and toes the number of times I have warned others about the same issue in the recent past. I have even repeated brief versions of that story at BB enough times that I fear someone will tell me to "can it already!" at some point. So, what should you do? You could migrate to another RDBMS, maybe the leader in the industry, and see what happens when your server crashes or is shutdown improperly then. Your experience may be different from a MAJOR NY bank whose online banking system was down for four weeks two summers ago because of just such a crash. That was also a case of not following the best practices. If you use a tool incorrectly you have to expect to deal with the consequences. What would I do? I would recode my applications to be transaction aware so they will continue to run on the existing unlogged databases but will recognize a database with logging and behave properly when connected to one. Then I would turn logging on as soon as possible and until then I would be taking frequent archives several times a day and occasional data exports for added safety. After that I would see how I can restructure my apps to take advantage of the new powers transaction logging has opened up to me and see where I can streamline my code or make it even safer. Flame off. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Mar 20, 2013 at 7:19 PM, FRANK <yunyaoqu@gmail.com> wrote: > OK. Art ! If the integrity of data or index can not be guaranteed, it is > just a meaningless db. Or unlogged db should never check these data loss > or integrity. Whenever the engine is not gracefully shutdown, the > database is almost certain in a inconsistent state unless read-only apps. > > Frank > On Wed, Mar 20, 2013 at 6:44 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > Yes, a very good reason. Recovery. With logging if a server goes down > > without a complete normal shutdown then when it restarts the server > enters > > a process caled "Fast Recovery". The server will slap down any physical > > log records it finds on startup to bring all pages modified since the > last > > checkpoint back to a known tabula rasa state then roll forward logical > logs > > until they run out, then finally roll back any incomplete transactions. > > This cleans up anything that could make an index page or data page > > inconsistent. It's a bit more complicated in 11.10 and later with > > non-blocking checkpoints but the basics are the same. Without logging > Fast > > Recover is truncated and only DDL and internal metadata modifications can > > be recovered as these are the only operations that are still logged. So > an > > extent added to a partition is safe, but your data and index pages m