db2操作 连接、备份、恢复db2

先deactivate后再start standby再primary
报错不能启动hadr standby的时候，先restore，但是别rollback，直接start hadr as standby

先把hadr起来后再推icm

1、先把所有的库的hadr stop了
2、把primary的库离线备份
3、在副节点db2stop force ，再start（可以保证是处于deactivate（其实db2 DEACTIVATE DATABASE cinder就行）的状态，然后再restore）
4、db2 get db cfg for ceilodb2 | grep "Path to log files" 删除得到的文件夹里面所有文件比如得到node/ 就rm -rf node/*
5、在副节点restore（千万别rollback）
6、在副节点start hadr as standby
7、db2 ACTIVATE DATABASE cinder
8、在主节点start hadr as primary

db2默认需要切换到db2inst1这个默认用户才能执行操作

[root@localhost instance]# su - db2inst1

[db2inst1@localhost ~]$ db2start

[db2inst1@localhost ~]$ db2 force application all

[db2inst1@localhost ~]$ db2stop

[db2inst1@localhost DB2]$ db2stop force

显示所有的实例
[db2inst1@localhost ~]$ db2ilist

显示当前的实例
[db2inst1@localhost ~]$ db2 get instance

删除一个实例(注:需切换到root用户权限下)
[root@localhost ~]# cd /opt/ibm/db2/V9.7/instance
[root@localhost instance]# pwd
/opt/ibm/db2/V9.7/instance
[root@localhost instance]# ./db2idrop db2inst1
DBI1070I Program db2idrop completed successfully.

列出当前实例中有哪些数据库
[root@localhost instance]# su - db2inst1
[db2inst1@localhost ~]$ db2 list db directory

创建数据库
[root@localhost instance]# su - db2inst1
[db2inst1@localhost ~]$ db2 create database test

连接数据库
[db2inst1@localhost ~]$ db2 connect to test

注:用密码情况下格式[db2inst1@localhost ~]$ db2 connect to test user username using password
db2 connect to <database> user <username> using <password>

列出当前实例中所有激活的数据库
[root@localhost ~]# su - db2inst1
[db2inst1@localhost ~]$ db2 list active databases

查看表的空间
[db2inst1@localhost ~]$ db2 list tablespaces [ show detail ]

列出数据库中所有用户表
[db2inst1@localhost ~]$ db2 connect to test
[db2inst1@localhost ~]$ db2 list tables

创建表
[db2inst1@localhost ~]$ db2 "create table student (id int,fname varchar(30),age int)"

向表student中添加数据信息
[db2inst1@localhost ~]$ db2 "insert into student values (1,'Tom',22)"

显示表student所有的信息
[db2inst1@localhost ~]$ db2 "select * from student"

更新数据
[db2inst1@localhost ~]$ db2 "update student set age=22 where fname='Sunrier'"

查看表student结构
[db2inst1@localhost ~]$ db2 describe table student

[db2inst1@localhost ~]$ db2 "describe select * from student"

修改一个表的字段类型(如表people中的fname字段把varchar(30)改为varchar(28))
[db2inst1@localhost ~]$ db2 describe table people

[db2inst1@localhost ~]$ db2 "alter table people alter column fname set data type varchar(28)"

向一个表添加字段(如向表people中添加备注信息字段notes;向表people中添加分数字段score)
格式:db2 "alter table <tablename> add <columnname> <datatype>"

[db2inst1@localhost ~]$ db2 "alter table people add notes varchar(100)"

显示当前数据库连接有哪些应用程序
[root@localhost ~]# su - db2inst1
[db2inst1@localhost DB2]$ db2 list application

导出表中的数据

以DEL格式导出
db2 "export to teacher.txt of del select * from teacher"
db2 "export to teacher_bak.txt of del modified by coldel| select * from teacher"
[db2inst1@localhost ~]$ ls
db2inst1 sqllib Sunrier teacher.sql TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001
[db2inst1@localhost ~]$ db2 "export to teacher.txt of del select * from teacher"
SQL3104N The Export utility is beginning to export data to file
"teacher.txt".

备份数据库(如防止表误操作)
格式:db2 backup db <database name> [ to <dir name> ]
[db2inst1@localhost ~]$ db2 backup db test
SQL3105N The Export utility has finished exporting "2" rows.

恢复数据库(如将一个表删除后,通过删除前的备份文件恢复)
格式:db2 restore db <database name> [ from <dir name> ]
database name:表示恢复的数据库名

from <dir name>:表示为从哪个目录路径下恢复,为可选项,默认在当前目录下
[db2inst1@localhost ~]$ db2 connect to test
注:如果想把恢复的数据库更改为新的数据库名,则格式如下
db2 restore db <database name> [ from <dir name> into <new database name> ]
例:db2 restore db test from /home/db2inst1/Sunrier into testdb
或者db2 restore db test from "/home/db2inst1/Sunrier" into testdb

.查看test数据库备份的历史记录
格式:db2 list history backup all for <database name>
[db2inst1@localhost ~]$ db2 list history backup all for test

查看错误码信息
[db2inst1@localhost ~]$ db2 ? 22003
SQLSTATE 22003: A numeric value is out of range.

======================================back up ========================================
db2 list db directory

1、离线全备份
db2 force application all
1）、首先确保没有用户使用DB2:
db2 list applications for db sample
2）、停掉数据库并重新启动，以便断掉所有连接：
db2stop force
db2start
3）、执行备份命令：（使用TSM作为备份的介质）
db2 backup db sample use tsm
备份成功，将会返回一个时间戳。
4）、检查备份成功：
db2 list history backup all for sample 可以看到多了这个备份的纪录。
db2adutl query 命令也可以看到返回值。

5、恢复数据库

db2 restore db GLANCE from /var/wenbin/backup

当恢复后需要连接数据库的时候报错

Last login: Wed Jan 30 02:23:23 2013

[root@localhost ~]# su - db2inst1

[db2inst1@localhost ~]$ db2 connect to sample

SQL1117N  A connection to or activation of database "SAMPLE" cannot be made 

because of ROLL-FORWARD PENDING.  SQLSTATE=57019

####这个提示是说需要前滚期间的日志才能激活数据库

执行下面的语句：

db2 rollforward db GLANCE to end of logs and stop