Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

sending data to another db thru sqlldr - error

SOLVED
Go to solution
Ratzie
Super Advisor

sending data to another db thru sqlldr - error

I collect data on my database and send to a report database using sqlldr.
The report server goes down for routine backup and the script has no way of telling if the server is down, so when the script executes I get an error:

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12500: TNS:listener failed to start a dedicated server process

Is there a way to check if another database is running before I do the sqlldr routine?

I thought tnsping, but it will still return a 0 for ${?}
7 REPLIES
Steven E. Protter
Exalted Contributor

Re: sending data to another db thru sqlldr - error

Shalom,

I would suggest a simple script that does the following:

select * from v$database;

That should get you a return code non-zero or some data from oracle to determine whether the database is up or down.

tnsping is a network connectivity tool to merely see if database communication is possible. Its not useful in determining whether the target database is up or down.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Indira Aramandla
Honored Contributor

Re: sending data to another db thru sqlldr - error

Hi LHradowy,


A simple sqlplus logon to the target database will return an error code saying database not available.

And in your loasing script you can look for this succussfull login and then start your loader sqlldr or exist saying database is not available.



Indira A
Never give up, Keep Trying
Leon Allen
Regular Advisor
Solution

Re: sending data to another db thru sqlldr - error

Here is a simple script which you can customise and/or incorporate into a sqlloadr script.

# echo Database Check for "`uname -a ; date`"

for DATABASE in
do
# echo Checking $DATABASE - \\c

if [ "`ps -ef | awk '/'co_$DATABASE'/ && $8 !~ /awk/ {print $8}' -`" = "ora_reco_$DATABASE" ] ; then
# echo $DATABASE is up and running.
# Put your SQL loader script in here (cause the database 'appears' to be up)
continue
elif [ "`ps -ef | awk '/'co_$DATABASE'/ && $8 !~ /awk/ {print $9}' -`" = "ora_reco_$DATABASE" ] ; then
# echo $DATABASE is up and running.
continue
else
echo **DATABASE ALERT! \\a \\a \\a
echo **DATABASE ALERT! The $DATABASE Database is DOWN! \\a \\a \\a
echo **DATABASE ALERT! \\a \\a \\a
fi
done

Time's fun when your having flys (ancient frog saying)
Arturo Galbiati
Esteemed Contributor

Re: sending data to another db thru sqlldr - error

Hi,
you can use this script to be sure that your db is up and running:
sqlplus -s <dbmon/dbmon
set pages 0 feed off tab off trim on
select 'OK'||'|'||host_name||'|'||database_status from v\$instance;
exit
END_OF_SQL
if [[ $sql_status = OK ]] && [[ $db_status = ACTIVE ]]; then
echo Database is active on $host_name
fi

Feel free to adapt it on your needs,
HTH,
Art
Peter Godron
Honored Contributor

Re: sending data to another db thru sqlldr - error

Hi,
have a look at:
http://www.oracleutilities.com/OSUtil/ping.html

Summary:
1. Test machine up with ping
2. Test listener up with tnsping
3. Test database up with tnsping and specified tnsnames.ora

Marty Metras
Super Advisor

Re: sending data to another db thru sqlldr - error

I do something like to verify that the database is running.
==========
THE_SID=DBNAME
let THE_TEST=`ps â ef|grep ora_pmon_$THE_SID |grep â v grep|wc â l`
if [ $THE_TEST â gt 0 ]
then
echo "database is up do some thing"
else
mailx â s "database "$THE_DB" is not open" Marty fi
==========
Marty
The only thing that always remain the same are the changes.
Sandman!
Honored Contributor

Re: sending data to another db thru sqlldr - error

IMHO...you could always request your DBAs to bring down the listener process as well during the backup schedule. This way tnsping will work.

cheers!