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.