OraFAQ Forum: Networking and Gateways » ORA-12514: TNS

Home » RDBMS Server » Networking and Gateways » ORA-12514: TNS (Oracle 12c Release 2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-12514: TNS [message #673388]

Mon, 19 November 2018 06:20

petar97
Messages: 120
Registered: October 2018

Senior Member

When I tried to connect as sqlplus / as sysdba
I get this error ORA-12514: TNS: the listener currently does not know about the service requested in the connect descriptor

This looks like a listener.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2/db_1/network/adm$
# Generated by Oracle configuration tools

ORCL12C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl12c)
    )
  )

LISTENER_ORCL12C =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

I guess I need to change something here, but I do not know what I do not have experience with the linux operating system

I have read that this problem can come in many ways, I tried to sql developer to connect as a sqlplus and since then I have this error.

Report message to a moderator

Re: ORA-12514: TNS [message #673391 is a reply to message #673388]

Mon, 19 November 2018 06:34

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Post the result of "env | sort".

[Updated on: Mon, 19 November 2018 06:35]

Report message to a moderator

Re: ORA-12514: TNS [message #673392 is a reply to message #673391]

Mon, 19 November 2018 06:39

petar97
Messages: 120
Registered: October 2018

Senior Member

How can I find them?

[Updated on: Mon, 19 November 2018 06:41]

Report message to a moderator

Re: ORA-12514: TNS [message #673393 is a reply to message #673392]

Mon, 19 November 2018 06:42

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

ora-12514 ALWAYS only occurs due to a problem on DB Server system.
One cause of this problem is when the Oracle database is down & needs to be started.
A remote client send a request to the Listener asking to be connected to a specific service.
If/when the listener does not know anything about that service, the listener responds with ora-12514

post results from the following two OS commands:
lsnrctl status
lsnrctl service

Since every connection request to the listener gets logged, listener.log file will contain a line with 12514 as status code.

This line contains valuable debugging details. So post this line & surrounding lines.

for additional debugging suggestions read the URL below:
http://edstevensdba.wordpress.com/2011/03/19/ora-12514/

Report message to a moderator

Re: ORA-12514: TNS [message #673394 is a reply to message #673393]

Mon, 19 November 2018 06:50

petar97
Messages: 120
Registered: October 2018

Senior Member

For lsnrctl status I get

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 19-NOV-2018 07:47:32

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                19-NOV-2018 06:54:14
Uptime                    0 days 0 hr. 53 min. 19 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           orcl12c
Listener Parameter File   /u01/app/oracle/product/12.2/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
Services Summary...
Service "orcl12c" has 2 instance(s).
  Instance "orcl12c", status UNKNOWN, has 1 handler(s) for this service...
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
The command completed successfully

For lsnrctl status I get

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 19-NOV-2018 07:49:35

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                19-NOV-2018 06:54:14
Uptime                    0 days 0 hr. 55 min. 22 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           orcl12c
Listener Parameter File   /u01/app/oracle/product/12.2/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
Services Summary...
Service "orcl12c" has 2 instance(s).
  Instance "orcl12c", status UNKNOWN, has 1 handler(s) for this service...
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
The command completed successfully

Through the listener.log, a similar text is repeated

TNS-01106: Listener using listener name LISTENER has already been started
/u01/app/oracle/product/12.2/db_1/bin/dbshut: Stoping Oracle Net Listener

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 12-JUN-2017 16:58:04

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
/u01/app/oracle/product/12.2/db_1/bin/dbstart: Starting Oracle Net Listener

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 22-OCT-2018 11:50:16

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

[Updated on: Mon, 19 November 2018 06:58]

Report message to a moderator

Re: ORA-12514: TNS [message #673395 is a reply to message #673394]

Mon, 19 November 2018 07:02

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Since every connection request to the listener gets logged, listener.log file will contain a line with 12514 as status code.

This line contains valuable debugging details. So post this line & surrounding lines.

Report message to a moderator

Re: ORA-12514: TNS [message #673396 is a reply to message #673395]

Mon, 19 November 2018 07:09

petar97
Messages: 120
Registered: October 2018

Senior Member

I copied the listener.log into a text document and searched, there is no 12514

Report message to a moderator

Re: ORA-12514: TNS [message #673398 is a reply to message #673396]

Mon, 19 November 2018 07:13

petar97
Messages: 120
Registered: October 2018

Senior Member

the error has changed now
ORA-12154: TNS:could not resolve the connect identifier specified

I changed the listener.ora, but I brought it back to the old one
I do not know what caused the error to change

Report message to a moderator

Re: ORA-12514: TNS [message #673399 is a reply to message #673396]

Mon, 19 November 2018 07:14

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

find / -name listener.log -ls 2>/dev/null

COPY & PASTE results from OS command above (It will take a long time to complete)

Report message to a moderator

Re: ORA-12514: TNS [message #673400 is a reply to message #673399]

Mon, 19 November 2018 07:15

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

ora-12154 ALWAYS only occurs on SQL Client & no SQL*Net packets ever leave client system
ora-12154 NEVER involves the listener, the database itself or anything on the DB Server.
ora-12154 occurs when client requests a connection to some DB server system using some connection string.
TNS-03505 is thrown by tnsping & is same error as ora-12154 thrown by sqlplus or others.
The lookup operation fails because the name provided can NOT be resolved to any remote DB.
The analogous operation would be when you wanted to call somebody, but could not find their name in any phonebook.
The most frequent cause for the ora-12154 error is when the connection alias can not be found in tnsnames.ora.
The lookup operation of the alias can be impacted by the contents of the sqlnet.ora file; specifically DOMAIN entry.
TROUBLESHOOTING GUIDE: ora-12154 & TNS-12154 TNS:could not resolve service name [ID 114085.1]
http://edstevensdba.com/oracle-tns/ora-12154tns-03505/

Report message to a moderator

Re: ORA-12514: TNS [message #673401 is a reply to message #673399]

Mon, 19 November 2018 07:17

petar97
Messages: 120
Registered: October 2018

Senior Member

10966247 32 -rw-r--r-- 1 oracle oinstall 27193 Nov 19 06:54 /u01/app/oracle/product/12.2/db_1/listener.log
44882611 824 -rw-r----- 1 oracle oinstall 839150 Nov 19 08:08 /u01/app/oracle/diag/tnslsnr/localhost/listener/trace/listener.log

Report message to a moderator

Re: ORA-12514: TNS [message #673402 is a reply to message #673388]

Mon, 19 November 2018 07:19

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

petar97 wrote on Mon, 19 November 2018 06:20

When I tried to connect as sqlplus / as sysdba
I get this error ORA-12514: TNS: the listener currently does not know about the service requested in the connect descriptor

A simple 'sqlplus / as sysdba' should not return any tns error, because it doesn't go through tns to make a local (BEQueath) connection.

You need to issue the following commands, then COPY and PASTE the entire command session, both the commands and the results:

env | grep ORA | sort
sqlplus / as sysdba

Once we see the output of that, we can know what to look at next. Until we see the output of that we are shooting in the dark.

Report message to a moderator

Re: ORA-12514: TNS [message #673403 is a reply to message #673400]

Mon, 19 November 2018 07:20

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please realize & understand that NEVER in this thread have you actually SHOWED us the command that you report produces posted errors.

We can't say what you do wrong, since you refuse to show what you actually do.

BTW - listener.ora is NOT required to have the listener start & run without error.

Report message to a moderator

Re: ORA-12514: TNS [message #673404 is a reply to message #673402]

Mon, 19 November 2018 07:23

petar97
Messages: 120
Registered: October 2018

Senior Member

[oracle@localhost ~]$ env | grep ORA | sort
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.2/db_1
ORACLE_SID=orcl12c
ORACLE_UNQNAME=orcl12c
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Nov 19 08:21:33 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
Enter user-name: system
Enter password: 
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

[Updated on: Mon, 19 November 2018 07:37]

Report message to a moderator

Re: ORA-12514: TNS [message #673405 is a reply to message #673403]

Mon, 19 November 2018 07:56

petar97
Messages: 120
Registered: October 2018

Senior Member

BlackSwan wrote on Mon, 19 November 2018 07:20

Please realize & understand that NEVER in this thread have you actually SHOWED us the command that you report produces posted errors.

sorry, I'll change that

Report message to a moderator

Re: ORA-12514: TNS [message #673406 is a reply to message #673404]

Mon, 19 November 2018 07:58

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>sqlplus / as sysdba
Above should connect to a local database which does NOT require use of SQL*Net.
Therefore should "never" throw ORA-12154 error.

This can results when OS/networking is mis-configured.
Simply put this system does not know who it is.

post results from OS commands below using COPY & PASTE showing actual commands issued.

ping <hostname>
ping <IP#>

where you supply correct values for placeholders within "<>" characters

Report message to a moderator

Re: ORA-12514: TNS [message #673407 is a reply to message #673404]

Mon, 19 November 2018 08:06

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Once again, post the result of "env | sort".

[Updated on: Mon, 19 November 2018 08:06]

Report message to a moderator

Re: ORA-12514: TNS [message #673408 is a reply to message #673407]

Mon, 19 November 2018 08:11

petar97
Messages: 120
Registered: October 2018

Senior Member

[oracle@localhost ~]$ env | sort
CLASSPATH=/u01/app/oracle/product/12.2/db_1/jlib:/u01/app/oracle/product/12.2/db_1/rdbms/jlib
DBENV=true
DBUS_SESSION_BUS_ADDRESS=unix:abstract=/tmp/dbus-ogoOSOsUjR,guid=e45dc2017858555721e9b8715bf2a48e
DESKTOP_AUTOSTART_ID=10c2342d17fedf69b1154262850619624300000037890003
DESKTOP_SESSION=gnome-classic
DISPLAY=:0
GDM_LANG=en_US.UTF-8
GDMSESSION=gnome-classic
GNOME_CHECK=0
GNOME_DESKTOP_SESSION_ID=this-is-deprecated
GNOME_SHELL_SESSION_MODE=classic
GPG_AGENT_INFO=/run/user/1000/keyring/gpg:0:1
HISTCONTROL=ignoredups
HISTSIZE=1000
HOME=/home/oracle
HOSTNAME=localhost.localdomain
IMSETTINGS_INTEGRATE_DESKTOP=yes
IMSETTINGS_MODULE=none
JAVAENV=true
JAVA_HOME=/home/oracle/java/jdk1.8.0_131
LANG=en_US.UTF-8
LESSOPEN=||/usr/bin/lesspipe.sh %s
LOGNAME=oracle
LS_COLORS=rs=0:di=38;5;27:ln=38;5;51:mh=44;38;5;15:pi=40;38;5;11:so=38;5;13:do=38;5;5:bd=48;5;232;38;5;11:cd=48;5;232;38;5;3:or=48;5;232;38;5;9:mi=05;48;5;232;38;5;15:su=48;5;196;38;5;15:sg=48;5;11;38;5;16:ca=48;5;196;38;5;226:tw=48;5;10;38;5;16:ow=48;5;10;38;5;21:st=48;5;21;38;5;15:ex=38;5;34:*.tar=38;5;9:*.tgz=38;5;9:*.arc=38;5;9:*.arj=38;5;9:*.taz=38;5;9:*.lha=38;5;9:*.lz4=38;5;9:*.lzh=38;5;9:*.lzma=38;5;9:*.tlz=38;5;9:*.txz=38;5;9:*.tzo=38;5;9:*.t7z=38;5;9:*.zip=38;5;9:*.z=38;5;9:*.Z=38;5;9:*.dz=38;5;9:*.gz=38;5;9:*.lrz=38;5;9:*.lz=38;5;9:*.lzo=38;5;9:*.xz=38;5;9:*.bz2=38;5;9:*.bz=38;5;9:*.tbz=38;5;9:*.tbz2=38;5;9:*.tz=38;5;9:*.deb=38;5;9:*.rpm=38;5;9:*.jar=38;5;9:*.war=38;5;9:*.ear=38;5;9:*.sar=38;5;9:*.rar=38;5;9:*.alz=38;5;9:*.ace=38;5;9:*.zoo=38;5;9:*.cpio=38;5;9:*.7z=38;5;9:*.rz=38;5;9:*.cab=38;5;9:*.jpg=38;5;13:*.jpeg=38;5;13:*.gif=38;5;13:*.bmp=38;5;13:*.pbm=38;5;13:*.pgm=38;5;13:*.ppm=38;5;13:*.tga=38;5;13:*.xbm=38;5;13:*.xpm=38;5;13:*.tif=38;5;13:*.tiff=38;5;13:*.png=38;5;13:*.svg=38;5;13:*.svgz=38;5;13:*.mng=38;5;13:*.pcx=38;5;13:*.mov=38;5;13:*.mpg=38;5;13:*.mpeg=38;5;13:*.m2v=38;5;13:*.mkv=38;5;13:*.webm=38;5;13:*.ogm=38;5;13:*.mp4=38;5;13:*.m4v=38;5;13:*.mp4v=38;5;13:*.vob=38;5;13:*.qt=38;5;13:*.nuv=38;5;13:*.wmv=38;5;13:*.asf=38;5;13:*.rm=38;5;13:*.rmvb=38;5;13:*.flc=38;5;13:*.avi=38;5;13:*.fli=38;5;13:*.flv=38;5;13:*.gl=38;5;13:*.dl=38;5;13:*.xcf=38;5;13:*.xwd=38;5;13:*.yuv=38;5;13:*.cgm=38;5;13:*.emf=38;5;13:*.axv=38;5;13:*.anx=38;5;13:*.ogv=38;5;13:*.ogx=38;5;13:*.aac=38;5;45:*.au=38;5;45:*.flac=38;5;45:*.mid=38;5;45:*.midi=38;5;45:*.mka=38;5;45:*.mp3=38;5;45:*.mpc=38;5;45:*.ogg=38;5;45:*.ra=38;5;45:*.wav=38;5;45:*.axa=38;5;45:*.oga=38;5;45:*.spx=38;5;45:*.xspf=38;5;45:
MAIL=/var/spool/mail/oracle
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.2/db_1
ORACLE_SID=orcl12c
ORACLE_UNQNAME=orcl12c
PATH=/home/oracle/Desktop/Database_Track/coffeeshop:/home/oracle/bin:/home/oracle/LDLIB:/u01/app/oracle/product/12.2/db_1/bin:/usr/sbin:/home/oracle/Desktop/Database_Track/coffeeshop:/home/oracle/java/jdk1.8.0_131/bin:/home/oracle/bin:/home/oracle/sqlcl/bin:/home/oracle/sqldeveloper:/home/oracle/datamodeler:/usr/local/bin:/usr/local/sbin:/usr/bin:/usr/sbin:/bin:/sbin:/home/oracle/sqlcl/bin:/home/oracle/sqldeveloper:/home/oracle/bin:/home/oracle/.local/bin:/home/oracle/bin
PWD=/home/oracle
QT_IM_MODULE=ibus
result=
SESSION_MANAGER=local/unix:@/tmp/.ICE-unix/3789,unix/unix:/tmp/.ICE-unix/3789
SHELL=/bin/bash
SHLVL=4
SOFAR=0
SQL_OR_SQLPLUS=sqlplus
SSH_AGENT_PID=4165
SSH_AUTH_SOCK=/run/user/1000/keyring/ssh
start=1542628533
TERM=xterm-256color
TMPDIR=/tmp
TMP=/tmp
TMZ=GMT
tries=50
TWO_TASK=ORCL
USERNAME=oracle
USER=oracle
_=/usr/bin/env
VTE_VERSION=3804
WINDOWID=41943047
WINDOWPATH=1
XAUTHORITY=/run/gdm/auth-for-oracle-YSmt3i/database
XDG_CURRENT_DESKTOP=GNOME-Classic:GNOME
XDG_MENU_PREFIX=gnome-
XDG_RUNTIME_DIR=/run/user/1000
XDG_SEAT=seat0
XDG_SESSION_DESKTOP=gnome-classic
XDG_SESSION_ID=1
XDG_VTNR=1
XMODIFIERS=@im=ibus

Report message to a moderator

Re: ORA-12514: TNS [message #673409 is a reply to message #673408]

Mon, 19 November 2018 08:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

COPY & PASTE the results from OS commands below

echo $TWO_TASK
unset TWO_TASK
echo $TWO_TASK
sqlplus / as sysdba

Report message to a moderator

Re: ORA-12514: TNS [message #673410 is a reply to message #673409]

Mon, 19 November 2018 08:23

petar97
Messages: 120
Registered: October 2018

Senior Member

This is how I connected

[oracle@localhost ~]$ echo $ TWO_TASK 
$ TWO_TASK
[oracle@localhost ~]$ unset TWO_TASK
[oracle@localhost ~]$ echo $TWO_TASK

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Nov 19 09:22:27 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Report message to a moderator

Re: ORA-12514: TNS [message #673411 is a reply to message #673406]

Mon, 19 November 2018 08:31

petar97
Messages: 120
Registered: October 2018

Senior Member

BlackSwan wrote on Mon, 19 November 2018 07:58

ping <hostname>
ping <IP#>

where you supply correct values for placeholders within "<>" characters

when I send

ping <localhost.localdomain>

I get bash: syntax error near unexpected token `newline'

Report message to a moderator

Re: ORA-12514: TNS [message #673412 is a reply to message #673411]

Mon, 19 November 2018 08:34

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

[oracle@localhost ~]$ ping localhost.localdomain
PING localhost (127.0.0.1) 56(84) bytes of data.
64 bytes from localhost (127.0.0.1): icmp_seq=1 ttl=64 time=0.028 ms
64 bytes from localhost (127.0.0.1): icmp_seq=2 ttl=64 time=0.030 ms
64 bytes from localhost (127.0.0.1): icmp_seq=3 ttl=64 time=0.030 ms
64 bytes from localhost (127.0.0.1): icmp_seq=4 ttl=64 time=0.033 ms
64 bytes from localhost (127.0.0.1): icmp_seq=5 ttl=64 time=0.044 ms
64 bytes from localhost (127.0.0.1): icmp_seq=6 ttl=64 time=0.071 ms
64 bytes from localhost (127.0.0.1): icmp_seq=7 ttl=64 time=0.049 ms
64 bytes from localhost (127.0.0.1): icmp_seq=8 ttl=64 time=0.039 ms
^C
--- localhost ping statistics ---
8 packets transmitted, 8 received, 0% packet loss, time 6999ms
rtt min/avg/max/mdev = 0.028/0.040/0.071/0.014 ms

When HOSTNAME=localhost, then NO remote client can ever connect to Oracle DB residing on this system

Report message to a moderator

Re: ORA-12514: TNS [message #673413 is a reply to message #673412]

Mon, 19 November 2018 08:47

petar97
Messages: 120
Registered: October 2018

Senior Member

ping localhost.localdomain

it works but ping statistics are over 300

I'll send it when the output is finished

Report message to a moderator

Re: ORA-12514: TNS [message #673414 is a reply to message #673413]

Mon, 19 November 2018 09:03

petar97
Messages: 120
Registered: October 2018

Senior Member

I have the impression that it will never end Smile

Smile

What should I do next?

[Updated on: Mon, 19 November 2018 09:03]

Report message to a moderator

Re: ORA-12514: TNS [message #673415 is a reply to message #673414]

Mon, 19 November 2018 09:10

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You can connect to the DB so what problem remains?

Ctrl-C aborts current running command

man ping
man ls
man <command> provides online help for any/every shell command

Report message to a moderator

Re: ORA-12514: TNS [message #673417 is a reply to message #673415]

Mon, 19 November 2018 09:21

petar97
Messages: 120
Registered: October 2018

Senior Member

I can only connect like this

echo $TWO_TASK
unset TWO_TASK
echo $TWO_TASK
sqlplus / as sysdba

If I insert sqlplus / as sysdba I get error

Report message to a moderator

Re: ORA-12514: TNS [message #673418 is a reply to message #673417]

Mon, 19 November 2018 09:26

petar97
Messages: 120
Registered: October 2018

Senior Member

When I get connected to some user, I get

SQL> conn
Enter user-name: petar
Enter password: 
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0


Warning: You are no longer connected to ORACLE.

Report message to a moderator

Re: ORA-12514: TNS [message #673419 is a reply to message #673418]

Mon, 19 November 2018 09:35

petar97
Messages: 120
Registered: October 2018

Senior Member

sqlplus / as sysdba

It does not even look for username and password to connect as a system

Report message to a moderator

Re: ORA-12514: TNS [message #673420 is a reply to message #673410]

Mon, 19 November 2018 10:04

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

petar97 wrote on Mon, 19 November 2018 15:23

This is how I connected

[oracle@localhost ~]$ echo $ TWO_TASK 
$ TWO_TASK
[oracle@localhost ~]$ unset TWO_TASK
[oracle@localhost ~]$ echo $TWO_TASK

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Nov 19 09:22:27 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

So it works now.
You have to first start your instance and database:

startup

Report message to a moderator

Re: ORA-12514: TNS [message #673421 is a reply to message #673420]

Mon, 19 November 2018 10:08

petar97
Messages: 120
Registered: October 2018

Senior Member

ok, but why not work the way it worked

Report message to a moderator

Re: ORA-12514: TNS [message #673422 is a reply to message #673421]

Mon, 19 November 2018 10:16

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Because the listener does listen for a service named "orcl":

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Services Summary...
Service "orcl12c" has 2 instance(s).
  Instance "orcl12c", status UNKNOWN, has 1 handler(s) for this service...
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
The command completed successfully

Report message to a moderator

Re: ORA-12514: TNS [message #673423 is a reply to message #673422]

Mon, 19 November 2018 10:38

petar97
Messages: 120
Registered: October 2018

Senior Member

Can I change that?
I'd like to connect as before

Report message to a moderator

Re: ORA-12514: TNS [message #673424 is a reply to message #673423]

Mon, 19 November 2018 10:46

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Set TWO_TASKS to orcl12c.

Report message to a moderator

Re: ORA-12514: TNS [message #673425 is a reply to message #673424]

Mon, 19 November 2018 12:02

petar97
Messages: 120
Registered: October 2018

Senior Member

I did not initiate instance and database earlier

Report message to a moderator

Re: ORA-12514: TNS [message #673426 is a reply to message #673425]

Mon, 19 November 2018 12:06

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

On server:

unset TWO_TASKS
sqlplus / as sysdba
startup
exit

Report message to a moderator

Re: ORA-12514: TNS [message #673427 is a reply to message #673426]

Mon, 19 November 2018 12:27

petar97
Messages: 120
Registered: October 2018

Senior Member

I get errors

[oracle@localhost ~]$ unset TWO_TASKS
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Nov 19 13:24:53 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size		    8798312 bytes
Variable Size		  339742616 bytes
Database Buffers	  486539264 bytes
Redo Buffers		    3780608 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 15 - see DBWR trace file
ORA-01110: data file 15: '/home/oracle/backup_vezba/rman_tbs.dbf'


SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Report message to a moderator

Re: ORA-12514: TNS [message #673428 is a reply to message #673427]

Mon, 19 November 2018 12:32

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It was TWO_TASK (without final S) but this does not change, it seems some files are missing.
I hope you have a good backup.

Report message to a moderator

Re: ORA-12514: TNS [message #673429 is a reply to message #673428]

Mon, 19 November 2018 12:36

petar97
Messages: 120
Registered: October 2018

Senior Member

I do not have a backup.
I almost installed pre-built Oracle

Report message to a moderator

Re: ORA-12514: TNS [message #673430 is a reply to message #673429]

Mon, 19 November 2018 12:39

petar97
Messages: 120
Registered: October 2018

Senior Member

I'll probably have to reinstall it Oracle
or this can be adjusted

[Updated on: Mon, 19 November 2018 12:43]

Report message to a moderator

Re: ORA-12514: TNS [message #673449 is a reply to message #673411]

Tue, 20 November 2018 06:20

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

petar97 wrote on Mon, 19 November 2018 08:31

BlackSwan wrote on Mon, 19 November 2018 07:58

ping <hostname>
ping <IP#>

where you supply correct values for placeholders within "<>" characters
when I send

ping <localhost.localdomain>

I get bash: syntax error near unexpected token `newline'

Because you did NOT "supply correct values for placeholders within "<>" characters"

Report message to a moderator

Pages (2): [1 2 › »]

Previous Topic:	ora-12224: tns:no hay ningun receptor
Next Topic:	Remote Connection Problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 03:38:04 CST 2024