{"id":4260,"date":"2015-01-05T20:10:00","date_gmt":"2015-01-05T19:10:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-multitenant-dictionary-upgrade\/"},"modified":"2015-01-05T20:10:00","modified_gmt":"2015-01-05T19:10:00","slug":"oracle-multitenant-dictionary-upgrade","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-multitenant-dictionary-upgrade\/","title":{"rendered":"Oracle multitenant dictionary: upgrade"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nThis is a second part of the previous post<\/a> about metadata link. I’ve shown how a sharing=metadata function becomes a sharing=none function when it is changed in the pdb – i.e when not having the same DDL, not having a different signature.<\/p>\n

Here is another experimentation doing the opposite: change the function in root and see what happens in the pdb. Again playing with internals in order to understand the ‘upgrade by unplug-plug’ feature available in 12c multi-tenant (and single-tenant).<\/p>\n

Create shared function<\/h3>\n

I’m doing the same as in the previous post:<\/p>\n

SQL> alter session set \"_oracle_script\"=true;\nSession altered.\n\nSQL> alter session set container=cdb$root;\nSession altered.\n\nSQL> show con_name\n\nCON_NAME\n------------------------------\nCDB$ROOT\n\nSQL> create function DEMO_FUNCTION1 sharing=metadata return varchar2 as begin return 'demo'; end;\n  2  \/\nFunction created.\n\nSQL> create function DEMO_FUNCTION0 sharing=metadata return varchar2 as begin return DEMO_FUNCTION1; end;\n  2  \/\nFunction created.\n\nSQL> grant execute on DEMO_FUNCTION0 to public;\nGrant succeeded.\n<\/code><\/pre>\n
now in the pdb:<\/code><\/pre>\n
SQL> alter session set container=pdb1;\nSession altered.\n\nSQL> create function DEMO_FUNCTION1 sharing=metadata return varchar2 as begin return 'demo'; end;\n  2  \/\nFunction created.\n\nSQL> create function DEMO_FUNCTION0 sharing=metadata return varchar2 as begin return DEMO_FUNCTION1; end;\n  2  \/\nFunction created.<\/code><\/pre>\n
Check multitenant dictionary<\/h3>\n
First in root<\/strong>:<\/p>\n
SQL> alter session set container=cdb$root;\nSession altered.\n\nSQL> select obj#,name,status,flags,decode(bitand(flags,196608),65536,'MDL',131072,'OBL','NONE') sharing,signature from obj$ where name like 'DEMO_FUNCTION_';\n\n OBJ# NAME           STATUS   FLAGS SHAR SIGNATURE\n----- -------------- ------ ------- ---- --------------------------------\n92370 DEMO_FUNCTION0      1 5308416 MDL  ECE1890DECB2F100A2F755CC0B76166D\n92369 DEMO_FUNCTION1      1 5308416 MDL  0BCDC8C960AD833F95F0595EEBB70745\n<\/code><\/pre>\n
And in the pdb<\/strong>:<\/p>\n
SQL> alter session set container=pdb1;\nSession altered.\n\nSQL> select obj#,name,status,flags,decode(bitand(flags,196608),65536,'MDL',131072,'OBL','NONE') sharing,signature from obj$ where name like 'DEMO_FUNCTION_';\n\n OBJ# NAME           STATUS   FLAGS SHAR SIGNATURE\n----- -------------- ------ ------- ---- --------------------------------\n91829 DEMO_FUNCTION0      1 5308416 MDL  ECE1890DECB2F100A2F755CC0B76166D\n91828 DEMO_FUNCTION1      1 5308416 MDL  0BCDC8C960AD833F95F0595EEBB70745\n<\/code><\/pre>\n
Nothing special here: different object_id but same signature and sharing=metadata.<\/p>\n
Upgrade the root<\/h3>\n
I’m now ‘upgrading’ the function DEMO_FUNCTION1 in the root<\/strong>:<\/p>\n
SQL> alter session set container=cdb$root;\nSession altered.\n\nSQL> create or replace function DEMO_FUNCTION1 sharing=metadata return varchar2 as begin return 'demo v2'; end;\n  2  \/\nFunction created.\n<\/code><\/pre>\n
and nothing in the pdb<\/strong><\/p>\n
Here is what is stored in both container dictionaries:<\/p>\n
SQL> alter session set container=cdb$root;\nSession altered.\n\nSQL> select obj#,name,status,flags,decode(bitand(flags,196608),65536,'MDL',131072,'OBL','NONE') sharing,signature from obj$ where name like 'DEMO_FUNCTION_';\n\n OBJ# NAME           STATUS      FLAGS SHAR SIGNATURE\n----- -------------- ------ ---------- ---- --------------------------------\n92370 DEMO_FUNCTION0      1    5308416 MDL  ECE1890DECB2F100A2F755CC0B76166D\n92369 DEMO_FUNCTION1      1    5308416 MDL  9772AA08AFD3F9F90BC9BDB9DE35A1F8\n\nSQL> select * from source$ where obj# in (select obj# from obj$ where name like 'DEMO_FUNCTION_');\n\n OBJ# LINE SOURCE\n----- ---- --------------------------------------------------\n92370    1 function DEMO_FUNCTION0                  return va\n           rchar2 as begin return DEMO_FUNCTION1; end;\n\n92369    1 function DEMO_FUNCTION1                  return va\n                      rchar2 as begin return 'demo v2'; end;\nSQL> alter session set container=pdb1;\nSession altered.\n\nSQL> select obj#,name,status,flags,decode(bitand(flags,196608),65536,'MDL',131072,'OBL','NONE') sharing,signature from obj$ where name like 'DEMO_FUNCTION_';\n\n OBJ# NAME           STATUS      FLAGS SHAR SIGNATURE\n----- -------------- ------ ---------- ---- --------------------------------\n91829 DEMO_FUNCTION0      1    5308416 MDL  ECE1890DECB2F100A2F755CC0B76166D\n91828 DEMO_FUNCTION1      1    5308416 MDL  0BCDC8C960AD833F95F0595EEBB70745\n\nSQL> select * from source$ where obj# in (select obj# from obj$ where name like 'DEMO_FUNCTION_');\n\nno rows selected\n<\/code><\/pre>\n
Ok. In the root<\/strong> the function has changed, and has a new signature. But nothing changed in the pdb<\/strong>. The DDL you did in the root<\/strong> has changed only the root<\/strong> dictionary.<\/p>\n
Then, what happens if we execute the function from the pdb<\/strong>?<\/p>\n
SQL> alter session set container=cdb$root;\nSession altered.\n\nSQL> select DEMO_FUNCTION1 from dual;\n\nDEMO_FUNCTION1\n--------------------------------------------\ndemo v2\n\nSQL> alter session set container=pdb1;\nSession altered.\n\nSQL> select DEMO_FUNCTION1 from dual;\n\nDEMO_FUNCTION1\n--------------------------------------------\ndemo\n<\/code><\/pre>\n
The pdb<\/strong> function returns the old value. Do you think it’s an expected result because pdb<\/strong> has not been upgraded? No. we have a problem here. Look at the above queries on SOURCE$: the code that returns ‘demo’ is not stored anywhere.<\/p>\n
When we run something that is not stored anywhere, it probably comes from a cache invalidation that did not occur. Let’s flush the shared pool:<\/p>\n
SQL> alter session set container=cdb$root;\nSession altered.\n\nSQL> alter system flush shared_pool;\nSystem altered.\n\nSQL> alter session set container=pdb1;\nSession altered.\n\nSQL> select DEMO_FUNCTION1 from dual;\n\nDEMO_FUNCTION1\n--------------------------------------------\ndemo v2\n<\/code><\/pre>\n
Good. We see the upgraded version now. We didn’t have anything to do in the pdb<\/strong>. The metadata link is still use and this is how ‘upgrade by unplug-plug’ works: when the upgrade concerns only sharing=metadata oracle objects that are stored only in root<\/strong>, then the pdb<\/strong> is automatically upgraded as it follows the links. This is how we can apply quickly a patch or a PSU: plug it into a CDB that already has the patch applied.<\/p>\n
So, what happened to the signature?<\/p>\n
SQL> select obj#,name,status,flags,decode(bitand(flags,196608),65536,'MDL',131072,'OBL','NONE') sharing,signature from obj$ where name like 'DEMO_FUNCTION_';\n\n OBJ# NAME           STATUS      FLAGS SHAR SIGNATURE\n----- -------------- ------ ---------- ---- --------------------------------\n91829 DEMO_FUNCTION0      5    5308416 MDL  ECE1890DECB2F100A2F755CC0B76166D\n91828 DEMO_FUNCTION1      1    5308416 MDL  9772AA08AFD3F9F90BC9BDB9DE35A1F8\n\nSQL> select * from source$ where obj# in (select obj# from obj$ where name like 'DEMO_FUNCTION_');\n\nno rows selected\n\n<\/code><\/pre>\n
The function has been recompiled when it was executed. It has never been invalidated (and that’s why we had to flush the shared pool) but the load which has followed the metadata link has detected that the signature is not the same and has recompiled it. The signature is the same. And dependent objects are invalidated (they will be recompiled when executed).<\/p>\n
This is a correct behaviour except the fact that we had to flush the shared pool. Anyway, that occurs only when we upgrade the root<\/strong> which should flush the shared pool or when we plug a new pdb<\/strong> which has nothing yet in shared pool.<\/p>\n
However, when you upgrade from 12.1.0.1 to 12.1.0.2 there are a lot of changes and a lot of dependencies and updating the signatures is not sufficient. The datapatch script has to update our application metadata, which is stored into the pdb<\/strong> dictionary. And this is why ‘upgrade by unplug-plug’ is not a lot faster than a non-CDB upgrade. But that may be improved in future versions.<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . This is a second part of the previous post about metadata link. I’ve shown how a sharing=metadata function becomes a sharing=none function when it is changed in the pdb – i.e when not having the same DDL, not having a different signature. Here is another experimentation doing the opposite: change 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,455,64,96,209,66,223],"type_dbi":[],"class_list":["post-4260","post","type-post","status-publish","format-standard","hentry","category-database-management","category-oracle","tag-cdb","tag-internals","tag-multitenant","tag-oracle","tag-oracle-12c","tag-pdb","tag-pluggable-databases"],"acf":[],"yoast_head":"\nOracle multitenant dictionary: upgrade - 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\/oracle-multitenant-dictionary-upgrade\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle multitenant dictionary: upgrade\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . This is a second part of the previous post about metadata link. I’ve shown how a sharing=metadata function becomes a sharing=none function when it is changed in the pdb – i.e when not having the same DDL, not having a different signature. Here is another experimentation doing the opposite: change the […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-multitenant-dictionary-upgrade\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-01-05T19:10:00+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=\"6 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\/oracle-multitenant-dictionary-upgrade\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-multitenant-dictionary-upgrade\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Oracle multitenant dictionary: upgrade\",\"datePublished\":\"2015-01-05T19:10:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-multitenant-dictionary-upgrade\/\"},\"wordCount\":524,\"commentCount\":0,\"keywords\":[\"CDB\",\"internals\",\"multitenant\",\"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\/oracle-multitenant-dictionary-upgrade\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-multitenant-dictionary-upgrade\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/oracle-multitenant-dictionary-upgrade\/\",\"name\":\"Oracle multitenant dictionary: upgrade - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2015-01-05T19:10:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-multitenant-dictionary-upgrade\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-multitenant-dictionary-upgrade\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-multitenant-dictionary-upgrade\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle multitenant dictionary: upgrade\"}]},{\"@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":"Oracle multitenant dictionary: upgrade - 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\/oracle-multitenant-dictionary-upgrade\/","og_locale":"en_US","og_type":"article","og_title":"Oracle multitenant dictionary: upgrade","og_description":"By Franck Pachot . This is a second part of the previous post about metadata link. I’ve shown how a sharing=metadata function becomes a sharing=none function when it is changed in the pdb – i.e when not having the same DDL, not having a different signature. Here is another experimentation doing the opposite: change the […]","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-multitenant-dictionary-upgrade\/","og_site_name":"dbi Blog","article_published_time":"2015-01-05T19:10:00+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-multitenant-dictionary-upgrade\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-multitenant-dictionary-upgrade\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Oracle multitenant dictionary: upgrade","datePublished":"2015-01-05T19:10:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-multitenant-dictionary-upgrade\/"},"wordCount":524,"commentCount":0,"keywords":["CDB","internals","multitenant","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\/oracle-multitenant-dictionary-upgrade\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-multitenant-dictionary-upgrade\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-multitenant-dictionary-upgrade\/","name":"Oracle multitenant dictionary: upgrade - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2015-01-05T19:10:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-multitenant-dictionary-upgrade\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-multitenant-dictionary-upgrade\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-multitenant-dictionary-upgrade\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle multitenant dictionary: upgrade"}]},{"@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\/4260","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=4260"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4260\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=4260"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=4260"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=4260"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=4260"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}