AAI 6.4.6 upgrade error: WARNING: UNABLE TO CREATE INDEX XIE12JobRun - See error below-1652 ORA-01652: unable to extend temp segment by 128 in tablespace AA

Products

• Automation Analytics & Intelligence

Issue/Introduction

When upgrading to AAI 6.4.6, there are a number of database updates that are made, including adding a new index on the JobRun table in the AAI database.

When running the upgradeJawsDbTo6.4.6.sql file against an Oracle database, you may run into the error below if your AAITEMP tablespace is too small

 

WARNING: UNABLE TO CREATE INDEX XIE12JobRun -  See error below-1652 ORA-01652: unable to extend temp segment by 128 in tablespace AA

 

When an index is created in Oracle, it will copy data from the table into a temp tablespace, if the temp tablespace is not large enough for this data, you may hit the error above.

Environment

Release : 6.4.6

Resolution

1. To prevent this from occurring, before upgrading to 6.4.6 or later if you are not already on 6.4.6, please work with your DBA to identify if you have sufficient space in your AAITEMP tablespace to handling adding an index to the AAI JobRun table.  This index will help improve the performance of adding or rebuilding jobstream history. 

 

2. If you have already run into this error, please work with your DBA to increase the AAITEMP tablespace size.

Below is an example of increasing the tablespace to 4GB's, however please work with your DBA to determine the proper sizing.

SQL> alter tablespace AAITEMP add tempfile '/opt/oracle/oradata/AAITEMP_1.db' size 4G ;

Tablespace altered.

 

3. Once the AAITEMP tablespace size is increased, run the part of the upgradeJawsDbTo6.4.6.sql script that creates the index on the JobRun table, which is listed below:

 

SET FEEDBACK ON;
SET SERVEROUT ON;
DECLARE
    v_code NUMBER;
    v_errm VARCHAR2(64);
BEGIN
    EXECUTE IMMEDIATE 'CREATE INDEX XIE12JobRun ON JobRun (jobId ASC, startTime ASC, jobRunId ASC, parentJobRunId ASC, runningTime, endTime, lastJobStateType, exitCode, extRunId, numberRetrys, latency1, latency2, latency3, latency4, jawsNumberRetrys, machine, triggerName, jobRunContextId, isFutureRun, lastUpdatedTime, statusCode, runType, entryMode, planDate, scheduledTime, lastNativeJobStateType, triggerId, jobSchedulerId, activatedTime, nativeExecutionId) TABLESPACE AAIINDICES';

    EXCEPTION
        WHEN OTHERS THEN
        -- index name may already be in use, or an existing index may be on the same columns with a different name
            v_code := SQLCODE;
            v_errm := SUBSTR(SQLERRM, 1, 64);      
            DBMS_OUTPUT.put_line ('WARNING: UNABLE TO CREATE INDEX XIE12JobRun -  See error below');
            DBMS_OUTPUT.put_line (v_code || ' ' || v_errm);
END;
/

 

4. Note that this step can take a while depending on the size your JobRun table, please do not interrupt the script from executing.

 

5. Once complete, please verify with your DBA that all other changes are applied to your database that are within the upgradeJawsDbTo6.4.6.sql file.

 

6. Once you have verified all updates have completed, your AAI upgrade is complete.