3
Using Oracle-Managed Files

This chapter discusses the use of the Oracle-managed files and contains the following topics:

• What are Oracle-Managed Files?

• Enabling the Creation and Use of Oracle-Managed Files

• Creating Oracle-Managed Files

• Behavior of Oracle-Managed Files

• Scenarios for Using Oracle-Managed Files

What are Oracle-Managed Files?

Using Oracle-managed files simplifies the administration of an Oracle database. Oracle-managed files eliminate the need for you, the DBA, to directly manage the operating system files comprising an Oracle database. You specify operations in terms of database objects rather than filenames. Oracle internally uses standard file system interfaces to create and delete files as needed for the following database structures:

• Tablespaces

• Online redo log files

• Control files

Through initialization parameters, you specify the file system directory to be used for a particular type of file. Oracle then ensures that a unique file, an Oracle-managed file, is created and deleted when no longer needed.

This feature does not affect the creation or naming of administrative files such as trace files, audit files, alert files, and core files.

Who Can Use Oracle-Managed Files?

Oracle-managed files are most useful for the following types of databases:

• Low end or test databases

• Databases that are supported by the following:
  • A logical volume manager that supports striping/RAID and dynamically extensible logical volumes

  • A file system that provides large, extensible files

The Oracle Managed Files feature is not intended to ease administration of systems that use raw disks. This feature provides better integration with operating system functionality for disk space allocation. Since there is no operating system support for allocation of raw disks (it is done manually), this feature cannot help. On the other hand, because Oracle-managed files require that you use the operating system file system (unlike raw disks), you lose control over how files are laid out on the disks and thus, you lose some I/O tuning ability.

What is a Logical Volume Manager?

A logical volume manager (LVM) is a software package available with most operating systems. Sometimes it is called a logical disk manager (LDM). It allows pieces of multiple physical disks to be combined into a single contiguous address space that appears as one disk to higher layers of software. An LVM can make the logical volume have better capacity, performance, reliability, and availability characteristics than any of the underlying physical disks. It uses techniques such as mirroring, striping, concatenation, and RAID 5 to implement these characteristics.

Some LVMs allow the characteristics of a logical volume to be changed after it is created, even while it is in use. The volume may be resized or mirrored, or it may be relocated to different physical disks.

What is a File System?

A file system is a data structure built inside a contiguous disk address space. A file manager (FM) is a software package that manipulates file systems, but it is sometimes called the file system. All operating systems have file managers. The primary task of a file manager is to allocate and deallocate disk space into files within a file system.

A file system allows the disk space to be allocated to a large number of files. Each file is made to appear as a contiguous address space to applications such as Oracle. The files may not actually be contiguous within the disk space of the file system. Files can be created, read, written, resized, and deleted. Each file has a name associated with it that is used to refer to the file.

A file system is commonly built on top of a logical volume constructed by an LVM. Thus all the files in a particular file system have the same performance, reliability, and availability characteristics inherited from the underlying logical volume. A file system is a single pool of storage that is shared by all the files in the file system. If a file system is out of space, then none of the files in that file system can grow. Space available in one file system does not affect space in another file system. However some LVM/FM combinations allow space to be added or removed from a file system.

An operating system can support multiple file systems. Multiple file systems are constructed to give different storage characteristics to different files as well as to divide the available disk space into pools that do not affect each other.

Benefits of Using Oracle-Managed Files

Consider the following benefits of using Oracle-managed files:

• They make the administration of the database easier.

  There is no need to invent filenames and define specific storage requirements. A consistent set of rules is used to name all relevant files. The file system defines the characteristics of the storage and the pool where it is allocated.

• They reduce corruption caused by administrators specifying the wrong file.

  Each Oracle-managed file and filename is unique. Using the same file in two different databases is a common mistake that can cause very large down times and loss of committed transactions. Using two different names that refer to the same file is another mistake that causes major corruptions.

• They reduce wasted disk space consumed by obsolete files.

  Oracle automatically removes old Oracle-managed files when they are no longer needed. Much disk space is wasted in large systems simply because no one is sure if a particular file is still required. This also simplifies the administrative task of removing files that are no longer required on disk, and prevents the mistake of deleting the wrong file.

• They simplify creation of test and development databases.

  You can minimize the time spent making decisions regarding file structure and naming, and you have fewer file management tasks. You can focus better on meeting the actual requirements of your test or development database.

• Oracle-managed files make development of portable third-party tools easier.

  Oracle-managed files eliminate the need to put operating system specific file names in SQL scripts.

Oracle-Managed Files and Existing Functionality

Using Oracle-managed files does not eliminate any existing functionality. Existing databases are able to operate as they always have. New files can be created as managed files while old ones are administered in the old way. Thus, a database can have a mixture of Oracle-managed and unmanaged files.

Enabling the Creation and Use of Oracle-Managed Files

The following initialization parameters allow the database server to use the Oracle Managed Files feature:

Parameter	Description
DB_CREATE_FILE_DEST	Defines the location of the default file system directory where Oracle creates datafiles or tempfiles when no file specification is given in the creation operation. Also used as the default file system directory for online redo log and control files if DB_CREATE_ONLINE_LOG_DEST_n is not specified.
DB_CREATE_ONLINE_LOG_DEST_n	Defines the location of the default file system directory for online redo log files and control file creation when no file specification is given in the creation operation. You can use this initialization parameter multiple times, where n specifies a multiplexed copy of the online redo log or control file. You can specify up to five multiplexed copies.

The file system directory specified by either of these parameters must already exist: Oracle does not create it. The directory must also have permissions to allow Oracle to create the files in it.

The default location is used whenever a location is not explicitly specified for the operation creating the file. Oracle creates the filename, and a file thus created is an Oracle-managed file.

Both of these initialization parameters are dynamic, and can be set using the ALTER SYSTEM or ALTER SESSION statement.

See Also: Oracle9i Database Reference for additional information about initialization parameters. "How Oracle-Managed Files are Named"

Setting the DB_CREATE_FILE_DEST Initialization Parameter

Include the DB_CREATE_FILE_DEST initialization parameter in your initialization parameter file to identify the default location for the database server to create:

• Datafiles

• Tempfiles

• Online redo log files

• Control files

You specify the name of a file system directory that becomes the default location for the creation of the operating system files for these entities. The following example sets /u01/oradata/payroll as the default directory to use when creating Oracle-managed files.

DB_CREATE_FILE_DEST = '/u01/oradata/payroll'

Setting the DB_CREATE_ONLINE_LOG_DEST_n Initialization Parameter

Include the DB_CREATE_ONLINE_LOG_DEST_n initialization parameter in your initialization parameter file to identify the default location for the database server to create:

• Online redo log files

• Control files

You specify the name of a file system directory that becomes the default location for the creation of the operating system files for these entities. You can specify up to five multiplexed locations.

For the creation of online redo log files and control files only, this parameter overrides any default location specified in the DB_CREATE_FILE_DEST initialization parameter. If you do not specify a DB_CREATE_FILE_DEST parameter, but you do specify this parameter, then only online redo log files and control files can be created as Oracle-managed files.

It is recommended that you specify at least two parameters. For example:

DB_CREATE_ONLINE_LOG_DEST_1 = '/u02/oradata/payroll'
DB_CREATE_ONLINE_LOG_DEST_2 = '/u03/oradata/payroll'

This allows multiplexing, which provides greater fault-tolerance for the online redo log and control file if one of the destinations fails.

Creating Oracle-Managed Files

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

• You have included either or both of the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n initialization parameters in your initialization parameter file.

• You have issued the ALTER SYSTEM or ALTER SESSION statement to dynamically set either or both the DB_CREATE_FILE_DEST and 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

• Creating Tempfiles for Temporary Tablespaces

• Creating Control Files

• Creating Online Redo Log Files

  See Also: Oracle9i SQL Reference

How Oracle-Managed Files are Named

The filenames of Oracle-managed files comply with the Oracle 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.

• Control files, online redo log files, and datafiles are identifiable as such.

• The association of datafile to tablespace is clearly indicated.

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 file system directory location

• A port-specific file name template that is chosen based on the type of file

• 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 Solaris:

File Type	Format	Example
Datafile	ora_%t_%u.dbf	/u01/oradata/payroll/ora_tbs1_2ixfh90q.dbf
Tempfile	ora_%t_%u.tmp	/u01/oradata/payroll/ora_temp1_6dygh80r.tmp
Redo log file	ora_%g_%u.log	/u01/oradata/payroll/ora_1_wo94n2xi.log
Control file	ora_%u.ctl	/u01/oradata/payroll/ora_cmr7t30p.ctl

where:

• %t is the tablespace name. At most, eight characters of the tablespace name are used. If eight characters causes the name to be too long, then the tablespace name is truncated. Placing the tablespace name before the uniqueness string means that all the datafiles for a tablespace appear next to each other in an alphabetic file listing.

• %u is an eight character string that guarantees uniqueness

• %g is the online redo log file group number

On other platforms the names are similar, subject to the constraints of the platform's naming rules.

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 the default control file destinations. In order of precedence, the default destination is defined as follows:

• If DB_CREATE_ONLINE_LOG_DEST_n initialization parameters are specified, then an Oracle-managed control file copy is created in each directory specified. The file in the first directory is the primary control file.

• If the DB_CREATE_FILE_DEST initialization parameter is specified, and no DB_CREATE_ONLINE_LOG_DEST_n initialization parameters are specified, then an Oracle-managed control file is created in the directory specified.

If the CONTROL_FILES parameter is not set and none of the above initialization parameters are set, then Oracle's 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 Oracle creates an Oracle-managed control file, and if there is a server parameter file, Oracle creates a CONTROL_FILES initialization parameter entry the server parameter file. If there is no server parameter file, then you must create a CONTROL_FILES initialization parameter entry manually and include it in the text initialization parameter file.

See Also: Chapter 6, "Managing Control Files"

Specifying Online 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 online redo log files. If the LOGFILE clause is omitted, then online redo log files are created in the default online redo log file destinations. In order of precedence, the default destination is defined as follows:

• If DB_CREATE_ONLINE_LOG_DEST_n initialization parameters are specified, then Oracle creates two online redo files in each directory specified. More specifically, Oracle creates two online redo groups with corresponding members in each directory specified. These online redo log files are Oracle-managed files.

• If the DB_CREATE_FILE_DEST initialization parameter is specified, but no DB_CREATE_ONLINE_LOG_DEST_n parameters are specified, then two online redo log files (two groups with one member each) are created in the directory specified. These online redo log files are Oracle-managed files.

• If the LOGFILE clause is omitted and neither of the above initialization parameters are specified, then two online redo log files are created in operating system dependent default locations. Any online redo log files created in this fashion are not Oracle-managed files.

The default size of an Oracle-managed online redo log file is 100 (MB).

Optionally, you can create Oracle-managed online redo log files, and override default attributes, by including the LOGFILE clause but omitting a filename. Online redo log files are created as above, 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.

See Also: Chapter 7, "Managing the Online Redo Log"

Specifying the SYSTEM Tablespace Datafile at Database Creation

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

• If DB_CREATE_FILE_DEST is set, then an Oracle-managed datafile for the SYSTEM tablespace is created in the DB_CREATE_FILE_DEST directory.

• If DB_CREATE_FILE_DEST is not set, then Oracle creates one SYSTEM tablespace datafile whose name and size are operating system dependent. Any SYSTEM 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 with an unlimited maximum size.

Optionally, you can create an Oracle-managed datafile for the SYSTEM tablespace, and override default attributes, by including the DATAFILE clause but omitting a filename. If a filename is not supplied and the DB_CREATE_FILE_DEST parameter is set, an Oracle-managed datafile for the SYSTEM tablespace is created in the DB_CREATE_FILE_DEST directory. If a filename is not supplied and the DB_CREATE_FILE_DEST parameter is not set, the CREATE DATABASE statement fails.

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 CREATE DATABASE. 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.

  See Also: Chapter 13, "Managing Undo Space"

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, and 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

Included in the initialization parameter file:

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

CREATE DATABASE statement:

SQL> CREATE DATABASE sample;

This example creates a database with the following Oracle-managed files:

• A SYSTEM tablespace datafile in directory /u01/oradata/sample that is 100 MB and autoextensible up to an unlimited size

• Two online log groups with two members of 100 MB each, one each in /u02/oradata/sample and /u03/oradata/sample

• If automatic undo management mode is enabled, then an undo tablespace datafile in directory /u01/oradata/sample2 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 was specified, then two control files, one each in /u02/oradata/sample and /u03/oradata/sample. The control file in /u02/oradata/sample is the primary control file.

CREATE DATABASE: Example 2

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.

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

This example creates a database with the following Oracle-managed files:

• A 100 megabyte SYSTEM tablespace datafile in directory /u01/oradata/sample2

• Two online redo log files of 100 MB each in directory /u01/oradata/sample2. They are not multiplexed.

• An undo tablespace datafile in directory /u01/oradata/sample2 that is 10megabytes and autoextensible up to an unlimited size. An undo tablespace named SYS_UNDOTBS is created.

• A control file in /u01/oradata/sample2

This database configuration is not recommended, and should only be used for a very low-end database or simple test database. To better protect this database from failures, at least one more control file should be created and the online redo log should be multiplexed.

CREATE DATABASE: Example 3

Included in the initialization parameter file:

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

CREATE DATABASE statement:

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

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 characteristics is created:

This example creates a database with the following Oracle-managed files:

• A 400 megabyte SYSTEM tablespace datafile in directory /u01/oradata/sample3

• Two online redo log groups with two members of 100 MB each, one each in directories /u02/oradata/sample3 and /u03/oradata/sample3

• For the default temporary tablespace named dflt_ts, a 10 megabyte tempfile in directory /u01/oradata/sample3

• For the undo tablespace named undo_ts, a 10 megabyte datafile in directory /u01/oradata/sample3

• If no CONTROL_FILES initialization parameter was specified, then two control files, one each in directories /u02/oradata/sample3 and /u03/oradata/sample3. The control file in /u02/oradata/sample3 is the primary control file.

Creating Datafiles for Tablespaces

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. If you include the DATAFILE clause, then 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.

If you add a datafile to a tablespace with the ALTER TABLESPACE ... ADD DATAFILE statement, then 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.

See Also: "Specifying the SYSTEM Tablespace Datafile at Database Creation" "Specifying the Undo Tablespace Datafile at Database Creation" Chapter 11, "Managing Tablespaces"

CREATE TABLESPACE: Examples

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

CREATE TABLESPACE: Example 1

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

This example sets the default location for datafile creations to /u01/oradata/sample 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.

CREATE TABLESPACE: Example 2

Included in initialization parameter file:

DB_CREATE_FILE_DEST = '/u01/oradata/sample2'

CREATE TABLESPACE statement:

SQL> CREATE TABLESPACE tbs_2 DATAFILE SIZE 400M AUTOEXTEND OFF;

This example creates a tablespace tbs_2 with a datafile in the directory /u01/oradata/sample2 that is not autoextensible and a size of 400 MB.

CREATE TABLESPACE: Example 3

Included in initialization parameter file:

DB_CREATE_FILE_DEST = '/u01/oradata/sample3'

CREATE TABLESPACE statement:

SQL> CREATE TABLESPACE tbs_3 DATAFILE AUTOEXTEND ON MAXSIZE 800M;

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

CREATE TABLESPACE: Example 4

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

This example sets the default location for datafile creations to /u01/oradata/sample4 and then creates a tablespace tbs_4 in that directory with two autoextensible datafiles with an unlimited maximum size and an initial size of 200 MB.

CREATE UNDO TABLESPACE: Example

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

Included in initialization parameter file:

DB_CREATE_FILE_DEST = '/u01/oradata/sample'

CREATE UNDO TABLESPACE statement:

SQL> CREATE UNDO TABLESPACE undotbs_1;

ALTER TABLESPACE: Example

The following adds an Oracle-managed autoextensible datafile to tablespace tbs_1 with an initial size of 100 MB and a maximum size of 800 MB.

Included in initialization parameter file:

DB_CREATE_FILE_DEST = '/u01/oradata/sample'

ALTER TABLESPACE statement:

SQL> ALTER TABLESPACE tbs_1 ADD DATAFILE AUTOEXTEND ON MAXSIZE 800M;

Creating Tempfiles for Temporary Tablespaces

The following statements that can 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.

If you add a tempfile to a tablespace with the ALTER TABLESPACE ... ADD TEMPFILE statement, then 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 tempfile for a tablespace is 100 MB and is autoextensible with an unlimited maximum size.

See Also: "Specifying the Default Temporary Tablespace Tempfile at Database Creation"

CREATE TEMPORARY TABLESPACE: Example

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

SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/u01/oradata/sample';
SQL> CREATE TEMPORARY TABLESPACE temptbs_1;

ALTER TABLESPACE ... ADD TEMPFILE: Example

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

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

Creating Control Files

When you issue the CREATE CONTROLFILE statement, a control file is created (or reused, if REUSE is specified) in the file(s) 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 destination(s). In order of precedence, the default destination is defined as follows:

• If DB_CREATE_ONLINE_LOG_DEST_n initialization parameters are specified, then an Oracle-managed control file copy is created in each directory specified. The file in the first directory is the primary control file.

• If the DB_CREATE_FILE_DEST initialization parameter is specified, and no DB_CREATE_ONLINE_LOG_DEST_n initialization parameters are specified, then an Oracle-managed control file is created in the directory specified.

• If neither DB_CREATE_ONLINE_LOG_DEST_n or DB_CREATE_ONLINE_LOG_DEST_n initialization parameters are specified, then a control file is created in an operating system specific default location. This control file is not an Oracle-managed file.

If Oracle creates an Oracle-managed control file, and there is a server parameter file, then Oracle 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 Oracle generated filenames for the files must be supplied in the DATAFILE clause of the statement.

If the online redo log files are Oracle-managed files, then the [NO]RESETLOGS keyword determines what can be supplied in the LOGFILE clause:

• NORESETLOGS: the Oracle generated filenames for the Oracle-managed online redo log files must be supplied in the LOGFILE clause.

• RESETLOGS: the online redo log file names can be supplied as with the CREATE DATABASE statement. See "Specifying Online Redo Log Files at Database Creation".

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

See Also: "Specifying Control Files at Database Creation"

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 online redo log files:

CREATE CONTROLFILE
     DATABASE sample
     LOGFILE GROUP 1 ('/u01/oradata/sample/ora_1_o220rtt9.log',
                      '/u02/oradata/sample/ora_1_v2o0b2i3.log') SIZE 100M,
             GROUP 2 ('/u01/oradata/sample/ora_2_p22056iw.log', 
                      '/u02/oradata/sample/ora_2_p02rcyg3.log') SIZE 100M
     NORESETLOGS
     DATAFILE '/u01/oradata/sample/ora_system_xu34ybm2.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. DB_CREATE_ONLINE_LOG_DEST_n or DB_CREATE_FILE_DEST must be set.

CREATE CONTROLFILE
     DATABASE sample
     RESETLOGS
     DATAFILE '/u01/oradata/sample/ora_system_aawbmz51.dbf' SIZE 100M
     MAXLOGFILES 5
     MAXLOGHISTORY 100
     MAXDATAFILES 10
     MAXINSTANCES 2
     ARCHIVELOG;

Later, you must issue the ALTER DATABASE OPEN RESETLOGS statement to recreate the online redo log files. This is discussed in the next section. If the previous log files were Oracle-managed files, then they are not deleted.

Creating Online Redo Log Files

Online 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 allows you to later add a new group to your current online 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. In order of precedence, the default destination is defined as follows:

• If DB_CREATE_ONLINE_LOG_DEST_n initialization parameters are specified, then an Oracle-managed log file member is created in each directory specified in the parameters (up to MAXLOGMEMBERS for the database).

• If the DB_CREATE_FILE_DEST initialization parameters specified, and no DB_CREATE_ONLINE_LOG_DEST_n initialization parameters are specified, then an Oracle-managed log file member is created in the directory specified in the parameter.

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.

Online redo log file members continue to be added and dropped by specifying complete filenames.

See Also: "Specifying Online Redo Log Files at Database Creation" "Creating Control Files"

Adding New Online Redo Log Files: Example

The following example creates a log file with a member in /u01/oradata/sample and another member in /u02/oradata/sample. The size of the log file is 100 MB.

Included in the initialization parameter file:

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

The ALTER DATABASE statement:

SQL> ALTER DATABASE ADD LOGFILE;

Using the ALTER DATABASE OPEN RESETLOGS Statement

If you previously created a control file specifying RESETLOGS, and either did not specify filenames, or specified non-existent filenames, then Oracle creates online 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, are the same as those discussed in "Specifying Online Redo Log Files at Database Creation".

Behavior of Oracle-Managed Files

The filenames of Oracle-managed files are accepted in SQL statements wherever a filename is used to identify an existing file. These filenames, like other filenames, are stored in the control file, and, if using Recovery Manager (RMAN) for backup and recovery, in the RMAN catalog. They are visible in all of the usual fixed and dynamic performance views that are available for monitoring datafiles and tempfiles (for example, V$DATAFILE or DBA_DATA_FILES).

Some examples of statements using Oracle generated filenames are:

SQL> ALTER DATABASE RENAME FILE 'ora_tbs01_ziw3bopb.dbf'
  2> TO 'tbs0101.dbf';

SQL> ALTER DATABASE DROP LOGFILE 'ora_1_wo94n2xi.log';

SQL> ALTER TABLE emp ALLOCATE EXTENT ( DATAFILE 'ora_tbs1_2ixfh90q.dbf' );

You can backup and restore Oracle-managed datafiles, tempfiles, and control files as you would corresponding non Oracle-managed files. Using Oracle generated filenames does not impact the use of logical backup files such as export files. This is particularly important for tablespace point-in-time recovery (TSPITR) and transportable tablespace export files.

There are some cases where Oracle-managed files behave differently. These are discussed in the sections that follow.

Dropping Datafiles and Tempfiles

Unlike files that are not Oracle managed, when an Oracle-managed datafile or tempfile is dropped, the filename is removed from the control file and the file is automatically deleted from the file system. The statements that delete Oracle-managed files when they are dropped are:

• DROP TABLESPACE

• ALTER DATABASE TEMPFILE ... DROP

Dropping Online Redo Log Files

When an Oracle-managed online redo log file is dropped its Oracle-managed files are deleted. You specify the group or members to be dropped. The following statements drop and delete online redo log files:

• ALTER DATABASE DROP LOGFILE

• ALTER DATABASE DROP LOGFILE MEMBER

Renaming Files

The following statements are used to rename files:

• ALTER DATABASE RENAME FILE

• ALTER TABLESPACE ... RENAME DATAFILE

These statements do not actually rename the files on the operating system, but rather, the names in the control file are changed. If the old file is an Oracle-managed file and it exists, then it is deleted. You must specify each filename using the conventions for filenames on your operating system when you issue this statement.

Managing Standby Databases

The datafiles, control files, and online redo log files in a standby database can be Oracle managed. This is independent of whether Oracle-managed files are used on the primary database.

When recovery of a standby database encounters redo for the creation of a datafile, if the datafile is an Oracle-managed file then the recovery process creates an empty file in the local default file system location. This allows the redo for the new file to be applied immediately without any human intervention.

When recovery of a standby database encounters redo for the deletion of a tablespace, it deletes any Oracle-managed datafiles in the local file system. Note that this is independent of the INCLUDING DATAFILES option issued at the primary database.

See Also: Oracle9i Data Guard Concepts and Administration for information about using Oracle-managed files with standby databases

Scenarios for Using Oracle-Managed Files

This section further demonstrates the use of Oracle-managed files by presenting scenarios of their use.

Scenario 1: Create and Manage a Database with Multiplexed Online Redo Logs

In this scenario, a DBA creates a database where the datafiles and online redo log files are created in separate directories. The online redo log files and control files are multiplexed. The database uses an undo tablespace, and has a default temporary tablespace. The following are tasks involved with creating and maintaining this database.

1. Setting the initialization parameters

   The DBA includes three generic file creation defaults in the initialization parameter file before creating the database. Automatic undo management mode is also specified.

   DB_CREATE_FILE_DEST = '/u01/oradata/sample'
   DB_CREATE_ONLINE_LOG_DEST_1 = '/u02/oradata/sample'
   DB_CREATE_ONLINE_LOG_DEST_2 = '/u03/oradata/sample'
   UNDO_MANAGEMENT = AUTO
   
   

   The DB_CREATE_FILE_DEST parameter sets the default file system directory for the datafiles and tempfiles.

   DB_CREATE_ONLINE_LOG_DEST_1 and DB_CREATE_ONLINE_LOG_DEST_2 set the default file system directories for online redo log file and control file creation. Each online redo log file and control file is multiplexed across the two directories.

2. Creating a database

   Once the initialization parameters are set, the database can be created:

   SQL> CREATE DATABASE sample
   2>   DEFAULT TEMPORARY TABLESPACE dflt_tmp;
       
   

   Because a DATAFILE clause is not present and the DB_CREATE_FILE_DEST initialization parameter is set, the SYSTEM tablespace datafile is created in the default file system (/u01/oradata/sample in this scenario). The filename is uniquely generated by Oracle. The file is autoextensible with an initial size of 100 MB and an unlimited maximum size. The file is an Oracle-managed file.

   Because a LOGFILE clause is not present, two online redo log groups are created. Each log group has two members, with one member in the DB_CREATE_ONLINE_LOG_DEST_1 location and the other member in the DB_CREATE_ONLINE_LOG_DEST_2 location. The filenames are uniquely generated by Oracle. The log files are created with a size of 100 MB. The log file members are Oracle-managed files.

   Similarly, because the CONTROL_FILES initialization parameter is not present, and two DB_CREATE_ONLINE_LOG_DEST_n initialization parameters are specified, two control files are created. The control file located in the DB_CREATE_ONLINE_LOG_DEST_1 location is the primary control file; the control file located in the DB_CREATE_ONLINE_LOG_DEST_2 location is a multiplexed copy. The filenames are uniquely generated by Oracle. They are Oracle-managed files. Assuming there is a server parameter file, a CONTROL_FILES initialization parameter in generated.

   Automatic undo management mode is specified, but because an undo tablespace is not specified and the DB_CREATE_FILE_DEST initialization parameter is set, a default undo tablespace named SYS_UNDOTBS is created in the directory specified by DB_CREATE_FILE_DEST. The datafile is a 10 megabyte datafile that is autoextensible. It is an Oracle-managed file.

   Lastly, a default temporary tablespace named dflt_tmp is specified. Because DB_CREATE_FILE_DEST is included in the parameter file, the tempfile for dflt_tmp is created in the directory specified by that parameter. The tempfile is 100 MB and is autoextensible with an unlimited maximum size. It is an Oracle-managed file.

   The resultant file tree, with generated filenames, is as follows:

   /u01
        /oradata
             /sample
                  /ora_system_cmr7t30p.dbf
                  /ora_sys_undo_2ixfh90q.dbf
                  /ora_dflt_tmp_157se6ff.tmp
   /u02
        /oradata
             /sample
                  /ora_1_0orrm31z.log
                  /ora_2_2xyz16am.log
                  /ora_cmr7t30p.ctl
   /u03
        /oradata
             /sample
                  /ora_1_ixfvm8w9.log
                  /ora_2_q89tmp28.log
                  /ora_x1sr8t36.ctl
   
   

   The internally generated filenames can be seen when selecting from the usual views. For example:

   SQL> SELECT NAME FROM V$DATAFILE;
   
   NAME
   ----------------------------------------------------
   /u01/oradata/sample/ora_system_cmr7t30p.dbf
   /u01/oradata/sample/ora_sys_undo_2ixfh90q.dbf
   
   2 rows selected
                                  
   

   The name is also printed to the alert file when the file is created.

3. Managing control files

   The control file was created when generating the database, and a CONTROL_FILES initialization parameter was added to the parameter file. If needed, the DBA can recreate the control file or build a new one for the database using the CREATE CONTROLFILE statement.

   The correct Oracle-managed filenames must be used in the DATAFILE and LOGFILE clauses. The ALTER DATABASE BACKUP CONTROLFILE TO TRACE statement generates a script with the correct filenames. Alternatively, the filenames can be found by selecting from the V$DATAFILE, V$TEMPFILE, and V$LOGFILE views. The following example recreates the control file for the sample database:

   SQL> CREATE CONTROLFILE REUSE
   2>       DATABASE sample
   3>       LOGFILE GROUP 1('/u02/oradata/sample/ora_1_0orrm31z.log', 
   4>                       '/u03/oradata/sample/ora_1_ixfvm8w9.log'), 
   5>               GROUP 2('/u02/oradata/sample/ora_2_2xyz16am.log',
   6>                       '/u03/oradata/sample/ora_2_q89tmp28.log')
   7>       NORESETLOGS
   8>       DATAFILE '/u01/oradata/sample/ora_system_cmr7t30p.dbf',
   9>                '/u01/oradata/sample/ora_sys_undo_2ixfh90q.dbf',
   10>               '/u01/oradata/sample/ora_dflt_tmp_157se6ff.tmp'
   11>      MAXLOGFILES 5
   12>      MAXLOGHISTORY 100
   13>      MAXDATAFILES 10
   14>      MAXINSTANCES 2
   15>      ARCHIVELOG;
   
   

   The control file created by this statement is located as specified by the CONTROL_FILES initialization parameter that was generated when the database was created. The REUSE clause causes any existing file(s) to be overwritten.

4. Managing the online redo log

   To create a new group of online redo log files, the DBA can use the ALTER DATABASE ADD LOGFILE statement. The following statement adds a log file with a member in the DB_CREATE_ONLINE_LOG_DEST_1 location and a member in the DB_CREATE_ONLINE_LOG_DEST_2 location. These files are Oracle-managed files.

   SQL> ALTER DATABASE ADD LOGFILE;
   
   

   Log file members continue to be added and dropped by specifying complete filenames.

   The GROUP clause can be used to drop a log file. In the following example the operating system file associated with each Oracle-managed log file member is automatically deleted.

   SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
   
   

5. Managing tablespaces

   The default storage for all datafiles for future tablespace creations in the sample database is the location specified by the DB_CREATE_FILE_DEST initialization parameter (/u01/oradata/sample in this scenario). Any datafiles for which no filename is specified, are created in the file system specified by the initialization parameter DB_CREATE_FILE_DEST. For example:

   SQL> CREATE TABLESPACE tbs_1;
   
   

   The preceding statement creates a tablespace whose storage is in /u01/oradata/sample. A datafile is created with an initial size of 100 MB and it is autoextensible with an unlimited maximum size. The datafile is an Oracle-managed file.

   When the tablespace is dropped, the Oracle-managed files for the tablespace are automatically removed. The following statement drops the tablespace and all the Oracle-managed files used for its storage:

   SQL> DROP TABLESPACE tbs_1;
   
   

   Once the first datafile is full, Oracle does not automatically create a new datafile. More space can be added to the tablespace by adding another Oracle-managed datafile. The following statement adds another datafile in the location specified by DB_CREATE_FILE_DEST:

   SQL> ALTER TABLESPACE tbs_1 ADD DATAFILE;
   
   

   The default file system can be changed by changing the initialization parameter. This does not change any existing datafiles. It only effects future creations. This can be done dynamically using the following statement:

   SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u04/oradata/sample';
   
   

6. Archiving redo information

   Archiving of online redo log files is no different for Oracle-managed files, than it is for unmanaged files. A file system location for the archived log files can be specified using the LOG_ARCHIVE_DEST_n initialization parameters. The filenames are formed based on the LOG_ARCHIVE_FORMAT parameter or its default.

   The archived logs are not Oracle-managed files

7. Backup, restore, and recover.

   Since an Oracle-managed file is compatible with standard operating system files, you can use operating system utilities to backup or restore Oracle-managed files. All existing methods for backing up, restoring, and recovering the database work for Oracle-managed files.

Scenario 2: Add Oracle-Managed Files to an Existing Database

Assume in this case that an existing database does not have any Oracle-managed files, but the DBA would like to create new tablespaces with Oracle-managed files and locate them in directory /u03/oradata/sample2.

1. Setting the initialization parameters

   To allow automatic datafile creation, set the DB_CREATE_FILE_DEST initialization parameter to the file system directory in which to create the datafiles. This can be done dynamically as follows:

   SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/u03/oradata/sample2';
   
   

2. Creating tablespaces

   Once DB_CREATE_FILE_DEST is set, the DATAFILE clause can be omitted from a CREATE TABLESPACE statement. The datafile is created in the location specified by DB_CREATE_FILE_DEST by default. For example:

   SQL> CREATE TABLESPACE tbs_2;
   
   

   When tablespace tbs_2 is dropped, its datafiles are automatically deleted.