Veritas InfoScale™ 8.0.2 Storage and Availability Management for Oracle Databases - AIX, Linux, Solaris

Last Published:
Product(s): InfoScale & Storage Foundation (8.0.2)
Platform: AIX,Linux,Solaris
  1. Section I. Storage Foundation High Availability (SFHA) management solutions for Oracle databases
    1. Overview of Storage Foundation for Databases
      1.  
        Introducing Storage Foundation High Availability (SFHA) Solutions for Oracle
      2. About Veritas File System
        1.  
          About the Veritas File System intent log
        2.  
          About extents
        3.  
          About file system disk layouts
      3.  
        About Veritas Volume Manager
      4.  
        About Dynamic Multi-Pathing (DMP)
      5.  
        About Cluster Server
      6.  
        About Cluster Server agents
      7.  
        About Veritas InfoScale Operations Manager
      8.  
        Feature support for Oracle across Veritas InfoScale 8.0.2 products
      9.  
        Use cases for Veritas InfoScale products
  2. Section II. Deploying Oracle with Veritas InfoScale products
    1. Deployment options for Oracle in a Storage Foundation environment
      1.  
        Oracle deployment options in a Storage Foundation environment
      2.  
        Oracle single instance in a Storage Foundation environment
      3.  
        Single instance Oracle with off-host in a Storage Foundation environment
      4.  
        Single instance Oracle in a highly available cluster with Storage Foundation High Availability
      5.  
        Single instance Oracle in a parallel cluster with SF Cluster File System HA environment
      6.  
        About Oracle RAC in a Storage Foundation for Oracle RAC environment
      7.  
        About Oracle in a replicated Storage Foundation environment
      8.  
        Deploying Oracle and Storage Foundation in a virtualization environment
      9.  
        Deploying Oracle with Storage Foundation SmartMove and Thin Provisioning
    2. Deploying Oracle with Storage Foundation
      1.  
        Tasks for deploying Oracle databases
      2.  
        Planning your Oracle storage
      3.  
        About selecting a volume layout for deploying Oracle
      4. Setting up disk group for deploying Oracle
        1.  
          Disk group configuration guidelines for deploying Oracle
      5. Creating volumes for deploying Oracle
        1.  
          Volume configuration guidelines for deploying Oracle
      6. Creating VxFS file system for deploying Oracle
        1.  
          File system creation guidelines for deploying Oracle
      7.  
        Mounting the file system for deploying Oracle
      8.  
        Installing Oracle and creating database
    3. Deploying Oracle in an off-host configuration with Storage Foundation
      1.  
        Requirements for an off-host database configuration
    4. Deploying Oracle with High Availability
      1.  
        Tasks for deploying Oracle in an HA configuration
      2.  
        Configuring VCS to make the database highly available
    5. Deploying Oracle with Volume Replicator (VVR) for disaster recovery
      1.  
        About deploying Oracle with VVR for disaster recovery
      2.  
        Tasks for deploying Oracle with VVR for disaster recovery
      3.  
        Setting up the primary node for deploying Oracle
      4.  
        Setting up the secondary node for deploying Oracle
      5.  
        Configuring VVR between primary and secondary nodes for deploying Oracle
  3. Section III. Configuring Storage Foundation for Database (SFDB) tools
    1. Configuring and managing the Storage Foundation for Databases repository database
      1.  
        About the Storage Foundation for Databases (SFDB) repository
      2.  
        Requirements for Storage Foundation for Databases (SFDB) tools
      3.  
        Storage Foundation for Databases (SFDB) tools availability
      4. Configuring the Storage Foundation for Databases (SFDB) tools repository
        1.  
          Locations for the SFDB repository
        2.  
          Setting up the SFDB repository
      5.  
        Backing up and restoring the Storage Foundation for Databases (SFDB) repository
      6.  
        Updating the Storage Foundation for Databases (SFDB) repository after adding a node
      7.  
        Updating the Storage Foundation for Databases (SFDB) repository after removing a node
      8.  
        Removing the Storage Foundation for Databases (SFDB) repository
    2. Configuring authentication for Storage Foundation for Databases (SFDB) tools
      1.  
        Configuring vxdbd for SFDB tools authentication
      2.  
        Adding nodes to a cluster that is using authentication for SFDB tools
      3.  
        Authorizing users to run SFDB commands
  4. Section IV. Improving Oracle database performance
    1. About database accelerators
      1.  
        About Veritas InfoScale product components database accelerators
    2. Improving database performance with Veritas Extension for Oracle Disk Manager
      1. About Oracle Disk Manager in the Veritas InfoScale products environment
        1. How Oracle Disk Manager improves database performance in the SFHA environment
          1.  
            About SmartSync in SFHA environment
        2.  
          How Oracle Disk Manager works with Oracle Managed Files
      2.  
        Setting up Veritas Extension for Oracle Disk Manager in SFHA environment
      3.  
        Configuring the Veritas Extension for Oracle Disk Manager in SFHA environment
      4.  
        How to prepare existing database storage for Oracle Disk Manager in SFHA environment
      5.  
        Verifying that Oracle Disk Manager is configured in SFHA environment
      6.  
        Disabling the Oracle Disk Manager feature in SFHA environment
    3. Improving database performance with Veritas Cached Oracle Disk Manager
      1. About Cached ODM in SFHA environment
        1.  
          Considerations for using Cached ODM in SFHA environment
        2.  
          Cached ODM supported configurations in SFHA environment
        3.  
          About Cached ODM Advisor in SFHA environment
      2. Configuring Cached ODM in SFHA environment
        1.  
          Enabling Cached ODM for file systems in SFHA environment
        2.  
          Tuning Cached ODM settings for individual files in SFHA environment
        3.  
          Tuning Cached ODM settings via the cachemap in SFHA environment
        4.  
          Making the caching settings persistent across mounts in SFHA environment
      3. Administering Cached ODM settings with Cached ODM Advisor in SFHA environment
        1. Generating reports of candidate datafiles by using Cached ODM Advisor in SFHA environment
          1.  
            Generating a basic report of the current read activity by using Cached ODM Advisor in SFHA environment
          2. Generating summary reports of historical activity by using Cached ODM Advisor in SFHA environment
            1.  
              Using the Cached ODM Advisor blacklist to ignore files in SFHA environment
            2.  
              Average metric values during a time interval by using Cached ODM Advisor in SFHA environment
            3.  
              Average metric values during an interval specified as a range of AWR snapshots by using Cached ODM Advisor in SFHA environment
          3.  
            Generating historical activity reports on individual datafiles by using Cached ODM Advisor in SFHA environment
        2.  
          Enabling and disabling Cached ODM on data files by using Cached ODM Advisor in SFHA environment
        3.  
          Display the Cached ODM states of the database files by using Cached ODM Advisor in SFHA environment
        4.  
          Show Cached ODM statistics by using Cached ODM Advisor in SFHA environment
        5.  
          Displaying ODM I/O statistics by using Cached ODM Advisor in SFHA environment
    4. Improving database performance with Quick I/O
      1. About Quick I/O
        1.  
          How Quick I/O improves database performance
      2.  
        Creating Oracle database files as Quick I/O files using qiomkfile
      3.  
        Preallocating space for Quick I/O files using the setext command
      4.  
        Accessing regular VxFS files as Quick I/O files
      5.  
        Converting Oracle files to Quick I/O files
      6.  
        About sparse files
      7.  
        Handling Oracle temporary tablespaces and Quick I/O
      8.  
        Displaying Quick I/O status and file attributes
      9.  
        Extending a Quick I/O file
      10.  
        Using Oracle's AUTOEXTEND with Quick I/O files
      11.  
        Recreating Quick I/O files after restoring a database
      12.  
        Disabling Quick I/O
      13.  
        Creating Quick I/O files in Solaris local zone
    5. Improving database performance with Cached Quick I/O
      1.  
        About Cached Quick I/O
      2.  
        Tasks for setting up Cached Quick I/O
      3. Enabling Cached Quick I/O on a file system
        1.  
          Enabling and disabling the qio_cache_enable flag
        2.  
          Making Cached Quick I/O settings persistent across reboots and mounts
        3.  
          Using vxtunefs to obtain tuning information
      4. Determining candidates for Cached Quick I/O
        1.  
          Collecting I/O statistics
        2.  
          About I/O statistics for Oracle
        3.  
          Effects of read-aheads on I/O statistics
        4.  
          Other tools for analysis
      5. Enabling and disabling Cached Quick I/O for individual files
        1.  
          Setting cache advisories for individual files
        2.  
          Making individual file settings for Cached Quick I/O persistent
        3.  
          Determining individual file settings for Cached Quick I/O using qioadmin
  5. Section V. Using point-in-time copies
    1. Understanding point-in-time copy methods
      1.  
        About point-in-time copies
      2.  
        When to use point-in-time copies
      3.  
        About Storage Foundation point-in-time copy technologies
      4.  
        Point-in-time copy solutions supported by SFDB tools
      5.  
        About snapshot modes supported by Storage Foundation for Databases (SFDB) tools
      6. Volume-level snapshots
        1.  
          Persistent FastResync of volume snapshots
        2.  
          Data integrity in volume snapshots
        3.  
          Third-mirror break-off snapshots
        4. Space-optimized instant volume snapshots
          1.  
            How space-optimized instant snapshots work
      7. About Reverse Resynchronization in volume-level snapshots (FlashSnap)
        1.  
          Types of Reverse Resync operations
        2.  
          Limitations of Reverse Resync
        3. Pre-requisites for performing Reverse Resync operation
          1.  
            Reverse Resync in HA Configuration
          2.  
            Recommendations for performing reverse resync
        4.  
          Changes introduced in the implementation of Reverse Resync from 5.X
        5.  
          Configuration parameters for Reverse Resync operation
      8. Storage Checkpoints
        1.  
          How Storage Checkpoints differ from snapshots
        2. How a Storage Checkpoint works
          1.  
            Copy-on-write
          2. Storage Checkpoint visibility
            1.  
              Storage Checkpoints and 64-bit inode numbers
        3.  
          About Database Rollbacks using Storage Checkpoints
        4.  
          Storage Checkpoints and Rollback process
        5. Types of Storage Checkpoints
          1.  
            Data Storage Checkpoints
          2.  
            Removable Storage Checkpoints
          3.  
            Non-mountable Storage Checkpoints
        6.  
          Storage Checkpoint space management considerations
      9. About FileSnaps
        1.  
          Properties of FileSnaps
        2.  
          Concurrent I/O to FileSnaps
        3.  
          Copy-on-write and FileSnaps
        4.  
          Reading from FileSnaps
        5.  
          Block map fragmentation and FileSnaps
        6.  
          Backup and FileSnaps
    2. Considerations for Oracle point-in-time copies
      1.  
        Considerations for database layouts
      2.  
        Supported Oracle configurations
      3.  
        Considerations for multitenant database support
      4.  
        Behavior of clone databases
    3. Administering third-mirror break-off snapshots
      1. Database FlashSnap for cloning
        1.  
          Database FlashSnap advantages
      2. Preparing hosts and storage for Database FlashSnap
        1. Setting up hosts
          1.  
            Same-node configuration
          2.  
            Database FlashSnap off-host configuration
        2.  
          Creating a snapshot mirror of a volume or volume set used by the database
      3.  
        Creating a clone of a database by using Database FlashSnap
      4. Resynchronizing mirror volumes with primary volumes
        1.  
          To reverse resynchronize the snapshot volume
        2.  
          Example outputs for reverse resync parameters
      5.  
        Recovering the clone database manually
      6.  
        Cloning a database on the secondary host
    4. Administering space-optimized snapshots
      1.  
        Planning to create an instant space-optimized snapshot
      2.  
        Preparing a legacy volume for the creation of an instant snapshot
      3.  
        Creating a shared cache object
      4. Creating a clone of an Oracle database by using space-optimized snapshots
        1.  
          Space-optimized snapshots in HA environment
      5.  
        Creating multiple clones using FlashSnap snapshots
      6.  
        Recovering the clone database manually
    5. Administering Storage Checkpoints
      1.  
        About Storage Checkpoints
      2. Database Storage Checkpoints for recovery
        1.  
          Advantages and limitations of Database Storage Checkpoints
      3.  
        Creating a Database Storage Checkpoint
      4.  
        Deleting a Database Storage Checkpoint
      5.  
        Mounting a Database Storage Checkpoint
      6.  
        Unmounting a Database Storage Checkpoint
      7.  
        Creating a database clone using a Database Storage Checkpoint
      8.  
        Restoring database from a Database Storage Checkpoint
      9.  
        Gathering data for offline-mode Database Storage Checkpoints
    6. Administering FileSnap snapshots
      1.  
        Preparing to use FileSnap
      2.  
        Creating a clone of an Oracle database by using FileSnap
      3.  
        Destroying a database clone created by using FileSnap
      4.  
        Destroying a FileSnap snapshot
      5.  
        Restoring database files from a FileSnap snapshot
      6.  
        Viewing the list of snapshots and clones created by using FileSnap
    7. Backing up and restoring with Netbackup in an SFHA environment
      1. About Veritas NetBackup
        1.  
          How NetBackup block-level incremental backup works for Oracle database files
      2.  
        About using Veritas NetBackup for backup and restore for Oracle
      3.  
        About using Veritas NetBackup to backup and restore Oracle Disk Manager files
      4.  
        Using NetBackup to backup and restore Quick I/O files for Oracle
      5. Using NetBackup in an SFHA Solutions product environment
        1.  
          Clustering a NetBackup Master Server
        2.  
          Backing up and recovering a VxVM volume using NetBackup
        3.  
          Recovering a VxVM volume using NetBackup
        4.  
          Backing up an Oracle database using NetBackup
        5.  
          Recovering an Oracle database using NetBackup
  6. Section VI. Optimizing storage costs for Oracle
    1. Understanding storage tiering with SmartTier
      1. About SmartTier
        1.  
          About VxFS multi-volume file systems
        2.  
          About VxVM volume sets
        3.  
          About volume tags
        4.  
          SmartTier file management
        5.  
          SmartTier sub-file object management
      2. How SmartTier for Oracle works
        1.  
          How partitions change the way Oracle stores database objects
        2.  
          Database extents to file location
        3.  
          Statistics in Oracle
        4.  
          Optimizing storage for Oracle database objects
        5.  
          About Automatic Workload Repository statistics
      3.  
        SmartTier in a High Availability (HA) environment
    2. Configuring and administering SmartTier
      1. Configuring SmartTier for Oracle
        1.  
          SmartTier for Oracle command requirements
        2.  
          Defining database parameters
        3.  
          Configuring storage classes
        4.  
          Converting a Veritas File System (VxFS) to a VxFS multi-volume file system
        5.  
          Classifying volumes into a storage class
        6.  
          Displaying free space on your storage class
        7.  
          Adding new volumes to a storage class
        8.  
          Removing volumes from a storage class
      2. Optimizing database storage using SmartTier for Oracle
        1.  
          Relocating files manually using SmartTier for Oracle
        2.  
          Relocating tablespaces manually using SmartTier for Oracle
        3.  
          Relocating table partitions manually using SmartTier for Oracle
        4.  
          Setting policies to automate storage tiering using SmartTier for Oracle
      3.  
        Running reports using SmartTier for Oracle
      4. Extent balancing in a database environment using SmartTier for Oracle
        1.  
          Extent balancing file system to imrove database performance
        2.  
          Creating an extent balanced file system using SmartTier for Oracle
      5.  
        Running sub-file database object reports using SmartTier for Oracle
      6.  
        Optimizing sub-file database object placement using SmartTier for Oracle
    3. SmartTier use cases for Oracle
      1.  
        SmartTier use cases for Oracle
      2. SmartTier for Oracle file-based use cases
        1.  
          Migrating partitioned data and tablespaces
        2.  
          Scheduling the relocation of archive and Flashback logs
      3. SmartTier for Oracle sub-file use cases
        1.  
          Using AWR statistics to identify sub-file objects for potential relocation
        2.  
          Relocating a hot database segment to a higher performance tier
        3.  
          Relocating old table data to a lower cost or secondary tier
    4. Compressing files and databases to optimize storage costs
      1. Compressed files and databases
        1.  
          Supported database versions and environment
        2.  
          Compressing archive logs
        3.  
          Compressing read-only tablespaces
        4.  
          Compressing infrequently accessed table partitions
        5.  
          Compressing infrequently accessed datafiles
        6.  
          Best practices for compressing files in an Oracle database
    5. Using the Compression Advisor tool
      1.  
        About the Compression Advisor tool
      2.  
        Compressing Oracle archive logs using Compression Advisor
      3.  
        Displaying compression candidate data files using Compression Advisor
      4.  
        Compressing Oracle data files using Compression Advisor
      5.  
        Displaying compressed data files using Compression Advisor
      6.  
        Uncompressing Oracle data files using Compression Advisor
      7.  
        Compression Advisor command reference
  7. Section VII. Managing Oracle disaster recovery
    1. Using Storage Foundation for Databases (SFDB) tools in a replicated environment
      1.  
        About deploying SF Databases tools with Volume Replicator (VVR)
      2.  
        Using database accelerators for the Oracle database in a Volume Replicator (VVR) environment
      3.  
        About deploying Storage Foundation for Databases (SFDB) tools with Oracle Data Guard
    2. Using volume snapshots in a replicated environment
      1.  
        Considerations for using volume snapshots with VVR
      2.  
        Creating space-optimized snapshots in a VVR setup
      3.  
        Creating third-mirror break-off snapshots in a VVR setup
      4.  
        Considerations for using vxsfadm after replication role transfer
  8. Section VIII. Storage Foundation for Databases administrative reference
    1. Storage Foundation for Databases command reference
      1.  
        vxsfadm command reference
      2. FlashSnap reference
        1.  
          FlashSnap configuration parameters
        2.  
          FlashSnap supported operations
      3. Space-optimized snapshots reference
        1.  
          Space-optimized snapshots configuration parameters
        2.  
          Space-optimized snapshots supported operations
      4. FileSnap reference
        1.  
          FileSnap configuration parameters
        2.  
          FileSnap supported operations
      5. Database Storage Checkpoints reference
        1.  
          Database Storage Checkpoints configuration parameters
        2.  
          Database Storage Checkpoints supported operations
    2. Tuning for Storage Foundation for Databases
      1.  
        Additional documentation
      2. About tuning Veritas Volume Manager (VxVM)
        1.  
          About obtaining volume I/O statistics
      3. About tuning VxFS
        1. How monitoring free space works
          1.  
            About monitoring fragmentation
        2.  
          How tuning VxFS I/O parameters works
        3.  
          About tunable VxFS I/O parameters
        4.  
          About obtaining file I/O statistics using the Quick I/O interface
        5.  
          About I/O statistics data
        6.  
          Obtaining file I/O statistics using Veritas extension for Oracle Disk Manager
        7.  
          About I/O statistics
      4. About tuning Oracle databases
        1.  
          Sequential table scans
        2.  
          Sequential table scans
        3.  
          Asynchronous I/O
        4.  
          Tuning buffer cache
        5.  
          Tuning buffer cache
        6.  
          Setting Oracle block reads during sequential scans
        7.  
          Setting slave parameters
        8.  
          Configuring memory allocation
      5.  
        About tuning AIX Virtual Memory Manager
      6. About tuning Solaris for Oracle
        1.  
          maxuprc
        2.  
          shmmax
        3.  
          shmmin
        4.  
          shmmni
        5.  
          shmseg
        6.  
          semmap
        7.  
          semmni
        8.  
          semmns
        9.  
          semmnu
        10.  
          semmsl
    3. Troubleshooting SFDB tools
      1. About troubleshooting Storage Foundation for Databases (SFDB) tools
        1.  
          Running scripts for engineering support analysis for SFDB tools
        2.  
          Storage Foundation for Databases (SFDB) tools log files
      2. About the vxdbd daemon
        1.  
          Starting and stopping vxdbd
        2.  
          Configuring listening port for the vxdbd daemon
        3.  
          Limiting vxdbd resource usage
        4.  
          Configuring encryption ciphers for vxdbd
      3.  
        Troubleshooting vxdbd
      4. Resources for troubleshooting SFDB tools
        1.  
          SFDB logs
        2.  
          SFDB error messages
        3.  
          SFDB repository and repository files
      5.  
        Troubleshooting SmartTier for Oracle
      6.  
        Upgrading Storage Foundation for Databases (SFDB) tools from 5.0.x to 8.0.2 (2184482)
      7.  
        Troubleshooting Reverse Resynchronization
    4. Manual recovery of Oracle database
      1.  
        Manual recovery of a clone database
      2.  
        Manual recovery after a restore operation
    5. Storage Foundation for Databases command reference for the releases prior to 6.0
      1.  
        About SFDB commands backward compatibility
      2.  
        Storage Foundation for Databases (SFDB) tools features which are no longer supported
      3. Preparing storage for Database FlashSnap
        1.  
          Example procedure to create a snapshot mirror of a volume
        2. Upgrading existing volumes to use Veritas Volume Manager 8.0.2
          1.  
            Example procedure to upgrade existing volumes to use Veritas Volume Manager 8.0.2
        3.  
          Updating after structural changes to the database
      4. About creating database snapshots
        1.  
          Online database snapshots
        2.  
          Tasks before creating a snapshot
        3.  
          Creating a snapshot
        4. Tasks after creating a snapshot
          1.  
            Do you want to clone the database?
          2.  
            Do you want to resynchronize the snapshot volumes?
          3.  
            Will you use the clone on a secondary host?
          4.  
            Are you done with the snapshot?
      5. FlashSnap commands
        1. Creating a snapplan (dbed_vmchecksnap)
          1.  
            Example snapplans created for a snapshot image
          2.  
            Example for creating a snapplan with mapped mount option enabled
          3.  
            Creating multi-mirror snapshots
        2. Validating a snapplan (dbed_vmchecksnap)
          1.  
            Example to validate snapplan snap1 for a snapshot image
        3. Displaying, copying, and removing a snapplan (dbed_vmchecksnap)
          1.  
            Displaying a snapplan
          2.  
            Copying a snapplan
          3.  
            Removing a snapplan
        4. Creating a snapshot (dbed_vmsnap)
          1.  
            Example to create a snapshot image of the database PROD
        5. Backing up the database from snapshot volumes (dbed_vmclonedb)
          1.  
            Mounting the snapshot volumes and backing up
          2.  
            Restoring from backup
        6. Cloning a database (dbed_vmclonedb)
          1. Using Database FlashSnap to clone a database
            1.  
              Example: Mounting the file systems without bringing up the clone database
          2.  
            Shutting down the clone database and unmounting file systems
          3.  
            Restarting a Clone Database
          4.  
            Recreating Oracle tempfiles
        7.  
          Resynchronizing the snapshot to your database
        8.  
          Removing a snapshot volume
      6. Guidelines for Oracle recovery
        1.  
          Back up all control files before Storage Rollback
        2.  
          Ensure that the control files are not rolled back
        3.  
          Ensure that all archived redo logs are available
        4.  
          Media recovery procedures
      7. Database Storage Checkpoint Commands
        1.  
          Creating or updating the repository using dbed_update
        2.  
          Creating Storage Checkpoints using dbed_ckptcreate
        3.  
          Displaying Storage Checkpoints using dbed_ckptdisplay
        4.  
          Mounting Storage Checkpoints using dbed_ckptmount
        5.  
          Unmounting Storage Checkpoints using dbed_ckptumount
        6.  
          Performing Storage Rollback using dbed_ckptrollback
        7.  
          Removing Storage Checkpoints using dbed_ckptremove
        8.  
          Cloning the Oracle instance using dbed_clonedb
  9. Section IX. Reference
    1. Appendix A. VCS Oracle agents
      1. VCS agents for Oracle
        1. Oracle agent functions
          1.  
            Monitor options for the Oracle agent in traditional database and container database
          2.  
            Info entry point for Cluster Server agent for Oracle
          3.  
            Action entry point for Cluster Server agent for Oracle
        2. Resource type definition for the Oracle agent
          1.  
            Attribute definitions for the Oracle agent
        3.  
          Netlsnr agent functions
        4. Resource type definition for the Netlsnr agent
          1.  
            Attribute definition for the Netlsnr agent
    2. Appendix B. Sample configuration files for clustered deployments
      1.  
        About sample configuration files
      2.  
        Sample configuration file for Storage Foundation for High Availability
      3.  
        Sample configuration file for Storage Foundation for Clustered File System HA
      4.  
        Sample configuration file for Storage Foundation for Oracle RAC
    3. Appendix C.  Database FlashSnap status information
      1.  
        About Database FlashSnap status information
      2. Database FlashSnap Snapshot status information from the CLI
        1.  
          Snapshot status information from the CLI
        2.  
          Snapshot database status information from the CLI
    4. Appendix D. Using third party software to back up files
      1.  
        About using third party software to back up files
      2. Using third party software to back up files
        1.  
          Backing up and restoring Oracle Disk Manager files using Oracle RMAN

Statistics in Oracle

Oracle collects various statistics and stores them inside a database repository called the Automatic Workload Repository (AWR). The statistics collected include CPU, memory, file IO, and disk statistics. AWR is useful to find out usage trends, bottlenecks and improve performance. By default, the statistics snapshot is taken every 60 minutes and stored in AWR. AWR keeps the snapshot for seven days by default. Both statistics interval and retention period can be changed via init.ora. Oracle recommends a retention period of 30 days for statistics. Oracle provides tools to collect extra statistics on demand. There are various scripts and tools available to query the AWR and generate trend reports. AWR can be used to get file usage trends that will identify least-used files that can then be relocated to secondary storage.

Advantages of using file statistics from AWR:

  • AWR file stats are available by default. No extra configuration is needed.

  • AWR file stats are the statistics generated by the database only. File stats from Veritas File System include both database transaction and other IO like backup/restore.

  • In a clustered Oracle RAC environment, AWR file stats include I/O from all the nodes. There is no need collect from each node and sum it up.

  • AWR file statistics provides history and hence it is easy to get the usage trends.

AWR also provides Oracle object level statistics like segment statistics. These can be used to find out the least used table segments.

One large disadvantage of Oracle file statistics is that they do not provide statistics at less than the file level.