Oracle数据库规划设计和运行维护方案
Company Document number：WTUT-WT88Y-W8BBGB-BWYTT-19998
Oracle数据库规划设计
和运行维护方案
（）
目 录
1. 前言
1.1. 编写目的
随着以使用Oracle数据库的应用系统不断增加，为了加强应用系统在规划、开发、实施、维护等环节工作的规范化，特编写本文档，力求对实际的设计、实施工作起到规范和指导作用。
本方案以设计者的角度进行组织编写，其主要思路是：作为设计人员首先要了解数据库系统的运行模式，如何选择运行模式，其次要考虑系统的规划设计因素，有针对性的做好数据库的设计，提高数据库的性能，并对系统开发也提出相关要求。
1.2. 方案说明
本方案分为两大部分，其中第一部分是第二到七章，主要介绍Oracle数据库的运行方式和规划设计以及与开发相关内容；第二部分是第八到十二章，主要介绍数据库的日常运行维护相关的内容。第一部分偏重于规划和开发，第二部分偏重于实际管理及命令。
其中：第二章介绍了Oracle数据库的部署运行方式；第三章介绍了业务系统特点和数据库的类型规模以及部署前的建议；第四章介绍了数据库运行的物理环境的配置规划；第五章介绍了Oracle数据库的安装部署规划以及命名原则；第六章介绍了数据库的应用规划；第七章介绍了系统开发的建议；第八章
介绍数据库的体系架构；第九章介绍数据库的基本管理方法；第十章介绍了数据库集群的管理；第十一章介绍了数据库的备份和恢复；第十二章介绍了数据库的日常运行维护管理。
本方案编写过程中注重实际的可操作性，所提出的建议尽量覆盖系统生命周期中的各个关键点。其中所涉及的参数和指标对系统的建设和运营起指导作用，但是在实际工作中，每个系统都有各自的特点，所以建议在初期对系统进行初始配置后，应根据系统的实际运行状态进行相应调整。
1.3. 预期读者
项目基础设施可行性研究、设计和实施人员，项目组系统设计开发人员，相关运行维护技术人员。
2. 数据库部署模式
2.1. 单机模式
数据库服务器采用单服务器模式，满足对可用性和性能要求不高的应用，具备以下特点:
1、硬件成本低。单节点，硬件投入较低，满足非重要系统的需求。 2、安装配置简单。由于是单节点、单实例，所以安装配置比较简单。 3、管理维护成本低。单实例，维护成本低。
4、对应用设计的要求较低。由于是单实例，不存在RAC系统应用设计时需要注意的事项，所以应用设计的要求较低。
5、可用性不高。由于是单服务器、单实例，所以服务器和实例的故障都会导致数据库不可用。
6、扩展性差。无法进行横向扩展，只能进行纵向扩展。当应用对性能有更高的要求时，该模式的数据库服务器无法进行增加节点、实例等横向扩展，只能进行增加硬件配置等纵向扩展，且扩展性有局限。
7、根据该模式的特点有如下要求：
1) 硬件配置方面预留扩展量。由于该模式无法进行横向扩展，所以
在选择硬件配置时要为以后的纵向扩展预留扩展量，避免硬件无法满足性能需求的情况。
2)
充分考虑该模式是否满足应用未来一段时间的需求。需要考虑应
用在未来一段时间是否会发生变化，该模式是否满足应用变化的需求。
2.2. 双机热备模式（HA模式）
数据库服务器采用双机热备模式，可以满足对可用性有一定要求的应用， 具备以下特点：
1、需要冗余的服务器设备。该模式需要有冗余的服务器硬件，以满足一备一或者一备多的需求。硬件成本较高。
2、需要HA软件的支持。该模式需要配合HA软件才可以实现。
3、安装配置相对简单。该模式比单节点、单实例的模式配置复杂一些，需要更多的配置步骤，但相比较RAC、DATAGUARD等模式要简单。
4、管理维护成本低。单实例，对维护人员的要求较低，维护成本低。 5、对应用设计的要求较低。由于是单实例，不存在RAC系统应用设计时需要注意的事项，所以应用设计的要求较低。
6、具备一定的高可用性。由于是多服务器、单实例，所以服务器和实例有故障时会发生实例在不同服务器上的切换，导致数据库的暂时不可用。无法满足对可用性有严格要求的应用类型。
7、扩展性差。无法进行横向扩展，只能进行纵向扩展。当应用对性能有更高的要求时，该模式的数据库服务器无法进行增加节点、实例等横向扩展，只能进行增加硬件配置等纵向扩展，且扩展性有局限。
根据该模式的特点有如下要求： 1)
硬件配置方面预留扩展量。由于该模式无法进行横向扩展，所以
在选择硬件配置时要为以后的纵向扩展预留扩展量，避免硬件无法满足性能需求的情况。
2)
充分考虑该模式是否满足应用未来一段时间的需求。需要考虑应
用在未来一段时间是否会发生变化，该模式是否满足应用变化的需求。
2.3. 集群模式（RAC）
数据库服务器采用RAC模式，满足对高可用性要求高的应用类型， 具备以下特点:
1、需要多个硬件服务器。根据节点的个数，相应的需要多个硬件服务器。硬件成本较高。
2、某些数据库版本需要HA软件的支持。该模式下，某些数据库版本需要配合HA软件才可以实现。
3、安装配置复杂。该模式比起单实例模式，安装配置相对复杂，安装配置周期长。
4、管理维护成本高。该模式的管理维护，对管理维护人员的要求较高，管理维护成本较高。
5、对应用设计的要求较高。需要充分考虑业务的逻辑性，以避免在多节点之间的信息交换和全局锁的产生。
6、具备较高的高可用性。由于是多服务器、多实例，单服务器和实例有故障不会影响数据库的可用性。可以满足对可用性有严格要求的应用类型。
7、扩展性好。既可以进行横向扩展，也可以进行纵向扩展。当应用对性能有更高的要求时，该模式的数据库可以通过增加节点的方式进行横向扩展，也可以通过增加硬件配置等纵向扩展，具备良好的扩展性。
根据该模式的特点有如下要求：
1)硬件配置方面预留扩展量。预留一定的硬件扩展量，可以更灵活的进行扩展。
2)在应用设计时，充分考虑业务逻辑，减少多节点间的信息交换量，更好的发挥RAC的优点。
2.4. 主从模式（DataGuard）
数据库服务器采用DataGuard主从模式，可以满足对可用性有特殊需求的应用，具备以下特点：
1、需要冗余的服务器设备。该模式需要有冗余的服务器硬件。硬件成本较高。
2、需要冗余的存储设备。主机和备机都需要同样的存储空间，成本较高。 3、安装配置比较复杂。该模式比单节点、单实例的模式配置复杂一些，需要更多的配置步骤。
4、管理维护成本高。该模式对维护人员的要求较高，维护成本高。 5、具备一定的容灾特性。当主机整个数据库系统不可用并短期内无法恢复时，可以把数据库系统切换到备机上，具备容灾的功能。
6、备机可以用作只读查询。备机可以切换到只读状态供报表之类的查询操作，减轻主机的压力。
根据该模式的特点有如下要求：
1）主机与备机在物理上要分开。为了实现容灾的特性，需要在物理上分割主机和备机。
2）进行合理的设计，充分实现DATAGUARD的功能。
2.5. 混合模式（DataGrard+RAC）
数据库服务器采用DataGuard+RAC模式，可以满足对可用性和容灾都有特定需求的应用，具备以下特点：
1、需要冗余的服务器设备。该模式需要有冗余的服务器硬件。硬件成本较高。
2、需要冗余的存储设备。主机和备机都需要同样的存储空间，成本较高。 3、安装配置比较复杂。该模式既需要配置RAC又需要配置DATAGUARD，配置过程比较复杂，配置周期长。
4、管理维护成本高。该模式对维护人员的要求较高，维护成本高。 5、具备很高的可用性和容灾性。该模式既满足高可用性也满足容灾的需求。
6、备机可以用作只读查询。备机可以切换到只读状态供报表之类的查询操作，减轻主机的压力。
根据该模式的特点有如下要求：
1）主机与备机在物理上要分开。为了实现容灾的特性，需要在物理上分割主机和备机。
2）进行合理的设计，充分实现DataGuard的功能。
2.6. 数据库运行模式选择
在设计数据库时必须考虑系统的可用性、业务连续性，针对系统所能容忍的最大业务中断时间（RTO）和最大数据丢失数量（RPO）需求，采用不同的数据库部署模式：
1、系统不能中断且不允许数据丢失的业务，建议数据库采用集群或混合模式，数据库单台设备故障时对业务没有影响，并考虑灾备系统的设计。
2、对于允许以分钟级别中断，数据不能丢失的系统，建议数据库采用双机热备或主从的模式，设备故障时通过HA技术切换到备用设备，保证系统的可用性，对重要的系统要考虑灾备的设计。
3、对于允许以天为级别中断的业务系统，建议可采用双机热备模式，或单机。
4、对非关键系统、开发环境、测试环境，建议采用PC服务器、冷备或单机的模式。
3. 系统特点和数据库类型
3.1. 业务系统的特点
业务系统处理数据的特点决定了设计人员规划和创建什么样的数据库，通常来说，业务分为两类：在线事务处理系统（OLTP）和在线分析系统（OLAP）或者DSS（决策支持系统）。这两类系统在数据库的设计上是不同的，比如OLTP 系统强调数据库的内存效率，强调各种内存指标的命中率，强调绑定变量，强调并发操作：而OLAP 系统则强调数据分析，强调SQL 执行时长，强调磁盘I/O，强调分区等。
3.1.1. OLTP特点
通常OLTP（在线事务处理系统）的用户并发数很多，但只对数据库做很小的操作，数据库侧重于对用户操作的快速响应，这是对数据库最重要的性能要求。
对于一个OLTP 系统来说，数据库内存设计非常重要，如果数据都可以在内存中处理，那么数据库的性能会提高很多。
内存的设计通常是通过调整Oracle 和内存相关的初始化参数来实现的，比较重要的几个是内存相关的参数，包括SGA 的大小（Data Buffer，Shared Pool），PGA 大小（排序区，Hash 区等）等，这些参数对一个OLTP系统是非常重要的。OLTP 系统是一个数据块变化非常频繁，SQL 语句提交非常频繁的一个系统。对于数据块来说，应尽可能让数据块保存在内存当中，对于SQL 来说，尽可能使用变量绑定技术来达到SQL的重用，减少物理I/O 和重复的SQL解析，能极大的改善系统的性能。
此外，没有绑定变量的SQL会对OLTP 数据库造成极大的性能影响，还有一些因素也会导致数据库的性能下降，比如热块（hot block）的问题，当一个块被多个用户同时读取的时候，Oracle 为了维护数据的一致性，需要使用Latch 来串行化用户的操作，当一个用户获得了这个Latch，其他的用户就只能被迫的等待，获取这个数据块的用户越多，等待就越明显，就造成了这种热块问题。这种热块可能是数据块，也可能是回滚段块。对于数据块来讲，通常是数据块上的数据分布不均匀导致，如果是索引的数据块，可以考虑创建反向索引来达到重新分布数据的目的，对于回滚段数据块，可以适当多增加几个回滚段来避免这种争用。
3.1.2. OLAP特点
OLAP 数据库在内存上可优化的余地很小，但提升CPU 处理速度和磁盘I/O 速度是最直接的提高数据库性能的方式。实际上，用户对OLAP 系统性能的期望远远没有对OLTP 性能的期望那么高。
对于OLAP 系统，SQL 的优化显得非常重要，如果一张表中只有几千数据，无论执行全表扫描或是使用索引，对用户来说差异都很小，几乎感觉不出来，但是当数据量提升到几亿或者几十亿或者更多的时候，全表扫描，索引可能导致极大的性能差异，因此SQL语句的优化显得重要起来。
分区技术在OLAP 数据库中很重要，这种重要主要是体现在数据管理上，比如数据加载，可以通过分区交换的方式实现，备份可以通过备份分区表空间，删除数据可以通过分区进行删除。
3.2. 数据库的规模
对于数据库的规模，仅从数据量来衡量其规模的大小。因为数据量的规模是反映数据库规模的主要指标。具体如下：
1）数据库业务数据量小于100GB 属小规模数据库 2）数据库业务数据量1TB 以内属中大规模数据库 3）数据库业务数据量大于1TB 属大规模数据库
3.3. 数据库版本建议
Oracle数据库产品推出新的主要版本后，要经历一个版本不稳定期。在此期间新版的数据库产品存在较多的bug。在安装和运行过程中，会存在数据库安装困难和运行不稳定现象。因此在选择版本时，要选择成熟稳定的版本。
4. 数据库运行环境规划
根据用户需求在业务系统前期的实施规划上，需要做好详细的规划设计，包括主机、网络和存储环境规划等，要将整个软硬件融为一体，充分考虑系统的安全性，可靠性，高可用性等因素，只有一个规划好的系统才能充分发挥其优于单节点的优势，同时也为后期的运维管理提供方便。
在评估数据库服务器性能时，最困难的事情是如何把握准确度问题，到底考虑哪些因素等。理想情况下，应考虑下列要素：交易的复杂性、交易率、数
据读/写比例、并发连接数目、并发交易数目、数据库最大表的大小、性能度量的目标。
4.1. 主机规划
主机规划主要需考虑服务器在不同的用户数量下，系统的响应时间和吞吐量，并得出当前服务器的各种资源的利用情况。在规划系统配置时要预留做系统管理时所消耗的资源，如在做备份、恢复、问题诊断、性能分析、系统维护时都会对系统资源带来额外的消耗，对重要资源要考虑为将来留下升级和可扩展的余地。
在进行服务器配置规划时，要注意以下几点：
1）CPU：要考虑业务高峰时处理器的能力，并适当保留一些缓冲，确保在业务增长时，系统有扩展的余地。
2）内存：要为运行在此服务器的所有应用软件考虑内存，所需要的内存主要依赖于用户数、应用程序类型、进程的方式、和应用程序处理的数据量决定。
3）磁盘：评估业务的实际用户的数据量，以此推算出磁盘的最小个数，不要忘记选择备份设备（如磁带机）。
4）IO槽：尽量保留更多的IO槽，防止将来插更多的PCI卡。 5）网络：选择合适的网卡，保证网络不是系统的瓶颈。
数据库服务器优先考虑使用小型机和UNIX操作系统，但是当前用户大都选择PC服务器和Linux操作系统，推荐的数据库服务器配置如下：
处理器：核心不低于24，主频不低于，三级缓存不低于30MB。
内存：容量不低于256G，DDR4频率不低于2133MHz，支持ECC纠错、内存镜像、内存热备等功能。
存储：双盘，单盘容量不低于300GB，支持RAID。
网卡和HBA卡：2个千兆网口，2个万兆网口，2个FC接口。网卡进行绑定。
操作系统：Linux。
4.2. 网络规划
网络规划的基本原则就是将业务生产网络、存储网络和管理网络分开，推荐在生产网络使用万兆网，存储使用SAN存储网络，管理网可使用千兆网。
对于数据库单机模式、HA双机模式以及主备模式的生产环境，主要基本符合网络规划的基本原则即可。对于集群模式，因Oracle有其特殊要求，所以在结合网络规划基本原则的基础上，可进行细化实施。
在Oracle 11gR2中，安装RAC发生了显着变化。在10g以及11gR1的时代，安装RAC的步骤是先安装CRS，再安装DB，而到了11gR2的时代，crs与asm被集成在一起，合称为GRID，必须先安装GRID后，才能继续安装DB。本方案以主流的ORACLE 11g R2 RAC的网络规划为例进行详细说明。
假设服务器上有4块网卡：eth0,eth1,eth2，eth3.可以将eth0 和 eth2绑定成bond0。作为RAC的public-ip，提供外部通信。然后将eth1和eth3 绑定成bond1，作为RAC的private-ip，提供内部心跳通信。服务器上的HBA卡通过光纤交换机与后端存储通信。
4.2.1.1. 公有IP和虚拟IP
Oracle RAC中每个节点都有一个虚拟IP，简称VIP，与公网PUBLIC IP在同一个网段。VIP附属在public网口接口。
VIP和PUBLIC IP最主要的不同之处在于：VIP是浮动的，而PUBLIC IP是固定的。在所有节点都正常运行时，每个节点的VIP会被分配到public NIC上；在linux下ifconfig查看，public网卡上是2个IP地址；如果一个节点宕机，这个节点的VIP会被转移到还在运行的节点上。也就是幸存的节点的public NIC这个网卡上，会有3个IP地址。
PUBLIC IP地址是一个双网卡绑定的公有地址，用户通过交换机来进行访问。
4.2.1.2. RAC中的私有IP
RAC中的 Private私有IP用于心跳同步，这个对于用户层面，可以直接忽略。简单理解，这个IP用来保证数据库节点同步数据用的，属于RAC内部之间通信。priv 与public不应同属一个接口。
Metalink上的RAC文档是推荐使用交换机作为内部网卡的连接,而不使用交叉线,原因是避免因为对连节点关闭或重启而导致网卡检查到链接故障状态而删除绑定的协议。导致高速缓存合并网络将会变为不可用。
4.2.1.3. SCAN IP
在11gR2中，SCAN IP是作为一个新增IP出现的, SCAN IP其实是Oracle在客户端与数据库之间，新加的一个连接层，当有客户端访问时，连接到 SCAN IP LISTENER，而SCAN IP LISTENER接收到连接请求时，会根据 LBA 算法将该客户端的连接请求，转发给对应的instance上的VIP LISTENER，从而完成了整个客户端与服务器的连接过程。简化如下：
client -> scan listener -> local listener -> local instance 也可以把scan理解为一个虚拟主机名，它对应的是整个RAC集群。客户端主机只需通过这个scan name即可访问数据库集群的任意节点。当然访问的节点是随机的，Oracle强烈建议通过DNS Server的round robin模式配置解析SCAN，实现负载均衡（即轮换连接SCAN对应的IP地址）。
4.3. 存储规划
数据库一般使用磁盘阵列（RAID）保存数据，使用磁盘阵列有两个优点：首先，盘阵可以提供一个具有容错能力的I/O系统，当系统中某个磁盘驱动器出现故障时，可避免丢失数据，因此具有容错能力；其次，允许多个但磁盘驱动器配置成为一个大的虚拟磁盘驱动器，从而方便管理，提高性能。
盘阵RAID方式分为RAID0，RAID1，RAID10，RAID2，RAID3，RAID4，RAID5等，其逻辑和物理组合方式各有差异。
基于Oracle数据库配置RAID系统，有以下几种解决方案：
1、最佳解决方案
对容错能力最好的解决方案就是最大限度地使用RAID1和RAID10，规划部署时遵循以下原则：
1）对操作系统和Oracle程序使用RAID1；
2）对数据库重做日志文件使用RAID1，可以优化性能；
3）对归档日志文件使用RAID01，既能保护数据，又不会影响性能； 4）对数据文件使用RAID10，并使用多个磁盘驱动器以保证不超过单块盘的负载。
2、较好的解决方案
对于容错能力，较好的解决方案是混合使用RAID10和RAID5，遵循以下原则：
1）对操作系统和Oracle程序使用RAID1；
2）对数据库重做日志文件共享一个RAID1或RAID10；
3）对归档日志文件可使用RAID10或RAID5，这两种方式均可保护数据且不影响性能；
4）对数据文件使用RAID10，并使用过个磁盘驱动器以保证不超过单个磁盘负载。混合使用RAID10和RAID5可实现很好的性能，容错能力也很高。
3、经济型解决方案
对容错能力，此方案使用RAID1和RAID5，遵循以下原则： 1）对操作系统和Oracle程序使用RAID1; 2）对重做日志文件使用RAID1;
3）对归档日志文件使用RAID10或RAID5;
4）对数据文件使用RAID5或RAID0，其中RAID0用来提供必要的性能，并使用过个磁盘驱动器以保证不超过单盘的负载。
此方案提供的系统性能比前两个方案要低，其价格是优势。 在进行存储规划时，需要特别注意：
1）若系统没有使用容错功能，那么只要有一块磁盘驱动器发生故障，就必须恢复整个数据库；
2）容错磁盘不能替代数据库备份策略；
3）系统可能会发生变化，要紧跟用户的需求；
4）以上的解决方案都要考虑磁盘驱动器的数量，应具备足够数量的磁盘驱动器以防系统瓶颈的产生；
5）对于硬件的保护不仅仅是磁盘驱动器，还包括冗余电源，磁盘控制卡和风扇等等，如果存储系统没有冗余机制，则这其中任何一项故障都会导致业务系统停机和丢失数据等损失。
5. 数据库安装部署规划
5.1. 软件安装路径
建立单独的文件系统来安装数据库软件，且文件系统的mount点不要直接建立在根目录下。
安装路径： /home/db/oracle 各种环境变量设置： ORACLE_BASE=/home/db/oracle CRS_HOME=/home/db/oracle/crs/{数据库release版本} ORACLE_HOME=/home/db/oracle/product/{数据库release版本} 普通使用模式的Oracle数据库的服务名和实例名（SID）是相同的；RAC模式下的Oracle数据库的服务名与实例名不同。
数据库服务名的命名格式为：XXXYYdb{m} 数据库的SID的命名格式为：XXXYYdb{m}{n} 说明：
1、其中XXX表示长度为3个字符的应用项目缩写，具体的见相关设计文档。
2、YY：代表数据库用途，pd代表生产库，hi代表历史库，rp代表报表库，cf代表配置库；
3、m表示数据库序号，从0-9，根据项目的数据库数量进行编号。
4、n表示RAC节点实例序号1,2,3……。用以区分多节点的RAC数据库的不同实例。对于普通模式的数据库，该位不指定。
5.2. 表空间设计 5.2.1.
业务数据量估算
估算所有业务对象下的所有表的尺寸。 数据量估算的前提：
1）数据库的物理表结构已经确定，并且设计已凝固。
2）用户方提供较为准确的估算依据，例如业务变动的频率、数据需要保存的周期等。
该表是一个示例，可根据业务的不同有所变化。 序号 表名 增长量 （/小时/天/周） 增长量 （/月/半年） 年数据量 数据库生命周期内的总计 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 合计
新上线或扩容时，对所申请的存储不得全部一次性挂上，应该预留出30％左右的空间用于追加，以防止出现业务发展和预期不一致时剩余空间多寡不均，调整困难。
操作系统上应该预先做好几个合适大小的逻辑卷备用，包括用于system/sysaux等表空间的小尺寸的逻辑卷和用于数据表空间、索引表空间的大尺寸逻辑卷，这些逻辑卷要求在HA两边主机都可见，不必单纯因为数据库增加数据文件而需要重新同步HA。
5.2.2. 表空间使用规则
目前多数数据库系统采用数据“大集中”原则，对数据库的性能要求较高。这就要求对数据库进行必要的优化配置。在表空间的配置上，应遵循以下原则：
1、最小化磁盘I/O。
2、在不同的物理磁盘设备上，分配数据。 3、尽可能使用本地管理表空间。
多数系统采用RAID1+0 或 RAID0+1，该技术很好的解决了最小化磁盘I/O。基本不必考虑在不同的物理磁盘设备上，分配数据的原则。
5.2.2.1. 表空间的类型
按照表空间所包含的数据文件类型，Oracle表空间类型有三类： 1、数据表空间（permanence tablespace）：用来保存永久数据，包含永久数据文件。强烈建议在永久表空间内创建永久数据文件，不要创建临时数据文件。
2、临时表空间（temporary tablespace）：用来保存临时数据，多用于数据的磁盘排序。强烈建议在临时表空间内创建临时数据文件，不要创建永久数据文件。
3、回滚表空间（rollback/undo tablespace）：仅用来保存回退信息。不能在该表空间创建其他类型的段（如表、索引等）。
为了更好的管理表空间，同时提高Oracle数据库系统性能，在上述三类基础上，针对数据的业务功能，进一步对其加以分类。因此Oracle数据库的表空间划分为基本表空间和应用表空间。如下表：
1）基本表空间：是指Oracle数据库系统为其自身运行而使用的表空间。 表空间类别 表空间名称 存储内容 存储oracle数据库系统数据字典对象 数据表空间 SYSAUX 存储SYSAUX数据 说明 Oracle数据库系统自身生成的和使用—基本表空间 Oracle数据库系统自身生成的和使用—基本表空间 回滚表空间 UNDO表空间 容纳回滚数据 如果UNDO表空间是自动管理，则Oracle数据库系统自身生成的。 生产数据库不得有如TOOLS、XDB、EXAMPLE等oracle默认安装表空间。 2）应用表空间：是指业务应用数据保存在此类表空间中。它由DBA或相关的数据库规划设计人员创建和规划。 表空间类别 表空间名称 临时表空间 TEMP表空间 存储内容 容纳排序数据 说明 由DBA设定—应用表空间 数据表空间 TABLES表空间 存储小数据表公用业务数据 数据表空间 TABLES PARTITION表空间 数据表空间 INDEXS表空间 存储小数据表的索引 由DBA设定—应用表空间 存储巨型表数据 由DBA设定—应用表空间 由DBA设定—应用表空间 数据表空间 SYSTEM表空间
数据表空间 INDEXS PARTITION表空间 数据表空间 LOB表空间 存储巨型数据表的索引 由DBA设定—应用表空间 存储LOB的数据 由DBA设定—应用表空间 5.2.2.2. 表空间和数据文件的命名规则
数据文件都使用裸设备方式，使用固定大小，不得设置为自动扩展。 1、基本表空间及其文件命名规范 表空间名称 SYSTEM 裸设备连接文件名 rsystem_nn_size rsysaux_nn_size 普通文件名 说明 总空间大小设置为2G SYSAUX Oracle10g中必须有的表空间。总空间大小设置为4G，如果空间非常紧张，可设置为2G UNDOTBS1 TEMP 说明：
rundotbs_nn_size rtemp_nn_size 总空间不小于8G 总空间不小于4G 裸设备连接文件名
nn为从01开始计数的序号，表示文件的个数。如：01，02，03，04…… size表示了设备的大小，由数字部分和单位部分组成：XU。其中，X是一个正整数，取值范围从1～1023，U是单位标识位，是1位的字符，取值范围为k、m、g、t，分别表示了KByte、MByte、GByte、TByte，size的值应该根据设备的数据大小指定。
普通文件名（即创建在文件系统上的文件）
nn为从01开始计数的两位整数序号。如：01，02，03，04……
各表空间根据需求在建库时确定。
数据文件路径：/home/db/oracle/oradata/{DB_NAME}/ 数据文件的使用方式：
裸设备：适用于RAC及共享磁盘双机热备数据库架构。创建数据库前，在指定的目录下创建指向裸设备的软连接文件。命令如下： ln -s /dev/rxxxxx /home/db/oracle/oradata/{DB_NAME}/ 2、应用表空间和数据文件设计规范 应用表空间分类如下： 表空间种类 TABLES公用表空间 TABLES PARTITION分区表空间 INDEXS公用索引表空间 INDEXS PARTITION大表索引空间 LOB表空间 B_<功能模块名称>_nn TEMP表空间 T_<功能模块名称>_nn 说明：
表空间的命名规则
nn为从01开始计数的两位整数序号，表示表空间的数目。如：01，02，03，04……
裸设备连接文件名
r+表空间名称_nn_size r+表空间名称_nn_size 表空间名称 表空间名称 I_<功能模块名称>_nn I_<数据表名>_nn r+表空间名称_nn_size r+表空间名称_nn_size 表空间名称 表空间名称 表空间命名规则 D_<功能模块名称>_nn D_<数据表名>_nn 裸设备连接文件名 r+表空间名称_nn_size r+表空间名称_nn_size 表空间名称 普通文件名 表空间名称
nn为从01开始计数的两位整数序号，表示数据文件的数目。如：01，02，03，04……
size表示了设备的大小，由数字部分和单位部分组成：XU。其中，X是一个正整数，取值范围从1～1023，U是单位标识位，是1位的字符，取值范围为k、m、g、t，分别表示了KByte、MByte、GByte、TByte，size的值应该根据设备的数据大小指定。
普通文件名（即创建在文件系统上的文件）
nn为从01开始计数的两位整数，表示数据文件的数目。如：01，02，03，04……
各表空间根据需求在建库时确定。
数据文件路径：/home/db/oracle/oradata/{DB_NAME}/ 数据文件的使用方式：
裸设备：适用于RAC及共享磁盘双机热备数据库架构。创建数据库前，在指定的目录下创建指向裸设备的连接文件。命令如下：
ln -s /dev/rxxx /home/db/oracle/oradata/{DB_NAME}/r+表空间名称_nn_size 其中：xxx为裸设备的名称。
5.2.3. 表空间的概念和分配原则
5.2.3.1.
表空间相关概念
在规范表空间存储参数之前有必要澄清关于数据块（data block）、区（extent）、段（segment）的概念及其之间的关系。如下图：
数据块（data block）：Oracle存储数据最细粒度是数据块，它是操作系统文件块的整数倍（有时也称逻辑块，Oracle块，或页）。一个数据块大小有2k、4k、8k、16k等，并以此单位大小保存在物理磁盘中。
区（extent）：是由一序列相邻连续的数据块组成的区域叫区。区存储特定类型的数据。它比数据块高一级别。
段（segment）：比区（extent）高一逻辑存储级别的称作段
（segment）。段是由一系列区组成。用来存储一个特定的数据结构，并且该段只能分配在同一表空间中，不能跨越表空间。如：每个表（table）的数据保存在自己的数据段中；而每个索引保存在自己的索引段中；如果表或索引是分区的，则每个分区拥有自己的段
5.2.3.2. 表空间的分配原则
对于小规模数据库，I/O不是主要的性能瓶颈，可以不考虑物理分布的问题。
对于中规模数据库及大规模数据库，应当考虑：
1、尽可能把应用数据表空间、应用的索引表空间以及相应得分区表空间分布在独立的物理卷上。
2、其次把UNDO、TEMP、REDOLOG分布在不同的物理卷上。
5.2.4. 表空间的参数配置
对于数据库的存储空间管理Oracle有以下的选择：
5.2.4.1. Extent管理
对Extent的管理有两种方式。一般情况下，推荐数据库管理员使用本地管理中的指定大小(Uniform Size)的方式创建表空间。
1.
数据字典管理(Dictionary Management)
在数据字典的管理方式中，数据库使用数据字典来跟踪数据对象的存储分配，这样当出现数据对象的存储变化时，数据库需要更新数据字典以保证系统可以跟踪数据库对象的存储变化，这在某种程度上会造成系统性能的下降。
2.
本地管理(Local Management)
在本地管理方式中，数据库使用每一个数据文件的前面8个数据块中的每一位来代表数据块的占用方式。由于这种方式跟踪数据对象的存储分配不需要
访问数据字典，这在一定程度上避免了递归调用的出现，提高了系统存储管理的效率。
对于本地的Extent管理有两种方式： (1)
自动分配(Autoallocate)
自动分配的方式指由数据库系统按照数据对象的大小决定该对象的每一个EXNENT的大小。一般情况下，由于数据库系统并不能预先的确定该对象的总的大小，数据库总是倾向于在初始的几个Extent使用较小的值，然后按照8－128－1024－8192个数据块的方式急剧的增大。
这通常会造成系统过多的碎片和较低的存储空间的利用效率。 (2)
指定大小(Uniform Size)
指定大小的方式指由数据库管理员在创建表空间时间指定该表空间的所有的EXNENT的大小，这样该表空间的所有的Extent具有同样的大小。
一般情况下，由于数据库管理员能够预先的估计出该表空间的数据对象的大小，所以数据库管理员通常能够确定合适的UNIFORM SIZE来创建数据表空间。
通过指定合适的数据表空间，可以避免系统出现过多的碎片和提高存储空间的利用效率。
一般情况下，建议数据库管理员能够使用指定大小的方式来创建表空间，除非明确知道表空间中仅仅存储较小的数据对象，否则不要使用自动的EXTENT管理方式。
5.2.4.2. Segment管理
对Segment的管理可分为两种。推荐使用ASSM方式。 1.
手工管理方式（Manual）
手工管理方式是指用户创建表空间时使用手工指定参数Freelist， Freelist Group来控制表空间的段的空闲块。
手工的管理管理可以带来更多的灵活性。 2.
自动管理方式（ASSM）
自动的管理方式指数据库系统使用BITMAP的方式来管理空闲块。在这种情况下如果多个对象需要分配空间，可能会造成对某一块的竞争。
5.2.4.3. 数据表空间的存储参数
数据表空间的区（extent）管理：表空间是以区为单位进行分配空间的。自从9i及以后版本推荐使用本地管理表空间，并且本地管理表空间是默认的。对应的create tablespace语句子句为EXTENT MANAGEMENT LOCAL。Oracle已不推荐使用字典管理的表空间。如下图：
如果表空间包含各种不同大小的数据库对象，而这些对象拥有不同尺寸的区，则选择AUTOALLOCATE是最好的选择。即字句EXTENT MANAGEMENT LOCAL AUTOALLOCATE 。让Oracle来管理EXTENT的分配。如下例：
SQL>CREATE TABLESPACE test DATAFILE '/u02/oracle/data/' SIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE; 如果能够预先估算出单个对象或一系列对象的所分配的空间及EXTENTS的尺寸，则选择UNIFORM 是个比较好的选择。即字句UNFORM SIZE SQL>CREATE TABLESPACE test DATAFILE '/u02/oracle/data/' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K; 表空间的段（segment）管理：段管理分为自动段空间管理（缺省参数）和手动段空间管理，对应的子句如下图： 自动段管理是一种相对简单而有效的段空间的管理方式。该方式完全摒除了PCTUSED, FREELISTS, FREELISTS GROUPS等物理存储参数的设置。即使这些参数被指定，Oracle仍然会忽略它。自动段管理可根据用户数和实例数自动调整，对于大多数标准负载和应用性能来说，要比手动调整管理段要更好。因此多数情况下推荐使用段管理。如下列： SQL>CREATE TABLESPACE test DATAFILE '/u02/oracle/data/' SIZE 50M EXTENT SQL>MANAGEMENT LOCAL SQL>SEGMENT SPACE MANAGEMENT AUTO; 下面是完整的例子： 例1：本地管理表空间+自动段空间管理sql SQL>CREATE TABLESPACE TEST SQL> SQL>EXTENT MANAGEMENT LOCAL SQL>SEGMENT SPACE MANAGEMENT AUTO; 例2：本地管理表空间+自动统一尺寸段空间管理sql SQL>CREATE TABLESPACE TEST SQL> SQL>EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M SQL>SEGMENT SPACE MANAGEMENT AUTO; 5.2.4.4. 临时表空间的存储参数 Oracle推荐使用本地表空间管理，统一区尺寸管理1M，分别对应的子句是EXTENT MANAGEMENT LOCAL和UNIFORM SIZE 1M。 例：SQL SQL>CREATE TEMPORARY TABLESPACE TEMP1 SQL> SQL>EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M; 自动扩展语句会造成数据文件的自动增长，在使用裸设备的情况下可能造成文件越界，在使用文件系统的情况下可能造成文件系统无空闲空间。 不应使用自动扩展的功能。 5.2.5. Undo/temp表空间的估算 Undo设置原则 oracle9i以后的版本，推荐使用UNDO TABLESPACE，让系统自动管理回滚段。 须考虑以下几个问题： 系统并发事务数有多少 系统是否存在大查询或者大是事务频繁与否 能提供给系统的回滚段表空间的磁盘空间是多少 Temp设置原则 可创建缺省临时表空间temp，取数据库的缺省参数。一般情况下，生产数据库系统的临时表空间不是用缺省的。应另外创建临时表空间，以供较大的排序事务使用。可设置每个Transaction类别用户，对应一个临时表空间。 5.2.6. 表的参数设置 、Pctused 存储参数pctfree和pctused决定了一个数据块在不同的数据库操作下的可用性，它与数据对象的操作性质密切相关。 对于主要操作为insert的数据对象，可以考虑设定较小pctfree和较大的pctused，如pctfree=5 Pctused=60。对于更新较为频繁的系统，可以设定较大的pctfree和较小的pctused来避免行的迁移，如pctfree=20 Pctused=40。对于银行系统，由于数据的保留时间较长，同时数据的删除较少可以考虑设定较小的pctfree和较大的pctused，如：Pctfree=10 Pctused=50。 、Maxtrans 存储参数initrans和maxtrans决定了数据对象的同一个数据块中能够并发进行的事务数。 由于目前的数据块由逐步变大的趋势，故此同一个数据块中发生并发事务的几率在上升。 对于db_Block_Size=8192的OLTP系统，可以设定initrans＝4，Maxtrans＝10 5.2.7. 索引的使用原则 1.基本使用原则 1)当查询的行数占整个表总行数的比例<=5%时，建立b*树索引效果比较明显。（普通索引就是b*数索引） 2)在频繁进行排序或分组（即进行group By或order By操作）的列上建立索引。 3)在频繁使用distinct关键字进行查询的列上面建立索引。 4)进行表连接时，在连接字段上面建立索引。 5)对于键值频繁更新的索引，需要定期的进行重建。 2.基本存储参数设置原则 1)物理属性子句（Physical_Attributes_Clause） 参见表的物理属性参数设置原则 2)Storage_Clause 参见表空间的存储参数设置原则 3)Blevel 索引的blevel代表了索引中从根节点到叶节点的深度，对于索引来说，由于索引键值的频繁更新可能造成该索引的节点的过度分裂，使得索引的层次较多。因此系统管理人员应该定期的对索引进行分析，对索引深度较深的的索引进行重建工作。 3.复合索引的使用原则 一般情况下，对于经常同时使用多个数据项进行查询的对象可以创建复合索引，使用复合索引时特别要考虑的各个数据项在索引中的相对位置。 一般情况下，把最常用的列放在第一位而不太常用的列放在稍后面的位置。 在复合索引创建后，要求用户在查询数据的时候也遵循同样的方式来使用索引。 虽然目前的Oracle数据库版本能够使用复合索引中的后面的数据项，但是按序使用复合索引可以带来较高的效率。 4.函数索引的使用原则 1)在使用函数索引(Function-based INDEX)时，需要设置初始化参数QUERY_REWRITE_ENABLED=TRUE，创建该索引的用户需要有CREATE INDEX和QUERY REWRITE权限。 2)对于经常进行运算比较的一些列，可以考虑建立函数索引，但是也可以通过在表中使用原来的列的函数形式来实现 3)在OLTP系统中，一般情况下不建议使用函数索引。 5.3. 文件设计 如果使用裸设备作为数据库设备，则在该目录下建立到相应的裸设备的链接文件。如果使用文件作为数据库设备，则根据存储空间的需求，建立独立的文件系统，挂接到该目录下。 5.3.1. RAC配置文件 srvconfig_Size Size表示了文件/设备的大小，由数字部分和单位部分组成：XU，其中，X是一个正整数，取值范围从1～1023，U是单位标识位，是1位的字符，取值范围为k、m、g、t，分别表示了KByte、MByte、GByte、TByte，Size的值应该根据文件/设备的数据大小指定。 5.3.2. 参数文件 对于共享磁盘的双机热备的系统，发生失效接管（failover）时，应使用pfile参数文件设置；在没有发生失效接管情况下，使用spfile参数文件。 对于单机或RAC方式的系统，可使用共享的spfile参数文件设置； 5.3.2.1. 参数文件命名规则 Oracle数据库系统在启动时，先读取初始化参数文件，根据该文件的设置，系统才能启动成功。从Oracle9i以后的版本，Oracle系统使用spfile文件和pfile参数文件。数据库系统启动时，首先查找$ORACLE_HOME/dbs/目录的spfile文件，如果无此文件，系统在查找pfile文件。spfile文件是二进制文件，而pfile文件是ASCII文件。 pfile初始化参数文件：该文件是ASCII码文件，可用文本编辑器编辑（注：在编辑前，一定要先备份）。 文件命名：init{SID}.ora 文件路径：/home/db/{OS_oracle_user}/admin/{DB_NAME}/pfile/ 以及$ORACLE_HOME/dbs/ spfile初始化参数文件：该文件是二进制文件，不可以直接编辑。只能通过Oracle SQL语句进行创建。方法如下（注：在创建前，一定预先备份spfile及pfile）： SQL〉show parameter spfile SQL〉create spfile from pfile; spfile的两种使用方式： 文件系统：spfile{DB_NAME}.ora 裸设备：rspfile{DB_NAME}_size 保存路径：/home/db/oracle/oradata/{DB_NAME}/； 缺省路径：$ORACLE_HOME/dbs/ size表示了文件/设备的大小，由数字部分和单位部分组成：XU，其中，X是一个正整数，取值范围从1～1023，U是单位标识位，是1位的字符，取值范围为k、m、g、t，分别表示了KByte、MByte、GByte、TByte，Size的值应该根据文件/设备的数据大小指定。 5.3.3. 控制文件 每个数据库实例应至少有两个控制文件，且每个文件存储在独立的物理磁盘上。如果有一个磁盘失效而导致控制文件不可用，与其相关的数据库实例必须关闭。一旦失效的磁盘得到修复，可以把保存在另一磁盘上的控制文件复制到该盘上。这样数据库实例可重新启动。并通过非介质恢复操作使数据库得到恢复。 因此，为了使整个系统的高可靠地运行，建议系统设置2-3个控制文件。 5.3.3.1. 控制文件命名规则 1）保存路径：/home/db/oracle/oradata/{DB_NAME}/ 2）控制文件的使用方式： 裸设备：创建数据库前，在指定的目录下创建指向裸设备的连接文件。 rcontrol_n_size 其中：n为从1 开始计数的整数，表示控制文件序号。如：1，2…… size表示了文件/设备的大小，由数字部分和单位部分组成：XU，其中，X是一个正整数，取值范围从1～1023，U是单位标识位，是1位的字符，取值范围为k、m、g、t，分别表示了KByte、MByte、GByte、TByte，Size的值应该根据文件/设备的数据大小指定。 文件系统： 其中：nn为从01开始计数的两位整数，表示控制文件序号。如：01，02，03…… 3）控制文件数量：为2-3个。如果控制文件所在存储已作镜像，建议2个控制文件。如果没有做镜像，建议3个控制文件。 4）控制文件大小 裸设备：一个物理分区大小，一般为256MB。 文件系统：系统缺省大小。 5.3.4. 重做日志文件 重做日志文件的尺寸会对数据库的性能产生重要影响，因为它的尺寸大小决定着数据库的写进程（DBWn）和日志归档进程（ARCn）。一般情况下，较大的日志文件提供较好的数据库性能，较小的重做日志文件会增加核查点（checkpoint）的活动，从而导致性能的降低。当然为了防止I/O争用，还应把各个重做日志文件分布到不同的物理磁盘上。 重做日志文件在几百兆字节到几GB字节都被认为是合理的。欲确定数据库重做日志文件的大小，应根据该系统产生重做日志的数量，并依据最多每二十 分钟发生一次日志切换这个大致原则来决定。在系统运行后，从alert文件获取日志的切换时间，并根据切换的间隔来调整重组日志的大小。初始大小建议不低于50M，小于1G。 5.3.4.1. 日志文件命名规则 归档日志（archivelog ）文件，建议放在独立物理磁盘上。 1）重做日志保存路径：/home/db/oracle/oradata/{DB_NAME}/ 2）归档日志保存路径：/home/db/oracle/orarch# #表示实例号，1，2，3，…… 通常要求归档日志备份文件系统大小可以保证容纳2天产生的归档日志。AIX操作系统还要求该文件系统设置rbrw属性，以避免归档日志被放入操作系统内存。 3）日志文件的使用方式： 裸设备 创建数据库前，在指定的目录下创建指向裸设备的连接文件。 rlog#_n_m_size（单机） rlog#_n_m_size（RAC） 文件系统 #表示实例号，1，2，3，…… n表示日志组的编号，取值范围为01-06 m表示日志组成员的编号，取值范围为01-02 size表示了设备的大小，由数字部分和单位部分组成：XU，其中，X是一个正整数，取值范围从1～1023，U是单位标识位，是1位的字符，取值范围为k、m、g、t，分别表示了KByte、MByte、GByte、TByte，size的值应该根据设备的数据大小指定。 日志组数量：为3-6，具体日志组数量根据各项目情况确定，每个日志组包括2个成员。 日志文件大小：为512，1024，2048M,……根据各项目情况选择此区间值。 6. 数据库应用规划 6.1. 数据库用户设计 6.1.1. 用户权限规划 业务功能的安全分配是指开发团队定义的用户、角色、特权，它是面向应用程序和开发的。它在数据库部署是几乎是不可修改的。 数据库用户安全分配往往取自前台应用设计开发团队的交付生产时的定义。这种安全定义了用户、角色、系统特权、对象特权分配等等。它往往是面向开发的，没有细致考虑用户权限的控制。在数据库系统上线时，才发现有不妥之处。而这种用户安全分配多数情况下不能修改，否则对前台应用造成运行错误。 但在交付生产时，投产方和用户方必须对其安全性进行审计。因为这时提供的用户安全往往是面向开发的，而不是面向末端用户的。主要检查一下几个方面： 1、每个业务用户不得授予DBA角色。 2、取消一些系统特权。但必须之前征询开发者的意见，否则可能对前台应用运行带来不可预测的错误。 3、坚持最小化特权原则。 根据数据库管理、数据维护、开发、功能等方面分为以下类型用户： 序号 用户类型 描述 该类用户拥有DBA角色，只有数据库管理员能够使用。其他用户不要授予该角色。 2. DATA OWNER 该类用户拥有数据库业务schema 对象，特别是tables及其他对象。不对末端用户开放。只有通过对象授权和系1. DBA 统授权，Transaction类型用户才可访问DATA OWNER类型用户；表空间的使用，要通过空间授权配额，才可访问；CREATE SESSION特权使用时方可临时授权，使用完毕后，取消授权。 3. Transaction 该类型用户拥有数据库最小权限。只有通过明确的系统和对象授权才可访问DATA OWNER中的对象（如CREATE SESSION,ALTER SESSION等）。一般用于末端用户的访问。 4. Monitor 该类型用户一般用于监控数据库性能，或者是第三方工具使用。 作为监控软件，如QUEST监控软件、patrol、statspack、rman等； 5. 其他 作为普通用户使用，使用权限严格限制，并服从DBA管理。如执行一般的查询sql语句等。为非ＤＢＡ用户使用。 对象（如:表、索引、触发器、过程等）管理权限归数据中心，应用（属记录级别，创建临时表权限）权限归项目组。 用户命名规范如下： 1、DBA类型命名格式： 注：XXX为长度为3个字符的项目英文简称 2、DATA OWNER类型的命名格式： 注监控软件用户应按照第三方的供应商提供的方式命名。 5、其他类型命名格式： 注：XXX为长度3个字符的功能英文简称。 对于一个软件项目，在应用系统开发过程中，就开始对数据库用户权限进行严格的控制。即按照该系统未来生产时的方式进行分配，尽管此时数据库还处在开发服务器之中，尽管给开发项目的控制带来更多的工作，但数据库的安全性大大提高了。 对数据库用户（user）的授权，应通过数据库角色（role）进行分配。而不要把对象特权和系统特权直接授权给数据库用户。 各用户类型的角色命名规范如下： 1、DATA OWNER类型用户分配的角色命名规则：R_ 2、Transaction类型用户分配的角色命名规则：R_ 4、其他类型用户分配的角色命名规则：R_ 6.1.2. 用户安全实现 Oracle数据库是通过“特权”（Privilege）这个概念来实现数据安全的。所谓特权指用一种指定的方式访问数据库数据对象的一个许可，如查询一个数据表的许可等。这个特权能够被授予某个实体，因此这个授予实体特权（privilege）的过程，称之为“授权”（Grant）。 涉及Oracle数据库系统安全的实体有两个，分别是系统特权（System Privileges）和对象特权（Object Privileges）。 1、系统特权 系统特权是指登录到ORACLE数据库系统的用户，执行数据库系统级别的某种操作或者是某一数据库对象的创建、修改、删除。在ORACLE数据库系统中有一系列的系统内置预定义特权，系统用这些特权去控制数据的安全。 不得授予普通用户额外的全局权限，如select any/delete any/execute any等，应用有特殊需求的除外。 2、对象特权 对象特权是指登录到ORACLE数据库系统的用户，有权执行数据库对象级别的某种操作。例如表的INSERT，DELETE，UPDATE操作等。同样，在ORACLE数据库系统中有一系列的对象内置预定义特权，系统用这些特权去控制数据的安全。 由于ORACLE数据库系统业务处理的复杂性，对ORACLE数据库的系统特权和对象特权的分配也就变得十分复杂。因此，为了方便管理系统特权和对象特权，需要引入角色这个基本概念。 所谓角色是指系统特权和对象特权的集合。通过对角色的管理，使得ORACLE数据库的系统特权和对象特权管理变得更加方便和容易。 基于角色的安全管理主要有以下几点优势： 1、减少授权工作量：可以通过授权给与一组用户相关联的角色，再由该角色授权给该用户组的成员用户。 2、动态特权管理：如果授权给某个用户的特权需要改变，只须修改相关角色的授权，那么与这个角色相关的用户的特权会自动改变，不须修改授权给用户特权。 3、设置特权的可用性：当某个被授予用户的角色，需要取消，只须对相应的角色设置禁用（DISABLED）。因此，在任何特定的情况下，都可对用户的授权进行必要的控制。 4、应用程序级的设置可用性：前台应用程序在试图以某个数据库用户的身份与后台数据库相连接时，可以对角色设置可用性。这种做法可以把非应用程序例如SQL*PLUS或第三方的数据库操作工具等，屏蔽在数据库系统之外，以保证数据库的安全。 角色可以根据业务的需求自由定义，系统特权和对象特权可以授权给角色，角色也可授权给另外的角色，角色也可授权给用户。基于上面描述的角色安全管理的优点和特点，ORACLE数据库系统选择角色来实施数据库用户的授权管理，并根据ORACLE的业务需求从不同的角度实现业务的权限分配。 根据需求，设置不同级别的角色，某一级别体现对某一项业务的特权。各角色级别之间或是子集关系，或是交集关系；同一级别的角色之间，或是交 集，或是互为独立集合的关系。随着对业务需求的增加或变化，不断增加、完善访问控制的粒度，并坚持最小化特权原则。如下图： 1、通过存储过程管理特权（stored procedures） 使用存储过程（stored procedures）来限制数据库的操作，客户端用户只需有权执行存储过程，并通过存储过程来实现对数据库表的访问。因而就屏蔽了用户直接对数据库表的操作。 2、通过视图（VIEWS）管理特权 通过视图（VIEWS）来控制ORACLE数据库系统的安全。即只分配给用户查询视图的特权，而对基表（定义视图的相关的数据表）则进行屏蔽，禁止对数据表的直接操作。 视图可以实现以下两种安全级别： 1、使用视图可以限制对数据表中的特定的列的访问。 2、使用视图可以限制对数据表中的特定的行的访问。 如：对于某一基表，要求只显示部分行，则可通过创建实体的WHERE子句来控制行的显示。 6.1.3. 用户类型及角色命名规范 可以用SQL语句GRANT来授予系统权限和角色给其它角色和用户。有GRANT ANY ROLE系统权限的任何用户可以授予数据库里的任何角色。 下面的语句授予系统权限CREATE SESSION和角色ACCTS_PAY给用户JWARD: SQL>GRANT CREATE SESSION, ACCTS_PAY TO JWARD; 注意：对象权限不能跟系统权限和角色在同一句GRANT语句里授予。 当一个用户创建一个角色，会把自动这个角色带关键字ADMIN OPTION地授予给它的创建者。一个带有关键字ADMIN OPTION的被授予者有几项扩展性能： 被授予者可以对数据库的其它用户或角色进行授予或撤销系统权限或角色的操作。（用户不可以撤销它本身的角色。） 被授予者可以进一步授予有关键字ADMIN OPTION系统或角色。 拥有一个角色的被授予者可以改变或卸载这个角色。 在下面的语句中，安全管理员把NEW_DBA角色授予给MICHAEL： SQL>GRANT NEW_DBA TO MICHAEL WITH ADMIN OPTION; 用户MICHAEL不但可以使用隐含在角色NEW_DBA里的所有权限，当有需要时还可以授予，撤销或卸载NEW_DBA角色。 只有在对安全管理员进行相关权限和角色授予时，才允许带有关键字ADMIN OPTION。 同样可以使用GRANT语句来授予对象权限给角色和用户。要授予对象权限，必须要具备下面任意一个条件： 拥有被授予的对象 被授予过有关键字GRANT OPTION的对象权限。 注意：系统权限和角色不能和对象权限在同一句GRANT语句中授予。 下面的语句授予了对应EMP表所有列的SELECT，INSERT和DELETE的对象权限给用户JFEE和TSMITH： SQL>GRANT SELECT, INSERT, DELETE ON EMP TO JFEE, TSMITH; 要授予只对应EMP表的ENAME列和JOB列的INSERT的对象权限给用户JFEE和TSMITH，声明下面的句子： SQL>GRANT INSERT (ENAME, JOB) ON EMP TO JFEE, TSMITH; 要把对应于SALARY视图的所有对象权限给用户JFEE，要使用ALL关键字，例子如下所示： SQL>GRANT ALL ON SALARY TO JFEE; 拥有对象的用户会自动授予所有相关的有关键字GRANT OPTION的对象权限。这几个权限让被授予者有以下几个扩展权限： 被授予者可以授予有或没有关键字GRANT OPTION的对象权限给数据库里的任何用户或者任何角色。 如果在带有关键字GRANT OPTION情况下授予，被授予者得到一个表的对象权限，并且被授予者有CREATE VIEW 或 CREATE ANY VIEW的系统权限，那么被授予者就可以在这个表上建视图和把这个视图相应的权限授予给数据库的任何用户或角色。 当把一个对象权限授予给一个角色时，关键字GRANT OPTION是不起作用的。Oracle防止通过角色来传播对象权限，因此有某角色的被授予者不能通过角色来传播拥有的对象权限。 可以授予对应于表里单独一列的INSERT，UPDATE或REFERENCES的权限。 在授予对应列的INSERT权限之前，观察这个表是否有许多定义了NOT NULL约束的列，如果是就终止授予。选择性的把插入特性授予给那些没有NOT NULL特性的列，防止了用户插入任何列到表里。要避免这种情形，就要确定每一个NOT NULL的列既可以插入又有非空的默认值。否则，被授予者插入记录就会不成功并出现错误。 把ACCOUNTS表的ACCT_NO列的INSERT权限授予给用户SCOTT： SQL>GRANT INSERT (ACCT_NO) ON ACCOUNTS TO SCOTT; 除了sys、system、rman用户，其他oracle默认用户都应该置为expire或lock状态，除非有特殊需求; 数据库所有活动状态用户的密码不得设置为已知的默认密码（如：sys用户密码也不能设置为manager）； 数据库用户的密码应该定期修改。 6.2. 数据库分区 6.2.1. 数据库分区介绍 分区技术是为解决数据库中巨大的表或索引读写速度过慢而提出的解决方案。分区技术是利用物理上和逻辑上对数据进行分割来提高处理速度的。 同时，合理的分区也提高了数据库数据的可管理性。 Partition表和索引考虑使用分区的条件： 1、数据损坏的故障隔离； 2、支持在线增加、删除； 3、特定分区上的批处理； 4、按分区备份； 5、维护时可访问正常分区； 6、恢复最关键的数据分区。 6.2.2. 逻辑分割 根据分区策略，一张表的数据可以逻辑上分布于多个分区、子分区中，对数据的查询如果利用分区策略就可以缩小访问的范围，在一定量级上提高查询速度。 同样，对于索引分区也是一样的，从逻辑上分割表，缩小处理中的范围，能够极大地提高Oracle本身的处理速度。 6.2.3. 物理分割 对于逻辑分割后的分区，可以通过策略分布到不同的表空间中，从而分布到不同的数据文件中，而数据文件又可以分配到不同的存储介质空间中，这样就可以充分利用操作系统的并行访问，同时也利用存储介质的并行访问，极大地同时提高写入和读取速度。 同样，对于索引分区也是一样的机制。 6.2.4. 数据分区的优点 1、分区允许数据库管理员进行数据管理操作：数据装载，索引的创建和重建，在分区级别的备份与恢复。因此可节省数据库管理员大量的操作时间。 2、分区可大幅度减少因维护引起的宕机时间。分区的独立性使数据库管理员对同一表或索引的各个分区进行并发的维护管理操作；也可对分区表并行的执行SELECT 和DML操作。 3、分区可增加数据库的可用性。减少维护窗口，恢复次数以及系统失效的影响。 4、分区不要求修改任何应用程序。如，数据库管理员可把非分区表转换为分区表，而不必修改或重写SELECT 语句或DML语句。同时也不必重写前端的应用程序代码。 6.2.5. 数据分区的不足 数据分区带来了数据库创建方面的复杂度。即维护操作原来是面向一个表或索引对象，现在则要面向几十至上百个分区。但后台工作复杂度的加大换来了前台数据操作的效率提高，也是值得的。 6.2.6. Oracle分区技术 1、范围分区 范围分区是对某个可度量的字段在可以预见的范围内进行划分的分区方式，例如：日期字段。 2、枚举值分区 枚举值分区是对某个可列举确定值的字段按照不同值进行划分的分区方式，例如：区县代码字段。 3、散列分区 散列分区是对某个离散性很大的字段按照根据散列算法计算出的散列值进行分区，例如：证件号码。 4、组合范围-散列分区 组合范围-枚举值分区是按照范围做主分区，在主分区的基础上再次进行按照枚举值分区的组合分区。例如：日期-区县。 5、组合枚举-散列分区 组合范围-散列分区是按照范围做主分区，在主分区的基础上再次进行按照散列分区的组合分区。例如：日期-证件号码。 6.2.7. 分区使用建议 如果数据按照某个（某些）值分区，那么range分区就最合适，比如按照“销售定额”、“财务年度”、“月份”等等，在这种情况下，range分区可以利用分区消除，这包括应用中使用“＝”、“>”、“<”等作为条件。 如果不能找到其他合适的自然条件进行分区，那么HASH 分区就比较合适，这里建议选择唯一列或者几乎唯一的列作为分区键值。这种情况下，分区 数据是均匀的，使用分区键值“＝”或者in(value1,value2…)时，hash分区可以利用分区消除，但是使用其他条件时，hash无法利用分区消除。 如果分区后，每个分区的数据量还是很大，建议使用组合分区，例如，首先按照自然条件做range分区，之后，对分区再进行分区。 6.2.8. 分区索引 全局索引(GLOBAL index ) 1、指向任何一个分区中的记录 2、索引可以被分区或不分区 3、表可以被分区或不分区 4、分区键值可以是有前缀后没有前缀 本地索引(LOCAL index) 1、每个本地索引分区只包含本分区的记录 2、二种类型的本地索引 前缀(prefixed )：唯一或非唯一列，可有效的使用分区消除，适于索引并行查询，适用于OLTP 非前缀(non-prefixed)：适合于索引并行查询，可有效的使用分区消除，唯一索引受限，适用于DSS 3、分区键值可以与索引键值不同 本地分区索引：每个表分区都有一个索引分区，而且只索引该表分区的数据。一个给定索引分区中的所有条目都指向一个表分区，表分区中的所有行都表示在一个索引分区中。 优点: 容易维护; 适合并行索引扫描 缺点: 对少量记录查询相对效率不高 全局非分区索引：一个索引可以指向多个分区的数据 优点: 对单个或少量记录的访问比较有效 缺点: 管理维护上有额外成本 全局分区索引：索引有独立的分区键值，每个索引分区可能指向多个不同的表分区数据 优点: 可用性和管理性 缺点: 对新数据的导入不会太有效 6.3. 数据库实例配置 对于Oracle各个版本的参数优化，随版本的区别而有所不同。但万变不离其宗，大部分的参数设置原理是相同的，其优化原则也一致。 数据库字符集的确定非常重要，如果选择不当，会给业务数据的保存带来麻烦。如在现实中，有的汉字保存到数据库时发生乱码，从而使客户的信息不能正常保存和显示。 数据库字符集在系统设计开发阶段就应当确定。在数据库系统上线后，再更改数据库字符集，代价会非常大。因为不同的字符集设置之间，存在转换操作，如果不兼容，只能逐条转换。一般选择原则是要适中，满足当前和未来业务数据的保存。既不要选择太大，也不要过小。现有支持汉字的字符集包括 ： ZHS16CGB231280：此字符集较小，不建议使用 ZHS16GBK：此字符包含了大部需要的汉字字符，由于目前已经有了新的国标GB18030－2005，而GB18030不是GBK的严格超集，部分字符编码有改动，如果要升级到GB18030只能是将库导出导入重建，因此不建议使用该字符集。 ZHS32GB18030： 此字符集是最新国家标准字符集，包含字符较全，如果系统将来不会有其他国家字符被使用，可以使用此字符集。注意10g的DBCA工具建库时，选不到该字符集，要使用该字符集只能手工建库。 AL32UTF8：此字符集是oracle推荐使用的字符集，对汉字支持也较好。如果系统可能会有其他国家字符录入，建议选用此字符集。 对于其他字符集的选择，请查询应用安装手册。 6.4. 数据库参数设置 数据库参数的设置与业务应用密不可分。不同的业务类型，决定着关键参数的值，同时合理参数值也就决定着数据库系统的性能优劣。不仅如此，业务数据的需求也决定着数据库系统的设置。对于实例创建成功而不可更改的或者 修改代价比较大的参数，必须慎重考虑，在充分调研各个方面的需求后，方可确定参数值，以满足业务的正常健康的运行。 6.4.1. 必须修改的初始化参数 6.4.1.1. DB_CACHE_SIZE 数据库缓冲高速缓存用来存储最近使用的数据。处理查询时，服务器进程在数据库缓冲高速缓存中查找任何所需的块。如果未在数据库缓冲高速缓存中找到这个块，服务器进程就从数据文件读取这个块并且在数据库缓冲高速缓存中放置一个副本。由于对同一个块的后续请求可能在内存中找到这个块，因此这些后续请求可以不需要再次进行物理读取。 Oracle使用最近最少使用算法来释放近期未被访问的缓冲区，以便在缓冲高速缓存中为新块腾出空间。 衡量一个系统的的数据缓冲区设定效果的的一个重要指标是数据的缓存命中率。 对于OLAP系统，由于大量的数据访问使用全表扫描的方式来运行，回答的是一些统计结果和“如果”“那么”之类的分析和预测，其数据缓存的利用率较低，此时主要应该考虑的是规划系统的IO分布以提高系统IO效率，而不是提高系统缓存的命中率。 对于OLTP系统，由于每次处理的数据量较小而且大量的数据是可以重复使用的，所以数据的共享对提高系统的效率非常重要。下面的说明是针对OLTP应用系统的。 对于一个设计良好的应用系统，其数据库的缓存命中率应该在85%以上。对于DB_CACHE_SIZE的最终确定，需要依靠对实际系统的运行分析。 建议值：该参数一般取物理内存的30%-50%左右。以后根据运行分析，再作调整。 6.4.1.2. SHARED_POOL_SIZE 共享池是用来存放可供全局共享的对象，比如可重用的SQL语句执行计划，PL/SQL包、存储过程、函数和游标等信息。 共享池的大小是由SHARED_POOL_SIZE参数决定的，而且可以使用ALTER SYSTEM SET语句来动态改变，但总的SGA区大小不能超过SGA_MAX_SIZE。 设置的一般性原则 一般情况下，共享池大小的设定与应用的类型和并发用户的数量有关系。其实际设定值与系统的可用内存相关。 对于复杂的应用系统――指每个功能拥有较多的SQL语句同时每个SQL语句的执行计划又比较复杂的情况下，可以适当的增加SHARED_POOL_SIZE的值，随着并发用户的增加应当适当的调整SHARED_POOL_SIZE的值。 对于较小的应用系统，共享池的大小和数据缓冲区的比例可以为1：2，随着内存的增加，该比例可以进一步的减小，一般地共享池不必超过4GB。 对于一个正常的OLTP系统SQL语句的缓存命中率在95%就是一个比较好的系统了，对于OLAP系统80%的缓存命中率也是可以接受的。 对于一个具有4GB物理内存300个并发用户的C/S应用体系的OLTP应用系统可以设定SHARED_POOL_SIZE=800M，而对于一个具有8GB物理内存600个并发用户的C/S应用体系的OLTP应用系统则可以设定SHARED_POOL_SIZE=1200M。 建议值：建议此参数设置为512M,至少在200M-2G之间。 对于SHARED_POOL_SIZE的最终确定，需要依靠对实际系统的运行分析。 6.4.1.3. LARGE_POOL_SIZE 大池用来存储和SQL语句处理无直接关系的大内存结构。如在备份和恢复过程中复制的数据块。 大池是SGA区的可选选项，目的为了减轻共享池的压力。大池不使用最近最少使用（LRU）算法来进行管理。 大池的大小由参数文件中的LARGE_POOL_SIZE设置。能被ALTER SYSTEM语句动态改变。 设置的一般性原则 大池一般用来存放并行查询的相关信息和RMAN的磁盘IO缓冲区。对于为RMAN所分配的大池，其设定与RMAN分配的通道数有关，公式为： 通道数 * (16 MB + ( 4 * size_of_tape_buffer ) ) 硬盘备份时，size_of_tape_buffer为0，磁带备份时，该值默认为256k 建议值：OLTP：100M OLAP：256M。 6.4.1.4. DB_BLOCK_SIZE DB_BLOCK_SIZE参数决定数据库系统的性能和使用。 关于数据库的业务应用，该文档已在“数据库类型特点分析”中已讨论。数据库参数DB_BLOCK_SIZE的设置与原则： 1、OLTP类型的业务应用数据库系统，则DB_BLOCK_SIZE=4k或8k； 2、DSS类型的业务应用数据库系统，则DB_BLOCK_SIZE=16k或32k； 3、混合类型的业务应用数据库系统，则DB_BLOCK_SIZE=8k或16k；在oracle9i之后已经可以建立不同block size的表空间，根据不同的业务特性将不同的表放置于不同block size的表空间中，这个特性对于混合型系统会有性能的提升。 针对不同业务类型的数据库系统，以上设置方式，不是绝对的，要取决于业务具体情况。 6.4.1.5. SP_FILE 参数设置文件的绝对路径。 默认值：ORACLE_HOME/dbs/ 建议值：根据命名规范命名，文件文件应该放入存储，特别是在HA环境中。 6.4.1.6. PGA_AGGREGATE_TARGET 使用该参数的前提是，必须WORKAREA_SIZE_POLICY=AUTO，并且数据库运行方式是Dedicate sever和连接时Dedicate方式时，这个参数才可生效。此时参数SORT_AREA_SIZE,HASH_AREA_SIZE,BITMAP_AREA_SIZE会被忽略失效。但如果使用MTS和SHARE SERVER连接方式时，则PGA_AGGREGATE_TARGET会被忽略失效，此时*_AREA_SIZE就会生效。 PGA_AGGREGATE_TARGET是系统自动管理*_AREA_SIZE的大小，因此推荐使用这个参数。其设置原则为： 1、OLTP：PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20% 2、OLAT：PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50% 其中：total_mem为系统的物理内存。该原则是经验值，对于实际运行系统可视情况适当浮动调整。 6.4.1.7. PROCESSES 根据实际连接数据库的连接数的倍进行设置，不得低于500。 6.4.1.8. OPEN_CURSORS 该参数设定了一个会话一次可以打开的游标的最大数量， 并且限制使用的 PL/SQL游标高速缓存的大小， 以避免用户再次执行语句时重新进行语法分析。 如果初始化参数CURSOR_SPACE_FOR_TIME设为TRUE，则OPEN_CURSORS也需要相应的增大。 1)值范围：1 ～ 4GB-1。 2)默认值：50 3)推荐值：该值与应用类型相关，设置值不得低于200 6.4.1.9. MAX_DUMP_FILE_SIZE 建议不要使用默认值（UNLIMITED），最大设置100M。以避免数据库多次dump出trc文件时迅速撑满整个文件系统。 6.4.1.10. RECOVERY_PARALLELISM 参与实例恢复（RECOVERY）的进程数量，0或1意味着串行。 默认值：操作系统相关，通常为0 取值范围：操作系统相关，不超过PARALLEL_MAX_SERVER设置值 推荐值： 物理CPU个数－1 6.4.1.11. PARALLEL_EXECUTION_MESSAGE_SIZE 并行操作（并行查询，并行DML，并行恢复、复制）时消息的大小，设置较大值对并行查询时有并行进程间大量数据传输时比较有利。 默认值：2148 取值范围：2148 to 65535 推荐值： 8192，超过1T的系统可以更大 6.4.1.12. INSTANCE_GROUPS（RAC） 描述当前实例所属的资源组，RAC中并行操作模式时用到，和下面的PARALLEL_INSTANCE_GROUPS共同使用，可以限制并行操作使用的实例资源。 默认值：无 取值范围：无 推荐值：数据中心通常只使用两节点的RAC，因此只有两个设置值，RAC两个节点各使用一个（和两个实例的instance_name中的顺序保持一致）： node_01,node_both node_02,node_both 6.4.1.13. PARALLEL_INSTANCE_GROUP（RAC） 描述当前实例并行操作时可使用的资源组，RAC中并行操作模式时用到，和上面的INSTANCE_GROUPS共同使用，可以限制并行操作使用的实例资源。 默认值：无 取值范围：其设置名称必须在RAC节点的INSTANCE_GROUPS中曾出现。 推荐值：数据中心通常只使用两节点的RAC，需要保证日常并行仅在本节点进行，避免同时耗尽两台实例的所有资源。设置时应根据INSTANCE_GROUPS来设置： INSTANCE_GROUPS设置为node_01,node_both则PARALLEL INSTANCE_GROUP设置为node_01; INSTANCE_GROUPS设置为node_02,node_both则PARALLEL INSTANCE_GROUP设置为node_02。 node_both在通常情况下不会被使用，除非应用有非常大的并行操作需要同时使用两个节点上的资源，此时需要在应用中显式调用alter session set PARALLEL INSTANCE_GROUPS ＝‘node_both’; 6.4.1.14. 与DRM有关的隐藏参数（RAC） 10g的RAC中引入的DRM（Dynamic Resource Mastering）的概念,在RAC的不同节点间根据资源使用情况决定master节点。由于该功能并不十分完善，常常造成节点间master节点不断转换，触发bug，而且数据中心中所有的RAC都是两节点的，使用该功能意义不大，因此应该屏蔽该功能。屏蔽时需要配置如下两个默认参数： _gc_undo_affinity=FALSE _gc_affinity_time=0 6.4.2. 建议修改的初始化参数 6.4.2.1. SESSION_CACHED_CURSORS 设置该参数有助于减少系统parse SQL的时间，提高系统效率。10g中该参数的默认值为20。 建议设置session_cached_cursors＝50，对于实际运行系统可视情况适当浮动调整。 6.4.2.2. BACKUP_TAPE_IO_SLAVES 该参数指定了恢复管理器（Recovery Manager）所使用的从属进程的数目，这些从属进程被用来备份，拷贝或恢复数据到磁带上。当此参数为TRUE时，一个I/O从属进程被用来从磁带设备读取或写入数据。如果此参数为FALSE（默认情况下），在备份中不会使用I/O从属进程，而是由备份的阴影进程（shadow process）来存取磁带设备。 该参数设置为true后，通过模拟AIO的行为，主进程可以不必等待磁带写完成后就能继续处理其他数据块，会启动子进程来等待，从而加快了备份速度。 建议值：备份到磁带上的系统，设置该参数为true以加快速度。 6.4.2.3. JAVA_POOL_SIZE 参数JAVA_POOL设定了数据库用来存放JAVA代码的缓冲池的大小。 设置的一般性原则 对于使用了数据库中的JAVA的应用系统，该参数的大小取决于应用的类型，可以通过查询数据库系统中关于SGA的动态视图V$SGASTAT来确定数据库中是否使用了JAVA和合理的JAVA_POOL的值。 建议： 对于没有使用数据库中的JAVA的应用，不设置，使用系统默认值。 对于使用了数据库中的JAVA的应用，JAVA_POOL_SIZE的大小由应用的类型和数量确定。 6.4.2.4. OPTIMIZER_INDEX_COST_ADJ 控制优化器是否会更倾向于使用索引。 默认值：100， 推荐值： OLTP：50，即优化器评估时将走索引的代价评估为标准状况下（默认值100时）走索引代价的50％。 OLAP：不设置，使用默认值 6.4.3. 不可修改的初始化参数 6.4.3.1. COMPATIBLE 该参数设定了Oracle的版本，建库时取默认值。 只有在数据库upgrade（升级更高版本）时修改，该参数一旦设置为高版本后，不能回退到旧版本设定数值。 6.4.3.2. CURSOR_SHARING 控制可以共享相同的共享游标的 SQL 语句类型。 (1)值范围：FORCE | SIMILAR | EXACT EXACT：完全相同的 SQL 语句才能共享一个游标。 SIMILAR：让语句类似并且执行计划相同的语句共享一个游标。 FORCE：强制表达方式不同但语句意思相同的语句共享一个游标。 (2)默认值：EXACT 该参数设置为SIMILAR、FORCE对共享SQL执行计划，避免共享池碎片有一定好处，但是这样设置容易触发ORACLE的BUG，从保证系统稳定运行的角度来讲，不建议修改数据库默认设置，应该尽量从应用开发的角度来解决SQL共享问题。 6.4.3.3. SGA_TARGET 在Oracle 9i中，必须手工调整和设置各种数据库缓存区的大小。在Oracle 10g中，新增加了一个SGA_TARGET参数，该参数的作用是通过设置一个总值，让数据库根据实际需要，动态调整各块缓存区的大小，且每块缓存区的大小不会小于手动设置的参数值。 但在实际使用过程中，如果只是设置SGA_TARGET，而不手动设置各种缓存区的参数值，在系统繁忙时由于动态调整不同区域内存占用将将有可能会引发Oracle内部的一些bug。因此，建议SGA区内存管理通过手工设置，不设置该参数，维持默认值0。 6.4.3.4. SGA_MAX_SIZE SGA_MAX_SIZE为SGA的最大允许设置值，系统会自动根据DB_CACHE_SIZE /SHARED_POOL_SIZE /JAVA_POOL_SIZE /LARGE_POOL_SIZE 的值扩大，动态修改以上参数设置值时，以上参数设置值的总和不得超过本参数指定值。 设置该值大于以上pool的总和或SGA_TA RGET的设置值，会造成多余值无法被自动利用而造成浪费。应该尽量通过测试确实合适的pool设置值，避免系统自动扩展或临时扩展。 6.4.4. 建议不可修改的初始化参数 6.4.4.1. UNDO_RETENTION 以秒为单位设置UNDO争用值的下限。对自动扩展的UNDO表空间有价值，对固定大小的UNDO表空间（数据中心推荐做法），系统根据UNDO使用情况、UNDO表空间大小自动确定该值，除非启用了retention guarantee。 默认值：900， 6.4.4.2. SESSIONS 指定用户会话和系统会话的总量。默认数量大于 PROCESSES， 以允许递归会话。 1)值范围: 任何整数值。 2)默认值: 派生 * PROCESSES + 5) 3)建议值：该值无需特意指定，建议在指定PROCESS后使用系统计算值。 6.4.4.3. TRANSACTIONS 指定并行事务处理的最大数量。如果将该值设置得较大， 将增加 SGA 区的大小， 并可增加实例启动过程中分配的回滚段的数量。默认值大于 SESSIONS， 以实现递归事务处理。 对于连接到Oracle数据库的应用，根据其连接方式可以分为： 1.直接连接到数据库应用。如：客户前台直接通过SQL*NET，JDBC，OCI，IIOP连接到数据库应用，这类应用中由于用户的操作关系，一般的并发事务数较小，远远的小于连接到数据库的会话数； 2.用户前台并不直接与数据库相连而是通过中间件与数据库相连，这类应用中连接到数据库的会话数等于中间件的服务器的数量。由于应用服务器的集中作用，这时每个连接到数据库的会话为多个最终客户服务，会话的负荷较重，事务较多，极限情况下每个会话都可能出现由活动事务的情况，考虑到系统的递规调用的情况，最大值可能为倍的会话数。 1)值范围：4 到4G 2)默认值：派生 * SESSIONS) 对于第一类应用， TRANSACTIONS最好为1/10到1/5的PROCESS的值；对于第二类应用， TRANSACTIONS最好为倍的PROCESS的值. 不建议修改默认值，由ORACLE根据SESSIONS/PROCESS的设的设置自动计算。 6.4.4.4. DB_KEEP_CACHE_SIZE 建议对频繁使用的较小的字典表将其放入到KEEP池中。 通常情况下，系统新上线时如果开发部门没有明确要求，不进行设置。运维过程中觉得有必要设置时，可以进行修改。 6.4.4.5. LOCK_SGA 决定设置数据库参数lock_sga将目前数据库SGA索住在物理内存中。 同时，对于AIX，上述从操作还需要修改操作系统参数v_pinshm从缺省的0设置为1；对于HPUX，还需修改数据库hpux_sched_noage=178。 由于行内系统DB机器通常都是专用的，平时不会被交换至硬盘，可以不设置。 6.4.4.6. DB_FILES 该参数定义了数据库可以打开的数据文件的数目，此参数默认值为200，在创建实例的时指定，在RAC、DATAGUARD等环境要求各实例保持一致，修改时要求重启实例。 10g及以上版本中，该值重启实例就能修改，从数据中心的实践来看，通常数据文件都不会超过150个，设置过大的值会浪费一定内存，因此不必过早设置过大数值。 6.4.4.7. DB_FILE_MULTIBLOCK_READ_COUNT 该参数指定了在涉及一个全表连续扫描的一次I/O操作过程中读取的数据块（其大小由数据库初始化参数DB_BLOCK_SIZE决定）的最大数量。只有在系统对数据库的一个表进行全表扫描时（db file scattered read）该参数才起作用。 一般的情况下，该参数的设定与系统的应用类型相关，对于做大量数据查询的OLAP系统可以设定较大的值，如32，对于一般的OLTP系统设定为8或16是一个较好的选择。 值范围：此参数只适合Oracle9i，Oracle10g对此参数已不要求。 默认值：8 建议值：9i对OLAP系统推荐使用32，对OLTP系统推荐使用16,10g不必设置 6.4.4.8. LOG_BUFFER 重做日志文件跟踪服务器进程和后台进程对数据库进行的更改。该缓冲区是循环使用的，包含对数据文件所做的各种修改动作的信息，例如：INSERT，UPDATE，DELETE，CREATE，ALTER和DROP等操作。 重做日志缓冲区大小由参数文件中的LOG_BUFFER设置。 较大的日志缓冲区有助于降低日志文件的读写频率，提高系统的效率。 设置的一般性原则 由于系统的写日志进程(LGWR)在日志缓冲区中的重做条目(redo entry)达到1MB的时候，会把日志缓冲区的数据写入日志文件，同时当日志缓冲区1/3满的时候也会写日志文件，所以过大的日志缓冲区是没有意义的。 10g中该参数的值一般接近SGA设置值决定的粒度值（通常为16M），不必专门设置增大。 6.4.4.9. FAST_START_MTTR_TARGET 控制快速检查点的参数，指定从单个数据库例程崩溃中恢复所需的时间 (估计秒数)。该参数设置较低会造成数据库频繁执行检查点(checkpoint)，对系统运行效率有影响。 在系统设定以下参数后该参数失效： FAST_START_IO_TARGET LOG_CHECKPOINT_INTERVAL 值范围：[0, 3600]。 默认值：0 推荐值：一般系统可以不设置该值，除非AWR报告显示估计MTTR时间（Estd MTTR）超过300s。对不可用时间有严格要求的系统可设置为120或更低，但不得小于30。 6.4.5. 与并行有关的参数 PARALLEL_MIN_SERVER PARALLEL_MAX_SERVER PARALLEL_MIN_PERCENT PARALLEL_ATUOMATIC_TUNING PARALLEL_EXECUTION_MESSAGE_SIZE 一般情况下，除了PARALLEL_EXECUTION_MESSAGE_SIZE，以上参数都使用默认值，系统会根据CPU个数、表设置等自动选择并行。 在执行SQL语句操纵的数据对象中指定相应的参数，其优先次序依次为：语句中的提示最优先，其次为对象中的并行定义参数，最后为数据库的初始化参数。 有建议在OLTP系统上关闭oracle并行，以免并行任务对日常业务的冲击，考虑到数据中心各实例系统资源一般比较充足、数据量都比较大，在统计信息收集、索引重建维护等过程中充分利用并行还会大大提高效率，因此建议系统通常按默认设置打开并行。日常运维过程中，需要注意的是当使用并行时，最好由系统来自行决定并行度，如果没有充足的把握，尽量不要指定过高的并行度，以尽量避免并行操作对日常业务的影响。 6.5. 数据库连接服务 6.5.1. 专用服务器连接 在专用服务器模式下，客户连接和服务器进程（或者有可能是线程）之间会有一个一对一的映射。如果一台主机上有100条专用服务器连接，就会有相应的100个进程在执行。下图显示了专有服务器连接时的体系结构。 6.5.2. 共享服务器连接 共享服务器连接强制要求必须使用Oracle Net，即使客户和服务器都在同一台机器上也不例外。如果不使用Oracle TNS监听器，就无法使用共享服务器。如前所述，客户应用会连接到Oracle TNS监听器，并重定向或转交给一个调度器。调度器充当客户应用和共享服务器进程之间的“导管”。下图显示了共享服务器连接时的体系结构。 6.5.3. 连接服务建议 专用服务器连接 1、服务器只要有足够的资源（CPU和RAM）就使用专用服务器连接 共享服务器连接。 1、共享服务器只适用于OLTP系统，只在资源（CPU和RAM）不足的情况下使用。 2、其他高级连接特性需要使用共享服务器连接。 6.6. 数据库安全建议 1、采用满足需求的最小安装 随着Oracle数据库版本的不断升级，Oracle的功能也越来越多，因此安全检查点也越来越多。因此最好根据需求只安装所需内容。如：不需要web 功能，就不要安装相应组件。 2、安装时的安全 Oracle软件的安装目录要和系统盘分开。在Unix下，Oracle数据库系统的帐号和组的权限也要作相应设置。创建数据库管理员组(DBA)并分配root 和Oracle 软件拥有者的用户ID 给这个组。DBA 能执行的程序只有“710” 权限。在安装过程中SQL*DBA 系统权限命令被自动分配给DBA 组。 3、删除或修改默认的用户名和密码 Oracle 的默认安装会建立很多缺省的用户名和密码，而大部分的数据库管理员都不清楚到底有多少数据库用户，从而留下了很大的安全隐患。因此数据库软件应以定制方式安装。 4、安装最新的安全补丁 Oracle 数据库虽然被称为“Unbreakable”，但还是存在不同程度的安全漏洞。保证ORACLE数据库安全性的一个比较好的办法是时刻关注Oracle公司的安全公告，并及时安装安全补丁。安全公告和补丁位置如下： 6.7. 数据库备份和恢复 6.7.1. RMAN备份 该类备份是最为完善的备份恢复解决方案。对于从几百M至TB级的数据库，都可选择其作为备份方案。正因其完善，相关的备份架构相对复杂，维护操作也复杂。对于小型数据库（如几十M）来说，硬件和软件成本以及维护代价过高。其逻辑架构如下图： 6.7.2. EXP/IMP备份 该类备份是用于小型数据库备份作为合适。硬件和软件成本以及维护代价比较低。因其备份方式灵活，可以进行表级、模式级备份，常作为RMAN备份方案的补充。另外，对于跨操作系统平台迁移数据库，也经常使用其作为首选方案。 6.7.3. 存储级备份 数据块级虚拟磁带库相对其它备份设备具备了显着优势： 1）性能大幅提高，可支持接近磁盘阵列极限的备份/恢复速度。 2）免疫病毒，应用安全性等同物理磁带库。 3）去除磁盘碎片，保障性能持续性及磁盘效率。 4）无转译数据传输，良好的对数据结构损坏的承受力。 5）通用于主机软硬件环境，免除适配、维护和管理风险。 6.7.4. 数据库恢复 6.7.4.1. 一致性恢复 当实例意外地（如掉电、后台进程故障等）或预料地（发出SHUTDOUM ABORT语句）中止时出现实例故障，此时需要实例恢复。实例恢复将数据库恢复到故障之前的事务一致状态。如果在在线后备发现实例故障，则需介质恢复。在其它情况Oracle在下次数据库起动时（对新实例装配和打开），自动地执行实例恢复。如果需要，从装配状态变为打开状态，自动地激发实例恢复，由下列处理： 1、为了解恢复数据文件中没有记录的数据，进行向前滚。该数据记录在在线日志，包括对回滚段的内容恢复。 2、回滚未提交的事务，按步1重新生成回滚段所指定的操作。 3、释放在故障时正在处理事务所持有的资源。 4、解决在故障时正经历一阶段提交的任何悬而未决的分布事务。 6.7.4.2. 不一致恢复 介质故障是当一个文件、一个文件的部分或磁盘不能读或不能写时出现的故障。文件错误一般指意外的错误导致文件被删除或意外事故导致文件的不一致。这种状态下的数据库都是不一致的，需要DBA手工来进行数据库的恢复，这种恢复有两种形式，决定于数据库运行的归档方式和备份方式。 1、完全介质恢复可恢复全部丢失的修改。一般情况下需要有数据库的备份且数据库运行在归档状态下并且有可用归档日志时才可能。对于不同类型的错误，有不同类型的完全恢复可使用，其决定于毁坏文件和数据库的可用性。 2、不完全介质恢复是在完全介质恢复不可能或不要求时进行的介质恢复。重构受损的数据库，使其恢复介质故障前或用户出错之前的一个事务一致性状态。不完全介质恢复有不同类型的使用，决定于需要不完全介质恢复的情况，有下列类型：基于撤消、基于时间和基于修改的不完全恢复。 基于撤消(CANCEL)恢复：在某种情况，不完全介质恢复必须被控制，可撤消在指定点的操作。基于撤消的恢复地在一个或多个日志组（在线的或归档的）已被介质故障所破坏，不能用于恢复过程时使用，所以介质恢复必须控制，以致在使用最近的、未损的日志组于数据文件后中止恢复操作。 基于时间(TIME)和基于修改(SCN)的恢复：如果希望恢复到过去的某个指定点，是一种理想的不完全介质恢复，一般发生在恢复到某个特定操作之前，恢复到如意外删除某个数据表之前。 6.8. 数据库网络配置 6.8.1. 监听器的使用配置原则 1、创建监听器时，每个实例必须有一个监听器。不能服务于多个实例。 2、listener port 端口为安全起见，不使用缺省1521。 6.8.2. TNSNAMES的使用配置原则 1、数据库连接别名，推荐选择专用连接。如果是中间层应用服务器作为连接数据库的客户端，出于性能的考虑强烈推荐使用专用连接。 2、对于Oracle9i以上的数据库，推荐使用数据库服务名（service name），而不是实例名（SID）。 3、数据库连接的主机名，推荐使用数据库服务器的IP地址。如果是Oracle10g RAC，推荐使用虚拟IP（Virtual IP）。 6.8.3. RAC环境下TNSNAMES的配置 6.8.3.1. 启用负载均衡功能 可以通过Oracle自身的负载均衡功能，对应用发起的事务请求进行负载均衡。启用此功能必须对各客户端的文件进行相关配置，通过设置文件的LOAD_BALANCE参数为ON来启用负载均衡功能，配置如下： ODSPDDB1 = (DESCRIPTION = (load_balance = on) (failover = on) (ADDRESS = (PROTOCOL = TCP)(Host = )(Port = 1521)) (ADDRESS = (PROTOCOL = TCP)(Host = )(Port = 1521)) (CONNECT_DATA = (SERVICE_NAME = oadb) (FAILOVER_MODE = (BACKUP = oadb2) (TYPE = SESSION) (METHOD = BASIC) ) ) ) 6.8.3.2. 停用负载均衡功能 如果不需要Oracle平均的处理应用发起的请求，而是RAC各节点执行不同客户端发起的请求，必须通过设置文件的LOAD_BALANCE参数为OFF来取消负载均衡功能，配置如下： ODSPDDB1 = (DESCRIPTION = (load_balance = off) (failover = on) (ADDRESS = (PROTOCOL = TCP)(Host = )(Port = 1521)) (ADDRESS = (PROTOCOL = TCP)(Host = )(Port = 1521)) (CONNECT_DATA = (SERVICE_NAME = oadb) (FAILOVER_MODE = (BACKUP = oadb2) (TYPE = SESSION) (METHOD = BASIC) ) ) ) 7. 业务系统开发建议 7.1. 数据库模型设计规范 7.1.1. 命名规则 数据库对象如表、列、序列、过程、函数等在命名时要遵循如下规则： 1. 命名要使用富有意义英文词汇，避免使用缩写。 2. 数据库、数据库对象的名称可由多个单词组成的。 3. 数据库对象名称由如下部分组成：范围、类型、名称实体，各词汇间 采用\"_\" 连接。 4. 其中各数据库对象的范围和类型的具体含义及取值详见各数据库对象 的命名规则。 5. 数据库对象的名称不允许是Oracle SQL、Oracle PL/SQL的保留字和 关键字。 数据库对象 格式 样例 说明 同一个模块的表 <模块名称>_T_<表名称> ODS_T_SYSCONFIG 表使用相同前缀，长度不超过15个字符 普通索引 IND_<表名>_N<序号> IND_ORDER_N1 ORDER表第一个普通索引 ORDER表第一个位图索引 ORDER表第一个唯一索引 位图索引 IND_<表名>_B<序号> IND_ORDER_B1 唯一索引 IND_<表名>_U<序号> IND_ORDER_U1 普通视图 物化视图 V_<视图名> MV_<视图名> P_存储过程名 F_函数名 PKG_包名 V_ORDER MV_ORDER P_get_sysdata F_reception PKG_print 获取系统时间 业务受理 打印发票 初始化数据触发器 受理编号序列 存储过程 函数 包 触发器 TRG_触发器名称 TRG_initial 序列 别名 SEQ_序列名称 <用户名>_<表名> SEQ_register_number 7.1.2. 数据库对象 7.1.2.1. 建表的参数设置 1. 不允许将表建立在SYSTEM表空间上。 2. 表和索引建立在不同表空间上。 3. 建表时必须指明所存储的表空间。 4. 生成建表脚本时非空的列放在表的前部，可空的列放置在表的后部。 5. 数据缓冲池的类型：查询频繁且数据量较小的参数表采用buffer pool keep。 6. INITIAL：对于初始化数据量大的表，设置的值要大于初始化数据。 7. PARALLEL：对于OLTP系统，不允许使用该参数。 7.1.2.2. 主外键设计 1. 必须设置主键，通常不使用存在实际意义的列做主键，具体情况应结 合业务特性综合考虑。 2. 由Sequence产生的ID列，不作为组合PK中的列。 3. 删除约束时使用keep index参数。 4. 唯一性约束用于限定表中记录的唯一性，允许为空，允许对创建唯一 性约束的列进行修改操作。 5. 检查(check)，一般使用于列的取值受限于一些特定值的情况下，如员 工的性别，年龄，贷款的状态等。 6. 触发子约束，一般情况下不要使用。 7.1.2.3. 列设计 1. 定长字符型列使用CHAR类型，不定长字符型列使用VARCHAR2类型。 2. DATE精确到微秒。 3. 使用NUMBER型时必须指定长度。 7.1.2.4. 临时表 1. 对于只对本事务有效的临时表使用ON COMMIT DELETE ROWS关键字创 建该表。 2. 对于只对本会话有效的临时表使用ON COMMIT PRESERVE ROWS关键字 创建该表。 7.1.2.5. 索引 1. 选择使用普通B树索引。 2. 小表（数据量小于5000条记录为标准）不需要创建索引。 3. 对于OLTP应用，分区表使用分区索引。 4. 分区索引必须包含分区列，分区列按序放置在分区索引的末尾。 5. 建立分区索引必须指明表空间，不允许只写一个LOCAL。 6. 单个表上索引的个数不超过5个。 7. 将记录差别数最多的列放在索引顺序的最前面。 8. 索引数据的重复率不能超过20%。 9. 进行order by column desc排序时，创建column desc索引。 7.1.2.6. 视图 1. 物化视图的刷新间隔时间最小为3分钟。 2. 物化视图的基表必须创建主键。 7.1.2.7. 存储过程、函数和包 1. 存储过程、函数和包中不允许使用DML或DDL语句。 2. 存储过程、函数和包必须有相应的出错处理功能。 7.1.2.8. 触发器 1. 触发器的的选择必须与应用程序事务属性保持一致，以避免死锁发 生。 2. 在有大量数据导入表中的情况下，不使用触发器。 7.1.2.9. 序列 1. 加大序列的cache值，可以减少对于X$SEQ等基表的锁争用，但是会 造成序列的不连续性。 2. 应用程序不要将任何商业逻辑建立在序列的完全连续性上。 3. 不要设置序列为循环使用，防止数据覆盖现象。 7.1.2.10. Directory 1. 目录设置要求与Oracle系统用户目录分开。 2. Directory使用权限只赋予需要使用的数据库用户。 3. 对应的操作系统目录必须对oracle操作系统用户开放读写权限。 4. 定期清理和备份Directory对应的操作系统目录。 7.1.2.11. 别名 1. 对于只读用户，必须创建与表相同名字的别名。 2. 别名的访问顺序：public别名 -> private别名 -> 与表同名的对 象。 7.1.2.12. Database Link 1. 只允许从其它数据库中查询少量数据时使用dblink。 2. 不使用dblink更新其它数据库中的数据。 7.2. PLSQL开发规则 7.2.1. 总体开发原则 1. 完全按照设计文档进行开发； 2. 程序模块内聚度要高；外联度要低。 3. 要有正确、全面的故障对策。 4. 程序编写结构合理，条理清晰。 5. 程序名称要按照统一的命名规则进行命名。 6. 要充分考虑程序的运行效率，包括程序的执行效率和数据库的查询、 存储效率。在保证应用的同时要尽量使用效率高的处理方法。 7. 程序注释要详细、正确、规范。 8. 除非应用特别需要控制commit和rollback的提交时机，否则必须在 存储过程结束时执行显式的commit或者rollback操作。 9. 程序处理尽量支持7×24小时；对于中断，应用程序提供安全、简单 的断点再续处理； 10. 提供标准、简单的应用输出，为应用维护人员提供明确的进度显示、 错误描述和运行结果；为业务人员提供明确、直观的报表、凭证输出。 7.2.2. 程序编写规范 7.2.2.1. PL /SQL中的SQL编写规范 知识点描述 PL/SQL是Oracle公司对SQL的过程化扩展。在标准SQL的基础上面增加了流程控制、游标、异常处理等机制。PL/SQL是一种第四代的高级编程语言，它可以单独使用，也可以嵌入其他宿主语言一起使用。PL/SQL使得用户对Oracle数据库的操作变得非常简单。 使用原则 1. 1) 相关参数 CURSOR_SHARING 该参数可以设为exact，force和similar，缺省值为exact。 2) 使用情况 只有在用户的应用已经投入使用的并且SQL语句的共享效果不好，出现library cache latch的情况下，推荐使用cursor_sharing作为一种补救手段。 在进行开发的过程中，设定cursor_sharing＝exact 2. 1) 如何使用可以提高效率 尽量使用变量绑定 2) 3) 尽量使用RETURNING子句 使用NOCOPY的编译提示。默认情况下，OUT和IN OUT参数都是 按值传递的。为参数NOCOPY的编译提示表示按照引用传递。 举例 1. 询条件。 SQL>DEFINE c=1; SQL>SELECT * FROM t1 WHERE c1=&c; 2. 使用RETURNING子句的例子：更改某员工工资水平的同时返回更使用变量绑定的例子：在sqlplus中定义变量，使用变量做为查 改员工的姓名和更改后的薪水。 PROCEDURE update_salary (emp_id NUMBER) IS name VARCHAR2(15); new_sal NUMBER; BEGIN 7.2.2.2. 1.使用%TYPE声明 变量声明原则 变量类型声明时，如果其含义和应用表某字段含义或某变量相同时，使用%TYPE声明。如： credit REAL(7,2); debit credit%TYPE; 2.使用%ROWTYPE声明 记录类型声明时，如果其含义和某应用表行数据或某cursor定义相同时，使用%ROWTYPE声明，如： DECLARE r_emp emp%ROWTYPE; CURSOR c1 IS SELECT deptno, dname, loc FROM dept; r_dept c1%ROWTYPE; 3.变量声明格式 每行至多包含一条语句，例如： 正确写法： v_valid BOOLEAN; v_area VARCHAR2(20); 错误写法： v_valid BOOLEAN; v_area VARCHAR2(20); UPDATE emp SET sal = sal * WHERE empno = emp_id RETURNING ename, sal INTO name, new_sal; END; 当参数包含大数据结构（如集合，对象类型等），使用按值传递将会消耗过多的内存资源。为了避免这种情况，在参数传递时使用NOCOPY的编译提示。 DECLARE TYPE Platoon IS VARRAY(200) OF Soldier; PROCEDURE reorganize (my_unit IN OUT NOCOPY Platoon) IS …… BEGIN …… END; 7.2.2.3. 知识点描述 游标 为了处理SQL语句，Oracle必须分配一片内存区域，用来存储完成该处理所必需的信息，这就是上下文区域（CONTEXT AREA）。游标（CURSOR）就是一个指向上下文区域的句柄（handle）或指针。必须首先通过游标定位某个位置，然后才能处理该位置的内容。 显式游标用来处理返回多于一行的select语句。显式游标可以由用户自定义，并通过open，fetch，close步骤来调用。 隐式游标用于处理insert，update，delete和单行select…into语句。隐式游标是系统自定义的，通过PL/SQL引擎打开和关闭，因此，隐式游标不需要用户open，fetch，close。 游标变量动态地指向sql语句。它类似于pl/sql变量，在运行时刻可以拥有不同的取值，因此可以实现在运行时刻与不同的语句相关联。 游标属性附加在游标名字的后面，但游标属性返回的不是类型，它返回的是在表达式中可以使用的数值。游标属性分以下四种： %FOUND:这是一个布尔属性。如果前一个fetch语句返回一个行，那么它就会返回true，否则，返回false。 %NOTFOUND:这是一个布尔属性，如果前一个FETCH语句返回一个行，返回FALSE。仅当前一个FETCH语句没有返回任何行，才返回TRUE。 %ISOPEN:这是一个布尔属性，用来确定相关的游标是否被打开。如果已打开，返回TRUE，否则，返回FALSE。 %ROWCOUNT:这是个数字属性，它返回到目前位置，由游标返回的行的数目。 使用原则 1.相关参数 1) 2) 3) cursor_space_for_time open_cursors session_cached_cursors 2.如何使用可以提高效率 1) 尽量使用变量 对于经常使用的SQL语句，推荐在语句中使用变量来代替常量 ，这样SQL语句可以在SGA中共享，避免了每次执行对语句的分析，提高了内存和CPU的使用效率。 2) 空记录测试 在应用开发的过程中，经常需要测试表中是否含有符合某种条件的记录，一种常见的错误的做法是使用count(*)的方式判断其值是否为0，这是一个极端低效率的做法，因为它要对全部数据扫描一遍。 推荐使用判断游标属性的方法。 原来的代码： SELECT count(*) INTO :v_tmp FROM … WHERE …. IF v_tmp >0 THEN ……………. END IF; 改进的代码： CURSOR c1 is SELECT 原因： 以下处理流程，是Oracle开发的经典错误： step1：先打开cursor（以综合前置为例：select 当日所有交易 from 联机表，十多万条）； step2：从游标中fetch记录，insert入历史表，delete联机表，如此循环，到达若干条记录后commit； step3：再接着如此处理，直到所有记录处理完毕； step4：处理结束后关闭cursor。 按照ANSI标准，cursor在提交时无效，必须重新打开。但是Oracle允许编写非标准的SQL，应用程序可以跨commit从cursor中获取行，不过commit后rbs段是可以被重用的，而cursor本身又必须保持读一致性，所以容易出现“rollback too old”的错误。 b. 解决建议： 方案1：若干记录commit后立即关闭cursor，再重新打开cursor，继续处理。 方案2：只commit一次或尽量减少commit，但需要较大rbs空间。 2)“PLS-00382: expression is of wrong type” a. 原因： 有相同的返回类型，但是变量类型不一样的两个游标变量之间赋值，会产生类型不匹配的错误。 例如： DECLARE TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; TYPE TmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; emp_cv1 EmpCurTyp; emp_cv2 TmpCurTyp; BEGIN emp_cv2 := emp_cv1; --会产生类型不匹配的错误 …… END; b. 解决建议： 将两个游标变量改为相同类型。 上面的例子改为如下： DECLARE TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; emp_cv1 EmpCurTyp; emp_cv2 EmpCurTyp; BEGIN emp_cv2 := emp_cv1; …… END; 举例 1.打开游标时，通过游标属性判断游标纪录是否为空的方法。 DECLARE CURSOR c1 IS SELECT app_id FROM app_def WHERE app_enname = p_app_enname; BEGIN OPEN c1; EXCEPTION WHEN no_data_found THEN END; END; 2.打开游标前判断游标纪录是否为空的方法。 DECLARE CURSOR c1 IS SELECT app_id FROM app_def WHERE app_enname = p_app_enname; v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FROM app_def WHERE app_enname = p_app_enname; IF v_count = 0 THEN OPEN c1; ...... END IF; END; BEGIN ...... 7.2.2.4. 知识点描述 集合 集合是一组类型相同的元素。每个元素有一个唯一的下标，下标确定元素在集合中的位置。 数组：数组包含固定数目（数组长度）的元素，可以在运行过程中改变数组长度。 嵌套表：嵌套表可以包含任意数目的元素，嵌套表使用顺序数字做为下标。 索引表：类似于其他编程语言中的散列表，索引表的下标可以是数字或者字符串。 使用原则 1.使用情况 1)嵌套表和索引表的比较： 在许多方面嵌套表和索引表有相似之处，例如：两种表数据类型拥有相同的结构，他们都使用下标记号进行访问。 但是两者还是有一些不同之处： a.嵌套表存储可以为数据库的列，但是索引表不行。 b.嵌套表适合于重要的数据关系需要持久保存的情况。索引表适合于相对较小的集合，这些集合可以在内存构建。索引表还有一个优点在于下标的灵活性。索引表的下标可以是负数，可以不是顺序的，而且可以是字符串而非数字。 2)嵌套表和数组的比较： a.如果预先知道元素的数目而且数组中的元素经常需要一起顺序访问，数组是一个很好的选择。数组可以是行内存储，也可以是行外存储，可以一次更新或返回数组中所有的元素。 b.在嵌套表中，可以删除任意元素，而不仅仅只能去掉最后一个元素。嵌套表存储在行外，每个嵌套表和一个系统产生的数据库表相关联。嵌套表适用于查询和更新只影响集合中某些元素。 2.如何使用可以提高效率 使用批量绑定提高性能所谓绑定就是指为SQL语句中的PL/SQL变量赋值。PL/SQL引擎执行过程控制语句而将SQL语句传给SQL引擎，SQL引擎执行SQL语句并将执行的结果返回给PL/SQL引擎。在PL/SQL引擎和SQL引擎之间进行频繁的上下文切换会严重影响性能。如下面的PL/SQL块所示，循环执行某个 SQL语句，该SQL语句是单独处理集合的一个元素。FOR循环的每一次迭代，DELETE语句都会发往SQL引擎。Oracle提供一下一些途径来减少PL/SQL引擎和SQL引擎之间的上下文切换。 1)使用FORALL语句。 FORALL关键字通知PL/SQL引擎在将PL/SQL变量值传入SQL语句前进行批量绑定。虽然FORALL语句在形式上是循环迭代，但在实质上它不是FOR循环语句。FORALL的语法格式如下，其中index只能是集合的下标，sql_statement必须有引用集合元素的INSERT，UPDATE或者DELETE语句： FORALL index IN lower_bound..upper_bound sql_statement; 2)使用BULK COLLECT子句将查询结果返回到集合中。 关键字BULK COLLECT通知SQL引擎在将结果返回到PL/SQL引擎前进行批量绑定，这里的批量绑定是指将输出结果绑定到集合中。BULK COLLECT可以用在SELECT INTO，FETCH INTO和RETURNING INTO语句中。BULK COLLECT的语法如下所示： ... BULK COLLECT INTO collection_name[, collection_name] ... 3.使用过程之中会经常出现的错误以及解决方案 1) a. DECLARE TYPE NumList IS TABLE OF NUMBER; nums NumList; -- atomically null BEGIN nums(1) := 1; -- raises COLLECTION_IS_NULL (1) END; 该例会报ora-06531的错误。 b. 原因： ORA-06531: Reference to uninitialized collection 范例一： 定义集和之后，要求初始化集合。该语句在对集合中的元素操作（语句1）之前，没有对集合进行初始化。 c. 解决建议： 对集合或集合中的元素进行操作前，首先初始化集合，例如，在语句（1）之前加上初始化语句：nums := NumList(null,null); 2)ORA-06502: PL/SQL: numeric or value error: NULL index table key value a.范例二： DECLARE TYPE NumList IS TABLE OF NUMBER; nums NumList; -- atomically null BEGIN nums := NumList(1,3,6); -- initialize table nums(null) := 3 ; END; 该例会报错：ORA-06502: PL/SQL: numeric or value error: NULL index table key value b. DECLARE TYPE NumList IS TABLE OF NUMBER; nums NumList; -- atomically null a VARCHAR2(2); BEGIN nums := NumList(1,3,6); -- initialize table a:='a'; (1) nums(a) := 3 ; (2) END; 该例会报错：ORA-06502: PL/SQL: numeric or value error: character to number conversion error. c.原因： 下标的类型不对，或者下标的转换未成功。 范例三： 例如范例二中下标为”null”，不是number类型，也不能转换成number类型，因此会报以上错误。 例如范例三中下标a的值为’a’，该值不能转换成number类型，因此会报以上错误。 d. 解决建议： 将下标的值改为数字，或者number型变量，或者可以转换为number型变量的字符型变量。例如，将范例三中的语句(1)改为a:=’2’，或者直接将语句(2)改为nums(2):=3。 3)ORA-06532: Subscript outside of limit a.范例四： DECLARE TYPE NumList IS TABLE OF NUMBER; nums NumList; -- atomically null BEGIN nums := NumList(0,0); -- initialize table（1） nums(0) := 3; -- （2） nums(3) := 3; -- （3） END; 语句2）和3）都会报这个错误。 b.原因： 初始化语句初始了n个元素，则下标的范围就是1~n，超出该范围就会报错。 范例四中，初始化语句（1）初始化该集合有两个元素，因此，下标的范围为1~2，超出这个范围就会报错。 c. 解决建议： 将下标的值改为正确的范围以内。 (4) ORA-01403: no data found a.范例五： DECLARE TYPE NumList IS TABLE OF NUMBER; nums NumList; -- atomically null BEGIN nums := NumList(0,0); -- initialize table (1); -- delete element 1 (1) IF nums(1) = 1 THEN ('successful!');--(2) END IF; END; 该语句会报ora-01403的错误。 b. 原因： 语句(1)中已将nums(1)元素删去，nums(1)元素已经没有数据了，因此语句（2）中读num(1)元素的值就会报错。 c. 解决建议： 如果想让某元素中没有数据，可以给它赋空值，以后对该值的读取就不会出错。例如范例五中，语句（1）可以换为：nums(1):=null。 举例 1.下面的DELETE语句只发往SQL引擎一次，即使它执行了三次DELETE操作。 DECLARE TYPE NumList IS VARRAY(20) OF NUMBER; depts NumList := NumList(10, 30, 70); -- department numbers BEGIN FORALL i IN .. DELETE FROM emp WHERE deptno = depts(i); END; 2.在下面的例子的例子中，SQL引擎将EMP表中所有的EMPNO，EMPNAME数据装载到嵌套表中，然后将嵌套表返回给PL/SQL引擎。 DECLARE TYPE NumTab IS TABLE OF %TYPE; TYPE NameTab IS TABLE OF %TYPE; enums NumTab; -- no need to initialize names NameTab; BEGIN SELECT empno, ename BULK COLLECT INTO enums, names FROM emp; ... END; 7.2.2.5. 知识点描述 动态PL/SQL 大多数PL/SQL程序所做的工作都是特定的、预知的工作。例如，一个存储过程可能接受一个员工号码做为输入参数，然后更新该员工的薪水。在这种情况下，UPDATE语句的全文在编译时是确定的。这样的语句是不会在执行阶段发生变化的，这种SQL语句称之为静态SQL语句。 但是有些时候，程序必须在执行阶段处理一个变化的SQL语句。例如，一个通用的报表程序为了产生不同的报表必须运行不同的SQL语句。这种语句很可能在执行阶段发生改变，这种SQL语句称之为动态SQL语句。 动态SQL语句存储在字符串中，程序在执行阶段建立这个语句。这些字符串必须包含合法的SQL语句，要绑定的变量前要加冒号，变量的名称是无关的。例如，下面两个语句是没有区别的： 'DELETE FROM emp WHERE sal > :my_sal AND comm < :my_comm' 'DELETE FROM emp WHERE sal > :s AND comm < :c' 拼装SQL语句执行的性质与动态SQL语句相同。如果拼装SQL语句的源如果有些是别人输入的、外部文件读取的，则不能直接拼上去，必须先检查，防止攻击。 防止攻击的办法有： 1.尽量使用绑定变量的的动态sql； 2.对输入条件进行字符检查 使用原则 1.使用限制 由于使用动态sql，在编译阶段是分析不出该语句的。因此每次执行都需要编译，对系统运行产生额外的性能开销。所以，不建议联机系统的交易程序使用动态SQL。 2.使用情况 由于在通常的PL/SQL中只能使用数据操作语言DML和事务控制语言TCL而不能使用其它一些命令如数据定义语言DDL和系统控制命令，当出现这种需求是一个做法是调用外部任务，另外一个做法就是使用动态的SQL。 动态 SQL并不在客户端被解释转换，而是由客户端的程序传输到服务器端后在服务器端解释并执行。 使用动态SQL的最常见的用法有：使用truncate table命令代替delete from table的操作、在系统进行日结、月结、年终结算时创建新的数据表，创建应用系统使用的临时表等， 下面是通过动态SQL来使用truncate table命令的方法： ... CREATE OR REPLACE PROCEDURE p_truncate_table (table_name varchar2(30)) as cursor_handle INTEGER; no_query_sql_command VARCHAR2(200); BEGIN no_query_sql_command := 'truncate table ’||table_name; cursor_handle:=; (cursor_handle,no_query_sql_command,1); cursor_handle:=(cursor_handle); (cursor_handle); END; / ... 也可以直接使用EXECUTE IMMEDIATE来执行，如： ... BEGIN EXECUTE IMMEDIATE 'truncate table ’||table_name;; END; ... 应当说明的是动态SQL的使用在不同的版本间稍有不同，在使用是需要在不同的版本上做测试。 3.如何使用可以提高效率 1)尽量使用变量绑定 联机交易的事务特点是执行的频度高，执行时间短。此类交易的sql语句都建议使用变量绑定。因为使用变量绑定的方式编写的sql语句，能大大减少sql语句硬分析的性能消耗。 2)使用批量动态SQL（BULK FETCH语句， BULK EXECUTE IMMEDIATE语句， FORALL语句， COLLECT INTO语句， RETURNING INTO子句） 4.使用过程之中会经常出现的错误以及解决方案 1)“ORA-00903: invalid table name” a. 原因： 在动态PL/SQL中数据对象名称不能使用变量绑定。 例如：下面的存储过程 CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS BEGIN EXECUTE IMMEDIATE 'DROP TABLE :tab' USING table_name; END; b. 解决方法： 将变量绑定改为直接字符串拼凑。 CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS BEGIN EXECUTE IMMEDIATE 'DROP TABLE ' || table_name; END; 举例 1. 在动态SQL中使用变量绑定的例子 CREATE PROCEDURE fire_employee (emp_id NUMBER) AS BEGIN EXECUTE IMMEDIATE 'DELETE FROM emp WHERE empno = :num' USING emp_id; END; 7.2.2.6. 知识点描述 对象 对象（object）是一个用户定义的复杂的数据类型，其数据结构包含一些变量，以及对这些变量进行处理的函数和过程。组成数据结构的变量，称为“属性”（attributes）。对属性进行操作的过程和函数，称为“方法”(methods)。当创建一个对象时，实际上是创建了一个对真实世界中的对象的抽象模板。例如，对一个雇员创建对象，这个对象包括的属性有：姓名，部门，职位，等等；包括的方法有：工资计算，考勤计算，等等。 使用对象时，需要创建实例（instance），通过实例来调用对象中的方法。在真实世界中，不难理解这样做的目的。还是以雇员对象为例，只有一个对象，确有很多个雇员，每个雇员的属性都不相同，所以，要为每个雇员创建实例，他们可以共享“雇员对象”这个数据结构，而分别拥有自己的数据。 使用原则 1. 使用过程之中会经常出现的错误以及解决方案 1)ORA-30625: method dispatch on NULL SELF argument is disallowed a.范例： DECLARE Eagle employee; --创建实例 Jacky employee; --创建实例 BEGIN ,); --通过实例调用对象中的方法 END; 该语句会报ora-30625的错误。 b.原因： 实例已创建，但没有初始化。 c.解决建议： 在调用方法前，初始化实例。以上范例中，加上初始化语句： Eagle := NEW employee('EAGLE',24,7,'TACH'); 举例 例一：创建对象 CREATE OR REPLACE TYPE employee AS OBJECT ( -- 定义属性（attribute） Name VARCHAR2(40), -- 定义方法(method) MEMBER FUNCTION salary (rank NUMBER,dept VARCHAR2) RETURN NUMBER, ); CREATE OR REPLACE TYPE BODY employee AS MEMBER FUNCTION salary (rank NUMBER,dept VARCHAR2) RETURN AGE IS BEGIN ………… END salary; END; 例二：使用对象 DECLARE Eagle employee; --创建实例 Jacky employee; --创建实例 BEGIN Eagle := NEW employee('EAGLE',24,7,'TACH'); --初始化实例 Jacky := NEW employee('JACKY',36,1,'MANAGE'); --初始化实例 ,); --通过实例调用对象中的方法 END; 7.2.2.7. 知识点描述 LOB类型 一个LOB是一个存储大量数据的数据库字段，例如图形文件或长的文本形式的文档。Oracle提供了四种不同类型的LOB：CLOB、NCLOB、BLOB、BFILE。 LOB类型 CLOB BLOB NCLOB BFILE 说明 用于存储单字节字符数据 存储没有结构的二进制数据 用于存储定宽的多字节国家字符集数据 用于对Oracle数据库以外存储的大型二进制文件进行只读形式的访问。 LOB数据不是以内联的方式（inline）存储在数据库表中。LOB数据是存储在一个单独的位置上，同时一个“LOB定位符”存储在原始表中。该定位符是一个指向实际数据的指针，实际数据可以存储在和原始数据有不同存储参数的表空间中。 使用原则 1．使用情况 LOB是用于存储非结构化数据的。所谓非结构化数据是指不能被分解为标准组件的数据。例如，一个员工可以分解为姓名（字符串），标识（数字），薪水等等。但是，如果给的是一张图片，会发现数据是由一长串0和1的数字流，在数据存储方面，他们不能分解为良好的结构。非结构化的数据往往是非 常大的，如文本、图片、视频片断或者音乐等等。一个典型的员工纪录可以有几百个字节，但是即使是少量的多媒体数据也可能有它几千倍那么大。 LOB类型帮助支持Internet应用，随着Internet和内容丰富应用的发展，数据支持这样一种数据类型是非常有需要的：1）可以存储非结构化数据；2）优化处理大量的非结构化数据；3）为存储在数据库内或者数据库外非结构化数据提供统一的访问方式。 由于BLOB没有字符集转换，因此当几种LOB都能满足应用要求时，推荐使用BLOB。 2．如何使用以提高效率 1)SQL*Loader性能：加载数据到内部LOB。 下表可以看到加载数据到内部LOB的各种方法的相对性能的比较，在后面将一一列举每种加载方法。 加载方法 字段长度预先确定 字段之间由分隔符分隔 长度-值对字段 每个文件一个LOB 最高 低 高 高 相对性能 2)选择内联存储和外联存储的比较。 一般情况下，当LOB的容量小于4K时，Oracle建议对LOB进行内联存储。内联存储在数据操作时效率更高。但是当需要进行大量的基本表操作时，则不希望LOB进行内联存储，如全表扫描、范围扫描或者在表的非LOB列上面有大量的更新、查询操作。 3)提高JDBC驱动加载BLOB和CLOB的性能 使用JDBC Thin驱动向LOB中加载数据是比较慢的，因为JDBC Thin驱动实际上是使用DBMS_LOB包。而使用JDBC OCI和JDBC server-side internal驱动使用本地的LOB API，加载数据会比JDBC Thin驱动快。因为使用本地LOB API没有JDBC Thin驱动实现带来的一些额外消耗。 Oracle建议使用InputStream和OutputStream来访问和操作LOB数据。因为使用流的方式，JDBC驱动可以合理的控制缓存以减少网络的数据往返。 ()加载LOB数据的效率比()高，但使用()加载数据要求数据必须放在服务端。 4)适时的释放临时LOB。 在PL/SQL，C (OCI)和Java中，当SQL语句在LOB列上面操作时会返回临时LOB作为结果，临时LOB在PL/SQL块结束时会自动释放。 可以在任何时候释放不需要的临时LOB，以释放系统系统资源和临时表空间。如果不合理的释放SQL语句返回的临时LOB，临时表空间将会被过度的消耗，而系统性能将会下降。 3.使用过程之中会经常出现的错误以及解决方案 1)更改LOB段的存储参数时出现ORA-904错误。 a. 问题描述： 更改一个LOB对象的MAXEXTENTS存储参数时得到904错误， SQL> CREATE TABLE t_lob 2 (DOCUMENT_NR NUMBER(16,0) NOT NULL, 3 DOCUMENT_BLOB BLOB NOT NULL 4 ) 5 STORAGE (INITIAL 100k 6 NEXT 100K 7 PCTINCREASE 0 8 MAXEXTENTS 100 9 ) 10 TABLESPACE users; Table created. SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE, INITIAL_EXTENT, 2 NEXT_EXTENT, PCT_INCREASE, MAX_EXTENTS 3 FROM USER_SEGMENTS; SEGMENT_NAME SEGMENT_TYPE INITIAL NEXT_EXTENT PCT MAX_EXTENTS ------------------------- ------------ ------- ----------- --- ----------- T_LOB TABLE 106496 106496 0 100 SYS_LOB0000030066C00002$$ LOBSEGMENT 40960 40960 50 505 SQL> ALTER TABLE T_LOB MODIFY LOB (SYS_LOB0000030066C00002$$) 2 (STORAGE (MAXEXTENTS 200)); ALTER TABLE T_LOB MODIFY LOB (SYS_LOB0000030066C00002$$) * ERROR at line 1: ORA-00904: \"SYS_LOB0000030066C00002$$\": invalid identifier b. 解决办法：更改LOB对象的列名而不要直接使用LOB段的名字 SQL> SELECT TABLE_NAME, COLUMN_NAME FROM USER_LOBS 2 WHERE SEGMENT_NAME = 'SYS_LOB0000030066C00002$$'; TABLE_NAME COLUMN_NAME ------------- --------------------------------- T_LOB DOCUMENT_BLOB SQL> ALTER TABLE T_LOB MODIFY LOB (DOCUMENT_BLOB) 2 (STORAGE (MAXEXTENTS 200)); Table altered. Explanation ----------- The ALTER TABLE is a command that allows you to modify logical entities, not physical entities. 2)更改LOB时出现ORA-22853错误。 a. 问题描述 在执行下属语句时出现错误 MODIFY LOB lobitem (storage_clause pctversion cache logging) ORA-22853: invalid LOB storage option specification Cause: A LOB storage option was not specified Action: Specify one of CHUNK, PCTVERSION, CACHE, NOCACHE, TABLESPACE, STORAGE, INDEX as part of the LOB storage clause. b. 解决办法：把LOB对象的名字放在括号内，正确的命令是： MODIFY LOB (lobitem) (storage_clause pctversion cache logging) 举例 1.加载字段长度预先确定的LOB数据 在控制文件中，加载入列的LOB的数据长度是确定的，字段长度预先确定可以使数据解析器非常高效的工作。不幸的是，通常很难保证所有的LOB的数据长度是相等的。 Control File： LOAD DATA INFILE '' INTO TABLE Multimedia_tab FIELDS TERMINATED BY ',' (Clip_ID INTEGER EXTERNAL(5), Story LOBFILE (CONSTANT '') CHAR(32)) Data File ： 007, 008, Secondary Data File ： Once upon the time ... The end, Upon another time ... The end, 2.字段之间由分隔符分隔的数据的加载 在LOBFILE文件中，LOB数据由分隔符分隔，这样就允许不同长度的LOB加载入相同的列。当然，高度的灵活性是以牺牲性能为代价的。加载这种格式的数据相对而言是比较慢的，因为加载器必须扫描所有数据并查找分隔符字符串。 Control File LOAD DATA INFILE '' INTO TABLE Multimedia_tab FIELDS TERMINATED BY ',' (Clip_ID INTEGER EXTERNAL(5), Story LOBFILE (CONSTANT '') CHAR(2000) TERMINATED BY \" 每个LOB数据前有一个数字标识该LOB的长度。这种方式的性能会比分隔符分隔数据的方式高效，但是这种方式要求在加载数据之前知道每个LOB的长度。 Control File LOAD DATA INFILE '' INTO TABLE Multimedia_tab FIELDS TERMINATED BY ',' ( Clip_ID INTEGER EXTERNAL(5), Story LOBFILE (CONSTANT '') VARCHARC(4,2000) ) Data File 007, 008, Secondary Data File 0031 Once upon a time ... The end. 0000 4.每个LOBFILE仅包含一个LOB的加载方式。 Control File LOAD DATA INFILE '' INTO TABLE Multimedia_tab REPLACE FIELDS TERMINATED BY ',' ( Clip_ID INTEGER EXTERNAL(5), ext_FileName FILLER CHAR(40), Story LOBFILE(ext_FileName) TERMINATED BY EOF ) Data File 007,, 008,/tmp/, Secondary Data File Once upon a time ... The end. 5.将一个GIF文件写入BLOB的例子。 /**将一个GIF文件写入BLOB的例子。*/ import .*; import .*; import .*; import .*; public class LobExample { public static void main (String args []) throws Exception { } etBLOB (1); OutputStream ostream = (); while ((length = (buffer)) != -1) (buffer, 0, length); 何使用可以提高效率 1)使包尽量大众化，使其可以在未来的应用中被重用。 2)尽量使用Oracle提供的包，注意不要重复创建Oracle已有的包。 3)包头反映出应用的设计。应该在包头只声明用户或其他开发人员必须用到的类型，项和子程序，而将包自己用的对象作为私有变量放在包体中。 4)当代码改变时，为减少重编译，除非必要，尽量少在包头中声明对象。因为对包体做改变时，不要求重编译，而对包头做改变时，要求重编译。 5)减少包中静态的全局变量的使用，避免操作的相互影响。 7.2.2.8. 知识点描述 故障处理规则 PL/SQL中，一个警告或错误条件被称为“异常”，这个条件可以是系统本身的，也可以由用户自定义。当满足条件时，程序从当前块中跳出，进入异常处理块，程序的异常处理由用户自己定义。 使用异常处理的目的：防止错误的结果被带到外部程序，并且使出错的程序异常处理后退出，而不是中断后直接返回操作系统，从而提高应用程序的健壮性。 使用情况 在使用的PLSQL块中，如果使用了不能返回的确定结果的SQL语句，应用要在EXCEPTION中对程序可能出现的异常进行处理，避免出现未处理的出错被传递到外层块，导致程序逻辑错误。 对于系统已经定义了的异常，应用可以直接使用，对于用户自行定义的异常需要使用pragma exception_init(exception_name，-SQLCODE)方式来初始化。 对于继续处理的EXCEPTION，可以不记录错误堆栈，对于无法继续处理的程序，EXCEPTION中至少要获取程序的返回码，返回错误码，错误描述，其名称分别定为out_retcode， out_errcode， out_errm。为了能够进一步分析程序出现异常的原因，最好能够获得出现异常时的调用错误堆栈。 常见错误及解决方案 很多情况下，特别是含有DML语句的程序中，系统希望一个存储过程是一个原子操作，要么不执行，要么完全执行。由于出错后程序跳出当前块，进入异常处理块，所以有可能因为程序只执行了一半而造成数据不一致。为防止这种情况发生，可以将commit尽量放在块的末尾，或者可能发生出错的地方之后。并在异常处理中使用rollback。将没做完的事务回滚。 在应用设计时应该设置一个错误信息表，出错后，必须向这个表写入错误信息。写入的方式建议使用独立会话，把错误信息插入到错误信息表。 举例 EXCEPTION WHEN NO_DATA_FOUND THEN ROLLBACK; out_retcode:='L9003'; out_errcode:=SQLCODE; out_errm:=SQLERRM; <把错误代码插入到错误信息表> RETURN; WHEN OTHERS THEN ROLLBACK; out_retcode:='L9000'; out_errcode:=SQLCODE; out_errm:=SQLERRM; <把错误代码插入到错误信息表> RETURN; 7.3. SQL语句编写规则 7.3.1. 查询语句的使用原则 7.3.1.1. 使用正确索引 合理的使用正确的索引是提高系统执行效率的关键因素，对索引的使用需要注意以下一些问题： 1.过度索引 一般情况下，使用索引可以缩短查询语句的执行时间，提高系统的执行效率，但是要避免以下两种过度索引的情况出现： 1)对一个表建立了过多的索引，从而造成维护索引所需要的时间超过使用索引所降低的时间，从而造成整个系统效率的下降，这一般发生在对一些进行大量更新的表上面。因此一个联机表上的索引，最多不要超过5个。 2)由于索引数据的区分度不够，造成了使用索引而引起的效率的下降，这一般发生在对数据进行大的统计分析的时候。可以通过指定全表扫描等提示(hint)来避免。 运算符 在应用程序中为了使用方便，对字符型变量进行比较时经常使用LIKE运算符进行字符模式的匹配。 需要注意的是对于LIKE运算，如果通配符%出现在字符串的尾部或中间，LIKE运算符将可以使用索引进行字符串的匹配，否则如果通配符%出现在字符串的开始，则LIKE必须使用全表扫描的方式去匹配字符串，这将产生较大的系统负荷。 一般情况下，为了提高系统的效率，可在通配符的左端提供较多的数据信息以降低查询的数量。 值 NULL值是系统中目前尚无法确定的值，在Oracle数据库系统中NULL是一个比所有的确定值都大的值，然而又不能用大于小于等于运算符来比较，对NULL值的处理只能用是与否来判定，所有的对NULL值的判定都会引起全表扫描，除非同时使用其它的查询条件。 4.复合索引 复合索引是使用多个数据列的索引，第一个字段的数据区分度非常重要，也是影响一个联合索引效率的关键所在。 7.3.1.2. 改写查询语句 1.关联子查询与非关联子查询 对于一个关联子查询，子查询是每行外部查询的记录都要计算一次，然而对于一个非关联子查询，子查询只会执行一次，而且结果集被保存在内存中。 因此，通常在外部查询返回相对较少的记录时，关联子查询比非关联子查询执行得更快；而子查询中只有少量的记录的时候，则非关联子查询将会比关联子查询执行得更快。 2.尽量用相同的数据类型的数据进行比较，以避免发生数据转换 SQL语言对于数据类型不像JAVA和C++那样进行严格的数据类型检查，不同种数据间可以进行某些运算，但是在做数据操作时需要数据库进行隐含的类 型转换，在大数据量的查询中，由于要对每一个数据项做同样的操作，会造成时间和CPU处理能力的浪费。 实际应用中通常发生的隐含的数据类型的转换有： 1)字符型到数字型的转换，如：SELECT ‘1234’ +3 FROM DUAL等 2)数字型到字符型的转换，如：UPDATE DEPT SET EMPNO=5678等 3)日期型到字符型的转换，如：UPDATE EMP SET DNAME=SYSDATE等 上述的转换都是隐含发生的，在实际使用中要避免使用不同类型的数据操作。 7.3.1.3. 减少排序发生 排序是数据库中执行频度比较大的一种操作，根据排序执行的范围不同又可以分为内排序和外排序。数据库中的排序操作的数量应该尽量的减少同时每个排序的时间能够缩短。为此可以： 1.使用UNION ALL来代替UNION 2.添加索引。在表连接的时候使用索引可以避免排序的发生，比如添加了合适的索引，可以使连接方式由排序合并连接(Sort Merge Join)转变为索引的嵌套循环连接(Indexed Nestted Loop Join)。 3.在DISTINCT，GROUP BY，ORDER BY子句涉及到的列上创建索引。 4.使用较大SORT_AREA_SIZE 5.在用户的临时表空间上使用大的extent大小。 7.3.1.4. 使用并行查询 并行查询适合下列情况： 1.全表扫描的查询语句 2.返回大数据量的查询所改造的语句 3.其它一些数据操作中的查询子句 对于较大的数据量的查询，可以使用提示(hint)来强制数据库使用并行查询，在Oracle数据库中，并行查询的优先级为语句提示(hint)，表的定义，数据库初始化参数。 7.3.1.5. 减少死锁发生 在Oracle数据库中大量的数据库的锁都是行级锁，不同的会话间竞争同一条记录的可能性较小，同时Oracle数据库中提供了自动的死锁检测机制来避免数据库的死锁，保证数据库系统的可用性。因此一般情况下应用系统不需要特殊的设计来解决系统的死锁问题，但是在下列情况下系统可能出现死锁： 1.表A上的列n上有一个索引，表B上的列m使用A上的列n作为外键，然后表A的列n上的索引被删除，此时更新表B上列m将造成对表A的表级锁，会导致死锁的发生。 2.应用大量的使用SELECT ……FOR UPDATE语句造成系统不必要的加锁。 对于第一种情况要对出现死锁的相关表进行检查，确认是否相关索引被错误的删除。对于第二种情况要修改应用，避免对数据的不必要的加锁。 7.3.1.6. 使用集合运算符 Oracle数据库的集合运算包括: UNION， UNION ALL， INTERSECT和MINUS操作。 一般情况下当两个集合中的数据都比较多时，集合运算都是比较耗时的操作，使用时需要谨慎小心。如果可能，可以使用UNION ALL操作代替UNION操作。 7.3.1.7. 使用连接方式的原则 1.嵌套循环连接（NESTED LOOP JOIN） 知识点描述 嵌套循环连接操作关系到两个表，一个内部表和一个外部表。Oracle比较内部数据集的每一条记录和外部数据集的每一条记录，并返回满足条件的记录。 嵌套循环连接通常会产生巨大的数据量，所以对嵌套循环连接的使用要有严格的限制。 当一个嵌套循环连接的内部表中存在索引的情况，嵌套循环连接变为改进的有索引的嵌套循环连接(INDEXED NESTED LOOP JOIN)，通常有索引的嵌套循环连接在产生较小的数据量的情况下可以较快的执行。 在使用有索引的嵌套循环连接是必须确保在查询中使用了正确的驱动表和正确的驱动数据集，通常情况下使用包含数据量较小的表作为驱动表。 一般如果使用基于成本的优化器，系统会自动选择驱动表，如果是使用基于规则的优化器，则后表作为驱动表。 应用原则 一般的嵌套循环连接的速度较慢，产生的数据量较大，应该严格控制其使用。 在使用有索引的嵌套循环连接时，必须保证其驱动表有合适的索引，最好为主键或唯一键，同时希望在另外一张表在相同的列上有索引。 举例 下面给出了两种连接的例子： 对于不存在索引的表EMP和DEPT执行以下操作： SELECT count(*) FROM dept,emp WHERE = and empno=30 为NESTED LOOP JOIN，因为数据库需要对表DEPT中所有的行进行处理。如果此时JOB表的JOBNO列上有索引则上述查询的方式转变为有索引的嵌套循环连接(Indexed Nested Loop Join) *************************************************** * Nested Loop Join * -- table emp has index on jobno column * -- table job has index on jobno column ***************************************************** SQL> SET AUTOT ON EXP SQL> SELECT JOBNAME,SUM(SAL) 2 FROM EMP E,JOB J 3 WHERE = 4 AND <3 5 GROUP BY ; JOBNAME SUM(SAL) ---------------------------------------- ---------- job name 1 27725 job name 2 26025 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (GROUP BY) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' 3 2 NESTED LOOPS 4 3 TABLE ACCESS (BY INDEX ROWID) OF 'JOB' 5 4 INDEX (RANGE SCAN) OF 'IND_JOB_JOBNO' (UNIQUE) 6 3 INDEX (RANGE SCAN) OF 'IND_EMP_JOBNO' (NON-UNIQUE) 2.散列连接(Hash Join) 知识点描述 散列连接将驱动表加载进内存，使用散列技术将驱动表与较大的表进行连接，连接过程中，对大表的访问使用了散列访问。散列连接可以提高等连接的速度。 如果可用的散列空间内存足够大，可以加载构建输入，那么散列连接算法能够很好地运行简单的散列连接，但是并不需要将整个输入放入hash_area_size内存。如果散列连接中较小的驱动表无法放入 hash_area_size，那么Oracle将拆分该散列连接，并使用temp表空间中的临时段来管理这个溢出。 Oracle推荐将驱动表的hash_area_size设置为驱动表字节总数的倍。 应用原则 一般的散列连接发生在一个大表和一个小表做连接的时候，此时小表中的数据全部被读入内存，其处理的速度较快。 举例 **************************************************** * Hash Join * -- table emp has 450000+ rows * -- table dept has round 1000 rows ***************************************************** SQL> SET AUTOT ON EXP SQL> SELECT COUNT(*) \"NUMBER OF DEPT\ 2 FROM EMP E,DEPT D 3 WHERE = 4 AND DNAME LIKE 'DNAME LINE 100%' 5 ; number of dept total salary -------------- ------------ 260 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=281 Card=1 Bytes=32) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=281 Card=1062 Bytes=33984) 3 2 TABLE ACCESS (FULL) OF 'DEPT' (Cost=4 Card=106 Bytes=2650) 4 2 TABLE ACCESS (FULL) OF 'EMP' (Cost=265 Card=458752 Bytes=3211264) 3.排序合并连接(Sort Merge Join) 知识点描述 排序合并连接是指从目标表中读取两个记录数据集，并使用连接字段将两个记录集分别排序的操作。合并过程将来自一个数据集的每一条记录同来自另一个数据集与之匹配的记录相连接，并返回记录数据集的交集。 排序合并连接有几种不同的排序方法：外部合并连接，反合并连接和半合并连接。这些不同的排列方法使得合并步骤服务于不同的目的，可以是找到记录数据集的交集，也可以是找到满足SQL语句中WHERE子句条件的那些记录。 应用原则 一般的排序合并连接是在散列连接不能达到应用的要求或Oracle优化器认为排序合并连接效率更高的情况下使用。在下述的条件下排序合并连接被使用： 1)数据表之间的连接不是等值连接而是其它连接 2)数据库使用的优化模式是基于RBO而不是CBO 举例 在下述的查询中 ***************************************************** * Sort Merge Join * -- table emp has no index on deptno column * -- table dept has no index on deptno column ***************************************************** SQL> set autot on exp SQL> select dname,sum(sal) 2 from dept d,emp e 3 where = 4 and <5 5 group by dname; DNAME SUM(SAL) ---------------------------------------- ---------- DNAME LINE 1 20475 DNAME LINE 2 19475 DNAME LINE 3 22400 DNAME LINE 4 22675 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (GROUP BY) 2 1 MERGE JOIN 3 2 SORT (JOIN) 4 3 TABLE ACCESS (FULL) OF 'EMP' 5 2 SORT (JOIN) 6 5 TABLE ACCESS (FULL) OF 'DEPT' 7.3.1.8. 复杂查询的原则 1.限制表连接操作所涉及的表的个数 对于数据库的连接操作操作，可以简单的将其想象为一个循环匹配的过程，每一次匹配相当于一次循环，每一个连接相当于一层循环，则N个表的连接操作就相当于一个N-1层的循环嵌套。 一般的情况下在数据库的查询中涉及的数据表越多，则其查询的执行计划就越复杂，其执行的效率就越低，为此需要尽可能的限制参与连接的表的数量。 1)3-5个表的处理方法 对于较少的数据表的连接操作，需要合理的确定连接的驱动表，从某种意义上说，确定合理的驱动表就是确定多层循环嵌套中的最外层的循环，可以最大限度的提高连接操作的效率，可见选择合适的驱动表的重要性。 RBO模式下，在SQL语句中FROM子句后面的表就是要进行连接操作的数据表，Oracle 按照从右到左的顺序处理这些表，让它们轮流作为驱动表去参加连 接操作，这样可以把包含参与连接的数据量最少的表放在FROM子句的最右端，按照从右到左的顺序依次增加表中参与连接数据的量。 CBO模式下，则不需要考虑表放置的位置。 2)5个表以上的处理方法 对于涉及较多的表(>5+)的数据连接查询，其查询的复杂度迅速增加，其连接的存取路径的变化更大，存取路径的个数与连接的表的个数的阶乘有关：当n=5时存取路径＝1X2X3X4X5=120个，而当连接的表的个数为6时存取路径变为1X2X3X4X5X6=720个，数据库优化器对于数据的存取路径的判断近乎为不可能，此时完全依赖与用户的语句书写方式。 对于较多的表的连接，要求开发人员查询返回的结果能够有所预测，同时判断出各个参与连接的表中符合条件的记录的数量，从而控制查询的运行时间。 同时为了提高查询的效率，此时可以把部分表的一些连接所形成的中间结果来代替原来的连接表，从而减少连接的表的数目。 3)对表连接操作涉及的表数目不应多于8个表 如果查询语句拥有过多的表连接，那么它的执行计划过于复杂且可控性降低，容易引起数据库的运行效率低下，即使在开发测试环境中已经经过充分的测试验证，也不能保证在生产系统上由于数据量的变化而引发的相关问题。应该在应用设计阶段就避免这种由于范式过高而导致的情况出现。 2.限制嵌套查询的层数 应用中影响数据查询的效率的因素除了参与查询连接的表的个数以外，还有查询的嵌套层数。对于非关联查询，嵌套的子查询相当于使查询语句的复杂度在算术级数的基础上增长，而对于关联查询而言，嵌套的子查询相当于使查询语句的复杂度在几何级数的基础上增长。 因此，降低查询的嵌套层数有助于提高查询语句的效率。 对嵌套查询层数的限制要求：如果查询语句拥有过多的嵌套层数，那么会使该查询语句的复杂度高速增加，应该在数据库设计阶段就避免这种情况出现，不应多于5层。 3.灵活应用中间表或临时表 在对涉及较多表的查询和嵌套层数较多的复杂查询的优化过程中，使用中间表或临时表是优化、简化复杂查询的一个重要的方法。 通过使用一些中间表，可以把复杂度为M*N的操作转化为复杂度为M+N的操作，当M和N都比较大时M+N < SQL> CREATE TABLE LOC1 AS SELECT * FROM LOC2; Table created. Elapsed: 00:00: SQL> DELETE FROM LOC1 2 WHERE ROWID IN 3 (SELECT ROW_ID FROM 4 (SELECT ROWID ROW_ID,ROWNUM ROW_NUM FROM LOC1) 5 WHERE ROW_NUM >10000)； 121088 rows deleted. Elapsed: 00:00: SQL> COMMIT; Commit complete. Elapsed: 00:00: SQL> DROP TABLE LOC1; Table dropped. Elapsed: 00:00: SQL> CREATE TABLE LOC1 AS SELECT * FROM LOC2; Table created. Elapsed: 00:00: SQL> CREATE GLOBAL TEMPORARY TABLE ROWID_ROWNUM 2 (ROW_ID ROWID, ROW_NUM NUMBER); Table created. Elapsed: 00:00: SQL> INSERT INTO ROWID_ROWNUM SELECT ROWID,ROWNUM FROM LOC1; 131088 rows created. Elapsed: 00:00: SQL> DELETE FROM LOC1 WHERE ROWID IN 2 (SELECT ROW_ID FROM ROWID_ROWNUM WHERE ROW_NUM >10000); 121088 rows deleted. Elapsed: 00:00: 4.使用一些改写复杂查询的技巧 1)转换连接类型 参见上文的改写查询语句部分 2)把OR转换为UNION ALL 3)区分不同的情况使用IN或EXISTS 对于主查询中包含较多条件而子查询条件较少的表使用EXISTS，对于主查询中包含较少条件而子查询条件较多的表使用IN。 4)使用合理的连接方式 在不同的情况下使用不同的连接方式：散列连接适用于一个较小的表和较大的表的连接，排序合并连接需要对小表进行排序操作，索引的嵌套循环连接对于一般连接是有效的，但是需要在连接的关键字上有索引的存在。 应用开发人员应该根据不同的情况选取合适的连接方式。 1.使用并行查询 如果查询的数据在表中所占的比例较大，可以考虑使用并行查询来提高查询的执行速度。 对于UPDATE/INSERT/DELETE操作的查询部分也可以同样做并行查询的处理。 2.使用PL/SQL过程和临时表代替复杂查询。 对于涉及巨大的表的连接的统计查询，由于可能会造成大量的排序统计工作，使得查询的速度变慢，此时可以考虑使用PLSQL替代原来的查询。 7.3.2. DML语句的调整原则 DML语句包括Insert、Update、Delete和Merge。在使用DML语句的时候，也会遇到性能低下的情况，可以参考以下的内容来做出调整。 7.3.2.1. Oracle存储参数的影响 Oracle的DML语句出现性能问题的一些情况： 操作缓慢并且占用过多的I/O资源。 这种情况发生在PCTFREE较高且行记录较大，频繁地寻找新的空闲数据块的时候。 在数据对象有(多个)索引的情况下，Insert 操作还需要对索引进行维护，这额外的增加了数据插入的成本，所以对于没有过度索引的表的维护是比较花费资源的。 操作缓慢。 Update操作需要获得操作对象上的独占锁，如果其它的用户已经占有了该对象的非兼容的锁，那么Update操作就需要等待，通常这是非常短的时间，但是如果该用户在操作时被打断，则该用户持有这个锁的时间就可能变长，造成其它用户的等待，这是一个管理上的问题。 如果Update操作扩展了一个Varchar或Blob列导致发生了行迁移的时候，其更新也会变慢。 操作缓慢。 通常发生在记录被删除，而且Oracle必须将数据块重新连接到该表的Freelist的时候。 由于删除操作会产生大量的undo和redo信息，所以对系统的性能的影响较大。如果可能可以使用更改状态标志和在另外的表中插入新的记录来代替删除操作。 对于删除全表的操作可以用truncate table等命令来实现，在PL/SQL等不支持truncate命令的环境中可以使用动态SQL来实现truncate的功能。对于碎片比较多的系统，删除操作在某些时候涉及到数据块的回收。 另外，当有多个任务想要对一张数据表进行Insert或Update操作的时候，这张数据表的段头可能会产生冲突情况，这种冲突可以表现为出现等待事件：Buffer Busy Waits，此时对于数据库表的处理办法是提高Pctfree的值，降低一个数据块中数据的行数，对于冲突的索引可以使用倒排索引来避免同一数据块中的数据的索引存放在同一索引块中。 调整原则 请参考上文相关部分以适当地对这几个参数进行设置，以在有效空间利用和高性能之间获得一个平衡点。 一般情况下对于并发更新比较频繁的对象要降低同一数据块中的数据行数以减少系统对于同一数据块的竞争，以空间换时间以提高性能。对于并发更新竞争不是那么频繁的对象要提高同一数据块中的数据行数，以提高系统空间的利用效率，同时提高缓存的利用率以达到提高系统整体效率的目的。 作为队列使用的表的竞争会比较剧烈，这类表中包含的总的数据行数不会太多，所以可以使用空间来换取效率。 7.3.2.2. 大数据类型的影响 使用大数据类型（RAW，LONG，BLOB，CLOB等）的时候，主要问题在于它们常常会超出普通的数据块大小，导致数据列分散到相邻的数据块。这样，每行记录被访问时，Oracle都会产生两次以上的I/O操作。 调整原则 对于使用大数据类型的表，一般情况下其数据的行连接是不可避免的，因此只能尽量的降低这种事件发生的频率。 一般情况下，对于单独存储的LOB对象，可以指定其使用较大的db_block_size的表空间以控制其使用的数据块的个数，同时减少对LOB对象的访问次数，其中数据块的大小根据各个不同的LOB的平均大小有所不同。 小于4k的LOB对象可以和数据存储在一起，这样对数据访问速度的影响较大，这种情况下可以按照LOB对象的实际大小而选择不同表空间来存储数据。 7.3.2.3. DML执行时约束的开销 约束会对DML操作的性能产生影响。 1.完整性约束：时间会耗费在验证每一个数据值是否合法上。 2.主键约束：主键约束是由唯一索引来强制实施的，而且这个索引在插入和更新操作上的开销使大容量的插入操作和更新操作运行变慢，因为每个值都必须从索引中查询一次。 3.外键约束：强制实施了交互表之间的数据关系，必须访问外部的数据表以确认当前值是否合法，才能进行插入。 4.其它约束：对于其它检查的，数据也需要做相应的检查 5.触发器：触发器对DML的执行效率也有较大的影响，特别当触发器的类型为for each row的时候。 调整原则 1.在执行大容量的插入或更新任务时，可以暂时禁用所有与所影响的数据表有关的约束、触发器，装载数据，最后才重新启用约束 2.在启用约束时，需要考虑非法的数据。 7.3.2.4. DML执行时维护索引所需的开销 在记录被插入和修改时，表上所有的参与索引都必须实时地进行更新。这通常会产生由于大量排序而增加的系统开销，严重降低系统的执行性能。 调整原则 1.在大型的DML批操作中，在更改数据表之前，删除全部索引。在批操作之后，重新建立起索引。 2.如果索引因为不平衡而产生拆分等额外操作，那么可以通过重建索引操作，也会减少维护索引所需的时间。 3.如果在数据被加载到数据库之前其数据已在外部完成排序，则在创建索引是可以使用NOSORT 选项，需要注意的是NOSORT只能使用升序而不能使用降序，并且不能使用在倒排、分区、位图索引中。 在批量数据加载后的创建索引的过程中，可以指定用来排序的表空间已提高排序的效率，在指定排序表空间是需要注意该表空间的extent的大小，一般情况下该值越大越好。 8. 数据库体系结构 8.1. 整体结构 Oracle数据库是一个“对象-关系”型数据库管理系统，由数据库（database）和数据库实例（instance）组成。 数据库的整体结构如下图所示： 数据库启动后，首先会在内存中分配一块存储空间，叫做系统全局区（System Global Area），然后启动一系列后台进程，用于管理Oracle的物理文件。其中SGA和后台进程叫做数据库实例，数据库的物理文件包括：数据文件，控制文件，重做日志文件，归档文件，参数文件和密码文件等等。通常数据库就是指数据库的物理文件，一个数据库可由一个（单机）或多个（RAC）数据库实例相对应。 8.2. 内存结构 数据库的启动和运行需要一定的内存空间和后台进程，这些内存空间和后台进程叫做数据库实例。数据库实例所占用的内存空间分为两部分：系统全局区（SGA）和程序全局区（PGA）。 Oracle数据库的内存结构如下图所示： 8.2.1. 系统全局区（SGA） 系统全局区是一组共享的内存结构，用于存放数据和数据库实例的控制信息。多个用户可以同时连接到同一个实例，这些用户可以共享该实例的SGA中的数据，所以系统全局区又叫做“共享全局区”。当启动一个实例时，Oracle会分配SGA给这个实例，关闭实例时它的SGA又被回收，SGA主要由以下几个 部分构成：数据告诉缓冲区（Database Buffer Cache）、重做日志缓冲区（Redo Log Buffer）、共享池（Shared Pool）、Java池（Java Pool）、大池（Large Pool）、流池（Streams Pool）。 8.2.1.1. 数据库高速缓冲区 数据库高速缓冲区是SGA的重要组成部分，用于存放从数据文件中读取的数据或存放用户对数据库所做的修改，所有连接到相同实例的用户都可以共享这些数据。 数据库高速缓冲区中的缓冲器被组织成两个列表：写列表（Write List）和最近最少使用列表（Least Recently Used List，LRU）。写列表存放那些被修改但还未保存到磁盘上的数据。LRU中包含三个缓存器：自由缓存（Free Buffers）、正在被访问的缓存（Pinned Buffers）和脏数据缓存（Dirty Buffers）。 当用户查询数据时，Oracle首先在数据库高速缓冲区中的正在被访问缓存中进行查找，如果找到，则把数据返回给用户；如果未找到，Oracle则从数据文件中读取所需数据到Free Buffers中，自由缓存就变成了Pinned Buffers，并把该缓存排到LRU的末尾，然后把Pinned Buffers中的内容返回给用户。 当用户膝盖数据库时，直接修改的是Pinned Buffers中的内容，Pinned Buffers则变成了Dirty Buffers。当LRU中不再有自由缓存可用时，Oracle会把位于LRU头部（最近最少使用的）的Dirty Buffers中的内容移动到写列表中，此时Dirty Buffers又变成了Free Buffers，LRU中的缓存就是这样周而复始的循环使用。写列表中的数据最终要被写入磁盘上的数据文件中。 8.2.1.2. 重做日志缓冲区 重做日志缓冲区（Redo Log Buffer）是SGA的组成部分，它是一个循环使用的缓冲区，用于存放日志记录，日志记录可用于数据库实例的恢复。 当用户对数据库进行操作时（insert,update,delete,create,alter,drop etc..）一旦发生实例中断的意外（例如断电），用户所做的修改还没有来得及写入数据文件就丢失了。为防止这种情况发生，在数据库运行过程中Oracle会把用户的修改以日志记录的形式记载在SGA的重做日志缓冲区中，并每隔3秒钟或用户提交一个事务时由日志写进程（LGWR）把日志记录写入磁盘上的联机日志文件（Redo Log）。 也就是说Oracle为了以防万一，会在数据库运行过程中对还没写入数据文件的修改进行备份，当数据库重新启动时，Oralce会从联机日志文件中读取反映修改操作的日志记录，读入日志缓冲区，再根据日志缓冲区中的日志记录恢复用户先前对SGA中的数据库高速缓冲区所做的修改，最终把修改结果写入数据文件，从而恢复数据库实例。 初始化参数LOG_BUFFER用于设置日志缓冲区的大小。比较大的日志缓冲区能够减少联机日志文件的磁盘I/O。如果一个系统中经常有大的、长时间运行的事务，应该增大LOG_BUFFER的值。 8.2.1.3. 共享池 共享池也是SGA的重要组成部分，用于存放SQL语句、PL/SQL程序代码、数据字典等信息。共享池主要包含库缓冲区和数据字典缓冲区。初始化参数SHARED_POOL_SIZE用于控制共享池的大小。在默认情况下，在32bit的操作系统上SHARED_POOL_SIZE的值是8M，在64bit的操作系统上是64M。 库缓冲区存放解析并执行过的SQL语句和PL/SQL程序代码。当用户发送一条SQL语句或PL/SQL程序代码到数据库时，Oracle首先对这条语句进行语法分析，然后将分析结果（执行树和执行计划）保存到库缓冲区中。若不同的用户执行相同的SQL语句，那么他们可共享这些解析结果，这会加快SQL语句的执行。 库缓冲区主要包含共享SQL区，私有SQL区，PL/SQL过程和包等。共享SQL区可被所有用户共享。SQL语句和PL/SQL程序代码的解析树和执行计划是存放在库缓冲区的共享SQL区中。之所以称之为共享，是因为如果另外一个用户执行相同SQL语句，那么这个用户可以直接使用共享SQL区中的解析树和执 行计划，而不必对这条SQL语句重新解析。这样可以提高SQL语句的执行效率。SQL语句的一部分信息存放在共享SQL区中，另外一部分存放在私有SQL区中。存放在私有SQL区中的这部分信息只能被执行这条SQL语句的用户独享，其他用户不能使用。这部分信息包括SQL语句的绑定变量，环境和会话参数，还包括运行PL/SQL程序单元所需的局部变量，全局变量和包变量。 Oracle在执行SQL语句时要访问表、视图、存储过程、和触发器等数据库对象，Oracle把这些对象的信息存放在数据字典中。数据字典是在创建数据库的时候由Oracle自动创建的，存储在磁盘上的系统表空间。数据库对象的信息是以表和视图的形式存储在数据字典中的。由于每次执行SQL语句都要访问数据字典，会降低数据库性能，所以Oracle把频繁访问的数据字典信息缓存到共享池的数据字典缓冲区，然后根据其中的表，视图等对象的信息对SQL语句进行解析并执行。 8.2.1.4. Java池 Java池主要用于支持在数据库中运行Java代码。 8.2.1.5. 大池 大池是Oracle可选的内存结构，数据库的备份和恢复，并行查询时会用到大池。 8.2.1.6. 流池 流池是Oracle可选的内存结构，主要用于对流的支持。 8.2.2. 程序全局区（PGA） 连接到数据库实例的用户程序请求数据库时，用户的SQL语句由Oracle的服务器进程负责解析并执行。启动一个服务器进程的时候，Oracle会给这个进程分配一块内存区域，叫做程序全局区。程序全局区用于存放服务器进程的数 据和控制信息。每个服务器进程的程序全局区不能被其它进程共享，只能由这个服务器进程读写这块内存区域。 程序全局区由排序区、会话区、游标区以及堆栈区组成。其中排序区主要用于存放排序操作所产生的临时数据，其大小由初始化参数SORT_AREA_SIZE定义；会话区用于保存用户会话所具有的权限、角色、性能统计信息；游标区用于存放执行游标操作时所产生的数据；堆栈区用于保存会话过程中的绑定变量、会话变量等信息。 8.2.3. 后台进程 进程是操作系统的一种机制，它执行一系列的步骤，完成指定的任务（有的操作系统叫做作业或任务）。进程和程序的区别是，进程是动态的，程序是静态的。程序是一系列指令的集合，进程强调的是执行过程。一个进程通常有自己的私有内存区。Oracle的后台进程分为两类：用户进程和Oracle进程。 Oracle进程运行在Oracle数据库服务器端，分为服务器进程和后台进程。服务器进程用于处理连接到数据库实例的用户进程的请求，负责解析并执行SQL语句，如果用户需要在数据不在SGA中，服务器进程会把数据从磁盘上的数据文件中读取到SGA的数据库高速缓冲区，并把结果返回给用户。也就是说服务器进程是为用户进程服务的。 Oracle系统为了使性能最佳和协调多个用户，使用了一些附加进程，成为后台进程，后台进程是在实例启动时自动建立的。一个Oracle实例可以有许多后台进程，但它们不一定一直或者同时存在，每个后台进程只在需要的时候才存在。Oracle后台进程有：数据库写进程、日志写进程、归档进程、系统监控进程、检查点进程、进程监控进程，恢复进程，作业队列进程，队列监控进程，其它后台进程等等。 8.2.3.1. 用户进程 当用户运行应用程序或使用Oracle客户端时，就创建了用户进程。 用户进程在访问数据库实例时，会建立一条通信路径，这条通信路径叫做连接。连接使用不同的通信机制，如果用户进程和实例在同一台机器上，则使用进程间的通信机制；如果用户进程和实例进程不在同一台机器上，则使用网络通信机制。 一个用户必须提供用户名和密码才能登录到数据库，用户成功登录数据库后就建立了一次会话。会话是有生命周期的，会话的生命从用户登录开始，到退出数据库实例为止。一个用户没有登录到数据库时就是一个用户，一旦登录到数据库以后就建立了一次会话。一个用户可以创建多个会话。如果100个客户端使用同一个用户名同时登录一个数据库实例，就创建了100个会话。 会话是Oracle数据库服务器对连接数据库的用户进行记录的一种手段，管理员通过对会话进行管理，就可以掌握有哪些用户，在什么时间，以什么客户程序连接了数据库并执行了什么样的操作，在必要的时候管理员还可以随时中断会话，切断连接，保护数据库的安全。 8.2.3.2. 数据库写进程（DBWR） 当Oracle发现SGA中的数据库高速缓冲区中没有自由缓存时，Oracle会通知数据库写进程利用最近最少使用机制把最近最少使用的脏缓存中的数据从写列表写入磁盘，从而脏缓存又变成了自由缓存，可以接收从数据库中读取的数据了。 8.2.3.3. 日志写进程（LGWR） 当用户输入SQL语句对SGA中的数据库高速缓冲区进行修改时，为了防止发生断电等意外，修改还没有来得及写入数据文件就丢失了，服务器进程把与这个修改相关信息以日志记录的形式复制到日志缓冲区中。通常日志写进程会以最快的速度（每隔3秒）把这些日志记录写到联机日志文件中，以便在日志缓冲区中预留出更过的空余空间，进而服务器进程又把新的日志记录写到日志缓冲区中。日志文件用于日后的数据库实例的修复。每当用户提交一个事务或 者每隔3秒钟或日志缓冲区的1/3已被填满时，日志写进程会把日志缓冲区中的日志记录写入联机日志文件。 8.2.3.4. 日志归档进程（ARCn） Oracle把写满的联机日志文件复制到一个或多个路径下，这个过程叫归档。这样生成的文件叫归档日志文件（Archived Redo Log），存放归档日志的路径叫归档路径（目录）。只有数据库运行在归档模式下才能进行归档。完成归档过程的进程叫做日志归档进程。 在创建数据库的时候，DBA可以指定数据库运行在归档模式。 联机日志文件实际上由多个连接日志组构成，每组里有几个日志文件。如果当前联机日志文件写满，日志写进程（LGWR）会切换到下一组联机日志文件组，当下一组联机日志文件写满，LGWR又会切换，直到最后一组联机日志文件。当最后一组联机日志文件被写满后，LGWR将切换到第一组联机日志文件。所以，此时若数据库运行在归档模式，LGWR会等待归档进程（ARCn）把第一组联机日志文件复制到归档日志文件中，然后才切换到第一组联机日志文件。 8.2.3.5. 其它后台进程 Oracle数据库的其它后台进程还包括：CKPT检查点进程，SMON系统监控进程，PMON进程监控进程，RECO恢复进程，LCKn封锁进程，Dnnn调度进程，Snnn共享服务器进程等。 8.3. 存储结构 Oracle的存储结构分为物理存储结构和逻辑存储结构，物理存储结构主要由参数文件、数据文件、控制文件、联机日志文件等构成。逻辑存储结构主要由表空间、段、区、块等概念组成。 Oracle数据库的物理结构和逻辑结构的对应关系如下图所示： 8.3.1. 物理结构 8.3.1.1. 参数文件 Oracle数据库中有许多初始化参数，这些参数用于对整个数据库进行设置或调整，如设置数据库名，数据库块大小，控制文件的名称和位置，数据库实例数等信息，Oracle把这些参数保存于一个文件中，这个文件就叫做初始化参数文件。启动数据库时，Oracle会读取参数文件中的值，并根据这些值对数据库实例进行设置。详细的过程是Oracle先从初始化参数文件中获得控制文件的名称和位置，打开控制文件，然后再从控制文件中读取数据文件和联机日志文件的名称和位置，打开它们，从而打开了数据库。 8.3.1.2. 控制文件 数据库在启动时会访问控制文件，控制文件是在创建数据库的时候创建的，它是一个很小的二进制文件，记录了数据库的名称、创建数据库的时间，数据文件的名称和位置，联机日志文件的名称和位置及表空间等信息。 所以数据库的控制文件是相当重要的，一旦被损坏，数据库将不能正常启动和运行。因此DBA应镜像控制文件，并把每个镜像分布到不同的物理磁盘上，发生灾难时即使一个控制文件被损坏，也不会使整个数据库瘫痪。 8.3.1.3. 数据库文件 当创建数据库对象时，就创建了一个或多个数据文件，表和索引等对象就存放在物理文件中。一个表的数据可能会存储在多个数据文件中，但一个数据文件只能属于一个数据库，用户查询一个表时，若所需数据不在内存中，Oracle会读取该表所在的数据文件，并把数据调入到内存，访问表就是对数据文件的读写操作，创建数据文件时可以指定其初始大小，当其空间耗尽时，也可自动或手动扩展。 8.3.1.4. 联机日志文件 当用户在内存中对数据库进行了修改时（增删改），如果数据还未来及写入数据文件就发生中断，用户的修改就会丢失。为了防止这种情况发生，Oracle运行过程中会在内存中产生日志记录，并每隔一段时间把日志记录写到联机日志文件中。重启数据库时，Oracle会从联机日志文件中找回丢失的修改数据并重新把它写到数据文件中。有此可见，联机日志文件主要用于对数据库实例的恢复。当用户提交一个事务或每隔3秒或日志缓冲区写满1/3时，产生的日志信息就会被写到联机日志文件中。 8.3.1.5. 其它物理文件 Oracle数据的其它物理文件还包括归档文件、跟踪文件、口令文件、警告文件、备份文件等等。 8.3.2. 逻辑结构 Oracle的逻辑存储结构由数据块，区，段和表空间构成。 8.3.2.1. 表空间 一个数据库被划分成一个个新的逻辑单元，这些逻辑单元叫“表空间”。表空间用于在逻辑上存储数据库对象，如表、索引等。一个数据库逻辑上由一个或多个表空间组成。表空间又由一个或多个数据文件物理的支持着，表空间中的数据库对象的数据实际上是存储在数据文件中。表空间的大小是组成表空间的所有数据文件的大小之和。但表和数据文件却不是一一对应的，一个表的数据可以存放在一个或多个数据文件中。每个数据库用户都要有一个默认表空间，当用户创建对象时，如果没有指定这个对象放在哪个表空间中，则这个对象将被存放在默认表空间。 表空间按数据量大小可分为大文件表空间与小文件表空间。所谓大文件表空间（Bigfile Tablespace）是指一个表空间只包含一个大数据文件，该文件 的最大尺寸为128 TB（数据块大小为32 KB）或只32 TB（数据块大小为8 KB）。与大文件表空间相对应，系统默认创建的表空间称为小文件表空间（Smallfile Tablespace），如SYSTEM表空间、SYSAUX表空间等。小文件表空间可以包含多达1024个数据文件。小文件表空间的总容量与大文件表空间的容量基本相似。 表空间按性质可分为系统表空间和非系统表空间，例如SYSTEM表空间就是系统表空间，主要保存了数据库的数据字典；PL/SQL程序的源代码和解释代码，包括存储过程、函数、包、触发器等；数据库对象的定义，如表、视图、序列、同义词等。 SYSAUX表空间也是系统表空间，SYSAUX表空间是Oracle 10g新增的辅助系统表空间，主要用于存储数据库组件等信息，以减小SYSTEM表空间的负荷。在通常情况下，不允许删除、重命名及传输SYSAUX表空间。 非系统表空间主要包括：撤销表空间，临时表空间，和用户表空间，其中撤销表空间专门用于回滚信息的自动管理，由UNDO_TABLESPACE参数设置。临时表空间专门用于临时数据管理的表空间 在数据库实例运行过程中，执行排序等SQL语句时会产生大量的临时数据，这些临时数据将保存在数据库临时表空间中。用户表空间用于保存保存用户数据。 表空间的管理方式分为字典管理方式和本地管理方式。 字典管理方式是指表空间使用数据字典来管理存储空间的分配，当进行区的分配与回收时，Oracle将对数据字典中的相关基础表进行更新，同时会产生回滚信息和重做信息。字典管理方式将渐渐被淘汰。 在本地管理方式中，区的分配和管理信息都存储在表空间的数据文件中，而与数据字典无关。表空间在每个数据文件中维护一个“位图”结构，用于记录表空间中所有区的分配情况，因此区在分配与回收时，Oracle将对数据文件中的位图进行更新，不会产生回滚信息或重做信息。 8.3.2.2. 段 段是由一个或多个区组成的逻辑存储单元。表由段组成，一个表由一个或多个段组成，普通表由一个段组成，分区表由多个段组成。 创建表的时候实际上就创建了段，Oracle会给表的段分配一个初始区，随着数据的插入，初始区被填满，Oracle会再分配一个新区给表的数据段，这个区叫做“递增区”。如果递增区用完，Oracle还会分配另外的递增区。 段的类型包括：数据段，索引段，临时段，回滚段。 8.3.2.3. 区 区是数据库中的一个逻辑存储单元，由连续的数据块组成。区是一段连续的存储空间，当段中的空间耗尽时，Oracle会分配一个新区给这个段。 8.3.2.4. 数据块 数据块简称块，是Oracle存取数据的最小单位。Oracle数据存储在块中，一个块占用一定的磁盘空间。这里的块是Oracle的数据块，而不是操作系统的块。Oracle每次读写数据的时候都以块为单位，如果读写的数据不到一块，Oracle也会读写整个块。Oracle块的大小一般是操作系统块大小的整数倍。如2k，4k。块的大小有初始化参数文件中的DB_BLOCK_SIZE 参数指定。 9. 数据库基本管理 9.1. 参数文件管理 查看一个参数的值： SQL> show parameter parameter_name 检查数据库是否以spfile启动的： SQL> show parameter spfile 如果返回的value为空，说明是以pfile文件启动。否则value的值就是spfile文件。 Pfile文件与spfile文件互相生成： 以spfile启动的数据库，则可由SPFILE生成PFILE： SQL> create pfile =’path/’ from spfile; 不是以spfile启动的数据库，也可由SPFILE生成PFILE： SQL> create pfile =’path/’ from spfile=’spfile文件的路径及名称’; 如果不指定pfile文件的路径及名称，则它会替换现有$ORACLE_HOME/dbs/文件的内容。 从pfile文件生成spfile： SQL>create spfile from pfile=’path/’ 动态修改参数的值： SQL>alter system set 参数名=新值 scope=both/memory/spfile [sid=INSTANCE_NAME] 1．如果both为scope子句的默认值，表示同时修改内存和SPFILE文件中此参数的值 2．如果SCOPE为memory，则修改会马上生效，但下次启动数据库后就失效了 3．如果SCOPE为spfile，则只有重启数据库后修改才能生效 4．如果数据库是以pfile文件启动的，那么scope只能等于memory 5．在RAC中，如果仅想修改一个实例的值，那么需要加子句sid 9.2. 表空间管理 9.2.1. 用户表空间管理 9.2.1.1. 查看用户表空间 查询表空间的总大小： SQL> SELECT tablespace_name, SUM(bytes) / 1024 / 1024 / 1024 jg FROM dba_data_files GROUP BY tablespace_name 其中字段jg的单位为G 查询表空间当前可用大小： SQL> SELECT tablespace_name, SUM(bytes) / 1024 / 1024 / 1024 jg FROM dba_free_space GROUP BY tablespace_name 1.普通表空间中，如果数据库大小为8K，那么每个数据文件最大不能超过32G。 2.大表空间中数据文件大小几乎不受限制。建立大表空间的语法为create bigfile tablespace … 9.2.1.2. 设置用户表空间扩展方式 修改表空间下的文件为不可自动扩张： SQL> alter database datafile ‘文件路径/文件名.dbf’ autoextend off 修改表空间下的文件为不可自动扩张： SQL> alter database datafile ‘文件路径/文件名.dbf’ autoextend on 9.2.1.3. 设置用户表空间名称 可以在线修改表空间的名称： SQL> alter tablespace 表空间名称 rename to 新名称； 改名后，其他使用到该表空间的地方也自动被修改，无需用户再手工逐一修改。 9.2.1.4. 设置数据文件名称 将数据文件从一个地方挪到另外一个地方后，需要在数据库中修改一下数据文件的名称（含文件的绝对路径）。修改方法如下： SQL>shutdown immediate; SQL>startup mount; SQL> alter database rename file '/old path/' to '/new path/'; SQL> recover database; SQL> alter database open; 9.2.1.5. 删除用户表空间 可以使用如下命令删除表空间，其中如果没有including contents and datafile，则表空间的内容及 数据文件需要手工删除： SQL>alter database drop tablespace 表空间名 including contents and datafile; 不能删除数据库的默认表空间和默认临时表空间 不能删除SYSTEM表空间和SYSAUX表空间 9.2.1.6. 默认表空间管理 查看用户默认的表空间： SQL>Select DEFAULT_TABLESPACE from dba_users where username='ORACLE'; 新建用户时如不指定默认表空间，则使用默认表空间： SQL>create user 用户名 identified by 密码 [default tablespace 表空间名] 修改用户默认的表空间： SQL>alter user oracle default tablespace新表空间名; 9.2.1.7. 变更表空间状态 表空间状态有下面几种状态：online、offline、read only、read write。若要查看表空间的状态，可以通过下面SQL语句来查看。 SELECT TABLESPACE_NAME, CONTENTS, STATUS FROM DBA_TABLESPACES; 1.表空间脱机 ALTER TABLESPACE TBS_DM_DAT OFFLINE IMMEDIATE; 脱机（offline）一般用于数据库的联机备份，数据恢复等维护操作。有些表空间不能OFFLINE，如：SYSTEM,UNDO等 1） SYSTEM表空间不能offline，也不能read only 2） 当前的UNDO表空间，不能offline,也不能read only 3） 当前的临时表空间不能offline,也不能read only 4） SYSAUX可以offline 不能read only 2.表空间联机 SQL> ALTER TABLESPACE TBS_DM_DAT ONLINE; 3.表空间只读 SQL>ALTER TABLESPACE TBS_DM_DAT READY ONLY; 表空间只读时，其中的表，不能进行任何DML操作，否则会报错：ORA-00372: file xxx cannot be modified at this time ORA-01110: data file xx: ********。但是能删除表。 4.表空间读写 SQL>ALTER TABLESPACE TBS_DM_DAT READ WRITE; 5.表空间改名 在ORACLE 10g 之前，表空间的名称是不能被修改的。在ORACLE 11G中，通过ALTER TABLESPACE 语句中使用RENAME子句，数据库管理员可以修改表空间的名称。 9.2.2. 临时表空间管理 临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当Oracle需要用到排序的时候，并且当PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序。像数据库中一些操作： CREATE INDEX、 ANALYZE、SELECT DISTINCT、ORDER BY、GROUP BY、 UNION ALL、 INTERSECT、MINUS、SORT-MERGE JOINS、HASH JOIN等都可能会用到临 时表空间。当操作完成后，系统会自动清理临时表空间中的临时对象，自动释放临时段。这里的释放只是标记为空闲、可以重用，其实实质占用的磁盘空间并没有真正释放。这也是临时表空间有时会不断增大的原因。 临时表空间存储大规模排序操作(小规模排序操作会直接在内存里完成，大规模排序才需要磁盘排序Disk Sort)和散列操作的中间结果.它跟永久表空间不同的地方在于它由临时数据文件（temporary files）组成的,而不是永久数据文件（datafiles）。临时表空间不会存储永久类型的对象，所以它不会也不需要备份。另外，对临时数据文件的操作不产生redo日志，不过会生成undo日志。 创建临时表空间或临时表空间添加临时数据文件时，即使临时数据文件很大，添加过程也相当快。这是因为ORACLE的临时数据文件是一类特殊的数据文件:稀疏文件（Sparse File）,当临时表空间文件创建时，它只会写入文件头部和最后块信息(only writes to the header and last block of the file)。它的空间是延后分配的.这就是你创建临时表空间或给临时表空间添加数据文件飞快的原因。 另外，临时表空间是NOLOGGING模式以及它不保存永久类型对象，因此即使数据库损毁，做Recovery也不需要恢复Temporary Tablespace。 9.2.2.1. 查看临时表空间信息 1.查看临时表空间名称 SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; 2.查看临时表空间详细信息 SQL> SELECT FILE# AS FILE_NUMBER ,NAME AS NAME ,CREATION_TIME AS CREATION_TIME ,BLOCK_SIZE AS BLOCK_SIZE ,BYTES/1024/1024/1024 AS \"FILE_SIZE(G)\" ,CREATE_BYTES/1024/1024/1024 AS \"INIT_SIZE(G)\" ,STATUS AS STATUS ,ENABLED AS ENABLED FROM V$TEMPFILE; 3.查看临时表空间当前使用情况 SQL> SELECT BYTES,BLOCKS, USER_BYTES, USER_BLOCKS, BLOCKS -USER_BLOCKS AS SYSTEM_USED FROM DBA_TEMP_FILES; 9.2.2.2. 创建临时表空间 下面是一个简单的创建临时表空间的例子，具体很多细节可以参考官方文档，这里省略,不做过多介绍。 SQL> CREATE TEMPORARY TABLESPACE TMP TEMPFILE '/u01/gsp/oradata/' SIZE 8G AUTOEXTEND OFF; 9.2.2.3. 增加临时表空间数据文件 当临时表空间太小时，就需要扩展临时表空间（添加数据文件、增大数据文件、设置文件自动扩展）；有时候需要将临时数据文件分布到不同的磁盘分区中，提升IO性能，也需要通过删除、增加临时表空间数据文件。 SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u04/gsp/oradata/' SIZE 4G AUTOEXTEND ON NEXT 128M MAXSIZE 6G; 9.2.2.4. 删除临时表空间数据文件 SQL> ALTER TABLESPACE TEMP DROP TEMPFILE '/u01/app/oracle/oradata/GSP/'; 9.2.2.5. 调整临时表空间数据文件大小 如下例子，需要将临时数据文件从1G大小调整为2G。 SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/' RESIZE 2G; 9.2.2.6. 临时表空间文件脱机联机 SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/' OFFLINE; SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/' ONLINE; 默认临时表空间并不能脱机,否则会报错。 9.2.2.7. 设置文件自动扩展 SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED; 9.2.2.8. 移动重命名数据文件 例如，将/u01/app/oracle/oradata/GSP/这个文件重命名为/u01/app/oracle/oradata/GSP/ 1.将临时表空间的临时文件脱机 SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/' OFFLINE; 2.移动或重命名相关的临时文件 Mv /u01/app/oracle/oradata/GSP/ /u01/app/oracle/oradata/GSP/' 3.使用脚本ALTER DATABASE RENAME FILE SQL> ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/GSP/' TO '/u01/app/oracle/oradata/GSP/'; 4.将临时表空间的临时文件联机 SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/' ONLINE; 9.2.2.9. 删除临时表空间 SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS; 不能删除当前用户的默认表空间，否则会报ORA-12906错误。如果需要删除某一个默认的临时表空间，则必须先创建一个临时表空间，然后指定新创建的表空间为默认表空间，然后删除原来的临时表空间 9.2.3. 回滚段表空间管理 undo表空间是Oracle特有的概念。undo表空间中会自动分配undo段，这些undo段用来保存事务中的DML语句的undo信息，也就是来保存数据在被修改之前的值。在rollback，实例恢复(前滚)，一致性读并发数据块的构造时会使用到undo信息。由于undo的引入，从而Oracle的select语句实现一致性读时，不需要任何锁。 undo表空间和其它表空间有很多类似的地方：undo数据块也会被读到buffer cache缓存起来，修改时也会产生redo log，数据也会写回到undo表空间的磁盘上。所以崩溃后，undo块的buffer cache也会恢复过来。 9.2.3.1. 查看回滚段表空间 1.查看回滚段表空间名称 SQL> show parameter undo_tablespace; 2.查看回滚段表空间数据文件以及大小 SQL> select file_name,bytes/1024/1024 size_M from dba_data_files where tablespace_name='xxx'; 3.查看系统中现有的回滚段 SQL> select * from v$rollname; 在返回的结果中，其中name=SYSTEM的undo段位于SYSTEM表空间，当涉及到对SYSTEM表空间修改时，使用的是上面name=SYSTEM的表空间。比如DDL语句使用的就是SYSTEM这个undo段。另外当undo表空间UNDOTBS1损坏了时，也会使用SYSTEM这个undo段。随着系统的负载，undo段会根据需要自动增加。 4.根据回滚段的名称查看段的信息 SQL> select segment_name,blocks,extents from dba_segments where segment_name='_SYSSMU1$'; 从结果可见，段与段之间是不连续的，而段内部的block是连续的。 9.2.3.2. 创建回滚段表空间 如果使用的是UNDO管理模式，但是没有指定UNDO TABLESPACE，那么建立数据库的时候oracle会自动生成名称为SYS_UNDOTBS的UNDO表空间。也可以使用CREATE UNDO TABLESPACE 命令建立UNDO表空间，例如 建立数据库以后，可以使用该命令建立UNDO表空间。 SQL> CREATE DATABASE db01 UNDO TABLESPACE undotbs_01 DATAFILE '/opt/oracle/oradata/ge01/' SIZ E 30M; 9.2.3.3. 修改回滚段表空间 使用ALTER TABLESPACE命令修改UNDO表空间。当事务用尽了UNDO表空间后,使用ALTER TABLESPACE … ADD DATAFILE增加数据文件。当UNDO表空间所在的磁盘填满是,使用ALTER TABLESPACE … RENAME DATAFIEL命令移动数据文件到其他磁盘上。使用ALTER DATABASE … OFFLINE/ONLINE使表空间脱机/联机。 当数据库处于ARCHIVELOG模式时，使用ALTER TABLESPACE …BEGIN BACKUP/END BACKUP命令备份UNDO表空间。 9.2.3.4. 切换回滚段表空间 启动例程并打开数据库后,同一时刻特定例程只能使用一个UNDO表空间，切换UNDO表空间是指停止例程当前使用的UNDO表空间，并启动其他UNDO表空间，下面以启用undotbs2表空间为例，说明切换UNDO表空间的方法。 SQL> ALTER SYSTEM SET undo_tablespace=undotbs02; 在RAC(Real Application Cluster)集群环境中，不同数据库实例必须使用独立的UNDO表空间，而不能共用同一个UNDO表空间。 9.2.3.5. 删除回滚段表空间 当前数据库实例正在使用的UNDO表空间是不能被删除的，如果确定要删除当前例程正在使用的UNDO表空间，应首先切换UNDO表空间，然后删除相应的UNDO表空间。 SQL> DROP TABLESPACE undotbs3; 9.3. 重做日志文件管理 重做日志文件是Oracle数据库中一种非常重要的日志文件，重做日志文件会纪录对于数据库的任何操作，如利用DML语句或者DDL语句对数据进行更改，或者数据库管理员对数据库结构进行更改，都会在重做日志中进行记录。 可见，当数据被意外的删除或者修改，可以利用重新日志文件进行恢复; 当出现数据库实例失败或者介质失败的情况下，也可以利用日志文件实现例程恢复或者介质恢复。所以若能够管理好重做日志文件的话，对于保障数据库数据的安全是非常重要的。 每个节点有各自的redo log文件，他们都存放在同一个disk group中，修改REDO文件的大小：不能直接修改重做日志文件的大小，只能先增加新size的日志组，然后切换到新组上，再把旧size的日志组drop。 9.3.1. 增加重做日志文件组 SQL> alter database add logfile thread 1 group 11 '+DG_DBFILE/ora11g/' size 200M 这里，thread 1表示在RAC环境中，将为节点1增加日志组，如果是单实例数据库，则不需要该子句。另外，及时目前节点1已经down，也可以在节点2上执行上面的语句来为节点1增加日志组。该日志组中的日志文件大小为200M。 9.3.2. 添加重做日志文件组成员 添加重做日志文件组成员时不需要分配大小，新成员的大小由组中已有成员大小决定，sql语句如下： SQL> alter database add logfile member '/opt/oracle/oradata/ORCL/onlinelog/' to group 1; 9.3.3. 改变重做日志文件组成员 只能修改处于inactive或者unused状态的重做日志文件组的成员的名称或位置，数据库要启动在mount状态下 1）检查要修改的日志文件所在的日志文件组的状态 SQL> select group#,status from v$log; 如果不是处于inactive或者unused状态则需要进行手动切换 SQL> alter system switch logfile; 2）操作系统上重命名日志文件或改变位置 mv 3）执行下面语句（数据库不能处于open状态） SQL> alter database rename file '/opt/oracle/oradata/ORCL/onlinelog/' to '/opt/oracle/oradata/ORCL/onlinelog/'; 9.3.4. 限制： 删除重做日志文件组成员 组中的最后一个成员不能删除； 所在组处于current不能删除； SQL> alter database drop logfile member '/opt/oracle/oradata/ORCL/onlinelog/'; 这样删除并没有删除操作系统上的日子文件，只是更新了控制文件 9.3.5. 删除重做日志文件组 SQL> select group#,status from v$log; SQL> alter database drop logfile group 1; 删除重做日志文件组的限制：一个数据库至少需要两个重做日志文件正在处于归档模式下的不能删除，即处于active状态的只能删除处于inactive或者unused状态的日志文件组也是更新控制文件了，并没有真正的从操作系统上删除。 9.3.6. 重做日志切换 在RAC环境中，进入各个实例，执行如下语句： SQL> alter system switch logfile; 9.3.7. 重做日志清理 如果因redo日志损坏或不能归档导致数据库不能启动，可以先清理日志文件。查看每个日志组是否已经归档： SQL> select group#,status,SEQUENCE#,ARCHIVED from v$log; 清除未归档的日志组： SQL>alter database clear unarchived logfile group group_number; 清除已归档的日志组： SQL>alter database clear logfile group group_number; 清理日志文件并不会使日志中的内容丢失。 9.3.8. 重做日志切换次数查询 可以使用如下SQL语句来查询过去30天中每天每小时的日志切换次数： SQL> SELECT to_char(first_time, 'yyyy-mm-dd') DAY, COUNT(*) switch_times, SUM(decode(to_char(first_time, 'hh24'), '00', 1, 0)) h00, SUM(decode(to_char(first_time, 'hh24'), '01', 1, 0)) h01, SUM(decode(to_char(first_time, 'hh24'), '02', 1, 0)) h02, SUM(decode(to_char(first_time, 'hh24'), '03', 1, 0)) h03, SUM(decode(to_char(first_time, 'hh24'), '04', 1, 0)) h04, SUM(decode(to_char(first_time, 'hh24'), '05', 1, 0)) h05, SUM(decode(to_char(first_time, 'hh24'), '06', 1, 0)) h06, SUM(decode(to_char(first_time, 'hh24'), '07', 1, 0)) h07, SUM(decode(to_char(first_time, 'hh24'), '08', 1, 0)) h08, SUM(decode(to_char(first_time, 'hh24'), '09', 1, 0)) h09, SUM(decode(to_char(first_time, 'hh24'), '10', 1, 0)) h10, SUM(decode(to_char(first_time, 'hh24'), '11', 1, 0)) h11, SUM(decode(to_char(first_time, 'hh24'), '12', 1, 0)) h12, SUM(decode(to_char(first_time, 'hh24'), '13', 1, 0)) h13, SUM(decode(to_char(first_time, 'hh24'), '14', 1, 0)) h14, SUM(decode(to_char(first_time, 'hh24'), '15', 1, 0)) h15, SUM(decode(to_char(first_time, 'hh24'), '16', 1, 0)) h16, SUM(decode(to_char(first_time, 'hh24'), '17', 1, 0)) h17, SUM(decode(to_char(first_time, 'hh24'), '18', 1, 0)) h18, SUM(decode(to_char(first_time, 'hh24'), '19', 1, 0)) h19, SUM(decode(to_char(first_time, 'hh24'), '20', 1, 0)) h20, SUM(decode(to_char(first_time, 'hh24'), '21', 1, 0)) h21, SUM(decode(to_char(first_time, 'hh24'), '22', 1, 0)) h22, SUM(decode(to_char(first_time, 'hh24'), '23', 1, 0)) h23 FROM v$log_history WHERE first_time > trunc(SYSDATE - 30) GROUP BY ROLLUP(to_char(first_time, 'yyyy-mm-dd')); 其中h**表示小时。 9.4. 归档模式设置 9.4.1. 单实例数据库设置归档模式 1.正常关闭数据库： SQL>shutdown immediate; 注意，这里不能使用abort或断电方式关闭数据库。 2.启动到MOUNT状态： SQL>startup mount; 3.设置归档参数： SQL> alter system set db_recovery_file_dest=''; SQL> alter system set log_archive_dest_1=’LOCATION=path’; 4.修改为归档模式： SQL> alter database archivelog; 5.启动数据库： SQL> alter database open; 9.4.2. RAC数据库设置归档模式 1.在两个节点上，分别正常关闭数据库： SQL>shutdown immediate; 注意，这里不能使用abort或断电方式关闭数据库。 2.启动节点1上的数据库到MOUNT状态： SQL>startup mount; 3.修改为非RAC模式： SQL> alter system set cluster_database=false scope=spfile; 4.关闭并重新启动节点1上的数据库到MOUNT状态 5.设置归档参数： SQL> alter system set db_recovery_file_dest='path'; SQL> alter system set db_recovery_file_dest_size=2G; 如果参数log_archive_dest_1有设置，则清空。 6.修改为归档模式： SQL> alter database archivelog; 7.修改为RAC模式： SQL> alter system set cluster_database=true scope=spfile; 8.关闭节点1上的数据库。然后重新启动两个节点上的数据库。 9.4.3. 归档路径设置 可以使用flash recory area来作为归档目标路径，需要同时设置参数db_recovery_file_dest和db_recovery_file_dest_size两个参数。如果归档文件的总大小超过了db_recovery_file_dest_size的值，将发生因归档失败而无法启动数据库的现象。该参数默认值为2G。 也可以使用log_archive_dest_n 来执行归档目标路径。默认地，log_archive_dest_1如果为空，则表示归档时会在log_archive_dest_1默认 的$ORACLE_HOME/dbs目录下生成一份归档文件。这会导致$ORACLE_HOME目录爆涨而发生严重的空间问题。 参数log_archive_dest与log_archive_dest_n不能同时使用，参数log_archive_dest_1的值如果没有设，则默认为$ORACLE_HOME/dbs目录。在11g中，参数log_archive_dest就不要再使用了! 设置log_archive_dest_1的语法如下，这里引号中的LOCATION=是必不可少的： SQL> alter system set log_archive_dest_1='LOCATION=dest_path '; 1）如果db_recovery_file_dest与log_archive_dest_1设置了不同的值，那么归档文件将在两个路径下同时生成（文件内容一致，文件名称可能不同） 2）即使log_archive_dest_1为空，与将它设置为$ORACLE_HOME/dbs目录的实际效果是一样的 9.5. 控制文件重建 当数据库因重做日志文件或者数据文件丢失或不一致，或者要修改数据库名称时，一般就可通过重建控制文件来实现。 1.备份控制文件成脚本文件 SQL> alter database backup controlfile to trace; 注意：数据库必须为MOUNT或OPEN状态。 2.找到刚生成的脚本文件 在目录$ORACLE_BASE/oradb/diag/rdbms/db_name/instance_name/trace中找到最新生成的trc文件，该文件中应包含CREATE CONTROLFILE语句。 3.重整建控制文件的SQL语句 从该文件中取出最核心的内容，例如： CREATE CONTROLFILE REUSE DATABASE \"INOMC\" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/dev/vx/dsk/i2kdgtest/redo1_1_vol' SIZE 50M, GROUP 2 '/dev/vx/dsk/i2kdgtest/redo2_1_vol' SIZE 50M, GROUP 3 '/dev/vx/dsk/i2kdgtest/redo3_1_vol' SIZE 50M, GROUP 4 '/dev/vx/dsk/i2kdgtest/redo4_1_vol' SIZE 50M, GROUP 12 '/dev/vx/dsk/i2kdgtest/redo12_1_vol' SIZE 100M -- STANDBY LOGFILE DATAFILE '/dev/vx/dsk/i2kdgtest/system_vol', '/dev/vx/dsk/i2kdgtest/sysaux_vol', '/dev/vx/dsk/i2kdgtest/undotbs1_vol', '/dev/vx/dsk/i2kdgtest/users_vol' CHARACTER SET ZHS16GBK 然后修改为： CREATE CONTROLFILE set DATABASE 'INOMC' RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/dev/vx/dsk/i2kdgtest/redo1_1_vol' SIZE 50M, GROUP 2 '/dev/vx/dsk/i2kdgtest/redo2_1_vol' SIZE 50M, GROUP 3 '/dev/vx/dsk/i2kdgtest/redo3_1_vol' SIZE 50M, GROUP 4 '/dev/vx/dsk/i2kdgtest/redo4_1_vol' SIZE 50M DATAFILE '/dev/vx/dsk/i2kdgtest/system_vol', '/dev/vx/dsk/i2kdgtest/sysaux_vol', '/dev/vx/dsk/i2kdgtest/undotbs1_vol', '/dev/vx/dsk/i2kdgtest/users_vol' CHARACTER SET ZHS16GBK; 这个例子中，是要去掉group为12的日志文件，如果已经有控制文件存在，则仍使用reuse，如果控制文件没有了，那么使用set。 4.将数据库启动到nomount状态 SQL>shutdown immediate; SQL>startup nomount; 5.执行修改后的CREATE CONTROLFILE语句 6.打开数据库 SQL> ALTER DATABASE OPEN RESETLOGS; 9.6. 内存参数管理 9.6.1. Oracle内存管理发展阶段 9i：通过PGA_AGGREGATE_TARGET参数实现PGA自动管理。 10g：通过SGA_TARGET参数实现了SGA的自动管理。 11g：通过MEMORY_TARGET参数实现了所有内存块的自动管理。 可以通过视图V$MEMORY_DYNAMIC_COMPONENTS和V$MEMORY_RESIZE_OPS来查询各个内存部分的大小。 下面分别介绍这三个管理方式的设置方法。但在11g中，建议使用AMM。 9.6.2. 自动内存管理AMM AMM是Automatic Memory Management的缩写，表示自动内存管理。有两个参数memory_max_target和memory_target，参数memory_max_target表示可用的最大内存值，memory_target表示在memory_max_target的范围内，有多少内存是可用于动态分配的，它应小于等于memory_max_target的值。 设置方法如下： SQL> alter system set memory_max_target=3200M scope=spfile; SQL> alter system set memory_target=3200M scope=spfile; SQL> shutdown immediate; SQL> startup; 其他内存参数如PGA_AGGREGATE_TARGET、SGA_MAX_SIZE等，在自动内存管理的情况下是不需要设置的，等于0即可，Oracle在运行期间会根据每个部分的实际需求分配相应大小的内存。但是如果设置了这些参数为非0值，那么这个值将是该参数的最小值，实际运行中的值应是大于等于它的。如果初始化参数 LOCK_SGA ＝ true ，则 AMM 是不可用的。 9.6.3. 自动共享内存管理ASMM ASMM是Automatic Shared Memory Management的缩写，表示共享内存（SGA）的自动管理。要使用这种方式，需要设置初始化参数 MEMORY_TARGET=0 ，然后显式的指定 SGA_TARGET 的值。 SQL> alter system set memory_target=0 scope=both; SQL> alter system set sga_target=1024m scope=both; 这两个参数的修改是有严格顺序的，如果不遵守倒也没问题--Oracle 会报告错误。 9.6.4. 自动PGA管理 如果使用AMM，则对PGA不用操心。如果要做到精细控制而切换到自动PGA内存管理模式，需要设定WORKAREA_SIZE_POLICY＝AUTO(默认为AUTO)，然后需要指定PGA_AGGREGATE_TARGET 的值。如需要精确控制PGA，则 WORKAREA_SIZE_POLICY＝MANUAL，然后对PGA的各个部分的大小进行设置。 9.7. 其它管理内容 9.7.1. 查看数据库版本 两种方法可以查看数据库的版本信息。一是执行sqlplus / as sysdba，在输出内容中有版本及包含的关键特征： oracle@linux2:~> sqlplus / as sysdba Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options 如果是标准版，那么输出的结果如下： oracle@linux1:~> sqlplus / as sysdba Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: 是11g的标准版，64位的 方法二是查看v$version视图。或者查看PRODUCT_COMPONENT_VERSION。 9.7.2. 数据库字符集 数据库服务器端字符集可通过视图V$NLS_PARAMETERS中的 NLS_CHARACTERSET的值查看。本地设置环境变量NLS_LANG（我的电脑，右键，属性，高级，环境变量），如设置为SIMPLIFIED 。数据库的字符集在安装时指定后，不可修改。如一定要修改字符集，那么建议重新建库。 9.7.3. 创建密码文件 以oracle用户执行以下命令，建立密码文件，如果此文件已有，则需要先删除后建立： # orapwd file=$ORACLE_HOME/dbs/orapwORACLE_SID password=mima entries=6 9.7.4. 关闭数据库审计功能 数据库安装完成后，默认为数据库级的审计，即AUDIT_TRAIL的值为DB。这样每次有用户登录到数据库时都会在系统表空间中记录一行，并且在/opt/app/admin/ora11g/adump目录下生成一个.aud文件。一段时间后，系统表空间不断增大，磁盘文件也不断增大。应该定期清理。因为此审计功能并无太多意义，建议关闭。命令如下： SQL> alter system set audit_trail='NONE' scope=spfile; 清理打开审计时遗留的一些信息： SQL> truncate table $; 9.7.5. 账号管理 1.创建账号 SQL> create user USERNAME identified by PASSWORD default tablespace=TBS; 2.删除账号 1）删除账号但不删除账号下的数据 SQL> drop user USERNAME; 2）删除账号并删除数据 SQL> drop user USERNAME cascade; 3)修改密码 SQL> alert user USERNAME identified by PASSWORD; 9.7.6. Profile管理 修改profile中的设置： SQL> alter profile DEFAULT limit idle_time 60; 创建profile的样例： SQL> create profile TEST_PROFILE limit FAILED_LOGIN_ATTEMPTS 3; 10. 数据库集群（RAC）管理 10.1. RAC状态检查 10.1.1. 检查守护进程 守护进程是由/etc/inittab文件中如下三行内容拉起并管理： h1:35:respawn:/etc/ run >/dev/null 2>&1 /dev/null 2>&1 /dev/null 2>&1 如果这三行内容错误或没有，则守护进程可能会受到影响。 三个守护进程如下： /bin/sh /etc/ fatal /bin/sh /etc/ run /bin/sh /etc/ run 通过ps –ef |grep 可以检查他们是否存在。 10.1.2. 检查资源状态 检查RAC所有资源的状态： # $ORA_CRS_HOME/bin/crs_stat –t 检查CRS的状态： # $ORA_CRS_HOME/bin/crsctl check crs 单独检查CSS的状态： # $ORA_CRS_HOME/bin/crsctl check css 10.2. RAC启动与关闭 使用 start启动RAC，使用 stop关闭RAC。该命令可以将整个RAC的所有资源（不包括三个守护进程）启动或停止。 不同操作系统上，文件的位置不同： SUSE： /etc/ HP: /sbin/ AIX: /etc/ SUN: /etc/ 不建议使用crsctl start/stop来启动或关闭RAC，因为这个命令并不校验各资源间的依赖关系，不是很可靠。 10.3. 管理OCR 10.3.1. OCR盘状态查看 命令ocrcheck检查OCR存储的情况，执行结果类似于： midware01% ocrcheck Status of Oracle Cluster Registry is as follows : Version : 2 Total space (kbytes) : 513652 Used space (kbytes) : 2792 Available space (kbytes) : 510860 Device/File Name : /dev/rdsk/c3t12d0s4 Device/File integrity check succeeded Device/File not configured Cluster registry integrity check succeeded 10.3.2. OCR备份与恢复 默认地，OCR会被自动备份，使用命令ocrconfig -showbackup查看OCR进行的自动备份: linux1:~ # /oracle/crs/bin/ocrconfig -showbackup linux2 2008/12/05 07:06:09 /oracle/crs/cdata/RAC/ linux2 2008/12/05 03:06:09 /oracle/crs/cdata/RAC/ linux2 2008/12/04 23:06:09 /oracle/crs/cdata/RAC/ linux2 2008/12/04 07:06:08 /oracle/crs/cdata/RAC/ linux2 2008/11/19 19:06:08 /oracle/crs/cdata/RAC/ 如果要手工备份，可执行下面的命令： # $ORA_CRS_HOME/bin/ocrconfig –export /oracle/db/ 恢复OCR的方法如下： 1）先关闭两个节点上的RAC，分别执行以下命令： # /etc/ stop 2）然后执行恢复： 如果是一个空的OCR盘则用如下命令导入： # $ORA_CRS_HOME/bin/ocrconfig –import /oracle/db/ 如果OCR盘已经有信息了，那么应使用如下命令做恢复： # $ORA_CRS_HOME/bin/ocrconfig –restore /oracle/db/ 10.3.3. 查看OCR内容 将OCR的内容用ocrdump导出后，可以用vi打开来查看： # ocrdump /home/oracle/ 10.3.4. 指定OCR盘 OCR使用哪个磁盘或文件是在/etc/oracle/文件中指定的。此文件包含两行内容，例如： ocrconfig_loc=/oracle/app/product/11g/db/cdata/localhost/ local_only=TRUE 这里，ocrconfig_loc指定的是OCR所用的磁盘名称或者文件名。如果是RAC环境，则这里应为磁盘名，如果是单实例的环境，则这里为一个绝对路径及文件名。local_only表示CRS的二进制文件是安装在本地还是多个节点共享的存储上，单实例环境中该参数为TRUE，RAC中二进制文件装在各自本地盘上则为FALSE。 10.3.5. 增加OCR盘 一个RAC环境中，可以有两个OCR，一个为OCR，另外一个为OCRMIRROR。 可以增加OCRMIRROR盘，命令如下： # ocrconfig -replace ocrmirror /dev/raw/raw3 增加以后，执行ocrcheck将看到两个OCR盘了： # /oracle/crs/bin/ocrcheck Status of Oracle Cluster Registry is as follows : Version : 2 Total space (kbytes) : 296940 Used space (kbytes) : 3948 Available space (kbytes) : 292992 Device/File Name : /dev/raw/raw1 Device/File integrity check succeeded Device/File Name : /dev/raw/raw3 Device/File integrity check succeeded Cluster registry integrity check succeeded Logical corruption check succeeded 10.3.6. 删除OCR盘 以下命令将删除OCR盘，原来的OCRMIRROR变成OCR： # ocrconfig -replace ocr 删除OCRMIRROR： # ocrconfig -replace ocrmirror 10.4. 管理VOTING DISK 10.4.1. 检查Voting Disl盘 # $ORA_CRS_HOME/bin/crsctl query css votedisk 10.4.2. VOTING DISK备份与恢复 备份命令如下： # dd if=/dev/raw/raw2 of=/oracle/db/ 备份时间会比较长，备份文件与VOTING DISK盘的大小接近。 恢复命令如下： # dd if=/oracle/db/ of=/dev/raw/raw2 恢复前不需要先关闭RAC。 10.4.3. 添加VOTING DISK盘 可以添加一个盘为voting disk，命令如下： # crsctl add css votedisk /dev/raw/raw3 10.4.4. 删除VOTING DISK盘 # crsctl delete css votedisk /dev/raw/raw3 -force 10.5. CSS管理 CSS的作用是协调ASM实例与数据库实例之间的通信。因此要使用ASM管理存储，无论是单实例还是RAC环境，都必须先配置CSS。 查看CSS进程是否正常，以oracle用户执行以下命令： # crsctl check cssd 如果CSS正常，那么显示如下信息： Cluster Synchronization Services appears healthy 如果显示CSS未启动，则执行如下命令： # /etc/ start 10.6. SRVCTL管理工具 在RAC中，可以使用srvctl（service control tool）工具来管理CRS的各类资源。SRVCTL的有些命令会修改OCR的信息，例如添加service。有些命令则是通过发送指令给CRSD（oracle clusterware process），然后由CRSD去执行，例如启动数据库。 可以使用-h查看命令的帮助信息，例如，要查看srvctl add asm的用法，可以执行如下命令： # srvctl add asm –h 10.6.1. 管理实例 启动数据库实例： # srvctl start instance -d db_name -i instance_name -o open; 检查数据库实例的状态： # srvctl status database -d db_name 10.6.2. 管理监听程序 1.添加监听程序： #srvctl add listener –n hostname -o $ORACLE_HOME 2.删除监听程序： #srvctl remove listener –n hostname 如果要指定删除一个资源，例如一个节点上有两个lsnr资源，一个为LISTENER3_HOST1，另外一个为LISTENER_HOST1，现在需要将不正确的LISTENER3_HOST1删除： # srvctl remove listener -n hostname -l LISTENER3_HOST1 3.启动监听程序： #srvctl start listener –n hostname 4.停止监听程序： #srvctl stop listener –n hostname 5.检查监听程序是否存在 #srvctl config listener –n hostname 10.6.3. 管理ASM 1.添加ASM实例： #srvctl add asm –n hostname –i ASM_ORACLE_SID -o $ORACLE_HOME 2.删除ASM实例： #srvctl add asm –n hostname –f [-i ASM_ORACLE_SID] 其中-f表示如果删除失败，则强制删除 3.启动ASM实例： #srvctl start asm –n hostname [-i ASM_ORACLE_SID] 4.停止ASM实例： # srvctl stop asm –n hostname [-i ASM_ORACLE_SID] 5.检查ASM实例是否存在 # srvctl config asm –n hostname 10.6.4. 管理service 1.增加service： # srvctl add service -d db_name -s service_name -r 主节点的ORACLE_SID -a 备节点A的ORACLE_SID,备节点B的ORACLE_SID 2.删除service： # srvctl remove service -d db_name -s service_name 3.启动service： # srvctl start service -d db_name -s service_name 4.停止service： # srvctl stop service -d db_name -s service_name 10.7. 修改RAC的IP及VIP 10.7.1. 修改外网IP和心跳IP 当外网IP修改并重新绑定到网卡上以后，需要修改RAC中记录的外网IP。修改方法如下： 1）关闭两个节点上的RAC； 2）修改/etc/hosts文件，用新的IP替换旧IP值 3）启动RAC，在两个节点上分别执行： # /etc/ start 4）假设要修改节点1的外网IP： # cd $ORA_CRS_HOM/bin 5）然后删除旧的外网IP信息： 6）修改 $ORACLE_HOME/network/admin/ 文件中HOST等于的外网IP值 7）假设 # cd $ORA_CRS_HOM/bin 可以查看现在RAC中IP的值： # ./oifcfg getif –global 8）最后在两个节点上关闭并重启RAC。 10.7.2. 修改VIP 查看当前的VIP设置： # /oracle/crs/bin/srvctl config nodeapps -a -n linux1 RAC安装完以后，可以修改两个节点的VIP。前提是修改后的VIP必须没有被其他系统使用掉。正确的修改方法如下： 1）使用srvctl修改VIP 进入/crs/bin目录下执行如下命令： 如果两个节点的VIP都需要修改，则两个节点上都需要执行该步骤 2）停止CRS的所有资源 # /etc/ stop 3）卸掉旧的VIP网卡 假定旧的VIP是绑定在eth3:1上，那么执行命令： # ifconfig eth3:1 down 两个节点上都需要执行该步骤 4）修改/etc/host文件 修改该文件，将旧的VIP改为新的VIP值. 两个节点上都需要执行该步骤 5）启动CRS资源 在任意一个节点上执行以下命令： # /etc/ start 6）修改客户端或应用程序连接服务器所使用的VIP值 10.7.3. 查看与删除IP 1.查看与管理OCR中IP的配置信息： 查看IP配置信息： # oifcfg getif 返回结果例如： linux1:~ # /oracle/crs/bin/oifcfg getif # oifcfg iflist 返回结果例如： linux1:~ # /oracle/crs/bin/oifcfg iflist 即使两个节点的VIP都漂到一个节点上，那么此命令的执行结果也仍只有两行。 2.删除网卡信息： # oifcfg delif -global ce0 这里，-global表示删除RAC中所有节点上的网卡ce0。如果只想删除一个节点的，则用- n nodename 10.8. 管理ASM 10.8.1. 管理DiskGroup 10.8.1.1. 建立与扩充DiskGroup 1.建立外部冗余的DG： SQL>create diskgroup dg_name external redundancy disk ‘/dev/raw/raw5’; 冗余度：normal(2-way镜像),high(3-way镜像),external(不做镜像) 建立normal冗余的DG： SQL> CREATE DISKGROUP dg_name NORMAL REDUNDANCY FAILGROUP FGROUP1 DISK 'D:MYRAWSRAW1' NAME DISKn, ' ... other disk ... ' FAILGROUP FGROUP2 DISK 'D:MYRAWSRAW3' NAME DISKn, ' ... other disk ... ' ; 2.向一个disk group中增加一个裸盘 SQL>alter diskgroup dg_name add disk '/dev/raw/raw6'; 3.从disk group中去掉disk： SQL> alter diskgroup dg_name drop disk disk在diskgroup中的名称 （可从v$asm_disk中查询获得，也可在添加disk到group时加name disk名称来指定） 10.8.1.2. 挂载： 挂载和解除挂载disk group SQL>alter diskgroup dg_name dismount; 解除挂载： SQL>alter diskgroup dg_name mount; 10.8.1.3. 删除DiskGroup 要在节点1上执行DG的删除动作，则节点1上DG应为MOUNT，节点2上此DG的状态应为DISMOUNT。如果不是，则要先调整。 删除DG的命令如下： SQL>DROP DISKGROUP dg_name (including contents); 如果DG为DISMOUNT状态，则可以强制删除： SQL> drop diskgroup dg_name force including contents; 10.8.1.4. 扩充Disk 当包含在DG中的RAW被扩大后，应在ASM中执行以下命令来扩张ASM可见此RAW的total_mb： SQL> alter diskgroup dg_name resize all size 19085M; 如果DG中有多个RAW，每个RAW的大小不同，则这里应指定具体的DISK名称： SQL> alter diskgroup dg_name resize disk disk_name size 19085M 10.8.1.5. 设置DiskGroup的AU属性 在11G中，默认每个DG的AU大小为1M，可以为每个DG设置不同的AU大小。在建立DG时加上如下子句即可：… attribute 'au_size' = '2M' AU_SIZE 的值应为 1M、2M、4M、8M、16M、32M 或 64M。 可以修改已有DG的AU值： SQL> alter diskgroup dg_name set attribute 'au_size'='2M'; 10.8.1.6. 设置DiskGroup的离线删除时间 ASM会删除离线时间超过小时的磁盘，可以通过修改DISK_REPAIR_TIME(单位可以是分钟，M或m，或小时，H或h)参数设置磁盘组默认时间限制。 以小时为单位进行设置： SQL> ALTER DISKGROUP dg_name SET ATTRIBUTE 'disk_repair_time' = ''; 以分钟为单位进行设置 SQL> ALTER DISKGROUP dg_name SET ATTRIBUTE 'disk_repair_time' = '300m'; ALTER DISKGROUP命令的DROP AFTER子句用于废除DISK_REPAIR_TIME参数设置的默认时间。 使用默认的DISK_REPAIR_TIME： SQL> ALTER DISKGROUP dg_name OFFLINE DISK D1_0001; 废除默认的DISK_REPAIR_TIME： SQL> ALTER DISKGROUP dg_name OFFLINE DISK D1_0001 DROP AFTER 30m; 如果磁盘在滚动升级期间离线，直到滚动升级完成，计时器才会启动。 10.8.2. ASM兼容性管理 ASM 是一个适用于从 10g 到当前版本的 Oracle 数据库的存储平台。因此，11g 上的 ASM 实例可以保存 10g 第 1 版、10g 第 2 版以及 11g 第 1 版（以及更高版本）的数据库。只要 ASM 版本与 RDBMS 的版本相同或者更高，就可以在该 ASM 实例上创建数据库。如果 ASM 和 RDBMS 实例的版本不同，则需要将ASM的版本转换成适合RDBMS的版本。 默认情况下，ASM 实例可以支持10g数据库。在Oracle数据库11g中，可以使用ASM兼容性和RDBMS兼容性磁盘组属性实现。 首先，检查磁盘组的当前属性： SQL> select name, compatibility, database_compatibility from v$asm_diskgroup; 返回结果例如： NAME COMPATIBILITY DATABASE_COMPATIBILI -------------------- -------------------- -------------------- 由于数据库为11g，并只希望创建11g ASM 和RDBMS结构，因此无需拥有10g元素。要将该磁盘组的 ASM 兼容性属性设置为 ，可以执行以下语句（在ASM实例中）： SQL> alter diskgroup dg_name set attribute ''=''; 现在ASM 兼容性设置为 ；但 RDBMS 兼容性仍然设置为 .要将它也更改为 ，命令如下： SQL> alter diskgroup dg_name set attribute ''=''; 兼容性是针对磁盘组设置的，而不是针对整个 ASM 实例。只需使用一个ASM 实例即可满足所有数据库版本类型的需要。根据所使用的版本，可以相应地设置属性，从而减少版本间的通信。 10.8.3. ASM中添加disk的方法 1.划好磁盘分区，例如sdd6 2.查看现有的RAW： # raw –qa 确定下一个RAW的编码，例如此时最大的RAW编码为RAW11，那么下面将使用RAW12 3.将RAW12添加到文件/etc/raw中，使它永久生效： 添加的内容请参考该文件中的其他行 4.修改RAW12的权限 # chown oracle:dba /dev/raw/raw12 5.将sdd6绑定为裸设备，如raw12： # raw /dev/raw/raw12 /dev/sdd6 6.将raw12添加到要扩充的dg中，例如dg_dbfile中： 确定diskstring当前的值，以oracle用户登录节点1： #export ORACLE_SID=+ASM1 #sqlplus / as sysdba SQL>show parameter asm_diskstring; 例如该参数的值为/dev/raw/raw1,/dev/raw/raw2 登录节点1，然后执行如下步骤： #export ORACLE_SID=+ASM1 #sqlplus / as sysdba 修改参数asm_diskstring： SQL>alter system set asm_diskstring='/dev/raw/raw1’,’/dev/raw/raw2’,’/dev/raw/raw12'; 登录节点2，然后执行如下步骤： #export ORACLE_SID=+ASM2 #sqlplus / as sysdba 修改参数asm_diskstring： SQL> alter system set asm_diskstring='/dev/raw/raw1’,’/dev/raw/raw2’,’/dev/raw/raw12'; 增加raw12到dg中： SQL>alter diskgroup dg_dbfile add disk '/dev/raw/raw12';查看该dg的状态： SQL>select name,state from v$asm_diskgroup where name=’DG_DBFILE’; 如果状态为DISMOUNT，则执行下面的命令将它MOUNT起来： SQL>alter diskgroup dg_dbfile mount; 回到节点1，执行如下步骤： 查看该dg的状态： SQL>select name,state from v$asm_diskgroup where name=’DG_DBFILE’; 如果状态为DISMOUNT，则执行下面的命令将它MOUNT起来： SQL>alter diskgroup dg_dbfile mount; 7.修改init文件 在节点1和节点2上，分别修改文件$ORACLE_BASE/admin/+ASM/pfile/ ，将原来设置参数asm_diskstring的行注销掉，然后添加一行，内容如下： asm_diskstring=/dev/raw/raw1,/dev/raw/raw2,/dev/raw/raw12 10.8.4. ASMCMD管理命令介绍 10.8.4.1. ASMCMD常用命令 在节点1上，以oracle用户登录，然后设置为+ASM1实例: # su – oracle # export $ORACLE_SID=+ASM1 # asmcmd asmcmd命令清单如下： 命令 ls ll cd mkdir 用途 说明 显示现有的diskgroup名称 显示所有可用的命令 Cd +diskgroup名 作用等同于进入普通目录 完全和普通目录的管理方法一致 ... rm pwd cp 删除文件或目录 查看当前目录 拷贝文件 可在两个DG间拷贝，也可从DG拷贝到文件系统， 但不能从文件系统拷贝到DG 10.8.4.2. 复制ASM文件 拷贝ASM文件到文件系统下： ASMCMD>cd +DG_1 ASMCMD>cp /opt/oracle/ 如果不指定文件系统的具体目录，那么默认为$ORACLE_HOME/dbs 拷贝ASM文件到ASM的DG上： ASMCMD>cd +DG_1 ASMCMD>cp +DG_1/ 这样就将DG_1下的做了个复制文件，并存放在DG_1中cp命令不能将文件系统中的文件拷贝到DG上。 10.8.4.3. lsdg命令 命令lsdg可以查看DG的各个属性，返回结果例如： lASMCMD>lsdg State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name MOUNTED EXTERN N 512 4096 1048576 19085 18990 0 18990 0 DGB/ MOUNTED EXTERN N 512 4096 1048576 28615 28498 0 28498 0 DG_ARCH/ MOUNTED EXTERN N 512 4096 1048576 19085 3936 0 3936 0 DG_DBFILE/ MOUNTED EXTERN N 512 4096 1048576 28615 13159 0 13159 0 DG_INDEX/ ASMCMD> 10.8.4.4. 元数据备份与恢复 可以在asmcmd中对ASM的DG元数据进行备份与恢复。可以将备份的DG恢复其元数据，也可恢复成一个新名称的DG。 备份元数据： ASMCMD> md_backup -b /oracle/db/dg2_bk -g dg2 其中，-b表示备份文件的路标路径，如果不指定，则会备份到当前目录。-g表示要备份的DG名，如有多个，则用多个-g来开始，例如：-g dg1 –g dg2，如果不带此参数，则默认备份所有的DG。 恢复元数据： 恢复dg_data的元数据，并生成新名称dg2： ASMCMD> md_restore -t newdg -o 'dg_data:dg2' -b /oracle/db/dg_data_bk 其中,-b表示使用哪个备份文件来做恢复。-g表示要恢复的DG名称。-t后面有三个选项，一是full，表示创建DG并恢复其元数据，一是nodg，表示仅仅恢复元数据，另外一个是newdg，表示建立一个新名称的dg并恢复其元数据。如果是newdg，那么后面必须跟上-o，来指定旧名称和新名称。也可以在恢复的时候执行一个脚本，如下： ASMCMD> md_restore –t newdg –of –i backup_file 特别说明： 上存放的数据库中建立的对象，如数据文件，以及由此生成的目录，都不属于元数据 2.进入ASMCMD后mkdir建立的目录等属于元数据，可以被备份出来 10.8.4.5. 使用dd备份头信息： ASM磁盘头信息备份与恢复 # dd if=/dev/raw/raw201 bs=4096 count=1 of=/oracle/db/raw201bak 这里，只需要备份第一个块的内容即可，即count=1。参数of出去的文件可以放在本地盘上。这个备份文件只能使用dd命令来查看其内容。 使用dd做头信息恢复： 如果头信息被破坏，ASM将看不到此盘，即在v$asm_disk中看不到此raw文件。只要用之前备份的头信息文件恢复一下即可。 # dd if=/oracle/db/raw204bak of=/dev/raw/raw204 bs=4096 count=1 使用kfed备份头信息： # kfed read /dev/raw/raw204 aunum=0 >/oracle/db/raw204au0 这样备份出来的文件可以使用vi或more命令查看其内容。 使用kfed恢复头信息： # kfed merge /dev/raw/raw208 text=/wch/raw208au 这里文件/wch/raw208au是可以用vi编辑的。 10.8.5. ASM常用视图 10.8.5.1. 字段名称 GROUP_NUMBER NAME SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE V$ASM_DISKGROUP视图 字段含义 DG组编号，主键 DG名称 Physical block size (in bytes)。 Automatic Storage Management metadata block size (in bytes)。ASM固定它为4096 Size of the allocation unit (in bytes)。即AU的大小，一般为1M。建立DG时可以指定 MOUNTED：可以被使用，但此时未被数据库实例连接使用 CONNECTED：DG正在被数据库使用 DISMOUNTED：DG被干净地关闭了 BROKEN：ASM已MOUNT了DG，但数据库实例看不到它 UNKNOWN：ASM都没有试图去MOUNT过它 冗余方式，EXTERN、NORMAL、HIGH 总容量 TYPE TOTAL_MB FREE_MB 未被使用的容量 REQUIRED_MIRROR_FREE_MB 如果做了冗余，则需要保留一些空间以便失败时保留当时的信息 USABLE_FILE_MB Amount of free space that can be safely utilized taking mirroring into account, and yet be able to restore redundancy after a disk failure OFFLINE_DISKS 此DG中有多少个disk处于offline状态 COMPATIBILITY ASM所要求的最低版本 DATABASE_COMPATIBILITY 要使用此ASM的数据库要求的最低版本 10.8.5.2. 字段名称 GROUP_NUMBER DISK_NUMBER COMPOUND_INDEX V$ASM_DISK视图 INCARNATION MOUNT_STATUS HEADER_STATUS 字段含义 DG组编号，主键。如果为0，表示不归属于任何DG DISK在所属DG内的编号。从0开始 A 32-bit number consisting of a disk group number in the high-order 8 bits and a disk number in the low-order 24 bits (for efficient access to the view) Incarnation number for the disk CACHED：此disk永久存在于ASM中，并已加入了DG OPENED：此disk永久存在于ASM中，并已加入了DG，并有数据库实例正在使用它 IGNORED：它存在于 CLOSED：存在于ASM中但并未被ASM使用 CLOSING：ASM正在关闭此DISK MISSING：ASM参数里设置了 MEMBER：此盘已属于一个DG。除非使用force，否则不可将它加入其他DG中 UNKNOWN：ASM无法读该磁盘的头信息 CANDIDATE：此盘不属于任何一个DG，可被add到一个DG中 INCOMPATIBLE：头信息中记录的版本号与ASM的版本不一致 PROVISIONED - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement. The PROVISIONED header status is different from the CANDIDATE header status in that PROVISIONED implies that an additional platform-specific action has been taken by an administrator to make the disk available for Automatic Storage Management. FORMER：此盘曾经属于一个DG，现可被加到其他DG中 CONFLICT：由于冲突，ASM没有MOUNT它 FOREIGN：此盘中包含了文件系统上建立的文件 11. 数据库备份 11.1. 备份概念说明 无论数据库如何精心设计、配置和优化，但都难免会出现系统或硬件故障，如：语句故障、用户进程故障、实例故障、介质故障灯。一旦数据库出现故障将给用户数据带来灾难性后果。因此必须采取必要的备份策略以避免数据灾难的发生。 Oracle提供了强大的备份方案，备份方式分为物理备份和逻辑备份两个类型。物理备份是数据库文件的拷贝，根据备份时数据所处的状态，物理备份又分为脱机备份和联机备份；逻辑备份时对数据库逻辑组件的备份，逻辑备份使用工具将数据备份为二进制文件。 11.1.1. 脱机备份 脱机备份是在数据库正常关闭后，对数据文件进行的物理备份。当数据库关闭后，将数据库的所有数据文件、控制文件、参数文件、口令文件、重做日志文件等都拷贝到备份介质中。脱机备份是最安全的备份方式，数据库可以完全正常恢复。 11.1.1.1. 1）关闭数据库。 脱机备份步骤 2）拷贝数据文件，控制文件，参数文件，重做日志文件，归档日志文件到备份磁盘。 3）备份完毕，启动数据库。 脱机备份操作简单，安全可靠，但脱机备份只能将数据库恢复到备份的时间点，且在备份前必须要关闭数据库。在实际生产环境中很多数据库必须不间断的工作，不能采取脱机备份的方式，在这种情况下只能使用联机备份方案。 11.1.1.2. 脱机备份优点 1）脱机备份是非常简单的备份方法。 2）容易归档。 3）可完全恢复到备份时间点 4）简单有效。 11.1.2. 联机备份 联机数据库备份是不一致备份，因为在数据库备份过程中，部分数据可能会被修改，所以在恢复数据库时需要使用备份文件和归档日志文件，进行完全或不完全恢复，要实施联机备份方案时，数据库应运行在归档模式下。 11.1.2.1. 联机备份步骤 1）首先需要对数据库做一次完全备份，即备份基线。 2）定期对数据库进行联机备份。（增量备份） 3）当数据文件发生变化时，需要对控制文件进行物理备份。 在数据量不大的情况下，也可以使用exp，expdp，sqlloader等命令和工具对数据库进行逻辑备份。开发、测试阶段可使用逻辑备份，在生产环境中不推荐使用逻辑备份。 11.1.2.2. 联机备份优点 1）可进行表空间级别或数据文件级别的备份，备份时间短。 2）备份和恢复时不需要停库。 11.1.3. RMAN备份 RMAN是指Recovery Manager是Oracle恢复管理器工具。通过它可以对数据库进行备份与恢复操作。RMAN备份有很多方式、可以对数据库进行脱机、联机的物理备份也可以对数据库进行增量备份。 在实际过程中用得比较多的是RMAN增量备份。通过建立RMAN增量备份脚本使用系统shell脚本调用执行，通过crontab定时调度执行数据库的备份。 使用RMAN的优点如下： 1）支持在线热备份； 2）支持多级增量备份； 3）支持并行备份、恢复 ； 4）减少所需要备份量； 5）备份、恢复使用简单。 11.2. 备份策略规划 正确的备份策略不仅能保证数据库服务器高效率高性能的运行和使用，还能确保恢复与备份的有效性和可靠性。 Oracle的备份方法主要有全备份、增量备份和差分备份。全备份（Full Backup）是每次转储数据库全部数据，生成所有数据的备份。这种备份即占用时间又不经济，所以除非是小型数据库，一般情况对Oracle数据的备份不采用完全备份方式。 增量备份(Incremental Backup)只转储上次转储后更新过的数据。全转储与增量转储相比，从恢复角度看，使用海量存储全备份得到的后备副本进行恢复往往更方便，但对于大型数据库，事务处理又十分频繁，所以增量转储方式更实用更有效。 差分备份(Differential Backup)融合以上两种备份的优点，首先进行完全备份，其次在以后的每次备份中只备份每个数据文件较上次完全备份所更新的部分(差分数据)，所以，数据文件损坏后，利用该时间点之前最近一次的完全备份进行恢复，然后再使用时间点A的差分数据即可完成所有数据的恢复。 在制定Oracle备份策略应时应注意根据不同企业，不同系统的特点定制不同备份策略。选择完全备份，增量备份，还是差分备份。主要要求如下： 1）做好沟通工作，数据库管理员DBA需要使企业管理人员获知维护数据库的可用性的代价，因此首先需评估恢复需要的花费，以及若丢失数据给企业带来的损失。 2）对数据库中的数据进行重要程度的分类，作为确定备份频率的依据。即数据库系统都需全面保护，但要分主次，重要的数据要经常备份。 3）需要考虑数据库变化的情况。对于经常进行insert,update,delete的表和表结构发生变化的表，应重点对待经常备份。对于查询比较多的表，更重要的工作则是做好查询优化。 以下是一个典型的备份策略配置规划： 1）每半年做一个数据库的全备份（包括所有.dbf文件）； 2）每一个月做一次零级备份（不包含只读表空间）； 3）每个星期做一次一级备份； 4）每天做一次二级备份。 11.3. RMAN备份和恢复案例 因为在生产环境中，数据库都是运行在归档模式下，所以在本方案中只讨论在归档模式下数据库的备份和恢复。 11.3.1. 归档模式下的完全恢复 11.3.1.1. 备份脚本如下： RMAN>run{ 整库备份与恢复 allocate channel ch1 type disk; sql ‘alter system archive log current’; backup full database include current controlfile tag ‘dbfull’; format ‘/u06/oracle/rmanback/full_%u_%s_%p’; sql ‘alter system archive log current’; release channel ch1; } 恢复命令如下： RMAN>target / RMAN>startup mount RMAN>restore database; RMAN>recover database; RMAN>restore database open; 11.3.1.2. 备份脚本如下： 表空间备份与恢复 RMAN>run{ allocate channel ch1 type disk; backup tablespace users tag ‘ts_users’ format ‘/u06/oracle/rmanback/full_%u_%s_%p’; release channel ch1; } 如果只丢失了特定的表空间的数据文件，那么可以选择只恢复这个数据文件，而不是整个数据库，恢复表空间时不需要关闭数据库，只需要将该表空间至于“”“OFFLINE”状态下即可，恢复命令如下： RMAN>run{ sql ‘alter tablespace users offline immediate;’ restore tablespace users; recover tablespace users; sql ‘alter tablespace users online;’ } 11.3.1.3. 备份命令如下： 数据文件备份与恢复 RMAN>backup datafile 3; RMAN>backup datafile ‘/u02/oradata/orcl/’; 数据库文件的恢复和表空间恢复类似，可以指定文件号或文件名称进行恢复，恢复命令如下： RMAN>run{ allocate channel ch1 type disk; sql ‘alter tablespace users offline immediate’; restore datafile 4;--或者restore datafile ‘/u02/oradata/orcl/’ recover datafile 4; sql ‘alter tablespace users online’; release channel ch1; } 11.3.1.4. 备份命令如下： 归档日志文件备份与恢复 RMAN>backup archivelog all;天对数据库状态至少巡检一次。 2.定期对数据库进行优化和垃圾清理。 3.做好数据库备份情况与可恢复性检查 4.数据库检查的操作必须在业务相对比较空闲的时候执行，否则可能会影响系统性能。 11.4. 技能要求 1.数据库安装与配置；主要指定制数据库安装配置方案，检查软件安装环境，安装数据库软件，完成数据库配置，并测试之； 2.数据库性能优化： 主要是指核心参数调优，SQL语句调优，性能评估方案的提供； 3.数据库备份与恢复；主要是指本地、异地、同步、实时的分级备份与恢复方案及实施； 4.数据迁移：不同版本、不同厂商、不同结构数据库间的数据迁移； 5.故障排除；通过远程、上门等方式按服务级别实施故障排除； 6.预防性巡检；定期提供预防性巡检，并完成系统参数、配置调优，及补丁分发、安装服务。 11.5. 运维内容 11.5.1. 检查数据库运行情况 11.5.1.1. 数据库实例状态 SQL> select instance_name,host_name,startup_time,status,database_status from v$instance; INSTANCE_NAME HOST_NAME STARTUP_TIME STATUS DATABASE_STATUS ---------------- ------------------- -------------------- ---------- ------------ ---- CKDB AS14 2016-7-31 9:3 OPEN ACTIVE 其中“STATUS”表示Oracle当前的实例状态，必须为“OPEN”；“DATABASE_STATUS”表示Oracle当前数据库的状态，必须为“ACTIVE”。 SQL> select name,log_mode,open_mode from v$database; SQL> select name,log_mode,open_mode from v$database; NAME LOG_MODE OPEN_MODE --------- ------------ ----------------- CKDB ARCHIVELOG READ WRITE 其中“LOG_MODE”表示Oracle当前的归档方式。“ARCHIVELOG”表示数据库运行在归档模式下，“NOARCHIVELOG”表示数据库运行在非归档模式下。生产系统的数据库必须运行在归档方式下。 11.5.1.2. 检查服务进程 $ps -ef|grep ora_|grep -v grep&&ps -ef|grep ora_|grep -v grep|wc –l oracle 2960 1 0 May07 00:01:02 ora_pmon_CKDB oracle 2962 1 0 May07 00:00:22 ora_psp0_CKDB oracle 2964 1 0 May07 00:00:00 ora_mman_CKDB oracle 2966 1 0 May07 00:03:20 ora_dbw0_CKDB oracle 2968 1 0 May07 00:04:29 ora_lgwr_CKDB oracle 2970 1 0 May07 00:10:31 ora_ckpt_CKDB oracle 2972 1 0 May07 00:03:45 ora_smon_CKDB oracle 2974 1 0 May07 00:00:00 ora_reco_CKDB oracle 2976 1 0 May07 00:01:24 ora_cjq0_CKDB oracle 2978 1 0 May07 00:06:17 ora_mmon_CKDB oracle 2980 1 0 May07 00:07:26 ora_mmnl_CKDB oracle 2982 1 0 May07 00:00:00 ora_d000_CKDB oracle 2984 1 0 May07 00:00:00 ora_s000_CKDB oracle 2994 1 0 May07 00:00:28 ora_arc0_CKDB oracle 2996 1 0 May07 00:00:29 ora_arc1_CKDB oracle 3000 1 0 May07 00:00:00 ora_qmnc_CKDB oracle 3625 1 0 May07 00:01:40 ora_q000_CKDB oracle 31594 1 0 Jul20 00:00:00 ora_q003_CKDB oracle 23802 1 0 05:09 00:00:33 ora_j000_CKDB 19 在检查Oracle的进程命令输出后，输出显示至少应包括以下一些进程： Oracle写数据文件的进程，输出显示为：“ora_dbw0_CKDB” Oracle写日志文件的进程，输出显示为：“ora_lgwr_ CKDB” Oracle监听实例状态的进程，输出显示为：“ora_smon_ CKDB” Oracle监听客户端连接进程状态的进程，输出显示为：“ora_pmon_ CKDB” Oracle进行归档的进程，输出显示为：“ora_arc0_ CKDB” Oracle进行检查点的进程，输出显示为：“ora_ckpt_ CKDB” Oracle进行恢复的进程，输出显示为：“ora_reco_ CKDB” 11.5.1.3. 检查监听状态 /home/oracle>lsnrctl status 16 14:11:53 Copyright (c) 1991, 2005, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Start Date 07-MAY-2016 09:35:52 Uptime 77 days 4 hr. 36 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=AS14)(PORT=1521))) Services Summary... Service \"CKDB\" has 1 instance(s). Instance \"CKDB\ Service \"CKDBXDB\" has 1 instance(s). Instance \"CKDB\Service \"CKDB_XPT\" has 1 instance(s). Instance \"CKDB\service...The command completed successfully “Services Summary”项表示Oracle的监听进程正在监听哪些数据库实例，输出显示中至少应该有“CKDB”这一项。 检查监听进程是否存在： [oracle@AS14 ~]$ ps -ef|grep lsn|grep -v grep 11.5.2. 检查操作系统和数据库日志文件 此项工作包含检查操作系统的日志文件，检查Oracle日志文件，检查Oracle核心转储目录，检查Root用户和Oracle用户的email，总共四个部分。 11.5.2.1. 检查操作系统日志文件 # cat /var/log/messages |grep failed 查看是否有与Oracle用户相关的出错信息。 11.5.2.2. 检查数据库日志文件 [oracle@AS14 ~]$ cat /data/oracle/admin/CKDB/bdump/ |grep ora- [oracle@AS14 ~]$ cat /data/oracle/admin/CKDB/bdump/ |grep err [oracle@AS14 ~]$ cat /data/oracle/admin/CKDB/bdump/ |grep fail Oracle在运行过程中，会在警告日志文件中记录数据库的一些运行情况：数据库的启动、关闭，启动时的非缺省参数；数据库的重做日志切换情况，记录每次切换的时间，及如果因为检查点(checkpoint)操作没有执行完成造成不能切换，会记录不能切换的原因；对数据库进行的某些操作，如创建或删除表空间、增加数据文件；数据库发生的错误，如表空间不够、出现坏块、数据库内部错误(ORA－600)等。定期检查日志文件，根据日志中发现的问题及时进行处理： 问题 处理 启动参数不对 因为检查点操作或归档操作没有完成造成重做日志不能切换 检查初始化参数文件 如果经常发生这样的情况，可以考虑增加重做日志文件组；想办法提高检查点或归档操作的效率； 有人未经授权删除了表空间 检查数据库的安全问题，是否密码太简单；如有必要，撤消某些用户的系统权限 出现坏块 检查是否是硬件问题(如磁盘本生有坏块)，如果不是，检查是那个数据库对象出现了坏块，对这个对象进行重建 表空间不够 出现ORA-600 增加数据文件到相应的表空间 根据日志文件的内容查看相应的TRC文件，如果是Oracle的bug，要及时打上相应的补丁 11.5.2.3. 检查Oracle核心转储目录 $ls $ORACLE_BASE/admin/CKDB/cdump/*.trc|wc -l $ls $ORACLE_BASE/admin/CKDB/udump/*.trc|wc –l 如果上面命令的结果每天都在增长，则说明Oracle进程经常发生核心转储。这说明某些用户进程或者数据库后台进程由于无法处理的原因而异常退出。频繁的核心转储特别是数据库后台进程的核心转储会导致数据库异常终止。 11.5.3. 检查Oracle对象状态 检查Oracle对象的状态主要包含：检查Oracle控制文件状态，检查Oracle在线日志状态，检查Oracle表空间的状态，检查Oracle所有数据文件状态，检查Oracle所有表、索引、存储过程、触发器、包等对象的状态，检查Oracle所有回滚段的状态，总共六个部分。 11.5.3.1. 检查控制文件状态 SQL> select status,name from v$controlfile; STATUS NAME ------- -------------------------------------------------------------------------------- /data/oradata/CKDB/ /data/oradata/CKDB/ /data/oradata/CKDB/ 输出结果应该有3条以上（包含3条）的记录，“STATUS”应该为空。状态为空表示控制文件状态正常。 11.5.3.2. 检查在线日志文件状态 SQL> select group#,status,type,member from v$logfile; GROUP# STATUS TYPE MEMBER ---------- ------- ------- ----------- 3 ONLINE /data/oradata/CKDB/ 2 ONLINE /data/oradata/CKDB/ 1 ONLINE /data/oradata/CKDB/ 4 ONLINE /data/oradata/CKDB/ 5 ONLINE /data/oradata/CKDB/ 6 ONLINE /data/oradata/CKDB/ 6 rows selected 输出结果应该有3条以上（包含3条）记录，“STATUS”应该为非“INVALID”，非“DELETED”。 注：“STATUS”显示为空表示正常。 11.5.3.3. 检查表空间状态 SQL> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE UNDOTBS1 ONLINE SYSAUX ONLINE TEMP ONLINE USERS ONLINE SJ1 ONLINE ADM_INDEX ONLINE HOME_DATA ONLINE HOME_INDEX ONLINE PHOTO_DATA ONLINE PHOTO_INDEX ONLINE 输出结果中STATUS应该都为ONLINE。 11.5.3.4. 检查数据文件状态 SQL> select name,status from v$datafile; NAME STATUS -------------------------------------------------- ------- /data/oradata/CKDB/ SYSTEM /data/oradata/CKDB/ ONLINE /data/oradata/CKDB/ ONLINE /data/oradata/CKDB/ ONLINE /data/oradata/CKDB/ ONLINE /data/oradata/CKDB/ ONLINE /data/oradata/CKDB/ ONLINE /data/oradata/CKDB/ ONLINE /data/oradata/CKDB/ ONLINE /data/oradata/CKDB/ ONLINE /data/oradata/CKDB/ ONLINE /data/oradata/CKDB/ ONLINE /data/oradata/CKDB/ ONLINE /data/oradata/CKDB/ ONLINE /data/oradata/CKDB/ ONLINE /data/oradata/CKDB/ ONLINE /data/oradata/CKDB/ ONLINE /data/oradata/CKDB/ ONLINE /data/oradata/CKDB/ ONLINE /data/oradata/CKDB/ ONLINE 输出结果中“STATUS”应该都为“ONLINE”。或者： SQL> select file_name,status from dba_data_files; FILE_NAME STATUS --------------------------------------------- --------- /data/oradata/CKDB/ AVAILABLE /data/oradata/CKDB/ AVAILABLE /data/oradata/CKDB/ AVAILABLE /data/oradata/CKDB/ AVAILABLE /data/oradata/CKDB/ AVAILABLE /data/oradata/CKDB/ AVAILABLE /data/oradata/CKDB/ AVAILABLE /data/oradata/CKDB/ AVAILABLE /data/oradata/CKDB/ AVAILABLE 输出结果中“STATUS”应该都为“AVAILABLE”。 11.5.3.5. 检查无效对象 sql>select owner,object_name,object_type from dba_objects where status!='VALID' and owner!='SYS' and owner!='SYSTEM'; no rows selected 如果有记录返回，则说明存在无效对象。若这些对象与应用相关，那么需要重新编译生成这个对象，或者： 11.5.3.6. 检查回滚段状态 SQL> select segment_name,status from dba_rollback_segs; SEGMENT_NAME STATUS ------------------------------ ---------------- SYSTEM ONLINE _SYSSMU1$ ONLINE _SYSSMU2$ ONLINE _SYSSMU3$ ONLINE _SYSSMU4$ ONLINE _SYSSMU5$ ONLINE _SYSSMU6$ ONLINE _SYSSMU7$ ONLINE _SYSSMU8$ ONLINE _SYSSMU9$ ONLINE _SYSSMU10$ ONLINE 11 rows selected 输出结果中所有回滚段的“STATUS”应该为“ONLINE”。 11.5.4. 检查数据库相关资源的使用 检查Oracle相关资源的使用情况主要包含：检查Oracle初始化文件中相关的参数值，检查数据库连接情况，检查系统磁盘空间，检查Oracle各个表空间使用情况，检查一些扩展异常的对象，检查system表空间内的内容，检查对象的下一扩展与表空间的最大扩展值，总共七个部分。 11.5.4.1. 检查数据库参数文件的值 SQL> select resource_name,max_utilization,initial_allocation, limit_value from v$resource_limit; RESOURCE_NAME MAX_UTILIZATION INITIAL_ALLOCAT LIMIT_VALUE -------------------- --------------- --------------- --------------- processes 162 500 500 sessions 168 555 555 enqueue_locks 136 6930 6930 enqueue_resources 111 2660 UNLIMITED ges_procs 0 0 0 ges_ress 0 0 UNLIMITED ges_locks 0 0 UNLIMITED ges_cache_ress 0 0 UNLIMITED ges_reg_msgs 0 0 UNLIMITED ges_big_msgs 0 0 UNLIMITED ges_rsv_msgs 0 0 0 gcs_resources 0 0 0 gcs_shadows 0 0 0 dml_locks 76 2440 UNLIMITED temporary_table_locks 26 UNLIMITED UNLIMITED transactions 13 610 UNLIMITED branches 0 610 UNLIMITED cmtcallbk 3 610 UNLIMITED sort_segment_locks 5 UNLIMITED UNLIMITED max_rollback_segments 11 610 65535 RESOURCE_NAME MAX_UTILIZATION INITIAL_ALLOCAT LIMIT_VALUE -------------------- --------------- --------------- --------------- max_shared_servers 1 UNLIMITED UNLIMITED parallel_max_servers 16 80 3600 22 rows selected 若LIMIT_VALU-MAX_UTILIZATION<=5，则表明与RESOURCE_NAME相关的Oracle初始化参数需要调整。可以通过修改Oracle初始化参数文件$ORACLE_BASE/admin/CKDB/pfile/来修改。 11.5.4.2. 检查数据库连接情况 select sid,serial#,username,program,machine,status from v$session; SID SERIAL# USERNAME PROGRAM MACHINE STATUS ---- ---------- ------------ ---------------------------- ------------ -------- 1 3 oracle@xz15saledb (PMON) xz15saledb ACTIVE 2 3 oracle@xz15saledb (DBW0) xz15saledb ACTIVE 3 3 oracle@xz15saledb (DBW1) xz15saledb ACTIVE 4 3 oracle@xz15saledb (LGWR) xz15saledb ACTIVE 5 3 oracle@xz15saledb (CKPT) xz15saledb ACTIVE 6 3 oracle@xz15saledb (SMON) xz15saledb ACTIVE 7 3 oracle@xz15saledb (RECO) xz15saledb ACTIVE 8 1 oracle@xz15saledb (CJQ0) xz15saledb ACTIVE 9 3 oracle@xz15saledb (ARC0) xz15saledb ACTIVE 10 3 oracle@xz15saledb (ARC1) xz15saledb ACTIVE 11 11319 ZK AccPrtInv_svr@xz15tuxedo2 (TNS V1-V3) xz15tuxedo2 INACTIVE 13 48876 ZG upload@xz15saleap (TNS V1-V3) xz15saleap INACTIVE 17 20405 ZK AccCreateRpt@xz15tuxedo1 (TNS V1-V3) xz15tuxedo1 INACTIVE 20 12895 ZK OweScanSvr@xz15billdb (TNS V1-V3) xz15billdb INACTIVE 其中： SID 会话(session)的ID号； SERIAL# 会话的序列号，和SID一起用来唯一标识一个会话； USERNAME 建立该会话的用户名； PROGRAM 这个会话是用什么工具连接到数据库的； STATUS 当前这个会话的状态，ACTIVE表示会话正在执行某些任务， INACTIVE表示当前会话没有执行任何操作； 如果建立了过多的连接，会消耗数据库的资源，同时，对一些“挂死”的连接可能需要手工进行清理。如果DBA要手工断开某个会话，则执行：（一般不建议使用这种方式去杀掉数据库的连接，这样有时候session不会断开。容易引起死连接。建议通过sid查到操作系统的spid,使用ps –ef|grep spidno 的方式确认spid不是ORACLE的后台进程。使用操作系统的kill -9命令杀掉连接 ） alter system kill session 'SID,SERIAL#'; 11.5.4.3. 检查系统磁盘空间 如果文件系统的剩余空间过小或增长较快，需对其进行确认并删除不用的文件以释放空间。 [oracle@AS14 ~]$ df -h Filesystem Size Used Avail Use% Mounted on /dev/sda5 42% / /dev/sda1 479M 16M 438M 4% /boot /dev/sda2 49G 19G 28G 41% /data none 1014M 0 1014M 0% /dev/shm 11.5.4.4. 检查表空间使用情况 SQL> select ,,,round(*100) \"% Free\" from (select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a, (select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) f WHERE = (+) order by \"% Free\"; TABLESPACE_NAME TOTAL FREE % Free ------------------------------ ---------- ---------- ---------- OPERATION_DATA 1800 547 30 WAPWEB_DATA 100 36 36 OPERATION_INDEX 500 186 37 SYSTEM 1024 515 50 SYSAUX 1024 534 52 SALE8_TEMP 100 62 62 SJ1 500 348 70 PERFSTAT 500 356 71 ………. HOME_DATA 100 77 77 SYS_INDEX 100 100 100 VIDEO_INDEX 100 100 100 VIDEO_DATA 100 100 100 BLOG_DATA 100 100 100 39 rows selected 如果空闲率%Free小于10%以上（包含10%），则注意要增加数据文件来扩展表空间而不要是用数据文件的自动扩展功能。请不要对表空间增加过多的数据文件，增加数据文件的原则是每个数据文件大小为2G或者4G，自动扩展的最大限制在8G。 11.5.4.5. 检查system表空间的内容 select distinct(owner) from dba_tables where tablespace_name='SYSTEM' and owner!='SYS' and owner!='SYSTEM' union select distinct(owner) from dba_indexes where tablespace_name='SYSTEM' and owner!='SYS' and owner!='SYSTEM'; no rows selected 如果记录返回，则表明system表空间内存在一些非system和sys用户的对象。应该进一步检查这些对象是否与应用相关。如果相关请把这些对象移到非System表空间，同时应该检查这些对象属主的缺省表空间值。 11.5.5. 检查数据库备份 11.5.5.1. 检查数据库备份日志信息 假设：备份的临时目录为/backup/hotbakup，那么需要检查2016年7月22日的备份结果，则用下面的命令来检查： #cat /backup/hotbackup/|grep –i error 备份脚本的日志文件为hotbackup-月份-日期-年份.log，在备份的临时目录下面。如果文件中存在“ERROR:”，则表明备份没有成功，存在问题需要检查。 11.5.5.2. 检查backup卷中文件产生的时间 #ls –lt /backup/hotbackup backup卷是备份的临时目录，查看输出结果中文件的日期，都应当是在当天凌晨由热备份脚本产生的。如果时间不对则表明热备份脚本没执行成功。 11.5.5.3. 检查Oracle用户的email #tail –n 300 /var/mail/oracle 热备份脚本是通过Oracle用户的cron去执行的。cron执行完后操作系统就会发一条Email通知Oracle用户任务已经完成。查看Oracle email中今天凌晨部分有无ORA-，Error，Failed等出错信息，如果有则表明备份不正常。 11.5.6. 检查数据库的性能 检查Oracle数据库性能情况主要包含：检查数据库的等待事件，检查死锁及处理，检查cpu、I/O、内存性能，查看是否有僵死进程，检查行链接/迁移，定期做统计分析，检查缓冲区命中率，检查共享池命中率，检查排序区，检查日志缓冲区，总共十个部分。 11.5.6.1. 检查数据库的等待事件 set pages 80 set lines 120 col event for a40 select sid,event,p1,p2,p3,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where event not like 'SQL%' and event not like 'rdbms%'; 如果数据库长时间持续出现大量像latch free，enqueue，buffer busy waits，db file sequential read，db file scattered read等等待事件时，需要对其进行分析，可能存在问题的语句。 11.5.6.2. Disk Read最高的Sql语句 SQL>SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS) WHERE ROWNUM<=5 desc; 11.5.6.3. 查找性能最差的前10条Sql语句 SELECT * FROM (SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS, SQL_TEXT FROM V$SQLAREA ORDER BY DISK_READS DESC) WHERE ROWNUM<10 ; 11.5.6.4. 查找等待最长的系统事件 SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%' ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM<=5; 11.5.6.5. 查找运行最长的SQL语句 COLUMN USERNAME FORMAT A12 COLUMN OPNAME FORMAT A16 COLUMN PROGRESS FORMAT A8 SELECT USERNAME,SID,OPNAME,ROUND(SOFAR*100 / TOTALWORK,0) || '%' AS PROGRESS,TIME_REMAINING,SQL_TEXT FROM V$SESSION_LONGOPS , V$SQL WHERE TIME_REMAINING <> 0 AND SQL_ADDRESS=ADDRESS AND SQL_HASH_VALUE = HASH_VALUE; 11.5.6.6. 查找最消耗CPU的进程 SET LINE 240 SET VERIFY OFF COLUMN SID FORMAT 999 COLUMN PID FORMAT 999 COLUMN S_# FORMAT 999 COLUMN USERNAME FORMAT A9 HEADING \"ORA USER\" COLUMN PROGRAM FORMAT A29 COLUMN SQL FORMAT A60 COLUMN OSNAME FORMAT A9 HEADING \"OS USER\" SELECT PID, SID, SPID, USERNAME, OSNAME,# S_#,, PROGRAM,,,RTRIM(SUBSTR, 1, 80)) SQLFROM V$PROCESS P, V$SESSION S,V$SQLAREA A WHERE = AND = (+) AND LIKE '%&1%'; 11.5.6.7. 查找碎片程度高的表 SQL> SELECT segment_name table_name,COUNT(*) extents FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name HAVING COUNT(*)=(SELECT MAX(COUNT(*)) FROM dba_segments GROUP BY segment_name); 11.5.6.8. 检查表空间的I/O比例 SQL>SELECT NAME, \"FILE\DBA_DATA_FILES DF WHERE # = ORDER BY ; 11.5.6.9. 检查文件系统的I/O比例 SQL>SELECT SUBSTR#,1,2) \"#\A, V$FILESTAT B WHERE # = #; 11.5.6.10. 检查并处理死锁 查询锁对象信息： col sid for 999999 col username for a10 col schemaname for a10 col osuser for a16 col machine for a16 col terminal for a20 col owner for a10 col object_name for a30 col object_type for a10 select sid,serial#,username,SCHEMANAME,osuser,MACHINE, terminal,PROGRAM,owner,object_name,object_type, from dba_objects o,v$locked_object l,v$session s where = and =; 在操作系统中kill掉连接会话： #>kill -9 pid 11.5.6.11. 检查操作系统的性能 记录数据库的cpu使用、IO、内存等使用情况，使用 vmstat,iostat,sar,top等命令进行信息收集并检查这些信息，判断资源使用情况。 1、cpu使用情况 [root@sale8 ~]# top top - 10:29:35 up 73 days, 19:54, 1 user, load average: , , Tasks: 353 total, 2 running, 351 sleeping, 0 stopped, 0 zombie Cpu(s): % us, % sy, % ni, % id, % wa, % hi, % si PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 30495 oracle 15 0 8329m 866m 861m R 10 7: oracle 32501 oracle 15 0 8328m S 2 1: oracle 32503 oracle 15 0 8329m S 2 2: oracle 2、内存使用情况 # free -m total used free shared buffers cached Mem: 2026 1958 67 0 76 1556 -/+ buffers/cache: 326 1700 Swap: 5992 92 5900 3、系统I/O # iostat -k 1 3 avg-cpu: %user %nice %sys %iowait %idle Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn sda avg-cpu: %user %nice %sys %iowait %idle Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn sda 0 0 4、系统负载 #uptime 12:08:37 up 162 days, 23:33, 15 users, load average: , , 11.5.6.12. 检查僵尸进程 select spid from v$process where addr not in (select paddr from v$session); 有些僵尸进程有阻塞其他业务的正常运行，定期杀掉僵尸进程。 11.5.6.13. 检查行链接/迁移 Sql>select table_name,num_rows,chain_cnt From dba_tables Where owner='CTAIS2' And chain_cnt<>0; 注：含有long raw列的表有行链接是正常的,找到迁移行保存到chained_rows表中,如没有该表执行../rdbms/admin/ Sql>analyze table tablename list chained rows; 可通过表chained_rows中table_name,head_rowid看出哪些行是迁移行 11.5.6.14. 定期做统计分析 对于采用Oracle Cost-Based-Optimizer的系统，需要定期对数据对象的统计信息进行采集更新，使优化器可以根据准备的信息作出正确的explain plan。在以下情况更需要进行统计信息的更新： 1、应用发生变化 2、大规模数据迁移、历史数据迁出、其他数据的导入等 3、数据量发生变化 查看表或索引的统计信息是否需更新，如： Sql>Select table_name,num_rows,last_analyzed From user_tables where table_name ='DJ_NSRXX' sql>select count(*) from DJ_NSRXX如num_rows和count(*) 如果行数相差很多,则该表需要更新统计信息，建议一周做一次统计信息收集，如： Sql>exec 'CTAIS2',cascade => TRUE,degree => 4); 11.5.6.15. 检查缓冲区命中率 SQL> SELECT + logical_reads, phys_reads, round(100*+),4) hit_ratio FROM v$sysstat a,v$sysstat b,v$sysstat c WHERE ='db block gets' AND ='consistent gets' AND ='physical reads' ; LOGICAL_READS PHYS_READS HIT_RATIO ------------- ---------- ---------- 如果命中率低于90% 则需加大数据库参数db_cache_size。 11.5.6.16. 检查共享池命中率 SQL> select sum(pinhits)/sum(pins)*100 from v$librarycache; SUM(PINHITS)/SUM(PINS)*100 -------------------------- 如低于95%，则需要调整应用程序使用绑定变量，或者调整数据库参数shared pool的大小。 11.5.6.17. 检查排序区 SQL> select name,value from v$sysstat where name like '%sort%'; NAME VALUE ---------------------------------------------------------------- ---------- sorts (memory) 6135534 sorts (disk) 8 如果disk/(memoty+row)的比例过高，则需要调整sort_area_size(workarea_size_policy=false)或pga_aggregate_target(workarea_size_policy=true)。 11.5.6.18. 检查日志缓冲区 SQL> select name,value from v$sysstat where name in ('redo entries','redo buffer allocation retries'); NAME VALUE ---------------------------------------------------------------- ---------- redo buffer allocation retries 880 如果redo buffer allocation retries/redo entries 超过1% ，则需要增大log_buffer。 11.5.7. 数据库安全性检查 11.5.7.1. 检查系统安全日志信息 系统安全日志文件的目录在/var/log 下，主要检查登录成功或失败的用户日志信息。 检查登录成功的日志 [root@rac2 ~]# grep -i accepted /var/log/secure 检查登录失败的日志 [root@rac2 ~]# grep -i inval /var/log/secure &&grep -i failed /var/log/secure 在出现的日志信息中没有错误(Invalid、refused)提示，如果没有(Invalid、refused)视为系统正常，出现错误提示，应作出系统告警通知。 11.5.7.2. 检查用户修改密码 在数据库系统上往往存在很多的用户，如：第三方数据库监控系统，初始安装数据库时的演示用户，管理员用户等等，这些用户的密码往往是写定的，被很多人知道，会被别有用心的人利用来攻击系统甚至进行修改数据。需要修改密码的用户包括： 数据库管理员用户SYS，SYSTEM；其他用户。 登陆系统后，提示符下输入cat /etc/passwd，在列出来的用户中查看是否存在已经不再使用的或是陌生的帐号。若存在，则记录为异常。 修改密码方法： Sql>alter user USER_NAME identified by PASSWORD; 因篇幅问题不能全部显示，请点此查看更多更全内容