Managing Datafiles
This chapter describes the various aspects of
datafile management, and contains the following topics:
- Guidelines for Managing Datafiles
- Creating Datafiles and Adding Datafiles to a Tablespace
- Changing a Datafile's Size
- Altering Datafile Availability
- Renaming and Relocating Datafiles
- Dropping Datafiles
- Verifying Data Blocks in Datafiles
- Mapping Files to Physical Devices
- Viewing Datafile Information
|
Chapter 3,
"Using Oracle-Managed Files" for information about creating
datafiles and tempfiles that are both created and managed by the Oracle
database server
|
Guidelines for Managing Datafiles
Datafiles are physical files of the operating
system that store the data of all logical structures in the database. They must
be explicitly created for each tablespace. Oracle assigns each datafile two
associated file numbers, an absolute file number and a relative file number,
that are used to uniquely identify it. These numbers are described in the
following table:
File numbers are displayed in many data dictionary
views. You can optionally use file numbers instead of file names to identify
datafiles or tempfiles in SQL statements. When using a file number, specify the
file number that is displayed in the
FILE#
column of the V$DATAFILE or V$TEMPFILE view. This file number is
also displayed in the FILE_ID
column of the DBA_DATA_FILES
or DBA_TEMP_FILES view.- Determine the Number of Datafiles
- Determine the Size of Datafiles
- Place Datafiles Appropriately
- Store Datafiles Separate from Redo Log Files
Determine the Number of Datafiles
At least one datafile is required for the
SYSTEM tablespace of a database. A small
system might have a single datafile. The following are some guidelines to
consider when determining the number of datafiles for your database.Determine the Value of the DB_FILES Initialization Parameter
When starting an Oracle instance, the
DB_FILES initialization parameter
indicates the amount of SGA space to reserve for datafile information and thus,
the maximum number of datafiles that can be created for the instance. This
limit applies for the life of the instance. You can change the value of DB_FILES (by changing the initialization
parameter setting), but the new value does not take effect until you shut down
and restart the instance.- If the value of
DB_FILESis too low, you cannot add datafiles beyond theDB_FILESlimit without first shutting down the database. - If the value of
DB_FILESis too high, memory is unnecessarily consumed.
Limitations When Adding Datafiles to a Tablespace
- Operating systems often impose a limit on the number of files a process can open simultaneously. More datafiles cannot be created when the operating system limit of open files is reached.
- Operating systems impose limits on the number and size of datafiles.
- Oracle imposes a maximum limit on the number of datafiles for any Oracle database opened by any instance. This limit is operating system specific.
- You cannot exceed the number of datafiles specified by the
DB_FILESinitialization parameter. - When you issue
CREATE DATABASEorCREATE CONTROLFILEstatements, theMAXDATAFILESparameter specifies an initial size of the datafile portion of the control file. However, if you attempt to add a new file whose number is greater thanMAXDATAFILES, but less than or equal toDB_FILES, the control file will expand automatically so that the datafiles section can accommodate more files.
Consider the Performance Impact
The number of datafiles comprising a tablespace,
and ultimately the database, can have an impact upon performance.
Oracle allows more datafiles in the database than
the operating system defined limit. Oracle's DBWn processes can open
all online datafiles. Oracle is capable of treating open file descriptors as a
cache, automatically closing files when the number of open file descriptors
reaches the operating system-defined limit. This can have a negative
performance impact. When possible, adjust the operating system limit on open
file descriptors so that it is larger than the number of online datafiles in
the database.
|
Determine the Size of Datafiles
The first datafile (in the original
SYSTEM tablespace) must be at least 150M
to contain the initial data dictionary and rollback segment. If you install
other Oracle products, they may require additional space in the SYSTEM tablespace. See the installation
instructions for these products for information about their space requirements.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, 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.
Store Datafiles Separate 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 losing all of your redo log files is low, so you can store
datafiles on the same drive as some redo log files.
Creating Datafiles and Adding Datafiles to a Tablespace
When creating a tablespace, you should estimate
the potential size of database objects and create sufficient datafiles. Later,
if needed, you can create additional datafiles and add them to a tablespace to
increase the total amount of disk space allocated to it, and consequently the
database. Preferably, place datafiles on multiple devices, so as to ensure that
data is spread evenly across all devices.
You can create datafiles and associate them with a
tablespace using any of the statements listed in the following table. In all
cases, you can either specify the file specifications for the datafiles being
created, or you can use the Oracle Managed Files feature to create files that
are created and managed by the database server. The table includes a brief
description of the statement, as used to create datafiles, and references the
section of this book where use of the statement is most completely described:
|
Not discussed in this book. See Oracle9i
User-Managed Backup and Recovery Guide.
|
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.
Changing a Datafile's Size
This section describes the various ways to alter
the size of a datafile, and contains 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.
- 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 datafile 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:
You can enable or disable automatic file extension
for existing datafiles, or manually resize a datafile using the
ALTER DATABASE statement.ALTER TABLESPACE users
ADD DATAFILE '/u02/oracle/rbdb1/users03.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.ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf'
AUTOEXTEND OFF;
|
Oracle9i
SQL Reference for more information about the SQL statements for
creating or altering datafiles
|
Manually Resizing a Datafile
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 datafile
/u02/oracle/rbdb1/stuff01.dbf has
extended up to 250M. However, because its tablespace now stores smaller
objects, the datafile can be reduced in size.ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf'
RESIZE 100M;
Altering Datafile Availability
You can take individual datafiles or tempfiles 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 tempfiles 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 datafile is when Oracle has problems writing to a
datafile and automatically takes the datafile offline. Later, after resolving
the problem, you can bring the datafile 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 datafile 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 datafile offline, or bring it online,
you must have the
ALTER DATABASE
system privilege. To take all datafiles or tempfiles 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.- Bringing Datafiles Online or Taking Offline in ARCHIVELOG Mode
- Taking Datafiles Offline in NOARCHIVELOG Mode
- Altering the Availability of All Datafiles or Tempfiles in a Tablespace
|
You can make all datafiles in any tablespace, except 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.
For more information about taking a tablespace offline, see "Taking
Tablespaces Offline".
|
Bringing Datafiles Online or Taking Offline in ARCHIVELOG Mode
To bring an individual datafile online, issue the
ALTER DATABASE statement and include the
DATAFILE clause.The
following statement brings the specified datafile online:ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;
Taking Datafiles Offline in NOARCHIVELOG Mode
To take a datafile 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 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.ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE DROP;
Altering the Availability of All Datafiles or Tempfiles in a Tablespace
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. Specifically,
the statements that affect online/offline status are:
You are required only to enter the tablespace
name, not the individual datafiles or tempfiles. All of the datafiles or
tempfiles are affected, but the online/offline status of the tablespace itself
is not changed.
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).Renaming and Relocating Datafiles
You can rename datafiles to either change their
names or relocate them. Some options, and procedures which you can follow, are
described in the following sections:
For example, renaming filename1
and filename2 in tablespace1, while the rest of the database
is open.
For example, renaming filename1
in tablespace1 and filename2 in tablespace2, while
the database is mounted but closed.
|
To rename or relocate datafiles of the
SYSTEM tablespace, you must use the
second option, because you cannot take the SYSTEM
tablespace offline. |
When you rename and relocate datafiles with these
procedures, only the pointers to the datafiles, as recorded in the database's
control file, are changed. The procedures do not physically rename any
operating system files, nor do they copy files at the operating system level.
Renaming and relocating datafiles involves several steps. Read the steps and
examples carefully before performing these procedures.
Renaming and Relocating Datafiles for a Single Tablespace
The section offers some procedures for renaming
and relocating datafiles in a single tablespace. You must have the
ALTER TABLESPACE system privilege to
rename datafiles of a single tablespace.Renaming Datafiles in a Single Tablespace
ALTER TABLESPACE users OFFLINE NORMAL;
- Use the
ALTER TABLESPACEstatement with theRENAME DATAFILEclause to change the filenames within the database.
For example, the
following statement renames the datafiles /u02/oracle/rbdb1/user1.dbf and
/u02/oracle/rbdb1/user2.dbf to/u02/oracle/rbdb1/users01.dbf and
/u02/oracle/rbdb1/users02.dbf, respectively:
ALTER TABLESPACE users
RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf',
'/u02/oracle/rbdb1/user2.dbf'
TO '/u02/oracle/rbdb1/users01.dbf',
'/u02/oracle/rbdb1/users02.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 datafile 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.
Relocating and Renaming Datafiles in a Single Tablespace
- An open database has a tablespace named
usersthat is made up of datafiles all located on the same disk. - The datafiles of the
userstablespace are to be relocated to different and separate disk drives. - You are currently connected with administrator privileges to the open database.
- You have a current backup of the database.
The following query of
the data dictionary view
DBA_DATA_FILES
lists the datafile names and respective sizes (in bytes) of the users tablespace:SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'USERS';
FILE_NAME BYTES
------------------------------------------ ----------------
/U02/ORACLE/RBDB1/USERS01.DBF 102400000
/U02/ORACLE/RBDB1/USERS02.DBF 102400000
- 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.
The datafile 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 '/u02/oracle/rbdb1/users01.dbf',
'/u02/oracle/rbdb1/users02.dbf'
TO '/u03/oracle/rbdb1/users01.dbf',
'/u04/oracle/rbdb1/users02.dbf';
If the
users tablespace 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.
Renaming and Relocating Datafiles for 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.
For example, the
following statement renames the datafiles/u02/oracle/rbdb1/sort01.dbf and
/u02/oracle/rbdb1/user3.dbf to /u02/oracle/rbdb1/temp01.dbf and
/u02/oracle/rbdb1/users03.dbf, respectively:
ALTER DATABASE
RENAME FILE '/u02/oracle/rbdb1/sort01.dbf',
'/u02/oracle/rbdb1/user3.dbf'
TO '/u02/oracle/rbdb1/temp01.dbf',
'/u02/oracle/rbdb1/users03.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 datafile 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.
Dropping Datafiles
There is no SQL statement that specifically drops
a datafile. The only means of dropping a datafile is to drop the tablespace
that contains the datafile. For example, if you want to remove a datafile from
a tablespace, you could do the following:
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP
INCLUDING DATAFILES;
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 parameter can be
changed dynamically, or set in the initialization parameter file. The default
value of DB_BLOCK_CHECKSUM
is FALSE. Regardless of the
setting of this parameter, checksums are always used to verify data blocks in
the system tablespace.
When you enable block checking, Oracle computes a
checksum for each block written to disk. Checksums are computed for all data
blocks, including temporary blocks.
The DBWn 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 corruption in the block. If a corruption is detected,
Oracle returns message
ORA-01578
and writes information about the corruption to a trace file.|
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. |
|
Oracle9i
Database Reference for information about checksums and the
DB_BLOCK_CHECKSUM initialization
parameter |
Mapping Files to Physical Devices
In an environment where datafiles are simply file
system files or are created directly on a raw device, it is relatively straight
forward to see the association between a tablespace and the underlying device.
Oracle provides views, such as
DBA_TABLESPACES,
DBA_DATA_FILES, and V$DATAFILE, that provide a mapping of
files onto devices. These mappings, along with device statistics can be used to
evaluate I/O performance.
However, with the introduction of host based
Logical Volume Managers (LVM), and sophisticated storage subsystems that
provide RAID (Redundant Array of Independent Disks) features, it is not easy to
determine file to device mapping. This poses a problem because it becomes
difficult to determine your "hottest" files when they are hidden
behind a "black box". This section presents Oracle's approach to
resolving this problem.
- Overview of Oracle's File Mapping Interface
- How Oracle's File Mapping Interface Works
- Using Oracle's File Mapping Interface
- File Mapping Examples
Overview of Oracle's File Mapping Interface
To acquire an understanding of I/O performance,
one must have detailed knowledge of the storage hierarchy in which files
reside. Oracle provides a mechanism to show a complete mapping of a file to
intermediate layers of logical volumes to actual physical devices. This is
accomplished though a set of dynamic performance views (
V$ views). Using these views, you can
locate the exact disk on which any block of a file resides.
To build these views, storage vendors must
provide mapping libraries that are responsible for mapping their particular I/O
stack elements. Oracle communicates with these libraries through an external
non-Oracle process that is spawned by an Oracle background process called FMON.
FMON is responsible for managing the mapping information. Oracle provides a
PL/SQL package,
DBMS_STORAGE_MAP,
that you use to invoke mapping operations that populate the mapping views.How Oracle's File Mapping Interface Works
This section describes the components of Oracle's
file mapping interface and how the interface works. It contains the following
topics:
Components of File Mapping
Figure 12-1 Components of File Mapping
The following sections briefly describes these components and how
they work together to populate the mapping views:
FMON
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:
These structures are
explained in "Mapping
Structures".
- Refreshing mapping information when a change occurs because of:
- Changes to Oracle datafiles (size)
- Addition or deletion of datafiles
- 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.
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.
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.
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.
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.
A subelement mapping
structure describes the link between an element and the next elements in the
I/O mapping stack. This structure contains the subelement number, size, the
element name where the subelement exists, and the element offset.
Example of Mapping Structures
Consider an Oracle database which is composed of
two data files X and Y. Both files X and Y reside on a file system mounted on
volume A. File X is composed of two extents while file Y is composed of only
one extent. Element A is striped over two elements B and C. Element B is a
partition of element D and element C is mirrored over elements E and F. Note
that elements D, E, and F are physical disks. Subelement B0 connects the parent
element A to element B, subelement C1 connects A to C, ....
All of the mapping structures are illustrated in Figure 12-2.
Figure 12-2 Illustration of Mapping Structures
Note that the mapping structures represented are sufficient to
describe the entire mapping information for the Oracle instance and
consequently to map every logical block within the file into a (element name,
element offset) tuple (or more in case of mirroring) at each level within the
I/O stack.
Configuration ID
The configuration ID captures the version
information associated with elements or files. The vendor library provides the
configuration ID and updates it whenever a change occurs. Without a
configuration ID, there is no way for Oracle to tell whether the mapping has
changed.
The configuration IDs
are not persistent across instance shutdown. Oracle is only capable of
refreshing the mapping information while the instance is up.
Using Oracle's File Mapping Interface
This section discusses how to use Oracle's file
mapping interface. It contains the following topics:
- Enabling File Mapping
- Using the DBMS_STORAGE_MAP Package
- Obtaining Information from the File Mapping Views
Enabling File Mapping
2.
The
filemap.ora
file is the configuration file that describes all of the available mapping
libraries. FMON requires that a filemap.ora
file exists and that it points to a valid path to mapping libraries. Otherwise,
it will not start successfully.
Note that the ordering
of the libraries in this file is extremely important. The libraries are queried
based on their order in the configuration file.
The file mapping
service can be even started even if no mapping libraries are available. The
filemap.ora file still needs to be
present even though it is empty. In this case, the mapping service is
constrained in the sense that new mapping information cannot be discovered.
Only restore and drop operations are allowed in such a configuration.3. FILE_MAPPING=TRUE
4.
The instance does not
have to be shut down to set this parameter. It can be set using an
ALTER SYSTEM statement.
·
In a cold startup scenario, the Oracle database
is just started and no mapping operation has been invoked yet. You execute the
DBMS_STORAGE_MAP.MAP_ALL procedure to build the
mapping information for the entire I/O subsystem associated with the Oracle
database.
·
In a warm start scenario where the mapping
information is already built, you have the option to invoke the
DBMS_STORAGE_MAP.MAP_SAVE procedure to save the
mapping information in the data dictionary. (Note that this procedure is
invoked in DBMS_STORAGE_MAP.MAP_ALL()
by default.) This forces all of the mapping information in the SGA to be
flushed to disk.
Once you restart the
database, use
DBMS_STORAGE_MAP.RESTORE()
to restore the mapping information into the SGA. If needed, DBMS_STORAGE_MAP.MAP_ALL() can be called
to refresh the mapping information.Using the DBMS_STORAGE_MAP Package
The
DBMS_STORAGE_MAP
package enables you control the mapping operations. The various procedures
available to you are described in the following table.
|
Obtaining Information from the File Mapping Views
Mapping information generated by
DBMS_STORAGE_MAP package is captured in
dynamic performance views. Brief descriptions of these views are presented
here.|
Oracle9i
Database Reference contains complete descriptions of the dynamic
performance views
|
However, the information generated by the
DBMS_STORAGE_MAP.MAP_OBJECT procedure is
captured in a global temporary table named MAP_OBJECT.
This table displays the hierarchical arrangement of storage containers for
objects. Each row in the table represents a level in the hierarchy. A
description of the MAP_OBJECT
table follows.File Mapping Examples
The following examples illustrates some of the
powerful capabilities of Oracle's file mapping feature. This includes :
- The ability to map all Oracle files that span a particular device
- The ability to map a particular file into its corresponding devices
- The ability to map a particular Oracle object, including its block distribution at all levels within the I/O stack
These files are created on a Solaris UFS file
system mounted on a VERITAS VxVM host based striped volume,
/dev/vx/dsk/ipfdg/ipf-vol1, that
consists of the following host devices as externalized from an EMC Symmetrix
array:Example 1: Map All Oracle Files that Span a Device
SELECT UNIQUE me.ELEM_NAME, mf.FILE_NAME
FROM V$MAP_FILE_IO_STACK fs, V$MAP_FILE mf, V$MAP_ELEMENT me
WHERE mf.FILE_MAP_IDX = fs.FILE_MAP_IDX
AND me.ELEM_IDX = fs.ELEM_IDX
AND me.ELEM_NAME = /dev/vx/rdmp/c2t1d1s2;
ELEM_NAME FILE_NAME
------------------------ --------------------------------
/dev/vx/rdmp/c2t1d1s2 /oracle/dbs/t_db1.f
/dev/vx/rdmp/c2t1d1s2 /oracle/dbs/t_db2.f
Example 2: Map a File into Its Corresponding Devices
WITH fv AS
(SELECT FILE_MAP_IDX, FILE_NAME FROM V$MAP_FILE
WHERE FILE_NAME = /oracle/dbs/t_db1.f)
SELECT fv.FILE_NAME, LPAD(' ', 4 * (LEVEL - 1)) || el.ELEM_NAME ELEM_NAME
FROM V$MAP_SUBELEMENT sb, V$MAP_ELEMENT el, fv,
(SELECT UNIQUE ELEM_IDX FROM V$MAP_FILE_IO_STACK io, fv
WHERE io.FILE_MAP_IDX = fv.FILE_MAP_IDX) fs
WHERE el.ELEM_IDX = sb.CHILD_IDX
AND fs.ELEM_IDX = el.ELEM_IDX
START WITH sb.PARENT_IDX IN
(SELECT DISTINCT ELEM_IDX
FROM V$MAP_FILE_EXTENT fe, fv
WHERE fv.FILE_MAP_IDX = fe.FILE_MAP_IDX)
CONNECT BY PRIOR sb.CHILD_IDX = sb.PARENT_IDX;
FILE_NAME ELEM_NAME
----------------------- -------------------------------------------------
/oracle/dbs/t_db1.f _sym_plex_/dev/vx/rdsk/ipfdg/ipf-vol1_-1_-1
/oracle/dbs/t_db1.f _sym_subdisk_/dev/vx/rdsk/ipfdg/ipf-vol1_0_0_0
/oracle/dbs/t_db1.f /dev/vx/rdmp/c2t1d0s2
/oracle/dbs/t_db1.f _sym_symdev_000183600407_00C
/oracle/dbs/t_db1.f _sym_hyper_000183600407_00C_0
/oracle/dbs/t_db1.f _sym_hyper_000183600407_00C_1
/oracle/dbs/t_db1.f _sym_subdisk_/dev/vx/rdsk/ipfdg/ipf-vol1_0_1_0
/oracle/dbs/t_db1.f /dev/vx/rdmp/c2t1d1s2
/oracle/dbs/t_db1.f _sym_symdev_000183600407_00D
/oracle/dbs/t_db1.f _sym_hyper_000183600407_00D_0
/oracle/dbs/t_db1.f _sym_hyper_000183600407_00D_1
Example 3: Map an Oracle Object
This example displays the block distribution at
all levels within the I/O stack for the
scott.bonus
table.EXECUTE DBMS_STORAGE_MAP.MAP_OBJECT('BONUS','SCOTT','TABLE');
SELECT io.OBJECT_NAME o_name, io.OBJECT_OWNER o_owner, io.OBJECT_TYPE o_type,
mf.FILE_NAME, me.ELEM_NAME, io.DEPTH,
(SUM(io.CU_SIZE * (io.NUM_CU - DECODE(io.PARITY_PERIOD, 0, 0,
TRUNC(io.NUM_CU / io.PARITY_PERIOD)))) / 2) o_size
FROM MAP_OBJECT io, V$MAP_ELEMENT me, V$MAP_FILE mf
WHERE io.OBJECT_NAME = 'BONUS'
AND io.OBJECT_OWNER = 'SCOTT'
AND io.OBJECT_TYPE = 'TABLE'
AND me.ELEM_IDX = io.ELEM_IDX
AND mf.FILE_MAP_IDX = io.FILE_MAP_IDX
GROUP BY io.ELEM_IDX, io.FILE_MAP_IDX, me.ELEM_NAME, mf.FILE_NAME, io.DEPTH,
io.OBJECT_NAME, io.OBJECT_OWNER, io.OBJECT_TYPE
ORDER BY io.DEPTH;
O_NAME O_OWNER O_TYPE FILE_NAME ELEM_NAME DEPTH O_SIZE
------ ------- ------ ------------------- ----------------------------- ------ ------
BONUS SCOTT TABLE /oracle/dbs/t_db1.f /dev/vx/dsk/ipfdg/ipf-vol1 0 20
BONUS SCOTT TABLE /oracle/dbs/t_db1.f _sym_plex_/dev/vx/rdsk/ipf 1 20
pdg/if-vol1_-1_-1
BONUS SCOTT TABLE /oracle/dbs/t_db1.f _sym_subdisk_/dev/vx/rdsk/ 2 12
ipfdg/ipf-vol1_0_1_0
BONUS SCOTT TABLE /oracle/dbs/t_db1.f _sym_subdisk_/dev/vx/rdsk/ipf 2 8
dg/ipf-vol1_0_2_0
BONUS SCOTT TABLE /oracle/dbs/t_db1.f /dev/vx/rdmp/c2t1d1s2 3 12
BONUS SCOTT TABLE /oracle/dbs/t_db1.f /dev/vx/rdmp/c2t1d2s2 3 8
BONUS SCOTT TABLE /oracle/dbs/t_db1.f _sym_symdev_000183600407_00D 4 12
BONUS SCOTT TABLE /oracle/dbs/t_db1.f _sym_symdev_000183600407_00E 4 8
BONUS SCOTT TABLE /oracle/dbs/t_db1.f _sym_hyper_000183600407_00D_0 5 12
BONUS SCOTT TABLE /oracle/dbs/t_db1.f _sym_hyper_000183600407_00D_1 5 12
BONUS SCOTT TABLE /oracle/dbs/t_db1.f _sym_hyper_000183600407_00E_0 6 8
BONUS SCOTT TABLE /oracle/dbs/t_db1.f _sym_hyper_000183600407_00E_1 6 8
Viewing Datafile Information
USER_EXTENTS |
|
USER_FREE_SPACE |
|
SELECT NAME,
FILE#,
STATUS,
CHECKPOINT_CHANGE# "CHECKPOINT"
FROM V$DATAFILE;
NAME FILE# STATUS CHECKPOINT
-------------------------------- ----- ------- ----------
/u01/oracle/rbdb1/system01.dbf 1 SYSTEM 3839
/u02/oracle/rbdb1/temp01.dbf 2 ONLINE 3782
/u02/oracle/rbdb1/users03.dbf 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 (system change number) written for a datafile's most recent checkpoint.
No comments:
Post a Comment