Skip to main content

Search

Welcome to Tibero GTS!

Error 7249 When Rebuilding After Index Online Build Failure

Document Type | Troubleshooting

Category | Administration

Applicable Product Versions | 6FS06, 6FS07, 6FS07PS, 7FS02, 7FS02PS

Document Number | TADTS028

 

Issue

Error 7249 occurs when rebuilding after an index online build failure.
Note
TBR-7249: The index is being built or rebuilt.

 

Scenario When Issue Occurs

--Create table, index, and data for issue occurrence
SQL>
DROP TABLE T PURGE;
CREATE TABLE T( C1 NUMBER, C2 VARCHAR2(10), C3 VARCHAR2(20) );

INSERT INTO T
SELECT LEVEL C1
     , '2014'||(LPAD(TRUNC(DBMS_RANDOM.VALUE(1,12)), 2,'0'))||(LPAD(TRUNC(DBMS_RANDOM.VALUE(1,30)), 2,'0')) C2
     , 'TEST'|| LEVEL
FROM DUAL
CONNECT BY LEVEL <= 5000000;
COMMIT;


CREATE INDEX IDX_T ON T(C1, C2);

--Scenario for index rebuild failure causing 7249 error 
--Perform rebuild online and cancel approximately 5 seconds after start (Ctrl+c)
SQL> ALTER INDEX IDX_T REBUILD ONLINE;
^C
Trying to cancel the current statement by user's request.

TBR-12040: Statement canceled.

--Reattempt index rebuild โ†’ TBR-7249 error occurs, rebuild fails
SQL> ALTER INDEX IDX_T REBUILD ONLINE;
TBR-7249: Index 'IDX_T' is being built or rebuilt.

 

 

Cause

The journal table remains without being deleted when the index online build fails, causing error 7249.

Query Objects Subject to DBMS_REPAIR

--Query index objects currently rebuilding
SQL> 
SELECT * FROM SYS._DD_IDXRE;

   OBJ_ID    SGMT_ID PCTFREE_BYTES   INITRANS      FLAGS
---------- ---------- ------------- ---------- ----------
   173705     173708           819          2          0


--Query index objects created with online option
SQL> 
SELECT * FROM SYS._DD_IDXON;

   OBJ_ID    TYPE_NO      FLAGS
---------- ---------- ----------
   173705          1          0LAGS



--Query index names requiring DBMS_REPAIR package execution based on above results
SQL>
SELECT A.OWNER, A.OBJECT_NAME, A.OBJECT_TYPE, C.TABLE_NAME
FROM DBA_OBJECTS A, SYS._DD_IDXRE B, DBA_INDEXES C
WHERE A.OBJECT_ID=B.OBJ_ID
AND A.OBJECT_NAME=C.INDEX_NAME
AND A.OWNER=C.OWNER;

OWNER      OBJECT_NAM OBJECT_TYP TABLE_NAME
---------- ---------- ---------- ------------------------------
TIBERO     IDX_T      INDEX      T
 
 
 

Solutions

Execute the drop procedure to delete the journal table.

1. Execute the journal table deletion script

SQL>
set serveroutput on

DECLARE
cleaned BOOLEAN;

BEGIN
cleaned := false;
WHILE cleaned = false
LOOP
  cleaned := DBMS_REPAIR.ONLINE_INDEX_CLEAN(DBMS_REPAIR.ALL_INDEX_ID,
                                            DBMS_REPAIR.LOCK_WAIT);
END LOOP;

EXCEPTION
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE );
         DBMS_OUTPUT.PUT_LINE('SQL MESSAGE: ' || SQLERRM(SQLCODE));  
END;
/ 

PSM completed.
 

2. Query Index Objects

After running the script, query the index objects that were previously executed.

SQL> SELECT * FROM SYS._DD_IDXRE;

0 row selected.

SQL> SELECT * FROM SYS._DD_IDXON;

0 row selected.
 

3. Perform Index Online Rebuild

Then, perform the index online rebuild and verify it completes successfully.

SQL> ALTER INDEX IDX_T REBUILD ONLINE;

Index 'IDX_T' altered.
Note
Since the DBMS_REPAIR package acquires locks during execution, it is recommended to perform it during periods of low DML load.

 

 

 
 
 
 

Related to