Oracle Standby Database Parameters in PDB

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'

Related Posts

Leave a Reply

Your email address will not be published.