log_archive_dest_?? is ISPDB_MODIFIABLE?

In Oracle 12c, some of the parameters can be changed in a pluggable database if the impact is limited to the current container.   However, the global parameters like SGA_TARGET can be set only through root container – CDB$ROOT.    A new column ISPDB_MODIFIABLE is added  in V$PARAMETER view to list the parameters modifiable from a PDB.

SQL> select count(*) from  v$parameter where ISPDB_MODIFIABLE='TRUE';

COUNT(*)

----------

171

There are 171 parameters listed to be modifiable from a PDB. Looking at the parameters closely, I was interested in the flowing parameters.

SQL> select name from v$parameter where name like '%log_archive%'  and ISPDB_MODIFIABLE='TRUE';

NAME

--------------------------------------------------------------------------------

log_archive_dest_1

log_archive_dest_2

log_archive_dest_3

log_archive_dest_4

log_archive_dest_5

log_archive_dest_6

log_archive_dest_7

log_archive_dest_8

log_archive_dest_9

log_archive_dest_10

log_archive_dest_11

log_archive_dest_12

log_archive_dest_13

log_archive_dest_14

log_archive_dest_15

log_archive_dest_16

log_archive_dest_17

log_archive_dest_18

log_archive_dest_19

log_archive_dest_20

log_archive_dest_21

log_archive_dest_22

log_archive_dest_23

log_archive_dest_24

log_archive_dest_25

log_archive_dest_26

log_archive_dest_27

log_archive_dest_28

log_archive_dest_29

log_archive_dest_30

log_archive_dest_31

log_archive_dest_state_1

log_archive_dest_state_2

log_archive_dest_state_3

log_archive_dest_state_4

log_archive_dest_state_5

log_archive_dest_state_6

log_archive_dest_state_7

log_archive_dest_state_8

log_archive_dest_state_9

log_archive_dest_state_10

log_archive_dest_state_11

log_archive_dest_state_12

log_archive_dest_state_13

log_archive_dest_state_14

log_archive_dest_state_15

log_archive_dest_state_16

log_archive_dest_state_17

log_archive_dest_state_18

log_archive_dest_state_19

log_archive_dest_state_20

log_archive_dest_state_21

log_archive_dest_state_22

log_archive_dest_state_23

log_archive_dest_state_24

log_archive_dest_state_25

log_archive_dest_state_26

log_archive_dest_state_27

log_archive_dest_state_28

log_archive_dest_state_29

log_archive_dest_state_30

log_archive_dest_state_31

log_archive_min_succeed_dest

63 rows selected.

Above 63 parameters said to be modifiable from a PDB, but it is contrary to the definition – only a PDB affected parameter can be modified.   Above parameters are global parameters which will affect  in the stance level as oracle is sharing a single set of redo log files for all PDBs.    So, this is not making senesce that we can modify the parameter from a PDB.  I tried to change the log_archive_dest_state_1 to defer from a PDB, but failed.

SQL> show con_name

CON_NAME

------------------------------

SALESDB

SQL> alter system set log_archive_dest_state_1=defer;

alter system set log_archive_dest_state_1=defer

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-65040: operation not allowed from within a pluggable database

Then I tried to change the value for the parameter  log_archive_dest_1 from a PDB which got failed agaian.  And I was able to change from the root container.

SQL> show con_name

CON_NAME

------------------------------

SALESDB

SQL> alter system set log_archive_dest_1= 'location=/n01/oradata1/12cDB/arch1' container=current ;

alter system set log_archive_dest_1= 'location=/n01/oradata1/12cDB/arch1' container=current

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-65040: operation not allowed from within a pluggable database

SQL> conn / as sysdba

Connected.

SQL> alter system set log_archive_dest_1= 'location=/n01/oradata1/12cDB/arch1' scope=both;

System altered.

So, my assumption is correct – even though these parameters are listed as modifiable, you can’t really modify them.  Had a quick search in Oracle Support came up a bug note:  17266585 for wrongly listed above parameters are modifiable, they are not.