Compatibility Issues

Oracle can be set to use a broad range of version settings, controlled by the compatible setting. Normally upon install this is set to the current version of the database that’s been installed. There may be times, however, when a lower compatibility setting is warranted, possibly for performance reasons. Let’s look at an example where a fairly recent version of Oracle (19c) is configured to use 11.2.0.4 compatibility.

The compatible setting does several things:

• sets datafile compatibility
• sets character limits based on the version
  ( can change the datafile headers
• enables version-specific features based upon structures available in the listed version

When a database is started compatibility is checked and, if necessary, changes are made to bring any datafiles from an older release up to specification for the version configured. Because of this compatibility can never be set to a lower version than specified in the version-specific documentation. When upgrading an 11.2.0.4 database compatibility can remain at 11.2.0.4 to allow prior backups to be used in the event a database restore is required. Once a database is set to a higher compatibility the changes made are not reversible.

Compatibility and optimizer features are different. The optimizer_features_enable parameter sets the optimizer version of the database engine and can be changed at any time to any valid version. Knowing that information it should seem logical that a 19.3.0.0 dsatabase cab be set to 11.2.0.4 compatibility AND 19.1.0 for the optimizer features. The optimizer setting enables execution pathways available to the version setting. Execution steps like index skip scan can be enabled even though the database’s physical compatibility is set to 11.2.0.4. This can create an interesting situation with Data Pump.

With the 11.2.0.4 compatibility file and object names may be limited to 35 characters. This is certainly the case with Data Pump. Job names, associated table names are generated using a sequence, and as that sequence increases so can the length of the file/job/object names. The sequence in question is AQ$_KUPC$DATAPUMP_QUETAB_1_N, owned by SYS. As this sequences increases the names generated become longer until they cross that 35 character threshold. At that point (the sequence value is greater than 9999) errors get thrown:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
ORA-31626: job does not exist
ORA-31638: cannot attach to job SYS_IMPORT_FULL_01 for user xxxx
ORA-06512: at “SYS.KUPV$FT”, line 1142
ORA-06512: at “SYS.KUPV$FT”, line 1744
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPV$FT_INT”, line 498
ORA-39077: unable to subscribe agent KUPC$A_1_104552960533000 to queue “KUPC$C_1_20200218104552_0”
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPC$QUE_INT”, line 294
ORA-00972: identifier is too long
ORA-06512: at “SYS.DBMS_AQADM_SYS”, line 9006
ORA-06512: at “SYS.DBMS_PRVTAQIS”, line 1558
ORA-06512: at “SYS.DBMS_PRVTAQIS”, line 3431
ORA-06512: at “SYS.DBMS_RULE_ADM”, line 296
ORA-06512: at “SYS.DBMS_RULEADM_INTERNAL”, line 106
ORA-24000: invalid value “SYS”.”KUPC$C_1_20200218104552_0$10016″, RULE SET should be of the form [SCHEMA.]NAME
ORA-00972: identifier is too long
ORA-06512: at “SYS.DBMS_RULEADM_INTERNAL”, line 109
ORA-06512: at “SYS.DBMS_RULEADM_INTERNAL”, line 97
ORA-06512: at “SYS.DBMS_RULE_ADM”, line 290
ORA-06512: at “SYS.DBMS_PRVTAQIS”, line 3386
ORA-06512: at “SYS.DBMS_PR

and data pump exits unsuccessfully.

One solution to this issue is to set compatible to match the database version. An excellent option if performance is not impacted. However, if the compatibility is set to 11.2.0.4 to retain the performance profile of the database prior to upgrade, this is not a viable solution. Fear not, help is at hand.

It is possible to rebuild the sequence and the associated pieces using scripts provided by Oracle, used to create these objects at database creation:

@$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

The first four scripts drop and recreate the various Data Pump objects, with the final script performing a recompile of the objects to address any that may have been invalidated when the previous objects were dropped. Note that the sequence in question is NOT recreated by these scripts. That sequence wil; be recreated once either expdp or impdp is called. This, of course, resets the sequence to 0 and provides shorter file/job/object names that the 11.2.0.4 structures can handle.

If this issue affects an installation is would be prudent to check the sequence value periodically. The following PL/SQL code can be used:

set serveroutput on size unlimited

declare
cursor get_seq_val is
select last_number
from dba_sequences
where sequence_name = ‘AQ$_KUPC$DATAPUMP_QUETAB_1_N’;

    v_curr_val      number:=0;
    v_sql           varchar2(400);

begin
open get_seq_val;
fetch get_Seq_val into v_curr_val;
close get_seq_val;
dbms_output.put_line(‘Current value is: ‘||v_curr_val);

    if v_curr_val > 9999 then
            dbms_output.put_line('Sequence value too large, rebuild.');
    end if;

end;
/

Creating another script that executes the rebuild scripts would make it easier to fix the sequence size issue. A reference to that script could be output instead of the generic message shown above. Please note the database does NOT need to be in upgrade mode or restarted to execute the rebuild scripts.

It should be noted that this situation can be created when Data Pump utilities are run frequently, and may only occur in development and/or test environments. It would be wise to set up a script to return the current sequence value so it can be checked occasionally to ensure it is not nearing the magic limit of 10000 (if compatible for the database is set to 11.2.x.a).

Sometimes the most unexpected things can throw a monkey wrench into the works. Forewarned is forearmed, as they say, and this should provide enough warning to allow the DBA to avert disaster with Data Pump. Keep a watchful eye on the current sequence value and Data Pump should stay working regardless of how often is is used. Best to be safe rather than sorry.

Unless being awakened in the middle of the night is the goal.