–          Every Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo

–          Undo records are used to L:

• Rollback transactions when a ROLLBACK statement is issued
• Recover the databsae
• Provide read consistency

When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.

–          In automatic undo management mode, you create an undo tablespace, and the server automatically manages undo segments and space among the various active sessions

–          When the instance starts, the database automatically selects the first available undo tablespace. If no undo tablespace is available, then the instance starts without an undo tablespace and stores undo records in the SYSTEM tablespace.

Undo Retention

–          After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks. Furthermore, the success of several Oracle Flashback features can also depend upon the availability of older undo information. For these reasons, it is desirable to retain the old undo information for as long as possible.

–          When automatic undo management is enabled, there is always a current undo retention period, which is the minimum amount of time that Oracle Dataabase attempts to retain old undo information before overwriting it. Old (commiteed) undo information that is older than the current undo retention period is said to be expired. Old undo information with an age that is less than the current undo retention period is said to be unexpired.

–          Oracle database automatically tunes the undo retention period based on undo tablespace size and system activity. You can specify a minimum undo retention period (in seconds) by setting the UNDO_RETENTION parameter. The database makes its best effort to honor the specified minimum undo retention period, provided that the undo tablespace has space available for new transactions. When available space for new transactions becomes short, the databsae begins to overwrite expired undo. If the undo tbalespace has no space for new transactions after all expired undo is overwritten, the databsae may begin overwriting unexpired undo information. If any of this overwritten undo information is required for consistent read in a current long-running query, the query could fail with the snapshot too old error message.

–          To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed, the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace. If retention guarantee is not enabled, the database can overwrite unexpired undo when space is low, thus lowering the undo retention for the system. This option is disabled by default.

–          You enable retention guarantee by specifying the RETENTIN GUARANTEE clause for the undo tablespace when you create it with either the CREATE DATABASE or CREATE UNDO TABLESPACE statement.

–          Oracle database assigns each datafile two associated file numbers, an absolute file number and a relative file number, that are used to uniquely identify it. The absolute file number can be found in the FILE# column of the V$DATAFILE or V$TEMPFILE view. Relative file number uniquely identifieds a datafile within a tablespace. For small and medium size databases, relative file numbers usually have the same value as the absolute file number. However, when the number of datafiles in a database exceeds a threshold (typically 1023), the relative file number differs from the absolute file number.

–          When starting an instance, the DB_FILES parameter specifies the maximum number of database files that can be opened for this database. Default value is 200

–          If several disk drives are available to store the databsae, consider placing potentially contending datafiles on separate disks. This way, when users query information, both disk drives can work simultaneously, retrieving data at the same time

–          Datafiles should not be stored on the same disk drive that stores the databsae redo log files. if the datafiles and redo log files are stored on the same disk drive and that disk drive fails, the files cannot be used in your database recovery procedures.

–          To determine whether a datafile is auto-extensible, query the DBA_DATA_FILES view and examine the AUTOEXTENSIBLE column

–          To take a datafile offline when the database is in NOARCHIVELOG mode, use the ALTER DATABASE statement with both the DATAFILE and OFFLINE FOR DROP clauses

–          Clauses of the ALTER TABLESPACE statement allow you to change the online or offline status of all of the datafiles or tempfiles within a tablespace.

ALTER TABLESPACE tbs_test DATAFILE [ONLINE | OFFLINE]

To rename and relocate datafiles use the following steps:

·         Take the tablespace that contains the datafiles offline:

ALTER TABLESAPCE users OFFLINE NORMAL;

·         Rename the datafiles using the operating system

·         Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the filenames within the database

ALTER TABLESPACE users RENAME DATAFILE ‘file1’ TO ‘file2’;

·         Backup the database

–          To drop the datafile, the database must be open and the file must be empty. If you must remove a datafile that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the datafile. You can’t drop the first or only datafile in a tablespace, or datafiles in a read only tablespace or SYSTEM tablespace.

–          If you want to configure the database to use checksums to verify data blocks, set the initialization parameter DB_BLOCK_CHECKSUM to TRUE. This causes the DBWn process and the direct loader to calculate a checksum for each block and to store the checksum in the block header when writing the block to disk.

The checksum is verified when the block is read, but only if DB_BLOCK_CHECKSUM is TRUE and the last write of the block stored a checksum. If corruption is detected, the database returns message ORA-01578 and writes information about the corruption to the alert log