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.
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.
thanks! very informative, I too was looking for where they are stored. great detective work.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteisnt there any other way to clearly see the persistent parameters of pluggable database apart from the x$ views?
ReplyDeletefrom a guy who discovered Anonymous for apex recently :)
Hi Anil.
ReplyDeleteQuerying 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
caesiabso_1998 Kenneth Seibert https://marketplace.visualstudio.com/items?itemName=7riomenmesu.WORLD-END-ECONOMiCA-Episode-03-gratuita
ReplyDeletekarnlongderwea
YremppoAstabfu Tim Hundley Avast Pro Antivirus
ReplyDeleteBootstrap Studio
MorphVOX Pro
sogibooco