I'm trying to setup data guard broker in my production instance. I have duplicated the primary database to standby database successfully. I've added primary database to data broker configuration and when I'm trying to add standby database following error is getting DGMGRL> ADD DATABASE 'orcl_standby' AS CONNECT IDENTIFIER IS orcl_standby maintained as physical; ERROR: ORA - 12154 : TNS: could not resolve the connect identifier specified Tnsping from primary db to standby db and vice-versa is successful. I'm able to connect primary db from standby db using sqlplus sys/password@orcl as sysdba but when trying to connect to standby db from primary db, error is getting like > ORA - 12154 : TNS: could not resolve the connect identifier specified. Tried out all possible solutions. Below is my tnsnames.ora file ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.25.42)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) (SID = orcl) (UR = A) ) ) ORACLR_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) (CONNECT_DATA = (SID = CLRExtProc) (PRESENTATION = RO) ) ) ORCL_STANDBY= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.25.44)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl_standby) (SID = orcl_standby) (UR = A) ) ) Any help will be appreciated

We have stand-by application-servers in multiple datacenters -- in case we must fail-over in a hurry -- and would like to make _some_ use of them during the times of normalcy. The Oracle databases are replicated from the master-instances located in the currently-active datacenter to the slaves located in the redundant ones. The application -- provided by a vendor -- can only talk to _one_ database, so we cannot even direct read-only queries to the Oracle-replica closer to it. Thus, all of the remote application-servers have to talk to the master Oracle-instance, that's geographically far from them. The latency is substantial: tens of milliseconds instead of a fraction of one, when the app- and db-servers are on the same LAN. This slows down some of the operations _tremendously_ -- 20-minute jobs turn into hours-long ones... The vendor frowns at it and says, there is nothing to do -- they never tested their software in such a configuration, and make no effort in batching up requests, for example. The app is "chatty", talking to the DB all the time and awaiting responses before sending new ones. And, maybe, there is nothing to be done, but I'm wondering, if we can tune some settings in the tnsnames.ora, that may help ease the problem -- even if by a little bit... Can we?

These are my active services: SQL> SELECT NAME FROM V$ACTIVE_SERVICES; NAME ---------------------------------------------------------------- pdb3 orcldb pdb2 pdb1 and this is my listener.ora config file: [oracle@oracle admin]$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) ORCLDB = (DESCRIPTION = (ADDRESS = (PROTOCOL=TCP)(HOST = oracle)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcldb) ) ) This is my tnsnames.ora file: [oracle@oracle admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. LISTENER_ORCL = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521)) ORCLDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcldb) ) ) I am gettting this error after lsnrctl stop and start commands: [oracle@oracle admin]$ lsnrctl start LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 04-SEP-2023 20:38:41 Copyright (c) 1991, 2019, Oracle. All rights reserved. Starting /u01/app/oracle/product/19.0.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 19.0.0.0.0 - Production System parameter file is /u01/app/oracle/product/19.0.0/db_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 04-SEP-2023 20:38:41 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19.0.0/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) The listener supports no services The command completed successfully Once I connect orcldb, I will then move forward to pdbs.

These are my listener.ora and tnsnames.ora files

listener.ora



SID_LIST_LISTENER =
  (SID_LIST =
      (SID_DESC =
      (GLOBAL_DBNAME = dr_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = dr)
      (UR=A)
    )
  )
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.XX.XX)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.XX.XX)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle


tnsname.ora


DR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.XX.XX)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dr) (UR=A)
    )
  )


PDB1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.XX.XX)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = pdb1)
    )
  )


PDB2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.XX.XX)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = pdb2)
    )
  )

PDB3 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.XX.XX)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = pdb3)
    )
  )

when I start the listener, only getting this output:

lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 23-JAN-2024 20:38:21

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.XX.XX)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                23-JAN-2024 20:28:41
Uptime                    0 days 0 hr. 9 min. 39 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/hostname/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.XX.XX)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.XX.XX)(PORT=1521)))
Services Summary...
Service "dr_DGMGRL" has 1 instance(s).
  Instance "dr", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

I want to connect to an oracle database located on another host using sqlplus. [This page](http://thomas.eibner.dk/oracle/sqlplus/) suggested adding an item on my tnsnames to connect to that database local_SID = (DESCRIPTION = (ADDRESS = (PROTOCOL= TCP)(Host= hostname.network)(Port= 1521)) (CONNECT_DATA = (SID = remote_SID)) ) and then use that in sqlplus sqlplus user/pass@local_SID However, in my circumstances modifying the local tnsnames is not possible. Is it possible to connect to a remote database just by using sqlplus argument without having to change tnsnames? Something like sqlplus user/pass@remote_SID@hostname.network ;( I know, this one is not valid)

I have configured oracle 12c on Rhel 7. When I try to connect database using following command it gets connected with no issues: sqlplus system/Forest123@orcl I'm facing problem when I try to put the connection string with hostname and port as shown below: sqlplus system/Forest123@localhost:1521/orcl Error occurred is > ORA-12514: TNS:listener does not currently know of service requested in connect descriptor I have gone through the questions asked before, but none of them helped me solve this problem. Following are my tnsnames.ora, sqlnet.ora and listener.ora files. **tnsnames.ora** ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_12C.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.localdomain) ) ) **listener.ora** LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_12C.localdomain)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) **sqlnet.ora** NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) **hosts file** 127.0.0.1 localhost 127.0.0.1 localhost.localdomain 127.0.0.1 localhost4 127.0.0.1 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 ::127.0.0.1 localhost.localdomain localhost 172.31.15.86 oracle_12C.localdomain oracle_12C

I have this tnsnames: JUPITER = (DESCRIPTION = (ADDRESS_LIST = (LOAD_BALANCE = OFF) (FAILOVER = ON) (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.mercurio.sp)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.85)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = jupiter) ) ) ) rac-scan.mercurio.sp is the scan for prod test db and 192.168.0.85 is the standby. When I stop the prod db and try connect, it still direct me to prod db rac-scan.mercurio.sp My steps: **Testing connection with prod database using tns alias:** C:\Users\Danilo>sqlplus sys/xxxxxx@jupiter as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 9 10:55:09 2022 Version 19.6.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> select database_role from v$database; DATABASE_ROLE ---------------- PRIMARY Everything ok, now I will stop this database: **Stopping the primary database** (rac1)(oracle@jupiter1):~> srvctl stop database -d jupiter -o immediate **Now I try to connect again using the same tns alias** C:\Users\Danilo>sqlplus sys/xxxxxx@jupiter as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 9 10:58:47 2022 Version 19.6.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. **using a non-sysdba account** C:\Users\Danilo>sqlplus user_test/xxxxx@jupiter SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 9 11:12:27 2022 Version 19.6.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. ERROR: ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist Linux-x86_64 Error: 2: No such file or directory Additional information: 3701 Additional information: -1145261241 Process ID: 0 Session ID: 0 Serial number: 0 Why tns don't try 192.168.0.85? > NOTE: I have a static listener, could be it? how to resolve?

I am using Oracle 10g Cluster. So, the listener on 1 Oracle machine hasn't been working for a week now. I get an error:

ORA-12541: TNS:no listener

When I want to start listener from oracle user, listener started without services.

lsnrctl status

LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 08-NOV-2021 11:05:53

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.3.0 - Production
Start Date                02-NOV-2021 09:12:26
Uptime                    6 days 1 hr. 53 min. 26 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /oracle/10.2.0/db/network/admin/listener.ora
Listener Log File         /oracle/10.2.0/db/network/log/listener_amrac1.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=amrac1)(PORT=1521)))
The listener supports no services
The command completed successfully

But database not working. I am not changed IP, names of machines, etc. Please help resolve issue.

> I'm using Oracle 12c in Debian 8 (on a vm) > > This question is for educational purposes only. I'm not using any production servers, so anything that you can tell me won't have consecuences To start with, I found this link to delete registers in tnsnames.ora. I didn't test it yet because first, I needed to know if there were active connections in the database using the information of the tnsnames.ora. I'm asking here because I found no way of doing this, but it may be possible to do it. For you to know, I'm using this command for connecting to the database, so that I clearly specify a tnsname

rlwrap sqlplus sys as sysdba@tnstest

The contents of my tnsnames.ora are the following

tnstest =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1539))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

But, if I want to be 100% sure that this is working, I use this command

tnsping tnstest

Which results are...

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 28-JAN-2020 23:51:38

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

Used parameter files:
/opt/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1539))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)

After all of this I know that my configuration is working, I'm using a register in tnsnames.ora for the connection, but I don't know a way of checking... "Hey, now that I'm connected, I want to know which register I used from the tnsnames file for connecting, if I used that file of course" Is there any possibility that I could get this information? Well, my following attempts of trying to figure this out, were checking the v$session view, if there was any field that I could use to get this information, but I couldn't find one. After all of this, ultimately, I came here for asking. I don't know what else to try nor I have more ideas of what should I do next. > P.S: there's a chance that this problem is impossible to solve, because this is task I'm doing due to I'm a student of sysadmin, and our teacher intentionally added unsolvable questions. Even so, I still think there might be a solution for this particular case

I'm trying to connect to a Oracle Autonomous Data Warehouse database with Jetbrains DataGrip. Oracle provides me with a wallet file (a zip), with contains tnsnames.ora, a keystore, ojdbc.properties, and some other files. I'm having a lot of trouble using this information to connect to the database using DataGrip. I found a thread on the DataGrip support forums, but I'm not having any luck with that either. Jetbrains support thread: https://intellij-support.jetbrains.com/hc/en-us/community/posts/360001792539-Connect-with-Oracle-Cloud Relevant Oracle documentation: https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/adwud/connect-using-client-application.html **What I did:** 1. Created the 'TNS_ADMIN' environment variable and set it to: C:\\Users\\xxx\\Documents\\[folder with wallet files] 2. Added the Oracle JDBC driver files (ojdbc8.jar, osdt_cert.jar, oraclepki.jar, osdt_core.jar) to the standard Oracle driver in DataGrip 3. edited the 'sqlnet.ora' file to include the path to the wallet files 4. Added the following to the Data Source VM Options: -Doracle.net.tns_admin=C:\\Users\\xxx\\Documents\\[folder with wallet files] -Djavax.net.ssl.trustStore=truststore.jks -Djavax.net.ssl.trustStorePassword=[password] -Djavax.net.ssl.keyStore=keystore.jks -Djavax.net.ssl.keyStorePassword=[password] -Doracle.net.ssl_server_dn_match=true -Doracle.net.ssl_version=1.2 5. Set connection type to URL only 6. Tried different connection strings in the URL field:

:oracle:thin:@//adb.eu-frankfurt-1.oraclecloud.com:1522/xxxxxx_adw1_high.adwc.oraclecloud.com?TNS_ADMIN=C:\\Users\\xxx\\Documents\\[folder with wallet files]

:oracle:thin:@xxxxxx_adw1_high.adwc.oraclecloud.com?TNS_ADMIN=C:\\Users\\xxx\\Documents\\[folder with wallet files]

:oracle:thin:@//adb.eu-frankfurt-1.oraclecloud.com:1522/mnr6yzqr22jgywm_adw1_high.adwc.oraclecloud.com

**Result:** Connection to ADW1 failed. IO Error: Got minus one from a read call, connect lapse 32 ms., Authentication lapse 0 ms. I have also tried using the 'Service name' and 'TNS' connection types and filled in the info from tnsnames.ora. No dice, same error. Also tried explicitely setting the 'tcp.validnode_checking' parameter to null. (The connection works fine with sqldeveloper) What's the proper way to do this?

I am new to Oracle DB and need help in solving the following issue. I am using **sqlplus** (SQL*Plus: Version 21.1.0.0.0) to connect to the **Oracle DB 19c**. **Statement:** I am able to connect to the Oracle DB from the host (192.168.0.109), VM running on top of the host (192.168.0.108), and also from other system within the same LAN (192.168.0.106) when I change the IP to 0.0.0.0 from 'localhost' in the listener.ora file as below: LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) **Issue faced:** Cannot connect from the VM (192.168.0.108) or from the other systems within the LAN (192.168.0.106) when I change the IP to the host IP (192.168.0.109) in the listener.ora file. Please find the modified listener.ora below: LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.109)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) **Error Details:** The error reported by sqlplus tool is as follows: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor Now, let me share the content of the tnsnames.ora file too (if required): LISTENER_POLA = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ORACLR_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) (CONNECT_DATA = (SID = CLRExtProc) (PRESENTATION = RO) ) ) POLA = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pola) ) ) **Some more details:** 1. The host on which the Oracle DB is residing (192.168.0.109) is reachable from all the other systems including the VM 2. telnet 192.168.0.109 1521 from all the systems is showing it's connected. 3. The VM is running on Oracle Virtual Box 6.1 and is configured in Bridge mode. 4. The Oracle DB 19c and the VM is running on top of Windows Server 2016. **Query:** What configuration do I need to do so that sqlplus can connect to the Oracle DB from any system within the same LAN when the listener.ora file is configured with the IP address of the host system (192.168.0.109)? **Output of LSNRCTL status:** LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 16-MAR-2021 23:18:31 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.109)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production Start Date 16-MAR-2021 23:16:07 Uptime 0 days 0 hr. 2 min. 23 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File C:\Polarbear\WINDOWS.X64_193000_db_home\network\admin\listener.ora Listener Log File C:\Polarbear\diag\tnslsnr\WIN-4SO5O8T8UJ6\listener\alert\log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.109)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc))) Services Summary... Service "CLRExtProc" has 1 instance(s). Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully **Output of the command sqlplus / as sysdba:** SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 17 00:00:29 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select count(*) from dba_tables; COUNT(*) ---------- 2182

I have just installed an oracle 19c (19.3.0.0.0 to be exact) database on a centOS 8 virtual machine. The databases instances can all be started with sqlplus on the host machine. I have set up listener.ora and tnsnames.ora based on an existing (functioning) oracle 12c, which has a very similar setup. I did the initial installation using the .rpm from Oracle, thinking that would make things easier. It probably didn't make things any more difficult, but it meant that the directory structure was not what I'd have expected. Still, it isn't as though it is unusable, just located differently. Having done that, I did a basic install of the DB instances I wanted (using pretty much all default settings) with dbca. Finally, I setup listener.ora and tnsnames.ora based on the working 12c installation. The problem is, no matter what I do, the DB instances don't seem to be registering themselves with the listener. The tnsnames.ora looks like this:

## initially copied from linuxoracle12 (20200617-1112)

LISTENER_TEMP =
  (ADDRESS = (PROTOCOL = TCP)(HOST = linuxoracle19.my-company-name.com)(PORT = 1521))


TMF =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linuxoracle19.my-company-name.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tmf.my-company-name.com)
    )
  )

LISTENER_TMFDE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = linuxoracle19.my-company-name.com)(PORT = 1521))


LISTENER_TMF =
  (ADDRESS = (PROTOCOL = TCP)(HOST = linuxoracle19.my-company-name.com)(PORT = 1521))


TEMP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linuxoracle19.my-company-name.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEMP.my-company-name.com)
    )
  )


TMFDE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linuxoracle19.my-company-name.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TMFDE.my-company-name.com)
    )
  )

The listener.ora looks like this:

## initially copied from linuxoracle12 (20200617-1117)

SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
   (GLOBAL_DBNAME = TMF)
   (ORACLE_HOME = /opt/oracle/product/19c/dbhome_1)
   (SID_NAME = TMF)
  )
  (SID_DESC =
   (GLOBAL_DBNAME = TEMP)
   (ORACLE_HOME = /opt/oracle/product/19c/dbhome_1)
   (SID_NAME = TEMP)
  )
  (SID_DESC =
   (GLOBAL_DBNAME = TMFDE)
   (ORACLE_HOME = /opt/oracle/product/19c/dbhome_1)
   (SID_NAME = TMFDE)
  )
 )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = linuxoracle19.my-company-name.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

As an example the initTEMP.ora looks like this:

## initially generated with dbca
## 20200617 added: *.local_listener='LISTENER_TEMP'
TEMP.__data_transfer_cache_size=0
TEMP.__db_cache_size=855638016
TEMP.__inmemory_ext_roarea=0
TEMP.__inmemory_ext_rwarea=0
TEMP.__java_pool_size=0
TEMP.__large_pool_size=16777216
TEMP.__oracle_base='/opt/oracle'#ORACLE_BASE set from environment
TEMP.__shared_io_pool_size=0
TEMP.__shared_pool_size=301989888
TEMP.__streams_pool_size=0
TEMP.__unified_pga_pool_size=0
*.audit_file_dest='/opt/oracle/admin/TEMP/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/opt/oracle/oradata/temp/TEMP/controlfile/o1_mf_hgkpt5t0_.ctl','/opt/oracle/fast_recovery_area/TEMP/controlfile/o1_mf_hgkpt5w0_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/opt/oracle/oradata/temp/'
*.db_domain='ubs-hainer.com'
*.db_name='TEMP'
*.db_recovery_file_dest='/opt/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=8256m
*.diagnostic_dest='/opt/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TEMPXDB)'
*.local_listener='LISTENER_TEMP'
*.open_cursors=300
*.pga_aggregate_target=378m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1134m
*.undo_tablespace='UNDOTBS1'

So far so good... I thought. But regardless of what I try, I cannot get the DB instances to register with the listener. When I run lsnrctl services, I get the following:

[oracle@linuxoracle19 admin]$ lsnrctl services

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-JUN-2020 12:11:01

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linuxoracle19.my-company-name.com)(PORT=1521)))
Services Summary...
Service "TEMP" has 1 instance(s).
  Instance "TEMP", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "TMF" has 1 instance(s).
  Instance "TMF", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "TMFDE" has 1 instance(s).
  Instance "TMFDE", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully

In various posts and what not, I have read that status UNKNOWN, whilst not seriously bad, actually means that the service in question is not registered with the listener. This is exemplified when I try to connect using NetBeans or SQLDeveloper. In both cases I get the error:

IO Error: The Network Adapter could not establish the connection

Before anybody asks, yes, I did try tnsping. When I did, I got:

[oracle@linuxoracle19 dbhome_1]$ tnsping TEMP 5

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 17-JUN-2020 16:02:19

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
/opt/oracle/product/19c/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = linuxoracle19.my-company-name.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TEMP.my-company-name.com)))
OK (10 msec)
OK (0 msec)
OK (0 msec)
OK (0 msec)
OK (10 msec)
[oracle@linuxoracle19 dbhome_1]$ trcroute TEMP

Trace Route Utility for Linux: Version 19.0.0.0.0 - Production on 17-JUN-2020 16:05:39

Copyright (c) 1995, 2019, Oracle.  All rights reserved.

Route of TrcRoute:
------------------

Node: Client		Time and address of entry into node:
-------------------------------------------------------------
17-JUN-2020 16:05:39 ADDRESS= PROTOCOL=TCP  HOST=linuxoracle19.my-company-name.com  PORT=1521

Node: Server		Time and address of entry into node:
-------------------------------------------------------------
17-JUN-2020 16:05:39 ADDRESS= PROTOCOL=TCP  HOST=linuxoracle19.my-company-name.com  PORT=1521

Can anybody out there tell me what is going wrong? I really have no idea why the current setup doesn't work. **Edit 20200618:** I have researched some more and made a few changes. Although these have not been successful, I feel it is important to mention them. Firstly, after reading the answer from *pifor* and reading up on the database init parameters corresponding to the listener settings (Section 9.2.1 of the Oracle 19 Net Services Administrator's Guide) I added The following settings to the initTEMP.ora:

*.instance_name='TEMP'
*.service_names='TEMP.linuxoracle19.my-company-name.com'

At the same time, based on some more Oracle documentation, I altered the domain parameter to the host name of the server *.db_domain='linuxoracle.my-company-name.com' This also required changing the value of GLOBAL_NAME on the DB instance: SQL> UPDATE GLOBAL_NAME SET GLOBAL_NAME ='TEMP.LINUXORACLE19.MY-COMPANY-NAME.COM'; Finally, I altered the the service name in tnsnames.ora (SERVICE_NAME = TEMP.linuxoracle19.my-company-name.com) I made corresponding changes for the other database instances. Then I took the following steps: - Stopped all 3 databases. - Restarted the listener with lsnrctl stop and lsnrctl start (yes, I'm aware there is a reload command). - Restarted the TEMP and TMF instances. Checking the listener status with lsnrctl status revealed the following:

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 18-JUN-2020 11:57:13

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linuxoracle19.my-company-name.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                18-JUN-2020 11:51:40
Uptime                    0 days 0 hr. 5 min. 32 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/linuxoracle19/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linuxoracle19.my-company-name.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "TEMP.linuxoracle19.my-company-name.com" has 2 instance(s).
  Instance "TEMP", status UNKNOWN, has 1 handler(s) for this service...
  Instance "TEMP", status READY, has 1 handler(s) for this service...
Service "TEMPXDB.linuxoracle19.my-company-name.com" has 1 instance(s).
  Instance "TEMP", status READY, has 1 handler(s) for this service...
Service "TMF.linuxoracle19.my-company-name.com" has 2 instance(s).
  Instance "TMF", status UNKNOWN, has 1 handler(s) for this service...
  Instance "TMF", status READY, has 1 handler(s) for this service...
Service "TMFDE.linuxoracle19.my-company-name.com" has 1 instance(s).
  Instance "TMFDE", status UNKNOWN, has 1 handler(s) for this service...
Service "TMFXDB.linuxoracle19.my-company-name.com" has 1 instance(s).
  Instance "TMF", status READY, has 1 handler(s) for this service...

I figured that looked pretty good. So I tried connecting with SQL Developer, NetBeans, and one of our own tools. In each case I got the error message: IO Error: The Network Adapter could not establish the connection In the case of SQL Developer , it also metioned a "Vendor Code 17002". Ishould mention that each of the tools in question use JDBC to connect to a database. So, as yet I have no solution to the problem. Any further tips would be much appreciated. **Edit#2 20200618** In addition to the above, a colleague suggested that the host machine doesn't "know" what it's external name is. Therefore I should add the host name as used from a remote site to the loop-back address in /etc/hosts file. I was skeptical, but it makes sort of sense, since DNS plays no role when the machine is "talking to itself". So the /etc/hosts now looks like this:

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4 linuxoracle19 linuxoracle19.my-company-name.com
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6 linuxoracle19 linuxoracle19.my-company-name.com

(Note the last two entries in the IPV4 and IPV6 loop-back entries.) Unfortunately this had no effect, even after restarting the listener. A further suggestion from *pifor* was to try connecting on with sqlplus (I assume that meant "connect from a remote site"). I therefore logged onto the other oracle host (linuxoracle12) and tried it out. The result:

[oracle@linuxoracle12 admin]$ sqlplus tmf/xxxxxxxx@linuxoracle19.my-company-name.com:1521/TEMP

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jun 18 17:42:21 2020

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

ERROR:
ORA-12543: TNS:destination host unreachable

OK, that's something I can start with. To make sure I'd done the test correctly, I tried it in the other direction (i.e. connect to a DB instance on linuxoracle12 when logged onto linuxoracle19):

[oracle@linuxoracle19 bin]$ sqlplus tmf/xxxxxxxx@linuxoracle12.my-company-name.com:1521/TMF

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 18 16:46:17 2020
Version 19.3.0.0.0

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

Letzte erfolgreiche Anmeldezeit: Do Jun 18 2020 15:22:27 +02:00

Verbunden mit: 
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

So now I have to figure out why the first test failed whereas the second worked. **Edit#3 20200706** The preceding information makes it pretty clear that the problem is one in networking, rather than in the database setup, Following an extended chat session with *pifor* the following aspects were checked: - Ensure SELinux is disabled. - Ensure that no firewall is running on the host server. - Check that the port for the listener service is open (on Linux use nmap). - As mentioned by *pifor* in the answer below, ensure that the service names match between the init.ora and tnsnames.ora. (Note: in init.ora the property is called service_names, i.e. plural). - After a database or server restart, remember to restart the listener service. ;-) Once I'd done all this, the database was usable.

How could I use "Linked Server" from SSMS IDE to connect the Oracle server database with "SYSDBA"? I've tried many methods as following to show you what I did right now: Oracle client connect to Oracle Server data: SSMS connect to Oracle Server data: More testing information I also use "SQLPLUS" to check *tnsnames.ora* the file is okay or not but the answer is as following 1. sqlplus SYS/Oradoc_db1@DBNAME as SYSDBA <-- is okay 2. sqlplus SYS/Oradoc_db1@DBNAME <-- Error

I would like to modify TNSNAMES.ORA file to connect to an ORACLE Database and I noticed that on my PC there are 3 different files with name TNSNAMES.ORA files. Which of these three should I modify in order to make connection D:\app\XXX\product\11.2.0\client_32\network\admin E:\app\XXX\product\11.2.0\client_64\network\admin D:\app\XXX\product\11.2.0\client_1\network\admin

I would like to know, if there is any chance to get the currently used TNS connect string, that will be used for a DB-Link from a db-server. I'm connected to a schema on a database located on any server. Now, I have some database links, using tnsnames and which are currently not working properly. I would like to check, which target database is behind this identifier to verify easily, if this can be correct. If I would have OS access to the db-server, I would be able to read the ./tnsnames.ora or using tnsping. But the DDL of the dblink just uses the TNSNAME (RDBX in this example): > CREATE DATABASE LINK "ABC" CONNECT TO "RUSER" IDENTIFIED BY VALUES ':1' USING 'RDBX'; Of course, I could ask a DBA, but this is not always as easy in our environment and I would like to be able to double-check it myself without having OS-access.

I normally connect to my Oracle database at work using a connection string. Other then the obvious answer of asking the DBA what it is, using SQL Developer or some other tool can I find out what the tns listener name is. This is an excerpt from a config file in .net where i use to connect: > DATA SOURCE=myURL:port/servicename;PASSWORD=password

this is our classic tnsnames.ora test1= (DESCRIPTION= (CONNECT_TIMEOUT=4) (TRANSPORT_CONNECT_TIMEOUT=3) (ENABLE=BROKEN) (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=example1.example.com)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=example2.example.com)(PORT=1521)) ) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=EXAMPLE.EXAMPLE.DBS) ) ) I use this SQLcl command: sql -nohistory -noupdates -S $username/$password@$hostname:$port/$servicename @$filename How to specifiy multiple hostnames? Not only one? It's some kind of active passive cluster (Exadata). *Edit after first answer:* I added in shell script (in this dir tnsnames.ora): TNS_ADMIN=/example/example I call $sqlcl -nohistory -noupdates -S $username/$password@"MY-DB" @$filename and get back the error: ./script.sh USER = MY_USER URL = jdbc:oracle:thin:@MY-DB Error Message = IO Error: Unknown host specified USER = MY_USER URL = jdbc:oracle:thin:@MY-DB:1521/MY-DB Error Message = IO Error: Invalid connection string format, a valid format is: "host:port:sid"

I think it's not reliable because those ports are used for FTP, SSH and SFTP but I am not sure that's the correct response in respect for Oracle DBA

I have Linux RedHat Enteprise with Oracle 11g database and I've stumbled upon a circle of errors and I can not find a way out. First error in alert_XE.log: ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available. ************************************************************************ You have following choices to free up space from recovery area: 1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard, then consider changing RMAN ARCHIVELOG DELETION POLICY. 2. Back up files to tertiary device such as tape using RMAN BACKUP RECOVERY AREA command. 3. Add disk space and increase db_recovery_file_dest_size parameter to reflect the new space. 4. Delete unnecessary files using RMAN DELETE command. If an operating system command was used to delete files, then use RMAN CROSSCHECK and DELETE EXPIRED commands. ************************************************************************ ARCH: Error 19809 Creating archive log file to '/u01/app/oracle/fast_recovery_area/XE/archivelog/2018_12_01/o1_mf_1_580_%u_.arc' Errors in file /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_ora_2940.trc: ORA-16038: log 2 sequence# 580 cannot be archived ORA-19809: limit exceeded for recovery files ORA-00312: online log 2 thread 1: '/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_dh40n8hk_.log' System state dump requested by (instance=1, osid=2940), summary=[abnormal instance termination]. System State dumped to trace file /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_diag_2859.trc USER (ospid: 2940): terminating the instance due to error 16038 Which would be easily solveable with rman, but here comes error number 2 when "connect target /" on rman: ORA-01031: insufficient privileges Which is of course again solveable with orapwd file. I've created a new file with the right SYS password. Trying again to login via rman, but of course, no luck. I've tried also with sqlplus with various commands like "sqlplus sys/@XE as sysdba" and I get error number 3: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor I've tried "tnsping XE" and it looks ok: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE))) OK (0 msec) Any ideas how to proceed?

Is there a way to have multiple tnsnames.ora locations defined? We currently use a server path like TNS_ADMIN=\\192.168.1.100\infosys\oracle\tnsnames but are in a situation this week where that server had to be taken offline for a couple days. I've tried stacking locations similar to PATH variable but it doesn't work: > set TNS_ADMIN=z:\Common\oracle;\\192.168.1.100\infosys\oracle\tnsnames > mctnsping ora-server McTnsping Utility by Michel Cadot: Version 2018.08.17 on 18-OCT-2018 11:16:33 Copyright (c) Michel Cadot, 2016-2018. All rights reserved. *** TNS-03514: Failed to find tnsnames.ora file Single location is fine: > set TNS_ADMIN=Z:\Common\oracle > mctnsping ora-server Used parameter files: Z:\Common\oracle\tnsnames.ora ...snip... Attempting to contact ora-server:1521 OK (201 msec) How to have an alternate location without touching every client to change TNS_ADMIN?