Friday, February 25, 2011

ORA-20401: A job with the specified name already exists

Sometimes in EM Dbconsole 10.2 you try to create a new job and you get the follwoing error:

ORA-20401: A job with the specified name already exists ORA-06512: at "SYSMAN.MGMT_JOBS

First thing is to check the Job and Job library EM screens to check if the job appears there. If so check the status and try to delete from there.

If you cannot change the status please follow :

Stop dbconsole job hung in status Stop Pending on this blog to change the status to stopped.

The if you want to delete it , and you are sure there is other instance of the job scheduled execute :

as sysman:

select job_id, job_name, job_owner from mgmt_job where job_name like '%[JOB_NAME]%';
exec mgmt_job_engine.delete_job('[JOB_ID_FROM_PREVIOUS_STEP]');
commit;


References

Oracle Metalink Note ID 430626.1
Vijay R. Dumpa Oracle Blog

Wednesday, February 9, 2011

A very nice article about the 11g Result Cache Feature. It also discusses how RAC benefits from this feature and differences between 11g and 10g.

http://www.oracle.com/technetwork/articles/datawarehouse/vallath-resultcache-rac-284280.html?msgid=3-3351742848

Tuesday, February 8, 2011

Thursday, February 3, 2011

TSPITR Fails with ORA-29308: view TS_PITR_CHECK failure

Trying to do automated tablespace point in time recovery(TSPITR) containing an IOT with a LOB column and fails to the Export Face of the recovered tablespace with the following :


Export: Release 10.2.0.4.0 - Production on Thu Feb 3 15:18:34 2011

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Export done in US7ASCII character set and UTF8 NCHAR character set
server uses UTF8 character set (possible charset conversion)
Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...
EXP-00008: ORACLE error 29308 encountered
ORA-29308: view TS_PITR_CHECK failure
ORA-06512: at "SYS.DBMS_PITR", line 889
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
host command complete

Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/03/2011 15:18:38
RMAN-03015: error occurred in stored script Memory Script
RMAN-06135: error executing host command: Additional information: 256
Additional information: 11

Bug 6620517 ORA-29308 from TSPITR with IOT with LOB column
Fixed in 10.2.0.5


ATTENTION !!!!
Bug 6620517 is not only to IOT's with LOB's but more issue with IOT's and TSPITR.

This an except from an Oracle SR i opened :

"
So the option for 10.2.0.4 and 10.2.0.5, when IOT's are involved, is to do the Tablespace Point In Time Recovery manually.

- DUPLICATE DATABASE SKIP TABLESPACE
- Transportable Tablespace export of the related tablespace on the duplicate database
- Transportable Tablespace import of the related tablespace into the source database.

....

Indeed Bug 6620517 is not resolving the 'IOT with NO LOB 'issue in 10.2.0.5.
That is fixed in 11g.
"




And you can use the same workaround for Bug:7016765 as here:
RMAN Tablespace Point-in-Time Recovery and ORA-19602

The bug case can be cheked with the following procedure:

1) the recovery-set of tablespaces (eg 'MYTBS') fully contains all the storage objects of an Index-Organized Table (IOT) with a LOB column.
select
tablespace_name,
segment_type,
owner||'.'||segment_name "OWNER.OBJECT"
from
dba_segments
where
tablespace_name = 'MYTBS'
order by
2,3;

2) Query the TS_PITR_CHECK view shows the IOT-with-LOB consists of storage objects in the recovery set of tablespaces and the IOT also has a storage object in the SYSTEM tablespace, for example if the following query returns at least one row:

select * from
sys.ts_pitr_check
where ( ts1_name in ('MYTBS')
and ts2_name not in ('MYTBS') )
or ( ts1_name not in ('MYTBS')
and ts2_name in ('MYTBS') );

with one object is reported as being a TABLE in the SYSTEM tablespace and the other object is reported as being an INDEX in one of the tablespaces in the recovery-set and the reason is: "Tables and associated indexes not fully contained in the recovery set"

3) Assure that the table is an IOT:
select iot_type from dba_tables where table_name='[Paste the table_name from the above query]';
It must return IOT
Verify the INDEX is a LOB index:
select index_type from dba_indexes where index_name='Paste the table_name from the above query';
It must return LOB
The (bogus) violation reported above is not reported by the following method (correctly):
execute dbms_tts.transport_set_check('MYTBS',TRUE,TRUE);
select * from transport_set_violations;

This method is also (also) used to check if a set of tablespaces are fully contained and for that reason you can export the recovered tablespace from the auxiliary instance as the workround mentioned previous in the article.