Tuesday, December 10, 2013

Creating pluggable databases with and without OMF structure; [CDB=OMF storage structure]

For creating a pluggable database you have to consider how to create the pluggable database, and what are you current CDB settings. In this post i have played with and without OMF and difference file structures inside ONE CDB database. Mostly pluggable database will be created from the seed database. What is needed when you want the oracle files of the pluggable database on an other file structure storage. What will you use, and when should you use what. After the createing of a pluggable database, you need possible extra steps inside the pluggable database to keep your optimalisize storage environment. Think about the steps that are needed with a single none CDB, those steps would also be needed on a pluggable database by or after the creating steps. When you don't think about this, the change is that you get a spaghetti mixture of files inside your environment.

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.dbf

As 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     MOUNTED

Open 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        string

Create 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_cdb

Here 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_cdb

See 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