ORA-03113: When Creating or Testing DBLink

Hello,

I'm getting the error -
ORA-03113 end-of-file on communication channel
When I attempt to create a new DBLink or test an existing one.

I've searched my server for the alert.log, but it doesn't exist, I read somewhere to check the User Dump but don't know where to find it, or what it is called exactly.
Does anyone have any ideas on what the problem could be, what to check and where etc.?

Regards Al

what I do to ensure connectivity with the remote database before I create a datbase link is..
from the command line I try to connect to the remote database using the userid I want to use. That way if I can't connect to it when I create the database link, I know it's not because of underlying connectivity issues. Other than syntax issues, not having the remote database listed in the init.ora on the server side is usually the main reason for connectivity issues.

Quote:

---

Originally posted by OracleDoc
what I do to ensure connectivity with the remote database before I create a datbase link is..
from the command line I try to connect to the remote database using the userid I want to use. That way if I can't connect to it when I create the database link, I know it's not because of underlying connectivity issues. Other than syntax issues, not having the remote database listed in the init.ora on the server side is usually the main reason for connectivity issues.

---

Word!

Hello OracleDoc,

Firstly may I say; Top quality handle and just what I need!!

I'm new to Oracle and mostly use TOAD when working with oracle so don't really understand what you mean by connecting via the command line do you mean SQL Plus or the DOS Prompt?
I presume in either case that I should be running sql pluse or dos from on the host server?

Also I don't know what you mean by listing the remote DB in the init.ora? I usually insert the connection string into the tnsnames.ora? Is that the same thing... doubt it??

I've tested my DBLink in reverse and it works okay. By this I mean that I'm having trouble connecting my Live DB to my BackUp DB.
BackUp DB connects to the Live without any problems

Cheers Al

Quote:

---

Originally posted by Running Bear
I'm new to Oracle and mostly use TOAD when working with oracle

---

That's your first mistake! Toad is the spawn of the devil! You need to learn how to type stuff out via the command line which is either sql or dos prompt. :rolleyes: Then once you know that stuff you can use toad. Bahhh!

to find out if you have connectivity from the live database to the backup database you would do this. From the dos prompt issue

$> sqlplus userid/used_for_db_link@backup_datbase

if you connect then you're good to go
if you don't, that means you don't have the backup database listed in your tnsnames.ora file.

it's nice that you have connectivity from your backup to your live but that's not what we're trying to accomplish here, we want the reverse which means you need to tell Oracle that it exsits. Which means you need to go open up netmanger and fix it :D

Quote:

---

Originally posted by OracleDoc
. . . . that means you don't have the backup database listed in your init.ora file.

---

:confused: don't you mean tnsnames.ora ?

Also try tnsping servicename from your live db to backup db.
This will also tell u if connectivity is there from live db to backup db.

regards
anandkl

Quote:

---

Originally posted by DaPi
:confused: don't you mean tnsnames.ora ?

---

DOAHH! damn misprint. yep good catch Dapi thanks

I've attempted to connect via the command line

Code:

---

I:\orant\ora81\bin>sqlplus fcontdba/*****@fastcont_backup.nat.bt.com

error: ORA-12154: TNS:could not resolve service name

---

I have already manually entered the tnsnames entry but am now trying to register it manually.
I've tried to run the Net8 Configuration Assistant but when I choose "Local net service name configuration" and click next nothing happens. So I've been trying to do it from the command line

I searched for NAMESCTL on the server. Found NAMESCTL.exe in I:\orant\ora81\bin
so I've attempted to run the following

Code:

---

I:\orant\ora81\bin>register FastCont_BackUp.Nat.BT.com -t oracle_database -d (description=(address=(protocol=tcp)(host=123.45.67.8)(port=1521))(connect_data=(sid=fcont)))

Error: Invalid Parameter(s)
Register a program so it can have special...

---

Can anyone see where I'm going wrong? Is Oracle_Database a static parameter or am I supposed to be substituting it for something of my own? Apologies for all the questions, however I am soaking it all up :o

Cheers Al

Apologies it clicked just after I posted my last reply that I needed to execute the namesctl.exe which I've now done

Got the following errors -
NNL-00024: warning: no preferred name servers in SQLNET.ORA
NNL-00018: warning: could not contact default name server
Welcome to NAMESCTL, type "help" for information.

Are these critical errors? I've just checked my other db which works and it throws the same errors?

Have attempted to register my db

now getting error
Response status: NNC-00406: name "FastCont_BackUp.nat.bt.com" does not exist

Running bear I hate to say this but it looks like you need to read up on some things. I would start out by reading up on net manager. Even though you've already done so, I would never mess with the tnsnames.ora manually, that thing is just too fragile, one wrong syntax and the whole thing is messed up. Could you imagine trying to edit a tnsnames with about 20 databases in it? Unless you've got some serious experience under your belt leave the thing alone. :)
Anyway, in all honesty you're getting wayyyy to deep into something that is really simple.
Without having a live conversation to guide you, read up on adding a service name.

Running Bear, you have gone off on a false trail with namesctl & register - they are for configuring a names server. You sound like you should be trying to set up local naming with tnsnames.ora - the simplest option.

I strongly recommend NOT to use "Net8 Configuration Assistant" use the "Net8 Assistant" instead - you can see what you are doing. If you can't get anywhere with that, post the tnsnames.ora and sqlnet.ora from your server.

Using the Net8 Assistant. When I click/double click service naming then Edit - Create. Nothing happens, which is why I tried the Net8 Configuration Assistant that failed then thought right I should try doing it from the command line, getting myself into a state of desperation resulting in me using the wrong tool. Does it sound like the Net8 Assistant isn't installed properly?

Anyway here is my TSNames.ora

Code:

---

# TNSNAMES.ORA Network Configuration File: I:\ORANT\ora81\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

# --------------------------------------------------------------------

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

# -- FastContinuity Live -- #
FastCont_Live.Nat.BT.com =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 147.149.112.67)(PORT = 1521))
    )
    (CONNECT_DATA = (SID = fcont)(SERVER = DEDICATED))
  )

# -- FastContinuity BackUp -- #
FastCont_BackUp.Nat.BT.com =
  )
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 147.147.240.167)(PORT = 1521))
    )
    (CONNECT_DATA = (SID = fcont)(SERVER = DEDICATED))
  )

# -- FastContinuity Historic connection -- #
FCONT.IUSER.IROOT.ADIDOM.COM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = sryndent98)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = fcont)
    )
  )

---

SQLNet.ora (Could the default domain name be where my problem is?)

Code:

---

# SQLNET.ORA Network Configuration File: I:\ORANT\ora81\NETWORK\ADMIN\sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DEFAULT_DOMAIN = iuser.iroot.adidom.com

SQLNET.AUTHENTICATION_SERVICES= (None)

#NAMES.DEFAULT_DOMAIN= .nat.bt.com
#NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
# SQLNET.AUTHENTICATION_SERVICES= (NTS)

SQLNET.CRYPTO_SEED = 4fhfguweotcadsfdsafjkdsfqp5f201p45mxskdlfdasf

---

Quote:

---

Originally posted by OracleDoc
I would never mess with the tnsnames.ora manually, that thing is just too fragile, one wrong syntax and the whole thing is messed up.

---

Humble apologies gents I see where I've gone wrong I had an extra bracket. Now connects. :o :o :o

Code:

---

# -- FastContinuity BackUp -- #
FastCont_BackUp.Nat.BT.com =
  )# Extra bracket here!!
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 147.147.240.167)(PORT = 1521))
    )
    (CONNECT_DATA = (SID = fcont)(SERVER = DEDICATED))
  )

---

Well spotted! Goes to show that a good way to solve a problem is to try to explain it to someone else.

Dapi & OracleDoc,

Thanks for all your help, I'm a clown! :o Since removing the bracket from my tnsnames the net8 Assistant is working, so will be using that in the future. Lesson learnt!

Regards Al :D