Part 03 - Introduction to DB2 Logging Mechanism
Part 04 - DB2 Backup with example
Introduction
Recovery of database is one of the DBAs worst nightmare. In the age of internet, where information is capture and stored for analytical purpose, there is pressure on IT infrastructure to accomodate such a large volume of data. Database are growing in size each day and pressure to deliver result in sub-second response time become every more critical. Failure in such a critical system result into hugh loss. Further, corruption of data results into serious consequence.
In order to support such kind of critical data, new way of are develop to protect and recovery the data. Today, every RDBMS available in the market support it own level of recovery mechanism. DB2 is no different to any of other product available in the market. It offer a more simple and faster way to recover the data in the every of corruption or data loss. With each new version of DB2, there is a major improvement in the area of Backup and Recovery thereby reducing the RTO and RPO.
In this blog, I will discuss different type of DB2 recovery option and will present sample examples.
Type of Recoveries
DB2 support three type of Recovery mechanism. They are discussed below
01) 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 clause
db2 "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 recovery
db2 "restart database sample"
02) 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 find more about 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 on Circular Logging Database | db2 “restore db sample from /home/db2inst1/gilroy/bar taken at 20110520114322001 into testdb” |
| 5.b. | Version recovery on Archival logging database | db2 “restore db sample from /home/db2inst1/gilroy/bar taken at 20110520114322001 into testdb without rolling forward” |
3) Rollforward Recovery (RFR)
- It is the most flexible recovery type and provide different option of recovery.
- It work in combination with BACKUP image, ARCHIVE LOG and ACTIVE LOG files.
- Click here for more detail on DB2 Roll Forward Recovery.
- The two type of recovery to consider are
- Database rollforward recovery
- Tablespace rollforward recovery
- If the database is restored from online full backup “WITHOUT ROLLING FORWARD” clause then “ROLLFORWARD” operation is needed after “RESTORE” operation.
- In this recovery type, after the RESTORE of the database or tablespace is performed, it sets the database in “ROLLFORWARD PENDING” state.
- To take the database or tablespace out of “ROLLFORWARD PENDING” state, “ROLLFORWARD” command needs to be executed.
3.a) Database Rollforward Recovery
- The database rollforward recovery option available are
- End of logs
- End of backup
- Point in time
- In DPF environment,
- If Point in time recovery is performed, then all the database partition needs to be rollforwarded to a particular point in time. i.e PIT applies to all partition
- If a single Database partition recovery is needed, then only recovery option available is the “End of Logs”.
3.b) Tablespace Rollforward Recovery
- The tablespace rollforward recovery option available are
- End of logs
- Point in time
- In order to perform tablespace recovery, a full backup of the database is needed which include all the tablespaces or backup of individual tablespaces are needed.
- Log files are also needed to perform the rollforward operation.
- After a tablespace restore, it always places tablesplace in roll forward pending state.
- After the restore a ROLLFORWARD DATABASE command is needed on the the restored tablespace.
Useful Examples
1) Redirect restore of the database using version recovery
a) DB is enable for archival logging.
b) Perform offline backup of the database
- Offline backup is needed when the current database has to be redirected restore.(Requires, only the Offline backup image)
- Online backup can also be used, but transaction done during the online backup needs to be rollforward (Requires, Online Backup image + Archive/Active Log file)
c) Performing recovery of database
- Version recovery if offline backup is used
- Roll-forward recovery if online backup is used
d) Activate the database
2) Restore a failed Tablespace (Non-SYSCAT)
A) Restore failed tablespace from the available backup image
a) Identify the tablespace that is failed
- ADMIN NOTIFICATION FILE
- db2pd -tablespace -db sample
b) Restore the tablespace from the available backup image
db2 restore db sample tablespace (userspace1) online
c) Rollforward the tablespace to end of logs
db2 "rollforward db sample to end of logs tablespace(userspace1) online"
d) Optional. Take backup of database or backup of the restored tablespace
B) Backup image is not available for the failed tablespace
a) Identify the tablespace that is failed
- ADMIN NOTIFICATION FILE
- db2pd -tablespace -db sample
b) Check the create time of the failed tablespace
db2 "select varchar(TBSPACE,40),CREATE_TIME from syscat.tablespaces order by CREATE_TIME desc"
c) Check the available backup image
| Sample DB2 Command |
|---|
select
START_TIME
,varchar(FIRSTLOG,12) as firstlog
,varchar(LASTLOG,12) as lastlog
,case OPERATIONTYPE
when 'N' then 'Online Full DB Backup'
when 'O' then 'Incremental DB backup'
when 'F' then 'Offline full DB backup'
when 'E' then 'Delta DB Backup'
end as Backup_type
,varchar(LOCATION,80) as backup_location
--,SQLCODE
,case OBJECTTYPE
when 'D' then 'Database'
when 'P' then 'Tablespace'
when 'T' then 'Table'
end as Object_type
from
sysibmadm.db_history
where
operation='B'
and
OPERATIONTYPE in ('N','O','F','E')
and
SQLCODE is null
order by
START_TIME desc |
d) If the
BKP_IMAGE < TBSP_CREATE_TIME then
- Perform full database restore from the latest BKP image
- Rollforward the database to end of logs and complete
e) If the
BKP_IMAGE > TBSP_CREATE_TIME then
- Deactivate the Database
- Restore the tablespace from full database backup or TBSP backup
- Rollforward DB to end of logs and complete
f) Activate DB sample and check for the tablespace state
g)
Note:-
- When any new tablespace is created after Full/incremental/Delta backup, it is important that backup of new tablespace is created. This will avoid a full database restore operation if the tablespace fail before the next backup. This recovery scenario is covered in this case.
- Also in DB2, recovery never goes below tablespace level. i.e the maximum recovery that you can achive is until Tablespace level nothing below that.
- i.e. we have Database => Tablespace => Container => Disk.
- Here, we can recover Database, Tablespace. But no Container recovery is possible.
- Container recovery is possible by doing
- Restore Tablespace from DB backup or Tablespace backup
- Rollforward to end of log
h)
Command:-
Method 1 : Manual Incremental Restore of tablespace with backup having tablespace details
-- This command will restore the tablespace from the incremental backup images. It will set the tablespace in “Restore Pending” and “Restore in Progress” state. The “Online” clause is needed in order to keep application accessing the other tablespace.
db2 “restore db sample tablespace ( RST_BKP_TEST ) online incremental taken at 201108051628”
-- This command will take the tablespace out of “Restore Pending” state and put it in “Roll forward Pedding” state.Note here we are not mentioning the tablespace name.
db2 “restore db sample tablespace online incremental taken at 20110805162803”
-- Rollforward the database to end of logs
db2 "rollforward db sample to end of logs and complete tablespace(RST_BKP_TEST) online"
Method 2: Manual Restore of tablespace with backup having tablespace details
-- This command will restore the tablespace from backup image and put it in “Roll Forward Pending” state
db2 "restore db sample tablespace(RST_TBSP_TEST) online taken at 20110804170807"
-- Rollforward the database to end of logs
db2 "rollforward db sample to end of logs and complete tablespace(RST_BKP_TEST) online"
Method 3: Manual Restore of tablespace with no backup of the tablespace
-- This command will consider all the tablespace that was available before the RESTORE start. This does not require the backup image to have all new tablespace. i.e if any new tablespace is created after last backup then this command will take care of it.
db2 "restore db sample REBUILD WITH ALL TABLESPACES IN DATABASE"
==OR==
-- This command will do a simple restore of Database from the backup image
db2 “restore db sample taken at 20110804170807”
-- Rollforward the database to end of logs
db2 "rollforward db sample to end of logs and complete tablespace(RST_BKP_TEST) online"
Method 4: Automatic Incremental Restore of the tablespace with backup image having tablespace details
-- Restore Database using the latest backup or the backup image that you want to restore. It can be incremental or full backup
db2 "restore db sample tablespace ( RST_BKP_TEST ) online incremental auto taken at 20110805162803"
-- Rollforward the database to end of logs
db2 "rollforward db sample to end of logs and complete tablespace(RST_BKP_TEST) online"
3)
Get the backup status
| Sample DB2 command |
|---|
drop function get_bkp_status
@
create function get_bkp_status()
returns table
(
start_time timestamp
,firstlog varchar(12)
,lastlog varchar(12)
,backup_type varchar(30)
,backup_location varchar(50)
-- ,sqlcode integer
,object_type varchar(12)
)
specific get_bkp_status
BEGIN ATOMIC
return
select
START_TIME
,varchar(FIRSTLOG,12) as firstlog
,varchar(LASTLOG,12) as lastlog
,case OPERATIONTYPE
when 'N' then
'Online ' ||
case OBJECTTYPE
when 'D' then 'Database'
when 'P' then 'Tablespace'
when 'T' then 'Table'
end
|| ' Backup'
when 'O' then
'Incremental Online' ||
case OBJECTTYPE
when 'D' then 'Database'
when 'P' then 'Tablespace'
when 'T' then 'Table'
end
|| ' Backup'
when 'F' then
'Offline ' ||
case OBJECTTYPE
when 'D' then 'Database'
when 'P' then 'Tablespace'
when 'T' then 'Table'
end
|| ' Backup'
when 'E' then
'Delta Online' ||
case OBJECTTYPE
when 'D' then 'Database'
when 'P' then 'Tablespace'
when 'T' then 'Table'
end
|| ' Backup'
end as Backup_type
,varchar(LOCATION,80) as backup_location
--,SQLCODE
,case OBJECTTYPE
when 'D' then 'Database'
when 'P' then 'Tablespace'
when 'T' then 'Table'
end as Object_type
from
sysibmadm.db_history
where
operation='B'
and
OPERATIONTYPE in ('N','O','F','E')
and
SQLCODE is null
order by
START_TIME desc
;
end
@ |
4)
Get Restore Status
| Sample DB2 Command |
|---|
drop function get_rst_status
@
create function get_rst_status()
returns table
(
start_time timestamp
,firstlog varchar(12)
,lastlog varchar(12)
,restore_type varchar(30)
,restore_location varchar(50)
--,sqlcode integer
,Object_type varchar(12)
)
specific get_rst_status
BEGIN ATOMIC
return
select
START_TIME
,varchar(FIRSTLOG,12) as firstlog
,varchar(LASTLOG,12) as lastlog
,case OPERATIONTYPE
when 'N' then 'Online ' ||
case OBJECTTYPE
when 'D' then 'Database'
when 'P' then 'Tablespace'
when 'T' then 'Table'
end
|| ' Restore'
when 'O' then
'Incremental Online ' ||
case OBJECTTYPE
when 'D' then 'Database'
when 'P' then 'Tablespace'
when 'T' then 'Table'
end
|| ' Restore'
when 'F' then
'Offline ' ||
case OBJECTTYPE
when 'D' then 'Database'
when 'P' then 'Tablespace'
when 'T' then 'Table'
end
|| ' Restore'
end as Restore_type
,varchar(LOCATION,80) as restore_location
-- ,SQLCODE
,case OBJECTTYPE
when 'D' then 'Database'
when 'P' then 'Tablespace'
when 'T' then 'Table'
end as Object_type
from
sysibmadm.db_history
where
operation='R'
and
OPERATIONTYPE in ('N','O','F')
and
SQLCODE is null
order by
START_TIME desc
;
end
@ |
5)
Get Rollforward Status
| Sample DB2 Command |
|---|
drop function get_rf_status
@
create function get_rf_status()
returns table
(
start_time timestamp
,firstlog varchar(12)
,lastlog varchar(12)
,rf_type varchar(40)
,rf_location varchar(50)
--,sqlcode integer
,Object_type varchar(12)
)
specific get_rf_status
BEGIN ATOMIC
return
select
START_TIME
,varchar(FIRSTLOG,12) as firstlog
,varchar(LASTLOG,12) as lastlog
,case OPERATIONTYPE
when 'E' then
'Rollforward ' ||
case OBJECTTYPE
when 'D' then 'Database'
when 'P' then 'Tablespace'
when 'T' then 'Table'
end
|| ' to End of Logs '
when 'P' then
'Rollforward ' ||
case OBJECTTYPE
when 'D' then 'Database'
when 'P' then 'Tablespace'
when 'T' then 'Table'
end
|| ' to Point in Time'
end as rf_type
,varchar(LOCATION,80) as restore_location
-- ,SQLCODE
,case OBJECTTYPE
when 'D' then 'Database'
when 'P' then 'Tablespace'
when 'T' then 'Table'
end as Object_type
from
sysibmadm.db_history
where
operation='F'
and
OPERATIONTYPE in ('E','P')
and
SQLCODE is null
order by
START_TIME desc
;
end
@ |
6)
Get Current Log details
| Sample DB2 Command |
|---|
select
varchar(db_name,8) as dbname,
FIRST_ACTIVE_LOG,
LAST_ACTIVE_LOG,
CURRENT_ACTIVE_LOG,
CURRENT_ARCHIVE_LOG
from
SYSIBMADM.SNAPDETAILLOG |
7)
Track the log utilization Progress
| Sample DB2 Command |
|---|
while true; do db2 -x "select current timestamp,varchar(DB_NAME,8) as DBNAME ,LOG_UTILIZATION_PERCENT ,substr(TOTAL_LOG_USED_KB,0,15) as TOTAL_LOG_USED_KB ,substr(TOTAL_LOG_AVAILABLE_KB,0,15) as TOTAL_LOG_AVAILABLE_KB ,substr(TOTAL_LOG_USED_TOP_KB ,0,15) as TOTAL_LOG_USED_TOP_KB FROM SYSIBMADM.LOG_UTILIZATION"; sleep 10; done |
8)
Simulate Insert without commit
| Sample DB2 Command |
|---|
while true; do db2 +c "insert into rst_bkp_test select int(RAND() * 10000) from sysibm.sysdummy1"; done |
9)
Simulate Insert with Commit
| Sample DB2 Command |
|---|
while true; do db2 "insert into rst_bkp_test select int(RAND() * 10000) from sysibm.sysdummy1"; db2 commit; done |
10)
Generating SQL from the output of SELECT query
| Sample DB2 Command |
|---|
db2 -x "select TBSPACE from syscat.tablespaces" | while read applid ; do echo "alter tablespace $applid maxsize none"; done |
11)
Get the number of logs in each chain... This is using GROUPING SET clause
| Sample DB2 Command |
|---|
db2 "select
last_log,
first_log,
sum(r2) as total_logs_in_chain
from
(select
varchar(LASTLOG,12) as last_log,
varchar(FIRSTLOG,12) as first_log,
RANK() OVER(PARTITION BY FIRSTLOG order by FIRSTLOG) as r2
from
sysibmadm.db_history
where
operation = 'X'
and
OPERATIONTYPE in ('1')
GROUP BY
LASTLOG,FIRSTLOG
) as t
GROUP BY
GROUPING SETS(
(last_log,first_log),
(last_log)
)"
== OR ==
db2 "select
last_log as log_chain,
sum(r2) as total_logs_in_chain
from
(select
varchar(LASTLOG,12) as last_log,
varchar(FIRSTLOG,12) as first_log,
RANK() OVER(PARTITION BY FIRSTLOG order by FIRSTLOG) as r2
from
sysibmadm.db_history
where
operation = 'X'
and
OPERATIONTYPE in ('1')
GROUP BY
LASTLOG,FIRSTLOG
) as t
GROUP BY
GROUPING SETS(
(last_log,first_log),
(last_log)
)
fetch first 2 rows only" |
12)
Get the number of logs in the chains......This is using ROLLUP Function
| Sample DB2 Command |
|---|
db2 "select
last_log,
first_log,
sum(r2) as total_logs_in_chain
from
(select
varchar(LASTLOG,12) as last_log,
varchar(FIRSTLOG,12) as first_log,
RANK() OVER(PARTITION BY FIRSTLOG order by FIRSTLOG) as r2
from
sysibmadm.db_history
where
operation = 'X'
and
OPERATIONTYPE in ('1')
GROUP BY
LASTLOG,FIRSTLOG
) as t
GROUP BY
rollup(
last_log,
first_log
)
fetch first 3 rows only”
==OR==
db2 "select
last_log as log_chain,
sum(r2) as total_logs_in_chain
from
(select
varchar(LASTLOG,12) as last_log,
varchar(FIRSTLOG,12) as first_log,
RANK() OVER(PARTITION BY FIRSTLOG order by FIRSTLOG) as r2
from
sysibmadm.db_history
where
operation = 'X'
and
OPERATIONTYPE in ('1')
GROUP BY
LASTLOG,FIRSTLOG
) as t
GROUP BY
rollup(
last_log,
first_log
)
fetch first 3 rows only" |
References
1) SQL1268N Roll-forward recovery stopped due to SQL2062N error when trying to restore from an online backup using the INCLUDE LOGS option.
Click here to view more
2)
DB2 Backup and Recovery Overview
3)
DB2 9 Database Rebuild Support 7 Recovery Enhancements -
by Melanie Stopfer, IDUG 2008, North America, Session:D07
