Centos66 Db2 V9.1 升级到V9.7

最新推荐文章于 2021-10-02 10:57:00 发布

Dsql

最新推荐文章于 2021-10-02 10:57:00 发布

阅读量1.4k

点赞数

分类专栏： DB2 文章标签： db2升级

本文链接：https://blog.csdn.net/Eckel07/article/details/51505340

版权

DB2 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

常规升级

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