DB2 LUW: Recovery history file

(0) What is Recovery history file?

A file stores information about various operations against database. This information is used during restore/recovery.

(1) Location of Recovery history file:

Location: DBPATH/<instance name>/<Node Name>/SQLXXXXX/db2rhist.asc

This recovery history log file has a backup. Backup file name is: db2rhist.bak

$ls -l db2rhist.*
-rw-r—– 1 db2inst1 db2iadm1 272491532 Dec 22 05:35 db2rhist.asc
-rw-r—– 1 db2inst1 db2iadm1 272491532 Dec 22 05:35 db2rhist.bak

How to get DBPATH of a database?

From below “list db directory” entry, DBPATH is /mnt/DB2_data/ts1

Database 1 entry:

Database alias = SAMPLE
Database name = SAMPLE
Local database directory = /mnt/DB2_data/ts1
Database release level = f.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =

(2) When Recovery history file is created?

Scope of recovery history file is database. Whenever you create a new database (CREATE DATABASE command), a recovery history file is created inside DBPATH.

(3) When Recovery history file is updated?

This file is updated automatically during below operations:

  • A database or table spaces are backed up
  • A database or table spaces are restored
  • A database or table spaces are rolled forward
  • A database is automatically rebuilt and more than one image is restored
  • A table space is created
  • A table space is altered
  • A table space is quiesced
  • A table space is renamed
  • A table space is dropped
  • A table is loaded
  • A table is dropped (when dropped table recovery is enabled and you are using recoverable logging)
  • A table is reorganized
  • On-demand log archiving is invoked
  • A new log file is written to (when using recoverable logging)
  • A log file is archived (when using recoverable logging)
  • A database is recovered
  • A failed restore database or table space operation

(4) How to see entries of Recovery history file?

There are two ways:

(a) LIST HISTORY Command

db2 => ? list history backup
LIST HISTORY {BACKUP | ROLLFORWARD | REORG |
CREATE TABLESPACE | ALTER TABLESPACE | DROPPED TABLE | LOAD |
RENAME TABLESPACE | ARCHIVE LOG}
{ALL | SINCE timestamp |CONTAINING {schema.object_name | object_name}}
FOR [DATABASE] database-alias

To list backup history since 10/SEP/2015
********************************
db2 => list history backup since 20150910 for database SAMPLE

To list backup history on a specific object
*********************************
Please note that you can’t take backup at table level. You can take backup at tablespace level (or) database level
db2 => list history backup containing <tablespace name> for database SAMPLE

(b) History via SQL

Select substr(comment,1,20), end_time from sysibmadm.DB_history Where comment like ‘DB2 BACKUP%’ Order by end_time desc FETCH FIRST 3 ROWS ONLY

(5) Pruning (removing) recovery history file

Ans: PRUNE HISTORY command

db2 ? prune history
PRUNE HISTORY timestamp [WITH FORCE OPTION] [AND DELETE]

(6) Automating recovery history file pruning

The following configuration parameters help us to automatically manage the recovery history file:

  • num_db_backups
  • rec_his_retentn
  • auto_del_rec_obj

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: