cancel
Showing results for 
Search instead for 
Did you mean: 

error when connecting Oracle

Etienne Roseau
Frequent Advisor

error when connecting Oracle

hi everybody !
working on Oracle release 9.2.0.4.0(HP-UX 11.0), my collegue has a connection problem with this message:
ORA-12547: TNS:lost contact.

because i don't deal with it and he seems to be unable to resolve it, i'm asking your help.
thanks.
E.
13 REPLIES
Rita C Workman
Honored Contributor

Re: error when connecting Oracle

There could be a number of things to cause this..

His or the host tnsnames is not in 'sync.
Check and make sure you have the latest tnsnames.ora on your client...and that it is pointing correctly to the hosts Oracle.

Did anybody make a recent change in the hostfile or DNS ? Check this.
< I once made a simple change and didn't notice that an alias I added was on a line above the reference to my pkg name - was the same..oops...tns error >

Or did you have any disruption to your network ? Remember that Oracle listener can be fussy...folks attached may continue to work, but anyone who logs out will probably not be able to get back into Oracle - because the Oracle Listener is now out of sync with the database. Resolution - halt database, kill listener, cleanup resources (or reboot, whichever is quicker for ya) and restart your database & listener clean.

Just a couple things to check,
Rgrds,
Rita
Etienne Roseau
Frequent Advisor

Re: error when connecting Oracle

hi Rita,
the only thing i know is that they have installed patches for our Oracle version, but i don't know in what purpose.
i will check with him tomorrow.
but you have given me a beginning of answer, in my un-understanding of Oracle.. :-)
thanks.
E.
Hein van den Heuvel
Honored Contributor

Re: error when connecting Oracle

Like Rita said, this coudl be a number of things. You'll need to find someone that knows a little about oracle to really work through this, but here are a few hints:

ps -ef | grep ora

This should should give something like (| cut -c 49-70 ):

ora_pmon_test
ora_dbw0_test
ora_lgwr_test
ora_ckpt_test
ora_smon_test
ora_reco_test
oracletest (DESCRIPTIO
oracletest (DESCRIPTIO
oracletest (DESCRIPTIO

That would be a handfull of oracle 'system' processes and here 3 'slaves' for instance 'test'.

If you don't see those first, then Oracle is not started. If you do see those last, then some folks can connect.

If Oracle is not running... try to start it:

su - oracle (or whatever name looks like oracle in /etc/passwd)

env | grep -i ora

sqlplus "/ as sysdba"
sql> startup


if that complains, check the error text and or tail the alert file:

find $ORACLE_HOME -name "alert*"


You should probably also have a listener active.
Check with 'lsnrctl status'.
If that mummble complaints, then tail the .log file is points to.
The listener is a unix process that looks like:
/oracle/xxxxx/bin/tnslsnr LISTENER



Good luck!

Hein.
Jan Sladky
Trusted Contributor

Re: error when connecting Oracle

hi Etienne,

output from ORA HELP:
oerr ORA 12547
12547, 00000, "TNS:lost contact"
// *Cause: Partner has unexpectedly gone away, usually during process
// startup.
// *Action: Investigate partner application for abnormal termination. On an
// Interchange, this can happen if the machine is overloaded.

as it was said, it can be many things. The fastes way how to solve it could be following:

check alert_db.log and try tnsping db (probably won't work, but...)
than check
tnsnames.ora and listener.ora
than
lsnsctl stop/start
than
log into oracle as sys or system and:
shutdown immediate
startup

post the results

br Jan
GSM, Intelligent Networks, UNIX
Yogeeraj_1
Honored Contributor

Re: error when connecting Oracle

hi,

you should also have a look at the listerner.log and the alert.log for possible errors.

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Etienne Roseau
Frequent Advisor

Re: error when connecting Oracle

thanks everybody.
I gave my collegue the whole informations.
apparently he has already checked most of it; and he is now checking the things he didnot think of....
PAVIC Thierry
Frequent Advisor

Re: error when connecting Oracle

The resolution failled cause one part of chain is not as U suppose. I know that it's difficult to check all things.

Can you check the ping of your server, first by the hostname, check the ip adress,

after check the connect tring : tnsping ORACLE_SID;

check to your com port 1521 or 1526 and may be an other.

Do you use different database version on the same server

check the database is running good

check the following files :
tnsnames.ora
listener.ora
sqlnet.ora

PAVIC Thierry
Frequent Advisor

Re: error when connecting Oracle

Do you have on Unix trace directory a file like .dat.


Can you move it in /tmp/trace for exmple and try to connect at you database maybe after a cycle shutdown and startup if is it possible
R. Allan Hicks
Trusted Contributor

Re: error when connecting Oracle

In addition to what PAVIC has already suggested.....

Look at the tnsnames on the client machine. I've had this problem before and seems like I resolved it with an article from metalink.

When you connect, (the no trouble case) are you going through the network. sqlplus scott/tiger@oracle_service?

The sqlnet.ora file usually has an entry for a domain name. The domain name is appended to the service name in the tnsnames file and may not match the service name in the listener.ora file. If the server name is orcl and the host name is mydatabase.org, then the service name in the listener should be orcl.mydatabase.org.

In general, for most small installations I find the domain name a pain in the rear that I can do without. You can comment out the domain name in the sqlnet.ora on the client.

On the server, lsnrctl service will list the services that the listener is listening for. Compare these to the services in the tnsnames.ora file on the client.

If the ping works, try tnsping

ex: tnsping orcl
(where orcl is the service name)

This will ping the service in the listener. If you can't tnsping the listener, you have no prayer of connecting to the listener and the database.


Check metalink.oracle.com. I've had this problem before and I know I found the answer there. I'm just too old to remember it.

--Good Luck
"Only he who attempts the absurd is capable of achieving the impossible
twang
Honored Contributor

Re: error when connecting Oracle

I've seen a similar case, but for that case it was the listner.log that
had grown to over 2GB, and the filesystem wont accept files over 2GB.
Renaming the file, and restarting the lister - it should work fine.
Indira Aramandla
Honored Contributor

Re: error when connecting Oracle

Hi,

When you say that they installed patches for Oracle version, did they check if the patch installation was successful. If not then reinstall again and may be this will resolve the issue.

And as others mentioned this error may be caused due to 'n' number of reasons. For beter analysis, the other accociated error message will be helpful. Please check the alert log, and listener.log to get the associated error messages with ORA-12547:


Indira A
Never give up, Keep Trying
sreejith_4
Frequent Advisor

Re: error when connecting Oracle

Hi,

R u using a DHCP address there ?
The check your net manager configuration.

Regards
Sreejith M
Etienne Roseau
Frequent Advisor

Re: error when connecting Oracle

hi everybody !
many thanks for your help,
but as these patches were to prepare for a new application, my collegue finally decide to ask help from the developpers of it (included in the price of this upgrade !).
he thanks you also (don't speak english !)
but now i'll try to understand more Oracle administration !!