Db2 log mode and backup archiving, recovery Parsing

DB2 logs are divided into two modes: log loop and archive log, which are non-archive and archive modes. The following describes the two methods and their relationship with backup archiving settings.

1. Log Loop

This is the default mode, that is, non-archive mode. This mode only supports backup offline) offline backup, and DB2 needs to stop the service during the backup process.

View the database settings in DB2. If the following information is found

$ Db2 get db cfg for db_name | grep-I log result: Log retain for recovery enabled ( LOGRETAIN) = OFF
User exit for logging enabled (USEREXIT) = OFF
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
First log archive method (LOGARCHMETH1) = OFF

This indicates the non-archive mode, which is a loop log by default.

In this mode, consider the following:

1. Stop the application's access to DB2. You can run the db2 list applications command to view the existing connections and then run the db2 force application Command to end the connections. In addition, the db2 deactivate database command is used to ensure that the database is not active.

2. Back up the database using the db2 backup db database name command.

Ii. Archiving logs

Archive logs are not default and will take effect only after configuration. In this mode, the database is recoverable and supports online backup, rollback, and crash recovery.

To configure the DB2 archive Log mode, you must modify the log retain for recovery enabled and First Log archive method parameters.

After modifying the update parameter), check that the database is set as follows:

$ Db2 get db cfg for db_name | grep-I log

Log retain for recovery enabled(LOGRETAIN) = RECOVERY
User exit for logging enabled (USEREXIT) = OFF
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
First log archive method(LOGARCHMETH1) = LOGRETAIN

Note that the First log archive method parameterLOGRETAIN only indicates that you have enabled archiving, and further settings are required for normal backup.

Next, you need to specify the path of the archive directory.

Change the archive directory:$ Db2 update db cfg for db_name using LOGARCHMETH1 "disk:/archive/db_name_db_log"

Archive logs are automatically archived When the log file is full. The Archiving destination is the location set by Logarchmeth1. After archiving a log file, you must manually delete useless archived logs so that new log files can reuse disk space. Every time the log file is full, DB2 begins to write records to another log file and constantly creates new log files.

If an error occurs when archiving Log files, the archive will be suspended for a period of time, which is specified by the "Log archive retry Delay (secs)" ARCHRETRYDELAY database configuration parameter, you can also use the "Number of log archive retries on error" NUMARCHRETRY database configuration parameters to specify the Number of times DB2 tries to archive log files to the primary or secondary archiving directories, then it tries to archive the log file to the Failover Directory, which is specified by the "Failover log archive path" FAILARCHPATH Database Configuration Parameter ).

There is also the problem that the database transaction logs are full and cause backup errors. You can view these three parameters.

Log file size (4 kb) (LOGFILSIZ) = 8192
Number of primary log files (LOGPRIMARY) = 10
Number of secondary log files (LOGSECOND) = 4

Adjust the log file size and the number of primary and secondary logs.

Finally, pay attention to the settings of these two parameters.

Options for logarchmeth1 (LOGARCHOPT1) =

Second log archive method (LOGARCHMETH2) =

There are several situations:

1. Set Logarchmeth1 to LOGRETAIN and Logarchmeth2 to OFF.

In this case, the archiving log location is the location of the DB2 database log, and manual intervention is required for archiving log transfer and space maintenance.

2. Set Logarchmeth1 to USEREXIT and Logarchmeth2 to OFF.

At this time, the management of archive logs is handled by USEREXIT. By setting USEREXIT for compilation, You can implement more complex archive management methods.

3. Set Logarchmeth1 to the specified Directory <Directory> and Logarchmeth2 to OFF.

Archiving logs is automatically performed. Logs to be archived are automatically archived to the specified location in <Directory>, the DB2 log directory contains only the database logs with the normal logprimary + logsecond count.

4. Set Logarchmeth1 to <Directory1> and Logarchmeth2 to <Directory2>

Archiving logs is automatically performed. Logs to be archived are automatically archived to the specified locations of <Directory1> and <Directory2>, that is, two archived logs are generated. Because archiving is performed automatically, the DB2 log directory contains only the database logs with the number of normal logprimary + logsecond. It has a certain impact on performance.

Either <Directory1> or <Directory2> can be set to TSM. We recommend that you set <Directory1> to the file system and <Directory2> to TSM, which can be archived to TSM for offline storage and archived logs in the file system online for ease of use.

Note: After Logarchmeth1 and Logarchmeth2 are set, the database enters the backup pending state. You must perform an offline backup before the data enters the recovery mode and works properly.

Iii. backup and recovery

1. Simple cold backup and recovery

$ Db2 backup db [dbname] to path
$ Db2 restore db [dbname] from path taken at Timestamp

2. Online hot standby and recovery

$ Db2 backup db dbname online to path include logs

$ Db2 restore db dbname from path taken at Timestamp

$ Db2 "rollforward db dbname to 2013-02-31-000000.00.000000 using local time and complete overflow log path (/backup/logs)" rollback log recovery to a specified time point

3. View backup records

$ Db2 list history backup all for dbname: the backup record is displayed.

Appendix: Concepts and knowledge points of DB2 database backup recovery

Backup Type: offline backup is also called cold backup or offline backup), online backup is also called hot backup or online backup), full backup, Incremental backup is also called cumulative backup), differential backup
650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/221Z92I6-0.jpg "title =" beifenyuanlitu.jpg "width =" 650 "style =" padding: 0px; margin: 0px; vertical-align: top; border: none; "alt =" 160826301.jpg"/>

Database Backup file structure

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/221Z92391-1.jpg "title =" 222data character file structure .jpg "width =" 650 "style =" padding: 0px; margin: 0px; vertical-align: top; border: none; "alt =" 162.162.167.jpg"/>

Recovery Type: crash recovery, version recovery, roll-forward recovery, recovery to the latest point in time)

Recovery: full recovery, Incomplete recovery

Sequence of manual database recovery

Log Type: cyclic logs by default), archived Log Activity logs, online archived logs, and offline archived logs)

Log Type and Recovery Type: cyclic logs only support crash recovery and version recovery. Archived logs support recovery of all types.

All backup sets generated by online backup must use archive logs for recovery. Archiving logs is the only method that allows you to perform rollforward rollback.

The rollback time must be after the minimum recovery time point and before the last transaction commit time point.

This article from the "Drop water stone" blog, please be sure to keep this source http://xjsunjie.blog.51cto.com/999372/1337515