2021-03-07

reading PDBs spfile parameters

Last week I was asked by a customer if changes in a PDBs spfile parameter can be monitored easily. 

My first thought was to check if AWR on CDB$ROOT level does this for me already - unfortunately it doesn't. 

So the next idea was to sample PDB_SPFILE$ on my own. 
Unfortunately this table is not documented by Oracle and my colleague Miguel Anjo found records there are not deleted when they are reset on PDB level

To identify such behavior, I like to have a look in all the nice files in $ORACLE_HOME/rdbms/admin.
In e1201000.sql I luckily found this code. Even e1201000.sql is used in case of downgrade, it's quite helpful.


Rem *************************************************************************
Rem BEGIN BUG 23300354 - remove HWM and flags column values in pdb_spfile$
Rem *************************************************************************
update pdb_spfile$ set
db_uniq_name=(select sys_context('userenv', 'DB_UNIQUE_NAME') from dual)
where db_uniq_name='*';
Rem Set HWM value to 0
update pdb_spfile$ set spare1=0;
Rem Delete deleted rows and reset flags to 0
delete from pdb_spfile$ where bitand(nvl(spare2,0),1) = 1;
update pdb_spfile$ set spare2=0;
commit;
bla
Another approach is to check what#s Oracle doing at UNPLUGGING a PDB: 


select value$ from pdb_spfile$ where name=:1 and pdb_uid=:2                   and bitand(nvl(spare2,0),1)=0


I tried the probably most obvious method: 


CDB$ROOT> select * from containers(v$spparameter);

But this only show results for CDB$OOT (CON_ID => 1). 


Even a test with x$kspspfile shows only values for CON_ID => 1. 

This is quite sad, especially as x$kspspfile IS used when I query v$spparameter from my PDB1 (which has CON_ID=3 in this test), but there seems to be some additional magic in x$kspspfile which I can not mitigate on SQL layer.




3 Kommentare:

Mikhail Velikikh hat gesagt…

Hi Martin,

> I tried the probably most obvious method:
>
> CDB$ROOT> select * from containers(v$spparameter);
>
> But this only show results for CDB$OOT (CON_ID => 1).

It is related to container_data, e.g.:

conn / as sysdba
drop user c##test;
create user c##test identified by test;
alter user c##test set container_data=all container=current;
--alter user c##test set container_data=default container=current;
grant create session, select_catalog_role to c##test container=all;

conn c##test/test
select con_id, value from containers(v$spparameter) where name='parallel_servers_target';

CON_ID VALUE
---------- --------------------------------------------------
1
3 8

After doing:
alter user c##test set container_data=default container=current;
The last query returns:

SQL> select con_id, value from containers(v$spparameter) where name='parallel_servers_target'

CON_ID VALUE
---------- --------------------------------------------------
1

Regards,
Mikhail.

Martin Berger hat gesagt…

Thank you Mikahil!

When using CONTAINERS(), CONTAINER_DATA must be taken into consideration.
I should remember all the details I'm presenting at some time myself ;-)

Martin

panzer.hier hat gesagt…

Hi,

It's stil not clear why there are no data for other containers when performing the following SQL from the CDB$ROOT (being connected AS SYSDBA)

CDB$ROOT> select * from containers(v$spparameter);

Regards,
Dmitry