oracle 报错2PC,关于ORACLE的ora-12505报错以及连接问题的解决及相关资料 (2)

最新推荐文章于 2023-07-20 18:08:18 发布

庄黑胖-伍拾万

最新推荐文章于 2023-07-20 18:08:18 发布

阅读量434

点赞数

文章标签： oracle 报错2PC

最近,我在使用jdbc连接oracle9i数据库时，发生如下错误：

Erroroccuredwhiletryingtoconnecttothedatabase

Errorconnectingtodatabase:(usingclassracle.jdbc.driver.OracleDriver)

Listenerrefusedtheconnectionwiththefollowingerror:

ORA-12505,TNS:listenerdoesnotcurrentlyknowofSIDgiveninconnectdescriptor

TheConnectiondescriptorusedbytheclie

Followup:

thereisnothingtofix,exceptsomethinginyourconfigurationiswhy....

andunfortunatelyyouhavenoneoftheneededstuffhere.

Icannotseewhatcommandlineyouusedtotryandconnect(eg:wasitoverthe

network,local,how)

IcannotseewhatORACLE_HOMEandORACLE_SIDwereusedtostartthedatabase.

IcanseeanobvioustypoinyourORACLE_HOMEenvironmentvariable--producr.

Hmmm....

=====================================

Tom,

Wehavetwoinstances/environmentProduction&Staging.

WecanlogontotheOracleDatabasethroughtelnetinbothenvironments.

WecanalsologontoProductionDatabasethroughclientbyusingSQLPlusor

Toad.

ButwearenotabletologontoStagingDatabasethroughclient.

Followingerrormessagecomesup:

ORA-12505:TNS:listenercouldnotresolveSIDgiveninconnectdescriptor

Action1

---------

Staging(Ilogontostagingboxthroughtelnet)

--------

ps-ef|grepLISTEN

getnovaluesthan

ThenIrun“lsnrctlstart"

ps-ef|grepLISTEN

oracle23010Jul09?0:00/oracle/rev/7.1.3/bin/tnslsnrLISTNER-inherit

Note:HereistheOracleversionofListneris7.1.3.

ButmyOracleServerDatabaseversionis7.3.4.5

FortheProductionwehavesameversion7.3.4inbothscenarios.

Production:

-----------

oracle72208422057008:27:02pts/30:00grepLISTEN

oracle731010Ju;09?0:00/disk/oracle/product/7.3.4/bin/tnslsnrLISTNER

-inherit

Action2

---------

Atclientmachine/PCunderOrant/binwehavetwosqlplusfile.+Plus80w&

+Plus33w

Ilogonfromthereasfollows:

Note:forProductionitsworksfinebutIamgettingerrorsforStaging.

ForStaging:Iamgettingerrorsasbelow

sys/manager@witstage

SQL*Plus:Release3.3.4.0.0-ProductiononMonJul2617:53:562004

ERROR:ORA-12505:TNS:listenercouldnotresolveSIDgiveninconnect

descriptor

+Plus80w.

Hereistheresultfrom+Plus80w

sys/manager@witstage

SQL*Plus:Release8.0.5.0.0-ProductiononMonJul2617:46:212004

ERROR:ORA-12505:TNS:listenercouldnotresolveSIDgiveninconnect

descriptor

Action3

--------

Iusedtelnettogotodirectoriesasbelow

Staging

/disk/app/oracle/product/7.3.4/network/admin

wits244:tnspingwitstage

TNSPingUtilityforSolaris:Version2.3.4.0.0-Productionon26-JUL-04

16:33:

Attemptingtocontact

(ADDRESS=(COMMUNITY=dhs.world)(PROTOCOL=TCP)(Host=wits2)(P

ort=1521))

OK(120msec)

wits245:

Regards,

Dawar

Followup:

fairlysimple(althoughyourenvironmentisamess--youshouldbeusingthe

listeneroftheHIGHESTversion--notthelowest)

[tkyte@tkyte-pctkyte]$oerrora12505

12505,00000,"TNS:listenercouldnotresolveSIDgiveninconnectdescriptor"

//*Cause:TheSIDintheCONNECT_DATAwasnotfoundinthelistener's

tables.

//*Action:ChecktomakesurethattheSIDspecifiediscorrect.

//TheSIDsthatarecurrentlyregisteredwiththelistenercanbeobtainedby

//typing"LSNRCTLSERVICES".TheseSIDscorrespondto

//SID_NAMEsinTNSNAMES.ORA,ordb_namesinINIT.ORA.

//*Comment:Thiserrorwillbereturnedifthedatabaseinstancehasnot

//registeredwiththelistener;theinstancemayneedtobestarted.

Tom,

Thanksforyourfeedback.

Problemhasbeensolved.

IneedtostopLISTENERfrom7.1.3directory(disk/rev/7.1.3)andstartfrom

7.3.4dir($ORACLE_HOME)anditsworks.

NowIamgettingcorrectversionforstagingenvironment.

========================

HiTom,

Ireadthispageandtriedtofixmyproblembuttonoavail!

IhavetwoOracleHomeonaHP-UXbox:

ORACLE_HOME=/opt/oracle/product/9.0.1db1=nmdb01db2=nmdb02

ORACLE_HOME=/opt/oracle/product/9.2.0db1=dbo01

Asyousee,9.01hastwoDBsand9.2hasone.

Istoppedoldlistenerandstartedthenewone:

oracle@hera>lsnrctlstat

LSNRCTLforHPUX:Version9.0.1.3.0-Productionon19-JAN-200513:42:35

Connectingto

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hera.nm.cbc.ca)(PORT=1521)))

STATUSoftheLISTENER

------------------------

AliasLISTENER

VersionTNSLSNRforHPUX:Version9.2.0.1.0-Production

StartDate19-JAN-200512:53:04

Uptime0days0hr.49min.31sec

TraceLeveloff

SecurityOFF

SNMPOFF

ListenerParameterFile/opt/oracle/product/9.2.0/network/admin/listener.ora

ListenerLogFile/opt/oracle/product/9.2.0/network/log/listener.log

ListeningEndpointsSummary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hera.nm.cbc.ca)(PORT=1521)))

ServicesSummary...

Service"PLSExtProc"has1instance(s).

Instance"PLSExtProc",statusUNKNOWN,has1handler(s)forthisservice...

Service"dbo01"has1instance(s).

Instance"dbo01",statusREADY,has1handler(s)forthisservice...

Service"nmdb01.cbc.ca"has1instance(s).

Instance"nmdb01",statusREADY,has1handler(s)forthisservice...

Service"nmdb02.cbc.ca"has1instance(s).

Instance"nmdb02",statusREADY,has1handler(s)forthisservice...

Thecommandcompletedsuccessfully

oracle@hera>

Asyousee,listenerhasaninstanceforallDBs.

IalsoaddedDBstotnsname.oraonbothOracleHome:

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)

)

Almosteverythingisset.However,whenIconnecttotheoldDBusingSQLPLUS,I

cannotconnecttothenewDBsfromthereandviceversa.Imeanwhen

ORACLE_HOMEis…./9.0.1usingSQLPLUS,Ijustcanconnectto‘nmdb01’and

‘nmdb02’using‘exportORACLE_SID=nmdb02’.Imeaneventhefollowingfailed:

nmdb01>connectsystem@nmdb02

Enterpassword:

ERROR:

ORA-12154:TNS:couldnotresolveservicename

Couldyoupleasetellmewhyitislikethis?Whycan’tIconnectto9.2DBwhen

OracleHomeis…./9.2.0orviceversa?andalsowhycan’tIuse‘connect

system@foo’insqlplus?

Bestregards,

-Arash

===============

Followup:

youareusingthewronglistenersoftware,youalwaysusetheHIGHESTversion

listener--9.2inthiscase.

makesureyourTNS_ADMINisnotpointingtoadifferentlocation.

testwith

connectsystem/(description=........)

(eg:removethetnsnames.orafromtheequationforamoment)

dothisafterusingthecorrectlistener.

Trackback:http://tb.blog.csdn.net/TrackBack.aspx?PostId=1105713

------当tns或dbconsole出现问题时，oracle10g下相关检查的目录和文件：

1、$oracle_home\localhost_sid\sysman\config,如：C:\oracle\product\10.1.0\Db_1\lslhlh_shmzy\sysman\config

下的emd.properties文件中的emd_url,如：EMD_URL=http://lslhlh:1831/emd/main(lslhlh是我计算机名，即，localhost)

还有C:\oracle\product\10.1.0\Db_1\lslhlh_shmzy\sysman\log\OracleDBConsoleshmzysrvc.log这个日志文件。

对应看一下C:\oracle\product\10.1.0\Db_1\sysman\config路径下的emd.properties文件中的EMD_URL=http://lslhlh:%EM_SERVLET_PORT%/emd/main/

2、C:\oracle\product\10.1.0\Db_1\BIN下的emctl.bat文件

3、C:\oracle\product\10.1.0\admin\shmzy\pfile下的init.ora.214200723148,和C:\oracle\product\10.1.0\Db_1\database下的SPFILESHMZY.ORA。(这两个不能直接在文本编辑器中修改)

4、很重要的C:\oracle\product\10.1.0\Db_1\NETWORK\ADMIN路径下的tnsnames.ora，sqlnet.ora，listener.ora，sid_name,service_name,global_name的区别见上文。

5、注册表中搜索oracle_sid和oracle_home的值。

6、dbconsole还跟C:\oracle\product\10.1.0\Db_1\oc4j\j2ee下的OC4J_DBConsole_lslhlh_shmzy文件夹有关

7、常用的程序有oracle程序菜单中的netmanager和netconfigurationassistant，还用dos窗口下的netca……

问题解决了，

是要注释掉listener.ora中的(PROGRAM=extproc)

但我不知道是为什么？

遇到ORA-28547的人不少，可是原因却各不一样：

[url]http://forums.oracle.com/forums/message.jspa?messageID=693607[/url]

检查tns是否通常的几个常用命令：

C:\DocumentsandSettings\Administrator>tnspingshmzy----tns中的sid_name

TNSPingUtilityfor32-bitWindows:Version10.1.0.2.0-Productionon16-3月-200708:18:29

Usedparameterfiles:

c:\oracle\product\10.1.0\Db_1\network\admin\sqlnet.ora

UsedTNSNAMESadaptertoresolvethealias

Attemptingtocontact(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521

)))(CONNECT_DATA=(SERVICE_NAME=shmzy)))

OK(50msec)

----通过

C:\DocumentsandSettings\Administrator>lsnrctlstatus----start、stop、status

LSNRCTLfor32-bitWindows:Version10.1.0.2.0-Productionon16-3月-200708:18:37

Connectingto(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))

STATUSoftheLISTENER

------------------------

AliasLISTENER

VersionTNSLSNRfor32-bitWindows:Version10.1.0.2.0-Production

StartDate16-3月-200708:13:17

Uptime0days0hr.5min.21sec

TraceLeveloff

SecurityON:LocalOSAuthentication

SNMPOFF

ListenerParameterFilec:\oracle\product\10.1.0\Db_1\network\admin\listener.ora

ListenerLogFilec:\oracle\product\10.1.0\Db_1\network\log\listener.log

ListeningEndpointsSummary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

ServicesSummary...

Service"shmzy"has1instance(s).

Instance"shmzy",statusUNKNOWN,has1handler(s)forthisservice...

Thecommandcompletedsuccessfully

----通过

C:\DocumentsandSettings\Administrator>setoracle_sid=shmzy----有时不通其实就是因为找不到oracle_sid

C:\DocumentsandSettings\Administrator>echo%oracle_sid%

shmzy

C:\DocumentsandSettings\Administrator>emctlstatusdbconsole

OracleEnterpriseManager10gDatabaseControlRelease10.1.0.2.0

http://lslhlh:5501/em/console/aboutApplication

OracleEnterpriseManager10gisrunning.

------------------------------------------------------------------

Logsaregeneratedindirectoryc:\oracle\product\10.1.0\Db_1/lslhlh_shmzy/sysman/log

----通过

SQL>selectopen_modefromv$database;

OPEN_MODE

----------

READWRITE

SQL>showparametersga

NAMETYPEVALUE

--------------------------------------------------------

lock_sgabooleanFALSE

pre_page_sgabooleanFALSE

sga_max_sizebiginteger512M

sga_targetbiginteger0

------

最终找到原因，居然是listener.ora文件配置的问题。晕：

# listener.ora Network Configuration File:

c:\oracle\product\10.1.0\Db_1\network\admin\listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC

(SID_NAME = shmzy)

(ORACLE_HOME = c:\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 = lslhlh)(PORT = 1521))

)

－－－－host必须写主机名lslhlh

庄黑胖-伍拾万

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle 报错2PC,关于ORACLE的ora-12505报错以及连接问题的解决及相关资料 (2)

最近,我在使用jdbc连接oracle9i数据库时，发生如下错误：ErroroccuredwhiletryingtoconnecttothedatabaseErrorconnectingtodatabase:(usingclassracle.jdbc.driver.OracleDriver)Listenerrefusedtheconnectionwiththefollowingerror:ORA-...
复制链接

扫一扫

oracle 报错2PC,关于ORACLE的ora-12505报错以及连接问题的解决及相关资料 (2)

“相关推荐”对你有帮助么？