分类目录归档：数据库

跟踪ORACLE非当前会话

发表于 2012 年 3 月 27 日 由 惜分飞

使用oradebug

session 1
--查询出需要跟踪会话v$process.pid

session 2
SQL> oradebug SETORAPID 15
Unix process pid: 14851, image: oracle@xifenfei (TNS V1-V3)

SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 4
Statement processed.

--执行session 1

session 1
SQL> oradebug EVENT 10046 trace name context off
Statement processed.

SQL> oradebug TRACEFILE_NAME
/u01/oracle/admin/xifenfei/udump/xff_ora_14851.trc

使用dbms_system.set_sql_trace_in_session

session 1
--查询出需要跟踪会话的sid，SERIAL#

session 2
SQL> exec dbms_system.set_sql_trace_in_session(12,130,true);

PL/SQL procedure successfully completed.

--session 1执行sql

session2关闭跟踪
SQL> exec dbms_system.set_sql_trace_in_session(12,130,false);

PL/SQL procedure successfully completed.

--通过session 1找出trace文件
发表在 Oracle | 评论关闭

ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified

发表于 2012 年 3 月 27 日 由 惜分飞

SCN说明
1、Oracle的SCN在每秒16384次commit的情况下可以维持534年，每秒16384次commit是Oracle早先认为的任何系统的极限commit强度；
2、Oracle里SCN的起点是1988年1月1日；
3、_minimum_giga_scn=n的含义是把SCN往前推进到nG，但请注意，只有在SCN小于nG的时候才会用到这个隐含参数，反之则Oracle会置这个隐含参数于不顾。

求模拟_minimum_giga_scn值
这里通过时间差，大概的模拟_minimum_giga_scn小于当前时间和1988年1月1日的scn最大值(300>290)

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')from dual;

TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS'
--------------------------------------
2012-03-18 04:27:50


SQL> select months_between(sysdate,to_date('19880101','yyyymmdd')) from dual;

MONTHS_BETWEEN(SYSDATE,TO_DATE('19880101','YYYYMMDD'))
------------------------------------------------------
                                             290.55443

SQL> select 16384*60*60*24*31*300/(1024*1024*1024) SCN from dual;

       SCN
----------
12260.7422

启动数据库测试

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

--------------------------------
*._minimum_giga_scn=12260
--------------------------------

SQL> startup pfile='/tmp/pfile'
ORACLE instance started.

Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified

分析ORA-01052

SQL> !oerr ora 1052
01052, 00000, "required destination LOG_ARCHIVE_DUPLEX_DEST is not specified"
// *Cause:  A valid destination for parameter LOG_ARCHIVE_DUPLEX_DEST was not
//          specified when parameter LOG_ARCHIVE_MIN_SUCCEED_DEST was set to
//          two.
// *Action: Either specify a value for parameter LOG_ARCHIVE_DUPLEX_DEST, or
//          reduce the value for parameter LOG_ARCHIVE_MIN_SUCCEED_DEST to one.

SQL> show parameter LOG_ARCHIVE_DUPLEX_DEST;

NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
log_archive_duplex_dest              string
SQL> show parameter LOG_ARCHIVE_MIN_SUCCEED_DEST

NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
log_archive_min_succeed_dest         integer    1

这里可以看出，不是以为我上面的两个参数设置错误导致ORA-01052，而是因为推进scn过大导致ORA-01052的错误

减小_minimum_giga_scn测试
这里选择_minimum_giga_scn小于当前时间和1988年1月1日的scn最大值(280<290) 

SQL>  select 16384*60*60*24*31*280/(1024*1024*1024) SCN from dual;

       SCN
———-
11443.3594

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

—————————–
*._minimum_giga_scn=11443
—————————–


SQL> startup pfile=’/tmp/pfile’
ORACLE instance started.

Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.

SQL> select to_char(dbms_flashback.get_system_change_number(),
2    ’9999999999999999′) from dual;

TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_
———————————-
   12286827692251

SQL> select dbms_flashback.get_system_change_number()/(1024*1024*1024) from dual;

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()/(1024*1024*1024)
———————————————————-
                                                     11443

通过试验可以发现，在我们的数据库遇到异常，需要恢复通过推进scn来恢复的时候，不是推进的越大越好；如果推进的太大可能导致ORA-01052错误，一般建议是比当前不一致的scn稍微大一点
参考:http://www.dbsnake.net/two-scn-internal-points.html

发表在 ORA-xxxxx | 标签为 | 一条评论

收集统计信息出现ORA-00600[ksxprqfre3]

发表于 2012 年 3 月 26 日 由 惜分飞

alert日志出现ORA-00600[ksxprqfre3]错误

Sun Mar 18 22:11:44 2012
Errors in file /oracle9/app/admin/offon/bdump/offon2_p001_8220790.trc:
ORA-00600: internal error code, arguments: [ksxprqfre3], [0x11033D5A8], [], [], [], [], [], []
Sun Mar 18 22:11:44 2012
Errors in file /oracle9/app/admin/offon/bdump/offon2_p001_8220790.trc:
ORA-00600: internal error code, arguments: [ksxprqfre3], [0x11033E178], [], [], [], [], [], []

通过这里可以发现，是由于并发进程导致异常

trace文件内容

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /oracle9/app/product/9.2.0
System name:    AIX
Node name:      zwq_offon2
Release:        3
Version:        5
Machine:        00CA56A44C00
Instance name: offon2
Redo thread mounted by this instance: 2
Oracle process number: 328
Unix process pid: 8220790, image: oracle@zwq_offon2 (P001)

*** 2012-03-18 22:11:43.994
*** SESSION ID:(381.10338) 2012-03-18 22:11:43.989
krqh = 11033d5a8
rqh trace:
  Slot: 6    Op: 114  ksxpirqh: irqh requested
  Slot: 7    Op: 130  ksxpirqh: rqh SUCC
  Slot: 8    Op: 123  ksxpwait: receive mv to doneq
  Slot: 9    Op: 121  ksxprcv: rqh found on doneq
  Slot: 10   Op: 102  ksxprqfre: freed to queue
  Slot: 11   Op: 101  ksxprqalo: allocated
  Slot: 12   Op: 105  ksxpdteq: enqueue to delta q
  Slot: 13   Op: 117  ksxpvsnd: send via ksxpisnd
  Slot: 14   Op: 106  ksxpisnd: send requested
  Slot: 0    Op: 107  ksxpisnd: send submit OK
  Slot: 1    Op: 153  pre send completion cbk
  Slot: 2    Op: 102  ksxprqfre: freed to queue
  Slot: 3    Op: 114  ksxpirqh: irqh requested
  Slot: 4    Op: 130  ksxpirqh: rqh SUCC
  Slot: 5    Op: 153  pre send completion cbk
Dump of memory from 0x000000011033D5A8 to 0x000000011033D660
11033D5A0                   00000001 1033D5A8          [.....3..]
11033D5B0 00000001 1033D5A8 00000001 1033D5B8  [.....3.......3..]
11033D5C0 00000001 1033D5B8 00000006 00000001  [.....3..........]
11033D5D0 00000000 00000000 00000000 00000000  [................]
11033D5E0 00000572 00000000 07000000 EE473B28  [...r.........G;(]
11033D5F0 00010000 00000000 00000001 1029CEA8  [.............)..]
11033D600 00000001 104382A0 07000000 F279B5A8  [.....C.......y..]
11033D610 00000000 00000572 00000000 00000000  [.......r........]
11033D620 00000000 00000000 00000000 13DCD225  [...............%]
11033D630 13DA7A25 00010001 26750000 00000000  [..z%....&u......]
11033D640 00000001 10339C70 6B996672 82997282  [.....3.pk.fr..r.]
11033D650 7B796665 69756A06 00000001 1033E228  [{yfeiuj......3.(]
*** 2012-03-18 22:11:43.996
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [ksxprqfre3], [0x11033D5A8], [], [], [], [], [], []
Current SQL statement for this session:
SELECT /*+ PIV_SSF */ SYS_OP_MSR(SYS_OP_COUNTCHG(SUBSTRB(ROWIDTOCHAR(A1.C0),1,15),1),COUNT(DISTINCT A1.C3),COUNT(*)) C0 FROM (SELECT /*+ INDEX_RRS(A2 "PK_TINEXWORK_DATA
") */ A2.ROWID C0,A2."WORKSHTSN" C1,A2."EXWORKSN" C2,SYS_OP_LBID(8574,'L',A2.ROWID) C3 FROM "OFFON"."TINEXWORK_DATA"  PX_GRANULE(0, BLOCK_RANGE, DYNAMIC) A2) A1
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
ksedmp+0148          bl       ksedst               102973B94 ?
ksfdmp+0018          bl       01FD34D8             
kgerinv+00e8         bl       _ptrgl               
kgeasnmierr+004c     bl       kgerinv              127C7F9D7226029B ?
                                                   9770A0AFAF4BDC0 ?
                                                   12E0BE826D694B2F ?
                                                   000000000 ? 000000010 ?
ksxprqfre+0054       bl       kgeasnmierr          110006728 ? 110338C88 ?
                                                   1030069D4 ? 100000001 ?
                                                   000000002 ? 11033D5A8 ?
                                                   080000000 ?
                                                   7F7F7F7F7F7F7F7F ?
ksxpwait+06bc        bl       ksxprqfre            7000000D6D793D8 ?
ksliwat+02e0         bl       ksxpwait             1014F4508 ? 1101FB1C8 ?
kslwaitns+001c       bl       ksliwat              FFFFFFFFFFFC4B0 ? 000000004 ?
                                                   FFFFFFFFFFFC590 ? 11030BC60 ?
                                                   FFFFFFFFFFFC530 ?
                                                   7000000DDF1FBE0 ? 110006868 ?
kskthbwt+01b4        bl       kslwaitns            000000002 ? 7000000EAEEB7A0 ?
                                                   000000000 ? 000000000 ?
                                                   000000868 ? 7000000DAFEFB30 ?
                                                   7000000F4979B10 ?
kslwait+005c         bl       kskthbwt             FFFFFFFFFFFC6E0 ?
                                                   2422424800000000 ?
                                                   1015EDD34 ? 110305194 ?
                                                   FFFF0000000003 ? 000000000 ?
                                                   000000001 ? FFFFFFFFFFFF920 ?
ksxprcv+0184         bl       kslwait              102970928 ? 000000006 ?
                                                   000000000 ? 000000000 ?
                                                   110305148 ? 110006728 ?
kxfpqidqr+0600       bl       ksxprcv              11029CEA8 ? 000000008 ?
                                                   FFFFFFFFFFFD020 ?
                                                   FFFFFFFFFFFCFC0 ?
                                                   162DFEF4180 ? 000000000 ?
                                                   000000000 ? 000000000 ?
kxfpqdqr+02c0        bl       kxfpqidqr            11029CF80 ? 11029CF20 ?
                                                   FFFFFFFFFFFD7C0 ?
                                                   7000000EAE2DA30 ?
kxfxgs+0050          bl       kxfpqdqr             101518AF0 ? 000002000 ?
                                                   FFFFFFFFFFFD9F0 ? 000000000 ?
kxfxmai+0100         bl       kxfxgs               102970928 ? 000000060 ?
                                                   7000000EEF059F0 ?
                                                   70000000006D670 ?
kxfprdp+05b0         bl       _ptrgl               
opirip+0390          bl       kxfprdp              
opidrv+0300          bl       opirip               000000018 ? 0101FB1C8 ?
                                                   000000000 ?
sou2o+0028           bl       opidrv               320C000000 ? 0A00E8B50 ?
                                                   000000000 ?
main+01a4            bl       01FD2EF0             
__start+0098         bl       main                 000000000 ? 000000000 ?
 
--------------------- Binary Stack Dump ---------------------

    SO: 7000000cf267198, type: 4, owner: 7000000cd2a53e0, flag: INIT/-/-/0x00
    (session) trans: 0, creator: 7000000cd2a53e0, flag: (48000041) USR/- -/-/-/-/-/-
              DID: 0002-0148-003F8809, short-term DID: 0000-0000-00000000
              txn branch: 0
              oct: 0, prv: 0, sql: 0, psql: 0, user: 25/MONITOR
    O/S info: user: maint1, term: , ospid: 8220790, machine: zwq_acc2
              program: oracle@zwq_offon2 (P001)
    application name: SQL*Plus, hash value=3669949024
    waiting for 'PX Deq: reap credit' blocking sess=0x0 seq=182 wait_time=0
                =0, =0, =0
    temporary object counter: 0

这里可以得出信息如下：
1)系统环境AIX 5.3 9.2.0.8.0 RAC
2)报错的语句是收集TINEXWORK_DATA表相关统计信息
3)是由于SQL*Plus收集统计信息使用并发导致该错误

查询MOS发现
Bug 5887047: ORA-00600 [KSXPRQFRE3] TRACE WHEN GATHERING OPTIMIZER STATISTICS描述相符：
1）数据库版本9.2.0.8
2）收集统计信息操作导致
3）STACK TRACE一致

处理建议
针对官方没有给出明确的解决方案，因为该问题出现概率比较低，如果经常出现需要考虑升级数据库版本

发表在 ORA-xxxxx | 标签为 | 一条评论