{"id":1273,"date":"2013-07-25T08:00:26","date_gmt":"2013-07-25T06:00:26","guid":{"rendered":"http:\/\/www.oradba.ch\/?p=1273"},"modified":"2013-07-29T12:31:46","modified_gmt":"2013-07-29T10:31:46","slug":"query-alert-log-from-sqlplus","status":"publish","type":"post","link":"https:\/\/www.oradba.ch\/wordpress\/2013\/07\/query-alert-log-from-sqlplus\/","title":{"rendered":"Query alert log from sqlplus"},"content":{"rendered":"<p>It is not really a novum that you can directly query the alertlog from SQLPlus. <a href=\"http:\/\/blog.tanelpoder.com\/2009\/03\/21\/oracle-11g-reading-alert-log-via-sql\/\">Tanel Poder<\/a> and others already have discussed this a while ago. Somehow I can never remember the name of the X$ view when I need it. So it is time to sum up the information a little bit.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">\r\nSQL&gt; desc X$DBGALERTEXT\r\n    Name                         Null?    Type\r\n    ---------------------------- -------- ---------------------------\r\n 1  ADDR                                  RAW(8)\r\n 2  INDX                                  NUMBER\r\n 3  INST_ID                               NUMBER\r\n 4  CON_ID                                NUMBER\r\n 5  ORIGINATING_TIMESTAMP                 TIMESTAMP(3) WITH TIME ZONE\r\n 6  NORMALIZED_TIMESTAMP                  TIMESTAMP(3) WITH TIME ZONE\r\n 7  ORGANIZATION_ID                       VARCHAR2(64)\r\n 8  COMPONENT_ID                          VARCHAR2(64)\r\n 9  HOST_ID                               VARCHAR2(64)\r\n10  HOST_ADDRESS                          VARCHAR2(46)\r\n11  MESSAGE_TYPE                          NUMBER\r\n12  MESSAGE_LEVEL                         NUMBER\r\n13  MESSAGE_ID                            VARCHAR2(64)\r\n14  MESSAGE_GROUP                         VARCHAR2(64)\r\n15  CLIENT_ID                             VARCHAR2(64)\r\n16  MODULE_ID                             VARCHAR2(64)\r\n17  PROCESS_ID                            VARCHAR2(32)\r\n18  THREAD_ID                             VARCHAR2(64)\r\n19  USER_ID                               VARCHAR2(64)\r\n20  INSTANCE_ID                           VARCHAR2(64)\r\n21  DETAILED_LOCATION                     VARCHAR2(160)\r\n22  PROBLEM_KEY                           VARCHAR2(550)\r\n23  UPSTREAM_COMP_ID                      VARCHAR2(100)\r\n24  DOWNSTREAM_COMP_ID                    VARCHAR2(100)\r\n25  EXECUTION_CONTEXT_ID                  VARCHAR2(100)\r\n26  EXECUTION_CONTEXT_SEQUENCE            NUMBER\r\n27  ERROR_INSTANCE_ID                     NUMBER\r\n28  ERROR_INSTANCE_SEQUENCE               NUMBER\r\n29  VERSION                               NUMBER\r\n30  MESSAGE_TEXT                          VARCHAR2(2048)\r\n31  MESSAGE_ARGUMENTS                     VARCHAR2(512)\r\n32  SUPPLEMENTAL_ATTRIBUTES               VARCHAR2(512)\r\n33  SUPPLEMENTAL_DETAILS                  VARCHAR2(4000)\r\n34  PARTITION                             NUMBER\r\n35  RECORD_ID                             NUMBER\r\n<\/pre>\n<p>A simple query to get the alert log messages and timestamp would look like.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">\r\nset linesize 160 pagesize 200\r\ncol RECORD_ID for 9999999 head ID\r\ncol ORIGINATING_TIMESTAMP for a20 head Date\r\ncol MESSAGE_TEXT for a120 head Message\r\n\r\nselect \r\n    record_id,\r\n    to_char(originating_timestamp,&#039;DD.MM.YYYY HH24:MI:SS&#039;),\r\n    message_text \r\nfrom \r\n    x$dbgalertext;\r\n<\/pre>\n<p>For daily use I&#8217;ve put together two scripts. <\/p>\n<ul>\n<li><img data-recalc-dims=\"1\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/plugins\/wp-downloadmanager\/images\/ext\/unknown.gif?w=625&#038;ssl=1\" alt=\"\" title=\"\" style=\"vertical-align: middle;\" \/>&nbsp;<a href=\"https:\/\/www.oradba.ch\/wordpress\/download\/tal.sql\">tal.sql<\/a> list all or some alert log messages. Messages will be filtered by the parameter <\/li>\n<li><img data-recalc-dims=\"1\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/plugins\/wp-downloadmanager\/images\/ext\/unknown.gif?w=625&#038;ssl=1\" alt=\"\" title=\"\" style=\"vertical-align: middle;\" \/>&nbsp;<a href=\"https:\/\/www.oradba.ch\/wordpress\/download\/taln.sql\">taln.sql<\/a> list the last n numbers of rows in an alert log.<\/li>\n<\/ul>\n<h3>Write into the alertlog<\/h3>\n<p>The procedure kdswrt in dbms_system package allows us to write own messages in the alert log \/ trace files or both. It receives two parameters:<\/p>\n<ul>\n<li>A number that indicates where do we want to write our message\n<ol>\n<li>Writing to a TRACE file<\/li>\n<li>Writing to the Alert.log file<\/li>\n<li>Writing to both of them<\/li>\n<\/ol>\n<\/li>\n<li>A text string (the message itself).<\/li>\n<\/ul>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">\r\n\r\nexec dbms_system.ksdwrt(2, &#039;ORA-00042: Test message in alert log.&#039;);\r\n\r\n<\/pre>\n<h3>Query the Alertlog<\/h3>\n<p>List the last 10 lines in the alert log.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">\r\nSQL&gt; @taln 10\r\n\r\n\r\nSQL&gt; @taln 10\r\n\r\n      ID Date                 Message\r\n-------- -------------------- ----------------------------------------------------------------------\r\n    4333 23.07.2013 22:00:47  Thread 1 advanced to log sequence 94 (LGWR switch)\r\n    4334 23.07.2013 22:00:47    Current log# 1 seq# 94 mem# 0: \/u00\/oradata\/TDB01\/redog1m1TDB01.dbf\r\n    4335 23.07.2013 22:00:47    Current log# 1 seq# 94 mem# 1: \/u01\/oradata\/TDB01\/redog1m2TDB01.dbf\r\n    4336 23.07.2013 22:00:47  Archived Log entry 111 added for thread 1 sequence 93 ID 0xa3d43dfa...\r\n    4337 24.07.2013 02:00:00  Closing scheduler window\r\n    4338 24.07.2013 02:00:00  Closing Resource Manager plan via scheduler window\r\n    4339 24.07.2013 02:00:00  Clearing Resource Manager plan via parameter\r\n    4340 24.07.2013 03:38:21  VKTM detected a time drift. Please check trace file for more details.\r\n    4341 24.07.2013 09:18:38  VKTM detected a time drift. Please check trace file for more details.\r\n    4342 24.07.2013 14:50:05  ORA-00042: Test Message in alert log\r\n\r\n10 rows selected.\r\n<\/pre>\n<p>Query the alert log string ORA-00042.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">\r\n\r\nSQL&gt; @tal ORA-00042\r\n\r\n      ID Date                 Message\r\n-------- -------------------- -------------------------------------------\r\n    4342 24.07.2013 14:50:05  ORA-00042: Test Message in alert log\r\n\r\n\r\nFilter on alert log message =&gt; ORA-00042\r\n\r\n<\/pre>\n<h3>Other fixed tables<\/h3>\n<p>There are bunch of other X$ Fixed Tables. At lease the following are somehow related to the ADR<\/p>\n<ul>\n<li><em>X$DBGDIREXT<\/em> list all file and directory names under diagnostic_dest\/diag directory. Will be quite a lot on a shared DB server<\/li>\n<li><em>X$DBGRICX<\/em> list of ADR Incidents<\/li>\n<\/ul>\n<h3>References<\/h3>\n<p>Some links related to this post.<\/p>\n<ul>\n<li><img data-recalc-dims=\"1\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/plugins\/wp-downloadmanager\/images\/ext\/unknown.gif?w=625&#038;ssl=1\" alt=\"\" title=\"\" style=\"vertical-align: middle;\" \/>&nbsp;<a href=\"https:\/\/www.oradba.ch\/wordpress\/download\/tal.sql\">tal.sql<\/a> list all or some alert log messages. Messages will be filtered by the parameter <\/li>\n<li><img data-recalc-dims=\"1\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/plugins\/wp-downloadmanager\/images\/ext\/unknown.gif?w=625&#038;ssl=1\" alt=\"\" title=\"\" style=\"vertical-align: middle;\" \/>&nbsp;<a href=\"https:\/\/www.oradba.ch\/wordpress\/download\/taln.sql\">taln.sql<\/a> list the last n numbers of rows in an alert log.<\/li>\n<li>How To Edit, Read, and Query the Alert.Log [<em><a href=\"https:\/\/support.oracle.com\/epmos\/faces\/DocumentDisplay?id=1072547.1\">1072547.1<\/a><\/em>]<\/li>\n<li>How To Test The Generic Alert Log Error Metric on 10g [<em><a href=\"https:\/\/support.oracle.com\/epmos\/faces\/DocumentDisplay?id=850320.1\">850320.1<\/a><\/em>]<\/li>\n<li>Tanel Poder <a href=\"http:\/\/blog.tanelpoder.com\/2009\/03\/21\/oracle-11g-reading-alert-log-via-sql\/\" title=\"Oracle 11g: Reading alert log via SQL\" target=\"_blank\">Oracle 11g: Reading alert log via SQL<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>It is not really a novum that you can directly query the alertlog from SQLPlus. Tanel Poder and others already have discussed this a while ago. Somehow I can never remember the name of the X$ view when I need it. So it is time to sum up the information a little bit. SQL&gt; desc [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"Query alert log from sqlplus http:\/\/wp.me\/p1aErb-kx #trivadis","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[8,83,9,5],"tags":[93,28,18,90,94,91,92],"class_list":["post-1273","post","type-post","status-publish","format-standard","hentry","category-11gr2","category-12cr1","category-internals","category-oracle-database","tag-fixed-table-views","tag-script","tag-trivadiscontent","tag-troubleshooting","tag-xdbgalertext","tag-xdbgdirext","tag-xdbgricx"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p1aErb-kx","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":768,"url":"https:\/\/www.oradba.ch\/wordpress\/2012\/01\/oracle-hidden-init-ora-parameter\/","url_meta":{"origin":1273,"position":0},"title":"Oracle hidden init.ora parameter","author":"Stefan","date":"26. January 2012","format":false,"excerpt":"This post focuses on init.ora parameters. It is not really new topic, but rather a personal reference to some practical queries and scripts. If you are the customer, it's always handy when you can easily access your own queries.","rel":"","context":"In &quot;10gR2&quot;","block_context":{"text":"10gR2","link":"https:\/\/www.oradba.ch\/wordpress\/category\/oracle-database\/10gr2\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2031,"url":"https:\/\/www.oradba.ch\/wordpress\/2015\/09\/memory-leak-in-network-checksum-with-new-sha-2-functions\/","url_meta":{"origin":1273,"position":1},"title":"Memory Leak in Network Checksum with new SHA-2 Functions","author":"Stefan","date":"3. September 2015","format":false,"excerpt":"I've just stumbled over an issue with the new checksum algorithm introduced with Oracle 12c. It seams that in certain situation the new SHA-2 function cause a memory leak. A search on My Oracle Support revealed that there is a Bug on AIX. See Bug 19451972 MEMEORY LEAKS WITH SHA512,\u2026","rel":"","context":"In &quot;12cR1&quot;","block_context":{"text":"12cR1","link":"https:\/\/www.oradba.ch\/wordpress\/category\/oracle-database\/12cr1\/"},"img":{"alt_text":"MemoryLeak","src":"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/MemoryLeak-300x180.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":13000,"url":"https:\/\/www.oradba.ch\/wordpress\/2023\/03\/help-i-lost-my-brand-new-unified-audit-policy\/","url_meta":{"origin":1273,"position":2},"title":"Help I lost my brand new Unified Audit Policy?","author":"Stefan","date":"16. March 2023","format":false,"excerpt":"Just realized that under some conditions audit policies are not shown in AUDIT_UNIFIED_POLICIES. This blog post does provide a few information and a test case.","rel":"","context":"In &quot;Bug&quot;","block_context":{"text":"Bug","link":"https:\/\/www.oradba.ch\/wordpress\/category\/bug\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1389,"url":"https:\/\/www.oradba.ch\/wordpress\/2013\/08\/oracle-database-12c-new-feature-last-login-time\/","url_meta":{"origin":1273,"position":3},"title":"Oracle Database 12c New Feature: Last Login Time","author":"Stefan","date":"22. August 2013","format":false,"excerpt":"As Markus Flechtner has already mentioned in his blog, Oracle has started to record the last login time. It is a small but very useful 12c security feature and operates independently of the database audit. Nevertheless, there are some restrictions. But let's start at the beginning\u2026 A simple example Ok,\u2026","rel":"","context":"In &quot;12cR1&quot;","block_context":{"text":"12cR1","link":"https:\/\/www.oradba.ch\/wordpress\/category\/oracle-database\/12cr1\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":13131,"url":"https:\/\/www.oradba.ch\/wordpress\/2023\/04\/get-oracle-database-23c-for-free-on-your-mac-m1\/","url_meta":{"origin":1273,"position":4},"title":"Get Oracle Database 23c for free on your Mac M1","author":"Stefan","date":"5. April 2023","format":false,"excerpt":"Oracle Database 23c Free - Developer Release is all over since Oracle released it yesterday. See the Official Oracle pages Oracle Database Free or the blog post by Gerald Venzl Introducing Oracle Database 23c Free \u2013 Developer Release. Connor McDonald even got a special delivery from Oracle. A few important\u2026","rel":"","context":"In &quot;23c&quot;","block_context":{"text":"23c","link":"https:\/\/www.oradba.ch\/wordpress\/category\/oracle-database\/23c\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/Screenshot-2023-04-05-at-11.26.07.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/Screenshot-2023-04-05-at-11.26.07.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/Screenshot-2023-04-05-at-11.26.07.png?resize=525%2C300&ssl=1 1.5x"},"classes":[]},{"id":13985,"url":"https:\/\/www.oradba.ch\/wordpress\/2023\/09\/oracle-sqlnet-encryption-why-is-it-needed-in-first-place\/","url_meta":{"origin":1273,"position":5},"title":"Oracle SQLNet Encryption, why is it needed in first place?","author":"Stefan","date":"22. September 2023","format":false,"excerpt":"I recently wrote a blog post about Oracle SQLNet TLS encryption and how easy it is to configure. See also Oracle SQLNet TLS configuration simplified. This was followed by a short discussion about whether a simple ALTER USER \u2026 IDENTIFIED BY is encrypted in a regular connection or not. So\u2026","rel":"","context":"In &quot;Good Practice&quot;","block_context":{"text":"Good Practice","link":"https:\/\/www.oradba.ch\/wordpress\/category\/good-practice\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/unencrypted_load_file.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/unencrypted_load_file.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/unencrypted_load_file.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/unencrypted_load_file.png?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/unencrypted_load_file.png?resize=1050%2C600&ssl=1 3x, https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/unencrypted_load_file.png?resize=1400%2C800&ssl=1 4x"},"classes":[]}],"_links":{"self":[{"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/posts\/1273","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/comments?post=1273"}],"version-history":[{"count":12,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/posts\/1273\/revisions"}],"predecessor-version":[{"id":1285,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/posts\/1273\/revisions\/1285"}],"wp:attachment":[{"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/media?parent=1273"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/categories?post=1273"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/tags?post=1273"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}