{"id":9754,"date":"2017-02-08T10:11:09","date_gmt":"2017-02-08T09:11:09","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one\/"},"modified":"2017-02-08T10:11:09","modified_gmt":"2017-02-08T09:11:09","slug":"oracle-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one\/","title":{"rendered":"Oracle 12c – RMAN list failure does not show any failure even if there is one"},"content":{"rendered":"

By William Sescu<\/h2>\n

Relying to much on the RMAN Data Recovery Advisor is not always the best idea. In a lot of situations, \u00a0it tells you the right things, however, sometimes it tells you not the optimal things, and sometimes, RMAN list failure does not show any failure at all, even if there is one.<\/p>\n

So … let’s simulate quickly a loss of a datafile during the normal runtime of the database. The result is a clear error message which says that the datafile 5 is missing.<\/p>\n

SQL> select count(*) from hr.employees;\nselect count(*) from hr.employees\n                        *\nERROR at line 1:\nORA-01116: error in opening database file 5\nORA-01110: data file 5: '\/u01\/oradata\/DBTEST1\/hrDBTEST01.dbf'\nORA-27041: unable to open file\nLinux-x86_64 Error: 2: No such file or directory\nAdditional information: 3<\/pre>\n
Of course, the error message is immediately reflected in the alert.log as well where it clearly says that Oracle in unable to open file number 5.<\/p>\n
Errors in file \/u00\/app\/oracle\/diag\/rdbms\/dbtest1\/DBTEST1\/trace\/DBTEST1_smon_17115.trc:\nORA-01116: error in opening database file 5\nORA-01110: data file 5: '\/u01\/oradata\/DBTEST1\/hrDBTEST01.dbf'\nORA-27041: unable to open file\nLinux-x86_64 Error: 2: No such file or directory<\/pre>\n
Only the RMAN Data Recovery advisor does not know what it’s going on.<\/p>\n
RMAN> list failure;\n\nusing target database control file instead of recovery catalog\nDatabase Role: PRIMARY\n\nno failures found that match specification<\/pre>\n
Of course, I could shutdown the DB, and then startup again which would trigger a Health Check, but shutting down an instance is not always so easy on production systems. Especially when only one datafile is missing, but all others are available and only a part of the application is affected.<\/p>\n
The solution to that issue, is to run a manual health check. Quite a lot of health checks can be run manually, like show in the following documentation.<\/p>\n
https:\/\/docs.oracle.com\/database\/121\/ADMIN\/diag.htm#ADMIN11269<\/a><\/p>\n
I start with the DB Structure Integrity Check. This check verifies the integrity of database files and reports failures if these files are inaccessible, corrupt or inconsistent.<\/p>\n
SQL> begin\n  2  dbms_hm.run_check ('DB Structure Integrity Check','Williams Check 00000001');\n  3  end;\n  4  \/\n\nPL\/SQL procedure successfully completed.<\/pre>\n
After running the Health Check, Oracle finds the failure and in the alter.log you will see an entry like the following:<\/p>\n
Checker run found 1 new persistent data failures<\/pre>\n
If you want to take a look what exactly the Health check found, you can invoke the ADRCI and execute the “show hm_run” command.<\/p>\n
oracle@vmoratest1:\/oracle\/workshop\/bombs\/ [DBTEST1] adrci\n\nADRCI: Release 12.1.0.2.0 - Production on Tue Feb 7 16:02:21 2017\n\nCopyright (c) 1982, 2014, Oracle and\/or its affiliates.  All rights reserved.\n\nADR base = \"\/u00\/app\/oracle\"\nadrci> show homes\nADR Homes:\ndiag\/clients\/user_oracle\/host_1833655127_82\ndiag\/tnslsnr\/vmoratest1\/listener\ndiag\/rdbms\/cdb1p\/CDB1P\ndiag\/rdbms\/dbtest1\/DBTEST1\ndiag\/rdbms\/rcat\/RCAT\n\nadrci> set home diag\/rdbms\/dbtest1\/DBTEST1\n\nadrci> show hm_run\n\nADR Home = \/u00\/app\/oracle\/diag\/rdbms\/dbtest1\/DBTEST1:\n*************************************************************************\n\n...\n...\n\n**********************************************************\nHM RUN RECORD 9\n**********************************************************\n   RUN_ID                        206\n   RUN_NAME                      Williams Check 00000001\n   CHECK_NAME                    DB Structure Integrity Check\n   NAME_ID                       2\n   MODE                          0\n   START_TIME                    2017-02-07 16:03:44.431601 +01:00\n   RESUME_TIME                   <NULL>\n   END_TIME                      2017-02-07 16:03:44.478127 +01:00\n   MODIFIED_TIME                 2017-02-07 16:03:44.478127 +01:00\n   TIMEOUT                       0\n   FLAGS                         0\n   STATUS                        5\n   SRC_INCIDENT_ID               0\n   NUM_INCIDENTS                 0\n   ERR_NUMBER                    0\n   REPORT_FILE                   <NULL>\n9 rows fetched\n\nadrci><\/pre>\n
However, if you take a look at the HM RUN report, is gives you an error.<\/p>\n
adrci> show report hm_run 'Williams Check 00000001'\nDIA-48415: Syntax error found in string [show report hm_run 'Williams Check 00000001'] at column [44]<\/pre>\n
This is not a bug. The HM run name must be only alphanumeric and underscore. \u00a0So … better don’t use spaces in between your name. The following would have been better.<\/p>\n
SQL> begin\n  2  dbms_hm.run_check ('DB Structure Integrity Check','WilliamsCheck');\n  3  end;\n  4  \/\n\nPL\/SQL procedure successfully completed.<\/pre>\n
In case, the “adrci show report hm_run” does not work for you, it is not the end of the story. We still can look up the v$hm_finding view.<\/p>\n
select RUN_ID, TIME_DETECTED, STATUS, DESCRIPTION, DAMAGE_DESCRIPTION from v$hm_finding where run_id = '206';\n\nSQL> select RUN_ID, TIME_DETECTED, STATUS, DESCRIPTION, DAMAGE_DESCRIPTION from v$hm_finding where run_id = '206';\n\nRUN_ID TIME_DETECTED                STATUS       DESCRIPTION                                  DAMAGE_DESCRIPTION\n------ ---------------------------- ------------ -------------------------------------------- --------------------------------------------\n   206 07-FEB-17 04.03.44.475000 PM OPEN         Datafile 5: '\/u01\/oradata\/DBTEST1\/hrDBTEST01 Some objects in tablespace HR might be unava\n                                                 .dbf' is missing                             ilable<\/pre>\n
Now let’s check the RMAN “list failure” again.<\/p>\n
RMAN> list failure;\n\nDatabase Role: PRIMARY\n\nList of Database Failures\n=========================\n\nFailure ID Priority Status    Time Detected        Summary\n---------- -------- --------- -------------------- -------\n2          HIGH     OPEN      07-FEB-2017 15:39:38 One or more non-system datafiles are missing\n\n\nRMAN> advise failure;\n...\nAutomated Repair Options\n========================\nOption Repair Description\n------ ------------------\n1      Restore and recover datafile 5\n  Strategy: The repair includes complete media recovery with no data loss\n  Repair script: \/u00\/app\/oracle\/diag\/rdbms\/dbtest1\/DBTEST1\/hm\/reco_668410907.hm\n\n  \nRMAN> repair failure preview;\n\nStrategy: The repair includes complete media recovery with no data loss\nRepair script: \/u00\/app\/oracle\/diag\/rdbms\/dbtest1\/DBTEST1\/hm\/reco_668410907.hm\n\ncontents of repair script:\n   # restore and recover datafile\n   sql 'alter database datafile 5 offline';\n   restore ( datafile 5 );\n   recover datafile 5;\n   sql 'alter database datafile 5 online';<\/pre>\n
Conclusion<\/h3>\n
The Oracle Data Recovery Advisor is quite good, but sometimes you need to push it into the right direction. Besides that, take care of the naming convention that you use for your health check runs. \ud83d\ude09<\/p>\n","protected":false},"excerpt":{"rendered":"
By William Sescu Relying to much on the RMAN Data Recovery Advisor is not always the best idea. In a lot of situations, \u00a0it tells you the right things, however, sometimes it tells you not the optimal things, and sometimes, RMAN list failure does not show any failure at all, even if there is one. […]<\/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":[209,270],"type_dbi":[],"class_list":["post-9754","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","tag-oracle-12c","tag-rman"],"acf":[],"yoast_head":"\nOracle 12c - RMAN list failure does not show any failure even if there is one - 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-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle 12c - RMAN list failure does not show any failure even if there is one\" \/>\n<meta property=\"og:description\" content=\"By William Sescu Relying to much on the RMAN Data Recovery Advisor is not always the best idea. In a lot of situations, \u00a0it tells you the right things, however, sometimes it tells you not the optimal things, and sometimes, RMAN list failure does not show any failure at all, even if there is one. […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-02-08T09:11:09+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=\"5 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-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Oracle 12c – RMAN list failure does not show any failure even if there is one\",\"datePublished\":\"2017-02-08T09:11:09+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one\/\"},\"wordCount\":428,\"commentCount\":0,\"keywords\":[\"Oracle 12c\",\"RMAN\"],\"articleSection\":[\"Database Administration & Monitoring\",\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one\/\",\"name\":\"Oracle 12c - RMAN list failure does not show any failure even if there is one - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2017-02-08T09:11:09+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle 12c – RMAN list failure does not show any failure even if there is one\"}]},{\"@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 12c - RMAN list failure does not show any failure even if there is one - 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-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one\/","og_locale":"en_US","og_type":"article","og_title":"Oracle 12c - RMAN list failure does not show any failure even if there is one","og_description":"By William Sescu Relying to much on the RMAN Data Recovery Advisor is not always the best idea. In a lot of situations, \u00a0it tells you the right things, however, sometimes it tells you not the optimal things, and sometimes, RMAN list failure does not show any failure at all, even if there is one. […]","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one\/","og_site_name":"dbi Blog","article_published_time":"2017-02-08T09:11:09+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Oracle 12c – RMAN list failure does not show any failure even if there is one","datePublished":"2017-02-08T09:11:09+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one\/"},"wordCount":428,"commentCount":0,"keywords":["Oracle 12c","RMAN"],"articleSection":["Database Administration & Monitoring","Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one\/","name":"Oracle 12c - RMAN list failure does not show any failure even if there is one - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2017-02-08T09:11:09+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle 12c – RMAN list failure does not show any failure even if there is one"}]},{"@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\/9754","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=9754"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9754\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=9754"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=9754"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=9754"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=9754"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}