ORACLE12C Multi-tenant CDB and PDB parameter file location discussion, querying the parameters of different values of CDB and PDB

One, oracle12c multi-tenant CDB and PDB parameter file location

CDB parameter file is still used 12c the parameter file of the previous spifle,pdb does not appear in SPFILE, but is inherited directly from the CDB, if there is privete Local parameter in the PDB there will be a pdb_spfile$ dictionary table of CDB In con_id, when the PDB Un-plug, the PDB parameter is written to the PDB's XML file, and when the drop pluggable database, the PDB information and pdb_spfile$ records are also cleared. When the PDB is re-plug-in to the CDB, it reloads back to the PDB, but because some PDB special parameters are discarded when plug-in.

Experimental validation:

Sql> Show Con_name

Con_name
------------------------------
Cdb$root
Sql> Show Parameter SPFile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
SPFile String/home/oracle/app/oracle/produc
T/12.2.0/dbhome_1/dbs/spfilean
Dycdb.ora
Sql> alter session set CONTAINER=PDB01;

Session altered.

Sql> Show Parameter SPFile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
SPFile String/home/oracle/app/oracle/produc
T/12.2.0/dbhome_1/dbs/spfilean
Dycdb.ora
This step verifies that the parameters file of CDB is still using 12c the spifle,pdb parameter file does not appear in the SPFile, but is inherited directly from the CDB.

Sql> Show Parameter Undo_retention

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Undo_retention integer900
Sql> alter system set undo_retention=901;

System altered.

Sql> Show Parameter Undo_reten

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Undo_retention integer901

Sql> select pdb_uid,name,value$ from pdb_spfile$;

No rows selected
Sql> alter session set Container=cdb$root;

Session altered.

Sql> select pdb_uid,name,value$ from pdb_spfile$;

Pdb_uid NAME value$
---------- ---------------------------------------- -------------------------
2550500229 Undo_retention 901

Sql> select Con_id,dbid,con_uid,guid from V$pdbs;

con_id Dbidcon_uid GUID
---------- ---------- ---------- --------------------------------
2 2683777510 2683777510 4ecf66d93a6233b5e0531019640a6041
3 2550500229 2550500229 4ecf8621e3da38eee0531019640aa598

This step verifies that if there is privete Local parameter in the PDB there will be a pdb_spfile$ dictionary table in the CDB to con_id the difference

Sql>alter Pluggable database pdb01 close immediate;
Sql>alter Pluggable database Pdb01 unplug into '/home/oracle/pdb01.xml ';
[Email protected] ~]$ pwd
/home/oracle
[email protected] ~]$ ll Pdb01.xml
-rw-r--r--. 1 Oracle Oinstall 7758 May 7 05:09 Pdb01.xml
[email protected] ~]$ cat Pdb01.xml
<?xml version= "1.0" encoding= "UTF-8"?>
<PDB>
<xmlversion>1</xmlversion>
<pdbname>PDB01</pdbname>
<cid>3</cid>
<byteorder>1</byteorder>
<vsn>203424000</vsn>
<vsns>
<vsnnum>12.2.0.1.0</vsnnum>
<cdbcompt>12.2.0.0.0</cdbcompt>
<pdbcompt>12.2.0.0.0</pdbcompt>
<vsnlibnum>0.0.0.0.24</vsnlibnum>
<vsnsql>24</vsnsql>
<vsnbsv>8.0.0.0.0</vsnbsv>
</vsns>
<dbid>2550500229</dbid>
... Omitted
Sql> Show PDBs

con_id con_name OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 Pdb$seed READ only NO
3 PDB01 Mounted
sql> Drop pluggable database pdb01 keep datafiles;

Pluggable database dropped.

Sql> Show PDBs

con_id con_name OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 Pdb$seed READ only NO
Sql> select pdb_uid,name,value$ from pdb_spfile$;

No rows selected
This step verifies that the PDB un-plug after PDB parameter and SPFILE will be advanced XML files, and the PDB information and pdb_spfile$ records will be cleared when the drop pluggable database.

Sql> Create pluggable database pdb01 using '/home/oracle/pdb01.xml ' nocopy;

Sql> Show PDBs

con_id con_name OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 Pdb$seed READ only NO
3 PDB01 Mounted
sql> Alter pluggable database PDB01 open;
Pluggable database altered.
Sql> select pdb_uid,name,value$ from pdb_spfile$;

Pdb_uid NAME value$
---------- ---------------------------------------- -------------------------
1196085469 Undo_retention 901

This step verifies that when the PDB is re-plug-in to the CDB, it reloads back to the PDB, but is discarded due to some special reasons for the PDB parameters at plug-in. Because there are no special parameters, there are no missing parameters.

Two, CDB and PDB the same parameters of different values

In a multitenant environment, if you set container=all in CDB when setting parameters, the parameters of the PDB also inherit this value, but you can modify the PDB local parameter in the PDB container through alter system, overwriting (precedence) The arguments inherited from the CDB. Sometimes, we have a need to verify which parameters in the comparison PDB differ from CDB. What should we do, let's explore the next.

Example: query for the same parameters for the PDB named PDB01 and the different values of CDB
1. Lab Environment View
Sql> Show Con_name

Con_name
------------------------------
Cdb$root
Sql> Show Parameter Undo_reten

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Undo_retention integer900
Sql> alter session set CONTAINER=PDB01;
Sql> Show Parameter Undo_reten

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Undo_retention integer901
2. Query what parameters in the PDB are different from CDB
Sql>
Set Lin 300;
Set pages 300;
Col pdb_name for A30;
Col parameter for A25;
Col value$ for A20;
Select V.dbid,v.name pdb_name,p.name parameter,p.value$
From pdb_spfile$ P,v$pdbs V
where P.pdb_uid=v.con_uid and v.name= ' PDB01 ';
DBID pdb_name PARAMETER value$
---------- ------------------------------ ------------------------- -------------
2550500229 PDB01 undo_retention 901

ORACLE12C Multi-tenant CDB and PDB parameter file location discussion, querying the parameters of different values of CDB and PDB