Understanding the Implications of Creating a New PDB Using CREATE_FILE_DEST
qOverview
When creating a new pluggable database, or PDB, you can optionally include the CREATE_FILE_DEST clause.
Here’s an example:
create pluggable database PDB1 admin user admin identified by "************" create_file_dest = '+DATA_DG'; |
The value provided can be an ASM (Automatic Storage Management) disk group or an OS filesystem path.
Either way, the question is: “What are the implications of including this clause when creating a PDB?”
The answer is “it’s complicated,” but there are at least four key implications (tested using Oracle Database 19c):
- When the new PDB is first opened, the PDB initialization parameter DB_CREATE_FILE_DEST will be set in the PDB spfile (which is really a catalog table, but that’s beside the point) to the CREATE_FILE_DEST value.
- The behavior of the DB_CREATE_FILE_DEST parameter within the PDB is changed; it becomes a restriction (meaning new datafiles must align with this setting) rather than being just a default. There are some caveats, based on whether Oracle Managed Files (OMFs) or unmanaged files are created, whether they’re inside ASM and whether the CREATE_FILE_DEST option specifies an ASM disk group.
- The DB_CREATE_FILE_DEST parameter within the PDB can now only be changed by SYSDBA connections—not normal PDB DBAs.
- The DB_CREATE_FILE_DEST restrictions not only affect data files and temp files but also extend to RMAN backup pieces.
Of the above, only the first implication is mentioned in the official Oracle documentation for the CREATE PLUGGABLE DATABASE statement. Similarly, the official Oracle documentation for the DB_CREATE_FILE_DEST initialization parameter makes no mention of the other points.
Complexities with DB_CREATE_FILE_DEST
First some important background as a reminder:
- Oracle Managed Files (OMFs) are files with names and paths automatically created and managed by Oracle and are created in the location specified by DB_CREATE_FILE_DEST, DB_CREATE_ONLINE_LOG_DEST_n or DB_RECOVERY_FILE_DEST.
- All files in ASM are OMFs, however you can specify the ASM disk group name—Oracle builds the rest of the path and file name from the disk group onwards.
- File aliases can be used in ASM, but they all reference an OMF.
- Non-Oracle Managed Files are known as “unmanaged files.”
- OMFs and unmanaged files can exist on operating system filesystems.
Now, how the DB_CREATE_FILE_DEST initialization parameter works is dependent on whether the PDB was created with the CREATE_FILE_DEST clause.
When the PDB is not created with the CREATE_FILE_DEST clause, the DB_CREATE_FILE_DEST parameter acts as a default. If no file name is specified in the DDL command, an OMF is created in the location specified by the parameter—simple enough. But unmanaged files can be created in OS filesystem paths and OMFs can be created in ASM (by simply specifying a disk group). It’s only a default and no legitimate file location is blocked.
When the PDB is created with CREATE_FILE_DEST, the situation becomes more complicated. Rather than just being a default, DB_CREATE_FILE_DEST is now enforced but with some variance depending on whether the parameter is set to an ASM disk group or an OS filesystem path, and whether the file being created is in ASM. Confusing!
The full implications and permutations are easiest to summarize in a table (again, applicable only when the PDB was created using the CREATE_FILE_DEST option):
| DB_CREATE_FILE_DEST Set to: | Creating a data file in: | Result: |
| ASM disk group | No-file specification (pure OMF) | PERMITTED |
| A different ASM disk group | INVALID PATH | |
| Unmanaged file in any OS filesystem path | PERMITTED | |
| OS filesystem path | No-file specification (pure OMF) | PERMITTED |
| Any ASM disk group | INVALID PATH | |
| Unmanaged file in a different OS path | INVALID PATH | |
| Unmanaged file in the same OS path | PERMITTED | |
| NULL or an empty string | No-file specification (pure OMF) | INVALID FILE NAME |
| Any ASM disk group | PERMITTED | |
| Unmanaged file in any OS filesystem path | PERMITTED |
These results are a bit surprising. For example, if I created a PDB with CREATE_FILE_DEST (and subsequently DB_CREATE_FILE_DEST) set to an ASM disk group, presumably I want all data files to be in that disk group, so why does Oracle also let me add unmanaged data files to an OS filesystem location?!?!
And, it doesn’t matter what value was used in the CREATE_FILE_DEST clause when the PDB was created (as it can be changed as explained below). All that matters is that the clause was included. After that the DB_CREATE_FILE_DEST parameter enforcement is in place as per the rules in the table above.
Demonstration
First we can create two PDBs: DEMO1 without the CREATE_FILE_DEST clause and DEMO2 with it:
SQL> create pluggable database DEMO1 2 admin user admin identified by "********"; Pluggable database created. SQL> create pluggable database DEMO2 2 admin user admin identified by "********" 3 create_file_dest = '+DATA_DG'; Pluggable database created. SQL> alter pluggable database all open; Pluggable database altered. SQL> |
Now, if we try to create a datafile in DEMO1 outside of the ASM disk group specified in DB_CREATE_FILE_DEST, the command works:
SQL> alter session set container = DEMO1; Session altered. SQL> show parameter db_create_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string +DATA_DG SQL> SQL> SQL> create tablespace USERS datafile '+FRA_DG' size 10m; Tablespace created. SQL> select file_name from dba_data_files where tablespace_name = 'USERS'; FILE_NAME -------------------------------------------------------------------------------- +FRA_DG/ORCL/C1B18FCBEED60ACDE0536B3810AC5C7E/DATAFILE/users.258.1071850539 SQL> |
But if we try the same thing in DEMO2, the command fails:
SQL> alter session set container = DEMO2; Session altered. SQL> show parameter db_create_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string +DATA_DG SQL> SQL> create tablespace USERS datafile '+FRA_DG' size 10m; create tablespace USERS datafile '+FRA_DG' size 10m * ERROR at line 1: ORA-65250: invalid path specified for file - +FRA_DG SQL> |
This illustrates that DB_CREATE_FILE_DEST is a restriction in DEMO2 but only a default in DEMO1.
Surprisingly, this is enforced only for Oracle Managed Files (OMFs)! If we try an unmanaged file (non-OMF), outside of ASM, it works:
SQL> alter session set container = DEMO2; Session altered. SQL> show parameter db_create_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string +DATA_DG SQL> SQL> create tablespace USERS datafile '/u01/oradata/users01.dbf' size 10m; Tablespace created. SQL> |
Testing with RMAN backup pieces yields similar results. This doesn’t seem desirable—arguably it would make sense to enforce the location for data files and temp files (i.e. to a specific ASM disk group) but allow RMAN backups to be written to another disk group.
Adjusting the Parameter
The second change is with respect to which type of user can adjust the parameter.
In the DEMO1 database, any local DBA user can change the parameter:
SQL> alter session set container = DEMO1; Session altered. SQL> grant dba to simon_dba identified by testing; Grant succeeded. SQL> connect simon_dba/testing@localhost:1521/DEMO1 Connected. SQL> SQL> show user con_name USER is "SIMON_DBA" CON_NAME ------------------------------ DEMO1 SQL> SQL> show parameter db_create_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string +DATA_DG SQL> SQL> alter system set db_create_file_dest = '+FRA_DG' scope=both; System altered. SQL> show parameter db_create_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string +FRA_DG SQL> |
But in the DEMO2 database, a local DBA user cannot change it—only a SYSDBA user can:
SQL> alter session set container = DEMO2; Session altered. SQL> grant dba to simon_dba identified by testing; Grant succeeded. SQL> connect simon_dba/testing@localhost:1521/DEMO2 Connected. SQL> SQL> show user con_name USER is "SIMON_DBA" CON_NAME ------------------------------ DEMO2 SQL> SQL> show parameter db_create_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string +DATA_DG SQL> SQL> alter system set db_create_file_dest = '+FRA_DG' scope=both; alter system set db_create_file_dest = '+FRA_DG' scope=both * ERROR at line 1: ORA-32017: failure in updating SPFILE ORA-01031: insufficient privileges SQL> alter system set db_create_file_dest = '+FRA_DG' scope=memory; alter system set db_create_file_dest = '+FRA_DG' scope=memory * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-01031: insufficient privileges SQL> alter system reset db_create_file_dest; alter system reset db_create_file_dest * ERROR at line 1: ORA-32017: failure in updating SPFILE ORA-01031: insufficient privileges SQL> connect / as sysdba Connected. SQL> alter session set container = DEMO2; Session altered. SQL> show user con_name USER is "SYS" CON_NAME ------------------------------ DEMO2 SQL> SQL> alter system set db_create_file_dest = '+FRA_DG' scope=both; System altered. SQL> show parameter db_create_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string +FRA_DG SQL> |
This is similar behavior to restrictions from a PDB Lockdown Profile, but no PDB Lockdown Profile exists:
SQL> connect / as sysdba Connected. SQL> show parameter pdb_lockdown NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pdb_lockdown string SQL> SQL> alter session set container = DEMO2; Session altered. SQL> show parameter pdb_lockdown NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pdb_lockdown string SQL> SQL> select * from dba_lockdown_profiles; no rows selected SQL> |
So, we see PDB Lockdown Profile functionality, but without using an actual Lockdown Profile and only for this one parameter.
Verification
The final question is how to tell whether the PDB was created using CREATE_FILE_DEST. Surprisingly it doesn’t seem to be shown in a catalog view. Rather a BIT flag in CONTAINER$ tracks it using bit 30 (which is 29 in binary, starting at 0):
SQL> SELECT CON_ID_TO_CON_NAME(con_id#) pdb_name, 2 DECODE(BITAND(flags, POWER(2,29)), POWER(2,29), 'YES', 'NO') CREATE_FILE_DEST 3 FROM container$ 4 WHERE status != 4 AND con_id# > 2 5 ORDER BY 1; PDB_NAME CREATE_FILE_DEST ------------------------ ---------------- DEMO1 NO DEMO2 YES SQL> |
Bonus Tip
When decoding bit masks from decimal values, you can use a handy query such as the following provided by Jared Still (just update the con_id# as required) to see which bits are, and are not, set:
WITH
data AS (
SELECT flags
FROM container$
WHERE con_id# = 9
),
masked AS (
SELECT flags
, level-1 bit
, POWER(2,level-1) mask
FROM data
CONNECT BY level <= 64
ORDER BY level
)
SELECT bit
, mask
, DECODE(BITAND(flags, mask), 0, 'NOT SET', ' SET') bitset
FROM masked; |
Conclusion
Unfortunately, the Oracle documentation is not explicitly clear as to what creating a database using CREATE_FILE_DEST really does.
But testing shows at least four key implications of including this clause:
- It sets the default value for DB_CREATE_FILE_DEST in the PDB (documented).
- DB_CREATE_FILE_DEST in the PDB becomes an enforced restriction instead of a default (with some conditions depending on the parameter setting).
- Only SYSDBAs can change the parameter in the PDB (not regular PDB DBAs).
- The enforcement extends to RMAN backup pieces if they are OMFs written to ASM.
I hope this was helpful. If you have any questions or thoughts, please leave them in the comments.
