Oracle Enterprise Stage - sqlldr, ORA-00604, ORA-00054

[dsexplorer]

Hello,

We are using Oracle Enterprise Stage to load Oracle 10g from DS7.5.2.
We have two Oracle Schemas and webservices use one and other is used to refresh data. Once this is done, Webservices switch to using this schema.
Issue - time and again we get Oracle errors like below
SQL*Loader-951: Error calling once/load initialization
ORA-00604: error occurred at recursive SQL level 1
ORA-00054: resource busy and acquire with NOWAIT specified

DBA's have confirmed no one else is loading these tables/accessing any object in the passive schema (Passive means not used by webservices).

APT file has 5 nodes specified. At a time around 3-4 tables are being loaded in parallel. Each time a different job fails. Options - DIRECT=TRUE, PARALLEL=TRUE. Works fine from development environment which has same configuration. Oracle is RAC so APT_ORACLE_NO_OPS=1

[chulett]

ORA-00054: resource busy and acquire with NOWAIT specified

DBA's have confirmed no one else is loading these tables/accessing any object in the schema

Short answer is the DBAs are incorrect, as the error shows. Someone or something has the table in question 'in use' such that your operation cannot proceed. Any chance it is your jobs themselves? Is there any overlap in the jobs that run and the tables they service? Assuming no, then perhaps your services are not as fully switched as you think they are when these jobs are launched.

Are you doing a full 'truncate and load' type load when you say the data is being refreshed?

[dsexplorer]

Hello,

Jobs are doing Truncate and Load

Sqlldr ctrl files look like

OPTIONS(DIRECT=TRUE, PARALLEL=TRUE, SKIP_INDEX_MAINTENANCE=YES)

LOAD DATA CHARACTERSET WE8ISO8859P1 INFILE 'ora.136.645058d.fifo.0' "FIX 180"
APPEND INTO TABLE dct3_wls.DCT_DELIVERY_ADDITIONAL_PLC

Ofcourse there are 5 loads in parallel for same table. (5 ctl files created by Datastage due to 5 nodes)

I assume Datastage is truncating the table before this internally. Funny thing is that everytime it is different job and next time that job goes through.

No we are not loading same table in different jobs.

Could it be that number of parallel jobs are causing this issue? Is there some limitation on number of parallel Oracle load sessions?

Any hint would be much appreciated

Thanks

[dsexplorer]

Hello,

We may have found the cause.

This pasive schema had indexes with "NOPARALLEL" set when active schema had all with "PARALLEL DEGREE ....".

Now we changed it all jobs look fine so far

Best Regards

[dsexplorer]

Sorry for false hope, the jobs failed again even after change but at different further table now.

Any help would be much appreciated

Thanks

[chulett]

Could have told you that while that index observation was an issue it certainly wasn't the issue. No-one can help you with this except you and yours, especially your DBAs. Resource busy means the table is in use. Period. Unless you are the victim of some bizarre Oracle bug, as hard as that is to believe. [/sarcasm]

Regardless, keep working with your DBAs to trace this.

[dsexplorer]

We reduced no of nodes to 3 from 5 and somehow it is working now.

Also our DBA's have opened a case with Oracle.

I cannot believe the table is used by anything else and every time a different table.

Mayble a combination of Unix Server Load with Oracle client version (which is 9.2 and DB is 10g).

Will post here if we find something from Oracle.

Best Regards

[chulett]

9.2.what.what?

[chulett]

Memory is a little fuzzy but I think that was a fairly stable release. We had good luck with 9.2.0.6 from what I recall but weren't on RAC... though I'm not sure that would be an issue here.

[dsexplorer]

Our DBA's think we might have hit the Oracle bug

Bug 5973648 - ORA-54 during concurrent parallel direct SQLLOAD in RAC [ID 5973648.8]

DBA's have opened a case with Oracle support so will let you know how it goes.

Today we will try to use only one Node to load data and see if it helps

Best Regards

[dsexplorer]

Loading to one Oracle node (not making use of both nodes of Oracle RAC) with full parallelism on Datastage end worked fine now.

Oracle has asked us to apply the patch so will do so in two weeks time and till then we load to one Oracle instance only.

I will leave this post open and close it after we apply patch and see the results

[chulett]

Interesting... so something specific to RAC, it seems. Do let us know how this all works out!