ORA-12514 TNS : listener가 현재 접속 설명자에서 요청한 서비스를 모릅니다

ORA-12514 TNS : listener가 현재 접속 설명자에서 요청한 서비스를 모릅니다

---

다음 오류가 발생하는 로컬에서 실행중인 응용 프로그램이 있습니다.

ORA-12514 : TNS : listener는 (는) 현재 연결 설명자에서 요청한 서비스를 알지 못합니다.

TNSPing올바르게 해결 된 연결을 테스트하고 연결 SQLPlus을 시도 했지만 위와 동일한 오류로 실패했습니다. 이 구문을 SQLPlus다음 용도로 사용했습니다 .

sqlplus username/password@addressname[or host name]

우리는 다음을 확인했습니다.

• 서버에서 TNS 리스너가 실행 중입니다.
• 서버에서 Oracle 자체가 실행 중입니다.

우리는이 환경에 어떤 변화가 있었는지 모른다. 우리가 테스트 할 수있는 다른 것이 있습니까?

---

나는이 문제가 있었고 수정은 데이터베이스에서 유효한 서비스 이름 인지 확인 tnsnames.ora하는 SERVICE_NAME것이 었습니다 . 유효한 서비스 이름을 찾으려면 oracle에서 다음 쿼리를 사용할 수 있습니다.

select value from v$parameter where name='service_names'

내가 업데이트 한 tnsnames.ora후 :

TEST =
   (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = *<validhost>*)(PORT = *<validport>*))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = *<servicenamefromDB>*)
    )
)

그런 다음 나는 달렸다.

sqlplus user@TEST

성공! 리스너는 기본적으로 사용중인 service_name이 DB에 따라 유효한 서비스가 아니라고 알려줍니다.

(* Win7 클라이언트 워크 스테이션에서 원격 DB로 sqlplus를 실행하고 DBA를 비난했습니다.) *)

---

나는 이것이 오래된 질문이지만 여전히 답이 없다는 것을 알고 있습니다. 하루 동안의 연구가 필요했지만 적어도 내 경우 (Windows 2008 R2의 Oracle 11.2)에서 가장 간단한 솔루션을 발견하고 공유하고 싶었습니다.

직접 보면 오류는 리스너가 서비스 이름을 인식하지 못함을 나타냅니다. 그러나 서비스 이름은 어디에 유지됩니까? 에%ORACLE_HOME%\NETWORK\ADMIN\listener.ora

"SID_LIST"는 복사 또는 조회 할 수있는 형식으로 SID 및 서비스 이름 목록이 결합 된 것입니다.

서비스 이름 문제를 추가 한 다음 Windows "서비스"제어판에서 Oracle 리스너 서비스에서 "다시 시작"을 수행했습니다. 이제 모든 것이 잘되었습니다.

---

예를 들어 listener.ora 파일은 처음에 다음과 같습니다.

# listener.ora Network Configuration File: C:\app\oracle_user\product\12.1.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\oracle_user\product\12.1.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\oracle_user\product\12.1.0\dbhome_1\bin\oraclr12.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

... 및 서비스 이름을 인식하기 위해 다음과 같이 orcl변경할 수 있습니다.

# listener.ora Network Configuration File: C:\app\oracle_user\product\12.1.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\oracle_user\product\12.1.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\oracle_user\product\12.1.0\dbhome_1\bin\oraclr12.dll")
    )
    (SID_DESC = 
        (GLOBAL_DBNAME = orcl)
        (ORACLE_HOME = C:\app\oracle_user\product\12.1.0\dbhome_1)
        (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

---

Windows Server 2008 R2 및 Oracle 11g 에서이 문제가 발생했습니다.

Netbox> 리스너로 이동하여 combox에서 데이터베이스 서비스 선택> "글로벌 데이터베이스 이름"이 "SID"와 같아야하고 "Oracle 홈 디렉토리"가 정확해야합니다.

If you don't have any entry for database services, create one and set correct global database , sid and oracle home.

---

In my circumstances the error was due to the fact the listener did not have the db's service registered. I solved this by registering the services. Example:

My descriptor in tnsnames.ora:

LOCALDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = LOCALDB)
    )
  )

So, I proceed to register the service in the listener.ora manually:

SID_LIST_LISTENER =
    (SID_DESC =
      (GLOBAL_DBNAME = LOCALDB)
      (ORACLE_HOME = C:\Oracle\product\11.2.0\dbhome_1)
      (SID_NAME = LOCALDB)
    )

Finally, restart the listener by command:

> lsnrctl stop
> lsnrctl start

Done!

---

Starting the OracleServiceXXX from the services.msc worked for me in Windows.

---

This really should be a comment to Brad Rippe's answer, but alas, not enough rep. That answer got me 90% of the way there. In my case, the installation and configuration of the databases put entries in the tnsnames.ora file for the databases I was running. First, I was able to connect to the database by setting the environment variables (Windows):

set ORACLE_SID=mydatabase
set ORACLE_HOME=C:\Oracle\product\11.2.0\dbhome_1

and then connecting using

sqlplus / as sysdba

Next, running the command from Brad Rippe's answer:

select value from v$parameter where name='service_names';

showed that the names didn't match exactly. The entries as created using Oracle's Database Configuration Assistant where originally:

MYDATABASE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mylaptop.mydomain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydatabase.mydomain.com)
    )
  ) 

The service name from the query was just mydatabase rather than mydatabase.mydomain.com. I edited the tnsnames.ora file to just the base name without the domain portion so they looked like this:

MYDATABASE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mylaptop.mydomain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydatabase)
    )
  ) 

I restarted the TNS Listener service (I often use lsnrctl stop and lsnrctl start from an administrator command window [or Windows Powershell] instead of the Services control panel, but both work.) After that, I was able to connect.

---

Check to see the database is up. Log onto the server, set the ORACLE_SID environment variable to your database SID, and run SQL*Plus as a local connection.

---

This error can occur when an application makes a new connection for every database interaction or the connections are not closed properly. One of the free tools to monitor and confirm this is Oracle Sql developer (although this is not the only tool you can use to monitor DB sessions).

you can download the tool from oracle site Sql Developer

here is a screenshot of how to monitor you sessions. (if you see many sessions piling up for your application user during when you see the ORA-12514 error then it's a good indication that you may have connection pool problem).

---

i had also faced same problem and spent 3 days to dig it out. This happens because of your wrong TNS service entry. First check whether you are able to connect to standby database from primary database using sql > sqlplus sys@orastand as sysdba ( orastand is standby database) , if you are not able to connect then it is problem with service. Correct the entry of service name in TNS file at primary end. Check same way in standby database , if requires make the changes here too. and Make sure the log_archive_dest_2 parmater has correct service name.

---

I resolved this issue in my linux enviroment updating the IP of my machine in /etc/hosts file.

You can verify your network IP (inet end.) with:

$ifconfig

See if your IP matches with /etc/hosts file:

$cat /etc/hosts

Edit your /etc/hosts file, if nedded:

$sudo gedit /etc/hosts

Bye.

---

I had the same problem, for me just writing

sqlplus myusername/mypassword@localhost

did the trick, doing so makes it connect to the default service name i guess.

---

For those that may be running Oracle in a VM (like me) I saw this issue because my VM was running out of memory, which seems to have prevented OracleDB from starting up/running correctly. Increasing my VM memory and restarting fixed the issue.

---

what worked for me was really simple, I just needed to initiate the service manually in the "Windows Services" (services.msc in cmd trompt). my service name is: OracleServiceXXXXX.

---

Lots of answers here, but here comes a working example with code that you can copy and paste and test immediately:

For me the error 12514 was solved after specifying the correct SERVICE_NAME. You find that on the server in the file tnsnames.ora which comes with 3 predefined service names (one of them is "XE").

1. I installed the Oracle Express database OracleXE112 which already comes with some preinstalled demo tables.
2. When you start the installer you are asked for a password. I entered "xxx" as password. (not used in production)
3. My server runs on the machine 192.168.1.158
4. On the server you must explicitely allow access for the process TNSLSNR.exe in the Windows Firewall. This process listens on port 1521.
5. OPTION A: For C# (.NET2 or .NET4) you can download ODAC11, from which you have to add Oracle.DataAccess.dll to your project. Additionally this DLL depends on: OraOps11w.dll, oci.dll, oraociei11.dll (130MB!), msvcr80.dll. These DLLs must be in the same directory as the EXE or you must specify the DLL path in: HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\ODP.NET\4.112.4.0\DllPath. On 64 bit machines write additionally to HKLM\SOFTWARE\Wow6432Node\Oracle\...
6. OPTION B: If you have downloaded ODAC12 you need Oracle.DataAccess.dll, OraOps12w.dll, oci.dll, oraociei12.dll (160MB!), oraons.dll, msvcr100.dll. The Registry path is HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\ODP.NET\4.121.2.0\DllPath
7. OPTION C: If you don't want huge DLL's of more than 100 MB you should download ODP.NET_Managed12.x.x.x.xxxxx.zip in which you find Oracle.ManagedDataAccess.dll which is only 4 MB and is a pure managed DLL which works in 32 bit and 64 bit processes as well and depends on no other DLL and does not require any registry entries.
8. The following C# code works for me without any configuration on the server side (just the default installation):

using Oracle.DataAccess.Client;
or
using Oracle.ManagedDataAccess.Client;

....

string oradb = "Data Source=(DESCRIPTION="
    + "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.158)(PORT=1521)))"
    + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)));"
    + "User Id=SYSTEM;Password=xxx;";

using (OracleConnection conn = new OracleConnection(oradb)) 
{
    conn.Open();
    using (OracleCommand cmd = new OracleCommand())
    {
        cmd.Connection  = conn;
        cmd.CommandText = "select TABLESPACE_NAME from DBA_DATA_FILES";

        using (OracleDataReader dr = cmd.ExecuteReader())
        {
            while (dr.Read())
            {
                listBox.Items.Add(dr["TABLESPACE_NAME"]);
            }
        }
    }
}

If the SERVICE_NAME=XE is wrong you get error 12514. The SERVICE_NAME is optional. You can also leave it away.

---

In my case the database had ran out of disk space. Which caused it to not respond. Once I cleared up that issue everything worked again.

---

I got the same error because the remote SID specified was wrong:

 > sqlplus $DATASOURCE_USERNAME/$DATASOURCE_PASSWORD@$DB_SERVER_URL/$REMOTE_SID 

I queried the system database:

select * from global_name;

and found my remote SID ("XE").

Then I could connect without any problem.

---

For me this was caused by using a dynamic ipadress using installation. I reinstalled Oracle using a static ipadress and then everything was fine

---

Restarting the VM worked for me

---

My issue was resolved by replacing the'SID' in URL with 'service name' and correct host.

---

I have done below work around to resolve this issue.

1. I have set the oracle_home in cmd prompt (right click cmd.exe Run as System administrator). used below command

   set oracle_home="path to the oracle home"

i had oracle installed in my D: drive.

2. And Go to All programs --> Oracle -ora home1 --> Configuration migration tools Net Manager -->Listener --> select Database Services from dropdown -->Global database name and SID both are set to same in my case it is ORCL, set oracle_home directory. Click on File and save network configuration.

참고URL : https://stackoverflow.com/questions/10786782/ora-12514-tnslistener-does-not-currently-know-of-service-requested-in-connect-d