Oracle数据库管理员手册

ORACLE管理工作标准手册ORACLE管理工作标准手册一、引言随着信息技术的迅猛发展，数据库管理工作变得越来越重要。

数据库管理工作的标准化和规范化对于提高工作效率、确保数据安全和准确性具有举足轻重的意义。

本手册旨在建立起一个统一的ORACLE数据库管理工作标准，对相关工作进行规范和指导，以提高工作质量和效率。

二、数据库管理职责1. 数据库创建与维护：负责数据库的创建、备份、恢复、维护，确保数据库的稳定运行。

2. 数据库性能优化：负责分析数据库性能瓶颈，制定相应的优化方案，提高数据库的响应速度。

3. 数据安全管理：负责数据库的安全策略制定和执行，确保数据的安全性和完整性。

4. 数据库监控与预警：负责实时监控数据库运行状况，及时处理异常情况并提供预警。

5. 数据备份与恢复：负责数据库的定期备份和恢复，确保数据的可靠性和可用性。

6. 数据库版本升级和迁移：负责数据库版本的升级和迁移，确保系统的平稳过渡。

三、数据库管理工作标准1. 数据库命名规范：数据库应根据业务功能或业务部门进行命名，名称应具有可理解和易于识别的特点。

2. 数据库表结构规范：表名、字段名应具有相关性，使用英文小写字母和下划线分割，避免使用特殊字符或中文命名。

3. 数据库连接规范：连接数据库应使用唯一的用户名和密码，并设置正确的权限以控制对数据库的访问。

4. 数据库备份规范：定期制定数据库备份计划，包括全量备份和增量备份，并存储在独立的备份设备上，避免与数据库服务器放在同一设备上。

5. 数据库恢复规范：备份数据应按照备份计划进行恢复，恢复过程应进行测试和验证，确保数据的完整性和一致性。

6. 数据库性能优化规范：对于性能较差的SQL语句，应进行分析和优化，采用索引、分区等技术提高查询效率。

7. 数据库安全策略规范：完善数据库用户权限管理，禁止使用默认用户名和密码，并及时屏蔽不再使用的用户账号。

8. 数据库监控规范：使用数据库性能监控工具进行实时监控，设置阈值并及时处理异常情况。

acle的文件 1）Oracle数据库由三种文件类型组成 数据库文件 一个数据库至少有一个数据文件 一个数据文件只能与一个数据库相关 可以为数据文件设置某些特性以便它们在数据库运行空间不足时能够自扩展 一个或多个数据文件形成数据库存储的逻辑单元这个单元称为表空间 重做日志 包含对数据库所做的更改记录， 这样万一出现故障可以启用数据恢复。 一个数据库 至少需要两个重做日志文件 控制文件 包含维护和验证数据库完整性的必要信息、 例如， 控制文件用于识别数据文件和重 做日志文件，一个数据库至少需要一个控制文件 2）其它关键文件：Oracle 服务器也使用一些其它文件，这些文件并不是数据库的一部分 参数文件 定义Oracle 例程的特性，例如它包含调整SGA 中一些内存结构大小的参数 口令文件 认证哪些用户有权限启动和关闭Oracle例程 归档的重做日志文件 是重做日志文件的脱机副本，这些副本可能对于从介质失败中进行恢复很必要。 2、oracle例程：Oracle 例程由SGA 内存结构和用于管理数据库的后台进程组成。例程一次只 能打开和使用一个数据库。 3、SGA：是用于存储数据库信息的内存区，该信息为数据库进程所共享。它包含Oracle 服务 器的数据和控制信息, 它是在Oracle 服务器所驻留的计算机的实际内存中得以分配， 如果实际内存不够再往虚拟内存中写。SGA 由几种内存结构组成： shared pool:用于存储最近执行的SQL语句和最近使用的数据字典数据。这些SQL 语句 可以是用户进程提交的，也可以是从数据字典读取的在存储过程的情况中，共享池 的大小由SHARED_POOL_SIZE 决定,它包括两个方面。 Library cache: 包含最近使用的SQL语句信息, 分析代码和执行计划。如果重 新执行SQL 语句而且共享SQL 区域已经包含语句的执行计划, 那么服务器进 程就不需要分析语句。 如果SQL 语句没有重新使用， 那么它最终会从库高速缓 存中超龄释放 Data dictionary cache：它包括有关数据库文件表索引列用户权限以及其它 数据库对象的信息。在分析阶段，服务器进程在字典高速缓存中查找信息，以 解析SQL语句中指定的对象名并验证访问权限。如果有必要，服务器进程启动 从数据文件对该信息的加载

第17章配置客户机/服务器和Web 网络环境由于O r a c l e 支持许多不同的配置和平台，所以还没有一套对每个客户机/服务器或瘦客户机环境有效的规范说明。

不过，有许多可用于网络环境配置的常规过程。

有关基本网络配置和U N I X 配置的详细情况，请参见第1 3章和第1 4章。

本章描述客户机/服务器和瘦客户机环境的网络配置准则。

17.1 客户机/服务器处理概述使用客户机/服务器配置可以把C P U 和对应用程序的处理负载分布在两个机器上。

第一个机器叫做客户机，它支持发出数据库请求的应用程序。

数据库所驻留的后端叫做服务器。

当数据库服务器致力于查询而不是应用程序时，客户机可以承受 C P U 负载的大部分。

图1 7-1示出了这种资源需求的分布。

与图1 7-1所示的配置稍有不同的是把应用程序的逻辑移到存储在数据库中的P L /S Q L 软件包。

在这种配置中，数据库服务器承受应用处理和数据访问的 C P U 负载，而客户机致力于数据的图形显示。

这种环境的网络配置与图1 7-1相同，但应用程序执行对P L /S Q L 软件包的调用而不是执行i n s e r t 、u p d a t e 和d e l e t e 命令。

当客户机向服务器发出一个数据库请求(通过S Q L *N e t 或N e t 8)时，服务器接收并执行给它的S Q L 语句。

S Q L 语句的结果要加上返回的所有错误信息，一起返回到客户机。

若要使用客户机/服务器结构，客户机和服务器必须能相互进行通信。

这意味着两个机器间有一个硬件连接。

每个机器都必须支持使它们能交换数据的通信协议。

可以使用S Q L *N e t V 2多协议交换或N e t 8连接管理器来解决各种协议之间的兼容性问题。

本章将描述两种配置：客户机/服务器配置和基于We b (瘦客户机)的配置。

首先，设想一个客户机运行Microsoft Wi n d o w s 和SQL*Net V2，它通过网络接口卡(NIC, network interface c a r d )连接到以太网网络上。

Oracle® Database Storage Administrator's Guide 11g Release 2 (11.2)E10500-01August 2009Oracle Database Storage Administrator's Guide, 11g Release 2 (11.2)E10500-01Copyright © 2007, 2009, Oracle and/or its affiliates. All rights reserved.Contributing Authors: Jerry Abraham, Prasad Bagal, Mark Bauer, Eric Belden, Bill Bridge, Chan Chan, Xiaobin Chen, Hanlin Chien, Jonathan Creighton, Steve Fogel, Dave Friedman, Barb Glover, Fred Glover, Shie-rei Huang, Diane Lebel, Rich Long, Colin McGregor, Gregory Mengel, Valarie Moore, Darshan Nagarajappa, Harish Nandyala, Bob Nelson, Satish Panchumarthy, Kathy Rich, Francisco Sanchez, Santhosh Selvaraj, Ara Shakian, Duane Smith, Joshua Smith, Malai Stalin, Amar Subba, Yaser Suleiman, Michael Timpanaro-Perrotta, Nitin Vengurlekar, Anton Verhulst, Radek Vingralek, Ron Weiss, Rajiv Wickremesinghe, Jim A. Williams, Sivakumar Yarlagadda, Song Ye, Krishnan Yegnashankaran, Hector Yuen This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.If this software or related documentation is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA 94065.This software is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications which may create a risk of personal injury. If you use this software in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure the safe use of this software. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software in dangerous applications.Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.This software and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.ContentsPreface (xxi)Audience (xxi)Documentation Accessibility (xxi)Related Documents (xxii)Conventions (xxii)What's New in Oracle Database Storage? (xxiii)New Storage Features for Oracle Database 11g Release 2 (11.2) (xxiii)New Storage Features for Oracle Database 11g Release 1 (11.1) (xxvi)1 Introduction to Oracle Automatic Storage ManagementOverview of Oracle Automatic Storage Management...................................................................... 1-1 Understanding Oracle ASM Concepts................................................................................................. 1-2 About Oracle ASM Instances........................................................................................................... 1-2 About Oracle ASM Disk Groups..................................................................................................... 1-6 About Mirroring and Failure Groups............................................................................................. 1-6 About Oracle ASM Disks.................................................................................................................. 1-7 Allocation Units ......................................................................................................................... 1-7 About Oracle ASM Files.................................................................................................................... 1-7 Extents.......................................................................................................................................... 1-8Oracle ASM Striping................................................................................................................... 1-9File Templates.............................................................................................................................. 1-9 Understanding Oracle ASM Disk Group Administration............................................................... 1-9 About Discovering Disks...............................................................................................................1-10 About Mounting Disk Groups......................................................................................................1-10 About Adding and Dropping Disks.............................................................................................1-10 About Online Storage Reconfigurations and Dynamic Rebalancing......................................1-10 2 Considerations for Oracle ASM StorageStorage Resources for Disk Groups...................................................................................................... 2-1 Oracle ASM and Multipathing.............................................................................................................. 2-3 Recommendations for Storage Preparation........................................................................................ 2-4iii3 Administering Oracle ASM InstancesOperating With Different Releases of Oracle ASM and Database Instances Simultaneously3-1 Configuring Initialization Parameters for an Oracle ASM Instance............................................. 3-2 Initialization Parameter Files for an Oracle ASM Instance.......................................................... 3-3 Backing Up, Copying, and Moving an Oracle ASM Initialization Parameter File................... 3-4 Setting Oracle ASM Initialization Parameters............................................................................... 3-5 Automatic Memory Management for Oracle ASM............................................................... 3-5 Oracle ASM Parameter Setting Recommendations....................................................................... 3-6 ASM_DISKGROUPS................................................................................................................... 3-7ASM_DISKSTRING.................................................................................................................... 3-7ASM_POWER_LIMIT................................................................................................................ 3-8ASM_PREFERRED_READ_FAILURE_GROUPS.................................................................. 3-8DB_CACHE_SIZE....................................................................................................................... 3-9DIAGNOSTIC_DEST.................................................................................................................. 3-9INSTANCE_TYPE....................................................................................................................... 3-9LARGE_POOL_SIZE...............................................................................................................3-10PROCESSES..............................................................................................................................3-10REMOTE_LOGIN_PASSWORDFILE...................................................................................3-10SHARED_POOL_SIZE............................................................................................................3-10 Setting Database Initialization Parameters for Use with Oracle ASM....................................3-11 Managing Oracle ASM Instances.......................................................................................................3-12 Administering Oracle ASM Instances with Server Control Utility.........................................3-12 Using Oracle Restart.......................................................................................................................3-12 Starting Up an Oracle ASM Instance............................................................................................3-13 About Mounting Disk Groups at Startup.............................................................................3-15About Restricted Mode...........................................................................................................3-15 Shutting Down an Oracle ASM Instance.....................................................................................3-16 Upgrading an Oracle ASM Instance With Oracle Universal Installer.....................................3-17 Downgrading an Oracle ASM Instance.......................................................................................3-19 Active Session History Sampling for Oracle ASM.....................................................................3-21 Using Oracle ASM Rolling Upgrade.................................................................................................3-21 Patching Oracle ASM Instances.........................................................................................................3-22 Authentication for Accessing Oracle ASM Instances....................................................................3-22 About Privileges for Oracle ASM.................................................................................................3-23 Using One Operating System Group for Oracle ASM Users............................................3-23Using Separate Operating System Groups for Oracle ASM Users...................................3-24The SYSASM Privilege for Administering Oracle ASM....................................................3-25The SYSDBA Privilege for Managing Oracle ASM Components.....................................3-26 Creating Users with the SYSASM Privilege................................................................................3-26 Operating System Authentication for Oracle ASM ...................................................................3-26 Password File Authentication for Oracle ASM ..........................................................................3-27 Migrating a Database to Use Oracle ASM........................................................................................3-27 Using Oracle Enterprise Manager to Migrate Databases to Oracle ASM...............................3-28 Using Oracle Recovery Manager to Migrate Databases to Oracle ASM.................................3-28 Best Practices White Papers on Migrating to Oracle ASM........................................................3-28 iv4 Administering Oracle ASM Disk GroupsDisk Group Attributes............................................................................................................................ 4-2 Creating Disk Groups............................................................................................................................. 4-3 Using the CREATE DISKGROUP SQL Statement........................................................................ 4-3 Example: Creating a Disk Group..................................................................................................... 4-5 Creating Disk Groups for a New Oracle Installation.................................................................... 4-6 Specifying the Allocation Unit Size................................................................................................. 4-7 Specifying the Sector Size for Drives............................................................................................... 4-8 Oracle Cluster Registry and Voting Files in Oracle ASM Disk Groups..................................4-10 Altering Disk Groups...........................................................................................................................4-11 Adding Disks to a Disk Group......................................................................................................4-12 Example: Adding Disks to a Disk Group.............................................................................4-12 Adding Volumes to Disk Groups.................................................................................................4-13 Dropping Disks from Disk Groups..............................................................................................4-14 Example: Dropping Disks from Disk Groups......................................................................4-15 Intelligent Data Placement.............................................................................................................4-15 Resizing Disks in Disk Groups......................................................................................................4-16 Example: Resizing Disks in Disk Groups.............................................................................4-16 Undropping Disks in Disk Groups...............................................................................................4-17 Example: Undropping Disks in Disk Groups......................................................................4-17 Manually Rebalancing Disk Groups............................................................................................4-17 Example: Manually Rebalancing a Disk Group..................................................................4-18 Tuning Rebalance Operations.......................................................................................................4-18 Oracle ASM Disk Discovery ..............................................................................................................4-18 How A Disk is Discovered.............................................................................................................4-19 Disk Discovery Rules......................................................................................................................4-20 Improving Disk Discovery Time...................................................................................................4-20 Managing Capacity in Disk Groups..................................................................................................4-21 Negative Values of USABLE_FILE_MB.......................................................................................4-23 Oracle ASM Mirroring and Disk Group Redundancy..................................................................4-23 Oracle ASM Mirroring and Failure Groups................................................................................4-23 Oracle ASM Failure Groups...................................................................................................4-24How Oracle ASM Manages Disk Failures............................................................................4-25Guidelines for Using Failure Groups....................................................................................4-25Failure Group Frequently Asked Questions........................................................................4-25 Oracle ASM Recovery from Read and Write I/O Errors..........................................................4-26 Oracle ASM Fast Mirror Resync...................................................................................................4-27 Preferred Read Failure Groups.....................................................................................................4-29 Configuring and Administering Preferred Read Failure Groups....................................4-29 Performance and Scalability Considerations for Disk Groups....................................................4-30 Determining the Number of Disk Groups...................................................................................4-31 Performance Characteristics When Grouping Disks.................................................................4-31 Oracle ASM Storage Limits...........................................................................................................4-31 Disk Group Compatibility..................................................................................................................4-31 Overview of Disk Group Compatibility......................................................................................4-32 Disk Group Compatibility Attributes..........................................................................................4-33 COMPATIBLE.ASM................................................................................................................4-33vCOMPATIBLE.RDBMS...........................................................................................................4-33COMPATIBLE.ADVM............................................................................................................4-33 Setting Disk Group Compatibility Attributes.............................................................................4-34 Valid Combinations of Compatibility Attribute Settings..................................................4-34Using CREATE DISKGROUP with Compatibility Attributes..........................................4-35Using ALTER DISKGROUP with Compatibility Attributes.............................................4-35Viewing Compatibility Attribute Settings...........................................................................4-36Features Enabled By Disk Group Compatibility Attribute Settings................................4-36Reverting Disk Group Compatibility....................................................................................4-37 Considerations When Setting Disk Group Compatibility in Replicated Environments......4-38 Managing Oracle ASM File Access Control for Disk Groups......................................................4-38 About Oracle ASM File Access Control.......................................................................................4-39 Using SQL Statements to Set Disk Group Attributes for Oracle ASM File Access Control.4-40 Using SQL Statements to Manage Oracle ASM File Access Control.......................................4-41 Mounting and Dismounting Disk Groups......................................................................................4-42 Mounting Disk Groups Using the FORCE Option.....................................................................4-43 Checking the Internal Consistency of Disk Group Metadata......................................................4-44 Dropping Disk Groups........................................................................................................................4-44 Renaming Disks Groups......................................................................................................................4-45 5Introduction to Oracle ACFSOverview of Oracle ACFS...................................................................................................................... 5-1 Understanding Oracle ACFS Concepts................................................................................................ 5-3 About Oracle ACFS............................................................................................................................ 5-4 About Oracle ACFS and Oracle Database Homes........................................................................ 5-4 About Oracle ASM Dynamic Volume Manager............................................................................ 5-5 About the Oracle ACFS Driver Model............................................................................................ 5-6 About the Oracle ACFS Mount Model and Namespace.............................................................. 5-6 About the Oracle ACFS Mount Registry........................................................................................ 5-6 About Oracle ACFS Snapshots......................................................................................................... 5-6 About Oracle ACFS and Backup and Restore................................................................................ 5-7 About Oracle ACFS Integration with Oracle ASM....................................................................... 5-7 Understanding Oracle ACFS Administration.................................................................................... 5-8 Oracle ACFS and File Access and Administration Security........................................................ 5-8 Oracle ACFS and Grid Infrastructure Installation........................................................................ 5-8 Oracle ACFS and Grid Infrastructure Configuration................................................................... 5-8 Clusterware Resources and Oracle ACFS Administration.......................................................... 5-9 Oracle ACFS and Dismount or Shutdown Operations.............................................................5-10 Overview of Oracle ASM Dynamic Volume Manager..................................................................5-10 6Using Views to Display InformationViews Containing Oracle ASM Disk Group Information............................................................... 6-1 Viewing Oracle ASM File Access Control Information................................................................ 6-4 Viewing Disk Region Information................................................................................................... 6-5 Views Containing Oracle ACFS Information..................................................................................... 6-6 vi7 Administering Oracle ASM Files, Directories, and TemplatesWhat Types of Files Does Oracle ASM Support?.............................................................................. 7-1 About Oracle ASM Filenames............................................................................................................... 7-2 Single File Creation Form................................................................................................................. 7-3 Multiple File Creation Form............................................................................................................. 7-4 Fully qualified File Name Form....................................................................................................... 7-4 Alias Oracle ASM Filename Forms................................................................................................. 7-5 Creating a Tablespace in Oracle ASM: Using a Data File with an Alias Name................. 7-6 Alias Oracle ASM Filename with Template Form........................................................................ 7-6 Incomplete Oracle ASM Filename Form........................................................................................ 7-7 Incomplete Oracle ASM Filename with Template Form.............................................................. 7-7 Creating and Referencing Oracle ASM Files in the Database........................................................ 7-7 Creating Oracle ASM Files Using a Default File Location for Disk Group Specification....... 7-8 Using Oracle ASM Filenames in SQL Statements......................................................................... 7-9 Managing Alias Names for Oracle ASM Filenames......................................................................... 7-9 Adding an Alias Name for an Oracle ASM Filename...............................................................7-10 Renaming an Alias Name for an Oracle ASM Filename...........................................................7-10 Dropping an Alias Name for an Oracle ASM Filename............................................................7-10 Dropping Files and Associated Aliases from a Disk Group.....................................................7-10 Managing Disk Group Directories....................................................................................................7-11 Creating a New Directory..............................................................................................................7-11 Renaming a Directory.....................................................................................................................7-12 Dropping a Directory.....................................................................................................................7-12 Accessing Oracle ASM Files with the XML DB Virtual Folder...................................................7-12 Inside /sys/asm..............................................................................................................................7-13 Using DBMS_FILE Transfer Utility for Oracle ASM.....................................................................7-14 Managing Disk Group Templates......................................................................................................7-15 Template Attributes........................................................................................................................7-15 Adding Templates to a Disk Group.............................................................................................7-17 Modifying a Disk Group Template..............................................................................................7-18 Dropping Templates from a Disk Group....................................................................................7-18 Creating Tablespaces in Oracle ASM: Specifying Attributes with Templates.......................7-18 8 Performing Oracle ASM Data Migration With RMANOverview of Oracle ASM Data Migration.......................................................................................... 8-1 Purpose of Oracle ASM Data Migration......................................................................................... 8-1 Basic Concepts of Oracle ASM Data Migration............................................................................. 8-2 Basics Steps of Data Migration to Oracle ASM Using RMAN.................................................... 8-2 Preparing to Migrate the Database to Oracle ASM Using RMAN................................................. 8-3 Migrating the Database to Oracle ASM Using RMAN.................................................................... 8-5 Migrating a Database from Oracle ASM to Alternative Storage.................................................... 8-9 Moving Data Files Between Oracle ASM Disk Groups Using RMAN......................................... 8-9 9 Administering Oracle ASM with Oracle Enterprise ManagerOracle Automatic Storage Management Home Page........................................................................ 9-1 Accessing the Oracle ASM Home Page in Single-Instance Oracle Databases ......................... 9-2vii。