This was first published on https://blog.dbi-services.com/12cr2-create_file_dest-for-pdb-isolation (2016-11-16)
Republishing here for new followers. The content is related to the the versions available at the publication date
Two years ago I filled an OTN idea to ‘Constrain PDB datafiles into specific directory’ and made it an enhancement request for 12c Release 2. When you provision a PDB, the PDB admin can create tablespaces and put datafiles anywhere in your system. Of course this is not acceptable in a cloud environment. 12.1 has a parameter for directories (PATH_PREFIX) and 12.2 brings CREATE_FILE_DEST for datafiles.
Here is the new option when you create a pluggable database:
SQL> create pluggable database PDB1 admin user admin identified by password role=(DBA) create_file_dest='/u02/app/oracle/oradata/CDB2/PDB1'; Pluggable database created.
Let’s see where are my datafiles:
SQL> alter pluggable database PDB1 open; Pluggable database altered. SQL> alter session set container=PDB1; Session altered. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u02/app/oracle/oradata/CDB2/PDB1/CDB2/415260E5D27B5D4BE0534E186A0A4CB8/datafile/o1_mf_system_d2od2o7b_.dbf /u02/app/oracle/oradata/CDB2/PDB1/CDB2/415260E5D27B5D4BE0534E186A0A4CB8/datafile/o1_mf_sysaux_d2od2o7j_.dbf /u02/app/oracle/oradata/CDB2/PDB1/CDB2/415260E5D27B5D4BE0534E186A0A4CB8/datafile/o1_mf_undotbs1_d2od2o7l_.dbf
My files have been created in the CREATE_FILE_DEST directory specified at PDB creation, and with an OMF structure. So maybe I don’t want to include the CDB name and the PDB name but only a mount point.
If, as a local user, I try to create a datafile elsewhere I get an error:
SQL> connect admin/password@//localhost/pdb1.opcoct.oraclecloud.internal Connected. SQL> create tablespace APPDATA datafile '/tmp/appdata.dbf' size 5M; create tablespace APPDATA datafile '/tmp/appdata.dbf' size 5M * ERROR at line 1: ORA-65250: invalid path specified for file - /tmp/appdata.dbf
This is exactly what I wanted.
Because I’m bound to this directory, I don’t need to give an absolute path:
SQL> create tablespace APPDATA datafile 'appdata.dbf' size 5M; Tablespace created. SQL> select name from v$datafile; NAME ------------------------------------------------------------------------------------------------------------- /u02/app/oracle/oradata/CDB2/PDB1/CDB2/415260E5D27B5D4BE0534E186A0A4CB8/datafile/o1_mf_system_d2od2o7b_.dbf /u02/app/oracle/oradata/CDB2/PDB1/CDB2/415260E5D27B5D4BE0534E186A0A4CB8/datafile/o1_mf_sysaux_d2od2o7j_.dbf /u02/app/oracle/oradata/CDB2/PDB1/CDB2/415260E5D27B5D4BE0534E186A0A4CB8/datafile/o1_mf_undotbs1_d2od2o7l_.dbf /u02/app/oracle/oradata/CDB2/PDB1/appdata.dbf
So you don’t need to use OMF there. If the PDB administrator wants to name the datafiles, he can, as long as they stays under the create_file_dest directory. You can create a datafile in a sub-directory of create_file_dest but it needs to exist of course.
Here it just looks like OMF, so I check the db_create_file_dest parameter:
SQL> show parameter file_dest NAME TYPE VALUE ------------------------------------ ----------- --------------------------------- db_create_file_dest string /u02/app/oracle/oradata/CDB2/PDB1
and I try to change it (as local user):
SQL> connect admin/password@//localhost/pdb1.opcoct.oraclecloud.internal; Connected. SQL> alter system set db_create_file_dest='/tmp'; alter system set db_create_file_dest='/tmp' * ERROR at line 1: ORA-32017: failure in updating SPFILE ORA-01031: insufficient privileges SQL> alter session set db_create_file_dest='/tmp'; ERROR: ORA-02097: parameter cannot be modified because specified value is invalid ORA-01031: insufficient privileges
No need to use lockdown profile here, it is verified at runtime that a local user cannot change it.
If you are connected with a common user, here connected as sysdba, this is the way to change what has been specified at PDB creation time:
SQL> show con_id CON_ID ------------------------------ 3 SQL> alter system set db_create_file_dest='/tmp'; System altered. SQL> create tablespace APP1; Tablespace created. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u02/app/oracle/oradata/CDB2/PDB1/CDB2/415260E5D27B5D4BE0534E186A0A4CB8/datafile/o1_mf_system_d2od2o7b_.dbf /u02/app/oracle/oradata/CDB2/PDB1/CDB2/415260E5D27B5D4BE0534E186A0A4CB8/datafile/o1_mf_sysaux_d2od2o7j_.dbf /u02/app/oracle/oradata/CDB2/PDB1/CDB2/415260E5D27B5D4BE0534E186A0A4CB8/datafile/o1_mf_undotbs1_d2od2o7l_.dbf /u02/app/oracle/oradata/CDB2/PDB1/appdata.dbf /tmp/CDB2/415260E5D27B5D4BE0534E186A0A4CB8/datafile/o1_mf_app1_d2ohx5sp_.dbf
The behavior when you create the PDB with the CREATE_FILE_DEST clause is different than when you create it without, and set db_create_file_dest later. In the second case, the restriction does not occur and a local DBA can create a datafile wherever he wants.
So I wanted to check whether this attribute is shipped when plugging PDBs. When looking at the pdb_descr_file xml file I don’t see anything different except the parameter:
<parameters> <parameter>processes=300 <parameter>nls_language='AMERICAN' <parameter>nls_territory='AMERICA' <parameter>filesystemio_options='setall' <parameter>db_block_size=8192 <parameter>encrypt_new_tablespaces='CLOUD_ONLY' <parameter>compatible='12.2.0' <parameter>db_files=250 <parameter>open_cursors=300 <parameter>sql92_security=TRUE <parameter>pga_aggregate_target=1775294400 <parameter>sec_protocol_error_trace_action='LOG' <parameter>enable_pluggable_database=TRUE <spfile>*.db_create_file_dest='/u02/app/oracle/oradata/CDB2/PDB1' </parameters>So I tried to unplug/plug my PDB and the restriction is gone. So be careful.
I’ve not find a documented way to check if restriction is enabled or not (except trying to create a file outside of db_create_file_dest). Please comment if you know. However, it seems that that a flag in CONTAINER$ is unset when restriction is there:
SQL> create pluggable database PDB1 admin user admin identified by password role=(DBA) create_file_dest='/u02/app/oracle/oradata/CDB2/PDB1'; Pluggable database created. SQL> select con_id#,flags,decode(bitand(flags, 2147483648), 2147483648, 'YES', 'NO') from container$; CON_ID# FLAGS DEC ---------- ---------- --- 1 0 NO 2 3221487616 YES 3 1610874880 NOCreating the same PDB but without the create_file_dest clause has the same flag as ‘NO’
create pluggable database PDB1 admin user admin identified by password role=(DBA); Pluggable database created. SQL> select con_id#,flags,decode(bitand(flags, 2147483648), 2147483648, 'YES', 'NO') from container$; CON_ID# FLAGS DEC ---------- ---------- --- 1 0 NO 2 3221487616 YES 3 1074003968 NOI suppose that it is stored elsewhere because those flags are set only once PDB is opened.