ORA-12514: TNS:listener does not currently know of service requested in connect

There are no services registered to your listener. Normally they should register automatically but it may take some time. You could also register the services statically. See the Oracle documentation

Are Oracle services started properly? Can you try to put server IP directly, within your TNSnames.ora file?

Host=0.0.0.0 ???? What's that?! This should be the name or address of the server, e.g. 127.0.0.1 or localhost or the DNS name!!

Host=0.0.0.0 means listen an all interfaces in the listener configuration

host 0.0.0.0 ... >  listen an all interfaces, lol i doubt it
try it with 127.0.0.1 or the server name

in sqlplus :
show parameter local_listener

host 0.0.0.0 ... >  listen an all interfaces, lol i doubt it

LOL, I don't. Test it.

Hi Geert G, pls see the attached screenshot

@HuaMin Chen: i tried to put 127.0.0.1 but did not help
or5.PNG

Where did you change the host to 127.0.0.1? In tnsnames or in listener?
You should leave 0.0.0.0 in listener and change to 127.0.0.1 in tnsnames.

local_listener is empty ... that's good
it means the database will register itself automatically on the current host on port 1521

just a trick:
rename the files listener.ora and tnsnames.ora to something different

and restart the listener
> without a listener.ora file, the listener will start on the default port, on the localhost

without supplying what databases it needs to look for in the listener.ora, the listener will not limit the list to what it looks for
any database with an empty local_listener will register on the default port and localhost

after restart the listener, in sqlplus "/ as sysdba":
alter system register;

Is

127.0.0.1

the exact IP of your server? Try to put the exact IP of server instead.

Try running this...

netstat -pluten | grep LISTEN | grep -i oracle

Select allOpen in new window

You can remove each of the two grep commands to produce more detail.

Tip: netstat is your friend + can help to instantly show all listeners.

Tip: As stated above, leave your interface to 0.0.0.0 to begin with, so you're effecting all interfaces. Likely you'll change this eventually to 127.0.0.1 to scope/allow only local connections. If you really do require opening this listener to every IP in the world (shudder), make sure you do this...

1) Wrap the connection with an SSL cert.

2) Run Fail2Ban + anytime 5 failed login attempts occur within an hour (there should be 0 failed attempts), then block the attacking IP for an hour.

Fail2Ban is the unsung hero of the Internet. With Fail2Ban there's no requirement to run IP range ACLs. Just let Fail2Ban block + unblock attacking IPs adaptively, so no work/maintenance on your part.

@HuaMin Chen: I changed to my IP address in listener.ora but it did not help


@Geert G: I renamed the files but could not start the listener after that


@ David Favor: here is the result of "netstat -ano", and I think the server is listening on port 1521



After all, I still got this error:

in the file TNSNAMES.ORA you have to add one  entry to your Instance.

The fact that you can use sqlplus shows that the instance is running. This is good.

Do not use 0.0.0.0   instead use 'localhost'

Please check the content of the file /etc/hosts and publish it here.

Also be aware that you use static registration of the instance but due connection problems it can't be found by the Listener.
Check the TNS_ADMIN environment variable.
Set this variable pointing to the location of the listener.ora file.
Restart the listener.
Check if you have firewalls actiwated on the laptop and deactivate if they are on.

Just because the "sqlplus / as sysdba" shows connected doesn't mean the database is open.  If it isn't open, it will not register with the listener.

Connect as sysdba and run this query:
select open_mode from v$database;

If the database isn't open, the query should return "READ WRITE", check the alert log to see why it isn't opening.

@slightwv (䄆 Netminder): I checked but it is on OPEN MODE:



@schwertner:

Yes I set the variable TNS_ADMIN, set the "localhost" in tnsname.ora file but it did not help; and I'm trying to connect from the server itself, not from a Laptop client

>> I checked but it is on OPEN MODE:

Your database isn't open therefore cannot register with the listener.  It is only Mounted hence the MOUNTED value in the query.

I posted that it should say "READ WRITE" if it is open.

From the sqlplus prompt:
alter database open;

Post the error if it doesn't open.

In my 25+ years of installing and working with Oracle databases on a variety of operating systems, I usually consider getting the listener.ora and tnsnames.ora file values correct to be the most challenging part of an Oracle database install.

Yes, by default Oracle will use TCP/IP port: 1521.

Oracle also provides a utility that you can use to check the tnsnames/listener combination.  That is: tnsping.exe.  This O/S executable will be in your $ORACLE_HOME/bin directory.  I expect your $PATH variable will include your $ORACLE_HOME/bin directory, so as the oracle software owner, on your server, run:
tnsping GLOBALDB [Enter]

That should return 5-6 lines of text with the last line starting with "OK".

If that returns an ORA-xxxxx or TNS-xxxxx error on the last line, then some part of your listener.ora or tnsnames.ora file is incorrect.

@slightwv (䄆 Netminder): Actually you posted the opposite :)


btw, here is the result of your instructions:


@Mark Geerlings: Hi, it is ok I think:

There is something known as loopback adapter.
In Unix there should be an entry in /etc/hosts

https://en.wikipedia.org/wiki/Hosts_(file)

Sofar I understand there is static registration in the listener.ora file.
There is network problrm.

1. Firewall activated  - port 1521 should be open
2. Missing entry in /etc/hosts

>>Actually you posted the opposite

oops... sorry.   ;)

"READ WRITE" means open and ready for business.

>>btw, here is the result of your instructions:

The database was open before you tried to open it.  Anyway, with the database open, check the listener status:  lsnrctl status

You should see the instance registered.  If not, try to force it from the sqlplus prompt:
alter system register;

>>There is network problrm.

Sorry but I believe the database wasn't open in all the previous tests.  I do reserve the right to be completely wrong but when I posted the suggestion to check, it was only mounted.

Hi, I installed version 12c on Centos7 this morning. it completed successfully but when I type "sqlplus", it stated that "command not found". Cna you please help?

This is a different question and should have been asked as a new question since it has nothing to do with the original.

Anyway, the backslash in your exports for ORACLE_HOME, PATH and the rest are escaping the variable substitution.

It should be:
export PATH=$ORACLE_HOME/bin:$PATH

Same for the rest.

If you do an echo you will see this:
echo $PATH

Also, Oracle should have set most of that for you through /etc/oratab and the install.  You shouldn't need to set them manually.

Hi, I installed version 12c on Centos7 this morning. it completed successfully but when I type "sqlplus", it stated that "command not found". Cna you please help?

Oracle is not being supported in Cent OS, and better option (to OS) could be Oracle Red hat instead.

Not Supported does not mean "doesn't run".  My guess is this is just for test and education so it should be fine.

I agree.