{"id":4911,"date":"2021-05-28T08:54:02","date_gmt":"2021-05-28T08:54:02","guid":{"rendered":"https:\/\/swiv.com.br\/enabing-local-undo-mode-in-oracle-multitenant\/"},"modified":"2026-05-27T20:02:31","modified_gmt":"2026-05-27T19:02:31","slug":"enabing-local-undo-mode-in-oracle-multitenant","status":"publish","type":"post","link":"https:\/\/swiv.com.br\/index.php\/2021\/05\/28\/enabing-local-undo-mode-in-oracle-multitenant\/","title":{"rendered":"Enabing Local UNDO mode in Oracle Multitenant"},"content":{"rendered":"\n<p>A partir da vers\u00e3o 12.2 do Oracle Database, conseguimos escolher o modo de Local ou Shared UNDO na cria\u00e7\u00e3o dos nossos bancos de dados, na arquitetura Multitenant. Neste artigo vou explorar como podemos mudar um ambiente de Shared para Local.<\/p>\n\n\n\n<p>Validando que nosso ambiente est\u00e1 como Shared:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n&#x5B;oracle@oel7 PITR]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Thu May 27 21:24:19 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle.  All rights reserved.\n \n \nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.3.0.0.0\n \nSQL&gt; SELECT NAME,OPEN_MODE,CDB FROM V$DATABASE;\n \nNAME      OPEN_MODE            CDB\n--------- -------------------- ---\nASWAN     READ WRITE           YES\n \nSQL&gt; col PROPERTY_NAME format a25\nSQL&gt; col PROPERTY_VALUE format a10\nSQL&gt; SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = &#039;LOCAL_UNDO_ENABLED&#039;;\n \nPROPERTY_NAME             PROPERTY_V\n------------------------- ----------\nLOCAL_UNDO_ENABLED        FALSE\n<\/pre><\/div>\n\n\n<p>O procedimento em si exige indisponibilidade do ambiente, pois vamos baixar o CDB$ROOT e entrar no modo upgrade conforme abaixo:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; SHU IMMEDIATE;\nDatabase closed.\nDatabase dismounted.\nORACLE instance shut down.\nSQL&gt; STARTUP UPGRADE;\nORACLE instance started.\n \nTotal System Global Area 2382364040 bytes\nFixed Size                  9137544 bytes\nVariable Size             520093696 bytes\nDatabase Buffers         1845493760 bytes\nRedo Buffers                7639040 bytes\nDatabase mounted.\nDatabase opened.\nSQL&gt;\n<\/pre><\/div>\n\n\n<p>Comando para alterar o mode:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; ALTER DATABASE LOCAL UNDO ON;\n \nDatabase altered.\n \nSQL&gt; SHU IMMEDIATE;\nDatabase closed.\nDatabase dismounted.\nORACLE instance shut down.\nSQL&gt; STARTUP;\nORACLE instance started.\n \nTotal System Global Area 2382364040 bytes\nFixed Size                  9137544 bytes\nVariable Size             520093696 bytes\nDatabase Buffers         1845493760 bytes\nRedo Buffers                7639040 bytes\nDatabase mounted.\nDatabase opened.\n<\/pre><\/div>\n\n\n<p>O legal \u00e9 que no momento que os PDBs s\u00e3o abertos, uma UNDO j\u00e1 \u00e9 criada para cada um. Validando que agora nosso ambiente est\u00e1 em Local UNDO:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; col PROPERTY_NAME format a25\nSQL&gt; col PROPERTY_VALUE format a10\nSQL&gt; SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = &#039;LOCAL_UNDO_ENABLED&#039;;\n \nPROPERTY_NAME             PROPERTY_V\n------------------------- ----------\nLOCAL_UNDO_ENABLED        TRUE\n<\/pre><\/div>\n\n\n<p>Pelo REPORT SCHEMA do RMAN podemos ver que a UNDO do nosso PDB e do PDB$SEED foram criadas:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nRMAN&gt; REPORT SCHEMA;\n \nusing target database control file instead of recovery catalog\nReport of database schema for database with db_unique_name ASWAN\n \nList of Permanent Datafiles\n===========================\nFile Size(MB) Tablespace           RB segs Datafile Name\n---- -------- -------------------- ------- ------------------------\n1    890      SYSTEM               YES     \/oracle\/dados\/ASWAN\/datafile\/o1_mf_system_jc0blms9_.dbf\n3    540      SYSAUX               NO      \/oracle\/dados\/ASWAN\/datafile\/o1_mf_sysaux_jc0bn0x8_.dbf\n4    420      UNDOTBS1             YES     \/oracle\/dados\/ASWAN\/datafile\/o1_mf_undotbs1_jc0bnt0l_.dbf\n5    270      PDB$SEED:SYSTEM      NO      \/oracle\/dados\/ASWAN\/datafile\/o1_mf_system_jc0bys8k_.dbf\n6    330      PDB$SEED:SYSAUX      NO      \/oracle\/dados\/ASWAN\/datafile\/o1_mf_sysaux_jc0bys95_.dbf\n7    5        USERS                NO      \/oracle\/dados\/ASWAN\/datafile\/o1_mf_users_jc0bnv2t_.dbf\n9    270      HIPOFISE1:SYSTEM     YES     \/oracle\/dados\/ASWAN\/C35954A5800908B7E0536C00A8C0EE5B\/datafile\/o1_mf_system_jc0congn_.dbf\n10   340      HIPOFISE1:SYSAUX     NO      \/oracle\/dados\/ASWAN\/C35954A5800908B7E0536C00A8C0EE5B\/datafile\/o1_mf_sysaux_jc0congs_.dbf\n11   100      HIPOFISE1:USERS      NO      \/oracle\/dados\/ASWAN\/C35954A5800908B7E0536C00A8C0EE5B\/datafile\/o1_mf_users_jc0cp8xo_.dbf\n12   126      PDB$SEED:UNDO_1      NO      \/oracle\/dados\/ASWAN\/C3592AD9E96374EFE0536C00A8C0C507\/datafile\/o1_mf_undo_1_jc0g9hlp_.dbf\n13   126      HIPOFISE1:UNDO_1     YES     \/oracle\/dados\/ASWAN\/C35954A5800908B7E0536C00A8C0EE5B\/datafile\/o1_mf_undo_1_jc0g9pp5_.dbf\n \nList of Temporary Files\n=======================\nFile Size(MB) Tablespace           Maxsize(MB) Tempfile Name\n---- -------- -------------------- ----------- --------------------\n1    32       TEMP                 32767       \/oracle\/dados\/ASWAN\/datafile\/o1_mf_temp_jc0bsfwd_.tmp\n2    36       PDB$SEED:TEMP        32767       \/oracle\/dados\/ASWAN\/datafile\/temp012021-05-27_20-32-06-927-PM.dbf\n3    36       HIPOFISE1:TEMP       32767       \/oracle\/dados\/ASWAN\/C35954A5800908B7E0536C00A8C0EE5B\/datafile\/o1_mf_temp_jc0congs_.dbf\n<\/pre><\/div>\n\n\n<p>Como estou usando a vers\u00e3o 19C, a UNDO do PDB$SEED foi criado automaticamente, mas na vers\u00e3o 12.2 esse procedimento deve ser realizado manualmente, conforme etapas abaixo:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nALTER PLUGGABLE DATABASE PDB$SEED OPEN READ WRITE FORCE;\nALTER SESSION SET CONTAINER=PDB$SEED;\nCREATE UNDO TABLESPACE LOCAL UNDO DATAFILE SIZE 100M AUTOEXTEND ON NEXT 100;\nALTER PLUGGABLE DATABASE PDB$SEED CLOSE IMMEDIATE;\nALTER PLUGGABLE DATABASE PDB$SEED OPEN READ ONLY;\n<\/pre><\/div>\n\n\n<p>Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laborat\u00f3rio pessoal para fins de aprendizado.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A partir da vers\u00e3o 12.2 do Oracle Database, conseguimos escolher o modo de Local ou Shared UNDO na cria\u00e7\u00e3o dos nossos bancos de dados, na arquitetura Multitenant. Neste artigo vou explorar como podemos mudar um ambiente de Shared para Local. Validando que nosso ambiente est\u00e1 como Shared: O procedimento em si exige indisponibilidade do ambiente, [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[],"class_list":["post-4911","post","type-post","status-publish","format-standard","hentry","category-multitenant"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/4911","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=4911"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/4911\/revisions"}],"predecessor-version":[{"id":9126,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/4911\/revisions\/9126"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=4911"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=4911"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=4911"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}