Shulou(Shulou.com)06/01 Report--

This article mainly introduces the db2 log mode and backup archiving, recovery example analysis, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to know it.

I. Log cycle

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

View the database settings in DB2, such as finding the following information

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

The results are as follows:

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

Then it is in non-archive mode and circular log by default

For offline backups in this mode, note:

1. Stop the application's access to DB2. You can view the existing connection through the db2 list applications command, and then end the connection with the db2 force application command. Also use the db2 deactivate database command to ensure that the database is not active.

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

2. Archiving log

Archived logs are not the default and need to be configured before they take effect. Databases in this mode are recoverable and support online backup, roll-forward recovery, and crash recovery.

The main purpose of configuring the DB2 archive log mode is to modify the parameters Log retain for recovery enabled and First log archive method.

After modifying the parameters (update update parameters), 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 parameter LOGRETAIN of First log archive method only indicates that you have enabled the archive, and you need to set it further before you can back up normally.

The following needs to further specify the path to the archive directory

Change the archive directory:

$db2 update db cfg for db_name using LOGARCHMETH1 "disk:/archive/db_name_db_log"

Using the archive log, the log file is automatically archived when it is full, and the destination of the archive is the location set by Logarchmeth2. After archiving the log files, you must manually delete the useless archive logs so that the new log files can reuse disk space. Whenever the log file is full, DB2 starts writing records to another log file and keeps creating new log files.

If an error occurs while archiving the log file, the archive is suspended for a period of time specified by the "Log archive retry Delay (secs)" ARCHRETRYDELAY database configuration parameter, and the "Number of log archive retries on error" NUMARCHRETRY database configuration parameter can also be used to specify the number of times DB2 attempts to archive the log file to the primary or secondary archive directory It then attempts to archive the log file to the failover directory (specified by the "Failover log archive path" FAILARCHPATH database configuration parameter).

In addition, it is easy to encounter the problem that the database transaction log is full, which leads to backup errors. You can view these three parameters.

Log file size (4KB) (LOGFILSIZ) = 8192

Number of primary log files (LOGPRIMARY) = 10

Number of secondary log files (LOGSECOND) = 4

And adjust the log file size and the number of primary and secondary log files appropriately.

Finally, we should pay attention to the setting of these two parameters.

Options for logarchmeth2 (LOGARCHOPT1) =

Second log archive method (LOGARCHMETH2) =

There are the following situations:

1. Logarchmeth2 is set to LOGRETAIN,Logarchmeth3 and OFF.

At this time, the location of the archive log is the location of the DB2 database log, which requires manual intervention in the transfer of the archive log and space maintenance.

2. Logarchmeth2 is set to USEREXIT,Logarchmeth3 and OFF.

At this time, the management of archived logs is handled by USEREXIT, and a relatively complex way of archiving management can be achieved by setting up compiling USEREXIT.

3. Set Logarchmeth2 to the specified directory and Logarchmeth3 to OFF

The work of archiving logs will be carried out automatically, and the logs that need to be archived will be automatically archived to the specified location. Because the archiving is done automatically, there are only normal logprimary+logsecond database logs in the log directory of DB2.

4. Logarchmeth2 is set to, Logarchmeth3 is set to

The work of archiving logs will be carried out automatically, and the required archived logs will be automatically archived to the specified location, that is, two archived logs will be generated. Because archiving is automatic, there are only normal number of logprimary+logsecond database logs in DB2's log directory. It has a certain influence on the performance.

Or both can be set to TSM. It is generally recommended to use the file system and set it to TSM, so that you can not only archive to TSM for offline storage, but also use the archive logs in the file system online, which is more convenient.

Note: after setting up Logarchmeth2 and Logarchmeth3, the database will enter the backup pending state, and an offline backup must be performed before the data will enter recovery mode and work 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 backup 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-20.00.00.000000 using local time and complete overflow log path (/ backup/logs)" rollforward log recovery to a specified point in time

3. View backup records

$db2 list history backup all for dbname can see the backup record

Attached: the concept and knowledge points of backup and recovery of DB2 database

Backup types: offline backup (also known as cold backup or offline backup), online backup (also known as hot backup or online backup), full backup, incremental backup (also known as cumulative backup), differential backup

Database backup file structure

Recovery types: crash recovery, version recovery, roll forward recovery (any point-in-time recovery, restore to the most recent point in time)

Recovery situation: full recovery, incomplete recovery

Restore the order of the database manually

Log type: circular log (default), archive log (activity log, online archive log, offline archive log)

Log types and recovery types: circular logs only support crash recovery and version recovery, and archive logs support all types of recovery

All backup sets generated by online backups need to be restored with archived logs, which is the only way to allow users to perform a roll forward (rollforward) recovery.

The roll forward time is after the minimum recovery time point and before the last transaction commit time point.

Thank you for reading this article carefully. I hope the article "sample Analysis of Log Mode and backup Archiving and recovery in db2" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.