{"id":498,"date":"2021-07-05T16:03:37","date_gmt":"2021-07-05T14:03:37","guid":{"rendered":"https:\/\/anjo.pt\/keyword-oracle\/?p=498"},"modified":"2021-07-05T16:09:31","modified_gmt":"2021-07-05T14:09:31","slug":"how-to-change-pdb-open-mode-without-disconnecting-existing-sessions-the-ambiguous-force","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2021\/07\/05\/how-to-change-pdb-open-mode-without-disconnecting-existing-sessions-the-ambiguous-force\/","title":{"rendered":"How to change PDB open mode without disconnecting existing sessions: the ambiguous FORCE"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">When the database is in READ ONLY mode, the way to change it to READ WRITE is by issuing<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; ALTER DATABASE OPEN READ WRITE;\nDatabase altered.\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">However, when working on a PDB, the similar syntax does not work:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE;\nALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE\n*\nERROR at line 1:\nORA-65019: pluggable database pdb1 already open\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">This is strange, and to my surprise, the correct syntax to change a PDB from READ ONLY to READ WRITE is:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE FORCE;\nPluggable database altered.\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">I would expect that the &#8220;FORCE&#8221; would do the same as on the STARTUP command here:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; STARTUP FORCE OPEN READ WRITE;\nORACLE instance started.\n\nTotal System Global Area 2147481656 bytes\nFixed Size                  8898616 bytes\nVariable Size             872415232 bytes\nDatabase Buffers         1258291200 bytes\nRedo Buffers                7876608 bytes\nDatabase mounted.\nDatabase opened.\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">On the normal STARTUP command, the FORCE keyword performs a shutdown abort, before starting back the database. It is correctly described in the documentation:<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p>If the database is open, then <code>FORCE <\/code>shuts down the database with a <code>SHUTDOWN ABORT<\/code> statement before re-opening it.<\/p><cite><a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/admin\/starting-up-and-shutting-down.html#GUID-CCA52747-05CA-4ED3-BE6D-E2E684C4D87D\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/admin\/starting-up-and-shutting-down.html#GUID-CCA52747-05CA-4ED3-BE6D-E2E684C4D87D<\/a><\/cite><\/blockquote>\n\n\n\n<p class=\"wp-block-paragraph\">The ALTER PLUGGABLE DATABASE explains the different behaviour of the FORCE keyword in this case:<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p>Specify this keyword [<code>FORCE<\/code>] to change the open mode of a PDB from\u00a0<code>READ\u00a0WRITE<\/code>\u00a0to\u00a0<code>READ\u00a0ONLY<\/code>, or from\u00a0<code>READ\u00a0ONLY<\/code>\u00a0to\u00a0<code>READ\u00a0WRITE<\/code>. The\u00a0<code>FORCE\u00a0<\/code>keyword allows users to remain connected to the PDB while the open mode is changed.<\/p><p>When you specify\u00a0<code>FORCE<\/code>\u00a0to change the open mode of a PDB from\u00a0<code>READ\u00a0WRITE<\/code>\u00a0to\u00a0<code>READ\u00a0ONLY<\/code>, any\u00a0<code>READ\u00a0WRITE<\/code>\u00a0transaction that is open when you change the open mode will not be allowed to perform any more DML operations or to\u00a0<code>COMMIT<\/code>.<\/p><cite><a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/sqlrf\/ALTER-PLUGGABLE-DATABASE.html#GUID-A29491AD-8F0F-4E52-9D94-57FC3FF8FBC7\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/sqlrf\/ALTER-PLUGGABLE-DATABASE.html#GUID-A29491AD-8F0F-4E52-9D94-57FC3FF8FBC7<\/a><\/cite><\/blockquote>\n\n\n\n<p class=\"wp-block-paragraph\">So, at the end it is good news. It is possible to change the PDB open mode without killing the users&#8217; sessions. Just that &#8220;FORCE&#8221; keyword seems a bit too strong for my taste.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When the database is in READ ONLY mode, the way to change it to READ WRITE is by issuing However, when working on a PDB, the similar syntax does not work: This is strange, and to my surprise, the correct syntax to change a PDB from READ ONLY to READ WRITE is: I would expect [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[65,6,39],"tags":[],"class_list":["post-498","post","type-post","status-publish","format-standard","category-multitenant","category-oracle","category-oracle-19c","czr-hentry"],"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/498","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=498"}],"version-history":[{"count":4,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/498\/revisions"}],"predecessor-version":[{"id":504,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/498\/revisions\/504"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=498"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=498"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=498"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}