ORA-19808错误一例

一套Linux上的11.2.0.2 RAC系统,其中一个节点startup mount时出现ORA-19808错误,日志如下:


SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01105: mount is incompatible with mounts by other instances
ORA-19808: recovery destination parameter mismatch


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options


[oracle@rh3 ~]$ oerr ora 19808
19808, 00000, "recovery destination parameter mismatch"
// *Cause:  The value of parameters DB_RECOVERY_FILE_DEST and
//          DB_RECOVERY_FILE_DEST_SIZE must be same in all instances.
//          instance. All databases must have same recovery destination
//          parameters.
// *Action: Check DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE
//          values in all instances.

SQL> col name for a30
SQL> col value for a30

SQL> select name ,value,inst_id from gv$system_parameter  where name='db_recovery_file_dest_size';

NAME                           VALUE                             INST_ID
------------------------------ ------------------------------ ----------
db_recovery_file_dest_size     0                                       1
db_recovery_file_dest_size     42278584320                             2

该错误是由于启动节点使用了pfile形式的参数文件,而该参数文件中的db_recovery_file_dest_size值与已经启动的另一个节点的db_recovery_file_dest_size不一致所造成。

如果使用共享的server parameter file则不可能出现上述情况,当然也可以通过在启动节点上修改db_recovery_file_dest_size来解决问题。

Oracle RAC内部错误:ORA-00600[keltnfy-ldmInit]一例

一套SUNOS上的2节点10.2.0.2 RAC系统日前出现ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], []内部错误,错误发生时系统操作人员误使用hostname命令修改了1号主机的主机名,之后陆续出现以上ora-00600错误,同时操作系统日志显示RAC CSS进程意外终止,具体日志如下:

================== OS Message=====================
Jan 10 11:15:10 cupd25k-a root: [ID 702911 user.error] Cluster Ready Services completed waiting on dependencies.
Jan 10 11:15:16 cupd25k-a root: [ID 702911 user.error] Duplicate Oracle CLSMON found. Killing and restarting it.
Jan 10 11:15:16 cupd25k-a root: [ID 702911 user.error] Oracle CSS daemon failed to start up. Check CRS logs for diagnostics.
Jan 10 11:15:16 cupd25k-a root: [ID 702911 user.error] Oracle CLSMON terminated with unexpected status 137. Respawning

/* 这里的Duplicate Oracle CLSMON found 因该指的是OCLSMON进程,
"In Oracle 10.2.0.2 and above there is an additional process called OCLSOMON
which monitors the CSS daemon for hangs or scheduling issues and can reboot a
node if there is a perceived hang. OCLSOMON is spawned in init.cssd and runs
as the Oracle user."
   oclsmon进程在10.2.0.2以后版本被引入,用以监视css进程,
   若发生hang或操作系统调度问题时该进程可能会reboot节点,
   oclsmon进程会被init.cssd脚本spawned.  */

==================oclsmon.log======================
2011-01-10 11:15:11.376
unspecified member number is (1)
Member 1 group OCLSMON_ in use. Is oclsmon already up?
2011-01-10 11:15:11.479
Internal Error Information:
  Category: 8
  Operation: skgxnreg: the member number is i
  Location: skgxnreg_7
  Other:
  Dep: 1
2011-01-10 11:15:11.737
unspecified member number is (1)
Member 1 group OCLSMON_ in use. Is oclsmon already up?
2011-01-10 11:15:11.751
Internal Error Information:
  Category: 8
  Operation: skgxnreg: the member number is i
  Location: skgxnreg_7
  Other:
  Dep: 1
2011-01-10 11:15:12.006
unspecified member number is (1)
Member 1 group OCLSMON_ in use. Is oclsmon already up?
2011-01-10 11:15:12.023
Internal Error Information:
  Category: 8
  Operation: skgxnreg: the member number is i
  Location: skgxnreg_7
  Other:
  Dep: 1
2011-01-10 11:15:12.278
unspecified member number is (1)
Member 1 group OCLSMON_ in use. Is oclsmon already up?
2011-01-10 11:15:12.293
Internal Error Information:
  Category: 8
  Operation: skgxnreg: the member number is i
  Location: skgxnreg_7
  Other:
  Dep: 1

/*  skgxn是Oracle Clusterware用以监视skgxn事件(即第三方CLUSTERWARE相关的事宜,他们应该有用sun的cluster);
    似乎是修改hostname导致了Oracle CSS出现了fatal error,并启动了一个以上的OCLSMON进程(Duplicate Oracle CLSMON found),
    最后"Oracle CSS daemon failed to start up. Check CRS logs for diagnostics",
    在Oracle instance启动的情况下25k-a节点的CSS进程意外终止,
    可能导致该节点上的所有实例的LMD(global Enqueue Service daemon)、LMON无法正常工作而导致实例hang住。*/

==========================alert.log====================
Errors in file /oracle/oracle/admin/BOCPCS/udump/bocpcs1_ora_12320.trc:
ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], []

=========================part of trace file===============
*** 2011-01-10 11:11:02.957
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], []
Current SQL information unavailable - no session.
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp()+716         CALL     ksedst()             FFFFFFFF7FFF9D40 ?
                                                   000000000 ? 0FFFFFFFF ?
                                                   FFFFFFFF7FFF8EE8 ?
                                                   FFFFFFFF7FFFA640 ?
                                                   000000008 ?
kgerinv()+200        PTR_CALL 0000000000000000     000000002 ? 10638A1CC ?
                                                   000000001 ? 000000000 ?
                                                   10638A000 ? 10638A1CC ?
kgeasnmierr()+28     CALL     kgerinv()            106384B98 ? 000000000 ?
                                                   105D3B940 ? 000000002 ?
                                                   FFFFFFFF7FFFDFF0 ?
                                                   000001430 ?
keltnfy()+784        CALL     kgeasnmierr()        106384B98 ? 1064DCBF0 ?
                                                   105D3B940 ? 000000002 ?
                                                   000000000 ? 00000002E ?
kscnfy()+552         PTR_CALL 0000000000000000     10639B498 ? 38001E7A8 ?
                                                   1055AC5D0 ? 10639B498 ?
                                                   000102C00 ? 10638A1C0 ?
ksucrp()+2436        CALL     kscnfy()             000008000 ? 000808214 ?
                                                   100C4C220 ? 1055C6680 ?
                                                   00000000F ? 000000001 ?
opiino()+2056        CALL     ksucrp()             000106387 ? 380007608 ?
                                                   000000000 ? 000380000 ?
                                                   000106000 ? 106387618 ?
opiodr()+1488        PTR_CALL 0000000000000000     10555A000 ?
                                                   FFFFFFFF7FFFF1C8 ?
                                                   00010555A ? 000106000 ?
                                                   105C83000 ? 000000001 ?
opidrv()+828         CALL     opiodr()             106391000 ? 000000000 ?
                                                   106390DD8 ? 106390000 ?
                                                   106391BD0 ? 000106000 ?
sou2o()+80           CALL     opidrv()             106394358 ? 000000001 ?
                                                   00000003C ? 000000000 ?
                                                   00000003C ? 000106000 ?
opimai_real()+124    CALL     sou2o()              FFFFFFFF7FFFF788 ?
                                                   00000003C ? 000000004 ?
                                                   FFFFFFFF7FFFF7B0 ?
                                                   105C82000 ? 000105C82 ?
main()+152           CALL     opimai_real()        000000002 ?
                                                   FFFFFFFF7FFFF888 ?
                                                   103F1BBCC ? 10632DB10 ?
                                                   002411E44 ? 000014400 ?
_start()+380         CALL     main()               000000002 ? 000000008 ?
                                                   000000000 ?
                                                   FFFFFFFF7FFFF898 ?
                                                   FFFFFFFF7FFFF9A8 ?
                                                   FFFFFFFF7C700200 ?

/* 可以看到以上trace文件指出了no session,
    在服务进程启动阶段遭遇了该keltnfy-ldmInit内部错误*/

metalink文档Startup Database Produces Ora-00600: [Keltnfy-Ldminit] [ID 336447.1]
介绍了该内部错误一般由主机上的不当网络配置引起,很显然使用hostname命令修改了一个无法解析的
主机名时可能引发该ORA-00600[keltnfy-ldmInit]内部错误。

Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.3 - Release: 10.2 to 10.2
Information in this document applies to any platform.
***Checked for relevance on 09-Jun-2010***
Symptoms

An startup nomount on Oracle 10g Release 2 database produces the following exception in alert log

Starting up ORACLE RDBMS Version: 10.2.0.1.0.
Errors in file /opt/oracle/10.2/admin/ORCL/udump/ORCL_ora_535.trc:
ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], []
USER: terminating instance due to error 600
Instance terminated by USER, pid = 535
Cause

The problem is related to getting host information.
In this case, ldmInit()/sldmInit() is failing with error 46 : LDMERR_HOST_NOT_FOUND

The following exception may also occur :

LDMERR_SOSD_INIT         OSD init failed to be specific in these OSD failures
 LDMERR_BAD_ADDR         bad address when system call gethostname failed
 LDMERR_HOST_NOT_FOUND   gethostbyname system call fails
 LDMERR_NO_SUPPORT       when specific address type is not supported

Development has fixed two bugs so far regarding this issue

Bug:5438154 - Abstract: ORA-600[KELTNFY-LDMINIT]  STARTING THE DB
Release Notes:
ldmInit returned LDMERR_HOST_NOT_FOUND for the machine huge alias list/address list
Workaround:
reduce the alais list of the machine

Bug:5486074 - Abstract: ORA-600 [KELTNFY-LDMINIT] WHEN DNS IS NOT AVAILABLE
Release Notes:
Internal error is raised by the Server Generated Alert subsystem when it can not determine Host Name or
Network Address. This can be caused by DNS server being unaavilable. 

Solution

The fix for 5486074 will not fix any underlying error from gethostbyname(), it just change the internal error to a warning message :

 "Warning: keltnfy call to ldmInit failed with error 46"

You will still need to fix the network config issue.  

These are the check you can do verify the host information 

      Check permission on /etc/hosts 

$ ls -l /etc/hosts
-rw-r--r--  2 root root 194 Oct 17  2006 /etc/hosts

      Check if /etc/hosts file is correctly configured

              ( all of this on one line ). 

Check the hostname:
$ hostname
$ ping `hostname`

Make sure you are able to ping the hostname
      Check if /etc/nodename is correctly configured

If you have DNS setup, ping is not a tool to diagnose DNS problem. A better tool to use is nslookup, dnsquery, or dig.

$ nslookup
$ nslookup
$ nslookup 

The forward and reverse lookup should succeed and return consistent address/info.  

 Check nsswitch.conf

$ more nsswitch.conf
hosts:      files dns
Make sure host lookup is also done through the /etc/hosts file and not just dns.  It is recommended that FILES come first before DNS.
Also, check the resolv.conf. This makes sure that the DNS is working properly.

显然在生产主机上使用hostname命令是危险的,因为你很难保证你在打字的时候不会因为同事的一下拍击而输错,有人说在生产环境中rm命令因该被禁用,那么这种特殊待遇对hostname命令也适用,我们可以用什么来代替hostname查看主机名呢?选择可以有非常多,这里我推荐一种:

-bash-3.00$ oslevel -r 
5300-07

-bash-3.00$ hostname
askmaclean.com

-bash-3.00$ uname -n
askmaclean.com

/* uname -n完全可以满足你的需要! */
That's great!

诊断RAC全局缓存块丢失gc blocks lost

在Oracle RAC环境中,无论我们从AWR自动负载性能报告、Statspack或者Grid Control中都可以找到Oracle数据库软件所收集的全局缓存工作负载统计信息(global cache work load statistics);其中就包含了全局缓存块丢失(Global cache lost blocks)的统计信息(这些丢失的全局缓存块可能是gc cr block lost或者gc current block lost),若集群中的任意节点出现大量的全局缓存块丢失(下文简写为gc blocks lost),则可能意味着内联(private)网络存在问题或者packet网络包处理低效。通过监控和评估这些全局缓存的相关统计信息,可以有效保证内联全局缓存(interconnect Global Cache)和全局队列服务(Global Enqueue Service)(GCS/GES)以及整个集群的正常工作。全局缓存块丢失一般预示着网络包处理存在问题并需要进一步勘察。另外全局缓存块丢失(gc blocks lost)的问题常会伴随着gc cr multiblock waits等待发生(传输多个连续的数据块全局缓存)。

就目前来看最有嫌疑造成或加速gc blocks lost的”元凶”往往是因为错误地或者不当的配置了内联网络(interconnects)。接下来我们会进一步介绍如何找出造成gc blocks lost的原因。

虽然gc blocks lost对集群造成的影响更多的反应在性能方面,但我们也无法保证其没有造成节点/实例被驱逐(eviction)的可能性。Oracle Clusterware集群及Oracle RAC实例的节点成员管理依赖于内联网络的心跳(heartbeats)。假设在网络心跳持续丢失的情况下,节点/实例的驱逐可以发生。以下我们列出gc blocks lost可能造成的主次要症状:

主要症状:

  • ‘gc cr block lost’或’gc current block lost’成为实例中Top 5的主要等待事件

次要症状:

  • SQL trace报告显示多次出现gc cr requests,gc current request等待事件
  • 出现长时间的gc cr multiblock requests等待
  • 糟糕的应用性能与吞吐量
  • ifconfig或其他网络工具显示存在大量的网络包packet发送接收(send/receive)错误
  • netstat报告显示存在errors/retransmits/reassembly等失败
  • 单个或多个节点失败
  • 由网络处理引发的异常CPU使用率

下面我们尝试列出可能引起gc blocks lost的多种可能性:
1.设置过小的UDP receive (rx) buffer sizes/UDP buffer socket overflows
描述:在真实环境中Oracle RAC全局缓存块处理总是集送式(bursty)地、连续地;当OS在等待可用CPU时需要将接受到的packet存放的相关协议的buffer中。当buffer空间不足时将可能导致静默的packet丢失进而造成全局缓存块丢失(global cache block loss)。在绝大多数UNIX平台上`netstat -s`或`netstat -su`命令帮助我们了解udp溢出(UDPInOverflows),packet接收错误,帧丢弃(frame dropped),或由buffer full造成的packet丢弃。
措施:Packet丢失大多数情况下归因于在接受服务器上不当的UDP buffer缓存大小,进而导致buffer溢出和global cache block loss。当操作系统所设置的UDP接收缓存大小(UDP receive (rx) buffer size)小于128k时Oracle打开一个socket套接字的udp rx buffer size为128K。若OS的设置大于128K时Oracle会遵从该设置值保持不变。Oracle所使用的UDP receive buffer大小会因不同的数据库标准块(>8k)的大小而增大,但不会超过OS系统所决定的限度。当DB_FILE_MULTIBLOCK_READ_COUNT初始化参数设置大于4的环境中出现因不当的udp缓存设置所造成的过度的’global cache cr requests’等待事件超时一般很容易观察到udp buffer溢出、丢包、缓存块丢失等现象。为了缓解这种问题,增大udp buffer的大小是一种行之有效的方法,此外我们还可以降低DB_FILE_MULTIBLOCK_READ_COUNT参数值。
在绝大多数UNIX/Linux平台上以下命令可以帮助我们了解udp socket buffer溢出或丢包的情况:

[maclean@rh2 ~]$ netstat -s
Ip:
    103300 total packets received
    0 forwarded
    0 incoming packets discarded
    103296 incoming packets delivered
    105287 requests sent out
Icmp:
    101 ICMP messages received
    0 input ICMP message failed.
    ICMP input histogram:
        destination unreachable: 75
        echo replies: 26
    175 ICMP messages sent
    0 ICMP messages failed
    ICMP output histogram:
        destination unreachable: 119
        echo request: 56
IcmpMsg:
        InType0: 26
        InType3: 75
        OutType3: 119
        OutType8: 56
Tcp:
    30355 active connections openings
    73 passive connection openings
    29589 failed connection attempts
    35 connection resets received
    3 connections established
    93218 segments received
    102780 segments send out
    68 segments retransmited
    0 bad segments received.
    29644 resets sent
Udp:
    2264 packets received
    46 packets to unknown port received.
    0 packet receive errors
    2270 packets sent
TcpExt:
    17 invalid SYN cookies received
    59 ICMP packets dropped because they were out-of-window
    181 TCP sockets finished time wait in fast timer
    166 delayed acks sent
    1 delayed acks further delayed because of locked socket
    Quick ack mode was activated 3 times
    6247 packets directly queued to recvmsg prequeue.
    6427 packets directly received from backlog
    554572 packets directly received from prequeue
    4171 packets header predicted
    1039 packets header predicted and directly queued to user
    9183 acknowledgments not containing data received
    4216 predicted acknowledgments
    2 times recovered from packet loss due to SACK data
    TCPDSACKUndo: 14
    18 congestion windows recovered after partial ack
    0 TCP data loss events
    2 fast retransmits
    46 other TCP timeouts
    6 DSACKs sent for old packets
    19 DSACKs received
    26 connections reset due to unexpected data
    25 connections reset due to early user close
    9 connections aborted due to timeout
IpExt:
    InMcastPkts: 4168
    InBcastPkts: 3505

[maclean@rh2 ~]$ netstat -su
IcmpMsg:
    InType0: 26
    InType3: 75
    OutType3: 119
    OutType8: 56
Udp:
    2264 packets received
    46 packets to unknown port received.
    0 packet receive errors
    2270 packets sent
IpExt:
    InMcastPkts: 4168
    InBcastPkts: 3505

此外udp丢包常会造成延迟增加,降低带宽,增大cpu使用率(kernel和user部分的),及因包重传(packet retransmission)导致消耗额外的内存。

2.糟糕的内联网络性能及高cpu使用率,`netstat -s`显示出现packet reassembly包重组失败
描述:庞大的UDP数据报(datagrams)可能需要被拆分并以多个帧的形式发送(取决于Medium Transmission Unit MTU的大小),在接收端服务器需要将这些拆分包重组(reassemble);高cpu使用率(持续地或高频率的波峰),不当的reassembly buffers及UDP buffer空间可能造成包重组失败。在接收端服务器`netstat -s`报告可以显示IP统计信息中存在大量的重组失败’reassembles failed’和超时后帧丢弃’fragments dropped after timeout’。碎片包(Fragmented packets)有一个重组的保留时间。未被成功重组的包可能会被丢弃并需要再次申请。在没有重组空间的情况下包会被静默地丢弃。

`netstat –a` 显示IP统计:
     3104582 fragments dropped after timeout
     34550600 reassemblies required
     8961342 packets reassembled ok
     3104582 packet reassembles failed.

措施:增加碎片重组buffer的大小,为重组分配更多的空间。增加重组碎片包的保留时间。增加udp receiver buffer以降低网络延迟,缓解包重组失败及cpu使用率对网络栈处理造成的负面影响。

在Linux上我们可以修改如下阀值以增大重组缓存空间:
/proc/sys/net/ipv4/ipfrag_low_thresh (默认为196608)
/proc/sys/net/ipv4/ipfrag_high_thresh (默认为262144)

为修改碎片包重组时间,可以修改:
/proc/sys/net/ipv4/ipfrag_time (默认为30)

以下上列出可能造成gc blocks lost性能问题的最主要的2种可能性,更多信息可以参考原文:gc lost blocks diagnostics。同时因各UNIX平台的差异可能你无法使用以上指出的命令来观测udp溢出、丢包等现象,那么可以采用OSwatcher工具来收集相关的网络信息。

ORA-00600:[kclchkinteg_2]及[kjmsm_epc]内部错误一例

一套AIX上的9.2.0.6 RAC系统,alert日志中最初报LMS进程(Lock Manager Server process,即锁服务管理进程,仅出现在RAC系统中)遭遇ORA-00600[kclchkinteg_2],继而出现ORA-00600[[kjmsm_epc]内部错误导致实例crash(instance crashed)。相关日志如下:

Tue Dec 1 01:20:25 2009
Errors in file /oracle/admin/crmdb/bdump/crmdb1_lms7_2143190.trc:
ORA-00600: internal error code, arguments: [kclchkinteg_2], [], [], [], [], [], [], []
Tue Dec 1 01:20:25 2009
Errors in file /oracle/admin/crmdb/bdump/crmdb1_lms7_2143190.trc:
ORA-00600: internal error code, arguments: [kclchkinteg_2], [], [], [], [], [], [], []
Tue Dec 1 01:20:26 2009
Trace dumping is performing id=[cdmp_20091201012026]
Tue Dec 1 01:20:40 2009
Errors in file /oracle/admin/crmdb/bdump/crmdb1_lms7_2143190.trc:
ORA-00600: internal error code, arguments: [kjmsm_epc], [], [], [], [], [], [], []
Tue Dec 1 01:20:41 2009
Errors in file /oracle/admin/crmdb/bdump/crmdb1_lms7_2143190.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [kjmsm_epc], [], [], [], [], [], [], []
Tue Dec 1 01:20:43 2009

经过和metalink确认,认为此次实例意外终止时由于”Bug 3671867 – OERI[kclchkinteg_2] possible in RAC environment”:

Hdr: 3671867 10.1.0.2 RDBMS 10.1.0.2 RAC PRODID-5 PORTID-212 ORA-600
Abstract: INSTANCE TERMINATED WITH ORA-600 [KCLCHKINTEG_2]
PROBLEM:
--------
During testing, one instance was terminated with ORA-600 [KCLCHKINTEG_2]

DIAGNOSTIC ANALYSIS:
--------------------
Alert log contains:

Thu Jun  3 12:57:26 2004
Private_strands 0 at log switch
Thread 1 advanced to log sequence 153
  Current log# 1 seq# 153 mem# 0: +EMDBD1/dbotazc0/onlinelog/group_1.267.1
  Current log# 1 seq# 153 mem# 1: +EMDBD1/dbotazc0/onlinelog/group_1.268.1
Thu Jun  3 13:41:45 2004
Private_strands 0 at log switch
Thread 1 advanced to log sequence 154
  Current log# 2 seq# 154 mem# 0: +EMDBD1/dbotazc0/onlinelog/group_2.269.1
  Current log# 2 seq# 154 mem# 1: +EMDBD1/dbotazc0/onlinelog/group_2.270.1
Thu Jun  3 13:55:06 2004
Errors in file /DBA/nest/oracle/DBOTA1/logs/dbota1_dbw0_82244.trc:
ORA-600: internal error code, arguments: [kclchkinteg_2], [], [], [], [],
[], [], []
Thu Jun  3 13:55:08 2004
Errors in file /DBA/nest/oracle/DBOTA1/logs/dbota1_dbw0_82244.trc:
ORA-600: internal error code, arguments: [kclchkinteg_2], [], [], [], [],
[], [], []
Thu Jun  3 13:55:08 2004
DBW0: terminating instance due to error 471
Thu Jun  3 13:55:08 2004
Trace dumping is performing id=[cdmp_20040603135508]
Thu Jun  3 13:55:10 2004
Dump system state for local instance only
Thu Jun  3 13:55:10 2004
Trace dumping is performing id=[cdmp_20040603135510]
The only trace file we have been sent is the dbw trace.
WORKAROUND:
-----------
None
RELATED BUGS:
-------------
There are several which end in KCLCHKINTEG but not KCLCHKINTEG_2
REPRODUCIBILITY:
----------------
Appears only to have happened once so far.
TEST CASE:
----------
N/A
STACK TRACE:
------------
ksedmp ksfdmp kgerinv kgeasnmierr kclassertle kclchkinteg kclfwrite1 kcbbic1
kcbbiop kcbbdrv ksbabs ksbrdp opirip opidrv sou2o main start

SUPPORTING INFORMATION:
-----------------------
Bruce Carter has looked at this and suggested a bug be raised.
Affects:
    Product (Component)	Oracle Server (Rdbms)
    Range of versions believed to be affected	Versions < 10.2
    Versions confirmed as being affected
        * 10.1.0.3
    Platforms affected	Generic (all / most platforms affected)
Fixed:
    This issue is fixed in
        * 9.2.0.7 (Server Patch Set)
        * 10.1.0.4 (Server Patch Set)
        * 10.2.0.1 (Base Release)
Symptoms:
Related To:
    * Internal Error May Occur (ORA-600)
    * Instance May Crash
    * ORA-600 [kclchkinteg_2]
    * RAC (Real Application Clusters) / OPS
Description
    ORA-600 [kclchkinteg_2] possible in RAC environment

Oracle开发部分确认该3671867 bug已在9.2.0.8中得到修复,建议通过应用9.2.0.8补丁集或者打上bug 3671867的one-off patch来修复该问题;该Bug可能导致实例意外终止,因此其Severity也极高,值得手头仍有9i RAC系统需要管理的dba注意。

Oracle 11g中数据库能有多大?

根据11g release 2文档《Oracle® Database Reference 11g Release 2 (11.2)》,我们可以计算出11g中数据库大小的一个理论极限:

Physical Database Limits

Item Type of Limit Limit Value
Database Block Size Minimum 2048 bytes; must be a multiple of operating system physical block size
Database Block Size Maximum Operating system dependent; never more than 32 KB
Database Blocks Minimum in initial extent of a segment 2 blocks
Database Blocks Maximum per datafile Platform dependent; typically 222 – 1 blocks
Controlfiles Number of control files 1 minimum; 2 or more (on separate devices) strongly recommended
Controlfiles Size of a control file Dependent on operating system and database creation options; maximum of 20,000 x (database block size)
Database files Maximum per tablespace Operating system dependent; usually 1022
Database files Maximum per database 65533

May be less on some operating systems

Limited also by size of database blocks and by the DB_FILES initialization parameter for a particular instance

Database extents Maximum per dictionary managed tablespace 4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier)
Database extents Maximum per locally managed (uniform) tablespace 2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier)
Database file size Maximum Operating system dependent. Limited by maximum operating system file size; typically 222 or 4 MB blocks
MAXEXTENTS Default value Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
MAXEXTENTS Maximum Unlimited
Redo Log Files Maximum number of logfiles Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statementControl file can be resized to allow more entries; ultimately an operating system limit
Redo Log Files Maximum number of logfiles per group Unlimited
Redo Log File Size Minimum size 4 MB
Redo Log File Size Maximum Size Operating system limit; typically 2 GB
Tablespaces Maximum number per database 64 K

Number of tablespaces cannot exceed the number of database files because each tablespace must include at least one file

Bigfile Tablespaces Number of blocks A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 232 ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks.
Smallfile (traditional) Tablespaces Number of blocks A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (222) blocks.
External Tables file Maximum size Dependent on the operating system.An external table can be composed of multiple files.

11g中当采用32 K块时单个表空间最大可以扩展到128 TB,而一个数据库最多拥有64 K个表空间,则可以得出单个数据库的理论最大值为128TB *64 K= 8192 PB= 8EB,该数据与Oracle 10g 数据库的容量持平,换而言之11g中数据库的容量并未增加。

Youtube视频网站一年产生的信息量大约在800TB,世界上1000强的公司所有数据库的总数据量约合1EB(平均一个公司1PB),而64位可寻址内存的最大容量为16EB,理论上我们可以把所有这些少于8EB的信息存储到您的笔记本上安装的Oracle数据库中,而且可以将它们完全缓存到内存中以供读取。

在几年前没有人相信TB级别的数据库会在不久后普及,但是我们今天可以很自信地说随着TB级别的数据库不断涌现,无论从硬件角度或者软件角度支撑该数量级的数据库的solution都已经十分成熟了。

在11g未来的日子里,我们将注目于EB级数据库的出现!

发一个Oracle Dba招聘启事

公司招聘初中级Oracle Dba。主要向客户提供oracle数据库技术支持服务。
工作地点在上海,具体薪资视乎应聘者的技术水平。

具体要求:
1.熟悉Oracle基础理论和知识,对Oracle技术有持久的兴趣
2.熟悉数据库常见错误和简单性能问题的诊断
3.不对经验作强制要求,当然有经验更佳
4.熟悉unix/solaris操作系统/存储等硬件环境者优先
5.拥有ocp证书者优先

如有意向,请将简历发至 maclean.liu@parnassusdata.com
该职位长期招聘中!

Database Force open example

帮网友强制打开了一个没有备份的测试库,这个库没有备份也没有打开归档,因为之前也出现过active日志文件损毁,一直使用隐式参数才能正常打开:

                 _allow_resetlogs_corruption= TRUE

 

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638   QQ号:47079569    邮箱:service@parnassusdata.com

 

 

这次一开始这个库报ORA-600[2662]错误:

Mon Aug 23 09:37:00 2010
Errors in file /oracle/QAS/saptrace/usertrace/qas_ora_852096.trc:
ORA-00600: internal error code, arguments: [2662], [0], [130131504], [0], [130254136], [4264285], [], []
Mon Aug 23 09:37:02 2010
Errors in file /oracle/QAS/saptrace/usertrace/qas_ora_852096.trc:
ORA-00600: internal error code, arguments: [2662], [0], [130131506], [0], [130254136], [4264285], [], []

ORA-600 [2662] “Block SCN is ahead of Current SCN”错误是当数据块中的SCN领先于current SCN,由于后台进程或服务进程都会比对UGA中的dependent SCN和数据库当前的SCN,如果数据库当前SCN小于dependent SCN,那么该进程就会报ORA-600 [2662]错误,如果遭遇该错误的是服务进程,那么服务进程一般会异常终止;如果遭遇该错误的是后台进程譬如SMON,则会导致实例CRASH。
ORA-600 [2662]错误可以能由以下几种情况引起:
1.启用隐含参数_ALLOW_RESETLOGS_CORRUPTION后,以resetlogs形式打开数据库;这种情况下发生2662错误,根本原因是没有完全前滚导致控制文件中的SCN滞后于数据块中的SCN。
2.硬件故障导致数据库没法写控制文件和联机日志文件
3.错误的部分恢复数据库
4.恢复了控制文件,但是没有使用recover database using backup controlfile进行恢复
5.数据库crash后设置了_DISABLE_LOGGING隐含参数
6.在并行服务器环境中DLM存在问题

该错误的5个参数的具体含义如下:
ARGUMENTS:
Arg [a] Current SCN WRAP
Arg [b] Current SCN BASE
Arg [c] dependent SCN WRAP
Arg [d] dependent SCN BASE
Arg [e] Where present this is the DBA where the dependent SCN came from.

我们的case当中dependent SCN为130254136,而当前SCN为130131506,其差值为122630;从以上告警日志中可以看到数据库的当前SCN是在不断缓慢增长的,当我们遭遇到2662错误时,很滑稽的一点是只要不断重启数据库保持current SCN的增长,一段时间后2662错误会不药而愈。当然我们也可以不用这种笨办法,10015事件可以帮助我们调整数据库当前SCN:

/* 当数据库处于mount状态,可以使用10015事件来调整scn */

alter session  set events '10015 trace name adjust_scn level 1';

/* 这里可以设置level 2..10等 (level 1是在每次打开数据库时scn增加1000k)*/

/* 需要注意的是10g某些版本不同于9i,需要设置隐式参数_allow_error_simulation,才能真正增进scn */

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> col current_scn format 999,999,999,999

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    1141408

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size                  2213896 bytes
Variable Size             989857784 bytes
Database Buffers          654311424 bytes
Redo Buffers                7135232 bytes
Database mounted.

SQL> alter session set events '10015 trace name adjust_scn level 1';
Session altered.

SQL> alter database open;
Database altered.

SQL>  select current_scn from v$database;
CURRENT_SCN
-----------
    1142031

/* 可以看到current_scn并未大量增加,10.2.0.4上默认10015 adjust_scn不被触发 */

SQL>  alter system set "_allow_error_simulation"=true scope=spfile;
System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size                  2213896 bytes
Variable Size             989857784 bytes
Database Buffers          654311424 bytes
Redo Buffers                7135232 bytes
Database mounted.

SQL> alter session set events '10015 trace name adjust_scn level 1';
Session altered.

SQL> alter database open;
Database altered.

SQL>select current_scn from v$database;
     CURRENT_SCN
----------------
   1,073,741,980

在接手之前该网友已经通过反复重启数据库将数据库的当前SCN提高到dependent SCN的127037138;原以为这样就可以打开数据库了,谁知道又出现了一下错误:

Wed Aug 25 07:43:53 2010
Errors in file /oracle/QAS/saptrace/usertrace/qas_ora_929958.trc:
ORA-00600: internal error code, arguments: [4000], [8], [], [], [], [], [], []
Wed Aug 25 07:43:53 2010
Errors in file /oracle/QAS/saptrace/usertrace/qas_ora_929958.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [4000], [8], [], [], [], [], [], []
Wed Aug 25 07:43:53 2010
Error 704 happened during db open, shutting down database

bootstrap自举过程中遭遇了ORA-600 [4000]错误,该错误一般当Oracle尝试读取数据字典(主要是undo$基表)中记录的USN对应的回滚段失败引起.,通过设置隐式参数_corrupted_rollback_segments可以一定程度上规避该错误,强制打开数据库,其Argument[a]代表造成读取失败的USN(undo segment number),但实际上有问题的回滚段可能不止这一个:

/* 通过strings工具从system表空间上找回各回滚段的名字  */
$strings system.dbf |grep _SYSSMU|less
_SYSSMU1$
_SYSSMU2$
_SYSSMU3$
_SYSSMU4$
_SYSSMU5$
_SYSSMU6$
_SYSSMU7$
_SYSSMU8$
_SYSSMU9$
.........
alter system set "_corrupted_rollback_segments"='(_SYSSMU1$, _SYSSMU2$, _SYSSMU3$, _SYSSMU4$, _SYSSMU5$, _SYSSMU6$, _SYSSMU7$, _SYSSMU8$, _SYSSMU9$, _SYSSMU10$, _SYSSMU11$, _SYSSMU12$)' scope=spfile;
System altered.

/* 即便设置了_corrupted_rollback_segments隐式参数,也还有一定概率遭遇4000错误,尝试加上10513事件,并多次重启数据库 */

SQL> alter system set event='10513 trace name context forever,level 2' scope=spfile;
System altered.

/* 再次出现4000 错误 */
Errors in file /oracle/QAS/saptrace/usertrace/qas_ora_1016014.trc:
ORA-00600: internal error code, arguments: [4000], [8], [], [], [], [], [], []
Thu Aug 26 09:43:39 2010
Errors in file /oracle/QAS/saptrace/usertrace/qas_ora_1016014.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [4000], [8], [], [], [], [], [], []
Thu Aug 26 09:43:39 2010
Error 704 happened during db open, shutting down database

/* 再次重启后发现4000错误不再出现 * /

再次重启发现不再出现ORA-600[4000]错误,但在字典检查阶段Oracle认为数据文件227不匹配于当前的incarnation:

Thu Aug 26 11:13:22 2010
Dictionary check beginning
Thu Aug 26 09:46:00 2010
Errors in file /oracle/QAS/saptrace/usertrace/qas_ora_897162.trc:
ORA-01177: data file does not match dictionary - probably old incarnation
ORA-01110: data file 227: '/oracle/QAS/sapdata2/qas_192/qas.data196'
Error 1177 happened during db open, shutting down database
USER: terminating instance due to error 1177
Instance terminated by USER, pid = 897162

初步判断出现ORA-01177可能为2种可能性:
1.数据字典出现讹误,227号文件对应的incarnation信息不正确
2.在之前的某次resetlogs open过程中,227号文件头由于某些原因没有正确更新incarnation信息

针对这样的情况如果一定要找回该数据文件上的数据的话只能通过手动修改数据字典或文件头,当然也可以尝试使用一些直接从数据文件上抽取数据的工具。
因为这是一次友情协助,就没有继续深入下去,通过重建控制文件并跳过该数据文件解决了:

CREATE CONTROLFILE REUSE DATABASE "QAS" RESETLOGS  NOARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE
    MAXLOGFILES 255
    MAXLOGMEMBERS 3
    MAXDATAFILES 254
    MAXINSTANCES 50
    MAXLOGHISTORY 36302
LOGFILE
  GROUP 1 (
    '/oracle/QAS/redolog/redolog11A.dbf',
    '/oracle/QAS/redolog/redolog11B.dbf'
  ) SIZE 500M,
  GROUP 2 (
    '/oracle/QAS/redolog/redolog12A.dbf',
    '/oracle/QAS/redolog/redolog12B.dbf'
  ) SIZE 500M
-- STANDBY LOGFILE
DATAFILE
  '/oracle/QAS/sapdata1/system_1/system.data1',
........
  '/oracle/QAS/sapdata2/qas_192/qas.data195'
CHARACTER SET WE8DEC
Thu Aug 26 14:04:50 2010
Successful mount of redo thread 1, with mount id 2117500093
Thu Aug 26 14:04:50 2010
Completed: CREATE CONTROLFILE REUSE DATABASE "QAS" RESETLOGS
Thu Aug 26 14:05:05 2010
alter database mount
Thu Aug 26 14:05:05 2010
ORA-1100 signalled during: alter database mount...
Thu Aug 26 14:05:15 2010
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 1125281471596
Resetting resetlogs activation ID 0 (0x0)
Online log 1 of thread 1 was previously cleared
Thu Aug 26 14:05:36 2010
Assigning activation ID 2117500093 (0x7e367cbd)
Thread 1 opened at log sequence 1
  Current log# 2 seq# 1 mem# 0: /oracle/QAS/redolog/redolog12A.dbf
  Current log# 2 seq# 1 mem# 1: /oracle/QAS/redolog/redolog12B.dbf
Successful open of redo thread 1
Thu Aug 26 14:05:36 2010
SMON: enabling cache recovery
Thu Aug 26 14:05:36 2010
Dictionary check beginning
Tablespace 'PSAPTEMP' #2 found in data dictionary,
but not in the controlfile. Adding to controlfile.
File #227 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00227' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #228 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00228' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #229 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00229' in the controlfile.
This file can no longer be recovered so it must be dropped.
Dictionary check complete
Thu Aug 26 14:05:38 2010
SMON: enabling tx recovery
Thu Aug 26 14:05:38 2010
Database Characterset is WE8DEC
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open resetlogs

这个case告诉我们,测试库并不一定就不重要,测试库也是需要备份的。

SGA_MAX_SIZE,SGA_TARGET以及PRE_PAGE_SGA参数

10g引入ASMM后SGA_TARGET取代shared_pool_size,db_cache_size等参数,成为DBA关注的主要SGA内存管理参数;有不少情况下SGA_TARGET参数会设置为一个小于SGA_MAX_SIZE的值(这样做在多实例情况下更具灵活性)。但不少人会问,这样岂不是要浪费一部分物理内存吗?Oracle会为实例分配SGA_MAX_SIZE大小的内存的,SGA_TARGET要设得和SGA_MAX_SIZE一样大才合理啊!

让我们来看看实际的情况:

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

/*  linux上的10.2.0.4  */

SQL> show parameter sga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 3600M
sga_target                           big integer 368M

SQL> col component for a25;
SQL> select component, current_size / 1024 / 1024, last_oper_type
  2    from v$sga_dynamic_components
  3   where current_size != 0;

COMPONENT                 CURRENT_SIZE/1024/1024 LAST_OPER_TYP
------------------------- ---------------------- -------------
shared pool                                   96 STATIC
large pool                                    16 STATIC
java pool                                     16 STATIC
DEFAULT buffer cache                         224 INITIALIZING

/* 此时的SGA_TARGET为368M,而SGA_MAX_SIZE为3600M */

/* 我们来看一下实际的物理内存使用情况  */

/* 以root用户登录,因为我们需要用到清理文件系统缓存的命令 */

[maclean@rh2 ~]$ su - root
Password:

[root@rh2 ~]# sync
[root@rh2 ~]# sync

/* sync 命令用以写出文件系统脏缓存,类似于Oracle的checkpoint手动检查点 */

[root@rh2 ~]# echo 3 > /proc/sys/vm/drop_caches
[root@rh2 ~]# echo 3 > /proc/sys/vm/drop_caches

/*  将proc文件系统下的drop_caches文件置为3,清理文件系统缓存,以免其干扰我们的实验 */

[root@rh2 ~]# free
             total       used       free     shared    buffers     cached
Mem:       4046352     429328    3617024          0        296     176100
-/+ buffers/cache:     252932    3793420
Swap:     10241428          0   10241428

/*  4g内存中仅有419M处于使用状态,其他均为free。可见Oracle没有为实例分配大小等于SGA_MAX_SIZE的内存空间,而接近于SGA_TARGET_SIZE的大小 */
[root@rh2 ~]# ps -ef|grep pmon|grep -v grep
maclean   6361     1  0 18:35 ?        00:00:00 ora_pmon_YOUYUS

[root@rh2 ~]# pmap -x 6361
6361:   ora_pmon_YOUYUS
Address           Kbytes     RSS   Dirty Mode   Mapping
0000000000400000  100412    7300       0 r-x--  oracle
000000000680f000     544     180      64 rwx--  oracle
0000000006897000     148     104     104 rwx--    [ anon ]
000000001e9d0000     672     532     532 rwx--    [ anon ]
0000000060000000 3688448    1044     388 rwxs-    [ shmid=0x390005 ]

/* 利用pmap工具探测Oracle后台进程的内存地址空间,可以看到这里虚拟共享内存段(也就是SGA)的大小为3602M */

[root@rh2 ~]# ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x74018366 3112960    root      600        4          0
0x00000000 3473409    root      644        80         2
0x74018365 3080194    root      600        4          0
0x00000000 3506179    root      644        16384      2
0x00000000 3538948    root      644        280        2
0x1cdd16e8 3735557    maclean   640        3776970752 15

/* 使用ipcs 命令也可以观察到这个大小为3602M的共享内存段 */

/* 可以猜测Oracle在这里使用malloc函数或类似的方法实现虚拟内存的分配,没有像calloc函数那样对空间中的每一位都初始为0,保证了不浪费物理内存  */

/*  我们动态修改SGA_TARGET参数来看看*  /

SQL> alter system set sga_target=3000M;

System altered.

SQL> select component, current_size / 1024 / 1024, last_oper_type
  2    from v$sga_dynamic_components
  3   where current_size != 0;

COMPONENT                 CURRENT_SIZE/1024/1024 LAST_OPER_TYP
------------------------- ---------------------- -------------
shared pool                                   96 STATIC
large pool                                    16 STATIC
java pool                                     16 STATIC
DEFAULT buffer cache                        2864 INITIALIZING

/*   BUFFER_CACHE 的空间大幅增加 * /

SQL> !free
             total       used       free     shared    buffers     cached
Mem:       4046352    3187784     858568          0        536    2889920
-/+ buffers/cache:     297328    3749024
Swap:     10241428          0   10241428

/* used mem部分上升到3113M,随着SGA_TARGET的动态增长而增长了 */

/* 如果我们改小SGA_TARGET呢? */

SQL> alter system set sga_target=368M;

System altered.

SQL> select component, current_size / 1024 / 1024, last_oper_type
  2    from v$sga_dynamic_components
  3   where current_size != 0;

COMPONENT                 CURRENT_SIZE/1024/1024 LAST_OPER_TYP
------------------------- ---------------------- -------------
shared pool                                   96 STATIC
large pool                                    16 STATIC
java pool                                     16 STATIC
DEFAULT buffer cache                         224 SHRINK

/* 可以看到BUFFER CACHE最近执行了SHRINK收缩操作,SIZE下降到224M */

QL> !free
             total       used       free     shared    buffers     cached
Mem:       4046352    3204608     841744          0       1596    2912888
-/+ buffers/cache:     290124    3756228
Swap:     10241428          0   10241428

/* 此时OS层仍认为used memory部分为3130M;但可以放心,它们是可被其他进程复用的  * /

官方对pre_page_sga参数的定义是”PRE_PAGE_SGA determines whether Oracle reads the entire SGA into memory at instance startup. Operating system page table entries are then prebuilt for each page of the SGA. This setting can increase the amount of time necessary for instance startup, but it is likely to decrease the amount of time necessary for Oracle to reach its full performance capacity after startup.” 它决定Oracle是否在启动阶段将整个SGA读入内存,实际上在启动阶段SGA的内容是十分稀疏的,当pre_page_sga为TRUE时Oracle所要做的是向OS所要SGA_MAX_SIZE大小的实际物理页。

我们来看一下这个参数的实际效果:

SQL> alter system set sga_max_size=2500M scope=spfile;
System altered.

SQL> alter system set pre_page_sga=true scope=spfile;
System altered.

SQL> startup force ;
ORACLE instance started.

Total System Global Area 2634022912 bytes
Fixed Size                  2086288 bytes
Variable Size            2382367344 bytes
Database Buffers          234881024 bytes
Redo Buffers               14688256 bytes
Database mounted.
Database opened.

[root@rh2 ~]# echo 3 > /proc/sys/vm/drop_caches

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 2512M
sga_target                           big integer 368M

SQL> !free
             total       used       free     shared    buffers     cached
Mem:       4046352    2895256    1151096          0        648    2623692
-/+ buffers/cache:     270916    3775436
Swap:     10241428     104936   10136492

/*  设置pre_page_sga参数为true后,Oracle实际分配的物理内存不再局限于SGA_TARGET,而是在实例生命周期内几乎恒等于SGA_MAX_SIZE指定的值了 * /

/*  当SGA_TARGET配合pre_page_sga使用时,这的确可能造成一种浪费 ! * /

10g中Oracle已经强烈不建议使用pre_page_sga参数了,在ASMM下它带来的问题可不小!

undo backup optimization does not work on 11.2.0.1?

Backup Undo Optimization是11g的新特性之一,RMAN将避免备份撤销表空间上那些已提交事务的撤销数据。且该特性无法被禁用(You can enable and disable backup optimization, but backup undo optimization is built-in behavior.)。

我们在11.2.0.1版本上具体测试一下这个新特性:

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

/*  为了避免undo自动调优干扰我们的测试,修改_undo_autotune参数禁用该特性 */

SQL> alter system set "_undo_autotune"=false;
System altered.

/* 创建一个新的undo表空间,清理现场 */
SQL> create undo tablespace UNDOTEST datafile size 500M autoextend on next 50M maxsize unlimited;
Tablespace created.

SQL> alter system set undo_tablespace=UNDOTEST;
System altered.

/* 列出相关的undo参数,可以看到这里undo_retention参数设为极短的10s */

SQL> show parameter undo;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_undo_autotune                       boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     10
undo_tablespace                      string      UNDOTEST

RMAN> list backup;

specification does not match any backup in the repository

RMAN> backup tablespace UNDOTEST;

RMAN> list backup of tablespace UNDOTEST;

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    1.90M      DISK        00:00:00     25-AUG-10

/* undo表空间初始的备份大小为1.9M  */

SQL> conn maclean/maclean
Connected.

SQL> select count(*) from YOUYUS;
  COUNT(*)
----------
    579808

/* YOUYUS表有大约60万条数据,我们批量删除这些数据,将产生大量的undo*/

SQL> delete YOUYUS;
579808 rows deleted.

/*  此时再次执行备份undo表空间操作 */

RMAN> backup tablespace UNDOTEST;

RMAN>  list backup of tablespace UNDOTEST;

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    134.43M    DISK        00:00:03     25-AUG-10

/*  在存在大量active undo数据的情况下,备份文件增大到134M */

SQL> commit;
Commit complete.

SQL> exec dbms_lock.sleep(20);
PL/SQL procedure successfully completed.

SQL> select status,sum(blocks) from dba_undo_extents group by status;
STATUS    SUM(BLOCKS)
--------- -----------
UNEXPIRED        2696
EXPIRED         32936

/* commit后等待20s,确定没有active的撤销段 */

RMAN> backup tablespace UNDOTEST;

RMAN> list backup of tablespace UNDOTEST;
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    134.49M    DISK        00:00:02     25-AUG-10

/* 备份文件还要大于commit前,undo backup optimization居然没有起作用? */

/* 这个会是BUG吗? */

根据以上情况我提交了SR,ORACLE GCS给出的回复:

Bug 6399468: UNDO OPTIMIZATION
====> Undo optimization was changed from ‘all undo not needed’ to ‘undo not in use older than 1 hour’
In unpublished bug 6399468 DEV has confirmed the Undo optimization was changed from ‘all undo not needed’ to ‘undo not in use older than 1 hour’. For your last test case please wait for 1 hour and try backup again.

居然又是一个unpublished的BUG,Oracle DEV部门确认了backup undo optimization所避免备份的是1个小时以上未被尝试使用的undo,而非所有不再需要的undo。这是典型的开发部门和文档撰写部门间没有充分交流造成的问题!

/* 尝试等待3600s */

SQL> exec dbms_lock.sleep(3600);
PL/SQL procedure successfully completed.

/* 3600s还真漫长....... */

RMAN> backup tablespace UNDOTEST;

RMAN> list backup of tablespace UNDOTEST;
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10      Full    15.01M     DISK        00:00:00     25-AUG-10

/* 备份集缩小到15m,undo backup optimization起到了作用!*/

That's great!

关于DataPump的external_table模式

在pre10g的很长时间内,Oracle仅提供exp/imp导入导出工具,虽然这2个实用程序十分有效(现在也是如此),但因为它们受限于client/server模式工具自身的限制,以普通用户程序的身份来运转数以TB计的数据,其才不堪大用!DataPump是10g以后主推的数据抽取/导入工具,不同于exp/imp工具,DataPump是一个服务器端的实用程序,因为运行在服务器上故而DataPump进程可以直接访问数据文件与SGA(无需借shadow进程之手),与exp/imp工具相比使用DataPump可以获得显著的性能改善。DataPump可以通过直接路径或外部表路径这两种方法导出数据;其中直接路径避开了数据库高速缓存。当使用直接路径模式抽取数据时,DataPump从磁盘直接读取数据文件,抽取和格式化文件内容,最后将内容写出到转储文件。该种模式和SGA交互等待少,其导入导出速度直接取决于数据库所在磁盘速度和cpu;因此,直接路径极为快速。

外部表路径模式将使用到数据库的高速缓存buffer cache,通过外部表路径方法导出数据时,DataPump使用普通的SELECT操作将数据块从数据文件中读入buffer cache,为了写出转储文件,数据会在缓存中被格式化。通过外部表路径导入数据时,DataPump根据转储文件的内容构造标准的插入语句,并且通过将数据块从数据文件读至缓存来执行这些语句,插入操作按照标准的样式在缓存中完成;如同任何普通DML操作一样,外部表路径也会同时产生撤销和重做。

DataPump自身会根据对象的复杂性作出使用直接路径还是外部表路径的选择;对于较复杂的对象(后文将列出)而言,为了分解复杂性而必须同SGA进行交互,此情况下Data Pump只能采用外部表模式。我们还可以通过使用access_method参数来控制其行为,当然这仅在我们确认Data Pump作出了错误选择时才有必要。

若满足右列条件EXPDP将采用direct_path即直接路径模式 表结构允许使用直接路径模式,举例而言:

表上没有启用针对SELECT操作的fine-grained access control

非队列表(queue table)

表上没有BFILE和opaque类型的列,或包含有opaque列的对象类型

表上没有加密列

表上没有被废弃的旧类型列

若表上存在LONG或LONG RAW类型列,则此列只能是最后一列
使用Expdp执行导出任务时没有为相关表指定QUERY, SAMPLE, or REMAP_DATA等参数

需要导出的表或分区相对较少(多达250M),亦或者表或分区其实很大,但导出任务无法工作在并行模式(未指定parallel参数,或parallel参数设置为1)

若满足右列条件EXPDP将采用external_table即外部表模式 数据结构不满足在直接路径模式下抽取的条件,举例而言:

表上启用了针对SELECT操作的精细粒度控制

队列表

表上包含了BFILE或opaque类型列,或者包含有opaque列的对象类型

表上存在加密列

表上存在被废弃的旧类型列

表上存在LONG或LONG RAW类型列,且不是最后列

数据结构满足使用直接路径模式的条件,但执行导出任务时相关表上指定了QUERY, SAMPLE, or REMAP_DATA等参数

数据结构满足使用直接路径模式的条件,但相关的表或分区相对较大(大于250M),此时并行SQL可以用来加速数据抽取

若满足右列条件IMPDP将采用direct_path即直接路径模式 数据结构满足使用直接路径模式的条件,举例而言:

当导入某单一表分区时该分区表上没有建立全局索引,这一点也包括分区的对象表

没有基于LOB列建立的域索引(domain index)

非cluster表

表上没有BFILE列或opaque类型列

表上没有嵌入了opaque类型的VARRAY列

表上没有加密列

没有启用补全日志(Supplemental logging)且表上没有LOB类型列

若导入表已预先建立了表建构,则需满足以下条件:

表上没有激活的触发器 并且 若是分区表则应有索引 并且 表上上没有启用针对INSERT操作的精细粒度控制 并且 表上除去check类型约束不存在其他类型约束 并且 表上没有unique的索引

执行导入任务时没有为相关表指定QUERY, REMAP_DATA等参数

需要导入的表或分区相对较小(少于250M),或者表或分区其实很大,但无法以并行模式导入(未指定parallel参数或指定其为1)

若满足右列条件IMPDP将采用external_table即外部表路径 当数据结构不满足在直接路径模式下导入的条件,举例而言:

当导入某单一表分区时该分区表上建有全局索引,这一点也包括分区的对象表

表上有基于LOB列建立的域索引(domain index)

cluster表

表上有BFILE列或opaque类型列

表上有嵌入了opaque类型的VARRAY列

表上有加密列

启用了补全日志且表上有至少一个LOB列

若导入表已预先建立了表建构,且满足以下条件:

表上有激活的触发器 或者

是分区表且没有任何索引 或者

表上启用了针对INSERT操作的精细粒度控制 或者

表上除去check类型约束还还有其他类型约束 或者

表上有unique的索引

执行导入任务时有为相关表指定QUERY, REMAP_DATA等参数

数据结构满足使用直接路径模式的条件,但相关的表或分区相对较大(大于250M),此时并行SQL可以用来加速数据导入

我们有必要深入了解一下外部表路径究竟是如何工作的:

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.

/*  执行expdp导出任务,TCACHE表大小为465M,这里显示指定了外部表路径 */

[maclean@rh2 ~]$ expdp maclean/maclean directory=dumps tables=TCACHE dumpfile=tcache1.dmp access_method=external_table
Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 24 August, 2010 20:31:48
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "MACLEAN"."SYS_EXPORT_TABLE_07":  maclean/******** directory=dumps tables=TCACHE dumpfile=tcache1.dmp access_method=external_table
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 544 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "MACLEAN"."TCACHE"                          465.6 MB 4999999 rows
Master table "MACLEAN"."SYS_EXPORT_TABLE_07" successfully loaded/unloaded
******************************************************************************
Dump file set for MACLEAN.SYS_EXPORT_TABLE_07 is:
  /s01/dump/tcache1.dmp
Job "MACLEAN"."SYS_EXPORT_TABLE_07" successfully completed at 20:32:18

/*  这次我们指定了并行度为4  */

[maclean@rh2 ~]$ expdp maclean/maclean directory=dumps tables=TCACHE dumpfile=tcache2.dmp,tcache3.dmp,tcache4.dmp,tcache5.dmp access_method=external_table parallel=4
Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 24 August, 2010 20:32:49
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "MACLEAN"."SYS_EXPORT_TABLE_07":  maclean/******** directory=dumps tables=TCACHE dumpfile=tcache2.dmp,tcache3.dmp,tcache4.dmp,tcache5.dmp access_method=external_table parallel=4
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 544 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "MACLEAN"."TCACHE"                          465.6 MB 4999999 rows
Master table "MACLEAN"."SYS_EXPORT_TABLE_07" successfully loaded/unloaded
******************************************************************************
Dump file set for MACLEAN.SYS_EXPORT_TABLE_07 is:
  /s01/dump/tcache2.dmp
  /s01/dump/tcache3.dmp
  /s01/dump/tcache4.dmp
Job "MACLEAN"."SYS_EXPORT_TABLE_07" successfully completed at 20:33:04

SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/awrrpt

接着我们分析该时段内的awr报告!

从AWR报告中,我们不难找到以下语句:
SQL ID 3qwsywpvtdyjp:
CREATE TABLE "ET$088200010001" ( "OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY" ) ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY DUMPS ACCESS PARAMETERS (DEBUG =0 DATAPUMP INTERNAL TABLE "MACLEAN"."TCACHE" JOB ( "MACLEAN", "SYS_EXPORT_TABLE_07", 1) WORKERID 1 PARALLEL 1 VERSION COMPATIBLE ENCRYPTPASSWORDISNULL ) LOCATION ('bogus.dat') ) PARALLEL 1 REJECT LIMIT UNLIMITED AS SELECT /*+ PARALLEL(KU$, 1) */ "OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY" FROM RELATIONAL("MACLEAN"."TCACHE" ) KU$

SQL ID 1rxax27p7anr3:
CREATE TABLE "ET$088000020001" ( "OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY" ) ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY DUMPS ACCESS PARAMETERS (DEBUG =0 DATAPUMP INTERNAL TABLE "MACLEAN"."TCACHE" JOB ( "MACLEAN", "SYS_EXPORT_TABLE_07", 1) WORKERID 2 PARALLEL 2 VERSION COMPATIBLE ENCRYPTPASSWORDISNULL ) LOCATION ('bogus.dat') ) PARALLEL 2 REJECT LIMIT UNLIMITED AS SELECT /*+ PARALLEL(KU$, 2) */ "OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY" FROM RELATIONAL("MACLEAN"."TCACHE" ) KU$

以上就是Expdp在导出数据时使用的创建组织类型为ORACLE_DATAPUMP的外部表的SQL语句;后者语句中指定的并行度为2,而非我所指定的4,因该是Oracle考虑到所需导出表的具体大小,设置过高的并行度反而会适得其反,所以对设定值做了修正。

启用了并行的SQL 1rxax27p7anr3执行时间为22.63s,反而要比不使用并行慢;可见在TCACHE表这个数量级,完全没有必要使用并行导出。

接着我们来探究一下外部表路径的导入:

SQL> truncate table tcache;
Table truncated.
SQL> set pagesize 1400;
SQL> set linesize 140;

/* 执行外部表路径的数据导入,并行度1  */

[maclean@rh2 admin]$ impdp maclean/maclean directory=dumps tables=TCACHE content=data_only  dumpfile=tcache1.dmp access_method=external_table

/* 在以上SQL执行期间查询V$SQL动态视图的相关语句*/

select sql_text,
       sql_id,
       cpu_time,
       elapsed_time,
       disk_reads,
       buffer_gets,
       rows_processed
  from v$sql
 where sql_text like '%TCACHE%'
   and sql_text not like '%like%';

SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID          CPU_TIME ELAPSED_TIME DISK_READS BUFFER_GETS ROWS_PROCESSED
------------- ---------- ------------ ---------- ----------- --------------
INSERT /*+ SYS_DL_CURSOR */ INTO RELATIONAL("MACLEAN"."TCACHE") ("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT
_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDARY") VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N
ULL,NULL,NULL,NULL,NULL)
6tn47a220d34q        408          408          0           0              0

INSERT /*+APPEND+*/  /*+PARALLEL("TCACHE",1)+*/ INTO RELATIONAL("MACLEAN"."TCACHE" ) ("OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID",
 "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY")     SELECT "OWNE
R", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPO
RARY", "GENERATED", "SECONDARY"     FROM "ET$087B00060001" KU$
66q4w8c4ak0wf      31576        31576          0         608              0

/* 当导入job完成后再次查询 */
select sql_text,
       sql_id,
       cpu_time,
       elapsed_time,
       disk_reads,
       buffer_gets,
       rows_processed
  from v$sql
 where sql_text like '%TCACHE%'
   and sql_text not like '%like%';

SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID          CPU_TIME ELAPSED_TIME DISK_READS BUFFER_GETS ROWS_PROCESSED
------------- ---------- ------------ ---------- ----------- --------------
INSERT /*+ SYS_DL_CURSOR */ INTO RELATIONAL("MACLEAN"."TCACHE") ("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT
_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDARY") VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N
ULL,NULL,NULL,NULL,NULL)
6tn47a220d34q        408          408          0           0              0

/* 执行完成后一句SQL立马消失了!? Oracle好像很不情愿我们看到外部表路径使用的INSERT语句 :) */

INSERT /*+APPEND+*/ /*+PARALLEL("TCACHE",1)+*/
INTO RELATIONAL
  ("MACLEAN"."TCACHE")
  ("OWNER",
   "OBJECT_NAME",
   "SUBOBJECT_NAME",
   "OBJECT_ID",
   "DATA_OBJECT_ID",
   "OBJECT_TYPE",
   "CREATED",
   "LAST_DDL_TIME",
   "TIMESTAMP",
   "STATUS",
   "TEMPORARY",
   "GENERATED",
   "SECONDARY")
  SELECT "OWNER",
         "OBJECT_NAME",
         "SUBOBJECT_NAME",
         "OBJECT_ID",
         "DATA_OBJECT_ID",
         "OBJECT_TYPE",
         "CREATED",
         "LAST_DDL_TIME",
         "TIMESTAMP",
         "STATUS",
         "TEMPORARY",
         "GENERATED",
         "SECONDARY"
    FROM "ET$087F00230001" KU$

/* 这里的KU$代表的就是以ORACLE_DATAPUMP转储文件形式存放在数据库外的外部表 */

/* 可以看到这里尝试使用了APPEND插入模式,以减少undo和redo的产生*/

SQL> truncate table tcache;
Table truncated.

/* 若启用并行导入呢? */

[maclean@rh2 admin]$ impdp maclean/maclean directory=dumps tables=TCACHE content=data_only  dumpfile=tcache2.dmp,tcache3.dmp,tcache4.dmp access_method=external_table parallel=3
........

select sql_text,
       sql_id,
       cpu_time,
       elapsed_time,
       disk_reads,
       buffer_gets,
       rows_processed
  from v$sql
 where sql_text like '%TCACHE%'
   and sql_text not like '%like%';
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID          CPU_TIME ELAPSED_TIME DISK_READS BUFFER_GETS ROWS_PROCESSED
------------- ---------- ------------ ---------- ----------- --------------
INSERT /*+APPEND+*/  /*+PARALLEL("TCACHE",1)+*/ INTO RELATIONAL("MACLEAN"."TCACHE" ) ("OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID",
 "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY")     SELECT "OWNE
R", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPO
RARY", "GENERATED", "SECONDARY"     FROM "ET$087A00040001" KU$
2mvs15623ssvy    5910289      5910289          0       73578              0

INSERT /*+ SYS_DL_CURSOR */ INTO RELATIONAL("MACLEAN"."TCACHE") ("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT
_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDARY") VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N
ULL,NULL,NULL,NULL,NULL)
6tn47a220d34q       1167         1167          0           0              0

/* 似乎因为沿用了APPEND插入模式,插入语句的并行度并未上升 */

/* 因为Oracle不想同我们分享它的小秘密,所以awr和ash报告中都不记录外部表路径插入SQL的相关信息 */

/* 好吧,它有权保持沉默!  */

That's great!

沪ICP备14014813号-2

沪公网安备 31010802001379号