Creating Oracle-Managed Files

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:

• 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 initialization 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.

  See Also:

  Chapter 14, "Managing Undo"

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 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 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;

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:

DB_CREATE_FILE_DEST = '/u01/oradata'

The following statement is issued at the SQL prompt:

SQL> CREATE TABLESPACE tbs_2 DATAFILE SIZE 400M;

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:

DB_CREATE_FILE_DEST = '/u01/oradata'

The following statement is issued at the SQL prompt:

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

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:

DB_CREATE_FILE_DEST = '/u01/oradata'

The following statement is issued at the SQL prompt:

SQL> CREATE UNDO TABLESPACE undotbs_1;

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:

DB_CREATE_FILE_DEST = '/u01/oradata'

The following statement is entered at the SQL prompt:

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

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.

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

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;

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. This is discussed in "Using the ALTER DATABASE OPEN RESETLOGS Statement". If the previous log files are Oracle-managed files, then they are not deleted.

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. The default destination is determined according to the precedence documented in "Specifying Redo Log Files at Database Creation".

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:

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 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, are the same as those discussed in "Specifying Redo Log Files at Database Creation".