DB2备份+日志恢复并前滚数据库

最新推荐文章于 2023-10-11 09:34:08 发布
最新推荐文章于 2023-10-11 09:34:08 发布
阅读量886 收藏 2
点赞数
文章标签： 数据库 sql oracle 大数据 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/hbhe0316/article/details/122701309
版权

1.环境准备

https://www.cndba.cn/hbhe0316/article/4811 
[root@db01 ~]# cat /etc/hosts
192.168.2.113 db01
192.168.2.123 db02

2.在db01上面创建测试库
创建测试库

db2 "CREATE DB testdb on /db2data/testdb using codeset UTF-8 territory us PAGESIZE 8192"
db2 "UPDATE DB CFG FOR testdb USING applheapsz 4096"
db2 "UPDATE DB CFG FOR testdb USING app_ctl_heap_sz 1024"
db2 "UPDATE DB CFG FOR testdb USING stmtheap 32768"
db2 "UPDATE DB CFG FOR testdb USING dbheap 2400"
db2 "UPDATE DB CFG FOR testdb USING locklist 1000"
db2 "UPDATE DB CFG FOR testdb USING logfilsiz 4000"
db2 "UPDATE DB CFG FOR testdb USING logprimary 12"
db2 "UPDATE DB CFG FOR testdb USING logsecond 20"
db2 "UPDATE DB CFG FOR testdb USING logbufsz 32"
db2 "UPDATE DB CFG FOR testdb USING avg_appls 5"
db2 "UPDATE DB CFG FOR testdb USING locktimeout 30"
db2 "UPDATE DB CFG FOR testdb using AUTO_MAINT off"

启用归档

[db2inst1@db01 db2data]$ db2 update db cfg for testdb using LOGARCHMETH1 DISK:/archivelogs/
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

启用增量备份

[db2inst1@db01 db2data]$ db2 update db cfg for testdb using TRACKMOD YES
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

解除backup pendinghttps://www.cndba.cn/hbhe0316/article/4811

[db2inst1@db01 db2data]$ db2 backup db testdb to /dev/null 

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

创建测试表，并插入数据

[db2inst1@db01 db2data]$ db2 connect to testdb

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.1.0
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTDB

[db2inst1@db01 db2data]$ db2 "CREATE TABLE t1(id int, val varchar(20))"
db2 "insert into t1 values(1, 'aaa')"
db2 "insert into t1 values(2, 'bbb')"
DB20000I  The SQL command completed successfully.
[db2inst1@db01 db2data]$ db2 "insert into t1 values(1, 'aaa')"
DB20000I  The SQL command completed successfully.
[db2inst1@db01 db2data]$ db2 "insert into t1 values(2, 'bbb')"
DB20000I  The SQL command completed successfully.
[db2inst1@db01 db2data]$

2.全量备份https://www.cndba.cn/hbhe0316/article/4811https://www.cndba.cn/hbhe0316/article/4811

[db2inst1@db01 db2data]$ db2 backup db testdb online to /db2data/backup/ include logs

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

3.插入数据再进行增量备份

[db2inst1@db01 db2data]$ db2 connect to testdb
[db2inst1@db01 db2data]$ db2 "insert into t1 values(3, 'ccc')"
[db2inst1@db01 db2data]$ db2 "insert into t1 values(4, 'ddd')"
[db2inst1@db01 db2data]$ db2 backup db testdb online INCREMENTAL to /db2data/backup/ include logs
Backup successful. The timestamp for this ackup image is : 20190812215450

4.插入数据并进行差异备份

[db2inst1@db01 db2data]$ db2 connect to testdb
[db2inst1@db01 db2data]$ db2 "insert into t1 values(5, 'eee')"
[db2inst1@db01 db2data]$ db2 "insert into t1 values(6, 'fff')"
[db2inst1@db01 db2data]$ db2 backup db testdb ONLINE INCREMENTAL DELTA to /db2data/backup/ include logs

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

5.活动日志

[db2inst1@db01 db2data]$ db2 connect to testdb
[db2inst1@db01 db2data]$ db2 "insert into t1 values(7, 'ggg')"
[db2inst1@db01 db2data]$ db2 "insert into t1 values(8, 'hhh')"

6.查看所有数据

[db2inst1@db01 db2data]$ db2 "select * from t1"

ID          VAL                 
----------- --------------------
          1 aaa                 
          2 bbb                 
          3 ccc                 
          4 ddd                 
          5 eee                 
          6 fff                 
          7 ggg                 
          8 hhh                 

  8 record(s) selected.

7.将三个备份文件拷贝至需要恢复的节点https://www.cndba.cn/hbhe0316/article/4811

[db2inst1@db01 backup]$ ll
total 278692
-rw------- 1 db2inst1 db2iadm1 184627200 Aug 12 21:52 TESTDB.0.db2inst1.DBPART000.20190812215227.001
-rw------- 1 db2inst1 db2iadm1  50376704 Aug 12 21:54 TESTDB.0.db2inst1.DBPART000.20190812215450.001
-rw------- 1 db2inst1 db2iadm1  50376704 Aug 12 22:00 TESTDB.0.db2inst1.DBPART000.20190812220039.001
[db2inst1@db01 db2data]$ scp -r backup/ 192.168.2.123:/db2data/

8.备份恢复步骤

全备：TESTDB.0.db2inst1.DBPART000.20190812215227.001
增量1：TESTDB.0.db2inst1.DBPART000.20190812215450.001
差异增量：TESTDB.0.db2inst1.DBPART000.20190812220039.001

.指定通过增量备份恢复的形式最后要恢复到 20190812220039https://www.cndba.cn/hbhe0316/article/4811

[db2inst1@db02 backup]$ db2 restore db testdb incremental from /db2data/backup/ taken at 20190812220039
DB20000I  The RESTORE DATABASE command completed successfully.

第一步恢复全备

[db2inst1@db02 backup]$ db2 restore db testdb incremental from /db2data/backup/ taken at 20190812215227 logtarget /db2data/logs/
DB20000I  The RESTORE DATABASE command completed successfully

第二步恢复增备1

https://www.cndba.cn/hbhe0316/article/4811 
[db2inst1@db02 backup]$ db2 restore db testdb incremental from /db2data/backup/ taken at 20190812215450 logtarget /db2data/logs/
SQL2580W  Warning! Restoring logs to a path which contains existing log files. 
Attempting to overwrite an existing log file during restore will cause the 
restore operation to fail.
Do you want to continue ? (y/n) y
DB20000I  The RESTORE DATABASE command completed successfully.

第三步恢复差异备份2https://www.cndba.cn/hbhe0316/article/4811

[db2inst1@db02 backup]$ db2 restore db testdb incremental from /db2data/backup/ taken at 20190812220039 logtarget /db2data/logs/
SQL2580W  Warning! Restoring logs to a path which contains existing log files. 
Attempting to overwrite an existing log file during restore will cause the 
restore operation to fail.
Do you want to continue ? (y/n) y
DB20000I  The RESTORE DATABASE command completed successfully.

查看logtarget下的日志

[db2inst1@db02 LOGSTREAM0000]$ pwd
/db2data/logs/NODE0000/LOGSTREAM0000
[db2inst1@db02 LOGSTREAM0000]$ ls
S0000001.LOG  S0000004.LOG  S0000007.LOG

3.确定所需的活动日志
在源数据库执行

[db2inst1@db01 backup]$ db2 get db cfg for testdb | grep -i 'First active log'
 First active log file                                   = S0000009.LOG

在目标数据库执行

[db2inst1@db02 backup]$ db2 get db cfg for testdb | grep -i 'First active log'
 First active log file                                   = S0000007.LOG

所以恢复的话是需要S0000008.LOG和S0000009.LOG的，DB01上的第一个活动日志是S0000009.LOG，故S0000008.LOG已经归档了，要把该日志拷贝到归档日志的目录中(恢复时的overflow log path
中，在此我们指定overflow log path为/data/db2data/logs下，刚刚restore时的logtarget)：

[db2inst1@db01 backup]$ cd /archivelogs/db2inst1/TESTDB/NODE0000/LOGSTREAM0000/C0000000
[db2inst1@db01 C0000000]$ scp S0000008.LOG db02:/db2data/logs/NODE0000/LOGSTREAM0000

拷贝归档日志，活动日志在/home/db2inst1/db2inst1/NODE0000/SQL00001/LOGSTREAM0000/下面https://www.cndba.cn/hbhe0316/article/4811

[db2inst1@db02 LOGSTREAM0000]$ db2 get db cfg for testdb | grep -i log
 Log retain for recovery status                          = NO
 User exit for logging status                            = YES
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 300
 Log buffer size (4KB)                        (LOGBUFSZ) = 32
 Log file size (4KB)                         (LOGFILSIZ) = 4000
 Number of primary log files                (LOGPRIMARY) = 12
 Number of secondary log files               (LOGSECOND) = 20
 Changed path to log files                  (NEWLOGPATH) = 
 Path to log files                                       = /home/db2inst1/db2inst1/NODE0000/SQL00001/LOGSTREAM0000/
 Overflow log path                     (OVERFLOWLOGPATH) = 
 Mirror log path                         (MIRRORLOGPATH) = 
 First active log file                                   = S0000007.LOG
 Block log on disk full                (BLK_LOG_DSK_FUL) = NO
 Block non logged operations            (BLOCKNONLOGGED) = NO
 Percent max primary log space by transaction  (MAX_LOG) = 0
 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
 Percent log file reclaimed before soft chckpt (SOFTMAX) = 520
 HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC
 HADR spool log data limit (4KB)      (HADR_SPOOL_LIMIT) = 0
 HADR log replay delay (seconds)     (HADR_REPLAY_DELAY) = 0
 First log archive method                 (LOGARCHMETH1) = DISK:/archivelogs/
 Archive compression for logarchmeth1    (LOGARCHCOMPR1) = OFF
 Options for logarchmeth1                  (LOGARCHOPT1) = 
 Second log archive method                (LOGARCHMETH2) = OFF
 Archive compression for logarchmeth2    (LOGARCHCOMPR2) = OFF
 Options for logarchmeth2                  (LOGARCHOPT2) = 
 Failover log archive path                (FAILARCHPATH) = 
 Number of log archive retries on error   (NUMARCHRETRY) = 5
 Log archive retry Delay (secs)         (ARCHRETRYDELAY) = 20
 Log pages during index build            (LOGINDEXBUILD) = OFF
 Log DDL Statements                      (LOG_DDL_STMTS) = NO
 Log Application Information             (LOG_APPL_INFO) = NO

拷贝活动日志

[db2inst1@db01 LOGSTREAM0000]$ scp -r /db2data/testdb/db2inst1/NODE0000/SQL00001/LOGSTREAM0000/ db02:/home/db2inst1/db2inst1/NODE0000/SQL00001
db2inst1@db02's password: 
S0000020.LOG                                                                                                                                                                            100%   16MB  15.6MB/s   00:00    
S0000012.LOG                                                                                                                                                                            100%   16MB  15.6MB/s   00:01    
S0000014.LOG                                                                                                                                                                            100%   16MB  15.6MB/s   00:00    
S0000019.LOG                                                                                                                                                                            100%   16MB  15.6MB/s   00:00    
S0000013.LOG                                                                                                                                                                            100%   16MB  15.6MB/s   00:01    
S0000009.LOG                                                                                                                                                                            100%   16MB  15.6MB/s   00:00    
S0000018.LOG                                                                                                                                                                            100%   16MB  15.6MB/s   00:00    
S0000017.LOG                                                                                                                                                                            100%   16MB  15.6MB/s   00:01    
S0000011.LOG                                                                                                                                                                            100%   16MB  15.6MB/s   00:00    
S0000010.LOG                                                                                                                                                                            100%   16MB  15.6MB/s   00:00    
S0000016.LOG                                                                                                                                                                            100%   16MB  15.6MB/s   00:00    
S0000015.LOG                                                                                                                                                                            100%   16MB  15.6MB/s   00:01    
S0000008.LOG                                                                                                                                                                            100%   12KB  12.0KB/s   00:00

执行前滚恢复https://www.cndba.cn/hbhe0316/article/4811

[db2inst1@db02 LOGSTREAM0000]$ db2 "rollforward db testdb to end of logs overflow log path (/db2data/logs)"

                                 Rollforward Status

 Input database alias                   = testdb
 Number of members have returned status = 1

 Member ID                              = 0
 Rollforward status                     = DB  working
 Next log file to be read               = S0000010.LOG
 Log files processed                    = S0000007.LOG - S0000009.LOG
 Last committed transaction             = 2019-08-12-14.32.48.000000 UTC

DB20000I  The ROLLFORWARD command completed successfully.
[db2inst1@db02 LOGSTREAM0000]$ db2 "rollforward db testdb complete"

                                 Rollforward Status

 Input database alias                   = testdb
 Number of members have returned status = 1

 Member ID                              = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000007.LOG - S0000009.LOG
 Last committed transaction             = 2019-08-12-14.32.48.000000 UTC

DB20000I  The ROLLFORWARD command completed successfully.

验证

[db2inst1@db02 LOGSTREAM0000]$ db2 connect to testdb

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.1.0
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTDB

[db2inst1@db02 LOGSTREAM0000]$ db2 "select * from t1"

ID          VAL                 
----------- --------------------
          1 aaa                 
          2 bbb                 
          3 ccc                 
          4 ddd                 
          5 eee                 
          6 fff                 
          7 ggg                 
          8 hhh                 

  8 record(s) selected.

db2 “rollforward db sample to 2005-12-23-12.09.02.000000 using local time and stop overflow log path(/home/db2inst1/db2log/)”

版权声明：本文为博主原创文章，未经博主允许不得转载。

oracle,linux

hbhe0316
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
DB2的日志相关参数
nayanminxing的专栏
10-13 2888
重要观点明晰1.LOGARCHMETH1和Path to log files的区别1.创建一个新数据库 [db2inst1@db22 ~]$ db2 create database yynewlog DB20000I The CREATE DATABASE command completed successfully. ####2.此时查看数据库目录 [db2inst1@db22 YYNEWLOG
DB2数据库的备份与恢复
zj_C_的博客
02-22 9471
1.恢复数据库 1.拷贝数据库至DB2服务器，例如路径“C:\Users\Administrator”下 2.打开“db2cmd”，默认路径就是上面的例子，如数据库不在这个目录下，需切换目录 在“D:\DB2\IBM\SQLLIB\license”路径下不存在数据库映像文件，恢复失败，输入命令切换路径 3.列出所有对数据库的连接：db2 list applications 4.输入“db2 restore db AUTOSR taken at 20220221103759”，回车，稍等几分钟，日.
DB2开启归档日志压缩，并通过恢复数据库前滚日志验证
liys0811的博客
08-03 342
在线备份数据库，备份完成后插入大量标记数据，然后恢复数据库，最后前滚到生成的压缩归档日志文件。设置后插入数据生成归档日志发现归档日志已经被压缩。2.设置归档日志压缩，注意需要重启实例才会生效。4.通过查询发现压缩的日志文件也能前滚使用。
AIX上DB2数据库日志满了的解决办法
一个老IT的成长日记
09-05 9580
现象：AIX上的DB2数据库，使用过程中，DataGrip忽然提示日志已满，数据无法保存。 调查：由于DBA不在，所以只能自己解决。先用连接用的账号登录了，执行db2 get db cfg for  dbname，发现没有权限。只好要来 instance的账号密码。 $su - instname $#取得设置内容 $db2 get db cfg for dbname $#找到以下设置,确定是...
DB2 archive log mode 设置
11-22 482
Two method to set up the db2 archive log mode. Archive log test: Action desc: Change LOGARCHMETH1, back...
DB2日志的管理
拙言的专栏
12-10 4076
DB重要的日志概念 $ db2 get db cfg for sample  Log file size (4KB) (LOGFILSIZ) = 1000  Number of primary log files (LOGPRIMARY) = 3  Number of secondary log files (LOGSECOND) = 2  Changed path to log
DB2数据库备份与恢复
03-06
DB2数据库备份与恢复 DB2数据库备份与恢复是指使用BACKUP、RESTORE和ROLLFORWARD命令来保存和恢复数据库中的数据。备份是指将数据库中的数据复制到外部存储设备中，以便在出现故障或灾难时能够快速恢复数据库。恢复...
DB2备份及恢复技术简介
10-23
备份与恢复是数据库管理的重要环节，DB2提供了离线和在线备份两种方式，以满足不同场景的需求。离线全备份通常在数据库无用户使用时进行，首先需确保没有活动应用，然后停止并重启数据库，接着执行备份命令。例如，...
db2数据库备份和恢复
09-12
1. **恢复前准备**：确保目标系统已安装有兼容版本的DB2，并有足够的磁盘空间存放恢复后的数据。 2. **恢复数据文件**：使用`db2 restore database`命令恢复数据文件。例如，`db2 restore database sample from e:\...
DB2数据库备份与恢复.zip
01-30
本资料包“DB2数据库备份与恢复.zip”聚焦于DB2数据库系统如何进行有效备份以及在数据丢失或系统故障时如何进行恢复操作。 首先，备份是防止数据丢失的重要手段。DB2提供了多种备份类型，包括完整备份、增量备份和...
DB2数据库备份恢复批处理工具
11-29
综上所述，"DB2数据库备份恢复批处理工具"是一个旨在提高数据库管理效率，保障数据安全的重要工具，它通过自动化备份和恢复流程，简化了数据库运维工作，降低了数据丢失的风险。在实际使用中，应结合具体业务需求，...
db2 日志管理
荣耀之路
04-20 8198
DB2日志是以文件的形式存放在文件系统中，分为两种模式：循环日志和归档日志。当创建新数据库时，日志的缺省模式是循环日志。在这种模式下，只能实现数据库的脱机备份和恢复。如果要实现联机备份和恢复，必须设为归档日志模式。
DB2 数据库备份、恢复和前滚
似水流年 -- 韩欣的博客
09-04 6550
  、 2003 年 3 月 01 日本文提供了执行数据库备份、恢复和前滚日志文件这些基本操作的循序渐进指南。以下示例将介绍这些实用程序句法上的变体，以便展示 DB2 产品的灵活性。简介本文提供了执行数据库备份、恢复和前滚日志文件这些基本操作的循序渐进指南。以下示例将介绍这些实用程序句法上的变体
基础架构：SQL查询语句的执行流程
qq_38670588的博客
09-11 393
目录MySQL基础架构示意图连接器长连接和短连接查询缓存分析器优化器执行器 MySQL基础架构示意图 大体上，MySQL 分为 Server 层和存储引擎层两部分。 Server 层： 包括连接器、查询缓存、分析器、执行器等，以及所有的内置函数（如日期、时间、数学和加密函数等）和跨存储引擎的功能（如存储过程、触发器、视图）。 存储引擎层： 负责数据的存储和提取，支持 InnoDB、MyISAM、Memory 等多个存储引擎。MySQL 5.5.5 版本后默认存储存储引擎是 InnoDB。 连接器 在查询
DB2日志和增量备份
最新发布
互联网知识分享
10-11 521
增量备份的原理是通过对数据库的日志进行解析，找出上次备份之后发生变化的数据，并将这些数据备份到备份文件中。恢复：通过回放日志中的操作，可以将数据库恢复到某个特定的时间点或者某个特定的事务操作之前的状态。崩溃恢复：在数据库发生崩溃时，可以利用日志文件来进行数据库的恢复，保证数据的一致性。：进行全量备份：首次备份需要进行全量备份，将整个数据库的数据备份到备份文件中。位置还原数据库，替换同名数据库，禁用自动配置数据库参数，并进行日志回滚操作。前滚日志：记录正在进行中的事务的操作，用于数据库的恢复和并发控制。
linux db2备份,DB2学习过程--备份与恢复（一）
weixin_42131276的博客
05-13 901
===========================================================【备份与恢复】===========================================================DB2 日志处理：DB2日志是以文件的形式存放在文件系统中，分为两种模式：循环日志和归档日志。当创建新数据库时，日志的缺省模式是循环日志。在这种模式下...
DB2数据库学习之数据库备份还原
HeapStone
03-02 9138
一、数据库离线（脱机）备份 1、cd /db2_backup 切换到存放备份文件的目录，如果没有要新建 2、db2 list applications for db dbName(数据库名) 确保没人使用数据库断开连接 3、db2stop force 4、db2stopdb2start 重启数据库实例保证断开全部连接 5、db2 backup dbdbName 执行备份命令 6、...
【DB2】数据库的事务日志已满。SQLSTATE=57011
ajsyipsc40270的博客
11-30 532
问题描述 在使用数据库的时候报错如上图，我们先使用db2 get db cfg for sample查看相关配置参数,其中sample为数据库名称 C:\Users\Thinkpad>db2 get db cfg for sample 数据库 sample 的数据库配置 数据库配置发行版级别...
db2循环日志备份恢复实战教程与要点
本文档深入探讨了DB2数据库的备份与恢复实践，由作者zhuhichn发布在JavaEye...这份文档不仅提供了理论知识，而且包含了大量的实践案例，对于DB2数据库管理员和希望学习DB2备份恢复技术的人来说，是一份宝贵的参考资料。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值