.
\nIf you are in multitenant, you probably already felt the desire to open the PDB$SEED in READ WRITE mode.<\/p>\n
\nIn 12.1 you have no reason to open the seed read write yourself. In 12.2 there is one reason when you are in LOCAL UNDO mode, because you may want to customize the UNDO tablespace. <\/p>\n
I am in 12.1 or in 12.2 in shared undo mode:<\/p>\n
SYS@CDB$ROOT SQL> select * from database_properties where property_name like '%UNDO%';\n \nno rows selected\n<\/code><\/pre>\nWhen the CDB is opened, the PDB$SEED is opened in read only mode.<\/p>\n
SYS@CDB$ROOT SQL> show pdbs\n&nsbp;\nCON_ID CON_NAME OPEN MODE RESTRICTED\n------ -------- ---- ---- ----------\n2 PDB$SEED READ ONLY NO\n3 PDB01 READ WRITE NO<\/code><\/pre>\nI try to open the PDB$SEED in read write mode (FORCE is a shortcut to avoid to close it before)<\/p>\n
SYS@CDB$ROOT SQL> alter pluggable database pdb$seed open force;\n&nsbp;\nError starting at line : 1 in command -\nalter pluggable database pdb$seed open force\nError report -\nORA-65017: seed pluggable database may not be dropped or altered\n65017. 00000 - \"seed pluggable database may not be dropped or altered\"\n*Cause: User attempted to drop or alter the Seed pluggable database which is not allowed.\n*Action: Specify a legal pluggable database name.\nSYS@CDB$ROOT SQL>\n<\/code><\/pre>\nObviously, this is impossible and clearly documented. PDB$SEED is not a legal pluggable database for this operation.<\/p>\n
Oracle Script<\/h3>\n
There is an exception to that: internal Oracle scripts need to run statements in the PDB$SEED. They run with “_oracle_script”=true where this operation is possible:<\/p>\n
SYS@CDB$ROOT SQL> alter session set \"_oracle_script\"=true;\nSession altered.\n \nSYS@CDB$ROOT SQL> alter pluggable database pdb$seed open read write force;\nPluggable database PDB$SEED altered.\n<\/code><\/pre>\ncatcon.pl<\/h3>\n
Of course, when upgrading, there are phases where you need the seed opened read-write. But you don’t to that yourself. The scripts to run in each container are called through catcon.pl which, by default, opens the seed read-write and ensures that the initial open mode is restored at the end even in case of error.<\/p>\n
\n -m mode in which PDB$SEED should be opened; one of the following values\n may be specified:\n - UNCHANGED - leave PDB$SEED in whatever mode it is already open\n - READ WRITE (default)\n - READ ONLY\n - UPGRADE\n - DOWNGRADE\n<\/code><\/pre>\nI have the following “\/tmp\/show_open_mode.sql” script<\/p>\n
\ncolumn name format a10\nselect name,open_mode,current_timestamp-open_time from v$containers;\n<\/code><\/pre>\nI call it with catcon to run in PDB$SEED:<\/p>\n
\n$ORACLE_HOME\/perl\/bin\/perl $ORACLE_HOME\/rdbms\/admin\/catcon.pl -c 'PDB$SEED' -n 1 -d \/tmp -l \/tmp -b tmp -show_open_mode.sql\n<\/code><\/pre>\nHere is the output in \/tmp\/tmp0.log<\/p>\n
\nCATCON_STATEMENT\n--------------------------------------\ncatconExec(): @\/tmp\/show_open_mode.sql\nSQL> SQL> column name format a10\nSQL> select name,open_mode,current_timestamp-open_time from v$containers;\nNAME OPEN_MODE CURRENT_TIMESTAMP-OPEN_TIME\n---------- ---------- ---------------------------------------------------------------------------\nPDB$SEED READ WRITE +000000000 00:00:00.471398\nSQL>\nEND_RUNNING\n------------------------------------------------------------------------------------------------------------------------\n==== @\/tmp\/show_open_mode.sql Container:PDB$SEED Id:2 17-05-07 05:02:06 Proc:0 ====\nSQL>\nEND_RUNNING\n------------------------------------------------------------------------------------------------------------------------\n==== @\/tmp\/show_open_mode.sql Container:PDB$SEED Id:2 17-05-07 05:02:06 Proc:0 ====\n<\/code><\/pre>\nThe PDB$SEED was opened READ WRITE to run the statements.<\/p>\n
We can see that in alert.log:<\/p>\n
\nalter pluggable database pdb$seed close immediate instances=all\nALTER SYSTEM: Flushing buffer cache inst=0 container=2 local\nPluggable database PDB$SEED closed\nCompleted: alter pluggable database pdb$seed close immediate instances=all\nalter pluggable database pdb$seed OPEN READ WRITE\nDatabase Characterset for PDB$SEED is WE8MSWIN1252\nOpening pdb PDB$SEED (2) with no Resource Manager plan active\nPluggable database PDB$SEED opened read write\nCompleted: alter pluggable database pdb$seed OPEN READ WRITE\nalter pluggable database pdb$seed close immediate instances=all\nALTER SYSTEM: Flushing buffer cache inst=0 container=2 local\nPluggable database PDB$SEED closed\nCompleted: alter pluggable database pdb$seed close immediate instances=all\nalter pluggable database pdb$seed OPEN READ ONLY instances=all\nDatabase Characterset for PDB$SEED is WE8MSWIN1252\nOpening pdb PDB$SEED (2) with no Resource Manager plan active\nPluggable database PDB$SEED opened read only\nCompleted: alter pluggable database pdb$seed OPEN READ ONLY instances=all\n<\/code><\/pre>\nWhen the pre-upgrade and post-upgrade scripts are run from DBUA you can see the following in the logs:<\/p>\n
exec_DB_script: opened Reader and Writer\nexec_DB_script: executed connect \/ AS SYSDBA\nexec_DB_script: executed alter session set \"_oracle_script\"=TRUE\n\/\nexec_DB_script: executed alter pluggable database pdb$seed close immediate instances=all\n\/\nexec_DB_script: executed alter pluggable database pdb$seed OPEN READ WRITE\n\/<\/code><\/pre>\nThis is displayed because DBUA runs catcon.pl in debug mode and you can do the same by adding ‘-g’ to the catcon.pl arguments.<\/p>\n
12c<\/em>R2 in local undo<\/h3>\nIn 12.2 there is a case where you can make a change to the PDB$SEED to customize the UNDO tablespace template. Here I am changing to LOCAL UNDO:<\/p>\n
\nSYS@CDB$ROOT SQL> shutdown immediate;\nDatabase closed.\nDatabase dismounted.\nORACLE instance shut down.\n&nsbp;\nSYS@CDB$ROOT SQL> startup upgrade;\nORACLE instance started.\n&nsbp;\nTotal System Global Area 1107296256 bytes\nFixed Size 8791864 bytes\nVariable Size 939526344 bytes\nDatabase Buffers 150994944 bytes\nRedo Buffers 7983104 bytes\nDatabase mounted.\nDatabase opened.\n&nsbp;\nSYS@CDB$ROOT SQL> alter database local undo on;\nDatabase altered.\n&nsbp;\nSYS@CDB$ROOT SQL> shutdown immediate;\nDatabase closed.\nDatabase dismounted.\nORACLE instance shut down.\n \nSYS@CDB$ROOT SQL> select * from database_properties where property_name like '%UNDO%';\n \nPROPERTY_NAME PROPERTY_VALUE DESCRIPTION\n------------- -------------- -----------\nLOCAL_UNDO_ENABLED TRUE true if local undo is enabled\n<\/code><\/pre>\nPDB$SEED is read only:<\/p>\n
\nSYS@CDB$ROOT SQL> show pdbs\nCON_ID CON_NAME OPEN MODE RESTRICTED\n------ -------- ---- ---- ----------\n2 PDB$SEED READ ONLY NO\n3 PDB01 READ WRITE NO\n<\/code><\/pre>\nand _oracle_script is not set:<\/p>\n
\nSYS@CDB$ROOT SQL> show parameter script\n \nNAME TYPE VALUE\n---- ---- -----\n \n<\/code><\/pre>\nI get no error now and can open the seed in read-write mode:<\/p>\n
\nSYS@CDB$ROOT SQL> alter pluggable database PDB$SEED open force;\nPluggable database PDB$SEED altered.\n \nSYS@CDB$ROOT SQL> show pdbs\n \nCON_ID CON_NAME OPEN MODE RESTRICTED\n------ -------- ---- ---- ----------\n2 PDB$SEED READ WRITE NO\n3 PDB01 READ WRITE NO\n<\/code><\/pre>\nCustomize UNDO seed<\/h3>\n
Once you open read write an undo tablespace is created. If you want to customize it, you can create another one and drop the previous one. This requires changing the undo_tablespace parameter:<\/p>\n
\nSYS@CDB$ROOT SQL> show parameter undo\nNAME TYPE VALUE\n----------------- ------- ------\nundo_tablespace string UNDO_1\n \nSYS@CDB$ROOT SQL> create undo tablespace UNDO;\nTablespace UNDO created.\n \nSYS@CDB$ROOT SQL> alter system set undo_tablespace=UNDO;\nSystem SET altered.\n \nSYS@CDB$ROOT SQL> drop tablespace UNDO_1 including contents and datafiles;\nTablespace UNDO_1 dropped.\n \nSYS@CDB$ROOT SQL> shutdown immediate\nPluggable Database closed\n<\/code><\/pre>\nYou can leave it like this, just close and re-open read only. If you want to keep the same undo tablespace name as before, you need to play with create and drop, and change undo_tablespace again.<\/p>\n
So what?<\/h3>\n
Don’t forget that you should not modify or drop PDB$SEED. If you want a customized template for your PDB creations, then you should create your PDB template to clone. You can clone remotely, so this is possible in single-tenant as well. Being able to open the PDB$SEED in read write is possible only for the exception of creating the UNDO tablespace in PDB$SEED when you move to local undo mode. This is not required, and then an UNDO tablespace will be created when you open a PDB with no undo_tablespace.
\nWhen running pre-upgrade and post-upgrade scripts, then don’t worry: catcon.pl is there to help run scripts in containers and handles that for you.<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . If you are in multitenant, you probably already felt the desire to open the PDB$SEED in READ WRITE mode. Can you open PDB$SEED read write yourseld? Yes and No. Should you open PDB$SEED read write yourself? Yes and No. How to run upgrade scripts that need to write to PDB$SEED? catcon.pl<\/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],"tags":[656,1085,220,1086,64,96,209,66,223],"type_dbi":[],"class_list":["post-10024","post","type-post","status-publish","format-standard","hentry","category-database-management","tag-12-2","tag-catcon","tag-cdb","tag-local-undo","tag-multitenant","tag-oracle","tag-oracle-12c","tag-pdb","tag-pluggable-databases"],"acf":[],"yoast_head":"\n
Can you open PDB$SEED read write? - 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\/can-you-open-pdbseed-read-write\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Can you open PDB$SEED read write?\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . If you are in multitenant, you probably already felt the desire to open the PDB$SEED in READ WRITE mode. Can you open PDB$SEED read write yourseld? Yes and No. Should you open PDB$SEED read write yourself? Yes and No. How to run upgrade scripts that need to write to PDB$SEED? catcon.pl\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/can-you-open-pdbseed-read-write\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-05-07T13:27:19+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=\"7 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\/can-you-open-pdbseed-read-write\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/can-you-open-pdbseed-read-write\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Can you open PDB$SEED read write?\",\"datePublished\":\"2017-05-07T13:27:19+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/can-you-open-pdbseed-read-write\/\"},\"wordCount\":594,\"commentCount\":1,\"keywords\":[\"12.2\",\"catcon\",\"CDB\",\"local undo\",\"multitenant\",\"Oracle\",\"Oracle 12c\",\"PDB\",\"Pluggable Databases\"],\"articleSection\":[\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/can-you-open-pdbseed-read-write\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/can-you-open-pdbseed-read-write\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/can-you-open-pdbseed-read-write\/\",\"name\":\"Can you open PDB$SEED read write? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2017-05-07T13:27:19+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/can-you-open-pdbseed-read-write\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/can-you-open-pdbseed-read-write\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/can-you-open-pdbseed-read-write\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Can you open PDB$SEED read write?\"}]},{\"@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":"Can you open PDB$SEED read write? - 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\/can-you-open-pdbseed-read-write\/","og_locale":"en_US","og_type":"article","og_title":"Can you open PDB$SEED read write?","og_description":"By Franck Pachot . If you are in multitenant, you probably already felt the desire to open the PDB$SEED in READ WRITE mode. Can you open PDB$SEED read write yourseld? Yes and No. Should you open PDB$SEED read write yourself? Yes and No. How to run upgrade scripts that need to write to PDB$SEED? catcon.pl","og_url":"https:\/\/www.dbi-services.com\/blog\/can-you-open-pdbseed-read-write\/","og_site_name":"dbi Blog","article_published_time":"2017-05-07T13:27:19+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/can-you-open-pdbseed-read-write\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/can-you-open-pdbseed-read-write\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Can you open PDB$SEED read write?","datePublished":"2017-05-07T13:27:19+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/can-you-open-pdbseed-read-write\/"},"wordCount":594,"commentCount":1,"keywords":["12.2","catcon","CDB","local undo","multitenant","Oracle","Oracle 12c","PDB","Pluggable Databases"],"articleSection":["Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/can-you-open-pdbseed-read-write\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/can-you-open-pdbseed-read-write\/","url":"https:\/\/www.dbi-services.com\/blog\/can-you-open-pdbseed-read-write\/","name":"Can you open PDB$SEED read write? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2017-05-07T13:27:19+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/can-you-open-pdbseed-read-write\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/can-you-open-pdbseed-read-write\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/can-you-open-pdbseed-read-write\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Can you open PDB$SEED read write?"}]},{"@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\/10024","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=10024"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10024\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=10024"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=10024"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=10024"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=10024"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}