记录一次金仓V8R3数据库坏块处理过程、PostgreSQL数据库适用

因数广政务云华为业务存储固件升级，导致数据库产生坏块，业务SQL查询报错如下：

ERROR:  missing chunk number 0 for toast value 38166585 in SYS_TOAST_30170
CONTEXT:  PL/SQL function inline_code_block line 12 at FOR over EXECUTE statement

解决办法：

1、先查询出 SYS_TOAST_30170 属于哪张表的大字段。

select 
  n.nspname AS schema_name,
  c.relname AS table_name,
  a.attname AS column_name,
  bb.relname as TOAST_name
 from 
  sys_class c 
  inner join (select substring(sc.RELNAME from '[0-9]+') gg, sc.relname from sys_class sc) bb on c.oid = bb.gg
  inner join sys_attribute a on a.attrelid = c.oid
  inner join sys_namespace n ON n.oid = c.relnamespace
  WHERE 
    a.attnum > 0 
  AND NOT a.attisdropped
  AND a.atttypid = (SELECT oid FROM sys_type WHERE typname = 'text')  
  and bb.relname = 'SYS_TOAST_30170';

PostgreSQL使用以下脚本：

select 
  n.nspname AS schema_name,
  c.relname AS table_name,
  a.attname AS column_name,
  bb.relname as TOAST_name
 from 
    pg_class c 
    inner join (select substring(sc.RELNAME from '[0-9]+') gg, sc.relname from pg_class sc) bb on c.oid = bb.gg
    inner join pg_attribute a on a.attrelid = c.oid
    inner join pg_namespace n ON n.oid = c.relnamespace
    WHERE 
    a.attnum > 0 
    AND NOT a.attisdropped
    AND a.atttypid = (SELECT oid FROM pg_type WHERE typname = 'text')  
    and bb.relname = 'SYS_TOAST_30170'；

2、创建一张记录含有坏块行的表、row_error_table

CREATE TABLE row_error_table (
    err_ctid tid,
    err_tablename varchar2(1000),
    err_cloum varchar2(500),
    err_CONTEXT text
);

3、使用下面脚本找到坏块在哪些行进行处理。

DO $$
DECLARE
    rec RECORD;        
    /* 需要检测坏块的表、手动输入 */
    v_tablename text := 'g_inbox';     
    /* 需要检测坏块的列、手动输入 */    
    v_columnname text := 'PROCESS_CONTENT';                
BEGIN
    FOR rec IN EXECUTE format('SELECT ctid, %I FROM %I ORDER BY ctid ', v_columnname, v_tablename) LOOP
        BEGIN
            RAISE NOTICE 'CTID Parameter is: %', rec.ctid;
            RAISE NOTICE 'ROW Parameter is: %', rec.PROCESS_CONTENT;
        EXCEPTION WHEN others THEN 
      /* 遇到 ERROR:  missing chunk number 0 for toast value ... 异常 直接记录在 row_error_table*/
            INSERT INTO row_error_table (err_ctid, err_tablename,err_cloum,err_CONTEXT) VALUES (rec.ctid, v_tablename,v_columnname,SQLERRM);
        END;
    END LOOP;
END;
$$ 
LANGUAGE plpgsql;