Oracle managed files 10g

The filename is uniquely generated by the database. The file is autoextensible with an initial size of MB and an unlimited maximum size. The file is an Oracle-managed file. The filenames are uniquely generated by the database. The log files are created with a size of MB. The log file members are Oracle-managed files. They are Oracle-managed files. The datafile is a 10 MB datafile that is autoextensible.

It is an Oracle-managed file. Lastly, a default temporary tablespace named dflttmp is specified. The following example re-creates the control file for the sample database:. These files are Oracle-managed files. In the following example the operating system file associated with each Oracle-managed log file member is automatically deleted. A datafile is created with an initial of 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:. Once the first datafile is full, the database does not automatically create a new datafile. More space can be added to the tablespace by adding another Oracle-managed datafile.

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

Archiving of redo log files is no different for Oracle-managed files, than it is for unmanaged files. The archived logs are not Oracle-managed files. 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.

In this scenario, a DBA creates a database where the control files and redo log files are multiplexed. Archived logs and RMAN backups are created in the flash recovery area. The following tasks are involved in creating and maintaining this database:.

Each redo log and control file is multiplexed across the two directories. Archiving online logs is no different for Oracle-managed files than it is for unmanaged files. An Oracle-managed file is compatible with standard operating system files, so you can use operating system utilities to backup or restore Oracle-managed files. The backups are Oracle-managed files. This can be done dynamically as follows:.

The database internally uses standard file system interfaces to create and delete files as needed for the following database structures: Tablespaces Redo log files Control files Archived logs Block change tracking files Flashback logs RMAN backups Through initialization parameters, you specify the file system directory to be used for a particular type of file.

See Also: Chapter 12, "Using Automatic Storage Management" for information about the Oracle Database integrated storage management system that extends the power of Oracle-managed files. With Oracle-managed files, files are created and managed automatically for you, but with Automatic Storage Management you get the additional benefits of features such as file redundancy and striping, without the need to purchase a third-party logical volume manager.

What Is a Logical Volume Manager? What Is a File System? Benefits of Using Oracle-Managed Files Consider the following benefits of using Oracle-managed files: They make the administration of the database easier.

They reduce corruption caused by administrators specifying the wrong file. They reduce wasted disk space consumed by obsolete files.

They simplify creation of test and development databases. Oracle-managed files make development of portable third-party tools easier. Oracle-Managed Files and Existing Functionality Using Oracle-managed files does not eliminate any existing functionality. You can use this initialization parameter multiple times, where n specifies a multiplexed copy of the redo log or control file.

You can specify up to five multiplexed copies. It is recommended that you specify at least two parameters. 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. 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. Caution: Do not rename an Oracle-managed file.

The database identifies an Oracle-managed file based on its name. If you rename the file, the database is no longer able to recognize it as an Oracle-managed file and will not manage the file accordingly.

The default size of an Oracle-managed redo log file is MB. Automatic undo management mode is enabled. The default size for an Oracle-managed log file is MB. You continue to add and drop redo log file members by specifying complete filenames. 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.

Dropping Datafiles and Tempfiles Unlike files that are not managed by the database, 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.

Managing Standby Databases The datafiles, control files, and redo log files in a standby database can be managed by the database. 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 Redo Logs In this scenario, a DBA creates a database where the datafiles and redo log files are created in separate directories.

Setting the initialization parameters The DBA includes three generic file creation defaults in the initialization parameter file before creating the database. The archived logs are not Oracle-managed files 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.

Archiving redo log information Archiving online logs is no different for Oracle-managed files than it is for unmanaged files. Backup, restore, and recover An Oracle-managed file is compatible with standard operating system files, so you can use operating system utilities to backup or restore Oracle-managed files.

Defines the location of the default file system directory where the database creates datafiles or tempfiles when no file specification is given in the creation operation. Defines the location of the default file system directory for redo log files and control file creation when no file specification is given in the creation operation.

Defines the location of the default file system directory where the database creates RMAN backups when no format option is used, archived logs when no other local destination is configured, and flashback logs. Using database-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.

Unlike files that are not managed by the database, 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:. When an Oracle-managed 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 redo log files:.

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. The datafiles, control files, and redo log files in a standby database can be managed by the database. 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.

Oracle 10g Interview Questions. Oracle 10g Practice Tests. IT Skills. Management Skills. Communication Skills. When an extent is allocated or freed for reuse, Oracle changes the bitmap values to show the new status of the blocks.

These changes do not generate rollback information because they do not update tables in the data dictionary except for special cases such as tablespace quota information.

Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents. Local management of extents avoids recursive space management operations.

Such recursive operations can occur in dictionary managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a data dictionary table or rollback segment. The sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed tablespace and override object storage options. Your choices are:. This keyword tells Oracle that you want to use bitmaps to manage the free space within segments.

A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps enable Oracle to manage free space more automatically; thus, this form of space management is called automatic segment-space management. Locally managed tablespaces using automatic segment-space management can be created as smallfile traditional or bigfile tablespaces.

AUTO is the default. This keyword tells Oracle that you want to use free lists for managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows. Oracle Database Administrator's Guide for more information about automatic segment space management.

If y ou created your database with an earlier version of Oracle, then you could be using dictionary managed tablespaces. For a tablespace that uses the data dictionary to manage its extents, Oracle updates the appropriate tables in the data dictionary whenever an extent is allocated or freed for reuse.

Oracle also stores rollback information about each update of the dictionary tables. Because dictionary tables and rollback segments are part of the database, the space that they occupy is subject to the same space management operations as all other data. Oracle supports multiple block sizes in a database. This is set when the database is created and can be any valid size.

Legitimate values are from 2K to 32K. In the initialization parameter file or server parameter, you can configure subcaches within the buffer cache for each of these block sizes.

Subcaches can also be configured while an instance is running. You can create tablespaces having any of these block sizes. The standard block size is used for the system tablespace and most other tablespaces. Multiple block sizes are useful primarily when transporting a tablespace from an OLTP database to an enterprise data warehouse.

This facilitates transport between databases of different block sizes. Oracle Database Data Warehousing Guide for information about transporting tablespaces in data warehousing environments. A database administrator can bring any tablespace other than the SYSTEM tablespace online accessible or offline not accessible whenever the database is open. A tablespace is usually online so that the data contained within it is available to database users.

However, the database administrator can take a tablespace offline for maintenance or backup and recovery purposes. When a tablespace goes offline, Oracle does not permit any subsequent SQL statements to reference objects contained in that tablespace. Active transactions with completed statements that refer to data in that tablespace are not affected at the transaction level.

Oracle saves rollback data corresponding to those completed statements in a deferred rollback segment in the SYSTEM tablespace. When the tablespace is brought back online, Oracle applies the rollback data to the tablespace, if needed. When a tablespace goes offline or comes back online, this is recorded in the data dictionary in the SYSTEM tablespace. If a tablespace is offline when you shut down a database, the tablespace remains offline when the database is subsequently mounted and reopened.

You can bring a tablespace online only in the database in which it was created because the necessary data dictionary information is maintained in the SYSTEM tablespace of that database.

An offline tablespace cannot be read or edited by any utility other than Oracle. Thus, offline tablespaces cannot be transposed to other databases. Oracle automatically switches a tablespace from online to offline when certain errors are encountered.

For example, Oracle switches a tablespace from online to offline when the database writer process, DBW n , fails in several attempts to write to a datafile of the tablespace. Users trying to access tables in the offline tablespace receive an error. Oracle Database Utilities for more information about tools for data transfer.

If you create multiple tablespaces to separate different types of data, you take specific tablespaces offline for various procedures.

Other tablespaces remain online, and the information in them is still available for use. However, special circumstances can occur when tablespaces are taken offline. For example, if two tablespaces are used to separate table data from index data, the following is true:.

If the tablespace containing the indexes is offline, then queries can still access table data because queries do not require an index to access the table data. If the tablespace containing the tables is offline, then the table data in the database is not accessible because the tables are required to access the data.

If Oracle has enough information in the online tablespaces to run a statement, it does so. If it needs data in an offline tablespace, then it causes the statement to fail. The primary purpose of read-only tablespaces is to eliminate the need to perform backup and recovery of large, static portions of a database.

Read-only tablespaces cannot be modified. After updating the tablespace, you can then reset it to be read only. Also, if you need to recover your database, you do not need to recover any read-only tablespaces, because they could not have been modified. You can manage space for sort operations more efficiently by designating one or more temporary tablespaces exclusively for sorts. Doing so effectively eliminates serialization of space management operations involved in the allocation and deallocation of sort space.

A single SQL operation can use more than one temporary tablespace for sorting. For example, you can create indexes on very large tables, and the sort operation during index creation can be distributed across multiple tablespaces. All operations that use sorts, including joins, index builds, ordering, computing aggregates GROUP BY , and collecting optimizer statistics, benefit from temporary tablespaces. The performance gains are significant with Real Application Clusters.

One or more temporary tablespaces can be used only for sort segments. A temporary tablespace is not the same as a tablespace that a user designates for temporary segments, which can be any tablespace available to the user. No permanent schema objects can reside in a temporary tablespace.

Sort segments are used when a segment is shared by multiple sort operations. One sort segment exists for every instance that performs a sort operation in a given tablespace. Temporary tablespaces provide performance improvements when you have multiple sorts that are too large to fit into memory. The sort segment of a given temporary tablespace is created at the time of the first sort operation.

The sort segment expands by allocating extents until the segment size is equal to or greater than the total storage demands of all of the active sorts running on that instance.

Oracle Database Performance Tuning Guide for information about setting up temporary tablespaces for sorts and hash joins. A transportable tablespace lets you move a subset of an Oracle database from one Oracle database to another, even across different platforms. You can clone a tablespace and plug it into another database, copying the tablespace between databases, or you can unplug a tablespace from one Oracle database and plug it into another Oracle database, moving the tablespace between databases.

When you transport tablespaces you can also move index data, so you do not have to rebuild the indexes after importing or loading the table data. You can transport tablespaces across platforms. Many, but not all, platforms are supported for cross-platform tablespace transport. This can be used for the following:. Provide an easier and more efficient means for content providers to publish structured data and distribute it to customers running Oracle on a different platform.

Simplify the distribution of data from a data warehouse environment to data marts which are often running on smaller platforms. A tablesp ace repository is a collection of tablespace sets. Tablespace repositories are built on file group repositories, but tablespace repositories only contain the files required to move or copy tablespaces between databases. Different tablespace sets may be stored in a tablespace repository, and different versions of a particular tablespace set also may be stored.

A version of a tablespace set in a tablespace repository consists of the following files:. Both the datafiles and the metadata export file must be copied to the target database. The transport of these files can be done using any facility for copying flat files, such as the operating system copying facility, ftp, or publishing on CDs.

These files have identical on disk formats for file header blocks, which are used for file identification and verification. Oracle Database Administrator's Guide for details about how to move or copy tablespaces to another database, including details about transporting tablespaces across platforms.

Oracle Streams Concepts and Administration for more information on ways to copy or transport files. A t ablespace in an Oracle database consists of one or more physical datafiles. A datafile can be associated with only one tablespace and only one database. Oracle creates a datafile for a tablespace by allocating the specified amount of disk space plus the overhead required for the file header.