oracle 12c database - pluggable database and spfile/parameter - how does that work?

In trying to understand more about how pluggable databases in 12c are working I've been looking at how some of the fundamentals of how the database works have been engineered in the new version. I've already looked at how users are handled in an earlier post and now I've turned my attention to database parameters. Can they changed for a PDB? How is that stored? What happens when you plug/unplug?

 Here are the steps i went through to try and see what is going on (I'm using s as an alias for sqlplus / as sysdba).

OK first lets log on and see what we have:

[oracle@server-name]:ED12G:[~]# s

SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 5 09:03:54 2013

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> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORA12GCONTAINER                READ WRITE NO
         4 TEST                           READ WRITE NO



 Right - now i want to change a parameter for just one of those containers - how do i do that - lets try this:


SQL> alter system set open_cursors=555 container=test;
alter system set open_cursors=555 container=test
                                            *
ERROR at line 1:
ORA-65013: invalid CONTAINER clause


OK no luck there
Lets try doing it the way other parts of the system seem to be configured and switch to the container we want to change:


SQL> alter session set container=test;

Session altered.



Can I now change a parameter?

SQL>  alter system set open_cursors=555 container=current;

System altered.



So now i expect that this value will be written to the spfile of the 'main' container database? It will just be formatted in a 'special' way with db.instance.pdb.parameter=value? Lets have a look:


[oracle@server-name]:ED12G:[~]# cd $ORACLE_HOME/dbs
[oracle@server-name]:ED12G:[/oracle/12.0.0/dbs]# strings spfileED12G.ora
ED12G.__data_transfer_cache_size=0
ED12G.__db_cache_size=1023410176
ED12G.__java_pool_size=33554432
ED12G.__large_pool_size=83886080
ED12G.__oracle_base='/oracle'#ORACLE_BASE set from environment
ED12G.__pga_aggregate_target=1140850688
ED12G.__sga_target=3388997632
ED12G.__shared_io_pool_size=167772160
ED12G.__shared_pool_size=2013265920
ED12G.__streams_pool_size=33554432
*.audit_file_dest='/oracle/admin/orcl/adump'
*.audit_trail='NONE'
*.compatible='12.1.0.0.0'
*.control_files='/ora
cle/ED12G/oradata/orcl/control01.ctl','/oracle/ED12G/recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain='e-ssi.net'
*.db_name='orcl'
*.db_recovery_file_dest='/oracle/ED12G/recovery_area'
*.db_recovery_file_dest_size=4800m
*.diagnostic_dest='/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ED12GXDB)'
*.enable_pluggable_database=true
*.job_queue_processes=1000
*.local_listener='LISTENER_ED12G'
*.open_cursors=300
*.pga_aggregate_target=1075m
*.processes=300
*.remote_login
_passwordfile='EXCLUSIVE'
*.sga_target=3226m
*.undo_tablespace='UNDOTBS1'



And there is no mention of this value at all.... hmmmm. Did it actually persist this value at all - lets log on and check.

[oracle@server-name]:ED12G:[/oracle/12.0.0/dbs]# s

SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 5 09:09:43 2013

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> sho parameter curso

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_bind_capture_destination      string      memory+disk
cursor_sharing                       string      EXACT
cursor_space_for_time                boolean     FALSE
open_cursors                         integer     300
session_cached_cursors               integer     50
SQL> alter session set container=test;

Session altered.

SQL> sho parameter curso

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_bind_capture_destination      string      memory+disk
cursor_sharing                       string      EXACT
cursor_space_for_time                boolean     FALSE
open_cursors                         integer     555
session_cached_cursors               integer     50
SQL>

So it did - but it only seems to be visible when switched to the correct container. Lets see if it survives a restart of the pluggable database.

[oracle@server-name]:ED12G:[/oracle/12.0.0/dbs]# s

SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 5 09:10:30 2013

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> alter pluggable database test close immediate;

Pluggable database altered.

SQL> alter pluggable database test open;

Pluggable database altered.

SQL> alter session set container=test;

Session altered.

SQL> sho parameter curso

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_bind_capture_destination      string      memory+disk
cursor_sharing                       string      EXACT
cursor_space_for_time                boolean     FALSE
open_cursors                         integer     555
session_cached_cursors               integer     50

And it does no problem, still no update to the spfile though - maybe there is some other special file - but it seems not - they are just standard files:

[oracle@server-name]:ED12G:[/oracle/12.0.0/dbs]# ls -lrt
total 24
-rw-r--r-- 1 oracle oinstall 2992 2012-02-03 08:24 init.ora
-rw-r----- 1 oracle oinstall   24 2013-07-05 11:15 lkORCL
-rw-r----- 1 oracle oinstall 7680 2013-07-05 11:24 orapwED12G
-rw-rw---- 1 oracle oinstall 1544 2013-07-08 07:39 hc_ED12G.dat
-rw-r----- 1 oracle oinstall 3584 2013-07-29 17:32 spfileED12G.ora


So if it doesnt use the spfile it must be storing the files somewhere in the data dictionary and reading that on PDB startup. Lets check whats in the various parameter views to see if we can track down where it is.

SQL> select name,value,con_id from v$parameter where name like '%curso%'
  2  /
cursor_space_for_time
FALSE
         1

session_cached_cursors
50
         1

cursor_sharing
EXACT
         1

open_cursors
300
         1

cursor_bind_capture_destination
memory+disk
         1

So v$parameter doesnt show it (well at least without not swtiching containers). Lets check v$system_parameter

  1* select name,value,con_id from v$system_parameter where name like '%curso%'
SQL> /
cursor_space_for_time
FALSE
         0

session_cached_cursors
50
         0

cursor_sharing
EXACT
         0

open_cursors
300
         0

cursor_bind_capture_destination
memory+disk
         0

open_cursors
555
         4


And it's there - so where if this value being read from?
Lets look at the view definition:

SQL> select * from GV_$FIXED_VIEW_DEFINITION where view_name='V$SYSTEM_PARAMETER'
  2  /
         1 V$SYSTEM_PARAMETER
select  NUM , NAME , TYPE , VALUE , DISPLAY_VALUE,  ISDEFAULT , ISSES_MODIFIABLE
 , ISSYS_MODIFIABLE , ISPDB_MODIFIABLE , ISINSTANCE_MODIFIABLE, ISMODIFIED , ISA
DJUSTED , ISDEPRECATED, ISBASIC, DESCRIPTION, UPDATE_COMMENT, HASH, CON_ID from
GV$SYSTEM_PARAMETER where inst_id = USERENV('Instance')

SQL>  select * from GV_$FIXED_VIEW_DEFINITION where view_name='GV$SYSTEM_PARAMETER'
  2  /
         1 GV$SYSTEM_PARAMETER
select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdvl, ksppstdf,  decode
(bitand(ksppiflg/256,1),1,'TRUE','FALSE'),  decode(bitand(ksppiflg/65536,3),1,'I
MMEDIATE',2,'DEFERRED',                                  3,'IMMEDIATE','FALSE'),
  decode(bitand(ksppiflg/524288,1),1,'TRUE','FALSE'),  decode(bitand(ksppiflg,4)
,4,'FALSE',                                     decode(bitand(ksppiflg/65536,3),
 0, 'FALSE', 'TRUE')),     decode(bitand(ksppstvf,7),1,'MODIFIED','FALSE'),  dec
ode(bitand(ksppstvf,2),2,'TRUE','FALSE'),  decode(bitand(ksppilrmflg/64, 1), 1,
'TRUE', 'FALSE'),  decode(bitand(ksppilrmflg/268435456, 1), 1, 'TRUE', 'FALSE'),
  ksppdesc, ksppstcmnt, ksppihash, y.con_id from x$ksppi x, x$ksppsv y  where (x
.indx = y.indx) and  bitand(ksppiflg,268435456) = 0 and  ((translate(ksppinm,'_'
,'#') not like '##%') and    ((translate(ksppinm,'_','#') not like '#%')      or
 (ksppstdf = 'FALSE') or      (bitand(ksppstvf,5) > 0)))
         0


So values seem to be coming from the x$ksppi/x$ksppsv objects (which you may be familiar with for listing out the underscore parameters). Lets see the defintion of them and where the actual raw data is held:


SQL> desc x$ksppi
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 CON_ID                                             NUMBER
 KSPPINM                                            VARCHAR2(80)
 KSPPITY                                            NUMBER
 KSPPDESC                                           VARCHAR2(255)
 KSPPIFLG                                           NUMBER
 KSPPILRMFLG                                        NUMBER
 KSPPIHASH                                          NUMBER

SQL> desc x$ksppsv
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 CON_ID                                             NUMBER
 KSPPSTVL                                           VARCHAR2(4000)
 KSPPSTDVL                                          VARCHAR2(4000)
 KSPPSTDF                                           VARCHAR2(9)
 KSPPSTVF                                           NUMBER
 KSPPSTCMNT                                         VARCHAR2(255)




  1  select KSPPSTDVL,CON_ID from x$ksppsv
  2* where indx in (select indx from x$ksppi  where KSPPINM='open_cursors')
SQL> /
300
         0

555
         4

SO it would seem that these objects do hold the data for all containers but is that table just held in the main root container database or are the values present in the PDB's too? How do we find that out - lets unplug the database and see if the values come back after being replugged back in - if they do then the data is held in the PDB.


SQL> alter pluggable database test close;

Pluggable database altered.

SQL> alter pluggable database test unplug into '/tmp/test.xml';

Pluggable database altered.

SQL> drop pluggable database test keep datafiles;

Pluggable database dropped.

SQL>


Now lets check whats i nthe x$ objects:


  1  select KSPPSTDVL,CON_ID from x$ksppsv
  2* where indx in (select indx from x$ksppi  where KSPPINM='open_cursors')
SQL> /
300
         0


Sure enough the setting has gone as we would expect - perhaps the value has made it into the xml file? Lets have a look:


    <parameters>
      <parameter>processes=300</parameter>
      <parameter>sga_target=3388997632</parameter>
      <parameter>db_block_size=8192</parameter>
      <parameter>compatible=12.1.0.0.0</parameter>
      <parameter>job_queue_processes=1000</parameter>
      <parameter>pga_aggregate_target=1127219200</parameter>
      <parameter>enable_pluggable_database=TRUE</parameter>
    </parameters>


So there are some values there but not the one we are looking for - these seem to be more generic settings.



Lets plug things back in and see if the value comes back or not......


[oracle@server-name]:ED12G:[/oracle/12.0.0/dbs]# s

SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 5 09:51:43 2013

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> create pluggable database test using '/tmp/test.xml';
create pluggable database test using '/tmp/test.xml'
                                                   *
ERROR at line 1:
ORA-65018: FILE_NAME_CONVERT or NOCOPY must be specified


SQL>  create pluggable database test using '/tmp/test.xml' nocopy;

Pluggable database created.

SQL>
SQL> select KSPPSTDVL,CON_ID from x$ksppsv
  2   where indx in (select indx from x$ksppi  where KSPPINM='open_cursors')
  3  /

KSPPSTDVL
--------------------------------------------------------------------------------
    CON_ID
----------
300
         0


SQL> alter pluggable database test open;

Pluggable database altered.

SQL>  select KSPPSTDVL,CON_ID from x$ksppsv
  2   where indx in (select indx from x$ksppi  where KSPPINM='open_cursors')
  3  /

KSPPSTDVL
--------------------------------------------------------------------------------
    CON_ID
----------
300
         0

555
         4


SQL>








So sure enough the setting did come back so the parameters are stored in the dictionary of the PDB. It would seem to work exactly the same way as the other parts of the data dictionary in 12c - data is essentially partitioned (for want of a better word) between the CDB and the PDB. On PDB startup the parameters used are the ones defined for the CDB + any additional/overrides from those set in the dictionary.


There is no 'spfile' that can be seen with all of the PDB parameters in - these would all have to be selected out of views/tables to see there values.

4 comments:

  1. thanks! very informative, I too was looking for where they are stored. great detective work.

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. isnt there any other way to clearly see the persistent parameters of pluggable database apart from the x$ views?
    from a guy who discovered Anonymous for apex recently :)

    ReplyDelete
  4. Hi Anil.
    Querying the v$ views seems to be the only way to get them - see oracle's own whitepaper http://www.oracle.com/technetwork/database/multitenant-wp-12c-1949736.pdf (page 42). This makes sense as the pdb plug/unplug does not take the spfile with it so the information has to be held in some internal table/object inside the database only.

    Cheers,
    Rich

    ReplyDelete