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:
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
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:
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:
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:
where:
For example, assume the following parameter settings:
Then an example datafile name would be:
/u01/oradata/PAYROLL/datafile/o1_mf_tbs1_2ixh90q_.dbfNames 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:
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:
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:
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:
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:
The following parameter settings relating to Oracle-managed files, are included in the initialization parameter file:
CREATE DATABASE: Example 2 This example creates a database with the following Oracle-managed files:
In this example, it is assumed that:
The following statements are issued at the SQL prompt:
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:
The following parameter settings are included in the initialization parameter file:
The following statement is issued at the SQL prompt:
Creating Datafiles for Tablespaces Using Oracle-Managed Files
The following statements that can create datafiles are relevant to the discussion in this section:
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:
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.
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
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;Creating Tempfiles for Temporary Tablespaces Using Oracle-Managed Files
The following statements that create tempfiles are relevant to the discussion in this section:
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:
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.
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.
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:
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 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.
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:
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:
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.
Creating Archived Logs Using Oracle-Managed Files
Archived logs are created in the DB_RECOVERY_FILE_DEST location when:
For example, assume that the following parameter settings are included in the initialization parameter file:
|
|
Oracle 10g Related Tutorials |
|
---|---|
Oracle 9i Tutorial | Oracle 8i Tutorial |
Informatica Tutorial | Oracle 11g Tutorial |
Oracle 10g Related Interview Questions |
|
---|---|
Oracle 10g Interview Questions | Oracle 9i Interview Questions |
Oracle 8i Interview Questions | Informatica Interview Questions |
PL/SQL Interview Questions | Oracle 11g Interview Questions |
SQL Interview Questions | Oracle apps Interview Questions |
Sybase Interview Questions | Oracle Apps ERP Interview Questions |
Oracle 7.3 Interview Questions | Oracle Access Manager Interview Questions |
Oracle Application Framework Interview Questions | Oracle Apps DBA Interview Questions |
Oracle 10g Related Practice Tests |
|
---|---|
Oracle 10g Practice Tests | Oracle 9i Practice Tests |
Oracle 8i Practice Tests | Informatica Practice Tests |
PL/SQL Practice Tests | Oracle 11g Practice Tests |
SQL Practice Tests | Oracle apps Practice Tests |
Sybase Practice Tests | Oracle Apps ERP Practice Tests |
Oracle 7.3 Practice Tests |
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.