ORA-12505, TNS:listener does not currently know of SID - 오류 해결하기

org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Listener refused the connection with the following error:

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

 )

        at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)

        at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)

        at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)

위와 같은 메세지가 나오면 다음과 같이 수정하여 Listener를 restart 시킨다. 

• ORA-12505, TNS:listener does not currently know of SID given in connect descriptor 이 메세지 구글링 해보면 lsnrctl services 수행해서 SID가 잘 나오는지 확인해 보라고 하는데 백날 해봐야 SID 안나오고 UNKNOWN 이라고 나온다. 
• 그럼 listener.ora 파일을 열어보자. 하기와 같이 나올 것이다. 

SID_LIST_LISTENER =

  (SID_LIST =    (SID_DESC =      (SID_NAME = PLSExtProc)      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)      (PROGRAM = extproc)    )    (SID_DESC =      (SID_NAME = CLRExtProc)      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)      (PROGRAM = extproc)    )  )
LISTENER =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))    )  )
DEFAULT_SERVICE_LISTENER = (XE)

• 위의 문구를 하기와 같이 수정한다. 

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = XE)

      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)

      (SID_NAME  = XE)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    )

  )

DEFAULT_SERVICE_LISTENER = (XE)

• 서비스에서 Listener를 restart 해보고, lsnrctl services 명령어 날려보자. 하기 빨간색 문구가 새롭게 나온다. 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0

         LOCAL SERVER

Service "XE" has 1 instance(s).

  Instance "XE", status UNKNOWN, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:3 refused:0

         LOCAL SERVER

The command completed successfully

기술적인 부분은 구글링해 보자.