[1] Set up Network Tracing / Logging with LISTENER.ora
1.1 TRACE_LEVEL_LISTENER & TRACE_DIRECTORY_LISTENER
1.2 TRACE_FILELEN_LISTENER & TRACE_FILENO_LISTENER
1.3 LOGGING_LISTENER,LOG_FILE_LISTENER & LOG_DIRECTORY_LISTENER
1.4 DIAG_ADR_ENABLED_LISTENER
[2] Set up Network Tracing / Logging with SQLNET.ora
2.1 How to disable Operating system authenticate ?
2.2 Check use_dedicated_server with shared server
2.3 TRACE_LEVEL_CLIENT,TRACE_LEVEL_SERVER & TRACE_DIRECTORY_CLIENT,TRACE_DIRECTORY_SERVER
- NETCA can be use to add listener – NETCA can be use to add service at tnsnames.ora on “Local Net Service Name configuration”; – NETCA can be use to add service at sqlnet.ora on “Naming Methods Configuration”; Parameters: REMOTE_LISTENER= For remote listeners; LOCAL_LISTENER= For local listeners; - by default ADR is enabled so to disable ADR : DIAG_ADR_ENABLED_listener_name parameter is set to off – To enable tracing : TRACE_LEVEL_listener_name; – Oracle Restart Configuration (You must install GRID configuration first); Oracle Connection Manager is a proxy server that forwards connection requests to databases or other proxy servers. It operates on the session level. It usually resides on a computer separate from the database server and client computers. Oracle Connection Manager is available for installation with Oracle Database 12c Enterprise Edition. [1] Set up Network Tracing / Logging with LISTENER.ora 1. Documentation -> Database Administration -> Net Services Administrator’s Guide -> 16 Troubleshooting Oracle Net Services -> Tracing Error Information for Oracle Net Services #Logging Error Information for Oracle Net Services #Tracing Error Information for Oracle Net Services What do you need to study: Parameter to enable traceability of the connections coming through the listener (listener.ora) Parameter to enable traceability of all clients / servers connections (sqlnet.ora) The different trace levels to use We can enable trace in the LISTENER but can also generate trace customers using the SQL * Net (For SQL * Plus example). Enabling traces started in the LISTENER. To enable trace in the LISTENER have to modify the listener.ora file. We review the various key parameters that can enable the LISTENER: DIAG_ADR_ENABLED_listener_name => Enables repository hierarchy traces and logs (default = ON) ADR_BASE_listener_name => Directory ADR (default = $ORACLE_BASE) LOGGING_listener_name => Enables logging of LISTENER, not to be confused with traces (default = ON) TRACE_LEVEL_listener_name => Level trace: OFF [0] / USER [4] / ADMIN [10] / SUPPORT [16] (default = OFF) TRACE_TIMESTAMP_listener_name => Enter a timestamp on each event (default = ON) The following parameters apply only when we have the DIAG_ADR_ENABLED = parameter OFF TRACE_DIRECTORY_listener_name => Directory destination trace file (default = ORACLE_HOME/network/trace) TRACE_FILE_listener_name => Name of trace file (default = listener.trc) TRACE_FILELEN_listener_name => KB maximum file size (default = unlimited) TRACE_FILENO_listener_name => Number of trace files (default = 1) By default, we DIAG_ADR_ENABLED parameter = ON (but do not specify) then to enable trace TRACE_LEVEL enough to define the parameter. If we want to go to a different trace file that was used by default with ADR, we set the DIAG_ADR_ENABLED = OFF(DIAG_ADR_ENABLED_LISTENER=OFF) parameter and then specify the directory and file name (TRACE_FILE and TRACE_DIRECTORY parameters). HOL : TRACE_LEVEL_LISTENER & TRACE_DIRECTORY_LISTENER #TRACE_LEVEL_LISTENER With ADR -bash-4.1$ vi listener.ora -bash-4.1$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(HOST = racnode1)(PORT = 1521)) (ADDRESS = (PROTOCOL = ipc)(KEY = PNPKEY)) ) TRACE_LEVEL_LISTENER=USER $ lsnrctl reload LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 31-JUL-2019 17:11:13 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521)) The command completed successfully -bash-4.1$ lsnrctl status | grep "Trace File" Listener Trace File /u01/app/oracle/diag/tnslsnr/racnode1/listener/trace/ora_4115_140570732438976.trc $ tail -10f /u01/app/oracle/diag/tnslsnr/racnode1/listener/trace/ora_4115_140570732438976.trc 2019-07-31 17:13:05.735222 : nstoSetupTimeout:ATO enabled for ctx=0x0x2319870, val=60000(millisecs) 2019-07-31 17:13:05.735289 : nstoUpdateActive:Active timeout is 0 (see nstotyp) 2019-07-31 17:13:05.735305 : nsopen:opening transport... 2019-07-31 17:13:05.735360 : nttcnp:getting sockname 2019-07-31 17:13:05.735401 : nttcnp:getting peername 2019-07-31 17:13:05.735415 : nttcnr:waiting to accept a connection. 2019-07-31 17:13:05.735437 : nttcnr:getting sockname 2019-07-31 17:13:05.735454 : nttcnr:connected on source ipaddr 192.168.56.101 port 1521 .......... 2019-07-31 17:13:11.277675 : nsglgrDoRegister:service:cdb1 what:2 value:0 2019-07-31 17:13:11.277827 : nsdo:352 bytes to NS buffer #Without ADR -bash-4.1$ vi listener.ora -bash-4.1$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(HOST = racnode1)(PORT = 1521)) (ADDRESS = (PROTOCOL = ipc)(KEY = PNPKEY)) ) TRACE_LEVEL_LISTENER=USER DIAG_ADR_ENABLED_LISTENER=OFF -bash-4.1$ lsnrctl reload LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 31-JUL-2019 17:17:53 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521)) The command completed successfully -bash-4.1$ lsnrctl status | grep "Trace File" Listener Trace File /u01/app/oracle/product/12.1.0.2/db_1/network/trace/listener.trc -bash-4.1$ -bash-4.1$ tail -10f /u01/app/oracle/product/12.1.0.2/db_1/network/trace/listener.trc [31-JUL-2019 17:19:24:164] nsglgrDoRegister: service:pdb1 what:4 value:1 [31-JUL-2019 17:19:24:164] nsglgrDoRegister: service:pdb1 what:2 value:0 [31-JUL-2019 17:19:24:164] nsglgrDoRegister: service:cdb1 what:4 value:1 [31-JUL-2019 17:19:24:164] nsglgrDoRegister: service:cdb1 what:2 value:0 [31-JUL-2019 17:19:24:164] nsdo: 352 bytes to NS buffer .......... [31-JUL-2019 17:19:33:174] nsglgrDoRegister: service:cdb1 what:4 value:1 [31-JUL-2019 17:19:33:174] nsglgrDoRegister: service:cdb1 what:2 value:0 [31-JUL-2019 17:19:33:174] nsdo: 352 bytes to NS buffer #TRACE_DIRECTORY_LISTENER -bash-4.1$ net -bash-4.1$ vi listener.ora -bash-4.1$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(HOST = racnode1)(PORT = 1521)) (ADDRESS = (PROTOCOL = ipc)(KEY = PNPKEY)) ) TRACE_LEVEL_LISTENER=USER DIAG_ADR_ENABLED_LISTENER=OFF TRACE_DIRECTORY_LISTENER=/home/oracle -bash-4.1$ -bash-4.1$ lsnrctl reload LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 31-JUL-2019 17:45:25 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521)) The command completed successfully -bash-4.1$ -bash-4.1$ lsnrctl status | grep "Trace File" Listener Trace File /home/oracle/listener.trc -bash-4.1$ -bash-4.1$ tail -10f /home/oracle/listener.trc [31-JUL-2019 17:45:30:282] nsgldissolve: Deallocating cxd 0x2322980. [31-JUL-2019 17:45:34:225] nsdo: 716 bytes from NS buffer [31-JUL-2019 17:45:34:225] nsglgrDoRegister: inst loads: ld1:46 mld1:10240 ld2:0 mld2:472 [31-JUL-2019 17:45:34:225] nsglgrDoRegister: service:pdb2 what:4 value:1 ............. [31-JUL-2019 17:45:55:233] nsglgrDoRegister: service:cdb1 what:4 value:1 [31-JUL-2019 17:45:55:233] nsglgrDoRegister: service:cdb1 what:2 value:1 [31-JUL-2019 17:45:55:233] nsdo: 352 bytes to NS buffer --------------------------------------------------------------------------------------- # To disable it, just we have to erase/comments and reload parameter settings LISTENER --------------------------------------------------------------------------------------- -bash-4.1$ vi listener.ora -bash-4.1$ -bash-4.1$ cat listener cat: listener: No such file or directory -bash-4.1$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(HOST = racnode1)(PORT = 1521)) (ADDRESS = (PROTOCOL = ipc)(KEY = PNPKEY)) ) #TRACE_LEVEL_LISTENER=USER #DIAG_ADR_ENABLED_LISTENER=OFF #TRACE_DIRECTORY_LISTENER=/home/oracle -bash-4.1$ -bash-4.1$ lsnrctl reload LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 31-JUL-2019 17:47:46 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521)) The command completed successfully -bash-4.1$ -bash-4.1$ lsnrctl status | grep "Trace File" -bash-4.1$ -bash-4.1$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 31-JUL-2019 17:47:54 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 31-JUL-2019 16:56:31 Uptime 0 days 0 hr. 51 min. 23 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/racnode1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))) Services Summary... Service "cdb1" has 1 instance(s). Instance "cdb1", status READY, has 3 handler(s) for this service... Service "cdb1XDB" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Service "pdb1" has 1 instance(s). Instance "cdb1", status READY, has 3 handler(s) for this service... Service "pdb2" has 1 instance(s). Instance "cdb1", status READY, has 3 handler(s) for this service... The command completed successfully ---------------------------------------------------------------------------------------- #You can enable trace without modifying the listener.ora file with the LSNRCTL utility. ---------------------------------------------------------------------------------------- -bash-4.1$ lsnrctl LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 31-JUL-2019 17:50:37 Copyright (c) 1991, 2014, Oracle. All rights reserved. Welcome to LSNRCTL, type "help" for information. LSNRCTL> help The following operations are available An asterisk (*) denotes a modifier or extended command: start stop status services version reload save_config trace spawn quit exit set* show* LSNRCTL> trace user listener Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521)) Opened trace file: /u01/app/oracle/diag/tnslsnr/racnode1/listener/trace/ora_4115_140570732438976.trc The command completed successfully LSNRCTL> LSNRCTL> trace admin listener Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521)) Opened trace file: /u01/app/oracle/diag/tnslsnr/racnode1/listener/trace/ora_4115_140570732438976.trc The command completed successfully LSNRCTL> LSNRCTL> trace support listener Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521)) Opened trace file: /u01/app/oracle/diag/tnslsnr/racnode1/listener/trace/ora_4115_140570732438976.trc The command completed successfully LSNRCTL> LSNRCTL> trace off listener Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521)) The command completed successfully LSNRCTL> LSNRCTL> status Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 31-JUL-2019 16:56:31 Uptime 0 days 0 hr. 55 min. 27 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/racnode1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))) Services Summary... Service "cdb1" has 1 instance(s). Instance "cdb1", status READY, has 3 handler(s) for this service... Service "cdb1XDB" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Service "pdb1" has 1 instance(s). Instance "cdb1", status READY, has 3 handler(s) for this service... Service "pdb2" has 1 instance(s). Instance "cdb1", status READY, has 3 handler(s) for this service... The command completed successfully LSNRCTL> exit -bash-4.1$ HOL : TRACE_FILELEN_LISTENER & TRACE_FILENO_LISTENER -bash-4.1$ vi listener.ora -bash-4.1$ -bash-4.1$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(HOST = racnode1)(PORT = 1521)) (ADDRESS = (PROTOCOL = ipc)(KEY = PNPKEY)) ) DIAG_ADR_ENABLED_LISTENER=OFF TRACE_LEVEL_LISTENER=ADMIN TRACE_DIRECTORY_LISTENER=/home/oracle/mylistener TRACE_FILELEN_LISTENER=1024 TRACE_FILENO_LISTENER=5 -bash-4.1$ Note: Please make a note that if you are providing wrong parameter name then listener not giving any error but silently ignore it. -bash-4.1$ lsnrctl reload LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 31-JUL-2019 18:27:31 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521)) The command completed successfully -bash-4.1$ -bash-4.1$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 31-JUL-2019 18:27:39 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 31-JUL-2019 18:18:46 Uptime 0 days 0 hr. 8 min. 53 sec Trace Level admin 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/product/12.1.0.2/db_1/network/log/listener.log Listener Trace File /home/oracle/mylistener/listener1.trc Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))) Services Summary... Service "cdb1" has 1 instance(s). Instance "cdb1", status READY, has 3 handler(s) for this service... Service "cdb1XDB" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Service "pdb1" has 1 instance(s). Instance "cdb1", status READY, has 3 handler(s) for this service... Service "pdb2" has 1 instance(s). Instance "cdb1", status READY, has 3 handler(s) for this service... The command completed successfully -bash-4.1$ -bash-4.1$ ls -ltr total 76 -rw-r----- 1 oracle oinstall 0 Jul 31 18:27 listener5.trc -rw-r----- 1 oracle oinstall 0 Jul 31 18:27 listener4.trc -rw-r----- 1 oracle oinstall 0 Jul 31 18:27 listener3.trc -rw-r----- 1 oracle oinstall 0 Jul 31 18:27 listener2.trc -rw-r----- 1 oracle oinstall 77425 Jul 31 18:28 listener1.trc -bash-4.1$ for i in { 1..200 }; do lsnrctl status; done -bash-4.1$ ls -ltr total 2064 -rw-r----- 1 oracle oinstall 0 Jul 31 18:27 listener5.trc -rw-r----- 1 oracle oinstall 0 Jul 31 18:27 listener4.trc -rw-r----- 1 oracle oinstall 1048202 Jul 31 18:30 listener1.trc -rw-r----- 1 oracle oinstall 1048180 Jul 31 18:30 listener2.trc -rw-r----- 1 oracle oinstall 14635 Jul 31 18:30 listener3.trc HOL : LOGGING_LISTENER,LOG_FILE_LISTENER & LOG_DIRECTORY_LISTENER -bash-4.1$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(HOST = racnode1)(PORT = 1521)) (ADDRESS = (PROTOCOL = ipc)(KEY = PNPKEY)) ) DIAG_ADR_ENABLED_LISTENER=OFF LOGGING_LISTENER=ON LOG_FILE_LISTENER=hitgon LOG_DIRECTORY_LISTENER=/home/oracle/mylistener TRACE_LEVEL_LISTENER=USER TRACE_DIRECTORY_LISTENER=/home/oracle/mylistener -bash-4.1$ vi listener.ora -bash-4.1$ -bash-4.1$ lsnrctl reload LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 31-JUL-2019 18:58:52 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521)) The command completed successfully -bash-4.1$ -bash-4.1$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 31-JUL-2019 18:58:57 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 31-JUL-2019 18:18:46 Uptime 0 days 0 hr. 40 min. 11 sec Trace Level user 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 /home/oracle/mylistener/hitgon.log Listener Trace File /home/oracle/mylistener/listener.trc Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))) Services Summary... Service "cdb1" has 1 instance(s). Instance "cdb1", status READY, has 3 handler(s) for this service... Service "cdb1XDB" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Service "pdb1" has 1 instance(s). Instance "cdb1", status READY, has 3 handler(s) for this service... Service "pdb2" has 1 instance(s). Instance "cdb1", status READY, has 3 handler(s) for this service... The command completed successfully -bash-4.1$ -bash-4.1$ cd /home/oracle/mylistener/ -bash-4.1$ tail -f hitgon.log 31-JUL-2019 18:57:20 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=racnode1)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=202375680)) * status * 0 System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora Trace information written to /home/oracle/mylistener/listener.trc Trace level is currently 4 Log messages written to /home/oracle/mylistener/hitgon.log 31-JUL-2019 18:58:52 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=racnode1)(USER=oracle))(COMMAND=reload)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=202375680)) * reload * 0 31-JUL-2019 18:58:52 * service_register * cdb1 * 0 WARNING: Subscription for node down event still pending 31-JUL-2019 18:58:57 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=racnode1)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=202375680)) * status * 0 31-JUL-2019 18:59:04 * service_update * cdb1 * 0 ^C -bash-4.1$ tail -f listener.trc [31-JUL-2019 18:58:57:183] nsgldissolve: Deallocating cxd 0x11520a0. [31-JUL-2019 18:59:04:609] nsdo: 716 bytes from NS buffer [31-JUL-2019 18:59:04:609] nsglgrDoRegister: inst loads: ld1:5 mld1:10240 ld2:3 mld2:472 [31-JUL-2019 18:59:04:609] nsglgrDoRegister: service:pdb2 what:4 value:1 [31-JUL-2019 18:59:04:609] nsglgrDoRegister: service:pdb2 what:2 value:0 [31-JUL-2019 18:59:04:609] nsglgrDoRegister: service:pdb1 what:4 value:1 [31-JUL-2019 18:59:04:609] nsglgrDoRegister: service:pdb1 what:2 value:0 [31-JUL-2019 18:59:04:609] nsglgrDoRegister: service:cdb1 what:4 value:1 [31-JUL-2019 18:59:04:609] nsglgrDoRegister: service:cdb1 what:2 value:0 [31-JUL-2019 18:59:04:609] nsdo: 352 bytes to NS buffer HOL : DIAG_ADR_ENABLED_LISTENER -bash-4.1$ net -bash-4.1$ vi listener.ora -bash-4.1$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(HOST = racnode1)(PORT = 1521)) (ADDRESS = (PROTOCOL = ipc)(KEY = PNPKEY)) ) DIAG_ADR_ENABLED_LISTENER=OFF -bash-4.1$ -bash-4.1$ lsnrctl reload LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 31-JUL-2019 19:03:16 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521)) The command completed successfully -bash-4.1$ -bash-4.1$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 31-JUL-2019 19:03:20 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 31-JUL-2019 18:18:46 Uptime 0 days 0 hr. 44 min. 34 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/product/12.1.0.2/db_1/network/log/listener.log <<----ADR is OFF Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))) Services Summary... Service "cdb1" has 1 instance(s). Instance "cdb1", status READY, has 3 handler(s) for this service... Service "cdb1XDB" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Service "pdb1" has 1 instance(s). Instance "cdb1", status READY, has 3 handler(s) for this service... Service "pdb2" has 1 instance(s). Instance "cdb1", status READY, has 3 handler(s) for this service... The command completed successfully -bash-4.1$ -bash-4.1$ -bash-4.1$ vi listener.ora -bash-4.1$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(HOST = racnode1)(PORT = 1521)) (ADDRESS = (PROTOCOL = ipc)(KEY = PNPKEY)) ) -bash-4.1$ -bash-4.1$ lsnrctl reload LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 31-JUL-2019 19:03:41 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521)) The command completed successfully -bash-4.1$ -bash-4.1$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 31-JUL-2019 19:03:47 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 31-JUL-2019 18:18:46 Uptime 0 days 0 hr. 45 min. 1 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/racnode1/listener/alert/log.xml <<----DEFAULT ADR is ON Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))) Services Summary... Service "cdb1" has 1 instance(s). Instance "cdb1", status READY, has 3 handler(s) for this service... Service "cdb1XDB" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Service "pdb1" has 1 instance(s). Instance "cdb1", status READY, has 3 handler(s) for this service... Service "pdb2" has 1 instance(s). Instance "cdb1", status READY, has 3 handler(s) for this service... The command completed successfully SQL> set pagesize 1000 SQL> set linesize 1000 SQL> SELECT * FROM V$DIAG_INFO; INST_ID NAME VALUE CON_ID ---------- ------------------------ ---------------------------------------------------------------------------- 1 Diag Enabled TRUE 0 1 ADR Base /u01/app/oracle 0 1 ADR Home /u01/app/oracle/diag/rdbms/cdb1/cdb1 0 1 Diag Trace /u01/app/oracle/diag/rdbms/cdb1/cdb1/trace 0 1 Diag Alert /u01/app/oracle/diag/rdbms/cdb1/cdb1/alert 0 1 Diag Incident /u01/app/oracle/diag/rdbms/cdb1/cdb1/incident 0 1 Diag Cdump /u01/app/oracle/diag/rdbms/cdb1/cdb1/cdump 0 1 Health Monitor /u01/app/oracle/diag/rdbms/cdb1/cdb1/hm 0 1 Default Trace File /u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_ora_4127.trc 0 1 Active Problem Count 0 0 1 Active Incident Count 0 0 11 rows selected. SQL> show parameter diag NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ diagnostic_dest string /u01/app/oracle [2] Set up Network Tracing / Logging with SQLNET.ora Below are the some of the parameters BEQUEATH_DETACH -> To turn signal handling on or off for Linux and UNIX systems. DEFAULT_SDU_SIZE -> To specify the session data unit (SDU) size, in bytes to connections. default 8192 bytes (8 KB) NAMES.DEFAULT_DOMAIN To set the domain from which the client most often looks up names resolution requests. NAMES.DEFAULT_DOMAIN=example.com NAMES.DIRECTORY_PATH To specify the order of the naming methods used for client name resolution lookups. Default is NAMES.DIRECTORY_PATH=(tnsnames, ldap, ezconnect) RECV_BUF_SIZE To specify the buffer space limit for receive operations of sessions. The default value for this parameter is operating system specific. The default for Linux 2.6 operating system is 87380 bytes. SEND_BUF_SIZE To specify the buffer space limit for send operations of sessions. The default value for this parameter is operating system specific. The default for Linux 2.6 operating system is 16 KB. ------------------------------ SQLNET.AUTHENTICATION_SERVICES ------------------------------ To enable one or more authentication services. If authentication has been installed, then it is recommended that this parameter be set to either none or to one of the listed authentication methods. Usage Notes -> When using the SQLNET.AUTHENTICATION_SERVICES value all, the server attempts to authenticate using each of the following methods. The server falls back to the ones lower on the list if the ones higher on the list were unsuccessful. 1. Authentication based on a service external to the database, such as a service on the network layer, Kerberos, or RADIUS. 2. Authentication based on the operating system user's membership in an administrative operating system group. Group names are platform-specific. This authentication is applicable to administrative connections only. 3. Authentication performed by the database. 4. Authentication based on credentials stored in a directory server. Operating system authentication allows access to the database using any user name and any password when an administrative connection is attempted, such as using the AS SYSDBA clause when connecting using SQL*Plus. An example of a connection is as follows. sqlplus ignored_username/ignored_password AS SYSDBA When the operating-system user who issued the preceding command is already a member of the appropriate administrative operating system group, then the connection is successful. This is because the user name and password are ignored by the server due to checking the group membership first. Default is all Values Authentication methods available with Oracle Net Services: none for no authentication methods, including Microsoft Windows native operating system authentication. When SQLNET.AUTHENTICATION_SERVICES is set to none, a valid user name and password can be used to access the database. all for all authentication methods. beq for native operating system authentication for operating systems other than Microsoft Windows kerberos5 for Kerberos authentication nts for Microsoft Windows native operating system authentication radius for Remote Authentication Dial-In User Service (RADIUS) authentication tcps for SSL authentication HOL: How to disable Operating system authenticate ? -bash-4.1$ cat sqlnet.ora | grep services # bvasudev 02/07/97 - Change sqlnet.authentication_services documentation #sqlnet.authentication_services # Enables one or more authentication services. If sqlnet.authentication_services=NONE -bash-4.1$ -bash-4.1$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 31 12:37:10 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. ERROR: ORA-01017: invalid username/password; logon denied Enter user-name: ERROR: ORA-01017: invalid username/password; logon denied Enter user-name: ERROR: ORA-01017: invalid username/password; logon denied SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus -bash-4.1$ -bash-4.1$ vi sqlnet.ora -bash-4.1$ -bash-4.1$ cat sqlnet.ora | grep services # bvasudev 02/07/97 - Change sqlnet.authentication_services documentation #sqlnet.authentication_services # Enables one or more authentication services. If sqlnet.authentication_services=ALL -bash-4.1$ -bash-4.1$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 31 12:37:31 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. ----------------------------------- SQLNET.ALLOWED_LOGON_VERSION_CLIENT ----------------------------------- To set the minimum authentication protocol allowed for clients, and when a server is acting as a client, such as connecting over a database link, when connecting to Oracle Database instances. Default is 11 Values 12a for Oracle Database 12c Release 1 (12.1) release 12.1.0.2 or later 12 for the critical patch updates CPUOct2012 and later Oracle Database 11g authentication protocols (recommended) 11 for Oracle Database 11g authentication protocols (default) 10 for Oracle Database 10g authentication protocols 8 for Oracle8i authentication protocol Example If an Oracle Database 12c database hosts a database link to an Oracle Database 10g database, then the SQLNET.ALLOWED_LOGON_VERSION_CLIENT parameter should be set as follows in order for the database link connection to proceed: SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10 ------------------------------------ SQLNET.ALLOWED_LOGON_VERSION_SERVER ------------------------------------ To set the minimum authentication protocol allowed when connecting to Oracle Database instances. Default is 11 SQLNET.COMPRESSION To enable or disable data compression. If both the server and client have this parameter set to ON, then compression is used for the connection. SQLNET.COMPRESSION_LEVELS To specify the compression level. SQLNET.COMPRESSION_THRESHOLD To specify the minimum data size, in bytes, for which compression is needed. Compression is not be done if the size of the data to be sent is less than this value. Default is 1024 bytes SQLNET.ENCRYPTION_CLIENT To turn encryption on for the client. Default is accepted SQLNET.ENCRYPTION_SERVER To turn encryption on for the database server. Default is accepted SQLNET.EXPIRE_TIME -> To specify a time interval, in minutes, to send a check to verify that client/server connections are active. Default is 0 but Recommended Value is 10 SQLNET.INBOUND_CONNECT_TIMEOUT -> To specify the time, in seconds, for a client to connect with the database server and provide the necessary authentication information. Default is 60 seconds SQLNET.OUTBOUND_CONNECT_TIMEOUT -> To specify the time, in seconds, for a client to establish an Oracle Net connection to the database instance. Default None SQLNET.RECV_TIMEOUT SQLNET.SEND_TIMEOUT SSL_VERSION TCP.CONNECT_TIMEOUT -> To specify the time, in seconds, for a client to establish a TCP connection (PROTOCOL=tcp in the TNS connect address) to the database server. Default is 60 seconds TCP.NODELAY -> To preempt delays in buffer flushing within the TCP/IP protocol stack. Default is yes TCP.QUEUESIZE -> To configure the maximum length of the queue for pending connections on a TCP listening socket. Default is System-defined maximum value. The defined maximum value for Linux is 128. --------------------------------------- Black / White listing Specific IP/HOST -------------------------------------- TCP.INVITED_NODES -> To specify which clients are allowed access to the database. -> This list takes precedence over the TCP.EXCLUDED_NODES parameter if both lists are present. Example: TCP.INVITED_NODES=(sales.us.example.com, hr.us.example.com, 192.0.*,2001:DB8:200C:433B/32) TCP.EXCLUDED_NODES -> To specify which clients are denied access to the database. Example: TCP.EXCLUDED_NODES=(finance.us.example.com, mktg.us.example.com, 192.0.2.25, 172.30.*, 2001:DB8:200C:417A/32) TCP.VALIDNODE_CHECKING Purpose : To enable and disable valid node checking for incoming connections. Usage Notes If this parameter is set to yes, then incoming connections are allowed only if they originate from a node that conforms to list specified by TCP.INVITED_NODES or TCP.EXCLUDED_NODES parameters. The TCP.INVITED_NODES and TCP.EXCLUDED_NODES parameters are valid only when the TCP.VALIDNODE_CHECKING parameter is set to yes. Default : no Values : yes | no Example : TCP.VALIDNODE_CHECKING=yes ----------------------- TNSPING.TRACE_DIRECTORY ------------------------ Purpose : To specify the destination directory for the TNSPING utility trace file, tnsping.trc. Default : The ORACLE_HOME/network/trace directory. Example : TNSPING.TRACE_DIRECTORY=/oracle/traces ------------------- TNSPING.TRACE_LEVEL ------------------- Purpose : To turn TNSPING utility tracing on at a specified level or to turn it off. Default : off Values off for no trace output user for user trace information admin for administration trace information support for Oracle Support Services trace information Example : TNSPING.TRACE_LEVEL=admin -------- USE_CMAN -------- Purpose : To specify client routing to Oracle Connection Manager. Usage Notes If set to true, then the parameter routes the client to a protocol address for Oracle Connection Manager. If set to false, then the client picks one of the address lists at random and fails over to the other address list if the chosen ADDRESS_LIST fails. With USE_CMAN=true, the client always uses the first address list. If no Oracle Connection Manager addresses are available, then connections are routed through any available listener address. Default : false Values true | false Example : USE_CMAN=true --------------------- USE_DEDICATED_SERVER --------------------- Purpose : To append (SERVER=dedicated) to the CONNECT_DATA section of the connect descriptor used by the client. Usage Notes It overrides the current value of the SERVER parameter in the tnsnames.ora file. If set to on, then the parameter USE_DEDICATED_SERVER automatically appends (SERVER=dedicated) to the connect data for a connect descriptor. This way connections from this client use a dedicated server process, even if shared server is configured. Default : off Values on to append (SERVER=dedicated) off to send requests to existing server processes Example : USE_DEDICATED_SERVER=on HOL : Check use_dedicated_server with shared server SQL> show parameter dispatcher NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dispatchers string (PROTOCOL=TCP)(SERVICE=cdb1XDB) max_dispatchers integer SQL> SQL> alter system set dispatchers='(INDEX=1)(ADDRESS=(PROTOCOL=TCP)(SERVICE=pdb1))(DISPATCHER=2)'; SQL> ALTER SYSTEM SET SHARED_SERVERS=3; System altered. -bash-4.1$ lsnrctl services PDB_LISTENER Service "pdb2" has 1 instance(s). Instance "cdb1", status READY, has 3 handler(s) for this service... Handler(s): "D002" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=50376)) "D001" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=41146)) "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER The command completed successfully cat tnsnames.ora SS_PDB2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1523)) (CONNECT_DATA = (SERVER = SHARED) (SERVICE_NAME = pdb2) ) ) -bash-4.1$ tnsping ss_pdb2 TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 31-JUL-2019 12:46:47 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1523)) (CONNECT_DATA = (SERVER = SHARED) (SERVICE_NAME = pdb2))) -bash-4.1$ cat sqlnet.ora | grep use_dedicated_server use_dedicated_server = ON -bash-4.1$ sqlplus system/oracle_4U@ss_pdb2 SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 31 12:48:42 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Wed Jul 31 2019 12:47:45 +05:30 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> exit TERMINAL-B SERVER COUNT(*) --------- ---------- DEDICATED 37 -bash-4.1$ cat sqlnet.ora | grep use_dedicated_server #use_dedicated_server = ON -bash-4.1$ sqlplus system/oracle_4U@ss_pdb2 SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 31 12:50:20 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Wed Jul 31 2019 12:48:50 +05:30 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> select count(*) from dual; TERMINAL-B SERVER COUNT(*) --------- ---------- DEDICATED 37 NONE 1 WALLET_LOCATION ->To specify the location of wallets. Wallets are certificates, keys, and trustpoints processed by SSL. vi.sqlnet.ora WALLET_LOCATION= (SOURCE= (METHOD=file) (METHOD_DATA= (DIRECTORY=$ORACLE_HOME/wallets))) -------------------------------------------------------------------------------------- The following sqlnet.ora parameters are used when ADR is enabled (when DIAG_ADR_ENABLED is set to on): -------------------------------------------------------------------------------------- ADR_BASE -> To specify the base directory into which tracing and logging incidents are stored when ADR is enabled. -> The default on the server side is ORACLE_BASE, or ORACLE_HOME/log, if ORACLE_BASE is not defined. -> Example : ADR_BASE=/oracle/network/trace DIAG_ADR_ENABLED -> To specify whether ADR tracing is enabled. -> If the DIAG_ADR_ENABLED parameter is set to OFF, then non-ADR file tracing is used. -> Default is on -> Example : DIAG_ADR_ENABLED=off TRACE_LEVEL_CLIENT / TRACE_LEVEL_SERVER -> To turn client and server tracing on at a specified level or to turn it off respectively. -> This parameter is also applicable when non-ADR tracing is used. -> Default : off or 0 -> Values off or 0 for no trace output user or 4 for user trace information admin or 10 for administration trace information support or 16 for Oracle Support Services trace information -> Example : TRACE_LEVEL_CLIENT=user / TRACE_LEVEL_SERVER=admin TRACE_TIMESTAMP_CLIENT / TRACE_TIMESTAMP_SERVER Purpose : To add a time stamp in the form of dd-mmm-yyyy hh:mm:ss:mil to every trace event in the client trace file, which has a default name of sqlnet.trc / svr_pid.trc Usage Notes : This parameter is also applicable when non-ADR tracing is used. Default : on Values : on or true | off or false Example : TRACE_TIMESTAMP_CLIENT=true / TRACE_TIMESTAMP_SERVER=true -------------------------------------------------------------------------------------- This section lists the sqlnet.ora parameters used when ADR is disabled. -------------------------------------------------------------------------------------- Notes: The default value of DIAG_ADR_ENABLED is on. Therefore, the DIAG_ADR_ENABLED parameter must explicitly be set to off in order for non-ADR tracing to be used. LOG_DIRECTORY_CLIENT / LOG_DIRECTORY_SERVER Purpose : To specify the destination directory for the client/server log file. Usage Notes : Use this parameter when ADR is not enabled. Default : ORACLE_HOME/network/log / ORACLE_HOME/network/trace Values : Any valid directory path. / Any valid directory path to a directory with write permission. Example : LOG_DIRECTORY_CLIENT=/oracle/network/log LOG_DIRECTORY_SERVER=/oracle/network/trace LOG_FILE_CLIENT / LOG_FILE_SERVER Purpose : To specify the name of the log file for the client / server. Usage Notes : Use this parameter when ADR is not enabled. Default : ORACLE_HOME/network/log/sqlnet.log LOG_FILE_CLIENT-> Values : The default value cannot be changed. LOG_FILE_SERVER=svr.log TRACE_DIRECTORY_CLIENT / TRACE_DIRECTORY_SERVER Purpose : To specify the destination directory for the client/server trace file. Usage Notes : Use this parameter when ADR is not enabled. Default : The current working directory. Values : Any valid directory path to a directory with write permission. Example : TRACE_DIRECTORY_CLIENT=/oracle/traces TRACE_DIRECTORY_SERVER=/oracle/traces TRACE_FILE_CLIENT / TRACE_FILE_SERVER Purpose : To specify the name of the client/server trace file. Usage Notes : Use this parameter when ADR is not enabled. Default : ORACLE_HOME/network/trace/cli.trc ORACLE_HOME/network/trace/svr_pid.trc Values : Any valid file name. Any valid file name. The process identifier (pid) is appended to the name automatically. Example : TRACE_FILE_CLIENT=clientsqlnet.trc TRACE_FILE_SERVER=svrsqlnet.trc TRACE_FILELEN_CLIENT / TRACE_FILELEN_SERVER Purpose : To specify the size of the client/server trace files in kilobytes (KB). Usage Notes : When the size is met, the trace information is written to the next file. The number of files is specified with the TRACE_FILENO_CLIENT parameter. Use this parameter when ADR is not enabled. Example : TRACE_FILELEN_CLIENT=100 TRACE_FILELEN_SERVER=100 TRACE_FILENO_CLIENT / TRACE_FILENO_SERVER Purpose : To specify the number of trace files for client/server tracing. Usage Notes : When this parameter is set with the TRACE_FILELEN_CLIENT parameter, trace files are used in a cyclical fashion. The first file is filled first, then the second file, and so on. When the last file has been filled, then the first file is re-used, and so on. The trace file names are distinguished from one another by their sequence number. For example, if the default trace file of sqlnet.trc is used, and this parameter is set to 3, then the trace files would be named sqlnet1.trc, sqlnet2.trc and sqlnet3.trc. In addition, trace events in the trace files are preceded by the sequence number of the file. Use this parameter when ADR is not enabled. Default : None Example : TRACE_FILENO_CLIENT=3 TRACE_FILENO_SERVER=3 TRACE_UNIQUE_CLIENT Purpose : To specify whether a unique trace file is created for each client trace session. Usage Notes : When the value is set to on, a process identifier is appended to the name of each trace file, enabling several files to coexist. For example, trace files named sqlnetpid.trc are created if default trace file name sqlnet.trc is used. When the value is set to off, data from a new client trace session overwrites the existing file. Use this parameter when ADR is not enabled. Default : on Values : on or off Example : TRACE_UNIQUE_CLIENT=on HOL : TRACE_LEVEL_CLIENT,TRACE_LEVEL_SERVER & TRACE_DIRECTORY_CLIENT,TRACE_DIRECTORY_SERVER -bash-4.1$ vi sqlnet.ora -bash-4.1$ cat sqlnet.ora NAMES.DIRECTORY_PATH=(TNSNAMES,ONAMES,EZCONNECT) ADR_BASE=/u01/app/oracle DIAG_ADR_ENABLED=OFF TRACE_LEVEL_CLIENT=SUPPORT TRACE_LEVEL_SERVER=SUPPORT TRACE_DIRECTORY_CLIENT=/home/oracle/mylistener/client1 TRACE_DIRECTORY_SERVER=/home/oracle/mylistener/server1 -bash-4.1$ -bash-4.1$ cd -bash-4.1$ cd mylistener/ -bash-4.1$ mkdir client1 server1 -bash-4.1$ ls client1 hitgon.log listener.trc server1 -bash-4.1$ -bash-4.1$ ll /home/oracle/mylistener/client1/ total 0 -bash-4.1$ ll /home/oracle/mylistener/server1/ total 20 -rw-r----- 1 oracle oinstall 3916 Jul 31 19:12 svr_6256.trc -rw-r----- 1 oracle oinstall 3916 Jul 31 19:13 svr_6261.trc -rw-r----- 1 oracle oinstall 3916 Jul 31 19:14 svr_6264.trc -rw-r----- 1 oracle oinstall 3916 Jul 31 19:14 svr_6266.trc -rw-r----- 1 oracle oinstall 3281 Jul 31 19:14 svr_6268.trc -bash-4.1$ -bash-4.1$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 31-JUL-2019 19:14:33 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 31-JUL-2019 18:18:46 Uptime 0 days 0 hr. 55 min. 47 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/racnode1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))) Services Summary... Service "cdb1" has 1 instance(s). Instance "cdb1", status READY, has 3 handler(s) for this service... Service "cdb1XDB" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Service "pdb1" has 1 instance(s). Instance "cdb1", status READY, has 3 handler(s) for this service... Service "pdb2" has 1 instance(s). Instance "cdb1", status READY, has 3 handler(s) for this service... The command completed successfully -bash-4.1$ -bash-4.1$ tnsping cdb1 TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 31-JUL-2019 19:14:55 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cdb1))) OK (10 msec) -bash-4.1$ -bash-4.1$ sqlplus system/oracle_4U@cdb1 SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 31 19:14:59 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Wed Jul 31 2019 18:14:07 +05:30 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> exit -bash-4.1$ ll /home/oracle/mylistener/client1/ total 244 -rw-r----- 1 oracle oinstall 16749 Jul 31 19:14 cli_6271.trc -rw-r----- 1 oracle oinstall 30834 Jul 31 19:14 cli_6290.trc -rw-r----- 1 oracle oinstall 196364 Jul 31 19:15 cli_6316.trc -bash-4.1$ -bash-4.1$ ll /home/oracle/mylistener/server1/ total 336 -rw-r----- 1 oracle oinstall 3916 Jul 31 19:12 svr_6256.trc -rw-r----- 1 oracle oinstall 3916 Jul 31 19:13 svr_6261.trc ... -rw-r----- 1 oracle oinstall 3916 Jul 31 19:15 svr_6295.trc -rw-r----- 1 oracle oinstall 4300 Jul 31 19:14 svr_6297.trc -rw-r----- 1 oracle oinstall 3281 Jul 31 19:15 svr_6329.trc ====================== Path to Documentation ====================== Database Net Services Reference =>5. Parameters for the sqlnet.ora File --> sqlnet.ora =>6. Local Naming Parameters in the tnsnames.ora File --> tnsnames.ora =>7. Oracle Net Listener Parameters in the listener.ora File --> listener.ora =>8. Oracle Connection Manager Parameters (cman.ora) --> cman.ora Database Net Services Administrator’s Guide -> 9 Configuring and Administering Oracle Net Listener Database Net Services Administrator’s Guide -> 10 Configuring Oracle Connection Manager With Oracle Enterprise Manager Cloud Control Database Net Services Administrator’s Guide -> 9 Configuring and Administering Oracle Net Listener -> Configuring Listening Protocol Addresses Using Oracle Enterprise Manager Cloud Control
No responses yet