Database Data File Management

Db_create_file_dest
The parameter db_create_file_dest is used to specify the location of important database files, including: Data files, temporary files, redo log files, control files, block change tracking files.
Cases:
Db_create_file_dest = '/u01/app/oracle/oradata '--Specifies the default storage location for the data file

Db_recovery_file_dest
The parameter db_recovery_file_dest and parameter db_recovery_file_dest_size are used to specify the default location for the quick recovery area, and the quick recovery area includes the following files:
One or more redo log files, one or more control files, Rman backup files (copies of data files, copies of control files, etc.), archive logs, flashback logs.
Cases:
Db_recovery_file_dest = '/u01/app/oracle/fast_recovery_area '
Db_recovery_file_dest_size = 20G

Db_create_online_log_dest_n
The parameter db_create_online_log_dest_n is used to specify the default location for redo logs and control files, you can specify a directory or ASM disk group, and the last N of the parameter indicates that the parameter can be set multiple repeatedly, but no more than five.
For control files and redo log files, the value set by this parameter is overwritten by the value set by the Db_create_file_dest and db_recovery_file_dest parameters, but if db_create_file_dest is not set, only the Db_ Create_online_log_dest_n parameters, only redo log files and control files can be created through OCF (ORACLE Managed files).

Oracle recommends setting at least two locations, as this greatly improves fault tolerance, for example:
db_create_online_log_dest_1 = '/u02/oradata '
db_create_online_log_dest_2 = '/u03/oradata '

File will be created as OCF (Oracle Managed files)
When you set the appropriate parameters, and there is no explicit information about the file, the file is created as OCF:
* Parameter db_create_file_dest is set in the initialization parameter file.
Db_recovery_file_dest, or Db_create_online_log_dest_n.

* Use the command alter system to dynamically set parameters Db_recovery_file_dest, Db_create_file_dest, or Db_create_online_log_dest_n

* Use the command alter session to dynamically set parameters Db_recovery_file_dest, Db_create_file_dest, or Db_create_online_log_dest_n

If you create an OCF data file, the SQL statement discovers an error or fails for some reason, the data file you create is automatically deleted as if it were failed or error recovery, and in some cases it must be deleted manually.

Naming Oracle Managed files (OCF)
The naming rules for Oracle managed files follow the OFA (Optimal flexible Architecture) standard, and the file names specified should meet the following requirements:
* Database files are easily distinguishable from other documents;
* File names for different file types should explicitly contain related important attributes, such as the name of a data file should contain the name of the tablespace associated with it, and an archive log file containing information such as thread, queue, and creation date.

Oracle managed files are created with no duplicate names, and the file naming rules consist of the following:
* Default location for file creation
* A file naming template is based on the type of file and the type of operating system and whether Oracle Automatic storage management is used
* is a unique string--ensure that the file is created without damaging the existing file
Naming rules:
Destination_prefix/o1_mf_%t_%u_.dbf

Destination_prefix refers to the destination_location/db_unique_name/datafile;
destination_location is the directory specified by the parameter db_create_file_dest;
Db_unique_name is the only name of the target database (via the parameter Db_unique_name setting, if the Db_unique_name parameter is not set, then the value of the initialization parameter db_name will be used);
%t refers to the name of the table space;
%u is a eight-character string that is guaranteed to be unique;

Cases:
Assume that the parameter is set to the following value
Db_create_file_dest =/u01/app/oracle/oradata
Db_unique_name = PAYROLL
So, the name of the data file is as follows
/u01/app/oracle/oradata/payroll/datafile/o1_mf_tbs1_2ixh90q_.dbf

It is important to note that you should never rename these Oracle managed files (OCF), that the database is based on its name to identify whether it is an Oracle managed file, and that if you rename it, the database will no longer recognize it as an Oracle managed file (OCF).

Create a database with Oracle managed files
The CREATE DATABASE statement produces the associated Oracle managed file.

* Specify control files
In the configuration file set initialization parameter Control_files, you can create the corresponding control file press;

* Specify redo log files
The parameter logfile is ignored in the Create DATABASE statement because this parameter is ignored when the Oracle managed redo log file is created.

* Specify system and Sysaux tablespace data files
The datafile and Sysaux datafile parameters in the Create DATABASE statement do not need to be set because they are ignored when creating the Oracle managed tablespace data files for system and Sysaux.

* Specify data files for undo Tablespace
Parameter undo Tablespace's sub-parameter datafile is optional, and its file name does not need to be specified.

Control files
During database creation, the control file is created according to the settings of the Control_files parameter, if the parameter control_files is not set and the initialization parameters required for at least one Oracle managed file creation are set. The Oracle managed control file is created in the default location, and the default location is based on the setting of the initialization parameters, as follows:
1. Set one or more Db_create_online_log_dest_n initialization parameters, the first directory is the location of the primary control file, and the parameters db_create_file_dest and Db_recovery_file_ Dest will be ignored (if set);
2. If only db_create_file_dest is set, the database will create a control file under that directory;
3. Similarly, if only the parameter db_recovery_file_dest is set, the database will create a control file under that directory;

If the initialization parameters required for Control_files and Oracle managed file creation are not set, the default location of the control file will depend on the operating system. All control files created in this form are not Oracle managed files, and you must add the Control_files parameter to the initialization parameter file.
So if you want the database to create Oracle managed control files, if you have SPFile, you add control_files parameters to SPFile, if there is no spfile, then you must manually add pfile parameters to the TXT format control_files file.

Redo log Files
The default location for redo logs is determined by the settings of the following parameters
1. If one or more db_create_online_log_dest_n parameters are set, the database will create a log file in the directory specified by each parameter, but the number of log files is constrained by the value set by the parameter maxlogmembers.
2. If the parameter is removed. Db_create_online_log_dest, the parameters db_create_file_dest and Db_recovery_file_dest are set, and the database will create an Oracle managed file member in the directory set by each parameter , the parameter db_create_file_dest is the first member of the log file for the location that is set.
3. If only the Db_create_file_dest parameter is set or the Db_recovery_file_dest parameter database is set, a log file will be created in the appropriate directory.

The default Oracle managed log file size is 100M. If all of the Oracle managed file creation initialization parameters and logfile are not set, then the CREATE DATABASE statement will fail to execute.

Data files for system and Sysaux table spaces
When you create an Oracle managed tablespace data file for system and Sysaux, the parameters datafile and Sysaux datafile are ignored, and the location of the data file is as follows:
1. Set the Db_create_file_dest parameter only, the other Oracle managed file creation initialization parameters are not set, then the table empty will give the system table space and the Sysaux table space at the location specified by the parameter to create a data file;
2. If the Db_create_file_dest parameter is not set, the name and size of the data file for the system tablespace and Sysaux table space will be dependent on the operating system, and the data files created in this manner are no longer Oracle managed files;
Typically, the size of the Oracle managed file for system and Sysaux is 100M and extensible, each expansion size is 100M or its own size, and you can change the size of each expansion by alter TABLESPACE.
If the Oracle managed file is created, then the datafile parameter will be overwritten, and if none of the parameters are set, the CREATE database execution will fail. When the default parameter is overwritten by an Oracle managed file, the size parameter is set and the Autoextend parameter is not set, then the data file will not expand automatically.

Undo Table Space Data file
Parameter undo tablespace Sub-parameter datafile is optional and the file name does not need to be specified. When the Db_create_file_dest parameter is set and the file name is not given, the Oracle managed file will be created in the parameter specified directory, and if the data file name is not given the Db_create_file_dest parameter is not set, create The database statement will prompt for a syntax error.
Location of the data file:
1. The db_create_file_dest parameter is set, and the data file will be pathbreaker at the specified position of the parameter;
2. Without setting the Db_create_file_dest parameter, the location of the data file will be determined by the operating system.

temporary file for default tablespace
Parameter default temporary The tablespace sub-parameter tempfile is optional, and the file name does not need to be specified. If the file name is not specified but the parameter db_create_file_dest is set, the database will create the Oracle managed temporary file in the directory specified by the parameter, and if the parameter db_create_file_dest is not set, create Database will fail and prompt for syntax errors. The
Default temporary tablespace parameter is optional and is not created if it is not given. The Oracle Managed temp file defaults to 100M and can be expanded wirelessly.

Cases:
Example 1
The following example creates a database and its data files in the form of Oracle managed files, assuming the following
*system tablespace data File under Directory/u01/app/oracle/oradata, and can be unlimited expansion;
*sysaux table space data file in the/u01/app/oracle/oradata directory, and can be infinitely expanded, table space is the local automatic segment management;
* There are two online log file groups in the directory/u02/oradata and/u03/oradata, each group member file size is 100M;
* If automatic udno management mode is enabled (default), the undo Tablespace data file will be created in the/u01/app/oracle/oradata directory, the file size is 20M and can be infinitely expanded, the name of the undo tablespace is Sys_undots;
* If the initialization parameter control_files is not set, then the database will create a control file in/u02/oradata and/u03/oradata respectively, where the main control file is/u02/oradata;

To meet these requirements, you need to set the following parameters in the initialization parameter file:
Db_create_file_dest = '/u01/app/oracle/oradata '
db_create_online_log_dest_1 = '/u02/oradata '
db_create_online_log_dest_2 = '/u03/oradata '

When you are finished, execute the following statement:
CREATE DATABASE sample;

If you want to create a local management table space for the system table space of the database, simply add the parameter extent MANAGEMENT local after creating the statement; If you do not have this parameter, the tablespace will be directory-managed. Oracle officially recommends creating a locally managed system table space
CREATE DATABASE sample EXTENT MANAGEMENT LOCAL;

Example 2
The following example also creates a database and its data files as an Oracle managed file, assuming the following
*system table space data files are under the directory/u01/app/oracle/oradata, and can be wireless expansion;
*sysaux table Spatial data file is under the directory/u01/app/oracle/oradata, and it can be infinitely expanded and is automatically broken space management;
* Two 100M redo log files under directory/u01/app/oracle/oradata, and are not reusable;
* A name and size of Sys_undots and 20M of the undo Tablespace data file under the directory/u01/app/oracle/oradata, and is infinitely expandable;
* Control files under the directory/u01/app/oracle/oradata;

In this example, the following scenarios are also assumed:
* Db_create_online_log_dest_n parameter not set in initialization parameter file;
* Control_files parameter not set in initialization parameter file;
*undo Automatic management mode is turned on;

Next, execute the following statement
ALTER SYSTEM SET db_create_file_dest = '/u01/app/oracle/oradata ';
CREATE DATABASE sample2 EXTENT MANAGEMENT LOCAL;

It is necessary to note that the above configuration is not an example of a database configuration in a production environment, for illustration or testing purposes only; Create a database in a production environment you need to create at least two control files, and redo log files are reusable.

Example 3
In this example, the size of the Oracle managed default temporal tablespace data file and the undo Tablespace data file is given, with the following assumptions:
* A 400M system tablespace data file under directory/u01/app/oracle/oradata (because the size has been specified, all data files are not expandable);
* A 200M sysaux tablespace data file in the directory/u01/app/oracle/oradata (because the size has been specified, all data files are not expandable), table space is automatically broken space management;
* Two redo log file groups are in the directory/u02/oradata and/u03/oradata, and the member files in each group are 100M;
* The default temporary tablespace dflt_ts temporary file in the directory/u01/app/oracle/oradata, the size is 10M, and is non-expandable;
* The Undo table space of size 100M is undo_ts under the directory/u01/app/oracle/oradata, and is not expandable;
* If the Control_files initialization parameter is not set, the two control files will be created in the directory/u02/oradata and/u03/oradata respectively, where/u02/oradata is the main control file;

Next, set the following parameters in the initialization parameter file:
Db_create_file_dest = '/u01/app/oracle/oradata '
db_create_online_log_dest_1 = '/u02/oradata '
db_create_online_log_dest_2 = '/u03/oradata '

Then, execute the following SQL statement:
CREATE DATABASE Sample3
EXTENT MANAGEMENT LOCAL
DataFile SIZE 400M
Sysaux datafile SIZE 200M
DEFAULT temporary tablespace dflt_ts tempfile SIZE 10M
UNDO tablespace undo_ts datafile SIZE 100M;

Database data File Management