Set up network tracing is very important when some client complains "I'm having troubles when trying to connect in the DB and the application logs are inaccessible". It's also important for OCM if we need to find the RC of some network problem.
So, how to enable it? Probably touching the network configuration files listener.ora and sqlnet.ora.
Question: Do I know by heart the parameter name?
Answer: No, but I know by heart how to retrieve it's name in the Official Docs in less than 15 seconds.
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
Path to Documentation:
Database Administration -> Net Services Reference
Click here to go back to the Main OCM 11g Preparation page.
Have you enjoyed? Please leave a comment or give a 👍!
4 comments
Skip to comment form
Hey buddy, hope you are doing good..
I am 5 years experience in Oracle DBA 11g and attained OCP and RAC Expert 11g.
Planned to become OCM by this year end. Following your blogs as reference for my preparation, thank you for the information.
But I am quite nervous to complete OCM exam.
Author
Hi Udayakumar,
I'm happy this blog is helping you! I was extremely nervous during my OCM exam, just relax and prepare yourself. Prepare to be very fast in all the topics and I'm sure you'll pass.
Let me know when this happens!
Regards,
RJ
Worth mentioning if you set tracing in listener file you need to bounce listener.
Be careful when you set tracng on non-deafult listener. TRACE_LEVEL_ will need to be different.
Also, in documentation, I couldnt find info on the location of the trace files in case you use ADR locations. And it goes as the following:
For listener.ora:
----------------
ADR_BASE_LISTENER = /u01/app/oracle
TRACE_LEVEL_LISTENER = SUPPORT
Listener trace file location:
$ADR_BASE/diag/tnslsnr//listener/trace/ora__.trc
You can check it in lsnrctl status:
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 14-APR-2016 16:44:02
Uptime 0 days 3 hr. 7 min. 24 sec
Trace Level support
Security ON: Password or Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/dziunia/listener/alert/log.xml
Listener Trace File /u01/app/oracle/diag/tnslsnr/dziunia/listener/trace/ora_13825_140629499909952.trc
or
LSNRCTL> show trc_file
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dziunia.localdomain)(PORT=1521)))
LISTENER parameter "trc_file" set to ora_13825_140629499909952.trc
For the profile (sqlnet.ora):
-------------------------------
TRACE_LEVEL_CLIENT = ADMIN
TRACE_LEVEL_SERVER = USER
ADR_BASE = /u01/app/oracle
Profile trace file location (for the server):
$ADR_BASE/diag/rdbms///trace
Example:
SQL> select * from v$diag_info where name like '%Diag Trace%'
INST_ID NAME VALUE
---------- ---------------------------------------------------------------- --------------------------------------------------
1 Diag Trace /u01/app/oracle/diag/rdbms/baza2/baza2/trace
example:
/u01/app/oracle/diag/rdbms/baza2/baza2/trace/baza2_ora_29181.trc
Profile trace file location (for the client) - set it only on CLIENT computer. In ths case client is on the server. Just for illustrative purposes:
$ADR_BASE/oradiag_oracle/diag/clients/user_oracle/host_489049234_80/trace/ora_29180_140584486827840.trc
Author
Yes, good point!
Thanks,
RJ