Oracle7 Server Administrator's Guide Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

Managing Datafiles


This chapter describes the various aspects of datafile management, and includes the following topics:

See Also: This chapter contains several references to Oracle Server Manager. For more information about performing specific tasks using Server Manager/GUI or Server Manager/LineMode, see the Oracle Server Manager User's Guide.

Datafiles can also be created as part of database recovery from a media failure. For more information, see page 24 - 7.

For information on tablespaces and datafiles in Trusted Oracle7 Server, see the .

Guidelines for Managing Datafiles

This section describes aspects of managing datafiles, and includes the following topics:

Number of Datafiles

At least one datafile is required for the SYSTEM tablespace of a database; a small system might have a single datafile. In general, keeping a few large datafiles is preferable to many small datafiles, because you can keep fewer files open at the same time.

You can add datafiles to tablespaces, subject to the following operating system-specific datafile limits:

operating system limit

Each operating system sets a limit on the maximum number of files per process. Regardless of all other limits, more datafiles cannot be created when the operating system limit of open files is reached.

Oracle7 system limit

Oracle7 imposes a maximum limit on the number of datafiles for any Oracle7 database opened by any instance. This limit is port-specific.

control file upper bound

At database creation, you must indicate the maximum number of datafiles expected for the database so that an adequate amount of space can be reserved in the database's control file. You set this limit with the MAXDATAFILES parameter in the CREATE DATABASE statement. This maximum cannot exceed the Oracle7 system limit or any operating system limit. If you are not sure how to set this parameter, use a high number to avoid unnecessary limitation. The default value is operating system-specific.

Note: You can increase space in the database by resizing datafiles. Resizing existing datafiles is useful if you are nearing the MAXDATAFILES limit.

instance or SGA upper bound

When starting an Oracle7 instance, the database's parameter file indicates the amount of SGA space to reserve for datafile information; the maximum number of datafiles is controlled by the DB_FILES parameter. This limit applies only for the life of the instance. DB_FILES can temporarily reduce the control file upper bound, but cannot raise it. The default value is the value of the control file upper bound.

With the Oracle7 Parallel Server, all instances must set the instance datafile upper bound to the same value.

The use of DB_FILES and MAXDATAFILES is optional. If neither is used, the default maximum number of datafiles is the operating system-specific Oracle7 system limit.

See Also: For more information on operating system limits, see your operating system-specific Oracle documentation.

For information about Parallel Server operating system limits, see the manual.

For more information about MAXDATAFILES, see the Oracle7 Server SQL Reference.

Set the Size of Datafiles

The first datafile (in the original SYSTEM tablespace) must be at least 2M to contain the initial data dictionary and rollback segment. If you install other Oracle7 products, they may require additional space in the SYSTEM tablespace (for online help, for example); see the installation instructions for these products.

Place Datafiles Appropriately

Tablespace location is determined by the physical location of the datafiles that constitute that tablespace. Use the hardware resources of your computer appropriately.

For example, if several disk drives are available to store the database, it might be helpful to store table data in a tablespace on one disk drive, and index data in a tablespace on another disk drive. This way, when users query table information, both disk drives can work simultaneously, retrieving table and index data at the same time.

Store Datafiles Separately From Redo Log Files

Datafiles should not be stored on the same disk drive that stores the database's 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.

If you multiplex your redo log files, then the likelihood of your losing all of your redo log files is low, so you can store datafiles on the same drive as some redo log files.

Creating and Adding Datafiles to a Tablespace

You can create and add datafiles to a tablespace to increase the total amount of disk space allocated for the tablespace, and consequently the database.

To add datafiles to a tablespace, use either the Add Datafile dialog box of Server Manager/GUI, or the SQL command ALTER TABLESPACE. You must have the ALTER TABLESPACE system privilege to add datafiles to a tablespace.

The following statement creates a new datafile for the RB_SEGS tablespace:

ALTER TABLESPACE rb_segs
   ADD DATAFILE 'filename1' SIZE 1M;

If you add new datafiles to a tablespace and do not fully specify the filenames, Oracle7 creates the datafiles in the default directory of the database server. Unless you want to reuse existing files, make sure the new filenames do not conflict with other files; the old files that have been previously dropped will be overwritten.

Changing a Datafile's Size

This section describes the various ways to alter the size of a datafile, and includes the following topics:

Enabling and Disabling Automatic Extension for a Datafile

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 results in the following:

Note: FILEXT$ is not created with the data dictionary scripts, so there is no easy way to create database views on it. Thus, catalog scripts will be unsuccessful if you attempt to create a database view on FILEXT$. FILEXT$ is the only place you can query where datafiles have autoextend turned on, and the current settings of their parameter values.

You can specify automatic file extension when you create datafiles via the following SQL commands:

You can enable or disable automatic file extension for existing datafiles, or manually resize a datafile using the SQL command ALTER DATABASE.

The following example enables automatic extension for a datafile, FILENAME2, added to the USERS tablespace:

ALTER TABLESPACE users
   ADD DATAFILE 'filename2' 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 automatic extension for the datafile FILENAME2:

ALTER DATABASE DATAFILE 'filename2'
   AUTOEXTEND OFF

See Also: For more information about the SQL commands for creating or altering datafiles, see the .

Manually Resizing a Datafile

You can manually increase or decrease the size of a datafile using the ALTER DATABASE command.

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 allows you to reclaim unused space in the database. This is useful for correcting errors in estimates of space requirements.

In this example, assume that the datafile FILENAME2 has extended up to 250M. However, because its tablespace now stores smaller objects, the datafile can be reduced in size.

The following command decreases the size of datafile FILENAME2:

ALTER DATABASE DATAFILE 'filename2'
   RESIZE 100M

Note: It is not always possible to decrease the size of a file to a specific value.

See Also: For more information about the implications resizing files has for downgrading, see the Oracle7 Server Migration.

For more information about the ALTER DATABASE command, see the .

Altering Datafile Availability

This section describes ways to alter datafile availability, and includes the following topics:

In very rare situations, you might need to bring specific datafiles online (make them available) or take specific files offline (make them unavailable). For example, when Oracle7 has problems writing to a datafile, it can automatically take the datafile offline. You might need to take the damaged datafile offline or bring it online manually.

Note: You can make all datafiles in a tablespace, other than the files in the SYSTEM tablespace, temporarily unavailable by taking the tablespace offline. You must leave these files in the tablespace to bring the tablespace back online.

Offline datafiles cannot be accessed. Bringing a datafile in a read-only tablespace online makes the file readable. No one can write to the file unless its associated tablespace is returned to the read-write state. The files of a read-only tablespace can independently be taken online or offline using the DATAFILE option of the ALTER DATABASE command.

To bring a datafile online or take it offline, in either archiving mode, you must have the ALTER DATABASE system privilege. You can perform these operations only when the database is open in exclusive mode.

Bringing Datafiles Online in ARCHIVELOG Mode

To bring an individual datafile online, issue the SQL command ALTER DATABASE, and include the DATAFILE parameter.

Note: To use this option of the ALTER DATABASE command, the database must be in ARCHIVELOG mode. This requirement prevents you from accidentally losing the datafile, since taking the datafile offline while in NOARCHIVELOG mode is likely to result in losing the file.

The following statement brings the specified datafile online:

ALTER DATABASE DATAFILE 'filename' ONLINE;

See Also: For more information about bringing datafiles online during media recovery, see page 24 - 7.

Taking Datafiles Offline in NOARCHIVELOG Mode

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

The following statement brings the specified datafile offline:

ALTER DATABASE DATAFILE 'filename' OFFLINE DROP;

Renaming and Relocating Datafiles

This section describes the various aspects of renaming and relocating datafiles, and includes the following topics:

You can rename datafiles to change either their names or locations. Oracle7 provides options to make the following changes:

Note: To rename or relocate datafiles of the SYSTEM tablespace, you must use the second option, because you cannot take the SYSTEM tablespace offline.

Renaming and relocating datafiles with these procedures only change the pointers to the datafiles, as recorded in the database's control file; it does not physically rename any operating system files, nor does it copy files at the operating system level. Therefore, renaming and relocating datafiles involve several steps. Read the steps and examples carefully before performing these procedures.

You must have the ALTER TABLESPACE system privilege to rename datafiles of a single tablespace.

Renaming and Relocating Datafiles for a Single Tablespace

The following steps describe how to rename or relocate datafiles from a single tablespace.

To Rename or Relocate Datafiles for a Single Tablespace

For example, the following statement renames the datafiles FILENAME1 and FILENAME2 to FILENAME3 and FILENAME4, respectively:

ALTER TABLESPACE users
   RENAME DATAFILE 'filename1', 'filename2'
      TO 'filename3', 'filename4';

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

Renaming and Relocating Datafiles for Multiple Tablespaces

You can rename and relocate datafiles of one or more tablespaces with the SQL command ALTER DATABASE with the RENAME FILE option. 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.

To Rename and Relocate Datafiles for Multiple Tablespaces

For example, the following statement renames the datafiles FILENAME 1 and FILENAME2 to FILENAME3 and FILENAME4, respectively:

	ALTER DATABASE
	   RENAME FILE 'filename1', 'filename2'
	   TO 'filename3', 'filename4';

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

Relocating Datafiles: Example

For this example, assume the following conditions:

To Relocate Datafiles

SELECT file_name, bytes FROM sys.dba_data_files
   WHERE tablespace_name = 'USERS';
FILE_NAME         BYTES
---------------------------
FILENAME1         102400000
FILENAME2         102400000

Suggestion: You can execute an operating system command to copy a file without exiting Server Manager/LineMode by using the HOST command.

See Also: For more information about the DBA_DATA_FILES data dictionary view, see the .

For more information about taking a tablespace offline, see "Taking Tablespaces Offline" [*].

For more information about mounting a database without opening it, see Chapter 3.

Verifying Data Blocks in Datafiles

If you want to configure Oracle7 to use checksums to verify data blocks, set the initialization parameter DB_BLOCK_CHECKSUM to TRUE. The default value of DB_BLOCK_CHECKSUM is FALSE.

When you enable block checking, Oracle7 computes a checksum for each block written to disk. Checksums are computed for all data blocks, including temporary blocks.

The DBWR process calculates the checksum for each block and stores it in the block's header. Checksums are also computed by the direct loader.

The next time Oracle7 reads a data block, it uses the checksum to detect corruption in the block. If a corruption is detected, Oracle7 returns message ORA-01578 and writes information about the corruption to a trace file.

Warning: Setting DB_BLOCK_CHECKSUM to TRUE can cause performance overhead. Set this parameter to TRUE only under the advice of Oracle Support personnel to diagnose data corruption problems.

Viewing Information About Datafiles

The following data dictionary views provide useful information about the datafiles of a database:

Listing Status Information About Datafiles: Example

The following example illustrates how to use a view not already illustrated in other chapters of this manual. Assume you are using a database that contains two tablespaces, SYSTEM and USERS. USERS is made up of two files, FILE1 (100MB) and FILE2 (200MB); the tablespace has been taken offline normally. Here, you query V$DATAFILE to view status information about datafiles of a database:

SELECT name,
   file#,
   status,
   checkpoint_change# "CHECKPOINT"   FROM v$datafile;
NAME                                FILE# STATUS  CHECKPOINT
----------------------------------- ----- ------- ----------
filename1                           1     SYSTEM        3839
filename2                           2     OFFLINE       3782
filename3                           3     OFFLINE       3782

FILE# lists the file number of each datafile; the first datafile in the SYSTEM tablespace, created with the database, is always file 1. STATUS lists other information about a datafile. If a datafile is part of the SYSTEM tablespace, its status is SYSTEM (unless it requires recovery). If a datafile in a non-SYSTEM tablespace is online, its status is ONLINE. If a datafile in a non-SYSTEM tablespace is offline, its status can be either OFFLINE or RECOVER. CHECKPOINT lists the final SCN written for a datafile's most recent checkpoint.




Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index