12C DB on RHEL 6.5
From within pluggable DB, I managed to change a dynamic parameter (JOB_QUEUE_PROCESSES) as shown below
But, when I tried to change a static parameter (PROCESSES) , I cannot do that from within PDB. I can only do that from root container level which will get reflected in all PDBs. But the Dynamic parameter can be independantly set for PDBs. Is this the expected behaviour or is there a way to set a different value for a static parameter for a particular PDB ?
$ sqlplus pdb_admin/tiger123@LGMPDB
SQL*Plus: Release 12.1.0.2.0 Production on Sat Aug 29 20:58:57 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Sat Aug 29 2015 20:58:16 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show con_name
CON_NAME
------------------------------
LGMPDB
SQL>
SQL>
SQL> show parameter job_q
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 25
SQL>
SQL>
SQL> alter system set job_queue_processes=30 scope=both;
System altered.
SQL>
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 30
log_archive_max_processes integer 4
processes integer 200
SQL>
SQL> alter system set processes=300 scope=spfile;
alter system set processes=300 scope=spfile
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
SQL> alter system set processes=300 scope=spfile container= current;
alter system set processes=300 scope=spfile container= current
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
$
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sat Aug 29 21:02:28 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 50
log_archive_max_processes integer 4
processes integer 200
SQL>
SQL> alter system set processes=300 scope=spfile container = all;
alter system set processes=300 scope=spfile container = all
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM
SQL> alter system set processes=300 scope=spfile;
System altered.
After a bounce of CDB (and PDBs) i can see that the PROCESSES parameter has been changed to 300 in CDB and all PDBs.
But, the dynamic parameter JOB_QUEUE_PROCESSES is 50 in CDB and 30 in the pluggable DB !