ANBOB™

这是一个经常执行expdp做表级备份的数据库，版本Oracle 11.2.0.4 2nodes RAC，在12.2之前存在一个bug就是expdp调用的内部sequence在达到6位数后而导致的失败ORA-31638 \ ORA-39077\ORA-06502 ，bug修复后是增加了当此sequence达到6位数后drop并自动re-create。 下面记录一下这个问题。下一篇我会分享这个库同样expdp导致的另一个问题。

SQL> @dirs

DIRECTORY_OWNER                DIRECTORY_NAME                           DIRECTORY_PATH
------------------------------ ---------------------------------------- ------------------------------------------------------------------------------------------
SYS                            DUMPBACK3                                /anbob/orafile/backup_tmp

SQL> create table system.t1 as select * from dba_tables where rownum<=10; 
Table created. 
oracle@kdhd1:/home/oracle>expdp \'\/ as sysdba\' directory=DUMPBACK3 tables=system.t1 dumpfile=t1.dump
Export: Release 11.2.0.4.0 - Production on Tue May 12 10:37:46 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31638: cannot attach to job SYS_EXPORT_TABLE_01 for user SYS
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 428
ORA-39077: unable to subscribe agent KUPC$A_1_103747201940000 to queue "KUPC$C_1_20200512103746"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 254
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

SQL> @seq datapump

SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS                            AQ$_KUPC$DATAPUMP_QUETAB_N              1 1.0000E+28            1 N N         20           1
SYS                            AQ$_KUPC$DATAPUMP_QUETAB_2_N            1 1.0000E+28            1 N N         20          41
SYS                            AQ$_KUPC$DATAPUMP_QUETAB_3_N            1 1.0000E+28            1 N N         20          41
SYS                            AQ$_KUPC$DATAPUMP_QUETAB_1_N            1 1.0000E+28            1 N N         20     1000038
SYS                            AQ$_KUPC$DATAPUMP_QUETAB_4_N            1 1.0000E+28            1 N N         20          81

这是因为bug 16928674引起的, 每次运行DATAPUMP作业，Sequence “SYS”.”AQ$_KUPC$DATAPUMP_QUETAB_1_N” 都会增长， 当该序列值超过6位数时就会报上面的错误ORA-31638 \ ORA-39077\ORA-06502 。 安装了补丁后当sequence超过6位数据时，会自动删除并重建。

当前的解决办法，重建datapump对象。无需重启实例

Recreate datapump related catalogs manually by running following scripts as SYS user:

@$ORACLE_HOME/rdbms/admin/catdph.sql
@$ORACLE_HOME/rdbms/admin/prvtdtde.plb
@$ORACLE_HOME/rdbms/admin/catdpb.sql
@$ORACLE_HOME/rdbms/admin/dbmspump.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL> @seq datapump

SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS                            AQ$_KUPC$DATAPUMP_QUETAB_N              1 1.0000E+28            1 N N         20           1
SYS                            AQ$_KUPC$DATAPUMP_QUETAB_1_N            1 1.0000E+28            1 N N         20          21

根据文档档的描述，也可以手动重建sequence作为临时办法。

SQL> drop sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N ;
Sequence dropped.

SQL> create sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N minvalue 1 maxvalue 999999 start with 1 increment by 1 cache 20 cycle;
Sequence created.

另外下载的补丁中包含了对两个package body进行了修改 SYS.KUPC$QUEUE_INT 和 KUPC$QUE_INT， 解密KUPC$QUE_INT对比升级前后发现有以下差异:

FUNCTION PREPARE_QUEUE_TABLE
   RETURN  PLS_INTEGER
IS
  L_C1                  T_CUR_TYPE;
  L_QUE_TABNUM          PLS_INTEGER;
  L_QUE_TABNAM          VARCHAR2(30);
  L_QUE_TABEXISTS       NUMBER;
  L_QUE_TABOWNER        NUMBER;
  L_QUE_STALE_TABNAM    VARCHAR2(30);
  L_SAX_FREE            NUMBER := 0;
  L_SEQNO               NUMBER;
  L_RESET_SEQNO         BOOLEAN := FALSE;
  L_STMT                VARCHAR2(200);
BEGIN
  L_QUE_TABNUM := SYS_CONTEXT('USERENV','INSTANCE');
  DEBUG('Preparing queue table. Selected table number is ' ||
        TO_CHAR(L_QUE_TABNUM));
  L_QUE_TABNAM := QUEUETABNAME (L_QUE_TABNUM);
  DEBUG('Generated queue table name is ' || L_QUE_TABNAM);
  SELECT COUNT(*) INTO L_QUE_TABEXISTS FROM DBA_QUEUE_TABLES WHERE
    OWNER = 'SYS' AND QUEUE_TABLE = L_QUE_TABNAM;
  IF L_QUE_TABEXISTS <> 0 THEN
    L_STMT := 'SELECT "SYS"."AQ$_' || L_QUE_TABNAM || '_N".NEXTVAL FROM ' ||
              'DUAL';
    EXECUTE IMMEDIATE L_STMT INTO L_SEQNO;
    IF L_SEQNO > 999899 THEN
      DEBUG('Detected AQ sequence number about to overflow');
      DEBUG('Dropping queue table (forced) to recreate it.');
      DBMS_AQADM.DROP_QUEUE_TABLE(QUEUE_TABLE => 'SYS.' || L_QUE_TABNAM,
                                  FORCE       => TRUE);
      L_RESET_SEQNO := TRUE;
    END IF;
  END IF;
  IF (L_QUE_TABEXISTS = 0) OR (L_RESET_SEQNO) THEN

打赏

微信扫一扫，打赏作者吧～