恢复出来的 Oracle 租户无法在 DBA_CONSTRAINTS 查询到表的外键约束

问题现象

OceanBase 数据库 V3.2.3 BP3 版本恢复 Oracle 租户后，源端表是有外键约束，恢复出来的表丢掉了外键约束。

源端表结构，T2 表存在一个外键约束，示例。

CREATE TABLE "T1" (
  "NO" VARCHAR2(20) CONSTRAINT "T1_OBNOTNULL_1709038404423200" NOT NULL ENABLE,
  "SALE_TYPE" VARCHAR2(2) CONSTRAINT "T1_OBNOTNULL_1709038404423231" NOT NULL ENABLE,
  "APPTYPE" VARCHAR2(2) CONSTRAINT "T1_OBNOTNULL_1709038404423235" NOT NULL ENABLE,
  "ISSUMFILE" VARCHAR2(2) DEFAULT '00',
  CONSTRAINT "PK_SALE_TYPE" PRIMARY KEY ("NO"),
  CONSTRAINT "U_T1" UNIQUE ("SALE_TYPE")
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0


CREATE TABLE "T2" (
  "NO" VARCHAR2(20) CONSTRAINT "T2_OBNOTNULL_1709038409862986" NOT NULL ENABLE,
  "INSU_MIDNO" VARCHAR2(50) CONSTRAINT "T2_OBNOTNULL_1709038409863015" NOT NULL ENABLE,
  "SALE_TYPE" VARCHAR2(2) CONSTRAINT "T2_OBNOTNULL_1709038409863023" NOT NULL ENABLE,
  CONSTRAINT "PK_T_INPAYMENT" PRIMARY KEY ("NO"),
  CONSTRAINT "FK_T_INPAYMENT" FOREIGN KEY ("SALE_TYPE") REFERENCES "T1"("SALE_TYPE") ,
  CONSTRAINT "U_T_INPAYMENT" UNIQUE ("INSU_MIDNO", "SALE_TYPE")
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0

恢复出来的租户丢掉了外键约束。

obclient [SYS]> select * from oceanbase.DBA_CONSTRAINTS where CONSTRAINT_NAME='FK_T_INPAYM
ENT'\G
Empty set (0.003 sec)

关键诊断信息

触发条件

存在外键的表并且在备份恢复后在新租户读取相应视图。

问题原因

由于 __all_foreign_key 表的 ref_cst_id 字段编码了 tenant_id，但是并没有加到 columns_with_tenant_id 里，物理备份恢复后，tenant_id 发生变化，DBA_CONSTRAINTS 视图定义的连接条件中使用了 ref_cst_id = table_id， 在物理恢复后该条件无法匹配上导致视图展示的结果缺失。

问题的风险及影响

查询结果丢失。

影响租户

影响 OceanBase 数据库中的 SYS 租户和 Oracle 租户以及 MySQL 租户。

适用版本

OceanBase 数据库 V3.2.x 版本。

解决方法

修改视图的查询语句：

1. 直接在 ALL_VIRTUAL_FOREIGN_KEY_REAL_AGENT 查找对应的外键约束。

2. 如果要查 DBA_CONSTRAINTS 将要用到的 ref_cst_id 改为 ref_cst_id & 4294967295，即取 ref_cst_id 的低 32 位数。

    SELECT
        CAST(C.DATABASE_NAME AS VARCHAR2(128)) AS OWNER,
        CAST(SUBSTR(A.TABLE_NAME, 7 + INSTR(SUBSTR(A.TABLE_NAME, 7), '_')) AS VARCHAR2(128)) AS CONSTRAINT_NAME,
        CAST('U' AS VARCHAR2(1)) AS CONSTRAINT_TYPE,
        CAST(B.TABLE_NAME AS VARCHAR2(128)) AS TABLE_NAME,
        CAST(NULL AS VARCHAR2(4000)) AS SEARCH_CONDITION,
        CAST(NULL AS VARCHAR2(128)) AS R_OWNER,
        CAST(NULL AS VARCHAR2(128)) AS R_CONSTRAINT_NAME,
        CAST(NULL AS VARCHAR2(9)) AS DELETE_RULE,
        CAST('ENABLED' AS VARCHAR2(8)) AS STATUS,
        CAST('NOT DEFERRABLE' AS VARCHAR2(14)) AS DEFERRABLE,
        CAST('IMMEDIATE' AS VARCHAR2(9)) AS DEFERRED,
        CAST('VALIDATED' AS VARCHAR2(13)) AS VALIDATED,
        CAST(NULL AS VARCHAR2(14)) AS "GENERATED",
        CAST(NULL AS VARCHAR2(3)) AS BAD,
        CAST(NULL AS VARCHAR2(4)) AS RELY,
        CAST(NULL AS DATE) AS LAST_CHANGE,
        CAST(C.DATABASE_NAME AS VARCHAR2(128)) AS INDEX_OWNER,
        CAST(SUBSTR(A.TABLE_NAME, 7 + INSTR(SUBSTR(A.TABLE_NAME, 7), '_')) AS VARCHAR2(128)) AS INDEX_NAME,
        CAST(NULL AS VARCHAR2(7)) AS INVALID,
        CAST(NULL AS VARCHAR2(14)) AS VIEW_RELATED
        FROM SYS.ALL_VIRTUAL_TABLE_REAL_AGENT A, SYS.ALL_VIRTUAL_TABLE_REAL_AGENT B, SYS.ALL_VIRTUAL_DATABASE_REAL_AGENT C WHERE A.DATA_TABLE_ID = B.TABLE_ID AND A.DATABASE_ID = C.DATABASE_ID
          AND A.INDEX_TYPE IN (2, 4, 8) AND C.DATABASE_NAME != '__recyclebin'
          AND A.TENANT_ID = EFFECTIVE_TENANT_ID()
          AND B.TENANT_ID = EFFECTIVE_TENANT_ID()
          AND C.TENANT_ID = EFFECTIVE_TENANT_ID()
        UNION ALL
        SELECT
        CAST(C.DATABASE_NAME AS VARCHAR2(128)) AS OWNER,
        CAST(A.FOREIGN_KEY_NAME AS VARCHAR2(128)) AS CONSTRAINT_NAME,
        CAST('R' AS VARCHAR2(1)) AS CONSTRAINT_TYPE,
        CAST(B.TABLE_NAME AS VARCHAR2(128)) AS TABLE_NAME,
        CAST(NULL AS VARCHAR2(4000)) AS SEARCH_CONDITION,
        CAST(C.DATABASE_NAME AS VARCHAR2(128)) AS R_OWNER,
        CAST(NULL AS VARCHAR2(128)) AS R_CONSTRAINT_NAME,
        CAST(CASE WHEN DELETE_ACTION = 1 THEN 'NO ACTION'
            WHEN DELETE_ACTION = 2 THEN 'CASCADE'
          ELSE 'SET NULL' END AS VARCHAR2(9)) AS DELETE_RULE,
        CASE WHEN A.ENABLE_FLAG = 1 THEN CAST('ENABLED' AS VARCHAR2(8))
            ELSE CAST('DISABLED' AS VARCHAR2(8)) END AS STATUS,
        CAST('NOT DEFERRABLE' AS VARCHAR2(14)) AS DEFERRABLE,
        CAST('IMMEDIATE' AS VARCHAR2(9)) AS DEFERRED,
        CASE WHEN A.VALIDATE_FLAG = 1 THEN CAST('VALIDATED' AS VARCHAR2(13))
            ELSE CAST('NOT VALIDATED' AS VARCHAR2(13)) END AS VALIDATED,
        CAST(NULL AS VARCHAR2(14)) AS "GENERATED",
        CAST(NULL AS VARCHAR2(3)) AS BAD,
        CASE WHEN A.RELY_FLAG = 1 THEN CAST('RELY' AS VARCHAR2(4))
            ELSE CAST(NULL AS VARCHAR2(4)) END AS RELY,
        CAST(NULL AS DATE) AS LAST_CHANGE,
        CAST(NULL AS VARCHAR2(128)) AS INDEX_OWNER,
        CAST(NULL AS VARCHAR2(128)) AS INDEX_NAME,
        CAST(NULL AS VARCHAR2(7)) AS INVALID,
        CAST(NULL AS VARCHAR2(14)) AS VIEW_RELATED
        FROM SYS.ALL_VIRTUAL_FOREIGN_KEY_REAL_AGENT A, SYS.ALL_VIRTUAL_TABLE_REAL_AGENT B, SYS.ALL_VIRTUAL_DATABASE_REAL_AGENT C
        WHERE A.CHILD_TABLE_ID = B.TABLE_ID AND B.DATABASE_ID = C.DATABASE_ID AND A.REF_CST_TYPE = 0 AND A.REF_CST_ID = -1
          AND A.TENANT_ID = EFFECTIVE_TENANT_ID()
          AND B.TENANT_ID = EFFECTIVE_TENANT_ID()
          AND C.TENANT_ID = EFFECTIVE_TENANT_ID()
        UNION ALL
        SELECT
        CAST(C.DATABASE_NAME AS VARCHAR2(128)) AS OWNER,
        CAST(A.FOREIGN_KEY_NAME AS VARCHAR2(128)) AS CONSTRAINT_NAME,
        CAST('R' AS VARCHAR2(1)) AS CONSTRAINT_TYPE,
        CAST(B.TABLE_NAME AS VARCHAR2(128)) AS TABLE_NAME,
        CAST(NULL AS VARCHAR2(4000)) AS SEARCH_CONDITION,
        CAST(E.DATABASE_NAME AS VARCHAR2(128)) AS R_OWNER,
        CAST(CASE WHEN A.REF_CST_TYPE = 2 THEN SUBSTR(F.TABLE_NAME, 7 + INSTR(SUBSTR(F.TABLE_NAME, 7), '_'))
            ELSE NULL END AS VARCHAR2(128)) AS R_CONSTRAINT_NAME,
        CAST(CASE WHEN DELETE_ACTION = 1 THEN 'NO ACTION'
            WHEN DELETE_ACTION = 2 THEN 'CASCADE'
            ELSE 'SET NULL' END AS VARCHAR2(9)) AS DELETE_RULE,
        CASE WHEN A.ENABLE_FLAG = 1 THEN CAST('ENABLED' AS VARCHAR2(8))
            ELSE CAST('DISABLED' AS VARCHAR2(8)) END AS STATUS,
        CAST('NOT DEFERRABLE' AS VARCHAR2(14)) AS DEFERRABLE,
        CAST('IMMEDIATE' AS VARCHAR2(9)) AS DEFERRED,
        CASE WHEN A.VALIDATE_FLAG = 1 THEN CAST('VALIDATED' AS VARCHAR2(13))
            ELSE CAST('NOT VALIDATED' AS VARCHAR2(13)) END AS VALIDATED,
        CAST(NULL AS VARCHAR2(14)) AS "GENERATED",
        CAST(NULL AS VARCHAR2(3)) AS BAD,
        CASE WHEN A.RELY_FLAG = 1 THEN CAST('RELY' AS VARCHAR2(4))
            ELSE CAST(NULL AS VARCHAR2(4)) END AS RELY,
        CAST(NULL AS DATE) AS LAST_CHANGE,
        CAST(NULL AS VARCHAR2(128)) AS INDEX_OWNER,
        CAST(NULL AS VARCHAR2(128)) AS INDEX_NAME,
        CAST(NULL AS VARCHAR2(7)) AS INVALID,
        CAST(NULL AS VARCHAR2(14)) AS VIEW_RELATED
        FROM SYS.ALL_VIRTUAL_FOREIGN_KEY_REAL_AGENT A, SYS.ALL_VIRTUAL_TABLE_REAL_AGENT B, SYS.ALL_VIRTUAL_DATABASE_REAL_AGENT C, SYS.ALL_VIRTUAL_TABLE_REAL_AGENT D, SYS.ALL_VIRTUAL_DATABASE_REAL_AGENT E, SYS.ALL_VIRTUAL_TABLE_REAL_AGENT F
          WHERE A.CHILD_TABLE_ID = B.TABLE_ID AND B.DATABASE_ID = C.DATABASE_ID AND A.PARENT_TABLE_ID = D.TABLE_ID AND D.DATABASE_ID = E.DATABASE_ID AND (A.REF_CST_ID & 4294967295 = F.TABLE_ID AND A.REF_CST_TYPE = 2)
          AND A.TENANT_ID = EFFECTIVE_TENANT_ID()
          AND B.TENANT_ID = EFFECTIVE_TENANT_ID()
          AND C.TENANT_ID = EFFECTIVE_TENANT_ID()
          AND D.TENANT_ID = EFFECTIVE_TENANT_ID()
          AND E.TENANT_ID = EFFECTIVE_TENANT_ID()
          AND F.TENANT_ID = EFFECTIVE_TENANT_ID()
        UNION ALL
        SELECT
        CAST(C.DATABASE_NAME AS VARCHAR2(128)) AS OWNER,
        CAST(A.FOREIGN_KEY_NAME AS VARCHAR2(128)) AS CONSTRAINT_NAME,
        CAST('R' AS VARCHAR2(1)) AS CONSTRAINT_TYPE,
        CAST(B.TABLE_NAME AS VARCHAR2(128)) AS TABLE_NAME,
        CAST(NULL AS VARCHAR2(4000)) AS SEARCH_CONDITION,
        CAST(E.DATABASE_NAME AS VARCHAR2(128)) AS R_OWNER,
        CAST(CASE WHEN A.REF_CST_TYPE = 1 THEN F.CONSTRAINT_NAME
            ELSE NULL END AS VARCHAR2(128)) AS R_CONSTRAINT_NAME,
        CAST(CASE WHEN DELETE_ACTION = 1 THEN 'NO ACTION'
            WHEN DELETE_ACTION = 2 THEN 'CASCADE'
            ELSE 'SET NULL' END AS VARCHAR2(9)) AS DELETE_RULE,
        CASE WHEN A.ENABLE_FLAG = 1 THEN CAST('ENABLED' AS VARCHAR2(8))
            ELSE CAST('DISABLED' AS VARCHAR2(8)) END AS STATUS,
        CAST('NOT DEFERRABLE' AS VARCHAR2(14)) AS DEFERRABLE,
        CAST('IMMEDIATE' AS VARCHAR2(9)) AS DEFERRED,
        CASE WHEN A.VALIDATE_FLAG = 1 THEN CAST('VALIDATED' AS VARCHAR2(13))
            ELSE CAST('NOT VALIDATED' AS VARCHAR2(13)) END AS VALIDATED,
        CAST(NULL AS VARCHAR2(14)) AS "GENERATED",
        CAST(NULL AS VARCHAR2(3)) AS BAD,
        CASE WHEN A.RELY_FLAG = 1 THEN CAST('RELY' AS VARCHAR2(4))
            ELSE CAST(NULL AS VARCHAR2(4)) END AS RELY,
        CAST(NULL AS DATE) AS LAST_CHANGE,
        CAST(NULL AS VARCHAR2(128)) AS INDEX_OWNER,
        CAST(NULL AS VARCHAR2(128)) AS INDEX_NAME,
        CAST(NULL AS VARCHAR2(7)) AS INVALID,
        CAST(NULL AS VARCHAR2(14)) AS VIEW_RELATED
        FROM SYS.ALL_VIRTUAL_FOREIGN_KEY_REAL_AGENT A, SYS.ALL_VIRTUAL_TABLE_REAL_AGENT B, SYS.ALL_VIRTUAL_DATABASE_REAL_AGENT C, SYS.ALL_VIRTUAL_TABLE_REAL_AGENT D, SYS.ALL_VIRTUAL_DATABASE_REAL_AGENT E, SYS.ALL_VIRTUAL_CONSTRAINT_REAL_AGENT F
          WHERE A.CHILD_TABLE_ID = B.TABLE_ID AND B.DATABASE_ID = C.DATABASE_ID AND A.PARENT_TABLE_ID = D.TABLE_ID AND D.DATABASE_ID = E.DATABASE_ID AND (A.PARENT_TABLE_ID = F.TABLE_ID AND A.REF_CST_TYPE = 1 AND F.CONSTRAINT_TYPE = 1 AND A.REF_CST_ID = F.CONSTRAINT_ID)
          AND A.TENANT_ID = EFFECTIVE_TENANT_ID()
          AND B.TENANT_ID = EFFECTIVE_TENANT_ID()
          AND C.TENANT_ID = EFFECTIVE_TENANT_ID()
          AND D.TENANT_ID = EFFECTIVE_TENANT_ID()
          AND E.TENANT_ID = EFFECTIVE_TENANT_ID()
          AND F.TENANT_ID = EFFECTIVE_TENANT_ID()
        UNION ALL
        SELECT
          CAST(C.DATABASE_NAME AS VARCHAR2(128)) AS OWNER,
          CAST(CONSTRAINT_NAME AS VARCHAR2(128)) AS CONSTRAINT_NAME,
          CASE WHEN A.CONSTRAINT_TYPE = 1 THEN CAST('P' AS VARCHAR2(1))
            ELSE CAST('C' AS VARCHAR2(1)) END AS CONSTRAINT_TYPE,
          CAST(B.TABLE_NAME AS VARCHAR2(128)) AS TABLE_NAME,
          CASE WHEN A.CONSTRAINT_TYPE = 1 THEN CAST(NULL AS VARCHAR2(4000))
            ELSE CAST(A.CHECK_EXPR AS VARCHAR2(4000)) END AS SEARCH_CONDITION,
          CAST(NULL AS VARCHAR2(128)) AS R_OWNER,
          CAST(NULL AS VARCHAR2(128)) AS R_CONSTRAINT_NAME,
          CAST(NULL AS VARCHAR2(9)) AS DELETE_RULE,
          CASE WHEN A.ENABLE_FLAG = 1 THEN CAST('ENABLED' AS VARCHAR2(8))
            ELSE CAST('DISABLED' AS VARCHAR2(8)) END AS STATUS,
          CAST('NOT DEFERRABLE' AS VARCHAR2(14)) AS DEFERRABLE,
          CAST('IMMEDIATE' AS VARCHAR2(9)) AS DEFERRED,
          CASE WHEN A.VALIDATE_FLAG = 1 THEN CAST('VALIDATED' AS VARCHAR2(13))
            ELSE CAST('NOT VALIDATED' AS VARCHAR2(13)) END AS VALIDATED,
          CAST(NULL AS VARCHAR2(14)) AS "GENERATED",
          CAST(NULL AS VARCHAR2(3)) AS BAD,
          CASE WHEN A.RELY_FLAG = 1 THEN CAST('RELY' AS VARCHAR2(4))
            ELSE CAST(NULL AS VARCHAR2(4)) END AS RELY,
          CAST(NULL AS DATE) AS LAST_CHANGE,
          CAST(C.DATABASE_NAME AS VARCHAR2(128)) AS INDEX_OWNER,
          CASE WHEN A.CONSTRAINT_TYPE = 1 THEN CAST(A.CONSTRAINT_NAME AS VARCHAR2(128))
            ELSE CAST(NULL AS VARCHAR2(128)) END AS INDEX_NAME,
          CAST(NULL AS VARCHAR2(7)) AS INVALID,
          CAST(NULL AS VARCHAR2(14)) AS VIEW_RELATED
        FROM SYS.ALL_VIRTUAL_CONSTRAINT_REAL_AGENT A, SYS.ALL_VIRTUAL_TABLE_REAL_AGENT B, SYS.ALL_VIRTUAL_DATABASE_REAL_AGENT C
        WHERE A.TABLE_ID = B.TABLE_ID AND B.DATABASE_ID = C.DATABASE_ID AND C.DATABASE_NAME != '__recyclebin'
        AND A.TENANT_ID = EFFECTIVE_TENANT_ID()
        AND B.TENANT_ID = EFFECTIVE_TENANT_ID()
        AND C.TENANT_ID = EFFECTIVE_TENANT_ID()
   

规避方式

无。