Rollforward on a backup server to another db name

Sometimes it’s handy to restore a production database on a testing server to a particular point in time (PIT). This procedure involves several nuances.

First of all production databases usually have much more memory and larger buffer pools. If you have less memory (as in our case) on a testing server, then your database just won’t rollforward because it won’t be able to activate database which in its turn is due to inability to activate buffer pools. In this situation you also cannot alter buffer pools manually because you cannot connect to the database. Looks like a deadlock. Solution here is to use DB2_OVERRIDE_BPF registry variable with say 5000 pages. After a database restart all buffer pools will be of 5000 pages size. Then you will be able to alter your buffer pools. Don’t forget to unset DB2_OVERRIDE_BPF and restart after you finished all maintenance procedures. I do that inside a batch script:

db2set DB2_OVERRIDE_BPF=5000
db2stop force
db2start

db2cmd -w -c db2 -t -f db_rf_restore.db2 -z db_rf_restore.err

db2set DB2_OVERRIDE_BPF=
db2stop force
db2start

Actual restore is done inside db_rf_restore.db2 DB2 CLP script. Important point here is to use -w flag which will wait for script completion before moving on to the next command. And -c which will automatically close Command Window after script has been run. Otherwise you will need to do it by hand.

Another matter here is how to rollforward a database if you changed its name and moved it to another server. To accomplish that you will need to copy archival (don’t confuse them with active) logs from a production server to your testing server and use OVERFLOW LOG PATH (“D:\backup\tlogs”) clause in ROLLFORWARD DATABASE command to point to directory where you copied them to. Use also AND COMPLETE clause to finish rollforward process and turn off rollforward pending state.

Here is the complete restore script:

DROP DATABASE db_2;

CREATE DATABASE db_2 ON D:;

RESTORE DATABASE db
FROM “D:\backup”
TO D:
INTO db_2
WITH 2 BUFFERS
BUFFER 1024
PARALLELISM 1
COMPRLIB C:\SQLLIB\BIN\db2compr.dll
WITHOUT PROMPTING;

ROLLFORWARD DATABASE db_2
TO 2012-02-16-12.30.00.000000
AND COMPLETE
OVERFLOW LOG PATH (“D:\backup\tlogs”);

CONNECT TO db_2;
ALTER BUFFERPOOL data_pool SIZE 75000;
CONNECT RESET;

Advertisement