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 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?
> > > > > >
> > > > > > 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.
> > > > >
> > > > >
> > > >
> > > > --bcaec554dbc86e7ffe04d7fabf98
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
> *******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --20cf300faae3050bc704d861592a
> > >
> > >
> > >
> > >
> >
> >
>
> *******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --bcaec554d232ed4f1004d862f68c
> >
> >
> >
> >
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --20cf3074b4665766eb04d8637286
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec5524216143da104d8649d64
