From: cs_mj@gsbacd.uchicago.edu (Mark Jaeger)
Subject: Re: Auditdb on old journals?
Organization: 
Date: Fri, 20 Nov 1992 22:23:57 GMT

In article <reid.6.722272148@UPEI.CA>, reid@UPEI.CA (Mike Reid) writes:
>
> Is there any way to get auditdb to write an audit trail for some previous
> journal file?  The journal file is deleted after each checkpoint but I would
> like to be able to look for particular changes in a journal file restored
> from tape. We are running Ingres 6.4/02 under VMS 5.4. Any suggestions
> would be appreciated.

This sounds hard.  It might require restoring the database to the state
it was in before the journals started using rollforwarddb in order to
trick the JSP into knowing about them.  If you have a separate INGRES
installation in addition to the one for your real database, then you
could practice there without hurting the real one.  I think the whole
trick is in getting the configuration file for the database
[ingres.data.my_db]aaaaaaaa.cnf in a state where it knows about the
checkpoint from which the journals started.

I have some insight into this due to a rather unpleasant experience I
had last week when a disk went bad and we had to recover a database from
checkpoints, journals, and dumps.  We discovered that some dodo brain
had inadvertently written over the tape with the current checkpoint. 
After tearing out a few hairs we also discovered that it _is_ possible to
roll forward from older checkpoints using the "#c" switch on the
rollforwarddb command.  This is documented in the DBA Guide.

However, the "#c" did not work seamlessly (maybe cuz I didn't follow
_all_ the rules).  In order to recover the database, I _should_ have
recovered the dump directory in addition to the old checkpoint, because
there is critical information in there.

Since our operator was feeling somewhat overstressed, I wasn't about to
task him to restore an itty bitty 16-block file from a 2-gigabyte backup
tape, so I resorted to ruse.  In order to get rollforwarddb to work, I
had to go to the dump directory for the database and change the
aaaaaaaa.cnf file there so that it thought that the old checkpoint was a
valid one.  The current config file was written after a "ckpdb" with the
"-d" switch, and so it had lost the information about the old
checkpoint.  I merely copied the dump file to the config file, and
eureka.

Finally we got this command to work:

		$ rollforwarddb -s -v +c -j #c123 my_db

and then I was able to use auditdb to look at all the journal files.

I would suggest simulating the situation you have with a test database. 
Create a new, empty database; checkpoint it as you do your real
database, with journaling; create a table with journaling; insert a row
to generate journal records; and then shut down INGRES (this flushes the
journals to disk).  Copy the checkpoint, journal, and dump directories
to somewhere else; fire up INGRES; and then run "ckpdb -d".  Generate
some more journals; and now try to simulate the recovery of the old
checkpoints, journals, and dumps from tape by putting them back in the
right directories; and run rollforwarddb.

--Mark Jaeger				internet: cs_mj@gsbvax.uchicago.edu
Graduate School of Business		yellnet:  (312) 702-0328
University of Chicago			faxnet:   (312) 702-0233
Disclaimer: My opinions are my own and not those of my employer.
Ich bin ein Virus.  Mach' mit und kopiere mich in Deine .signature.

Newsgroups: comp.databases.ingres