IBM Support

Rollforward to Point-In-Time (PIT) with overflow log path after a db2inidb as standby fails with SQL4970N

Troubleshooting


Problem

Rollforward of logs doesn't go past the "write suspend" time when using a split mirror to initialize a standby database for disaster recovery process.

Symptom

Testcase:

$ db2 +c "insert into test_rf values ('first uncommited insert')"

At Timestamp T1:
$ db2 set write suspend for database
- Perfomed database files copy using "tar -cvf sample.tar db2inst1/*"

- This tar file now has the default active log path and the active logs.
$ db2 set write resume for database

At Timestamp T2:
$ db2 "insert into test_rf values ('regular commited insert')"
$ db2 "insert into test_rf values ('second insert comitting the long transaction')"


$ db2 "select * from test_rf"

TEXT
----------------------------------------------------------------------------------------------------
second insert comitting the long transaction
first uncommited insert
regular commited insert

3 record(s) selected.

At Timestamp T3:
Backup the active log directory:

$ db2stop
$ cp ./home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/* bkp_logs

Deleting the db2inst1 directory from the /home/db2inst1 path
$ rm -rf db2inst1

$ tar -xvf sample.tar

$ db2start
$ db2inidb sample as standby

$ db2 rollforward db sample query status USING LOCAL TIME

Rollforward Status

Input database alias = sample
Number of nodes have returned status = 1

Node number = 0
Rollforward status = DB pending
Next log file to be read = S0000005.LOG
Log files processed = S0000002.LOG - S0000004.LOG
Last committed transaction = 2012-09-17-16.40.44.000000 Local

Attempt rollforward to timestamp T3 with backed up logs in overflow log path:
$ db2 "rollforward db sample to 2012-09-19-11.51 USING LOCAL TIME overflow log path ('/home/db2inst1/bkp_logs')"

SQL4970N Roll-forward recovery on database "SAMPLE" cannot reach the specified
stop point (end-of-log or point-in-time) on database partition(s) "0".

Roll-forward recovery processing has halted on log file "S0000006.LOG".

$ db2 rollforward db sample query status USING LOCAL TIME

Rollforward Status

Input database alias = sample
Number of nodes have returned status = 1

Node number = 0
Rollforward status = DB working
Next log file to be read = S0000006.LOG
Log files processed = -
Last committed transaction = 2012-09-19-11.48.07.000000 Local

The above timestamp is the write suspend timestamp T1.

A Rollforward database to "end of logs and stop" completes and a connection to the database is successful. However, the values inserted at timestamp T2 are missing.



[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Recovery - Rollforward","Platform":[{"code":"PF033","label":"Windows"},{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF010","label":"HP-UX"}],"Version":"9.5;9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Log InLog in to view more of this document

This document has the abstract of a technical article that is available to authorized users once you have logged on. Please use Log in button above to access the full document. After log in, if you do not have the right authorization for this document, there will be instructions on what to do next.

Document Information

More support for:
Db2 for Linux, UNIX and Windows

Software version:
9.5, 9.7

Operating system(s):
Windows, AIX, Linux, Solaris, HP-UX

Document number:
478881

Modified date:
01 May 2025

UID

swg21612040

Manage My Notification Subscriptions