Error-ORA-12505: TNS:listener does not currently know of SID given in conne

[Former Member]

Dears,

On our AIX 6.1 server we were having one PI dev(PD1) instance,Now we have installed one PI QAS(PQ1) instance on it.

Now issue is that PD1 is working fine but while starting PQ1 instance it is giving error:

I have also changed port in listner,tnsnames.ora and in configtool to 1526 for PQ1 instance.

Checking PQ1 Database

-

---

Database is not available via R3trans

Running /usr/sap/PQ1/SYS/exe/run/startdb

Trying to start PQ1 database ...

Log file: /home/pq1adm/startdb.log

/usr/sap/PQ1/SYS/exe/run/startdb: Terminating with error code 12

DB startup failed

While by SQLPLUS I checked that oracle is up but issue is SAP is not able to connect to oracle.

R3trans -x

This is R3trans version 6.18 (release 710 - 08.10.07 - 13:37:00).

unicode enabled version

2EETW169 no connect possible: "DBMS = ORACLE --- dbs_ora_tnsname = 'PQ1'"

R3trans finished (0012).

trans.log says:

ORA-12505: TNS:listener does not currently know of SID given in connect descriptor'

Please suggest.

Shivam

[Former Member]

ORA-12505: TNS:listener does not currently know of SID given in connect descriptor - YouTube

this can help you..

[Former Member]

In /profile/oracle directory port entry was not changed.

[Former Member]

Hi Shivam,

ORA-12505 means The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a SID for an instance (usually a database instance) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.

Action: -

1)Wait a moment and try to connect a second time.

2) Check which instances are currently known by the listener by executing: lsnrctl services <listener name>

These SIDs correspond to SID_NAMEs in TNSNAMES.ORA or DB_NAME in the initialisation file.

3) Check that the SID parameter in the connect descriptor specifies an instance known by the listener.

4) Check for an event in the listener.log file.

You can also refer this link ..

Regds,

Pinangshuk.

[Former Member]

Dears,

Content of the files are:

tnsnames.ora

PQ1.WORLD=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS =

(COMMUNITY = SAP.WORLD)

(PROTOCOL = TCP)

(HOST = pidev)

(PORT = 1526)

)

)

(CONNECT_DATA =

(SID = PQ1)

(GLOBAL_NAME = PQ1.WORLD)

)

)

listner.ora

ADMIN_RESTRICTIONS_LISTENER = on

LISTENER =

(ADDRESS_LIST =

(ADDRESS =

(PROTOCOL = IPC)

(KEY = PQ1.WORLD)

)

(ADDRESS=

(PROTOCOL = IPC)

(KEY = PQ1)

)

(ADDRESS =

(COMMUNITY = SAP.WORLD)

(PROTOCOL = TCP)

(HOST = pidev)

(PORT = 1526)

)

)

STARTUP_WAIT_TIME_LISTENER = 0

CONNECT_TIMEOUT_LISTENER = 10

TRACE_LEVEL_LISTENER = OFF

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PQ1)

(ORACLE_HOME = /oracle/PQ1/102_64)

)

)

Output of R3trans -x

pidev:pq1adm> R3trans -x

This is R3trans version 6.18 (release 710 - 08.10.07 - 13:37:00).

unicode enabled version

2EETW169 no connect possible: "DBMS = ORACLE --- dbs_ora_tnsname = 'PQ1'"

R3trans finished (0012).

Service file entries are:

#pdap-np 1526/tcp # Prospero Data Access Prot non-priv

#pdap-np 1526/udp # Prospero Data Access Prot non-priv

tlisrv 1527/tcp # oracle

tlisrv 1527/udp # oracle

tlisrv 1526/tcp

tlisrv 1526/udp

trans.log contains:

4 ETW000 [dboci.c ,00000] *** ERROR => OCI-call 'OCIServerAttach' failed: rc = 12505

4 ETW000 55 0.017769

4 ETW000 [dbsloci. ,00000] *** ERROR => CONNECT failed with sql error '12505'

4 ETW000 52 0.017821

4 ETW000 [dev trc ,00000] set_ocica() -> OCI or SQL return code 12505 31 0.017852

4 ETW000 [dev trc ,00000] OCIErrorGet -> SQL error code: 0 70 0.017922

4 ETW000 [dev trc ,00000] error text: 'ORA-12505: TNS:listener does not currently know of SID given in connect descriptor'

4 ETW000 48 0.017970

4 ETW000 [dblink ,00000] ***LOG BY2=>sql error 12505 performing CON 71 0.018041

4 ETW000 [dblink ,00000] ***LOG BY0=>ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

4 ETW000 61 0.018102

2EETW169 no connect possible: "DBMS = ORACLE --- dbs_ora_tnsname = 'PQ1'"

Please suggest.

Shivam

[Former Member]

Hi,

Normally this error comes if the database configured in the tnsnames.ora is not available in the database...

so either check in the database whether that database is available or not ..or provided the different database names in config file

HTH

Rajesh

[ravi_raman2]

As part of the Net8 connect, Oracle clients (such as R/3 work processes) use the configuration file tnsnames.ora to create a link to the listener, which in turn is configured with the listener.ora file. In tnsnames.ora, you can now define the SID used in the corresponding CONNECT_DATA section

(CONNECT_DATA =

(SID = C11)

(GLOBAL_NAME = C11.WORLD)

)

This SID must also feature as SID_NAME in the listener.ora in the SID_LIST_LISTENER section:

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = C11)

(ORACLE_HOME = /oracle/C11/817_64)

)

)

Make sure that several listeners are not listening to the same TCP/IP port. Either use only one listener for all instances on the system or use different ports for each ndividual listener.

Regards

Ravi Raman