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):
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.
First some important background as a reminder:
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.
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.
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.
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> |
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;
|
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:
I hope this was helpful. If you have any questions or thoughts, please leave them in the comments.
Ready to optimize your Oracle Database for the future?