Infrastructure at your Service

Oracle Team

12cR2: CREATE_FILE_DEST for PDB isolation

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/[email protected]//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/[email protected]//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.

12 Comments

  • goutham says:

    Hi Frank.
    need your assistance on the below error.
    i just created a new pdb on test machine with CREATE_FILE_DEST=’/opt/oracle/oradata/pdb1′.
    and when i check the rman log it failed with this error.

    input datafile file number=00017 name=/opt/oracle/oradata/pdb1/ORCLCDB/9F77E3685F5F31D7E0530300000ABB38/datafile/o1_mf_sysaux_h5df4jck_.dbf
    input datafile file number=00016 name=/opt/oracle/oradata/pdb1/ORCLCDB/9F77E3685F5F31D7E0530300000ABB38/datafile/o1_mf_system_h5df4jbq_.dbf
    input datafile file number=00018 name=/opt/oracle/oradata/pdb1/ORCLCDB/9F77E3685F5F31D7E0530300000ABB38/datafile/o1_mf_undotbs1_h5df4jcl_.dbf
    channel ch1: starting piece 1 at 26-FEB-20
    RMAN-03009: failure of backup command on ch1 channel at 02/26/2020 22:32:05
    ORA-65250: invalid path specified for file – /u01/oradata/rcv_area/daily_bk_ORCLCDB_20200226_2tuphk74_1_1
    continuing other job steps, job failed will not be re-run

    Thanks
    goutham

  • Franck Pachot says:

    Hi Goutham,

    Can you precise which user/privileges you are using and what is your backup command? I tried this and it works (backup destination is outside of the CREATE_FILE_DEST path, I tried with SYSDBA and SYSBACKUP):

    Connected to:
    Oracle Database 20c EE High Perf Release 20.0.0.0.0 – Production
    Version 20.2.0.0.0

    SQL> alter session set container=xxx;

    Session altered.

    SQL> grant sysdba to pdbadmin;

    Grant succeeded.

    SQL> exit
    Disconnected from Oracle Database 20c EE High Perf Release 20.0.0.0.0 – Production
    Version 20.2.0.0.0
    [[email protected] admin]$ rman target pdbadmin/[email protected]//localhost/xxx.subnet.vcn.oraclevcn.com

    Recovery Manager: Release 20.0.0.0.0 – Production on Fri Feb 28 09:45:31 2020
    Version 20.2.0.0.0

    Copyright (c) 1982, 2020, Oracle and/or its affiliates. All rights reserved.

    connected to target database: CDB1A:XXX (DBID=4105981834)

    RMAN> backup database;

    Starting backup at 28-FEB-20
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=218 device type=DISK
    channel ORA_DISK_1: starting full datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    input datafile file number=00014 name=/var/tmp/CDB1A_IAD1SB/9FA0A225C8B7136CE0530600000A2A18/datafile/o1_mf_sysaux_h5kr1523_.dbf
    input datafile file number=00013 name=/var/tmp/CDB1A_IAD1SB/9FA0A225C8B7136CE0530600000A2A18/datafile/o1_mf_system_h5kr151s_.dbf
    input datafile file number=00015 name=/var/tmp/CDB1A_IAD1SB/9FA0A225C8B7136CE0530600000A2A18/datafile/o1_mf_undotbs1_h5kr1524_.dbf
    channel ORA_DISK_1: starting piece 1 at 28-FEB-20
    channel ORA_DISK_1: finished piece 1 at 28-FEB-20
    piece handle=/u03/app/oracle/fast_recovery_area/CDB1A_IAD1SB/9FA0A225C8B7136CE0530600000A2A18/backupset/2020_02_28/o1_mf_nnndf_TAG20200228T094540_h5krb4pt_.bkp tag=TAG20200228T094540 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
    Finished backup at 28-FEB-20

  • Robert says:

    See patch 30419450.

  • Franck Pachot says:

    Thanks Robert for this info about ORA-65250 DURING RMAN PLUGGABLE DATABASE BACKUP
    https://support.oracle.com/epmos/faces/SearchDocDisplay?_afrLoop=302078580566855

  • Sreedhar says:

    Hello Frank,

    Could you please help me with this…I created a refreshable PDB with create_file_dest=’/u01′ . Now I created a new tablespace on the source. When I performed the refresh again, the datafile of the new PDB is created in the +DATA disk group instead of of /u01. Of course the db_create_file_dest on the CDB is ‘+DATA’. But shouldn’t the refresh create the new datafile user the create_file_dest path specified at the time of cloning the PDB? Is there a workaround for this or am I doing something wrong?

  • Franck Pachot says:

    Hi Sreedhar,
    I must admin that i never did that: I usually have the whole CDB going to the same dest. And finally didn’t use this feature to isolate a PDB (I’m not a cloud provider and trust my users ) I would consider this as a bug as the create_file_dest is a security feature and should not create a datafile out of it. You should open a SR for that.
    Franck.

  • Simon says:

    Hi Franck, nice investigation. However, I believe you have the bit mask for container$.flag incorrect at the very end and in your final two examples. I believe that from my testing (using 19.10) it should be BITANDed on 536870912 instead?

  • Bartlomiej Sowa says:

    Hi. This is a very interesting topic. I have used separate filesystems for each PDB (created with create_file_dest parameter) in order to have them on different storage tiers (ssd vs magnetic for som archival database). This has caused nothing else than troubles, since when creating or plugging-in the pdb, there was no problem. But doing a full CDB restore with rman completely ignored pdb-level-set db_create_file_dest and create_file_dest argument used when creating/plugging-in the PDB and put ALL data files (also those from PDBs) under the db_create_file_test od CDB (which led to filesystem fillup and finally to restore failure). It was not possible to restore this PDB otherwise, than specifying individual datafiles and explicit set newname and so on… It is not possible to restore a PDB with OMF in a way, that will respect the per-pdb create_file_dest. One way would be to disable OMF completely on both CDB and PDB levels, but this has another drawbacks… Were you able to check this use case? I mean – when you create a standby database, regardless whether this is dataguard or dbvisit. You still end up with datafiles being created on standby unter CDBs’ create_file_dest. In my opinion this is a big TODO for oracle, or i am really messing things up.

  • Hi Bartlomiej ,
    Actually I didn’t use it like this. I consider CREATE_FILE_DEST as a session setting, the PDB/CDB values being the default, like telling my session: “when I don’t mention a datafile name, put all there as you want”. And mentioning it at PDB create level is more: “don’t allow users to spread their files elsewhere”. And… RMAN is not a PDB user 😉 When using OMF is really like “I don’t micromanage the files, put all there.” But here you want to manage them.
    However, I understand what you want to do. I would create different CDB for SSD/HDD tiering. Then archiving a PDB (moving to SSD to HDD) would be an online relocate, on same host, maybe same listener. Just changing the files, and the instance. And changing the instance makes sense: probably not the same settings for archival.

  • Bartlomiej Sowa says:

    Hi. Well – this is exactly how this ended. I created a new CDB and placed it on magnetic-backed filesystem. Sometimes however to spare license costs on dbvisit for example (it is licensed per CDB and a CDB can hold up to 3 PDBs for Standard Edition) it makes sense to mix those PDBs within single CDB. But you are right totally – wither I let Oracle manage the files completely, or I do it all by myself not using OMF at all and sticking to *_name_convert. Just wonder why did Oracle implement pdb-level parameter db_create_file_dest since its not always working as expected. I mean – it will be used when creating new datafiles within PDB, that’s right. But any CDB-level restore will ignore it completely and I have not found any way around it other than to split the restore and do for each pdb a separate restore session with different create_file_dest :/. It cannot be done in one session i’m afraid and I think this is to be fixed by oracle, as the usage of pdb-level parameter is really inconsistent here.

  • Hi Bartlomiej, yes I understand the need to consolidate with Dbvisit. Thanks for your feedback.
    Parameter scope in multitenant is very complex with this additional level, and how they propagate to standby databases. A good overview of the complexity is the length of those notes:
    Initialization parameters in a Multitenant database – FAQ and Examples (Doc ID 2101638.1)
    Initialization parameters in a Multitenant database – Facts and additional information (Doc ID 2101596.1)

  • Gerrit Haase says:

    Well, last time I tried AS SYSDBA I was able to create files anywhere I want on my machine on my filesystem, I don’t care about cloud environments.

    But now I can not create files anymore aside from create_file_dest setting, this sucks many times, I am SYSDBA and it is rather annoying, that this restriction also applies to me now.

    Probably it is just a bug, but of course, if there is a feature, than this must happen.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Oracle Team
Oracle Team