Database Server Blocked in Checkpoint (Blocked:CKPT) due to disabled dbspace

Problem(Abstract)

How to unblock the database server after a dbspace has been disabled and the onDBSPACEDOWN configuration parameter is set to 2.

Symptom

The database server is blocked with a checkpoint request that never finishes. User sessions are only able to perform read operations and any update, delete or insert operations will hang. 

When you run the onstat - command, the output shows that the database server is Online, but blocked on a checkpoint request:  

• There is a message in the online.log file that says:

• The output of the onstat -d command shows one or more dbspaces marked as down (indicated by a D in the flags column). Any dbspace marked as down will have all its chunks marked as down as well.

Example 

The following output shows that the dbspace test has been marked as down and its associated chunks (numbers 5 and 6) have also been marked as down. The database server is waiting on a checkpoint request - on-Line (CKPT REQ) :





(right click the image for a larger view using your browser)

Cause

When one or more dbspaces become unavailable and the configuration parameter ONDBSPACEDOWNis set to 2, the database server will prevent any further updates to the database and waits for intervention from the database administrator.

There are two reasons why the database server will mark a dbspace as down:

1. The database server has been unable to access the primary chunk of a dbspace and its mirror chunk either does not exist or is not accessible.

2. The database server has detected corruption within the chunk. The database server will mark the chunk as down to help preserve the integrity of the database.

Resolving the problem

There are two possible solutions available. 

• Run onmode -O (Override)

The database server will issue a checkpoint and will then be available for use. The down dbspaces will continue to be disabled and will only become available if they are restored from archive. 

Important: This action can not be reversed as information about down dbspaces is written to the reserved pages of the database server. 

1. There will be a message on the screen after issuing the onmode -O command:



2. Press the keys y+Enter to confirm or press the keys n+Enter to cancel.

• Run onmode -ky (Shutdown)

The database server will stop without issuing a checkpoint and information about down dbspaces will not be written to the reserved pages of the database server.

• There will be a message on the screen after issuing the onmode -ky command (except for versions 9.21x and 7.30x):



• There will be also a message in the online.log (except for versions 9.21x and 7.30x):

The onmode -ky option should be used if the database server has been unable to access the chunks (marked as down) due to an operating system error or hardware problem. You must take corrective action to ensure that the database server can access all of the allocated chunks . You must ensure that there has been no modification or corruption of the data pages within these chunks. When the database server is restarted, any chunks that were previously marked as down will be online again.

http://www-01.ibm.com/support/docview.wss?uid=swg21153098