暂无图片
暂无图片
1
暂无图片
暂无图片
暂无图片

「YashanDB个人版体验」_YashanDB 数据文件损坏恢复测试

内容概述

本文测试YashanDB数据文件异常损坏,使用备份集进行恢复。

数据库备份

SQL命令备份

[yashan@snyxdb ~]$ yasql sys/Root_1234 YashanDB SQL Personal Edition Release 23.1.1.100 x86_64 Connected to: YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit Linux SQL> 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/catalog create catalog successfully [yashan@snyxdb ~]$ ### show all [yashan@snyxdb ~]$ yasrman sys/Root_1234@127.0.0.1:1688 -c "configure PARALLELISM 4" -D /home/yashan/catalog configure 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/catalog backup successfully [yashan@snyxdb ~]$ ### 显示所有的备份集信息 [yashan@snyxdb ~]$ yasrman sys/Root_1234@192.168.1.115:1688 -c "LIST BACKUP" -D /home/yashan/catalog Group: 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: 0 backup path: /home/yashan/yasdb_data/db-1-1/backup/full_bak_1 Group: 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: 12288 backup path: /home/yashan/yasdb_data/db-1-1/backup/full_001 [yashan@snyxdb ~]$

故障模拟

[yashan@snyxdb dbfiles]$ pwd /home/yashan/yasdb_data/db-1-1/dbfiles [yashan@snyxdb dbfiles]$ rm -rf * [yashan@snyxdb dbfiles]$ yasql sys/Root_1234 YashanDB SQL Personal Edition Release 23.1.1.100 x86_64 Connected to: YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit Linux SQL> select status from v$instance; STATUS ------------- OPEN 1 row fetched. SQL> alter system switch logfile; YAS-06023 database is set to read-only because of database abnormal SQL> shutdown abort 2 ; Succeed. SQL> exit

启动数据库

[yashan@snyxdb ~]$ yasboot cluster stop -c yashandb type | 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 open type | uuid | name | hostid | index | status | return_code | progress | cost ---------------------------------------------------------------------------------------------------------- task | dfbbb5133eb8df81 | StartYasdbCluster | - | yashandb | FAILED | 1 | 100 | 13 ------+------------------+-------------------+--------+----------+--------+-------------+----------+------ task completed, status: FAILED retcode: 1 stdout: node:1-1 start with ping failed stderr: wait node 1-1 process start failed: Starting instance open failed 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 ~]$

恢复数据库

### 检查备份集信息 [yashan@snyxdb ~]$ yasrman sys/Root_1234@192.168.1.115:1688 -c "LIST BACKUP" -D /home/yashan/catalog Group: 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: 0 backup path: /home/yashan/yasdb_data/db-1-1/backup/full_bak_1 Group: 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: 12288 backup 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/catalog restore successfully [yashan@snyxdb ~]$

业务数据检查

[yashan@snyxdb ~]$ yasql sys/Root_1234 YashanDB SQL Personal Edition Release 23.1.1.100 x86_64 Connected to: YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit Linux SQL> select status from v$instance; STATUS ------------- OPEN 1 row fetched. SQL> select count(1) from hsql.drop_1; COUNT(1) --------------------- 10000000 1 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 67108864 1 /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 67108864 2 /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 67108864 3 /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 402653184 4 /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 805310464 5 /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 738197504 6 rows fetched. SQL> alter system dump datafile 0 block 1; Succeed. SQL>

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 67108864 1 /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 67108864 2 /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 67108864 3 /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 402653184 4 /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 805310464 5 /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 738197504 6 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.845 dump type : datafile blocks size : 8192bytes block: 0 data file head head: id 0-0, type 1, instance id 0 lsn 0, checksum: 0, change num 0, isEncrypted 0, isCompressed 0 space id: 0 db id: 3249731628 db create time: 2023-11-24 08:40:31, create lsn 1, truncate lsn 0 tail: change num: 0 *** 2023-11-28 15:51:53.913 dump type : datafile blocks size : 8192bytes block: 1 space head head: id 0-1, type 2, instance id 0 lsn 1, checksum: 0, change num 0, isEncrypted 0, isCompressed 0 migration number: 0 tail: change num: 0 *** 2023-11-28 15:51:57.065 dump type : datafile blocks size : 8192bytes block: 2 datafile map head: id 0-2, type 9, instance id 0 lsn 106801, checksum: 0, change num 237, isEncrypted 0, isCompressed 0 data first: 128 free begin: 160 free count: 64829 ### 小结: YashDB文件头包含信息较少(和Oracle对比),大量管理信息可能存储基表中。

数据存储格式dump

### 查询rowid信息 SQL> select rowid,t.* from hsql.drop_1 t where rownum=1; ROWID C1 C2 -------------------------------------------- ----------- ---------------------------------------------------------------- 2258:4:0:132:0 1 orastar 1 row fetched. SQL> ### rowid格式解释 2258:4:0:132:0 ROWID的数据格式为dataoid:spaceid:fileid:blockid:dir。 在用于存储行表的ROWID时,UROWID的数据格式为0x1+ROWID的十六进制表述。 #dataoid data object id,行所在的Segment的ID,该值可从USER_OBJECTS等视图中的DATA_OBJECT_ID字段查询获得。 #spaceid space id,行所在的表空间的ID,该值可从V$TABLESPACE等视图中的ID字段查询获得。 #fileid file id,行所在数据文件在对应表空间中的数据文件ID,该值可从V$DATAFILE等视图中的ID字段查询获得。 #blockid block id,行所在数据块在对应文件中的块ID。 #dir dir,行在数据块上的槽位。 ### dump data block *** 2023-11-28 16:36:55.638 dump type : datafile blocks size : 8192bytes block: 132 heap data head: id 0-132, type 4, instance id 0 lsn 30469, checksum: 0, change num 416, isEncrypted 0, isCompressed 0 dataOid: 2258, extent change number: 0 block scn: 503703439844323328, map block: 0-128, slot: 4, freeness: 0 rows: 410, dirs: 410, xslots: 2, si xslots: 0, free size: 680, free begin: 6640, free end: 7320 free dir: 4095 row[0]: size: 16 xslot id: 0 columns: 2 format/link/migr/deleted/compact:1/0/0/0/0 row[1]: size: 16 xslot id: 0 columns: 2 format/link/migr/deleted/compact:1/0/0/0/0 row[2]: size: 16 xslot id: 0 columns: 2 format/link/migr/deleted/compact:1/0/0/0/0 row[3]: size: 16 xslot id: 0 columns: 2 format/link/migr/deleted/compact:1/0/0/0/0 row[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/0 row[405]: size: 16 xslot id: 0 columns: 2 format/link/migr/deleted/compact:1/0/0/0/0 row[406]: size: 16 xslot id: 0 columns: 2 format/link/migr/deleted/compact:1/0/0/0/0 row[407]: size: 16 xslot id: 0 columns: 2 format/link/migr/deleted/compact:1/0/0/0/0 row[408]: size: 16 xslot id: 0 columns: 2 format/link/migr/deleted/compact:1/0/0/0/0 row[409]: size: 16 xslot id: 0 columns: 2 format/link/migr/deleted/compact:1/0/0/0/0 xslot[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: 503703439844323328 xslot[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: 0 tail: 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 -C 1+0 records in 1+0 records out 8192 bytes (8.2 kB) copied, 6.2154e-05 s, 132 MB/s 00000000 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 61 02 columns 01: size of column 01: number格式数据 07:size of column 6f 72 61 73 74 61 72: varchar2格式数据 61: 推测为行结尾或varchar2格式column结尾。

遇到问题

问题一、[Node 0]YAS-00402 failed to connect socket, errno 111, error message “Connection refused”

只能在数据库状态为NOMOUNT,且旧的数据文件必须已被删除时执行恢复。

问题二、YAS-00311 failed to create file /home/yashan/yasdb_data/db-1-1/dbfiles/ctrl1, errno 17, error message “File exists”

只能在数据库状态为NOMOUNT,且旧的数据文件必须已被删除时执行恢复。

问题三、[ERROR][errno=06006]: instance has been started by another process

[yashan@snyxdb ~]$ ps -ef|grep yasdb yashan 28939 17057 7 20:52 pts/4 00:00:04 yasdb nomount yashan 29036 17057 0 20:53 pts/4 00:00:00 grep --color=auto yasdb yashan 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 -d yashan 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

问题四、YAS-02193 the account is locked

[yashan@snyxdb instance]$ ls -ltr total 2052 -rw-r----- 1 yashan yashan 1048576 Nov 24 08:40 yasdb1.pwd -rwx------ 1 yashan yashan 4 Nov 24 17:58 yasdb.pid srwxrwxr-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

问题五、restore datafile YAS-04115 “DATABASE” expected but missing

syntax::= RESTORE DATABASE [DECRYPTION password] FROM backup_path [PARALLELISM integer] 只支持restore database

实验总结

1. 不支持restore datafile,如果单个文件损坏,只能restore database进行恢复,数据量较大的库影响恢复效率。 2. yasrman不支持OS权限登陆,只支持账号密码登陆方式,实现逻辑固化不支持交互操作,与Oracle rman实现逻辑略有不同。 [yashan@snyxdb ~]$ yasrman --help YashanDB Recovery Manager Personal Edition Release 23.1.1.100 x86_64 0e623bd Usage 1: yasrman -H | -V -H show version and help -V show version Usage 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 ~]$ 3. YashDB文件头包含信息较少(和Oracle对比),大量管理信息可能存储基表中。 4. 通过数据块dump,xslots: 2,代表事务槽的数量,xslot[n]可以控制块上的事务数,xid: 事务标识符 (XID) 惟一地标识系统中的事务,dump中并没有显示数据信息。 5. 行存储数据格式为: 02 01 01 07 6f 72 61 73 74 61 72 61 02 columns 01: size of column 01: number格式数据 07:size of column 6f 72 61 73 74 61 72: varchar2格式数据 61: 推测为行结尾或varchar2格式column结尾。 6. number格式是直接存储数字的hex值,与Oracle实现逻辑不一致。 7. rowid 中 file id是相对于所在表空间从0开始id,与v$datafile中的id不一致。容易造成混淆。 2258:4:0:132:0 ROWID的数据格式为dataoid:spaceid:fileid:blockid:dir。 file id,行所在数据文件在对应表空间中的数据文件ID,该值可从V$DATAFILE等视图中的ID字段查询获得。 Oracle原实现逻辑如下所示, 1、 FILE#(File identification number):数据文件唯一标识,database级唯一。 2、 RFILE#(Tablespace relative datafile number):表空间相对文件号,tablespace级唯一。 3、 File#取值范围为: 1~65533。 4、 RFILE#的取值范围为: 1~1023,大于1023时重置为1保持表空间内唯一。 5、 当file#<1024时,file#=rfile#,否则file#自增,rfile重置为1自增。 6、 rdba由4 bytes组成,共32 bits,offset 22~31(10 bits)表示 RFILE#,offset 0~21(22 bits)表示:block# 7、 Oracle Physical Database Limits中(Database files Maximum per tablespace Operating system dependent; usually 1022),2^10 = 1024-1(去掉0),所以rfile#在单个表空间内最多1023个,官方文档和mos中提到的1022,有点不准确。
最后修改时间:2023-11-28 17:31:46
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论