Backup And Recovery

Backup and Recovery:

                       Maintaining a copy of all the files necessary to reconstruct the database in the case of a physical or media failure. A complete database backup will be able to reconstruct the  system files, data files, log files, and control files of a database.

Logging:

When you make a backup plan, you should consider using the database logging mode. The database records every change of rows in tables, and each database object. These logs or log files are used to recover from applications from system errors.

             Types of loggings:

1.         Circular logging

                        2.         Archive logging
                        3.         Dual logging

Circular logging:

             By default, the logs are written in a circular fashion. The first log is overwritten once the cycle is completed. In this manner, the data in the logs files are always overwritten.  So there is data loss.

            With circular logging, you can only do offline database backup. Users cannot use the database while an offline back up is taking place.

            This backup is only used for the version recovery, that is recovering a database to the last backup time. Log files are not applicable for version recovery.

Archive logging:

            Here the primary logs in the memory (RAM) are written first. Once the transaction is committed, the data is written to the disk. Once all the primary logs are filled, the data in the first log is transferred to the secondary logs in the disk & it is cleared to hold the coming transactions data. In this manner there is no data loss as the log files are not overwritten anywhere.

            The archive mode supports online backup and database recovery using log files called rollforward recovery.

            The logging mode can be changed from circular to archive by setting parameters LOGRETAIN or USEREXIT to ON.

Set either of these 2 parameters.

db2 UPDATE DB CFG FOR <dbname> USING LOGRETAIN RECOVERY/ON

db2  UPDATE DB CFG FOR <dbname> USING USEREXIT ON

You can also do a Table space either offline or online.

            There are 2-types of logs:

                                      1.    Active logs files.

                                      2.    Archive log files.

 Backup Types:
                      

• Full backup.
• Incremental Backup and Recovery.
• Delta backup and recovery.

Backup Command:

Syntax:          BACKUPDATABASE <db Name> [USER <User_Name> USING <pwd>]

            [TABLESPACE ( TableSpaceName) ]  [ONLINE] [INCREMENTAL / DELTA]

            TO <Location>

            [WITH <Num_of_ Buffers> BUFFERS] [BUFFER <Buffer Size> K]

[PARALLELISM <Parallelism_Num> ] [WITHOU

Create New TablespaceT PROMPTING] [COMPRESS]

Ex: -              ]$ db2 backup database cartdb online to /home/itlokam with 2                                                buffers buffer 1024 parallelism 2 without prompting

             Backup successful. The timestamp for this backup image is: 20060728023249

Duel Logging:

To prevent the log failures, which could be caused by administrators, who accidentally delete corrupted active logs or log files at the hardware level. Dual logging can be enable by setting the 

  DB2 registry Variable  : DB2_NEWLOGPATH2 to 1.

            If an error happens and a message will be written to the db2diag.log.

            $db2set DB2_NEWLOGPATH=1

configuration of the second log path is controlled by the database configuration parameter mirrorlogpath. When a value is present in mirrorlogpath dual logging is enabled.

db2 update db cfg for <dbname> using MIRRORLOGPATH <LOG_DIR2>

Afterwards you must have to restart the instance through db2stop force and db2start.