.
\nIn multitenant, the spfile parameters at pluggable database level are not stored in the spfile but in the CDB$ROOT table SYS.PDB_SPFILE$
\nIf you open the standby database in read only for reporting, which is one reason to get Active Data Guard option, you may want different optimizer settings suited for reporting. In non-CDB it’s easy: the standby has its own spfile where parameters can be changed. But for a pluggable database, it’s stored in a table which is impossible to update in a read-only database, but there’s a solution.
\n
\nLet’s say I’m not a big fan of Adaptive Dynamic Sampling for my OLTP application and set the optimizer_dynamic_sampling to 0 in my PDB:<\/p>\n
\nSQL> alter session set container=PDB001;\nSession altered.\nSQL> alter system set optimizer_dynamic_sampling=0 scope=spfile;\nSystem altered.\nSQL> show spparameter optimizer_dynamic_sampling;\nSID NAME TYPE VALUE\n-------- ----------------------------- ----------- -------------------------\n* optimizer_dynamic_sampling integer 0\n<\/code><\/pre>\nAs I said this is stored in the CDB$ROOT:<\/p>\n
\nSQL> select db_uniq_name,pdb_uid,sid,name,value$ from pdb_spfile$;\nDB_UNIQ PDB_UID SID NAME VALUE\n------- ---------- --- -------------------------- -----\nCDB 4058593923 * optimizer_dynamic_sampling 0\n<\/code><\/pre>\nInteresting things here. First, the pluggable database is identified by its PDB_UID which do not change on unplug\/plug rather than the CON_ID which is related to the CDB.
\nSecond there is a db_unique_name here which is the one of the CDB<\/p>\n
Ignored in Standby<\/h3>\n
Now in my standby, which value do I have?<\/p>\n
\nSQL> alter session set container=PDB001;\nSession altered.\nSQL> show parameter optimizer_dynamic_sampling;\nNAME TYPE VALUE\n------------------------------------ ----------- ------------------------------\noptimizer_dynamic_sampling integer 2\n<\/code><\/pre>\nThis is the default value. Actually, no spfile parameter is set in the standby:<\/p>\n
\nSQL> show spparameter optimizer_dynamic_sampling;\nSID NAME TYPE VALUE\n-------- ----------------------------- ----------- ----------------------------\n* optimizer_dynamic_sampling integer\n<\/code><\/pre>\nThat’s not bad here but I want more. I want to set the level to 8 for my reporting activity here.
\nIf I want to set it I’ll get: “ORA-65099: Operation cannot be performed when the CDB is not open” because it’s stored in the PDB_SPFILE$ table and we cannot update a table in a read-only database. I can change the value at CDB level, but this is not what I want to do. Let’s have a look at that PDB_SPFILE$ in the standby:<\/p>\n
\nSQL> select db_uniq_name,pdb_uid,sid,name,value$ from pdb_spfile$;\nDB_UNIQ PDB_UID SID NAME VALUE\n------- ---------- --- -------------------------- -----\nCDB 4058593923 * optimizer_dynamic_sampling 0\n<\/code><\/pre>\nOf course, the table is replicated. It’s the same than in the primary database. This is where the DB_UNIQ_NAME columns is used: the parameter is not used here because the standby CDB has a different unique name.<\/p>\n
Change in Standby<\/h3>\n
So how to change the parameter in the standby? Just change it in the primary but specify which db_unique_name it belongs to.
\nBack in the primary:<\/p>\n
\nSQL> alter session set container=PDB001;\nSession altered.\nSQL> alter system set optimizer_dynamic_sampling=8 scope=spfile db_unique_name='CDB_ADG';\nSystem altered.\nSQL> alter session set container=CDB$ROOT;\nSession altered.\nSQL> select db_uniq_name,pdb_uid,sid,name,value$ from pdb_spfile$;\nDB_UNIQ PDB_UID SID NAME VALUE\n------- ---------- --- -------------------------- -----\nCDB 4058593923 * optimizer_dynamic_sampling 0\nCDB_ADG 4058593923 * optimizer_dynamic_sampling 8\n<\/code><\/pre>\nThis is perfect. The database has both entries, and when in the standby I open the PDB read only it get the right value:<\/p>\n
\nSQL> show spparameter optimizer_dynamic_sampling;\nSID NAME TYPE VALUE\n-------- ----------------------------- ----------- ----------------------------\n* optimizer_dynamic_sampling integer 8\n<\/code><\/pre>\nReset<\/h3>\n
If I want to remove a parameter from spfile, I can use the ALTER SYSTEM RESET for it. If I want to remove it on the primary, then no problem no need to specify the db_unique_name as the current one is the default. But let’s try to reset the one for the standby:<\/p>\n
\nSQL> alter session set container=PDB001;\nSession altered.\nSQL> alter system reset optimizer_dynamic_sampling scope=spfile db_unique_name='CDB_ADG';\nSystem altered.\n<\/code><\/pre>\nBut there’s a bug. The db_unique_name is ignored so the wrong one has been removed:<\/p>\n
\nSQL> alter session set container=CDB$ROOT;\nSession altered.\nSQL> select db_uniq_name,pdb_uid,sid,name,value$ from pdb_spfile$;\nDB_UNIQ PDB_UID SID NAME VALUE\n------- ---------- --- -------------------------- -----\nCDB_ADG 4058593923 * optimizer_dynamic_sampling 8\n<\/code><\/pre>\nIf I try it again, I’ll get a “ORA-32010: cannot find entry to delete in SPFILE”<\/p>\n
Bug<\/h3>\n
This is a bug and I’ll put the bug number as soon as My Oracle Support engineer dares to reproduce that 3 lines test-case.<\/p>\n
Workaround<\/h3>\n
The workaround is easy. It’s stored in a table so you can just remove the row and commit. I’ve sql_traced it, it’s just a delete except that it uses the wrong db_unique_name. That trace is my Plan B to get a bug opened for that. The 4 lines test case in case of the 3 lines one is not sufficient…<\/p>\n
Documentation<\/h3>\n
You don’t find that db_unique_name clause in the ALTER SYSTEM documentation. However, it’s documented in MOS Doc ID 2101638.1
\nThe reset is not documented at all, but there’s no reason to have a SET command without a RESET. Ok, I know for scope=memory you have a SET without RESET, but that’s another problem. And Enhancement Request to fill.<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . In multitenant, the spfile parameters at pluggable database level are not stored in the spfile but in the CDB$ROOT table SYS.PDB_SPFILE$ If you open the standby database in read only for reporting, which is one reason to get Active Data Guard option, you may want different optimizer settings suited for reporting. […]<\/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":[229],"tags":[220,221,64,96,209,66,223,238],"type_dbi":[],"class_list":["post-7396","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-cdb","tag-data-guard","tag-multitenant","tag-oracle","tag-oracle-12c","tag-pdb","tag-pluggable-databases","tag-standby"],"acf":[],"yoast_head":"\n
12c Multitenant: PDB spfile parameters for standby database - 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\/12c-multitenant-pdb-spfile-parameters-for-standby-database\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"12c Multitenant: PDB spfile parameters for standby database\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . In multitenant, the spfile parameters at pluggable database level are not stored in the spfile but in the CDB$ROOT table SYS.PDB_SPFILE$ If you open the standby database in read only for reporting, which is one reason to get Active Data Guard option, you may want different optimizer settings suited for reporting. […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/12c-multitenant-pdb-spfile-parameters-for-standby-database\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-03-16T20:04:20+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=\"5 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\/12c-multitenant-pdb-spfile-parameters-for-standby-database\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12c-multitenant-pdb-spfile-parameters-for-standby-database\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"12c Multitenant: PDB spfile parameters for standby database\",\"datePublished\":\"2016-03-16T20:04:20+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12c-multitenant-pdb-spfile-parameters-for-standby-database\/\"},\"wordCount\":646,\"commentCount\":0,\"keywords\":[\"CDB\",\"Data Guard\",\"multitenant\",\"Oracle\",\"Oracle 12c\",\"PDB\",\"Pluggable Databases\",\"Standby\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/12c-multitenant-pdb-spfile-parameters-for-standby-database\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12c-multitenant-pdb-spfile-parameters-for-standby-database\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/12c-multitenant-pdb-spfile-parameters-for-standby-database\/\",\"name\":\"12c Multitenant: PDB spfile parameters for standby database - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-03-16T20:04:20+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12c-multitenant-pdb-spfile-parameters-for-standby-database\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/12c-multitenant-pdb-spfile-parameters-for-standby-database\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12c-multitenant-pdb-spfile-parameters-for-standby-database\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"12c Multitenant: PDB spfile parameters for standby database\"}]},{\"@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":"12c Multitenant: PDB spfile parameters for standby database - 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\/12c-multitenant-pdb-spfile-parameters-for-standby-database\/","og_locale":"en_US","og_type":"article","og_title":"12c Multitenant: PDB spfile parameters for standby database","og_description":"By Franck Pachot . In multitenant, the spfile parameters at pluggable database level are not stored in the spfile but in the CDB$ROOT table SYS.PDB_SPFILE$ If you open the standby database in read only for reporting, which is one reason to get Active Data Guard option, you may want different optimizer settings suited for reporting. […]","og_url":"https:\/\/www.dbi-services.com\/blog\/12c-multitenant-pdb-spfile-parameters-for-standby-database\/","og_site_name":"dbi Blog","article_published_time":"2016-03-16T20:04:20+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/12c-multitenant-pdb-spfile-parameters-for-standby-database\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/12c-multitenant-pdb-spfile-parameters-for-standby-database\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"12c Multitenant: PDB spfile parameters for standby database","datePublished":"2016-03-16T20:04:20+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/12c-multitenant-pdb-spfile-parameters-for-standby-database\/"},"wordCount":646,"commentCount":0,"keywords":["CDB","Data Guard","multitenant","Oracle","Oracle 12c","PDB","Pluggable Databases","Standby"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/12c-multitenant-pdb-spfile-parameters-for-standby-database\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/12c-multitenant-pdb-spfile-parameters-for-standby-database\/","url":"https:\/\/www.dbi-services.com\/blog\/12c-multitenant-pdb-spfile-parameters-for-standby-database\/","name":"12c Multitenant: PDB spfile parameters for standby database - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-03-16T20:04:20+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/12c-multitenant-pdb-spfile-parameters-for-standby-database\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/12c-multitenant-pdb-spfile-parameters-for-standby-database\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/12c-multitenant-pdb-spfile-parameters-for-standby-database\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"12c Multitenant: PDB spfile parameters for standby database"}]},{"@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\/7396","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=7396"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7396\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=7396"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=7396"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=7396"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=7396"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}