Service/Listener : Como criar um novo serviço no Listener em ambientes Single Instance
Neste documento vou demonstrar como criar um novo serviço no listener para receber conexões com varias finalidades, uma delas é distinguir e segregar acessos de aplicações especificas no ambiente dessa forma temos maior rastreabilidade de qual serviço esta sendo usando, ou para balanceamento de carga de tal forma que em ambientes RAC podemos restringir o acesso pelo serviço apenas em um node específico e assim temos a possibilidade de realizar rotinas administrativas no ambiente como aplicar patches de maneira faseada ou restart do ambiente e etc sem afetar o uso do ambiente.
A partir do Oracle 10g foi introduzida a Package - DBMS_SERVICE para esta finalidade, abaixo podemos ver como criar um novo serviço com o CREATE_SERVICE:
INSTANCE_NAME HOST_NAME
---------------- ----------------------------------------------------------------
orcl1 ol07db01.localdomain
SQL> exec dbms_service.create_service('orcl1_svc_new','orcl1_svc_new');
PL/SQL procedure successfully completed.
SQL>
Validando o serviço criado:
SQL> COLUMN name FORMAT A30
COLUMN network_name FORMAT A30
SELECT name, network_name FROM dba_services where name = 'orcl1_svc_new' ORDER BY 1;SQL> SQL> SQL>
NAME NETWORK_NAME
------------------------------ ------------------------------
orcl1_svc_new orcl1_svc_new
SQL>
Agora devemos ativar o serviço:
SQL> exec dbms_service.start_service('orcl1_svc_new');
PL/SQL procedure successfully completed.
OBS: caso precisa desativar um serviço use DBMS_SERVICE.STOP_SERVICE;
Vamos agora validar o serviço ativo:
SQL> SELECT name,
network_name
FROM v$active_services
ORDER BY 1; 2 3 4
NAME NETWORK_NAME
------------------------------ ------------------------------
SYS$BACKGROUND
SYS$USERS
orcl1 orcl1
orcl1XDB orcl1XDB
orcl1_DGB orcl1_DGB
orcl1_svc_new orcl1_svc_new
6 rows selected.
Podemos ver acima que o serviço já consta como ativo na view - V$ACTIVE_SERVICES.
Se olharmos o listener agora, temos um novo serviço já criado apontando para o nosso database orcl1:
[oracle@ol07db01 admin]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 09-JUL-2024 02:36:15
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol07db01.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 02-JUL-2024 01:45:26
Uptime 7 days 0 hr. 50 min. 49 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ol07db01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol07db01.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl1" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orcl1XDB" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orcl1_DGB" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orcl1_DGMGRL" has 1 instance(s).
Instance "orcl1", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl1_svc_new" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@ol07db01 admin]$
Agora devemos configurar um entrada no TNSNAMES.ORA para testarmos o acesso por esse novo serviço criado:
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
#LISTENER_ORCL1 =
# (ADDRESS = (PROTOCOL = TCP)(HOST = ol07db01.localdomain)(PORT = 1521))
orcl1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.3)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl1)
)
)
orcl1_stby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.4)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl1)
)
teste_svc_orcl1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.3)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl1_svc_new)
)
))
[oracle@ol07db01 admin]$ sqlplus teste/teste@teste_svc_orcl1
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 9 02:45:33 2024
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Tue Jul 09 2024 02:02:23 -03:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
SQL>
SQL>
SQL> set lines 9999 pages 9999
SQL> select instance_name, host_name from v$instance;
INSTANCE_NAME HOST_NAME
---------------- ----------------------------------------------------------------
orcl1 ol07db01.localdomain
SQL> select instance_name, host_name from v$instance;
INSTANCE_NAME HOST_NAME
---------------- ----------------------------------------------------------------
orcl1 ol07db01.localdomain
SQL> select sid, serial#, username, service_name, status from v$session where username = 'TESTE';
SID SERIAL# USERNAME SERVICE_NAME STATUS
---------- ---------- ------------------------------ -------------- --------
82 4249 TESTE orcl1_svc_new INACTIVE
SQL>
Comentários
Postar um comentário