Step Into Oracle DBA ( SID ): 2015

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>

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

[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>

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

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>

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

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
=====================================================================================================================

Step Into Oracle DBA ( SID )