sending data to another db thru sqlldr - error

[Ratzie]

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 ${?}

[Steven E. Protter]

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

[Indira Aramandla]

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

[Leon Allen · Accepted solution]

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

[Arturo Galbiati]

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]

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]

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

[Sandman!]

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!