备注：表空间初步的认识，理解不正确的地方，一起交流学习。
1.基本概念介绍
1）YashanDB默认表空间
- 永久表空间，包括SYSTEM表空间、SYSAUX表空间和USERS表空间
- 临时表空间，要用于临时表的段分配以及与临时表相关undo空间分配，并存储临时表数据和临时表相关的undo。
- UNDO表空间，为内置表空间，无法正常删除重建。
- SWAP表空间，简单来说，就是存放临时数据的表空间，比如order by，hash join，统计信息收集等缓存计算的中间结果，如果虚拟内存VM_BUFFER_SIZE不足时，交换到SWAP表空间来释放内存，必要时再将从SWAP表空间换入。参数DEFAULT_SWAP_TABLESPACE用于控制当前使用SWAP表空间
- USERS_AIM表空间（仅存在于分布式部署）
2）redo日志条目组成
redo日志由redo head + batch组成，而batch有group组成，group由atomic组成。可以通过dump redo日志文件查看
3）CKPT -- 检查点线程
默认每隔5分钟（300秒）触发一次，会将日志恢复点（RCY_POINT）写入控制文件
CHECKPOINT_TIMEOUT，单位秒，检查点时间间隔，表示每过一段时间，执行一次增量检查点
CHECKPOINT_INTERVAL，检查点REDO大小阈值，表示当检查点覆盖的REDO（重启回放所需的REDO）的总大小超过该值时，将执行一次增量检查点。
SQL> show parameter checkpoint
NAME VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
CHECKPOINT_INTERVAL 100000
CHECKPOINT_TIMEOUT 300
2 rows fetched.
2024-04-30 10:08:02.230 5707 [DEBUG] [CKPT] update instance 0 rcy_begin 0-5-769-12339
2024-04-30 10:13:02.234 5707 [DEBUG] [CKPT] update instance 0 rcy_begin 0-5-769-12339
rcy_begin: 0-6-212-12372, 格式为{rst}-{asn}-{blockid}-{lfn}
- rst：reset id，每次failover后，数据库新产生的redo文件的reset id会加1。
- asn：archive sequence number（即归档序列号），每产生一个redo，ASN会加1，每个redo的ASN不相同。
- blockid：redo文件内页面所在ID，页面的偏移量为block id * block size。
- lfn：log flush number，日志序列号，每次redo刷盘，LFN加1。
4）LSN
log sequence number，日志序列号
2.表空间创建过程简述
观察数据库日志及redo日志可以看到，创建表空间过程出现2次ckpt，分别是create tablespace和add datafile
第一次CKPT，创建表空间信息
redo 日志：
---------------------------
batch: lfn 12341, block id 2, size 182, space size 4096, part count 1, scn 559531139802017792, scn to time 2024-04-30 09:38:02.178227
reset id 0, asn 6, last lsn 40692, has nologging 0
group: lsn 40692, size 98, handler's id 26, encrypt: 0
atomic 0, offset 16
CREATE_SPACE 0-0-0 id: 6, Name: YYT01, extentBlocks: 8, type: 2, autoAlloc: 1, aim: 0, encrypted: 0, temp: 0
数据库日志：
---------------------------
2024-04-30 10:13:39.175 7235 [INFO] [TABLESPACE] try to update instance 0 's space change lsn from 38858 to 40692
2024-04-30 10:13:39.176 7235 [DEBUG] [TABLESPACE] try to update instance 0 's space change lsn from 38858 to 40692
2024-04-30 10:13:39.196 5707 [DEBUG] [CKPT] update instance 0 rcy_begin 0-6-3-12342
第二次CKPT，增加数据文件
redo 日志：
batch: lfn 12342, block id 3, size 390, space size 4096, part count 1, scn 559531139802017792, scn to time 2024-04-30 09:38:02.178227
reset id 0, asn 6, last lsn 40693, has nologging 0
group: lsn 40693, size 306, handler's id 26, encrypt: 0
atomic 0, offset 16
ADD_DATAFILE 0-0-0 id: 6, Name: /yashan/yasdb_data/db-1-1/dbfiles/yyt01, space id: 6, internal id: 0, ident: -631542418, blockSize: 8192, blockCount: 12800, autoExtend: 0, nextBlocks: 0, maxBlocks: 0
数据库日志：
---------------------------
2024-04-30 10:13:39.641 7235 [INFO] [TABLESPACE] try to update instance 0 's space change lsn from 40692 to 40693
2024-04-30 10:13:39.643 7235 [DEBUG] [TABLESPACE] try to update instance 0 's space change lsn from 40692 to 40693
2024-04-30 10:13:39.652 7235 [INFO] [TABLESPACE] succeed to create datafile /yashan/yasdb_data/db-1-1/dbfiles/yyt01
2024-04-30 10:13:39.652 7235 [INFO] [TABLESPACE] succeed to create tablespace YYT01
2024-04-30 10:13:39.653 5707 [DEBUG] [CKPT] update instance 0 rcy_begin 0-6-4-12343
下面是创建过程的数据库操作及日志信息输出：
SQL> @rdo
ID NAME BLOCK_SIZE BLOCK_COUNT USED_BLOCKS SEQUENCE# STATUS
------------ -------------------------------------------------- ------------ --------------------- --------------------- ------------ ---------
0 /yashan/yasdb_data/db-1-1/dbfiles/redo1 4096 32768 769 5 CURRENT
1 /yashan/yasdb_data/db-1-1/dbfiles/redo2 4096 32768 568 2 INACTIVE
2 /yashan/yasdb_data/db-1-1/dbfiles/redo3 4096 32768 11535 3 INACTIVE
3 /yashan/yasdb_data/db-1-1/dbfiles/redo4 4096 32768 1068 4 INACTIVE
2024-04-30 10:13:02.234 5707 [DEBUG] [CKPT] update instance 0 rcy_begin 0-5-769-12339
2024-04-30 10:13:38.410 7235 [INFO] [REDO] instance: 0, switch redo target asn: 4294967295, current asn: 5
2024-04-30 10:13:38.412 7235 [INFO] [REDO] instance 0, switch redo file, type 1, point 0-5-769-12339
2024-04-30 10:13:38.418 7235 [INFO] [DB] update current redo /yashan/yasdb_data/db-1-1/dbfiles/redo2 version 0.2.59 to database version 0.2.59
2024-04-30 10:13:38.423 7235 [INFO] [REDO] switch redo file, new asn 6
2024-04-30 10:13:38.747 5720 [DEBUG] load pack from redo /yashan/yasdb_data/db-1-1/dbfiles/redo1
2024-04-30 10:13:38.762 5720 [DEBUG] load pack from redo /yashan/yasdb_data/db-1-1/dbfiles/redo1
2024-04-30 10:13:38.762 5720 [INFO] [ARCH] instance: 0, itemCounts: 4, add archive file filename /yashan/yasdb_data/db-1-1/archive/arch_0_5.ARC, asn: 5, ctrlId: 4, used: 1
2024-04-30 10:13:38.769 5720 [INFO] [ARCH] add new archive file /yashan/yasdb_data/db-1-1/archive/arch_0_5.ARC
2024-04-30 10:13:38.849 5812 [INFO] [REDO] instance 0, switch redo file, type 8, point 0-6-1-12340
2024-04-30 10:13:39.175 7235 [INFO] [TABLESPACE] try to update instance 0 's space change lsn from 38858 to 40692
2024-04-30 10:13:39.176 7235 [DEBUG] [TABLESPACE] try to update instance 0 's space change lsn from 38858 to 40692
2024-04-30 10:13:39.196 5707 [DEBUG] [CKPT] update instance 0 rcy_begin 0-6-3-12342
2024-04-30 10:13:39.641 7235 [INFO] [TABLESPACE] try to update instance 0 's space change lsn from 40692 to 40693
2024-04-30 10:13:39.643 7235 [DEBUG] [TABLESPACE] try to update instance 0 's space change lsn from 40692 to 40693
2024-04-30 10:13:39.652 7235 [INFO] [TABLESPACE] succeed to create datafile /yashan/yasdb_data/db-1-1/dbfiles/yyt01
2024-04-30 10:13:39.652 7235 [INFO] [TABLESPACE] succeed to create tablespace YYT01
2024-04-30 10:13:39.653 5707 [DEBUG] [CKPT] update instance 0 rcy_begin 0-6-4-12343
2024-04-30 10:13:48.355 6611 [DEBUG] load pack from redo /yashan/yasdb_data/db-1-1/dbfiles/redo2SQL> @rdo
ID NAME BLOCK_SIZE BLOCK_COUNT USED_BLOCKS SEQUENCE# STATUS
------------ -------------------------------------------------- ------------ --------------------- --------------------- ------------ ---------
0 /yashan/yasdb_data/db-1-1/dbfiles/redo1 4096 32768 770 5 INACTIVE
1 /yashan/yasdb_data/db-1-1/dbfiles/redo2 4096 32768 4 6 CURRENT
2 /yashan/yasdb_data/db-1-1/dbfiles/redo3 4096 32768 11535 3 INACTIVE
3 /yashan/yasdb_data/db-1-1/dbfiles/redo4 4096 32768 1068 4 INACTIVE dump redo日志信息
*** 2024-04-30 10:13:48.356
dump type : logfile blocks
size : 16384bytes
redo head: rstid 0, asn 6, first lfn 12340, next lfn 12340, block size 4096, total blocks 32768, used blocks 1, first scn 559531139802017792, last scn 559531139802017792
checksum 3859570451, initTime 1714443218413148, complete time 1970-01-01 00:00:00.000000, create time 2024-04-24 01:44:53.506225, database id 4035646550, ident 913320364, slot 1
batch: lfn 12340, block id 1, size 126, space size 4096, part count 1, scn 559531139802017792, scn to time 2024-04-30 09:38:02.178227
reset id 0, asn 6, last lsn 40691, has nologging 0
group: lsn 40691, size 42, handler's id 1, encrypt: 0
atomic 0, offset 16
SWITCH_REDOFILE 0-0-0 switch redo file, type: 8, flush point: 0-6-1-12340
batch: lfn 12341, block id 2, size 182, space size 4096, part count 1, scn 559531139802017792, scn to time 2024-04-30 09:38:02.178227
reset id 0, asn 6, last lsn 40692, has nologging 0
group: lsn 40692, size 98, handler's id 26, encrypt: 0
atomic 0, offset 16
CREATE_SPACE 0-0-0 id: 6, Name: YYT01, extentBlocks: 8, type: 2, autoAlloc: 1, aim: 0, encrypted: 0, temp: 0
batch: lfn 12342, block id 3, size 390, space size 4096, part count 1, scn 559531139802017792, scn to time 2024-04-30 09:38:02.178227
reset id 0, asn 6, last lsn 40693, has nologging 0
group: lsn 40693, size 306, handler's id 26, encrypt: 0
atomic 0, offset 16
ADD_DATAFILE 0-0-0 id: 6, Name: /yashan/yasdb_data/db-1-1/dbfiles/yyt01, space id: 6, internal id: 0, ident: -631542418, blockSize: 8192, blockCount: 12800, autoExtend: 0, nextBlocks: 0, maxBlocks: 0
3.表空间删除过程简述
删除表空间过程也是出现2次ckpt，分别是drop datafile 和 drop tablespace。
第一次CKPT，删除数据文件信息
先分配undo空间，然后删除数据文件
redo 日志：
---------------------------
redo head: rstid 0, asn 10, first lfn 12429, next lfn 12429, block size 4096, total blocks 32768, used blocks 1, first scn 559634404997152768, last scn 559634404997152768
checksum 2461189390, initTime 1714469088219963, complete time 1970-01-01 00:00:00.000000, create time 2024-04-24 01:44:53.506225, database id 4035646550, ident 913320364, slot 1
batch: lfn 12429, block id 1, size 126, space size 4096, part count 1, scn 559634404997152768, scn to time 2024-04-30 16:38:13.407508
reset id 0, asn 10, last lsn 40795, has nologging 0
group: lsn 40795, size 42, handler's id 1, encrypt: 0
atomic 0, offset 16
SWITCH_REDOFILE 0-0-0 switch redo file, type: 8, flush point: 0-10-1-12429
batch: lfn 12430, block id 2, size 4510, space size 8192, part count 2, scn 559634404997152768, scn to time 2024-04-30 16:38:13.407508
reset id 0, asn 10, last lsn 40797, has nologging 0
group: lsn 40796, size 4402, handler's id 3, encrypt: 0
atomic 0, offset 16
ATTACH_BLOCK 5-0-2 block id: spcBid(0, 2), space 5, flags 129
DF_CHANGE_MAP 5-0-2 bitNumber: 28560, size: 1, isSet: 0
DETACH_BLOCK 5-0-2 changeNum: 34014, changed
ATTACH_BLOCK 5-0-2 block id: spcBid(0, 2), space 5, flags 129
DF_CHANGE_MAP 5-0-2 bitNumber: 28561, size: 1, isSet: 0
DETACH_BLOCK 5-0-2 changeNum: 34015, changed
ATTACH_BLOCK 5-0-128 block id: spcBid(0, 128), space 5, flags 4
UNDO_MODIFY_SEGMENT 5-0-128 block list: count 2 head 0-28690 tail 0-28691, ufbCount: 108, ufbFirst: 0-28692
DETACH_BLOCK 5-0-128 changeNum: 767, changed
atomic 1, offset: 122
ATTACH_BLOCK 5-0-2 block id: spcBid(0, 2), space 5, flags 129
DF_CHANGE_MAP 5-0-2 bitNumber: 28562, size: 1, isSet: 0
DETACH_BLOCK 5-0-2 changeNum: 34016, changed
ATTACH_BLOCK 5-0-128 block id: spcBid(0, 128), space 5, flags 4
UNDO_MODIFY_SEGMENT 5-0-128 block list: count 1 head 0-28691 tail 0-28691, ufbCount: 108, ufbFirst: 0-28692
DETACH_BLOCK 5-0-128 changeNum: 768, changed
atomic 2, offset: 196
ATTACH_BLOCK 5-0-2 block id: spcBid(0, 2), space 5, flags 129
DF_CHANGE_MAP 5-0-2 bitNumber: 7186, size: 1, isSet: 0
DETACH_BLOCK 5-0-2 changeNum: 34017, changed
ATTACH_BLOCK 5-0-2 block id: spcBid(0, 2), space 5, flags 129
DF_CHANGE_MAP 5-0-2 bitNumber: 7187, size: 1, isSet: 0
DETACH_BLOCK 5-0-2 changeNum: 34018, changed
ATTACH_BLOCK 5-0-225 block id: spcBid(0, 225), space 5, flags 4
UNDO_MODIFY_SEGMENT 5-0-225 block list: count 2 head 0-7316 tail 0-7317, ufbCount: 1002, ufbFirst: 0-7318
DETACH_BLOCK 5-0-225 changeNum: 901, changed
atomic 3, offset: 302
ATTACH_BLOCK 5-0-2 block id: spcBid(0, 2), space 5, flags 129
DF_CHANGE_MAP 5-0-2 bitNumber: 7188, size: 1, isSet: 0
DETACH_BLOCK 5-0-2 changeNum: 34019, changed
ATTACH_BLOCK 5-0-225 block id: spcBid(0, 225), space 5, flags 4
UNDO_MODIFY_SEGMENT 5-0-225 block list: count 1 head 0-7317 tail 0-7317, ufbCount: 1002, ufbFirst: 0-7318
DETACH_BLOCK 5-0-225 changeNum: 902, changed
atomic 4, offset: 376
ATTACH_BLOCK 5-0-2 block id: spcBid(0, 2), space 5, flags 129
DF_CHANGE_MAP 5-0-2 bitNumber: 29227, size: 1, isSet: 0
DETACH_BLOCK 5-0-2 changeNum: 34020, changed
ATTACH_BLOCK 5-0-2 block id: spcBid(0, 2), space 5, flags 129
DF_CHANGE_MAP 5-0-2 bitNumber: 29228, size: 1, isSet: 0
DETACH_BLOCK 5-0-2 changeNum: 34021, changed
ATTACH_BLOCK 5-0-2 block id: spcBid(0, 2), space 5, flags 129
DF_CHANGE_MAP 5-0-2 bitNumber: 29229, size: 1, isSet: 0
DETACH_BLOCK 5-0-2 changeNum: 34022, changed
ATTACH_BLOCK 5-0-2 block id: spcBid(0, 2), space 5, flags 129
DF_CHANGE_MAP 5-0-2 bitNumber: 29230, size: 1, isSet: 0
DETACH_BLOCK 5-0-2 changeNum: 34023, changed
ATTACH_BLOCK 5-0-2 block id: spcBid(0, 2), space 5, flags 129
DF_CHANGE_MAP 5-0-2 bitNumber: 29231, size: 1, isSet: 0
DETACH_BLOCK 5-0-2 changeNum: 34024, changed
ATTACH_BLOCK 5-0-5754 block id: spcBid(0, 5754), space 5, flags 4
UNDO_MODIFY_SEGMENT 5-0-5754 block list: count 4 head 0-29360 tail 0-29363, ufbCount: 332, ufbFirst: 0-29364
DETACH_BLOCK 5-0-5754 changeNum: 951, changed
atomic 5, offset: 578
ATTACH_BLOCK 5-0-2 block id: spcBid(0, 2), space 5, flags 129
DF_CHANGE_MAP 5-0-2 bitNumber: 29232, size: 1, isSet: 0
DETACH_BLOCK 5-0-2 changeNum: 34025, changed
ATTACH_BLOCK 5-0-2 block id: spcBid(0, 2), space 5, flags 129
DF_CHANGE_MAP 5-0-2 bitNumber: 29233, size: 1, isSet: 0
DETACH_BLOCK 5-0-2 changeNum: 34026, changed
ATTACH_BLOCK 5-0-5754 block id: spcBid(0, 5754), space 5, flags 4
UNDO_MODIFY_SEGMENT 5-0-5754 block list: count 2 head 0-29362 tail 0-29363, ufbCount: 332, ufbFirst: 0-29364
DETACH_BLOCK 5-0-5754 changeNum: 952, changed
atomic 6, offset: 684
ATTACH_BLOCK 5-0-2 block id: spcBid(0, 2), space 5, flags 129
DF_CHANGE_MAP 5-0-2 bitNumber: 29234, size: 1, isSet: 0
DETACH_BLOCK 5-0-2 changeNum: 34027, changed
ATTACH_BLOCK 5-0-5754 block id: spcBid(0, 5754), space 5, flags 4
UNDO_MODIFY_SEGMENT 5-0-5754 block list: count 1 head 0-29363 tail 0-29363, ufbCount: 332, ufbFirst: 0-29364
DETACH_BLOCK 5-0-5754 changeNum: 953, changed
......
atomic 44, offset: 4328
ATTACH_BLOCK 5-0-2 block id: spcBid(0, 2), space 5, flags 129
DF_CHANGE_MAP 5-0-2 bitNumber: 25496, size: 1, isSet: 0
DETACH_BLOCK 5-0-2 changeNum: 34091, changed
ATTACH_BLOCK 5-0-1486 block id: spcBid(0, 1486), space 5, flags 4
UNDO_MODIFY_SEGMENT 5-0-1486 block list: count 1 head 0-25625 tail 0-25625, ufbCount: 998, ufbFirst: 0-25626
DETACH_BLOCK 5-0-1486 changeNum: 1240, changed
group: lsn 40797, size 20, handler's id 25, encrypt: 0
atomic 0, offset 16
REMOVE_DATAFILE 0-0-0 id: 6
数据库日志：
---------------------------
2024-04-30 17:24:49.107 5812 [INFO] [REDO] instance 0, switch redo file, type 8, point 0-10-1-12429
2024-04-30 17:24:52.192 13721 [INFO] [TABLESPACE] try to update instance 0 's space change lsn from 40771 to 40797
2024-04-30 17:24:52.193 13721 [DEBUG] [TABLESPACE] try to update instance 0 's space change lsn from 40771 to 40797
2024-04-30 17:24:52.204 5707 [DEBUG] [CKPT] update instance 0 rcy_begin 0-10-4-12431
第二次CKPT，删除表空间信息
redo 日志：
---------------------------
batch: lfn 12431, block id 4, size 104, space size 4096, part count 1, scn 559634404997152768, scn to time 2024-04-30 16:38:13.407508
reset id 0, asn 10, last lsn 40798, has nologging 0
group: lsn 40798, size 20, handler's id 25, encrypt: 0
atomic 0, offset 16
DROP_SPACE 0-0-0 id: 6
数据库日志：
---------------------------
2024-04-30 17:24:52.212 13721 [INFO] [TABLESPACE] succeed to drop datafile /yashan/yasdb_data/db-1-1/dbfiles/yyt01
2024-04-30 17:24:52.218 13721 [INFO] [TABLESPACE] try to update instance 0 's space change lsn from 40797 to 40798
2024-04-30 17:24:52.220 13721 [DEBUG] [TABLESPACE] try to update instance 0 's space change lsn from 40797 to 40798
2024-04-30 17:24:52.225 13721 [INFO] [TABLESPACE] succeed to drop tablespace YYT01
2024-04-30 17:24:52.226 5707 [DEBUG] [CKPT] update instance 0 rcy_begin 0-10-5-12432
