The listener log file contains a lot of very useful information, like the program which was used for the connection, the IP address where the connection is coming from, the OS user which was used on the client \u00a0and many many more.<\/p>\n
05-JUN-2017 12:36:19 * service_update * DBIT122 * 0\n05-JUN-2017 12:36:19 * (CONNECT_DATA=(SERVICE_NAME=DBIT122_SITE1_DGMGRL)(UR=A)(CID=(PROGRAM=sqlplus@dbidg01)(HOST=dbidg01)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.201)(PORT=42619)) * establish * DBIT122_SITE1_DGMGRL * 0\n05-JUN-2017 12:36:19 * (CONNECT_DATA=(SERVICE_NAME=DBIT122_SITE1_DGMGRL)(UR=A)(CID=(PROGRAM=sqlplus@dbidg01)(HOST=dbidg01)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.201)(PORT=42620)) * establish * DBIT122_SITE1_DGMGRL * 0<\/pre>\nHowever, it does not contain only successful information; it also shows when connections have been rejected because of TCP.VALIDNODE_CHECKING or any type of TNS errors. So, why not using it for auditing? And what about the performance overhead of listener logging.<\/p>\n
Let’s start first with the performance overhead. I am doing 4 types of tests here.<\/p>\n
1. Performance with no listener logging (100 connections)
\n2. Performance with a small listener.log (100 connections)
\n3. Performance with big listener.log, close to 4G (100 connections)
\n4. Performance with a full listener.log, exactly 4G (100 connections)<\/p>\n<\/p>\n
Test 1: Performance with no listener logging (100 connections)<\/h3>\n
oracle@dbidg01:\/home\/oracle\/ [DBIT122] time .\/performance_listener.sh\nSQLPlus count 1\nSQLPlus count 2\n...\n...\nSQLPlus count 99\nSQLPlus count 100\n\nreal 0m3.360s\nuser 0m1.065s\nsys 0m0.495s<\/pre>\nTest 2: Performance with a small listener.log (100 connections)<\/h3>\n
oracle@dbidg01:\/home\/oracle\/ [DBIT122] time .\/performance_listener.sh\nSQLPlus count 1\nSQLPlus count 2\n...\n...\nSQLPlus count 99\nSQLPlus count 100\n\nreal 0m3.401s\nuser 0m1.049s\nsys 0m0.511s<\/pre>\nTest 3: Performance with big listener.log, close to 4G (100 connections)<\/h3>\n
oracle@dbidg01:\/home\/oracle\/tmp\/ [DBIT122] time .\/performance_listener.sh\nSQLPlus count 1\nSQLPlus count 2\nSQLPlus count 3\n...\n...\nSQLPlus count 99\nSQLPlus count 100\n\nreal 0m3.766s\nuser 0m1.110s\nsys 0m0.522s<\/pre>\nTest 4: Performance with a full listener.log, exactly 4G (100 connections)<\/h3>\n
oracle@dbidg01:\/home\/oracle\/tmp\/ [DBIT122] time .\/performance_listener.sh\nSQLPlus count 1\nSQLPlus count 2\nSQLPlus count 3\n...\n...\nSQLPlus count 99\nSQLPlus count 100\n\nreal 0m3.430s\nuser 0m1.068s\nsys 0m0.501s<\/pre>\nAs you can see in the results, sqlplus connections without listener logging are the fastest one, and the bigger the file gets, the slower the connections are. But wait a second. What’s going on with test 4? As soon as the listener log is full, connections are faster again. The reason for test 4 being faster again, is that the listener is not logging anymore. As soon as it reaches the 4G limit, which is still the case with Oracle 12.2, the listener does not crash like in some older versions beforehand, but the logs are going to \/dev\/null. So, I will lose all my auditing information for those ones after the 4G limit was reached.<\/p>\n
How do we overcome this issue? The answer is to rotate the listener log. But how do we do it? We can’t just simply move the old listener log away and create an empty new one, because the file descriptor is still open and you would create a huge mess.<\/p>\n
We could stop the listener, rotate the listener log and start the listener again. A little better, but for the time the listener is stopped, no connection will be possible which is also not a good idea.<\/p>\n
From my point of view, the best solution is to stop listener logging online as soon as it hits 1G, rotate the listener log and start listener logging again, like in the following example:<\/p>\n
lsnrctl <<-EOF\nset current_listener $ListenerName\nset log_status off\nEOF\n\nmv ${ListenerLogFile} ${ListenerLogFile}.${Date}\n\nlsnrctl <<-EOF\nset current_listener $ListenerName\nset log_status on\nEOF<\/pre>\nNow you have the advantage, that the log rotation is an online operation, and you don’t create any mess with open file descriptors. And if you rotate the listener log before it reaches 1G, it is also less likely to run into performance issues.<\/p>\n
Conclusion<\/h3>\n
Take care of your listener log, so that it does not hit the 4G file size limit. You might lose very important information which will not be logged anymore. And do the listener log rotation correctly. \ud83d\ude09<\/p>\n","protected":false},"excerpt":{"rendered":"
By William Sescu The listener log file contains a lot of very useful information, like the program which was used for the connection, the IP address where the connection is coming from, the OS user which was used on the client \u00a0and many many more. 05-JUN-2017 12:36:19 * service_update * DBIT122 * 0 05-JUN-2017 12:36:19 […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198],"tags":[988],"type_dbi":[],"class_list":["post-10232","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","tag-oracle-12-2"],"acf":[],"yoast_head":"\n
Oracle 12.2 - How to rotate the 12.2 listener log (DIAG_ADR_ENABLED_LISTENER = OFF) - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-how-to-rotate-the-12-2-listener-log-diag_adr_enabled_listener-off\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle 12.2 - How to rotate the 12.2 listener log (DIAG_ADR_ENABLED_LISTENER = OFF)\" \/>\n<meta property=\"og:description\" content=\"By William Sescu The listener log file contains a lot of very useful information, like the program which was used for the connection, the IP address where the connection is coming from, the OS user which was used on the client \u00a0and many many more. 05-JUN-2017 12:36:19 * service_update * DBIT122 * 0 05-JUN-2017 12:36:19 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-how-to-rotate-the-12-2-listener-log-diag_adr_enabled_listener-off\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-06-05T09:49:13+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-how-to-rotate-the-12-2-listener-log-diag_adr_enabled_listener-off\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-how-to-rotate-the-12-2-listener-log-diag_adr_enabled_listener-off\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Oracle 12.2 – How to rotate the 12.2 listener log (DIAG_ADR_ENABLED_LISTENER = OFF)\",\"datePublished\":\"2017-06-05T09:49:13+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-how-to-rotate-the-12-2-listener-log-diag_adr_enabled_listener-off\/\"},\"wordCount\":503,\"commentCount\":0,\"keywords\":[\"Oracle 12.2\"],\"articleSection\":[\"Database Administration & Monitoring\",\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-how-to-rotate-the-12-2-listener-log-diag_adr_enabled_listener-off\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-how-to-rotate-the-12-2-listener-log-diag_adr_enabled_listener-off\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-how-to-rotate-the-12-2-listener-log-diag_adr_enabled_listener-off\/\",\"name\":\"Oracle 12.2 - How to rotate the 12.2 listener log (DIAG_ADR_ENABLED_LISTENER = OFF) - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2017-06-05T09:49:13+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-how-to-rotate-the-12-2-listener-log-diag_adr_enabled_listener-off\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-how-to-rotate-the-12-2-listener-log-diag_adr_enabled_listener-off\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-how-to-rotate-the-12-2-listener-log-diag_adr_enabled_listener-off\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle 12.2 – How to rotate the 12.2 listener log (DIAG_ADR_ENABLED_LISTENER = OFF)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Oracle 12.2 - How to rotate the 12.2 listener log (DIAG_ADR_ENABLED_LISTENER = OFF) - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-how-to-rotate-the-12-2-listener-log-diag_adr_enabled_listener-off\/","og_locale":"en_US","og_type":"article","og_title":"Oracle 12.2 - How to rotate the 12.2 listener log (DIAG_ADR_ENABLED_LISTENER = OFF)","og_description":"By William Sescu The listener log file contains a lot of very useful information, like the program which was used for the connection, the IP address where the connection is coming from, the OS user which was used on the client \u00a0and many many more. 05-JUN-2017 12:36:19 * service_update * DBIT122 * 0 05-JUN-2017 12:36:19 […]","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-how-to-rotate-the-12-2-listener-log-diag_adr_enabled_listener-off\/","og_site_name":"dbi Blog","article_published_time":"2017-06-05T09:49:13+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-how-to-rotate-the-12-2-listener-log-diag_adr_enabled_listener-off\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-how-to-rotate-the-12-2-listener-log-diag_adr_enabled_listener-off\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Oracle 12.2 – How to rotate the 12.2 listener log (DIAG_ADR_ENABLED_LISTENER = OFF)","datePublished":"2017-06-05T09:49:13+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-how-to-rotate-the-12-2-listener-log-diag_adr_enabled_listener-off\/"},"wordCount":503,"commentCount":0,"keywords":["Oracle 12.2"],"articleSection":["Database Administration & Monitoring","Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-12-2-how-to-rotate-the-12-2-listener-log-diag_adr_enabled_listener-off\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-how-to-rotate-the-12-2-listener-log-diag_adr_enabled_listener-off\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-how-to-rotate-the-12-2-listener-log-diag_adr_enabled_listener-off\/","name":"Oracle 12.2 - How to rotate the 12.2 listener log (DIAG_ADR_ENABLED_LISTENER = OFF) - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2017-06-05T09:49:13+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-how-to-rotate-the-12-2-listener-log-diag_adr_enabled_listener-off\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-12-2-how-to-rotate-the-12-2-listener-log-diag_adr_enabled_listener-off\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-how-to-rotate-the-12-2-listener-log-diag_adr_enabled_listener-off\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle 12.2 – How to rotate the 12.2 listener log (DIAG_ADR_ENABLED_LISTENER = OFF)"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10232","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=10232"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10232\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=10232"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=10232"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=10232"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=10232"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}