The Veritas File System

Transcript

® VERITAS® Database Edition for Oracle® Database Administrator’s Guide Release 2.0 Solaris October 1998 P/N 100-000690 © 1998 VERITAS® Software Corporation. All rights reserved. Trademarks VERITAS, VxVM, VxVA, VxFS, Firstwatch, and the VERITAS logo are registered trademarks of VERITAS Software Corporation in the United States and other countries. VERITAS Volume Manager, VERITAS File System, VERITAS NetBackup, VERITAS HSM, VERITAS Media Librarian, CVM, VERITAS Quick I/O, and VxSmartsync are trademarks of VERITAS Software Corporation. Other products mentioned in this document are trademarks or registered trademarks of their respective holders. Contents Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix 1. The Database Edition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 The VERITAS Database Edition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 The VERITAS File System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Quick I/O For Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Cached Quick I/O For Databases . . . . . . . . . . . . . . . . . . . . . . . . . 4 Extent-based Allocation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Fast Database Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 Online Administration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 Large File and Large File System Support . . . . . . . . . . . . . . . . . . . . . 7 Database Backup with VERITAS File System Snapshots . . . . . . . . . 7 Storage Checkpoint and Rollback . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 The VERITAS Volume Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 Volumes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Disk Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Volume Layouts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Spanning (Concatenation) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 iii Striping (RAID-0) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Mirroring (RAID-1) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Striping Plus Mirroring (RAID-0 +1) . . . . . . . . . . . . . . . . . . . . . . 11 RAID-5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Hot-Relocation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 Fast Volume Resynchronization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 The VERITAS Visual Administrator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 VERITAS NetBackup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 Block-level Incremental Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 2. Setting Up the Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . iv 15 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 A Roadmap for Setting Up a Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 Creating a Disk Group. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 Disk Group Configuration Guidelines . . . . . . . . . . . . . . . . . . . . . . . . 17 Creating a Disk Group For a Database . . . . . . . . . . . . . . . . . . . . . . . . 17 Adding Disks to a Database Disk Group . . . . . . . . . . . . . . . . . . . . . . 18 Selecting Volume Layouts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Selecting a Volume Layout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Choosing Appropriate Stripe Unit Sizes . . . . . . . . . . . . . . . . . . . . . . . 20 Choosing Between Mirroring and RAID-5 . . . . . . . . . . . . . . . . . . . . . 20 Volume Configuration Guidelines . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Creating a VxFS File System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 File System Configuration Guidelines . . . . . . . . . . . . . . . . . . . . . . . . . 22 Creating a File System on a New Volume . . . . . . . . . . . . . . . . . . . . . . 22 Creating a File System on a Simple Volume. . . . . . . . . . . . . . . . . 23 Creating a File System on a Striped Volume . . . . . . . . . . . . . . . . 25 Creating a File System on a RAID-5 Volume . . . . . . . . . . . . . . . . 27 VERITAS Database Edition for Oracle System Administrator’s Guide Creating a File System on a Mirrored Volume . . . . . . . . . . . . . . . 30 Creating a File System on an Existing Volume . . . . . . . . . . . . . . . . . . 31 Mounting a File System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 Unmounting a File System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 Checking a File System for Consistency . . . . . . . . . . . . . . . . . . . . . . . 33 Online File System Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 Displaying File System Properties. . . . . . . . . . . . . . . . . . . . . . . . . 34 Resizing a File System. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 Using the Command Line Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 Creating a Volume . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 Creating a File System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 Mounting a File System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 Unmounting a File System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 Online File System Administration. . . . . . . . . . . . . . . . . . . . . . . . 39 Creating Database Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 Creating a Database File Using qiomkfile. . . . . . . . . . . . . . . . . . . . 41 Accessing Regular UNIX Files as Quick I/O Files . . . . . . . . . . . . . . . 43 Converting Oracle Database Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 Using Absolute or Relative Pathnames . . . . . . . . . . . . . . . . . . . . . . . . 46 Extending and Resizing the Database File . . . . . . . . . . . . . . . . . . . . . . . . . 46 Enabling Cached Quick I/O . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 Set qio_cache_enable to 1. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 Enabling/Disabling Cached Quick I/O for Individual Files . . . 49 Upgrading From Existing Database Configurations. . . . . . . . . . . . . . . . . 51 Upgrading From UFS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 Upgrading From VxFS 1.2.x . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 Upgrading From VxFS 2.x. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 Contents v Upgrading From Raw Devices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3. Performance and Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55 Tuning VxFS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 Obtaining File I/O Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 Using File I/O Statistics Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 Tuning Cached Quick I/O. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58 Tuning VxVM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60 Obtaining Volume I/O Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 Tuning Oracle Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 Increasing DB_BLOCK_BUFFERS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 Tuning DB_FILE_MULTIBLOCK_READ_COUNT . . . . . . . . . . . . . . . . . 62 Tuning Sequential Table Scans/DSS Queries . . . . . . . . . . . . . . . . . . . 63 Tuning Oracle8 Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Setting DISK_ASYNCH_IO to TRUE. . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Setting DBWR_IO_SLAVES to 0 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Tuning Oracle7 Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Setting USE_READV to FALSE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Setting USE_ASYNC_IO to TRUE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Setting DB_WRITERS to 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 4. Database Backup and Restore . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . vi 53 67 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 Database Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 Using NetBackup for Block-level Incremental Backup . . . . . . . . . . . 69 Storage Checkpoint Database Backup . . . . . . . . . . . . . . . . . . . . . . . . . 70 Using Snapshot File Systems for Backup . . . . . . . . . . . . . . . . . . . . . . 71 Using Snapshot Volumes for Backup. . . . . . . . . . . . . . . . . . . . . . . . . . 71 VERITAS Database Edition for Oracle System Administrator’s Guide Choosing Between NetBackup, VxFS Snapshots, and VxVM Snapshots 72 Cost . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72 Database Run-time Performance . . . . . . . . . . . . . . . . . . . . . . . . . . 73 Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 Data Persistence and Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 Database Backup Guidelines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 Database Backup Using Snapshot File Systems . . . . . . . . . . . . . . . . . . . . 75 How a Snapshot File System Works. . . . . . . . . . . . . . . . . . . . . . . . . . . 75 Determining the Size of a Snapshot File System . . . . . . . . . . . . . . . . 76 Monitoring Snapshot File System Block Usage . . . . . . . . . . . . . . . . . 77 Performance of Snapshot File Systems . . . . . . . . . . . . . . . . . . . . . . . . 77 Database Backup Using Snapshot File Systems . . . . . . . . . . . . . . . . . 78 Backing Up and Restoring Individual Quick I/O Files . . . . . . . 79 Creating a Snapshot File System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 Creating a Snapshot Using the Visual Administrator. . . . . . . . . 79 Creating a Snapshot Using the Command Line . . . . . . . . . . . . . 82 Database Backup Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82 Restoring Database Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84 Database Restore Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 Database Backup Using Snapshot Volumes . . . . . . . . . . . . . . . . . . . . . . . 86 How a Snapshot Volume Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86 Creating a Snapshot Volume for Database Backup . . . . . . . . . . . . . . 87 Creating a Snapshot Using the Visual Administrator. . . . . . . . . 87 Creating a Snapshot Using the Command Line . . . . . . . . . . . . . 89 Restoring Database Files from Snapshot Volumes . . . . . . . . . . . . . . . . . . 90 Database Backup Using Storage Checkpoints . . . . . . . . . . . . . . . . . . . . . . 91 Contents vii What is a Storage Checkpoint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92 How a Storage Checkpoint Works . . . . . . . . . . . . . . . . . . . . . . . . 92 Performance of Storage Checkpoints . . . . . . . . . . . . . . . . . . . . . . 92 File System and Storage Checkpoint Space Management . . . . . 93 Database Backup with NetBackup . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94 Storage Checkpoint and Rollback Using vxdba . . . . . . . . . . . . . . . . 95 Using vxdba to Perform Database Backup and Rollback . . . . . . . . . 97 Starting vxdba . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97 The vxdba Main Menu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97 Startup database instance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100 Shutdown database instance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 Display database information . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103 Display tablespace information . . . . . . . . . . . . . . . . . . . . . . . . . . . 104 Display datafile/file system information . . . . . . . . . . . . . . . . . . . 105 Storage checkpoint administration . . . . . . . . . . . . . . . . . . . . . . . . 106 Restore files from storage checkpoint . . . . . . . . . . . . . . . . . . . . . . 110 File system space usage administration . . . . . . . . . . . . . . . . . . . . 117 Glossary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123 A. Configuration Considerations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131 Disk Management Strategies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131 Datafile Layout Strategies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132 Backup Strategies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133 Tuning Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135 viii VERITAS Database Edition for Oracle System Administrator’s Guide Preface Introduction The VERITAS® Database Edition for Oracle® is an integrated set of system software enhancements and configuration guidelines that help Oracle database administrators configure a database system for high performance, availability, and reliability. Audience The Database Administrator’s Guide is intended for database and system administrators responsible for configuring and maintaining Oracle databases with the VERITAS Database Edition. Its components include: the VERITAS File System (VxFS®), Volume Manager (VxVM®), Visual Administrator (VxVA®), and VERITAS Quick I/OTM for Databases, and VERITAS NetBackupTM Blocklevel Incremental (BLI) Backup Extension for Oracle (referred to in this document as BLI Backup). This guide assumes that the database administrator has: • a basic understanding of system and database administration • a working knowledge of the UNIX system • a general understanding of file systems ix Scope The purpose of this guide is to provide information relating to database administration using the VERITAS Database Edition, particularly its components—VxFS and VxVM, and BLI Backup. Organization This guide is organized as follows: • Chapter 1, “The Database Edition” provides an overview of the features and characteristics of the VERITAS Database Edition for Oracle. • Chapter 2, “Setting Up the Databases,” discusses how to select volume layouts and create file systems and database files for setting up Oracle databases. • Chapter 3, “Performance and Tuning,” provides some tuning tips and describes the commands that can be used to monitor and tune the database performance. • Chapter 4, “Database Backup and Restore,” describes the online backup facilities provided with VxFS, VxVM and NetBackup. Using This Guide Chapter 1 gives a general overview of VxFS, VxVM, and NetBackup features that are related to database administration. Chapter 2 describes how to set up a database configuration with optimal performance. It provides configuration guidelines as well as step-by-step instructions on setting up the database configurations using VxFS and VxVM. Chapter 3 describes the tuning tips and the commands that are available for improving system and database performance. Chapter 4 explains the trade-offs of performing database backup using snapshot volumes, snapshot file systems, and storage checkpoints and rollback, and describes step-by-step instructions on database backup using snapshot mechanisms. The VERITAS Database Edition also supports block-level incremental backups for Oracle databases. For detailed information on BLI backups, refer to the VERITAS NetBackup BLI Backup Extension for Oracle System Administrator’s Guide. x VERITAS Database Edition for Oracle Administrator’s Guide Related Documents The following documents provide related information: • The VERITAS Database Edition Installation Guide provides instructions on how to install and initialize the Volume Manager, the Visual Administrator, and the VERITAS File System. • The VERITAS Volume Manager User’s Guide provides information on how to use various Volume Manager interfaces to perform a variety of tasks. The User’s Guide documents the Visual Administrator graphical user interface, common VxVM command line operations, and the vxdiskadm menu interface. • The VERITAS Volume Manager System Administrator’s Guide provides information about advanced Volume Manager concepts, as well as information about the Volume Manager command line interface and recovery procedures. • The VERITAS File System Quick Start Guide describes how to perform common file system tasks with the VERITAS File System and provides examples of typical VERITAS File System operations. • The VERITAS File System System Administrator’s Guide provides conceptual information about the VERITAS File System and describes how to use associated commands and utilities. • The VERITAS NetBackup User’s Guide explains how to use NetBackup to back up, archive, and restore files and directories. • The VERITAS NetBackup System Administrator’s Guide describes how to configure and manage the operation of NetBackup. • The VERITAS NetBackup and Media Manager Release Notes explains how to install NetBackup and Media Manager. • The VERITAS NetBackup Block-level Incremental (BLI) Backup Extension for Oracle System Administrator’s Guide provides information on how to install, configure, and use NetBackup and the BLI Backup Extension for Oracle to perform Oracle database backup. Preface xi Conventions The following table describes the typographic conventions used in this guide xii Typeface Usage Examples courier Computer output, user input, command names, files, and directories $ You have mail. The cat command displays files. $ ls -a italics New terms, document titles, words to be emphasized, glossary cross references, variables to be substituted with a real name or value $ cat filename See the User’s Guide for details. bold Glossary terms Symbol Usage % C shell prompt $ Bourne/Korn shell prompt # Superuser prompt (all shells) \ In examples showing user input, indicates continued input on the following line; you do not type this character # mount -F vxfs \ /h/filesystem [ ]$ In a command synopsis, brackets indicates an optional argument. # ls [-a] |$ In a command synopsis, a vertical bar separates mutually exclusive arguments. # mount [suid|nosuid] VERITAS Database Edition for Oracle Administrator’s Guide Getting Help For license information or information about VERITAS service packages, contact VERITAS Customer Support. US and Canadian Customers: 1-800-342-0652 International Customers: +1 (650) 335-8555 Fax: (650) 335-8428 VERITAS Customer Support can also be reached through electronic mail at: [email protected] You can also get additional information about VERITAS products by visiting our website at www.veritas.com Preface xiii xiv VERITAS Database Edition for Oracle Administrator’s Guide The Database Edition 1 Introduction This chapter describes the features of the VERITAS Database Edition for Oracle. Topics covered in this chapter include: • The VERITAS Database Edition • The VERITAS File System • The VERITAS Volume Manager • The VERITAS Visual Administrator • VERITAS NetBackup The VERITAS Database Edition VERITAS Database Edition for Oracle combines the strengths of the VERITAS products with database-specific enhancements to offer unrivaled performance, availability, and manageability for Oracle database servers. VERITAS Database Edition for Oracle includes the following products: • VERITAS File System (VxFS) A high-performance, fast-recovery file system that is optimized for business-critical database applications and data-intensive workloads. • VERITAS Volume Manager (VxVM) 1 1 A disk management subsystem that supports disk striping and mirroring and simplified disk management for improved data availability and higher performance. • VERITAS Visual Administrator (VxVA) An easy to use graphical user interface to VxVM and VxFS. • VERITAS Quick I/O™ for Databases A feature that improves the throughput for Oracle databases built on VERITAS file systems. Quick I/O for Databases delivers raw disk performance to Oracle databases created on VxFS and provides database administrators with the advantages of using a file system without the performance penalties. Cached Quick I/O mode further enhances database performance by leveraging large system memories to buffer frequently accessed data. • VERITAS NetBackup Block-level Incremental (BLI) Backup Extension for Oracle The NetBackup BLI Backup Extension for Oracle software allows NetBackup to support incremental database backup to reduce database downtime, backup window and backup volume as well as CPU and network overhead. VxFS and VxVM offer unique online administration, allowing most frequentlyscheduled maintenance tasks (including backup, load balancing, and configuration changes) to be performed without interrupting data or system availability. VxVA simplifies online administration with an easy-to-use graphical user interface that supports most VxVM and some VxFS operations. Additionally, a High Availability (HA) version of VERITAS Database Edition for Oracle is available for customers who have a high system availability requirement. VERITAS Database Edition/HA for Oracle allows database administrators to integrate a pair of servers into a high availability database configuration, which can provide continuous services even if a server system or subsystem fails. The VERITAS Database Edition/HA for Oracle incorporates the following additional VERITAS products: • VERITAS FirstWatch® A high availability toolset that monitors system services and manages the failover of file system, database, and application services to designated systems. 2 VERITAS Database Edition for Oracle Administrator’s Guide 1 • VERITAS FirstWatch Agent for Oracle A FirstWatch extension that monitors, restarts, and manages the failover of Oracle servers. VERITAS Database Edition/HA for Oracle offers a server configuration that can significantly reduce the down time of an Oracle database caused by a system failure. The VERITAS File System The VERITAS File System (referred to as VxFS or vxfs) is an extent-based, intent-logging file system intended for use with UNIX operating systems such as Solaris. VxFS provides enhancements that make file systems more viable for use in the database environment. VxFS is particularly useful in environments that require high performance and availability and deal with large volumes of data. The following sections provide a brief overview of VxFS concepts and features that are relevant to database administration. For a more detailed description of VxFS and its features, refer to Chapter 1 of the VERITAS File System System Administrator’s Guide. Quick I/O For Databases Databases can run on file systems or raw disks. Many database administrators prefer to run their databases on top of file systems for the benefits of easy database management. Quick I/O for Databases is a feature that allows a regular, pre-allocated VxFS file to be accessed as a raw character device. This improves database throughput for Oracle databases built on top of VxFS file systems. Quick I/O delivers higher performance because it: • Supports kernel asynchronous I/O Quick I/O provides support for kernel asynchronous I/O for regular files on VxFS accessed via the Quick I/O interface • Supports direct I/O The Database Edition 3 1 Typically, I/O done on files on a regular file system is performed as buffered I/O, where the data is copied from disk into a kernel buffer and then from the kernel buffer into the application’s buffer (in this case the Oracle SGA). Quick I/O in its default mode, supports direct I/O, where the I/O is performed directly between the disk and the application’s buffers. • Avoids kernel single writer locks The semantics of I/O done to files on a file system requires the file system to maintain locks to prevent multiple processes from writing simultaneously to a file. This is done to ensure data integrity in multiprocessing environments. This unnecessary locking overhead is eliminated when Quick I/O is used because databases such as Oracle maintain their own separate set of locks. • Avoids double buffering and copying Databases, such as Oracle, maintain their own buffer cache and do not need the system buffer cache. Since Quick I/O used in its default mode, uses direct I/O, this overhead of double copying and buffering is eliminated. Additionally, more memory can be allocated to the Oracle databases to further improve transaction processing throughput. For information on how to use Quick I/O for Databases, refer to “Creating Database Files” in Chapter 2. Cached Quick I/O For Databases Normally, databases can not take advantage of system memory larger than 4G because of the 32-bit address space limitation on the Solaris platforms. For platforms that have memory larger than 4G, VxFS supports a Cached Quick I/O mode to further improve database performance, by taking advantage of the additional memory. For read operations, Cached Quick I/O allows database blocks to be cached in the system buffer cache. This potentially reduces the number of physical I/O operations and thus improves the read performance. For write operations, Cached Quick I/O uses a direct-write, copy-behind technique to preserve its buffer copy of the data. After the direct I/O is scheduled, and while it is waiting for the completion of the I/O, the file system updates its buffer to reflect the changed data being written out. For on-line 4 VERITAS Database Edition for Oracle Administrator’s Guide 1 transaction processing, Cached Quick I/O achieves better than raw performance in database throughput on large platforms with very large physical memories. Cached Quick I/O also helps sequential table scan because of the read-ahead algorithm used in the VERITAS File System. For most queries that require sequential table scans, Cached Quick I/O can significantly reduce the query response time. For information on when to use Cached Quick I/O and how to enable Cached Quick I/O for Database, refer to “Tuning Cached Quick I/O” in Chapter 3. Extent-based Allocation The ufs file system supplied with Solaris uses block-based allocation schemes and provides good random access to files and acceptable latency on small files. For larger files like database files, however, this block-based architecture limits throughput. This makes the ufs file system less than optimal choice for database environments. The vxfs file system addresses this performance issue by using a different allocation scheme which is extent-based. An extent is defined as one or more adjacent blocks of data within the file system. When storage is allocated to a file on a vxfs file system, it is grouped in extents, as opposed to being allocated a block at a time. By allocating disk space to files in extents, disk I/O to and from a file can be done in units of multiple blocks. This type of I/O can occur if storage is allocated in units of consecutive blocks. For sequential I/O, multiple-block operations are considerably faster than block-at-a-time operations. The vxfs file system allocates disk space to files in groups of one or more extents. vxfs also allows applications to control some aspects of the extent allocation for a given file. Extent attributes are the extent-allocation policies associated with a file. For information on how to create pre-allocated database files using extent attributes, refer to “Creating Database Files,” in Chapter 2. The Database Edition 5 1 Fast Database Recovery After a system crash, database recovery cannot start until after file system recovery is completed. The ufs file system relies on the full structural verification by the fsck utility as the only means to recover from a system failure. For large database configurations, this process is often very time consuming. The vxfs file system provides recovery only seconds after a system failure by using a tracking feature called intent logging. Intent logging is a logging scheme that records pending changes to the file system structure. During recovery, the vxfs fsck utility performs an intentlog replay to commit or abort any pending file system operations. The file system can then be mounted immediately after the log replay. Online Administration When a file system is active for extended periods of time, files grow, shrink, are created, and are removed. Over time, the file system tends to leave unused “gaps” or fragments between areas that are in use. This may lead to degraded performance because the file system has fewer choices when selecting an extent to assign to a file. Also, over time, changes in database size may result in file systems that are too small for the new database. VxFS provides the online administration utility fsadm to resolve these problems. A vxfs file system can be defragmented or grown while it remains online and accessible to databases. Fragmentation is not expected to be a common problem on file systems that are solely used by databases. However, to optimize performance if a file system has been in use for an extended period of time or a file system is shared with other applications, you should analyze the degree of fragmentation before creating new database files. Resizing may be necessary when the database outgrows the file system (which is common in many database environments). A file system can be expanded online only when the underlying device can be expanded online. VxVM allows online expandability of virtual disks. Working with VxVM, VxFS provides online expansion capability. Refer to “Resizing a File System,” in Chapter 2, for information on how to resize a file system. 6 VERITAS Database Edition for Oracle Administrator’s Guide 1 Large File and Large File System Support In conjunction with the VxVM, VxFS can support file systems up to a terabyte in size. For large database configurations, this eliminates the need for using multiple file systems due to the size limitation of the underlying device. Starting with Solaris 2.6, individual files are no longer limited to a 2G maximum size. Larger files can be used as a target for archiving a file system or exporting an Oracle table that is greater than 2G. In contrast, on Solaris 2.5.x, individual files are limited to a maximum of 2G. Be careful when selecting a target for archiving a file system larger than 2G on Solaris 2.5.x! The archive may well exceed the operating system’s 2G file-size limitation. The target can only be a tape device or a raw volume of equivalent size. Database Backup with VERITAS File System Snapshots VxFS provides a snapshot feature which allows instant database backup. A snapshot image of a mounted file system is created by snapshot mounting another file system, which then becomes an exact read-only copy of the first file system. The original file system is said to be snapped, and the copy is called the snapshot. The snapshot is a consistent view of the snapped file system at the point in time when the snapshot was made. The time required to make a snapshot file system is typically only a couple of seconds. Once a snapshot file system is created, a consistent database backup is made and the database can then resume its normal processing while data is being backed up from the snapshot file system to tapes or other media. For information on performing database backup using snapshot file systems, refer to “Database Backup Using Snapshot File Systems,” in Chapter 4. Storage Checkpoint and Rollback VxFS provides a storage checkpoint facility which allows block-level incremental database backups, storage checkpoint backup—cold backup while the database is hot—as well as storage rollback features. A storage checkpoint is a persistent snapshot of all user files in a file system at a moment in time. The Database Edition 7 1 The time required to create a file system checkpoint is typically only a couple of seconds. Once storage checkpoints are created, a consistent database backup image is made and the database can then resume its normal processing. Storage checkpoints are then used for rolling back the file system image to the point in time when the storage checkpoints were taken. In addition, these storage checkpoints also keep track of the block change information which enables incremental database backup at the block level. For information on performing database backup using file system checkpoints, refer to “Database Backup Using Storage Checkpoints,” in Chapter 4 and the VERITAS Block-level Incremental (BLI) Backup for Oracle System Administrator’s Guide. The VERITAS Volume Manager The VERITAS Volume Manager (referred to as VxVM) builds virtual devices called volumes on top of physical disks. Volumes are accessed by a UNIX file system, a database, or other applications in the same way physical disk partitions would be accessed. Using volumes, VxVM provides the following administrative benefits for databases: • Spanning of multiple disks—eliminates media size limitations. • Striping—increases throughput and bandwidth. • Mirroring or RAID-5—increases data availability. • Hot-relocation—automatically restores data redundancy in mirrored and RAID-5 volumes when a disk fails. • Free space pool management—simplifies administration and provides flexible use of available hardware. • Online administration—allows configuration changes without system or database down time. The following sections provide a brief overview of VxVM concepts and features that are relevant to database administration. For a more detailed description of VxVM and its features, refer to the VERITAS Volume Manager (VxVM) User’s Guide. 8 VERITAS Database Edition for Oracle Administrator’s Guide 1 Volumes A volume is a virtual disk device that appears to applications, databases, and file systems like a physical disk partition without the physical limitations of a disk partition. Due to its virtual nature, a volume is not restricted to a particular disk or a specific area. For example, a volume can span multiple disks and can be used to create a large file system. Volumes are composed of other virtual objects that can be manipulated to change the volume’s configuration. Volumes and their virtual components are referred to as Volume Manager objects. Volume Manager objects can be manipulated in a variety of ways to optimize performance, provide redundancy of data, and perform backups or other administrative tasks on one or more physical disks without interrupting applications. As a result, data availability and disk subsystem throughput are improved. The configuration of a volume can be changed without causing disruption to databases or file systems that are using the volume. For example, a volume can be mirrored on separate disks or moved to use different disk storage. Disk Groups A disk group is a collection of disks that share a common configuration (for example, configuration objects that belong to a single database). The default disk group is rootdg (the root disk group). Additional disk groups should be created for databases. It is recommended to create one disk group for each database. A disk group and its components can be moved as a unit from one host to another host. Volumes and file systems that belong to the same database and are created within one disk group can be moved as a unit. A given volume must be configured from disks belonging to one disk group. The Database Edition 9 1 Volume Layouts A Redundant Array of Inexpensive Disks (RAID) is a disk array where a group of disks appears to the system as a single virtual disk or a single volume. VxVM supports several RAID implementations as well as spanning. The following volume layouts are available to satisfy different database configuration requirements: • Spanning (Concatenation) • Striping (RAID-0) • Mirroring (RAID-1) • Striping Plus Mirroring (RAID-0+1) • RAID-5 CAUTION ! Spanning or striping a volume across multiple disks increases the chance that a disk failure will result in failure of that volume. Use mirroring or RAID-5 to substantially reduce the chance of a single volume failure caused by a single disk failure. Spanning (Concatenation) Spanning is a technique of mapping data in a linear manner onto multiple physical disks. If you were to access all the data in a concatenated volume sequentially, you would first access the data in the first disk from beginning to end, then the second disk from beginning to end, and so forth until the end of the last disk. Spanning is useful when you need to read or write data sequentially (for example, reading from or writing to database redo logs) and there is no sufficient contiguous space. Striping (RAID-0) Striping is a technique of mapping data so that the data is interleaved among multiple physical disks. Data is allocated in equal-sized units (called stripe units) that are interleaved between the disks. Each stripe unit is a set of contiguous blocks on a disk. The default stripe unit size is 64 kilobytes. A stripe consists of the set of stripe units at the same positions across all columns. 10 VERITAS Database Edition for Oracle Administrator’s Guide 1 Striping is useful if you need large amounts of data to be written to or read from the physical disks quickly by using parallel data transfer to multiple disks. Striping is also helpful in balancing the I/O load across multiple disks. Mirroring (RAID-1) Mirroring is a technique of using multiple copies of the data, or mirrors, to duplicate the information contained in a volume. In the event of a physical disk failure, the mirror on the failed disk becomes unavailable, but the system continues to operate using the unaffected mirrors. A volume requires at least two mirrors to provide redundancy of data. A volume can consist of up to 32 mirrors. Each of these mirrors must contain disk space from different disks in order for the redundancy to be effective. When spanning or striping across multiple disks, failure of any one disk will generally make the entire volume unusable. Mirroring increases system reliability and availability. Striping Plus Mirroring (RAID-0 +1) VxVM supports the combination of striping with mirroring. When used together on the same volume, striping plus mirroring offers the benefits of spreading data across multiple disks while providing redundancy of data. For striping and mirroring to be effective together, the mirrors must be allocated from separate disks. For databases that support online transaction processing (OLTP) workloads, it is recommended to use striped and mirrored volumes in order to improve the database reliability and availability. RAID-5 RAID-5 provides data redundancy through the use of parity (a calculated value that can be used to reconstruct data after a failure). While data is being written to a RAID-5 volume, parity is also calculated by performing an exclusive OR (XOR) procedure on data. The resulting parity is then written to another part The Database Edition 11 1 of the volume. If a portion of a RAID-5 volume fails, the data that was on that portion of the failed volume can be recreated from the remaining data and the parity. RAID-5 offers data redundancy similar to mirroring while requiring less disk space. RAID-5 read performance is similar to that of striping but with relatively slow write performance. RAID-5 is useful if the database workload is read-intensive (as in many data warehousing applications). Hot-Relocation In addition to providing volume layouts that help improve database performance and availability, VxVM offers additional features that can be used to further improve system availability in the event of a disk failure. Hotrelocation is the ability of a system to react automatically to I/O failures on mirrored or RAID-5 volumes and restore redundancy and access to those volumes. VxVM detects I/O failures on volumes and relocates the affected portions to disks designated as spare disks and/or free space within the disk group. VxVM then reconstructs the volumes that existed before the failure and makes them redundant and accessible again. The hot-relocation feature is enabled by default and is recommended for most database configurations. After hot-relocation occurs, it is advisable to verify the volume configuration for any possible performance impact. It is also a good idea to designate additional disks as spares to augment the spare pool. Fast Volume Resynchronization When storing data redundantly, using mirrored or RAID-5 volumes, the Volume Manager takes necessary measures to ensure that all copies of the data match exactly. However, if the system crashes, small amounts of the redundant data on a volume can become inconsistent or unsynchronized. This is very undesirable. For mirrored volumes, it can cause two reads from the same region of the volume to return different results if different mirrors are used to satisfy the read request. In the case of RAID-5 volumes, it can lead to parity corruption and incorrect data reconstruction. 12 VERITAS Database Edition for Oracle Administrator’s Guide 1 When the Volume Manager recognizes this situation, it needs to make sure that all mirrors contain exactly the same data and that the data and parity in RAID-5 volumes agree. This process is called volume resynchronization. Not all volumes may require resynchronization after a system failure. The Volume Manager notices when a volume is first written and marks it as dirty. Only volumes that are marked dirty when the system reboots require resynchronization. The process of resynchronization can be computationally expensive and can have a significant impact on system and database performance. However, it does not affect the availability of the database after system reboot. The database is immediately accessible after database recovery although the performance may suffer due to resynchronization. For very large volumes or for a very large number of volumes, the resynchronization process can take a long time. The time can be significantly reduced by using Dirty Region Logging (DRL) for mirrored volumes, or by making sure that RAID-5 volumes have valid RAID-5 logs. But using logs may slightly reduce the database write performance. For most database configurations, it is recommended to use the DRL logs or the RAID-5 logs when mirrored or RAID-5 volumes are used. On the other hand, it is advisable to evaluate the database performance requirements to determine the optimal volume configurations for the databases. The VERITAS Visual Administrator The Visual Administrator (referred to as VxVA) provides a graphical user interface to VxFS and VxVM. The Visual Administrator provides visual elements such as icons, menus, and forms to ease the task of manipulating Volume Manager objects and file systems. File systems and volumes created by VxVA are fully interoperable and compatible with those created via the command line. The command line interface can be used to create scripts for database administration. If you are new to VxFS and/or VxVM, it is recommended that you use the Visual Administrator first and turn on the Visual Administrator logging facility. The logging facility logs the commands performed during file system and volume creation which can be a useful tool for creating scripts as well as The Database Edition 13 1 for learning various commands. Once you are more familiar with the concepts and the command line syntax, it is often easier to use the command line interface, especially when setting up large database configurations. VERITAS NetBackup VERITAS NetBackup provides backup, archive, and restore capabilities for database files and directories contained on client systems where database servers reside in a client-server network. NetBackup server software resides on platforms that manage physical storage devices. The NetBackup server provides robotic control, media management, error handling, scheduling, and a repository of all client backup images. Administrators can set up schedules for automatic, unattended full and incremental backups. These backups are managed entirely by the NetBackup server. The administrator can also manually back up clients. Client users can perform backups, archives, and restores from their client system, and once started, these operations also run under the control of the NetBackup server. Block-level Incremental Backup NetBackup uses the VxFS Storage Checkpoint facility to identify all the data blocks that changed since the last Storage Checkpoint, thus allowing NetBackup to back up only the blocks changed since the last backup. This reduces both the time required to complete a database backup and the amount of data transferred during backups. It also allows more frequent backups, resulting in more up-to-date backup images. When restoring from backups, the restore time is increased only by the extra time required to apply the incremental backups after a full restore completes. However, frequent incremental backups can even speed up the database recovery by reducing the amount of redo logs to apply. Block-level incremental backup is particularly useful in a database environment where a database can be hundreds of gigabytes or terabytes. Using traditional backup methods for an offline database backup, any change in the database—no matter how small—requires backing up the entire database. Using block-level incremental backup, only modified data blocks need to be backed up. 14 VERITAS Database Edition for Oracle Administrator’s Guide Setting Up the Databases 2 Introduction This chapter describes how to use the VxVM and VxFS facilities to set up optimal system configurations for Oracle databases, and how to use Quick I/O for Databases to create database files. It also includes configuration guidelines and recommendations for database environments. Topics covered in this chapter include: • A Roadmap for Setting Up a Database • Creating a Disk Group • Selecting Volume Layouts • Creating a VxFS File System • Creating Database Files • Extending and Resizing the Database File • Enabling Cached Quick I/O • Upgrading From Existing Database Configurations 15 2 A Roadmap for Setting Up a Database Each database administrator may have environment-specific procedures for setting up optimal database configurations. The following steps are provided as a possible roadmap for setting up a database with reasonably good performance which can be tailored to fit any particular environment: 1. Create a disk group 2. Select volume layouts 3. Create file systems for database files 4. Create database files with preallocated space 5. Create a database These steps can be used to create a database configuration that takes advantage of the easy management features of VxVM and VxFS and the performance benefit of the Quick I/O for Databases feature. This chapter describes the basics of how to perform most of these tasks using the Visual Administrator graphical user interface and the command line interface. Refer to the Oracle documentation for procedures on creating Oracle databases. Refer to the VERITAS Volume Manager User’s Guide and VERITAS File System System Administrator’s Guide for additional information on how to create VxVM objects and VxFS file systems. Creating a Disk Group Before creating file systems for a database, set up a disk group for each database. A disk group lets you group disks, volumes, file systems, and files that are relevant to a single database into a logical collection for easy administration. Because a disk group and its components can be moved as a unit from one machine to another, an entire database can be moved as a unit when all the configuration objects of the database are in one disk group. 16 VERITAS Database Edition for Oracle Administrator’s Guide 2 Disk Group Configuration Guidelines Follow these guidelines when creating disk groups for use with databases: • Never put all of the disks in the rootdg disk group. The rootdg disk group cannot be moved and has certain limitations. • Create one disk group for each database. • Name each disk group using the Oracle database instance name specified by the environment variable $ORACLE_SID and a dg suffix. It is a good idea to append dg to the name to identify the object as a disk group. • Never create database files using file systems or volumes that are not in the same database disk group. Creating a Disk Group For a Database This section describes how to create a new disk group using either the Visual Administrator or the vxdg command. Note: Disk groups are represented by the Visual Administrator as view windows (such as rootdg) rather than icons. A disk group must contain at least one disk at the time it is created. To create a new disk group using the Visual Administrator: 1. Go to the View of Disks window. 2. Select the partition icon corresponding to the physical disk to be added to the new disk group. 3. From the Advanced-Ops menu, select Disk Group > Initialize. The Initialize Disk Group form appears. 4. Complete the Initialize Disk Group form by entering a unique name to apply to the new disk group. Use the database instance name ($ORACLE_SID) with a dg suffix to name the disk group. For example, if the instance name is data, name the disk group datadg. 5. When the form is completed, select Apply to activate the disk group initialization. Setting Up the Databases 17 2 A button representing the newly-created disk group appears in the Visual Administrator main window. You can access the new disk group’s view by clicking on its button. You can also use the vxdg command to create a new disk group. For example, to create a disk group named datadg on a raw disk partition c1t0d0s2 and name the disk datadg01, enter: # vxdg init datadg datadg01=c1t0d0s2 The disk name datadg01 references the disk within the disk group. For more information on the vxdg command, refer to the vxdg(1M) manual page. Adding Disks to a Database Disk Group When a disk group is first created, it contains only a single disk. You may need to add more disks to the disk group. This section describes how to add disks to a disk group using VxVA or the vxdg command. If you have many disks to add to the disk group, it is easier to use the vxdg command. To add a disk to the datadg disk group using the Visual Administrator: 1. Access the View of datadg window and position it so that it is at least partially visible. 2. Go to the View of Disks window and drag the partition icon corresponding to the physical disk to be added to a disk group into the View of datadg window. Drop the partition icon anywhere within the View of datadg window. Do not drag the entire physical disk icon surrounding the partition. A new VM disk icon appears in the View of datadg window. The partition icon in the View of Disks window changes color or pattern to indicate that it now belongs to a disk group. 18 VERITAS Database Edition for Oracle Administrator’s Guide 2 3. To add disks to the datadg disk group using the vxdg command, enter commands similar to the following: # vxdg -g datadg adddisk datadg02=c1t1d0s2 # vxdg -g datadg adddisk datadg03=c1t2d0s2 # vxdg -g datadg adddisk datadg04=c1t3d0s2 The disk names datadg02, datadg03, and datadg04 reference the disks in the disk group. By default, VxVA assigns disk names using the disk group name as a prefix. Selecting Volume Layouts The Volume Manager offers a variety of disk layouts that allow you to configure your database to meet performance and availability requirements. The proper selection of storage layouts provides optimal performance for the database workload. Selecting a Volume Layout The most important factor in database performance is usually the table placement on the disks. Disk I/O is one of the most important determining factors of your database’s performance. Having a balanced I/O load usually means optimal performance. Designing a disk layout for the database objects to achieve a balanced I/O is a crucial step in configuring a database. When deciding where to put tablespaces, it is often difficult to anticipate future usage patterns. VxVM provides flexibility in configuring storage to do the initial database set up and improve database performance. VxVM can split volumes across multiple drives to provide a finer level of granularity in data placement. By using striped volumes, I/O can be balanced across multiple disk drives. For most transaction processing databases, ensuring that different database files and tablespaces are distributed across the available disks may be sufficient. Striping also helps sequential table scan performance. When a table is striped across multiple devices, a high transfer bandwidth can be achieved by setting the Oracle parameter DB_FILE_MULTIBLOCK_READ_COUNT to a multiple of full stripe size divided by DB_BLOCK_SIZE (refer to “Tuning Oracle Databases”). Setting Up the Databases 19 2 Choosing Appropriate Stripe Unit Sizes When creating a striped volume, you need to decide the number of columns to form a striped volume and the stripe unit size. You also need to decide how to stripe the volume. You may stripe a volume across multiple disk drives on the same controller or across multiple disks on multiple controllers. The decision is based on the disk and controller bandwidth and the database workload. In general, for most OLTP databases, use the default stripe unit size of 64K or a smaller size for striped volumes and 16K for RAID-5 volumes. For OLTP workloads, stripe across as many available controllers or disks as possible. By striping across multiple controllers or disks, disk I/O can be balanced across multiple I/O channels. For decision support workloads in which sequential scans are common, however, do not stripe across more disks than the controller or the CPU can handle. Choosing Between Mirroring and RAID-5 VxVM provides two volume configuration strategies for data redundancy: mirroring and RAID-5. Both strategies allow continuous access to data in the event of disk failure. For most database configurations, it is recommended to use mirrored, striped volumes for databases. But if hardware cost is a significant concern and having higher data availability is still important, RAID5 volumes can be used. Note that a RAID-5 configuration has certain performance implications that must also be considered. When RAID-5 volumes are used, writes to RAID-5 volumes may require parity bit recalculation which adds significant I/O and CPU overhead. This can cause considerable performance penalties in an OLTP workload. However, if the database has a high read ratio, performance is similar to that of a striped volume. Volume Configuration Guidelines Follow these guidelines when selecting volume layouts: • Put the redo logs on a file system created on a simple volume separate from the user tablespaces. Concatenate multiple devices to create larger volumes if needed. Use mirroring to improve reliability. Do not use RAID-5 for redo logs. 20 VERITAS Database Edition for Oracle Administrator’s Guide 2 • When normal system availability is acceptable, put the tablespaces on file systems created on striped volumes for most OLTP workloads. • Create striped volumes across about 4 to 10 disks only. Do not stripe across less than 4 disks. Try to stripe across disk controllers. For sequential scans, do not stripe across too many disks or controllers. The single thread that processes sequential scan may not be able to keep up with the disk speed. • For most workloads, use the default 64K stripe unit size for striped volumes and 16K for RAID-5 volumes. • When system availability is critical, use mirroring for most write-intensive OLTP workloads. Turn on Dirty Region Logging (DRL) to allow fast volume resynchronization after a system crash. • When system availability is critical, use RAID-5 for most read-intensive OLTP workloads to improve database performance and availability. Use RAID-5 logs to allow fast volume resynchronization after a system crash. • For most decision support (DSS) workloads where sequential scans are common, it is advisable to experiment with different striping strategies and stripe unit sizes. Put the most frequently accessed tables or tables that are accessed together on separate stripe volumes to improve the bandwidth of data transfer. • If disk arrays with hardware RAID are available, use hardware RAID-5 for write-intensive workloads. Creating a VxFS File System The Visual Administrator performs file system operations by executing most file system commands directly. The file system must be placed on a volume before file system operations can be performed through the Visual Administrator. The Visual Administrator can detect mounted file systems on a given volume, but not unmounted file systems. Therefore, you must be aware of whether or not an unmounted file system already exists before performing certain file system operations. The Visual Administrator has no icons for file systems. However, the mount point name appears below the volume icon if a mounted file system exists on that volume. If the mount point is very long, only part of it appears under the volume icon. Setting Up the Databases 21 2 The Visual Administrator adds entries to the /etc/vfstab file (file system table) for new file systems. However, entries for removed file systems must be removed from this file manually. File System Configuration Guidelines Follow these guidelines when creating VxFS file systems: • Use the VxFS defaults when creating file systems for databases. Never disable the intent logging feature of the file system. • For redo logs, create a single file system using a simple (and mirrored, if necessary) volume. Put the other tablespaces and database files on a single file system created on a striped (and mirrored, if necessary) or RAID-5 volume. • Use the mount points to name the underlying volumes. For example, if a file system name /db01 is to be created on a mirrored volume, name the volume db01 and the mirrors db01-01 and db01-02 to relate to the configuration objects. Creating a File System on a New Volume File systems can be created and placed on volumes, one file system per volume. When the Visual Administrator is used to create a file system via the Basic-Ops >File Systems Operations menu, two tasks are actually performed: • A volume is created on a Volume Manager disk. • A file system is created on that volume. The Visual Administrator handles both of these tasks automatically. This approach can only be used to create a file system on simple, striped, and RAID-5 volumes. To create a file system on a mirrored volume or a striped and mirrored volume, a simple or striped volume can be created with a file system and then mirrored. 22 VERITAS Database Edition for Oracle Administrator’s Guide 2 Creating a File System on a Simple Volume To create a file system on a simple, concatenated volume: 1. Go to the view window corresponding to your database disk group. 2. Optionally designate a disk for the new volume. Ensure that the disk has sufficient free space to accommodate the desired length of the new volume—click the RIGHT button on an unobscured portion of that disk icon to access its VxVM VM disk properties form, then checking the value in the Maximum free space field. If there is sufficient free space, select the disk by clicking the LEFT button on its icon. If no disks are selected, disks with sufficient free space are automatically used. 3. From the Basic-Ops menu, select File System Operations > Create. A submenu listing basic volume types appears. 4. Select Simple. The Simple Volume/FS Create form appears (see Figure 1). This form creates a file system on a concatenated volume. The form is divided into two sections, one for volume creation and the other for file system creation. Most of the form fields are already set to the defaults for the creation of a file system on a new volume. Setting Up the Databases 23 2 Figure 1 Simple Volume/FS Create Form 5. Complete the Simple Volume/FS Create Form. Specify a size for the file system to be created and the mount point. Change the volume name to be identical to the mount point and use the default settings for the rest fields. Because some space is required for file system structural files, allocate approximately 10% more space for the file system or database. (Refer to the online help for details on Volume/FS Create form.) For example, if you want to create a database of 500 megabytes, specify a size of 550M. If you plan to use BLI Backup, you may need an additional 10-15% disk space depending on the amount of database activity during the backup and the length of the backup duration. Refer to the VERITAS NetBackup Block-level Incremental Backup Extension for Oracle System Adminstrator’s Guide and “Database Backup Using Storage Checkpoints” in Chapter 4 for more information. 24 VERITAS Database Edition for Oracle Administrator’s Guide 2 6. When the form is completed, select Apply to create the volume and file system. A new volume icon appears. If the file system is mounted, it is represented by the mount point, which appears below the new volume. Figure 2 shows a simple, concatenated volume with a mounted file system. Figure 2 Simple Volume and File System V datadg01-01 db01-01 db01 /db01 Creating a File System on a Striped Volume To create a file system on a striped volume: 1. Go to the view window corresponding to the database disk group. 2. Optionally designate four or more disks to be used for the new volume. These disks must belong to the same disk group. Ensure that the disks have sufficient free space to accommodate their portion of the desired length of the new volume (click on the RIGHT button on an unobscured portion of each disk icon to access its properties form, then check the value in the Maximum free space field). If there is sufficient free space, select the disks by clicking the MIDDLE button on their icons. If no disks are selected, disks with sufficient free space are automatically used. 3. From the Basic-Ops menu, select File System Operations > Create. A submenu listing basic volume types appears. 4. Select Striped. Setting Up the Databases 25 2 The Striped Volume/FS Create form appears (see Figure 3). This form creates a file system on a striped volume. The form is divided into two sections, one for volume creation and the other for file system creation. Most of the form fields are already set to the defaults for the creation of a file system on a new volume. Figure 3 Striped Volume/FS Create Form 5. Complete the Striped Volume/FS Create form. Specify a size for the file system to be created and the mount point. If you did not select a set of disks for striping, enter the number of columns for striping. Change the volume name to be identical to the mount point and use the default settings for the rest of the fields. Because some space is required for file system structural files, allocate approximately 10% more space for the file system or database. For example, if you want to create a database of 500 megabytes, specify a size of 550M. If you plan to use BLI Backup, you may need an additional 10-15% disk space depending on the amount of database activity during the backup and the length of the backup duration. Refer to the VERITAS NetBackup Block-level 26 VERITAS Database Edition for Oracle Administrator’s Guide 2 Incremental Backup Extension for Oracle System Adminstrator’s Guide and “Database Backup Using Storage Checkpoints” in Chapter 4 for more information. Refer to the online help for details on Volume/FS Create form. 6. When the form is properly completed, select Apply to create the volume and file system. A new striped volume icon appears. It contains a single box (called plex) and multiple smaller boxes (called subdisks). Note that there are gaps between the smaller boxes to indicate that it is striped. If the file system is mounted, it is represented by the mount point, which appears below the new volume. Figure 4 shows a striped volume with four columns with a mounted file system. Figure 4 Striped Volume with File System V datadg01-03 datadg02-02 datadg03-01 datadg04-01 db02-01 db02 /db02 Creating a File System on a RAID-5 Volume Create a file system on a RAID-5 volume as follows: 1. Go to the view window corresponding to the database disk group. 2. Optionally, designate four or more disks to be used for the new volume. Setting Up the Databases 27 2 These disks must belong to the same disk group. Ensure that the disks have sufficient free space to accommodate their portion of the desired length of the new volume—click the RIGHT button on an unobscured portion of each disk icon to access its properties form, then check the value in the Maximum free space field. If there is sufficient free space, select the disks by clicking the MIDDLE button on their icons. If no disks are selected, disks with sufficient free space are automatically used. 3. From the Basic-Ops menu, select File System Operations > Create. A submenu listing basic volume types appears. 4. Select RAID-5. The RAID-5 Volume/FS Create form appears (see Figure 5). This form creates a file system on a RAID-5 volume. The form is divided into two sections, one for volume creation and the other for file system creation. Most of the form fields are already set to the defaults for the creation of a file system on a new volume. Figure 5 28 RAID-5 Volume/FS Create Form VERITAS Database Edition for Oracle Administrator’s Guide 2 5. Complete the RAID-5 Volume/FS Create form. Specify a size for the file system to be created and the mount point. If you did not select a set of disks, enter the number for columns for the RAID-5 volume. Change the volume name to be identical to the mount point and use the default settings for the rest fields. Because some space is required for file system structural files, allocate approximately 10% more space for the file system or database. For example, if you want to create a database of 500 megabytes, specify a size of 550M. If you plan to use BLI Backup, you may need an additional 10-15% disk space depending on the amount of database activity during the backup and the length of the backup duration. Refer to the VERITAS NetBackup Block-level Incremental Backup Extension for Oracle System Adminstrator’s Guide and “Database Backup Using Storage Checkpoints” in Chapter 4 for more information. Refer to the online help for details on Volume/FS Create form. 6. When the form is properly completed, select Apply to create the RAID-5 volume and file system. A new RAID-5 volume icon appears. It contains two boxes (one RAID-5 plex and one log plex). If the file system is mounted, it is represented by the mount point, which appears below the new volume. By default, a RAID-5 log will be created on one of the selected disks, as a result, the RAID-5 volume will be striped over one less disk. Since a RAID-5 log takes only a small amount of space, if performance is not an issue, it is recommended to create first a RAID-5 volume without the log and then choose an appropriate disk to create the log. Figure 6 shows a RAID-5 volume with a RAID-5 log with a mounted file system, which is /db03. The volume contains two large boxes: one is a RAID5 plex (db03-01) striping across three disks, and one is a log plex (db03-02) on the fourth disk. Setting Up the Databases 29 2 Figure 6 RAID-5 Volume with File System and Log Plex RAID-5 datadg01-01 datadg02-02 datadg04-01 db03-02 datadg03-02 db03-01 db03 /db03 Creating a File System on a Mirrored Volume To create a file system on a mirrored volume, a volume can be created with a file system and then mirrored. The mirror layout can be simple or striped. The number of available disks must be sufficient to accommodate the layout type of both the existing volume and the mirror to be added. Note: A RAID-5 volume cannot be mirrored. Create a file system on a mirrored volume as follows: 1. Go to the view window corresponding to the database disk group. 2. Create a simple or striped volume containing a file system, as described in the previous sections. 3. Select the volume to which a mirror is to be added by clicking the LEFT button on its icon. 4. Designate enough disks (not already being used by the volume itself) to be used for the new mirror. Ensure that the disks have sufficient free space to accommodate their portion of the desired length of the new volume by clicking the RIGHT button on an unobscured portion of each disk icon to access its VM disk properties form, then checking the value in the Maximum free space field. If 30 VERITAS Database Edition for Oracle Administrator’s Guide 2 there is sufficient free space, select the disks by clicking the MIDDLE button on their icons. If no disks are selected, disks with sufficient free space are automatically used. 5. From the Basic-Ops menu, select Volume Operations > Add Mirror. A submenu listing mirror (plex) layout options appears. 6. Select either Simple or Striped, depending on the desired mirror layout. Use the same layout as the original volume. The volume’s icon expands visibly and a new mirror appears within its borders. The new mirror layout depends on what was specified during the Add Mirror operation. 7. From the Basic-Ops menu, select Volume Operations > Add Log > Add a DRL log to allow fast volume resynchronization after a system crash. Figure 7 shows a simple, mirrored volume with a DRL log with a file system. Figure 7 Mirrored Volume with File System V datadg01-01 db04-01 datadg02-01 db04-02 datadg03-01 db04-03 db04 /db04 Creating a File System on an Existing Volume File systems can be created and placed on existing volumes, one file system per volume. A volume icon must be selected for this operation to succeed. To create a file system on an existing volume: 1. Go to the view window corresponding to the database disk group. 2. Select the volume on which to make the file system. 3. From the Basic-Ops menu, select File System Operations > Make File System. Setting Up the Databases 31 2 The Make File System form appears. Most of the form fields are already set to the defaults for making a file system on an existing volume. 4. Complete the Make File System form. Specify the mount point for the file system to be created. Use the default settings for the rest of the fields. 5. When the form is properly completed, select Apply to create the file system. If the file system is mounted, it is represented by the mount point, which appears below the volume icon. Mounting a File System A file system must be mounted before files can be accessed or created. In situations where a file system exists on a volume but is not currently mounted, that file system can be mounted at any time. A volume icon containing a valid, unmounted file system must be selected in order for this operation to succeed. Note: The Visual Administrator has no way of knowing whether or not a valid, unmounted file system already exists on a given volume, so you must be aware of the existence of an unmounted file system on a volume, as well as that file system’s characteristics. To mount a file system on a volume: 1. Go to the view window corresponding to the database disk group. 2. Select the volume containing the file system to be mounted. 3. From the Basic-Ops menu, select File System Operations > Mount. The Mount File System form appears. Most of the form fields are already set to the defaults for mounting a file system on a volume. 4. Complete the Mount File System form. Specify the mount point where the file system is to be mounted. Use the default settings for the rest of the fields. 5. When the form is properly completed, select Apply to mount the file system. When the file system is successfully mounted, it is represented by the mount point, which appears below the volume. 32 VERITAS Database Edition for Oracle Administrator’s Guide 2 Unmounting a File System A file system can be unmounted from a volume as long as the mount point is not currently in use. A volume icon containing a mounted file system must be selected in order for this operation to succeed. To unmount a file system: 1. Go to the view window corresponding to the database disk group. 2. Select the volume whose file system is to be unmounted. 3. From the Basic-Ops menu, select File System Operations > Unmount. The mount point should disappear from beneath the volume icon, indicating that the unmount operation has succeeded. If the mount point is in use, the unmount will fail. Checking a File System for Consistency A file system can be checked for consistency using fsck with the Visual Administrator. The file system must be unmounted at the time that the file system is checked. A volume icon containing an unmounted file system must be selected for this operation to succeed. To check a file system for consistency: 1. Go to the view window corresponding to the database disk group. 2. Select the volume whose unmounted file system is to be checked. 3. From the Basic-Ops menu, select File System Operations > Check File System (fsck). 4. The File System Check Form pops up. Click the LEFT button on Apply to start checking. 5. Access the Command Info Window to see if fsck had any problems. Highlight the fsck command line listed in the Command History section of this window and then examine any corresponding output in the Output of the Highlighted Command section. This should reveal any fsck-related errors or inconsistencies. If the fsck line is accompanied by the word DONE, there are not likely to be any errors or inconsistencies present. Setting Up the Databases 33 2 Note: The Visual Administrator assumes that fsck will not require user input. If user input is required, the Check File System operation will fail, and fsck must be run from the command line. Online File System Administration This section describes how to display the file system properties of a VxFS file system and how to grow a VxFS file system when is becomes too small for the database. Displaying File System Properties The File System Properties form displays useful details on the attributes of a particular file system. A single File System Properties form provides access to all file systems known to Visual Administrator at any given time. Since file systems do not have associated icons, file system properties cannot be accessed by clicking the RIGHT button on an icon. Instead, the File System Properties form is accessed in either of the following ways: • Click the RIGHT button on the file system mount point, which appears below the volume icon on which the file system is mounted. • Select Display Properties from the File System Operations menu. 34 VERITAS Database Edition for Oracle Administrator’s Guide 2 Figure 8 shows the File System Properties form. All fields in this properties form are read-only, so this form cannot be used to alter the attributes of file systems. Figure 8 File System Properties Form To display information on a specific file system with the File System Properties form: 1. Select a volume whose mounted file system’s properties are to be displayed. 2. From the Basic-Ops menu, select File System Operations > Display Properties. The File System Properties form appears. For detailed information on this form refer to online help. If a volume has been selected, the properties for the file system that resides on that volume are displayed by default. The properties displayed on the right side of the form correspond to the selected mount point name from the list on the upper left of the form. Selecting a different mount point from the list causes the properties of that file system to be displayed instead. Setting Up the Databases 35 2 Resizing a File System Resizing allows a file system to be grown or shrunk according to current needs. The resize operation involves resizing both the file system and its underlying volume. If new disk space is needed during the resize, it is allocated as necessary. It is not common to reduce the size of a file system that is used by a database. However, when a database grows, the underlying file system needs to grow as well to accommodate the database files. A volume icon containing a mounted file system must be selected in order for this operation to succeed. Resizing can take one of four forms: • Increase the volume and file system to the given length. • Increase the volume and file system by the given length. • Reduce the volume and file system to the given length. • Reduce the volume and file system by the given length. Resize a file system and its underlying volume as follows: 1. Go to the view window corresponding to the database disk group. 2. Select the volume whose file system is to be resized. 3. From the Basic-Ops menu, select File System Operations > Resize. The File System Resize form appears (see Figure 9). Most of the form fields are already set to the defaults for resizing a file system on a volume. Figure 9 36 File System Resize Form VERITAS Database Edition for Oracle Administrator’s Guide 2 4. Complete the File System Resize Form. Select the action to be taken and the size for change. For detailed information on this form, refer to online help or the VERITAS Volume Manager User’s Guide. 5. When the form is properly completed, select Apply to resize the file system. As the file system and underlying volume are resized, the corresponding volume icon may change visibly. Note that growing a volume alone will not change the size of the file system. Use the File System Operations to grow the file system and the underlying volume. Using the Command Line Interface To create a file system on a volume, you need to create a volume first. You can use the Visual Administrator or the vxassist command to create a volume. Creating a Volume The vxassist command is used to create volumes. For example, to create a simple volume db01 of 100 megabytes on the disk datadg01, enter the following: # vxassist -g datadg -U fsgen make db01 100m datadg01 A virtual device /dev/vx/rdsk/datadg/db01 is then created. This virtual device can then be accessed as a regular raw device to create a file system. To create a striped volume db02 of 6 gigabytes across 4 disks (on datadg01, datadg02, datadg03, and datadg04) using the default stripe unit size of 64 kilobytes, enter the following: # vxassist -g datadg -U fsgen make db02 6g \ layout=stripe nstripe=4 datadg01 datadg02 \ datadg03 datadg04 For more information on the vxassist command, refer to the vxassist(1M) manual page. Setting Up the Databases 37 2 Creating a File System A file system can be created on a volume, but cannot be larger than the volume on which it is created. Always specify vxfs as the file system type to take advantage of online administration and fast recovery of the VxFS file system. To create a VxFS file system on an existing volume, you use the following command syntax: mkfs -F vxfs [generic_options] [-o specific_options] special_file [size] where vxfs is the file system type, generic_options are the options common to most file systems, specific_options are options specific to the VxFS file system, special_file is the full pathname of the volume on which to create the file system (such as /dev/vx/rdsk/datadg/db02), and size is the size of the new file system. The VxFS file system is tuned for most database configurations. Use the default options when you create a file system for your database. For more information about the options and variables available for use with the mkfs command, refer to the mkfs(1M) manual page. To create a VxFS file system on the newly-created db02 volume, enter: # mkfs -F vxfs /dev/vx/rdsk/datadg/db02 Note: The size of the file system will be calculated automatically to be the same size of the volume on which the file system is created. A message similar to this appears: version 4 layout 12288000 sectors, 6144000 blocks of size 1024, log size 1024 blocks unlimited inodes, 6139904 data blocks, 6139791 free data blocks 47 allocation units of 131136 blocks, 131072 data blocks last allocation unit has 110592 data blocks first allocation unit starts at block 1088 overhead per allocation unit is 64 blocks initial allocation overhead is 113 blocks The newly-created file system can now be mounted. 38 VERITAS Database Edition for Oracle Administrator’s Guide 2 Mounting a File System After creating the file system, mount it using the following command syntax: mount -F vxfs [generic_options][-o specific_options] \ block_special mount_point where block_special is a block special device and mount_point is the location where the file system will be mounted. Use the default mount options for your databases. To mount the file system /dev/vx/dsk/datadg/db02, enter: # mount -F vxfs /dev/vx/dsk/datadg/db02 /db02 Unmounting a File System If you no longer need to access the data in a file system, unmount it with the following command syntax: umount block_special | mount_point where block_special is a block special device and mount_point is the location where the file system is mounted. To unmount the file system /db02, enter: # umount /db02 Online File System Administration A VxFS file system can be resized using the fsadm command or the vxresize command. The vxresize command can be used to grow both a file system and its underlying volume to a specified new volume length. Refer to the VERITAS File System Quick Start Guide for information on how to perform common file system tasks using fsadm and other file system commands. For more information on the vxresize command, refer to the vxresize(1M) manual page. Setting Up the Databases 39 2 Creating Database Files Quick I/O for Databases allows databases to access regular files on a VxFS file system as raw character devices, improving transaction processing throughput for Oracle databases. Unlike raw devices, Quick I/O files can still be managed as regular UNIX files. There are two requirements to use Quick I/O: 1. Files must be preallocated on a VxFS file system. 2. The file must be accessed via its Quick I/O name extension (::cdev:vxfs:). The file must be preallocated because the file cannot be extended through writes via its Quick I/O interface. This preallocation can be done using the qiomkfile command. In a VxFS file system, a file can be accessed using two types of interface: regular file and device file. The device file interface allows a regular file to be accessed as a raw character device. This is achieved by using the Quick I/O naming extension of ::cdev:vxfs: while accessing a regular file. For example, a file named system.dbf can be accessed as a raw character device when the name system.dbf::cdev:vxfs: is used for database access. Quick I/O (VRTSqio) must be loaded before a regular file can be accessed through the Quick I/O interface. Note: By default, Quick I/O (VRTSqio) is loaded on the first reference to a VxFS file system. If the it is unloaded for any reason, a Quick I/O file name will be treated as a regular file name and access via this file name will fail. To check whether or not Quick I/O was successfully installed and enabled, use the command ls -lL on a Quick I/O file. The output looks like this: $ ls -lL cust.dbf crw-r--r-- 1 oracle dba 45, 1 May 8 13:42 cust.dbf where the first character, c, indicates it is a raw character device file, and the size field is replaced with a pair of (major, minor) numbers. If you see a No such file or directory message, Quick I/O is not installed properly or it may not have a valid license key. Pre-allocating database files using setext or qiomkfile causes the space for the files to be allocated contiguously. The file system space reservation algorithms attempt to allocate space for the entire file as a single contiguous extent. When this is not possible due to the space availability on the file 40 VERITAS Database Edition for Oracle Administrator’s Guide 2 system, the file is created as a series of direct extents. Accessing a file via direct extents is inherently faster than accessing the same data using indirect extents. Internal tests have shown 5-8% performance degradation in OLTP throughput when the access is via indirect extents. In addition, this type of pre-allocation sets the fragmentation of the file system to zero. The following sections describe how to set up database files to use the Quick I/O feature. If you want to create a new database, use the procedure described in “Creating a Database File Using qiomkfile.” If your database is already using UNIX files, follow the procedure in “Accessing Regular UNIX Files as Quick I/O Files.” Creating a Database File Using qiomkfile Note: For existing Database Edition for Oracle users, the qiomkfile command replaces the vxmkcdev command. The qiomkfile command creates two files: a regular file using preallocated, contiguous space and a symbolic link pointing to the Quick I/O file name (the ::cdev:vxfs: name extension). For example, to create a database file cust.dbf on the VxFS file system /db02, use the commands: $ qiomkfile -h 2k -s 100m /db02/cust.dbf $ svrmgrl SVRMGR> connect internal SVRMGR> create tablespace cust datafile ’/db02/cust.dbf’ SVRMGR> size 100M; SVRMGR> exit; Setting Up the Databases 41 2 The size of the file that is preallocated is the total size of the file (including the header) rounded to the nearest multiple of the file system block size. The qiomkfile command has five options: -h (For Oracle database files.) Creates a file with additional space allocated for the Oracle header. -s Preallocates space for a file. -e (For Oracle database files.) Extends the file by a specified amount to allow Oracle tablespace resizing. -r (For Oracle database files.) Increases the file to a specified size to allow Oracle tablespace resizing. -a Creates a symbolic link with an absolute pathname for a specified file. The default is to create a symbolic link with a relative pathname. You can specify file size in terms of bytes, kilobytes, megabytes, gigabytes, or sectors (512 bytes) by adding a k, K, m, M, g, G, s or S suffix, respectively. The default is bytes. Unlike the VxFS setext command, which requires superuser privileges, any user who has read/write permissions can run qiomkfile to create the files. The VxFS setext command can also be used to preallocate space for database files. Unlike the setext command which requires superuser privileges, the qiomkfile command can be executed by any user who has the read/write permission to create the Oracle database files. When qiomkfile is used to create database files, the command must be issued by the oracle owner (typically the user oracle) of the database. qiomkfile creates two files: a regular file with preallocated, contiguous space; and a symbolic link pointing to the Quick I/O name extension. For example, to create a 100 MB file named dbfile in /database, enter: $ qiomkfile -s 100m /database/dbfile In this example, the first file created is a regular file named /database/.dbfile (which has the real space allocated). 42 VERITAS Database Edition for Oracle Administrator’s Guide 2 The second file is a symbolic link named /database/dbfile. This is a relative link to /database/.dbfile via the Quick I/O interface, that is, to .dbfile::cdev:vxfs:. This allows .dbfile to be accessed by any database or application as a raw character device. To check the results, enter: $ ls -al -rw-r--r-- 1 oracle dba 104857600 Oct 22 15:03 .dbfile lrwxrwxrwx 1 oracle dba 19 Oct 22 15:03 dbfile -> \ .dbfile::cdev:vxfs: or: $ ls -lL crw-r----- 1 oracle dba 43, -rw-r--r-- 1 oracle dba 10485760 0 Aug 22 13:46 dbfile Aug 22 13:46 .dbfile If you specify the -a option, an absolute pathname (refer to “Using Absolute or Relative Pathnames”) is used so /database/dbfile points to /database/.dbfile::cdev:vxfs:. To check the results, enter: $ ls -al -rw-r--r-- 1 oracle dba 104857600 Oct 22 15:05 .dbfile lrwxrwxrwx 1 oracle dba 31 Oct 22 15:05 dbfile -> /database/.dbfile::cdev:vxfs: Refer to the qiomkfile(1) manual page for more information. Accessing Regular UNIX Files as Quick I/O Files Another way to use Quick I/O for Databases is to allow regular UNIX files to be accessed using the Quick I/O (::cdev:vxfs:) name extension. It is recommended to create a symbolic link for each database file and use the symbolic links to access the database files as Quick I/O files. The following commands allow a regular UNIX file system.dbf to be accessed as a Quick I/O file on the VxFS file system /db02: $ cd /db02 $ mv cust.dbf .cust.dbf $ ln -s .cust.dbf::cdev:vxfs: cust.dbf Setting Up the Databases 43 2 You can explicitly specify the Quick I/O file names in the SQL statements as shown in this SQL script: $ svrmgrl SVRMGR> connect internal SVRMGR> alter tablespace cust offline; SVRMGR> alter database rename file ’/db02/cust.dbf’ \ to ’/db02/cust.dbf::cdev:vxfs:’; SVRMGR> alter tablespace cust online; For best performance, use symbolic links for easy file system management and location transparency of database files. However, using symbolic links results in two sets of files to manage (for example, during database backup and restore). This example shows the symbolic links created: $ ls -lo .cust.dbf cust.dbf -rw-r--r-lrwxrwxrwx 1 oracle 10485760 May 8 13:42 .cust.dbf 1 oracle 19 May 8 13:43 cust.dbf -> .cust.dbf::cdev:vxfs: Converting Oracle Database Files The scripts getdbfiles.sh and mkqio.sh are provided to change Oracle database files to use Quick I/O. The database files must be on VxFS file systems before they can be changed. The getdbfiles.sh script is a shell script that needs to be run by the Oracle DBA (with appropriate user ID) of the database instance while the instance is up and running. This script extracts the filenames from the system tables of the database and stores the filenames in a file called mkqio.dat. Alternatively, you can manually create the mkqio.dat file containing the Oracle database filenames to convert for use with Quick I/O. The mkqio.sh script processes a list of filenames in the file mkqio.dat and converts them to use Quick I/O. This script must also be run by the Oracle user of the database instance to avoid any permission problems. 44 VERITAS Database Edition for Oracle Administrator’s Guide 2 Note: After running the getdbfiles.sh script, shut down the database before running the mkqio.sh script. Without any options specified, the mkqio.sh converts the list of files stored in the mkqio.dat file to use the Quick I/O interface. mkqio.sh results in two files: a regular file (the original file renamed) and a symbolic link pointing to the Quick I/O name. For example, while processing a file named cust.dbf, it renames the file cust.dbf to .cust.dbf and creates a symbolic link file with the Quick I/O (::cdev:vxfs:) extension. By default, the symbolic link uses a relative pathname. The mkqio.sh script also accepts the following options: -h Displays a help message. -u Changes Quick I/O files back to regular UNIX files. -u can undo any of the changes made by mkqio.sh. -a Changes regular files to Quick I/O files using absolute pathnames. -a is used when the symbolic links must point to absolute pathnames (for example, at a site that uses SAP on Oracle). CAUTION ! Although SAP requires Quick I/O files to use absolute links, using too long a pathname in the symbolic link could cause problems while doing an online backup with SAP. Typical SAP installations configure their SAP data files as follows: /oracle/SID/sapdata/loadi_1/loadi.data1 /oracle/SID/sapdata/docud_1/docud.data1 Configuring these as Quick I/O files using absolute links causes the SAP command brconnect to fail with a core dump. brconnect is called by brbackup when an online backup is done with the backup device type set to util_file_online. You can avoid this problem by using two shorter links. For example, for the file docud.data1, you can use: $ cd /oracle/SID/sapdata/docud_1 $ mv docud.data1 .docud.data1 Setting Up the Databases 45 2 $ ln -s ‘pwd‘/loadi.data1::cdev:vxfs: \ /dev/vxfs/docud.data1 $ ln -s /dev/vxfs/docud.data1 docud.data1 These file name conversions must be done when the SAP instance is down. The mkqio.sh script converts only regular files on VxFS file systems or links that point to regular files on VxFS file systems. The mkqio.sh script exits and prints an error message if any of the database files is not on a VxFS file system. Remove these non-VxFS files from the mkqio.dat file before running the mkqio.sh script. After the mkqio.sh script is finished, restart the Oracle database to access these database files via Quick I/O. Using Absolute or Relative Pathnames By default, qiomkfile and mkqio.sh use relative pathnames when creating/ converting to Quick I/O files. This is because having the links and the datafiles in the same directory makes them easier to manage for backup and restore purposes. In addition, if the files need to be moved to a different file system, the links would not need to be updated to reflect the change in mount point. But some applications such as SAP do not handle relative links properly and work only if absolute link names are used. Hence, the commands qiomkfile and mkqio.sh provide an option to create Quick I/O files with absolute path names. If absolute pathnames are not required, it is recommened that relative link names be used for Quick I/O files. Extending and Resizing the Database File Oracle supports an autoextend feature for database files, which extends a datafile by a pre-specified size up to a pre-specified maximum size. This feature works transparently if the datafiles are built on top of a file system, provided the file system has enough space. Quick I/O files can be managed as regular UNIX files with few minor exceptions; they have the same limitations with using Oracle autoextend as do raw devices. If Oracle datafiles are built on raw devices, then using autoextend without errors requires the raw device be preallocated to be at least as big as the maximum growth size expected for this datafile. 46 VERITAS Database Edition for Oracle Administrator’s Guide 2 For example, the current size of a datafile emp.dbf is 100M, and this is expected to accommodate the growth of the tables, at the current usage level, for the next three months. However, suppose this datafile is expected to grow to a maximum size of 300M. To accommodate this growth using the Oracle autoextend feature, the raw device must be extensible to 300M. In most environments, resizing raw devices is not easy and is not a practical option because it usually requires shutting down the database. The only option would be to size this device to be 300M and lock up 200M in the raw device making this storage unavailable for use by other applications. The only exception to this is if the raw device used is a VxVM volume, in which case it is possible to easily resize the volume. As described earlier, Quick I/O files do need to be preallocated files. The value of using Quick I/O is that the Quick I/O files are built on top of the file system and hence files can be grown at will. The DBA needs to monitor the free space available in the Oracle datafiles and use the qiomkfile command to grow the underlying Quick I/O file online as and when needed. This would typically be the time when the Oracle Datafile is about 80-90% full. Growing the Quick I/O file is a fast, online operation that does not need the database to be shut down. This way, there is no need to lock out additional disk space for Quick I/O files, and the free space on the file system is available for use by any other application. Use the following sequence to grow the Quick I/O file. The free space currently available in an Oracle tablespace, CUST can be checked using the following Oracle SQL Command: $ svrmgrl < connect internal SVRMGR> startup mount SVRMGR> alter database rename file filename to newfilename Repeat this step for each datafile. 7. Complete the database startup: SVRMGR> alter database open 54 VERITAS Database Edition for Oracle Administrator’s Guide Performance and Tuning 3 Introduction This chapter provides performance tuning tips that can be used to improve database performance. Tuning tips and information provided in this chapter should be used in conjunction with the following Oracle documents: • Oracle for Sun Performance Tuning Tips—covers Solaris specific tuning tips • Oracle for UNIX Performance Tuning Tips—covers generic UNIX tuning tips • Oracle8 Tuning—covers Oracle generic tuning tips for Oracle 8 • Other generic Oracle documentation that deals with Oracle tuning issues • Chapter 2 “VxVM Performance Monitoring” of the VERITAS Volume Manager System Administrator’s Guide. Topics covered in this chapter include: • Tuning VxFS • Tuning Cached Quick I/O • Tuning Oracle Databases 55 3 Tuning VxFS As a generic UNIX file system, VxFS provides a rich set of tuning options to optimize file system performance for different application workloads. Most of these tuning options will have very little or no impact on database performance when Quick I/O for Databases is used. It is recommended that the general VxFS defaults be used when the VxFS file system is used for databases. This section explains how to obtain and use file I/O statistics. Obtaining File I/O Statistics Note: For existing Database for Oracle users, the qiostat command replaces the vxfddstat command; however, the vxfddstat command will also work. The qiostat utility provides access to information for activity on Quick I/O files on vxfs file systems. qiostat reports statistics that reflect the activity levels of Quick I/O files from the time when the files are first opened as raw character devices. The accumulated statistics are reset once the last file open on the Quick I/O file is closed. By default, the qiostat command displays the following I/O statistics: • A count of operations • The number of data blocks (sectors) transferred • The average operation time When Cached Quick I/O is used, it also displays the caching statistics when the -l (the long format) option is selected. For detailed information on available options, refer to the qiostat(1M) manual page. The following is an example of qiostat output: OPERATIONS FILE NAME /db02/balance.dbf /db02/system.dbf /db02/cust.dbf 56 FILE BLOCKS AVG TIME(ms) READ WRITE READ WRITE READ WRITE 17128 9634 68509 38536 24.8 0.4 6 1 21 4 10.0 0.0 62552 38498 250213 153992 21.9 0.4 VERITAS Database Edition for Oracle Administrator’s Guide 3 Using File I/O Statistics Data Once performance data has been gathered, it can be used to adjust the system configuration to make the most efficient use of system resources. Examination of the I/O statistics may suggest reconfiguration. There are three primary statistics to look at: file I/O activity, volume I/O activity, and raw disk I/O activity. If your database is using one file system on a striped volume, you may only need to pay attention to the file I/O statistics. If you have more than one file system, you may need to monitor volume I/O activity as well. Before obtaining statistics, you should consider clearing (resetting) all existing statistics. Use the command qiostat -r to clear all statistics (and use vxstat -r to clear volume I/O statistics if you are monitoring volume I/O as well). After clearing the statistics, let the database run for a while and then display the accumulated statistics. Try to let the database run during typical database workload. When monitoring a database with many users, try to let statistics accumulate during normal use for several hours during the day. To display active file I/O statistics, use the qiostat command and specify an interval (using -i) for displaying the statistics for some period of time. Here’s a sample of the output: OPERATIONS FILENAME BLOCKS AVG TIME(ms) READ WRITE READ WRITE READ WRITE /db02/cust.dbf 21 36 872 144 22.8 55.6 /db02/hist.dbf 0 1 0 4 0.0 10.0 /db02/nord1.dbf 10 14 40 56 21.0 5.0 /db02/ord1.dbf 19 16 76 64 17.4 56.2 /db02/ordl1.dbf 189 41 756 164 21.1 50.0 /db02/roll1.dbf 0 50 0 200 0.0 49.0 /db02/stk.dbf 1614 38 6456 952 19.3 46.5 /db02/sys1.dbf 0 0 0 0 0.0 0.0 /db02/temp1.dbf 0 0 0 0 0.0 0.0 /db02/ware1.dbf 3 14 12 56 23.3 44.3 /logs/log1.dbf 0 0 0 0 0.0 0.0 /logs/log2.dbf 0 217 0 225 0.0 6.8 Performance and Tuning 57 3 This output helps to identify files with an unusually large number of operations or excessive read or write times. When this happens, you may want to experiment to move the “hot” files or busy file systems to different disks or to change the layout to balance the I/O load. Tuning Cached Quick I/O Deciding which files would benefit from Cached Quick I/O is an iterative process that varies with each application. Turning caching on for all the database files may cause degraded performance due to double buffer copying. You should first collect and analyze the caching statistics before you turn the Cached Quick I/O on for any of the database files. Cached Quick I/O can be enabled/disabled while the database is online and hence can be easily customized for the current environment. For instance, Cached Quick I/O could be disabled for certain files that store historical performance data during normal working hours when the database supports a large number of concurrent users. Caching could be enabled at off-peak hours when the same files are scanned for the purpose of generating reports. Scripts could be used to automate enabling and disabling Cached Quick I/O on a per file basis, favoring different sets of files. In the hands of the DBA, who understands the applications and the types of workloads on the database at any given time, this is a powerful tool that can be used to maximize performance by fully utilizing all of the resources available on the system. There is no one magic formula that applies to all. The following methodology can be used to arrive at a list of possible candidate files for Cached Quick I/O to improve your database performance. 1. Turn on caching for all files in the file systems being used with the vxtunefs command. 2. Run qiostat -r to reset the counters. 3. Run the machine under full normal load for a period of time. The period should be sufficiently long to get a good usage sample. Verify that the system has reached a steady state before you measure throughput. It is common to see a longer ramp-up time when using Cached Quick I/O, compared to Quick I/O. 58 VERITAS Database Edition for Oracle Administrator’s Guide 3 4. Run qiostat -l to report the caching statistics. For example $ qiostat -l /db02/*.dbf 5. Analyze the output to find out where the cache hit ratio is above a certain threshold (for example, 20%). Also, study the numbers reported for the read and write operations. The biggest gains with Cached Quick I/O can be realized when used for files that are read more than written. The output looks like something like this: OPERATIONS FILE BLOCKS AVG TIME(ms) CACHE STATISTICS FILE NAME /db02/cust.dbf /db02/system.dbf /db02/stk.dbf READ WRITE READ CREAD PREAD HIT RATIO 17128 9634 68509 17124 15728 8.2 6 1 21 6 6 0.0 62552 38498 250213 62567 49060 21.6 WRITE READ WRITE 38536 24.8 0.4 4 10.0 0.0 153992 21.9 0.4 From this example, it is clear that the file /db02/system.dbf does not benefit from the caching. In addition, there is very little I/O performed on the file during the sampling duration. However, the file /db02/stk.dbf has a cache hit ratio of 21.6%, which means the database can benefit from caching the file in the file system. When you compare the number of reads and writes for this file, you see that the number of reads is roughly twice the number of writes. Based on these two statistics, this is a prime candidate for Cached Quick I/O. We can then turn off Cached Quick I/O for each file using the qioadmin command described in the section “Enabling/Disabling Cached Quick I/O for Individual Files” in Chapter 2. It is recommended that such experiments be run on test/simulated loads before implementation in a production environment. Performance and Tuning 59 3 Note: Always check the setting of the flag qio_cache_enable via vxtunefs along with the individual cache advisories for each file to verify caching. The vxtunefs command can be also used to determine which values to use. For example, to display the I/O characteristics of a vxfs file system, enter the command: # vxtunefs /dev/vx/dsk/datadg/db02 A message similar to the following appears: Filesystem i/o parameters for /db02 read_pref_io = 65536 read_nstream = 4 read_unit_io = 65536 write_pref_io = 65536 write_nstream = 4 write_unit_io = 65536 pref_strength = 10 buf_breakup_size = 131072 discovered_direct_iosz = 262144 max_direct_iosz = 2097152 default_indir_size = 8192 qio_cache_enable = 0 max_diskq = 1572864 initial_extent_size = 8 max_seqio_extent_size = 2048 For a description of these parameters and the tuning instructions, refer to the vxtunefs(1M) manual page. Tuning VxVM The Volume Manager is tuned for most configurations ranging from small systems to larger servers. On smaller systems with less than about a hundred drives, tuning should not be necessary and the Volume Manager should be capable of adopting reasonable defaults for all configuration parameters. On 60 VERITAS Database Edition for Oracle Administrator’s Guide 3 very large systems, however, there may be configurations that require additional tuning of these parameters, both for capacity and performance reasons. For information on tuning the Volume Manager, refer to the “Tuning the Volume Manager” section of the “VxVM Performance Monitoring” chapter in the VERITAS Volume Manager System Administrator’s Guide. Obtaining Volume I/O Statistics If your database is created on a single file system that is created on a single volume, there is typically no need to monitor the volume I/O statistics. If your database is created on multiple file systems on multiple volumes, or the volume configurations have changed over time, it may be necessary to monitor the volume I/O statistics for the databases. The vxstat utility provides access to information for activity on volumes and their components under VxVM control. vxstat reports statistics that reflect the activity levels of VxVM objects since boot time. Statistics for a specific VxVM object or all objects can be displayed at one time. Use the -g option to specify the database disk group to report statistics for objects in the database disk group. VxVM records the following I/O statistics for reporting: • A count of operations • The number of blocks transferred • The average operation time VxVM records the preceding three pieces of information for logical I/Os, including reads, writes, atomic copies, verified reads, and verified writes for each volume and its components. VxVM also maintains other statistical data such as read failures, write failures, corrected read failures, corrected write failures, and so on. In addition to displaying volume statistics, the vxstat command is capable of displaying more detailed statistics on the components that form the volume. For detailed information on available options, refer to the vxstat(1M) manual page. vxstat is also capable of resetting the statistics information to zero. Use the command vxstat -r to clear all statistics. This can be done for all objects or for only those objects that are specified. Resetting just prior to a particular operation makes it possible to measure the impact of that particular operation afterwards. Performance and Tuning 61 3 The following is an example of vxstat output: OPERATIONS TYP NAME READ BLOCKS WRITE READ vol log2 0 6312 vol db02 2892318 3399730 0283759 0 AVG TIME(ms)) WRITE 79836 7852514 READ .0 20.6 WRITE 0.2 25.5 Additional volume statistics are available for RAID-5 configurations. Refer to the vxstat(1M) manual page for more information. Tuning Oracle Databases To achieve optimal performance on your Oracle database, the database may need to be tuned to work together with VxFS and VxVM. This section lists some of the Oracle parameters that may be tuned to improve your Oracle database performance along with tuning tips. Increasing DB_BLOCK_BUFFERS The UNIX buffer cache plays an important part in performance when ufs is used. However, when Quick I/O for Databases is used, the database cache should be tuned as if raw devices are being used. More memory can be allocated for the database buffer cache. This can significantly improve database performance. The parameter DB_BLOCK_BUFFERS is the most important determinant of database performance. When using Quick I/O for Databases, you can allocate more memory for the Oracle buffer cache by increasing the value of this parameter. Refer to the Oracle for Sun Performance Tuning Tips and Oracle for UNIX Performance Tuning Tips documents for more information on how to tune the database and UNIX buffer caches for optimal database performance. Tuning DB_FILE_MULTIBLOCK_READ_COUNT The DB_FILE_MULTIBLOCK_READ_COUNT parameter specifies (during a sequential scan) the maximum number of blocks Oracle reads in one I/O operation. When the file system is created on a striped or RAID-5 volume, set this parameter to a value that is a multiple of the full stripe size divided by 62 VERITAS Database Edition for Oracle Administrator’s Guide 3 DB_BLOCK_SIZE. Using a full stripe size allows the read operations to take advantage of the full bandwidth of the striped disks during sequential table scan. Set the DB_FILE_MULTIBLOCK_READ_COUNT to a value that is a multiple of read_pref_io*read_nstream/DB_BLOCK_SIZE, but it should not exceed the value of max_direct_iosz/DB_BLOCK_SIZE. Use the vxtunefs command to display the value of read_pref_io, read_nstream, and max_direct_iosz. Tuning Sequential Table Scans/DSS Queries Quick I/O in its default mode performs all I/O as direct I/O. In the case of single-threaded sequential scans, where a single process reads a file sequentially, using buffered reads may yield better performance/ Because the file system detects these sequential reads and performs read-aheads, the next few blocks that are requested by Oracle are readily available in the system buffer cache and are simply copied to the Oracle SGA. Since access from memory is inherently faster than access from disk, this achieves a significant reduction in response time. To handle large sequential scans when using Quick I/O, one of two methods is available to improve performance: 1. Use the Oracle parallel query process to break the single large scan into multiple smaller scans. This is done by setting the Oracle parameters parallel_max_servers, parallel_min_servers, and sort_area_size suitably. 2. The second method is to enable Cached Quick I/O for the files that would be read by the Oracle sequential scan process. Cached Quick I/O uses the buffered reads and the file system read-ahead helps lower response times. Performance and Tuning 63 3 Tuning Oracle8 Databases The following database parameters are all specific to Oracle version 8. Setting DISK_ASYNCH_IO to TRUE Asynchronous I/O enables the Oracle DBWR process to schedule multiple I/Os without waiting for the I/O to complete. When the I/O completes, the kernel notifies the DBWR via an interrupt. Quick I/O supports kernel asynchronous I/O (kaio), thus enabling this flag lets Oracle take advantage of kaio. This parameter is set to TRUE by default. Setting DBWR_IO_SLAVES to 0 Quick I/O provides support for kernel asynchronous I/O and hence eliminates the need for multiple db writers or db writer slaves. This parameter is set to 0 by default. Tuning Oracle7 Databases The following database parameters are all specific to Oracle version 7. Setting USE_READV to FALSE The readv() system call allows multiple I/O requests to be put into a list that can be treated as a single I/O request, which reduces the CPU overhead for buffer copying. In the ORACLE for Sun Performance Tuning Tips document, Oracle recommends setting the use_readv parameter to TRUE to improve sequential scan performance when the ufs file system is used. However, when raw I/O is used, using readv actually slows down the sequential scan performance. When Quick I/O for Databases is used, files are treated by Oracle databases as raw character devices and the use_readv flag should be set to FALSE. The default setting of this flag is FALSE. Setting USE_ASYNC_IO to TRUE Asynchronous I/O enables the Oracle DBWR process to schedule multiple I/Os without waiting for the I/O to complete. When the I/O completes, the kernel notifies the DBWR via an interrupt. Quick I/O supports kernel asynchronous 64 VERITAS Database Edition for Oracle Administrator’s Guide 3 I/O (kaio), thus enabling this flag lets Oracle take advantage of kaio. Setting this avoids having to configure multiple DBWR processes. This parameter is set to FALSE by default. Setting DB_WRITERS to 1 Quick I/O provides support for kernel asynchronous I/O eliminating the need for multiple db writers. This parameter is set to 1 by default. Performance and Tuning 65 3 66 VERITAS Database Edition for Oracle Administrator’s Guide Database Backup and Restore 4 Introduction This chapter describes how to use the snapshot and storage checkpoint facilities provided by VxFS, VxVM, and NetBackup to support Oracle database backup. Topics covered in this chapter include: • Database Backup • Database Backup Using Snapshot File Systems • Database Backup Using Snapshot Volumes • Database Backup Using Storage Checkpoints Database Backup One of your major responsibilities as the Database Administrator (DBA) is to prepare for the possibility of database failure due to a hardware, software, process, or system failure or a user error. Any occurrence of a failure (for example, a disk crash) may prevent reading or writing to database files. Such a failure can result in inconsistent data. To recover fully from media failure, it is necessary to restore a backup of the database and to apply redo log files up to the time the media failure occurred. To ensure that you can perform a successful recovery whenever it may be needed, you must back up the database regularly. 67 4 Note: If you’re using cached Quick I/O, be sure to back up the files /etc/vx/tunefstab and /etc/vx/qioadmin because you’ll need the settings in them after a restore. Otherwise it will be necessary to reapply these settings manually after every time the file system is mounted. A database backup is a copy of the database at a particular point in time. A database backup is merely a copy of a group of database files used by a database. Th three basic backup techniques offered by the Database Edition for Oracle: are: • Full- and block-level incremental backups with Storage Checkpoints and NetBackup (Note that Storage Checkpoints enable BLI Backups; however, these backups are transparent to users and are only accessible to and managed by NetBackup.) • Full database backup using snapshot file systems • Full database backup using snapshot volumes NetBackup uses the VxFS Storage Checkpoint facility which keeps track of changed data blocks of a database to perform block-level incremental backups. This reduces the time required for a database backup significantly. For example, if the total number of blocks changed in a database since the last full backup is only 10% of the total database size, the time required to do any “full image” backup using incremental backup will take about 10% of the original backup time and use about 10% of the tapes. Database backup using NetBackup’s block-level incremental backup will be a significant saving in time and tapes as well as CPU and network overhead during backups. You can also use the dd, tar, or cpio commands to perform your database backup using snapshot file systems or snapshot volumes. Database backups using snapshot file systems or snapshot volumes are no different than database backups using regular file systems or regular volumes. However, since the duration for performing a database snapshot is typically very fast, a database can return to its normal operation much faster than it could with traditional backup approaches. For example, if you are using a snapshot volume or snapshot file system to do an offline backup, database users will only experience a very brief service interruption while the snapshot is being taken. A consistent backup can then 68 VERITAS Database Edition for Oracle Administrator’s Guide 4 be performed on the snapshot while the database continues working. Using the traditional approach, the database may be unavailable while the entire database is being backed up. Note: Test the backup and recovery strategies in a test environment before using them on a production system. Using NetBackup for Block-level Incremental Backup VERITAS NetBackup provides for high performance, online backup of database that must be available on a 7x24 basis, as well as cold database backups. NetBackup lets system and database administrators back up and restore database files and directories. Administrators can set up schedules for automatic, unattended, online (hot), and offline (cold) database backup. These backups are managed entirely by the NetBackup server. The administrator can also manually back up database files from any of the NetBackup clients. Client users can perform database backups and restores from their client system on demand. When used with the Database Edition for Oracle, NetBackup uses the VxFS Storage Checkpoint facility to identify the blocks of data that have been changed since the last backup. This allows NetBackup to back up only the changed blocks, not the entire database, for an incremental backup. The incremental backup is done at the block level which means only the changed blocks not the changed files will be backed up. This significantly reduces the time required to perform a backup and the number of tapes needed to store the backup image. In addition, block-level incremental backups significantly lower CPU and network bandwidth consumption during backups. Block-level incremental backup is particularly useful in large database environments where a database can be hundreds of gigabytes or terabytes. Using the traditional backup methods, for an off-line database backup, any change in the database—no matter how small—would require the entire database to be backed up. Using block-level incremental backup, only those data blocks that have been modified will need to be backed up. Database Backup and Restore 69 4 Here’s an example backup schedule: Sunday Full backup Monday Tuesday Wednesday Thursday Friday Saturday Differential Differential Cumulative Differential Differential Differential Incremental Incremental Incremental Incremental Incremental Incremental Data blocks changed since Sunday’s full backup Data blocks changed since Wednesday’s backup Data blocks changed since Thursday’s backup Data blocks changed since Friday’s backup The above schedule will yield backups containing: Full database image (all data blocks up to Sunday) Only data blocks changed since Sunday’s full backup Data blocks changed since Monday’s backup During a restore, NetBackup first performs a restore from the last full backup that occurred before the target date and time, followed by a number of restores from the incremental backups that have occurred since the last full backup. All these operations are handled automatically by NetBackup. Using the above backup schedule, if a database file is found to be corrupted on Wednesday, the database administrator can direct NetBackup to restore the file from Tuesday’s image. NetBackup will first restore the file in its entirety from Sunday’s full backup, followed by restoring the data blocks from the incremental backups performed on Monday and Tuesday. Storage Checkpoint Database Backup In addition to block-level incremental backup, NetBackup also uses the VxFS Storage Checkpoint facility to take a snapshot of the database image for a whole database backup. A storage checkpoint database backup combines the consistency of cold database backups with online accessibility. The storage checkpoint backup allows the Oracle instance to be brought back online as soon as the storage checkpoint image is taken. The Oracle instance can remain online processing transactions while NetBackup is backing up the database using the storage checkpoint image. The backup can be full backup or blocklevel incremental backup. This storage checkpoint database backup method can dramatically reduce the database downtime required for a consistent backup image. A storage checkpoint database backup is different from the traditional Oracle hot (or online) backup. A storage checkpoint database backup is also an online database backup, but it requires the database to be brought down for a short period of time to take a snapshot or storage checkpoint database image. The 70 VERITAS Database Edition for Oracle Administrator’s Guide 4 database can be restarted immediately after the storage checkpoint is taken. A backup image from a storage checkpoint database backup is equivalent to a backup image from a cold database backup. Using Snapshot File Systems for Backup VxFS provides a mechanism for taking snapshot images of mounted file systems, which is useful for making database backups. The snapshot file system is an exact image of the original file system, which is referred to as the snapped file system. The snapshot is a consistent view of the snapped file system at the point in time the snapshot is made. You can select any convenient time at which to create a snapshot file system for database backup. The steps for using snapshot file systems for database backup are: 1. Prepare the database for either online or offline backup. 2. Create snapshot file systems for all file systems that contain the database. 3. Resume normal database operation. 4. Start backing up the snapshot file systems. The time required to create a snapshot file system is rather short (typically a couple of seconds). Once a snapshot file system is created and mounted, selected database files or the entire file system can be backed up from the snapshot, using standard backup utilities. For details on database backup using snapshot file systems, refer to “Database Backup Using Snapshot File Systems.” Using Snapshot Volumes for Backup VxVM provides a snapshot facility to allow backups of volumes based on disk mirroring. The steps for using snapshot volumes for database backup are: 1. Create snapshot mirrors for volumes that contain the database. 2. Prepare the database for either online or offline backup. 3. Create snapshot volumes by breaking up snapshot mirrors. Database Backup and Restore 71 4 4. Resume normal database operation. 5. Start backing up the snapshot volumes. Once the snapshot mirror is created and data on the mirrors is synchronized, you can then select a convenient time to create a snapshot volume as an image of the database. The time required to create a snapshot volume (in Step 3 above) is typically very short (typically less than a minute); however, the time involved in creating and synchronizing a snapshot mirror may be long (depending on the size of the original volume). For details on database backup using snapshot file systems, refer to “Database Backup Using Snapshot Volumes.” Choosing Between NetBackup, VxFS Snapshots, and VxVM Snapshots It is highly recommended to use NetBackup and VxFS storage checkpoint for database backups. VxFS Storage Checkpoint has the advantages of both snapshot file system and snapshot volume. The administration of the storage checkpoints is totally transparent. In addition, NetBackup supports block-level incremental database backup which can significantly reduce the time required for the backup and the media (tape or disk) space required to store the backup image. Block-level incremental backups also allow more frequent backups, making backup images more up-to-date If, however, NetBackup is not available, both file system snapshots and volume snapshots can be used for database backup. The trade-off can be cost, performance, and administration. The choice depends on budget, a window of time to do database backup, database workload, and other database performance and availability considerations. Cost Because the snapshot volume facility is based on disk mirroring, a snapshot volume of the identical size must be created for each volume to be backed up and the snapshot volume must be on a separate set of disks. When using a snapshot file system for database backup, the snapshot file system can be created on a volume of any size. The size is determined by the backup speed and the database update rate. If the update rate is low and the backup speed is fast, a smaller size would be sufficient. Refer to the section 72 VERITAS Database Edition for Oracle Administrator’s Guide 4 “Determining the Size of a Snapshot File System” to select an appropriate size. In addition, the volume on which the snapshot file system is to be created does not have to be on separate disks. However, having the snapshot file system on the same set of disks that hold the database data may result in somewhat reduced performance. When using NetBackup with the VxFS Storage Checkpoint facility, a storage checkpoint is using the free space pool from the same file systems. The additional space required is also determined by the backup speed and the database update rate. As in the case of database backup using snapshot file systems, if the update rate is low and the backup speed is fast, a smaller incremental of the file system size would be sufficient. The extra space needed depends on how busy the database workload is when the backup is running. If the database is offline during the entire backup window, the additional space required by each file system is about 1% of the file system size to keep track of the block change information. If the database is online while the backup is running, the additional space required by each file system depends on the duration of the backup and the database workload. If workload is light during the backup or the backup window is relatively short (for example, for incremental backups), for most database configurations, an additional 10% of the file system size will be sufficient. If the database has a busy workload while a full backup is running, the file systems may require more space. Database Run-time Performance A snapshot volume is created using the VxVM mirroring technology. In a write-intensive workload, mirroring may result in some performance degradation due to multiple disk writes for each database write request. Once a snapshot volume is created, backup can be performed on the snapshot volume without affecting the I/O performance on the original data volume. Both storage checkpoint and snapshot file system use a “copy-on-write” technique. When a data block is modified, the original content will be copied to the storage checkpoint or the snapshot file system before the block is written. Once a block has been copied to the storage checkpoint or the snapshot file system, any subsequent updates on the same block will be treated as regular write requests. Database Backup and Restore 73 4 When a storage checkpoint or a snapshot file system is used, the database may experience some performance degradation due to the copy-on-write operations. A performance degradation of 15% has been observed on a database with an OLTP workload when a snapshot file system is mounted. When backup is performed using the storage checkpoints and the snapshot file system, I/O performance on the original file system will also be affected. This is because data blocks that have not been updated are still under the control of the primary file system. Therefore, it is highly recommended that the backup be done when the database load is low. Administration Both storage checkpoints and snapshot file systems can be created instantly, whereas, snapshot volumes will take a long time to create due to the need for mirror synchronization. The speed of synchronizing two mirrors depends on the speed of the disks and the workload on the disks. A rate of about 5 gigabytes per hour has been commonly observed. It is also possible to set up 3way mirrors, and perform database backup using one of the mirrors on a rotation basis. When a database backup is needed, the database administrator then breaks up one mirror and performs the database backup to tapes or other media (or just makes a stand-by backup). But the cost is mirror synchronization upon reattaching the backup mirror. Data Persistence and Integrity Snapshot file systems are transient, while storage checkpoints and snapshot volumes are persistent. A snapshot file system is lost if the system is rebooted but a storage checkpoint will still be available after reboot. Both the storage checkpoint and the snapshot file system rely on the integrity of the underlying file system. If the disk on which the file system resides fails, the storage checkpoint and snapshot file system will be lost. To improve reliability, administrators are encouraged to mirror their underlying file system. Database Backup Guidelines Follow these guidelines when using these snapshot techniques for database backup: 74 VERITAS Database Edition for Oracle Administrator’s Guide 4 • Use block-level incremental backups with NetBackup and Storage Checkpoints whenever possible for reduced backup windows and higher performance. • For high performance, perform backups more frequently. • Use the snapshot file system when disk space is limited or you need to do a database backup immediately. • Use snapshot volumes if you have sufficient disk space to create mirrors and you can wait for the completion of mirror synchronization. • Schedule database backup when the system load is low. Databases can remain online when the data is being backed up to tapes or other media. • If disk space is not an issue and the purpose of database backup is for database recovery after a hardware failure, consider using a 3-way mirror on a rotation basis to provide a stand-by backup. Database Backup Using Snapshot File Systems The mount command is used to create a snapshot file system; there is no mkfs step involved. A snapshot file system is always read-only and exists only as long as it and the file system that has been snapped are mounted. A snapped file system cannot be unmounted until any corresponding snapshots are first unmounted. A snapshot file system ceases to exist when unmounted. How a Snapshot File System Works A snapshot file system is created by mounting an empty disk as a snapshot of a currently-mounted file system. The snapshot appears as an exact image of the snapped file system at the time the snapshot was made. Initially, the snapshot file system satisfies read requests by finding the data on the snapped file system and returning it to the requesting process. When write changes the data block n in the snapped file system, the old data is first read and copied to the snapshot before the snapped file system is updated. A subsequent read request for block n on the snapshot file system will be satisfied by reading the data from the snapshot file system, rather than from block n on the snapped file system. Subsequent writes to block n on the snapped file system do not result in additional copies to the snapshot file Database Backup and Restore 75 4 system, because the old data only needs to be saved once. As data blocks are changed on the snapped file system, the snapshot will gradually fill with data copied from the snapped file system. Determining the Size of a Snapshot File System The snapshot file system must be created large enough to hold any blocks on the snapped file system that may be written to while the snapshot file system exists during backup. The amount of disk space required for the snapshot depends on the rate of change of the snapped file system and the amount of time the snapshot is maintained. Since data is also buffered on the database buffer cache, the rate of database change typically is faster than the rate of change of the snapped file system. In the worst case, the snapped file system is completely full and every data block on the snapped file system is rewritten. The snapshot file system would need enough blocks to hold a copy of every block on the snapped file system, plus a few blocks for the data structures that make up the snapshot file system, or roughly 101% of the size of the snapped file system. During a slow period when the system is relatively inactive (for example, on nights and weekends), the snapshot only needs to contain 2 to 6% of the blocks of the snapped file system. During a busy period, the snapshot of an “average” file system might require 15% of the blocks of the snapped file system, though most file systems do not experience this much turnover of data over an entire day. These percentages tend to be lower for larger file systems and higher for smaller ones. The system and database administrator should manage the blocks allocated to the snapshot based on such conditions as database usage and duration of backups. Note: A snapshot file system ceases to exist when unmounted. If remounted, it will be a fresh snapshot of the snapped file system. Snapshot file systems must be unmounted before the corresponding snapped file system can be unmounted. Neither fuser nor mount will indicate that a snapped file system cannot be unmounted because a snapshot of it exists. CAUTION ! Any existing data on the disk used for the snapshot will be overwritten and lost. 76 VERITAS Database Edition for Oracle Administrator’s Guide 4 Monitoring Snapshot File System Block Usage You can monitor the data block usage of a snapshot file system using the vxtrace command. The vxtrace command can be periodically issued to see the last few blocks written to the volume of the snapshot file system. For example, enter the following command: # vxtrace -t 5 -g datadg -o vol dbsnap | grep\ “START write” A display similar to the following appears: 36365 START write vdev dbsnap block 64896 len 4 concurrency 1 pid 3 36366 START write vdev dbsnap block 210848 len 4 concurrency 2 pid 3 36367 START write vdev dbsnap block 226952 len 4 concurrency 3 pid 3 36368 START write vdev dbsnap block 226956 len 4 concurrency 4 pid 3 The display above shows the block numbers of the blocks that have been written to and the volume of the snapshot file system during the last five seconds (the -t option). Pay attention to the largest block number (in this case, 226956) that was written. If this number is getting closer to the number of blocks reserved for the snapshot file system (the snapsize option), the snapshot file system will run out of space soon. When this happens, you should try to speed-up the backup process or temporarily slow down the database update rate so that the backup can finish in time. For more information on the vxtrace command, refer to the vxtrace(1M) manual page. Note: If a snapshot file system runs out of space for changed data blocks, it is disabled and all further access to it fails. This does not affect the snapped file system. Any unfinished database backup that is using the disabled snapshot file system will be an incomplete backup and may not be usable. Performance of Snapshot File Systems Snapshot file systems maximize the performance of the snapshot at the expense of writes to the snapped file system. Reads from a snapshot file system typically perform at nearly the throughput of reads from a normal vxfs file system, allowing backups to proceed at the full speed of the vxfs file system. Database Backup and Restore 77 4 The performance of reads from the snapped file system should not be affected. Writes to the snapped file system, however, typically average two to three times as long as without a snapshot, since the initial write to a data block now requires a read of the old data, a write of the data to the snapshot, and finally the write of the new data to the snapped file system. If multiple snapshots of the same snapped file system exist, writes will be even slower. Only the initial write to a block suffers this penalty, however, so operations like writes to the intent log or inode updates proceed at normal speed after the initial write. However, because Quick I/O for Databases supports asynchronous I/O, the performance impact is less significant for Oracle databases. A performance degradation of 15% in throughput has been observed in an OLTP workload. Reads from the snapshot file system are impacted if the snapped file system is busy, since the snapshot reads are slowed by all of the disk I/O associated with the snapped file system. It is therefore recommended to perform database backup when the database has less activities. Database Backup Using Snapshot File Systems Once a snapshot file system is created, it can be used to perform a consistent backup of the snapped file system. Backup programs that function using the standard file system tree (such as cpio) can be used without modification on a snapshot file system, because the snapshot presents the same data as the snapped file system. Backup programs that access the disk structures of a vxfs file system require some modifications to deal with a snapshot file system. The vxfs utilities (such as volcopy and vxdump) understand snapshot file systems and make suitable modifications in their behavior so that their operation on a snapshot file system is indistinguishable from that on a normal file system. Other backup programs that normally read the raw disk image cannot work on snapshots without modification. These programs can use the fscat command to obtain a raw image of the entire file system identical to that which would have been obtained by a dd of the disk device containing the snapped file system at the exact moment the snapshot was created. If a complete backup of a snapshot file system is made through a utility such as volcopy and is later restored, it will be necessary to fsck the restored file system because the snapshot file system is only consistent and not clean. The file system may have some extended inode operations that must be completed, though there should be no other changes. However, since VxFS is a log-based 78 VERITAS Database Edition for Oracle Administrator’s Guide 4 file system, the time to run the fsck command is quite fast (usually within a couple of seconds). Since the snapshot file system is not writable, it cannot be fully fscked. However, the fsck -n command can be used to report any inconsistencies. Backing Up and Restoring Individual Quick I/O Files If you are backing up individual tablespaces, make sure the symbolic links used are pointing to the Quick I/O name extensions of the files in the snapshot file system not to the snapped file system. If the database files are created using the qiomkfile command, the symbolic links are relative path names. When a snapshot file system is created, the symbolic links will point to the regular files on the snapshot file system. You can backup just the regular data files. When restoring the individual data files, make sure symbolic links are created to point to the Quick I/O name extensions of the data files to use the Quick I/O for databases option. Refer to“Accessing Regular UNIX Files as Quick I/O Files,” in Chapter 2, for more information. Creating a Snapshot File System You can use the Visual Administrator graphical user interface or the command line interface to create or remove a snapshot file system. Creating or removing a snapshot file system using the Visual Administrator is relatively easy. The Visual Administrator will determine an appropriate size and create a volume automatically for the snapshot file system. When removing a snapshot file system, the Visual Administrator will automatically remove the volume that was used by the snapshot file system. When using the command line interface, you need to create a volume with an appropriate size before you use the mount command to create the snapshot file system. You will also need to remove the volume manually when the snapshot file system is no longer needed. Creating a Snapshot Using the Visual Administrator The Visual Administrator snapshot feature can be used to create or remove snapshot file systems for database backup: • Creating a Snapshot — Create a simple volume and then mount a snapshot file system of the selected file system on this new volume. Database Backup and Restore 79 4 • Removing a Snapshot — Unmount the snapshot file system and then remove the underlying volume. Removal can only be performed on a file system that was created by the Create process listed above. A volume icon containing a mounted file system must be selected in order for either the create or remove aspect of this operation to succeed. Creating a Snapshot Create a snapshot of a file system for database backup as follows: 1. Go to the view window corresponding to the database disk group. 2. Select the volume whose file system is used by the database for backup. 3. Prepare the database for online or offline backup. If you plan to do an offline backup, you can now shut down your database. If you plan to do an online backup, you can now issue the alter tablespace ... begin backup command to begin the database backup. $ svrmgrl SVRMGR> connect internal SVRMGR> alter tablespace tablespace begin backup SVRMGR> exit 4. From the Basic-Ops menu, select File System Operations > Snapshot > Create. The FS Snapshot Create form appears. 5. Complete the FS Snapshot Create form. Enter the mount point and the size of the snapshot file system to be created. The Visual Administrator calculates a default size which is approximately one fifth of the size of the original file system. Adjust the size according to the rate of database updates and the speed of the backup. 6. When the form is properly completed, select Apply to take a snapshot of the file system. 7. The database can be resumed to its normal operation. 80 VERITAS Database Edition for Oracle Administrator’s Guide 4 Restart the database if you are doing an offline backup. Use the command alter tablespace ... end backup to allow the database to return to its normal operation if you are doing an online backup. $ svrmgrl SVRMGR> connect internal SVRMGR> alter tablespace tablespace end backup SVRMGR> exit A new, simple volume containing a mounted snapshot file system appears. Figure 10 shows a file system on a volume and its snapshot file system on another volume. Figure 10 File System and Snapshot V V disk01-01 disk02-01 database-01 database-02 database disk03-02 dbsnap-01 dbsnap /database /dbsnap The snapshot file system can then be backed up to tape (or some other media). The snapshot mount point should be used for backup. Removing a Snapshot Note: This section applies only to a snapshot file system created by the method discussed in the previous section. Once database files on the snapshot file system have been backed up, the snapshot file system can then be removed. This involves unmounting the snapshot file system and removing its underlying volume. Remove a snapshot file system as follows: 1. Go to the view window corresponding to the database disk group. 2. Select the volume containing the snapshot file system that is to be removed. Database Backup and Restore 81 4 This volume and file system must have been created by the snapshot create operation presented in the previous section. 3. From the Basic-Ops menu, select File System Operations > Snapshot > Remove. Both the snapshot file system and its underlying volume disappear. The resulting free space is returned to the Volume Manager free space pool. Creating a Snapshot Using the Command Line A snapshot file system can be created using the command line interface. The command line interface can be used to create database administration scripts to support regular database backup and restore using snapshot file systems. A snapshot file system is created by using the -o snapof= option of the mount command. The -o snapsize= option may also be required if the device being mounted does not identify the device size in its disk label, or if a size smaller than the entire device is desired. The following command line could be used to create a snapshot file system: # mount -F vxfs -o snapof=special,snapsize=snapshot_size \ snapshot_special snapshot_mount_point where snapshot_special is a block special device (for example, a volume) and the snapshot_mount_point is the location where the snapshot file system will be mounted. For information on how to create a volume, refer to “Creating a Volume,” in Chapter 2. To remove a snapshot file system, just unmount the file system. The following command line removes a snapshot file system: # umount snapshot_mount_point Database Backup Examples Here are some typical database backup examples using the command line interface. The examples are backing up a tablespace of a database on a 300,000 block file system named /database/ts1 (which exists on disk /dev/vx/dsk/database/ts1) using a snapshot file system on /dev/vx/dsk/dbsnap with a snapshot mount point of /snapshot/ts1. For 82 VERITAS Database Edition for Oracle Administrator’s Guide 4 this example, we assume that /database/ts1 contains only data files that are a part of ts1 and that this file system contains all of the data files of tablespace ts1. • To back up the database files (*.dbf files) using cpio: $ svrmgrl SVRMGR> connect internal connected. SVRMGR> alter tablespace ts1 begin backup Statement processed. SVRMGR> exit; $ mount -F vxfs -o \ snapof=/dev/vx/dsk/database,snapsize=100000 \ /dev/vx/dsk/dbsnap /snapshot/ts1 $ svrmgrl SVRMGR> connect internal connected. SVRMGR> alter tablespace ts1 end backup Statement processed. SVRMGR> exit; $ cd /snapshot/ts1 $ find | cpio -oc > /dev/rmt/c0s0 $ umount /snapshot/ts1 • To perform an offline database backup to back up entire file system using volcopy: $ svrmgrl SVRMGR> connect internal connected. SVRMGR> shutdown SVRMGR> Database closed. Database dismounted. ORACLE instance shut down. SVRMGR> SQL*DBA complete. Database Backup and Restore 83 4 $ mount -F vxfs -o \ snapof=/dev/vx/dsk/database,snapsize=100000 \ /dev/vx/dsk/dbsnap /snapshot/ts1 $ svrmgrl SVRMGR> connect internal connected. SVRMGR> startup SVRMGR> ORACLE instance started. Database mounted. Database opened. SVRMGR> exit; $ volcopy -F vxfs database /dev/vx/rdsk/dbsnap dbsnap \ /dev/rmt/c0s0 tape77 $ umount /snapshot/ts1 • To do a full backup of a snapshot file system using the vxdump command: $ mount -o snapof=/dev/vx/dsk/database,snapsize=\ 100000 /dev/vx/dsk/dbsnap /snapshot/ts1 $ vxdump f - /dev/vx/dsk/dbsnap | dd bs=128k > \ /dev/rmt/c0s0 The vxdump and volcopy programs will ascertain that /dev/vx/dsk/dbsnap is a snapshot mounted as /snapshot/ts1 and do the appropriate work to get the snapshot data through the mount point. Restoring Database Files If a media failure permanently damages one or more database files of a database, you must restore backups of these damaged database files before you can recover the damaged files. You may need to replace the failed media and re-create the file system before you can restore the damaged files. Restoring database files from the backup that was done on snapshot file systems is no different than restoring database files from the backup done on regular file systems. Before restoring a database, be sure to follow the 84 VERITAS Database Edition for Oracle Administrator’s Guide 4 procedures described in “Creating a Database File Using qiomkfile” to create the Quick I/O files. When you need to restore the database files from an earlier file system backup, follow these steps: 1. Prepare the database for recovery. 2. Re-create the damaged file systems that contain database files, if needed. 3. Start restoring the file system or individual files from the backup media. 4. Perform database recovery and resume normal database operation. The time required to restore the files or the file systems depends on: the size of the files, the speed of the disk, and the backup media. Once the file systems are restored, you can then start the database recovery process. For details on database recovery, refer to the Oracle documentation. Database Restore Examples Here are some typical restore examples using the command line interface. The examples are restoring individual database files and the entire file system named /database using the backup performed previously on snapshot file systems: • To restore the database file user1.dbf using cpio: # cd /database # cpio -ic -C 65536 user1.dbf < /dev/rmt/0 # chown oracle:dba user1.dbf • To restore an entire file system from an earlier vxdump backup: # cd /database # dd bs=128k < /dev/rmt/c0s0 | vxrestore xf - • To restore the database file user1.dbf from an earlier vxdump backup: # cd /database # dd bs=128k < /dev/rmt/c0s0 | vxrestore xf - user1.dbf Database Backup and Restore 85 4 Database Backup Using Snapshot Volumes VxVM also provides the ability to perform snapshot backups of volumes based on disk mirroring. This capability is provided through the vxassist utility and the Visual Administrator facility. How a Snapshot Volume Works With the Volume Manager, the snapshot operation creates a new volume that is a snapshot of an existing volume. This is done by creating a mirror of the existing volume using disk space from the pool of free disk space. The mirror is brought up to date (this may take some time) and a separate (snapshot) volume is then created for it. The snapshot volume represents a consistent copy of the original volume at the time the snapshot was begun. The snapshot volume can be used to make a backup of the original volume at a convenient time without stopping it. After the backup is made, the snapshot volume can be removed without losing any data. The recommended approach to volume backup involves the use of the vxassist utility. The vxassist procedure is convenient and relatively simple. vxassist has three operations: snapstart, snapwait, and snapshot that provide a way to do database backup with minimal interruption of data change and access activity. The snapstart operation creates a backup mirror which gets attached to and synchronized with the volume. When synchronized with the volume, the backup mirror is ready to be used as a snapshot mirror. The end of the update procedure is signified by the new snapshot mirror changing its state to SNAPDONE. This change can be tracked by the vxassist snapwait operation, which waits until at least one of the mirrors changes its state to SNAPDONE. If the attach process fails, the snapshot mirror is removed and its space is released. Once the snapshot mirror is synchronized, it continues being updated until it is detached. You can then select a convenient time at which to create a snapshot volume as an image of the existing volume for database backup. At which point, you will then need to either shut down the database for an offline backup or use the alter tablespace ... begin backup command for an online backup during the brief time required to detach the snapshot volume 86 VERITAS Database Edition for Oracle Administrator’s Guide 4 (typically less than a minute). The amount of time involved in creating the snapshot mirror is long and indefinite in contrast to the brief amount of time that it takes to detach the mirror and create the snapshot volume. The backup procedure is completed by running a vxassist snapshot command on a volume with a SNAPDONE mirror. This operation detaches the finished snapshot (which becomes a normal mirror), creates a new normal volume and attaches the snapshot mirror to it. If the snapshot procedure is interrupted, the snapshot mirror is automatically removed the next time volume is started. Creating a Snapshot Volume for Database Backup You can use the Visual Administrator graphical user interface or the command line interface to create or remove a snapshot volume. Creating or removing a snapshot volume using the Visual Administrator is relatively easy. However, because the time required to synchronize a snapshot mirror may be long, using the command line interface through the vxassist command is recommended when creating snapshot volumes. Creating a Snapshot Using the Visual Administrator The Visual Administrator snapshot feature can be used to create or remove snapshot volumes for database backup: • Creating a snapshot — Create a snapshot volume for backup. • Removing a snapshot — Remove the snapshot volume. Creating a Snapshot Create a snapshot of a volume as follows: 1. Go to the view window corresponding to the database disk group. 2. Select the volume used by the database for backup. If multiple volumes are used, create a snapshot for each volume used by the database. 3. From the Basic-Ops menu, select Volume Operations > Snapshot > Snapstart. Database Backup and Restore 87 4 A snapshot mirror appears within the selected volume. As the snapshot mirror is updated, its icon is greyed out. This synchronization takes a variable amount of time, which could be significant (depending on the size of the selected volume). 4. Once the snapshot mirror is fully updated (and its icon is no longer greyed out), prepare to make the snapshot volume. 5. Prepare the database for online or offline backup. If you plan to do an offline backup, you can now shut down your database. If you plan to do an online backup, you can now issue the alter tablespace ... begin backup command to begin the database backup. 6. Select the volume containing the snapshot mirror. 7. From the Basic-Ops menu, select Volume Operations > Snapshot > Snapshot again. The Snapshot form appears. 8. Complete the Snapshot form. Enter a meaningful name of the snapshot volume to be created as a backup of the database volume. For detailed information on this form, refer to online help. 9. When the form is properly completed, select Apply to activate the snapshot operation. This portion of the procedure should only take a brief amount of time. The snapshot operation detaches the finished snapshot mirror, creates a new normal volume, and attaches the snapshot to it. As this occurs, the original volume icon returns to its former state and the backup mirror moves over into the new snapshot volume (which has the name specified in the Snapshot name field of the Snapshot form). The new snapshot volume remains as a consistent copy of the selected volume at the time the snapshot was begun. 10. The database can resume its normal operation. Re-start the database if you are doing an offline backup. Use the command alter tablespace ... end backup to allow the database to return to its normal operation if you are doing an online backup. 88 VERITAS Database Edition for Oracle Administrator’s Guide 4 11. Back up the snapshot volume to tape (or some other media). 12. Remove the snapshot volume when it is no longer necessary, because it takes up as much space as a normal volume. Figure 11 illustrates a volume and its snapshot. Figure 11 Volume and Snapshot V V disk01-01 disk02-01 database-01 database-02 database disk03-02 snapshot-01 snapshot /database Removing a Snapshot Once data on the snapshot volume has been backed up, a snapshot volume can be removed in the same way as a regular volume is removed. Remove a snapshot volume as follows: 1. Go to the view window corresponding to the database disk group. 2. Select the snapshot volume to be removed. 3. From the Basic-Ops menu, select Volume Operations > Remove Volumes Recursively. The snapshot volume and its components disappear after prompting for confirmation. Creating a Snapshot Using the Command Line A snapshot volume can be created using the vxassist command line interface. The command line interface can be used to create database administration scripts to support regular database backup and restore using snapshot volumes. Use the following steps to perform a complete backup: 1. Create a snapshot mirror for a volume as follows: Database Backup and Restore 89 4 # vxassist snapstart volume_name 2. Wait for the completion of the operation. When the snapstart operation is complete, the mirror is in a SNAPDONE state. You can use the following command to wait for the completion of the snapstart operation: # vxassist snapwait volume_name 3. Select a convenient time to prepare the database for online or offline backup and complete the snapshot operation. Wait for the time when the database or the tablespaces can be brought offline. If you plan to do an offline backup, you can now shut down your database. If you plan to do an online backup, you can now issue the command alter tablespace ... begin backup to begin the database backup. 4. Create a snapshot volume that reflects the original volume as follows: # vxassist snapshot volume_name snapshot_volume_name 5. The database can be resumed to its normal operation. 6. Use fsck (or some utility appropriate to the application running on the volume) to clean the temporary volume’s contents. For example: # fsck -F vxfs -y /dev/vx/rdsk/snapshot_volume_name 7. Copy the snapshot volume to tape, or to some other appropriate backup media. 8. Remove the new volume: # vxedit -rf rm snapshot_volume_name Restoring Database Files from Snapshot Volumes If a media failure permanently damages one or more database files of a database and you are using snapshot volumes for backup, you will need to restore the entire image of the volume from the backup. To restore individual database files, you will need find a large enough volume (or break off a 90 VERITAS Database Edition for Oracle Administrator’s Guide 4 mirror), restore the volume image, mount the file system as a different file system, and copy the database files to the file systems used by the database instance before you start the recovery process. When you need to restore database files from an earlier snapshot volume backup, the steps are: 1. Prepare the database for recovery. 2. Create a volume that is large enough for the restored volume image. 3. Restore the volume image from the backup media. 4. Mount the file systems that contain the damaged database files. 5. Copy the files to the file systems used by the databases. For Quick I/O files, you should copy both the regular files and the symbolic links to the target file systems. 6. Perform database recovery and resume normal database operation. The time required to restore the files or the file systems depends on: the size of the files, the speed of the disk, and the backup media. Once the file systems are restored, you can then start the database recovery process. For details on database recovery, refer to the Oracle documentation. Database Backup Using Storage Checkpoints VxFS provides a storage checkpoint mechanism which is similar to the snapshot file system mechanism but the storage checkpoint persists after a system reboot. A storage checkpoint allows an exact image of a database to be created instantly. The storage checkpoint provides a consistent view of the database at the point in time when the storage checkpoint is created. This checkpoint image becomes an exact copy of the database; however, it’s only available through NetBackup and the vxdba facility. The storage checkpoint will be created and managed by either NetBackup or vxdba for database backup; the operation is totally transparent to the administrators. Database Backup and Restore 91 4 What is a Storage Checkpoint How a Storage Checkpoint Works A storage checkpoint is created as a “clone” of a currently-mounted file system (the primary file system). Like a snapshot file system, a storage checkpoint appears as an exact image of the snapped file system at the time the storage checkpoint was made. However, unlike a snapshot file system that uses separate disk space, all storage checkpoints share the same free space pool where the primary file system resides. Initially, the storage checkpoint satisfies read requests by finding the data on the primary file system and returning it to the requesting process. When a write operation changes the data block n in the primary file system, the old data is first read and copied to the storage checkpoint before the primary file system is updated. A subsequent read request for block n on the storage checkpoint will be satisfied by reading the data from the storage checkpoint, rather than from block n on the primary file system. Subsequent writes to block n on the primary file system do not result in additional copies to the storage checkpoint, because the old data only needs to be saved once. As data blocks are changed on the primary file system, the storage checkpoint will gradually fill with data copied from the primary file system. Performance of Storage Checkpoints VxFS attempts to optimize the read and write performance on both the storage checkpoint and the primary file system. Reads from a storage checkpoint typically perform at nearly the throughput of reads from a normal vxfs file system, allowing backups to proceed at the full speed of the vxfs file system. The performance of reads from the primary file system is generally not affected by the existence of storage checkpoints. Writes to the primary file system, however, are typically affected by the storage checkpoints, because the initial write to a data block may now require: a read of the old data; a write of the data to the storage checkpoint; and finally the write of the new data to the primary file system. However, having multiple storage checkpoints on the same file system will not make writes even slower. Only the initial write to a block suffers this penalty. Hence, operations like writes to the intent log or inode updates proceed at normal speed after the initial write. 92 VERITAS Database Edition for Oracle Administrator’s Guide 4 VxFS is optimized when storage checkpoints are used with Quick I/O for Databases. The performance impact is less significant for Oracle databases that use Quick I/O files. A performance degradation of less than 5% in throughput has been observed in a typical OLTP workload when the storage checkpoints only keep track of the changed information. For storage checkpoints that are used for storage rollback, higher performance degradation (approximately 20%) has been observed in an OLTP workload. The degradation should be lower in most decision-support or data warehousing environments. Reads from the storage checkpoint are impacted if the primary file system is busy, because the reads on the storage checkpoint are slowed by all of the disk I/O associated with the primary file system. It is therefore recommended to perform database backup when the database has less activities. File System and Storage Checkpoint Space Management To support block-level incremental backup and storage rollback, the file systems need extra disk space to store the Storage Checkpoints. The extra space needed depends on how the storage checkpoints are used. The storage checkpoints that are used to keep track of the block changes contain only file system block maps, thus, they require very little additional space (about less than 1% of the file system size). These storage checkpoints are used by NetBackup to support block-level incremental backup. When NetBackup is backing up the database, NetBackup will create one set of storage checkpoints to freeze the file systems for the database backups. The space required to hold this additional set of storage checkpoints depends on how busy the database load is when the backup is running. If the database is offline during the entire backup window, there is no additional space required. If the database is online while the backup is running, the additional space required by each file system depends on the duration of the backup and the database workload. If workload is light during the backup or the backup window is relatively short (for example, for incremental backups), for most database configurations, an additional 10% of the file system size will be sufficient. If the database has a busy workload while a full backup is running, the file systems may require more space. To support storage checkpoints and rollback, VxFS needs to keep track of the original block contents when the storage checkpoints were created. The additional space needed is proportional to the number of blocks that have been Database Backup and Restore 93 4 changed since a storage checkpoint was taken. The number of blocks changed may not be identical to the number of changes. For example, if a data block has been changed many times, only the first change will require a new block to be allocated to store the original block content. Any subsequent changes to the same block will require no block allocation. If a file system that has storage checkpoints runs out of space, VxFS will first remove storage checkpoints automatically, instead of returning an ENOSPC error code (Unix errno 28- No space left on device) which may cause the Oracle instance to fail. This will ensure the expected I/O semantics. To avoid unnecessary storage checkpoint removal, the vxdba command can be used to set up a file system alarm to monitor the space usage. When a file system space usage exceeds a preset threshold value (say, 95% full), the alarm will start a script to grow the volume and the file system automatically. A notification will also be sent to the system administrator on the status of file system resizing. The system administrator should have enough free disk space reserved for growing the volumes and file systems. The administrator can also preallocate sufficient space for each file system when the file system was first made or grow the file system using the vxassist and fsadm commands to manage the file system space usage manually. Refer to the vxassist(1M) and fsadm_vxfs(1M) manual pages for more detailed information. Database Backup with NetBackup VERITAS NetBackup lets system and database administrators back up and restore database files and directories used by the database servers on the NetBackup client systems. Administrators can set up schedules for automatic, unattended, online (hot), and offline (cold) database backup. These backups are managed automatically by the NetBackup server. The administrator can also manually back up and restore database files from any of the NetBackup clients. Once a storage checkpoint is created, the database can then resume its normal operation, while backup is performed using the file images from the storage checkpoint. For example, for a cold database backup, the database can be brought back online immediately once the storage checkpoint is taken. That is, with the VxFS storage checkpoint, NetBackup can perform cold database backup while the database is actually being running ("hot"). This can reduce the database down time required for a cold database backup. NetBackup provides a set of notify scripts which can synchronize backup operations with database operations. 94 VERITAS Database Edition for Oracle Administrator’s Guide 4 Additionally, the storage checkpoint maintains a block map that keeps track of blocks that have been changed since the last storage checkpoint. NetBackup uses the block map to identify the data blocks that have been changed since the last backup. This allows NetBackup to support block-level incremental database backup. The time required to do the incremental backup is proportional to the number of the changes rather than the size of the database. For large databases where the percentage of changes tends to be low, blocklevel incremental backup will provide significant savings in time and tape usage for backup images. Figure 12 Database Backups with Storage Checkpoints Shutdown Database Storage Checkpoint Start the Database Perform Backup Resume Operation For more information on how to configure NetBackup for block-level incremental backups and restore, refer to the VERITAS NetBackup BLI Backup for Oracle System Administrator’s Guide. Storage Checkpoint and Rollback Using vxdba As the storage checkpoints records the before images of blocks that have been changed, they can be used to do a disk-based or file-system-based rollback to the exact time when the storage checkpoint was taken. As a restricted facility, the storage checkpoints can be considered as backups that are on-line and can be used to rollback the entire database, a file system, or file. Rolling back to any storage checkpoint is generally very fast, because only the before images of the data blocks need to be restored. Database Backup and Restore 95 4 The facility is restricted because storage checkpoints can only be used to restore files or an entire database from a logical error (for example, a human mistake) but it cannot not be used to restore files from a media failure since all the data blocks are on the same physical device. A media failure will require a database restore from a tape backup or a copy of the database files on a separate medium. Hence, it is recommended for highly critical data, the combination of data redundancy (disk mirroring) and storage checkpoints are used to protect from both physical media failure and logical errors. Figure 13 Storage Checkpoint as “online” backup Shutdown Database Storage Checkpoint Backup is available online Start the Database Resume Operation To create a storage checkpoint, the database administrator must use the vxdba command. The vxdba command presents a menu of possible operations to the database administrator. Based on the operations selected, the script guides the database administrator through the necessary steps, and prompts for data that must be user-supplied for completion of the operation. The database administrator also uses the vxdba command to create storage checkpoints as backups and to restore the entire database, a single tablespace, or any set of datafiles using any of the previously-created storage checkpoints. 96 VERITAS Database Edition for Oracle Administrator’s Guide 4 Using vxdba to Perform Database Backup and Rollback Starting vxdba The vxdba facility should be run by the oracle owner (typically, the user id oracle) of the Oracle instance for most operations. File system space management operations in vxdba, however, should be run by the system administrator (i.e., root). Before running vxdba, the environment variables $ORACLE_SID and $ORACLE_HOME must be defined. If the Oracle startup parameter file initSID.ora is not located at $ORACLE_HOME/dbs, the environment variable $PFILE must be defined to point to the pathname of the startup parameter file. To start vxdba, enter: % vxdba vxdba starts up and displays its main menu containing the operations available to you. The vxdba Main Menu The following is the vxdba main menu: VERITAS Database Edition for Oracle Menu: Database Main 1 2 Startup database instance Shutdown database instance 3 4 5 Display database information Display tablespace information Display datafile/filesystem information 6 7 8 Storage checkpoint administration Restore files from storage checkpoint Filesystem space usage administration ? ?? q Display help about menu Display help about the menuing system Exit from menus Select an operation to perform: Database Backup and Restore 97 4 • ? can be entered at any time to provide help in using the menu. The output of ? is a list of operations and a definition of each. • ?? lists inputs that can be used at any prompt. • q returns you to the main menu if you need to restart a process; however, using q at the main menu level exits vxdba. The vxdba menu provides access to the following operations: • Startup database instance Use this menu operation to start up the Oracle instance. Before this menu operation is selected, the environment variables $ORACLE_SID and $ORACLE_HOME must be defined. • Shutdown database instance Use this menu operation to shut down the Oracle instance. For example, to create a storage checkpoint of a cold database backup image, this menu operation can be used to bring the database instance down. • Display database information Use this menu operation to display the database information. The information displayed include the Oracle instance name ($ORACLE_SID), the Oracle home ($ORACLE_HOME), the Oracle release level, and the status (ONLINE or OFFLINE) of the Oracle instance. • Display tablespace information Use this menu operation to display the tablespaces and their associated database files in the Oracle instance. • Display datafile/file system information Use this menu operation to display the list of database files and the file systems used by the Oracle instance. • Storage checkpoint administration A storage checkpoint can be considered as an online copy of a database backup which contains a snapshot image of the database when the storage checkpoint was created. These storage checkpoints can be used to restore or rollback the image of a file, a tablespace, or the whole database to some earlier state. 98 VERITAS Database Edition for Oracle Administrator’s Guide 4 Use this menu operation to display, create, or purge storage checkpoints. Operations provided in the Storage Checkpoint Administration menu are: • Display storage checkpoint information • Create a new storage checkpoint • Purge old storage checkpoints • Restore files from storage checkpoint Use this menu operation to restore a file, a list of database files, any tablespace, or the entire database to the state of an earlier storage checkpoint. Options provided in the Restore files from storage checkpoint menu are: • Roll backward the database to a storage checkpoint • Restore a tablespace to a storage checkpoint • Restore files to a storage checkpoint • File system space usage administration There are additional space requirement to maintain the storage checkpoints. Use this menu operation to monitor the space usage of the storage checkpoints and to enable the VERITAS Storage Manager’s space alarm to monitor the space usage for automatically growing of the file systems. Operations provided in the File System Space Alarm Administration menu are: • Display file system space usage • Display file system space alarm • Enable file system space alarm • Disable file system space alarm Database Backup and Restore 99 4 Startup database instance The vxdba command uses the database dictionary to interrogate the list of tablespaces and the data files to determine the file systems for the storage checkpoint creation and removal. Use this menu operation to bring the Oracle instance online. Before this menu operation is selected, the environment variables $ORACLE_SID and $ORACLE_HOME must be defined. The following is an example display when the menu operation is selected: --------------------------------------------------VXDBA: Starting Oracle instance - DEMO --------------------------------------------------- Oracle Server Manager Release 3.0.4.0.0 - Production (c) Copyright 1997, Oracle Corporation. All Rights Reserved. Oracle8 Enterprise Edition Release 8.0.4.0.0 - Production With the Partitioning and Objects options PL/SQL Release 8.0.4.0.0 - Production SVRMGR> Connected. SVRMGR> ORACLE instance started. Total System Global Area 152778944 Fixed Size 47296 Variable Size 46809088 Database Buffers 104857600 Redo Buffers 1064960 Database mounted. Database opened. SVRMGR> Server Manager complete. bytes bytes bytes bytes bytes VXDBA: Oracle instance DEMO started. Press to continue... 100 VERITAS Database Edition for Oracle Administrator’s Guide 4 Shutdown database instance To create a storage checkpoint for a cold database backup image, the database must be offline. This menu operation can be used to bring the database instance down. The following screen is displayed when this menu operation is selected: Instance Shutdown Operations Menu: Database Main/Database Shutdown ORACLE_SID ORACLE_HOME Oracle Release Status : : : : DEMO /oracle/app/oracle/product/8.0.4 8.0.4.0.0 ONLINE 1 2 3 Shutdown NORMAL Shutdown IMMEDIATE Shutdown Abort ? ?? q Display help about menu Display help about the menuing system Exit from menus Select an operation to perform: Shutdown NORMAL This menu operation is used to shut down the Oracle instance in normal situations. When this operation is selected, no new database connections are allowed. Oracle will wait for all currently connected users to disconnect from the database before shutting down the database. Shutdown IMMEDIATE This menu operation is used to shut down the Oracle instance immediately. Use this operation in the situations that the database or some application is running irregularly or a power shutdown is going to occur. When this Database Backup and Restore 101 4 operation is selected, all current client SQL statements are terminated immediately, any uncommitted transactions will be rolled back, and all connected users will be disconnected. Shutdown ABORT This menu operation is used to shut down the Oracle instance instantaneously by aborting the database’s instance. Use this menu operation with extreme caution. Use this operation only when normal shutdown or immediate shutdown did not work, or you experience problems when starting the instance, or you need to shut down the instance instantaneously. When this operation is selected, the next startup of the database will require instance recovery. The following is an example display when the Shutdown NORMAL option is selected: -------------------------------------------------------------VXDBA: Shutting down (NORMAL) Oracle instance - DEMO -------------------------------------------------------------- Oracle Server Manager Release 3.0.4.0.0 - Production (c) Copyright 1997, Oracle Corporation. All Rights Reserved. Oracle8 Enterprise Edition Release 8.0.4.0.0 - Production With the Partitioning and Objects options PL/SQL Release 8.0.4.0.0 - Production SVRMGR> Connected. SVRMGR> Database closed. Database dismounted. ORACLE instance shut down. SVRMGR> Server Manager complete. VXDBA: Oracle instance DEMOsuccessfully shut down. Press to continue... 102 VERITAS Database Edition for Oracle Administrator’s Guide 4 Display database information This menu operation is used to display the Oracle instance name ($ORACLE_SID), the Oracle home ($ORACLE_HOME), the Oracle release level, and the status (ONLINE or OFFLINE) of the Oracle instance. The following is an example display when this menu option is selected: Oracle Instance Status Menu: Database Main/Database Info ORACLE_SID ORACLE_HOME Oracle Release Status : : : : DEMO /oracle/app/oracle/product/8.0.4 8.0.4.0.0 ONLINE 1 Refresh Status ? ?? q Display help about menu Display help about the menuing system Exit from menus Select an operation to perform: If the display screen has been idle for a while, the status of the Oracle instance may have been changed by other applications. The sub-menu option 1 (Refresh Status) can be used to refresh the display with the up-to-date status of the Oracle instance. Database Backup and Restore 103 4 Display tablespace information This menu operation is used to display the list of tablespaces of an Oracle instance and their associated data files. The following is an example display when this menu operation is selected: --------------------------------------------------VXDBA: Getting tablespace information -- DEMO --------------------------------------------------ORACLE_SID: ORACLE_HOME: Oracle Release: # Tablespaces: # Datafiles: DEMO /oracle/app/oracle/product/8.0.4 8.0.4.0.0 11 11 TABLESPACE_NAME -----------------------------SYSTEM ROLL HIST WARE CUST ITEMS ORD NORD ORDL STOCKS TEMP FILE_NAME -------------------/tpcc_disks/sys1 /tpcc_disks/roll1 /tpcc_disks/hist1 /tpcc_disks/ware1 /tpcc_disks/cust1 /tpcc_disks/item1 /tpcc_disks/ord1 /tpcc_disks/nord1 /tpcc_disks/ordl1 /tpcc_disks/stk1 /tpcc_disks/temp1 Press to continue... vxdba maintains a data dictionary that stores the pertinent information needed for storage checkpoint management. This data dictionary is located at /etc/vx/vxdba/$ORACLE_SID. When the database configuration changes, the information stored in the data dictionary may not be up to date. When vxdba detects that the data dictionary is no longer consistent with Oracle’s system catalog, vxdba will update its data dictionary with the latest configuration information. 104 VERITAS Database Edition for Oracle Administrator’s Guide 4 Display datafile/file system information This menu operation is used to display the list of database files and the file systems used by the Oracle instance. The following is an example display when the menu operation is selected: -------------------------------------------------------VXDBA: Getting datafile/filesystem information -- DEMO -------------------------------------------------------ORACLE_SID: ORACLE_HOME: Oracle Release: # Tablespaces: # Datafiles: DEMO /oracle/app/oracle/product/8.0.4 8.0.4.0.0 11 11 FILESYSTEM -----------------------------/tpcc_disks /tpcc_disks /tpcc_disks /tpcc_disks /tpcc_disks /tpcc_disks /tpcc_disks /tpcc_disks /tpcc_disks /tpcc_disks /tpcc_disks FILE_NAME -------------------/tpcc_disks/temp1 /tpcc_disks/stk1 /tpcc_disks/ordl1 /tpcc_disks/nord1 /tpcc_disks/ord1 /tpcc_disks/item1 /tpcc_disks/cust1 /tpcc_disks/ware1 /tpcc_disks/hist1 /tpcc_disks/roll1 /tpcc_disks/sys1 Press to continue... This information is also stored in vxdba’s data dictionary. When vxdba detects any configuration changes, vxdba will update its data dictionary with the latest configuration information. Database Backup and Restore 105 4 Storage checkpoint administration This menu operation is used to create new storage checkpoints, and to display or delete storage checkpoints created using vxdba. Currently, vxdba can not be used to display or delete storage checkpoints created by NetBackup. Storage checkpoints created by NetBackup are managed solely by NetBackup. When this menu operation is selected, an example screen is as follows: Storage Checkpoint Administration Menu: Database Main/Storage Checkpoint Administration ORACLE_SID ORACLE_HOME Oracle Release Status : : : : DEMO /oracle/app/oracle/product/8.0.4 8.0.4.0.0 ONLINE 1 2 3 Display storage checkpoints Create a new storage checkpoint Purge old storage checkpoints ? ?? q Display help about menu Display help about the menuing system Exit from menus Select an operation to perform: Use the menu option 1, Display storage checkpoints, to display the storage checkpoints created by vxdba. Use the menu option 2, Create a new storage checkpoint, to create a storage checkpoint as a snapshot copy of the database image. Use the menu option 3, Purge old storage checkpoints, to remove the storage checkpoints that are no longer needed. 106 VERITAS Database Edition for Oracle Administrator’s Guide 4 Display storage checkpoint information Use this menu operation to display the list of storage checkpoints created only by vxdba on the file systems used by the Oracle instance. Storage checkpoints created by NetBackup can only be accessed by NetBackup and will not be displayed. This operation also lists the storage checkpoints that are partially complete. A storage checkpoint is partially complete if any one of the file systems does not contain the named storage checkpoint. A storage checkpoint on a file system can be removed when the file system has no more free space. A partially complete storage checkpoint can still be used to roll back database files. File can only be rolled backward on those file systems that contain valid storage checkpoint. A whole database rollback is not possible with a storage checkpoint that is partially complete. --------------------------------------------------VXDBA: Getting checkpoint information -- DEMO --------------------------------------------------ORACLE_SID: ORACLE_HOME: Oracle Release: # Tablespaces: # Datafiles: DEMO /oracle/app/oracle/product/8.0.4 8.0.4.0.0 11 11 Checkpoint ------------------------Checkpoint_904769753 Checkpoint_903937870 Creation Time ------------------------Wed Sep 2 13:55:53 1998 Sun Aug 23 22:51:10 1998 Status -------- Press to continue... Database Backup and Restore 107 4 Create a new storage checkpoint Use this menu operation to create a storage checkpoint for the database instance. A storage checkpoint for a database is a collection of storage checkpoints created at exactly the same time on the file systems where the database files reside. The database can be offline or online when a storage checkpoint is created. If the database is online when the storage checkpoint is created, vxdba will switch the database to ‘hot backup’ mode before creating the storage checkpoint. Once the storage checkpoint is created, vxdba will switch the database back to its normal operation mode. The following is an example display when this menu operation is selected while the database is still online: --------------------------------------------------VXDBA: Creating new checkpoint -- DEMO --------------------------------------------------ORACLE_SID: ORACLE_HOME: Oracle Release: # Tablespaces: # Datafiles: DEMO /oracle/app/oracle/product/8.0.4 8.0.4.0.0 11 11 Do you want to create a new storage checkpoint? [y,n,q,?] (default: y) y VXDBA: Oracle instance DEMO is still running. A storage checkpoint can be created using hot database backup mode (using alter tablespace ... begin backup). Do you want to alter tablespaces for taking the storage checkpoint? [y,n,q,?] (default: n) 108 VERITAS Database Edition for Oracle Administrator’s Guide 4 A storage checkpoint can be considered as an online copy of a database backup which contains a snapshot copy of the database image when the storage checkpoint was created. These storage checkpoints can later be used to restore the image of a file, a tablespace, or the entire database to any state recorded by the storage checkpoints. Purge old storage checkpoints This menu operation is used to remove any storage checkpoints that are no longer needed. --------------------------------------------VXDBA: Purge old checkpoints -- DEMO --------------------------------------------ORACLE_SID: ORACLE_HOME: Oracle Release: # Tablespaces: # Datafiles: # --1 2 DEMO /oracle/app/oracle/product/8.0.4 8.0.4.0.0 11 11 Checkpoint ------------------------Checkpoint_904769753 Checkpoint_903937870 Creation Time ------------------------Wed Sep 2 13:55:53 1998 Sun Aug 23 22:51:10 1998 Status ---------- Do you want to remove any of these checkpoints? [y,n,q,?] (default: n) y Enter a number or a range to purge the checkpoints.Enter ’all’ to purge all the checkpoints [,-,all,q,?] 2 Do you want to remove Checkpoint_903937870? [y,n,q,?] (default: n) y Checkpoint 2: Checkpoint_903937870 removed. Updating VXDBA database... Done. Press to continue... Database Backup and Restore 109 4 Restore files from storage checkpoint This menu operation is used to restore a database file, a list of database files, files of a single tablespace, or the entire database using a storage checkpoint. The following screen is displayed when the menu option is selected: Database/Storage Rollback Menu: Database Main/Storage Rollback ORACLE_SID ORACLE_HOME Oracle Release : DEMO : /oracle/app/oracle/product/8.0.4 : 8.0.4.0.0 1 2 3 Roll backward the database to a checkpoint Restore a tablespace to a checkpoint Restore files to a checkpoint ? ?? q Display help about menu Display help about the menuing system Exit from menus Select an operation to perform: Storage checkpoints can only be used to restore files that are damaged due to a software error or a human error (for example, accidental deletion of a table). Because storage checkpoints reside on the same physical disks as the primary file system, when a file is corrupted due to a media failure, the file on the storage checkpoints will not be available either. In this case, restoring files from a tape backup is needed. Once the files are restored, you may need to follow the recovery procedure described in the Oracle manuals to recover the database before the database can be used. 110 VERITAS Database Edition for Oracle Administrator’s Guide 4 Rollback the database to a checkpoint This menu operation is used to restore the entire database to a storage checkpoint. When this option is selected, vxdba will restore all the datafiles used by the database, except the redo logs, to an earlier storage checkpoint. vxdba will first display a list of storage checkpoints for selection. After a storage checkpoint is selected, vxdba will roll back every database file in parallel. The following is a sample output during a database rollback. --------------------------------------------VXDBA: Rollback the database -- DEMO --------------------------------------------ORACLE_SID: ORACLE_HOME: Oracle Release: # Tablespaces: # Datafiles: DEMO /oracle/app/oracle/product/8.0.4 8.0.4.0.0 11 11 From the following checkpoint list, select a checkpoint state to roll back the database. # --1 2 Checkpoint ------------------------Checkpoint_904769753 Checkpoint_903937870 Creation Time ------------------------Wed Sep 2 13:55:53 1998 Sun Aug 23 22:51:10 1998 Enter a checkpoint number [,q,?] Status ---------- 1 Do you want to roll back the database to Wed Sep 2 13:55:53 1998? [y,n,q,?] (default: n) y Restoring the database using Checkpoint_904769753... Rollback status: ... Press to continue... Database Backup and Restore 111 4 Restore a tablespace to a storage checkpoint If a tablespace is corrupted or removed due to a software error or a human mistake, this menu operation can be used to restore all of the files of the corrupted/removed tablespace using an earlier storage checkpoint. A screen similar to the following is displayed when this menu operation is selected: --------------------------------------------VXDBA: Roll back a tablespace -- DEMO --------------------------------------------ORACLE_SID: ORACLE_HOME: Oracle Release: # Tablespaces: # Datafiles: DEMO /oracle/app/oracle/product/8.0.4 8.0.4.0.0 11 11 Select a tablespace: TABLESPACE_NAME -----------------------------CUST HIST ITEMS NORD ORD ORDL ROLL STOCKS SYSTEM TEMP WARE FILE_NAME ----------------------/tpcc_disks/cust1 /tpcc_disks/hist1 /tpcc_disks/item1 /tpcc_disks/nord1 /tpcc_disks/ord1 /tpcc_disks/ordl1 /tpcc_disks/roll1 /tpcc_disks/stk1 /tpcc_disks/sys1 /tpcc_disks/temp1 /tpcc_disks/ware1 Enter a tablespace name [,q,?] STOCKS 112 VERITAS Database Edition for Oracle Administrator’s Guide 4 vxdba will prompt for a tablespace name for rollback. When a tablespace name is entered, a list of storage checkpoints will be displayed. After a storage checkpoint is selected, vxdba will roll back the files of the selected tablespace in parallel. The following is an example session of a tablespace rollback: TABLESPACE: STOCKS # FILE_NAME --- ------------------------------------1 /tpcc_disks/stk1 Do you want to restore the files used by STOCKS using an earlier checkpoint? [y,n,q,?] (default: n) y From the following checkpoint list, select the checkpoint state to roll backward the files used by the tablespace. Use the ’Restore files from checkpoints’ option if you only plan to roll back a single file or any particular set of files. # --1 2 Checkpoint ------------------------Checkpoint_904769753 Checkpoint_903937870 Creation Time ------------------------Wed Sep 2 13:55:53 1998 Sun Aug 23 22:51:10 1998 Enter a checkpoint number [,q,?] Status ---------- 1 Do you want to restore tablespace STOCKS to Wed Sep 2 13:55:53 1998 ? [y,n,q,?] (default: n) y Restoring tablespace STOCKS using Checkpoint_904769753... Rollback status: ... Press to continue... Database Backup and Restore 113 4 Restore files to a storage checkpoint This menu operation is used to restore any database files to an earlier storage checkpoint. This menu operation can be used if more than one tablespace needs to be rolled back. The list of files can be specified in a list file and vxdba will use the list file for rollback. Or, the list of files can be entered one by one. When this menu operation is selected, a screen like the following is displayed prompting for the name of the list file. If there is a list file containing the files for rollback, enter the file name. Otherwise, press Return to enter the file names one by one. --------------------------------------------VXDBA: Roll back a tablespace -- DEMO --------------------------------------------ORACLE_SID: ORACLE_HOME: Oracle Release: # Tablespaces: # Datafiles: DEMO /oracle/app/oracle/product/8.0.4 8.0.4.0.0 11 11 To roll back files to a checkpoint, enter the name of a list file that contains the set of files for rollback or press to enter a list of filenames one by one. Enter the list file name [,,q,?] 114 VERITAS Database Edition for Oracle Administrator’s Guide 4 The following is a sample display when a list of files is entered one by one. Press Return to end the list. For example, the following screen shows two files: /tpcc_disks/stk1 and /tpcc_disks/cust1 need to be rolled back. --------------------------------------------VXDBA: Roll back files -- DEMO --------------------------------------------ORACLE_SID: ORACLE_HOME: Oracle Release: # Tablespaces: # Datafiles: DEMO /oracle/app/oracle/product/8.0.4 8.0.4.0.0 11 11 Enter a list of filenames for rollback. To end the list, enter . Enter file name [,,q,?] /tpcc_disks/stk1 Enter file name [,,q,?] /tpcc_disks/cust1 Enter file name [,,q,?] # --1 2 FILENAME ---------------------------------------/tpcc_disks/stk1 /tpcc_disks/cust1 Do you want to restore these files using an earlier checkpoint? [y,n,q,?] (default: n) y Database Backup and Restore 115 4 vxdba will display a list of storage checkpoints for selection. After a storage checkpoint is selected, vxdba will then roll back the files using the selected storage checkpoint. When all the files are rolled back, the Oracle recovery procedure must be followed to recover the database before the database can be used. From the following checkpoint list, select the checkpoint state to restore the files. # --1 2 Checkpoint ------------------------Checkpoint_904769753 Checkpoint_903937870 Creation Time ------------------------Wed Sep 2 13:55:53 1998 Sun Aug 23 22:51:10 1998 Enter a checkpoint number [,q,?] Status ---------- 1 Do you want to continue restoring files back to Wed Sep 2 13:55:53 1998 ? [y,n,q,?] (default: n) y Restoring files using Checkpoint_904769753... Restore status: ... You must follow the recovery procedure described in the Oracle documentation to recover the database before you can use the database. 116 VERITAS Database Edition for Oracle Administrator’s Guide 4 File system space usage administration This menu operation can be used to monitor the space usage of each storage checkpoint and to enable or disable the VERITAS Storage Manager’s space alarm. A space alarm is a Storage Manager’s agent that monitors the file system space usage. When the space usage reaches a threshold value, a predefined action script will be started to grow the file system automatically. The following screen is displayed when this menu operation is selected: Filesystem Space Alarm Administration Menu: Database Main/Filesystem Space Alarm Administration ORACLE_SID ORACLE_HOME Oracle Release Status : : : : DEMO /oracle/app/oracle/product/8.0.4 8.0.4.0.0 ONLINE 1 Display filesystem space usage 2 3 4 Display filesystem space alarm Enable filesystem space alarm Disable filesystem space alarm ? ?? q Display help about menu Display help about the menuing system Exit from menus Select an operation to perform: Database Backup and Restore 117 4 Display file system space usage This menu operation is used to display the space usage of the file systems and the storage checkpoints used by the Oracle instance. When this menu operation is selected, the space usage of the file systems and their storage checkpoints on each file system are displayed. The following is a sample output screen: -----------------------------------------------------VXDBA: Displaying filesystem space usage info -- DEMO -----------------------------------------------------ORACLE_SID: ORACLE_HOME: Oracle Release: # Tablespaces: # Datafiles: # Filesystems: DEMO /oracle/app/oracle/product/8.0.4 8.0.4.0.0 11 21 1 File System ----------------------------------/tpcc_disks FS Size Used Avail %full -------- -------- -------- ----8192MB 4622MB 3458MB 58% Storage Checkpoint Size -------------------- --------primary 4620MB Checkpoint_904769753 840KB Press to continue... In this example, the space used by Checkpoint_904769753 is less than 1M which means the checkpoint does not contain many data blocks. This may mean that the storage checkpoint was created recently or the database has had very little update since the storage checkpoint. This display can be used to monitor the database change history, for example, on a daily basis. The change history can be used in capacity planning to forecast the additional disk space needed for storage checkpoints. 118 VERITAS Database Edition for Oracle Administrator’s Guide 4 Display file system space alarm This menu operation is used by the system administrator (in other words, root) to display the information about the space alarm defined on the file systems used by the Oracle instance. Only the user with the super-user privilege can select this menu operation. The space alarm relies on VERITAS Storage Manager’s Agent. The Agent daemon processes must be running first. If the Agent daemons are not running, a message will be displayed asking the system administrator to start the agent daemons. The menu operation displays the list of file systems and the space alarm status (enabled or disabled). The following is a sample display when this menu operation is selected: -----------------------------------------------------VXDBA: Displaying filesystem space alarm info -- DEMO -----------------------------------------------------ORACLE_SID: ORACLE_HOME: Oracle Release: # Tablespaces: # Datafiles: # Filesystems: DEMO /oracle/app/oracle/product/8.0.4 8.0.4.0.0 11 11 1 File System Status ----------------------------------- --------/tpcc_disks disabled Press to continue... Database Backup and Restore 119 4 Enable file system space alarm This menu operation is used by the system administrator (in other words, root) to enable the space alarm that monitors the space usage of the file systems used by the Oracle instance. Only the user with the super-user privilege can select this menu operation. The space alarm relies on VERITAS Storage Manager’s Agent. The Agent daemon processes must be running first. If the Agent daemons are not running, a message will be displayed asking the system administrator to start the agent daemons. The space alarm should be enabled when the storage checkpoints are not to be removed by VxFS. When the file system runs out of the space, VxFS will automatically remove the storage checkpoints to free up space. This could happen when Oracle is processing update transactions such that original data blocks are saved in the storage checkpoints. Without the space alarm to grow the file systems automatically, the storage checkpoints will be removed automatically by VxFS. The following is a sample display when the menu option is selected: --------------------------------------------------VXDBA: Enabling Filesystem space alarm -- DEMO --------------------------------------------------ORACLE_SID: ORACLE_HOME: Oracle Release: # Tablespaces: # Datafiles: # Filesystems: DEMO /oracle/app/oracle/product/8.0.4 8.0.4.0.0 11 11 1 File System Status ----------------------------------- --------/tpcc_disks disabled Do you want to enable the space alarm to monitor filesystem space usage? [y,n,q,?] (default: y) y Space Alarm enabled on /tpcc_disks. 120 VERITAS Database Edition for Oracle Administrator’s Guide 4 Disable file system space alarm This menu operation is used by the system administrator (in other words, root) to disable the space alarm that monitors the space usage of the file systems used by the Oracle instance. Only the user with the super-user privilege can select this menu operation. The space alarm relies on VERITAS Storage Manager’s Agent. The Agent daemon processes must be running first. If the Agent daemons are not running, a message will be displayed asking the system administrator to start the agent daemons. The space alarm can be disabled if the file systems have sufficient free space to hold the storage checkpoints or the removal of the storage checkpoints will not cause any problem. When a file system that contains storage checkpoints runs out of space, VxFS is forced to remove the storage checkpoints to free up the space if the space alarm is disabled. The following is a sample display when the menu option is selected: --------------------------------------------------VXDBA: Disabling Filesystem space alarm -- DEMO --------------------------------------------------ORACLE_SID: ORACLE_HOME: Oracle Release: # Tablespaces: # Datafiles: # Filesystems: DEMO /oracle/app/oracle/product/8.0.4 8.0.4.0.0 11 11 1 File System Status ----------------------------------- --------/tpcc_disks enabled Do you want to disable the space alarm? [y,n,q,?] (default: y) y Space Alarm disabled on /tpcc_disks. Database Backup and Restore 121 4 122 VERITAS Database Edition for Oracle Administrator’s Guide Glossary atomic operation An operation that either succeeds completely or fails and leaves everything as it was before the operation was started. If the operation succeeds, all aspects of the operation take effect at once and the intermediate states of change are invisible. If any aspect of the operation fails, then the operation aborts without leaving partial changes. block The minimum unit of data transfer to a disk or array. block-level incremental backups Block-level incremental backup is a method to back up only changed data blocks, not changed files, since the last backup. boot disk A disk used for booting purposes. This disk may be under VxVM control. column A set of one or more subdisks within a striped plex. Striping is achieved by allocating data alternately and evenly across the columns within a plex. concatenation A layout style characterized by subdisks that are arranged sequentially and contiguously. configuration database A set of records containing detailed information on existing Volume Manager objects (such as disk and volume attributes). A single copy of a configuration database is called a configuration copy. 123 data blocks Blocks that contain the actual data belonging to files and directories. defragmentation A method of reorganizing a file system to reduce fragmentation. device file A regular UNIX file that is accessed as a raw character device file via the Quick I/O driver. device name The device name or address used to access a physical disk, such as c0t0d0s2. The c#t#d#s# syntax identifies the controller, target address, disk, and partition. direct I/O An unbuffered form of I/O that bypasses the kernel’s buffering of data. With direct I/O, the file system transfers data directly between the disk and the user-supplied buffer. extent A group of contiguous file system data blocks that are treated as a unit. An extent is defined by a starting block and a length. Dirty Region Logging The procedure by which the Volume Manager monitors and logs modifications to a plex. A bitmap of changed regions is kept in an associated subdisk called a log subdisk. disk A collection of read/write data blocks that are indexed and can be accessed fairly quickly. Each disk has a universally unique identifier. disk access name The name used to access a physical disk, such as c0t0d0s2. The c#t#d#s# syntax identifies the controller, target address, disk, and partition. The term device name can also be used to refer to the disk access name. disk array A collection of disks logically arranged into an object. Arrays tend to provide benefits such as redundancy or improved performance. 124 VERITAS Database Edition Database Administrator’s Guide disk group A collection of disks that share a common configuration. A disk group configuration is a set of records containing detailed information on existing Volume Manager objects (such as disk and volume attributes) and their relationships. Each disk group has an administrator-assigned name and an internally defined unique ID. The root disk group (rootdg) is a special private disk group that always exists. disk media name A logical or administrative name chosen for the disk, such as disk03. The term disk name is also used to refer to the disk media name. file system A collection of files organized together into a structure. The UNIX file system is a hierarchical structure consisting of directories and files. file system block The fundamental minimum size of allocation in a file system. This is roughly equivalent to the ufs fragment size. fileset A collection of files within a file system. fixed extent size An extent attribute associated with overriding the default allocation policy of the file system. free space An area of a disk under VxVM control that is not allocated to any subdisk or reserved for use by any other Volume Manager object. hot-relocation A technique of automatically restoring redundancy and access to mirrored and RAID-5 volumes when a disk fails. This is done by relocating the affected subdisks to disks designated as spares and/or free space in the same disk group. intent logging A logging scheme that records pending changes to the file system structure. These changes are recorded in a circular intent log. Glossary 125 large file A file more than 2 gigabytes in size. An operating system that uses a 32-bit signed integer to indicate position in a file will not support large files. For this reason, VxFS does not support large files. large file system A file system more than 2 gigabytes in size. VxFS, in conjunction with the VERITAS Volume Manager (VxVM) supports large file systems. latency The amount of time it takes for a given piece of work to be completed. For file systems, this typically refers to the amount of time it takes a given file system operation to return to the user. mirror A duplicate copy of a volume and the data therein (in the form of an ordered collection of subdisks). Each mirror is one copy of the volume with which the mirror is associated. The terms mirror and plex can be used synonymously. mirroring A layout technique that mirrors the contents of a volume onto multiple plexes. Each plex duplicates the data stored on the volume, but the plexes themselves may have different layouts. object An entity that is defined to and recognized internally by the Volume Manager. The VxVM objects are: volume, plex, subdisk, disk, and disk group. There are actually two types of disk objects—one for the physical aspect of the disk and the other for the logical aspect. parity A calculated value that can be used to reconstruct data after a failure. While data is being written to a RAID-5 volume, parity is also calculated by performing an exclusive OR (XOR) procedure on data. The resulting parity is then written to the volume. If a portion of a RAID-5 volume fails, the data that was on that portion of the failed volume can be recreated from the remaining data and the parity. preallocation The preallocation of space for a file so that disk blocks will physically be part of a file before they are needed. Enabling an application to preallocate space for a file guarantees that a specified amount of space will be available for that file, even if the file system is otherwise out of space. 126 VERITAS Database Edition Database Administrator’s Guide RAID A Redundant Array of Inexpensive Disks (RAID) is a disk array set up with part of the combined storage capacity used for storing duplicate information about the data stored in that array. This makes it possible to regenerate the data if a disk failure occurs. Quick I/O Quick I/O for Databases presents a regular VERITAS File System file to an application as a raw character device. This allows Quick I/O files to take advantage of kernel-supported asynchronous I/O and direct I/O to and from the disk device as well as bypassing the UNIX single-writer lock. root disk The disk containing the root file system. This disk may be under VxVM control. root disk group A special private disk group that always exists on the system. The root disk group is named rootdg. root file system The initial file system mounted as part of the UNIX kernel startup sequence. sector A unit of size, which can vary between systems. A sector is commonly 512 bytes. slice The standard division of a logical disk device. The terms partition and slice are sometimes used synonymously. snapshot file system An exact copy of a mounted file system, as of a specific point in time. This is used for online backup purposes. snapped file system A file system whose exact image has been used to create a snapshot file system. Glossary 127 spanning A layout technique that permits a volume (and its file system or database) too large to fit on a single disk to span across multiple physical disks. storage checkpoint An efficient snapshot technology for creating a "clone" of a currently mounted VxFS file system. A storage checkpoint presents a consistent, point-in-time view of the file system by identifying and maintaining modified file system blocks. The storage checkpoint facility is an enabling technology for block-level incremental backups and storage rollback storage rollback On-disk restore capability for a faster recovery from logical errors such as accidentally deleting a file. Since each storage checkpoint is a point-in-time image of a file system, storage rollback simply restores or rolls back a file or entire file system to a storage checkpoint. stripe A set of stripe units that occupy the same positions across a series of columns. stripe unit Equally-sized areas that are allocated alternately on the subdisks (within columns) of each striped plex. In an array, this is a set of logically contiguous blocks that exist on each disk before allocations are made from the next disk in the array. A stripe unit may also be referred to as a stripe element. stripe unit size The size of each stripe unit. The default stripe unit size is 32 sectors (16K). A stripe unit size has also historically been referred to as a stripe width. striping A layout technique that spreads data across several physical disks using stripes. The data is allocated alternately to the stripes within the subdisks of each plex. throughput A measure of work accomplished in a given amount of time. For file systems, this typically refers to the number of I/O operations in a given unit of time. ufs The Solaris name for a file system type derived from the 4.2 Berkeley Fast File System. 128 VERITAS Database Edition Database Administrator’s Guide unbuffered I/O I/O that bypasses the file system cache for the purpose of increasing I/O performance (also known as direct I/O). transaction A set of configuration changes that succeed or fail as a group, rather than individually. Transactions are used internally to maintain consistent configurations. volume A virtual disk, representing an addressable range of disk blocks used by applications such as file systems or databases. A volume is a collection of from one to 32 plexes. vxfs The name of the VERITAS File System type. Glossary 129 130 VERITAS Database Edition Database Administrator’s Guide Configuration Considerations A Introduction This appendix provides a number of considerations when configuring Oracle database files using the VERITAS Database Edition for Oracle 2.0. These considerations are intended to highlight specific infomation to save you time and maximize your use of the VERITAS Database Edition. The following topics are discussed: • Disk Management Strategies • Datafile Layout Strategies • Backup Strategies • Tuning Considerations Disk Management Strategies • Group all disks that are to be used by a specific database into one disk group, for example with a name SIDdg. • Ensure that there are enough spare drives for the hot relocation feature. • Ensure that the spare drives are spread across multiple controllers. • Use RAID 0 (striping) to improve performance if some database downtime can be tolerated. Consider using at least 4 drives per striped volume. Remember that increasing the number of columns in a striped volume decreases the Mean Time Between Failures for the Striped volume. Stripe across multiple controllers whenever possible. 131 A • If no database downtime can be tolerated, consider using mirroring to protect against drive failures. Mirroring and striping (RAID 0+1) configuration could be used to provide good performance with redundancy. • If disk storage is at a premium, consider using RAID-5 to improve redundancy. Isolate files that are read-intensive to a file system built on a RAID-5 volume. Do NOT use RAID-5 for write-intensive workloads. • When mirroring is used, enable the Dirty Region Logging feature to take advantage of the fast resync after a system failure. • When mirroring is used, ensure that the two mirrored plexes reside on different drives and preferably on different controllers. Do not disable the hot relocation facility. Datafile Layout Strategies • Isolate Oracle log files to a separate file system on a separate volume This file system should be built on a mirrored volume if possible. Log files should not be located on the same drives as those housing other Oracle data files. • For block-level incremental backups, Netbackup can stream data from multiple filesystems to a single tape drive, but is limited to 1 stream per file system. Hence, the Oracle datafiles should be spread among multiple file systems if multiple streams are desired. • When possible, the datafile placement on file systems should be balanced in terms of the amount of updates/inserts occuring to the datafiles. • Each file system should include an additional 10% storage space to provide for the file system structures. • If you are going to use BLI backup, additional disk storage on each file system is needed to keep track of changed blocks. Normally, an additional 10%-15% disk space is adequate. • Use the command qiomkfile to pre-allocate Oracle datafiles. This ensures that the file is created as a single large extent, or as a chain of direct extents. Using direct extents is inherently faster than using indirect extents. • For SAP installations, ensure that absolute links names are used for Quick I/O files. Without this, performing a backup and restore using brbackup can cause problems. 132 VERITAS Database Edition Database Administrator’s Guide A • For SAP installations, check the length of the Quick I/O file names. If the length of the link name that the Quick I/O file uses is very long, it could cause online backups via SAP (with the backup device set to util_file_online) to fail. To avoid this problem, configure Quick I/O files to go through a second shorter link. Backup Strategies • Use BLI backup to backup all Oracle datafiles. • If you have, for example, four tape drives in a robot, do NOT configure BLI backup to use all four tapes; otherwise, it will be difficult to deal with a bad drive without reconfiguring the NetBackup classes. • To reduce network traffic, consider setting up your database host as a NetBackup slave server. • Use user-directed backups to back up Oracle archive log files. Do not use BLI backup to backup Oracle online redo log files. • Datafiles should be setup so that the backup process can operate the tape drives in streaming mode. Tuning Considerations • Increase the Oracle parameter db_block_buffers so that the Oracle Hit Ratio is 85% or more. • If additional memory is available on the system, experiment and enable Cached Quick I/O for certain files that benefit from the caching. Configuration Considerations 133 A 134 VERITAS Database Edition Database Administrator’s Guide Index A allocation extent-based, 5 allocation policies block-based, 5 extent-based, 5 ufs, 5 vxfs, 5 allocation units, 129 autoextend, 46 B blocks data, 124 buffer cache, 62 C Cached Quick I/O, 4 cdev files accessing regular UNIX files, 43 Checkpoints, 7 concatenation, 10 consistency checking file systems, 33 creating, 29 a file system existing volume, 31 mirrored volume, 30 new volume, 22 simple volume, 23 striped volume, 25, 27 D data redundancy, 11 data persistence, 74 data warehousing, 12 database tuning, 62 database backup, 7 using snapshot file system, 68 using snapshot file systems, 71, 75, 78, 85, 91, 97 using snapshot volumes, 86, 87 snapshot volumes database backup, 68 database files creating, 40 DB_BLOCK_BUFFERS, 62 definition extent, 5 defragmentation, 124 direct I/O, 124 dirty region logging, 21 disk arrays, 10 135 disk group adding disks, 18 configuration guidelines, 17 creating, 17 naming a disk group, 17 disks failure and hot-relocation, 12 displaying file system properties, 34 DRL, 21 DSS workloads guidelines, 21 expansion, 6 fragmentation, 6 growing, 36 mounting, 32, 39 properties, displaying, 34 resizing, 36 snapped, 71 snapshot, 75, 92 unmounting, 39 fileset, 125 fixed extent size, 125 fragmentation, 6 fsadm, 39 E G extent, 5, 124 extent attributes, 5 extent-based allocation, 5 grow the underlying Quick I/O file, 47 guidelines creating a disk group, 17 creating file systems, 22 database backup, 74 for DSS workloads, 21 for OLTP workloads, 21 for redo logs, 20 striped volumes, 21 volumes, 20 F fast database recovery, 6 fast file system recovery, 6 fast recovery, 21 file pre-allocation, 5 file system fast recovery, 6 growing, 36 large, 7 file system block, 125 file systems, 39 and volume size, 38 checking, 33 configuration guidelines, 22 creating existing volume, 31 mirrored volume, 30 new volume, 22 simple volume, 23 striped volume, 25, 27 using command line, 38 136 H hot-relocation, 12 I I/O sequential, 5 statistics obtaining, 56, 61 unbuffered, 129 I/O statistics file I/O, 56 volume I/O, 61 intent logging, 6, 125 VERITAS Database Edition for Oracle Administrator’s Guide L large file systems, 7 latency, 126 M mirrored volumes adding a DRL log, 31 mirroring, 11 choosing, 20 mkfs, 38 mount point display, 21 mounting a file system, 32, 39 O OLTP workloads guidelines, 21 options qiomkfile, 42 P parity, 11 performance RAID-5, 11 performance data using, 57 preallocation, 126 Q qiomkfile options, 42 Quick I/O for Databases, 2, 3 R RAID, 10 RAID-0, 10 RAID-0+1, 10, 11 Index RAID-1, 10, 11 RAID-5, 10, 11, 20 choosing, 20 creating, 29 performance, 20 RAID-5 log, 21, 29 redo logs configuration guidelines, 20, 22 creating a file system, 22 removing a snapshot, 89 resizing, 39 resynchronization using DRL logs, 21 using RAID-5 logs, 21 S sequential I/O using extent-based allocation, 5 sequential scan tuning, 62 snapped file systems, 71, 127 unmounting, 76 snapshot, 90 creating, 80 database backup snapshot file systems, 7 removing, 89 snapshot file system, 7 snapshot file systems, 75, 92, 127 create, 79, 80 using command line, 82 database backup, 68, 78, 97 fsck, 78 fuser, 76 monitoring block usage, 77 performance, 77, 92 remove, 81 using command line, 82 size, 76 snapshot volumes 137 create, 87 using command line, 89 creating, 86 database backup, 86, 87 remove, 89 using command line, 90 Spanning, 10 stripe, 10 stripe unit sizes choosing, 20 stripe units, 10 striped volumes, 21 configuration guidelines, 21 Striping, 10 T throughput, 128 tuning database, 62 DB_BLOCK_BUFFERS, 62 sequential scan, 62 vxfs, 56 VxVM, 60 U unmount snapped file system, 76 snapshot file system, 76 upgrade from raw devices, 53 from UFS, 51 from VxFS 1.2.x, 51 from VxFS 2.2.x, 52 using performance data, 57 using snapshot volumes database backup, 71 VERITAS FirstWatch, 2 VERITAS FirstWatch Agent for Oracle, 3 VERITAS Visual Administrator, 2 VERITAS Volume Manager, 1 Visual Administrator, 13 volume layouts selecting, 19 Volume Manager and RAID, 10 objects, 9 volume resynchronization, 13 volumes, 8, 9 and file system size, 38 configuration guidelines, 20 creating, 37 definition, 9 resynchronization, 13 vxassist, 37, 86, 90 vxfddstat, 56 VxFS tuning, 56 vxresize, 39 vxstat, 57, 61 vxtrace, 77 vxtunefs, 60 VxVA, 13 VxVM tuning, 60 W wordloads write-intensive, 21 V VERITAS File System, 1 138 VERITAS Database Edition for Oracle Administrator’s Guide