Thursday, February 27, 2014

ORA-08104 after a session has been killed during an online index creation/rebuild

Killing a session that is creating/rebuilding an index online rebuild your session was killed and you trying to drop the index or re-creat/rebuild you get 

ORA-08104: this index object ##### is being online built or rebuilt

In order to resolve this issue you have to use function dbms_repair.online_index_clean as sysdba. This is a fix that is installed with patch for BUG:3805539 and if you get an error calling the function , then please install it.

SQL>set serveroutput on

SQL>declare
  isclean boolean;
begin
  isclean := false;

  while isclean=false
  loop
    isclean := dbms_repair.online_index_clean(object_id=>dbms_repair.all_index_id, wait_for_lock=>dbms_repair.lock_wait
    );
    dbms_lock.sleep(10);

  end loop;

exception

when others then
  raise;

end;
/

Parameter input:

object_id : 
    ALL_INDEX_ID -> cleanup all index that qualify
    [OBJECT_ID] -> cleanup the specified index by using the object_id

wait_for_lock :
     LOCK_WAIT -> retry getting DML locks on underlying table
     LOCK_NOWAIT -> do not retry getting DML locks on underlying table

Returns:
     TRUE -> specified indexe(s) were cleaned up successfully
     FALSE -> one or more indexes failed cleaned up but its possible that some may have been cleaned up successfully.

REFERENCES
Oracle Support Doc ID 375856.1 & 1378173.1





Thursday, February 20, 2014

How to avoid a performance problem commonly caused by Advanced Queuing usage in a RAC environment

A very nice article from to IOUG to investigate and avoid perfromance bottlenecks in an Oracle RAC having investigation ,analysis and resolution, starting from the top events such and latch: cache buffer chains & GC Buffer Waits of a singel instance on a 5 Node RAC.

PDF : http://www.ioug.org/d/do/3355

Alternative link: http://www.ioug.org/p/bl/et/blogid=40&blogaid=241

Wednesday, February 19, 2014

ORA-29902 DRG-10502 Utilizing Text Indexes on Standby in Read Only Mode (Active DataGuard)

Several Oracle Text indexexes were dropped and re-created in the Primary DB. 

The BO Reports that utilize the indexes on the Standby in an Active Data Guard configuration reported the follwoing error when the SQL uses CONTAINS :

ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drdmdcntORA-20000: Oracle Text error:DRG-10502: index 1174 does not existORA-06512: at "CTXSYS.DRUE", line 160ORA-06512: at "CTXSYS.DRVXMD", line 148ORA-06512: at line 1


The problem arises beacuse the SQL uses cached execution plan is used in the Standby DB.

The only workaround is to fulsh the shared pool in the Standby DB.

SQL>alter system flush shared_pool;

References : Oracle Spport Doc ID 1570093.1




Friday, February 14, 2014

expdp/impdp fails reports ORA-21780 at the end of export

Recently i had an expdp that reported the following:

ORA-39097: Data Pump job encountered unexpected error -21780
ORA-39065: unexpected master process exception in DISPATCH
ORA-21780: Maximum number of object durations exceeded.

After searching Oracle Support (see below) the issue is not affecting the data and the consistency of the dump file thus data were successfully exported.

The side effect is that it leaves an orphaned data pump job in dba_datapump_jobs.

Check http://agstamy.blogspot.be/2012/10/cleanup-orphaned-datapump-jobs.html of how to clean orphaned data pump jobs.

The issue is raised after changing the PARALELL workers of the running datapump job.

The Oracle Support document states that this happens only if lowering the PARALLEL workers of a running job, but the referenced bug reference and workaround state DO NOT MODIFY PARALLEL while the job is running.

REFERENCES:

Oracle Support Doc ID 1357811.1

Versions Affected : Version 10.1.0.2 to 11.2.0.3

BUG:10198387 - DATA PUMP EXPORT REPORTS ORA-21780 WHEN PARALLELISM (PARALLEL=N) IS MODIFIED

WORKAROUND

Don't change the number of parallel processes while Data Pump Export is running.

FIXED :

11.2.0.4
12.1

Wednesday, February 12, 2014

Slow Materialized View Fast Refreshes

A very good article about the mechanisms of MVIEW Fast Refresh, specially when fast refresh is occuring but becoming slow.

http://www.dba-oracle.com/t_materialized_view_fast_refresh_performance.htm


Tuesday, February 4, 2014

OMS12c Agent has stopped monitoring COLL_DISABLED

Agent has stopped communicating with OMS and collection has been halted.

Steps to workaround and fix this issue :

1. get the status of the agent

./emctl status agent

Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
.
.
Total Megabytes of XML files uploaded so far : 2.39
Number of XML files pending upload           : 5,067
Size of XML files pending upload(MB)         : 4.83
Available disk space on upload filesystem    : 79.38%
Collection Status                            : [COLLECTIONS_HALTED(
 UPLOAD SYSTEM Threshold - unable to purge files in upload system)]

2. If the error is the above then as workaround re-secure the agent and problem will be fixed.

./emctl stop agent

./emctl secure agent

Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
Agent is already stopped...   Done.
Securing agent...   Started.
Enter Agent Registration Password :
EMD gensudoprops completed successfully
Securing agent...   Successful.
./emctl start agent


Issue should be fixed.


REFERENCES
Oracle Support DOC ID : 1618514.1