Lets play;
Conclusion:
Set your storage environment in the pluggable database will keep your environment clean. Don't forget this after the creating a pluggable database, you will see the problem after you add tablespaces to the pluggable database, and then it can and it will use the global settings from the CDB.
Important is that OMF the PDB_FILE_NAME_CONVERT and FILE_NAME_CONVERT settings overruled , and use the settings OMF of the CDB setting.
First validate the CDB environment. Below you will see that this is and CDB with one seed plugable database.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO
SQL> show parameter db_create_file_dest
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string /u02/app/oradata_cdb
SQL> show spparameter db_create_file_dest;
SID NAME TYPE VALUE -------- ----------------------------- ----------- ---------------------------- * db_create_file_dest string /u02/app/oradata_cdb
Case 1; Lets create a plugable database PDB_T0 without altering settings.
SQL> CREATE PLUGGABLE DATABASE PDB_T0 ADMIN USER t0 IDENTIFIED BY t0 roles=(DBA) STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M); 2 Pluggable database created.
Case 2; Lets create a pluggable PDB_T1 with altering the OMF settings.
SQL> alter session set db_create_file_dest='/u02/app/oradata_pdb1' ; Session altered.Quick view on what are my settings so i can validate this later on.
SQL> show parameter db_create_file_dest
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string /u02/app/oradata_pdb1
SQL> show spparameter db_create_file_dest
SID NAME TYPE VALUE -------- ----------------------------- ----------- ---------------------------- * db_create_file_dest string /u02/app/oradata_cdb
SQL> CREATE PLUGGABLE DATABASE PDB_T1 ADMIN USER t1 IDENTIFIED BY t1 roles=(DBA) STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M); 2 Pluggable database created.
Case 3; Lets create a plugable database PDB_T2 without OMF settings.
For creating pluggable database without OMF you need the parameter setting PDB_FILE_NAME_CONVERT or during the creating of the pluggable database FILE_NAME_CONVERT. Those settings are important
SQL> alter session reset db_create_file_dest; Session altered.Set the parameter at session level PDB_FILE_NAME_CONVERT
SQL> ALTER SESSION SET PDB_FILE_NAME_CONVERT = '/u02/app/oradata_cdb/CDB1/pdbseed','/u02/app/oradata_pdb2/PDB_T2/'; Session altered.
SQL> !mkdir /u02/app/oradata_pdb2/PDB_T2/
SQL> show parameter db_create_file_dest
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string /u02/app/oradata_cdb
SQL> show spparameter db_create_file_dest
SID NAME TYPE VALUE -------- ----------------------------- ----------- ---------------------------- * db_create_file_dest string /u02/app/oradata_cdb
SQL> show parameter PDB_FILE_NAME_CONVERT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pdb_file_name_convert string /u02/app/oradata_cdb/CDB1/pdbs
eed, /u02/app/oradata_pdb2/PDB
_T2/
SQL> show spparameter PDB_FILE_NAME_CONVERT
SID NAME TYPE VALUE -------- ----------------------------- ----------- ---------------------------- *
SQL>CREATE PLUGGABLE DATABASE PDB_T2 ADMIN USER t2 IDENTIFIED BY t2 roles=(DBA) STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M); Pluggable database created.
Case 4; Lets create a plugable database PDB_T3 without OMF settings.
SQL> alter session reset db_create_file_dest; Session altered.
show parameter db_create_file_dest show spparameter db_create_file_dest show parameter PDB_FILE_NAME_CONVERT show spparameter PDB_FILE_NAME_CONVERT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
SID NAME TYPE VALUE
-------- ----------------------------- ----------- ----------------------------
* db_create_file_dest string /u02/app/oradata_cdb
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pdb_file_name_convert string /u02/app/oradata_cdb, /u02/app
/oradata_pdb2
SID NAME TYPE VALUE
-------- ----------------------------- ----------- ----------------------------
* pdb_file_name_convert string
SQL> !mkdir /u02/app/oradata_pdb3/CDB1/PDB_T3/Using the FILE_NAME_CONVERT in the pluggable database command.
SQL> CREATE PLUGGABLE DATABASE PDB_T3 ADMIN USER t3 IDENTIFIED BY t3 roles=(DBA)
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
FILE_NAME_CONVERT=('/u02/app/oradata_cdb/CDB1/pdbseed/','/u02/app/oradata_pdb3/CDB1/PDB_T3/');
2 3
Pluggable database created.
Now lets see what we have created in the CDB. If correct we have created 4 pluggable DB's with different setting related to the CDB storage.
SQL> alter pluggable database all open; Pluggable database altered.
PDBS
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB_T0 READ WRITE NO 4 PDB_T1 READ WRITE NO 5 PDB_T2 READ WRITE NO 6 PDB_T3 READ WRITE NO
All the pluggble database are read write and open. Investigation where are the datafiles placed of the pluggable database on the Oracle CDB environment.
SQL> select con_id, file_name from cdb_data_files order by 1
CON_ID FILE_NAME ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------ 1 /u02/app/oradata_cdb/CDB1/system01.dbf 1 /u02/app/oradata_cdb/CDB1/sysaux01.dbf 1 /u02/app/oradata_cdb/CDB1/undotbs01.dbf 1 /u02/app/oradata_cdb/CDB1/users01.dbf 2 /u02/app/oradata_cdb/CDB1/pdbseed/sysaux01.dbf 2 /u02/app/oradata_cdb/CDB1/pdbseed/system01.dbf 3 /u02/app/oradata_cdb/CDB1/ED1B30729CDD1F13E043960A0A0A08DF/datafile/o1_mf_sysaux_9bclbjk0_.dbf 3 /u02/app/oradata_cdb/CDB1/ED1B30729CDD1F13E043960A0A0A08DF/datafile/o1_mf_system_9bclbjk1_.dbf 4 /u02/app/oradata_pdb1/CDB1/ED1B30729CDE1F13E043960A0A0A08DF/datafile/o1_mf_sysaux_9bclms9g_.dbf 4 /u02/app/oradata_pdb1/CDB1/ED1B30729CDE1F13E043960A0A0A08DF/datafile/o1_mf_system_9bclmsb2_.dbf 5 /u02/app/oradata_pdb2/PDB_T2/system01.dbf 5 /u02/app/oradata_pdb2/PDB_T2/sysaux01.dbf 6 /u02/app/oradata_pdb3/CDB1/PDB_T3/system01.dbf 6 /u02/app/oradata_pdb3/CDB1/PDB_T3/sysaux01.dbfAs you can see no changes are made on the PDB's related to different storage environments of the CDB. What will happen if new datafiles or tablespaces are added to the pluggable database.
So lets start clean session and database
Test case with adding tablespaces to the pluggable databases
SQL> startup force;
ORACLE instance started. Total System Global Area 1252663296 bytes Fixed Size 2287864 bytes Variable Size 788530952 bytes Database Buffers 452984832 bytes Redo Buffers 8859648 bytes Database mounted. Database opened.Investigate the PDB's on the CDB
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB_T0 MOUNTED 4 PDB_T1 MOUNTED 5 PDB_T2 MOUNTED 6 PDB_T3 MOUNTEDOpen all the PDB's inside the CDB
SQL> alter pluggable database all open; Pluggable database altered.
Start with adding tablespace to TBD_T1
SQL> show parameter db_create_file_dest show spparameter db_create_file_dest show parameter PDB_FILE_NAME_CONVERT show spparameter PDB_FILE_NAME_CONVERT
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string /u02/app/oradata_cdb SID NAME TYPE VALUE -------- ----------------------------- ----------- ---------------------------- * db_create_file_dest string /u02/app/oradata_cdb NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pdb_file_name_convert string SID NAME TYPE VALUE -------- ----------------------------- ----------- ---------------------------- * pdb_file_name_convert stringCreate the tablespace
SQL> create tablespace ts_PDB_1 datafile size 10M; Tablespace created.Start with adding tablespace to TBD_T2
SQL> ALTER SESSION SET CONTAINER = PDB_T2; Session altered. SQL> create tablespace ts_PDB_2 datafile size 10M; Tablespace created.
Start with adding tablespace to TBD_T3
SQL> ALTER SESSION SET CONTAINER = PDB_T3; Session altered. SQL> create tablespace ts_PDB_3 datafile size 10M; Tablespace created.
Start with adding tablespace to TBD_T0
SQL> create tablespace ts_PDB_0 datafile size 10M; Tablespace created.
Results adding tablespaces to PDBS
We have now finished the adding tabespaces to the pluggable database. What is now the impact of this. Below you will see where the datafiles are placed for the pluggable database inside the CDB storage environment.SQL> connect / as sysdba Connected.
Storage impact inside the CDB
SQL> select con_id, file_name from cdb_data_files order by 1;
CON_ID FILE_NAME ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------ 1 /u02/app/oradata_cdb/CDB1/system01.dbf 1 /u02/app/oradata_cdb/CDB1/sysaux01.dbf 1 /u02/app/oradata_cdb/CDB1/undotbs01.dbf 1 /u02/app/oradata_cdb/CDB1/users01.dbf 2 /u02/app/oradata_cdb/CDB1/pdbseed/sysaux01.dbf 2 /u02/app/oradata_cdb/CDB1/pdbseed/system01.dbf 3 /u02/app/oradata_cdb/CDB1/ED1B30729CDD1F13E043960A0A0A08DF/datafile/o1_mf_system_9bclbjk1_.dbf 3 /u02/app/oradata_cdb/CDB1/ED1B30729CDD1F13E043960A0A0A08DF/datafile/o1_mf_sysaux_9bclbjk0_.dbf 3 /u02/app/oradata_cdb/CDB1/ED1B30729CDD1F13E043960A0A0A08DF/datafile/o1_mf_ts_pdb_0_9bcng0ml_.dbf 4 /u02/app/oradata_pdb1/CDB1/ED1B30729CDE1F13E043960A0A0A08DF/datafile/o1_mf_system_9bclmsb2_.dbf 4 /u02/app/oradata_pdb1/CDB1/ED1B30729CDE1F13E043960A0A0A08DF/datafile/o1_mf_sysaux_9bclms9g_.dbf 4 /u02/app/oradata_cdb/CDB1/ED1B30729CDE1F13E043960A0A0A08DF/datafile/o1_mf_ts_pdb_1_9bcn4moz_.dbf 5 /u02/app/oradata_pdb2/PDB_T2/system01.dbf 5 /u02/app/oradata_pdb2/PDB_T2/sysaux01.dbf 5 /u02/app/oradata_cdb/CDB1/ED1B30729CDF1F13E043960A0A0A08DF/datafile/o1_mf_ts_pdb_2_9bcn5j61_.dbf 6 /u02/app/oradata_pdb3/CDB1/PDB_T3/system01.dbf 6 /u02/app/oradata_cdb/CDB1/ED1B72D3EBE61FC0E043960A0A0A0D6E/datafile/o1_mf_ts_pdb_3_9bcn6jp1_.dbf 6 /u02/app/oradata_pdb3/CDB1/PDB_T3/sysaux01.dbf 18 rows selected.
See here all the new tablespaces with datafiles are created with the OMF settings of the CDB. No additionele settings where add to the pluggable database. For me it will be a spaghetti environment, where you will lost the logica related to storage.
Now lets specified settings on pluggable database level.
SQL> alter session set container=PDB_T1; Session altered. SQL> show parameter db_create_file_dest;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string /u02/app/oradata_cdbHere we set the OMF setting of the PDB.
SQL> alter system set db_create_file_dest='/u02/app/oradata_pdb1' scope=both; System altered. SQL> show parameter db_create_file_dest;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string /u02/app/oradata_pdb1
SQL> SQL> show spparameter db_create_file_dest;
SID NAME TYPE VALUE -------- ----------------------------- ----------- ---------------------------- * db_create_file_dest string /u02/app/oradata_cdbSee how we can get information from the root CDB
connect / as sysdba select DB_UNIQ_NAME, PDB_UID, NAME, VALUE$ from pdb_spfile$;
DB_UNIQ_NAME PDB_UID NAME VALUE$ ------------------------------ ---------- -------------------- ------------------------------ CDB1 1706463289 db_create_file_dest '/u02/app/oradata_pdb1'
Use the PDB_UID to finded your pluggable databse name.
SQL> select * from dba_pdbs where CON_UID=1706463289;
PDB_ID PDB_NAME DBID CON_UID GUID STATUS CREATION_SCN ---------- -------------------- ---------- ---------- -------------------------------- ------------- ------------ 4 PDB_T1 1706463289 1706463289 ED1B30729CDE1F13E043960A0A0A08DF NORMAL 1509163
Go back to the PDB_T1
SQL> alter session set container=PDB_T1; Session altered. SQL> show parameter db_create_file;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string /u02/app/oradata_pdb1
Create a new tablespace for this database with the new settings OMF
SQL> create tablespace PDB_A datafile size 10M; Tablespace created.Create a new tablespace for this database with the new settings OMF
SQL> create tablespace PDB_A datafile size 10M;
SQL> select file_name from dba_data_files;
FILE_NAME ------------------------------------------------------------------------------------------------- /u02/app/oradata_pdb1/CDB1/ED1B30729CDE1F13E043960A0A0A08DF/datafile/o1_mf_sysaux_9bclms9g_.dbf /u02/app/oradata_cdb/CDB1/ED1B30729CDE1F13E043960A0A0A08DF/datafile/o1_mf_ts_pdb_1_9bcn4moz_.dbf /u02/app/oradata_pdb1/CDB1/ED1B30729CDE1F13E043960A0A0A08DF/datafile/o1_mf_system_9bclmsb2_.dbf /u02/app/oradata_pdb1/CDB1/ED1B30729CDE1F13E043960A0A0A08DF/datafile/o1_mf_pdb_A_9bco9kdz_.dbf
No comments:
Post a Comment