DBCA Fails With ORA-15243

今天接到朋友的电话说他们装ORACLE 11G R1 RAC的时候遇到ORA-12801/ORA-15243错误,请求我帮忙解决
具体情况
AIX系统以前装过11G R2 RAC,现因为项目要求11G R1,已经重装了系统,然后安装R1,在安装到DBCA配置ASM的时候,出现ORA-12801/ORA-15243错误

ORA-12801: error signaled in parallel query server PZ99, instance wmsdb1:+ASM1(1)
ORA-15243: 11.2.0.0.0 is not a valid version number


通过SQLPLUS登录ASM1实例查询发现该有一个ORADATA磁盘组,包含了一个/dev/rhdisk1.通过询问,得出结论是这个磁盘组以前是安装R2的时候作为存储OCR和VOTINGDISK使用,重装系统的时候未对该磁盘进行处理.

处理思路[想办法清除磁盘中asm信息]
1.尝试通过sqlplus 删除该磁盘组,报该磁盘组处于dismount状态
2.尝试mount该磁盘组,提示版本无效(ORA-15243)[当前的asm程序是11.1而磁盘组信息是11.2 程序当然不一致了]
3.直接使用dd清理该asm disk header信息(dd if=/dev/zero of=/dev/rhdisk1 bs=4096 count=1)
4.重新运行dbca一切工作正常

MOS中相关文章[1460997.1]只适合linux asmlib情况

Applies to:
Oracle Server - Enterprise Edition - Version 11.1.0.7 and later
Information in this document applies to any platform.
Symptoms
On : 11.1.0.7 version, STORAGE
When attempting to create database or query gv$asm_diskgroup,
the following error occurs.
ERROR
-----------------------
ORA-12801: error signaled in parallel query server PZ99, instance dchilcmsdb2.hq.navteq.com:+ASM2 (2)
ORA-15243: 11.2.0.0.0 is not a valid version number
STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Previously had 11GR2 installed and configured. Removed this installation then installed
   11.1.0.7  and created diskgroups using some of the same disks previously used.
2. Attempt to create database and receive the errors. Drop the newly
   created diskgroups and query the view still get same errors.
BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot create new database.
Changes
 Removed 11.2.0.1 installation and installed 11.1.0.7 software without cleaning up all of
 the diskgroup information from previous installation.
Cause
All the current information shows that we are using correct binaries and
that the diskgroups that are being used have correct comparability settings.
HTML shows that the disks for the old diskgroup are still being discovered.
This in conjunction with the text of the error as follows shows that
we are picking up 11.2.0.0.0 as version from somewhere.
ORA-15243: 11.2.0.0.0 is not a valid version number
Problem was caused by the disks that had been used for the
OCR/Voting disk diskgroup in 11GR2 installation still being present and accessible.
Solution
As the root user execute /etc/init.d/oracleasm/deletedisk command against all the disks
that were previously used for the OCR/Voting disk diskgroup then try the operation again.

11g DirectPath Reads 噩梦案例

DirectPath Reads 说明
在oracle 11g以前的版本中，如果对大表进行全表扫描，wait event是：db file scattered read；在11g中，如果对大表进行全表扫描，wait event是：direct path read。在11g中，大表全表扫描时数据块不经过sga而直接进pga，这样会造成每次进行大表全表扫描，物理读都是很大，而在10g中，由于全表扫描的数据块在sga中已经存在，所以执行全表扫描时，它的物理读为0。但是这里主要是oracle在优化策略上的进步，即假定大表频繁全表扫描这种现象，在生产库上不会太多，通过把数据直接读入pga，进而减少了cache buffer的繁忙交换程度，提高了cache buffer的使用效率.

DirectPath Reads 优势
1. 减少了对栓的使用，避免可能的栓争用
2. 物理IO的大小不再取决于buffer_cache中所存在的块；试想某个8个块的extent中1,3,5,7号块在高速缓存中，而2,4,6,8块没有被缓存，传统的方式在读取该extent时将会是对2,4,6,8块进行4次db file sequential read,这是一种十分可怕的状况，其效率往往要比单次读取这个区间的所有8个块还要低得多，虽然Oracle为了避免这种情况总是尽可能的不缓存大表的块(读入后总是放在队列最冷的一端);而direct path read则可以完全避免这类问题，尽可能地单次读入更多的物理块。

DirectPath Reads 噩梦
这一切听起来都很美好,但是在大并发的OLTP系统中,这东西简直是一个噩梦.通过一个awr来说明该问题:这个是一个系统的awr报告,朋友反馈说系统有段运行缓慢,请求帮忙找出原因
分析总体信息

系统这段时间会话临时大幅度增加(从102增加到223),系统出现异常繁忙(60.62*16=969.92<2,454.52)
分析Load Profile信息

通过这个截图发现系统的业务不是很大,但是Physical reads参数异常
1.物理读大小:25071.1*8192/1024/1024=195.86796875M/S
2.物理读将近逻辑读一半,这个在一般系统中很难得到这个比例,进一步说明物理读过高

分析Top 5信息

这里可以发现direct path read等待很多

分析Host CPU

可以发现iowait很大占40.5%,io等待异常高(195M/S能不高吗？)
补充说明:在这里我们看到的%Idle=1-%System-%User不包括%WIO

处理建议
通过上面的评估,可以确定大部分是由于 导致了数据库的物理读过高,从而使得系统反应变慢,处理方法就是关闭掉11g该新特性
alter system set event= ‘10949 trace name context forever, level 1’ scope=spfile;
重启数据库

undo segment header坏块异常恢复

alert日志报ORA-00600[4137]与ORA-00600 [4198]错误
数据库报如下错误,运行一段时间数据库自动down掉

Fri Jul  6 18:00:40 2012
SMON: ignoring slave err,downgrading to serial rollback
Fri Jul  6 18:00:41 2012
Errors in file /usr/local/oracle/admin/techdb/bdump/techdb_smon_16636.trc:
ORA-00600: internal error code, arguments: [4137], [], [], [], [], [], [], []
ORACLE Instance techdb (pid = 8) - Error 600 encountered while recovering transaction (3, 17).
Fri Jul  6 18:00:41 2012
Errors in file /usr/local/oracle/admin/techdb/bdump/techdb_smon_16636.trc:
ORA-00600: internal error code, arguments: [4137], [], [], [], [], [], [], []
Fri Jul  6 18:05:53 2012
SMON: Restarting fast_start parallel rollback
Fri Jul  6 18:05:54 2012
Errors in file /usr/local/oracle/admin/techdb/bdump/techdb_p000_17124.trc:
ORA-00600: internal error code, arguments: [4198], [9], [], [], [], [], [], []
…………
Wed Jul  6 18:50:38 2012
Errors in file /usr/local/oracle/admin/techdb/bdump/techdb_pmon_4473.trc:
ORA-00474: SMON process terminated with error
Wed Jul  6 18:50:38 2012
PMON: terminating instance due to error 474

从三个地方得出3号回滚段异常
1.trace文件

SMON: about to recover undo segment 3
Parallel Transaction recovery caught exception 12801
Parallel Transaction recovery caught error 30317
*** 2012-07-06 17:55:19.042
SMON: Restarting fast_start parallel rollback
SMON: about to recover undo segment 3
SMON: mark undo segment 3 as available
SMON: about to recover undo segment 3
SMON: mark undo segment 3 as available
Parallel Transaction recovery caught exception 12801
Parallel Transaction recovery caught error 607
*** 2012-07-06 17:55:19.761
SMON: ignoring slave err,downgrading to serial rollback
SMON: about to recover undo segment 3
XID passed in =xid: 0x0003.011.00003c2b
XID from Undo block =xid: 0x0004.020.00002b35

2.alert中提示while recovering transaction (3, 17)
3.查询dba_rollback_segs发现_SYSSMU3$是NEED RECOVERY状态

尝试删除_SYSSMU3$
使用隐含参数_offline_rollback_segments= _SYSSMU3$

Fri Jul  6 18:16:19 2012
Completed: ALTER DATABASE OPEN
Fri Jul  6 18:16:56 2012
drop rollback segment "_SYSSMU3$"
Fri Jul  6 18:16:57 2012
Errors in file /usr/local/oracle/admin/techdb/udump/techdb_ora_17381.trc:
ORA-00600: internal error code, arguments: [kddummy_blkchk], [2], [41], [38508], [], [], [], []
Fri Jul  6 18:16:57 2012
Doing block recovery for file 2 block 41
Block recovery from logseq 209591, block 183 to scn 7788878085
Fri Jul  6 18:16:57 2012
Recovery of Online Redo Log: Thread 1 Group 1 Seq 209591 Reading mem 0
  Mem# 0 errs 0: /usr/local/oracle/oradata/techdb/redo01.log
Block recovery completed at rba 209591.225.16, scn 1.3493910790
ORA-607 signalled during: drop rollback segment "_SYSSMU3$"...
Fri Jul  6 18:16:57 2012
Corrupt Block Found
         TSN = 1, TSNAME = UNDOTBS1
         RFN = 2, BLK = 41, RDBA = 8388649
         OBJN = 0, OBJD = -1, OBJECT = _NEXT_OBJECT, SUBOBJECT =
         SEGMENT OWNER = SYS, SEGMENT TYPE = Invalid Type
Fri Jul  6 18:16:57 2012
Errors in file /usr/local/oracle/admin/techdb/bdump/techdb_smon_17367.trc:
ORA-00600: internal error code, arguments: [kddummy_blkchk], [2], [41], [38508], [], [], [], []
Doing block recovery for file 2 block 41
Block recovery from logseq 209591, block 183 to scn 7788878085
Fri Jul  6 18:17:46 2012
Errors in file /usr/local/oracle/admin/techdb/bdump/techdb_pmon_17355.trc:
ORA-00474: SMON process terminated with error
Fri Jul  6 18:17:46 2012
PMON: terminating instance due to error 474
Fri Jul  6 18:17:46 2012
Errors in file /usr/local/oracle/admin/techdb/bdump/techdb_dbw0_17361.trc:
ORA-00474: SMON process terminated with error
Fri Jul  6 18:17:46 2012
Errors in file /usr/local/oracle/admin/techdb/bdump/techdb_lgwr_17363.trc:
ORA-00474: SMON process terminated with error
Instance terminated by PMON, pid = 17355

这里可以看出在使用隐含参数删除异常回滚段的时候，因为该回滚段有坏块出现ORA-00600[kddummy_blkchk]使得数据库donw掉，重启过几次该库都因为这个错误直接down.
查看trace文件发现

SMON: about to recover undo segment 3
SMON: mark undo segment 3 as needs recovery
*** 2012-07-06 18:16:57.734
Block Checking: DBA = 8388649, Block Type = System Managed Segment Header Block
ERROR: SMU Segment Header Corrupted.  Error Code = 38508
ktu4smck: starting extent(0x77) of txn slot #0x11 is  invalid.
  valid value (0 - 0x76)
  TRN CTL:: seq: 0xed38 chd: 0x0020 ctl: 0x002a inc: 0x00000000 nfb: 0x0000
            mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x00a6610a.ed38.1d scn: 0x0001.d030de86
            Version: 0x01

因为该库是因为undo的3号回滚段的header出现坏块,即使使用了隐含参数屏蔽该回滚段恢复,smon进程依然会去读回滚段header,从而出现该错误导致直接down掉.

处理方案
1.使用隐含参数屏蔽异常回滚段_offline_rollback_segments= _SYSSMU3$
2.修改undo_tablespace=SYSTEM/undo_management=MANUAL
3.启动数据库,快速删除包含_SYSSMU3$ undo表空间
4.新建undo表空间
5.修改undo_tablespace=new_undo/undo_management=AUTO,除掉隐含参数
6.使用新参数文件重启数据库
7.建议:使用逻辑导出导入重建数据库

分析一例 TX Enqueue contention案例

应用反馈某个业务比较慢,需要紧急处理
查询等待事件

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
SQL> select A.INST_ID,count(*), event
  2    from Gv$session_wait a
  3  where event not in ('SQL*Net more data to client',
  4  'rdbms ipc message',
  5               'smon timer',
  6               'pmon timer',
  7               'SQL*Net message from client',
  8               'lock manager wait for remote message',
  9               'ges remote message',
 10               'gcs remote message',
 11               'gcs for action',
 12               'client message',
 13               'pipe get',
 14               'null event',
 15               'PX Idle Wait',
 16               'single-task message',
 17               'PX Deq: Execution Msg',
 18               'KXFQ: kxfqdeq - normal deqeue',
 19               'listen endpoint status',
 20               'slave wait',
 21               'wakeup time manager','jobq slave wait')
 22  group by INST_ID,event
 23  order by 1 desc,2 desc;
   INST_ID   COUNT(*) EVENT
---------- ---------- --------------------------------------
         2          8 enqueue
         2          1 async disk IO
         2          1 db file sequential read
         2          1 SQL*Net message to client
         2          1 PX Deq: reap credit
         1          2 global cache cr request
         1          1 async disk IO
         1          1 PX Deq: reap credit
         1          1 PX Deq: Execute Reply
9 rows selected.

发现enqueue等待有些多,怀疑是TX enquenue,查询阻塞者

SQL> set linesize 100
SQL> set pagesize 66
SQL> col c1 for a15
SQL> col c1 heading "Program Name "
SQL> select l.inst_id,l.SID,program c1,l.TYPE,l.ID1,l.ID2,l.LMODE,l.REQUEST
  2  from gv$lock l,gv$session s
  3  where l.type like 'TX' and l.REQUEST =6
  4  and l.inst_id=s.inst_id and l.sid=s.sid
  5  order by id1
  6  /
   INST_ID        SID Program Name    TY        ID1        ID2      LMODE    REQUEST
---------- ---------- --------------- -- ---------- ---------- ---------- ----------
         2        295 rtStopMain@zwq_ TX    1441805    2391806          0          6
                      bill_2 (TNS V1-
                      V3)
         2        992 rtStopMain@zwq_ TX    1441805    2391806          0          6
                      bill_2 (TNS V1-
                      V3)
         2       1238 rtStopMain@zwq_ TX    6946827    2546365          0          6
                      bill_2 (TNS V1-
                      V3)
         2       1298 rtStopMain@zwq_ TX    6946827    2546365          0          6
                      bill_2 (TNS V1-
                      V3)
         2       1684 rtStopMain@zwq_ TX    6946827    2546365          0          6
                      bill_2 (TNS V1-
                      V3)
         2       1553 rtStopMain@zwq_ TX    6946827    2546365          0          6
                      bill_2 (TNS V1-
                      V3)
         2         75 rtStopMain@zwq_ TX   12451856     199146          0          6
                      bill_2 (TNS V1-
                      V3)
         2       1125 rtStopMain@zwq_ TX   14352404      63837          0          6
                      bill_2 (TNS V1-
                      V3)

查询持有者

SQL> set linesize 100
SQL> set pagesize 66
SQL> col c1 for a15
SQL> col c1 heading "Program Name "
SQL> select l.inst_id,l.SID,program c1,l.TYPE,l.ID1,l.ID2,l.LMODE,l.REQUEST
  2  from gv$lock l,gv$session s
  3  where l.type like 'TX' and l.LMODE =6 and (l.ID1,l.ID2) in
  4  (select id1,id2 from gv$lock where type like 'TX' and REQUEST =6)
  5  and l.inst_id=s.inst_id and l.sid=s.sid
  6  order by id1
  7  /
   INST_ID        SID Program Name    TY        ID1        ID2      LMODE    REQUEST
---------- ---------- --------------- -- ---------- ---------- ---------- ----------
         2         75 rtStopMain@zwq_ TX    1441805    2391806          6          0
                      bill_2 (TNS V1-
                      V3)
         2        992 rtStopMain@zwq_ TX    6946827    2546365          6          0
                      bill_2 (TNS V1-
                      V3)
         2        295 rtStopMain@zwq_ TX   12451856     199146          6          0
                      bill_2 (TNS V1-
                      V3)
         2       1553 rtStopMain@zwq_ TX   14352404      63837          6          0
                      bill_2 (TNS V1-
                      V3)

通过持有者和阻塞者可以得出:
1.持有者和阻塞者都是在2号实例上
2.持有者75阻塞了295/992的会话
3.持有者992阻塞了1238/1298/1684/1553的会话
4.持有者295阻塞了75的会话
5.持有者1553阻塞了1125的会话
6.同时分析发现,所有的持有者sid也在阻塞者中,也就是持有者阻塞了某个sid,而自身又被其他sid给阻塞,形成了多级阻塞或者环.如:75阻塞了295,而295有阻塞了75;992阻塞了1553,而1553阻塞了1125

查询阻塞和持有者对象

SQL> set linesize 110
SQL> col c0 for 999
SQL> col c0 heading "INS"
SQL> col c1 for a15
SQL> col c1 heading "Program Name "
SQL> select inst_id c0,sid,program c1,ROW_WAIT_OBJ# object_no, ROW_WAIT_FILE# Rfile_no,
  2  ROW_WAIT_BLOCK# Block_no ,ROW_WAIT_ROW# Row_no
  3  from gv$session
  4  where (inst_id,sid) in (select inst_id,sid from gv$session_wait where p1='1415053318')
  5  /
 INS        SID Program Name     OBJECT_NO   RFILE_NO   BLOCK_NO     ROW_NO
---- ---------- --------------- ---------- ---------- ---------- ----------
   2         75 rtStopMain@zwq_    1323132         13     122601        111
                bill_2 (TNS V1-
                V3)
   2        295 rtStopMain@zwq_    1323132         13     122601        100
                bill_2 (TNS V1-
                V3)
   2        992 rtStopMain@zwq_    1323132         13     122601        101
                bill_2 (TNS V1-
                V3)
   2       1125 rtStopMain@zwq_    1323132         84      38445         70
                bill_2 (TNS V1-
                V3)
   2       1238 rtStopMain@zwq_    1323132         15     255066         41
                bill_2 (TNS V1-
                V3)
   2       1298 rtStopMain@zwq_    1323132         14     118411          8
                bill_2 (TNS V1-
                V3)
   2       1553 rtStopMain@zwq_    1323132         15     255066         19
                bill_2 (TNS V1-
                V3)
   2       1684 rtStopMain@zwq_    1323132         14     118411         21
                bill_2 (TNS V1-
                V3)
8 rows selected.
SQL> set linesize 100
SQL> set pagesize 100
SQL> col owner for a10
SQL> col object_name for a20
SQL> col object_type for a10
SQL> select owner,object_name,object_id,object_type
  2  from dba_objects
  3  where
  4  object_id in (select ROW_WAIT_OBJ# from gv$session
  5  where (inst_id, sid) in (select inst_id,sid from gv$session_wait where p1='1415053318'))
  6  /
OWNER      OBJECT_NAME           OBJECT_ID OBJECT_TYP
---------- -------------------- ---------- ----------
DBACCADM   DCUSTCREDITBALANCE      1323132 TABLE

通过查询的出来,所有操作的聚焦点都是在DBACCADM.DCUSTCREDITBALANCE表上面

查询相关sql语句

SQL> SQL> set linesize 120
SQL> set pagesize 66
SQL> col c0 for 999
SQL> col c0 heading "INS"
SQL> col c1 for a9
SQL> col c1 heading "OS User"
SQL> col c2 for a9
SQL> col c2 heading "Oracle User"
SQL> col c3 for a15
SQL> col c3 heading "Program Name"
SQL> col b1 for a9
SQL> col b1 heading "Unix PID"
SQL> col b2 for 9999 justify left
SQL> col b2 heading "ORA SID"
SQL> col b3 for 999999 justify left
SQL> col b3 heading "SERIAL#"
SQL> col sql_text for a45
SQL> set space 1
SQL> break on b1 nodup on c0 nodup on c3 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 2
SQL> select a.inst_id c0,b.sid b2,c.spid b1, b.program c3, b.username c2,b.serial# b3, a.sql_text
  2    from gv$sql a, gv$session b, gv$process c
  3   where
  4     a.address = b.sql_address
  5     and b.paddr = c.addr
  6     and a.hash_value = b.sql_hash_value
  7     and a.inst_id=b.inst_id and a.inst_id=c.inst_id
  8     and a.inst_id like '&inst_id' and b.sid like '&sid'
  9   order by c.spid,a.hash_value
 10  /
Enter value for inst_id: 2
Enter value for sid: 75
old   8:    and a.inst_id like '&inst_id' and b.sid like '&sid'
new   8:    and a.inst_id like '2' and b.sid like '75'
 INS ORA SID Unix PID  Program Name    Oracle Us SERIAL# SQL_TEXT
---- ------- --------- --------------- --------- ------- ---------------------------------------------
   2      75 1167392   rtStopMain@zwq_ DBCUSTOPR   42815 update dcustcreditbalance  set limit_owe=:b0,
                       bill_2 (TNS V1-                   unbill_fee=:b1,prepay_fee=:b2,owe_fee=:b3,op_
                       V3)                               time=sysdate where id_no=:b4
SQL> /
Enter value for inst_id: 2
Enter value for sid: 992
old   8:    and a.inst_id like '&inst_id' and b.sid like '&sid'
new   8:    and a.inst_id like '2' and b.sid like '992'
 INS ORA SID Unix PID  Program Name    Oracle Us SERIAL# SQL_TEXT
---- ------- --------- --------------- --------- ------- ---------------------------------------------
   2     992 2760870   rtStopMain@zwq_ DBCUSTOPR   56282 update dcustcreditbalance  set limit_owe=:b0,
                       bill_2 (TNS V1-                   unbill_fee=:b1,prepay_fee=:b2,owe_fee=:b3,op_
                       V3)                               time=sysdate where id_no=:b4
SQL> /
Enter value for inst_id: 2
Enter value for sid: 295
old   8:    and a.inst_id like '&inst_id' and b.sid like '&sid'
new   8:    and a.inst_id like '2' and b.sid like '295'
 INS ORA SID Unix PID  Program Name    Oracle Us SERIAL# SQL_TEXT
---- ------- --------- --------------- --------- ------- ---------------------------------------------
   2     295 1639008   rtStopMain@zwq_ DBCUSTOPR   35740 update dcustcreditbalance  set limit_owe=:b0,
                       bill_2 (TNS V1-                   unbill_fee=:b1,prepay_fee=:b2,owe_fee=:b3,op_
                       V3)                               time=sysdate where id_no=:b4

其他阻塞者和持有者执行sql语句均和该语句相同,省略其他查询.通过这些查询可以确定是因为对dcustcreditbalance表的更新操作导致了这样的现象发生.

处理方案
1.临时处理方案:kill掉持有者
2.永久处理方案:修改这部分程序业务逻辑

关闭数据库出现ORA-00379错误

关闭数据库出现ORA-00379错误

SQL> shutdown immediate
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-00379: 缓冲池 DEFAULT 中无法提供 8K 块大小的空闲缓冲区

查看内存分配

SQL> show parameter sga;
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 412M
sga_target                           big integer 0
SQL> select * from v$sgainfo;
NAME                                  BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size                      1333676 No
Redo Buffers                        6078464 No
Buffer Cache Size                 104857600 Yes
Shared Pool Size                  142606336 Yes
Large Pool Size                     4194304 Yes
Java Pool Size                     12582912 Yes
Streams Pool Size                         0 Yes
Shared IO Pool Size                       0 Yes
Granule Size                        4194304 No
Maximum SGA Size                  431038464 No
Startup overhead in Shared Pool    46137344 No
Free SGA Memory Available         159383552
--spfile中分配情况
orcl.__db_cache_size=104857600
orcl.__java_pool_size=12582912
orcl.__large_pool_size=4194304
orcl.__pga_aggregate_target=104857600
orcl.__sga_target=281018368
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=142606336
orcl.__streams_pool_size=0
--初始化参数
*.sga_max_size=0
*.sga_target=536870912
*.memory_max_target=536870912
*.memory_target=536870912

alert日志

Mon Jul 02 11:30:19 2012
DIA0 started with pid=8, OS id=1520
Errors in file e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_dia0_1520.trc  (incident=10883):
ORA-04030: out of process memory when trying to allocate 29916 bytes (heap_ksdhngreq,msg_body:ksdhng)
Errors in file e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_dia0_1520.trc  (incident=10884):
ORA-04030: out of process memory when trying to allocate 8204 bytes (diag pga,dbgtbDefaultBucket)
ORA-04030: out of process memory when trying to allocate 29916 bytes (heap_ksdhngreq,msg_body:ksdhng)
ORA-4030 : opidrv aborting process DIA0 ospid (1348_1520)
Errors in file e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_dia0_1520.trc  (incident=12013):
ORA-04030: out of process memory when trying to allocate 8204 bytes (diag pga,dbgtbDefaultBucket)
ORA-04030: out of process memory when trying to allocate 8204 bytes (diag pga,dbgtbDefaultBucket)
ORA-04030: out of process memory when trying to allocate 29916 bytes (heap_ksdhngreq,msg_body:ksdhng)
Process debug not enabled via parameter _debug_enable
Mon Jul 02 11:33:19 2012
Trace dumping is performing id=[cdmp_20120702113319]
Errors in file e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_smon_1820.trc:
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K
Mon Jul 02 11:33:49 2012
Errors in file e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_smon_1820.trc:
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K
Mon Jul 02 11:34:38 2012
Errors in file e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_smon_1820.trc:
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K
Mon Jul 02 11:37:05 2012
Errors in file e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_2400.trc:
ORA-00379: 缓冲池 DEFAULT 中无法提供 8K 块大小的空闲缓冲区
ORA-00379: 缓冲池 DEFAULT 中无法提供 8K 块大小的空闲缓冲区
ORA-00379: 缓冲池 DEFAULT 中无法提供 8K 块大小的空闲缓冲区
Tue Jul 03 09:58:06 2012
WARNING: sga_target 432013312 cannot be more than memory_target (432013312) -
pga_aggregate_target (104857600/0) or untunable pga 104857600, 73783296

通过这里可以看出,系统的data buffe和pga都有内存不足的报错.

解决问题
问题的原因是由于内存分配不多,导致sga组件被消耗完,现在数据库不能正常关闭,修改了相关的内存参数的配置[避免该bug采用asmm内存管理]也无法生效,现在需要做的任务是重启数据库.导致数据库不能被关闭的原因是因为data buffer中的脏数据不能写入新数据.查询MOS发现是Bug 7702085.正常关闭库解决办法手工刷sga组件,然后升级数据库到11.2.0.1 (Base Release)/11.1.0.7.3 (Patch Set Update)/11.1.0.7 Patch 25 on Windows Platforms

SQL>  alter system flush BUFFER_CACHE;
System altered.
SQL> alter system flush  SHARED_POOL;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

使用copy实现long类型转移表空间

在一次8.1.6的数据库恢复过程中,发现一个表空间的数据文件损坏,在转移该表空间相关表时,遇到让人郁闷的long类型.不能使用ctas和move来实现转移,最后通过古老的copy来实现该项工作.
模拟LONG类型表

SQL> create table chf.t_long (id number,name long) tablespace ts_xifenfei;
Table created.
SQL> insert into chf.t_long  select object_id,object_name from dba_objects where rownum<10;
9 rows created.
SQL> commit;
Commit complete.
SQL> desc chf.t_long
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 ID                                                 NUMBER
 NAME                                               LONG

测试ctas和move

SQL> create table chf.t_long_bak
  2  as
  3  select * from chf.t_long;
select * from chf.t_long
       *
ERROR at line 3:
ORA-00997: illegal use of LONG datatype
SQL> alter table chf.t_long move tablespace users;
alter table chf.t_long move tablespace users
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

使用copy实现LONG表跟换表空间

SQL> SET LONG 1000
SQL> select dbms_metadata.get_ddl('TABLE','T_LONG','CHF') from dual;
DBMS_METADATA.GET_DDL('TABLE','T_LONG','CHF')
--------------------------------------------------------------------------------
  CREATE TABLE "CHF"."T_LONG"
   (    "ID" NUMBER,
        "NAME" LONG
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS_XIFENFEI"
SQL>   CREATE TABLE "CHF"."T_LONG_BAK"
  2     (    "ID" NUMBER,
  3          "NAME" LONG
  4     ) SEGMENT CREATION IMMEDIATE
  5    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  6   NOCOMPRESS LOGGING
  7    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  8    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  9    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 10    TABLESPACE "USERS";
Table created.
SQL> copy from chf/xifenfei@ora11g_d INSERT chf.t_long_bak using select * from chf.t_long;
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
   9 rows selected from chf@ora11g_d.
   9 rows inserted into CHF.T_LONG_BAK.
   9 rows committed into CHF.T_LONG_BAK at DEFAULT HOST connection.
SQL> alter table t_long rename to t_long_old;
Table altered.
SQL> alter table t_long_bak rename to t_long;
Table altered.
SQL> select tablespace_name,table_name from dba_tables where table_name like 'T_LONG%';
TABLESPACE_NAME TABLE_NAME
--------------- ---------------
TS_XIFENFEI     T_LONG_OLD
USERS           T_LONG
SQL> DROP TABLE T_LONG_OLD PURGE;
Table dropped.

iscsiadm主要操作命令

当前包含磁盘

[root@xifenfei ~]# fdisk -l
Disk /dev/sda: 21.4 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1        2355    18916506   83  Linux
/dev/sda2            2356        2610     2048287+  82  Linux swap / Solaris
Disk /dev/sdb: 21.4 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1        2610    20964793+  83  Linux
Disk /dev/sdc: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Disk /dev/sdc doesn't contain a valid partition table
Disk /dev/sdd: 21.4 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
   Device Boot      Start         End      Blocks   Id  System
/dev/sdd1               1         100      803218+  83  Linux
/dev/sdd2             101        1000     7229250   83  Linux

查看iscsi运行情况

[root@xifenfei ~]# rpm -aq|grep iscsi
iscsi-initiator-utils-6.2.0.872-10.0.1.el5
[root@xifenfei ~]#  chkconfig --list |grep iscsi
iscsi           0:off   1:off   2:on    3:on    4:on    5:on    6:off
iscsid          0:off   1:off   2:off   3:on    4:on    5:on    6:off
[root@xifenfei ~]# ps -ef|grep iscs
root      2753     2  0 Jun21 ?        00:00:00 [iscsi_eh]
root     15793     1  0 09:08 ?        00:00:00 brcm_iscsiuio
root     15800     1  0 09:08 ?        00:00:00 iscsid
root     15802     1  0 09:08 ?        00:00:00 iscsid
root     19533 15269  0 10:11 pts/1    00:00:00 grep iscs

配置iscsi存储

[root@xifenfei ~]# iscsiadm -m discovery -t sendtargets -p 192.168.1.254:3260
192.168.1.254:3260,1 iqn.2006-01.com.openfiler:tsn.32b32087937b
[root@xifenfei ~]# iscsiadm -m node –T iqn.2006-01.com.openfiler:tsn.32b32087937b -p 192.168.1.254:3260 -l
Logging in to [iface: default, target: iqn.2006-01.com.openfiler:tsn.32b32087937b, portal: 192.168.1.254,3260]
Login to [iface: default, target: iqn.2006-01.com.openfiler:tsn.32b32087937b, portal: 192.168.1.254,3260] successful.
[root@xifenfei ~]# iscsiadm -m node –T iqn.2006-01.com.openfiler:tsn.32b32087937b -p 192.168.1.254:3260
>--op update -n node.startup -v automatic

当前包含磁盘

[root@xifenfei ~]# fdisk -l
Disk /dev/sda: 21.4 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1        2355    18916506   83  Linux
/dev/sda2            2356        2610     2048287+  82  Linux swap / Solaris
Disk /dev/sdb: 21.4 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1        2610    20964793+  83  Linux
Disk /dev/sdc: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Disk /dev/sdc doesn't contain a valid partition table
Disk /dev/sdd: 21.4 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
   Device Boot      Start         End      Blocks   Id  System
/dev/sdd1               1         100      803218+  83  Linux
/dev/sdd2             101        1000     7229250   83  Linux
Disk /dev/sde: 1073 MB, 1073741824 bytes
34 heads, 61 sectors/track, 1011 cylinders
Units = cylinders of 2074 * 512 = 1061888 bytes
Disk /dev/sde doesn't contain a valid partition table
Disk /dev/sdf: 1073 MB, 1073741824 bytes
34 heads, 61 sectors/track, 1011 cylinders
Units = cylinders of 2074 * 512 = 1061888 bytes
Disk /dev/sdf doesn't contain a valid partition table
Disk /dev/sdg: 1073 MB, 1073741824 bytes
34 heads, 61 sectors/track, 1011 cylinders
Units = cylinders of 2074 * 512 = 1061888 bytes
Disk /dev/sdg doesn't contain a valid partition table

卸载iscsi存储

[root@xifenfei ~]# iscsiadm -m node --logoutall=all
Logging out of session [sid: 3, target: iqn.2006-01.com.openfiler:tsn.32b32087937b, portal: 192.168.1.254,3260]
Logout of [sid: 3, target: iqn.2006-01.com.openfiler:tsn.32b32087937b, portal: 192.168.1.254,3260] successful.
[root@xifenfei ~]# iscsiadm -m node --op delete --targetname iqn.2006-01.com.openfiler:tsn.32b32087937b

当前包含磁盘

[root@xifenfei ~]# fdisk -l
Disk /dev/sda: 21.4 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1        2355    18916506   83  Linux
/dev/sda2            2356        2610     2048287+  82  Linux swap / Solaris
Disk /dev/sdb: 21.4 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1        2610    20964793+  83  Linux
Disk /dev/sdc: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Disk /dev/sdc doesn't contain a valid partition table
Disk /dev/sdd: 21.4 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
   Device Boot      Start         End      Blocks   Id  System
/dev/sdd1               1         100      803218+  83  Linux
/dev/sdd2             101        1000     7229250   83  Linux

iscsi操作总结

增加iscsi存储
(1)发现iscsi存储:iscsiadm -m discovery -t st -p ISCSI_IP
(2)查看iscsi发现记录:iscsiadm -m node
(3)登录iscsi存储:iscsiadm -m node -T LUN_NAME -p ISCSI_IP -l
(4)开机自动: iscsiadm -m node –T LUN_NAME -p ISCSI_IP --op update -n node.startup -v automatic
删除iscsi存储
(1)登出iscsi存储 iscsiadm -m node -T LUN_NAME -p ISCSI_IP -u
(2)对出iscsi所有登录 iscsiadm -m node --logoutall=all
(3)删除iscsi发现记录:iscsiadm -m node -o delete -T LUN_NAME -p ISCSI_IP
登入需验证码的节点
(1)开启认证
iscsiadm -m node -T LUN_NAME -o update --name node.session.auth.authmethod --value=CHAP
*.使用-o同--op
(2)添加用户
iscsiadm -m node -T LUN_NAME --op update --name node.session.auth.username --value=[用户名]
(3)添加密码
iscsiadm –m node –T LUN_NAME –op update –name node.session.auth.password –value=[密码]

gv$视图不能查询所有节点信息

今天遇到诡异的事情,AIX 5.3 ORACLE 9I RAC的gv$视图只能查询到本地的记录,而不是所有节点.但是所有节点均运行正常,除gv$视图之外未发现其他异常.
异常时节点1信息

SQL> show parameter clu;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string      192.168.6.24
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
SQL> col host_name for a10
SQL> select inst_id,HOST_NAME,STATUS from gv$instance;
   INST_ID HOST_NAME  STATUS
---------- ---------- ------------
         1 zwq_crm1   OPEN
SQL> show parameter par;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
log_parallelism                      integer     1
parallel_adaptive_multi_user         boolean     FALSE
parallel_automatic_tuning            boolean     FALSE
parallel_execution_message_size      integer     2152
parallel_instance_group              string
parallel_max_servers                 integer     5
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_server                      boolean     TRUE
parallel_server_instances            integer     2
parallel_threads_per_cpu             integer     2
partition_view_enabled               boolean     FALSE
recovery_parallelism                 integer     0
SQL> !ps -ef|grep p0
  oracrm 1929258       1   0 09:49:19      -  0:01 ora_p005_crm1
  oracrm  745844       1   0   Jun 26      -  0:20 ora_p004_crm1
  oraeye 2421272 3948648   0 21:53:49  pts/0  0:00 grep p0
  oracrm 3060406       1   0   Jun 26      -  0:20 ora_p002_crm1
  oracrm 3170868       1   0   Jun 20      -  2:13 ora_p000_crm1
  oracrm  787414       1   0   Jun 26      -  0:20 ora_p001_crm1
  oracrm 2552690       1   0   Jun 26      -  0:20 ora_p003_crm1

1.节点最大允许5个并发进程,现在已经启动并发进程到p005(6个)
2.gv$视图只能查询一个节点信息

异常时节点2信息
问题所有情况和1节点完全相似

SQL> col host_name for a10
SQL> select inst_id,HOST_NAME,STATUS from gv$instance;
   INST_ID HOST_NAME  STATUS
---------- ---------- ------------
         2 zwq_crm2   OPEN
SQL> show parameter par;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
log_parallelism                      integer     1
parallel_adaptive_multi_user         boolean     FALSE
parallel_automatic_tuning            boolean     FALSE
parallel_execution_message_size      integer     2152
parallel_instance_group              string
parallel_max_servers                 integer     5
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_server                      boolean     TRUE
parallel_server_instances            integer     2
parallel_threads_per_cpu             integer     2
partition_view_enabled               boolean     FALSE
recovery_parallelism                 integer     0
SQL> !ps -ef|grep p0
  oracrm 1867938       1   0 15:17:25      -  0:00 ora_p004_crm2
  oracrm 2633748       1   0 09:49:19      -  0:01 ora_p005_crm2
  oraeye 3059876 1007714   0 21:54:01  pts/0  0:00 grep p0
  oracrm  323884       1 120   Jun 20      - 10692:47 ora_p000_crm2
  oracrm 1839818       1   0 06:16:32      -  0:00 ora_p003_crm2
  oracrm  459660       1 107   Jun 26      - 1857:00 ora_p001_crm2
  oracrm 2351894       1   0 16:52:52      -  0:00 ora_p002_crm2

在异常2节点上做10046

SQL> oradebug setmypid
Statement processed.
SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 4
Statement processed.
SQL> select * from gv$version;
   INST_ID BANNER
---------- ----------------------------------------------------------------
         2 Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
         2 PL/SQL Release 9.2.0.8.0 - Production
         2 CORE 9.2.0.8.0       Production
         2 TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
         2 NLSRTL Version 9.2.0.8.0 - Production
SQL> oradebug EVENT 10046 trace name context off
Statement processed.
SQL> oradebug TRACEFILE_NAME
/oracle9/app/admin/crm/udump/crm2_ora_517066.trc

分析10046内容发现

PARSING IN CURSOR #1 len=24 dep=0 uid=0 oct=3 lid=0 tim=41759005850609 hv=775381991 ad='21195808'
select * from gv$version
END OF STMT
PARSE #1:c=20000,e=33894,p=1,cr=59,cu=3,mis=1,r=0,dep=0,og=4,tim=41759005850607
BINDS #1:
kxfpg1srv
        could not start P006, inst 1
kxfpg1srv
        could not start local P006
EXEC #1:c=0,e=3540,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=41759005854254
FETCH #1:c=0,e=28,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=41759005854347
FETCH #1:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=4,dep=0,og=4,tim=41759005854678
STAT #1 id=1 cnt=5 pid=0 pos=1 obj=0 op='VIEW  '
STAT #1 id=2 cnt=5 pid=1 pos=1 obj=304 op='FIXED TABLE FULL X$VERSION '

通过这里观察10046可以看到:在2节点上查询gv$instance,需要通过使用并发进程去访问1节点,但是因为1节点的parallel_max_servers为5,而当前的并发进程已经达到最大数目,从而使得想在节点1上启动并发失败,进入使得gv$视图只能查询出来本节点数据

gv$视图异常解决方法

--重启两个节点,查询正常
SQL> col host_name for a10
SQL> select inst_id,HOST_NAME,STATUS from gv$instance;
   INST_ID HOST_NAME  STATUS
---------- ---------- ------------
         1 zwq_crm1   OPEN
         2 zwq_crm2   OPEN

针对这个问题,很可能是oracle bug(因为是9i版本,我无法深究),或者是并发进程僵死所致,当时有一个想法,kill 掉数据库并发进程,因客户不同意(采用稳妥重启方案),未能通过尝试验证我的猜想.

对gv$视图正常做10046

SQL> oradebug setmypid
Statement processed.
SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
Statement processed.
SQL> select * from gv$version;
   INST_ID BANNER
---------- ----------------------------------------------------------------
         1 Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
         1 PL/SQL Release 9.2.0.8.0 - Production
         1 CORE 9.2.0.8.0       Production
         1 TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
         1 NLSRTL Version 9.2.0.8.0 - Production
         2 Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
         2 PL/SQL Release 9.2.0.8.0 - Production
         2 CORE 9.2.0.8.0       Production
         2 TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
         2 NLSRTL Version 9.2.0.8.0 - Production
10 rows selected.
SQL> oradebug EVENT 10046 trace name context off
Statement processed.
SQL> oradebug TRACEFILE_NAME
/oracle9/app/admin/crm/udump/crm1_ora_1708916.trc

分析10046内容

PARSING IN CURSOR #1 len=24 dep=0 uid=0 oct=3 lid=0 tim=41752681925071 hv=775381991 ad='92ef64f0'
select * from gv$version
END OF STMT
PARSE #1:c=0,e=6770,p=0,cr=12,cu=3,mis=1,r=0,dep=0,og=4,tim=41752681925070
BINDS #1:
WAIT #1: nam='PX Deq: reap credit' ela= 21 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Join ACK' ela= 237 p1=268500992 p2=1 p3=504403208016510312
WAIT #1: nam='PX Deq: reap credit' ela= 4 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Join ACK' ela= 64 p1=268500992 p2=2 p3=504403208016510312
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Join ACK' ela= 46 p1=268566528 p2=1 p3=504403208016502096
WAIT #1: nam='PX Deq: reap credit' ela= 4 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Join ACK' ela= 855 p1=268566528 p2=2 p3=504403208016502096
WAIT #1: nam='PX Deq: reap credit' ela= 4 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq Credit: send blkd' ela= 188 p1=268566528 p2=1 p3=504403208016502096
WAIT #1: nam='PX Deq: reap credit' ela= 1 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Parse Reply' ela= 406 p1=200 p2=1 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Parse Reply' ela= 7442 p1=200 p2=2 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 4 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Parse Reply' ela= 1664 p1=200 p2=1 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
EXEC #1:c=0,e=11572,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=41752681936727
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1650815232 p2=1 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Execute Reply' ela= 166 p1=200 p2=1 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Execute Reply' ela= 310 p1=200 p2=2 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
FETCH #1:c=0,e=557,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=41752681937320
WAIT #1: nam='SQL*Net message from client' ela= 223 p1=1650815232 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1650815232 p2=1 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 18 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='DFS lock handle' ela= 263 p1=1128857605 p2=9 p3=5
WAIT #1: nam='DFS lock handle' ela= 254 p1=1128857605 p2=9 p3=1
WAIT #1: nam='DFS lock handle' ela= 263 p1=1128857605 p2=9 p3=3
WAIT #1: nam='DFS lock handle' ela= 73 p1=1128857605 p2=9 p3=2
WAIT #1: nam='DFS lock handle' ela= 363 p1=1128857605 p2=9 p3=2
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 24 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Signal ACK' ela= 3 p1=0 p2=1 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Signal ACK' ela= 155 p1=10 p2=2 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 1 p1=0 p2=0 p3=0
FETCH #1:c=0,e=1735,p=0,cr=0,cu=0,mis=0,r=9,dep=0,og=4,tim=41752681939329
WAIT #1: nam='DFS lock handle' ela= 249 p1=1128857605 p2=9 p3=1
WAIT #1: nam='DFS lock handle' ela= 258 p1=1128857605 p2=9 p3=3
WAIT #1: nam='DFS lock handle' ela= 66 p1=1128857605 p2=9 p3=2
WAIT #1: nam='DFS lock handle' ela= 369 p1=1128857605 p2=9 p3=2
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 19 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 1 p1=0 p2=0 p3=0
WAIT #1: nam='enqueue' ela= 201 p1=1347616774 p2=2 p3=0
WAIT #1: nam='enqueue' ela= 41 p1=1347616774 p2=2 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 6981860 p1=1650815232 p2=1 p3=0
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='VIEW  '
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=304 op='FIXED TABLE FULL X$VERSION '

通过这里可以看出,rac在正常情况下gv$视图默认的查询就是并发方式进行

因未配置Hugepage会话数添增悲剧案例

今天一朋友反馈他们的一个数据库hang住了,通过ssh也不能登录系统,他们没有办法重启系统解决问题,现在想让我帮忙找出问题原因
分析awr得出


询问朋友,他们的库一般session保持在200个左右,这次突然飙升到750以上,属于异常情况

分析监听日志

看到在截图的时间内,整体访问较频繁,某个ip访问异常频繁,通过这些信息,初步怀疑是用户的数据库内存使用完,导致系统数据库hang住.

查看系统日志

Jun 26 14:35:55 result01 kernel: [5613531.566617] Free swap  = 0kB
Jun 26 14:35:55 result01 kernel: [5613531.566618] Total swap = 2104504kB
Jun 26 14:35:55 result01 kernel: [5613531.566620] Free swap:            0kB
Jun 26 14:35:55 result01 kernel: [5613531.591073] 2359296 pages of RAM
Jun 26 14:35:55 result01 kernel: [5613531.591074] 318236 reserved pages
Jun 26 14:35:55 result01 kernel: [5613531.591075] 73353 pages shared
Jun 26 14:35:56 result01 kernel: [5613531.591076] 529 pages swap cached
Jun 26 14:35:56 result01 kernel: [5613531.591079] Out of Memory: Kill process 8904 (oracle) score 891 and children.
Jun 26 14:35:56 result01 kernel: [5613531.591201] Out of memory: Killed process 8904 (oracle).
Jun 26 14:35:56 result01 kernel: [5613531.592280] oracle invoked oom-killer: gfp_mask=0x201d2, order=0, oomkilladj=0

通过这个日志看出系统内存和交换分区都使用完,因为内存不够,系统开始kill掉部分oracle进程.通过这些确定是系统内存使用完导致hang住可以理解.

分析hang住原因
为什么session意外的从200添增到750的时候,系统内存被使用完

cat /proc/meminfo
MemTotal:      8164240 kB
SwapTotal:     2104504 kB
PageTables:      69732 kB
HugePages_Total:     0
HugePages_Free:      0
HugePages_Rsvd:      0
Hugepagesize:     2048 kB
sga_target=3674210304
pga_aggregate_target=1732247552

从这里得出几个信息:
1.数据库总内存8g,swap配置2g
2.数据库未使用Hugepage
3.数据库设置sga和pga信息

内存参数估算
数据库总计占用内存为:(3674210304+1732247552)/1024/1024=5156M(pga可能未使用完,也可能超过)
结合实际sga_target=3674210304,会话数.
保守估计下Oracle进程占用的系统内存3674210304/(4*1024)*1.5*750/1024/1024=960M
估算如果使用Hugepage Oracle进程占用系统内存为:3674210304/(2*1024*1024)*1.5*750/1024/1024=1.9M
通过这里分析Oracle总占用内存为:5156+960=6116M
通过保守计算留给系统的内存大概为:1.8G左右
因为系统的其他操作,最终导致该系统内存耗完,系统和数据库hang住

总结说明
这是一个实实在在因为linux中因为未配置Hugepage,因为用户突增,导致系统内存消耗光,从而使得系统和数据库hang住的例子.
这个库因为sga不是非常大,所以Oracle占用系统内存不是高到离谱,如果sga配置为32g,1000个session,那就会占用12g的系统内存
通过这些可以看出在linux中配置Hugepage的优点:Hugepage不光是为了减轻cpu的负担,还可以减少系统内存的消耗;在没有极端的情况下,建议linux的数据库系统配置Hugepage.

To find the TX Enqueue contention in a RAC or OPS environment

今天查找TX Enqueue看到的一篇文章,拿出来共享下

PURPOSE
-------------
To find the TX Enqueue contention in a RAC or OPS environment
What is TX Enqueue ?
In one word oracle is maintaining queue for transaction.
How Many Resources ?
1/ active transaction
How Many Locks?
1/transaction + 1/process waiting for a locked row by that
transaction.
How Many Users?
1 + 1/ process waiting for something locked by this transaction.
Who Uses?
All processes
What need to investigate?
The mode of TX (6/4), Holding/Waiting/Requesting
SCOPE & APPLICATION
=====================
This document will help to analyze the application design related to transaction bottlenecks
and database performance tuning.
Let start with an example:
===================
create table akdas (A1 number, Col1 Varchar2(10), Col2 Varchar2(10));
insert into akdas values(5,'Hello','Hi');
insert into akdas values(6,'Sudip','Datta');
insert into akdas values(7,'Preetam','Roy');
insert into akdas values(8,'Michael','Polaski');
From Node 1:
==========
update akdas set a1=11 where a1=6;
From Node 2:
==========
update akdas set a1=12 where a1=7;
update akdas set a1=11 where a1=6;  /* this will wait for Node1: to complete the transaction */
This Note Is Made To Analyzing Only the TX-Mode-6 (Exclusive).
1. Now run the following query to track down the problem: Who is waiting
===================================================================
prompt
prompt Query 1. Waiting for TX Enqueue where mode is Exclusive
prompt =====================================
prompt
set linesize 100
set pagesize 66
col c1 for a15
col c1 heading "Program Name "
select l.inst_id,l.SID,program c1,l.TYPE,l.ID1,l.ID2,l.LMODE,l.REQUEST
from gv$lock l,gv$session s
where l.type like 'TX' and l.REQUEST =6
and l.inst_id=s.inst_id and l.sid=s.sid
order by id1
/
Output will be here
===============
   INST_ID      SID     Program Name       TY     ID1     ID2       LMODE      REQUEST
-----------  ---------- ------------------ ---   -------- --------  ---------- --------
         2           13  sqlplus@opcbsol   TX     393236  780       0          6
                         2 (TNS V1-V3)
It is clear that SID 12 of instance 2 is doing a DML and waiting on REQUEST Mode 6.
2. Let's run the next query to find who is holding
===========================================
prompt
prompt
prompt Query 2. Holding for TX Enqueue where mode greater than 6
prompt =======================================
prompt
set linesize 100
set pagesize 66
col c1 for a15
col c1 heading "Program Name "
select l.inst_id,l.SID,program c1,l.TYPE,l.ID1,l.ID2,l.LMODE,l.REQUEST
from gv$lock l,gv$session s
where l.type like 'TX' and l.LMODE =6 and (l.ID1,l.ID2) in
(select id1,id2 from gv$lock where type like 'TX' and REQUEST =6)
and l.inst_id=s.inst_id and l.sid=s.sid
order by id1
/
Output will be here
===============
   INST_ID      SID     Program Name      TY        ID1        ID2      LMODE    REQUEST
   ----------  ---------- -------------- ---   ---------- --------   ----------- --------
         1          12    sqlplus@opcbsol TX     393236        780      6          0
                          1 (TNS V1-V3)
So holder is SID 12 on instance 1. Where LMODE = 6.
3. Let's find out the exact file#, block# and Record# where it is waiting
===============================================================
prompt
prompt
prompt Query 3. Object# ,File#, Block# and Slot# TX Enqueue in detail
prompt ========================================
prompt
set linesize 110
col c0 for 999
col c0 heading "INS"
col c1 for a15
col c1 heading "Program Name "
select inst_id c0,sid,program c1,ROW_WAIT_OBJ# object_no, ROW_WAIT_FILE# Rfile_no,
ROW_WAIT_BLOCK# Block_no ,ROW_WAIT_ROW# Row_no
from gv$session
where (inst_id,sid) in (select inst_id,sid from gv$session_wait where p1='1415053318')
/
Output Will be here
===============
 INS     SID    Program Name     OBJECT_NO RFILE_NO BLOCK_NO  ROW_NO
----- ---------- -------------   ---------------    --------- -------
   2         13     sqlplus@opcbsol  7261      9        12346     1
                      2 (TNS V1-V3)
From the output, it is clear that it is waiting on Relative_File# 9, Block# 12346, Row Number 1.
Here Row Number 1 means the slot number in the block 12346. This Row_No start from 0 (zero).
4. Let's Find the object details
=============================
prompt
prompt
prompt Query 4. Object Involve for TX Enqueue in detail
prompt ===============================
prompt
set linesize 100
set pagesize 100
col owner for a10
col object_name for a20
col object_type for a10
select owner,object_name,object_id,object_type
from dba_objects
where
object_id in (select ROW_WAIT_OBJ# from gv$session
where (inst_id, sid) in (select inst_id,sid from gv$session_wait where p1='1415053318'))
/
Output Will be here
===============
OWNER      OBJECT_NAME  OBJECT_ID   OBJECT_TYP
---------  ------------ --------    -----------
AKDAS      AKDAS        7261        TABLE
5. Let’s find the row value details
=============================
prompt
prompt
prompt Query 5. Finding the row value
prompt ====================
prompt
select * from <Owner>.<Table Name>  where rowid like
DBMS_ROWID.ROWID_CREATE(1,&Object_No,&Rfile_No, &Block_No, &Row_Number)
/
From query 3 and 4  we will get the value for all variables.
Owner = AKDAS
Table_Name = AKDAS
Object_No = 7261
Rfile_No =  9
Block_No = 12346
Row_Number = 1
Output Will be here
===============
        A1    Col1                 Col2
  ---------- --------------- ----------
         6      Hello                Hi
So we can drag down to the row value where TX Enqueue contention exists.
6. Let’s find the user activity that is "Holder" and "Waiter"
====================================================
set linesize 120
set pagesize 66
col c0 for 999
col c0 heading "INS"
col c1 for a9
col c1 heading "OS User"
col c2 for a9
col c2 heading "Oracle User"
col c3 for a15
col c3 heading "Program Name"
col b1 for a9
col b1 heading "Unix PID"
col b2 for 9999 justify left
col b2 heading "ORA SID"
col b3 for 999999 justify left
col b3 heading "SERIAL#"
col sql_text for a45
set space 1
break on b1 nodup on c0 nodup on c3 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 2
select a.inst_id c0,b.sid b2,c.spid b1, b.program c3, b.username c2,b.serial# b3, a.sql_text
  from gv$sql a, gv$session b, gv$process c
 where
   a.address = b.sql_address
   and b.paddr = c.addr
   and a.hash_value = b.sql_hash_value
   and a.inst_id=b.inst_id and a.inst_id=c.inst_id
   and a.inst_id like '&inst_id' and b.sid like '&sid'
 order by c.spid,a.hash_value
/
This query asks the Instance Number and Sid number, which you can get from step 1 and 2.
But remember , you can see the waiter activity, but you may not see the holder activity.
Reason is, the holder is sitting idle after doing the DML operation. So SQL for Holder
should not be seen under gv$sql.
This all query can be run for single instance database, but all GV$ view need to replace to V$
and there is no INST_ID for V$ View, that part need to be taken care.

来自:How to Find TX Enqueue Contention in RAC or OPS [ID 179582.1]