UTC problem at online tablespace rollforward
cat
I'm trying to familiarize myself with the process of backup, restore
and follforward.
I did an offline backup and online tablespace backup(I set logretain=
recovery), my recovery history file looks as following:
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------
--------------
B D 20030121211900001 F D S0000004.LOG S0000004.LOG
----------------------------------------------------------------------------
Contains 2 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
----------------------------------------------------------------------------
Comment: DB2 BACKUP SAMPLE OFFLINE
Start Time: 20030121211900
End Time: 20030121211925
----------------------------------------------------------------------------
00009 Location: /home/db2inst1
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup
ID
-- --- ------------------ ---- --- ------------ ------------
--------------
B P 20030121212023001 N D S0000004.LOG S0000005.LOG
----------------------------------------------------------------------------
Contains 1 tablespace(s):
00001 USERSPACE1
----------------------------------------------------------------------------
Comment: DB2 BACKUP SAMPLE ONLINE
Start Time: 20030121212023
End Time: 20030121212045
----------------------------------------------------------------------------
00010 Location: /home/db2inst1
Then I take the step to restore the database as following:
1.restore database sample tablespace (userspace1) online taken at
20030121212023
DB20000I The RESTORE DATABASE command completed successfully.
2.db2 => rollforward database sample query status
Rollforward Status
Input database alias = sample
Number of nodes have returned status = 1
Node number = 0
Rollforward status = TBS pending
Next log file to be read = S0000009.LOG
Log files processed = -
Last committed transaction = 2003-01-22-02.26.29.000000
3.rollforward database sample to 2003-01-22-02.26.29.000000 tablespace
(userspace1) online
Rollforward Status
Input database alias = sample
Number of nodes have returned status = 1
Node number = 0
Rollforward status = TBS working
Next log file to be read = S0000008.LOG
Log files processed = -
Last committed transaction = 2003-01-22-02.26.29.000000
DB20000I The ROLLFORWARD command completed successfully.
4.rollforward database sample stop ( Then I get the error message as
following)
SQL4906N The list of table space names specified is an incomplete set
for the
rollforward operation.
The thing confused me is if I issue 'rollforward database sample
complete', it going smooth. I check the IBM document, it said the
command rolls-forward time is UTC, but how can you decide the UTC time
of a UOW, Let's say if we have two transaction after we did the backup
image, how can we rollforward to each point of time commit the UOW.( I
think we don't have that kind of luck always rollforwd to end of logs
;-)
Cat
Lee Dilworth
tablespace userspace1 from the tablespace level backup but you have not
rolled it forward past its minimum recovery point in time. To see this value
you need to run "list tablespaces show detail" this will have the value you
need and ensures the tablespace is sync'd with the catalogs.
once you have this time you need to run something like the commands below
but sub in your time obviously :)
db2 rollforward database sample to 2003-01-22-11.55.31.000000 and
stop tablespace (userspace1) online
if you now run the "rollforward....query status" command you should see "not
pending" and you can connect to the database. BUT your tablespace userspace1
will now be in backup pending state, so you will need to perform another
backup to change this to "Normal"
Lee Dilworth
BUT your tablespace userspace1
> will now be in backup pending state, so you will need to perform another
> backup to change this to "Normal"
ooops, forgot to add this can simply be a tablespace level backup again.....
cat
I tried that, it works. But still has the problem how to rollforward
to point of time ( transaction commited time).
As you mentioned
once you have this time you need to run something like the commands
below
but sub in your time obviously :)
db2 rollforward database sample to 2003-01-22-11.55.31.000000 and
stop tablespace (userspace1) online
That time should be UTC, but how can I find out the UTC time of the
transaction. I tried to create an event monitor of statements and
output it. it looks like following:
17) Statement Event ...
Appl Handle: 78
Appl Id: *LOCAL.DB2.030122185651
Appl Seq number: 0001
Record is the result of a flush: FALSE
-------------------------------------------
Type : Dynamic
Operation: Execute Immediate
Section : 203
Creator : NULLID
Package : SQLC2D01
Cursor :
Cursor was blocking: FALSE
Text : update staff set name='ingrid' where id=350
-------------------------------------------
Start Time: 01-22-2003 14:07:54.558383
Stop Time: 01-22-2003 14:07:54.560824
Exec Time: 0.002441 seconds
Number of Agents created: 1
User CPU: 0.000000 seconds
System CPU: 0.000000 seconds
Fetch Count: 0
Sorts: 0
Total sort time: 0
Sort overflows: 0
Rows read: 2
Rows written: 1
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
SQLCA:
sqlcode: 0
sqlstate: 00000
24) Statement Event ...
Appl Handle: 78
Appl Id: *LOCAL.DB2.030122185651
Appl Seq number: 0001
Record is the result of a flush: FALSE
-------------------------------------------
Type : Dynamic
Operation: Execute Immediate
Section : 203
Creator : NULLID
Package : SQLC2D01
Cursor :
Cursor was blocking: FALSE
Text : update staff set name='lily' where id=350
-------------------------------------------
Start Time: 01-22-2003 14:08:15.034283
Stop Time: 01-22-2003 14:08:15.036549
Exec Time: 0.002266 seconds
Number of Agents created: 1
User CPU: 0.000000 seconds
System CPU: 0.000000 seconds
Fetch Count: 0
Sorts: 0
Total sort time: 0
Sort overflows: 0
Rows read: 2
Rows written: 1
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
SQLCA:
sqlcode: 0
sqlstate: 00000
Let's say I try to rollforward to the stats just after the first
update transaction.
db2 rollforward database sample to (transaction) UTC time and stop
tablespace (userspace1) online
How to define the UTC time of that transaction?
Cat
"Lee Dilworth" <lee_dilwo...@hotmail.com> wrote in message news:<b0m25a$r3d40$1...@ID-118488.news.dfncis.de>...
cat
decide the UTC time.
cheers
ingrid...@yahoo.com (cat) wrote in message news:<74018764.0301...@posting.google.com>...
Lee Dilworth
too (they do on my systems) but then I haven't tested any timezones other
than GMT0 so my UTC offset is always 0 so dont know if output will look
different..........
if you wanted to see the commit times for all your txns since last online
tbs backup then "rollforward query status" is only ever going to show you
the last committed, however, i am pretty sure that the "Recovery Expert" db2
tool will allow you to see these times. but you have to buy this tool:
http://www-3.ibm.com/software/data/db2imstools/db2tools/db2recovexpert.html
another point is that when restoring tbs level backup use the output from
"list histroy backup all for <db>" to obtain the time the earliest time you
can rollforward to and issue the stop or complete clause for ROLLFORWARD
command.
Contains 1 tablespace(s):
00001 USERSPACE1
---------------------------------------------------------------------------
-
Comment: DB2 BACKUP SAMPLE ONLINE
Start Time: 20030123131415
End Time: 20030123131436
based on this if you tried to rollforward to a time before 20030123131436
you would see the message:
SQL1275N The stoptime passed to roll-forward must be greater than or equal
to
"2003-01-23-13.14.36.000000", because database "SAMPLE" on node(s) "0"
contains information later than the specified time.
but I could run:
db2 rollforward db sample to 2003-01-23-13.14.36.000000 and stop
tablespace (userspace1) online
then backup the tbs, and you will find any txns that occurred after your tbs
backup will have been rolled back.