Backup Retention Policy

Creating a backup and restore policy based on service level agreements, Recovery Point Objectives (RPO)  and Recovery Time Objectives (RTO) is a critical part of DBA work. Another long-term but important Backup and Restore component is devising a Backup Retention Policy.

 A Backup Retention Policy is defined as a set of rules outlining the steps of how long to retain a backup and the process of purging.

 The broad categories influencing the Backup Retention Policy are based on : Legal\Compliance, Recovery Points and Cost.

 As a first step identify a list of use cases within the organization. It is important to step back from the daily grind and look back to all the situations occurring in the last couple of years.

Some typical situations to consider are:

1)  How to deal with backup and restore  copy requirements in a project development  life cycle longer than the immediate DR period, such as 2 years.

2) Managing\Differentiating retention policies of , for example, financial data , which may have legal implications versus session management servers

3) The  current schedule backups the whole instance on the assumption the retention policy is based on per instance. Is  data classification policy  based on a per instance or per database? This impacts how database servers are built and maintained.

4) Retention of auxillary software , for example , recovering a db from 5 years ago – may require recovery of OS, fixpacks, passwords etc . Is the retention policy organised in a way to support a restore across a number of  code and information sources

5) Purging of expired backups.  Should they be automatic or manual?

Read More

DB2 – How to manage backup failures - DBA DB2

The Backup failed - DBA DB2

Data Center Migration , Database Servers and data migration planning

DB2 block all connections to a database - svcename

Taking a OFFLINE DB2 backup requires shutdown of all database  connections . Often you'll see the SQL1035N The database is currently in use  mesage. There are a number of workarounds to achieve the outcome of starting the OFFLINE backup, such as :

 

db2 force applications all 
--or

QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS  

 

 Sometimes the required outcome does not occur., particularly with very persistent connection attempts.

 

One method of blocking connections to the DB2 database server instance is to change the SVCENAME configuration parameter. This forces the attempted connections to all databases on the instance to fail. If you don’t want all connections to the database server instance to be blocked, do not use this method.

Once you’ve completed the OFFLINE backup , change the SVCENAME back to the original value .

 

db2 UPDATE DBM CFG USING SVCENAME 50006

 Read More

Force connections of a database in DB2

DB2 - SQL1035N The database is currently in use

Question : I’m trying to complete an OFFLINE FULL BACKUP and  receive this error message when executing db2 backup DB mydb.

 SQL1035N  The database is currently in use.  SQLSTATE=57019

 I’ve checked db2 list applications  - which returns no applications .

 How can I  fix?

Answer: First , check if the database is activated.

db2pd -db mydb -app

or

db2 list active databases

Second, if it’s activated , then deactivate the database

 db2 deactivate database

Third, try OFFLINE FULL BACKUP again

Related Posts

DB2 – Restore database from a ONLINE backup

DB2 – check backup command and contents of a database

DB2 – Redirect Restore

 Question: How do I relocate an existing DB2 database? I have a database on a path such as “/db2inst/db/”   and would like to move the database to “/db2inst/dbnew/”.

 Answer: One option is to backup and restore with a DB2 Redirect Restore

  Second option – use db2relocatedb

I prefer the first option  - DB2 Redirect Restore as it forces the DBA to take more precautionary steps. Although relocatedb should follow the same levels of caution such as backups.  In this response, I’ll focus on Redirect Restore and leave relocate db for another post.

 One of things I like about the Redirect Restore is DB2 will generate the script , a few modifications are required , execute the script and Redirect Restore completed!

 Create a Restore Redirect script

Step 1- Ensure all relevant database server files are backed up. Usually I force a backup to tape. If it’s a VM consider a VM snapshot for  extra rollback flexibility.

Step 2 – For the Restore Redirect : Confirm a FULL BACKUP is available and the FULL BACKUP was completed by DB2 backup facility

Step 3 -  Confirm you can connect to this database. If crash recovery is required complete first.

Step 4 - Is the database on multiple partitions and no target database exists – then the script must be executed partitions by partition. I prefer to create a target dummy database which runs the script over all the partitions

Step 5 –  Using the CLP – here is an example of how to generate the script

 

db2 restore db MYDB from /db2ins/backups taken at 20121126133630  redirect generate script mydb.clp

 

Step 6 – Open the redirected restore script in a text editor to make any modifications necessary. You can modify:

Login details – username\password

Restore options  - ON,DBPATH ON, INTO,NEWLOGPATH etc

Automatic storage paths

Container layout and paths

Step 6 – Execute the script

 

  db2 -tvf mydb.clp

 

Step 7 – Confirm  directory details

 

db2 list db directory show detail
db2 list db directory on /db2wbm/home

Related Posts

DB2 – Restore database from a ONLINE backup

DB2 - SQL1051N while Redirected Restore

DB2 – How to manage backup failures

Taking a DB2 backup is straightforward, but in a large DB2 environment where hundreds of mission critical DB2 databases are backed up daily, what steps are in place to deal with failed backups?

A  well managed backup strategy is one of the  DBA secrets to stress reduction  

Backups can fail for different reasons – software failure, corruption, hardware issues, tapes unavailable,connectivity issues are just some of the few reasons. From an Operational perspective , how do you deal with this issue?

On a basic level – 1) Attempt a backup 2) If success , OK , if failure , what next?

Normally , there are overnight backups. In most organisations Operations staff will manage the scheduling and follow up on DB2 backup failures. Dealing with failure will depend on the cause. If it’s a missing tape Operations staff can replace, but let’s say it’s a database corruption issue-  escalate to DBA

One of the issues for Operations staff , is developing a standardised approach to Backups. They might be managing a wide range of file systems – such as DB2, SQL Server , flat files etc. How can you manage the risk but keep the effectiveness of Operations staff ?

 Most large scalable backup systems will have a scheduling element. Backups are scheduled according to SLAs, RPO and RTO. What you don’t want is Operations taking a cowboy approach and logging on to DB2 server at any time of the day- potentially creating serious contention and possible downtime. At the same time , Opertion staff have to deal with backup failures.

I prefer a rescheduling approach. In other words, don’t log onto the system and issue another backup.

First, assess reason – look at log files and report to the subject matter experts.

Second , agree on a scheduled time.It might be immediate or later on.

One question arising is should Operations Staff log directly onto the server and run the backup, bypassing the resschedule?

 The questions to consider are :

1) Scheduling offers a standardised approach across the multi – filesystems such as SQL Server and DB2 environments i.e SQL Server backup failures are rescheduled . Would this be lost?

2) Are there other logging\monitoring  benefits that come with scheduling?

For example, if Operations logged on and issued the local backup script   , is there a standardised way they would assess success\failure?

3) It will give you more flexibility in planning - for out of business hrs. For example , if a backup failure  was discovered at 9am M-F , is it suitable for someone to log onto root and issue the backup command?

Related Posts

The Backup failed

DBA secrets to stress reduction  

DB2 – Restore database from a ONLINE backup