Pickleball spielen

In a Multitenant Database, the handling of initialization parameters has changed. This post shows some major differences. The playing field:

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jan 14 21:44:10 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>  select name,open_mode,con_id from v$pdbs;

NAME                           OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED                       READ ONLY           2
PDB1                           MOUNTED             3

Apart from the mandatory pluggable seed database, there is one pluggable database which is presently not opened. Can I modify initialization parameters for pdb1 now?

SQL> alter session set container=pdb1;

Session altered.

SQL> alter system set ddl_lock_timeout=30;
alter system set ddl_lock_timeout=30
*
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only

SQL> alter system set ddl_lock_timeout=30 scope=spfile;

System altered.

In spite of the syntax, this did not modify the spfile:

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/12.1.0
                                                 /dbhome_1/dbs/spfilecdb1.ora
SQL> host strings /u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfilecdb1.ora
cdb1.__data_transfer_cache_size=0
cdb1.__db_cache_size=251658240
cdb1.__java_pool_size=4194304
cdb1.__large_pool_size=4194304
cdb1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
cdb1.__pga_aggregate_target=167772160
cdb1.__sga_target=503316480
cdb1.__shared_io_pool_size=20971520
cdb1.__shared_pool_size=209715200
cdb1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/cdb1/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0'
*.control_files='/u01/app/oracl
e/oradata/cdb1/control01.ctl','/u01/app/oracle/fast_recovery_area/cdb1/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='cdb1'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4800m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cdb1XDB)'
*.enable_pluggable_database=true
*.open_cursors=300
*.pga_aggregate_target=160m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=480m
*.undo_tablesp
ace='UNDOTBS1'

Initialization parameters for pluggable databases are not kept in the spfile – if they are not inherited from the container database, that is. Instead, they are stored in a dictionary table of the container database. The documented view V$SYSTEM_PARAMETER displays them. But only if the pluggable database is opened:

SQL> connect / as sysdba
Connected.
SQL> select name,value,con_id from v$system_parameter where name='ddl_lock_timeout';

NAME                           VALUE                                    CON_ID
------------------------------ ---------------------------------------- ------
ddl_lock_timeout               0                                             0

SQL> select a.name,value$,con_id from pdb_spfile$ a join v$pdbs b on (a.pdb_uid=b.con_uid);

NAME                           VALUE$                                   CON_ID
------------------------------ ---------------------------------------- ------
ddl_lock_timeout               30                                            3

The undocumented table PDB_SPFILE$ is internally queried and the parameters are set accordingly when the pluggable database is opened:

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> select name,value,con_id from v$system_parameter where name='ddl_lock_timeout';

NAME                           VALUE                                    CON_ID
------------------------------ ---------------------------------------- ------
ddl_lock_timeout               0                                             0
ddl_lock_timeout               30                                            3

Attention, show parameter displays different results, depending on the current container now:

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show parameter ddl_lock_timeout

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0

SQL> alter session set container=pdb1;

Session altered.

SQL> show parameter ddl_lock_timeout

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     30

Upon unplugging the pluggable database, an xml file is generated, containing the description of the pluggable database. I thought that this file contains also the modified initialization parameters of that pluggable database, but that doesn’t seem to be the case. I checked it for the parameters DDL_LOCK_TIMEOUT and RESUMABLE_TIMEOUT and both do not show in the xml file. Instead, they got stored in the system tablespace of the unplugged database! Again, that is not documented and may change in the future.

Isn’t that fun to play with this exciting new stuff? At least I enjoyed it, and I hope you found it useful 🙂