How to set listener.ora and tnsnames.ora for oracle instances

Question

I set listener.ora and tnsnames.ora, but I do not know if it is well set up. When I call the lsnrctl status command, I get instances rcadb and proddb unknown.

I'm a beginner in Oracle, I installed the Oracle 12c pre-built.

Now that I've made two databases, I have this problem, I can not find a mistake.

The database orcl12c I got with the installation, had host = 0.0.0.0 which I changed

Listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl12c)
      (SID_NAME = orcl12c)
      (ORACLE_HOME = /u01/app/oracle/product/12.2/db_1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = prodDB)
      (SID_NAME = prodDB)
      (ORACLE_HOME = /u01/app/oracle/product/12.2/db_1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = rcatDB)
      (SID_NAME = rcatDB)
      (ORACLE_HOME = /u01/app/oracle/product/12.2/db_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.2)(PORT = 1524))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.3)(PORT = 1525))
    )
  )

And I set tnsnames.ora like this:

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

prodDB =
 (DESCRIPTION =
    (ADDRESS = (PROTOCOL=TCP)(HOST = 127.0.0.2)(PORT = 1524))
    (CONNECT_DATA =
       (SERVICE_NAME = prodDB)
    )
 )

rcatDB =
 (DESCRIPTION =
    (ADDRESS = (PROTOCOL=TCP)(HOST = 127.0.0.3)(PORT = 1525))
    (CONNECT_DATA =
       (SERVICE_NAME = rcatDB)
    )
 )


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)
    )
  )

lsnrctl status

Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.2)(PORT=1524)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.3)(PORT=1525)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=8081))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "51c99766d7e2568de0530100007f4fae" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
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...
Service "orcl12cXDB" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "prodDB" has 1 instance(s).
  Instance "prodDB", status UNKNOWN, has 1 handler(s) for this service...
Service "rcatDB" has 1 instance(s).
  Instance "rcatDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

EDIT:

[oracle@localhost ~]$ unset TWO_TASK
[oracle@localhost ~]$ . ./.profile_productionDB
[oracle@localhost ~]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 15 05:33:45 2019

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


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

SQL> select name from v$database;

NAME
---------
PRODDB

SQL> 

Answer 1

There is nothing wrong with that.

Listener Control Utility SERVICES Command

• UNKNOWN means the instance is registered statically in the listener.ora file rather than dynamically with service registration. Therefore, the status is non known.

Answer 2

Status UNKNOWN is not necessarily a problem. It could happen if your start your listener before your database, then your listener does not know the status of your database.

When you do :

[oracle@localhost ~]$ sqlplus sys/oracle as sysdba

You are connecting to your database locally, whithout using your listener. You can stop your listener and try to connect. It should work.

If you want to test your tnsnames/listener, first create a test user :

[oracle@localhost ~]$ sqlplus / as sysdba
create user test_tns identified by "test_tns";
grant create session to test_tns;
exit;

Then test with :

[oracle@localhost ~]$ sqlplus test_tns/test_tns@rcadb

Answer 3

As others have already pointed out, the 'unknown' is simply reporting was was listed in the SID_LIST section of listener.ora. It's the listener saying, "I'll try to service requests to these, but I have no way of knowing of their status at this time." The "ready" listings are from the database itself registering with the listener - dynamic registration.

As an aside, you are accomplishing nothing by having the listener on different ports and listing different ports in tnsnames. There is nothing that actually binds the ports to a particular database. It is used only as an entry to the listener itself. Once the connection is made, the listener gives the client and the server a different (quasi-random) port to communicate on and then the listener is out of the picture. One single port is all that is needed, regardless of how many databases the listener is servicing.

As another aside, ip address 127.0.0.1 is a loopback address. If a process (any process, not just oracle clients) requests a packet be sent to 127.0.0.1 (or 'localhost'), that request is looped back to the requesting system. So if desktop A requests a connection to a database on server B, but specifies the address is 127.0.01, the request will never get to server B.

TWO_TASK simply provides a variable to substitute for the use of '@dbname' in a connection string. If you set TWO_TASK=mydb, then

sqlplus fred/fred

is the same as

sqlplus fred/fred@mydb