1

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

3 Answers 3

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.

enter image description here

1
  • Thank you, you've helped me a lot with this, and the links above
    – Goku
    Jan 15, 2019 at 11:09
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
6
  • I can connect to a database when I use unset TWO_TASK, if I do not use, I always connect to instance orcl12c. although I have set $ ORACLE_SID and $ ORACLE_HOME
    – Goku
    Jan 15, 2019 at 10:19
  • @petar so if you can connect, what is the problem ? can you edit your post and copy your command lines ?
    – Sy10100
    Jan 15, 2019 at 10:27
  • I've edited the text, why I need to use unset TWO_TASK?
    – Goku
    Jan 15, 2019 at 10:40
  • UNKNOWN means static registration, not this. Jan 15, 2019 at 10:46
  • "I've edited the text, why I need to use unset TWO_TASK? " - when you 'edit the text' you render any existing replies to that text to be confusing -- at best. As for why you 'need' to unset TWO_TASK, I explained what TWO_TASK does. Personally I've used only once in 20+ years of oracle DBA work. I'd be interested in why it was set in the first place.
    – EdStevens
    Jan 16, 2019 at 22:49
2

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
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.