{"id":286,"date":"2017-10-15T16:35:20","date_gmt":"2017-10-15T16:35:20","guid":{"rendered":"https:\/\/www.spotonoracle.com\/?p=286"},"modified":"2017-10-15T16:51:18","modified_gmt":"2017-10-15T16:51:18","slug":"are-you-fishing-or-catching-server-side-sqlnet-tracing-for-specific-clients","status":"publish","type":"post","link":"https:\/\/www.spotonoracle.com\/?p=286","title":{"rendered":"Are you fishing or catching? – Server-side SQL*Net tracing for specific clients"},"content":{"rendered":"

SQL*Net tracing on the database server is all or nothing (sqlnet.ora):
\nTRACE_LEVEL_SERVER=support<\/p>\n

On a busy system this quickly gets out of hand. More often than not I find myself in need of enabling SQL*Net trace on the server for a particular database client.
\nThe first step is to setup a separate TNS_ADMIN location that we want the sever process for our client connections to pick up:<\/p>\n

\r\n$ mkdir \/u01\/app\/oracle\/network\/admin-trc\r\n$ copy \/u01\/app\/oracle\/network\/admin\/sqlnet.ora \/u01\/app\/oracle\/network\/admin-trc\/.\r\n<\/pre>\n
Then enable SQL*Net tracing in the new TNS_ADMIN location:<\/p>\n
vi \/u01\/app\/oracle\/network\/admin-trc\/sqlnet.ora<\/p>\n
\r\n...\r\nTRACE_LEVEL_SERVER=support\r\n\r\n<\/pre>\n
If you can modify the client’s connection string things are easy. All we need is a separate service on the existing listener that the client then connects to:
\nvi \/u01\/app\/oracle\/network\/admin\/listener.ora<\/p>\n
\r\nSID_LIST_LISTENER =\r\n  (SID_LIST =\r\n    (SID_DESC =\r\n      (GLOBAL_DBNAME = pdbdev1_trc.localdomain)\r\n      (ORACLE_HOME = \/u01\/app\/oracle\/product\/ora12201)\r\n      (SID_NAME = DEV1)\r\n      (ENVS="TNS_ADMIN=\/u01\/app\/oracle\/network\/admin-trc")\r\n    )\r\n  )\r\n<\/pre>\n
Line 4: Defines our new service name
\nLine 7: This makes server processes for connections through that service read our sqlnet.ora in the “admin-trc” directory and, therefore enable SQL*Net tracing.<\/p>\n
Now, change the client’s connection string to use “pdbdev_trc” service and we’re done.
\nBut, what if for some reason you cannot easily change the client’s connect string. That’s when I like to resort to port-forwarding using firewalld.<\/p>\n
First<\/strong>, we spin up an additional listener on another port. Let’s assume our original listener runs on port 1521.
\nvi \/u01\/app\/oracle\/network\/admin\/listener.ora<\/p>\n
\r\nLISTENER_TRC =\r\n  (DESCRIPTION_LIST =\r\n    (DESCRIPTION =\r\n      (ADDRESS = (PROTOCOL = TCP)(HOST = ol7ora122dev1.localdomain)(PORT = 1522))\r\n    )\r\n  )\r\n\r\nSID_LIST_LISTENER_TRC =\r\n  (SID_LIST =\r\n    (SID_DESC =\r\n      (GLOBAL_DBNAME = pdbdev1.localdomain)\r\n      (ORACLE_HOME = \/u01\/app\/oracle\/product\/ora12201)\r\n      (SID_NAME = DEV1)\r\n      (ENVS="TNS_ADMIN=\/u01\/app\/oracle\/network\/admin-trc")\r\n    )\r\n  )\r\n<\/pre>\n
Line  4: listening on new port
\nLine 11: the service name is the same as on the original listener (where the client connects to)
\nLine 14: Again, this sets the TNS_ADMIN location for the server process to pick up our sqlnet.ora with SQL*Net tracing enabled.<\/p>\n
Secondly<\/strong>, add the port forwarding rule:<\/p>\n
\r\nfirewall-cmd --zone=oradb-server \\\r\n  --add-rich-rule='rule family="ipv4" source address="<client-IP>" forward-port port=1521 protocol=tcp to-port=1522'\r\n<\/pre>\n
Now, every connection request from the client we want to have a server-side SQL*Net trace from gets forwarded to our new listener and picks up our modified sqlnet.ora.
\nObviously, this is assuming you have firewalld configured and running already. If not, await my next blog post on basic firewalld configuration for Oracle database servers.<\/p>\n
Enjoy!<\/p>\n
P.s. when you have all the traces you need you can remove the port forwading rule like this:<\/p>\n
\r\nfirewall-cmd --zone=oradb-server \\\r\n  --remove-rich-rule='rule family="ipv4" source address="<client-IP>" forward-port port=1521 protocol=tcp to-port=1522'\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
SQL*Net tracing on the database server is all or nothing (sqlnet.ora): TRACE_LEVEL_SERVER=support On a busy system this quickly gets out of hand. More often than not I find myself in need of enabling SQL*Net trace on the server for a particular database client. The first step is to setup a separate TNS_ADMIN location that we […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,16],"tags":[],"class_list":["post-286","post","type-post","status-publish","format-standard","hentry","category-general","category-linux"],"_links":{"self":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/286","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=286"}],"version-history":[{"count":3,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/286\/revisions"}],"predecessor-version":[{"id":289,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/286\/revisions\/289"}],"wp:attachment":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=286"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=286"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=286"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}