先找到是哪个索引

SELECT r.oid::regclass
FROM pg_class r
   JOIN pg_class t ON r.reltoastrelid = t.oid
   JOIN pg_index i ON t.oid = i.indrelid
   JOIN pg_class ti ON i.indexrelid = ti.oid
WHERE ti.relname = 'i_xx'
  AND ti.relnamespace = 'pg_toast'::regnamespace;

复制

这个错误表明在 PostgreSQL 数据库中，发现了一个与预期不符的索引链指向。具体来说，它指出了一个叫做 “i_xx” 的索引出现了问题。
错误信息中提到 “right sibling’s left-link doesn’t match”，这意味着在 B-树索引中，某个节点的右兄弟的左链接（left-link）与预期值不匹配。B-树是一种常用于数据库索引的数据结构。
在这种情况下，可能发生了数据损坏、磁盘故障或者其他未知的系统错误。
要解决这个问题，你可以考虑以下步骤：
备份数据：在采取任何进一步的行动之前，务必先备份数据库以防万一。
尝试恢复：如果你有备份，可以尝试从备份中恢复数据库。
运行检查和修复：PostgreSQL 提供了 REINDEX 命令来重新构建索引。你可以尝试运行以下命令：

REINDEX INDEX idx_table;
复制

这将尝试重新构建 “idx_table” 索引，以期解决问题。
检查磁盘和文件系统：如果出现这种错误，可能是由于磁盘故障或文件系统错误导致的。请确保你的磁盘和文件系统没有问题。
检查日志：查看 PostgreSQL 的日志文件以获取更多关于错误的信息。
查找专业帮助：如果以上步骤无法解决问题，可能需要寻求专业的数据库管理员或 PostgreSQL 支持团队的帮助。
请注意，如果你不是数据库专业人员，尤其是当涉及到可能导致数据丢失的操作时，最好咨询专业人士的建议，或者在安全的测试环境中进行实验。

mps_schools=> create table test_test(hello int);
ERROR:  right sibling's left-link doesn't match: block 19 links to 346956 instead of expected 346955 in index "pg_depend_reference_index"

REINDEX TABLE pg_catalog.pg_depend;
复制

问题分析

PostgreSQL是一个关系型数据库管理系统 (RDBMS)，而DNA双螺旋结构则是生物学中的一种分子结构。

尽管两者涉及完全不同的领域，它们之间并没有直接的类比。然而，如果你想以比喻的方式来理解，我们可以尝试以一种抽象的方式来比较它们：

结构和组织：

PostgreSQL：PostgreSQL 是一个数据库管理系统，它使用一种称为B-树（Balanced Tree）的数据结构来组织和管理数据。B-树是一种高效的索引结构，它被设计用于在数据库中快速查找和访问数据。

DNA双螺旋结构：DNA双螺旋结构是生物学中DNA分子的一种特定三维结构，由两条螺旋状的链组成，它们通过一系列的氢键连接在一起。

不匹配的情况：

• PG：在前面提到的错误信息中，出现了一个索引链的不匹配情况，这可能是由于数据库中的某些数据损坏或系统故障引起的。
• DNA双螺旋：在生物学中，DNA的双螺旋结构是一种非常稳定和准确的结构，但在某些情况下，可能会发生突变或者损伤，导致DNA的结构发生变化，这可能会影响到生物体的基因表达和遗传信息。

虽然这两者在领域和实际意义上没有直接的类比，但你可以将它们视为不同领域中的结构和组织，并尝试以这种比喻的方式理解它们。然而，请记住，这只是一个抽象的比喻，它们在实质上是完全不同的事物。

出现了一个索引链的不匹配情况，这可能是由于数据库中的某些数据损坏或系统故障引起的，还可能有哪些具体原因，我要更具体的原因

索引链不匹配可能有多种具体原因，以下是一些可能导致这种情况发生的常见原因：

• 磁盘故障或损坏：硬盘故障或者损坏可能会导致数据库文件在写入或读取时出现错误，从而影响了索引的正确性。
• 系统崩溃或突然断电：突然断电或者系统崩溃可能会导致正在进行的数据库操作被中断，从而导致数据文件的损坏。
• 数据库维护或升级错误：在进行数据库维护或升级操作时，如果过程中出现错误或中断，可能会导致索引链不匹配的问题。
• 硬件故障：除了硬盘故障外，其他硬件故障（如内存故障）也可能导致数据写入或读取时出现问题。
• 数据库软件错误：尽管相对较少见，但某些情况下，数据库软件本身可能存在bug或者错误，可能会导致索引链的问题。
• 操作错误：某些情况，不正确的数据库操作，如手动删除索引文件或者直接修改数据库文件，可能会导致索引链不匹配的情况
• 文件系统问题：文件系统的错误或者损坏也可能会导致数据库文件的问题，进而影响索引的正确性。

病毒或恶意软件：虽然比较罕见，但恶意软件可能会损坏系统或者数据库文件，导致索引链不匹配。

Oracle 索引出现坏块处理

SQL> create table test as select * from dba_objects where rownum<1001;
Table created.

SQL> create index idx_test on test(object_id);
Index created.

SQL> select file_id, block_id, blocks from dba_extents where owner = 'LILC' and segment_name = 'IDX_TEST';
   FILE_ID   BLOCK_ID  BLOCKS
---------- ---------- ----------
6 6032       8
从第4个块开始存储,构造坏块，
RMAN>  recover datafile 6 block 6035 clear;
Starting recover at 23-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=75 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=14 device type=DISK
Finished recover at 23-SEP-15
[oracle@cwogg ~]$ dbv userid=grid/grid file=+DATA/phub/datafile/llc01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Wed Sep 23 08:51:16 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = +DATA/phub/datafile/llc01.dbf
Page 6035 is marked corrupt
Corrupt block relative dba: 0x01801793 (file 6, block 6035)
Bad check value found during dbv: 
Data in bad block:
 type: 6 format: 2 rdba: 0x01801793
 last change scn: 0x0000.001e13c3 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x13c30601
 check value in block header: 0xc307
 computed block checksum: 0x5f27
DBVERIFY - Verification complete
Total Pages Examined         : 655360
Total Pages Processed (Data) : 7507
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 1181
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 646167
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 504
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 0 (0.0)

验证是否是该索引出现坏块：

SQL> SELECT tablespace_name, segment_type, owner,segment_name, partition_name FROM dba_extents WHERE file_id=6 and 6035 between block_id AND block_id+blocks-1;
TABLESPACE_NAME SEGMENT_TY OWNER    SEGMENT_NAME   PARTITION_NAME
--------------- ---------- -------- -------------- ------------------------------
LLCINDEX   LILC     IDX_TEST
此时如果全表扫描，是正常的，索引扫描报错：

SQL> select object_id from test;

1000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation  | Name | Rows  | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  | |  1000 | 13000 |     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |  1000 | 13000 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------

 0  recursive calls

 0  db block gets
81  consistent gets
 0  physical reads
 0  redo size
68  SQL*Net roundtrips to/from client
 0  sorts (memory)
 0  sorts (disk)

1000  rows processed
SQL> select object_id from test where object_id<100;
select object_id from test where object_id<100
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 6035)
ORA-01110: data file 6: '+DATA/phub/datafile/llc01.dbf
索引状态仍然是有效:

SQL> select status from dba_indexes where index_name='IDX_TEST'; 

STATUS
--------
VALID

可以加hint全表扫描就不会报错了：
SQL> select /*+ full(test) */object_id from test where object_id<100;
98 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  |        Operation  | Name | Rows  | Bytes | Cost (%CPU)|     Time |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    65 |   845 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |    65 |   845 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"<100)
Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
 0  recursive calls
 0  db block gets
23  consistent gets
 0  physical reads
 0  redo size
2137  bytes sent via SQL*Net to client
590  bytes received via SQL*Net from client
 8  SQL*Net roundtrips to/from client
 0  sorts (memory)
 0  sorts (disk)
98  rows processed
解决办法：在线重建索引
SQL> alter index idx_test rebuild online;
Index altered.

SQL> select object_id from test where object_id<100;
98 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1128569081
-----------------------------------------------------------------------------
| Id  |        Operation | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |    98 |  1274 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_TEST |    98 |  1274 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_ID"<100)

Note
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
 0  recursive calls
 0  db block gets
 9  consistent gets
 0  physical reads

 0  redo size

       2137  bytes sent via SQL*Net to client

590  bytes received via SQL*Net from client

 8  SQL*Net roundtrips to/from client

 0  sorts (memory)

 0  sorts (disk)

98  rows processed

通过DBV和 RMAN

[oracle@cwogg ~]$ dbv userid=grid/grid file=+DATA/phub/datafile/llc01.dbf

DBVERIFY: Release 11.2.0.4.0 - Production on Wed Sep 23 09:25:38 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = +DATA/phub/datafile/llc01.dbf
Page 6035 is marked corrupt
Corrupt block relative dba: 0x01801793 (file 6, block 6035)
Bad check value found during dbv: 
Data in bad block:
 type: 6 format: 2 rdba: 0x01801793
 last change scn: 0x0000.001e13c3 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x13c30601
 check value in block header: 0xc307
 computed block checksum: 0x5f27
DBVERIFY - Verification complete
Total Pages Examined         : 655360
Total Pages Processed (Data) : 7507
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 1179
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 646169
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 504
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 0 (0.0)
RMAN> backup check logical validate datafile 6;
Starting backup at 23-SEP-15
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/phub/datafile/llc01.dbf

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35

List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    FAILED 0              504          655364          1974761   
  File Name: +DATA/phub/datafile/llc01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              7507            
  Index      1              1177            
  Other      0              646172          
validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/phub/PHUB/trace/PHUB_ora_26417.trc for details
Finished backup at 23-SEP-15
SQL> analyze index lilc.idx_test validate structure;
Index analyzed.

RMAN> recover datafile 6 block 6035;
Starting recover at 23-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=73 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=141 device type=DISK
finished standby search, restored 1 blocks
starting media recovery
media recovery complete, elapsed time: 00:00:03--从备库修复
Finished recover at 23-SEP-15

删除索引后，重新创建索引，坏块仍然存在，但是索引可以使用

SQL> drop index idx_test;
Index dropped.

RMAN> backup check logical validate datafile 6;
Starting backup at 23-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=141 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=13 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/phub/datafile/llc01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    FAILED 0              504          655364          1977414   
  File Name: +DATA/phub/datafile/llc01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              7507            
  Index      1              1177            
  Other      0              646172          

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/phub/PHUB/trace/PHUB_ora_26936.trc for details
Finished backup at 23-SEP-15
删除索引，然后resize 数据文件，完成后再重建索引：
SQL> select tablespace_name,file_id,sum(bytes/1024/1024) Mb from dba_free_space where file_id=6 group by tablespace_name,file_id;
TABLESPACE_NAME  FILE_ID  MB
------------------------------ ---------- ----------
LLC 6   388.6875
破坏
RMAN> recover datafile 6 block 14211 clear;

Starting recover at 23-SEP-15

using channel ORA_DISK_1

using channel ORA_DISK_2

Finished recover at 23-SEP-15

[oracle@cwogg ~]$ dbv userid=grid/grid file=+DATA/phub/datafile/llc01.dbf

DBVERIFY: Release 11.2.0.4.0 - Production on Wed Sep 23 12:15:48 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = +DATA/phub/datafile/llc01.dbf
Page 14211 is marked corrupt
Corrupt block relative dba: 0x01803783 (file 6, block 14211)
Bad header found during dbv: 
Data in bad block:
 type: 6 format: 2 rdba: 0x8af33783
 last change scn: 0x5302.93e68286 seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x5f820602
 check value in block header: 0x7198
 computed block checksum: 0x968d

DBVERIFY - Verification complete

Total Pages Examined         : 25600
Total Pages Processed (Data) : 12140
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 1823
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 334
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 11302
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 0 (0.0)
删除索引，resize数据文件

SQL> drop index idx_test;
Index dropped.
SQL> select tablespace_name,file_id,sum(bytes/1024/1024) Mb from dba_free_space where file_id=6 group by tablespace_name,file_id;

TABLESPACE_NAME  FILE_ID  MB
------------------------------ ---------- ----------
LLC 6   402.6875

SQL> alter database datafile '+DATA/phub/datafile/llc01.dbf' resize 100M;
Database altered.

坏块消除：
[oracle@cwogg ~]$ dbv userid=grid/grid file=+DATA/phub/datafile/llc01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Wed Sep 23 12:15:58 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = +DATA/phub/datafile/llc01.dbf
DBVERIFY - Verification complete
Total Pages Examined         : 12800
Total Pages Processed (Data) : 12140
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 329
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 311
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 20
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 0 (0.0)
复制

参考：

https://stackoverflow.com/questions/49307292/error-in-postgresql-right-siblings-left-link-doesnt-match-block-5-links-to-8
https://stackoverflow.com/questions/48850188/error-right-siblings-left-link-doesnt-match-block-19-links-to-346956-instead