Cross node restore question

rangupt · #1 (**permalink**) 08-04-08, 10:53

Hi all,

I have sample1 database on Production which I would like to restore on our test environment with the help of cross node recovery using db2adutl. I am already taking online backups of this sample1 db on prod. The db sample1 does not exists on TEST env.

ENV DETAILS:

PROD: AIX 5.3, DB2 UDB v8.1 FP 7, database is sample1 and instance is prodinst

TEST: AIX 5.3, DB2 UDB v8.1 FP 7, database to be restored is sample1 and the instance i am using is testinst

Here's what I am doing...

PROD:

backup db sample1 online use tsm

db2adutl grant user testinst on nodename TEST for db sample1

Code:

db2adutl queryaccess
	
Node                 Username             Database Name   Type
--------------------------------------------------------------
TEST          testinst                SAMPLE1         A
--------------------------------------------------------------
 Access Types:  B - backup images   L - logs   A - both

I also archived the logs on prod for sample1 by using "archive log for db sample1" command

TEST:

I can view the image on test by using the following command

Code:

db2adutl query db sample1 nodename PROD owner prodinst

--- ERROR! Database not found in system directory! ---

Query for database SAMPLE1


Retrieving FULL DATABASE BACKUP information.
    1 Time: 20080804091654  Oldest log: S0000000.LOG  DB Partition Number: 0    Sessions: 1

Retrieving LOG ARCHIVE information.
   Log file: S0000000.LOG, Chain Num: 0, DB Partition Number: 0, Taken at: 2008-08-04-09.15.39
   Log file: S0000001.LOG, Chain Num: 0, DB Partition Number: 0, Taken at: 2008-08-04-09.18.32

To restore the backup image, i do the following:

Code:

restore db sample1 use tsm options "'-fromnode=TEST -fromowner=prodinst'" taken at 20080804091654
DB20000I  The RESTORE DATABASE command completed successfully.

Now to be able to do rollforward recovery, I change the following db parameters:

Code:

update db cfg for sample1 using logarchopt1 "'-fromnode=TEST -fromowner=prodinst'"
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

update db cfg for sample1 using vendoropt "'-fromnode=TEST -fromowner=prodinst'"
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

Now I start the rollforward recovery:

Code:

connect to sample1
SQL1117N  A connection to or activation of database "SAMPLE1" cannot be made because of ROLL-FORWARD PENDING.  SQLSTATE=57019

rollforward db sample1 to end of logs and stop
SQL4970N  Roll-forward recovery on database "SAMPLE1" cannot reach the
specified stop point (end-of-log or point-in-time) because of missing log
file(s) on node(s) "0".

Now if I do rollfoward complete, DB2 tells me that roll-forward has passed a point greater than almost 5 hours ahead of my backup image

Code:

rollforward db sample1 complete
SQL1276N  Database "SAMPLE1" cannot be brought out of rollforward pending
state until roll-forward has passed a point in time greater than or equal to "2008-08-04-14.17.05.000000", because node "0" contains information later than the specified time.

I don't understand why db2 is doing this. why the timestamp is way ahead of the system time. Both the systems are at CDT. This is really confusing me. I have followed the steps mentioned in the following link but still can't make the rollforward work.

http://publib.boulder.ibm.com/infoce...n/c0011981.htm

Am I missing something?

Thanks

n_i · #2 (**permalink**) 08-05-08, 09:20

I can't tell why ROLLFORWARD cannot access the logs in TSM - I suggest you look in db2diag.log for specific error messages, possibly updating DIAGLEVEL to 4 first.

As for the timestamp, by default ROLLFORWARD uses UTC time, which I believe is exactly 5 hours ahead of CDT.

rangupt · #3 (**permalink**) 08-05-08, 13:20

I ran the test again still no luck. This time I tried to rollforward to a point in time instead of end of logs. I have copied a snippet of my db2diag.log

2008-08-05-11.34.00.293464-300 I201218A422 LEVEL: Warning
PID : 2519060 TID : 1 PROC : db2agent (SAMPLE1) 0
INSTANCE: testinst NODE : 000 DB : SAMPLE1
APPHDL : 0-429 APPID: *LOCAL.testinst.080805163359
FUNCTION: DB2 UDB, database utilities, sqludPrintStartingMsg, probe:1
DATA #1 : String, 53 bytes
Starting a full database restore. Agent PID: 2519060

2008-08-05-11.34.00.293854-300 I201641A449 LEVEL: Severe
PID : 2519060 TID : 1 PROC : db2agent (SAMPLE1) 0
INSTANCE: testinst NODE : 000 DB : SAMPLE1
APPHDL : 0-429 APPID: *LOCAL.testinst.080805163359
FUNCTION: DB2 UDB, database utilities, sqluCompareDB, probe:45
MESSAGE : DiagData
DATA #1 : Hexdump, 4 bytes
0x07800000205D4CD0 : 0000 09EB

2008-08-05-11.34.21.365568-300 I202091A371 LEVEL: Severe
PID : 3125308 TID : 1 PROC : db2med.2519060.0 0
INSTANCE: testinst NODE : 000
FUNCTION: DB2 UDB, database utilities, sqluReadFromVendorDevice, probe:34
MESSAGE : DiagData
DATA #1 : Hexdump, 4 bytes
0x0FFFFFFFFFFFDD60 : 0000 0017

2008-08-05-11.34.21.365972-300 I202463A371 LEVEL: Severe
PID : 3125308 TID : 1 PROC : db2med.2519060.0 0
INSTANCE: testinst NODE : 000
FUNCTION: DB2 UDB, database utilities, sqluReadFromVendorDevice, probe:35
MESSAGE : DiagData
DATA #1 : Hexdump, 4 bytes
0x0FFFFFFFFFFFDDA0 : 0000 4000

2008-08-05-11.34.33.087014-300 I202835A359 LEVEL: Warning
PID : 2519060 TID : 1 PROC : db2agent (SAMPLE1) 0
INSTANCE: testinst NODE : 000 DB : SAMPLE1
APPHDL : 0-429 APPID: *LOCAL.testinst.080805163359
FUNCTION: DB2 UDB, database utilities, sqludrsa, probe:100
MESSAGE : Restore Complete.

2008-08-05-11.35.05.133020-300 I203195A287 LEVEL: Event
PID : 1450182 TID : 1 PROC : db2flacc
INSTANCE: testinst NODE : 000
FUNCTION: DB2 UDB, config/install, sqlfLogUpdateCfgParam, probe:30
CHANGE : CFG DBM: "Diaglevel" From: "3" To: "4"

2008-08-05-11.35.23.136259-300 I203483A474 LEVEL: Event
PID : 1806342 TID : 1 PROC : db2agent (instance) 0
INSTANCE: testinst NODE : 000 DB : SAMPLE1
APPHDL : 0-432 APPID: *LOCAL.testinst.080805163523
FUNCTION: DB2 UDB, config/install, sqlfLogUpdateCfgParam, probe:20
CHANGE : CFG DB SAMPLE1: "Vendoropt" From: "-fromnode=PROD -fromowner=db2inst1" To: "-fromnode=PROD -fromowner=
db2inst1"

2008-08-05-11.35.37.385822-300 I203958A476 LEVEL: Event
PID : 1806342 TID : 1 PROC : db2agent (instance) 0
INSTANCE: testinst NODE : 000 DB : SAMPLE1
APPHDL : 0-432 APPID: *LOCAL.testinst.080805163523
FUNCTION: DB2 UDB, config/install, sqlfLogUpdateCfgParam, probe:20
CHANGE : CFG DB SAMPLE1: "Logarchopt1" From: "-fromnode=PROD -fromowner=db2inst1" To: "-fromnode=PROD -fromowne
r=db2inst1"

2008-08-05-11.36.05.670421-300 I204435A453 LEVEL: Severe
PID : 835784 TID : 1 PROC : db2agent (SAMPLE1) 0
INSTANCE: testinst NODE : 000 DB : SAMPLE1
APPHDL : 0-434 APPID: *LOCAL.testinst.080805163605
FUNCTION: DB2 UDB, base sys utilities, sqleFirstConnect, probe:100
MESSAGE : DiagData
DATA #1 : Hexdump, 4 bytes
0x00000001100D2274 : FFFF FBA3

2008-08-05-11.38.51.302039-300 I204889A404 LEVEL: Event
PID : 1806342 TID : 1 PROC : db2agent (instance) 0
INSTANCE: testinst NODE : 000 DB : SAMPLE1
APPHDL : 0-432 APPID: *LOCAL.testinst.080805163523
FUNCTION: DB2 UDB, config/install, sqlfLogUpdateCfgParam, probe:20
CHANGE : CFG DB SAMPLE1: "Newlogpath" From: "" To: "/home/testinst"

2008-08-05-11.42.30.815868-300 I205294A387 LEVEL: Error
PID : 835784 TID : 1 PROC : db2agent (SAMPLE1) 0
INSTANCE: testinst NODE : 000 DB : SAMPLE1
APPHDL : 0-437 APPID: *LOCAL.testinst.080805164230
FUNCTION: DB2 UDB, data protection, sqlpgnlp, probe:1240
MESSAGE : Active log path is moved to /home/testinst/NODE0000/

2008-08-05-11.42.30.878133-300 I205682A453 LEVEL: Severe
PID : 835784 TID : 1 PROC : db2agent (SAMPLE1) 0
INSTANCE: testinst NODE : 000 DB : SAMPLE1
APPHDL : 0-437 APPID: *LOCAL.testinst.080805164230
FUNCTION: DB2 UDB, base sys utilities, sqleFirstConnect, probe:100
MESSAGE : DiagData
DATA #1 : Hexdump, 4 bytes
0x00000001100D2274 : FFFF FBA3 ....

2008-08-05-11.42.31.162883-300 E206136A389 LEVEL: Warning
PID : 835784 TID : 1 PROC : db2agent (SAMPLE1) 0
INSTANCE: testinst NODE : 000 DB : SAMPLE1
APPHDL : 0-438 APPID: *LOCAL.testinst.080805164231
FUNCTION: DB2 UDB, data protection, sqlufrol, probe:980
MESSAGE : ADM1602W Rollforward recovery has been initiated.

2008-08-05-11.42.31.163761-300 E206526A432 LEVEL: Warning
PID : 835784 TID : 1 PROC : db2agent (SAMPLE1) 0
INSTANCE: testinst NODE : 000 DB : SAMPLE1
APPHDL : 0-438 APPID: *LOCAL.testinst.080805164231
FUNCTION: DB2 UDB, data protection, sqlufrol, probe:1210
MESSAGE : ADM1603I DB2 is invoking the forward phase of the database
rollforward recovery.

2008-08-05-11.42.31.164066-300 I206959A472 LEVEL: Warning
PID : 835784 TID : 1 PROC : db2agent (SAMPLE1) 0
INSTANCE: testinst NODE : 000 DB : SAMPLE1
APPHDL : 0-438 APPID: *LOCAL.testinst.080805164231
FUNCTION: DB2 UDB, recovery manager, sqlpForwardRecovery, probe:720
DATA #1 : String, 103 bytes
Invoking database rollforward forward recovery,
lowtranlsn 0000000000FA000C minbufflsn 0000000000FA000C

2008-08-05-11.42.31.180681-300 I207432A408 LEVEL: Warning
PID : 835784 TID : 1 PROC : db2agent (SAMPLE1) 0
INSTANCE: testinst NODE : 000 DB : SAMPLE1
APPHDL : 0-438 APPID: *LOCAL.testinst.080805164231
FUNCTION: DB2 UDB, recovery manager, sqlprecm, probe:2000
MESSAGE : Using parallel recovery with 3 agents 3 QSets 9 queues and 2 chunks

2008-08-05-11.42.31.368838-300 I207841A339 LEVEL: Warning
PID : 1552520 TID : 1 PROC : db2loggr (SAMPLE1) 0
INSTANCE: testinst NODE : 000
FUNCTION: DB2 UDB, recovery manager, sqlpgSwitchFromRedoToUndo, probe:1880
MESSAGE : rc -2045837302 when trying to open log extent 2, index 0 TailIndex 0

2008-08-05-11.42.31.369522-300 I208181A313 LEVEL: Warning
PID : 1552520 TID : 1 PROC : db2loggr (SAMPLE1) 0
INSTANCE: testinst NODE : 000
FUNCTION: DB2 UDB, data protection, sqlpgCallGIFL, probe:1250
MESSAGE : New log extent 2, PId 1217859368, FirstLSN 000000FA0000

2008-08-05-11.42.31.610298-300 I208495A320 LEVEL: Warning
PID : 1552520 TID : 1 PROC : db2loggr (SAMPLE1) 0
INSTANCE: testinst NODE : 000
FUNCTION: DB2 UDB, recovery manager, sqlpgSwitchFromRedoToUndo, probe:3220
MESSAGE : nextLsn 0000000000FA000C at the start of log page

2008-08-05-11.42.31.611470-300 I208816A488 LEVEL: Error
PID : 835784 TID : 1 PROC : db2agent (SAMPLE1) 0
INSTANCE: testinst NODE : 000 DB : SAMPLE1
APPHDL : 0-438 APPID: *LOCAL.testinst.080805164231
FUNCTION: DB2 UDB, recovery manager, sqlprDoForwardPhase, probe:770
MESSAGE : Rollforward is not able to reach the end of online backup (or split
mirror), nextLsn 0000000000FA000C LastRecLsnLFH 000000BB8025

2008-08-05-11.42.31.611843-300 I209305A441 LEVEL: Error
PID : 835784 TID : 1 PROC : db2agent (SAMPLE1) 0
INSTANCE: testinst NODE : 000 DB : SAMPLE1
APPHDL : 0-438 APPID: *LOCAL.testinst.080805164231
FUNCTION: DB2 UDB, recovery manager, sqlpForwardRecovery, probe:1230
RETCODE : ZRC=0x801000BB=-2146434885=SQLPR_MISSING_LOGFILES
"rollforward missing log files"

2008-08-05-11.42.31.612316-300 I209747A476 LEVEL: Error
PID : 835784 TID : 1 PROC : db2agent (SAMPLE1) 0
INSTANCE: testinst NODE : 000 DB : SAMPLE1
APPHDL : 0-438 APPID: *LOCAL.testinst.080805164231
FUNCTION: DB2 UDB, recovery manager, sqlpForwardRecovery, probe:670
MESSAGE : dbcb->logfhdr.firstDeleteFile:
DATA #1 : Hexdump, 4 bytes
0x0780000020010264 : FFFF FFFF

DB2 seems to be looking for a log file 'S4967294.LOG' which does not even exists. I ran db2flsn and got this...

Code:

db2flsn 0000000000FA000C
Warning: the result is based on the last known log file size (2000
4K pages starting from log extent 0).  The input_LSN might be before
the database becomes recoverable.
Given LSN is contained in log page 1001 in log file S4967294.LOG

rangupt · #4 (**permalink**) 08-11-08, 11:12

For all those who are trying to do a cross node recovery like me and are not successful. I found the problem. The fixpak that I was at didn't allow rollforward recovery to a different instance name. Here's the APAR for that

http://www-1.ibm.com/support/docview...=utf-8&lang=en

Once I changed the instance name on my target (test) env to same as production env, i was able to successfully complete the rollforward.

Internet Services

Web Development

Digital Marketing

Consumer Tech