TSM备份DB2数据库[1].doc

1. 设置TSM服务器环境定义TSM的卷信息define dbvol /tsmdata/db.dsm formatsize=define logvol /tsmdata/log.dsm formatsize=100extend db 100extend log 500delete dbvol /usr/tivoli/tsm/server/bin/db.dsmdelete logvol /usr/tivoli/tsm/server/bin/log.dsmdefine spacetrigger db expansionprefix=/tsmdata/ maximumsize=1024define spacetrigger log expansionprefix=/tsmdata/ maximumsize=512定义基本的存储池：define stgpool diskpool DISK define vol diskpool /tsmdata/diskpool.dsm formatsize=1024定义策略域：copy domain standard test设置策略域中的策略集中的管理类中的副本组，定义使用指定的存储池：tsm：TSMupdate copygroup test standard standard destination=diskpoolANR1532I Backup copy group STANDARD updated in policy domain TEST, set STANDARD, management class STANDARD.tsm：TSMupdate copygroup test standard standard destination=diskpool type=archiveANR1537I Archive copy group STANDARD updated in policy domain TEST, set STANDARD, management class STANDARD.激活策略域：tsm：TSMactivate policy test standard查看副本组设置：tsm：TSMq copygroup test active standard format=detailed Policy Domain Name: TEST Policy Set Name: ACTIVE Mgmt Class Name: STANDARD Copy Group Name: STANDARD Copy Group Type: Backup Versions Data Exists: 2 Versions Data Deleted: 1 Retain Extra Versions: 30 Retain Only Version: 60 Copy Mode: Modified Copy Serialization: Shared Static Copy Frequency: 0 Copy Destination: DISKPOOLTable of Contents (TOC) Destination: Last Update by (administrator): ADMIN Last Update Date/Time: 01/28/2007 21:40:45 Managing profile: Changes Pending: Notsm：TSMq copygroup test active standard format=detailed type=archive Policy Domain Name: TEST Policy Set Name: ACTIVE Mgmt Class Name: STANDARD Copy Group Name: STANDARD Copy Group Type: Archive Retain Version: 365 Retention Initiation: Creation Retain Minimum Days: Copy Serialization: Shared Static Copy Frequency: CMD Copy Mode: Absolute Copy Destination: DISKPOOLLast Update by (administrator): ADMIN Last Update Date/Time: 01/28/2007 21:39:24 Managing profile: Changes Pending: No定义节点，并设其关联定制的策略域：register node db2node db2node domain=test 2. 设置TSM客户端设置OS环境变量$ env|grep DSMIDSMI_DIR=/usr/tivoli/tsm/client/api/binDSMI_CONFIG=/usr/tivoli/tsm/client/api/bin/dsm.optDSMI_LOG=/odsvr/log修改后,必须重启DB2.如果DSMI_LOG设置有误,将出错:SQL2062N An error occurred while accessing media /home/archive1/sqllib/adsm/libtsm.a. Reason code: 106.设置TSM客户端数据库参数db2 connect to sampledb2 update db cfg using TSM_NODENAME db2nodedb2 update db cfg using TSM_MGMTCLASS standarddb2 update db cfg using TSM_OWNER db2node (可选)db2 update db cfg using TSM_PASSWORD db2node如果TSM_NODENAME设置错误,将出错:SQL2062N An error occurred while accessing media /home/archive1/sqllib/adsm/libtsm.a. Reason code: 53.如果TSM_MGMTCLASS设置错误,将出错:SQL2062N An error occurred while accessing media /home/archive1/sqllib/adsm/libtsm.a. Reason code: 2025.如果TSM_PASSWORD设置错误,将出错:SQL2062N An error occurred while accessing media /home/archive1/sqllib/adsm/libtsm.a. Reason code: 137.(注:以上设置不需重启数据库)查看以上设定信息:$ db2 get db cfg for sample|grep TSM TSM management class (TSM_MGMTCLASS) = standardTSM node name (TSM_NODENAME) = db2nodeTSM owner (TSM_OWNER) = db2nodeTSM password (TSM_PASSWORD) = *以root用户为TSM的api客户端设定并加密TSM密码:# /usr/opt/db2_08_01/adsm/dsmapipw* Tivoli Storage Manager * API Version = 5.3.2 *Enter your current password:Enter your new password:Enter your new password again:Your new password has been accepted and updated.3. 设置数据库使用归档日志$ db2 terminate$ db2 update db cfg for sample using logretain onDB20000I UPDATE DATABASE CONFIGURATION 命令成功完成。设置数据库使用归档日志之后,使用数据库之前必需做一次完整备份：$ db2 backup db sample use tsm启动数据库更新跟踪:$ db2 update db cfg for sample using TRACKMOD on DB20000I UPDATE DATABASE CONFIGURATION 命令成功完成。查看数据库日志模式：$ db2 get db cfg for sample|grep LOGRETAIN启用的恢复的日志保留 (LOGRETAIN) = RECOVERY第一个日志归档方法 (LOGARCHMETH1) = LOGRETAIN查看日志文件路径:$ db2 get db cfg for sample|grep 日志文件路径日志文件路径 = /home/archive1/archive1/NODE0000/SQL00001/SQLOGDIR/4. 数据库备份4.1 每两月进行离线数据库全备份$ db2 create table t1(c1 char(20)离线数据库全备：$ db2 terminate$ db2 backup db sample use tsm备份成功。此备份映像的时间戳记是：20070201194607注:若执行备份的用户当前连接到数据库,执行备份前自动退出数据库连接若执行备份时另用用户连接数据库，会提示退出。如：$ db2 backup db sampleSQL1035N 当前正在使用数据库。 SQLSTATE=57019列出当前连到数据库的应用：$ db2 list applications for db sample强制当前连接退出：$ db2 force application all4.2 每周进行联机增备增备1:$ db2 connect to sample$ db2 insert into t1 values(增备1内容)$ db2 commit$ db2 backup db sample online incremental use tsm include logs备份成功。此备份映像的时间戳记是：20070201194742注：热备时默认不包括恢复所需的日志文件，这需要另外备份日志文件，以供恢复所需。加了include logs参数， 会自动备份恢复所需的当前活动log文件。？问题：经实验，include logs参数并不能备份日志。增备2:$ db2 deactivate db sample$ db2 connect to sample$ db2 insert into t1 values(增备2内容)$ db2 commit$ db2 backup db sample online incremental use tsm include logs备份成功。此备份映像的时间戳记是：200702011948514.3 验证备份内容注: 用q backup命令查不到做api客户端的备份,可以用DB2的命令通过API调用TSM查询:查看TSM归档映像:$ db2adutl query password db2node database sampleQuery for database SAMPLERetrieving FULL DATABASE BACKUP information. 1 Time: 20070201194607 Oldest log: S0000000.LOG DB Partition Number: 0 Sessions: 1 Retrieving INCREMENTAL DATABASE BACKUP information. 1 Time: 20070201194851 Oldest log: S0000003.LOG DB Partition Number: 0 Sessions: 1 2 Time: 20070201194742 Oldest log: S0000001.LOG DB Partition Number: 0 Sessions: 1 以上信息中同时显示出备份映像中是否包含了日志文件。由上可见，离线冷备不包含日志文件，联机热包含日志文件。从DB2数据库历史中检查增量复原映像序列:$ db2ckrst -d sample -t 20070201194851 Suggested restore order of images using timestamp 20070201111655 fordatabase sample.=restore db sample incremental taken at 20070201194851restore db sample incremental taken at 20070201194607restore db sample incremental taken at 20070201194851=列示DB2历史文件中的备份记录:$ db2 list history backup all for sample.Op 对象 时间戳记+序列 类型 设备 最早日志 当前日志 备份标识- - - - - - - - B D 20070201194607001 F A S0000000.LOG S0000000.LOG - 包含 2 表空间： 00001 SYSCATSPACE 00002 USERSPACE1 - 注释：DB2 BACKUP SAMPLE OFFLINE 开始时间：20070201194607 结束时间：20070201194628 状态：A- EID：3 位置：adsm/libtsm.aOp 对象 时间戳记+序列 类型 设备 最早日志 当前日志 备份标识- - - - - - - - B D 20070201194742001 O A S0000001.LOG S0000001.LOG - 包含 2 表空间： 00001 SYSCATSPACE 00002 USERSPACE1 - 注释：DB2 BACKUP SAMPLE ONLINE 开始时间：20070201194742 结束时间：20070201194802 状态：A- EID：4 位置：adsm/libtsm.aOp 对象 时间戳记+序列 类型 设备 最早日志 当前日志 备份标识- - - - - - - - B D 20070201194851001 O A S0000003.LOG S0000003.LOG - 包含 2 表空间： 00001 SYSCATSPACE 00002 USERSPACE1 - 注释：DB2 BACKUP SAMPLE ONLINE 开始时间：20070201194851 结束时间：20070201194911 状态：A- EID：5 位置：adsm/libtsm.a从TSM的存储池上查看备份内容:tsm: TSMq content /tsmdata/diskvol.dsm node=db2node format=detailed type=backup Node Name: DB2NODE Type: Bkup Filespace Name: /SAMPLE Hexadecimal Filespace Name: FSID: 11 Clients Name for File: /NODE0000/ FULL_BACKUP.20070201194607.1Hexadecimal Clients Name for File: Aggregated?: No Stored Size: 34.54 M Segment Number: Cached Copy?: No Node Name: DB2NODE Type: Bkup Filespace Name: /SAMPLE Hexadecimal Filespace Name: FSID: 11 Clients Name for File: /NODE0000/ DB_INCR_BACKUP.20070201194742.1Hexadecimal Clients Name for File: Aggregated?: No Stored Size: 9.53 M Segment Number: Cached Copy?: No Node Name: DB2NODE Type: Bkup Filespace Name: /SAMPLE Hexadecimal Filespace Name: FSID: 11 Clients Name for File: /NODE0000/ DB_INCR_BACKUP.20070201194851.1Hexadecimal Clients Name for File: Aggregated?: No Stored Size: 9.53 M Segment Number: Cached Copy?: No4.4 每天备份日志文件模拟生成日志：$ db2 terminate$ db2 deactivate db sample$ db2 connect to sample$ db2 insert into t1 values(第1日志文件的内容)$ db2 commit$ db2 terminate$ db2 deactivate db sample$ db2 connect to sample$ db2 insert into t1 values(第2日志文件的内容)$ db2 commit查看首个活动日志文件:$ db2 GET DATABASE CONFIGURATION for sample|grep 首个活动日志文件首个活动日志文件 = S0000005.LOG查看日志文件目录:$ ls -l /home/archive1/archive1/NODE0000/SQL00001/SQLOGDIR/总计 24168-rw- 1 archive1 db2grp1 12288 2月01 19时47 S0000000.LOG-rw- 1 archive1 db2grp1 12288 2月01 19时47 S0000001.LOG-rw- 1 archive1 db2grp1 12288 2月01 19时48 S0000002.LOG-rw- 1 archive1 db2grp1 12288 2月01 19时49 S0000003.LOG-rw- 1 archive1 db2grp1 12288 2月01 19时58 S0000004.LOG-rw- 1 archive1 db2grp1 4104192 2月01 19时58 S0000005.LOG-rw- 1 archive1 db2grp1 4104192 2月01 19时49 S0000006.LOG-rw- 1 archive1 db2grp1 4104192 2月01 19时58 S0000007.LOG由于最后一次增备的时间戳是20070201194851,对应当时日志文件是S0000003.LOG，所以编号大于3的日志文件都是数据库恢复需要前滚的，其中S0000004.LOG是活动日志，其余是活动日志。备份日志文件：$ db2stop$ dsmc backup /home/archive1/archive1/NODE0000/SQL00001/SQLOGDIR/5. 恢复数据库5.1 模拟数据库失败环境删除数据库:$ db2 drop db sample此命令也同时删除了归档日志目录新建一个数据库：$ db2 create db sample on /d2/backup$ db2 connect to sample$ db2 update db cfg using TSM_NODENAME db2node$ db2 update db cfg using TSM_MGMTCLASS standard$ db2 update db cfg using TSM_OWNER db2node(可选)$ db2 update db cfg using TSM_PASSWORD db2node$ db2 terminate注：如果直接将数据库备份到磁盘上（不使用TSM），恢复时可以直接恢复到一个新库中。而如果使用TSM恢复，必须恢复到一个现有的库中，并且对现有库进行以上设置。否则在恢复出错如下错误：SQL2062N 存取介质 /home/archive1/sqllib/adsm/libtsm.a时发生错误。原因码为：137。5.2 恢复数据库：$ db2 restore db sample incremental automatic use tsm taken at 20070201194851 into sampleSQL2529W 警告！复原到与备份映像数据库不相同的现有数据库，现有数据库的别名 SAMPLE 与备份映像的别名 SAMPLE 不匹配，并且现有数据库的数据库名称 SAMPNEW 与备份映像的数据库名称 SAMPLE 不匹配。目标数据库将被备份版本覆盖。将删除与目标数据库相关联的“前滚”恢复日志。想要继续吗？（y/n） y DB20000I RESTORE DATABASE 命令成功完成。此命令会自动使用全备20070201194607和增备20070201194851两个时间戳的备份完成恢复。恢复DB2 Online备份后，没有日志需要恢复,使用命令下面的命令完成恢复:$ db2 rollforward db sample complete或：$ db2 rollforward db sample stop如果需要恢复日志,使用命令:db2 rollforward db sample to end of logs and complete注：离线备份恢复时可以不需要恢复日志。但在线恢复恢复时必需有日志配合才能完整恢复。查看挂起数据库的前滚状态：$ db2 rollforward db sample query status此命令会显示下一个要读取的日志文件。5.3 恢复最后一次增备后的日志恢复归档日志:$ dsmc restore /home/archive1/archive1/NODE0000/SQL00001/SQLOGDIR/ANS1247I 正在等待服务器上的文件.正在恢复 12,288 /home/archive1/archive1/NODE0000/SQL00001/SQLOGDIR/S0000003.LOG 完成 正在恢复 12,288 /home/archive1/archive1/NODE0000/SQL00001/SQLOGDIR/S0000004.LOG 完成 正在恢复 12,288 /home/archive1/archive1/NODE0000/SQL00001/SQLOGDIR/S0000005.LOG 完成 正在恢复 4,104,192 /home/archive1/archive1/NODE0000/SQL00001/SQLOGDIR/S0000006.LOG 完成 正在恢复 4,104,192 /home/ar