关于ORACLE的ora-12505报错以及连接问题的解决及相关资料
关于ORACLE的ora-12505报错以及连接问题的解决及相关资料 String username = "username";
String password = "password";
connection = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
// Could not find the database driver
} catch (SQLException e) {
// Could not connect to the database
}

----------------
hehe,是我搞错了，在配置文件里的url后面加了个“；”号。
谢谢大家的提醒！非常感谢

=================================
5：以下资料的主要内容是，要使用正确的listener版本
???
http://asktom.oracle.com/pls/ask/f?p=4950:8:16065771798000354453::NO::LAST_PAGE:8:YES???
Q:
I tried it on my Redhat Enterprise 3 and Oracle10g,but sqlplus still failed:
ORA-01034 ORACLE not available
ORA-27101 share memory realm does not exist
Linux Error :2: No such file or directory
I searched on metalink.oracle.com,asktom.oracle.com and others website,peoples
said maybe ORACLE_HOME, ORACLE_SID or kernel parameters are not correct,I tried
in different ways,unfortunately, is not helpful. There are so many people
encounter the problems, why Oracle does not consider to fix it???
thanks,
Lawrence
here are some files:
1.tnsname.ora:
SALES =
( DESCRIPTION =
( ADDRESS = ( PROTOCOL = TCP) (HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sales)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

2.listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =/home/oracle/product/10.1.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST=localhost.localdomain)
(POST = 1521))
)
)
)
3.kernel parameters:
kernel parameters:
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
add following line to:
/etc/security/limits.conf:
* soft nproc 2047
* hard nproc 16384
* soft nofile 1024
* hard nofile 65536
add following line to:
/etc/pam.d/login
add following line to:
session required /lib/security/limits.so
add following line to:
/etc/profile
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

4.bash_profile
PATH = $PATH:$HOME/bin
umask 022
export ORACLE_BASE=/home/oracle
export ORACLE_HOME=ORACLE_BASE/product/10.1.0/db_1
export ORACLE_SID=sales
export ORACLE_TERM=xterm
LD_LIBRARY_PATH=$ORACLE_HOME/jdk/fre/lib/i386:$ORACLE_HOME/jdk/fre
export LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH
5.part of env:
SSH_AGENT_PID=2697
HOSTNAME=localhost.localdomain
SHELL=/bin/bash
TERM=xterm
NLS_LANG=AMERICAN
USER=oracle
LD_LIBRARY_PATH=/home/oracle/product/10.1.0/db_1/jdk/jre/lib/i386:/home/oracle/pr
oduct/10.1.0/db_1/jdk/jre
ORACLE_SID=sales
ORACLE_BASE=/home/oracle
USERNAME=oracle
PATH=/USR/kerberos/bin:/usr/local/bin:/usr/bin:/lib:/usr/X11R6/bin:/home/oracle/b
in:/home/oracle/product/10.1.0/db_1/bin
ORACLE_TERM=xterm
HOME=/home/oracle
LOGNAME=oracle
ORACLE_HOME=/home/oracle/producr/10.1.0/db_1
XAUTHORITY=/home/oracle/.xauthority
OLDPWD=/home/oracle/product/10.1.0/db_1/network/admin



Followup:?
there is nothing to fix, except something in your configuration is why....

and unfortunately you have none of the needed stuff here.
I cannot see what command line you used to try and connect (eg: was it over the
network, local, how)
I cannot see what ORACLE_HOME and ORACLE_SID were used to start the database.
I can see an obvious typo in your ORACLE_HOME environment variable -- producr.?
Hmmm....
?
=====================================

Tom,
We have two instances/environment Production & Staging.
We can logon to the Oracle Database through telnet in both environments.
We can also log on to Production Database through client by using SQL Plus or
Toad.
But we are not able to logon to Staging Database through client.
Following error message comes up:
ORA-12505: TNS :listener could not resolve SID given in connect descriptor
Action 1
---------
Staging (I logon to staging box through telnet)
--------
ps -ef|grep LISTEN
get no values than
Then I run “lsnrctl start"
ps -ef|grep LISTEN
oracle 230 1 0 Jul 09 ? 0:00 /oracle/rev/7.1.3/bin/tnslsnr LISTNER -inherit
Note: Here is the Oracle version of Listner is 7.1.3.
But my Oracle Server Database version is 7.3.4.5
For the Production we have same version 7.3.4 in both scenarios.
Production:
-----------
oracle 7 22084 22057 0 08:27:02 pts/3 0:00 grep LISTEN
oracle 7 310 1 0 Ju; 09 ? 0:00 /disk/oracle/product/7.3.4/bin/tnslsnr LISTNER
-inherit
Action 2
---------
At client machine/PC under Orant/bin we have two sqlplus file. +Plus80w &
+Plus33w
I logon from there as follows:
Note: for Production its works fine but I am getting errors for Staging.
For Staging: I am getting errors as below
sys/manager@witstage
SQL*Plus: Release 3.3.4.0.0 - Production on Mon Jul 26 17:53:56 2004
Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.
ERROR: ORA-12505: TNS:listener could not resolve SID given in connect
descriptor
+Plus80w.
Here is the result from +Plus80w
sys/manager@witstage
SQL*Plus: Release 8.0.5.0.0 - Production on Mon Jul 26 17:46:21 2004
(c) Copyright 1998 Oracle Corporation. All rights reserved.
ERROR: ORA-12505: TNS:listener could not resolve SID given in connect
descriptor

Action 3
--------
I used telnet to go to directories as below
Staging
/disk/app/oracle/product/7.3.4/network/admin
wits2 44: tnsping witstage
TNS Ping Utility for Solaris: Version 2.3.4.0.0 - Production on 26-JUL-04
16:33:
36
Copyright (c) Oracle Corporation 1995. All rights reserved.
Attempting to contact
(ADDRESS=(COMMUNITY=dhs.world)(PROTOCOL=TCP)(Host=wits2)(P
ort=1521))
OK (120 msec)
wits2 45:
?
???
Regards,
Dawar


Followup:?
fairly simple (although your environment is a mess -- you should be using the
listener of the HIGHEST version -- not the lowest)

[tkyte@tkyte-pc tkyte]$ oerr ora 12505
12505, 00000, "TNS:listener could not resolve SID given in connect descriptor"
// *Cause:? The SID in the CONNECT_DATA was not found in the listener's
tables.
// *Action: Check to make sure that the SID specified is correct.
// The SIDs that are currently registered with the listener can be obtained by
// typing "LSNRCTL SERVICES <listener name>". These SIDs correspond to
// SID_NAMEs in TNSNAMES.ORA, or db_names in INIT.ORA.
// *Comment: This error will be returned if the database instance has not
// registered with the listener; the instance may need to be started.

?
?

Tom,
Thanks for your feed back.
Problem has been solved.
I need to stop LISTENER from 7.1.3 directory (disk/rev/7.1.3)? and start from
7.3.4 dir ($ORACLE_HOME) and its works.
Now I am getting correct version for staging environment.
========================
Hi Tom,
I read this page and tried to fix my problem but to no avail!
I have two Oracle Home on a HP-UX? box:
ORACLE_HOME=/opt/oracle/product/9.0.1 db1=nmdb01? db2=nmdb02
ORACLE_HOME=/opt/oracle/product/9.2.0 db1=dbo01
As you see, 9.01 has two DBs and 9.2 has one.
I stopped old listener and started the new one:
oracle@hera> lsnrctl stat
LSNRCTL for HPUX: Version 9.0.1.3.0 - Production on 19-JAN-2005 13:42:35
Copyright (c) 1991, 2001, Oracle Corporation.? All rights reserved.
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hera.nm.cbc.ca)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias???????????????????? LISTENER
Version?????????????????? TNSLSNR for HPUX: Version 9.2.0.1.0 - Production
Start Date??????????????? 19-JAN-2005 12:53:04
Uptime??????????????????? 0 days 0 hr. 49 min. 31 sec
Trace Level?????????????? off
Security????????????????? OFF
SNMP????????????????????? OFF
Listener Parameter File?? /opt/oracle/product/9.2.0/network/admin/listener.ora
Listener Log File???????? /opt/oracle/product/9.2.0/network/log/listener.log
Listening Endpoints Summary...
? (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
? (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hera.nm.cbc.ca)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
? Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "dbo01" has 1 instance(s).
? Instance "dbo01", status READY, has 1 handler(s) for this service...
Service "nmdb01.cbc.ca" has 1 instance(s).
? Instance "nmdb01", status READY, has 1 handler(s) for this service...
Service "nmdb02.cbc.ca" has 1 instance(s).
? Instance "nmdb02", status READY, has 1 handler(s) for this service...
The command completed successfully
oracle@hera>
As you see, listener has an instance for all DBs.
I also added DBs to tnsname.ora on both OracleHome:
dbo01 =
? (DESCRIPTION =
??? (ADDRESS_LIST =
????? (ADDRESS = (PROTOCOL = TCP)(HOST = hera.nm.cbc.ca)(PORT = 1521))
??? )
??? (CONNECT_DATA =
????? (SERVICE_NAME = dbo01)
??? )
? )
nmdb01 =
? (DESCRIPTION =
??? (ADDRESS_LIST =
????? (ADDRESS = (PROTOCOL = TCP)(HOST = hera.nm.cbc.ca)(PORT = 1521))
??? )
??? (CONNECT_DATA =
????? (SERVICE_NAME = nmdb01)
??? )
?? )
nmdb02 =
? (DESCRIPTION =
??? (ADDRESS_LIST =
????? (ADDRESS = (PROTOCOL = TCP)(HOST = hera.nm.cbc.ca)(PORT = 1521))
??? )
??? (CONNECT_DATA =
????? (SERVICE_NAME = nmdb02)
??? )
?? )
Almost everything is set. However, when I connect to the old DB using SQLPLUS, I
can not? connect to the new DBs from there and vice versa. I mean when
ORACLE_HOME is …./9.0.1 using SQLPLUS, I just can connect to ‘nmdb01’ and
‘nmdb02’ using ‘export ORACLE_SID=nmdb02’. I mean even the following failed:
nmdb01> connect system@nmdb02
Enter password:
ERROR:
ORA-12154: TNS:could not resolve service name
Could you please tell me why it is like this? Why can’t I connect to 9.2 DB when
Oracle Home is …./9.2.0 or vice versa? and also why can’t I use ‘connect
system@foo’ in sqlplus?
Best regards,
- Arash

===============
?

Followup:?
you are using the wrong listener software, you always use the HIGHEST version
listener -- 9.2 in this case.
make sure your TNS_ADMIN is not pointing to a different location.
test with
connect system/(description=........)
(eg: remove the tnsnames.ora from the equation for a moment)
do this after using the correct listener.?
?