DATA FILES

How to manage datafiles??

The earlier discussions are on the control files and redo log files as also archived log files. Control files and redo log files are part of the database files and they count as part of the number of data files a database can have.

OMF (Oracle Managed Files) and User Managed Files are the two important system. OMF is to help reduce the management of the data files. This is good for any kind of development environment/s. We shall deal with this separately.

The following are some of the important considerations while managing the data files for your database.

(1) Number of data files
(2) Size of the files
(3) Placement of the files
(4) Management of the files
(5) I/O balancing and optimal distribution
(6) Reducing the file contention

We have already discussed how and why there is a limitation to the number of files.
The number of the files is also dependent on the file systems also. Raw devices when used to create the datafiles associated with the database cannot be set for auto extension, cannot be resized. This impacts the number of files. Apart from this, the size of the file is also an important consideration. There are limitations in the size of the files an OS can handle. The 2GB limitation days are to be remembered. The Oracle documentation for the OS has to be thoroughly read before deciding on the maximum number of data files a database can have.

So, be sure of

(1) The limitations of the OS as to the size it can handle
(2) The limitations of the OS as to the max number of data files it can open simultaneously for I/O
(3) Any limitation of the OS in performing I/O

The data files that are associated with the database are added at various stages of the database.

Before creating the database you are deciding on the number of control files and their locations, trace files and their destinations such background dump destination (bdump) , core dump destination (cdump) and user dump destination (udump).

While creating the database, the numbers of redo log files, the system tablespace and associated data file location, temporary tablespace and associated data file/s location, the undo tablespace and associated data file/s location and in the case of Oracel 10G additional tablespace for creating SYSTEM user schema objects and associated data files are decided.

After creating the database, the required tablespaces and associated data files and their location is decided.

During the life cycle of the database, additional data files are added existing datafiles are extended which directly or indirectly influence the number of data files and their individual sizes.
If you add new datafiles to a tablespace and do not fully specify the filenames, Oracle creates the datafiles in the default database directory or the current directory, depending upon your operating system. Oracle recommends you always specify a fully qualified name for a datafile. Unless you want to reuse existing files, make sure the new filenames do not conflict with other files. Old files that have been previously dropped will be overwritten.

If a statement that creates a datafile fails, Oracle removes any created operating system files. 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.

The following command impact the datafiles:

(1) Create tablespace
(2) Create temporary tablespace
(3) Create undo tablepsace
(4) Alter tablespace add datafile
(5) Alter tablespace add tempfile
(6) Create database
(7) Alter database create datafile

How can I change the data file size??

It is done in two ways.
(1) By enabling and disabling the automatic extension of the files.
(2) By resizing the data file

How can I set Automatic extension for a data file??

You can create datafiles or alter existing datafiles so that they automatically increase in size when more space is needed in the database. The files increase in specified increments up to a specified maximum.

Setting your datafiles to extend automatically provides these advantages:

Reduces the need for immediate intervention when a tablespace runs out of space
Ensures applications will not halt because of failures to allocate extents
To determine whether a data file is auto-extensible, query the DBA_DATA_FILES view and examine the AUTOEXTENSIBLE column.

You can specify automatic file extension by specifying an AUTOEXTEND ON clause when you create datafiles using the following SQL statements:

CREATE DATABASE
CREATE TABLESPACE
ALTER TABLESPACE
You can enable or disable automatic file extension for existing datafiles, or manually resize a data file using the ALTER DATABASE statement.

The following example enables automatic extension for a data file added to the userstablespace:

ALTER TABLESPACE users
ADD DATAFILE 'd:oracleoradatawhsusersusers_data_01.dbf' SIZE 10M
AUTOEXTEND ON
NEXT 512K
MAXSIZE 250M;

The value of NEXT is the minimum size of the increments added to the file when it extends. The value of MAXSIZE is the maximum size to which the file can automatically extend.

The next example disables the automatic extension for the data file.

ALTER DATABASE DATAFILE 'd:oracleoradatawhsusersusers_data_01.dbf'
AUTOEXTEND OFF;

How can I Resize a data file??

You can manually increase or decrease the size of a data file using the ALTER DATABASE statement.

Because you can change the sizes of datafiles, you can add more space to your database without adding more datafiles. This is beneficial if you are concerned about reaching the maximum number of datafiles allowed in your database.

Manually reducing the sizes of datafiles enables you to reclaim unused space in the database. This is useful for correcting errors in estimates of space requirements.

In the next example, assume that the data file d:oracleoradatawhsusersusers_data_01.dbf has extended up to 250M. However, because its tablespace now stores smaller objects, the data file can be reduced in size.

The following statement decreases the size of data file d:oracleoradatawhsusersusers_data_01.dbf :

ALTER DATABASE DATAFILE 'd:oracleoradatawhsusersusers_data_01.dbf'
RESIZE 100M;

How the data files can be set online and taken offline and why they are to be done so??

You can take individual datafiles or temp files of a tablespace offline or similarly, bring them online. Offline datafiles are unavailable to the database and cannot be accessed until they are brought back online.

You also have the option of taking all datafiles or temp files comprising a tablespace offline or online simply by specifying the name of a tablespace.

One example of where you might be required to alter the availability of a data file is when Oracle has problems writing to a data file and automatically takes the data file offline. Later, after resolving the problem, you can bring the data file back online manually.

The files of a read-only tablespace can independently be taken offline or brought online just as for read-write tablespaces. Bringing a data file online in a read-only tablespace makes the file readable. No one can write to the file unless its associated tablespace is returned to the read-write state.

To take a data file offline, or bring it online, you must have the ALTER DATABASE system privilege. To take all datafiles or temp files offline using the ALTER TABLESPACE statement, you must have the ALTER TABLESPACE or MANAGE TABLESPACE system privilege. In an Oracle Real Application Clusters environment, the database must be open in exclusive mode.

Is there any difference between taking data files offline for the databases with archivelog and noarchivelog options??

Yes. Here are some examples.

Archivelog Mode

– bringing a data file online

ALTER DATABASE DATAFILE '< data>’ ONLINE;

- taking the data file offline

ALTER DATABASE DATAFILE '< data>’ OFFLINE;

NOARCHIVELOG Mode

To take a data file offline when the database is in NOARCHIVELOG mode, use the ALTER DATABASE statement with both the DATAFILE and OFFLINE DROP clauses. This enables you to take the data file offline and drop it immediately. It is useful, for example, if the data file contains only data from temporary segments and has not been backed up and the database is in NOARCHIVELOG mode.

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE DROP;

For taking the data files ONLINE there is no difference between the databases with archive log mode on or off.

Some times ‘alter database’ and some times ‘alter tablespace’ is used to take data files offline or online. Why is it so??

In most cases the above ALTER TABLESPACE statements can be issued whenever the database is mounted, even if it is not open. However, the database must not be open if the tablespace is the system tablespace, an undo tablespace, or the default temporary tablespace. The ALTER DATABASE DATAFILE and ALTER DATABASE TEMPFILE statements also have ONLINE/OFFLINE clauses, however in those statements you must enter all of the filenames for the tablespace.

The syntax is different from the ALTER TABLESPACE ... ONLINE|OFFLINE statement that alters a tablespace's availability, because that is a different operation. The ALTER TABLESPACE statement takes datafiles offline as well as the tablespace, but it cannot be used to alter the status of a temporary tablespace or its tempfile(s).

Can I rename the data files associated with a single tablespace??

If it is not a SYSTEM tabelspace, you can do this. Here are the steps

(1) Take the non-SYSTEM tablespace that contains the datafiles offline
ALTER TABLESPACE users OFFLINE NORMAL;
(2) Rename the datafiles using the operating system.
(3) Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the filenames within the database.
ALTER TABLESPACE users
RENAME DATAFILE ‘< existing>’
TO ‘< new>’;
Note:
The new files must already exist; this statement does not create the files. Also, always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old data file name exactly as it appears in the DBA_DATA_FILES view of the data dictionary.

It is always advisable to backup the database after every structural change. Or at the least backup the control file to trace.

How to Relocate and Rename Datafiles in a Single Tablespace??

An open database has a tablespace named USERS that is made up of datafiles all located on the same disk.

SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'USERS';

FILE_NAME BYTES
------------------------------------------ ----------------
C:ORADATAWHSUSERSUSERS01.DBF 102400000
C:ORADATAWHSUSERSUSERS02.DBF 102400000

To do:

The datafiles of the userstablespace are to be relocated to different and separate disk drives.

Get connected as:

You are currently connected with administrator privileges to the open database like SYS. You have a current backup of the database.

Take the tablespace containing the datafiles offline, or shut down the database and restart and mount it, leaving it closed. Either option closes the datafiles of the tablespace. Copy the datafiles to their new locations and rename them using the operating system.

You can execute an operating system command to copy a file by using the SQL*Plus HOST command.

Rename the datafiles within Oracle.

The data file pointers for the files that make up the users tablespace, recorded in the control file of the associated database, must now be changed from the old names to the new names.

If the tablespace is offline but the database is open, use the ALTER TABLESPACE ... RENAME DATAFILE statement. If the database is mounted but closed, use the ALTER DATABASE ... RENAME FILE statement.

ALTER TABLESPACE users
RENAME DATAFILE ' C:ORADATAWHSUSERSUSERS01.DBF',
' C:ORADATAWHSUSERSUSERS02.DBF'
TO ' D:ORADATAWHSUSERSUSERS02.DBF ',
' E:ORADATAWHSUSERSUSERS02.DBF ';

Bring the tablespace online, or open the database.

If the userstablespace is offline and the database is open, bring the tablespace back online. If the database is mounted but closed, open the database.

Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

Can I rename the data files associated with a multiple tablespaces??

You can rename and relocate datafiles of one or more tablespaces using ALTER DATABASE statement with the RENAME FILE clause. This option is the only choice if you want to rename or relocate datafiles of several tablespaces in one operation, or rename or relocate datafiles of the SYSTEM tablespace. If the database must remain open, consider instead the procedure outlined in the previous section.

To rename datafiles of several tablespaces in one operation or to rename datafiles of the SYSTEM tablespace, you must have the ALTER DATABASE system privilege.

Ensure that the database is mounted but closed.

Copy the datafiles to be renamed to their new locations and new names, using the operating system.

Use ALTER DATABASE to rename the file pointers in the database's control file.

For example,

the following statement renames the datafiles

C:ORADATAWHSTOOLSTOOLS01.DBF and
C:ORADATAWHSTOOLSSORTS01.DBF to
D:ORADATAWHSTOOLSTOOLS01.DBF and
E:ORADATAWHSTOOLSTOOLS02.DBF, respectively:

ALTER DATABASE
RENAME FILE ' C:ORADATAWHSTOOLSTOOLS01.DBF ',
' C:ORADATAWHSTOOLSSORTS01.DBF '
TO ' D:ORADATAWHSTOOLSTOOLS01.DBF ',
' E:ORADATAWHSTOOLSTOOLS02.DBF;

The new files must already exist; this statement does not create the files. Also, always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old data file name exactly as it appears in the DBA_DATA_FILES view of the data dictionary.

Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

How can I drop the data files??
You are to drop the tablespace to drop a data file/s.
If you are moving the objects from one tablespace to another to drop the older tablespace
1. Create a new tablespace
2. Move the objects from old to new
3. Drop the old tablespace
To drop a tempfile using the ALTER DATABASE statement issue the following statement
ALTER DATABASE TEMPFILE 'h:oracleoradatawhstemptemp_user_01.dbf' DROP INCLUDING DATAFILES;
How can I do the file mapping which is new in Oracle 9i??

(As Said by Oracle)

File Mapping Components

FMON is a background process started by Oracle whenever the FILE_MAPPING initialization parameter is set to TRUE.

FMON is responsible for:

Building mapping information, which is stored in the SGA. This information is composed of the following structures:
Files
File system extents
Elements
Sub-elements
These structures are explained in "Mapping Structures".

Refreshing mapping information when a change occurs because of:
Changes to Oracle data-files (size)
Addition or deletion of data-files
Changes to the storage configuration (not frequent)
Saving mapping information in the data dictionary to maintain a view of the information that is persistent across startup and shutdown operations Restoring mapping information into the SGA at instance startup. This avoids the need for a potentially expensive complete rebuild of the mapping information on every instance startup. You help control this mapping using procedures that are invoked with the DBMS_STORAGE_MAP package.

External Process (FMPUTL)
FMON spawns an external non-Oracle process called FMPUTL, that communicates directly with the vendor supplied mapping libraries. This process obtains the mapping information through all levels of the I/O stack, assuming that mapping libraries exist for all levels. On some platforms the external process requires that the SETUID bit is set to ON because root privileges are needed to map through all levels of the I/O mapping stack.

The external process is responsible for discovering the mapping libraries and dynamically loading them into its address space.

Mapping Libraries
Oracle uses mapping libraries to discover mapping information for the elements that are owned by a particular mapping library. Through these mapping libraries information about individual I/O stack elements is communicated. This information is used to populate dynamic performance views that can be queried by users.

Mapping libraries need to exist for all levels of the stack for the mapping to be complete, and different libraries may own their own parts of the I/O mapping stack. For example, a VERITAS VxVM library would own the stack elements related to the VERITAS Volume Manager, and an EMC library would own all EMC storage specific layers of the I/O mapping stack.

Mapping libraries are vendor supplied. However, Oracle currently supplies a mapping library for EMC storage. The mapping libraries available to a database server are identified in a special file named filemap.ora.

Mapping Structures

The mapping structures and Oracle's representation of these structures are described in this section. You will need to understand this information in order to interpret the information in the mapping views.

The following are the primary structures that compose the mapping information:

Files

A file mapping structure provides a set of attributes for a file, including file size, number of file system extents that the file is composed of, and the file type.

File system extents

A file system extent mapping structure describes a contiguous chunk of blocks residing on one element. This includes the device offset, the extent size, the file offset, the type (data or parity), and the name of the element where the extent resides.

Note:
File system extents are not the same as Oracle extents. File system extents are physical contiguous blocks of data written to a device as managed by the file system. Oracle extents are logical structures managed by Oracle, such as tablespace extents.

Elements

An element mapping structure is the abstract mapping structure that describes a storage component within the I/O stack. Elements may be mirrors, stripes, partitions, RAID5, concatenated elements, and disks. These structures are the mapping building blocks.

Sub-elements

A sub-element mapping structure describes the link between an element and the next elements in the I/O mapping stack. This structure contains the sub-element number, size, the element name where the sub-element exists, and the element offset.