DB_CREATE_FILE_DEST is a server parameter which specifies the default destination where new Oracle-managed data files (OMF) should be placed.
In this post, we'll talk about the following topics.
How to Set DB_CREATE_FILE_DEST
Since the server parameter can be changed online, so we can set it at system-level or session-level.
Alter System Set DB_CREATE_FILE_DEST
RAC DB
For RAC databases, we should only specify the diskgroup name for the value, ASM knows where to categorize new data files.
SQL> alter system set db_create_file_dest='+DATA2' sid='*' scope=both;
System altered.
Single Instance DB
In contrast, we should specify the base directory for single-instance databases, because Oracle doesn't categorize new files in file system.
SQL> alter system set db_create_file_dest='/oradata2/ORCLCDB/ORCLPDB' scope=both;
System altered.
It just follows the base directory to create data files.
Alter Session Set DB_CREATE_FILE_DEST
For changing the value temporarily, you can set it in your session.
SQL> alter session set db_create_file_dest='+DATA2';
Session altered.
This could be useful when we want to allocate new datafiles evenly among several diskgroups.
How to Use DB_CREATE_FILE_DEST
To use DB_CREATE_FILE_DEST properly after setting a new place, we have several use cases:
Create Tablespace Datafile
Creating new tablespaces is a very common operation for a new database. With DB_CREATE_FILE_DEST specified, we don't have to specify the absolute path of files.
SQL> create tablespace ERPTBS datafile size 10m autoextend on next 10m maxsize unlimited, size 10m autoextend on next 10m maxsize unlimited;
Tablespace created.
Then we check the full path of the files.
SQL> set linesize 100;
SQL> column name format a60;
SQL> select file#, name from v$datafile where name like '%erptbs%' order by 1;
FILE# NAME
---------- ------------------------------------------------------------
2 +DATA2/ORCL/DATAFILE/erptbs.282.1693915171
8 +DATA2/ORCL/DATAFILE/erptbs.283.1693915171
Alter Tablespace Add Datafile
Adding new datafiles is a very common operation for a database in use. With DB_CREATE_FILE_DEST specified, we don't have to specify the absolute path of files.
SQL> alter tablespace ERPTBS add datafile size 10m autoextend on next 10m maxsize unlimited;
Tablespace altered.
SQL> select file#, name from v$datafile where name like '%erptbs%' order by 1;
FILE# NAME
---------- ------------------------------------------------------------
2 +DATA2/ORCL/DATAFILE/erptbs.282.1693915171
8 +DATA2/ORCL/DATAFILE/erptbs.283.1693915171
9 +DATA2/ORCL/DATAFILE/erptbs.284.1693962938
Alter Database Move Datafile
To move a datafile from the original place to the default one, you don't have to specify the destination. More about moving datafiles to another places, we have some ways to do it.
SQL> alter database move datafile 7;
Database altered.
Then we check the full path of the file.
SQL> select file#, name from v$datafile where file# = 7;
FILE# NAME
---------- ------------------------------------------------------------
7 +DATA2/ORCL/DATAFILE/users.312.1103143591
As for Oracle-managed redo logfiles, you should set DB_CREATE_ONLINE_LOG_DEST_n for the default destination.