In multi-tenant Active Data Guard database configuration, setting PDB parameters for standby PDB is not trivial. I discover this when we opened the Standby database in read only mode and resource manager plan was not set. I used Franck Pachot blog post to resolve my issue. It made me realize that other parameters may not be set.
In this blog post I’ll demonstrate setting a parameter in read only standby PDB.
In Primary PDB star_transformation_enabled is set to temp_disable. But in standby it is not set.
Primary:
SQL> set pages 10000 lines 130
SQL> select sysdate from dual;
SYSDATE
---------
01-DEC-20
SQL> select *
from table(DBMS_XPLAN.DISPLAY_CURSOR(format=>'ADVANCED')) t;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
SQL_ID 7h35uxf5uhmm1, child number 1
------------------------------------
select sysdate from dual
Plan hash value: 308129442
---------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)| |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
---------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / DUAL@SEL$1
Outline Data
------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
OPT_PARAM('optimizer_dynamic_sampling' 6)
OPT_PARAM('_optimizer_gather_stats_on_load' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
OPT_PARAM('star_transformation_enabled' 'temp_disable')
ALL_ROWS
NO_PARALLEL
OUTLINE_LEAF(@"SEL$1")
END_OUTLINE_DATA
*/
On standby database star_transformation_enabled is missing
SQL> select sysdate from dual;
SYSDATE
---------
01-DEC-20
SQL> select *
from table(DBMS_XPLAN.DISPLAY_CURSOR(format=>'ADVANCED')) t;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------
SQL_ID 7h35uxf5uhmm1, child number 1
------------------------------------
select sysdate from dual
Plan hash value: 1388734953
---------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100) | |
| 1 | FAST DUAL | | 1 | 2 (0) | 00:00:01 |
---------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / DUAL@SEL$1
Outline Data
------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
OPT_PARAM('optimizer_dynamic_sampling' 6)
OPT_PARAM('_optimizer_gather_stats_on_load' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
ALL_ROWS
NO_PARALLEL
OUTLINE_LEAF(@"SEL$1")
END_OUTLINE_DATA
*/
If you try to set the parameter in standby, you’ll get following error
SQL> alter system set star_transformation_enabled=TEMP_DISABLE; alter system set star_transformation_enabled=TEMP_DISABLE * ERROR at line 1: ORA-32000: write to SPFILE requested but SPFILE is not modifiable
In Primary database run the following to change parameter in CDB.
SQL> alter session set container = pdb1; Session altered. SQL> alter system set star_transformation_enabled=TEMP_DISABLE scope=both db_unique_name='cdb1s'; alter system set star_transformation_enabled=TEMP_DISABLE scope=both db_unique_name='cdb1s' * ERROR at line 1: ORA-65147: DB_UNIQUE_NAME specified without SPFILE scope SQL> alter system set star_transformation_enabled=TEMP_DISABLE scope=spfile db_unique_name='cdb1s'; System altered.
View SP Parameter in standby database
SYS@CDB$ROOT>sho spparameter star_trans
SID NAME TYPE VALUE
--- --------------------------- ------ ------------
star_transformation_enabled string TEMP_DISABLE
Current parameter setting in standby database
SYS@CDB$ROOT>sho parameter star_trans NAME TYPE VALUE --------------------------- ------ ----- star_transformation_enabled string FALSE
You can check pdb_spfile$ view to confirm the change.
SYS@CDB$ROOT>select db_uniq_name,pdb_uid,sid,name,value$ from pdb_spfile$ where name = 'star_transformation_enabled'; DB_UNIQ_NAME PDB_UID SID NAME VALUE$ ------------ ---------- --- --------------------------- ------------------ cdb1 1417277324 * star_transformation_enabled 'TEMP_DISABLE' cdb1s 1417277324 * star_transformation_enabled 'TEMP_DISABLE'
