OraFAQ Forum: Server Administration » Second listener on 19c (Linux)

Home » RDBMS Server » Server Administration » Second listener on 19c (Linux) (19C, Linux )

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

Second listener on 19c (Linux) [message #676585]

Thu, 20 June 2019 14:49

jr1
Messages: 7
Registered: June 2019

Junior Member

Dear All,

I like to use a second listener on the database.
So I update the listener.ora
Starting listener works fine.

I'm able to build up the tcp connection to the listener.
after login I received:

Enter password:
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: 4376
Additional information: 350823935
Process ID: 0
Session ID: 0 Serial number: 0

I'm not able to understand the issue.
Using the stdanart listener : all works fine.

But wahts the problem.
As I understand: I seems a problem to get in contact to the SGA.
But why ?

Thanks a lot for any information
JR

Report message to a moderator

Re: Second listener on 19c (Linux) [message #676587 is a reply to message #676585]

Thu, 20 June 2019 15:24

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

Senior Member
Account Moderator

You have to show us:
1/ Your listener.ora
2/ Your client tnsnames.ora
3/ The result of "lsnrctl status"
4/ Your connection string

The message you get indicates that your instance is not started.

Report message to a moderator

Re: Second listener on 19c (Linux) [message #676588 is a reply to message #676587]

Thu, 20 June 2019 15:41

jr1
Messages: 7
Registered: June 2019

Junior Member

Here we go:

is it right to copy the logs as ascii ???

Thanks for your time
JR

1/ Your listener.ora

# listener.ora Network Configuration File: /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = kl3dbs.test.com)(PORT = 1524))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1524))
)
)
SID_LIST_LISTENER =
(SID_DESC =
(GLOBAL_DBNAME = kl3db)
(ORACLE_HOME = /opt/oracle/product/19c/dbhome_1)
(SID_NAME = kl3db)
)
)

LISTENER_2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = kl3dbs.test.com)(PORT = 2524))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2524))
)
)
SID_LIST_LISTENER_2 =
(SID_DESC =
(GLOBAL_DBNAME = kl3db)
(ORACLE_HOME = /opt/oracle/product/19c/dbhome_1/)
(SID_NAME = kl3db)
)
)

#########################################################################

2/ Your client tnsnames.ora
(tnsnames.ora from the server, problems occurs using sqlplus on the server )

# tnsnames.ora Network Configuration File: /opt/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

KL3DB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = kl3dbs.test.com)(PORT = 1524))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = KL3DB)
)
)

KL3DB-ADMIN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = kl3dbs.test.com)(PORT = 2524))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = KL3DB)
)
)
LISTENER_KL3DB =
(ADDRESS = (PROTOCOL = TCP)(HOST = kl3dbs.test.com)(PORT = 1524))

#############################################################################

3a/ The result of "lsnrctl status"
( this listener works fine )

[oracle@kl3dbs ~]$ /opt/oracle/product/19c/dbhome_1/bin/lsnrctl status LISTENER

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 20-JUN-2019 22:35:45

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=kl3dbs.test.com)(PORT=1524)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 20-JUN-2019 21:29:42
Uptime 0 days 1 hr. 6 min. 3 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/kl3dbs/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=kl3dbs.test.com)(PORT=1524)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1524)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=kl3dbs.test.com)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/product/19c/dbhome_ 1/admin/KL3DB/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "8b8ad88a6a8c6388e0534a6ea8c06f80" has 1 instance(s).
Instance "KL3DB", status READY, has 1 handler(s) for this service...
Service "KL3DBXDB" has 1 instance(s).
Instance "KL3DB", status READY, has 1 handler(s) for this service...
Service "kl3db" has 2 instance(s).
Instance "KL3DB", status READY, has 1 handler(s) for this service...
Instance "kl3db", status UNKNOWN, has 1 handler(s) for this service...
Service "orclpdb1" has 1 instance(s).
Instance "KL3DB", status READY, has 1 handler(s) for this service...
The command completed successfully

####################################################################################

3b. This listern fires the error

[oracle@kl3dbs ~]$ /opt/oracle/product/19c/dbhome_1/bin/lsnrctl status LISTENER_2

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 20-JUN-2019 22:37:11

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=kl3dbs.test.com)(PORT=2524)))
STATUS of the LISTENER
------------------------
Alias LISTENER_2
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 20-JUN-2019 21:34:15
Uptime 0 days 1 hr. 2 min. 55 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/kl3dbs/listener_2/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=kl3dbs.test.com)(PORT=2524)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC2524)))
Services Summary...
Service "kl3db" has 1 instance(s).
Instance "kl3db", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

4/ Your connection string
[oracle@kl3dbs ~]$ sqlplus c##kl3@kl3db-admin

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 20 22:38:10 2019
Version 19.3.0.0.0

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

Enter password:
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: 4376
Additional information: 350823935
Process ID: 0
Session ID: 0 Serial number: 0

Enter user-name:

Report message to a moderator

Re: Second listener on 19c (Linux) [message #676589 is a reply to message #676585]

Thu, 20 June 2019 16:13

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

jr1 wrote on Thu, 20 June 2019 14:49

Dear All,

I like to use a second listener on the database.

Why? What problem do you imagine is being solved by a second listener?
Please stay after school and write this on the chalkboard 100 times:

One single listener, using the default name of LISTENER and the default port of 1521, is quite capable of -- indeed, WAS DESIGNED TO -- handle multiple databases of multiple versions running from multiple ORACLE_HOMEs. Multiple listeners add neither performance or robustness. Running on any port other than the default does NOT increase security.

Report message to a moderator

Re: Second listener on 19c (Linux) [message #676592 is a reply to message #676589]

Thu, 20 June 2019 17:53

jr1
Messages: 7
Registered: June 2019

Junior Member

Oh, that's very easy.

Shuting down one listener give a very comfortable feature, to avoid user to connect to the database , in case of mantanace for example.

The Admin can use the second port with normal, remote, tools and there is no need for login into the server an use local tool ( sqlplus....)

We did this for some database for years well.

But now, ob 19c, it seems a little tricky.

Hopefully this expains.

JR

Report message to a moderator

Re: Second listener on 19c (Linux) [message #676594 is a reply to message #676588]

Fri, 21 June 2019 00:46

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

Senior Member
Account Moderator

sqlplus c##kl3@kl3db-admin

KL3DB-ADMIN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = kl3dbs.test.com)(PORT = 2524))

LISTENER_2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = kl3dbs.test.com)(PORT = 2524))

SID_LIST_LISTENER_2 =
(SID_DESC =
(GLOBAL_DBNAME = kl3db)
(ORACLE_HOME = /opt/oracle/product/19c/dbhome_1/)
(SID_NAME = kl3db)

==> check status of LISTENER_2: lsnrctl status LISTENER_2
==> check instance processes are there: ps -eaf | grep kl3db
==> check instance SGA : ipcs -m | grep kl3db
==> check client kl3dbs.test.com IP and actual db server IP are same
==> try to locally connect as sysdba

Report message to a moderator

Re: Second listener on 19c (Linux) [message #676599 is a reply to message #676594]

Fri, 21 June 2019 16:17

jr1
Messages: 7
Registered: June 2019

Junior Member

Dear Michel,

here is the result:

==> check status of LISTENER_2: lsnrctl status LISTENER_2
lsnrctl status LISTENER_2

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 21-JUN-2019 23:11:01

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=kl3dbs.outokumpu.com)(PORT=2524)))
STATUS of the LISTENER
------------------------
Alias LISTENER_2
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 21-JUN-2019 23:07:54
Uptime 0 days 0 hr. 3 min. 7 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/kl3dbs/listener_2/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=kl3dbs.outokumpu.com)(PORT=2524)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC2524)))
Services Summary...
Service "KL3DB" has 1 instance(s).
Instance "kl3db", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

==> check instance processes are there: ps -eaf | grep kl3db

oracle@kl3dbs ~]$ ps -eaf | grep kl3db
avahi 5580 1 0 22:02 ? 00:00:00 avahi-daemon: running [kl3dbs.local]
oracle 12371 7212 0 23:11 pts/0 00:00:00 grep --color=auto kl3db
[oracle@kl3dbs ~]$

==> check instance processes are there: ps -eaf | grep KL3DBoracle@kl3dbs ~]$ ps -eaf | grep KL3DB
oracle 11722 1 0 23:06 ? 00:00:00 ora_pmon_KL3DB
oracle 11724 1 0 23:06 ? 00:00:00 ora_clmn_KL3DB
oracle 11726 1 0 23:06 ? 00:00:00 ora_psp0_KL3DB
oracle 11728 1 3 23:06 ? 00:00:12 ora_vktm_KL3DB
oracle 11732 1 0 23:06 ? 00:00:00 ora_gen0_KL3DB
oracle 11734 1 0 23:06 ? 00:00:00 ora_mman_KL3DB
oracle 11738 1 0 23:06 ? 00:00:00 ora_gen1_KL3DB
oracle 11741 1 0 23:06 ? 00:00:00 ora_diag_KL3DB
....and a lot more

==> check instance SGA : ipcs -m | grep kl3db
no response

[oracle@kl3dbs ~]$ ipcs -m

------ Gemeinsamer Speicher: Segmente --------
Schlüssel shmid Besitzer Rechte Bytes nattch Status
0x00000000 327680 oracle 600 9138176 92
0x00000000 360449 oracle 600 2415919104 46
0x00000000 393218 oracle 600 7639040 46
0xad7dc734 425987 oracle 600 16384 46

==> check client kl3dbs.test.com IP and actual db server IP are same
I try sqlplus on the server system

==> try to locally connect as sysdba[oracle@kl3dbs ~]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 21 23:15:14 2019
Version 19.3.0.0.0

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

SQL> connect sys as sysdba
Enter password:
Connect durchgefuhrt.
SQL>

I'm not sure about the results.
Please give e a short description for ipcs -m

Is there a a problem with uppercase / lowercase ?

And if, how to check it ?

Thanks a lot
Josef

Report message to a moderator

Re: Second listener on 19c (Linux) [message #676601 is a reply to message #676592]

Fri, 21 June 2019 23:23

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

jr1 wrote on Thu, 20 June 2019 17:53

Oh, that's very easy.

Shuting down one listener give a very comfortable feature, to avoid user to connect to the database , in case of mantanace for example.

Whatever floats your boat. What kind of maintenance would be done that would leave a dB up, thus requireing a listener to be up, yet would require a listener to be shut down "for maintenance"? I'm sorry, but I've only been at this for about 25 years and have never encountered such a situation.

Quote:

The Admin can use the second port with normal, remote, tools and there is no need for login into the server an use local tool ( sqlplus....)

We did this for some database for years well.

But now, ob 19c, it seems a little tricky.

Hopefully this expains.

Not really. It looks like a case of circular, self-justification. "We do it this way because this is how we have always done it."

Report message to a moderator

Re: Second listener on 19c (Linux) [message #676602 is a reply to message #676599]

Sat, 22 June 2019 00:35

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

Senior Member
Account Moderator

Quote:

Is there a a problem with uppercase / lowercase ?

Can be.
When you "connect sys as sysdba" how was ORACLE_SID set?

Note that the current way to use listeners is to not statically configure the instances like in:
SID_LIST_LISTENER_2 =
(SID_DESC =
(GLOBAL_DBNAME = kl3db)
(ORACLE_HOME = /opt/oracle/product/19c/dbhome_1/)
(SID_NAME = kl3db)
but to let the instance registered to the listener setting "local_listener" parameter:
local_listener = 'ADDRESS=(PROTOCOL=TCP)(HOST=kl3dbs.outokumpu.com)(PORT=2524))'
The value is the one given by the listener in answer to "status" command.
Verify that the host is correct in tnsnames.ora.

[Edit: typo]

[Updated on: Sat, 22 June 2019 12:07]

Report message to a moderator

Re: Second listener on 19c (Linux) [message #676603 is a reply to message #676602]

Sun, 23 June 2019 12:24

jr1
Messages: 7
Registered: June 2019

Junior Member

Dear Michel,

here are my results:
sqlplus sys as sysdba:

select instance from v$thread;
INSTANCE
--------------------------------------------------------------------------------
KL3DB

select name from v$database;
NAME
---------
KL3DB

SQL> select ora_database_name from dual;

ORA_DATABASE_NAME
--------------------------------------------------------------------------------
KL3DB

So: it seems Databasename ist entered in upppercase!

checking the hostname: ping kl3dbs....... works!

but connect using kl3db-admin fails....

On Ora10 we used the configuration on several servers. All works fine.
But not on ora 19c. Maybe the new configuration generates the issue ?

Thanks for a response.

JR

Report message to a moderator

Re: Second listener on 19c (Linux) [message #676604 is a reply to message #676603]

Sun, 23 June 2019 13:01

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> Maybe the new configuration generates the issue ?
Maybe 19c has error of commission (something was done wrong)
Maybe 19c has error of omission (something was NOT done that needs to be done).

EVERY network connection request that gets to the listener is logged in listener.log file. Errors are also logged.
If NO record gets logged, this indicates no request ever got to the listener (OS/networking mis-configuration & problem external to Oracle).

Report message to a moderator

Re: Second listener on 19c (Linux) [message #676605 is a reply to message #676603]

Sun, 23 June 2019 13:09

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

Senior Member
Account Moderator

When I asked "When you "connect sys as sysdba" how was ORACLE_SID set?" I meant what is the value of the environment variable:
env | grep ORACLE_SID

Anyway:
INSTANCE
--------------------------------------------------------------------------------
KL3DB

So you should have the same thing in SID_NAME of your listener.ora file (that is in upper case).
Note: database name does not matter, it is instance name which is important.

As BlackSwan said, have a look at listener log, you will see all tries to log on through it.

Report message to a moderator

Re: Second listener on 19c (Linux) [message #676606 is a reply to message #676605]

Mon, 24 June 2019 00:20

jr1
Messages: 7
Registered: June 2019

Junior Member

@Michel: oh, ORACLE_SID is set to KL3DB
@Black Swan: I can exstablished the connection to listener_2, but after input the asked password, I get the erroro.
So I chekted the listener-log:

That's my log:

LISTENER for Linux: Version 19.0.0.0.0 - Production
Version 19.3.0.0.0

System parameter file is /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/kl3dbs/listener_2/alert/log.xml
Trace information written to /opt/oracle/diag/tnslsnr/kl3dbs/listener_2/trace/ora_3238_140422115519552.trc
Trace level is currently 0

Started with pid=3238
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=kl3dbs.outokumpu.com)(PORT=2524)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC2524)))
Listener completed notification to CRS on start

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
WARNING: Subscription for node down event still pending
23-JUN-2019 18:13:42 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=kl3dbs)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER_2)(VERSION=318767104)) * status * 0
2019-06-23T18:14:01.052997+02:00
23-JUN-2019 18:14:01 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=KL3DB)(CID=(PROGRAM=sqlplus@kl3dbs)(HOST=kl3dbs)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.110.74)(PORT=24043)) * establish * KL3DB * 0

set the tracing level to "admin":
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
WARNING: Subscription for node down event still pending
24-JUN-2019 06:55:47 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=kl3dbs)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER_2)(VERSION=318767104)) * status * 0
2019-06-24T06:56:12.265561+02:00
WARNING: Subscription for node down event still pending
24-JUN-2019 06:56:12 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=kl3dbs)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER_2)(VERSION=318767104)) * status * 0
2019-06-24T06:56:24.075142+02:00
24-JUN-2019 06:56:24 * trc_level * 0
2019-06-24T06:56:31.181113+02:00
WARNING: Subscription for node down event still pending
24-JUN-2019 06:56:31 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=kl3dbs)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER_2)(VERSION=318767104)) * status * 0
2019-06-24T06:57:12.687193+02:00
24-JUN-2019 06:57:12 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=KL3DB)(CID=(PROGRAM=sqlplus@kl3dbs)(HOST=kl3dbs)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.110.74)(PORT=36587)) * establish * KL3DB * 0

I did it for both listener and try to check the diffence......but i need more time to understand the traces
I add the trace here

No working: ora_16099_140251439438912.trc
working : ora_2751_140073855573056.trc (txt)

Attachment: ora_2751_140073855573056.txt
(Size: 43.19KB, Downloaded 2508 times)

Report message to a moderator

Re: Second listener on 19c (Linux) [message #676607 is a reply to message #676606]

Mon, 24 June 2019 00:22

jr1
Messages: 7
Registered: June 2019

Junior Member

here the non working:

Attachment: ora_16099_140251439438912.txt
(Size: 59.92KB, Downloaded 2437 times)

Report message to a moderator

Re: Second listener on 19c (Linux) [message #676611 is a reply to message #676607]

Mon, 24 June 2019 06:54

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please NOTE that nowhere in posted excerpt from listener log file does it show any connection attempt similar to previously reported below.
>[oracle@kl3dbs ~]$ sqlplus c##kl3@kl3db-admin
It shows only "(PROGRAM=sqlplus@kl3dbs)" & indicates that the request using "@kl3db-admin" NEVER gets to the listener & guarantees failure.

Report message to a moderator

Previous Topic:	Oracle 10g -> 11g Upgrade
Next Topic:	Migration of Oracle Database 11g R2 from 32 bit to 64 bit

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 28 06:55:30 CST 2024