Oracle secure listener

Oracle secure listener https://anuj-singh.blogspot.com/2016/09/oracle-session-count-info-and-kill-and.html

$ srvctl add listener -listener SECURE_LISTENER  -oraclehome /u01/app/19.3.0/grid -endpoints "TCPS:2488"
$ srvctl config listener -l SECURE_LISTENER
$ srvctl start listener -l SECURE_LISTENER
$ srvctl status listener -l SECURE_LISTENER



-Modify listener - 

srvctl  modify listener -l listener -endpoints TCP:1521/TCPS:2488
 
-Set below parameter in listener.ora 

SSL_CLIENT_AUTHENTICATION=FALSE
 
-validate sqlnet.ora at DB HOME location
 
-update local listener 

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=ORA)(PORT=1521))','(ADDRESS=(PROTOCOL=tcps)(HOST=ORA)(PORT=2488))';
 
System altered.
 

ps -ef|grep -i tns
root        572      2  0 Apr25 ?        00:00:00 [netns]
oracle    50290      1  0 03:32 ?        00:00:26 /u01/app/19.3.0.0/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit
oracle    86637      1  0 04:07 ?        00:00:17 /u01/app/oracle/product/19.0.0/db_1/bin/tnslsnr TCPS_LISTENER -inherit


 ps -ef|grep -i tns

oracle    45866      1  0 Jun07 ?        1-16:12:34 /u01/app/19.3.0.0/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit
oracle    65750      1  0 04:09 ?        00:00:41 /u01/app/oracle/product/19.0.0/db_1/bin/tnslsnr TCPS_LISTENER -inherit



to test
sqlplus test/test123@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=ORA)(PORT=2488))(CONNECT_DATA=(SID=ora1)(SERVICE_NAME=XXXXX)))




srvctl config listener -l LISTENER
Name: LISTENER
Type: Database Listener
Home: /u01/app/19.3.0.0/grid
End points: TCP:1521
Listener is enabled.



 srvctl config listener -l TCPS_LISTENER
Name: TCPS_LISTENER
Type: Database Listener
Home: /u01/app/oracle/product/19.0.0/db_1
End points: TCPS:2488
Listener is enabled.



srvctl stop listener -l LISTENER2
srvctl config listener -l LISTENER2
srvctl remove listener -l LISTENER2
srvctl config listener -l LISTENER2
srvctl status listener -l LISTENER2






 locate listener.log
/u01/app/oracle/diag/tnslsnr/orcl/listener/trace/listener.log

/u01/app/oracle/diag/tnslsnr/orcl/tcps_listener/trace/tcps_listener.log



connection info !!!

----  Protocol for all Oracle connections.  


col NETWORK_SERVICE_BANNER for a90
select
distinct   NETWORK_SERVICE_BANNER
from
   gv$session_connect_info
;


SELECT
    sid,network_service_banner
FROM
    v$session_connect_info
WHERE sid=(SELECT sid FROM v$mystat WHERE ROWNUM = 1)
AND NETWORK_SERVICE_BANNER like '%Encryption%';


alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 400 pagesize 400
col kill for a17 
col machine for a38
col event for a28
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,machine
,logon_time
--,program
,event
,sql_id
,prev_sql_id,
case when program not like 'ora___@% (P%)' then
(select max(case
when NETWORK_SERVICE_BANNER like '%TCP/IP%'       	then 'TCP'
when NETWORK_SERVICE_BANNER like '%Bequeath%'       then 'BEQUEATH'
when NETWORK_SERVICE_BANNER like '%IPC%'     		then 'IPC'
when NETWORK_SERVICE_BANNER like '%SDP%'     		then 'SDP'
when NETWORK_SERVICE_BANNER like '%NAMED P%'     	then 'Named pipe'
when NETWORK_SERVICE_BANNER is null
      then 'TCPS' end)
	  from    gv$session_connect_info i
where 1=1
 and  i.sid=s.sid and i.inst_id = s.inst_id) end protocol
from
   gv$session s
   where 1=1 
   AND type != 'BACKGROUND'
  -- and  program not like '%ora%'
   order by logon_time desc 
   ;