Oracle Enterprise Stage - sqlldr, ORA-00604, ORA-00054
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 23
- Joined: Wed Apr 16, 2008 5:34 am
- Location: Prague,Cz
Oracle Enterprise Stage - sqlldr, ORA-00604, ORA-00054
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
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
Regards
DSExplorer
DSExplorer
Re: Oracle Enterprise Stage - sqlldr, ORA-00604, ORA-00054
dsexplorer wrote:ORA-00054: resource busy and acquire with NOWAIT specified
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.dsexplorer also wrote:DBA's have confirmed no one else is loading these tables/accessing any object in the schema
Are you doing a full 'truncate and load' type load when you say the data is being refreshed?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 23
- Joined: Wed Apr 16, 2008 5:34 am
- Location: Prague,Cz
Re: Oracle Enterprise Stage - sqlldr, ORA-00604, ORA-00054
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
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
Regards
DSExplorer
DSExplorer
-
- Premium Member
- Posts: 23
- Joined: Wed Apr 16, 2008 5:34 am
- Location: Prague,Cz
Re: Oracle Enterprise Stage - sqlldr, ORA-00604, ORA-00054
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
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
Regards
DSExplorer
DSExplorer
-
- Premium Member
- Posts: 23
- Joined: Wed Apr 16, 2008 5:34 am
- Location: Prague,Cz
Re: Oracle Enterprise Stage - sqlldr, ORA-00604, ORA-00054
Sorry for false hope, the jobs failed again even after change but at different further table now.
Any help would be much appreciated
Thanks
Any help would be much appreciated
Thanks
Regards
DSExplorer
DSExplorer
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.
Regardless, keep working with your DBAs to trace this.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 23
- Joined: Wed Apr 16, 2008 5:34 am
- Location: Prague,Cz
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
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
Regards
DSExplorer
DSExplorer
-
- Premium Member
- Posts: 23
- Joined: Wed Apr 16, 2008 5:34 am
- Location: Prague,Cz
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
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
Regards
DSExplorer
DSExplorer
-
- Premium Member
- Posts: 23
- Joined: Wed Apr 16, 2008 5:34 am
- Location: Prague,Cz
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
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
Regards
DSExplorer
DSExplorer