Hi guys,

Here is a scenario.
Online backup finishes 3 hours later in production. Logs are included.
Restore restores database and logs are extracted to 'h:\daily_backup\logs'.

I count and check that logs are 40 in count and 21 GB in size.

I decide not to go for full rollforward, but minimum to bring database out of rollforward-pending state. Data accuracy is not crucial as its a mirror database. So I decide to just start rollforward but to nearest point-in-time and complete.

I want to issue command like

ROLLFORWARD DB POS TO 20090614024601 AND COMPLETE OVERFLOW LOG PATH ('h:\daily_backup\logs')

But I donot know how to find nearest point-in-time so I always fail to provide correct one.
Hence the errror
Can someone help me find correct point-in-time token (first one after backup starts) so that it takes minimum time for roll-forward.


Thanks
DBFinder

Use a timestamp 10 years ago. DB2 will give you an error message which tells you the minimum rollforward time you can roll-forward to. I would also use the NORETRIEVE option on the rollforward.

Hi,


SQL1276N Database "POS" cannot be brought out of rollforward pending state until roll-forward has passed a point in time greater than or equal to "2009-06-14-07.46.01.000000 UTC", because node "0" contains information later than the specified time.

This message says that database should be rolled foward to "2009-06-14-07.46.01.000000 UTC" (which is min PIT to which you should rollfrward the database)

Please keep in mind that the time you indicate in the rollforward command is in UTC, unless you also specify "using local time".

Thanks all,

I got some idea.
But I think we donot have any way to determine first, second , third PITs by examining logfiles OR any SYS table that can provide these PITs.

The incident Sunday morning can tell upon my employment unless I have really vast knowledge of how and why rollforward can take that long.

Even though I know that I did not even touch anything, It was restoring and rollingforward as a scheduled job, no one will be ready to absorb that a database was rolling forward for over 26 hours and still did not complete.

And all I can tell them cannot be only that I read from books or certification.


Thanks anyway, I will be studying more for this kind of situations.

DBFinder

Database history (LIST HISTORY) and the backup image itself (db2ckbkp) can give you the completion timestamp of the backup, after which any timestamp will be valid for rolling forward.

You can monitor the rollforward progress with the LIST UTILITIES command or with "db2pd -utilities".

Thanks Nick,

Your prompt response encouraged me to study furthur how can db2pd will help me.
I had been using list utilities but never used db2pd.
Thanks again

DBFinder

Here is what I do when I want to rollforward to a minimum PIT (end of online backup):


- if logs are included in the backup image, then extract the logs to some dir and rollforward using the overflow log path and noretrieve option.


- if logs are not included, then two options:

1) issue rollforward stop and without specifying end of logs or PIT. For example: "db2 rollforward db <db name> stop". This command will return sql1276n and give you the PIT in UTC which is the minimum PIT

or

2) issue "db2 list history backup all for db <db name>" and look for the timestamp of your backup image. Once you found it, check the "Earliest Log" and "Current Log". This is the logs range you need to provide in order to rollforward to a minimum PIT. Copy these logs to some dir and issue rollforward using the overflow log path and noretrieve option.

Hi Bella,

Alright, in our setup we have been doing exactly all this for years except minimum PIT.

I used this for min PIT

rollforward DB POS complete logs overflow path ('h:\daily_backup\logs')

This generated same error as I listed in beg.

Then I used with to <timestamp> but I got same error.

Still wondering why, I should open a PMR.

Thanks
DBFinder

From your post:

ROLLFORWARD DB POS TO 20090614024601 AND COMPLETE OVERFLOW LOG PATH ('h:\daily_backup\logs')

Quote:
SQL1276N Database "POS" cannot be brought out of rollforward pending state until roll-forward has passed a point in time greater than or equal to "2009-06-14-07.46.01.000000 UTC", because node "0" contains information later than the specified time.


The minimum PIT is 2009-06-14-07.46.01.000000 UTC

You specified 20090614024601. This is not a valid timestamp format, but db2 didn't complain for some reason... The valid format is 2009-06-14-07.... Anyway, the reason it didn't work is because you specified a local time on the rollforward command but forgot to use "using local time". The command you needed to use is:

db2 "rollforward db pos to 2009-06-14-07.46.01.000000 and complete overflow..." - this is in UTC (by default)

or

execute db2 "values current timezone" to get the difference between UTC and local tme. For example:

db2 "values current timezone"

1
--------
-40000.


In my example, I need to substrat 4 hrs

db2 "rollforward db pos to 2009-06-14-03.46.01.000000 using local time and complete overflow..." - this is in local time

Actually I did mean UTC. DB2 takes this format too yyyymmdd.

DBFinder

To rollforward to a minimum PIT in UTC - you need to specify 2009-06-14-07.46.01.000000 or 20090614074601 (if it takes this format)

That's fine,

but now I got surprised

This happend just now while I was trying the command as you suggested.

DBFinder

ps: this is different DB

2009-07-11-17.15.15.000000 is in future. Was your OS time set to the future sometime in the past? If it was, the log records will store the future time.