金仓数据库KingbaseES 控制文件损坏后恢复操作

沉舟侧畔千帆过_

已于 2022-12-20 11:32:56 修改

阅读量298

点赞数

分类专栏：数据库文章标签：数据库 java 开发语言金仓数据库 kingbase

于 2022-12-20 11:13:05 首次发布

本文链接：https://blog.csdn.net/arthemis_14/article/details/128382255

版权

数据库专栏收录该内容

550 篇文章 29 订阅

订阅专栏

sys_ control文件损坏：需要手工指定一些参数完成sys_resetwal相关操作

当前数据库信息

test=# \d
                     关联列表
 架构模式 |        名称         |  类型  | 拥有者 
----------+---------------------+--------+--------
 public   | sys_stat_statements | 视图   | system
 public   | t                   | 数据表 | system
 public   | t2                  | 数据表 | system
 public   | t3                  | 数据表 | system
 public   | t4                  | 数据表 | system
 public   | t5                  | 数据表 | system
 public   | t6                  | 数据表 | system
 public   | t7                  | 数据表 | system
 public   | t8                  | 数据表 | system
 public   | tbl                 | 数据表 | system
(10 行记录)

test=# select * from t;
 bl |    var    
----+-----------
 1  | 12313
 0  | 12231313
 0  | 122se1
 1  | 1sdas2se1
(4 行记录)

test=# 

[kingbase@kingbase ~]$ ps -ef|grep kingbase
root       812  1194  0 14:56 ?        00:00:00 sshd: kingbase [priv]
kingbase   817   812  0 14:56 ?        00:00:00 sshd: kingbase@pts/2
kingbase   818   817  0 14:56 pts/2    00:00:00 -bash
root      9502  9452  0 17:37 pts/0    00:00:00 su - kingbase
kingbase  9503  9502  0 17:37 pts/0    00:00:00 -bash
root     12554  1194  0 18:35 ?        00:00:00 sshd: kingbase [priv]
kingbase 12559 12554  0 18:35 ?        00:00:00 sshd: kingbase@pts/3
kingbase 12560 12559  0 18:35 pts/3    00:00:00 -bash
kingbase 14046     1  0 19:02 ?        00:00:00 /home/kingbase/V8R6C6B21/ES/V8/KESRealPro/V008R006C006B0021/Server/bin/kingbase -D data
kingbase 14047 14046  0 19:02 ?        00:00:00 kingbase: logger   
kingbase 14049 14046  0 19:02 ?        00:00:00 kingbase: checkpointer   
kingbase 14050 14046  0 19:02 ?        00:00:00 kingbase: background writer   
kingbase 14051 14046  0 19:02 ?        00:00:00 kingbase: walwriter   
kingbase 14052 14046  0 19:02 ?        00:00:00 kingbase: autovacuum launcher   
kingbase 14053 14046  0 19:02 ?        00:00:00 kingbase: stats collector   
kingbase 14054 14046  0 19:02 ?        00:00:00 kingbase: ksh writer   
kingbase 14055 14046  0 19:02 ?        00:00:00 kingbase: ksh collector   
kingbase 14056 14046  0 19:02 ?        00:00:00 kingbase: kwr collector   
kingbase 14057 14046  0 19:02 ?        00:00:00 kingbase: logical replication launcher   
kingbase 14062 12560  0 19:02 pts/3    00:00:00 ksql -Usystem -dtest
kingbase 14063 14046  0 19:02 ?        00:00:00 kingbase: system test [local] idle
kingbase 14118   818  0 19:03 pts/2    00:00:00 ps -ef
kingbase 14119   818  0 19:03 pts/2    00:00:00 grep --color=auto kingbase
[kingbase@kingbase ~]$ 
[kingbase@kingbase ~]$ cd data/global/
[kingbase@kingbase global]$ pwd
/home/kingbase/data/global

[kingbase@kingbase global]$ ls -l
总用量 664
-rw------- 1 kingbase kingbase     0 11月 10 17:06 8059
-rw------- 1 kingbase kingbase     0 11月 10 17:06 8074
-rw------- 1 kingbase kingbase  8192 11月 10 17:06 8076
-rw------- 1 kingbase kingbase  8192 11月 10 19:02 sys_control
-rw------- 1 kingbase kingbase   512 11月 10 17:06 sys_filenode.map
-rw------- 1 kingbase kingbase 34324 11月 10 19:02 sys_internal.init
[kingbase@kingbase global]$ 


[kingbase@kingbase ~]$ sys_ctl -D data stop
waiting for server to shut down...... done
server stopped
[kingbase@kingbase ~]$ ps -ef|grep kingbase
root       812  1194  0 14:56 ?        00:00:00 sshd: kingbase [priv]
kingbase   817   812  0 14:56 ?        00:00:00 sshd: kingbase@pts/2
kingbase   818   817  0 14:56 pts/2    00:00:00 -bash
root      9502  9452  0 17:37 pts/0    00:00:00 su - kingbase
kingbase  9503  9502  0 17:37 pts/0    00:00:00 -bash
root     12554  1194  0 18:35 ?        00:00:00 sshd: kingbase [priv]
kingbase 12559 12554  0 18:35 ?        00:00:00 sshd: kingbase@pts/3
kingbase 12560 12559  0 18:35 pts/3    00:00:00 -bash
kingbase 14062 12560  0 19:02 pts/3    00:00:00 ksql -Usystem -dtest
kingbase 14303  9503  0 19:06 pts/0    00:00:00 ps -ef
kingbase 14304  9503  0 19:06 pts/0    00:00:00 grep --color=auto kingbase

模拟control文件损坏

[kingbase@kingbase global]$ pwd
/home/kingbase/data/global
[kingbase@kingbase global]$ ls -l sys_control 
-rw------- 1 kingbase kingbase 8192 11月 10 19:02 sys_control
[kingbase@kingbase global]$ rm -rf sys_control 
[kingbase@kingbase global]$

无法启动

[kingbase@kingbase ~]$ sys_ctl -D data start
waiting for server to start....kingbase: could not find the database system
Expected to find it in the directory "/home/kingbase/data",
but could not open file "/home/kingbase/data/global/sys_control": No such file or directory
 stopped waiting
sys_ctl: could not start server
Examine the log output.
[kingbase@kingbase ~]$

再次启动

[kingbase@kingbase ~]$ sys_ctl -D data start
waiting for server to start....2022-11-10 11:09:18.017 GMT [14432] FATAL:  could not read file "/home/kingbase/data/global/sys_control": read 0 of 368
 stopped waiting
sys_ctl: could not start server
Examine the log output.

使用 sys_resetwal恢复

sys_resetwal 使用说明

[kingbase@kingbase ~]$ sys_resetwal --help
sys_resetwal 重置一个Kingbase数据库集簇的预写式日志.
用法:
  sys_resetwal [选项]... 数据目录
选项:
  -c, --commit-timestamp-ids=XID,XID
                                 设置提交时间可以检索到的最老的和最新的事务ID
                                 (0意味着没有变化)
 [-D, --kingbase-data=]DATADIR          data directory
  -e, --epoch=XIDEPOCH           设置下一个事务ID的epoch
  -f, --force                    强制更新完成
  -g, --dbmode                   set database mode
  -l, --next-wal-file=WALFILE    设置新的WAL最小起始位置
  -m, --multixact-ids=MXID,MXID  设置下一个和最旧的多事务ID
  -n, --dry-run                  不更新，只显示将要执行的操作
  -o, --next-oid=OID             设置下一个OID
  -O, --multixact-offset=OFFSET  设置下一个多事务偏移量
  -V, --version                  输出版本信息，然后退出
  -x, --next-transaction-id=XID  设置下一个事务ID
      --wal-segsize=SIZE         WAL段的大小（兆字节）
  -?, --help                     显示本帮助，然后退出

Report bugs to <kingbase-bugs@kingbase.com.cn>.

设置next-wal-file

-l, –next-wal-file=WALFILE
设置新的WAL最小起始位置，这个值可以从data/sys_wal目录下去看最后一个WAL文件，这个文件的id+1即可

[kingbase@kingbase sys_wal]$ pwd
/home/kingbase/data/sys_wal
[kingbase@kingbase sys_wal]$ ls -l |wc -l
42
[kingbase@kingbase sys_wal]$ ll
总用量 655360
-rw------- 1 kingbase kingbase 16777216 11月 10 19:03 000000010000000000000029
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 00000001000000000000002A
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 00000001000000000000002B
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 00000001000000000000002C
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 00000001000000000000002D
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 00000001000000000000002E
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 00000001000000000000002F
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000030
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000031
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000032
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000033
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000034
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000035
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000036
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000037
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000038
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000039
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 00000001000000000000003A
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 00000001000000000000003B
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 00000001000000000000003C
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 00000001000000000000003D
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 00000001000000000000003E
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 00000001000000000000003F
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000040
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000041
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000042
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000043
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000044
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000045
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000046
-rw------- 1 kingbase kingbase 16777216 11月 10 18:26 000000010000000000000047
-rw------- 1 kingbase kingbase 16777216 11月 10 18:26 000000010000000000000048
-rw------- 1 kingbase kingbase 16777216 11月 10 18:26 000000010000000000000049
-rw------- 1 kingbase kingbase 16777216 11月 10 18:26 00000001000000000000004A
-rw------- 1 kingbase kingbase 16777216 11月 10 18:26 00000001000000000000004B
-rw------- 1 kingbase kingbase 16777216 11月 10 18:26 00000001000000000000004C
-rw------- 1 kingbase kingbase 16777216 11月 10 18:26 00000001000000000000004D
-rw------- 1 kingbase kingbase 16777216 11月 10 18:26 00000001000000000000004E
-rw------- 1 kingbase kingbase 16777216 11月 10 18:26 00000001000000000000004F
-rw------- 1 kingbase kingbase 16777216 11月 10 18:26 000000010000000000000050
drwx------ 2 kingbase kingbase        6 11月 10 17:06 archive_status
[kingbase@kingbase sys_wal]$ 

000000010000000000000050 + 1 000000010000000000000051

-l 000000010000000000000051

设置next-transaction

-x, –next-transaction-id=XID,设置下一个事务ID，这个值可以从sys_xact目录下的文件中查询

[kingbase@kingbase sys_xact]$ pwd
/home/kingbase/data/sys_xact
[kingbase@kingbase sys_xact]$ ll
总用量 256
-rw------- 1 kingbase kingbase 262144 11月 10 18:37 0000
[kingbase@kingbase sys_xact]$ 

只有一个0000，那么下一个XID+1就是0001，* 1048576 

0000 + 1 = 0001 * 1048576 = 1048576 转换为16进制就是 0x000100000 实际后面直接加5个0就行了

-x 0×000100000

multixact-ids设置

-m, –multixact-ids=MXID1,MXID2，设置下一个和最旧的多事务ID
MXID1和MXID2，都可以从DATA/sys_multixact/offsets目录下获得。

MXID1的值,首先找到最大值 +1 再乘以 65536 (0×10000,相当于后面加4个0)作为MXID1的值
MXID2的值,找到最小的值，后面加4个0，作为MXID2的值

/home/kingbase/data/sys_multixact/offsets
[kingbase@kingbase offsets]$ ll
总用量 8
-rw------- 1 kingbase kingbase 8192 11月 10 17:11 0000
[kingbase@kingbase offsets]$ 

MXID1=0000+1 * 65536 = 0x00010000
MXID2=0x00000000

-m 0x00010000,0x00000000

multixact-offset设置下一个事务偏移量

-O, –multixact-offset=OFFSET,通过DATA/sys_multixact/members目录下文件计算
找到最大值，+1，乘以 52352 (0xCC80)

[kingbase@kingbase members]$ pwd
/home/kingbase/data/sys_multixact/members
[kingbase@kingbase members]$ ll
总用量 8
-rw------- 1 kingbase kingbase 8192 11月 10 17:06 0000
[kingbase@kingbase members]$ 

-O 0xCC80

最终命令为：

sys_resetwal -l 000000010000000000000051 -x 0x000100000 -m 0x00010000,0x00000000 -O 0xCC80 -D /home/kingbase/data

[kingbase@kingbase ~]$ sys_resetwal -l 000000010000000000000051 -x 0x000100000 -m 0x00010000,0x00000000 -O 0xCC80 -D /home/kingbase/data
sys_resetwal: error: oldest multitransaction ID (-m) must not be 0

Latest checkpoint's oldestActiveXID:  1022
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1

实际环境不可能是0.调整后处理

sys_resetwal -l 000000010000000000000051 -x 0x000100000 -m 0x00010000,0x00000001 -O 0xCC80 -D /home/kingbase/data

确认输出信息无误

[kingbase@kingbase ~]$ sys_resetwal -l 000000010000000000000051 -x 0x000100000 -m 0x00010000,0x00000001 -O 0xCC80 -D /home/kingbase/data
sys_resetwal: warning: sys_control exists but is broken or wrong version; ignoring it
Guessed sys_control values:

pg_control version number:            1201
Catalog version number:               202208021
Database system identifier:           7164349438480498050
Latest checkpoint's TimeLineID:       1
Latest checkpoint's full_page_writes: off
Latest checkpoint's NextXID:          0:3
Latest checkpoint's NextOID:          12000
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        3
Latest checkpoint's oldestXID's DB:   0
Latest checkpoint's oldestActiveXID:  0
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 0
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1988
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0
database mode:                        0
auth method mode:                     0
Values to be changed:
First log segment after reset:        000000010000000000000051
NextMultiXactId:                      65536
OldestMultiXid:                       1
OldestMulti's DB:                     0
NextMultiOffset:                      52352
NextXID:                              1048576
OldestXID:                            2296015872
OldestXID's DB:                       0

If these values seem acceptable, use -f to force reset.
[kingbase@kingbase ~]$

确认信息没问题后加 -f 参数创建新的sys_control

[kingbase@kingbase global]$ pwd
/home/kingbase/data/global
[kingbase@kingbase global]$ ls -l sys_control 
-rw------- 1 kingbase kingbase 8192 11月 10 19:38 sys_control
[kingbase@kingbase global]$

创建成功

启动数据库

[kingbase@kingbase ~]$ sys_ctl -D data start
waiting for server to start....2022-11-10 19:39:44.626 CST [16022] FATAL:  shared_preload_libraries cannot contains libXXX_parser when pg-mode
2022-11-10 19:39:44.626 CST [16022] LOG:  database system is shut down
 stopped waiting
sys_ctl: could not start server
Examine the log output.

先注释掉shared_preload_libraries参数载启动

[kingbase@kingbase data]$ vi kingbase.conf 
[kingbase@kingbase data]$ cd ..
[kingbase@kingbase ~]$ sys_ctl -D data start
waiting for server to start....2022-11-10 19:42:41.220 CST [16253] LOG:  starting KingbaseES V008R006C006B0021 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
2022-11-10 19:42:41.220 CST [16253] LOG:  listening on IPv4 address "0.0.0.0", port 54321
2022-11-10 19:42:41.220 CST [16253] LOG:  listening on IPv6 address "::", port 54321
2022-11-10 19:42:41.224 CST [16253] LOG:  listening on Unix socket "/tmp/.s.KINGBASE.54321"
2022-11-10 19:42:41.249 CST [16253] LOG:  redirecting log output to logging collector process
2022-11-10 19:42:41.249 CST [16253] HINT:  Future log output will appear in directory "sys_log".
 done
server started
[kingbase@kingbase ~]$ 
[kingbase@kingbase ~]$ ksql -Usystem -dtest
ksql (V8.0)
Type "help" for help.

WARNING:License file will expire in 15 days.

test=# select * from t;
 bl |    var    
----+-----------
 1  | 12313
 0  | 12231313
 0  | 122se1
 1  | 1sdas2se1
(4 rows)

test=#