DB2数据库还原故事 — Ubuntu篇

背景

公司内部测试用服务器CentOS，本地虚拟机Ubuntu10。开始从生产服务器拿到一个备份，准备弄到本地做测试，这个还原实验进行了很久，开始是比较顺利的。后来就一直卡在一个db2 connect to上，每次连接都会显示下面的错误：

db2 connect to our_database
-- SQL0901N  The SQL statement failed because of a non-severe system error. 
-- Subsequent SQL statements can be processed.  (Reason "Scan interface error".)  
-- SQLSTATE=58004

Google一圈又一圈，没什么符合条件的答案，倒是有个帖子说更新数据库后就正常了。不过当时没有在意，服务器上的版本是：

DB21085I  Instance "db2inst1" uses "32" bits and DB2 code release "SQL09071"
with level identifier "08020107".
Informational tokens are "DB2 v9.7.0.1", "s091114", "IP23033", and Fix Pack "1".
Product is installed at "/opt/ibm/db2/V9.7".

而本地虚拟机Ubuntu上装的版本是"DB2 v9.7.0.0"，仅仅相差最后一个尾数，谁能想到，这竟然就是原因，报错就告诉我直接升级得了，无语了！！！都9.7了，还犯这种数据库还原的错误，真牛！！！

重新安装新版本数据库后，一切正常，下面把这个过程记录下来。

安装数据库

这一步很重要，直接影响到数据库是否能还原成功，最新的是9.7FixPack3，要是以后有更新的就用新的吧。这个过程都是图形安装，比Oracle简单多了，直接在DB2解压目录执行db2setup就可以了。

$ ./db2setup 
DBI1190I  db2setup is preparing the DB2 Setup wizard which will guide
      you through the program setup process. Please wait.


DBI1160I  Non-root install is being performed.

注意红色内容！切到root吧，避免不必要的麻烦。

卸载数据库

一不小心搞错什么就用这招吧！删除功能做得还是相当不错的！照着下面的URL一步步做！

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.qb.server.doc/doc/t0007439.html

如果忘记删除数据库，新数据库又无法还原的话，直接进入/home/db2inst/db2inst/，删除你之前还原的或使用过的数据库就行。vdir就能看到你熟悉的数据库了。

查看数据库版本信息

$su - db2inst1

$ whoami
db2inst1

$ db2level
DB21085I  Instance "db2inst1" uses "32" bits and DB2 code release "SQL09073" 
with level identifier "08040107".
Informational tokens are "DB2 v9.7.0.3", "s101006", "IP23218", and Fix Pack 
"3a".
Product is installed at "/opt/ibm/db2/V9.7".

还原数据库

以实例用户身份登录

su - db2inst1

查看数据库还原命令

db2 ? restore db

查看ROLLFORWARD命令

db2 ? rollforward db

这里我是把数据库备份文件放在/home/youbentou/share/Downloads目录下，还原时可根据具体情况做修改。

db2 restore db your_database from /home/youbentou/share/Downloads

DB20000I  The RESTORE DATABASE command completed successfully.

db2 rollforward db your_database to end of logs and stop

-------------------------
                                 Rollforward Status

 Input database alias                   = your_database
 Number of nodes have returned status   = 1

 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    =  -
 Last committed transaction             = 2010-04-20-21.08.17.000000 UTC

补充一下，有时候备份与还原的PATH可能不一致，这个时候需要手动指明具体PATH，利用工具db2ckbkp可以查看相关的信息：

db2ckbkp -S /mnt/hgfs/Downloads/DB2PTT/YOUR_DB.0.db2inst1.NODE0000.CATN0000.20110409213807.001


	Automatic storage enabled: Yes

	  Number of storage paths: 1

	             storage path: /DB2YOURDB (id = 0, state = 0x0)

	          Header:

	                  flavour: 1
	                  version: 0

	          Storage Group:

	                  flavour: 3
	                  version: 1
	               stateFlags: 0x0
	                  lastLSN: 0000000000000000
	               initialLSN: 0000000000000000
	               nextPathID: 1

还原的时候需要指定ON PATH路径。

创建数据库实例

数据库还原在实例db2inst1上，但有时我们可能会另起一个实例来访问这个数据库。当然，如果你不需要这样的访问机制，那就直接跳到最后的经验总结吧：）

首先创建一个Linux本地用户

sudo adduser timetrac

再创建数据库实例，如果不知道这个命令在哪，可以用下面方法查找：

find / -name db2icrt -type f 2>/dev/null

实例创建：

sudo /opt/ibm/db2/V9.7/instance/db2icrt -u db2fenc1 timetrac
-- DBI1070I  Program db2icrt completed successfully.

关联数据库

有了实例之后，下一步就是建立访问节点，用当前实例用户创建一个指向目标实例的节点，然后把这个节点所提供的数据库挂到当前实例上。过程如下：

timetrac@ubuntu:/opt/ibm/db2/V9.7/bin$ db2 catalog local node dbnode instance db2inst1
DB20000I  The CATALOG LOCAL NODE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is 
refreshed.
timetrac@ubuntu:/opt/ibm/db2/V9.7/bin$ db2 catalog db your_database at node dbnode
DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is 
refreshed.
timetrac@ubuntu:/opt/ibm/db2/V9.7/bin$ db2 connect to your_database

   Database Connection Information

 Database server        = DB2/LINUX 9.7.3
 SQL authorization ID   = TIMETRAC
 Local database alias   = YOUR_DATABASE

经验总结

db2 ? -- 显示所有db2命令列表

db2 ? command -- 描述db2命令列表的语法

db2 ? error -- 显示相关错误或信息，并告诉用户该如何应对，这个功能非常好，无论什么时候碰到问题都可以用它来询问DB2，来指导我们下一步应该怎样做。比如db2 connect to sample时，可能会出现下面这样的错误：

$ db2 connect to sample
SQL1013N  The database alias name or database name "SAMPLE" could not be 
found.  SQLSTATE=42705

这时就可以执行db2 ? SQL1013N，看看我们该怎么办

$ db2 ? SQL1013N


SQL1013N  The database alias name or database name "<name>" could not be
      found.

Explanation: 

The database name or alias specified in the command is not an existing
database or the database could not be found in the (client or server)
database directories.

User response: 

Ensure that the specified database name exists in the system database
directory. If the database name does not exist in the system database
directory, then the database either does not exist or the database name
has not been cataloged.

If the database name appears in the system database directory and the
entry type is INDIRECT, ensure that the database exists in the specified
local database directory. If the entry type is REMOTE, then ensure that
the database exists and is cataloged on the database directories of the
server node.

For CREATE DATABASE with the AT NODE clause, ensure that the database
name is in the system database directory with an entry type of INDIRECT
and with a catalog node number that does not equal -1.

Federated system users: in addition to the above, verify that the
database names specified in SYSCAT.SERVERS are all valid. Correct any
SYSCAT.SERVERS entry for which the database specified in that entry does
not exist.

 sqlcode: -1013

 sqlstate: 42705

这功能还算贴心，确实不错。

命令位置：/opt/ibm/db2/V9.7/install,/opt/ibm/db2/V9.7/instance,/opt/ibm/db2/V9.7/bin，install目录中工具与安装反安装相关，instance与实例相关，如查看实例，创建/删除实例等，bin里放置常用的工具。

Windows下DB2和Linux下DB2的数据库备份文件不能通用。

关于通信

如何本机访问一切正常，但局域网访问有问题时，需要查看路由或防火墙的配置，具体请查看相关操作系统帮助信息。之前遇到的就是CentOS的防火墙导致无法访问，打开后一切正常。另外还有一点，就是要注意DB2的通信方式，是否是TCPIP，如何设置请参考地址：http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/start/t0004727.htm

参考

http://it.toolbox.com/blogs/db2luw/db2-recovery-basics-part-1-22758