Types of Recovery in Db2

Crash Recovery

It is a type of recovery which bring the database back to the consistent state after unplanned failure
It perform rollback operation of the transaction that were not committed and perform data page externalization for all the committed transactions

If the tablespace is damaged in circular logging database and cannot be made online then in order to start the database it needs to be dropped to start the database else database will not start
If the database is not started, try restarting it using RESTART command

                    db2 "restart database sample"

If there are corrupt tablespace the database restart will fail with SQL0290N error code
Then check the DB2 administration notification file for list of all tablespace that are in DROP PENDING state                       db2 get dbm cfg | grep -i diag
                              cd /home/db2inst1/sqllib/db2dump
                               vi db2inst1.nfy

Try restarting the database with DROP PEDNING clausedb2 "restart database sample drop pending tablespaces(userspace1,tbsp_taba)"

Click here to get more detail on DB2 Crash Recovery

Crash recovery can be automated or can be done manually
Enabling Automated crash recovery
Set DB CFG parameter “autorestart” to “ON”db2 "update db cfg for sample using autorestart on immediate"

Following the failure, DB2 will automatically restore the database to a consistent point by rolling forward and backup the changes from Active log files
Manually performing the Crash recovery
DB CFG parameter “autorestart” is set to “OFF”
Following the failure, “RESTART DATABASE” is issued to perform the crash recoverydb2 "restart database sample"


2)Version Recovery

• It restores the database from the consistent full offline backup available.
• Restore from only full offline backup operation is available.
• Version recovery can be used with circular logging and archival logging database.
• If the database is enabled for archival logging, then using only the FULL OFFLINE BACKUP of the database with the “WITHOUT ROLLING FORWARD” at the end of the “RESTORE” command can be used to perform version recovery.
• All the transaction after the last full offline backup are lost in this type of recovery.
• click here to get more details on DB2 Version Recovery

Version recovery example

Sr. No	Description	Sample DB2 Command
1	Check if database is configured for Circular logging. If database is configured for Archival logging this step is not needed.	db2 get db cfg for sample | grep -i log                LOGARCHMETH1 = OFF
2	Perform Offline backup of the database	-- Deactivate the database db2 deactivate db sample -- Check if the database is deactivated. This command check for db2loggr process. If not listed then DB is deactivated db2pd -edus | grep -i db2lo -- Perform offline backup of the database db2 “backup database sample to /backup compress”
3	Identify the set of offline backup and determine the recovery point	db2 “list history backup all for database sample” Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID -- --- ------------------ ---- --- ------------ ------------ --------------  B  D  20110520114322001   F    D  S0000023.LOG S0000023.LOG ----------------------------------------------------------------------------  Contains 3 tablespace(s):  00001 SYSCATSPACE  00002 USERSPACE1  00003 SYSTOOLSPACE ----------------------------------------------------------------------------     Comment: DB2 BACKUP SAMPLE OFFLINE Start Time: 20110520114322   End Time: 20110520114345     Status: A ----------------------------------------------------------------------------  EID: 29 Location: /home/db2inst1/gilroy/bar
4	Determine which version of the database is needed using the timestamp displayed from the “LIST HISTORY” command	Details captured from previous command 1) Timestamp => 20110520114322001  2) Backup image location =>/home/db2inst1/gilroy/bar
5.a	Version recovery onCircular Logging Database	db2 “restore db sample from/home/db2inst1/gilroy/bar taken at20110520114322001 into testdb”
5.b.	Version recovery onArchival logging database	db2 “restore db sample from/home/db2inst1/gilroy/bar taken at20110520114322001 into testdb without rolling forward”