精华区文章阅读

发信人: rhine (风雨无阻), 信区: Database
标  题: Oracle DBA Guide V8, part 9
发信站: 哈工大紫丁香 (2000年07月08日20:31:01 星期六), 站内信件

发信人: chendu (Oracle Only), 信区: Database
标  题: Oracle DBA Guide V8, part 9
发信站: BBS 水木清华站 (Sat Nov 13 15:24:24 1999)

Hi, required by several friends, I am posting the
Oracle Database Administrator's Guide version 8
here.

Web Master, if my post it too long and not good for
the bbs, please delete it and I am sorry for it.

Hoping everyone enjoy it!

Good luck!

-----------------------------------------------------------------
9
Managing Datafiles
This chapter describes the various aspects of datafile management, and inclu
des the following topics:
Guidelines for Managing Datafiles
Creating and Adding Datafiles to a Tablespace
Changing a Datafile's Size
Altering Datafile Availability
Renaming and Relocating Datafiles
Verifying Data Blocks in Datafiles
Viewing Information About Datafiles
See Also: This chapter contains several references to Oracle Enterprise Mana
ger. For more information about performing specific tasks using Enterprise M
anager/GUI or Server Manager/LineMode, see the Oracle Enterprise Manager Adm
inistrator's Guide and Oracle Server Manager User's Guide.
Datafiles can also be created as part of database recovery from a media fail
ure. For more information, see the Oracle8 Backup and Recovery Guide.
Guidelines for Managing Datafiles
This section describes aspects of managing datafiles, and includes the follo
wing topics:
Number of Datafiles
Set the Size of Datafiles
Place Datafiles Appropriately
Store Datafiles Separately From Redo Log Files
Every datafile has two associated file numbers: an absolute file number and
a relative file number.
An absolute file number uniquely identifies a datafile in the database. Prio
r to Oracle8, the absolute file number was referred to as simply the "file n
umber."
umber."
A relative file number uniquely identifies a datafile within a tablespace. F
or small and medium size databases, relative file numbers usually have the s
ame value as the absolute file number. However, when the number of datafiles
in a database exceeds a threshold (typically 1023), the relative file numbe
r will differ from the absolute file number. You can locate relative file nu
mbers in many of the data dictionary views.
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 sys
tem-specific datafile limits:
operating system limit
  Each operating system sets a limit on the maximum number of open files per
process. Regardless of all other limits, more datafiles cannot be created w
hen the operating system limit of open files is reached.
Oracle system limit
  Oracle imposes a maximum limit on the number of datafiles for any Oracle d
atabase opened by any instance. This limit is port-specific.
control file upper bound
  When you issue CREATE DATABASE or CREATE CONTROLFILE statements, the MAXDA
TAFILES parameter specifies an initial size of the datafile portion of the c
ontrol file. Later, if you add a file whose number exceeds MAXDATAFILES but
is less than or equal to DB_FILES, the control file automatically expands to
allow the datafile portion to accommodate more files.
instance or SGA upper bound
  When starting an Oracle8 instance, the database's parameter file indicates
the amount of SGA space to reserve for datafile information; the maximum nu
mber of datafiles is controlled by the DB_FILES parameter. This limit applie
s only for the life of the instance.
Note: The default value of DB_FILES is operating system specific.
With the Oracle Parallel Server, all instances must set the instance datafil
e upper bound to the same value.
When determining a value for DB_FILES, take the following into consideration
:
If the value of DB_FILES is too low, you will be unable to add datafiles bey
ond the DB_FILES limit without first shutting down the database.
IF the value of DB_FILES is too high, memory is unnecessarily consumed.
Theoretically, an Oracle database can have an unlimited number of datafiles.
Nevertheless, you should consider the following when determining the number
of datafiles:
Performance is better with a small number of datafiles rather than a large n
umber of small datafiles. Large files also increase the granularity of a rec
umber of small datafiles. Large files also increase the granularity of a rec
overable unit.
Operating systems often impose a limit on the number of files a process can
open simultaneously. Oracle's DBW0 process can open all online datafiles. Or
acle is also capable of treating open file descriptors as a cache, automatic
ally closing files when the number of open file descriptors reaches the oper
ating system-defined limit.
Oracle allows more datafiles in the database than the operating system-defin
ed limit; this can have a negative performance impact. When possible, adjust
the operating system limit on open file descriptors so that it is larger th
an the number of online datafiles in the database.
The operating system specific limit on the maximum number of datafiles allow
ed in a tablespace is typically 1023 files.
See Also: For more information on operating system limits, see your operatin
g system-specific Oracle documentation.
For information about Parallel Server operating system limits, see Oracle8 P
arallel Server Concepts and Administration.
For more information about MAXDATAFILES, see the Oracle8 SQL Reference.
Set the Size of Datafiles
The first datafile (in the original SYSTEM tablespace) must be at least 7M t
o contain the initial data dictionary and rollback segment. If you install o
ther Oracle products, they may require additional space in the SYSTEM tables
pace (for online help, for example); see the installation instructions for t
hese 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 tab
le 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 databa
se's redo log files. If the datafiles and redo log files are stored on the s
ame disk drive and that disk drive fails, the files cannot be used in your d
atabase 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 amoun
t of disk space allocated for the tablespace, and consequently the database.

Ideally, when creating a tablespace DBAs should estimate the potential size
of the database objects and add sufficient files or devices. Doing so ensure
s that data is spread evenly across all devices.
To add datafiles to a tablespace, use either the Add Datafile dialog box of
Enterprise Manager/GUI, or the SQL command ALTER TABLESPACE. You must have t
he 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 filena
mes, Oracle creates the datafiles in the default directory of the database s
erver. 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 dr
opped 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
Manually Resizing a Datafile
Enabling and Disabling Automatic Extension for a Datafile
You can create datafiles or alter existing datafiles so that they automatica
lly increase in size when more space is needed in the database. The files in
crease in specified increments up to a specified maximum.
Setting your datafiles to extend automatically results in the following:
reduces the need for immediate intervention when a tablespace runs out of sp
ace
ensures applications will not halt because of failures to allocate extents
To find out if a datafile is auto-extensible, query the DBA_DATA_FILES view
and examine the AUTOEXTENSIBLE column.
You can specify automatic file extension when you create datafiles via the f
ollowing SQL commands:
CREATE DATABASE
CREATE TABLESPACE
ALTER TABLESPACE
You can enable or disable automatic file extension for existing datafiles, o
r 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 wh
The value of NEXT is the minimum size of the increments added to the file wh
en it extends. The value of MAXSIZE is the maximum size to which the file ca
n 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 alteri
ng datafiles, see the Oracle8 SQL Reference.
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 you
r database without adding more datafiles. This is beneficial if you are conc
erned about reaching the maximum number of datafiles allowed in your databas
e.
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 ca
n 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 spec
ific value.  ---------------------------------------------------------------
-----------------
See Also: For more information about the implications resizing files has for
downgrading, see Oracle8 Migration.
For more information about the ALTER DATABASE command, see the Oracle8 Refer
ence.
Altering Datafile Availability
This section describes ways to alter datafile availability, and includes the
following topics:
Bringing Datafiles Online in ARCHIVELOG Mode
Taking Datafiles Offline in NOARCHIVELOG Mode
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 Oracle has problems writing to a datafile, it can automat
ically take the datafile offline. You might need to take the damaged datafil
e offline or bring it online manually.
----------------------------------------------------------------------------
----
Note:
Note:
You can make all datafiles in a tablespace, other than the files in the SYST
EM tablespace, temporarily unavailable by taking the tablespace offline. You
must leave these files in the tablespace to bring the tablespace back onlin
e.
----------------------------------------------------------------------------
----
Offline datafiles cannot be accessed. Bringing a datafile in a read-only tab
lespace 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 th
e 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 operat
ions 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 AR
CHIVELOG mode. This requirement prevents you from accidentally losing the da
tafile, since taking the datafile offline while in NOARCHIVELOG mode is like
ly 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 Oracle8 Backup and Recovery Guide.
Taking Datafiles Offline in NOARCHIVELOG Mode
To take a datafile offline when the database is in NOARCHIVELOG mode, use th
e ALTER DATABASE command with the DATAFILE parameter and the OFFLINE DROP op
tion. 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 mod
e.
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 datafi
les, and includes the following topics:
Renaming and Relocating Datafiles for a Single Tablespace
Renaming and Relocating Datafiles for Multiple Tablespaces
iple Tablespaces
You can rename datafiles to change either their names or locations. Oracle p
rovides options to make the following changes:
Rename and relocate datafiles in a single offline tablespace (for example, F
ILENAME1 and FILENAME2 in TBSPACE1) while the rest of the database is open.
Rename and relocate datafiles in several tablespaces simultaneously (for exa
mple, FILE1 in TBSP1 and FILE2 in TBSP2) while the database is mounted but c
losed.
----------------------------------------------------------------------------
----
Note:
To rename or relocate datafiles of the SYSTEM tablespace, you must use the s
econd option, because you cannot take the SYSTEM tablespace offline.
----------------------------------------------------------------------------
----
Renaming and relocating datafiles with these procedures only change the poin
ters to the datafiles, as recorded in the database's control file; it does n
ot physically rename any operating system files, nor does it copy files at t
he operating system level. Therefore, renaming and relocating datafiles invo
lve several steps. Read the steps and examples carefully before performing t
hese procedures.
You must have the ALTER TABLESPACE system privilege to rename datafiles of a
single tablespace.
single tablespace.
Renaming and Relocating Datafiles for a Single Tablespace
To Rename or Relocate Datafiles from a Single Tablespace
Take the non-SYSTEM tablespace that contains the datafiles offline.
Copy the datafiles to the new location or new names using the operating syst
em.
Make sure that the new, fully specified filenames are different from the old
filenames.
Use the SQL command ALTER TABLESPACE with the RENAME DATAFILE option to chan
ge the filenames within the database.
For example, the following statement renames the datafiles FILENAME1 and FIL
ENAME2 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 identi
fy the old and new datafiles. In particular, specify the old filename exactl
y as it appears in the DBA_DATA_FILES view of the data dictionary.
Renaming and Relocating Datafiles for Multiple Tablespaces
You can rename and relocate datafiles of one or more tablespaces using the S
QL command ALTER DATABASE with the RENAME FILE option. This option is the on
ly 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 i
n the previous section.
To rename datafiles of several tablespaces in one operation or to rename dat
afiles of the SYSTEM tablespace, you must have the ALTER DATABASE system pri
vilege.
Ensure that the database is mounted but closed.
Copy the datafiles to be renamed to their new locations and new names, using
operating system commands.
Make sure the new copies of the datafiles have different fully specified fil
enames from the datafiles currently in use.
Use the SQL command ALTER DATABASE to rename the file pointers in the databa
se's control file.
For example, the following statement renames the datafiles FILENAME 1 and FI
LENAME2 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 identi
fy the old and new datafiles. In particular, specify the old filename exactl
y as it appears in the DBA_DATA_FILES view of the data dictionary.
y as it appears in the DBA_DATA_FILES view of the data dictionary.
Relocating Datafiles: Example
For this example, assume the following conditions:
An open database has a tablespace named USERS that is comprised of datafiles
located on the same disk of a computer.
The datafiles of the USERS tablespace are to be relocated to a different dis
k drive.
You are currently connected with administrator privileges to the open databa
se while using Enterprise Manager.
To Relocate Datafiles
Identify the datafile names of interest.
The following query of the data dictionary view DBA_DATA_FILES lists the dat
afile names and respective sizes (in bytes) of the USERS tablespace:
    SELECT file_name, bytes FROM sys.dba_data_files
       WHERE tablespace_name = 'USERS';
    FILE_NAME         BYTES
    ---------------------------
    FILENAME1         102400000
    FILENAME2         102400000
Here, FILENAME1 and FILENAME2 are two fully specified filenames, each 1MB in
size.
Back up the database.
Before making any structural changes to a database, such as renaming and rel
ocating the datafiles of one or more tablespaces, always completely back up
the database.
Take the tablespace containing the datafile offline, or shut down the databa
se and restart and mount it, leaving it closed. Either option closes the dat
afiles of the tablespace.
Copy the datafiles to their new locations using operating system commands. F
or this example, the existing files FILENAME1 and FILENAME2 are copied to FI
LENAME3 and FILENAME4.
----------------------------------------------------------------------------
----Note: You can execute an operating system command to copy a file without
exiting Server Manager/LineMode by using the HOST command.  ---------------
-----------------------------------------------------------------
Rename the datafiles within Oracle.
The datafile pointers for the files that comprise the USERS tablespace, reco
rded in the control file of the associated database, must now be changed fro
m FILENAME1 and FILENAME2 to FILENAME3 and FILENAME4, respectively.
If the tablespace is offline but the database is open, use the Enterprise Ma
nager Rename Datafiles dialog box or ALTER TABLESPACE...RENAME DATAFILE comm
and. If the database is mounted but closed, use the ALTER DATABASE...RENAME
FILE command.
Bring the tablespace online, or shut down and restart the database.
If the USERS tablespace is offline and the database is open, bring the table
If the USERS tablespace is offline and the database is open, bring the table
space back online. If the database is mounted but closed, open the database.

Back up the database. After making any structural changes to a database, alw
ays perform an immediate and complete backup.
See Also: For more information about the DBA_DATA_FILES data dictionary view
, see the Oracle8 Reference.
For more information about taking a tablespace offline, see "Taking Tablespa
ces Offline".
Verifying Data Blocks in Datafiles
If you want to configure Oracle to use checksums to verify data blocks, set
the initialization parameter DB_BLOCK_CHECKSUM to TRUE. The value of this pa
rameter can be changed dynamically, or set in the init.ora parameter file. T
he default value of DB_BLOCK_CHECKSUM is FALSE.
When you enable block checking, Oracle computes a checksum for each block wr
itten to disk. Checksums are computed for all data blocks, including tempora
ry blocks.
The DBW0 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 Oracle reads a data block, it uses the checksum to detect corr
uption in the block. If a corruption is detected, Oracle 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 p
arameter to TRUE only under the advice of Oracle Support personnel to diagno
se data corruption problems.
----------------------------------------------------------------------------
----
Viewing Information About Datafiles
The following data dictionary views provide useful information about the dat
afiles of a database:
USER_EXTENTS, DBA_EXTENTS
USER_SEGMENTS, DBA_SEGMENTS
USER_FREE_SPACE, DBA_FREE_SPACE
DBA_USERS
DBA_TS_QUOTAS
USER_TABLESPACES, DBA_TABLESPACES
DBA_DATA_FILES
V$DATAFILE
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 conta
ins two tablespaces, SYSTEM and USERS. USERS is made up of two files, FILE1
(100MB) and FILE2 (200MB); the tablespace has been taken offline normally. H
(100MB) and FILE2 (200MB); the tablespace has been taken offline normally. H
ere, you query V$DATAFILE to view status information about datafiles of a da
tabase:
SELECT name,
    file#,
    status,
    checkpoint_change# "CHECKPOINT"   FROM    $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 SYST
EM tablespace created with the database is always file 1. STATUS lists other
information about a datafile. If a datafile is part of the SYSTEM tablespac
e, its status is SYSTEM (unless it requires recovery). If a datafile in a no
n-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. C
HECKPOINT lists the final SCN written for a datafile's most recent checkpoin
t.
----------------------------------------------------------------------------
----
-------------------------------------------------------------------

--
※ 来源:·BBS 水木清华站 bbs.net.tsinghua.edu.cn·[FROM: 162.105.41.166]

--
           海纳百川，
                   有容乃大，
                           壁立千尺，
                                   无欲则刚。

※ 来源:·哈工大紫丁香 bbs.hit.edu.cn·[FROM: dip.hit.edu.cn]

Database 版 (精华区)