By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,649 Members | 2,142 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,649 IT Pros & Developers. It's quick & easy.

regarding tablespace PIT recovery.

P: n/a
hi all,
i was working on DB2 and came across an issue. i.e. can a tablespace be
rollforwarded to POINT-IN-TIME?

the scenario is as follows:
i created a data base and a tablespace in it.
i have enabled archival logging (enabled userexit and logretain) and
changed log path to a different location.
as the log path and logging method was changed, the database was in
backup pending state. so, i took a complete offline backup of the
database (say image1 and time t1)
And then i created a table and inserted some records into it (say 10
and time t2)
now i took a complete online backup of database (say image2 and time
t3)
after the backup action, i inserted some more records (say 5 and time
t4)
now i dropped the table (time t5)
i was trying to restore table which i dropped. so, i restored the
tablespace from the backup image2 and rollforwarded it to time t4.
but there was a pop up saying that tablespace cannot be rollforwaded to
t4. it should be either till t5 or end of logs.
when i rollforwarded tablespace to end of logs or time t5, the table
was found to be missing still......
now, i restored whole database from backup image2 and rollforwarded to
time t4.
when i checked the list of tables available, i could see the table and
records till time t4.

IS THERE ANY WAY TO ROLLFORWARD TABLESPACE TO POINT-IN-TIME?

May 17 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
You can always roll forward a tablespace to a PIT. The issue is that the
PIT an never be PRIOR to what it has been fixed for the tablespace.
This fixed time is determined by
A) The timestamp of the las backup if condition 2 is not met
2) The timestamp of the last DDL statement that was run against the
tablespace or any of its components.
Example`
Valid backup for TS1 exists at Time1
at Time2, alter table T1 in TS1, add a column (17 cols + 1). This also
updates the catalog tables.
at Time3, restore tablespace from backup image.
at Time3, roll forward restored tablespace, PRIOR to Time3. Command bounces
with your error message.
Why? The restored tablespace could not be rolled forward to the time chosen
because your table would show 17 cols, prior to alter which is where you
want to be. But the catalog tables SYSIBM.SYSTABLES shows 18 cols. and
SYSIBM.SYSCOLUMNS shows a column (the one you added) but it is not in the
restored, rolled forward tablespace. Ding, you lost because catalog always
wins.

Doing what you did at DATABASE level works, because both the tablespace and
the catalog tablespace are rolled forward together in sync.

Morale, tblspc. can be rolled forward to PIT byt always beyond their minimum
PIT. The minimum PIT can be found if you do:
db2 list tablespaces show detail
The last line will show it to you if it exists. If you don't see it then
the minimum PIT is the timestamp of the backup.

HTH, Pierre

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Raghava" <cv*******@gmail.com> a écrit dans le message de news:
11**********************@i39g2000cwa.googlegroups. com...
hi all,
i was working on DB2 and came across an issue. i.e. can a tablespace be
rollforwarded to POINT-IN-TIME?

the scenario is as follows:
i created a data base and a tablespace in it.
i have enabled archival logging (enabled userexit and logretain) and
changed log path to a different location.
as the log path and logging method was changed, the database was in
backup pending state. so, i took a complete offline backup of the
database (say image1 and time t1)
And then i created a table and inserted some records into it (say 10
and time t2)
now i took a complete online backup of database (say image2 and time
t3)
after the backup action, i inserted some more records (say 5 and time
t4)
now i dropped the table (time t5)
i was trying to restore table which i dropped. so, i restored the
tablespace from the backup image2 and rollforwarded it to time t4.
but there was a pop up saying that tablespace cannot be rollforwaded to
t4. it should be either till t5 or end of logs.
when i rollforwarded tablespace to end of logs or time t5, the table
was found to be missing still......
now, i restored whole database from backup image2 and rollforwarded to
time t4.
when i checked the list of tables available, i could see the table and
records till time t4.

IS THERE ANY WAY TO ROLLFORWARD TABLESPACE TO POINT-IN-TIME?


May 17 '06 #2

P: n/a
Like Pierre explained below, you can only rollforward to a PIT that is
equal or greater to the MRT (minimum recovery time) of the tablespace,
this has to do with the need to keep the database objects definitions
in synch with the catalogs.

If all you're interested in is recovering the dropped table, I think
there is an option to rollforward that will export all of your table
data prior to processing the drop table log record. That data can then
be used to recreate your table after the completion of the rollorward.
Read up on the "recover dropped table" rollforward option here:
http://publib.boulder.ibm.com/infoce...e/r0001978.htm

Hope this helps,
Miro

May 18 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.