BigdataLyn's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

[原创]DB2救命工具-db2dart

2016-05-09
DB2

DB2活动日志被误删除,怎么办?

活动日志被误删除

解决办法:

1.有备份的话,进行备份恢复,然后前滚到日志的末尾

2.如果没有备份,打电话给IBM技术中心协助处理了(db2lfh修改SQLOGCTL.LFH.1文件)

3.自己解决的话,只能使用最后的救命工具:db2dart

db2dart

db2dart使用限制有:

/DDEL(不支持LOB类型数据)
	Dumps formatted table data in delimited ASCII format. Requires four input values: 
	either a table object ID or table name, table space ID,page number to start with, and number of pages.

	The dumped delimited ASCII file is encoded in the database code page. 
	The db2dart command does not perform code page conversions.
	The /DDEL parameter supports only the following column data types. 
	If a table contains columns with any other data type, the column is skipped and not included in the delimited ASCII file.

		SMALLINT
		FLOAT
		REAL
		INTEGER
		TIME
		DECIMAL
		CHAR()
		VARCHAR()
		DATE
		TIMESTAMP
		BIGINT

	If a column of type CHAR and VARCHAR contains any binary data, or is defined with FOR BIT DATA,
	the /DDEL parameter generates the DEL file which contains the binary data. 
	When you load data from the DEL file to the table using the LOAD command, 
	ensure that you always specify the modified by delprioritychar option.

需要db2look知道表结构

然后db2dart /ddel 抽出数据之后 load 进去

如:

db2 "load from /tmp/DART0000/SAMPLE_TAB001.DEL of del modified by delprioritychar messages SAMPLE.BACKUP_OBJECTS.msg tempfiles path /tmp/LOAD insert into SAMPLE.BACKUP_OBJECTS nonrecoverable"

数据页损坏:”page is bad”

db2diag 下爆出类似如下错误:

FUNCTION: DB2 UDB, buffer pool services, sqlbcres, probe:10
MESSAGE : ZRC=0x86020001=-2046689279=SQLB_BADP "page is bad"
		  DIA8400C A bad page was encountered.
DATA #1 : String, 64 bytes
Error encountered trying to read a page - information follows :
DATA #2 : String, 23 bytes
Page verification error
DATA #3 : Page ID, PD_TYPE_SQLB_PAGE_ID, 4 bytes
16
DATA #4 : Object descriptor, PD_TYPE_SQLB_OBJECT_DESC, 104 bytes
	Obj: {pool:0;obj:0;type:0} Parent={0;0}
	lifeLSN:       0000000000000000
	tid:           0 0  0
	extentAnchor:                   0
	initEmpPages:                   0
	poolPage0:                      0
	poolflags:   0x              2102
	objectState: 0x                 0
	lastSMP:                        0
  pageSize:                    8192
  extentSize:                    16
  bufferPoolID:                   1
  partialHash:                    0
  objDescAttributes:                  0

db2look抽取:

db2look -d DBNAME -e -z SCHEMANAME -t TABLENAME -o TABLENAME.ddl

例如错误:

 In page 72200428, physical page 72200428 of object 4 in tablespace 11,pagesize 16384.
 In page 72200429, physical page 72200429 of object 4 in tablespace 11,pagesize 16384.
 In page 72200430, physical page 72200430 of object 4 in tablespace 11,pagesize 16384.
 In page 72200431, physical page 72200431 of object 4 in tablespace 11,pagesize 16384.

我们需要抽取跳过坏数据页的值:

page 0 到 72200427

page 72200432 到 9999999999

运行:

db2dart DBNAME /DDEL

然后我们删除并导入:

db2 drop table DBNAME

db2 -tvsf TABLENAME.ddl

db2 “load from part1.del of del insert into TABLENAME”

db2 “load from part2.del of del insert into TABLENAME”

db2dart 注意点

1. 如果缓冲池有数据没写到磁盘,用db2dart导出的数据可能是不完整的

2. db2dart抽取速度慢,如果表很多数据量大的情况,需要比较长的时间

3. 大数据对象db2dart抽取不了

4. 要有表,索引,存储过程,函数等对象的定义,一般用db2look,否则无法重建

5. 如果是系统表没有损坏才有可能用db2dart,如果损坏的是系统表,则只能restore的方式了.(所以平时的备份很重要!)

参考如下:

How to extract data from a corrupted table using db2dart

How to extract data if your DB2 LUW database becomes corrupted

Recovering a table that is hitting SQL1477N in DB2 9.*

db2dart恢复,是一个重复繁琐的苦力恢复工作!!!!切记备份和不要误删活动日志!



Comments