Home » RDBMS Server » Server Utilities » SQL LOADER in shell script (How to run multiple Load files with one sqlloader session)
SQL LOADER in shell script [message #680141] Fri, 24 April 2020 02:53 Go to next message
bkbora
Messages: 21
Registered: April 2020
Junior Member
Hi,
i am passing multiple LOAD files into a script, but the script is connecting to DB again and angain. Is there anyway we can pass mutiple LOAD files with one oracle session.

Report message to a moderator

Re: SQL LOADER in shell script [message #680142 is a reply to message #680141] Fri, 24 April 2020 03:00 Go to previous messageGo to next message
cookiemonster
Messages: 13937
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why does it matter?

Report message to a moderator

Re: SQL LOADER in shell script [message #680143 is a reply to message #680142] Fri, 24 April 2020 03:02 Go to previous messageGo to next message
bkbora
Messages: 21
Registered: April 2020
Junior Member
if something goes wrong from DB side then I am getting below error and not able to start the oracle session . Getting below error.
But if running one is loaded successfully.

SQL*Loader-128: unable to begin a session
ORA-28547: connection to server failed, probable Oracle Net admin error

Report message to a moderator

Re: SQL LOADER in shell script [message #680144 is a reply to message #680143] Fri, 24 April 2020 03:08 Go to previous messageGo to next message
bkbora
Messages: 21
Registered: April 2020
Junior Member
I meant to say, lets my A.LOAD is running and if something goes wrong then the A.LOAD is loaded successfully but after A.LOAD comoleted when B.LOAD started then i am getting error. My shell script is trying to connect again when it's loading B.LOAD after A.LOAD completed.



+ '[' 1 -ne 0 ']'
+ /usr/intranet/oracle_home/bin/sqlldr xx/xx@xx B.ctl B.log B.bad MB.LOAD errors=999999

SQL*Loader: Release 12.2.0.1.0 - Production on Fri Apr 24 06:23:25 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

SQL*Loader-128: unable to begin a session
ORA-28547: connection to server failed, probable Oracle Net admin error
+ retcode=1
+ echo 1

Report message to a moderator

Re: SQL LOADER in shell script [message #680147 is a reply to message #680144] Fri, 24 April 2020 04:38 Go to previous messageGo to next message
cookiemonster
Messages: 13937
Registered: September 2008
Location: Rainy Manchester
Senior Member
So the obvious thing to do is determine why you're getting that error.
Are you running the shell script on the DB server?

Report message to a moderator

Re: SQL LOADER in shell script [message #680148 is a reply to message #680147] Fri, 24 April 2020 04:40 Go to previous messageGo to next message
bkbora
Messages: 21
Registered: April 2020
Junior Member
the shell script is running on app server, when in DB side the DB server is failing over from A side to B side then the script should run and load the data. But the script is failing. Which is not correct.

Report message to a moderator

Re: SQL LOADER in shell script [message #680149 is a reply to message #680148] Fri, 24 April 2020 05:11 Go to previous messageGo to next message
cookiemonster
Messages: 13937
Registered: September 2008
Location: Rainy Manchester
Senior Member
So you're getting the error when doing a fail-over?
So what fail-over tech are you using? Data-guard?

Report message to a moderator

Re: SQL LOADER in shell script [message #680150 is a reply to message #680149] Fri, 24 April 2020 05:19 Go to previous messageGo to next message
bkbora
Messages: 21
Registered: April 2020
Junior Member
yes,

As per DBA it is a 3 node rac cluster
service will be running on 2 nodes
Service oar_sql is running on instance(s) DUK1,DUK2 .
so whenever you establish the connections it will be spread across the two nodes.

during the failover test, if one instance is stopped, then application will be able to connect to another instance

datagurard concept is for switchover from prod to dr
here we should not thing about dataguard which is different

Report message to a moderator

Re: SQL LOADER in shell script [message #680151 is a reply to message #680150] Fri, 24 April 2020 06:15 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3280
Registered: January 2010
Location: Connecticut, USA
Senior Member
Any session will fail during failover/switchover unless it is TAF. SQL*Loader isn't TAF-aware as far as I know. So all you can do is catch ORA-28547, do cleanup, if needed, and start over or track number of committed rows and start SQL*Loader again using skip number of committed rows (assuming you use 1 physical record = 1 logical record), Now about "not able to start the oracle session". Service to primary is stopped when failover/switchover occurs. Your TNS must have multiple ADDRESS entrees in order on how do you want to connect - primary first, then local dataguard (HA - high availability), then remote data guard (DR - disaster recovery site). For example:

XXX =
 (description=
  (load_balance=off)(failover=on)
  (address=(protocol=tcp)(host=primary_scan_listener)(port=1521))
  (address=(protocol=tcp)(host=ha_scan_listener)(port=1521))
  (address=(protocol=tcp)(host=dr_scan_listener)(port=1521))
  (connect_data=
     (service_name=XXX)
     (failover_mode=(type=select)(method=basic))
  )
 )
This way SQL*Loader will first attempt to connect to primary. If unsuccessful it will try connecting to HA. If unsuccessful it will try connecting to DR. And if that is unsuccessful connection attempt will error out.

SY.

Report message to a moderator

Re: SQL LOADER in shell script [message #680152 is a reply to message #680151] Fri, 24 April 2020 07:32 Go to previous messageGo to next message
bkbora
Messages: 21
Registered: April 2020
Junior Member
But the the DB servers are DAAS server and the connection string is same for both primary and HA.

is there anyway we can set connection pool for sqlloader and the way we can implement. Please help.

Report message to a moderator

Re: SQL LOADER in shell script [message #680153 is a reply to message #680141] Fri, 24 April 2020 16:07 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>Is there anyway we can pass mutiple LOAD files with one oracle session.
You can just pass multiple files as input.
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sutil/oracle-sql-loader-commands.html#GUID-4F0321D7-B141-453B-B5D2-25 53230CDFBF

Posting Oracle versions and scripts involved might help.
The issue that needs to be addressed is
>>ORA-28547

[Updated on: Fri, 24 April 2020 16:30]

Report message to a moderator

Re: SQL LOADER in shell script [message #680182 is a reply to message #680153] Mon, 27 April 2020 01:04 Go to previous messageGo to next message
bkbora
Messages: 21
Registered: April 2020
Junior Member
Hi,
we are using oracle 12c and my script is like below and if retcode is other than 0 then the script will try to load again. In my case while DBA is stopping the services/node in primary side the script is not able to connect to B side and not able to load the file. The connection string is same for both A and B. DBA is performing OPA for our DB and failover testing should be pass as part of OPA from Application point of view.
Any help will be more appreciated.

handle_retcode() {
if [ $1 -eq 0 ]
then
echo "SQL*Loader execution successful"
else
echo "There is error while running the sqlloader"
echo "Errors found in $2:"
error_code=`grep ORA- $2 | sed 's/^/ /'`
echo "$error_code"
fi
}


for file_name in A B
do
retcode=99999
while [ $retcode -ne 0 ]
do
sqlldr scott/tiger control=${name}.ctl log=${name}.log
retcode=$?
echo "$retcode"
handle_retcode $retcode ${name}.log
done
done

Report message to a moderator

Re: SQL LOADER in shell script [message #680188 is a reply to message #680182] Mon, 27 April 2020 08:07 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
The issue is not with SQL*Loader.
Oracle 12c TAF does not support DML. Some limited work can be done with JDBC. Not your case.
I would look into Oracle Continuity.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adfns/high-availability.html#GUID-4A142A4C-088F-42F5-84C2-C3883052CF6 7

Edit:
I don't think Oracle Continuity would be simple or even a viable solution.
Trying Solomon's suggestion with manual cleanups should work.

[Updated on: Mon, 27 April 2020 08:16]

Report message to a moderator

Previous Topic: ORA-12899 Error
Next Topic: Impdp issue (merged)
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Sep 08 10:46:05 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.