Technical Blog Post
Abstract
Thinking about Maximo DB2 Database backup / recovery strategy
Body
Maximo database can became corrupted as a result of software or hardware failure.
The human error can also contribute to it. So we need to be sure that our back up / recovery
procedures have been developed to minimize the down time of the application.
The questions we should ask and answer are :
- What is a time required to recover a database
- How often we need to take a backup
- What type of the backup is sufficient : tablespace or datase level, full or incremental
- How much storage we need for database and Log’s backup
- Should we consider using HADR ( stand by database)
The back up strategy should ensure that all information needed is available. We will discuss a different
recovery methods.
The backup of the database is a process of taking a copy of the data and storing it on a different medium in case the original fails. The simplest way, for example , is to take database down and take a copy of it.
The recovery process is a database restoration using a copy prepared by the back up.
Those two process required developing a procedure and interaction of the DBA.
On the other hand the database Cash Recovery is the automatic recovery of the database if a failure occurs before all of the changes that are part of one or more units of work (transactions) are completed and committed. This is done by rolling back incomplete transactions and completing committed transactions that were still in memory when the crash occurred
The other import information about available backups is stored in recovery history file that contains a summary of the backups information that can be used to determine recovery options, if all or a part of the database must be recovered to a given point in time. It is used to track recovery-related events such as backup and restore operations, among others. This file is located in the database directory. Maximo database is consider to be highly transactional, especially when MIF is used for data modification , therefore we should consider recoverability not only by restore to back up but also using Rollforward recovery to apply a committed changes from the logs. We should consider have a database in Archive ( Recoverable) mode when logarchmeth1 or logarchmeth2 set in database configuration to values other the OFF, so the backup operations can be performed online: meaning that application can connect to the database during the backup operation. Online table space restore and rollforward operations are supported only if the database is recoverable. During an online backup operation, rollforward recovery ensures that all table changes are captured and reapplied if the database is restored. The tablespace level back up recovery is also supported with online backup
Taken to consideration that maximo database does not have a Referential Integrity enforced by database engine , this option is not recommended.
DB2 offers, as an option, the automatic maintenance. With automatic maintenance, you specify your maintenance objectives, including when automatic maintenance can run. DB2 then uses these objectives to determine if the maintenance activities needed and runs only the required maintenance activities during the next available maintenance window.
How often to back up :
The recovery plan should allow for regularly scheduled backup operations, because backing up a database requires time and system resources. The plan might include a combination of full database backups and incremental backup operations. Consider to take at least one full backup a week ( often is better especially if the amount of time needed to apply archived logs when recovering and rolling an active database forward is a major concern) and daily incremental copies. Take in consideration that more frequent backups reduce the number of archived logs you need to apply when rolling forward.
FULL or Incremental:
Full database space backups are not always the best approach when dealing with large databases, because the storage requirements for multiple copies of such databases are enormous. To address this issue DB2 offers an incremental backup is a backup image that contains only pages that have been updated since the previous backup was taken. Two types of Incremental backup:
Incremental. An incremental backup image is a copy of all database data that has changed since the most recent, successful, full backup operations.
Delta. A delta, or incremental delta, backup image is a copy of all database data that has changed since the last successful backup (full, incremental, or delta).
To restore the database to the consistent state, the recovery process must begin with a consistent image of the entire object (database or table space) to be restored, and must then apply each of the appropriate incremental backup images
Storage consideration :
The roll-forward recovery method requires space to hold the backup copy and the archived database logs. Think that maximo is using a lot of columns defined as CLOB and BLOB so extra space will never heart . To avoid issue with the possible storage failure, it is highly recommended that you use the newlogpath configuration parameter and put database logs on a separate device once the database is created. Use a user exit program to copy these logs to another storage device. Try to use a row compression in your active database, you can also use backup compression to reduce the size of your database backups
Please review the fallowing documents for more details "
https://www.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.kc.doc/welcome.html
For the HADR configuration please review :
UID
ibm11129965