{"id":4414,"date":"2015-03-16T03:56:49","date_gmt":"2015-03-16T02:56:49","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/12c-shutdown-abort-a-pdb\/"},"modified":"2015-03-16T03:56:49","modified_gmt":"2015-03-16T02:56:49","slug":"12c-shutdown-abort-a-pdb","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/12c-shutdown-abort-a-pdb\/","title":{"rendered":"12c: shutdown abort a PDB?"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nCan we shutdown abort a PDB? Let’s try:<\/p>\n

SQL> show con_id\nCON_ID\n------------------------------\n3SQL> shutdown abort;\nPluggable Database closed.<\/code><\/pre>\n
But is it really a shutdown abort?<\/p>\n
The shutdown abort we know in non-CDB – or in a CDB from CDB$ROOT – is used when the instance cannot checkpoint before closing the file. It leaves the files fuzzy. And shutdown abort crashes the instance – loosing all changes made in buffer cache and not yet checkpointed. This is why we have online redo logs so that an instance recovery can be used to recover those changes.<\/p>\n
But if I shutdown abort only a PDB the instance is still there. Let’s see from alert.log what it did:<\/p>\n
2015-03-16 06:59:51.487000 +01:00\n ALTER PLUGGABLE DATABASE CLOSE ABORT\n ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local\n Pluggable database PDB closed\n Completed: ALTER PLUGGABLE DATABASE CLOSE ABORT<\/code><\/pre>\n
Ok. even if it’s called ‘close abort’ a checkpoint occured. Look at the file fuzziness:<\/p>\n
\u00a0SQL> select file#,con_id,fuzzy from v$datafile_header;<\/code><\/pre>\n
     FILE#     CON_ID FUZ\n---------- ---------- ---\n         4          0 YES\n         8          3 NO\n         9          3 NO\n        10          3 NO<\/code><\/pre>\n
Except for the UNDO which is in CDB$ROOT, none of my PDB files are fuzzy. There were closed cleanly.<\/p>\n
This is fine when we can write into the datafiles in order to checkpoint them. But if the file is not there we can’t checkpoint it. Then what happens?<\/p>\n
On a non-CDB – or on the CDB$ROOT of a CDB – it’s simple. The instance crashes. By default, since 11.2.0.2, and without changing the “_datafile_write_errors_crash_instance” default value, the instance crashes as soon as a file is missing at checkpoint. If “_datafile_write_errors_crash_instance” is set to true, the instance crashes only when the datafiles is a system one.<\/p>\n
Okay, we have to think about that behaviour change (and that’s for a future blog post) but usually the application is down when a datafile is missing, so better to stop everything.<\/p>\n
But the problem is in multitenant. As we have seen in previous posts, the whole instance crashes at checkpoint, even when a PDB only datafile is missing. You can set “_datafile_write_errors_crash_instance” but the issue is still there for system datafiles. I had open a bug about it and it’s fixed in latest patchset, but that is not sufficient.<\/p>\n
The problem is that offlining a file without checkpoint leaves it in a ‘need recovery’ state. But if the instance is still running then the online redo logs can be overwriten and all changes are lost. Of course, in archivelog mode you should be able to restore them. But by default crashing the instance is chosen.<\/p>\n
I’m in archivelog mode and I can shutdown abort the PDB as soon as I know that the datafile is missing (and before next checkpoint):<\/p>\n
RMAN> host \"rm \/u02\/oradata\/CDB\/datafile\/o1_mf_system_bjdz9ldm_.dbf\";\n\nhost command complete\n\nRMAN> alter pluggable database pdb close abort;\n\nStatement processed<\/code><\/pre>\n
and here is the alert.log:<\/p>\n
2015-03-16 08:04:37.194000 +01:00\nalter pluggable database pdb close abort\n**********************************************************\nPluggable Database PDB with id - 3 has a missing or corrupt\ndatafile belonging to the pluggable database. It will be\nclosed abnormally. The pdb needs to be dropped or recovered\n**********************************************************\nChecker run found 1 new persistent data failures\nPluggable database PDB closed\nCompleted: alter pluggable database pdb close abort<\/code><\/pre>\n
So I’m able to shutdown abort a PDB when I’m in archivelog mode. If I’m not in archivelog mode can’t do it (except if “_enable_pdb_close_noarchivelog”=true but that is very risky ).<\/p>\n
But I want that shutdown to occur automatically because consolidation should not decrease databases availability and I’m not sure to be able to manually close abort before the next checkpoint. Then I have to set “_enable_pdb_close_abort”=true and here is what happens in alert.log:<\/p>\n
2015-03-16 08:55:18.833000 +01:00\nErrors in file \/u01\/app\/oracle\/diag\/rdbms\/cdb\/CDB\/trace\/CDB_ckpt_1698.trc:\nORA-01243: system tablespace file suffered media failure\nORA-01116: error in opening database file 8\nORA-01110: data file 8: '\/u02\/oradata\/CDB\/datafile\/o1_mf_system_bjf31v1m_.dbf'\nORA-27041: unable to open file\nLinux-x86_64 Error: 2: No such file or directory\nAdditional information: 3\nInternal PDB shutdown abort of PDB (container=3)<\/code><\/pre>\n
\u00a0Conclusion<\/h3>\n
I order to have only the PDB shut down in case of the loss of a system file – instead of the whole CDB – you need:<\/p>\n
    \n
  • to be in archivelog mode<\/li>\n
  • to have latest patchset (with fix for bug 19001390)<\/li>\n
  • to set “_enable_pdb_close_abort”=true<\/li>\n<\/ul>\n
    As you are in archivelog mode, you should be able to recover, but I suppose you will not wait too long to restore and recover the missing datafiles.<\/p>\n","protected":false},"excerpt":{"rendered":"
    By Franck Pachot . Can we shutdown abort a PDB? Let’s try: SQL> show con_id CON_ID —————————— 3SQL> shutdown abort; Pluggable Database closed. But is it really a shutdown abort? The shutdown abort we know in non-CDB – or in a CDB from CDB$ROOT – is used when the instance cannot checkpoint before closing the […]<\/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":[198,59],"tags":[220,84,64,320,96,209,66,223],"type_dbi":[],"class_list":["post-4414","post","type-post","status-publish","format-standard","hentry","category-database-management","category-oracle","tag-cdb","tag-high-availability","tag-multitenant","tag-multitenant-database","tag-oracle","tag-oracle-12c","tag-pdb","tag-pluggable-databases"],"acf":[],"yoast_head":"\n12c: shutdown abort a PDB? - dbi Blog<\/title>\n<meta name=\"description\" content=\"close abort the PDB instead of the CDB in case of media failure.\" \/>\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\/12c-shutdown-abort-a-pdb\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"12c: shutdown abort a PDB?\" \/>\n<meta property=\"og:description\" content=\"close abort the PDB instead of the CDB in case of media failure.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/12c-shutdown-abort-a-pdb\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-03-16T02:56:49+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=\"4 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\\\/12c-shutdown-abort-a-pdb\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/12c-shutdown-abort-a-pdb\\\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"12c: shutdown abort a PDB?\",\"datePublished\":\"2015-03-16T02:56:49+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/12c-shutdown-abort-a-pdb\\\/\"},\"wordCount\":595,\"commentCount\":0,\"keywords\":[\"CDB\",\"High availability\",\"multitenant\",\"Multitenant database\",\"Oracle\",\"Oracle 12c\",\"PDB\",\"Pluggable Databases\"],\"articleSection\":[\"Database management\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/12c-shutdown-abort-a-pdb\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/12c-shutdown-abort-a-pdb\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/12c-shutdown-abort-a-pdb\\\/\",\"name\":\"12c: shutdown abort a PDB? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2015-03-16T02:56:49+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"description\":\"close abort the PDB instead of the CDB in case of media failure.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/12c-shutdown-abort-a-pdb\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/12c-shutdown-abort-a-pdb\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/12c-shutdown-abort-a-pdb\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"12c: shutdown abort a PDB?\"}]},{\"@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":"12c: shutdown abort a PDB? - dbi Blog","description":"close abort the PDB instead of the CDB in case of media failure.","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\/12c-shutdown-abort-a-pdb\/","og_locale":"en_US","og_type":"article","og_title":"12c: shutdown abort a PDB?","og_description":"close abort the PDB instead of the CDB in case of media failure.","og_url":"https:\/\/www.dbi-services.com\/blog\/12c-shutdown-abort-a-pdb\/","og_site_name":"dbi Blog","article_published_time":"2015-03-16T02:56:49+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/12c-shutdown-abort-a-pdb\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/12c-shutdown-abort-a-pdb\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"12c: shutdown abort a PDB?","datePublished":"2015-03-16T02:56:49+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/12c-shutdown-abort-a-pdb\/"},"wordCount":595,"commentCount":0,"keywords":["CDB","High availability","multitenant","Multitenant database","Oracle","Oracle 12c","PDB","Pluggable Databases"],"articleSection":["Database management","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/12c-shutdown-abort-a-pdb\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/12c-shutdown-abort-a-pdb\/","url":"https:\/\/www.dbi-services.com\/blog\/12c-shutdown-abort-a-pdb\/","name":"12c: shutdown abort a PDB? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2015-03-16T02:56:49+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"description":"close abort the PDB instead of the CDB in case of media failure.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/12c-shutdown-abort-a-pdb\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/12c-shutdown-abort-a-pdb\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/12c-shutdown-abort-a-pdb\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"12c: shutdown abort a PDB?"}]},{"@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\/4414","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=4414"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4414\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=4414"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=4414"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=4414"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=4414"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}