DB2 Backup & Recover

-
跨版本的备份恢复，必须使用离线备份集
备份类型：
1. 对数据库影响：
在线备份（热备份/联机备份）：用户可以使用
离线备份（冷备份/脱机备份）：用户无法使用
2. 备份内容：
完全备份：针对数据库所有数据的备份
增量备份：针对数据库部分数据备份
说明：累计增量、差异增量(delta备份)
累计增量：所有增量备份都是基于基础备份；log
差异增量：所有增量备份都是基于之前的增量备份。

备份文件命名规范：
Alias/type/instance/node/catalog/node/year/month/day/hour/minute/second/sequence


离线备份
离线备份：
backup database <dbname> [TO <path>]
查看当前到数据库的连接
db2 list applications
强制关闭所有到数据库连接
db2 force applications all
例如：
db2 force application all
db2 deactivate database B2CTEST1
backup database B2CTEST1 to "/tmp" with 2 buffers buffer 1024 parallelism 1 without prompting
增量备份
1. 修改DB2参数使其支持增量备份
db2 connect to <database name>;
update db cfg using logretain on;
update db cfg using trackmod on;
db2stop force;
db2start;
2. 增量备份
基础备份：db2 backup database <dbname> to <path>
差异增量备份：db2 backup database <dbname> incremental delta to <directory>
累计增量备份：db2 backup database <dbname> incremental to <directory> 
3. 增量还原
还原基础备份：db2 restore database <dbname> from <directory>
还原增量备份：db2 restore automatic
在线备份
1. 全库备份：db2 backup database <dbname> online to <directory>
2. 带日志全库备份：backup database <dbname> online to <directory> include logs
3. 表空间备份：backup database <dbname> tablespace (<tablespace_name1>, <tablespace_name2>,<tablespace3>) online to <directory>
检查备份完整性
db2ckbkp -h <backup file>
结果参数说明：
Backup Mode:     0：脱机备份、1：联机备份
Backup Type:      0：全备份、3：表空间备份
Backup Gran:      0：正常备份、16：增量备份、48：delta增量备份
Includes logs:      0：否、1：是
Compression:      0：没有压缩、1：压缩备份

恢复类型：
1. 崩溃或重启动恢复：DB2启动时自动运行
2. 版本及映像恢复：单单基于备份集的恢复
3. 向前回滚恢复：基于备份集和归档日志的恢复
数据库恢复命令：只能在数据库离线状态下可以恢复
restore database <dbname> from <path> [taken at <timestamp>] on <path> dbpathon <path>
说明： 
当路径下存在多个备份机，需要使用该参数[taken at <timestamp>]
on <path>：修改数据文件存储路径
dbpathon <path>：修改数据其他文件存储路径
例如：
db2 restore database b2ctest1 from "/tmp" taken at 20060905153604
db2 restore database b2ctest1 from "/tmp" on "/home/commerce"
离线备份恢复数据库：
restore database <dbname> from <directory> taken at <timestamp> without rolling forward without prompting
在线备份恢复数据库：
1. restore database <dbname> from <directory> taken at <timestamp> without prompting
2. 清空目标数据库的在线日志，或者备份到其他路径
3. 将源数据库的归档日志的最后一个，复制到目标数据库的在线日志目录
4. rolling forward database <dbname> to end of logs and complete
在线备份带日志恢复数据库及日志：
restore database <dbname> from <directory> taken at <timestamp> without prompting logtarget <directory>
在线备份带日志只恢复日志：
restore database <dbname> logs from <directory> logtarget <directory>
表空间恢复命令：可以离线或者在线恢复
restore database <dbname> tablespace (<tablespace_name>) online from <directory>
回滚：
检查是否需要回滚：db2 rollforward database <dbname> query status
前滚至日志最后：
db2 rollforward database <dbname> to end of logs and complete
前滚至某个时间：
db2 rollforward database <dbname> to timestamp and stop
使用标准世界时
db2 rollforward database <dbname> to timestamp using local time and stop
使用本地时间
例如：db2 rollforward database COMMERCE to 2012-03-28-14.50.00.000000 using local time and stop
如果备份和恢复期间，表空间名字变更，rollforward使用新名字
例如：
6-1 full backup
6-15 rename tablespace from emp_info to hr_info
7-1 恢复这个tablespace：
rollforward database <db name> to end of logs and stop tablespace(hr_info)
rollforward 命令参数overflow log path 指定归档日志路径
自动恢复
恢复并前滚至日志最后 recover db <dbname>
恢复并前滚至指定时间 recover db <dbname> to timestamp using local time
重建数据库
情况一：归档日志完整，没有完整的数据库备份，有每个表空间备份（时间戳不同）
1. db2 restore db <db name> rebuild with all tablespaces in database taken at <时间戳>
说明：时间戳可以选择表空间备份中最后一个的
2. db2 rollforward db <db name> to end of logs
3. db2 rollforward db <db name> stop
结果：数据库正常使用
情况二：归档日志完整，没有完整的数据库备份，有不完全表空间备份（时间戳不同）有SYSCATSPACE,USERSPACE1，USERSPACE2 缺失USERSPACE3
1. db2 restore db <db name> rebuild with tablespace (SYSCATSPACE, USERSPACE1, USERSPACE2) taken at <时间戳>
2. db2 rollforward db <dbname> to end of logs
3. db2 rollforward db <dbname> stop
结果：数据库正常使用除了USERSPACE3
找回USERSPACE3
1. db2 restore db <db name> tablespace (<tablespace_name>) taken at <时间戳>
2. db2 rollforward db <db name> to end of logs
3. db2 rollforward db <db name > stop
情况三：带日志的在线备份
1. db2 restore db <db name> rebuild with all tablespaces in dataabase taken at <时间戳> logtarget <path>
2. db2 rollforward db <db name> to end of logs overflow log path (<path>)
3. db2 rollforward db <db name> stop
查看备份和恢复过程
db2 list utilities
db2pd -utilities

Comments

Post a Comment

Popular posts from this blog

Nginx Proxy & Load Balance & LNMP

-
Nginx Proxy Service E-commerce website Server 1: provide image request response (  apache / nginx ) Server 2: provide dynamic request response ( php-fpm / apache-php ) Server 3: provide static request response ( apache / nginx ) Server 4: provide data request response ( mysql ) It need one additional nginx server 1. Install nginx 2. proxy to specific server Example location / {      proxy_pass http://192.168.199.187:80/; } location /bbs/ {      proxy_pass http://192.168.199.187:80/bbs/; } location ~* \.(jpg|png|gif)$  {      proxy_pass http://172.16.100.7; } Enhance the log information and specific the hostname location / {      proxy_pass  http://192.168.199.187:80/;      proxy_set_header <parameter name>  $host;  (client requst the hostname )      proxy_set_header <parameter name> $remote_addr; ...
Read more

Snort+barnyard2+Snorby CentOS 6.5_64 Installation

-
#download packages: daq-2.0.2.tar.gz ImageMagicK-6.8.9-8.tar.gz oinkmaster-2.0.tar.gz ruby-1.9.3-p547.tar.gz snort-2.9.6.2.tar.gz snortrules-snapshot-2962.tar.gz MySQL-server-5.5.40-1.rhel5.x86_64.rpm MySQL-client-5.6.21-1.rhel5.x86_64.rpm MySQL-devel-5.6.21-1.rhel5.x86_64.rpm MySQL-shared-5.6.21-1.rhel5.x86_64.rpm epel-release-6-8.noarch.rpm barnyard2-2.1.13.tar.gz #install yum package rpm -ivh epel-release-6-8.noarch.rpm yum install gcc-c++ patch readline readline-devel zlib zlib-devel flex git yum install libyaml-devel libffi-devel openssl-devel make  yum install bzip2 autoconf automake libtool bison iconv-devel libxml2-devel libxslt  yum install gcc g++ build-essential libssl-devel libreadline5-devel zlib1g-devel linux-headers-generic libsqlite3-devel libxslt-devel curl-devel sqlite-devel yum --enablerepo=epel install jasper jasper-libs jasper-devel #Mysql install yum remove mysql mysql-libs rpm -ivh MySQL-server-5.5.40-1.rh...
Read more

ORACLE Error

-
Image
oracle official error explanation  http://docs.oracle.com/cd/E11882_01/server.112/e17766/toc.htm 从服务器下载ALERT日志文件，传输方式需要选择"ASCII" ORA-01078: failure in processing system parameters 发生场景：新建数据库第一次启动时 SQL> startup ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file ‘/home/oracle/oracle/product/10.2.0/db_1/dbs/initorcl.ora’ 原因：oracle_sid没有配置正确 解决方案： 1. 修改oracle用户.bash_profile文件中ORACLE_SID后的内容； 2. 重新已oracle用户登录； ORA-01157: cannot identify/lock data file string - see DBWR trace file  Cause: The background process was either unable to find one of the data files or failed to lock it because the file was already in use. The database will prohibit access to this file but other files will be unaffected. However the first instance to open the database will need to access all online data files. Accompanying error from the operating system describes why the file could not be identified. Action: Have operating system make file ...
Read more