Hi Anne,

You might have to rollforward the whole database from the
last checkpoint and apply the journals up until just prior
the table drop transaction.

If the table existed during the last checkpoint,  it will exist
after the rollforward. Just make sure the '-eDD-MMM-YYYY' is before
the table was dropped.

Andre
-----Original Message-----
From: Anne O'Kelly
To: info-ingres@ams.org
Sent: 17/11/03 11:47 AM
Subject: Rollforwarddb Query

A table on our LIVE database has been accidentally dropped!!
What is the most efficient way to recover it and minimise data loss.



Can we rollforwarddb the one table from the checkpoint by just
recreating the table structure
as we are currently getting an error as the table does not exist.


Any quick replies would be appreciated greatly!

Thanks,

Anne

********************************************************************
This message is intended only for the use of the person(s) ("the
intended
recipient(s)") to whom it is addressed. It may contain information which
is
privileged and confidential within the meaning of applicable law. If you

are not the intended recipient, please contact the sender as soon as
possible. The views expressed in this communication may not necessarily
be the views held by LGCSB (Local Government Computer Services Board).

Any attachments have been checked by a virus scanner and appear to be
clean.
Please ensure that you also scan all messages, as LGCSB does not accept
any liability for contamination or damage to your systems.
********************************************************************

I should also mention that creating a new table, even with the same
name creates a new reltid in iirelation, hence a new datafile name.

This will likely mean rollforwardb, like auditdb will not find any
journal entries for it.

Andre
-----Original Message-----
From: Laframboise André
To: 'Anne O'Kelly '; 'info-ingres@ams.org '
Sent: 17/11/03 12:07 PM
Subject: RE: Rollforwarddb Query


 Hi Anne,

You might have to rollforward the whole database from the
last checkpoint and apply the journals up until just prior
the table drop transaction.

If the table existed during the last checkpoint,  it will exist
after the rollforward. Just make sure the '-eDD-MMM-YYYY' is before
the table was dropped.

Andre
-----Original Message-----
From: Anne O'Kelly
To: info-ingres@ams.org
Sent: 17/11/03 11:47 AM
Subject: Rollforwarddb Query

A table on our LIVE database has been accidentally dropped!!
What is the most efficient way to recover it and minimise data loss.



Can we rollforwarddb the one table from the checkpoint by just
recreating the table structure
as we are currently getting an error as the table does not exist.


Any quick replies would be appreciated greatly!

Thanks,

Anne

********************************************************************
This message is intended only for the use of the person(s) ("the
intended
recipient(s)") to whom it is addressed. It may contain information which
is
privileged and confidential within the meaning of applicable law. If you

are not the intended recipient, please contact the sender as soon as
possible. The views expressed in this communication may not necessarily
be the views held by LGCSB (Local Government Computer Services Board).

Any attachments have been checked by a virus scanner and appear to be
clean.
Please ensure that you also scan all messages, as LGCSB does not accept
any liability for contamination or damage to your systems.
********************************************************************

Hi Anne,

   Bummer!

   Depends!

   No, recreating the table gives it a new reltid and a new disk filename.
   Rollforwarddb at table level will need to be able to get the right file out
   of the checkpoint tar.

   But you can lie to it!

   
   You have a few options of varying ability, complexity and duration.
   
   A critical component of your decision making has to be the potential loss
   of referential integrity in the database. Will it matter if the table is
   recovered to some specific time but the rest of the database has now moved
   well beyond that time? Exactly how you handle that will be upto you, but on
   the plus side if the table is really vital, then the applications probably
   havent been doing much anyway.

   Furthermore, if this takes too long your users may bitch so much its not
   worth the extra effort to get the extra data back.

   It also depends on exactly what tools you have available for the job.

   0. Just recover the entire database back to the point in time the table
      was dropped.

       If referential integrity is a consideration then you may just have to
       do this.

       This means you wave good bye to all the work on the rest of the tables
       done afterwards. You could of course use audit trail techniques to
       recover transactions from after this recovery time, but unless you
       have a quality software tool to do this already the exercise is
       tedious and very time consuming.

   1. If you have a disaster recovery site and the recovery time is
      acceptable for you.

      Do a full database recovery to your recovery site, then simply transfer
      the required table back to your production site with a simply copydb.

      This is easy. But questions of referential integrity remain but at
      least you wont lose data.

   2. Table recovery to DR site via file swap technique.
       This is complex, but can be faster than a full recovery.

       I used to have some notes on this - I think - but I can't find them at
       the moment. If you want to flesh out this possibility then let me
       know, I'll see if I can find them again.

   Martin Bowes

Hi all,

We  recovered  the entire database back to the point in time the table
was dropped by rollforwarding the whole database from the

last checkpoint and apply the journals up until just prior the table
drop transaction.



Fortunately mot much activity was taking place on the database as it was
late afternoon.

Thank you for all your prompt replies!



Regards,



Anne



********************************************************************
This message is intended only for the use of the person(s) ("the intended
recipient(s)") to whom it is addressed. It may contain information which is
privileged and confidential within the meaning of applicable law. If you
are not the intended recipient, please contact the sender as soon as
possible. The views expressed in this communication may not necessarily
be the views held by LGCSB (Local Government Computer Services Board).

Any attachments  have been checked by a virus scanner and appear to be
clean.
Please ensure that you also scan all messages, as LGCSB does not accept
any liability for contamination or damage to your systems.
********************************************************************

Anne,
To recreate the lost table as was at the last checkpoint;

1) Extract the UNIX files that make up the table from the most recent
checkpoint file(s).
2) Re-Create the lost table (empty) in the approptiate locations,
with the same structure.
3) Move the files from (1) back into the ingres data location(s),
renaming them to overwrite the existing files associated with the
newly created table.

Its quick, dirty, and you'll need to be careful
(and be warned that there is more to it than stated above).
But this is a useful recovery option that can be a lifesaver.

HTH
Steve