September 29, 2009
DB2 restore with rollforward
I recently had a few problem trying to restore a DB2 online compressed backup from one AIX machine to another.
I thought it would have been a simple process as saying
which worked fine, Untill i tried to connect to the database. That is when i got the error
SQL1117N A connection to or activation of database “AM2″ cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
So i tried searching for a solution and tried everything i could.
SQL4970N Roll-forward recovery on database “MYDB” cannot reach the specifiedstop point (end-of-log or point-in-time) because of missing or corrupted log
file(s) on database partition(s) “0″. Roll-forward recovery processing has
halted on log file “S0001797.LOG”.
db2 => rollforward db MYDB complete
SQL1276N Database “MYDB” cannot be brought out of rollforward pending state
until roll-forward has passed a point in time greater than or equal to
“2009-09-25-06.39.04.000000 UTC”, because node “0″ contains information later
than the specified time.
db2 => rollforward db MYDB to 2009-09-25-06.39.04.000000 and stop
SQL1274N The database “MYDB” requires roll-forward recovery and the
point-in-time must be to the end of logs.
db2 => rollforward db MYDB query status
Rollforward Status
Input database alias = MYDB
Number of nodes have returned status = 1
Node number = 0
Rollforward status = DB working
Next log file to be read = S0001797.LOG
Log files processed = S0001795.LOG – S0001796.LOG
Last committed transaction = 2009-09-25-06.39.04.000000 UTC
To cut it short this is what i did to fix it.
DB20000I The RESTORE DATABASE command completed successfully.
db2 => rollforward db MYDB to end of logs and stop overflow log path (/tmp) noretrieve
Rollforward Status
Input database alias = MYDB
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0001795.LOG – S0001799.LOG
Last committed transaction = 2009-09-25-06.39.04.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
db2 => connect to MYDB
Database Connection Information
Database server = DB2/AIX64 9.1.4
SQL authorization ID = DB2INST1
Local database alias = MYDB
Comments(2)
