前言
YashanDB个人版开放后，陆续获得一大波「产品体验官」的关注和体验反馈，小崖在此把优秀的用户投稿文章分享给大家~今天分享的用户文章是《YashanDB数据文件损坏恢复测试》（作者：惠星星），欢迎大家一起交流。
内容概述
本文测试YashanDB数据文件异常损坏，使用备份集进行恢复。
01
数据库备份
SQL命令备份
[yashan@snyxdb ~]$ yasql sys/Root_1234YashanDB SQL Personal Edition Release 23.1.1.100 x86_64Connected to:YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit LinuxSQL> BACKUP DATABASE FULL FORMAT '/home/yashan/yasdb_data/db-1-1/backup/full_20211209191000' TAG 'yas_full_backup' PARALLELISM 3;Succeed.SQL>
yasrman工具备份
### create catalog[yashan@snyxdb ~]$ yasrman sys/Root_1234@127.0.0.1:1688 -c "create catalog" -D home/yashan/catalogcreate catalog successfully[yashan@snyxdb ~]$### show all[yashan@snyxdb ~]$ yasrman sys/Root_1234@127.0.0.1:1688 -c "configure PARALLELISM 4" -D home/yashan/catalogconfigure successfully[yashan@snyxdb ~]$[yashan@snyxdb ~]$ yasrman sys/Root_1234@127.0.0.1:1688 -c "show all" -D home/yashan/catalog+---------------------------+-----------+----------------+| NAME | DEFAULT | VALUE |+---------------------------+-----------+----------------+| PARALLELISM | 2 | 4 || SECTION SIZE | 134217728 | 134217728 || COMPRESSION ALGORITHM | NONE | NONE || COMPRESSION LEVEL | LOW | LOW || DEST | SERVER | SERVER |+---------------------------+-----------+----------------+[yashan@snyxdb ~]$### 单机全备[yashan@snyxdb ~]$ yasrman sys/Root_1234@192.168.1.115:1688 -c "BACKUP DATABASE TAG 'full_backup' FULL FORMAT 'full_001' PARALLELISM 3 DEST SERVER" -D home/yashan/catalogbackup successfully[yashan@snyxdb ~]$### 显示所有的备份集信息[yashan@snyxdb ~]$ yasrman sys/Root_1234@192.168.1.115:1688 -c "LIST BACKUP" -D home/yashan/catalogGroup: tag: full_1, format: home/yashan/yasdb_data/db-1-1/backup/full_bak_1, connect url: 192.168.1.115:1688, nodeCount: 1, distribution: FALSE, isClient: FALSE, offset: 0backup path: home/yashan/yasdb_data/db-1-1/backup/full_bak_1Group: tag: full_backup, format: home/yashan/yasdb_data/db-1-1/backup/full_001, connect url: 192.168.1.115:1688, nodeCount: 1, distribution: FALSE, isClient: FALSE, offset: 12288backup path: home/yashan/yasdb_data/db-1-1/backup/full_001[yashan@snyxdb ~]$
02
故障模拟
[yashan@snyxdb dbfiles]$ pwd/home/yashan/yasdb_data/db-1-1/dbfiles[yashan@snyxdb dbfiles]$ rm -rf *[yashan@snyxdb dbfiles]$ yasql sys/Root_1234YashanDB SQL Personal Edition Release 23.1.1.100 x86_64Connected to:YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit LinuxSQL> select status from v$instance;STATUS-------------OPEN1 row fetched.SQL> alter system switch logfile;YAS-06023 database is set to read-only because of database abnormalSQL> shutdown abort2 ;Succeed.SQL> exit
03
启动数据库
[yashan@snyxdb ~]$ yasboot cluster stop -c yashandbtype | uuid | name | hostid | index | status | return_code | progress | cost----------------------------------------------------------------------------------------------------------task | 34f06c3a36bcc753 | StopYasdbCluster | - | yashandb | SUCCESS | 0 | 100 | 1------+------------------+------------------+--------+----------+---------+-------------+----------+------task completed, status: SUCCESS[yashan@snyxdb ~]$[yashan@snyxdb ~]$ yasboot cluster start -c yashandb -m opentype | uuid | name | hostid | index | status | return_code | progress | cost----------------------------------------------------------------------------------------------------------task | dfbbb5133eb8df81 | StartYasdbCluster | - | yashandb | FAILED | 1 | 100 | 13------+------------------+-------------------+--------+----------+--------+-------------+----------+------task completed, status: FAILEDretcode: 1stdout: node:1-1 start with ping failedstderr: wait node 1-1 process start failed: Starting instance openfailed to open file home/yashan/yasdb_data/db-1-1/dbfiles/ctrl1, errno 2, error message "No such file or directory"Failed to start instance[yashan@snyxdb ~]$
04
恢复数据库
### 检查备份集信息[yashan@snyxdb ~]$ yasrman sys/Root_1234@192.168.1.115:1688 -c "LIST BACKUP" -D home/yashan/catalogGroup: tag: full_1, format: home/yashan/yasdb_data/db-1-1/backup/full_bak_1, connect url: 192.168.1.115:1688, nodeCount: 1, distribution: FALSE, isClient: FALSE, offset: 0backup path: home/yashan/yasdb_data/db-1-1/backup/full_bak_1Group: tag: full_backup, format: home/yashan/yasdb_data/db-1-1/backup/full_001, connect url: 192.168.1.115:1688, nodeCount: 1, distribution: FALSE, isClient: FALSE, offset: 12288backup path: home/yashan/yasdb_data/db-1-1/backup/full_001[yashan@snyxdb ~]$### 不使用TAG 尝试恢复[yashan@snyxdb ~]$ yasrman sys/Root_1234@192.168.1.115:1688 -c "RESTORE DATABASE" -D home/yashan/catalog[Node 0]YAS-04115 "TAG" expected but missing[yashan@snyxdb ~]$### 使用TAG 尝试恢复[yashan@snyxdb ~]$ yasrman sys/Root_1234@192.168.1.115:1688 -c "RESTORE DATABASE FROM TAG 'full_backup'" -D home/yashan/catalog[Node 0]YAS-00402 failed to connect socket, errno 111, error message "Connection refused"[yashan@snyxdb ~]$ nohup yasdb nomount &[1] 31862[yashan@snyxdb ~]$ nohup: ignoring input and appending output to ‘nohup.out’[yashan@snyxdb ~]$ yasrman sys/Root_1234@192.168.1.115:1688 -c "RESTORE DATABASE FROM TAG 'full_backup'" -D home/yashan/catalogrestore successfully[yashan@snyxdb ~]$
05
业务数据检查
[yashan@snyxdb ~]$ yasql sys/Root_1234YashanDB SQL Personal Edition Release 23.1.1.100 x86_64Connected to:YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit LinuxSQL> select status from v$instance;STATUS-------------OPEN1 row fetched.SQL> select count(1) from hsql.drop_1;COUNT(1)---------------------100000001 row fetched.SQL>SQL> select * from v$datafile;ID NAME CREATION_TIME TS# BLOCKS BLOCK_SIZE BYTES STATUS RELATIVE_FNO AUTO_EXTEND NEXT_SIZE MAX_SIZE FREE_BLOCKS DISK_BYTES------------ ---------------------------------------------------------------- ---------------------------------------------------------------- ------------ ------------ ------------ --------------------- --------- ------------ ----------- --------------------- --------------------- ------------ ---------------------0 home/yashan/yasdb_data/db-1-1/dbfiles/system 2023-11-24 08:40:33.293537 0 8192 8192 67108864 ONLINE 0 ON 67108864 549755813888 4968 671088641 home/yashan/yasdb_data/db-1-1/dbfiles/sysaux 2023-11-24 08:40:33.480727 1 8192 8192 67108864 ONLINE 0 ON 67108864 549755813888 7760 671088642 home/yashan/yasdb_data/db-1-1/dbfiles/temp 2023-11-28 15:44:49.288961 2 8192 8192 67108864 ONLINE 0 ON 67108864 549755813888 7552 671088643 home/yashan/yasdb_data/db-1-1/dbfiles/swap 2023-11-28 15:44:49.305069 3 49152 8192 402653184 ONLINE 0 ON 67108864 549755813888 25888 4026531844 home/yashan/yasdb_data/db-1-1/dbfiles/users 2023-11-24 08:40:34.120684 4 98304 8192 805306368 ONLINE 0 ON 67108864 549755813888 6144 8053104645 home/yashan/yasdb_data/db-1-1/dbfiles/undo 2023-11-24 08:40:34.307710 5 90112 8192 738197504 ONLINE 0 ON 67108864 68719476736 44518 7381975046 rows fetched.SQL> alter system dump datafile 0 block 1;Succeed.SQL>
06
datafile block dump
SQL> select * from v$datafile;ID NAME CREATION_TIME TS# BLOCKS BLOCK_SIZE BYTES STATUS RELATIVE_FNO AUTO_EXTEND NEXT_SIZE MAX_SIZE FREE_BLOCKS DISK_BYTES------------ ---------------------------------------------------------------- ---------------------------------------------------------------- ------------ ------------ ------------ --------------------- --------- ------------ ----------- --------------------- --------------------- ------------ ---------------------0 home/yashan/yasdb_data/db-1-1/dbfiles/system 2023-11-24 08:40:33.293537 0 8192 8192 67108864 ONLINE 0 ON 67108864 549755813888 4968 671088641 home/yashan/yasdb_data/db-1-1/dbfiles/sysaux 2023-11-24 08:40:33.480727 1 8192 8192 67108864 ONLINE 0 ON 67108864 549755813888 7760 671088642 home/yashan/yasdb_data/db-1-1/dbfiles/temp 2023-11-28 15:44:49.288961 2 8192 8192 67108864 ONLINE 0 ON 67108864 549755813888 7552 671088643 home/yashan/yasdb_data/db-1-1/dbfiles/swap 2023-11-28 15:44:49.305069 3 49152 8192 402653184 ONLINE 0 ON 67108864 549755813888 25888 4026531844 home/yashan/yasdb_data/db-1-1/dbfiles/users 2023-11-24 08:40:34.120684 4 98304 8192 805306368 ONLINE 0 ON 67108864 549755813888 6144 8053104645 home/yashan/yasdb_data/db-1-1/dbfiles/undo 2023-11-24 08:40:34.307710 5 90112 8192 738197504 ONLINE 0 ON 67108864 68719476736 44518 7381975046 rows fetched.SQL> alter system dump datafile 0 block 0;Succeed.SQL> alter system dump datafile 0 block 1;Succeed.SQL> alter system dump datafile 0 block 2;Succeed.SQL>*** 2023-11-28 15:51:50.845dump type : datafile blockssize : 8192bytesblock: 0data file headhead: id 0-0, type 1, instance id 0 lsn 0, checksum: 0, change num 0, isEncrypted 0, isCompressed 0space id: 0 db id: 3249731628 db create time: 2023-11-24 08:40:31, create lsn 1, truncate lsn 0tail: change num: 0*** 2023-11-28 15:51:53.913dump type : datafile blockssize : 8192bytesblock: 1space headhead: id 0-1, type 2, instance id 0 lsn 1, checksum: 0, change num 0, isEncrypted 0, isCompressed 0migration number: 0tail: change num: 0*** 2023-11-28 15:51:57.065dump type : datafile blockssize : 8192bytesblock: 2datafile maphead: id 0-2, type 9, instance id 0 lsn 106801, checksum: 0, change num 237, isEncrypted 0, isCompressed 0data first: 128 free begin: 160 free count: 64829### 小结：YashDB文件头包含信息较少(和Oracle对比)，大量管理信息可能存储基表中。
07
数据存储格式dump
### 查询rowid信息SQL> select rowid,t.* from hsql.drop_1 t where rownum=1;ROWID C1 C2-------------------------------------------- ----------- ----------------------------------------------------------------2258:4:0:132:0 1 orastar1 row fetched.SQL>### rowid格式解释2258:4:0:132:0ROWID的数据格式为dataoid:spaceid:fileid:blockid:dir。在用于存储行表的ROWID时，UROWID的数据格式为0x1+ROWID的十六进制表述。#dataoiddata object id，行所在的Segment的ID，该值可从USER_OBJECTS等视图中的DATA_OBJECT_ID字段查询获得。#spaceidspace id，行所在的表空间的ID，该值可从V$TABLESPACE等视图中的ID字段查询获得。#fileidfile id，行所在数据文件在对应表空间中的数据文件ID，该值可从V$DATAFILE等视图中的ID字段查询获得。#blockidblock id，行所在数据块在对应文件中的块ID。#dirdir，行在数据块上的槽位。### dump data block*** 2023-11-28 16:36:55.638dump type : datafile blockssize : 8192bytesblock: 132heap datahead: id 0-132, type 4, instance id 0 lsn 30469, checksum: 0, change num 416, isEncrypted 0, isCompressed 0dataOid: 2258, extent change number: 0block scn: 503703439844323328, map block: 0-128, slot: 4, freeness: 0rows: 410, dirs: 410, xslots: 2, si xslots: 0, free size: 680, free begin: 6640, free end: 7320 free dir: 4095row[0]: size: 16 xslot id: 0 columns: 2 format/link/migr/deleted/compact:1/0/0/0/0row[1]: size: 16 xslot id: 0 columns: 2 format/link/migr/deleted/compact:1/0/0/0/0row[2]: size: 16 xslot id: 0 columns: 2 format/link/migr/deleted/compact:1/0/0/0/0row[3]: size: 16 xslot id: 0 columns: 2 format/link/migr/deleted/compact:1/0/0/0/0row[4]: size: 16 xslot id: 0 columns: 2 format/link/migr/deleted/compact:1/0/0/0/0....row[404]: size: 16 xslot id: 0 columns: 2 format/link/migr/deleted/compact:1/0/0/0/0row[405]: size: 16 xslot id: 0 columns: 2 format/link/migr/deleted/compact:1/0/0/0/0row[406]: size: 16 xslot id: 0 columns: 2 format/link/migr/deleted/compact:1/0/0/0/0row[407]: size: 16 xslot id: 0 columns: 2 format/link/migr/deleted/compact:1/0/0/0/0row[408]: size: 16 xslot id: 0 columns: 2 format/link/migr/deleted/compact:1/0/0/0/0row[409]: size: 16 xslot id: 0 columns: 2 format/link/migr/deleted/compact:1/0/0/0/0xslot[0]: xid: 34-10185-1 active: 0 owscn: 0 fastcommit: 1 ura: block: 0-46945, ver: 0, dir: 157 ssn: 470822912 fsc: 33692 mfb: 1789 scn: 503703439844323328xslot[1]: xid: 0-0-0 active: 0 owscn: 0 fastcommit: 0 ura: block: 0-0, ver: 0, dir: 0 ssn: 0 fsc: 0 mfb: 0 scn: 0tail: change num: 416### hexdump 数据块[yashan@snyxdb trace]$ dd if=/home/yashan/yasdb_data/db-1-1/dbfiles/users bs=8192 skip=132 seek=0 count=1 |hexdump -C1+0 records in1+0 records out8192 bytes (8.2 kB) copied, 6.2154e-05 s, 132 MB/s00000000 1e bc 94 14 a0 01 00 00 05 77 00 00 00 00 00 00 |.........w......|00000010 00 21 00 00 04 00 00 00 d2 08 00 00 00 00 00 00 |.!..............|00000020 00 00 00 00 00 01 04 00 00 20 00 00 9a a1 19 02 |......... ......|00000030 00 30 10 1c 9c 83 fd 06 f0 19 98 1c a8 02 a8 02 |.0..............|00000040 ff 0f 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|00000050 10 00 01 00 02 01 01 07 6f 72 61 73 74 61 72 61 |........orastara|00000060 10 00 01 00 02 01 02 07 6f 72 61 73 74 61 72 61 |........orastara|00000070 10 00 01 00 02 01 03 07 6f 72 61 73 74 61 72 61 |........orastara|00000080 10 00 01 00 02 01 04 07 6f 72 61 73 74 61 72 61 |........orastara|00000090 10 00 01 00 02 01 05 07 6f 72 61 73 74 61 72 61 |........orastara|000000a0 10 00 01 00 02 01 06 07 6f 72 61 73 74 61 72 61 |........orastara|000000b0 10 00 01 00 02 01 07 07 6f 72 61 73 74 61 72 61 |........orastara|000000c0 10 00 01 00 02 01 08 07 6f 72 61 73 74 61 72 61 |........orastara|000000d0 10 00 01 00 02 01 09 07 6f 72 61 73 74 61 72 61 |........orastara|000000e0 10 00 01 00 02 01 0a 07 6f 72 61 73 74 61 72 61 |........orastara|000000f0 10 00 01 00 02 01 0b 07 6f 72 61 73 74 61 72 61 |........orastara|...00001f00 a0 06 90 06 80 06 70 06 60 06 50 06 40 06 30 06 |......p.`.P.@.0.|00001f10 20 06 10 06 00 06 f0 05 e0 05 d0 05 c0 05 b0 05 | ...............|00001f20 a0 05 90 05 80 05 70 05 60 05 50 05 40 05 30 05 |......p.`.P.@.0.|00001f30 20 05 10 05 00 05 f0 04 e0 04 d0 04 c0 04 b0 04 | ...............|00001f40 a0 04 90 04 80 04 70 04 60 04 50 04 40 04 30 04 |......p.`.P.@.0.|00001f50 20 04 10 04 00 04 f0 03 e0 03 d0 03 c0 03 b0 03 | ...............|00001f60 a0 03 90 03 80 03 70 03 60 03 50 03 40 03 30 03 |......p.`.P.@.0.|00001f70 20 03 10 03 00 03 f0 02 e0 02 d0 02 c0 02 b0 02 | ...............|00001f80 a0 02 90 02 80 02 70 02 60 02 50 02 40 02 30 02 |......p.`.P.@.0.|00001f90 20 02 10 02 00 02 f0 01 e0 01 d0 01 c0 01 b0 01 | ...............|00001fa0 a0 01 90 01 80 01 70 01 60 01 50 01 40 01 30 01 |......p.`.P.@.0.|00001fb0 20 01 10 01 00 01 f0 00 e0 00 d0 00 c0 00 b0 00 | ...............|00001fc0 a0 00 90 00 80 00 70 00 60 00 50 00 00 00 00 00 |......p.`.P.....|00001fd0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|00001fe0 00 00 00 00 22 00 c9 27 01 00 00 00 00 30 10 1c |...."..'.....0..|00001ff0 9c 83 fd 06 40 d8 2d 00 00 00 9d 40 a0 01 00 00 |....@.-....@....|00002000[yashan@snyxdb trace]$### 小结1. 通过数据块dump，xslots: 2,代表事务槽的数量，xslot[n]可以控制块上的事务数，xid: 事务标识符 (XID) 惟一地标识系统中的事务,dump中并没有显示数据信息。2. 行存储数据格式为：02 01 01 07 6f 72 61 73 74 61 72 6102 columns01: size of column01: number格式数据07：size of column6f 72 61 73 74 61 72: varchar2格式数据61: 推测为行结尾或varchar2格式column结尾。
遇到问题
问题1：[Node 0]YAS-00402 failed to connect socket, errno 111, error message “Connection refused”
只能在数据库状态为NOMOUNT，且旧的数据文件必须已被删除时执行恢复。
问题2：YAS-00311 failed to create file /home/yashan/yasdb_data/db-1-1/dbfiles/ctrl1, errno 17, error message “File exists”
只能在数据库状态为NOMOUNT，且旧的数据文件必须已被删除时执行恢复。
问题3：[ERROR][errno=06006]: instance has been started by another process
[yashan@snyxdb ~]$ ps -ef|grep yasdbyashan 28939 17057 7 20:52 pts/4 00:00:04 yasdb nomountyashan 29036 17057 0 20:53 pts/4 00:00:00 grep --color=auto yasdbyashan 116104 1 0 08:39 ? 00:00:17 /home/yashan/yasdb_home/yashandb/23.1.1.100/bin/yasagent --init -c yashandb -l 192.168.1.115:1676 --host-id host0001 -k 393d04f54487b2ad -dyashan 116132 1 0 08:39 ? 00:00:37 /home/yashan/yasdb_home/yashandb/23.1.1.100/bin/yasom --init -c yashandb -l 192.168.1.115:1675 -k 393d04f54487b2ad -d[yashan@snyxdb ~]$ kill -9 28939### 这个命令应该也可以yasboot cluster stop -c yashandb
问题4：YAS-02193 the account is locked
[yashan@snyxdb instance]$ ls -ltrtotal 2052-rw-r----- 1 yashan yashan 1048576 Nov 24 08:40 yasdb1.pwd-rwx------ 1 yashan yashan 4 Nov 24 17:58 yasdb.pidsrwxrwxr-x 1 yashan yashan 0 Nov 24 17:58 yasdb.ipc-rw-r----- 1 yashan yashan 1048576 Nov 24 17:58 yasdb.pwd[yashan@snyxdb instance]$ mv yasdb.pwd ~/[yashan@snyxdb instance]$ yaspwd file=yasdb.pwd问题5：restore datafile YAS-04115 “DATABASE” expected but missing
syntax::= RESTORE DATABASE [DECRYPTION password] FROM backup_path [PARALLELISM integer]只支持restore database实验总结
不支持restore datafile,如果单个文件损坏，只能restore database进行恢复，数据量较大的库影响恢复效率。yasrman不支持OS权限登陆，只支持账号密码登陆方式，实现逻辑固化不支持交互操作，与Oracle rman实现逻辑略有不同。[yashan@snyxdb ~]$ yasrman --helpYashanDB Recovery Manager Personal Edition Release 23.1.1.100 x86_64 0e623bdUsage 1: yasrman -H | -V-H show version and help-V show versionUsage 2: yasrman {<logon>} {<option>}<logon> is {<username>/<password>@<connect_identifier>}<option> is { -c "SQL"} {-D catalogpath}-c run only single backup command-D specify the catalog path[yashan@snyxdb ~]$YashDB文件头包含信息较少(和Oracle对比)，大量管理信息可能存储基表中。通过数据块dump，xslots: 2,代表事务槽的数量，xslot[n]可以控制块上的事务数，xid: 事务标识符 (XID) 惟一地标识系统中的事务,dump中并没有显示数据信息。行存储数据格式为：02 01 01 07 6f 72 61 73 74 61 72 6102 columns01: size of column01: number格式数据07：size of column6f 72 61 73 74 61 72: varchar2格式数据61: 推测为行结尾或varchar2格式column结尾。number格式是直接存储数字的hex值，与Oracle实现逻辑不一致。rowid 中 file id是相对于所在表空间从0开始id，与v$datafile中的id不一致。容易造成混淆。2258:4:0:132:0ROWID的数据格式为dataoid:spaceid:fileid:blockid:dir。file id，行所在数据文件在对应表空间中的数据文件ID，该值可从V$DATAFILE等视图中的ID字段查询获得。Oracle原实现逻辑如下所示，FILE#(File identification number):数据文件唯一标识，database级唯一。RFILE#(Tablespace relative datafile number):表空间相对文件号，tablespace级唯一。File#取值范围为：1~65533。RFILE#的取值范围为: 1~1023,大于1023时重置为1保持表空间内唯一。当file#<1024时,file#=rfile#,否则file#自增，rfile重置为1自增。rdba由4 bytes组成，共32 bits,offset 22~31（10 bits）表示 RFILE#,offset 0~21（22 bits）表示：block#Oracle Physical Database Limits中（Database files Maximum per tablespace Operating system dependent; usually 1022），2^10 = 1024-1(去掉0),所以rfile#在单个表空间内最多1023个，官方文档和mos中提到的1022，有点不准确。相关阅读
文章转载自YashanDB，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
