Wednesday, October 14, 2015

Tablespace Quota's Missing..


Recently we did migration on one of the database from HP-UX to Linux. Post migration we have provided unlimited quotas on few of its tablespace.

Later some time it came in our compliance tool that user is having UNLIMITED TABLESPACE privilege granted.

So this privilege was revoked and later few days we observed that tablespace quota's were missing.

I've generated a scenario which explains this.

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

Tablespace Quota's Missing

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

Revoke unlimited tablespace from XDB;

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

#########################################
# Reproduce the Issue
#########################################

a) Grant unlimited quota on specific tablespace.

Alter user XDB quota unlimited on USER_DATA_TS01;

SQL> Alter user XDB quota unlimited on USER_DATA_TS01;
User altered.

b) Grant unlimited tablespace privilege to the user.

Grant unlimited tablespace to xdb;

SQL> Grant unlimited tablespace to xdb;
Grant succeeded.
SQL> 

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

#########################################
# Check the Quota
#########################################

SQL> select username,tablespace_name name,decode(greatest(max_bytes, -1),-1,'Unrestricted',
to_char(max_bytes/1024, '999,999,990')) quota, bytes/1024 used from dba_ts_quotas where username in ('XDB') order by 1,2;
  2
USERNAME                       NAME                           QUOTA                           USED
------------------------------ ------------------------------ ------------------------- ----------
XDB                            USER_DATA_TS01                 Unrestricted                   57728

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

#########################################
# Scenario 1)
#########################################

Grant Unlimited Quota on Tablespace and Revoke UNLIMITED TABLESPACE privilege.

SQL> alter user XDB quota unlimited on USER_DATA_TS01;
User altered.
SQL> revoke unlimited tablespace from xdb;
Revoke succeeded.

#### Check Quota

SQL> select username,tablespace_name name,decode(greatest(max_bytes, -1),-1,'Unrestricted',
to_char(max_bytes/1024, '999,999,990')) quota, bytes/1024 used from dba_ts_quotas where username in ('XDB') order by 1,2;
  2
no rows selected
SQL> 

#### From this we can understand that revoking unlimited tablespace privilege removes unlimited tablespace quota on any/all tablespace to that user.

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

#########################################
# Scenario 2)
#########################################

Grant Specific(1gB) Quota on Tablespace and REVOKE UNLIMITED TABLESPACE privilege.

SQL>  Alter user  XDB quota 1g on USER_DATA_TS01;
User altered.
SQL> select username,tablespace_name name,decode(greatest(max_bytes, -1),-1,'Unrestricted',
to_char(max_bytes/1024, '999,999,990')) quota, bytes/1024 used from dba_ts_quotas where username in ('XDB') order by 1,2;
  2
USERNAME                       NAME                           QUOTA                           USED
------------------------------ ------------------------------ ------------------------- ----------
XDB                            USER_DATA_TS01                    1,048,576                   57728
SQL> revoke unlimited tablespace from xdb;
Revoke succeeded.

#### Check Quota

SQL> select username,tablespace_name name,decode(greatest(max_bytes, -1),-1,'Unrestricted',
to_char(max_bytes/1024, '999,999,990')) quota, bytes/1024 used from dba_ts_quotas where username in ('XDB') order by 1,2;
  2
no rows selected
SQL> 

#### From this we can understand that revoking unlimited tablespace privilege removes all quota's on all tablespace granted to that user.

=====================================================================================================================
So the bottom line is before revoking unlimited tablespace privilege from the user, make a note of the quota's that user has and then once revoked grant the tablespace quota's back.
=====================================================================================================================



Tuesday, May 26, 2015

ORA-09945: Unable to initialize the audit trail file, PRCD-1222 : Online relocation of database "ORALIN" failed but database was restored to its original state


I was testing the online relocation of RAC One Node database from host08 to host09 server. Command was run from host08 server.

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

ORA-09945: Unable to initialize the audit trail file

### Full Error

[oracle@host08 trace]$ srvctl relocate database -d ORALIN -n host09 -v
Configuration updated to two instances
Online relocation failed, rolling back to original state
Configuration reverted back to one instance
PRCD-1222 : Online relocation of database "ORALIN" failed but database was restored to its original state
PRCD-1129 : Failed to start instance ORALIN_2 for database ORALIN
PRCR-1064 : Failed to start resource ora.ORALIN.db on node host09
CRS-5017: The resource action "ora.ORALIN.db start" encountered the following error:
ORA-09817: Write to audit file failed.
Linux-x86_64 Error: 28: No space left on device
Additional information: 12
ORA-09945: Unable to initialize the audit trail file
Linux-x86_64 Error: 28: No space left on device
. For details refer to "(:CLSN00107:)" in "/ofa/rac/app/oracle/grid/11.2.0.4/log/host09/agent/crsd/oraagent_oracle//oraagent_oracle.log".
CRS-2674: Start of 'ora.ORALIN.db' on 'host09' failed
[oracle@host08 trace]$

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

Error occured while doing a relocation of database instance of a One Node RAC in 11.2.0.4.0 version to a different server

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

srvctl relocate database -d ORALIN -n host09 -v

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

#########################################
# 1) Check the Full Error
#########################################

The above error shows that there is some problem with the audit file creation.

Lets check oragent_oracle.log file in host09 server as the problem ocurred while relocating the instance to host09.

[oracle@host09 audit]$ vi "/ofa/rac/app/oracle/grid/11.2.0.4/log/host09/agent/crsd/oraagent_oracle//oraagent_oracle.log"
[oracle@host09 audit]$
2015-05-26 16:57:41.499: [ USRTHRD][2234124608]{0:1:24} CrsCmd::destroy
2015-05-26 16:57:41.500: [ora.orawin.orawindev.svc][2234124608]{0:1:24} [check] clsnUtils::error Exception type=2 string=
CRS-5017: The resource action "ora.orawin.orawindev.svc check" encountered the following error:
ORA-09817: Write to audit file failed.
Linux-x86_64 Error: 28: No space left on device
Additional information: 12
. For details refer to "(:CLSN00109:)" in "/ofa/rac/app/oracle/grid/11.2.0.4/log/host09/agent/crsd/oraagent_oracle//oraagent_oracle.log".

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

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

Online relocation command showed that error is because of the audit. But we shouldnt take that the audit got filled up for ORALIN database.

From above "oragent_oracle.log" we can see that the error occurred on "ora.orawin.orawindev.svc". So the database in problem is orawin.

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

#########################################
# 3) Check the audit location & Filesystem
#########################################

If we can login to orawin database then check audit location. (Show parameter audit)

In my case, i'm unable to login to the database, so checked the filesystems which reached 100%

[oracle@host09 bin]$ df -h /ofa/oracle_11.2.0.4_home
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_oracle-lv0205
                       20G   19G     0 100% /ofa/oracle_11.2.0.4_home
[oracle@host09 bin]$

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

#########################################
# 4) Check which folder is using more space
#########################################

[oracle@host09 oracle_11.2.0.4_home]$ du -sh *
14G     dbs

[oracle@host09 dbs]$ pwd
/ofa/oracle_11.2.0.4_home/dbs
[oracle@host09 dbs]$ ls arch*
arch1_19_871216596.dbf  arch1_31_871216596.dbf  arch1_43_871216596.dbf  arch1_51_871216596.dbf  arch1_60_871216596.dbf  arch1_70_871216596.dbf
arch1_20_871216596.dbf  arch1_32_871216596.dbf  arch1_44_871216596.dbf  arch1_52_871216596.dbf  arch1_61_871216596.dbf  arch1_71_871216596.dbf
arch1_21_871216596.dbf  arch1_33_871216596.dbf  arch1_45_871216596.dbf  arch1_53_871216596.dbf  arch1_62_871216596.dbf  arch1_72_871216596.dbf
arch1_22_871216596.dbf  arch1_34_871216596.dbf  arch1_46_871216596.dbf  arch1_54_871216596.dbf  arch1_63_871216596.dbf  arch1_73_871216596.dbf
arch1_23_871216596.dbf  arch1_35_871216596.dbf  arch1_47_871216596.dbf  arch1_55_871216596.dbf  arch1_64_871216596.dbf  arch1_74_871216596.dbf
arch1_24_871216596.dbf  arch1_36_871216596.dbf  arch1_4_870194590.dbf   arch1_56_871216596.dbf  arch1_65_871216596.dbf  arch1_75_871216596.dbf
arch1_25_871216596.dbf  arch1_37_871216596.dbf  arch1_4_870252683.dbf   arch1_57_871216596.dbf  arch1_66_871216596.dbf  arch1_76_871216596.dbf
arch1_26_871216596.dbf  arch1_38_871216596.dbf  arch1_4_870864967.dbf   arch1_5_870194590.dbf   arch1_67_871216596.dbf  arch1_77_871216596.dbf
arch1_27_871216596.dbf  arch1_39_871216596.dbf  arch1_4_870965663.dbf   arch1_5_870864967.dbf   arch1_6_870194590.dbf   arch1_78_871216596.dbf
arch1_28_871216596.dbf  arch1_40_871216596.dbf  arch1_48_871216596.dbf  arch1_5_870965663.dbf   arch1_6_870864967.dbf
arch1_29_871216596.dbf  arch1_41_871216596.dbf  arch1_49_871216596.dbf  arch1_58_871216596.dbf  arch1_68_871216596.dbf
arch1_30_871216596.dbf  arch1_42_871216596.dbf  arch1_50_871216596.dbf  arch1_59_871216596.dbf  arch1_69_871216596.dbf
[oracle@host09 dbs]$

=====================================================================================================================
If above we can see that the files are the archivelogs.
=====================================================================================================================

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

Backup and Delete the archive log files. (As this is the development database, i removed it without a backup)

[oracle@host09 dbs]$ rm arch*
[oracle@host09 dbs]$ df -h .
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_oracle-lv0205
                       20G  8.1G   11G  43% /ofa/oracle_11.2.0.4_home
[oracle@host09 dbs]$

=====================================================================================================================
Now database relocation worked fine.
=====================================================================================================================

[oracle@host08 trace]$ srvctl relocate database -d ORALIN -n host09 -v
Configuration updated to two instances
Instance ORALIN_2 started
Services relocated
Waiting for up to 30 minutes for instance ORALIN_1 to stop ...
Instance ORALIN_1 stopped
Configuration updated to one instance
[oracle@host08 trace]$

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



Sunday, March 1, 2015

De-Install Oracle WorkSpace Manager in 11g Database.




We have created a new database using DBCA with custom template option which has installed Oracle WorkSpace Manager in the database.

We don't use it and need to De-Install it. Below simple step can be used to De-Install it.


**************************************** Step By Step Procedure ******************************************************

#########################################
# 1) Check the users present in database..
#########################################

SQL> select username from dba_users where username like '%SYS%';
USERNAME
------------------------------
SYSTEM
SYS
APPQOSSYS
WMSYS

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

#########################################
# 2) Check for Version Tables
#########################################

Before De-Install Oracle WorkSpace manager we should make sure there is no version enabled tables on the database.

SQL> select * from all_wm_versioned_tables;
no rows selected
SQL>

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

#########################################
# 3) Script to De-Install WorkSpace Manager
#########################################

Below script is used to De-Install WorkSpace Manager from the 11g database.

$ORACLE_HOME/rdbms/admin/owmuinst.plb

#### Above Script Drops WMSYS user and its objects from the database which De-Install the Oracle WorkSpace Manager

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

#########################################
# 4) De-Install Oracle WorkSpace Manager
#########################################

[oracle@host01 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 27 17:41:01 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> @/u01/app/oracle/product/11.2.0.3/rdbms/admin/owmuinst.plb
Procedure created.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Procedure dropped.
SQL>

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

#########################################
# 4) Check the users present in database..
#########################################

SQL> select username from dba_users where username like '%SYS%';
USERNAME
------------------------------
SYSTEM
SYS
APPQOSSYS

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

De-Install Oracle Ware House Builder in 11g Database.




We have created a new database using DBCA with custom template option which has installed Oracle Ware House Builder in the database.

We don't use it and need to De-Install it. Below simple step can be used to De-Install it.

**************************************** Step By Step Procedure ******************************************************

#########################################
# 1) Check the users present in database..
#########################################

SQL> select username from dba_users where username like '%SYS%';
USERNAME
------------------------------
SYSTEM
SYS
APPQOSSYS
OWBSYS
OWBSYS_AUDIT

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

#########################################
# 2) Script to De-Install OWB
#########################################

Below script is used to De-Install OWB from the 11g database.

$ORACLE_HOME/owb/UnifiedRepos/clean_owbsys.sql

#### Lets see what is being stored in "clean_owbsys.sql"

[oracle@host01 UnifiedRepos]$ more clean_owbsys.sql
drop user owbsys cascade;
drop user owbsys_audit cascade;
drop role OWB_user;
drop role OWB_DESIGNCENTER_view;
drop role OWB$CLIENT;
-- Bug Fix 6233292:
-- exit
[oracle@host01 UnifiedRepos]$

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

#########################################
# 3) De-Install OWB
#########################################

[oracle@host01 UnifiedRepos]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 27 17:35:45 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> @/u01/app/oracle/product/11.2.0.3/owb/UnifiedRepos/clean_owbsys.sql
User dropped.

User dropped.

Role dropped.

Role dropped.

Role dropped.
SQL>

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

#########################################
# 4) Check the users present in database..
#########################################

SQL> select username from dba_users where username like '%SYS%';
USERNAME
------------------------------
SYSTEM
SYS
APPQOSSYS

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

Wednesday, February 25, 2015

ORA-39065: unexpected master process exception in DISPATCH


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

ORA-39065: unexpected master process exception in DISPATCH

### Full Error

ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-01403: no data found
ORA-39097: Data Pump job encountered unexpected error 100

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

Error occured while trying to run a datapump export for estimate the size of the dumpfile in 11.2.0.4.0 version

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

expdp \'/ as sysdba\' estimate_only=y

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

#########################################
# 1) Reproduce the Error
#########################################

$ expdp \'/ as sysdba\' estimate_only=y
Export: Release 11.2.0.4.0 - Production on Wed Feb 25 16:45:05 2015
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, OLAP, Data Mining and Real Application Testing options
ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-01403: no data found
ORA-39097: Data Pump job encountered unexpected error 100

$

From the error it looks like there is some problem with the library.

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

#########################################
# 2) Check the INVALID Objects
#########################################

#### Lets check if there is any INVALID objects present in SYS schema

SET LINES 200
COL OBJECT_NAME FOR A35
COL OBJECT_TYPE FOR A25
COL Owner FOR A25
Select owner,object_name,object_type,status,created,LAST_DDL_TIME from dba_objects where status='INVALID' and owner='SYS' order by object_name;

OWNER                     OBJECT_NAME                         OBJECT_TYPE               STATUS  CREATED   LAST_DDL_
------------------------- ----------------------------------- ------------------------- ------- --------- ---------
SYS                       DBMS_AQELM                          PACKAGE BODY              INVALID 10-DEC-12 10-DEC-12
                          DBMS_AW_EXP                         PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          DBMS_CMP_INT                        PACKAGE                   INVALID 10-DEC-12 23-FEB-14
                          DBMS_DATAPUMP_UTL                   PACKAGE BODY              INVALID 10-DEC-12 11-JUN-13
                          DBMS_FILE_GROUP                     PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          DBMS_FILE_GROUP_UTL_INVOK           PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          DBMS_LOGMNR_INTERNAL                PACKAGE BODY              INVALID 07-DEC-12 23-FEB-14
                          DBMS_PRVTAQIP                       PACKAGE                   INVALID 07-DEC-12 23-FEB-14
                          DBMS_PRVTAQIP                       PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          DBMS_REPCAT_MIGRATION               PACKAGE                   INVALID 07-DEC-12 23-FEB-14
                          DBMS_REPCAT_MIGRATION               PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          DBMS_SQLTCB_INTERNAL                PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          DBMS_STREAMS_AUTH                   PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          DBMS_STREAMS_MT                     PACKAGE                   INVALID 07-DEC-12 23-FEB-14
                          DBMS_STREAMS_MT                     PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          DBMS_STREAMS_SM                     PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          DBMS_STREAMS_SM                     PACKAGE                   INVALID 07-DEC-12 23-FEB-14
                          DBMS_SUMREF_UTIL                    PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          DBMS_WORKLOAD_REPLAY                PACKAGE BODY              INVALID 10-DEC-12 02-MAY-14
                          DBMS_WRR_INTERNAL                   PACKAGE BODY              INVALID 10-DEC-12 02-MAY-14
                          DBMS_XSTREAM_ADM_INTERNAL           PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          DBMS_XSTREAM_AUTH                   PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          HTTPURITYPE                         TYPE BODY                 INVALID 10-DEC-12 23-FEB-14
                          KUPW$WORKER                         PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          SCHEDULER$_JOB_EVENT_HANDLER        PROCEDURE                 INVALID 10-DEC-12 23-FEB-14
                          URIFACTORY                          PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          UTL_HTTP                            PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          UTL_SMTP                            PACKAGE BODY              INVALID 10-DEC-12 10-DEC-12
                          UTL_SMTP                            PACKAGE                   INVALID 07-DEC-12 25-JAN-15
                          UTL_TCP                             PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          UTL_URL                             PACKAGE BODY              INVALID 10-DEC-12 10-DEC-12
                          UTL_URL                             PACKAGE                   INVALID 07-DEC-12 23-FEB-14
32 rows selected.

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

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

We are receiving the error because the binary packages which belong to DATAPUMP is in INVALID state.

And one of the oracle notes (453796.1) says that there is a possibility that the last CPU patch applied might had a problem which left some of the Datapump Metadata missing.

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

#########################################
# 4) Check CPU Patch
#########################################

#### Check the latest patch applied to the database

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
------------------------------ ------------------------- --------- ---------- --------- ------------------------- -------------------------
13-DEC-12 11.48.14.623579 AM   APPLY                     SERVER    11.2.0.2           7 CPUOct2012                CPU
28-JAN-13 04.14.40.507736 PM   APPLY                     SERVER    11.2.0.2           8 CPUJan2013                CPU
01-DEC-13 02.49.06.524419 PM   APPLY                     SERVER    11.2.0.2          11 CPUOct2013                CPU
23-FEB-14 08.34.03.016928 AM   VIEW INVALIDATE                                  8289601 view invalidation
23-FEB-14 08.34.03.159818 AM   UPGRADE                   SERVER    11.2.0.4.0           Upgraded from 11.2.0.2.0
23-FEB-14 09.09.53.453945 AM   APPLY                     SERVER    11.2.0.4           1 CPUJan2014                CPU
23-FEB-14 09.11.10.377869 AM   APPLY                     SERVER    11.2.0.4           1 CPUJan2014                CPU
04-MAY-14 11.27.08.483044 AM   APPLY                     SERVER    11.2.0.4           2 CPUApr2014                CPU
02-NOV-14 11.03.14.032696 AM   APPLY                     SERVER    11.2.0.4           4 CPUOct2014                CPU
25-JAN-15 11.02.36.070561 PM   APPLY                     SERVER    11.2.0.4           5 CPUJan2015                CPU
10 rows selected.

We have applied the last CPU patch on 25-Jan-2015 which is coinciding with what the document says.

#### Query one of the Datapump Metadata.

select count(*) from metanametrans$;

SQL> select count(*) from metanametrans$;
  COUNT(*)
----------
         0

=====================================================================================================================
Above metadata table is empty which tells us that there is a problem. When i checked the same metadata table in another database it has 3393 rows.
=====================================================================================================================

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

#### Load datapump metadata using below sql and recompile all the INVALID objects in the database.

@$ORACLE_HOME/rdbms/admin/catmet2.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql

Note : You can also try once running "utlrp.sql" as its just a recompilation of INVALID objects. But in my case it was not working as the table data is missing.

=====================================================================================================================
Now there is no INVALID objects and export is running good now.
=====================================================================================================================

SQL> Select owner,object_name,object_type,status,created,LAST_DDL_TIME from dba_objects where status='INVALID' and owner='SYS' order by object_name;

no rows selected

$ expdp \'/ as sysdba\' estimate_only=y full=y
Export: Release 11.2.0.4.0 - Production on Wed Feb 25 16:53:07 2015
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, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_FULL_01":  "/******** AS SYSDBA" estimate_only=y full=y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
.  estimated "SID_OWNER"."RECOTOR":"P201404"   4.248 GB

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



Monday, February 16, 2015

Remove OLAP components installed in a 11g Database


I need to remove OLAP components in a 11.2.0.3 version of the database as its not being used.

SELECT comp_id,COMP_NAME,schema,VERSION,STATUS,MODIFIED FROM DBA_REGISTRY where comp_name like '%OLAP%' order by 1;

COMP_ID         COMP_NAME                           SCHEMA          VERSION         STATUS          MODIFIED
--------------- ----------------------------------- --------------- --------------- --------------- -------------------------
AMD             OLAP Catalog                        OLAPSYS         11.2.0.3.0      VALID           09-FEB-2015 10:23:04
APS             OLAP Analytic Workspace             SYS             11.2.0.3.0      VALID           09-FEB-2015 10:23:03
XOQ             Oracle OLAP API                     SYS             11.2.0.3.0      VALID           09-FEB-2015 10:23:03

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ Remove OLAP @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

=====================================================================================================================
Step 1 : Remove OLAP Catalog
=====================================================================================================================

#### First lets remove OLAP Catalog

spool remove_olap.log

@/u01/app/oracle/product/11.2.0.3/olap/admin/catnoamd.sql

Once the script completes the execution, it has removed OLAP Catalog component from Database.

COMP_ID         COMP_NAME                           SCHEMA          VERSION         STATUS          MODIFIED
--------------- ----------------------------------- --------------- --------------- --------------- -------------------------
AMD             OLAP Catalog                        OLAPSYS         11.2.0.3.0      VALID           09-FEB-2015 10:23:04

"catnoamd.sql" drops OLAPSYS user and its objects..

=====================================================================================================================
Step 2 : Remove OLAP API
=====================================================================================================================

@/u01/app/oracle/product/11.2.0.3/olap/admin/olapidrp.plb
@/u01/app/oracle/product/11.2.0.3/olap/admin/catnoxoq.sql

After we execute both the scripts, it drops the API component and update registry as removed like below,

COMP_ID         COMP_NAME                           SCHEMA          VERSION         STATUS          MODIFIED
--------------- ----------------------------------- --------------- --------------- --------------- -------------------------
XOQ             Oracle OLAP API                     SYS             11.2.0.3.0      REMOVED         11-FEB-2015 17:34:59

=====================================================================================================================
Step 3 : Remove OLAP APS
=====================================================================================================================

@/u01/app/oracle/product/11.2.0.3/olap/admin/catnoaps.sql

COMP_ID         COMP_NAME                           SCHEMA          VERSION         STATUS          MODIFIED
--------------- ----------------------------------- --------------- --------------- --------------- -------------------------
XOQ             Oracle OLAP API                     SYS             11.2.0.3.0      REMOVED         11-FEB-2015 17:34:59

"catnoaps.sql" updates registry and removes above OLAP API component and marks APS component as "REMOVED" in database registry.

COMP_ID         COMP_NAME                           SCHEMA          VERSION         STATUS          MODIFIED
--------------- ----------------------------------- --------------- --------------- --------------- -------------------------
APS             OLAP Analytic Workspace             SYS             11.2.0.3.0      REMOVED         11-FEB-2015 17:36:04

#### Drop the component

@/u01/app/oracle/product/11.2.0.3/olap/admin/cwm2drop.sql

Removed Below entry from database registry.

COMP_ID         COMP_NAME                           SCHEMA          VERSION         STATUS          MODIFIED
--------------- ----------------------------------- --------------- --------------- --------------- -------------------------
APS             OLAP Analytic Workspace             SYS             11.2.0.3.0      REMOVED         11-FEB-2015 17:36:04

=====================================================================================================================
Step 4 : Recompile INVALID Objects
=====================================================================================================================

As we removed the OLAP components some of the database objects goes to INVALID state. We have to recompile the database objects.

@/u01/app/oracle/product/11.2.0.3/rdbms/admin/utlrp.sql

spool off

select owner, object_name, object_type, status from dba_objects where status='INVALID';

=====================================================================================================================
Review the log file for detailed information.
=====================================================================================================================

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ END @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@



ORA-20000: function-based index "XDB"."XDB$ACL_XIDX" is disabled



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

DBMS_STATS: GATHER_STATS_JOB encountered errors.  Check the trace file.
Errors in file /u01/app/oracle/product/admin/ORALIN/diag/rdbms/ORALIN/ORALIN3/trace/ORALIN3_j000_25372.trc:
ORA-20000: function-based index "XDB"."XDB$ACL_XIDX"  is disabled

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

This database is newly created and Error occured while auto Gather statistics Collection job is run in 11.2.0.4.0 version

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

Gather stats command

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

#########################################
# 1) Check XDB Registry and Index Status
#########################################

#### Check Database registry

SELECT comp_id,COMP_NAME,schema,VERSION,STATUS,MODIFIED FROM DBA_REGISTRY where comp_id='XDB';

COMP_ID         COMP_NAME                           SCHEMA          VERSION         STATUS          MODIFIED
--------------- ----------------------------------- --------------- --------------- --------------- -------------------------
XDB             Oracle XML Database                 XDB             11.2.0.3.0      VALID           09-FEB-2015 10:23:02

#### Check the index

select owner, index_name, status, funcidx_status from dba_indexes where index_name = 'XDB$ACL_XIDX';

OWNER                          INDEX_NAME                     STATUS          FUNCIDX_
------------------------------ ------------------------------ --------------- --------
XDB                            XDB$ACL_XIDX                   VALID           DISABLED

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

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

Newly created database started giving these errors, looks like there is some problem with the installation which we have used is DBCA.

Most of the Database registry were in INVALID state. Made all the database components VALID and found the above error is occuring, which is due to the function based index in DISABLED state.

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

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

#### Enable the index

Alter index xdb.xdb$acl_xidx enable;

SQL> Alter index xdb.xdb$acl_xidx enable;
Index altered.

SQL> select owner, index_name, status, funcidx_status from dba_indexes where index_name = 'XDB$ACL_XIDX';

OWNER                          INDEX_NAME                     STATUS          FUNCIDX_
------------------------------ ------------------------------ --------------- --------
XDB                            XDB$ACL_XIDX                   VALID           ENABLED

=====================================================================================================================
Now the GATHER stats job was running without any errors.
=====================================================================================================================

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