本文档通过模拟DB2数据库的崩溃，展示了如何处理表空间异常。首先创建了一个名为ts1的表空间和一张表tb2，然后在执行大事务时杀掉db2进程，造成数据库崩溃。接着，通过mv命令模拟表空间异常，重启db2并观察崩溃恢复过程。在恢复过程中，表空间被置为ROLLFORWARD_PENDING状态，无法访问。最后通过将文件名还原，切换表空间在线并前滚到最新状态，成功恢复正常。
1.新建一个表空间ts1存储路径是/home/test/file
[test@demo SQL00001]$ db2 "create tablespace ts1 managed by database using (file '/home/test/file' 10M)"
DB20000I The SQL command completed successfully.
[test@demo ~]$ pwd
/home/test
[test@demo ~]$ ls
dlockmon file sqllib SQLOGDIR test
2.在表空间ts1上创建一张表
[test@demo ~]$ db2 "create table tb2(id int,name varchar(10)) in ts1"
3.执行一个大事务
[test@demo ~]$ db2 "begin atomic declare i int default 0;while (i<1000000) do insert into tb2 values(i,'yo'||char(i));set i=i+1;end while;end"
4.另一个窗口暴力kill掉db2进程
[root@demo Desktop]# ps -ef|grep db2sysc
root 3829 29534 0 15:39 pts/3 00:00:00 grep db2sysc
db2inst1 8676 8674 0 04:38 ? 00:00:05 db2sysc
test 25270 25268 0 02:49 pts/1 00:01:30 db2sysc
[root@demo Desktop]# kill -9 25270
[root@demo Desktop]# ps -ef|grep db2sysc
root 3874 29534 0 15:39 pts/3 00:00:00 grep db2sysc
db2inst1 8676 8674 0 04:38 ? 00:00:05 db2sysc
5.db2停止，SQL命令失败
[test@demo ~]$ db2 "begin atomic declare i int default 0;while (i<1000000) do insert into tb2 values(i,'yo'||char(i));set i=i+1;end while;end"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1224N The database manager is not able to accept new requests, has
terminated all requests in progress, or has terminated the specified request
because of an error or a forced interrupt. SQLSTATE=55032
6.mv /home/test/file /home/test/file-bak(重命令表空间container名字模拟表空间异常)
7,重启db2并连接数据库，观察崩溃恢复过程：
db2start
db2 connect to testdb
连接数据库的时候，自动进行数据库崩溃恢复，查看崩溃恢复进度：
[test@demo dlockmon]$ db2 list utilities show detail
ID = 1
Type = CRASH RECOVERY
Database Name = TESTDB
Partition Number = 0
Description = Crash Recovery
Start Time = 01/05/2019 16:39:03.549632
State = Executing
Invocation Type = User
Progress Monitoring:
Estimated Percentage Complete = 50
Phase Number [Current] = 1
Description = Forward
Total Work = 21361271 bytes
Completed Work = 21256820 bytes
Start Time = 01/05/2019 16:39:03.549639
Phase Number = 2
Description = Backward
Total Work = 21361271 bytes
Completed Work = 0 bytes
Start Time = Not Started
从db2diag.log看也有崩溃恢复信息:
2019-01-05-16.38.46.354876+480 E435613G302 LEVEL: Event
PID : 12429 TID : 1706352 PROC : db2star2
INSTANCE: test NODE : 000
FUNCTION: DB2 UDB, base sys utilities, DB2StartMain, probe:911
MESSAGE : ADM7513W Database manager has started.
START : DB2 DBM
2019-01-05-16.39.03.548337+480 I441038G492 LEVEL: Warning
PID : 12443 TID : 2951736208 PROC : db2sysc
INSTANCE: test NODE : 000 DB : TESTDB
APPHDL : 0-7 APPID: *LOCAL.test.190105083902
AUTHID : TEST
EDUID : 16 EDUNAME: db2agent (TESTDB)
FUNCTION: DB2 UDB, recovery manager, sqlpresr, probe:410
MESSAGE : Crash recovery started. LowtranLSN 000000001259D555 MinbuffLSN
0000000011EC8D99 ----->通过日志控制文件中的LSN，崩溃恢复开始的日志点
同时可以看到，表空间5的container不可访问，表空间状态被置为ROLLFORWARD_PENDING：
2019-01-05-16.39.03.468353+480 I438984G502 LEVEL: Error
PID : 12443 TID : 2951736208 PROC : db2sysc
INSTANCE: test NODE : 000 DB : TESTDB
APPHDL : 0-7 APPID: *LOCAL.test.190105083902
AUTHID : TEST
EDUID : 16 EDUNAME: db2agent (TESTDB)
FUNCTION: DB2 UDB, buffer pool services, sqlbStartPools, probe:30
MESSAGE : ZRC=0x8402001E=-2080243682=SQLB_CONTAINER_NOT_ACCESSIBLE
"Container not accessible"
2019-01-05-16.39.03.468872+480 E440025G552 LEVEL: Error
PID : 12443 TID : 2951736208 PROC : db2sysc
INSTANCE: test NODE : 000 DB : TESTDB
APPHDL : 0-7 APPID: *LOCAL.test.190105083902
AUTHID : TEST
EDUID : 16 EDUNAME: db2agent (TESTDB)
FUNCTION: DB2 UDB, buffer pool services, sqlbStartPoolsErrorHandling, probe:39
MESSAGE : ADM6080E The table space "TS1" (ID "5"), was put OFFLINE and in
ROLLFORWARD_PENDING. Tablespace state is 0x"00004080".
8.查看表空间5ts1里面的表tb2失败
[test@demo ~]$ db2 "select * from tb2"
ID NAME
----------- ----------
SQL0290N Table space access is not allowed. SQLSTATE=55039
查看表空间5的状态：
[test@demo ~]$ db2 list tablespaces
Tablespace ID = 5
Name = TS1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x4080
Detailed explanation:
Roll forward pending
Offline
[test@demo ~]$ db2 list tablespace containers for 5 show detail
Tablespace Containers for Tablespace 5
Container ID = 0
Name = /home/test/file
Type = File
Total pages = 2560
Useable pages = 2528
Accessible = No
9.把表空间5的container的文件重命名回来
[test@demo ~]$ mv file-bak file
修改表空间状态为online
[test@demo ~]$ db2 alter tablespace ts1 switch online
DB20000I The SQL command completed successfully.
[test@demo ~]$ db2 list tablespaces
Tablespace ID = 5
Name = TS1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x4080
Detailed explanation:
Roll forward pending.
10.前滚表空间到最新状态
db2 "rollforward db testdb to end of logs and complete tablespace(ts1) online"
查看前滚进度：
[test@demo dlockmon]$ db2 list utilities show detail
ID = 3
Type = ROLLFORWARD RECOVERY
Database Name = TESTDB
Partition Number = 0
Description = Online Tablespace Rollforward Recovery: 5
Start Time = 01/05/2019 16:51:26.406213
State = Executing
Invocation Type = User
Progress Monitoring:
Estimated Percentage Complete = 54
Phase Number = 1
Description = Forward
Total Work = 21361192 bytes
Completed Work = 21361192 bytes
Start Time = 01/05/2019 16:51:26.406220
Phase Number [Current] = 2
Description = Backward
Total Work = 18393787 bytes
Completed Work = 88830 bytes
Start Time = 01/05/2019 16:51:29.215704
11.表空间rollforward成功后状态正常
[test@demo ~]$ db2 "rollforward db testdb to end of logs and complete tablespace(ts1) online"
Rollforward Status
Input database alias = testdb
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 = 2019-01-05-08.38.05.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
扫一扫
5178
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
