We are running DB2 UDB 8.1 fp 14 on AIX using archival logging and
online backups. I was running some disaster tests and found my
database restore was not restoring to the latest active log. Online
backup include logs. For this test I created a disaster where we lost
the database about 30 minutes after the last backup. Current archive
and active logs were saved off and I ran a Restore of the latest
backup and rollforward to the end of logs.
Archive logs prior to database restore (after disaster):
1613824 Sep 11 17:29 S0002795.LOG
1966080 Sep 11 18:30 S0002796.LOG
65536 Sep 11 18:35 S0002797.LOG
86016 Sep 11 18:58 S0002798.LOG
2453504 Sep 11 20:00 S0002799.LOG <---before the backup time
3252224 Sep 11 21:01 S0002800.LOG
4202496 Sep 11 21:30 S0002801.LOG
Active logs prior to database restore (after disaster):
512 Jul 02 23:52 SQLLPATH.TAG
4202496 Aug 26 18:00 S0001487.LOG
3383296 Aug 26 22:55 S0001486.LOG
4202496 Sep 11 18:57 S0002806.LOG
4202496 Sep 11 18:57 S0002805.LOG
4202496 Sep 11 18:57 S0002804.LOG
4202496 Sep 11 18:57 S0002803.LOG
4202496 Sep 11 18:57 S0002807.LOG
4202496 Sep 11 18:58 S0002808.LOG
2453504 Sep 11 19:59 S0002799.LOG
4202496 Sep 11 20:00 S0002809.LOG
4202496 Sep 11 21:03 S0002810.LOG
4202496 Sep 11 21:44 S0002811.LOG
4202496 Sep 11 21:49 S0002802.LOG <---current active log. Had
completed transactions before disaster
Here is the Restore statement used:
//copies logs to the current active log path restore the
database
db2 restore db mydb user myuser using mypswrd from /usr/test/
backup/ taken at 20080911210038 to '/db2/database/syscat' logtarget /
db2/logs/test/NODE0000/ newlogpath /db2/logs/test replace history
file replace existing
This is what our log active log directory looks like after the copy/
restore
-rwxrwx--- 1 xx yy 3383296 Aug 26 22:55 /db2/logs/test/NODE0000/
S0001486.LOG
-rwxrwx--- 1 xx yy 4202496 Aug 26 18:00 /db2/logs/test/NODE0000/
S0001487.LOG
-rwxrwx--- 1 xx yy 2453504 Sep 11 19:59 /db2/logs/test/NODE0000/
S0002799.LOG
-rw------- 1 xx yy 3252224 Sep 12 16:30 /db2/logs/test/NODE0000/
S0002800.LOG <-- from backup
-rwxrwx--- 1 xx yy 4202496 Sep 11 21:49 /db2/logs/test/NODE0000/
S0002802.LOG
-rwxrwx--- 1 xx yy 4202496 Sep 11 18:57 /db2/logs/test/NODE0000/
S0002803.LOG
-rwxrwx--- 1 xx yy 4202496 Sep 11 18:57 /db2/logs/test/NODE0000/
S0002804.LOG
-rwxrwx--- 1 xx yy 4202496 Sep 11 18:57 /db2/logs/test/NODE0000/
S0002805.LOG
-rwxrwx--- 1 xx yy 4202496 Sep 11 18:57 /db2/logs/test/NODE0000/
S0002806.LOG
-rwxrwx--- 1 xx yy 4202496 Sep 11 18:57 /db2/logs/test/NODE0000/
S0002807.LOG
-rwxrwx--- 1 xx yy 4202496 Sep 11 18:58 /db2/logs/test/NODE0000/
S0002808.LOG
-rwxrwx--- 1 xx yy 4202496 Sep 11 20:00 /db2/logs/test/NODE0000/
S0002809.LOG
-rwxrwx--- 1 xx yy 4202496 Sep 11 21:03 /db2/logs/test/NODE0000/
S0002810.LOG
-rwxrwx--- 1 xx yy 4202496 Sep 11 21:44 /db2/logs/test/NODE0000/
S0002811.LOG
-rwxrwx--- 1 hdbuxx yy 512 Jul 2 23:52 /db2/logs/test/
NODE0000/SQLLPATH.TAG
The Restore copied archive log S0002800.LOG from the backup to our
directory. Now we perform the rollforward:
db2 rollforward db mydb user myuser using mypswd to end of logs and
stop
Rollforward Status
Input database alias = inventry
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0002800.LOG - S0002801.LOG
Last committed transaction = 2008-09-11-21.30.43.000000
According to my logs it seems it should have rolled through
S0002800.LOG - S0002802.LOG. It looks like DB2 rolled through
S0002800.LOG archive log from it's backup, then rolled through
S0002801.LOG that was archived after the backup was taken (and is
located in the archive directory. However, S0002802.LOG was the
active log at the time of the "crash" (not an archive log) and was not
rolled forward.
DB2 Info Center says that the ROLLFORWARD command rolls through
archive logs. Does this mean we are missing a command that needs to
be performed after we rollforward though the archive logs so that it
picks up the active log changes?
Thanks for helping!
online backups. I was running some disaster tests and found my
database restore was not restoring to the latest active log. Online
backup include logs. For this test I created a disaster where we lost
the database about 30 minutes after the last backup. Current archive
and active logs were saved off and I ran a Restore of the latest
backup and rollforward to the end of logs.
Archive logs prior to database restore (after disaster):
1613824 Sep 11 17:29 S0002795.LOG
1966080 Sep 11 18:30 S0002796.LOG
65536 Sep 11 18:35 S0002797.LOG
86016 Sep 11 18:58 S0002798.LOG
2453504 Sep 11 20:00 S0002799.LOG <---before the backup time
3252224 Sep 11 21:01 S0002800.LOG
4202496 Sep 11 21:30 S0002801.LOG
Active logs prior to database restore (after disaster):
512 Jul 02 23:52 SQLLPATH.TAG
4202496 Aug 26 18:00 S0001487.LOG
3383296 Aug 26 22:55 S0001486.LOG
4202496 Sep 11 18:57 S0002806.LOG
4202496 Sep 11 18:57 S0002805.LOG
4202496 Sep 11 18:57 S0002804.LOG
4202496 Sep 11 18:57 S0002803.LOG
4202496 Sep 11 18:57 S0002807.LOG
4202496 Sep 11 18:58 S0002808.LOG
2453504 Sep 11 19:59 S0002799.LOG
4202496 Sep 11 20:00 S0002809.LOG
4202496 Sep 11 21:03 S0002810.LOG
4202496 Sep 11 21:44 S0002811.LOG
4202496 Sep 11 21:49 S0002802.LOG <---current active log. Had
completed transactions before disaster
Here is the Restore statement used:
//copies logs to the current active log path restore the
database
db2 restore db mydb user myuser using mypswrd from /usr/test/
backup/ taken at 20080911210038 to '/db2/database/syscat' logtarget /
db2/logs/test/NODE0000/ newlogpath /db2/logs/test replace history
file replace existing
This is what our log active log directory looks like after the copy/
restore
-rwxrwx--- 1 xx yy 3383296 Aug 26 22:55 /db2/logs/test/NODE0000/
S0001486.LOG
-rwxrwx--- 1 xx yy 4202496 Aug 26 18:00 /db2/logs/test/NODE0000/
S0001487.LOG
-rwxrwx--- 1 xx yy 2453504 Sep 11 19:59 /db2/logs/test/NODE0000/
S0002799.LOG
-rw------- 1 xx yy 3252224 Sep 12 16:30 /db2/logs/test/NODE0000/
S0002800.LOG <-- from backup
-rwxrwx--- 1 xx yy 4202496 Sep 11 21:49 /db2/logs/test/NODE0000/
S0002802.LOG
-rwxrwx--- 1 xx yy 4202496 Sep 11 18:57 /db2/logs/test/NODE0000/
S0002803.LOG
-rwxrwx--- 1 xx yy 4202496 Sep 11 18:57 /db2/logs/test/NODE0000/
S0002804.LOG
-rwxrwx--- 1 xx yy 4202496 Sep 11 18:57 /db2/logs/test/NODE0000/
S0002805.LOG
-rwxrwx--- 1 xx yy 4202496 Sep 11 18:57 /db2/logs/test/NODE0000/
S0002806.LOG
-rwxrwx--- 1 xx yy 4202496 Sep 11 18:57 /db2/logs/test/NODE0000/
S0002807.LOG
-rwxrwx--- 1 xx yy 4202496 Sep 11 18:58 /db2/logs/test/NODE0000/
S0002808.LOG
-rwxrwx--- 1 xx yy 4202496 Sep 11 20:00 /db2/logs/test/NODE0000/
S0002809.LOG
-rwxrwx--- 1 xx yy 4202496 Sep 11 21:03 /db2/logs/test/NODE0000/
S0002810.LOG
-rwxrwx--- 1 xx yy 4202496 Sep 11 21:44 /db2/logs/test/NODE0000/
S0002811.LOG
-rwxrwx--- 1 hdbuxx yy 512 Jul 2 23:52 /db2/logs/test/
NODE0000/SQLLPATH.TAG
The Restore copied archive log S0002800.LOG from the backup to our
directory. Now we perform the rollforward:
db2 rollforward db mydb user myuser using mypswd to end of logs and
stop
Rollforward Status
Input database alias = inventry
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0002800.LOG - S0002801.LOG
Last committed transaction = 2008-09-11-21.30.43.000000
According to my logs it seems it should have rolled through
S0002800.LOG - S0002802.LOG. It looks like DB2 rolled through
S0002800.LOG archive log from it's backup, then rolled through
S0002801.LOG that was archived after the backup was taken (and is
located in the archive directory. However, S0002802.LOG was the
active log at the time of the "crash" (not an archive log) and was not
rolled forward.
DB2 Info Center says that the ROLLFORWARD command rolls through
archive logs. Does this mean we are missing a command that needs to
be performed after we rollforward though the archive logs so that it
picks up the active log changes?
Thanks for helping!