Dva različita listenera na jednom Oracle db serveru

Sunday, 22.03.2009 – msutic

Listener je proces koji omogućava komunikaciju sa Oracle bazom/bazama podataka.

Na mnogim db serverima ćemo najčešće imati jedan (defaultni) listener “LISTENER” koji sluša na (defaultnom) portu 1521.

Ali što ako želimo na jednom db serveru imati dva (ili više) listenera koji “slušaju” za dvije ili više baza podataka?

U ovom kratkom demo testu ću pokazati kako postići takvu situaciju.

Za test sam kreirao dvije različite baze, te ću svakoj od njih dodijeliti odgovarajući listener proces.

(Oracle 11g database)
SID : test11
HOME : /app/oracle/product/11.1.0

(Oracle 10g database)
SID : test10
HOME : /app/oracle/product/10.2.0

sa sqlnet.ora parametrom:

# sqlnet.ora Network Configuration File: /oracle/product/10.2.0/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Prvo ću konfigurirati listener za 11g bazu podataka.

LISTENER.ORA

# listener.ora Network Configuration File: /app/oracle/product/11.1.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER_1
=
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /app/oracle/product/11.1.0)
(PROGRAM = extproc)
)
)


LISTENER_1
=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = test11))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.10.18)(PORT = 1521))
)
)


TNSNAMES.ORA

# tnsnames.ora Network Configuration File: /app/oracle/product/11.1.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

TEST11 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.10.18)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test11)
)
)

U LISTENER.ORA datoteci sam naveo defaultnu putanju za listener proces, postavio ime listenera “SID_LIST_LISTENER_1” i definirao konfiguraciju za “LISTENER_1” listener. Ovdje su vrlo važne postavke adresa/naziv host-a koja je u mom slučaju 10.2.10.18 i port koji će LISTENER_1 koristiti za slušanje – ostavit ću defaultni 1521.

Startati ću LISTENER_1:

$ lsnrctl start LISTENER_1

$ lsnrctl service LISTENER_1

LSNRCTL for Linux: Version 11.1.0.7.0 – Production on 21-MAR-2009 22:59:11

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=test11)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0
LOCAL SERVER
The command completed successfully

Nema db servisa.

PMON dinamički registrira db servis sa listenerom, ali ja ću svejedno forsirati registraciju sa komandom “alter system register”.

$ sqlplus “/as sysdba”

SQL> alter system register;

System altered.

$ lsnrctl status LISTENER_1

LSNRCTL for Linux: Version 11.1.0.7.0 – Production on 21-MAR-2009 23:01:53

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
————————
Alias                     LISTENER_1
Version                   TNSLSNR for Linux: Version 11.1.0.7.0 – Production
Start Date                21-MAR-2009 22:58:54
Uptime                    0 days 0 hr. 2 min. 58 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/oracle/product/11.1.0/network/admin/listener.ora
Listener Log File         /app/oracle/diag/tnslsnr/dibidus2/listener_1/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=test11)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.10.18)(PORT=1521)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Service “test11” has 1 instance(s).
Instance “test11”, status READY, has 1 handler(s) for this service…
Service “test11XDB” has 1 instance(s).
Instance “test11”, status READY, has 1 handler(s) for this service…
Service “test11_XPT” has 1 instance(s).
Instance “test11”, status READY, has 1 handler(s) for this service…
The command completed successfully

Pa ćemo testirati:

$ tnsping test11

TNS Ping Utility for Linux: Version 11.1.0.7.0 – Production on 21-MAR-2009 23:03:19

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

Used parameter files:
/app/oracle/product/11.1.0/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.10.18)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test11)))
OK (0 msec)

$ sqlplus system/qwertz@test11

SQL*Plus: Release 11.1.0.7.0 – Production on Sat Mar 21 23:03:54 2009

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Radi 🙂

A sad da konfiguriramo i drugi listener proces za 10g bazu podataka.

LISTENER.ORA
# listener.ora Network Configuration File: /app/oracle/product/10.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER_2 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /app/oracle/product/10.2.0)
(PROGRAM = extproc)
)
)

LISTENER_2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = test11))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.10.18)(PORT = 1522))
)

TNSNAMES.ORA
TEST10 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.10.18)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = test10)
)
)

Za LISTENER_2 ću morati promijeniti defaultni port 1521 s obzirom da LISTENER_1 već sluša na tom portu, pa ću u ovom primjeru koristiti 1522 port.

PMON će po default-u registrirati db servis sa listenerom koji sluša na portu 1521 (u našem slučaju LISTENER_1).  Mi nekako moramo natjerati PMON da registrira db servis sa listenerom koji sluša na nekom drugom portu (1522 u našem slučaju).

To možemo postići ako ispravno definiramo LOCAL_LISTENER parametar.

(Iz dokumentacije) LOCAL_LISTENER specifies a network name that resolves to an address or address list of Oracle Net local listeners.

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=10.2.10.18)(PORT=1522))’ scope=both;

System altered.

Pod local_listener sam specificirao adresu LISTENER_2 listenera i zapisao vrijednost u memoriju i parametar datoteku.

Pa da testiramo:

$ lsnrctl start LISTENER_2

$ sqlplus “/as sysdba”

SQL> alter system register;

System altered.

$ lsnrctl status LISTENER_2

LSNRCTL for Linux: Version 10.2.0.4.0 – Production on 21-MAR-2009 23:19:00

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.2.10.18)(PORT=1522)))
STATUS of the LISTENER
————————
Alias                     LISTENER_2
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 – Production
Start Date                21-MAR-2009 23:18:06
Uptime                    0 days 0 hr. 0 min. 53 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/oracle/product/10.2.0/network/admin/listener.ora
Listener Log File         /app/oracle/product/10.2.0/network/log/listener_2.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.10.18)(PORT=1522)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Service “test10” has 1 instance(s).
Instance “test10”, status READY, has 1 handler(s) for this service…
Service “test10XDB” has 1 instance(s).
Instance “test10”, status READY, has 1 handler(s) for this service…
Service “test10_DGB” has 1 instance(s).
Instance “test10”, status READY, has 1 handler(s) for this service…
Service “test10_XPT” has 1 instance(s).
Instance “test10”, status READY, has 1 handler(s) for this service…
The command completed successfully

$ tnsping test10

TNS Ping Utility for Linux: Version 10.2.0.4.0 – Production on 21-MAR-2009 23:19:26

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

Used parameter files:
/app/oracle/product/10.2.0/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.10.18)(PORT = 1522))) (CONNECT_DATA = (SERVICE_NAME = test10)))
OK (0 msec)

$ sqlplus system/qwertz@test10

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Mar 21 23:19:43 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Radi 🙂

Listener procesi na db serveru (imam ih dva – LISTENER_1 i LISTENER_2) :
$ ps -ef |grep tnslsnr
oracle     444     1  0 23:18 ?        00:00:00 /app/oracle/product/10.2.0/bin/tnslsnr LISTENER_2 -inherit
oracle     601 31512  0 23:20 pts/3    00:00:00 grep tnslsnr
oracle   31773     1  0 22:58 ?        00:00:00 /app/oracle/product/11.1.0/bin/tnslsnr LISTENER_1 -inherit

  1. 2 Responses to “Dva različita listenera na jednom Oracle db serveru”

  2. Odlican tekst!
    Dodao bih samo, da se moze u listener.ora dodati i podrska za pojedinacne instance, tako da ne moras bazu eksplicitno registrovati:

    (SID_DESC =
    (SID_NAME = testbaza)
    (ORACLE_HOME = /app/oracle/product/10.2.0)
    )

    By Dejan on Mar 23, 2009

  3. Da, čak mislim da DBAši uglavnom preferiraju takvu konfiguraciju, ali eto za ovaj post se nisam sjetio to napisati.

    Pa super da si dodao 😉
    thx

    By Marko on Mar 23, 2009

Post a Comment