By Franck Pachot

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

create_file_dest

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.

db_create_file_dest

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

But…

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 NO

Creating 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 NO

I suppose that it is stored elsewhere because those flags are set only once PDB is opened.

Post Views: 1,369
Thumbnail [60x60]
by
Oracle Team