{"id":7046,"date":"2018-12-07T16:16:00","date_gmt":"2018-12-07T15:16:00","guid":{"rendered":"https:\/\/www.carajandb.com\/?p=7046"},"modified":"2018-12-14T13:14:26","modified_gmt":"2018-12-14T12:14:26","slug":"oracle-18-xe-and-multitenant","status":"publish","type":"post","link":"https:\/\/carajandb.com\/en\/2018\/12\/07\/oracle-18-xe-and-multitenant\/","title":{"rendered":"Oracle 18 XE and Multitenant"},"content":{"rendered":"

As already mentioned in my blog “Oracle 18 XE is available<\/a>” the\u00a0 XE version includes the multitenant database option. So you can create up to three pluggable databases. But in this case it doesn’t matter if it is an “ordinary” PDB, a Proxy PDB\u00a0 or an Application Root.If this is your first time dealing with the multitenant database Option I would advice to create datafiles as Oracle Managed Files (OMF) because with this the command CREATE PLUGGABLE DATABASE<\/code> becomes much easier. Unfortunately per default XE is not using Oracle Managed Files.<\/p>\n

Oracle Managed Files<\/h3>\n

Bevore you are going to start you should drop the already created pluggable database XEPDB1. You should gain experience with your own PDBs not by using the precreated ones:<\/p>\n

SQL> ALTER PLUGGABLE DATABASE xepdb1 CLOSE IMMEDIATE;\r\nSQL> DROP PLUGGABLE DATABASE xepdb1 INCLUDING DATAFILES;\r\n<\/pre>\n
If you don’t care about the fact that not all datafiles are created as OMF I would recommend not to touch the CDB. In this case it’s sufficient to set the following parameter:<\/p>\n
SQL> ALTER SYSTEM SET db_create_file_dest='\/u02\/oradata';\r\n<\/pre>\n
Please make sure that the directory exists (but you probably already added that directory while creating the database). The variable shouldn’t include the name of the database as this name will be added automatically.
\nNow it’s time to create the first PDB:<\/p>\n
SQL> CREATE PLUGGABLE DATABASE suzanne \r\n      ADMIN USER pdbadmin IDENTIFIED BY manager\r\n      DEFAULT TABLESPACE users;\r\nSQL> ALTER PLUGGABLE DATABASE suzanne OPEN;\r\nSQL> ALTER PLUGGABLE DATABASE suzanne SAVE STATE;\r\n<\/pre>\n
That’s it!
\nAs always there is one constraint: you shouldn’t try to type the entry path of the OMF datafiles but using “Drag & Drop” instead!<\/p>\n
SQL> ALTER SESSION SET \"_exclude_seed_cdb_view\"=FALSE;\r\nSQL> SELECT p.name, d.tablespace_name, file_name\r\n       FROM cdb_data_files d, v$containers p\r\n      WHERE p.con_id = d.con_id\r\n      ORDER BY p.con_id,2\r\n\r\nNAME       TABLESPACE FILE_NAME\r\n---------- ---------- ------------------------------------------------------------------------------------------\r\nCDB$ROOT   SYSAUX     \/u02\/oradata\/XE\/sysaux01.dbf\r\nCDB$ROOT   SYSTEM     \/u02\/oradata\/XE\/system01.dbf\r\nCDB$ROOT   UNDOTBS1   \/u02\/oradata\/XE\/undotbs01.dbf\r\nCDB$ROOT   USERS      \/u02\/oradata\/XE\/users01.dbf\r\nPDB$SEED   SYSAUX     \/u02\/oradata\/XE\/pdbseed\/sysaux01.dbf\r\nPDB$SEED   SYSTEM     \/u02\/oradata\/XE\/pdbseed\/system01.dbf\r\nPDB$SEED   UNDOTBS1   \/u02\/oradata\/XE\/pdbseed\/undotbs01.dbf\r\nSUZANNE    SYSAUX     \/u02\/oradata\/XE\/7B913842A2980993E0530100007F3756\/datafile\/o1_mf_sysaux_fzqrrx65_.dbf\r\nSUZANNE    SYSTEM     \/u02\/oradata\/XE\/7B913842A2980993E0530100007F3756\/datafile\/o1_mf_system_fzqrrx5n_.dbf\r\nSUZANNE    UNDOTBS1   \/u02\/oradata\/XE\/7B913842A2980993E0530100007F3756\/datafile\/o1_mf_undotbs1_fzqrrx67_.dbf\r\n<\/pre>\n
The command ALTER SESSION SET \"_exclude_seed_cdb_view\"=FALSE;<\/code> adds the datafiles of PDB$SEED to the result set. With version 12.1.0.2 Oracle decided that it’s not useful to show data belonging to PDB$SEED – you can have your own opinion on that.<\/p>\n
PDBs and Services<\/h3>\n
Every PDB is creaded with an associated service. In our case the result of the command lsnrctl status<\/code> is:<\/p>\n
Services Summary...\r\nService \"7b913842a2980993e0530100007f3756\" has 1 instance(s).\r\n  Instance \"XE\", status READY, has 1 handler(s) for this service...\r\nService \"XE\" has 2 instance(s).\r\n  Instance \"XE\", status UNKNOWN, has 1 handler(s) for this service...\r\n  Instance \"XE\", status READY, has 1 handler(s) for this service...\r\nService \"XEXDB\" has 1 instance(s).\r\n  Instance \"XE\", status READY, has 1 handler(s) for this service...\r\nService \"suzanne\" has 1 instance(s).\r\n  Instance \"XE\", status READY, has 1 handler(s) for this service...\r\nThe command completed successfully\r\n<\/pre>\n
This service (suzanne) is, by the way, the reason why PDBs have to be uniquely named on the entire server. This shouldn’t be a problem with XE (as long as we don’t have a newer version) but with the multitenant database Option and multiple databases you should take care that you don’t have PDB naming duplicates.
\nAs a best practice you shouldn’t use the default service in any case!<\/strong>
\nThe recommendation is to have individual services for each task. In our case we will create a service for the application:<\/p>\n
SQL> ALTER SESSION SET CONTAINER=suzanne;\r\n\r\nSQL> execute dbms_service.create_service('appl_suzanne','appl_suzanne');\r\n\r\nSQL> execute dbms_service.start_service('appl_suzanne');\r\n\r\nSQL> ALTER PLUGGABLE DATABASE SAVE STATE;\r\n<\/pre>\n
With the command ALTER PLUGGABLE DATABASE SAVE STATE<\/code> you ensure that the service will be started automatically each time the instance is started. Unfortunately this doesn’t work if you close and open the pluggable database on its own. The application should now only use the service appl_suzanne<\/code>.<\/p>\n
PDB Cloning<\/h3>\n
You can of course copy or clone a PDB. The command is the easiest one:<\/p>\n
SQL> CREATE PLUGGABLE DATABASE marianne FROM suzanne;\r\n\r\nPluggable database created.\r\n\r\nSQL> ALTER PLUGGABLE DATABASE marianne OPEN;\r\n\r\nPluggable database altered.\r\n<\/pre>\n
The result looks fine but there is a problem: The view pdb_plug_in_violations<\/code> will show the details:<\/p>\n
SQL> SELECT name, cause, type message, status FROM pdb_plug_in_violations;\r\n\r\nNAME            CAUSE                          MESSAGE                                            STATUS\r\n--------------- ------------------------------ -------------------------------------------------- ---------\r\nMARIANNE        Service Name Conflict          WARNING                                            PENDING\r\n<\/pre>\n
The reason for this warning is that cloning will clone the service as well. So we need to take an action as explained in the view:<\/p>\n
SQL> SELECT action FROM pdb_plug_in_violations;\r\n\r\nACTION\r\n------------------------------------------------------------\r\nDrop the service and recreate it with an appropriate name.\r\n<\/pre>\n
Let’s go:<\/p>\n
SQL> ALTER SESSION SET CONTAINER=marianne;\r\n\r\nSQL> SELECT name FROM dba_services;\r\n\r\nNAME\r\n---------------\r\nappl_suzanne\r\nMARIANNE\r\n\r\nSQL> execute dbms_service.delete_service('appl_suzanne');\r\n\r\nSQL> execute dbms_service.create_service('appl_marianne','appl_marianne');\r\n\r\nSQL> execute dbms_service.start_service('appl_marianne');\r\n\r\nSQL> ALTER PLUGGABLE DATABASE SAVE STATE;\r\n<\/pre>\n
Limits<\/h3>\n
As said there are some limits comming with XE databases. You cannot create more than three pluggable databases regardless if they are opened or only mounted:<\/p>\n
CREATE PLUGGABLE DATABASE hallelujah\r\n                          *\r\nERROR at line 1:\r\nORA-65010: maximum number of pluggable databases created\r\n<\/pre>\n
The second limitation is the amount of user data with 12 GByte. It doesn’t matter if this data is in one PDB or spreaded across multiple PDBs. While testing this I found that it doesn’t matter if the PDBs are opened or mounted.<\/p>\n
SQL> ALTER PLUGGABLE DATABASE leonard OPEN;\r\nALTER PLUGGABLE DATABASE leonard OPEN \r\n*\r\nERROR at line 1:\r\nORA-12954: The request exceeds the maximum allowed database size of 12 GB.\r\n<\/pre>\n
Saving PDBs<\/h3>\n
But what can you do if a PDB is reaching this border and you need to create an additional PDB? It’s very simple: you close the PDB, create a manifest (that’s an XML-File with the definition of the PDB) and drop the PDB. Dont be afraid for the saved data I’ll explain how to restore it with minimal effort.<\/p>\n
SQL> ALTER PLUGGABLE DATABASE leonard CLOSE IMMEDIATE;\r\nSQL> ALTER PLUGGABLE DATABASE leonard UNPLUG INTO '\/home\/oracle\/leonard.xml';\r\nSQL> DROP PLUGGABLE DATABASE leonard;\r\n<\/pre>\n
Please ensure that you don’t use INCLUDING CONTENTS<\/code> with the command DROP PLUGGABLE DATABASE<\/code> – otherwise your PDB is dropped irreversably. But you have a backup do you?
\nNow you can create new PDBs and use them. If you need the big PDB later on you drop the newly created PDBs and reuse the original one:<\/p>\n
SQL> CREATE PLUGGABLE DATABASE leonard USING '\/home\/oracle\/leonard.xml' NOCOPY;\r\nSQL> ALTER PLUGGABLE DATABASE leonard OPEN;\r\nSQL> ALTER PLUGGABLE DATABASE leonard SAVE STATE;\r\n<\/pre>\n
Conclusion<\/h3>\n
This was the first impression on multitenant database in XE. But the examples will work for Enterprise Edition as well. Without the XE limitations of course.
\nIf you have any questions or comments feel free to write a blog comment or drop an mail at info@carajandb.com<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
As already mentioned in my blog “Oracle 18 XE is available” the\u00a0 XE version includes the multitenant database option. So you can create up to three pluggable databases. But in this case it doesn’t matter if it is an “ordinary” PDB, a Proxy PDB\u00a0 or an Application Root.<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_crdt_document":"","_uag_custom_page_level_css":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[406],"tags":[307,139,218,275,431],"class_list":["post-7046","post","type-post","status-publish","format-standard","hentry","category-oracle-en","tag-multitenant-2","tag-multitenant","tag-pdb","tag-pluggable-database-2","tag-xe-en"],"acf":{"occupation":"Write the Occupation of the Person","person_can-be-speaker":true,"person_is-team":true,"person_related-user-account":null,"person_ordering-number":50,"publication_speakers":null,"publication_content-language":["de"],"publication_files":null,"publication_event":"","publication_date":null},"spectra_custom_meta":{"_vc_post_settings":["a:1:{s:10:\"vc_grid_id\";a:0:{}}"],"_wpml_location_migration_done":["1"],"_yoast_wpseo_content_score":["60"],"layout_header":["light"],"_layout_header":["field_5a48111185e5f"],"layout_background":[""],"_layout_background":["field_5a481193c0995"],"layout_hide_services":["0"],"_layout_hide_services":["field_5a4811c5c0996"],"layout_show-author-box":["1"],"_layout_show-author-box":["field_5a64ee9cc6490"],"layout_show-categories-tags":["1"],"_layout_show-categories-tags":["field_5a64eee4c6491"],"blogpost_related-publications":[""],"_blogpost_related-publications":["field_5a480debdac62"],"blogpost_related-files":[""],"_blogpost_related-files":["field_5a480f377db29"],"_yoast_wpseo_primary_category":["406"],"_edit_lock":["1544789530:3"],"_edit_last":["3"],"rank_math_primary_category":["406"],"rank_math_news_sitemap_robots":["index"],"rank_math_robots":["a:1:{i:0;s:5:\"index\";}"],"rank_math_internal_links_processed":["1"],"astra_style_timestamp_css":["1773726050"],"wpil_sync_report3":["1"],"wpil_links_inbound_internal_count":["0"],"wpil_links_inbound_internal_count_data":["eJxLtDKwqq4FAAZPAf4="],"wpil_links_outbound_internal_count":["2"],"wpil_links_outbound_internal_count_data":["eJzVUbFOwzAQ\/ZXIe+o4bWm4LixsQEfYrIttWlPXjmwHiqr+O3ZSVSBAQkgMbHend+\/dvYdQw0FDtVwBmwO577Tht04qw2+03RJgUzgEWAAxqeVakmUGB5gC6b0hqVqkchNjB5R6ZbC3YlNX9Wwi0OMTWtlOhNtRZWlr3JrWFWuo8yiMKllT7lWpQ4nPqA22RtFMOEt8LsRc1hdAvifNiAaItlF5i+mYFti43w37d6lJBJiWnR\/o0oerQbtgTfFwXehQnLUzIFuAUirJ21femX6tbWatRiHjBEbt7PA1EOGSsB3vrE4GvWx06JTnwiuM6uwWY+nMwLGPLsPej2O6Z6vt+p23ebxzsjeKf0DXJ5FBeB\/H+VGnp38d3vzyJ+HtkkXRgbaP7upTAn8XFxvQX2n+x5yObz34HFY="],"wpil_links_outbound_external_count":["0"],"wpil_links_outbound_external_count_data":["eJxLtDKwqq4FAAZPAf4="],"wpil_sync_report2_time":["2024-08-27T11:47:24+00:00"],"_uag_css_file_name":["uag-css-7046.css"]},"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false},"uagb_author_info":{"display_name":"Johannes Ahrends","author_link":"https:\/\/carajandb.com\/en\/author\/9aa6cdb2095bd409\/"},"uagb_comment_info":4,"uagb_excerpt":"As already mentioned in my blog “Oracle 18 XE is available” the\u00a0 XE version includes the multitenant database option. So you can create up to three pluggable databases. But in this case it doesn’t matter if it is an “ordinary” PDB, a Proxy PDB\u00a0 or an Application Root.","_links":{"self":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts\/7046","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/comments?post=7046"}],"version-history":[{"count":4,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts\/7046\/revisions"}],"predecessor-version":[{"id":7089,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts\/7046\/revisions\/7089"}],"wp:attachment":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/media?parent=7046"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/categories?post=7046"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/tags?post=7046"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}