IBM DB2 备份和恢复

最新推荐文章于 2023-11-14 10:21:44 发布

bestlanzi

最新推荐文章于 2023-11-14 10:21:44 发布

阅读量1.1k

点赞数

文章标签： DB2

本文链接：https://blog.csdn.net/bestlanzi/article/details/48247117

版权

本文档记录DB2数据库的基本全备份和恢复操作过程。

参考知识库

http://www-01.ibm.com/support/docview.wss?uid=swg27023555#ic

http://www-01.ibm.com/support/docview.wss?uid=swg27023555

http://www-01.ibm.com/support/knowledgecenter/#!/SSEPGG_9.7.0/com.ibm.db2.luw.kc.doc/welcome.html

一、概述

DB2数据库需要修改参数使其从循环日志的方式切换为归档日志方式方能支持在线备份。修改参数这部分不在本文档记录范围。本文档记录基本的DB2备份脚本文件；定时执行备份脚本能定期获得备份时间内的数据库备份文件和归档日志文件。通过数据文件，可以从灾难中抢救数据，把数据库恢复到归档时间。适应于更改、交易不频繁的数据库灾备策略。

二、数据库备份

数据库在线备份脚本，分实例进行数据库备份；每个数据库实例部署一套

#!/bin/ksh

###############

#标红色字体为数据库名称（alias）使用数据库实例用户执行db2 list db directory | grep alias | awk '{print $4}'获得

#所有的数据库实例使用 ps -ef |grep db2sysc | awk '{print $1}' 获得

# function backup

# parameter1: Database name

# parameter2: Backup directory

# parameter3: Archive log directory

###############

db2backup()

{

echo ""

DBNAME=$1

BACKUP_DIR=$2

ARCH_DIR=$3

echo "Backuping DB ${DBNAME} "

currTime=`date`

echo "Current time: ${currTime}"

#备份数据库

db2 backup db ${DBNAME} online to ${BACKUP_DIR} compress include logs

#备份成功后，删除备份前的归档日志和备份文件

if [ $? -eq 0 ]

then

echo "Complete Backup Database ${DBNAME} ! "

currTime=`date`

echo "Current time: ${currTime}"

#删除7天前的备份文件和归档日志

echo "the delete backup file is ${DBNAME}*.001"

find ${BACKUP_DIR} -name ${DBNAME}*.001 -mtime 7 -print

find ${ARCH_DIR} -name *.LOG -mtime 7 -print

find ${BACKUP_DIR} -name ${DBNAME}*.001 -mtime 7 -print|xargs -I{} rm {}

find ${ARCH_DIR} -name *.LOG -mtime 7 -print |xargs -I{} rm {}

echo ""

echo "Pruning previous archive logs and backup images is Completed! "

currTime=`date`

echo "Current time: ${currTime}"

else

echo "Backup Database ${DBNAME} Occur Error ! "

echo "Please check db2diag.log ! "

}

getDayBeforeN()

{

N=$1

TODAY=`date "+%Y %m %d";echo " $N "`

DAY=`echo $TODAY|awk \

'{\

Year=$1; Month=$2; Day=$3; N=$4; \

M[1]=31; M[2]=28; M[3]=31; M[4]=30; M[5]=31; M[6]=30; \

M[7]=31; M[8]=31; M[9]=30; M[10]=31; M[11]=30; M[12]=31; \

if (Year%4==0 && (Year%100!=0 || Year%400==0)) M[2]=29; \

else M[2]=28; \

if (Day-N<=0) \

{ \

if (Month-1==0) { Year-=1; Month=12; } \

else Month-=1; \

if (Day-N==0) Day=M[Month]; \

else Day=M[Month]-N+Day; \

} \

else Day=Day-N; \

printf("%04d%02d%02d",Year,Month,Day); \

}'`;

echo $DAY

}

#初始化实例信息

. ~/sqllib/db2profile

#备份目录、归档目录、数据库名设置，按实际情况修改

backup_dir=/software/db2_data/backup

FDBKDB_archlog=/software/db2_data/archiveLog/db2inst1/FDBKDB

JCRDB_archlog=/software/db2_data/archiveLog/db2inst1/JCRDB

COMDB_archlog=/software/db2_data/archiveLog/db2inst1/COMDB

RELDB_archlog=/software/db2_data/archiveLog/db2inst1/RELDB

LMDB_archlog=/software/db2_data/archiveLog/db2inst1/LMDB

CUSDB_archlog=/software/db2_data/archiveLog/db2inst1/CUSDB

today=`date +%Y%m%d`

#归档日志文件的存放位置和归档日志文件名，注意用户的权限

logdir=/usr/IBM/db2inst1/scripts/logs

logfile=${logdir}/portal_db2_backup_${today}.log

echo "###############################################"|tee ${logfile}

echo "# Backup DB Start #"|tee ${logfile}

echo "###############################################"|tee ${logfile}

db2backup FDBKDB ${backup_dir} ${FDBKDB_archlog} |tee -a ${logfile}

db2backup JCRDB ${backup_dir} ${JCRDB_archlog} |tee -a ${logfile}

db2backup COMDB ${backup_dir} ${COMDB_archlog} |tee -a ${logfile}

db2backup RELDB ${backup_dir} ${RELDB_archlog} |tee -a ${logfile}

db2backup LMDB ${backup_dir} ${LMDB_archlog} |tee -a ${logfile}

db2backup CUSDB ${backup_dir} ${CUSDB_archlog} |tee -a ${logfile}

db2 terminate |tee -a ${logfile}

find ${logdir} -name " portal_db2_backup_*.log" -mtime 20 -exec rm {} \;

FiveDaysAgo=` getDayBeforeN 5 `

rm ${backup_dir}/*${FiveDaysAgo}*

echo "###############################################"|tee -a ${logfile}

echo "# Backup DB2 End #" |tee -a ${logfile}

echo "###############################################"|tee -a ${logfile}

以上述脚本为例，执行上述脚本，会在备份数据文件目录 backup_dir=/software/db2_data/backup中生成带时间戳的数据文件，如

以第一个文件为例，生成时间是8月20日14:46分，数据库名为COMDB, 时间戳为20150820144605 这两个信息用于灾难恢复数据库。

三、数据库灾难恢复

若遇到存储故障等灾难情况需要恢复数据库，此时先重建好数据库环境，配置实例用户后将备份数据文件拷贝至新DB2环境。以上述第一个文件为例，在其目录下执行命令，在当前目录下生产一个恢复sql文件，自定义为restore.sql

$db2 "restore db COMDB reldb TAKEN AT 20150820144605 redirect generate script restore.sql"

对这个restore.sql进行编辑

$vi restore.sql

以下是文件内容，每行开头的--标示注释，修改参数后切记删除注释标志，需要修改的字段在代码中标红显示

-- *****************************************************************************

-- ** automatically created redirect restore script

-- *****************************************************************************

UPDATE COMMAND OPTIONS USING S ON Z ON COMDB_NODE0000.out V ON;

SET CLIENT ATTACH_DBPARTITIONNUM 0;

SET CLIENT CONNECT_DBPARTITIONNUM 0;

-- *****************************************************************************

-- ** automatically created redirect restore script

-- *****************************************************************************

RESTORE DATABASE COMDB

-- USER <username>

-- USING '<password>'

FROM '/software/db2_data/backup'

TAKEN AT 20150820144605

-- ON '/usr/IBM/db2inst1'

--下面的字段DBPATH标示回复后数据库的路径，这在db2 list db directory中能看到，这里自定义为/tmp/restoretest/data

-- DBPATH ON ' /tmp/restoretest/data'

-- 下面into字段标示恢复后的数据库名称，这里可以默认和原来一样，我这里自定义为test

INTO TEST

--下面的LOGTARGET标示在恢复过程中回滚操作生产的日志文件路径，需要自定义，这里定义为/tmp/restoretest/log

LOGTARGET ' /tmp/restoretest/log'

-- NEWLOGPATH '/usr/IBM/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/'

-- WITH <num-buff> BUFFERS

-- BUFFER <buffer-size>

-- REPLACE HISTORY FILE

-- REPLACE EXISTING

REDIRECT

-- PARALLELISM <n>

-- COMPRLIB '<lib-name>'

-- COMPROPTS '<options-string>'

-- WITHOUT ROLLING FORWARD

-- WITHOUT PROMPTING

;

-- *****************************************************************************

-- ** table space definition

-- *****************************************************************************

-- ** Tablespace name = SYSCATSPACE

-- ** Tablespace ID = 0

-- ** Tablespace Type = Database managed space

-- ** Tablespace Content Type = All permanent data. Regular table space.

-- ** Tablespace Page size (bytes) = 8192

-- ** Tablespace Extent size (pages) = 4

-- ** Using automatic storage = Yes

-- ** Auto-resize enabled = Yes

-- ** Total number of pages = 16384

-- ** Number of usable pages = 16380

-- ** High water mark (pages) = 12592

-- *****************************************************************************

-- ** Tablespace name = TEMPSPACE1

-- ** Tablespace ID = 1

-- ** Tablespace Type = System managed space

-- ** Tablespace Content Type = System Temporary data

-- ** Tablespace Page size (bytes) = 8192

-- ** Tablespace Extent size (pages) = 32

-- ** Using automatic storage = Yes

-- ** Total number of pages = 1

-- *****************************************************************************

-- ** Tablespace name = USERSPACE1

-- ** Tablespace ID = 2

-- ** Tablespace Type = Database managed space

-- ** Tablespace Content Type = All permanent data. Large table space.

-- ** Tablespace Page size (bytes) = 8192

-- ** Tablespace Extent size (pages) = 32

-- ** Using automatic storage = Yes

-- ** Auto-resize enabled = Yes

-- ** Total number of pages = 12288

-- ** Number of usable pages = 12256

-- ** High water mark (pages) = 12064

-- *****************************************************************************

-- ** Tablespace name = SYSTOOLSPACE

-- ** Tablespace ID = 3

-- ** Tablespace Type = Database managed space

-- ** Tablespace Content Type = All permanent data. Large table space.

-- ** Tablespace Page size (bytes) = 8192

-- ** Tablespace Extent size (pages) = 4

-- ** Using automatic storage = Yes

-- ** Auto-resize enabled = Yes

-- ** Total number of pages = 4096

-- ** Number of usable pages = 4092

-- ** High water mark (pages) = 116

-- *****************************************************************************

-- ** start redirected restore

-- *****************************************************************************

RESTORE DATABASE COMDB CONTINUE;

-- *****************************************************************************

-- ** end of file

-- *****************************************************************************

保存后退出，先创建刚才需要建立的几个目录 mkdir -p /tmp/restoretest/data /tmp/restoretest/log

然后执行sql文件

$db2 -tvf restore.sql

此时，数据库已经建立，可以使用 db2 list db directory 查看到新建立的test数据库

但是此时我们尝试连接数据库是会报错的，如下：

这时执行最后一步：

$db2 "rollforward db TEST to end of logs and stop overflow log path ( '/tmp/restoretest/log')"

前滚操作完成，这时即可连接数据库，数据库恢复成功。

bestlanzi

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
IBM DB2 备份和恢复

本文档记录DB2数据库的基本全备份和恢复操作过程。参考知识库http://www-01.ibm.com/support/docview.wss?uid=swg27023555#ichttp://www-01.ibm.com/support/docview.wss?uid=swg27023555http://www-01.ibm.com/support/knowledgecenter
复制链接

扫一扫