常规升级
1、 安装db2 v9.1
1.1、 创建实例和fence用户
groupadd -g 1001 db2iadm1
groupadd -g 1002 db2fadm1
useradd -u 1001 -g db2iadm1 -m -d /rdbms/db2home/db2inst1 db2inst1
useradd -u 1002 -g db2fadm1 -m -d /rdbms/db2home/db2fenc1 db2fenc1
passwd db2inst1 <<!
db2inst1
db2inst1
!
passwd db2fenc1 <<!
db2fenc1
db2fenc1
!
1.2、db2precheck
[root@Centos01 disk1]# ./db2prereqcheck
ERROR:
The required library file libstdc++.so.5 is not found on the system.
Check the following web site for the up-to-date system requirements
of IBM DB2 9
http://www.ibm.com/software/data/db2/udb/sysreqs.html
http://www.software.ibm.com/data/db2/linux/validate
32位、64位的都安装
[root@Centos01 disk1]# yum provides libstdc++.so.5
[root@Centos01 disk1]# yum install compat-libstdc++-33
[root@Centos01 disk1]# yum install compat-libstdc++-33-3.2.3-69.el6.i686
yum install libstdc++-devel
1.3、修改hostname
/etc/hosts 、hostname --字符数小于8位
1.4、安装db2 v9.1
[root@Centos01 disk1]# ./db2_install
Default directory for installation of products - /opt/ibm/db2/V9.1
…
…
Task #46 end
The execution completed successfully.
1.5、创建实例
[root@db1 ~]# db2ls
Install Path Level Fix Pack Special Install Number Install Date
--------------------------------------------------------------------------------------------
/opt/ibm/db2/V9.1 9.1.0.12 12 Tue Mar 29 01:37:38 2016 CST
[root@db1 ~]# cd /opt/ibm/db2/V9.1/instance/
[root@db1 instance]# ./db2icrt -u db2fenc1 db2inst1
DBI1070I Program db2icrt completed successfully.
1.6、创建sample 库
[db2inst1@db1 ~]$ db2sampl
Starting the DB2 instance...
Creating database "SAMPLE"...
Connecting to database "SAMPLE"...
Creating tables and data in schema "DB2INST1"...
Stopping the DB2 instance...
'db2sampl' processing complete.
1.7、启动数据库做简单操作
[db2inst1@db1 ~]$ db2start
03/29/2016 02:19:41 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
[db2inst1@db1 ~]$ db2 connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 9.1.C
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
[db2inst1@db1 ~]$ db2 "create table test1 (c1 int)"
DB20000I The SQL command completed successfully.
[db2inst1@db1 ~]$ db2 "insert into test1 values(2),(3),(6)"
DB20000I The SQL command completed successfully.
[db2inst1@db1 ~]$ db2 "select * from test1"
C1
-----------
2
3
6
3 record(s) selected.
[db2inst1@db1 ~]$ db2 terminate
DB20000I The TERMINATE command completed successfully.
[db2inst1@db1 ~]$ db2stop
03/29/2016 02:24:23 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
2、安装db2 v9.7
2.1、安装前准备
[root@db1 server]# ./db2prereqcheck
WARNING:
DBT3534W The db2prereqcheck utility determined that ASLR is set to ON and that this could cause issues with some tools.
WARNING:
The 32-bit library file libpam.so is not found on the system.
[root@db1 server]# yum install pam-devel.i686
--set ASLR to off
[root@db1 server]# vi /etc/sysctl.conf
kernel.randomize_va_space = 0
[root@db1 server]# sysctl -p
2.2、安装software
[root@db1 server]# ./db2prereqcheck
[root@db1 server]# ./db2_install
Default directory for installation of products - /opt/ibm/db2/V9.7
…
…
Task #48 end
A minor error occurred while installing "DB2 Enterprise Server Edition " on
this computer. Some features may not function correctly.
---因SA MP 原因,可忽略
2.3、chkupgrade
[db2inst1@db1 server]$ ./db2ckupgrade sample -l db2ckupgrade.log
## Home directory of instance to upgrade is /db1/db2home/db2inst1
db2ckupgrade was successful. Database(s) can be upgraded.
[db2inst1@db1 server]$ db2ls
Install Path Level Fix Pack Special Install Number Install Date Installer UID
---------------------------------------------------------------------------------------------------------------------
/opt/ibm/db2/V9.1 9.1.0.12 12 Tue Mar 29 01:37:38 2016 CST 0
/opt/ibm/db2/V9.7 9.7.0.7 7 Tue Mar 29 02:44:55 2016 CST 0
2.4、升级实例
[root@db1 instance]# ./db2iupgrade -a SERVER_ENCRYPT db2inst1
db2ckupgrade was successful. Database(s) can be upgraded.
DBI1070I Program db2iupgrade completed successfully.
2.5、查看数据库软件版本
[db2inst1@db1 ~]$ db2level
DB21085I This instance or install (instance name, where applicable:
"db2inst1") uses "64" bits and DB2 code release "SQL09077" with level
identifier "08080107".
Informational tokens are "DB2 v9.7.0.7", "s121002", "IP23374", and Fix Pack
"7".
Product is installed at "/opt/ibm/db2/V9.7".
2.6、 启动数据库
[db2inst1@db1 ~]$ db2start
03/29/2016 03:03:18 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
2.7、 升级db2数据库
[db2inst1@db1 ~]$ db2 upgrade database sample
DB20000I The UPGRADE DATABASE command completed successfully.
[db2inst1@db1 ~]$
2.8、连接测试
[db2inst1@db1 ~]$ db2 connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 9.7.7
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
[db2inst1@db1 ~]$ db2 "select * from test"
SQL0204N "DB2INST1.TEST" is an undefined name. SQLSTATE=42704
[db2inst1@db1 ~]$ db2 "select * from test1"
C1
-----------
2
3
6
3 record(s) selected.
[db2inst1@db1 ~]$ db2 terminate
DB20000I The TERMINATE command completed successfully.
[db2inst1@db1 ~]$ db2stop
03/29/2016 04:07:27 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
注: 通过备份方式恢复升级,仅适用于备份是离线备份的情况
===直接通过备份恢复达到升级目的(实例目录与原环境一致情况)
3.1、安装db2 v9.7 创建新实例
groupadd -g 1001 db2iadm1
groupadd -g 1002 db2fadm1
useradd -u 1001 -g db2iadm1 -m -d /db1/db2home/db2inst1 db2inst1
useradd -u 1002 -g db2fadm1 -m -d /db1/db2home/db2fenc1 db2fenc1
passwd db2inst1 <<!
db2inst1
db2inst1
!
passwd db2fenc1 <<!
db2fenc1
db2fenc1
!
[root@Centos02 db2]# cd /opt/ibm/db2/V9.7/instance/
[root@Centos02 instance]# ./db2icrt -u db2fenc1 db2inst1
DBI1070I Program db2icrt completed successfully.
3.2、启动实例
[db2inst1@Centos02 ~]$ db2start
03/30/2016 02:53:19 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
3.3、开始还原操作
[db2inst1@Centos02 ~]$ db2 restore database SAMPLE from /db1 taken at 20160329075525 into newsamp
SQL2555I The database was restored and then successfully upgraded to the
current DB2 release where you issued the RESTORE DATABASE command.
[db2inst1@Centos02 ~]$ db2 list db directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = NEWSAMP
Database name = NEWSAMP
Local database directory = /db1/db2home/db2inst1
Database release level = d.00
Comment = A sample database
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
[db2inst1@Centos02 ~]$ db2 connect to newsamp
Database Connection Information
Database server = DB2/LINUXX8664 9.7.7
SQL authorization ID = DB2INST1
Local database alias = NEWSAMP
[db2inst1@Centos02 ~]$ db2 get snapshot for database on newsamp |more
Database Snapshot
Database name = NEWSAMP
Database path = /db1/db2home/db2inst1/db2inst1/NODE0000/SQL00001/
Input database alias = NEWSAMP
Database status = Active
Catalog database partition number = 0
Catalog network node name =
Operating system running at database server= LINUXAMD64
Location of the database = Local
First database connect timestamp = 03/30/2016 03:09:08.231474
Last reset timestamp =
Last backup timestamp =
Snapshot timestamp = 03/30/2016 03:09:25.281186
Number of automatic storage paths = 1
Automatic storage path = /db1/db2home/db2inst1
Node number = 0
State = In Use
[db2inst1@Centos02 db1]$ db2level
DB21085I This instance or install (instance name, where applicable:
"db2inst1") uses "64" bits and DB2 code release "SQL09077" with level
identifier "08080107".
Informational tokens are "DB2 v9.7.0.7", "s121002", "IP23374", and Fix Pack
"7".
Product is installed at "/opt/ibm/db2/V9.7".
==直接通过恢复备份达到升级目的(实例目录与原环境不一致情况)
==从备份库路径/db1/db2home/db2inst1,恢复到另一台机器的/db2/db2home/db2inst1上
4.1、创建实例和fence用户
[root@Centos02 server]# cat t.sh
groupadd -g 1001 db2iadm1
groupadd -g 1002 db2fadm1
useradd -u 1001 -g db2iadm1 -m -d /db2/db2home/db2inst1 db2inst1
useradd -u 1002 -g db2fadm1 -m -d /db2/db2home/db2fenc1 db2fenc1
passwd db2inst1 <<!
db2inst1
db2inst1
!
passwd db2fenc1 <<!
db2fenc1
db2fenc1
!
4.2、 创建新实例
[root@Centos02 db2]# cd /opt/ibm/db2/V9.7/instance/
[root@Centos02 instance]# ./db2icrt -u db2fenc1 db2inst1
DBI1070I Program db2icrt completed successfully.
[db2inst1@Centos02 ~]$ db2start
03/30/2016 03:26:04 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
[db2inst1@Centos02 ~]$ pwd
/db2/db2home/db2inst1
[db2inst1@Centos02 ~]$ ls
sqllib
4.3、 生产恢复脚本restoSamp.sh
[db2inst1@Centos02 db2]$ mkdir sampdb
---参数 REDIRECT GENERATE SCRIPT restoSamp.sh 指定生成恢复脚本
[db2inst1@Centos02 db2]$ db2 restore db sample from /db2 taken at 20160329075525 REDIRECT GENERATE SCRIPT restoSamp.sh
DB20000I The RESTORE DATABASE command completed successfully.
查看刚生成的恢复脚本
[db2inst1@Centos02 db2]$ cat restoSamp.sh
-- *****************************************************************************
-- ** automatically created redirect restore script
-- *****************************************************************************
UPDATE COMMAND OPTIONS USING S ON Z ON SAMPLE_NODE0000.out V ON;
SET CLIENT ATTACH_DBPARTITIONNUM 0;
SET CLIENT CONNECT_DBPARTITIONNUM 0;
-- *****************************************************************************
-- ** automatically created redirect restore script
-- *****************************************************************************
RESTORE DATABASE SAMPLE
-- USER <username>
-- USING '<password>'
FROM '/db2'
TAKEN AT 20160329075525
-- ON '/db1/db2home/db2inst1'
-- DBPATH ON '<target-directory>'
INTO SAMPLE
-- NEWLOGPATH '/db1/db2home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/'
-- WITH <num-buff> BUFFERS
-- BUFFER <buffer-size>
-- REPLACE HISTORY FILE
-- REPLACE EXISTING
REDIRECT
-- PARALLELISM <n>
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) = 4096
-- ** 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) = 9020
-- *****************************************************************************
-- *****************************************************************************
-- ** Tablespace name = TEMPSPACE1
-- ** Tablespace ID = 1
-- ** Tablespace Type = System managed space
-- ** Tablespace Content Type = System Temporary data
-- ** Tablespace Page size (bytes) = 4096
-- ** 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) = 4096
-- ** Tablespace Extent size (pages) = 32
-- ** Using automatic storage = Yes
-- ** Auto-resize enabled = Yes
-- ** Total number of pages = 8192
-- ** Number of usable pages = 8160
-- ** High water mark (pages) = 1888
-- *****************************************************************************
-- *****************************************************************************
-- ** Tablespace name = IBMDB2SAMPLEREL
-- ** Tablespace ID = 3
-- ** Tablespace Type = Database managed space
-- ** Tablespace Content Type = All permanent data. Large table space.
-- ** Tablespace Page size (bytes) = 4096
-- ** Tablespace Extent size (pages) = 32
-- ** Using automatic storage = Yes
-- ** Auto-resize enabled = Yes
-- ** Total number of pages = 8192
-- ** Number of usable pages = 8160
-- ** High water mark (pages) = 672
-- *****************************************************************************
-- *****************************************************************************
-- ** start redirected restore
-- *****************************************************************************
RESTORE DATABASE SAMPLE CONTINUE;
-- *****************************************************************************
-- ** end of file
-- *****************************************************************************
==从备份库路径/db1/db2home/db2inst1,恢复到另一台机器的/db2/db2home/db2inst1上
4.4、 根据需要修改恢复脚本
[db2inst1@Centos02 db2]$ ls /db2/db2home/db2inst1/
sqllib
-------根据实际修改相应路径
[db2inst1@Centos02 db2]$ db2 RESTORE DATABASE SAMPLE FROM '/db2' TAKEN AT 20160329075525 ON '/db2/db2home/db2inst1' DBPATH ON '/db2/db2home/db2inst1' INTO SAMPLE NEWLOGPATH '/db2/db2home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/' REDIRECT WITHOUT ROLLING FORWARD ;
SQL1277W A redirected restore operation is being performed. Table space
configuration can now be viewed and table spaces that do not use automatic
storage can have their containers reconfigured.
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst1@Centos02 db2]$ ls /db2/db2home/db2inst1/
db2inst1 sqllib
[db2inst1@Centos02 db2]$ du -sh /db2/db2home/db2inst1/*
131M /db2/db2home/db2inst1/db2inst1
75M /db2/db2home/db2inst1/sqllib
4.5、开始恢复
可以通过db2pd -util 或 db2 list utilities show detail 查看恢复进度
[db2inst1@Centos02 db2]$ db2 RESTORE DATABASE SAMPLE CONTINUE;
SQL2555I The database was restored and then successfully upgraded to the
current DB2 release where you issued the RESTORE DATABASE command.
[db2inst1@Centos02 db2]$ du -sh /db2/db2home/db2inst1/*
207M /db2/db2home/db2inst1/db2inst1
75M /db2/db2home/db2inst1/sqllib
[db2inst1@Centos02 db2]$ db2 list db directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = SAMPLE
Database name = SAMPLE
Local database directory = /db2/db2home/db2inst1
Database release level = d.00
Comment = A sample database
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
[db2inst1@Centos02 db2]$ db2 connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 9.7.7
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
[db2inst1@Centos02 db2]$ db2 get snapshot for database on sample |more
Database Snapshot
Database name = SAMPLE
Database path = /db2/db2home/db2inst1/db2inst1/NODE0000/SQL00001/
Input database alias = SAMPLE
Database status = Active