ORA-14063: Unusable index exists on unique/primary constraint key

##########################
## Error
##########################

ORA-14063: Unusable index exists on unique/primary constraint key

### Full Error

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
ORA-39083: Object type CONSTRAINT failed to create with error:
ORA-14063: Unusable index exists on unique/primary constraint key
Failing sql is:
ALTER TABLE "ABC_OWNER"."ABC_TABLE" ADD CONSTRAINT "ABC_TABLE_PK" PRIMARY KEY ("RULE_NAME") USING INDEX "ABC_OWNER"."ABC_TABLE_PK"  ENABLE
ORA-39083: Object type CONSTRAINT failed to create with error:
ORA-14063: Unusable index exists on unique/primary constraint key

##########################
#  Error Occurred
##########################

Error occured while doing a full import in 11.2.0.4.0 version

##########################
## Command Executed
##########################

Schema level import command.

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check index status in Source Database
#########################################

#### Check the indexes which are not VALID in Source Database.

SET LINES 200
col owner for a15
col index_type for a15
col tablespace_name for a15
col table_owner for a15

Select owner,index_name,index_type,table_owner,table_name,table_type,tablespace_name,status,LAST_ANALYZED from dba_indexes where STATUS='UNUSABLE';

OWNER           INDEX_NAME                     INDEX_TYPE      TABLE_OWNER     TABLE_NAME                     TABLE_TYPE  TABLESPACE_NAME STATUS     LAST_ANAL
--------------- ------------------------------ --------------- --------------- ------------------------------ ----------- --------------- ---------- ---------
ABC_OWNER       ABC_TABLE_PK               NORMAL          ABC_OWNER       ABC_TABLE                  TABLE       IDX_TS1    UNUSABLE

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

Error is occuring because in the source database itself the indexes are in UNUSABLE state, so when we are trying to import in target database it fails to get created.

=====================================================================================================================

##########################
## Solution
##########################

Rebuild all the UNUSABLE indexes in source database and then perform an export in source and import in target.

@@@@ Rebuild Indexes

set pages 100
select 'Alter index ' || owner||'.' || index_name ||' rebuild online parallel 8;' from dba_indexes where STATUS='UNUSABLE';

'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILDONLINEPARALLEL8;'
----------------------------------------------------------------------------------------------------
Alter index ABC_OWNER.ABC_TABLE_PK rebuild online parallel 8;

=====================================================================================================================
After this import worked good.
=====================================================================================================================

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

ORA-00990: missing or invalid privilege, GRANT GLOBAL REWRITE TO "ABC_OWNER"

##########################
## Error
##########################

ORA-00990: missing or invalid privilege
Failing sql is:
GRANT GLOBAL REWRITE TO "ABC_OWNER"

### Full Error

ORA-39083: Object type SYSTEM_GRANT failed to create with error:
ORA-00990: missing or invalid privilege
Failing sql is:
GRANT GLOBAL REWRITE TO "ABC_OWNER"

##########################
#  Error Occurred
##########################

Error occured while import of database in 11.2.0.4.0 version

##########################
## Command Executed
##########################

Schema level import...

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check Database registry
#########################################

set lines 200
col action_time for a30
col action for a25
col namespace for a9
col version for a10
col id for 99999999
col comments for a25
col bundle_series for a25
select * from registry$history;

ACTION_TIME                    ACTION                    NAMESPACE VERSION           ID COMMENTS                  BUNDLE_SERIES
------------------------------ ------------------------- --------- ---------- --------- ------------------------- -------------------------
22-JAN-07 04.03.57.000000 PM   APPLY                                            5689799 CPUJan2007
13-AUG-08 11.42.39.793310 AM   UPGRADE                   SERVER    10.2.0.3.0           Upgraded from 8.1.7.0.0
29-JAN-09 08.23.06.616350 PM   CPU                       SERVER    10.2.0.3.0   7592354 CPUJan2009
27-MAY-09 10.15.51.127263 PM   APPLY                     SERVER    10.2.0.4           4 CPUApr2009                CPU
27-MAY-09 12.56.09.358348 AM   UPGRADE                   SERVER    10.2.0.4.0           Upgraded from 10.2.0.3.0
01-AUG-09 04.06.44.638446 PM   APPLY                     SERVER    10.2.0.4           5 CPUJul2009                CPU
31-OCT-09 05.23.10.775509 PM   APPLY                     SERVER    10.2.0.4           6 CPUOct2009                CPU
24-APR-10 03.34.35.916439 PM   APPLY                     SERVER    10.2.0.4           7 CPUApr2010                CPU
27-JUL-10 07.47.22.432114 PM   APPLY                     SERVER    10.2.0.4           8 CPUJul2010                CPU
14-NOV-10 08.56.47.965186 AM   APPLY                     SERVER    10.2.0.4           9 CPUOct2010                CPU
14-NOV-10 08.59.00.083118 AM   CPU                                              6452863 view recompilation
30-JAN-11 07.49.41.612062 AM   APPLY                     SERVER    10.2.0.4          10 CPUJan2011                CPU
20-MAY-11 11.13.20.184868 AM   APPLY                     SERVER    10.2.0.4          11 CPUApr2011                CPU
10-NOV-11 06.31.40.801048 PM   VIEW RECOMPILE                                   8289601 view recompilation
10-NOV-11 06.31.40.830187 PM   UPGRADE                   SERVER    10.2.0.5.0           Upgraded from 10.2.0.4.0
24-JAN-12 02.57.57.932675 PM   APPLY                     SERVER    10.2.0.5           6 CPUJan2012                CPU
24-APR-12 07.11.15.922326 PM   APPLY                     SERVER    10.2.0.5           7 CPUApr2012                CPU
24-JUL-12 09.14.33.976507 PM   APPLY                     SERVER    10.2.0.5           8 CPUJul2012                CPU
23-OCT-12 02.30.22.601483 PM   APPLY                     SERVER    10.2.0.5           9 CPUOct2012                CPU
24-JAN-13 01.22.55.748863 PM   APPLY                     SERVER    10.2.0.5          10 CPUJan2013                CPU
24-APR-13 11.05.11.000911 AM   APPLY                     SERVER    10.2.0.5          11 CPUApr2013                CPU
24-JUL-13 05.15.10.280957 PM   APPLY                     SERVER    10.2.0.5          12 CPUJul2013                CPU
01-NOV-13 04.54.12.078568 PM   APPLY                     SERVER    10.2.0.5          12 CPUJul2013                CPU
10-SEP-14 12.44.10.156902 PM   VIEW INVALIDATE                                  8289601 view invalidation
10-SEP-14 12.44.28.942689 PM   UPGRADE                   SERVER    11.2.0.4.0           Upgraded from 10.2.0.5.0
10-SEP-14 12.51.57.396320 PM   APPLY                     SERVER    11.2.0.4           0 Patchset 11.2.0.2.0       PSU
18-OCT-14 10.26.17.616975 AM   APPLY                     SERVER    11.2.0.4           4 CPUOct2014                CPU
03-FEB-15 10.20.24.622435 PM   APPLY                     SERVER    11.2.0.4           5 CPUJan2015                CPU
18-APR-15 10.59.02.947126 PM   APPLY                     SERVER    11.2.0.4           6 CPUApr2015                CPU
18-JUL-15 10.21.02.380054 PM   APPLY                     SERVER    11.2.0.4           7 CPUJul2015                CPU
24-OCT-15 10.47.20.282415 PM   APPLY                     SERVER    11.2.0.4           8 CPUOct2015                CPU
22-JAN-16 09.47.02.078327 PM   APPLY                     SERVER    11.2.0.4      160119 CPUJan2016                CPU
Elapsed: 00:00:00.04
12:33:16 SQL>

=====================================================================================================================

#########################################
# 2) Check Database System Privilege
#########################################

Select * from SYSTEM_PRIVILEGE_MAP where name like '%REWRITE%' order by name;

13:20:42 SQL> Select * from SYSTEM_PRIVILEGE_MAP where name like '%REWRITE%' order by name;
 PRIVILEGE NAME                                       PROPERTY
---------- ---------------------------------------- ----------
      -211 GLOBAL REWRITE                                    0
      -210 QUERY REWRITE                                     0

=====================================================================================================================

#########################################
# 3) Reason for Failure
#########################################

From Step 1), we can see that Database was upgraded from 8.1.7 version.
From Step 2), System privilege name is "GLOBAL REWRITE".

The GLOBAL REWRITE and REWRITE privileges were introduced in v8.1.3.  Then the privileges were renamed to GLOBAL QUERY REWRITE and QUERY REWRITE in v8.1.5.

The upgrade script does update both system privilege names when upgrading the database to v8.1.5.  So, the database was probably upgraded without this step being completed.

=====================================================================================================================

##########################
## Solution
##########################

#### Solution would be to update the rows with the new privilege in Source Database

update SYSTEM_PRIVILEGE_MAP
 set name='QUERY REWRITE' where name='REWRITE';

update SYSTEM_PRIVILEGE_MAP
 set name='GLOBAL QUERY REWRITE' where name='GLOBAL REWRITE';

Commit;

=====================================================================================================================
I didnt update the rows in Source because we are migrating the database to 11.2.0.4 which already has the right system privilge name to it.
=====================================================================================================================

Target :

SQL>  Select * from SYSTEM_PRIVILEGE_MAP where name like '%REWRITE%' order by name;
 PRIVILEGE NAME                                       PROPERTY
---------- ---------------------------------------- ----------
      -211 GLOBAL QUERY REWRITE                              0
      -210 QUERY REWRITE                                     0

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================