在11g里面,随着ASM、RAC、Data Guard(包括Active Data Guard)的成熟,使用RAC+ASM+Data Guard越来越成为一种可靠的、维护简单、稳定的高可用性和容灾保护方案。这篇文章谈谈如何管理Oracle 11g Data Guard环境中的归档日志。

归档日志是重要的,不然就不必提到这篇文章,备份恢复需要它,而Data Guard也需要它。在早期版本的Data Guard环境中,常常面临着归档日志管理问题。在Data Guard环境里面,对归档日志管理需要达到以下几个方面的要求或者说是需求:

  • 不能够随意删除掉归档日志,归档日志丢失会导致Data Guard需要重新搭建。
  • 不能随意使用RMAN删除归档日志,否则同样会导致Data Guard需要重新搭建。
  • 在使用RMAN备份后,如果归档没有被传送或应用到备库上,那么RMAN不应该删除归档日志,否则Data Guard需要的归档就必须从备份里面还原出来,增加了维护工作量。
  • 对RMAN的备份脚本没有特别的要求,否则脚本意外改动,可能会导致Data Guard需要的归档日志被删除。
  • 归档应尽量保存在磁盘上,以避免Data Guard长时间维护时归档被删除。
  • 备库的归档日志不需要花精力去维护,自动删除已经应用过的归档日志。

幸运的是,在11g环境里面,上述的几点很容易就满足,那就是只需要做到以下几点。

  • 使用快速恢复区(fast recovery area),在10g版本的文档中称为闪回恢复区(flash recovery area),老实说,一直不太明白为什么取名叫闪回恢复区,难道是因为10g有了数据库闪回功能?在RAC中,毫无疑问快速恢复区最好是置放在ASM上。
  • 为快速恢复区指定合适的空间。首先我们需要预估一个合理的归档保留时间长。比如由于备份系统问题或Data Guard备库问题、维护等,需要归档保留的时间长度。假设是24小时,再评估一下在归档量最大的24小时之内,会有多少量的归档?一般来说是在批量数据处理的时候归档量最大,假设这24小时之内归档最大为200G。注意对于RAC来说是所有节点在这24小时的归档量之和。最后为快速恢复区指定需要的空间量,比通过参数db_recovery_file_dest_size指定快速恢复区的大小。这里同样假设快速恢复区们存放归档日志。
  • 在备库上指定快速恢复区以及为快速恢复区指定合适的大小,在备库上指定快速恢复区的大小主要考虑的是:切换成为主库后归档日志容量;如果主库归档容量压力大,备库能否存储更多的归档日志以便可以通过备库来备份归档日志。
  • 对主库和备份使用RMAN配置归档删除策略:CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

完成了上述几个步骤,那么归档管理的要求基本上就达到了。通过这样的设置,实现的效果如下:

  • 归档日志如果没有应用到备库,那么在RMAN中使用backup .... delete inputs alldelete archivelog all不会将归档日志删除。但但是请注意如果是使用delete force命令则会删除掉归档,不管归档有没有被应用到备库。
  • 如果归档日志已经应用到了备库,那么在RMAN中使用backup .... delete inputs alldelete archivelog all可以删除归档日志,在正常情况下,由于归档日志可能很快应用到Data Guard,所以在RMAN备份之后可以正常删除归档日志。RMAN也不需要使用特别的备份脚本,也不必担心人为不小心使用。delete archivelog all命令删除了归档。
  • 备库的归档日志存储到快速恢复区中,备库的快速恢复区空间紧张时,会自动删除已经应用过的较早的归档日志以释放空间,这样便可以实现备库的归档日志完全自动管理。
  • 如果由于备份异常或Data Guard异常,在快速恢复区空间紧张时,Oracle在切换日志时,会自动删除掉已经应用过的归档日志,以释放空间。但是如果归档日志没有应用到Data Guard,那么归档日志不会被删除。这种情况下,快速恢复区的归档可能会增加到空间耗尽,最后就会出现数据库不能归档,数据库挂起的问题。

注意上面最后一点,当快速恢复区空间紧张时,Oracle开始删除归档日志,删除的条件还包括归档日志已经应用到备库,这种情况下如果归档日志还没有备份,也会被删除掉。这里的问题是,文档中描述的快速恢复区空间紧张,具体是指什么时间?也就是快速恢复区的空间消耗多少百分比的时候才算是空间紧张?在MOS文章《Files being deleted in the flash recovery area, messages in the alert log Deleted Oracle managed file <filename> (Doc ID 1369341.1)》里面有提到,空间使用率达到80%以后就开始删除文件(归档日志)。

Oracle在往快速恢复区存储文件时,其步骤大概是这样的:Oracle估计需要的空间大小(切换日志时就是归档日志大小),然后将这个大小与当前的占用空间大小相加,看是否超过了80%,如果超过了,那么就回收空间(回收的空间应大于等于新建文件需要的空间大小,也就是回收的空间以够用为原则)。如果不能回收空间(比如归档日志没有被应用到备库),那就只能继续占用新的空间,直到空间耗尽。

这里的问题是,假设快速恢复区设定了200G空间,那么在使用到80%,也就是160G的时候就开始回收空间。那么我们在估算空间时,就应该上浮20%。比如我们要求保留24小时归档,这24小时之内归档量最大是200G,那么我们应该为快速恢复区设置240G左右的容量。

那么,这个80%的比率能够更改吗以便延迟Oracle删除归档日志的时间吗?答案是肯定的。没有相应的数据库参数来设定,但是可以通过事件来设置,事件号是19823:

oerr ora 19823
19823, 00000, "soft limit recovery area space pressure percentage"
// *Document: NO
// *Cause: Set on all instances to alter recovery area space pressure
//         trigger percentage.
// *Action: level 1 to 100 indicates the percentage when the space
//          pressure has to be triggered.

下在是一个测试:
测试环境:主库是Oracle 11.2.0.3 for Linux两节点RAC,备库是Oracle 11.2.0.3 for linux单实例库。测试是在主库的节点1上进行的,其在线日志大小为512MB,快速恢复区指定的大小为16GB。
当前主库的FRA(快速恢复区)的使用率已经接近于80%:

select * from V$RECOVERY_AREA_USAGE;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               0
REDO LOG                          15.33                         0              13
ARCHIVED LOG                      64.04                     63.81              45
BACKUP PIECE                        .24                         0               1
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0

在主库上创建一个表,并插入一行数据:

create table t1 ( id number, name char(1000)) tablespace TBS_USERS;
insert into t1 values (1,'a');

然后执行下在的测试代码:

begin
  for i in 1..300000 loop
    update t1 set name=to_char(i);
  end loop;
  commit;
end;
/

这段代码产生的日志量大约在690MB左右:

SQL> select * from v$sesstat where sid=155 and statistic#=178;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       155        178  696908584

多次执行上述代码,发现FRA(快速恢复区)的空间使用率基本上在80%左右。alert日志也有相应的删除较早的归档日志的信息:

Thu Jan 02 12:28:50 2014
Thread 1 advanced to log sequence 981 (LGWR switch)
  Current log# 12 seq# 981 mem# 0: +DATA1/ractest/onlinelog/group_12.299.835542549
  Current log# 12 seq# 981 mem# 1: +DG_FLA/ractest/onlinelog/group_12.298.835542551
Thu Jan 02 12:28:50 2014
LNS: Standby redo logfile selected for thread 1 sequence 981 for destination LOG_ARCHIVE_DEST_2
Thu Jan 02 12:28:50 2014
Deleted Oracle managed file +DG_FLA/ractest/archivelog/2014_01_02/thread_2_seq_309.424.835783855
Deleted Oracle managed file +DG_FLA/ractest/archivelog/2014_01_02/thread_1_seq_947.426.835783855
Deleted Oracle managed file +DG_FLA/ractest/archivelog/2014_01_02/thread_1_seq_948.437.835784237
Archived Log entry 2645 added for thread 1 sequence 980 ID 0xc8804744 dest 1:

上面的日志也可以看到其过程是:切换日志;删除不需要的最老的归档日志;生成新的归档日志。

现在我们利用事件19823将这个比率调到95%看看会是什么样子:

SQL> alter system set event='19823 trace name context forever,level 95' scope=spfile sid='*';

然后重启主库。再运行上面的测试代码,发现Oracle不再删除归档日志,而是到接近95%的空间使用率时再开始删除归档日志:

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               0
REDO LOG                          15.33                         0              13
ARCHIVED LOG                      68.99                     65.72              49
BACKUP PIECE                        .24                         0               1
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0

.............

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               0
REDO LOG                          15.33                         0              13
ARCHIVED LOG                      78.62                      59.9              55
BACKUP PIECE                        .24                         0               1
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0

从上面的最后一次对v$recovery_area_usage的查询数据可以看到,此时空间利用率达到了94.19%,离95%已经很接近(在线日志的大小是512MB,占快速恢复区的3.1%,如果在快速恢复区里面多一个文件就会超过95%)。

接下来我们将这个比率调整到50%,看看是什么结果:

SQL> alter system set event='19823 trace name context forever,level 50' scope=spfile sid='*';

然后重启主库。再运行上面的测试代码,发现Oracle在删除归档日志,但是每次均删除的日志只需要容纳要新增的文件即可,不会一下子删除到使利用率到50%以下:

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               0
REDO LOG                          15.33                         0              13
ARCHIVED LOG                      72.47                     48.57              54
BACKUP PIECE                        .24                         0               1
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0

然后一直使用alter system switch logfile命令,每执行一次,Oracle会删除一个归档日志,到最后快速恢复区的空间利用率到接近于50%。

Thu Jan 02 12:56:29 2014
Thread 1 advanced to log sequence 1004 (LGWR switch)
  Current log# 12 seq# 1004 mem# 0: +DATA1/ractest/onlinelog/group_12.299.835542549
  Current log# 12 seq# 1004 mem# 1: +DG_FLA/ractest/onlinelog/group_12.298.835542551
Thu Jan 02 12:56:30 2014
Deleted Oracle managed file +DG_FLA/ractest/archivelog/2014_01_02/thread_1_seq_963.317.835788195
Thu Jan 02 12:56:30 2014
LNS: Standby redo logfile selected for thread 1 sequence 1004 for destination LOG_ARCHIVE_DEST_2
Archived Log entry 2703 added for thread 1 sequence 1003 ID 0xc8804744 dest 1:


FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               0
REDO LOG                          15.33                         0              13
ARCHIVED LOG                      33.29                     28.86              65
BACKUP PIECE                        .24                         0               1
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0   

因此,我们可以了解event 19823的用途。对于空间容量比较小的主机,但是希望归档能够尽量保留在快速恢复区,以便留有足够的备份时间窗口,那么可以考虑把这个百分比调整到更大,比如90%,95%等。

Oracle数据库在安装了2012年1月发布的CPU或PSU补丁之后,经常出现下面一些现象:

  • 应用出现ORA-19706: invalid SCN错误。
  • 在alert日志中出现类似于:
    Wed May 30 15:09:57 2012
    Advanced SCN by 68093 minutes worth to 0x0ba9.4111a520, by distributed transaction remote logon, remote DB:xxxx.
    Client info : DB logon user xxxx, machine xxxx, program oracle@xxxx (J001), and OS user oracle
    这样的警告。
  • 在alert日志中出现类似于:
    Wed May 30 12:02:00 2012
    Rejected the attempt to advance SCN over limit by 166 hours worth to 0x0ba9.3caec689, by distributed transaction remote logon, remote DB: xxxx.
    Client info : DB logon user xxxx, machine xxxx, program oracle@xxxx (J000), and OS user oracle
    这样的错误信息。
  • 在alert日志中出现类似于:
    Sat Mar 17 05:57:45 2012
    ALTER DATABASE OPEN
    ************************************************************
    Warning: The SCN headroom for this database is only 38 days!
    ************************************************************
    这样的信息。
  • 在MOS文档《ORA-19706 and Related Alert Log Messages [ID 1393360.1]》中还提到其他会出现在alert中的一些警告信息:
    Warning - High Database SCN: Current SCN value is 0x0b7b.0008e40b, threshold SCN value is 0x0b75.055dc000, If you have not previously reported this warning on this database, please notify Oracle Support so that additional diagnosis can be performed.
    WARNING: This patch can not take full effect until this RAC database has been completely shutdown and restarted again.Oracle recommends that it is done at the earliest convenience.

如果说以上的现象只是警告或应用级报错,影响范围有限,那么不幸的是如果遇到RECO进程在恢复分布式事务时遇到SCN问题,则可能使数据库宕掉,例如:


Wed May 30 14:44:02 2012
Errors in file /oracle/admin/miboss/bdump/xxxx_reco_225864.trc:
ORA-19706: invalid SCN
Wed May 30 14:44:02 2012
Errors in file /oracle/admin/miboss/bdump/xxxx_reco_225864.trc:
ORA-00600: internal error code, arguments: [18348], [0x000000000], [485331304561], [], [], [], [], []
.........
RECO: terminating instance due to error 476
Intance terminated by RECO, pid s= 225864

那么2012年1月发布的CPU或PSU补丁到底使数据库在SCN处理方面产生了什么样的变化?这种变化对数据库有什么危害吗?甚至于说,以上提示的信息是由于这个补丁的BUG引起的吗?

要回答这些问题,得先从SCN讲起。SCN可以说是Oracle中的很基础,但同时也是很重要的东西,它是一个单向增长的“时钟”,广泛应用于数据库的恢复、事务ACID、一致性读还有分布式事务中。那么除了这些,SCN还有以下一些知识点:

  • SCN的内部存储方式:在Oracle内部,SCN分为两部分存储,分别称之为scn wrap和scn base。实际上SCN长度为48位,即它其实就是一个48位的整数。只不过可能是由于在早些年通常只能处理32位甚至是16位的数据,所以人为地分成了低32位(scn base)和高16位(scn wrap)。为什么不设计成64位,这个或许是觉得48位已经足够长了并且为了节省两个字节的空间:)。那么SCN这个48位长的整数,最大就是2^48(2的48次方, 281万亿,281474976710656),很大的一个数字了。
  • Maximum Reasonable SCN:在当前时间点,SCN最大允许达到(或者说最大可能)的SCN值。也称为Reasonable SCN Limit,简称RSL。这个值是一个限制,避免数据库的SCN无限制地增大,甚至达到了SCN的最大值。这个值大约是这样一个公式计算出来的:(当前时间-1988年1月1日)*24*3600*SCN每秒最大可能增长速率。当前时间减1988年1月1日的结果是天数,24表示1天24小时,3600表示1小时3600秒。不过这个公式里面“当前时间-1988年1月”部分并不是两个时间直接相减,而是按每月31天进行计算的(或许是为了计算简单,因此在Oracle内部可能要频繁地计算,这个计算方法可以在安装了13498243这个补丁后得到的scnhealthcheck.sql文件中看到,《Installing, Executing and Interpreting output from the "scnhealthcheck.sql" script [ID 1393363.1]》这篇MOS文档解释了这个脚本的使用及对结果的解释,实际上直接看脚本代码更为清楚)。那么SCN最秒最大可能增长速率是多少呢,这个跟Oracle版本有一定的关系,在11.2.0.2之前是16384(即16K),在11.2.0.2及之后版本是32768(即32K)。在11.2.0.2的版本中有一个隐含参数,_max_reasonable_scn_rate,其默认值就是32768(不建议调整这个值)。如果按16K的最大值,SCN要增长到最大,要超过500年。
  • SCN Headroom:这个是指Maximum Reasonable SCN与当前数据库SCN的差值。在alert中通常是以“天”为单位,这个只是为了容易让人读而已。天数=(Maximum Reasonable SCN-Current SCN)/16384/3600/24。这个值就的意思就是,如果按SCN的每大增长速率,多少天会到达Maximum Reasonable SCN。但实际上即使如此,也不会到达Maximum Reasonable SCN,因为到那时Maximum Reasonable SCN也增大了(越时间增大),要到达Maximum Reasonable SCN,得必须以SCN最大可能速率的2倍才行。
  • SCN的异常增长:通常来说,每秒最大允许的16K/32K增长速率已经足够了,但是不排除由于BUG,或者人为调整导致SCN异常增长过大。特别是后者,比如数据库通过特殊手段强制打开,手工把SCN递增得很大。同时Oracle的SCN会通过db link进行传播。如果A库通过db link连接到B库,如果A库的SCN高于B库的SCN,那么B库就会递增SCN到跟A库一样,反之如果A库的SCN低于B库的SCN,那么A库的SCN会递增到跟B库的SCN一样。也就是说,涉及到db link进行操作的多个库,它们会将SCN同步到这些库中的最大的SCN。
  • 那么,如果是数据库本身操作而不是通过db link同步使得SCN的增长,其增长速率如何判断呢,这个可以通过系统的统计量“calls to kcmgas”和"DEBUG calls to kcmgas"来得到。kcmgas的意思是get and advance SCN,即获取并递增SCN。
  • 在两个库通过db link进行分布式事务时,假设B库的SCN值要高于A库的SCN,因此要将B库的SCN增同步到A库,但是如果B库的SCN过高,这样同步到A库之后,使得A库面临Headroom过小的风险,那么A库会拒绝同步SCN,这个时候就会报ORA-19706: Invalid SCN错误。分布式事务,或者说是通过db link的操作就会失败,即使是通过db link的查询操作。这里显然有一个阈值,如果递增SCN使得Headroom过小到什么值时,就会拒绝递增(同步)SCN?目前来看是这样:如果打了2012年1月CPU或PSU补丁,11.2.0.2及以后的版本,是1天即24小时,其他版本是31天即744小时,打了补丁之后可以由隐含参数_external_scn_rejection_threshold_hours来调整。而没有打补丁的情况下,视同此参数设为0,实际最小为1小时。由于Oracle 9.2.0.8没有了最新的补丁集,显示也不会有这个参数,保持默认为1小时。注意这是一个静态参数。所以打了2012年1月CPU或PSU补丁的一个重要变化是增加了_external_scn_rejection_threshold_hours参数,同时使11.2.0.2以下版本的数据库其Headroom的阈值增得较大。这带来的影响就是ORA-19706的错误出现的概率更高。解决的办法将_external_scn_rejection_threshold_hours这个隐含参数设置为较小的值,推荐的值是24,即1天。从_external_scn_rejection_threshold_hours这个参数名的字面意思结合它的作用,可以说这个参数就是”拒绝外部SCN“的阈值。对于数据库自身产生的SCN递增是没有影响的。
  • 虽然11.2.0.2及之后的版本,其默认的每秒最大可能SCN增长速率为32K,这使得Maximum Reasonable SCN更大,也就是说其SCN可以增长到更大的值。那也就是可能会使11.2.0.2的库与低版本的数据库之间不能进行db link连接。或者是11.2.0.2的库不能与16K速率的(比如调整了_max_reasonable_scn_rate参数值)的11.2.0.2的库进行db link连接。

现在是时候来回答以下几个问题了:

  • 2012年1月后发布的CPU或PSU补丁到底使数据库在SCN处理方面产生了什么样的变化?答案是:增加了_external_scn_rejection_threshold_hours参数,11.2.0.2及以上版本的这个参数默认值是24,其他版本默认值是744。这样使11.2.0.2以下版本的数据库其Headroom的阈值增得较大。
  • 这种变化对数据库有什么危害吗?答案是:在一个具有很多系统的大型企业环境里面,db link使用很多,甚至有一些不容易管控到的数据库也在跟关键系统通过 db link进行连接,在这样的环境中,过高的SCN扩散到关键系统,而系统如果打了这个补丁,其Headroom阈值变大,那么就更容易出现ORA-19706错误,对db link依赖很严重的系统可能会导致业务系统问题,严重情况下甚至会宕库。不过通过设置隐含参数_external_scn_rejection_threshold_hours可解决这样的问题。所以,如果你安装了2012年1月的CPU或PSU补丁,请尽快设置此参数为建议的值24,极端情况下你可以设置为1。
  • alert中的那些提示或警告信息是BUG引起的吗?答案是:这些提示或警告不是BUG引起的。它只是提醒你注意SCN过高增长,或者是你的Headroom较小(在Headroom小于62天时可能会提醒),引起你的重视。实际上根据MOS文档《System Change Number (SCN), Headroom, Security and Patch Information [ID 1376995.1]》的说法,这个补丁修复了SCN相关的一些BUG。如果非要说BUG,可以勉强认为补丁安装后新增的参数_external_scn_rejection_threshold_hours其默认值过大。Bug 13554409 - Fix for bug 13554409 [ID 13554409.8]就是说的这个问题。不过这个问题已经在2012年4月的CPU或PSU补丁中得到修复。

在最后我们来解读一下alert日志中的一些信息:

  • 信息:
    Wed May 30 15:09:53 2012
    Completed crash recovery at
    Thread 1: logseq 3059, block 19516, scn 12754630269552
    2120 data blocks read, 2120 data blocks written, 19513 redo blocks read
    .....
    Wed May 30 15:09:57 2012
    Advanced SCN by 68093 minutes worth to 0x0ba9.4111a520, by distributed transaction remote logon, remote DB:xxxx.
    Client info : DB logon user xxxx, machine xxxx, program oracle@xxxx (J001), and OS user oracle
    这里是说,SCN向前(跳跃)递增了68098分钟,其递增后的SCN是0x0ba9.4111a520。注意这里的分钟的计算就是根据SCN每秒最大可能增长速率为16K来的。我们计算一下:
    0x0ba94111a520转换成10进制12821569053984。
    在alert日志中,这个信息是刚打开数据库的时候,所以 crash recovery完成时的scn可以做为近似的当前SCN,其值为12754630269552:
    (12821569053984-12754630269552)/16384/60=68093.65278320313
    这里16384值的是SCN每秒最大可能增长速率,可以看到计算结果极为接近。

    我们再来计算一下这个SCN的headroom是多少:

    SQL>    select
      2     ((((
      3      ((to_number(to_char(cur_date,'YYYY'))-1988)*12*31*24*60*60) +
      4      ((to_number(to_char(cur_date,'MM'))-1)*31*24*60*60) +
      5      (((to_number(to_char(cur_date,'DD'))-1))*24*60*60) +
      6      (to_number(to_char(cur_date,'HH24'))*60*60) +
      7      (to_number(to_char(cur_date,'MI'))*60) +
      8      (to_number(to_char(cur_date,'SS')))
      9      ) * (16*1024)) - 12821569053984)
     10     / (16*1024*60*60*24)
     11     ) headroom
     12     from (select to_date('2012-05-30 15:09:57','yyyy-mm-dd hh24:mi:ss') cur_date from dual);
    
      HEADROOM
    ----------
    24.1496113
    

    可以看到结果为24天,由于这个时候_external_scn_rejection_threshold_hours参数值为24,即1天,所以虽然有这么大的跳跃,但SCN仍然增长成功。

  • 信息:
    Wed May 30 12:02:00 2012
    Rejected the attempt to advance SCN over limit by 166 hours worth to 0x0ba9.3caec689, by distributed transaction remote logon, remote DB: xxxx.
    Client info : DB logon user xxxx, machine xxxx, program oracle@xxxx (J000), and OS user oracle
    在这个信息中,拒绝了db link引起的SCN增加。计算一下这个SCN的headroom:
    0x0ba93caec689转换成10进制是12821495465609
    当前时间是2012-05-30 12:02:00,

    SQL>    select
      2     ((((
      3      ((to_number(to_char(cur_date,'YYYY'))-1988)*12*31*24*60*60) +
      4      ((to_number(to_char(cur_date,'MM'))-1)*31*24*60*60) +
      5      (((to_number(to_char(cur_date,'DD'))-1))*24*60*60) +
      6      (to_number(to_char(cur_date,'HH24'))*60*60) +
      7      (to_number(to_char(cur_date,'MI'))*60) +
      8      (to_number(to_char(cur_date,'SS')))
      9      ) * (16*1024)) - 12821495465609)
     10     / (16*1024*60*60*24)
     11     ) headroom
     12     from (select to_date('2012-05-30 12:02:00','yyyy-mm-dd hh24:mi:ss') cur_date from dual);
    
      HEADROOM
    ----------
    24.0710752
    

    由于这个时候_external_scn_rejection_threshold_hours参数值为744,即31天,计算出的headroom在这个阈值之内,因此拒绝增加SCN。
    (31-24.0710752)*24=166.2941952,正好是166小时。

--update on 2012/6/2--
实际上2012年1月的CPU或PSU补丁之后还会有下面的变化:

  1. _minimum_giga_scn这个隐含没有了,可惜了这个手工增加SCN的利器。
  2. 11.2.0.2及之后的版本,从原来的32K SCN最大速率调整回了16K速率。可以用下面的SQL来得到结果:
    SQL&gt select decode(bitand(DI2FLAG,65536),65536,'Y','N') using16 
      2   from x$kccdi2;
    
    U
    -
    Y
    

    上面的SQL的结果只有在11.2.0.2及以上版本才有意义,结果为Y,表示使用的是16K的速率,否则是使用32K速率。

本文涉及的一些参数,和SCN的一些算法,可能会随着版本或补丁的变化而产生较大的变化。

important update: 实际上在Jan 2012的PSU/CPU补丁中存在较大的SCN BUG,目前已经不建议打这个补丁集,而是打到更高的PSU补丁集上。

在某些环境下,运行11.2版本的RAC数据库的服务器上,连接了多个public网络,那么就会有如下的需求:

  1. 给其他的,或者说是新的public网络增加新的VIP地址。
  2. 在新的public网络上增加SCAN VIP.
  3. 在新的VIP地址上配置监听
  4. 在新的SCAN VIP地址上配置监听

实际上从11.2版本开始,grid infrastructure(以下可能会简写为GI)就是一个完整的clusterware,不仅仅是用于数据库,也可以用作应用软件的高可用性。上述的4个需求中,第1个是毫无疑问需要的;第2个需求对运行在grid infrastructure上的应用是必要的;第3个需求在运行在grid infrastructure上的数据库来说是必要的;第4个需求,是针对数据库的,这个需求有点难度,因为在一套grid infrastructure中只能允许有一个SCAN LISTENER的存在,由于可以不通过SCAN IP而是直接通过VIP地址连接数据库,所以这个新的SCAN监听可以不需要,但是为了完整,我们可以用变通的方法来实现。

下面我们用一个实例来演示如何实现上述的所有需求。

先简单介绍一下用于演示的环境:11.2.0.1 grid infrastructure for linux 32bit,共2个节点,节点名是xty3和xty4,没有使用DNS(实际上在有DNS的环境下,也不太可能会有第2个public network),所以下面的演示是以没有DNS来配置的。Grid Infrastructure用的是oracle用户安装的(实际生产建议用单独的grid用户),而RDBMS软件也是用oracle安装的。目前的private network是192.168.153.0,对应的网卡设备名是en1,public network是192.168.0.0,对应的网卡设备名是en0,打算新增加的public network是192.168.76.0,对应的网卡设备名是en2。

先看看/etc/hosts文件的内容:

127.0.0.1    localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6
192.168.0.134   xty3
192.168.0.144   xty4
192.168.153.134 xty3-priv
192.168.153.144 xty4-priv
192.168.0.135   xty3-vip
192.168.0.145   xty4-vip
192.168.0.155   xty-cluster-svip1

第1步,配置好新的public network的IP地址和网络路由。注意新的public network IP地址配好后,注意配置好网络路由。

第2步,在所有的节点上编辑/etc/hosts文件,增加如下内容:

192.168.76.134   xty3-pub2
192.168.76.144   xty4-pub2
192.168.76.135   xty3-vip2
192.168.76.145   xty4-vip2
192.168.76.155   xty-cluster-svip2

192.168.76.134和192.168.76.144是两个节点上新的public network的public ip,192.168.76.135和192.168.76.145是相对应的VIP地址,而192.168.76.155则是准备要在新的public network上增加的SCAN IP。而xty-cluster-svip2则是新增的SCAN名字。

第3步,增加新的public network上的vip地址,这一步比较简单,用root用户执行下面的命令:

srvctl add vip -n xty3 -k 2 -A xty3-vip2/255.255.255.0/eth2
srvctl add vip -n xty4 -k 2 -A xty4-vip2/255.255.255.0/eth2

在上述命令中,-n参数后面跟的是节点名(hostname); -k后面的参数表示第几个网络,这里是2,表示第2个public network。-A后面就是VIP地址的属性了,格式为"VIP地址名/子网/网卡设备名"。

然后用crsctl来检查,vip资源已经存在:

[root@xty3 ~]# crsctl stat res -t | egrep "vip|network"
ora.net1.network
ora.net2.network
ora.scan1.vip
ora.xty3-vip2.vip
ora.xty3.vip
ora.xty4-vip2.vip
ora.xty4.vip

[root@xty3 ~]# srvctl config vip -n xty3
VIP exists.:xty3
VIP exists.: /xty3-vip2/192.168.76.135/255.255.255.0/eth2
VIP exists.:xty3
VIP exists.: /xty3-vip/192.168.0.135/255.255.255.0/eth0

[root@xty3 ~]# srvctl config vip -n xty4
VIP exists.:xty4
VIP exists.: /xty4-vip2/192.168.76.145/255.255.255.0/eth2
VIP exists.:xty4
VIP exists.: /xty4-vip/192.168.0.145/255.255.255.0/eth0

ora.net2.network,ora.xty3-vip2.vip,ora.xty4-vip2.vip都是新增的资源。然后使用下面的命令启动新增的VIP:

Read the rest of this entry

,

在以前我写的一篇文章《Oracle与防火墙》中提到,网络防火墙会切断长时间空闲的TCP连接,这个空闲时间具体多长可以在防火墙内部进行设置。防火墙切断连接之后,会有下面的可能:

  • 切断连接之前,连接对应的Oracle会话正在执行一个耗时特别长的SQL,比如存储过程而在此过程中没有任何数据输出到客户端,这样当SQL执行完成之后,向客户端返回结果时,如果TCP连接已经被防火墙中断,这时候显然会出现错误,连接中断,那么会话也就会中断。但是客户端还不知道,会一直处于等待服务器返回结果的状态。
  • 切断连接之前,Oracle会话一直处于空闲状态,在防火墙中断之后,客户端向Oracle服务器提交SQL时,由于TCP连接已经中断,这时客户端侦测到连接中断,那么客户端就会报ORA-3113/ORA-3114这类错误,然后会话中断。但是在Oracle服务器端,会话一直在处于等待客户端消息的状态。
  • 当然,如果服务器和客户端都一直闲着,没有任何消息往来,那么客户端和客户端的会话就一直存在,直到客户端发送消息即提交SQL到Oracle服务器。

从上面的前面2种情况来看,防火墙切断数据库TCP连接,引起的后果就会有:

  • 客户端报ORA-3113/3114错误,对这于长连接的后台应用不是个好事,特别是对那种C/C++开发的后台应用没有重连机制,就会出现问题或者程序退出。如果应用在一个较长的时间内没有任何活动,而这个时间超过了防火墙的设置,那应用的连接被中断。
  • 对于有连接池或重连机制的应用,如果连接池过大,导致空闲连接过多,或者是防火墙的连接断开时间过短同时应用太闲,那么连接频繁地被中断,而在数据库服务器端,则连接越来越多,即会话数越来越多,甚至最终超过了数据为最大连接数。
  • 其他一些影响,比如你在跑一个脚本而长时间没有输出,结果防火墙切断了连接,你之前的工作就白做,当然这些影响范围都较小。

那么如何防止出现以上的问题? 对于没有重连机制的长连接应用,临时的解决办法是调大防火墙的连接切断时长;或者应用端在闲时定期执行一条类似于select 1 from dual这样的SQL;或者在Oracle服务端开启DCD功能,而DCD(死连接检测,即Dead Connection Detection)的时间长度短于防火墙的连接切断时长;或者使用操作系统的tcp keep-alive功能。对于有重连机制或有连接池的应用,为了避免数据库端连接数满,可以使用DCD或tcp keep-alive功能,如果DCD或TCP keep-alive的时间短于防火墙连接切断时长,那么连接将不会中断,因为防火墙视连接为活动状态(不过按MOS文档的说法,DCD的包有可能被防火墙忽略,即用DCD使连接保持活动状态可能没有作用)。而如果DCD或TCP keep-alive的时间长于防火墙连接切断时长,那么连接被中断的会话和进程将得到清理。

这里简单介绍一下TCP keep-alive,顾名思议,就是让TCP连接保持存活状态,这是由TCP协议层实现的功能,也是在TCP连接空闲时间超过设置的时间,就会发送探测包,因此,这个功能跟Oracle的DCD是极为类似的,所不同的是,这是由协议层实现的功能,是不能通过trace应用进程来跟踪包的发送。

理论是完美的,而现实差距越很远。

下面是一个有关的案例。一套运行在AIX 5.3上的RAC数据库,数据库版本为10.2.0.4,这套数据库在sqlnet.ora文件中有设置sqlnet.expire_time=5,后果却是,数据库的连接数过几天就要满一次,防火墙设置的时间是8小时,也就是说只会切断空闲时间超过8小时的连接,这个时间已经足够长,晚上系统太闲,总会有部分连接空闲时间过长,但是由于设置了DCD,连接应该不会被防火墙切断,而即使防火墙切断了连接,那么在DCD的作用下,数据库服务器端的会话和进程也应该被清理才是,即使DCD不起作用,那TCP keep-alive也应该起作用才是,那为什么会话和进程数越来越多,甚至达到了最大进程数或会话数呢?

检查应用服务器(weblogic)的连接池,并核对应用服务器到数据库的TCP连接,发现连接池监控中的连接数量并不多,而对比应用服务器上看到的到数据库数据库的TCP连接数量和数据库数据器上看到的应用服务器建立过来的TCP连接数量,二者之间差距非常大,前者远远小于后者,就是说,大量的连接已经被切断,应用服务器的连接已经退出,但是数据库端的连接还没有释放。从weblogic的日志来看,经常出现类似于连接失效并建立新的数据库连接这样的信息。

DCD为什么没有起作用,是不是DCD的BUG?DCD的确是有很多BUG,但是完全不工作的BUG应该是很少的。将sqlnet.expire_time设为1,5以及其他值,都不起作用,甚至重启过主机都没有起作用(当然并不是专门为了DCD而重启主机)。

使用truss命令来跟踪Oracle进程:


SQL> host
$ truss -p 828090
kread(0, 0x0000000000000000, 0) (sleeping...)
...很长时间没有任何反应...
^CPstatus: process is not stopped
$ 
$ exit

SQL> oradebug setospid 828090
Oracle pid: 247, Unix process pid: 828090, image: oracle@db2
SQL> oradebug short_stack 
ksdxfstk+002c<-ksdxcb+04e4<-sspuser+0074<-000044C0<-nttrd+0120<-nsprecv+07a0<-nsrdr+0114<-nsdo+1710<-nsbrecv+0040<-nioqrc+04a8<-opikndf2+0688<-opitsk+08a8<-opiino+0990<-opiodr+0ae0<-opidrv+0484<-sou2o+0090<-opimai_real+01bc<-main+0098<-__start+0098
SQL>  oradebug short_stack
ksdxfstk+002c<-ksdxcb+04e4<-sspuser+0074<-000044C0<-nttrd+0120<-nsprecv+07a0<-nsrdr+0114<-nsdo+1710<-nsbrecv+0040<-nioqrc+04a8<-opikndf2+0688<-opitsk+08a8<-opiino+0990<-opiodr+0ae0<-opidrv+0484<-sou2o+0090<-opimai_real+01bc<-main+0098<-__start+0098

没有看到DCD起任何作用的迹象,使用truss跟踪其他多个空闲Oracle也是如此。

那么TCP keep-alive呢,怎么样来判断有没有起作用?在AIX上其答案是使用kdb(这个需要root用户权限)。

在笔记本电脑上使用sqlplus连接到数据库,在数据库主机上使用netstat -Aan | grep 1521 | grep “你的IP地址”,以获得sqlplus连接的TCP连接信息,显示的第1列是一串16数据,然后使用kdb:

#kdb 
The specified kernel file is a 64-bit kernel
Preserving 1418178 bytes of symbol table
First symbol __mulh
           START              END <name >
0000000000001000 0000000003E5C050 start+000FD8
F00000002FF47600 F00000002FFDC940 __ublock+000000
000000002FF22FF4 000000002FF22FF8 environ+000000
000000002FF22FF8 000000002FF22FFC errno+000000
F100070F00000000 F100070F10000000 pvproc+000000
F100070F10000000 F100070F18000000 pvthread+000000
PFT:
PVT:
id....................0002
raddr.....0000000002000000 eaddr.....F200800090000000
size..............00080000 align.............00001000
valid..1 ros....0 fixlmb.1 seg....0 wimg...2
Command enhancement entry point is called.

 Welcome to VXDRV subcommands 
Command enhancement entry for vxodmdb called.called.

 Welcome to vxdrv subcommands 
(0)> sockinfo f100060009b04398 tcpcb | egrep "KEEP|opts"
    t_timer....... 00000000 (TCPT_KEEP)
    timewait.prv@0000000000000000  inp_v6opts  @0000000000000000  
    opts........ 0004 (REUSEADDR)   

可以看到,这个TCP连接没有KEEPALIVE选项(属性),keep-alive对应的timer(TCPT_KEEP)数值为0,表示没有设置timer时间。
会不会是DCD功能屏蔽了TCP keep-alive?
将sqlnet.ora中expire_time设置去掉,然后重复上述步骤,这次有了变化:

#kdb  
The specified kernel file is a 64-bit kernel
Preserving 1418178 bytes of symbol table
First symbol __mulh
           START              END <name >
0000000000001000 0000000003E5C050 start+000FD8
F00000002FF47600 F00000002FFDC940 __ublock+000000
000000002FF22FF4 000000002FF22FF8 environ+000000
000000002FF22FF8 000000002FF22FFC errno+000000
F100070F00000000 F100070F10000000 pvproc+000000
F100070F10000000 F100070F18000000 pvthread+000000
PFT:
PVT:
id....................0002
raddr.....0000000002000000 eaddr.....F200800090000000
size..............00080000 align.............00001000
valid..1 ros....0 fixlmb.1 seg....0 wimg...2
Command enhancement entry point is called.

 Welcome to VXDRV subcommands 
Command enhancement entry for vxodmdb called.called.

 Welcome to vxdrv subcommands 
(0)> sockinfo f100060009c1b398 tcpcb | egrep "KEEP|opts"
    t_timer....... 00000464 (TCPT_KEEP)
    timewait.prv@0000000000000000  inp_v6opts  @0000000000000000  
    opts........ 000C (REUSEADDR|KEEPALIVE)

可以看到,这个TCP连接已经有了KEEPALIVE选项(属性),同时keep-alive timer为16进制数464,即十进制1124,表示还有562秒(timer这里的时间以半秒为单位)就会发送keep-alive探测包。

反复通过加上DCD设置和去掉DCD设置进行测试,发现只要一加上DCD功能,那么Oracle进程就不会为TCP连接设置keep-alive选项(属性),反之则会设置keep-alive。从这里很明显地看出来,DCD功能开启后,不但没有起作用,还关闭了TCP keep-alive功能。

对于这套系统,最后去掉了DCD设置,并将所有的空闲连接清理掉,经过多天的观察,系统的连接数一直保持稳定,与应用服务器的实际连接数保持一致。

那是不是启用了DCD之后,TCP keep-alive就会被关闭,这里不能贸然下定论,不同的版本、不同的平台或许有差异,至少我一个同事在Linux上测试时,tcp keep-alive是共存的。

在诊断处理这个案例的过程之中,不得不提到另外一点,写在此处,与大家分享。在诊断问题的初期阶段,询问系统维护人员(通常连应用和数据库一起维护),应用服务器和数据库之间是否有防火墙,如果有,是怎么样设置的TCP连接断开时间。而维护人员最开始的回答是没有防火墙。从表面上看,也应该是没有防火墙。数据库服务器其中一个IP地址是192.168.8.42,而两台应用服务器服务器的IP地址是192.168.8.201和192.168.8.202,咋一看是在同一网段,中间应该不会有防火墙。然而仔细检查相关数据:

$ifconfig -a
....省略部分输出....
en4: flags=5e080867,c0<up ,BROADCAST,DEBUG,NOTRAILERS,RUNNING,SIMPLEX,MULTICAST,GROUPRT,64BIT,CHECKSUM_OFFLOAD(ACTIVE),PSEG,LARGESEND,CHAIN>
        inet 192.168.8.42 netmask 0xffffff80 broadcast 192.168.8.127
        inet 192.168.8.43 netmask 0xffffff80 broadcast 192.168.8.127
         tcp_sendspace 131072 tcp_recvspace 65536 rfc1323 0

可以看到,实际上数据库跟应用服务器是在不同的子网之中。这里子网掩码为0xffffff80,也就是我们通常写的子网掩码255.255.255.128,这样0-127为一个子网,128-255为一个子网(当然子网的第1个和最后一个地址都是不能使用的)。那么最后一个数字为201和202的两个IP地址显然就在第2个子网中,子网之间仍然需要有路由功能的网络设备,那么在之间有防火墙也是有可能的。用上面的方法去检查应用服务器的网络配置,发现其被划在了更小的子网之中。系统维护人员最终确认应用服务器和数据库之间的确是有防火墙存在。因此我们可以总结出两点:1,全面的知识对诊断数据库问题很有帮助。2, 如果经过详尽分析问题发现与别人说的不一致,需要坚持,并需要为自己的坚持寻找更多的数据来证实。

--The End--

,

关于全局索引和本地索引的优缺点,分别应该在什么情况下使用,这方面的资料很多,本文不作讨论。本文讨论一种特殊情况,即建立在分区键之上的本地索引。9i也算是很老的Oracle版本了,只是很多系统包括很多大型的核心的系统都在用,因此本文介绍建立在分区键列上的本地索引存在的问题。下面是一些测试:

SQL> create table t1 ( a int, b varchar2(300)) partition by range(a)
  2  (
  3   partition p01 values less than (1000),
  4   partition p02 values less than (2000),
  5   partition p03 values less than (3000),
  6   partition p04 values less than (4000),
  7   partition p05 values less than (5000),
  8   partition p06 values less than (6000),
  9   partition p07 values less than (7000),
 10   partition p08 values less than (8000),
 11   partition p09 values less than (9000),
 12   partition p10 values less than (10000),
 13   partition p11 values less than (11000),
 14   partition p12 values less than (12000),
 15   partition p13 values less than (13000),
 16   partition p14 values less than (14000),
 17   partition p15 values less than (15000),
 18   partition p16 values less than (16000),
 19   partition p17 values less than (17000),
 20   partition p18 values less than (18000),
 21   partition p19 values less than (19000),
 22   partition p20 values less than (20000)
 23  )
 24  /

表已创建。

SQL> insert into t1 select rownum,lpad('x',200,'x') from dual connect by rownum<20000;

已创建19999行。

SQL> commit;

提交完成。

SQL> insert /*+ append */ into t1 select * from t1;

已创建19999行。

SQL> commit;

提交完成。

SQL> insert /*+ append */ into t1 select * from t1;

已创建39998行。

SQL> commit;

提交完成。

SQL> insert /*+ append */ into t1 select * from t1;

已创建79996行。

SQL> commit;

提交完成。

SQL> insert /*+ append */ into t1 select * from t1;

已创建159992行。

SQL> commit;

提交完成。

SQL> insert /*+ append */ into t1 select * from t1;

已创建319984行。

SQL> commit;

提交完成。

首先建立一个测试范围分区表,分区键列是"a",共20个分区,在这个测试表中生成约64万行数据。下面在列a上建本地索引并收集统计信息:

SQL> create index t1_idx on t1(a) local;

索引已创建。

SQL> exec dbms_stats.gather_table_stats(user,'T1',
    method_opt=>'for all columns size 1',cascade=>true);

PL/SQL 过程已成功完成。
SQL> @sosi

Please enter Name of Table Owner (Null = TEST):
Please enter Table Name to show Statistics for: t1

***********
Table Level
***********


Table                   Number                 Empty
Name                   of Rows   Blocks       Blocks
--------------- -------------- -------- ------------
T1                     639,968   18,880            0

Column                    Column                       Distinct            Number       Number
Name                      Details                        Values   Density Buckets        Nulls
------------------------- ------------------------ ------------ --------- ------- ------------
A                         NUMBER(22)                     19,999   .000050       1            0
B                         VARCHAR2(300)                       1  1.000000       1            0

                              B
Index                      Tree     Leaf       Distinct         Number      Cluster
Name            Unique    Level     Blks           Keys        of Rows       Factor
--------------- --------- ----- -------- -------------- -------------- ------------
T1_IDX          NONUNIQUE     1    1,390         19,999        639,968      639,968

Index           Column                     Col Column
Name            Name                       Pos Details
--------------- ------------------------- ---- ------------------------
T1_IDX          A                            1 NUMBER(22)

***************
Partition Level
***************

  Part Partition               Number                 Empty
   Pos Name                   of Rows   Blocks       Blocks
------ --------------- -------------- -------- ------------
     1 P01                     31,968      944            0
     2 P02                     32,000      944            0
     3 P03                     32,000      944            0
     4 P04                     32,000      944            0
     5 P05                     32,000      944            0
     6 P06                     32,000      944            0
     7 P07                     32,000      944            0
     8 P08                     32,000      944            0
     9 P09                     32,000      944            0
    10 P10                     32,000      944            0
    11 P11                     32,000      944            0
    12 P12                     32,000      944            0
    13 P13                     32,000      944            0
    14 P14                     32,000      944            0
    15 P15                     32,000      944            0
    16 P16                     32,000      944            0
    17 P17                     32,000      944            0
    18 P18                     32,000      944            0
    19 P19                     32,000      944            0
    20 P20                     32,000      944            0
                                    B
Index           Partition        Tree     Leaf       Distinct         Number
Name            Name            Level     Blks           Keys        of Rows
--------------- --------------- ----- -------- -------------- --------------
T1_IDX          P01                 1       67            999         31,968
T1_IDX          P02                 1       67          1,000         32,000
T1_IDX          P03                 1       67          1,000         32,000
T1_IDX          P04                 1       67          1,000         32,000
T1_IDX          P05                 1       67          1,000         32,000
T1_IDX          P06                 1       67          1,000         32,000
T1_IDX          P07                 1       67          1,000         32,000
T1_IDX          P08                 1       67          1,000         32,000
T1_IDX          P09                 1       67          1,000         32,000
T1_IDX          P10                 1       67          1,000         32,000
T1_IDX          P11                 1       72          1,000         32,000
T1_IDX          P12                 1       72          1,000         32,000
T1_IDX          P13                 1       72          1,000         32,000
T1_IDX          P14                 1       72          1,000         32,000
T1_IDX          P15                 1       72          1,000         32,000
T1_IDX          P16                 1       72          1,000         32,000
T1_IDX          P17                 1       72          1,000         32,000
T1_IDX          P18                 1       72          1,000         32,000
T1_IDX          P19                 1       72          1,000         32,000
T1_IDX          P20                 1       72          1,000         32,000

下面执行查询:

Read the rest of this entry

,

有客户遇到SQL性能不稳定,突然变差导致系统性能出现严重问题的情况。对于大型的系统来说,SQL性能不稳定,有时突然变差,这是常常遇到的问题。这也是一些DBA的挑战。

对于使用Oracle数据库的应用系统,有时会出现运行得好好的SQL,性能突然变差。特别是对于OLTP类型系统执行频繁的核心SQL,如果出现性能问题,通常会影响整个数据库的性能,进而影响整个系统的正常运行。对于个别的SQL,比如较少使用的查询报表之类的SQL,如果出现问题,通常只影响少部分功能模块,而不会影响整个系统。

那么应该怎么样保持SQL性能的稳定性?

SQL的性能变差,通常是在SQL语句重新进行了解析,解析时使用了错误的执行计划出现的。下列情况是SQL会重新解析的原因:

  • 1. SQL语句没有使用绑定变量,这样SQL每次执行都要解析。
  • 2. SQL长时间没有执行,被刷出SHARED POOL,再次执行时需要重新解析。
  • 3. 在SQL引用的对象(表、视图等)上执行了DDL操作,甚至是结构发生了变化,比如建了一个索引。
  • 4. 对SQL引用的对象进行了权限更改。
  • 5. 重新分析(收集统计信息)了SQL引用的表和索引,或者表和索引统计信息被删除。
  • 6. 修改了与性能相关的部分参数。
  • 7. 刷新了共享池。
  • 8. 当然重启数据库也会使所有SQL全部重新解析。

SQL重新解析后,跟以前相比,性能突然变差,通常是下列原因:

  • 1. 表和索引的优化统计信息被删除,或者重新收集后统计信息不准确。重新收集统计信息通常是由于收集策略(方法)不正确引起。比如对分区表使用analyze命令而不是用dbms_stats包、收集统计信息时采样比例过小等等。Oracle优化器严重依赖于统计信息,如果统计信息有问题,则很容易导致SQL不能使用正确的执行计划。
  • 2. SQL绑定变量窥探(bind peeking),同时绑定变量对应的列上有直方图;或者绑定变量的值变化范围过大、分区数据分布极不均匀:
    • 1) 绑定变量的列上有直方图:
      假如表orders存储所有的订单,state列有3种不同的值:0表示未处理,1表示处理成功完成,2表示处理失败。State列上有一个索引,表中绝大部分数据的state列为1,0和2占少数。有下面的SQL:

      select * from orders where state=:b1
      

      这里:b1是变量,在大多数情况下这个值为0,则应该使用索引,但是如果SQL被重新解析,而第一次执行时应用传给变量b1值为1,则不会使用索引,采用全表扫描的方式来访问表。对于绑定变量的SQL,只在第一次执行时才会进行绑定变量窥探,并以此确定执行计划,该SQL后续执行时全部按这个执行计划。这样在后续执行时,b1变量传入的值为0的时候,仍然是第一次执行时产生的执行计划,即使用的是全表扫描,这样会导致性能很差。

    • 2) 绑定变量的值变化范围过大:
      同样假如orders表有一列created_date表示一笔订单的下单时间,orders表里面存储了最近1年的数据,有如下的SQL:

      Select * from orders where created_date >=:b1;
      

      假如大多数情况下,应用传入的b1变量值为最近几天内的日期值,那么SQL使用的是created_date列上的索引,而如果b1变量值为5个月之前的一个值,那么就会使用全表扫描。与上面描述的直方图引起的问题一样,如果SQL第1次执行时传入的变量值引起的是全表扫描,那么将该SQL后续执行时都使用了全表扫描,从而影响了性能。

    • 3) 分区数据量不均匀:
      对于范围和列表分区,可能存在各个分区之间数据量极不均匀的情况下。比如分区表orders按地区area进行了分区,P1分区只有几千行,而P2分区有200万行数据。同时假如有一列product_id,其上有一个本地分区索引,有如下的SQL:

      select * from orders where area=:b1 and product_id =:b2
      

      这条SQL由于有area条件,因此会使用分区排除。如果第1 次执行时应用传给b1变量的值正好落在P1分区上,很可能导致SQL采用全表扫描访问,如前面所描述的,导致SQL后续执行时全部使用了全表扫描。

  • 3. 其他原因,比如表做了类似于MOVE操作之后,索引不可用,对索引进行了更改。当然这种情况是属于维护不当引起的问题,不在本文讨论的范围。

综上所述,SQL语句性能突然变差,主要是因为绑定变量和统计信息的原因。注意这里只讨论了突然变差的情况,而对于由于数据量和业务量的增加性能逐步变差的情况不讨论。
为保持SQL性能或者说是执行计划的稳定性,需要从以下几个方面着手:

  • 1. 规划好优化统计信息的收集策略。对于Oracle 10g来说,默认的策略能够满足大部分需求,但是默认的收集策略会过多地收集列上的直方图。由于绑定变量与直方图固有的矛盾,为保持性能稳定,对使用绑定变量的列,不收集列上的直方图;对的确需要收集直方图的列,在SQL中该列上的条件就不要用绑定变量。统计信息收集策略,可以考虑对大部分表,使用系统默认的收集策略,而对于有问题的,可以用DBMS_STATS.LOCK_STATS锁定表的统计信息,避免系统自动收集该表的统计信息,然后编写脚本来定制地收集表的统计信息。脚本中类似如下:

    exec dbms_stats.unlock_table_stats…
    exec dbms_stats.gather_table_stats…
    exec dbms_stats.lock_table_stats…
    
  • 2. 修改SQL语句,使用HINT,使SQL语句按HINT指定的执行计划进行执行。这需要修改应用,同时需要逐条SQL语句进行,加上测试和发布,时间较长,成本较高,风险也较大。
  • 3. 修改隐含参数” _optim_peek_user_binds”为FALSE,修改这个参数可能会引起性能问题(这里讨论的是稳定性问题)。
  • 4. 使用OUTLINE。对于曾经出现过执行计划突然变差的SQL语句,可以使用OUTLINE来加固其执行计划。在10g中DBMS_OUTLN.CREATE_OUTLINE可以根据已有的执行正常的SQL游标来创建OUTLINE。如果事先对所有频繁执行的核心SQL使用OUTLINE加固执行计划,将最大可能地避免SQL语句性能突然变差。
    注:DBMS_OUTLN可以通过$ORACLE_HOME/rdbms/admin/dbmsol.sql脚本来安装。
  • 5. 使用SQL Profile。SQL Profile是Oracle 10g之后的新功能,此处不再介绍,请参考相应的文档。

除此之外,可以调整一些参数避免潜在的问题,比如将"_btree_bitmap_plans"参数设置为FALSE(这个参数请参考互联网上的文章或Oracle文档)。

而在实际工作中,通过使用定制的统计信息收集策略,以及在部分系统上使用OUTLINE,系统基本上不会出现已有的SQL性能突然变差的情况。当然也有维护人员操作不当引起的SQL性能突然变差,比如建了某个索引而没有收集统计信息,导致SQL使用了新建的索引,而该索引并不适合于那条SQL;维护人员意外删除了表个索引的统计信息。

,

很久没有更新BLOG了,无论出于什么样的原因,我都十分汗颜。向各位关注老熊一亩三分地的朋友们说声对不起了。

本文讲述端午节期间一位朋友在AIX 6.1上安装Oracle 10g RAC时死活不出现节点选择界面的问题的处理过程,希望对一些朋友有所帮助。在正文开始之前,向在端午期间仍然奋战在DBA工作一线的朋友们致敬^_^

对这个问题,是通过朋友的QQ远程协助处理的,因此在下面的过程描述中缺乏一些界面或者说是代码。不过我尽量将处理的思路描述清楚。

众所周知,在安装10g RAC时,需要先安装CRS,即Oracle Cluster,再安装Oracle RDBMS Software。安装这2部分都应该会出现选择安装节点的界面,本文描述的问题是出现在RDBMS部分出现的。下面是当时具体的处理过程:

  • 安装界面不出现节点选择界面,很多时候是由于/etc/hosts配置不当,以及rsh或ssh配置不当所导致。而之前CRS已经安装妥当,同时crs_stat命令检查CRS的各个资源也运行正常,使用crsctl check命令检查cluster也显示正常。那么hosts和rsh等问题应该是不会存在的,否则CRS也不能正确安装和运行。虽然如此,我还是检查了一下这2方面,没有发现任何问题。
  • 在OUI安装界面,在选择Oracle HOME时,发现主机上已经有2个HOME,分别是/oracle/product/10.2.0/crs和/oracle/product/10.2.0/cluster,此系统安装的朋友解释是在安装CRS时,出现了异常,然后重新安装到另一个目录成功。crs那个目录是废弃的HOME,cluster那个是正在运行的CRS的HOME。
  • 对于这样的问题,我习惯的做法是,先检查能够容易想到的,如果根据经验和能够想到的可能出现的问题进行排查后,仍然不能解决问题,那么我就会参考文档,按文档一步一步去检查。在此期间,我参考了Metalink上的文档《Minimum Software Versions and Patches Required to Support Oracle Products on IBM Power Systems [ID 282036.1]》、《RAC Assurance Support Team RAC Starter Kit and Best Practices (AIX) [ID 811293.1]》、《Oracle Database on Unix AIX,HP-UX,Linux,Mac OS X,Solaris,Tru64 Unix Operating Systems Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2) [ID 169706.1]》三篇文章。同时也参考了《Oracle Database 10gR2 RAC on AIX Install Guide》(本文可以自MOS 811293.1文章中的连接下载)。根据文档重点考虑到了AIX 6.1这个新版本,同时应用了文档中提到的rootpre.sh补丁。问题仍然没有解决。

其实根据安装的经验,安装RAC时问题通常会出现在CRS安装部分,而RDBMS这部分很少有遇到问题。接下来:

  • 在安装的时候还发现有java的报错,在网上搜索一番这个错误,未果。这个错误与安装问题可能有很大的关联,但是不知道问题出现在哪里,是JAVA的问题?是安装程序的问题?还是缺少什么组件?或者是操作系统的问题,不得而知。
  • 根据MOS文档《How to trace OUI Note:269837.1》,开启安装程序的跟踪:
    ./runInstaller -J-DTRACING.ENABLED=true -J-DTRACING.LEVEL=2
    从结果来看,安装程序获取了ocr的路径,同时获取了其他一些属性。在此之后,就报出了JAVA的错误。

在问题的整个处理过程中,我一直在思考一个问题,那就是安装程序OUI是怎么来判断应该安装RAC还是安装非RAC的软件。我想到的几个方面:

  • 从/etc/hosts来判断,我很快否决了这个想法,这个不太可靠。
  • RAC的运行离不开Cluster软件,因此检查是否有cluster,cluster中有些什么样的节点,这是一个可行的办法。以前在AIX上安装Oracle9i时,如果有HA软件在运行,在安装时则会自动出现节点的选择并安装RAC软件。这是我认为的比较可靠的,并且应该是Oracle应当会采用的方法。但这里一个关键的问题是,现在CRS运行正常,安装程序为什么不能检测到cluster,不能检测到cluster中的节点。
  • 通过Oracle Inventory来检测,这是从以前解决DBCA建库不出现节点选择界面所得到的经验。当时为了解决问题,甚至反编译了DBCA部分JAVA代码。

打开/oracle/oraInventory/ContentsXML/inventory.xml,文件内容如下:

<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 2005 Oracle Corporation. All rights Reserved -->
<!-- Do not modify the contents of this file by hand. -->
<INVENTORY>
<VERSION_INFO>
   <SAVED_WITH>10.2.0.1.0</SAVED_WITH>
   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="OUIHome1" LOC="/oracle/product/10.2.0/crs" TYPE="O" IDX="1" CRS="true">
   <NODE_LIST>
      <NODE NAME="node1"/>
      <NODE NAME="node2"/>
   </NODE_LIST>
</HOME>
<HOME NAME="OUIHome2" LOC="/oracle/product/10.2.0/cluster" TYPE="O" IDX="2" CRS="true">
   <NODE_LIST>
      <NODE NAME="node1"/>
      <NODE NAME="node2"/>
   </NODE_LIST>
</HOME>
</HOME_LIST>
</INVENTORY>

会不会是安装程序在根据第一个CRS HOME检测cluster出现了问题呢。删除掉IDX=“1”的那个HOME,将IDX=“2”改成IDX="1",然后重装安装。啊,上帝保佑,成功了。当时差点就内牛满面啊。

对于这个问题,其实还有一个不算太完美的解决办法,就是分别在2个节点上安装好Oracle RDBMS软件,然后打开rac_on选项,然后relink oracle。只不过这样的情况下,DBCA这样的工具又不能正常使用了。

也许有朋友问,为什么不一开始就关注inventory的问题。这可能跟我的处理问题习惯有关系,通常情况下我会按常规方式处理,或者看看有没有类似的案例。接下来才会考虑那些比较另类的方法。
--EOF

曾几何时,网络上流传着给Oracle数据库分配内存的一条法则:把80%的内存分配给Oracle使用,而又将这80%的内存分配80%给Oracle的SGA,剩下的20%分给Oracle的PGA。记得Tom曾说过类似这样的话:如果一个参数的设置对Oracle是最佳的,那么Oracle就会自动地将其设为了默认值。而显然,在内存分配这事上,Oracle的初始设置并不是按这个法则的,那么就是说从某一方面证明这个法则存在问题。

当然大部分DBA不会这样设置内存参数,但是也有不少的人在Oracle的内存分配上存在欠考虑的地方。

首先,我们来看看保留可用内存20%给操作系统是否合适。对于2G内存的服务器(现实中这样的机器不少),20%意味着400M,而通常400M对操作系统来说是不够用的。而对于内存特别多的主机,20%又显得太多。比如下面是一份来自于一台P595的内存情况:

====================================================|==========|===========
Memory Overview                                     |    Pages |  Megabytes 
----------------------------------------------------|----------|-----------
Total memory in system                              | 45875200 |  179200.00 
    Total memory in use                             | 34789026 |  135894.63 
    Free memory                                     | 11086174 |   43305.36 
====================================================|==========|===========
Segment Overview                                    |    Pages |  Megabytes 
----------------------------------------------------|---------|-----------
Total segment id mempgs                             | 32618956 |  127417.79 
    Total fork tree segment pages                   |     2074 |       8.10 
    Total kernel segment id mempgs                  |  3594452 |   14040.82 

这台主机共计179GB物理内存,已使用135G,其中内核占用14G。内核占用的内存不到总内存的10%。

以上的数据以及说明,只是表达这样一个观点,对于操作系统的保留内存,需要根据实际情况预以考虑,这包括了操作系统的内核参数的设置。比如在AIX下的默认设置,client和perm内存可以占用远远超过20%的内存,而HP-UX下的默认设置,File Cache和Buf Cache也可能占用远远超过20%的内存。所以对于这些环境的数据库,一定要注意调整OS的内核参数。对于OS的内存使用,至少保留20%也不失为一种稳妥的做法。

除了操作系统这一块,给Oracle分配内存的时候,还需要注意以下非常重要的几点,这几点经常被人忽略:

  • 注意业务高峰期的内存使用:我所维护的一套系统,平时的连接数通常在5000-5500左右,而在最高时连接数达到了8000,也就是到达了连接的上限才作罢。因此,我们需要为业务高峰期时保留足够的内存。
  • 对于RAC数据库,需要考虑到其他节点故障或停机维护时,连接和压力转移到继续工作的节点时的内存消耗。
  • 一些人只考虑到了连接时进程使用的PGA内存,这里存在一个很大的误解,就是认为一个连接,只会使用PGA的内存。但还有一个很重要的内存使用,那就是进程本身占用的操作系统内存,除了PGA之外的内存。进程本身有代码(在OS中这通常是共享的),有stack,有heap,还要有kernel的内存占用。PGA只是进程使用的内存中一部分,甚至大部分情况下只是一小部分。在Oracle 10.2.0.4 for AIX下测试过,一个空闲连接,也就是啥事儿都不干的一个连接,PGA占用500K左右,而server process进程占用的内存在4-5M之间。测试时这套库刚启动,没有任何负载。实际上据观察在一套运行比较长时间的库上,server process占用的内存在9-10M之间。当然不同的系统,不同的配置,oracle进程占用的内存有所不同,有兴趣的朋友可以测量一下Oracle进程在HP-UX和LINUX下的内存占用。

对于SGA内各组件的细分以及PGA大小设置,网上很多相关的文章可供参考,本文不再涉及。不过我的个人观点是:参数的调整也不是一步到位的事情,需要根据系统运行时对性能数据的分析来进行调整,直至达到最优化。

应该怎么样安装数据库,从安装软件到创建数据库?对于这个问题,或许有的人不屑一顾,因为他们觉得这没有丝毫问题;同时有另一部分人,觉得这是个大问题。

在安装Oracle上,通常会有几种类型的人:

  • 完全抓不着头脑,不知道怎么安装,这通常是初学者,连Linux/Unix都不太熟悉。
  • 很少安装Oracle的人,但是知道对照文档一步一步操作,出了错也知道上google、baidu和metalink查找解决方案。
  • Oracle老手,安装数据库不需要任何文档,对每个步骤也很熟悉。
  • 对Oracle的安装非常熟悉,但是在安装时仍然按文档一步一步操作。

对我个人来说,我是最后一种类型的人。我也自认为安装了不少的数据库,覆盖了大部分的平台和操作系统。但我安装的时候,仍然会不嫌麻烦的一步一步操作按文档操作。在安装数据库软件包括建库,基本上没有遇到过什么问题。因为我也见过
很多朋友,被安装这一问题折腾得焦头烂额,特别是在安装RAC的时候,这里只是写一写我自己的一些做法,仅供参考,毕竟每个人都有自己的习惯和做法。

本文主要描述Linux/Unix下的Oracle安装,不涉及Windows系统下的安装。

我的习惯做法:

  • 平时注意收集安装文档,包括oracle online document(所谓的官方文档),metalink上的,还有IBM、HP这些公司与Oracle合作部门提供的文档。oracle online document中安装部分没事看一遍就可以了,但是metalink上很多文档详细地记录了版本的兼容性,安装时可能出现的问题以及解决办法等等,比如很实用的文档:《Oracle Database on Unix AIX,HP-UX,Linux,Mac OS X,Solaris,Tru64 Unix Operating Systems Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2) [ID 169706.1]》、《Linux OS Requirements Reference List for Database Server [ID 851598.1]》、《Status of Certification of Oracle Clusterware with HACMP [ID 404474.1]》。而其他厂商的文档包括:《CookBook_V3.2_Oracle_9i_RAC_AIX5L》、《COOKBOOK_Oracle CTC RAC10g R2 on HP-UX》、《COOKBOOK-V2.0-10gRAC R2 - ASM - AIX5L - SAN Storage》如此等等,还包括网上一些朋友自己撰写的安装文档。在参考这些文档时,需要注意的是,一定要明白每一个步骤其目的,有什么作用。
  • 根据以上提到的文档,进行整理,形成自己的文档。我在前面说到,我安装时一步一步按文档操作,是指的按我自己的文档,而不是去参考前面提到的若干文档。在自己的文档中,甚至提供了详细的命令,这样在安装的时候对某些不熟悉的命令不至于现查资料。
  • 深入理解文档中提到的各个参数、各个命令的作用。这不光是对安装,而对于Oracle数据库的更深入理解也是大有好处的。

安装Oracle数据库时,在安装软件之前,通常有下面的操作:

  • 检查操作系统版本、相应组件是否安装,是否有安装好文档中指定的补丁,也包括c编译器或c语行环境,这些对Linux下的安装来说犹为重要。
  • 检查文件系统空间,特别是/tmp临时文件系统
  • 检查memory大小,特别是swap的大小。特别是在HP-UX下,swap的管理方式与其他系统有些不一样(此处不再细述),最
  • 好能够达到物理内存大小,对于特别大的物理内存,至少也要达到一半。
  • 检查主机时区,时间设置。这一步通常被很多人忽略。
  • 检查主机名设置,有的安装系统相当不负责任,直接将主机取名localhost。
  • 检查异步IO设置。
  • 检查网络设置,包括/etc/hosts文件的设置,特别是对RAC数据库犹为重要。
  • 检查内核参数,特别是共享内存、信号量、用户最多可运行进程数这些参数。
  • oracle用户创建后,注意编辑profile文件,设置相应的环境变量,注意不同的平台,相同意义的环境变量却有不同的名字,比如linux下的LD_LIBRARY_PATH和AIX下的LIBPATH。经常见到有的系统,oracle的PATH都没有设置,这样登录后操作相当不方便。
  • 对Oracle用户设置limit,通常是直接编辑/etc/security/limits.conf文件。
  • 给Oracle用户一些特定的权限,比如HP-UX下修改/etc/privgroup文件,10.2.0.4 for AIX下给用户CAP_NUMA_ATTACH, CAP_BYPASS_RAC_VMM, CAP_PROPAGATE 这样的权限等等。
  • 对于RAC,还需要设置rsh或ssh

至于安装的其他步骤,不是本文所要讲的主要内容,在此略过。

还要提及一点,安装的时候对于目录的选择,可以按照OFA的标准做法,也可以按照使用部门的习惯,建议使用OFA。经常有见到一些乱七八糟的目录,让人好找,这种做法不太好。

说到安装,不能不提到打补丁。在安装完成后,最好是打上较新的补丁包以及metlink上提到的一些建议打的补丁。而等到系统正式使用,发现问题再打补丁,其代价就昂贵得多。

由于安装Oracle软件以及升级版本和打补丁,比较耗时,有的朋友就喜欢下面的做法:安装好软件,打上补丁,然后tar成一个包,保存在自己的存储介质上,下次在其他主机上安装时,直接用这个tar包解开。这种做法可以省一些安装软件的时间,但是需要tar包的环境是否一致。我曾遇到过下面的问题:

某套新装9208的库,报ORA-600[504]错误,通过在metalink上搜索发现其最符合的一个BUG对应的补丁已经打上。其他类似环境下,包括有同样的补丁,却没有这样的错误。我找了一台操作系统完全一样的测试主机,安装与有问题库的版本完全一样的版本和补丁,却也没有这样的错误;接下来我将出问题的Oracle home 复制到测试主机上,结果问题重现了,看起来问题在Oracle软件上。我尝试执行relink操作,居然失败。发现这份Oracle在relink包括有HA代码,实际上这是一个单机的环境,也没有安装HACMP。最后,使用rac_off选项,再重新relink,新生成的oracle,不再出现这样的问题。在有问题的生产主机上重新relink后,问题解决。

出现问题的Oracle,就是通过解tar文件来安装的,在主机上我们也发现了以前安装时保留的tar文件。因此通过这种方式安装的,建议进行relink。

下面再谈谈创建数据库的一些个人经验:

  • 尽量使用new database(9i)或者custom database(10g)这样的选项来创建库,建库时只安装必须的组件,这种做法有3个主要好处:更安全、更稳定、以后升级时所花的时间更少。
  • 创建数据库时注意选择正确的字符集。
  • 如果是选用模板创建数据库,注意模板有可能不与Oracle软件软件版本相匹配。同时在建库完成后需要运行相应的脚本,比如在安装了PSU的情况下,那么使用模板建库,得需要运行PSU带的脚本(具体参考PSU的README)。
  • 在打完补丁之后,在创建数据库,避免在升级软件之后还要升级数据库。
  • 建完库后,建议设置一下大体上合理的数据库参数。

希望本文能够对Oracle数据库的安装不太熟悉的朋友一些帮助。

去年年底做了不少系统的数据迁移,大部分系统由于平台和版本的原因,做的是逻辑迁移,少部分做的是物理迁移,有一些心得体会,与大家分享。

首先说说迁移流程,在迁移之前,写好方案,特别是实施的方案步骤一定要写清楚,然后进行完整的测试。我们在迁移时,有的系统测试了四五次,通过测试来完善方案和流程。

针对物理迁移,也即通过RMAN备份来进行还原并应用归档的方式(这里不讨论通过dd方式进行的冷迁移),虽然注意的是要将数据库设为force logging的方式,在用RMAN做全备之前,一定要执行:

alter database force logging;

否则可能会产生坏块。

对于逻辑迁移,在job_processes设置为>0的数值之前,注意job的下次执行时间和job所属用户。比如job的定义在之前已经导入,但是在迁移之时,job已经运行过,那么迁移完成之后,job的下次时间还是原来的时间,这样可能会重复运行。另外,job通过IMP导入后,job所属用户会变成导入用户的名称,显然job原来的用户就不能对JOB进行管理了,可以通过下面的sql进行修改:

update sys.job$ set lowner=cowner , powner=cowner;

在迁移之前,应该禁止对系统进行结构上的修改和发布,比如表结构,索引,存储过程包等。

如果是用exp/imp导入的对象,包括存储过程等,应该检查对象是否与原生产库一致,比如由于dblink的原因,imp之后,存储过程不能创建,导致有部分存储过程丢失,尽管这些存储过程可能没有被使用。

下面是一些加快迁移速度的技巧:

  • 通过dblink,使用append insert的方式,同时利用并行,这种方式比exp/imp更快
  • 对于有LONG类型的列,insert..select的方式显然是不行的,可以通过exp/imp的方式,但是这种方式速度非常慢,其原因在于imp时一行一行地插入表。有另外一种方式,即sqlplus的copy命令,下面是一个示例:
    spool copy_long_table_1.log
    conn / as sysdba
    set copycommit=2000
    set arraysize 30
    set long 10485760
    
    copy from system/xxxx@source_db append username.table_name using select * from username.table_name;
    
    spool off
    exit
    

    不过,sqlpus的copy命令不支持有timestamp和lob列类型的表。如果有timestamp类型的表,可以通过在exp时,加上rowid的条件,将一个表分成多个部分同时操作,对于有lob类型的表,也可以同样处理(因为insert ...select方式下,有lob类型列时,也同样是一行一行地插入)。注意在这种方式下,就不能使用direct的方式exp/imp。下面是exp导出时parfile示例:

    query="where rowid>=dbms_rowid.rowid_create(1,71224,52,9,0) and rowid<=dbms_rowid.rowid_create(1,71224,55,1038344,10000)" 
    file=/dumpdata/n1.dmp
    tables=username.table1
    constraints=n
    grants=no
    indexes=no
    buffer=104857600
    ...
    ...
    query="where rowid>=dbms_rowid.rowid_create(1,71224,423,137,0) and rowid<=dbms_rowid.rowid_create(1,71224,432,59272,10000)" 
    file=/dumpdata/n6.dmp
    tables=username.table1
    constraints=n
    grants=no
    indexes=no
    buffer=104857600
    

    将表分成几部分同时操作,不仅仅可以利用rowid,也可以利用表上的列,比如说,表上有一个created_date的列,并且保证是递增插入数据,那么这种情况下,也可以使用这个字段将表分成不同的范围同时进行导出和导入。不过使用ROWID通常具有更高的效率。
    当然对于有lob列的表,可以按上述方式,拆成多个insert方式同时插入,不需要exp/imp。

  • 对于特别大的分区表,虽然使用并行可以提高速度,但是受限于单个进程(不能跨DB LINK进行并行事务,只能并行查询,也即insert..select只能是SELECT部分才能进行并行)的处理能力,这种方式下速度仍然有限。可以并行将数据插入多个中间表,然后通过exchange partition without validation 的方式,交换分区,这种方式将会大大提高了速度。
    有朋友可能会问,为什么不并行直接插入分区表,当然如果是非direct path(append)方式,则是没问题的,但是这种方式插入的性能较低。而direct path的方式,会在表上持有mode=6(互斥)的TM锁,不能多个会话同时插入。(update: 在insert 时使用这样的语句:insert into tablename partition (partname) select * from tablename where ....,更简单更有效率。)
  • 迁移时,将数据分成两部分,一部分是历史表,第二部分是动态变化的表,在迁移之前,先导入历史表,并在历史表上建好索引,这无疑会大大减少迁移时业务系统中断时间。
  • 迁移之前,考虑清理掉垃圾数据。
  • 迁移时,应保证表上没有任何索引,约束(NOT NULL除外)和触发器,数据导入完成后,再建索引。建索引时同样,同时使用多个进程跑脚本。索引创建无成后,应去掉索引的PARALLEL属性
  • 在创建约束时,应按先创建CHECK约束,主键,唯一键,再创建外键约束的顺序。约束状态为 ENABLE NOVALIDATE,这将大大减少约束创建时间。而在迁移完成后,再考虑设回为ENABLE VALIDATE。
  • 通过使用dbms_stats.export_schame_stats和dbms_stats.import_schame_stats导入原库上的统计信息,而不用重新收集统计使用。

朋友们可以看到,以上均是针对9i的,实际上在10g甚至11g环境下,也仍然很多借鉴意义。当然这些技巧不仅仅用于完整的数据库迁移,也可以应用到将个别表复制到其他数据库上。

这里没有提到的是利用物化视图或高级复制、触发器之类的技术,因为这些技术,毕竟要修改生产库,对生产库的运行有比较大的影响,因此,只有在停机时间要求特别严格,而在这个时间内又不能完成迁移时才应该考虑。

从迁移的经验来说,只有完善的流程,完整的测试才可以保证成功。这里只是列举了一些小技巧,如果对整个迁移过程有兴趣,可以针对这个话题再进行讨论。