Creating Oracle-Managed Files - Oracle 10g

If you have met any of the following conditions, then Oracle Database creates Oracle-managed files for you, as appropriate, when no file specification is given in the creation operation:

• You have included any of the DB _CREATE _FILE _DEST, DB _REDOVERY _FILE _DEST, or DB _CREATE _ONLINE _LOG _DEST _n initialization parameters in your initialization parameter file.
• You have issued the ALTER SYSTEM statement to dynamically set any of DB _RECOVERY _FILE _DEST, DB _CREATE _FILE _DEST, or DB _CREATE _ONLINE _LOG _DEST _n initialization parameters
• You have issued the ALTER SESSION statement to dynamically set any of the DB _CREATE _FILE _DEST, DB_RECOVERY_FILE_DEST, or DB _CREATE _ONLINE _LOG _DEST _n initialization parameters.

If a statement that creates an Oracle-managed file finds an error or does not complete due to some failure, then any Oracle-managed files created by the statement are automatically deleted as part of the recovery of the error or failure. However, because of the large number of potential errors that can occur with file systems and storage subsystems, there can be situations where you must manually remove the files using operating system commands. When an Oracle-managed file is created, its filename is written to the alert file. This information can be used to find the file if it is necessary to manually remove the file.

The following topics are discussed in this section:

• How Oracle-Managed Files Are Named
• Creating Oracle-Managed Files at Database Creation
• Creating Datafiles for Tablespaces Using Oracle-Managed Files
• Creating Tempfiles for Temporary Tablespaces Using Oracle-Managed Files
• Creating Control Files Using Oracle-Managed Files
• Creating Redo Log Files Using Oracle-Managed Files
• Creating Archived Logs Using Oracle-Managed Files

How Oracle-Managed Files Are Named

The filenames of Oracle-managed files comply with the Optimal Flexible Architecture (OFA) standard for file naming. The assigned names are intended to meet the following requirements:

• Database files are easily distinguishable from all other files.
• Files of one database type are easily distinguishable from other database types.
• Files are clearly associated with important attributes specific to the file type. For example, a datafile name may include the tablespace name to allow for easy association of datafile to tablespace, or an archived log name may include the thread, sequence, and creation date.

No two Oracle-managed files are given the same name. The name that is used for creation of an Oracle-managed file is constructed from three sources:

• The default creation location
• A file name template that is chosen based on the type of the file. The template also depends on the operating system platform and whether or not automatic storage management is used.
• A unique string created by the Oracle Database server or the operating system.

This ensures that file creation does not damage an existing file and that the file cannot be mistaken for some other file.
As a specific example, filenames for Oracle-managed files have the following format on a Solaris file system:

where:

• <destination_prefix> is <destination_location>/<db_unique_
  name>/<datafile>

  where:

  • <destination_location> is the location specified in DB_CREATE_FILE_DEST
  • <db_unique_name> is the globally unique name (DB_UNIQUE_NAME initialization parameter) of the target database. If there is no DB_UNIQUE_NAME parameter, then the DB_NAME initialization parameter value is used.
• %t is the tablespace name.
• %u is an eight-character string that guarantees uniqueness

For example, assume the following parameter settings:

DB_CREATE_FILE_DEST = /u01/oradata DB_UNIQUE_NAME = PAYROLL

Then an example datafile name would be:

Names for other file types are similar. Names on other platforms are also similar, subject to the constraints of the naming rules of the platform.

The examples on the following pages use Oracle-managed file names as they might appear with a Solaris file system as an OMF destination.

Creating Oracle-Managed Files at Database Creation

The behavior of the CREATE DATABASE statement for creating database structures when using Oracle-managed files is discussed in this section.

Specifying Control Files at Database Creation

At database creation, the control file is created in the files specified by the CONTROL_FILES initialization parameter. If the CONTROL_FILES parameter is not set and at least one of the initialization parameters required for the creation of Oracle-managed files is set, then an Oracle-managed control file is created in thedefault control file destinations. In order of precedence, the default destination is defined as follows:

• One or more control files as specified in the DB _CREATE _ONLINE _LOG _DEST _n initialization parameter. The file in the first directory is the primary control file. When DB _CREATE _ONLINE _LOG _DEST _n is specified, the database does not create a control file in DB _CREATE _FILE _DEST or in DB _RECOVERY _FILE _DEST (the flash recovery area).
• If no value is specified for DB_CREATE_ONLINE_LOG_DEST_n, but values are set for both the DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST, then the database creates one control file in each location. The location specified in DB_CREATE_FILE_DEST is the primary control file.
• If a value is specified only for DB_CREATE_FILE_DEST, then the database creates one control file in that location.
• If a value is specified only for DB_RECOVERY_FILE_DEST, then the database creates one control file in that location.

If the CONTROL_FILES parameter is not set and none of these initialization parameters are set, then the Oracle Database default behavior is operating system dependent. At least one copy of a control file is created in an operating system dependent default location. Any copies of control files created in this fashion are not Oracle-managed files, and you must add a CONTROL_FILES initialization parameter to any initialization parameter file. If the database creates an Oracle-managed control file, and if there is a server parameter file, then the database creates a CONTROL_FILES initialization parameter entry in the server parameter file. If there is no server parameter file, then you must manually include a CONTROL_FILES initialization parameter entry in the text initialization parameter file.

Specifying Redo Log Files at Database Creation

The LOGFILE clause is not required in the CREATE DATABASE statement, and omitting it provides a simple means of creating Oracle-managed redo log files. If the LOGFILE clause is omitted, then redo log files are created in the default redo log file destinations. In order of precedence, the default destination is defined as follows:

• If either the DB_CREATE_ONLINE_LOG_DEST_n is set, then the database creates a log file member in each directory specified, up to the value of the MAXLOGMEMBERS initialization parameter.
• If the DB _CREATE _ONLINE _LOG _DEST _n parameter is not set, but both the DB _CREATE _FILE _DEST and DB _RECOVERY _FILE _DEST nitialization parameters are set, then the database creates one Oracle-managed log file member in each of those locations. The log file in the DB _CREATE _FILE _DEST destination is the first member.
• If only the DB _CREATE _FILE _DEST initialization parameter is specified, then the database creates a log file member in that location.
• If only the DB_RECOVERY_FILE_DEST initialization parameter is specified, then the database creates a log file member in that location. The default size of an Oracle-managed redo log file is 100 MB.

Optionally, you can create Oracle-managed redo log files, and override default attributes, by including the LOGFILE clause but omitting a filename. Redo log files are created the same way, except for the following: If no filename is provided in the LOGFILE clause of CREATE DATABASE, and none of the initialization parameters required for creating Oracle-managed files are provided, then the CREATE DATABASE statement fails.

Specifying the SYSTEM and SYSAUX Tablespace Datafiles at Database Creation

The DATAFILE or SYSAUX DATAFILE clause is not required in the CREATE DATABASE statement, and omitting it provides a simple means of creating Oracle-managed datafiles for the SYSTEM and SYSAUX tablespaces. If the DATAFILE clause is omitted, then one of the following actions occurs:

• If DB _CREATE _FILE _DEST is set, then one Oracle-managed datafile for the SYSTEM tablespace and another for the SYSAUX tablespace are created in the DB _CREATE _FILE _DEST directory.
• If DB _CREATE _FILE _DEST is not set, then the database creates one SYSTEM, and one SYSAUX, tablespace datafile whose name and size are operating system dependent. Any SYSTEM or SYSAUX tablespace datafile created in this manner is not an Oracle-managed file.

The default size for an Oracle-managed datafile is 100 MB and the file is autoextensible. When autoextension is required, the database extends the datafile by its existing size or 100 MB, whichever is smaller. You can also explicitly specify the autoextensible unit using the NEXT parameter of the STORAGE clause when you specify the datafile (in a CREATE or ALTER TABLESPACE operation).

Optionally, you can create an Oracle-managed datafile for the SYSTEM or SYSAUX tablespace and override default attributes. This is done by including the DATAFILE clause, omitting a filename, but specifying overriding attributes. When a filename is not supplied and the DB_CREATE_FILE_DEST parameter is set, an Oracle-managed datafile for the SYSTEM or SYSAUX tablespace is created in the DB_CREATE_FILE_DEST directory with the specified attributes being overridden. However, if a filename is not supplied and the DB_CREATE_FILE_DEST parameter is not set, then the CREATE DATABASE statement fails.

When overriding the default attributes of an Oracle-managed file, if a SIZE value is specified but no AUTOEXTEND clause is specified, then the datafile is not autoextensible.

Specifying the Undo Tablespace Datafile at Database Creation

The DATAFILE subclause of the UNDO TABLESPACE clause is optional and a filename is not required in the file specification. If a filename is not supplied and the DB _CREATE _FILE _DEST parameter is set, then an Oracle-managed datafile is created in the DB_CREATE_FILE_DEST directory. If DB _CREATE _FILE _DEST is not set, then the statement fails with a syntax error.

The UNDO TABLESPACE clause itself is optional in the CREATE DATABASE statement. If it is not supplied, and automatic undo management mode is enabled, then a default undo tablespace named SYS_UNDOTBS is created and a 10 MB datafile that is autoextensible is allocated as follows:

• If DB_CREATE_FILE_DEST is set, then an Oracle-managed datafile is created in the indicated directory.
• If DB_CREATE_FILE_DEST is not set, then the datafile location is operating system specific.

Specifying the Default Temporary Tablespace Tempfile at Database Creation

The TEMPFILE subclause is optional for the DEFAULT TEMPORARY TABLESPACE clause and a filename is not required in the file specification. If a filename is not supplied and the DB_CREATE_FILE_DEST parameter set, then an Oracle-managed tempfile is created in the DB_CREATE_FILE_DEST directory. If DB_CREATE_FILE_ DEST is not set, then the CREATE DATABASE statement fails with a syntax error.

The DEFAULT TEMPORARY TABLESPACE clause itself is optional. If it is not specified, then no default temporary tablespace is created.

The default size for an Oracle-managed tempfile is 100 MB and the file is autoextensible with an unlimited maximum size.

CREATE DATABASE Statement Using Oracle-Managed Files: Examples

This section contains examples of the CREATE DATABASE statement when using the Oracle-managed files feature.

CREATE DATABASE: Example 1 This example creates a database with the following Oracle-managed files:

• A SYSTEM tablespace datafile in directory /u01/oradata that is 100 MB and auto extensible up to an unlimited size.
• A SYSAUX tablespace datafile in directory /u01/oradata that is 100 MB and auto extensible up to an unlimited size. The tablespace is locally managed with automatic segment-space management.
• Two online log groups with two members of 100 MB each, one each in /u02/oradata and /u03/oradata.
• If automatic undo management mode is enabled, then an undo tablespace datafile in directory /u01/oradata that is 10 MB and autoextensible up to an unlimited size. An undo tablespace named SYS_UNDOTBS is created.
• If no CONTROL_FILES initialization parameter is specified, then two control files, one each in /u02/oradata and /u03/oradata. The control file in /u02/oradata is the primary control file.

The following parameter settings relating to Oracle-managed files, are included in the initialization parameter file:

DB_CREATE_FILE_DEST = '/u01/oradata' DB_CREATE_ONLINE_LOG_DEST_1 = '/u02/oradata' DB_CREATE_ONLINE_LOG_DEST_2 = '/u03/oradata' The following statement is issued at the SQL prompt: SQL> CREATE DATABASE sample;

CREATE DATABASE: Example 2 This example creates a database with the following Oracle-managed files:

• A 100 MB SYSTEM tablespace datafile in directory /u01/oradata that is autoextensible up to an unlimited size.
• A SYSAUX tablespace datafile in directory /u01/oradata that is 100 MB and autoextensible up to an unlimited size. The tablespace is locally managed with automatic segment-space management.
• Two redo log files of 100 MB each in directory /u01/oradata. They are not multiplexed.
• An undo tablespace datafile in directory /u01/oradata that is 10 MB and autoextensible up to an unlimited size. An undo tablespace named SYS_ UNDOTBS is created.
• A control file in /u01/oradata.

In this example, it is assumed that:

• No DB_CREATE_ONLINE_LOG_DEST_n initialization parameters are specified in the initialization parameter file.
• No CONTROL_FILES initialization parameter was specified in the initialization parameter file.
• Automatic undo management mode is enabled.

The following statements are issued at the SQL prompt:

SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/u01/oradata'; SQL> CREATE DATABASE sample2;

This database configuration is not recommended for a production database. The example illustrates how a very low-end database or simple test database can easily be created. To better protect this database from failures, at least one more control file should be created and the redo log should be multiplexed.

CREATE DATABASE: Example 3 In this example, the file size for the Oracle-managed files for the default temporary tablespace and undo tablespace are specified. A database with the following Oracle-managed files is created:

• A 400 MB SYSTEM tablespace datafile in directory /u01/oradata. Because SIZE is specified, the file in not autoextensible.
• A 200 MB SYSAUX tablespace datafile in directory /u01/oradata. Because SIZE is specified, the file in not autoextensible. The tablespace is locally managed with automatic segment-space management.
• Two redo log groups with two members of 100 MB each, one each in directories /u02/oradata and /u03/oradata.
• For the default temporary tablespace dflt_ts, a 10 MB tempfile in directory /u01/oradata. Because SIZE is specified, the file in not autoextensible.
• For the undo tablespace undo_ts, a 10 MB datafile in directory /u01/oradata. Because SIZE is specified, the file in not autoextensible.
• If no CONTROL_FILES initialization parameter was specified, then two control files, one each in directories /u02/oradata and /u03/oradata. The control file in /u02/oradata is the primary control file.

The following parameter settings are included in the initialization parameter file:

DB_CREATE_FILE_DEST = '/u01/oradata' DB_CREATE_ONLINE_LOG_DEST_1 = '/u02/oradata' DB_CREATE_ONLINE_LOG_DEST_2 = '/u03/oradata'

The following statement is issued at the SQL prompt:

SQL> CREATE DATABASE sample3 DATAFILE SIZE 400M 2> SYSAUX DATAFILE SIZE 200M 3> DEFAULT TEMPORARY TABLESPACE dflt_ts TEMPFILE SIZE 10M 4> UNDO TABLESPACE undo_ts DATAFILE SIZE 10M;

Creating Datafiles for Tablespaces Using Oracle-Managed Files

The following statements that can create datafiles are relevant to the discussion in this section:

• CREATE TABLESPACE
• CREATE UNDO TABLESPACE
• ALTER TABLESPACE ... ADD DATAFILE

When creating a tablespace, either a regular tablespace or an undo tablespace, the DATAFILE clause is optional. When you include the DATAFILE clause the filename is optional. If the DATAFILE clause or filename is not provided, then the following rules apply:

• If the DB_CREATE_FILE_DEST initialization parameter is specified, then an Oracle-managed datafile is created in the location specified by the parameter.
• If the DB_CREATE_FILE_DEST initialization parameter is not specified, then the statement creating the datafile fails.

When you add a datafile to a tablespace with the ALTER TABLESPACE ... ADD DATAFILE statement the filename is optional. If the filename is not specified, then the same rules apply as discussed in the previous paragraph.

By default, an Oracle-managed datafile for a regular tablespace is 100 MB and is autoextensible with an unlimited maximum size. However, if in your DATAFILE clause you override these defaults by specifying a SIZE value (and no AUTOEXTEND clause), then the datafile is not autoextensible.

CREATE TABLESPACE: Examples

The following are some examples of creating tablespaces with Oracle-managed files.

CREATE TABLESPACE: Example 1 The following example sets the default location for datafile creations to /u01/oradata and then creates a tablespace tbs_ 1 with a datafile in that location. The datafile is 100 MB and is autoextensible with an unlimited maximum size.

SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/u01/oradata'; SQL> CREATE TABLESPACE tbs_1;

CREATE TABLESPACE: Example 2 This example creates a tablespace named tbs_2 with a datafile in the directory /u01/oradata. The datafile initial size is 400 MB, and because the SIZE clause is specified, the datafile is not autoextensible.

The following parameter setting is included in the initialization parameter file:

The following statement is issued at the SQL prompt:

CREATE TABLESPACE: Example 3 This example creates a tablespace named tbs_3 with an autoextensible datafile in the directory /u01/oradata with a maximum size of 800 MB and an initial size of 100 MB:

The following parameter setting is included in the initialization parameter file:

The following statement is issued at the SQL prompt:

CREATE TABLESPACE: Example 4 The following example sets the default location for datafile creations to /u01/oradata and then creates a tablespace named tbs_4 in that directory with two datafiles. Both datafiles have an initial size of 200 MB, and because a SIZE value is specified, they are not autoextensible

SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/u01/oradata'; SQL> CREATE TABLESPACE tbs_4 DATAFILE SIZE 200M SIZE 200M;

CREATE UNDO TABLESPACE: Example

The following example creates an undo tablespace named undotbs_1 with a datafile in the directory /u01/oradata. The datafile for the undo tablespace is 100 MB and is autoextensible with an unlimited maximum size.

The following parameter setting is included in the initialization parameter file:

The following statement is issued at the SQL prompt:

ALTER TABLESPACE: Example

This example adds an Oracle-managed autoextensible datafile to the tbs_1 tablespace. The datafile has an initial size of 100 MB and a maximum size of 800 MB.

The following parameter setting is included in the initialization parameter file:

The following statement is entered at the SQL prompt:

Creating Tempfiles for Temporary Tablespaces Using Oracle-Managed Files

The following statements that create tempfiles are relevant to the discussion in this section:

• CREATE TEMPORARY TABLESPACE
• ALTER TABLESPACE ... ADD TEMPFILE

When creating a temporary tablespace the TEMPFILE clause is optional. If you include the TEMPFILE clause, then the filename is optional. If the TEMPFILE clause or filename is not provided, then the following rules apply:

• If the DB_CREATE_FILE_DEST initialization parameter is specified, then an Oracle-managed tempfile is created in the location specified by the parameter.
• If the DB_CREATE_FILE_DEST initialization parameter is not specified, then the statement creating the tempfile fails.

When you add a tempfile to a tablespace with the ALTER TABLESPACE ... ADD TEMPFILE statement the filename is optional. If the filename is not specified, then the same rules apply as discussed in the previous paragraph.

When overriding the default attributes of an Oracle-managed file, if a SIZE value is specified but no AUTOEXTEND clause is specified, then the datafile is not autoextensible.

CREATE TEMPORARY TABLESPACE: Example

The following example sets the default location for datafile creations to /u01/oradata and then creates a tablespace named temptbs_1 with a tempfile in that location. The tempfile is 100 MB and is autoextensible with an unlimited maximum size.

ALTER TABLESPACE ... ADD TEMPFILE: Example

The following example sets the default location for datafile creations to /u03/oradata and then adds a tempfile in the default location to a tablespace named temptbs_1. The tempfile initial size is 100 MB. It is autoextensible with an unlimited maximum size.

SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/u03/oradata'; SQL> ALTER TABLESPACE TBS_1 ADD TEMPFILE;

Creating Control Files Using Oracle-Managed Files

When you issue the CREATE CONTROLFILE statement, a control file is created (or reused, if REUSE is specified) in the files specified by the CONTROL_FILES initialization parameter. If the CONTROL_FILES parameter is not set, then the control file is created in the default control file destinations.

If Oracle Database creates an Oracle-managed control file, and there is a server parameter file, then the database creates a CONTROL_FILES initialization parameter for the server parameter file. If there is no server parameter file,then you must create a CONTROL_FILES initialization parameter manually and include it in the initialization parameter file.

If the datafiles in the database are Oracle-managed files, then the database-generated filenames for the files must be supplied in the DATAFILE clause of the statement.

If the redo log files are Oracle-managed files, then the NORESETLOGS or RESETLOGS keyword determines what can be supplied in the LOGFILE clause:

• If the NORESETLOGS keyword is used, then the database-generated filenames for the Oracle-managed redo log files must be supplied in the LOGFILE clause.
• If the RESETLOGS keyword is used, then the redo log file names can be supplied as with the CREATE DATABASE statement.

The sections that follow contain examples of using the CREATE CONTROLFILE statement with Oracle-managed files.

CREATE CONTROLFILE Using NORESETLOGS Keyword: Example

The following CREATE CONTROLFILE statement is generated by an ALTER DATABASE BACKUP CONTROLFILE TO TRACE statement for a database with Oracle-managed datafiles and redo log files:

CREATE CONTROLFILE DATABASE sample LOGFILE GROUP 1 ('/u01/oradata/SAMPLE/onlinelog/o1_mf_1_o220rtt9_.log', '/u02/oradata/SAMPLE/onlinelog/o1_mf_1_v2o0b2i3_.log') SIZE 100M, GROUP 2 ('/u01/oradata/SAMPLE/onlinelog/o1_mf_2_p22056iw_.log', '/u02/oradata/SAMPLE/onlinelog/o1_mf_2_p02rcyg3_.log') SIZE 100M NORESETLOGS DATAFILE '/u01/oradata/SAMPLE/datafile/o1_mf_system_xu34ybm2_.dbf' SIZE 100M, '/u01/oradata/SAMPLE/datafile/o1_mf_sysaux_aawbmz51_.dbf' SIZE 100M, '/u01/oradata/SAMPLE/datafile/o1_mf_sys_undotbs_apqbmz51_.dbf' SIZE 100M MAXLOGFILES 5 MAXLOGHISTORY 100 MAXDATAFILES 10 MAXINSTANCES 2 ARCHIVELOG;

CREATE CONTROLFILE Using RESETLOGS Keyword: Example

The following is an example of a CREATE CONTROLFILE statement with the RESETLOGS option. Some combination of DB_CREATE_FILE_DEST, DB_ RECOVERY_FILE_DEST, and DB_CREATE_ONLINE_LOG_DEST_n or must be set.

CREATE CONTROLFILE DATABASE sample RESETLOGS DATAFILE '/u01/oradata/SAMPLE/datafile/o1_mf_system_aawbmz51_.dbf','/u01/oradata/SAMPLE/datafile/o1_mf_sysaux_axybmz51_.dbf','/u01/oradata/SAMPLE/datafile/o1_mf_sys_undotbs_azzbmz51_.dbf' SIZE 100M MAXLOGFILES 5 MAXLOGHISTORY 100 MAXDATAFILES 10 MAXINSTANCES 2 ARCHIVELOG;

Later, you must issue the ALTER DATABASE OPEN RESETLOGS statement to re-create the redo log files. If the previous log files are Oracle-managed files, then they are not deleted.

Creating Redo Log Files Using Oracle-Managed Files

Redo log files are created at database creation time. They can also be created when you issue either of the following statements:

• ALTER DATABASE ADD LOGFILE
• ALTER DATABASE OPEN RESETLOGS

Using the ALTER DATABASE ADD LOGFILE Statement

The ALTER DATABASE ADD LOGFILE statement lets you later add a new group to your current redo log. The filename in the ADD LOGFILE clause is optional if you are using Oracle-managed files. If a filename is not provided, then a redo log file is created in the default log file destination.

If a filename is not provided and you have not provided one of the initialization parameters required for creating Oracle-managed files, then the statement returns an error.

The default size for an Oracle-managed log file is 100 MB.

You continue to add and drop redo log file members by specifying complete filenames.

Adding New Redo Log Files: Example The following example creates a log group with a member in /u01/oradata and another member in /u02/oradata. The size of each log file is 100 MB.

The following parameter settings are included in the initialization parameter file:

DB_CREATE_ONLINE_LOG_DEST_1 = '/u01/oradata' DB_CREATE_ONLINE_LOG_DEST_2 = '/u02/oradata'

The following statement is issued at the SQL prompt:

Using the ALTER DATABASE OPEN RESETLOGS Statement

If you previously created a control file specifying RESETLOGS and either did not specify filenames or specified nonexistent filenames, then the database creates redo log files for you when you issue the ALTER DATABASE OPEN RESETLOGS statement. The rules for determining the directories in which to store redo log files, when none are specified in the control file.

Creating Archived Logs Using Oracle-Managed Files

Archived logs are created in the DB_RECOVERY_FILE_DEST location when:

• The ARC or LGWR background process archives an online redo log or
• An ALTER SYSTEM ARHIVE LOG CURRENT statement is issued.

For example, assume that the following parameter settings are included in the initialization parameter file:

DB_RECOVERY_FILE_DEST_SIZE = 20G DB_RECOVERY_FILE_DEST = '/u01/oradata' LOG_ARCHIVE_DEST_1= 'LOCATION=USE_DB_RECOVERY_FILE_DEST'