db2的日志模式和备份归档、恢复解析-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

db2的日志模式和备份归档、恢复解析

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 05:37 PM

db2log

DB2的日志分为两种模式，日志循环与归档日志，也就是非归档和归档模式。下面就具体介绍一下这两种方式以及和备份归档设置的关系。一、日志循环这是默认方式，也

DB2的日志分为两种模式，日志循环与归档日志，也就是非归档和归档模式。下面就具体介绍一下这两种方式以及和备份归档设置的关系。

一、日志循环

这是默认方式，也就是非归档模式，这种模式只支持（backup offline）脱机备份，在备份过程中需要DB2停止服务。

并适当调整日志文件大小和主辅日志的文件个数。

最后就是要注意这两个参数的设置

Options for logarchmeth1 (LOGARCHOPT1) =

Second log archive method (LOGARCHMETH2) =

有以下几种情况：

1、Logarchmeth1设置为LOGRETAIN，Logarchmeth2设置为OFF

　　这时归档日志位置就是DB2数据库日志的位置，需要人工干预归档日志的转移和空间维护工作

2、Logarchmeth1设置为USEREXIT，Logarchmeth2设置为OFF

　　这时归档日志的管理交由USEREXIT来处理，通过设置编译USEREXIT可以实现相对复杂一些的归档管理方式

3、Logarchmeth1设置为指定目录，Logarchmeth2设置为OFF

　　归档日志的工作将会自动进行，需要归档日志将会被自动归档到指定的位置，由于归档是自动进行，DB2的日志目录中只有正常logprimary+logsecond个数的数据库日志。

4、Logarchmeth1设置为，Logarchmeth2设置为

　　归档日志的工作将会自动进行，需要归档日志将会被自动归档到和指定的位置，也就是会产生两份归档日志。由于归档是自动进行，DB2的日志目录中只有正常logprimary＋logsecond个数的数据库日志。对性能有一定的影响。

　　或者都可以设置为TSM。一般推荐为文件系统，设置为TSM，这样既可以归档到TSM离线保存，，又可以在线使用文件系统中的归档日志，比较方便。

　　注意：设置Logarchmeth1和Logarchmeth2后，数据库会进入backup pending状态，必须进行一次脱机备份，数据才会进入recovery模式并且正常工作。

三、备份与恢复

1、简单的冷备与恢复

$ db2 backup db [dbname] to 路径
$ db2 restore db [dbname] from 路径 taken at 时间戳

2、在线热备与恢复

$ db2 backup db dbname online to 路径 include logs

$ db2 restore db dbname from 路径 taken at 时间戳

$ db2 "rollforward db dbname to 2013-02-31-20.00.00.000000 using local time and complete overflow log path (/backup/logs)" 前滚日志恢复至指定时间点

3、查看备份记录

$ db2 list history backup all for dbname 可以看到备份的纪录

附：DB2数据库备份恢复的概念和知识点

备份类型：脱机备份（也称冷备份或离线备份）、联机备份（也称热备份或在线备份）、完全备份、增量备份（也称累积备份）、差异备份

数据库备份文件结构

恢复类型：崩溃恢复、版本恢复、前滚恢复（任意时间点恢复，恢复到最近时间点）

恢复情形：完全恢复、不完全恢复

手动恢复数据库的顺序

日志类型：循环日志（默认）、归档日志（活动日志、在线归档日志、离线归档日志）

日志类型与恢复类型：循环日志只支持崩溃恢复和版本恢复，归档日志支持所有类型的恢复

凡是联机备份产生的备份集在恢复时都需要使用归档日志，归档日志方式是是允许用户执行前滚（rollforward）恢复的唯一方法。

前滚的时间要在最小恢复时间点之后，最后的事务提交时间点之前。

本文出自 “滴水穿石” 博客，请务必保留此出处

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AM

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

MySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Learning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AM

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

See all articles