Recovery of a Destroyed Database

> I have a question concerning Ingres backup and restore.
> 
> Using:
> Ingres 1.2
> Solaris 2.51
> 
> Can a checkpoint be used to restore a database that has been destroyed with
> 'destroydb' or otherwise completely removed from the system (i.e. from a
> hardware failure)?  If not, what is the recommended backup method to ensure
> that databases are fully recoverable?
> 
> Thanks,
> Jeff Hailer


The short answer is "no".  

The long answer is you need to recreate the database, extend it EXACTLY
the same, and then you need to replace the contects of the dmp, jnl, and
ckp directories (unless you back to tape) exactly.  And you need to 
get the aaaaaaaa.cnf file into the default data location.

The catch is that you need all these files as they were sometime AFTER
the checkpoint you are trying to recover was taken.

Hope that helps,

Cheers,

Michael Leo            mleo@cariboulake.com        Java, Oracle
Caribou Lake Software  http://www.cariboulake.com  Ingres, C/C++,
Minneapolis, Minnesota (612) 323-9713              sockets, fun



> The short answer is "no".  

    Hmmm, well its certainly a shorter answer than mine:-)
    I mean an entire character representing a 33% saving in text!

    I absolutely agree with Mike in the case of the checkpoint tar file alone.
	If thats all you've got then you are in more shit than a Werribee Duck.
	Trust me, that aint good.

    However, a checkpoint tar file associated with iifile_info and a copy.in
    from unloaddb/copydb would be a viable solution for recovery. Although in
    that case referential integrity couldnt be garunteed if the checkpoint was
    online. However, if its the only option available, then its better than a
    poke in the eye with a pointy stick!
> 
> The long answer is you need to recreate the database, extend it EXACTLY
> the same, and then you need to replace the contects of the dmp, jnl, and
> ckp directories (unless you back to tape) exactly.  And you need to 
> get the aaaaaaaa.cnf file into the default data location.
> 
    This is absolutely correct.

    Which is why I mentioned copying so much detail from iidbdb. Myself I
    extract that detail at every checkpoint so that its as fresh as possible.
    The file containing all this information is backed up on the DUMP tape
    containing the checkpoint and rcp'd to a remote host where I perform my
    ultimate (ie. we just lost the Uni) recovery.

    Specifically the requirements for creating the database and extending it
    accordingly are in iidatabase and iilocations.

> The catch is that you need all these files as they were sometime AFTER
> the checkpoint you are trying to recover was taken.
> 

    Yes, the system dumps to hold II_CHECKPOINT, II_JOURNAL and II_DUMP must be
    taken after the checkpoint or else they wont contain the checkpoint.

    Martin Bowes




Hi Jeff,

    The short answer is yes! The checkpoint recovery is the primary means of
    database recovery in Ingres. Unfortunately the devil is in the detail. 

    You must be aware that the checkpoint tar file alone is insufficient to 
    perform a meaningful recovery. You must ensure that all other data required
    is available on some media that cant be harmed by a destroydb or a machine 
    failure.

    If you have destroyed the database, then the action of destruction will
    remove all disk based checkpoints, journals, dumps, config and saved
    config files.  This will then leave you with tape based checkpoints, and 
    whatever else may have thwarted the destruction process by being in a 
    symlinked area.  eg. I keep a large number of my checkpoints in symlinked 
    NFS directories to store the disk checkpoints offsite. destroydb can't 
    remove these.

    In the event of hardware failure you may have lost substantially more!

    The first thing you must do is ensure that there are regular system dumps
    containing (at least) II_SYSTEM, II_JOURNAL, II_CHECKPOINT and II_DUMP.

    It is VERY useful to regurly copy the following details from iidbdb to
    file (and back up to tape).
        select * from iiuser;
        select * from iilocations;
        select * from iidbaccess;
        select * from iidbpriv;
        select * from iiusergroup;
        select * from iiextend;
        select * from iidatabase;
        select * from iirole;

    For each database in the installation you should take a copy of
    iifile_info before each checkpoint.
    
    It is very useful to perform an unloaddb or copydb on each database on a
    regular basis. Just execute the command to prepare the copy.in files.
    These contain the description of every table in the database. Like the
    iifile_info details above these can be very handy.

    If all this information is saved away, then no matter what happens to an
    individual database or the host it resides on, you should be able to get
    your databases back.

    Martin Bowes

    PS. I'm assuming your databases are small enough or that your window of
    support wide enough to take meaningful checkpoints. Those poor buggers who
    aren't in this glorious position have to come up with other strategies
    involving Disk Mirroring etc.
Ingres Q & A
Back to William's Home Page
Email William