ORACLE数据库优化和备份

Oracle SQL 性能优化： 1.选用适合的ORACLE优化器 ORACLE的优化器共有3种 A、RULE (基于规则) b、COST (基于成本) c、CHOOSE (选择性) 设置缺省的优化器，可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明，如RULE，COST，CHOOSE，ALL_ROWS，FIRST_ROW

Oracle SQL 性能优化：

1.选用适合的ORACLE优化器
ORACLE的优化器共有3种

A、RULE (基于规则) b、COST (基于成本) c、CHOOSE (选择性)

设置缺省的优化器，可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明，如RULE，COST，CHOOSE，ALL_ROWS，FIRST_ROWS 。 你当然也在SQL句级或是会话(session)级对其进行覆盖。

为了使用基于成本的优化器(CBO， Cost-Based Optimizer) ， 你必须经常运行analyze 命令，以增加数据库中的对象统计信息(object statistics)的准确性。

如果数据库的优化器模式设置为选择性(CHOOSE)，那么实际的优化器模式将和是否运行过analyze命令有关。 如果table已经被analyze过， 优化器模式将自动成为CBO ， 反之，数据库将采用RULE形式的优化器。

在缺省情况下，ORACLE采用CHOOSE优化器， 为了避免那些不必要的全表扫描(full table scan) ， 你必须尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。

2.访问Table的方式
ORACLE 采用两种访问表中记录的方式：

A、 全表扫描

全表扫描就是顺序地访问表中每条记录。ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描。

B、 通过ROWID访问表

你可以采用基于ROWID的访问方式情况，提高访问表的效率， ROWID包含了表中记录的物理位置信息。ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系。通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能上的提高。

3.共享SQL语句
为了不重复解析相同的SQL语句，在第一次解析之后，ORACLE将SQL语句存放在内存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享。 因此，当你执行一个SQL语句(有时被称为一个游标)时，如果它和之前的执行过的语句完全相同， ORACLE就能很快获得已经被解析的语句以及最好的执行路径。ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用。

可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering)，这个功能并不适用于多表连接查询。

数据库管理员必须在init.ora中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了。

当你向ORACLE提交一个SQL语句，ORACLE会首先在这块内存中查找相同的语句。这里需要注明的是，ORACLE对两者采取的是一种严格匹配，要达成共享，SQL语句必须完全相同(包括空格，换行等)。

数据库管理员必须在init.ora中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了。

共享的语句必须满足三个条件：

A、 字符级的比较： 当前被执行的语句和共享池中的语句必须完全相同。

B、 两个语句所指的对象必须完全相同：

C、 两个SQL语句中必须使用相同的名字的绑定变量(bind variables)。

4.选择最有效率的表名顺序(只在基于规则的优化器中有效)
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表 driving table)将被最先处理。在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时， 会运用排序及合并的方式连接它们。首先，扫描第一个表(FROM子句中最后的那个表)并对记录进行派序，然后扫描第二个表(FROM子句中最后第二个表)，最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。

如果有3个以上的表连接查询， 那就需要选择交叉表(interdiv table)作为基础表， 交叉表是指那个被其他表所引用的表。

5.WHERE子句中的连接顺序
ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前， 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

6.SELECT子句中避免使用 ' * '
当你想在SELECT子句中列出所有的COLUMN时，使用动态SQL列引用 '*' 是一个方便的方法。不幸的是，这是一个非常低效的方法。实际上，ORACLE在解析的过程中， 会将'*' 依次转换成所有的列名， 这个工作是通过查询数据字典完成的， 这意味着将耗费更多的时间。

7.减少访问数据库的次数
当执行每条SQL语句时，ORACLE在内部执行了许多工作：解析SQL语句，估算索引的利用率，绑定变量，读数据块等等。由此可见，减少访问数据库的次数，就能实际上减少ORACLE的工作量。

8.使用DECODE函数来减少处理时间
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。

9.整合简单，无关联的数据库访问
如果你有几个简单的数据库查询语句，你可以把它们整合到一个查询中(即使它们之间没有关系)

10.删除重复记录

11.用TRUNCATE替代DELETE
当删除表中的记录时，在通常情况下， 回滚段(rollback segments ) 用来存放可以被恢复的信息。 如果你没有COMMIT事务，ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)。

而当运用TRUNCATE时， 回滚段不再存放任何可被恢复的信息。当命令运行后，数据不能被恢复。因此很少的资源被调用，执行时间也会很短。

12.尽量多使用COMMIT
只要有可能，在程序中尽量多使用COMMIT，这样程序的性能得到提高，需求也会因为COMMIT所释放的资源而减少

COMMIT所释放的资源：

A、 回滚段上用于恢复数据的信息。

B、被程序语句获得的锁。

C、 redo log buffer 中的空间。

D、ORACLE为管理上述3种资源中的内部花费。

13.计算记录条数
和一般的观点相反，count(*) 比count(1)稍快，当然如果可以通过索引检索，对索引列的计数仍旧是最快的。例如 COUNT(EMPNO)

14.用Where子句替换HAVING子句
避免使用HAVING子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤。 这个处理需要排序，总计等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。

15.减少对表的查询
在含有子查询的SQL语句中，要特别注意减少对表的查询。

16.通过内部函数提高SQL效率。

17.使用表的别名(Alias)
当在SQL语句中连接多个表时， 请使用表的别名并把别名前缀于每个Column上。这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。

18.用EXISTS替代IN
在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS(或NOT EXISTS)通常将提高查询的效率。

19.用NOT EXISTS替代NOT IN
在子查询中，NOT IN子句将执行一个内部的排序和合并。 无论在哪种情况下，NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOT IN ，我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。

20.用表连接替换EXISTS
通常来说 ， 采用表连接的方式比EXISTS更有效率 。

21.用EXISTS替换DISTINCT
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时，避免在SELECT子句中使用DISTINCT。 一般可以考虑用EXIST替换 。

四、冷备份与热备份、双机热备与容错

冷备份与热备份

一、 冷备份

　　

　　冷备份发生在数据库已经正常关闭的情况下，当正常关闭时会提供给我们一个完整的数据库。冷备份时将关键性文件拷贝到另外的位置的一种说法。对于备份Oracle信息而言，冷备份时最快和最安全的方法。冷备份的优点是：

　　

　　１、 是非常快速的备份方法（只需拷文件）

　　２、 容易归档（简单拷贝即可）

　　３、 容易恢复到某个时间点上（只需将文件再拷贝回去）

　　４、 能与归档方法相结合，做数据库“最佳状态”的恢复。

　　５、 低度维护，高度安全。

　　

　　但冷备份也有如下不足：

　　

　　１、 单独使用时，只能提供到“某一时间点上”的恢复。

　　２、 再实施备份的全过程中，数据库必须要作备份而不能作其他工作。也就是说，在冷备份过程中，数据库必须是关闭状态。

　　３、 若磁盘空间有限，只能拷贝到磁带等其他外部存储设备上，速度会很慢。

　　４、 不能按表或按用户恢复。

　　

　　如果可能的话（主要看效率），应将信息备份到磁盘上，然后启动数据库（使用户可以工作）并将备份的信息拷贝到磁带上（拷贝的同时，数据库也可以工作）。冷备份中必须拷贝的文件包括：

　　

　　１、 所有数据文件

　　２、 所有控制文件

　　３、所有联机REDO LOG文件

　　４、 Init.ora文件（可选）

　　

　　值得注意的使冷备份必须在数据库关闭的情况下进行，当数据库处于打开状态时，执行数据库文件系统备份是无效的。

　　

　　下面是作冷备份的完整例子。

　　

　　（1） 关闭数据库

　　sqlplus /nolog

　　sql>connect /as sysdba

　　sql>shutdown normal;

　　

　　（2） 用拷贝命令备份全部的时间文件、重做日志文件、控制文件、初始化参数文件

　　sql>cp

　　

　　（3） 重启Oracle数据库

　　sql>startup

　　

　　二、 热备份

　　

　　热备份是在数据库运行的情况下，采用archivelog mode方式备份数据库的方法。所以，如果你有昨天夜里的一个冷备份而且又有今天的热备份文件，在发生问题时，就可以利用这些资料恢复更多的信息。热备份要求数据库在Archivelog方式下操作，并需要大量的档案空间。一旦数据库运行在archivelog状态下，就可以做备份了。热备份的命令文件由三部分组成：

　　

　　1． 数据文件一个表空间一个表空间的备份。

　　

　　（1） 设置表空间为备份状态

　　（2） 备份表空间的数据文件

　　（3） 恢复表空间为正常状态

　　

　　2． 备份归档log文件

　　

　　（1） 临时停止归档进程

　　（2） log下那些在archive rede log目标目录中的文件

　　（3） 重新启动archive进程

　　（4） 备份归档的redo log文件

　　

　　3． 用alter database bachup controlfile命令来备份控制文件热备份的优点是：

　　

　　1． 可在表空间或数据库文件级备份，备份的时间短。

　　2． 备份时数据库仍可使用。

　　3． 可达到秒级恢复（恢复到某一时间点上）。

　　4． 可对几乎所有数据库实体做恢复

　　5． 恢复是快速的，在大多数情况下爱数据库仍工作时恢复。

　　

　　热备份的不足是：

　　

　　1． 不能出错，否则后果严重

　　2． 若热备份不成功，所得结果不可用于时间点的恢复

　　3． 因难于维护，所以要特别仔细小心，不允许“以失败告终”。

双机热备的实现模式

双机热备有两种实现模式，一种是基于共享的存储设备的方式，另一种是没有共享的存储设备的方式，一般称为纯软件方式。

　基于存储共享的双机热备是双机热备的最标准方案。

　对于这种方式，采用两台（或多台，参见：双机与集群的异同）服务器，使用共享的存储设备（磁盘阵列柜或存储区域网SAN）。两台服务器可以采用互备、主从、并行等不同的方式。在工作过程中，两台服务器将以一个虚拟的IP地址对外提供服务，依工作方式的不同，将服务请求发送给其中一台服务器承担。同时，服务器通过心跳线(目前往往采用建立私有网络的方式)侦测另一台服务器的工作状况。当一台服务器出现故障时，另一台服务器根据心跳侦测的情况做出判断，并进行切换，接管服务。对于用户而言，这一过程是全自动的，在很短时间内完成，从而对业务不会造成影响。由于使用共享的存储设备，因此两台服务器使用的实际上是一样的数据，由双机或集群软件对其进行管理。

　对于纯软件的方式，则是通过镜像软件，将数据可以实时复制到另一台服务器上，这样同样的数据就在两台服务器上各存在一份，如果一台服务器出现故障，可以及时切换到另一台服务器。

　对于这种方式的深入分析，请参见：纯软件方式的双机热备方案深入分析

　纯软件方式还有另外一种情况，即服务器只是提供应用服务，而并不保存数据（比如只进行某些计算，做为应用服务器使用）。这种情况下同样也不需要使用共享的存储设备，而可以直接使用双机或集群软件即可。但这种情况其实与镜像软件无关，只不过是标准的双机热备的一种小的变化。

双机容错的工作原理

1、双机容错的两种方式

双机容错从工作原理上可以分为共享磁盘阵列柜方式和扩展镜像纯软件方式两种。这两种方式的共同特点都是围绕关键数据的可靠性，对操作系统、电源、CPU和主机主板进行容错。

双机共享磁盘阵列柜方式是以磁盘阵列柜为中心的双机容错方神机妙算，磁盘柜通过SCSI线连接到两个系统上，并能被两个系统所访问。关键数据放在共享磁盘柜中，在正常运行时，控制友在主用系统上，当主用系统发生故障或主用系统检查到某种故障后，系统控制权就切换到备用主机。主用系统修复后，主备角色互换，双机系统进入正常工作模式。

双机扩展镜像酏软件方式是纯软件方式的双机容错方案，两个系统之间通过以太网连接，关键数据在两个系统之间呈镜像存在。在正常运行时，控制权在主用系统上，数据实时地镜像到备用系统上。当主用系统发生故障或主用系统检查到某种故障后，系统控制权切换到备用主机。由于采用以太网作为系统的数据链路，主用系统可不干扰备用系统工作，自动脱离并在一个孤立的环境中进行故障的诊断和维修，主用系统修复后，控制权需要切回到主用系统，数据需要从备用系统恢复到主用系统，这个工作在后台自动完成，应用读取数据仍从备用系统上进行而不会中断。数据恢复完成后，双机系统进入正常工作模式。

以上两种双机容错的方式已经能很好地保证数据可靠，如果在主、备机上各运行一种应用还可实现相互备份。

2．共享磁盘阵列柜方式的工作原理

使用共享磁盘阵列柜方式的两台（或多台）服务器的数据同时存放在一个磁盘阵列柜里，因此，不需要进行数据复制，只需在其中一台服务器停机时将此服务器的工作转移到另外一台服务器，工作较为简单。由于数据存储在同一磁盘阵列柜里，一是磁盘阵列柜的数据捐赠 坏则数据全部丢失，有单点崩溃的可能性，而且由于服务器与磁盘阵列柜之间通常使用SCSI线连接，因此受到距离的了限制。

共享磁盘阵列车柜方式一般由监控系统与切换系统两部分组成。

（1） 监控系统

A、SCSI侦测。共享磁盘阵列柜方式内部含有侦测心跳通信线路，侦测结果置于共享磁盘阵列柜上的一个5MB的小区，用于监控，此小区一般在机柜逻辑盘的起始段，对于某一台服务器而言，将侦测信自己人以类似于记录方式写在该小区内，其中每一条记录包括如下内容。

系统对本服务器的监测状态信息

另一台服务器是滞看到本服务器状态信息，同时修改记录区内容。

B、网络侦测。当一台服务器有问题或出现故障时，对等服务器的可调变心跳频率不断提高。在最小心跳时间内发现记录内容没有更新，即会调用网络心跳侦测两次确认系统状态。当峡谷线心路都判断系统故障时，共享磁盘阵列柜方式将故障服务器的交易业务在最小安全切换时间内切换到对等服务器上继续运行。

C、切换系统

网络服务器。双服务器后台，对于用户一羰，由监控软件共享磁盘阵列柜方式提供一个逻辑的IP地址，如192.192.192.1,任一用户上网可以直接使用这一地址，当后台其中一台服务器出现故障时，另外一台服务器会自己将其网卡的IP地址替换为192.192.192.1，这样，用户一端的网络不会因为一台服务器出现故障而断掉。

数据库服务。当其中一台服务器出现故障时，另外一台服务器会自动接管数据库，同时启动数据库和应用程序，使用户数据库可以继续操作，对用户而言不受影响。

应用系统。当有一台服务器出现故障时，另外一台服务器会自动接管各类应用程序，同时启动应用程序，使用户可以继续操作，对用户而言不受影响。

3、扩展镜像纯软件方式的工作原理

使用纯软件方式的软件不需要共享磁盘阵列柜，它将数据存储于各自服务器内，通过镜相引擎将数据进行实时复制。当其中一台服务器停机时，设定的服务器接管停机服务器的工作。由于数据存储于不同服务器内，因此避免了单点崩溃的可能性，增加了数据的安全性。服务器之间通过网络连接，所以服务器之间的连接受距离的限制较小。由于数据存储在各自己服务器硬盘内，因此服务器之间有应用各不影响，提高了服务器正常使用时的效率。

4、热备份

热备份其实是计算机容错技术的一个概念，是实现计算机系统高可用性的主要方式。热备份采用磁盘镜相技术，将运行着的计算机系统数据和应用数据同时保存在不同的硬盘上，镜像在不同的磁盘上的数据在发生变化时同时刷新，从而保证数据一致性。当系统中的一个硬盘发生故障时，计算机可以使用镜像数据，避免因系统单点故障（如硬盘故障）导致整个计算机系统无法运行，从而实现计算机系统的高可用性。

现在的计算机系统在系统建设时都普遍采用了热备份方式，最典型的实现方式是双机热备份，即双机容错系统。双机容错系统在建设时选用两台同样服务器，运行相同的操作系统、应用软件（如数据库软件），两台服务器共享一个磁盘阵列，采用磁盘镜像，将应用数据建立在磁盘阵列车上，实现双机容错。其中一台服务器被指定为工作机，由它处理当前运行的业务，另一台为备份服务器。一旦工作机发生故障，运行的业务请求将被人工（或自动）地切换到备份服务器，使运行着的业务不至于因为系统的单点故障中断，实现系统的高可用性。

热备份实现了计算机系统的高可用性，使一些对实时性要求很强的业务（如银行信用卡业务）得以保障。然而，热备份方式并不能解决所有计算机系统数据管理问题，举一个最简单的例子，如果操作人员误删除了一个文件，热备系统为保证数据的一致性，会同时将这个文件的镜像文件删除，造成数据丢失。为防止有用的数据因系统故障和人为误操作而损坏或丢失，实行数据存储管理必不可少，数据存储管理的核心是数据备份。

双机容错环境下Oracle数据库的具体应用

目前许多建立和应用信息系统的企业，在系统应用不断改进的同时，开始注意提高企业信息系统的可用性和可靠性。通过双机容错系统为企业提供系统高可用性保障是目前企业普遍采用的方法。

医疗机构工作性质的特殊性要求其信息系统7天×24小时不间断运行，采用双机容错方案为系统提供了高可用解决方案。本文将对医疗信息系统的双机容错环境下Oracle数据库应用做详细介绍。

　　

　　系统配置

　　

　　　　该系统的硬件配置如下:

　　

　　　　主数据库服务器: 富士通Primergy MS-610服务器(双Xeon 700MHz CPU，1GB内存)。

　　　　数据库备份服务器: 富士通Team Server C870ie GP5C875(双PentiumⅢ 700MHz CPU，1GB内存)。

　　　　容错软件: 天地公司的SLHA 3.0软件包。

　　　　磁盘阵列: IQstore R1500(带2个SCSI接口)。

　　　　线路连接: 2台服务器用RS-232串口线和RJ-45网络线相连(如图1所示)。

　　

　　

　　

　　　　软件配置如下。

　　　　操作系统: Windows NT Server 4.0

　　　　服务器软件配置: Windows NT 4.0 Service Pack 5、Internet Explorer 5.0、Microsoft Data Access Component 2.0，Oracle数据库为7.3.3企业版。

　　

　　　　双机容错的实现

　　

　　　　1. 操作系统的安装

　　

　　　　我们用A机表示数据库服务器，用B机表示备份数据库服务器。首先在物理上将所需硬件设备连接好，分别在各自服务器上安装Windows NT Server 4.0操作系统及补丁包等。然后，进入磁盘管理器，将磁盘阵列划分为2个逻辑盘D和E，此时2台服务器都可访问磁盘阵列。

　　

　　　　2.Oracle数据库的安装

　　

　　　　先关闭B机，在A机上安装Oracle数据库，安装路径默认为D盘，归档日志放在E盘。安装完毕后，将Oracle的3个服务(此处SID为ORCL,所以3个服务就是OracleServiceORCL、OracleStartORCL和OracleTNSlistener)的启动方式改为手动并将此3个服务停止。注意: 改为手动的目的是为了让这3个服务由双机容错软件来启动,而不是由操作系统启动。

　　

　　　　然后，关闭A机，启动B机，格式化D盘，将刚刚由A机建立在磁盘阵列上的Oracle目录也格式化掉; 在B机上安装Oracle数据库，安装路径默认为D盘，安装完毕，同样将Oracle的3个服务的启动方式改为手动并停止3个服务。

　　

　　　　双机上安装Oracle的实质就是将Oracle系统分别装在2台服务器上，而数据只存储在磁盘阵列上。

　　

　　　　3.双机容错软件的安装及双机容错环境的建立

　　

　　　　双机容错软件的安装非常简单，只需启动A机和B机，在2台服务器上分别安装该软件即可。建立双机容错环境是将磁盘阵列上的D盘和E盘以及Oracle 的3个服务交由双机容错软件控制，并由双机容错软件进行切换。

　　

　　　　在双机容错软件SLHA的"Configuration"选项中将数据库服务器设为Active状态，即平时正常工作状态时，此时数据库服务器工作，备份服务器等待。当A机Active时，只有A机可以访问磁盘阵列，B机不能访问磁盘阵列。此时，Oracle数据库服务器实际上是A机，A机的IP地址就是Active IP Address，同时A机的主机名为Active Host Name; 当A机因故不能工作时，A机的状态会被"心跳线"侦测到，这时B机开始切换到Active状态，接管磁盘阵列，此时的Oracle数据库服务器改为B机，B机的IP地址就是Active IP Address，同时B机的主机名为Active Host Name。上述操作均由系统自动完成，实践证明切换所需的时间很快，对客户端的影响很小。

　　

　　　　需要注意的问题

　　

　　　　1．当在A机安装完Oracle数据库后在B机安装Oracle数据库时，一定要先将磁盘阵列D盘格式化，而不是只将D盘中已由A机安装的Oracle数据库删除，否则可能会出现意想不到的错误，例如Oracle侦听服务失败等;

　　

　　　　2．最终安装好Oracle数据库后，要对D:/Oracle/Orant/network/Admin/ Listener.ora文件进行修改，其中Server名称一定要改为Active host name Alias,如不进行修改将使客户端的Oracle数据库用户无法连接到Oracle数据库中。

　　

　　　　3．在Hosts文件中增加一条记录，使Active IP Address和Active Host Name相互对应，这样系统就会自动起到解析作用。Hosts文件位于c:/Winnt/ system32/drivers/etc目录下。

　　

　　　　4．要注意不到万不得已，不要强行切换，避免产生数据错误。如必须对双机进行切换，可先进入Svrmgr Oracle服务器控制台,用Shutdown命令关闭Oracle数据库，再进行切换。

　　　　双机容错的原理

　　

　　　　Oracle数据库安装在磁盘阵列上(即图2中Public Drives)，2台服务器都可以访问它，但不能同时访问。Oracle Server for NT主要提供3个服务：OracleServiceSID、OracleStartSID和OracleTNSlistener。在数据库服务器正常工作时，由数据库服务器控制磁盘阵列柜，此时只有该服务器可以访问磁盘阵列，该服务器上的Oracle服务处于启动(Active)状态，此时该服务器就扮演图2中Active Server的角色，备份服务器处于等待(Standby)状态，即图2中Backup Server。

　　

　　　　当数据库服务器发生故障不能工作时，双机容错系统会检测到数据库服务器的状态，从而使备份服务器自动激活，接管磁盘阵列并自动启动Oracle的3个服务，而对于客户端来说，只经历一个短暂的服务器重启过程，访问的数据仍是磁盘阵列中的数据。

　　

　　　　注意: 是双机容错软件而不是操作系统来控制Oracle 的启动和停止，即由双机容错软件来控制这3个Oracle服务的启动和停止，实现Oracle数据库在双机之间的切换