除了上一篇介绍的常用的 onstat 监控命令，SinoDB数据库还有其他一些常用命令，本文是对这些常用命令的汇总介绍。其中：
-
oninit 实用程序主要用于启动数据库；
-
onmode实用程序用于更改数据库服务器操作模式，并在共享内存、会话、事务、参数和段上执行各种其他操作。
-
oncheck实用程序检查指定的磁盘结构的不一致性、修复不一致索引结构，以及显示有关磁盘结构的信息。oncheck实用程序检查系统目录表时，在其上放置共享锁。在执行修复选项时，它在表上放置互斥锁，所以要谨慎执行oncheck命令。
1. 常用命令
2. 具体说明
oninit -v
启动数据库到在线模式
[informix@vm84145 ~]$ oninit -v
Reading configuration file '/home/informix/sinodb/etc/onconfig.sinodb'...succeeded
Creating /INFORMIXTMP/.infxdirs...succeeded
Allocating and attaching to shared memory...succeeded
Creating resident pool 22166 kbytes...succeeded
Creating infos file "/home/informix/sinodb/etc/.infos.sinodb"...succeeded
Linking conf file "/home/informix/sinodb/etc/.conf.sinodb"...succeeded
Initializing rhead structure...rhlock_t 32768 (1024K)... rlock_t (13281K)... Writing to infos file...succeeded
Initialization of Encryption...succeeded
Initializing ASF...succeeded
Initializing Dictionary Cache and SPL Routine Cache...succeeded
Initializing encryption-at-rest if necessary...succeeded
Bringing up ADM VP...succeeded
Creating VP classes...succeeded
Forking main_loop thread...succeeded
Initializing DR structures...succeeded
Forking 1 'soctcp' listener threads...succeeded
Starting tracing...succeeded
Initializing 32 flushers...succeeded
Initializing SDS Server network connections...succeeded
Initializing log/checkpoint information...succeeded
Initializing dbspaces...succeeded
Opening primary chunks...succeeded
Validating chunks...succeeded
Initialize Async Log Flusher...succeeded
Starting B-tree Scanner...succeeded
Init ReadAhead Daemon...succeeded
Init DB Util Daemon...succeeded
Initializing DBSPACETEMP list...succeeded
Init Auto Tuning Daemon...succeeded
Checking database partition index...succeeded
Initializing dataskip structure...succeeded
Checking for temporary tables to drop...succeeded
Updating Global Row Counter...succeeded
Forking onmode_mon thread...succeeded
Creating periodic thread...succeeded
Creating VP cache drain thread...succeeded
Creating limits manager thread...succeeded
Starting scheduling system...succeeded
Verbose output complete: mode = 5
[informix@vm84145 ~]$
onmode -j
切换到单用户模式，只允许管理员进行操作，不允许普通用户连接数据库。
[informix@vm84145 ~]$ onstat -
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 00:06:49 -- 2374324 Kbytes
[informix@vm84145 ~]$ onmode -j
This will change mode to single user. Only DBSA/informix can connect
in this mode.
Do you wish to continue (y/n)? y
All threads which are not owned by DBSA/informix will be killed.
Do you wish to continue (y/n)? y
[informix@vm84145 ~]$ onstat -
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- Single-User -- Up 00:07:04 -- 2374324 Kbytes
[informix@vm84145 ~]$
onmode -m
当数据库处于单用户模式或静态模式时，可以通过这个命令，将数据库切换到在线模式，并对外提供数据库服务。
[informix@vm84145 ~]$ onstat -
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- Single-User -- Up 17:45:43 -- 2374324 Kbytes
[informix@vm84145 ~]$ onmode -m
[informix@vm84145 ~]$ onstat -
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 17:46:02 -- 2374324 Kbytes
[informix@vm84145 ~]$
onmode -k
切换数据库到离线模式。
[informix@vm84145 ~]$ onstat -
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 17:46:02 -- 2374324 Kbytes
[informix@vm84145 ~]$ onmode -k
This will take Sinoregal SinoDB Dynamic Server OFF-LINE -
Do you wish to continue (y/n)? y
There are 0 user threads that will be killed.
Do you wish to continue (y/n)? y
[informix@vm84145 ~]$ onstat -
shared memory not initialized for INFORMIXSERVER 'sinodb'
onmode -s /onmode -u
切换数据库到静态模式。当管理员在维护时，不希望有任何的SQL连接，对数据库进行操作，可以将数据库切换到静态模式。
- onmode -s
s：优雅地切换到静态模式。当有用户连接到数据库时，会等用户断开连接后再进行模式切换
[informix@vm84145 ~]$ onstat -
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 00:00:19 -- 2374324 Kbytes
[informix@vm84145 ~]$ onmode -s
This will perform a GRACEFUL SHUTDOWN -
Do you wish to continue (y/n)? y
[informix@vm84145 ~]$ onstat -
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- Quiescent -- Up 00:00:48 -- 2374324 Kbytes
[informix@vm84145 ~]$
- onmode -u
u：立即切换到静态模式。当有用户连接到数据库时，会直接杀掉用户连接，并进行模式切换。
[informix@vm84145 ~]$ onstat -
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 00:01:15 -- 2374324 Kbytes
[informix@vm84145 ~]$ onmode -u
This will perform an IMMEDIATE SHUTDOWN -
Do you wish to continue (y/n)? y
There are 0 user threads that will be killed.
Do you wish to continue (y/n)? y
[informix@vm84145 ~]$ onstat -
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- Quiescent -- Up 00:02:17 -- 2374324 Kbytes
[informix@vm84145 ~]$
onmode -l
强制数据库做一次逻辑日志切换。切换后，新的逻辑日志将写入到下一个逻辑日志文件中。
如下所示：当前的逻辑日志被标记为C，将逻辑日志切换到下一个后，当前的逻辑日志（uniqid）由14变为15。
[informix@vm84145 ~]$ onstat -l
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- Single-User -- Up 00:09:41 -- 2374324 Kbytes
Physical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-1 0 1024 37 3 12.33
phybegin physize phypos phyused %used
3:53 1023945 84192 16 0.00
Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io
L-1 0 512 437 89 52 4.9 1.7
Subsystem numrecs Log Space used
OLDRSAM 435 119912
HA 2 88
address number flags uniqid begin size used %used
475dffa8 7 F------ 0 2:53 102390 0 0.00
475fbf88 8 A------ 0 2:102443 102390 0 0.00
4886fd90 9 A------ 0 2:204833 102390 0 0.00
4886fdf8 10 A------ 0 2:307223 102390 0 0.00
4886fe60 11 U-B---- 11 2:409613 102390 51 0.05
4886fec8 12 U-B---- 12 2:512003 102390 10 0.01
4886ff30 13 U------ 13 2:614393 102390 40589 39.64
4886ff98 14 U---C-L 14 2:716783 102390 7 0.01
473abf68 15 A------ 0 2:819173 102390 0 0.00
47481f40 16 A------ 0 2:921563 102390 0 0.00
10 active, 10 total
[informix@vm84145 ~]$ onmode -l
[informix@vm84145 ~]$ onstat -l
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- Single-User -- Up 00:10:14 -- 2374324 Kbytes
Physical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-1 16 1024 86 5 17.20
phybegin physize phypos phyused %used
3:53 1023945 84192 32 0.00
Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io
L-3 0 512 440 91 54 4.8 1.7
Subsystem numrecs Log Space used
OLDRSAM 437 119996
HA 3 132
address number flags uniqid begin size used %used
475dffa8 7 F------ 0 2:53 102390 0 0.00
475fbf88 8 A------ 0 2:102443 102390 0 0.00
4886fd90 9 A------ 0 2:204833 102390 0 0.00
4886fdf8 10 A------ 0 2:307223 102390 0 0.00
4886fe60 11 U-B---- 11 2:409613 102390 51 0.05
4886fec8 12 U-B---- 12 2:512003 102390 10 0.01
4886ff30 13 U------ 13 2:614393 102390 40589 39.64
4886ff98 14 U-----L 14 2:716783 102390 8 0.01
473abf68 15 U---C-- 15 2:819173 102390 4 0.00
47481f40 16 A------ 0 2:921563 102390 0 0.00
10 active, 10 total
[informix@vm84145 ~]$
onmode -c
强制执行检查点，会将缓冲区中的“脏”块，写入数据文件。
[informix@vm84145 ~]$ onstat -l
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 14 days 21:44:21 -- 2757528 Kbytes
Physical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-1 39 1024 187723 2708 69.32
phybegin physize phypos phyused %used
3:53 1023945 45925 39 0.00
Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io
L-2 0 512 2255100 274783 222228 8.2 1.2
Subsystem numrecs Log Space used
OLDRSAM 2253063 220219132
SBLOB 28 2140
HA 1986 87384
DDL 23 8004
address number flags uniqid begin size used %used
47635fa8 7 U-B---L 117 2:53 102390 53713 52.46
47651f88 8 U---C-- 118 2:102443 102390 11 0.01
488bfd90 9 F------ 0 2:204833 102390 0 0.00
488bfdf8 10 F------ 0 2:307223 102390 0 0.00
488bfe60 11 F------ 0 2:409613 102390 0 0.00
488bfec8 12 U-B---- 112 2:512003 102390 8910 8.70
488bff30 13 U-B---- 113 2:614393 102390 102390 100.00
488bff98 14 U-B---- 114 2:716783 102390 25185 24.60
473abf68 15 U-B---- 115 2:819173 102390 102390 100.00
47481f40 16 U-B---- 116 2:921563 102390 102390 100.00
10 active, 10 total
[informix@vm84145 ~]$ onmode -c
[informix@vm84145 ~]$ onstat -l
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 14 days 21:44:37 -- 2757528 Kbytes
Physical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-1 0 1024 187784 2710 69.29
phybegin physize phypos phyused %used
3:53 1023945 45986 22 0.00
Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io
L-1 0 512 2255125 274789 222234 8.2 1.2
Subsystem numrecs Log Space used
OLDRSAM 2253087 220221520
SBLOB 28 2140
HA 1987 87428
DDL 23 8004
address number flags uniqid begin size used %used
47635fa8 7 U-B---- 117 2:53 102390 53713 52.46
47651f88 8 U---C-L 118 2:102443 102390 13 0.01
488bfd90 9 F------ 0 2:204833 102390 0 0.00
488bfdf8 10 F------ 0 2:307223 102390 0 0.00
488bfe60 11 F------ 0 2:409613 102390 0 0.00
488bfec8 12 U-B---- 112 2:512003 102390 8910 8.70
488bff30 13 U-B---- 113 2:614393 102390 102390 100.00
488bff98 14 U-B---- 114 2:716783 102390 25185 24.60
473abf68 15 U-B---- 115 2:819173 102390 102390 100.00
47481f40 16 U-B---- 116 2:921563 102390 102390 100.00
10 active, 10 total
onmode -wm
只更新内存中的参数值，不更新onconfig配置文件中的参数值，只有部分参数支持动态更改。
[informix@vm84145 ~]$ onstat -c | grep LISTEN_TIMEOUT
# LISTEN_TIMEOUT - The number of seconds that the server
LISTEN_TIMEOUT 60
[informix@vm84145 ~]$ onstat -g cfg | grep LISTEN_TIMEOUT
LISTEN_TIMEOUT 60
[informix@vm84145 ~]$ onmode -wm LISTEN_TIMEOUT=50
Value of LISTEN_TIMEOUT has been changed to 50 seconds.
[informix@vm84145 ~]$ onstat -c | grep LISTEN_TIMEOUT
# LISTEN_TIMEOUT - The number of seconds that the server
LISTEN_TIMEOUT 60
[informix@vm84145 ~]$ onstat -g cfg | grep LISTEN_TIMEOUT
LISTEN_TIMEOUT 50
[informix@vm84145 ~]$
onmode -wf
更新内存与onconfig 文件中指定配置参数的值，只有部分参数支持动态更改。
[informix@vm84145 ~]$ onstat -g cfg | grep LISTEN_TIMEOUT
LISTEN_TIMEOUT 50
[informix@vm84145 ~]$ onstat -c | grep LISTEN_TIMEOUT
# LISTEN_TIMEOUT - The number of seconds that the server
LISTEN_TIMEOUT 60
[informix@vm84145 ~]$ onmode -wf LISTEN_TIMEOUT=70
Value of LISTEN_TIMEOUT has been changed to 70 seconds.
[informix@vm84145 ~]$ onstat -c | grep LISTEN_TIMEOUT
# LISTEN_TIMEOUT - The number of seconds that the server
LISTEN_TIMEOUT 70
[informix@vm84145 ~]$ onstat -g cfg | grep LISTEN_TIMEOUT
LISTEN_TIMEOUT 70
[informix@vm84145 ~]$
onmode -z
终止数据库服务器会话。
语法：onmode -z <session_id>
[informix@vm84145 ~]$ dbaccess - -
> database testdb;
Database selected.
> !onstat -g ses
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 01:35:57 -- 2374324 Kbytes
session #RSAM total used dynamic
id user tty pid hostname threads memory memory explain
60 informix - 0 - 0 16384 12560 off
59 informix 0 20442 vm84145 1 73728 64776 off
56 informix - 0 - 0 16384 14176 off
53 informix - 0 - 1 417792 367968 off
52 informix - 0 - 1 434176 370648 off
51 informix - 0 - 1 622592 484920 off
50 informix - 0 - 1 73728 67280 off
3 informix - 0 - 0 16384 12560 off
2 informix - 0 - 0 16384 12560 off
> select * from t_user;
f_userid f_username f_age
2 Jack 21
1 row(s) retrieved.
> !onmode -z 59
> select * from t_user;
25582: Network connection is broken.
Error in line 1
Near character position 1
>
在dbaccess中执行SQL时，由于Session被杀死，执行报错。
oncheck -cc
检查有关数据库表、列、索引、视图、约束、存储过程和权限等的信息的系统目录表。
语法：
oncheck -cc database
演示：
[informix@vm84145 ~]$ oncheck -cc testdb
Validating database testdb
Validating systables for database testdb
Validating syscolumns for database testdb
Validating sysindices for database testdb
Validating systabauth for database testdb
Validating syscolauth for database testdb
Validating sysdepend for database testdb
Validating syssyntable for database testdb
Validating sysviews for database testdb
Validating sysconstraints for database testdb
Validating sysams for database testdb
如果未在 oncheck -cc 中指定数据库名称，那么该命令将对所有数据库检测上面所列的系统目录表。
oncheck -cr
检查保留页。-cr 选项按以下方式检查每个根数据库空间保留页：
• 以 PAGE_CONFIG 保留页验证 ONCONFIG 文件的内容。
• 确保所有块可以打开、块不重叠，且块大小是正确的。
[informix@vm84145 ~]$ oncheck -cr
Validating Sinoregal SinoDB Dynamic Server reserved pages
Validating PAGE_PZERO...
Validating PAGE_CONFIG...
ONCONFIG config file error on element SBSPACENAME.
Value in reserved page:
Value in config file: sbdbs1
ONCONFIG config file error on element SYSSBSPACENAME.
Value in reserved page:
Value in config file: sbdbs1
Validating PAGE_1CKPT & PAGE_2CKPT...
Using check point page PAGE_1CKPT.
Validating PAGE_1DBSP & PAGE_2DBSP...
Using DBspace page PAGE_2DBSP.
Validating PAGE_1PCHUNK & PAGE_2PCHUNK...
Using primary chunk page PAGE_1PCHUNK.
Validating PAGE_1ARCH & PAGE_2ARCH...
Using archive page PAGE_1ARCH.
oncheck -pe
检查每个空闲块列表，以及相应的可用空间和每个表空间扩展数据块。
[informix@vm84145 ~]$ oncheck -pe |more
DBspace Usage Report: rootdbs Owner: informix Created: 02/14/2022
Chunk Pathname Pagesize(k) Size(p) Used(p) Free(p)
1 /home/informix/dbs/rootdbs 2 1024000 19905 1004095
Description Offset(p) Size(p)
------------------------------------------------------------- -------- --------
RESERVED PAGES 0 12
CHUNK FREELIST PAGE 12 1
rootdbs:'informix'.TBLSpace 13 250
sysadmin:'informix'.mon_prof 263 16
sysadmin:'informix'.mon_prof_idx2 279 4
sysadmin:'informix'.mon_prof_idx1 283 6
sysadmin:'informix'.aus_cmd_info 289 8
sysadmin:'informix'.aus_cmd_info_index1 297 4
sysadmin:'informix'.aus_cmd_info_index2 301 6
sysadmin:'informix'.aus_command 307 8
sysadmin:'informix'.mon_memory_system 315 16
sysadmin:'informix'.aus_command_ix1 331 16
......
testdb5:'informix'.sysprocbody 1535 128
testdb5:'informix'.sysprocedures 1663 128
testdb5:'informix'.sysdistrib 1791 248
FREE 2039 1021961
Total Used: 2039
Total Free: 1021961
BLOBspace Usage Report: bindbs2 Owner: informix Created: 04/27/2023
Chunk Pathname Size Used Free
14 /home/informix/dbs/bindbs2 8 512000 509 511491
Disk usage for Chunk 14 Total Pages
-------------------------------------------------------------------------------
OVERHEAD 509
FREE 511491
BLOBspace Usage Report: bindbs3 Owner: informix Created: 04/27/2023
Chunk Pathname Size Used Free
15 /home/informix/dbs/bindbs3 4 512000 1015 510985
Disk usage for Chunk 15 Total Pages
-------------------------------------------------------------------------------
OVERHEAD 1015
FREE 510985
DBspace Usage Report: datadbs6 Owner: informix Created: 05/22/2023
Chunk Pathname Pagesize(k) Size(p) Used(p) Free(p)
16 /home/informix/dbs/datadbs6-1 16 64000 57 63943
Description Offset(p) Size(p)
------------------------------------------------------------- -------- --------
RESERVED PAGES 0 2
CHUNK FREELIST PAGE 2 1
datadbs6:'informix'.TBLSpace 3 50
testdb:'informix'.t15 53 4
FREE 57 63943
Total Used: 57
Total Free: 63943
oncheck -pt
打印指定数据库和名称的表或分段的表空间报告。如未指定表，该选项显示数据库中所有表信息。该报告包含一般分配信息，包括最大行大小、键数量、扩展数据块数量和其大小、每个扩展数据块所分配和使用的页、当前序列值，以及表的创建日期。
[informix@vm84145 ~]$ oncheck -pt testdb:fy_project
TBLspace Report for testdb:informix.fy_project
Physical Address 9:5172
Creation date 02/22/2023 09:53:24
TBLspace Flags 902 Row Locking
TBLspace contains VARCHARS
TBLspace use 4 bit bit-maps
Maximum row size 13369
Number of special columns 18
Number of keys 0
Number of extents 1
Current serial value 1
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 8
Next extent size 64
Number of pages allocated 64
Number of pages used 52
Number of data pages 35
Number of rows 35
Partition partnum 9437422
Partition lockid 9437422
Extents
Logical Page Physical Page Size Physical Pages
0 9:100821 64 64
Index idx_project fragment partition datadbs1 in DBspace datadbs1
Physical Address 9:176300
Creation date 04/21/2023 11:20:19
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 13369
Number of special columns 0
Number of keys 1
Number of extents 1
Current serial value 1
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 4
Next extent size 8
Number of pages allocated 8
Number of pages used 5
Number of data pages 0
Number of rows 0
Partition partnum 9437989
Partition lockid 9437422
Extents
Logical Page Physical Page Size Physical Pages
0 9:176063 8 8