We use online backups every night, and periodically delete old logfiles that
are not in use to prevent the hard drive from filling up. We 'think' that
yesterday we accidentally deleted an active log file and the backup
failed and wiped out the DB. When trying to restore the DB from the
backup taken two nights ago we get:

$ db2 RESTORE DATABASE penglocl FROM /dbData/backup TAKEN AT 20050216233007 TO /dbData/PENGUIN
SQL2526W Warning! Restoring to an existing database that is the same as the backup image database, and the alias names are the same, but the database name "PENGLOCL" of the existing database does not match the database name "PENGUIN" of the backup image. The target database will be overwritten by the backup version.
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.
db2inst1@penguin:/home/db2inst1
$ db2 connect to penglocl
SQL1117N A connection to or activation of database "PENGLOCL" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
db2inst1@penguin:/home/db2inst1
$ db2 rollforward db penglocl to 2005-02-16-21.16.15.000000 and stop using local time
SQL0104N An unexpected token "using" was found following "STOP". Expected
tokens may include: "END-OF-STATEMENT". SQLSTATE=42601
db2inst1@penguin:/home/db2inst1
$ db2 rollforward db penglocl to 2005-02-16-21.16.15.000000 using local time and stop
SQL1275N The stoptime passed to roll-forward must be greater than or equal to
"2005-02-16-23.30.12.000000", because database "PENGLOCL" on node(s) "0"
contains information later than the specified time.
db2inst1@penguin:/home/db2inst1
$ db2 rollforward db penglocl to 2005-02-16-23.30.12.000000 using local time and stop
SQL4970N Roll-forward recovery on database "PENGLOCL" cannot reach the
specified stop point (end-of-log or point-in-time) because of missing log
file(s) on node(s) "0".


$ db2 rollforward db penglocl to 2005-02-16-16.16.15.000000 using local time and stop
SQL1266N Database "PENGLOCL" has been rolled forward to
"2005-02-16-23.30.12.000000", which is past the specified point-in-time.
db2inst1@penguin:/home/db2inst1
$ db2 connect to PENGLOCL
SQL1117N A connection to or activation of database "PENGLOCL" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019



$ db2 rollforward db penglocl to 2005-02-16-16.16.15.000000 using local time and stop
SQL1266N Database "PENGLOCL" has been rolled forward to
"2005-02-16-23.30.12.000000", which is past the specified point-in-time.
db2inst1@penguin:/home/db2inst1
$ db2 connect to PENGLOCL
SQL1117N A connection to or activation of database "PENGLOCL" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
db2inst1@penguin:/home/db2inst1
$ db2 rollforward db penglocl complete
SQL1276N Database "PENGLOCL" cannot be brought out of rollforward pending
state until roll-forward has passed a point in time greater than or equal to
"2005-02-17-05.30.12.000000", because node "0" contains information later than
the specified time.
db2inst1@penguin:/home/db2inst1
$ db2 rollforward db penglocl to 2005-02-17-05.30.12.000000 using local time and complete
SQL4970N Roll-forward recovery on database "PENGLOCL" cannot reach the
specified stop point (end-of-log or point-in-time) because of missing log
file(s) on node(s) "0".



It seems like we are really close but are missing something. Any help
would be appreciated.

Thanks,
Wayne

The net is we have a backup, and all we want to do is get the DB back up with the latest of whatever it has. How do we do that? If the hard drive
had went bad, the logs would have been lost anyway, so what good is
the backup image???

1. Try to find which log file is missing.

2. Use db2 history backup all for database and check the

backup image file and the the current log file.You need atleast that log file to use that backup.Since you have a missing log file to choose a backup to the log file that you have.

3. copy all your log files to another directory for safe keaping.

4. Copy your log files to a directory call it <logfiledir>

5. restore your database using the backup image.

6. db2 restore database <database_name> taken at <timestamp>.

7 Check the status of the database , it will be in rollforwad pending.

db2 rollforward database <database_name> query status.

It will give you the log file need to be applied.

8. use
db2 restore database <database_name> to end of logs overflow log path <logfiledir>.

9. It will show what the log files it has been applied.
It shows that db working.

10. You can use the command

db2 rollforward database <database_name> query status

it shows the same result.

11. When you are satisfied

db2 "rollforward database <db_name> stop/complete"

12. It will take out your database out of rollforward pending.

Also open another window, during that process

to see the diaglog file

tail -f db2diag.log.

Time is confusing to you, the image file has local time, the time that needs to be put are the cut time(GMT). You can find out the cut time by

date -u unix command.


I generally use the data, when I know the exact time and this must be a time greater or equal to your backup time . The END TIME at the history backup file. For example if you have

20050103183709 change to GMT or use the local time clause.


I hope this will help.

We have NO, NONE, NADA log files.... Does this mean our backup is useless?

You can call IBM support and I believe they can help you. Otherwise the backup is useless, even if no updates occurred while the on-line backup was running.

DB2 version 8.2 has a feature to include the needed log files in the backup itself when taking an on-line backup. Off-line backups don't need any log files for a recovery to the point in time of the backup.

Loosing or misplacing the log files active during an on-line backups (needed for restore) is a frequent topic on this newsgroup.

As Marcus told you contact IBM support.
"the backup is useless, even if no updates occurred while the on-line
backup was running"

For an online backup, no MATTER which backup, you need logfiles that
were created DURING the online backup.
Any backup that don't have log files are useless.
If you have backup that has these log files, you can use the log files that
are created during the backup and rollforward to the whatever,if any, log files you have.

regards

Mujeeb

Wayne, you have no log files at time of backup being taken ? All log files were deleted ? Is you Operating system team have anyway to restore deleted file, Or are they taking any tape backup for whole system ?

Ask these question to your cross functinal team , may be you get some clue.