{"id":4473,"date":"2020-03-06T22:56:54","date_gmt":"2020-03-07T01:56:54","guid":{"rendered":"https:\/\/www.dbarj.com.br\/2020\/03\/weird-ora-01031-insufficient-privileges-on-dgmgrl\/"},"modified":"2020-03-07T14:52:06","modified_gmt":"2020-03-07T17:52:06","slug":"weird-ora-01031-insufficient-privileges-on-dgmgrl","status":"publish","type":"post","link":"https:\/\/www.dbarj.com.br\/pt-br\/2020\/03\/weird-ora-01031-insufficient-privileges-on-dgmgrl\/","title":{"rendered":"Weird ORA-01031: insufficient privileges on DGMGRL"},"content":{"rendered":"<p>Those days, my production dataguard system suddenly started to receive a weird <strong>ORA-01031: insufficient privileges<\/strong> on DGMGRL for any task like &#8220;<em>show configuration<\/em>&#8221; or &#8220;<em>edit database set state<\/em>&#8220;. More weird is that nothing (apparently) was changed on the system. Another troubleshooting saga in my DBA life was about to start..<\/p>\n<p>My broker was configured with user SYS.. how is it possible for user SYS to have a missing privilege?! If the error was related with invalid password, this would be much easier to analyze.<\/p>\n<p>As sometimes DGMGRL is a little bit buggy, first thing I&#8217;ve tried was to remove the configuration and add it back. The remove only happened in one side.. so I had to drop manually the broker files from the standby side to create a new configuration. When I executed the <strong>ADD DATABASE<\/strong> command, I received the same boring error: <strong>ORA-01031: insufficient privileges<\/strong>. Time to double check all the dataguard setup.<\/p>\n<p>So I started my check list:<\/p>\n<ul>\n<li>Check if my password file and database SYS user password were aligned. \u2705<\/li>\n<li>Check if all password files were aligned across primary instances and standby. \u2705<\/li>\n<li>Check if REMOTE_LOGIN_PASSWORDFILE was correct. \u2705<\/li>\n<li>Check if <span class=\"st\"><em>AUTHENTICATION_SERVICES<\/em><\/span> on sqlnet.ora was not blocking password file authentication. \u2705<\/li>\n<li>If the static listeners were up and working. \u2705<\/li>\n<li>If the TNS file was aligned across primary and standby instances. \u2705<\/li>\n<li>Tested if SYS connection using @TNS alias worked using sqlplus to primary and standby. \u2705<\/li>\n<li>Tested if SYS connection using @TNS alias worked using dgmgrl to primary and standby. \u2705<\/li>\n<\/ul>\n<p>And everything was working but the ADD DATABASE command returned this damn boring &#8220;<strong>ORA-01031: insufficient privileges<\/strong>&#8220;. Again: it was not an authentication problem but a privilege problem with SYS!!<\/p>\n<p>I&#8217;ve decided to open a SR and meanwhile I started debugging DGMGRL. Running the ADD DATABASE with &#8220;<strong>dgmgrl -debug<\/strong>&#8221; didn&#8217;t give me any info at all.. so next step is to enable trace on the database side.<\/p>\n<p>I&#8217;ve turned on event 1031 on all my instances on both primary and standby:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">SQL&gt; alter system set events '1031 trace name errorstack level 10';<\/pre>\n<p>However, the ADD COMMAND, even triggering the ORA-01031 error, was not generating anything on the traces! I&#8217;ve turned it off to avoid excessive trace logs on the production daily operation:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">SQL&gt; alter system set events '1031 trace name context off';<\/pre>\n<p>A teammate also told me to check if the password on pwfile was case sensitive while insensitive withing the database. That was a good shoot.. my SEC_CASE_SENSITIVE_LOGON was set to FALSE and my SYS password had a mix of upper and lower chars. My pwfile was <span style=\"text-decoration: underline;\">not<\/span> created with ignorecase=y.. could it be the reason? Unfortunately, no.. I&#8217;ve changed it and realigned the pwfile but the error persisted.<\/p>\n<p>Finally, after +6 hours on the issue, another friend told me about this Doc ID:<\/p>\n<table id=\"kmPgTpl:r1:0:tl5\" border=\"0\" summary=\"\" width=\"100%\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr id=\"kmPgTpl:r1:0:rl12\">\n<td id=\"kmPgTpl:r1:0:cf49\" align=\"left\"><a href=\"https:\/\/support.oracle.com\/epmos\/faces\/DocumentDisplay?id=730067.1\" target=\"_blank\" rel=\"noopener noreferrer\"><span id=\"kmPgTpl:r1:0:ol22\" class=\"xq\"><label>Troubleshooting ORA-1031 or ORA-1017 Errors When Connecting As SYSDBA (Doc ID 730067.1)<\/label><\/span><\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Basically this note points some checks I&#8217;ve already tested, like if oracle account is within dba group, etc.. plus a very specific item that surprised me:<\/p>\n<blockquote>\n<table style=\"border-collapse: collapse; width: 100%;\">\n<tbody>\n<tr>\n<td style=\"width: 100%;\"><span style=\"font-size: 14pt;\"><strong>NSCD issues<\/strong><\/span><\/p>\n<p><span style=\"font-size: 14pt;\">Intermittent ora-1031 errors for sysdba OS authentication can occur because of the nscd service that caches information from the \/etc\/passwd and \/etc\/group as configured in <span class=\"sBugInternalContent\">\/etc\/nscd.conf<\/span>, if stopping this service makes the problem go away then please refer to the OS vendor.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/blockquote>\n<p>And voila! After a <strong><em>service nscd stop\/start<\/em><\/strong>, the ADD COMMAND succeeded. I could have never imagined this service would impact the SYS privileges.. meanwhile, oracle support was still asking in my sev1 ticket to check the archive gaps between primary and standby&#8230;<\/p>\n<b>Have you enjoyed? Please leave a comment or give a \ud83d\udc4d!<\/b>\n<div class='watch-action'><div class='watch-position align-left'><div class='action-like'><a class='lbg-style2 like-4473 jlk' href='javascript:void(0)' data-task='like' data-post_id='4473' data-nonce='de4404f630' rel='nofollow'><img class='wti-pixel' src='https:\/\/www.dbarj.com.br\/wp-content\/plugins\/wti-like-post\/images\/pixel.gif' title='Like' \/><span class='lc-4473 lc'>+1<\/span><\/a><\/div><\/div> <div class='status-4473 status align-left'><\/div><\/div><div class='wti-clear'><\/div>","protected":false},"excerpt":{"rendered":"<p>Those days, my production dataguard system suddenly started to receive a weird ORA-01031: insufficient privileges on DGMGRL for any task like &#8220;show configuration&#8221; or &#8220;edit database set state&#8220;. More weird is that nothing (apparently) was changed on the system. Another troubleshooting saga in my DBA life was about to start.. My broker was configured with &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"https:\/\/www.dbarj.com.br\/pt-br\/2020\/03\/weird-ora-01031-insufficient-privileges-on-dgmgrl\/\">Continue lendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29],"tags":[],"class_list":["post-4473","post","type-post","status-publish","format-standard","hentry","category-data-guard","item-wrap"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Weird ORA-01031: insufficient privileges on DGMGRL - DBA - Rodrigo Jorge - Oracle Tips and Guides<\/title>\n<meta name=\"description\" content=\"How to correct your dataguard when DGMGRL shows ORA-01031: insufficient privileges for any task like &quot;show configuration&quot; or &quot;edit database set state&quot;.\" \/>\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.dbarj.com.br\/pt-br\/2020\/03\/weird-ora-01031-insufficient-privileges-on-dgmgrl\/\" \/>\n<meta name=\"twitter:label1\" content=\"Escrito por\" \/>\n\t<meta name=\"twitter:data1\" content=\"DBA RJ\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. tempo de leitura\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutos\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/2020\\\/03\\\/weird-ora-01031-insufficient-privileges-on-dgmgrl\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/2020\\\/03\\\/weird-ora-01031-insufficient-privileges-on-dgmgrl\\\/\"},\"author\":{\"name\":\"DBA RJ\",\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/#\\\/schema\\\/person\\\/28a44ca3a6633fe4156ad1ea209d40a9\"},\"headline\":\"Weird ORA-01031: insufficient privileges on DGMGRL\",\"datePublished\":\"2020-03-07T01:56:54+00:00\",\"dateModified\":\"2020-03-07T17:52:06+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/2020\\\/03\\\/weird-ora-01031-insufficient-privileges-on-dgmgrl\\\/\"},\"wordCount\":591,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/#\\\/schema\\\/person\\\/28a44ca3a6633fe4156ad1ea209d40a9\"},\"articleSection\":[\"Data Guard\"],\"inLanguage\":\"pt-BR\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/2020\\\/03\\\/weird-ora-01031-insufficient-privileges-on-dgmgrl\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/2020\\\/03\\\/weird-ora-01031-insufficient-privileges-on-dgmgrl\\\/\",\"url\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/2020\\\/03\\\/weird-ora-01031-insufficient-privileges-on-dgmgrl\\\/\",\"name\":\"Weird ORA-01031: insufficient privileges on DGMGRL - DBA - Rodrigo Jorge - Oracle Tips and Guides\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/#website\"},\"datePublished\":\"2020-03-07T01:56:54+00:00\",\"dateModified\":\"2020-03-07T17:52:06+00:00\",\"description\":\"How to correct your dataguard when DGMGRL shows ORA-01031: insufficient privileges for any task like \\\"show configuration\\\" or \\\"edit database set state\\\".\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/2020\\\/03\\\/weird-ora-01031-insufficient-privileges-on-dgmgrl\\\/#breadcrumb\"},\"inLanguage\":\"pt-BR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/2020\\\/03\\\/weird-ora-01031-insufficient-privileges-on-dgmgrl\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/2020\\\/03\\\/weird-ora-01031-insufficient-privileges-on-dgmgrl\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Weird ORA-01031: insufficient privileges on DGMGRL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/#website\",\"url\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/\",\"name\":\"DBA - Rodrigo Jorge - Oracle Tips and Guides\",\"description\":\"Blog about Databases, Security and High Availability\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/#\\\/schema\\\/person\\\/28a44ca3a6633fe4156ad1ea209d40a9\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"pt-BR\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/#\\\/schema\\\/person\\\/28a44ca3a6633fe4156ad1ea209d40a9\",\"name\":\"DBA RJ\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"pt-BR\",\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/RodrigoJorgePOUG19.png\",\"url\":\"https:\\\/\\\/www.dbarj.com.br\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/RodrigoJorgePOUG19.png\",\"contentUrl\":\"https:\\\/\\\/www.dbarj.com.br\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/RodrigoJorgePOUG19.png\",\"width\":712,\"height\":712,\"caption\":\"DBA RJ\"},\"logo\":{\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/RodrigoJorgePOUG19.png\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Weird ORA-01031: insufficient privileges on DGMGRL - DBA - Rodrigo Jorge - Oracle Tips and Guides","description":"How to correct your dataguard when DGMGRL shows ORA-01031: insufficient privileges for any task like \"show configuration\" or \"edit database set state\".","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.dbarj.com.br\/pt-br\/2020\/03\/weird-ora-01031-insufficient-privileges-on-dgmgrl\/","twitter_misc":{"Escrito por":"DBA RJ","Est. tempo de leitura":"3 minutos"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbarj.com.br\/pt-br\/2020\/03\/weird-ora-01031-insufficient-privileges-on-dgmgrl\/#article","isPartOf":{"@id":"https:\/\/www.dbarj.com.br\/pt-br\/2020\/03\/weird-ora-01031-insufficient-privileges-on-dgmgrl\/"},"author":{"name":"DBA RJ","@id":"https:\/\/www.dbarj.com.br\/pt-br\/#\/schema\/person\/28a44ca3a6633fe4156ad1ea209d40a9"},"headline":"Weird ORA-01031: insufficient privileges on DGMGRL","datePublished":"2020-03-07T01:56:54+00:00","dateModified":"2020-03-07T17:52:06+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbarj.com.br\/pt-br\/2020\/03\/weird-ora-01031-insufficient-privileges-on-dgmgrl\/"},"wordCount":591,"commentCount":0,"publisher":{"@id":"https:\/\/www.dbarj.com.br\/pt-br\/#\/schema\/person\/28a44ca3a6633fe4156ad1ea209d40a9"},"articleSection":["Data Guard"],"inLanguage":"pt-BR","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbarj.com.br\/pt-br\/2020\/03\/weird-ora-01031-insufficient-privileges-on-dgmgrl\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbarj.com.br\/pt-br\/2020\/03\/weird-ora-01031-insufficient-privileges-on-dgmgrl\/","url":"https:\/\/www.dbarj.com.br\/pt-br\/2020\/03\/weird-ora-01031-insufficient-privileges-on-dgmgrl\/","name":"Weird ORA-01031: insufficient privileges on DGMGRL - DBA - Rodrigo Jorge - Oracle Tips and Guides","isPartOf":{"@id":"https:\/\/www.dbarj.com.br\/pt-br\/#website"},"datePublished":"2020-03-07T01:56:54+00:00","dateModified":"2020-03-07T17:52:06+00:00","description":"How to correct your dataguard when DGMGRL shows ORA-01031: insufficient privileges for any task like \"show configuration\" or \"edit database set state\".","breadcrumb":{"@id":"https:\/\/www.dbarj.com.br\/pt-br\/2020\/03\/weird-ora-01031-insufficient-privileges-on-dgmgrl\/#breadcrumb"},"inLanguage":"pt-BR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbarj.com.br\/pt-br\/2020\/03\/weird-ora-01031-insufficient-privileges-on-dgmgrl\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbarj.com.br\/pt-br\/2020\/03\/weird-ora-01031-insufficient-privileges-on-dgmgrl\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.dbarj.com.br\/pt-br\/"},{"@type":"ListItem","position":2,"name":"Weird ORA-01031: insufficient privileges on DGMGRL"}]},{"@type":"WebSite","@id":"https:\/\/www.dbarj.com.br\/pt-br\/#website","url":"https:\/\/www.dbarj.com.br\/pt-br\/","name":"DBA - Rodrigo Jorge - Oracle Tips and Guides","description":"Blog about Databases, Security and High Availability","publisher":{"@id":"https:\/\/www.dbarj.com.br\/pt-br\/#\/schema\/person\/28a44ca3a6633fe4156ad1ea209d40a9"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbarj.com.br\/pt-br\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"pt-BR"},{"@type":["Person","Organization"],"@id":"https:\/\/www.dbarj.com.br\/pt-br\/#\/schema\/person\/28a44ca3a6633fe4156ad1ea209d40a9","name":"DBA RJ","image":{"@type":"ImageObject","inLanguage":"pt-BR","@id":"https:\/\/www.dbarj.com.br\/wp-content\/uploads\/2019\/09\/RodrigoJorgePOUG19.png","url":"https:\/\/www.dbarj.com.br\/wp-content\/uploads\/2019\/09\/RodrigoJorgePOUG19.png","contentUrl":"https:\/\/www.dbarj.com.br\/wp-content\/uploads\/2019\/09\/RodrigoJorgePOUG19.png","width":712,"height":712,"caption":"DBA RJ"},"logo":{"@id":"https:\/\/www.dbarj.com.br\/wp-content\/uploads\/2019\/09\/RodrigoJorgePOUG19.png"}}]}},"_links":{"self":[{"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/posts\/4473","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/comments?post=4473"}],"version-history":[{"count":4,"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/posts\/4473\/revisions"}],"predecessor-version":[{"id":4482,"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/posts\/4473\/revisions\/4482"}],"wp:attachment":[{"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/media?parent=4473"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/categories?post=4473"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/tags?post=4473"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}