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 Database 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 VolumeManagers (LVM), and sophisticated storage subsystems that provide RAID (Redundant Array of Inexpensive 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 the Oracle Database approach to resolving this problem.
The following topics are contained in this section:
Overview of Oracle Database 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 Database 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. The database communicates with these libraries through an external non-Oracle Database process that is spawned by a 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 the Oracle Database File Mapping Interface Works
This section describes the components of the Oracle Database file mapping interface and how the interface works. It contains the following topics:
Components of File Mapping
The following figure shows the components of the file mapping mechanism.
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 the database whenever the FILE_MAPPING initialization parameter is set to TRUE. FMON is responsible for:
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 Database 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.
Oracle Database 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.
The mapping structures and the Oracle Database 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:
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.
All of these mapping structures are illustrated in the following example.
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.
The two extents of File X and the one extent of File Y both map to Element A. Element A is striped to Elements B and C. Element A maps to Elements B and C by way of Subelements B0 and C1, respectively.
Element B is a partition of Element D (a physical disk), and is mapped to Element D by way of subelement D0.
Element C is mirrored over Elements E and F (both physical disks), and is mirrored to those physical disks by way of Subelements E0 and F1, respectively.
Illustration of Mapping Structures
Note that the mapping structures represented are sufficient to describe the entire mapping information for the Oracle Database 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.
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 the database to tell whether the mapping has changed.
There are two kinds of configuration IDs:
These configuration IDs are persistent across instance shutdown
The configuration IDs are not persistent across instance shutdown. The database is only capable of refreshing the mapping information while the instance is up.
Using the Oracle Database File Mapping Interface
This section discusses how to use the Oracle Database file mapping interface. It contains the following topics:
Enabling File Mapping
The following steps enable the file mapping feature:
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.
The following row needs to be included in filemap.ora for each library :lib=vendor_name:mapping_library_path where:
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 operationsare allowed in such a configuration.
The instance does not have to be shut down to set this parameter. It can be set using an ALTER SYSTEM statement.
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 to 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 dynami performance views. Brief descriptions of these views are presented here.
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 the Oracle Database file mapping feature. This includes :
Consider an Oracle Database instance which is composed of two datafiles:
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:
Note that the following examples require the execution of a MAP_ALL() operation.
Example 1: Map All Database Files that Span a Device
The following query returns all Oracle Database files associated with the /dev/vx/rdmp/c2t1d1s2 host device:
Example 2: Map a File into Its Corresponding Devices
The following query displays a topological graph of the /oracle/dbs/t_db1.f datafile:
Example 3: Map a Database Object
This example displays the block distribution at all levels within the I/O stack for the scott.bonus table.
A MAP_OBJECT() operation must first be executed as follows:
The following is the result of the query. Note that the o_size column is expressed in KB. Table
Oracle 10g Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Informatica Interview Questions|
|PL/SQL Interview Questions||Oracle 11g Interview Questions|
|SQL Interview Questions||Oracle apps Interview Questions|
|Sybase Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle Access Manager Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps DBA Interview Questions|
Oracle 10g Related Practice Tests
|Oracle 10g Practice Tests||Oracle 9i Practice Tests|
|Oracle 8i Practice Tests||Informatica Practice Tests|
|PL/SQL Practice Tests||Oracle 11g Practice Tests|
|SQL Practice Tests||Oracle apps Practice Tests|
|Sybase Practice Tests||Oracle Apps ERP Practice Tests|
|Oracle 7.3 Practice Tests|
Oracle 10g Tutorial
Overview Of Administering An Oracle Database
Creating An Oracle Database
Starting Up And Shutting Down
Managing Oracle Database Processes
Managing Control Files
Managing The Redo Log
Managing Archived Redo Logs
Managing Datafiles And Tempfiles
Managing The Undo Tablespace
Using Oracle-managed Files
Using Automatic Storage Management
Managing Space For Schema Objects
Managing Partitioned Tables And Indexes
Managing Hash Clusters
Managing Views, Sequences, And Synonyms
General Management Of Schema Objects
Detecting And Repairing Data Block Corruption
Managing Users And Securing The Database
Managing Automatic System Tasks Using The Maintenance Window
Using The Database Resource Manager
Moving From Dbms_job To Dbms_scheduler
Overview Of Scheduler Concepts
Using The Scheduler
Administering The Scheduler
Distributed Database Concepts
Managing A Distributed Database
Developing Applications For A Distributed Database System
Distributed Transactions Concepts
Managing Distributed Transactions
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.