Category Archives: ORA-xxxxx

ORA-07445[dbgrmqmqpk_query_pick_key()+0f88]

Posted on by

alert发现如下错误ORA-07445[dbgrmqmqpk_query_pick_key()+0f88]

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0xB38F0000000049] 
[PC:0x100213C08, dbgrmqmqpk_query_pick_key()+0f88]
Errors in file /oracle/diag/rdbms/sgerp5/sgerp5/trace/sgerp5_m000_7602504.trc  (incident=579300):
ORA-07445: exception encountered: core dump [dbgrmqmqpk_query_pick_key()+0f88] [SIGSEGV] [ADDR:0xB38F0000000049] 
[PC:0x100213C08] [Address not mapped to object] []
Incident details in: /oracle/diag/rdbms/sgerp5/sgerp5/incident/incdir_579300/sgerp5_m000_7602504_i579300.trc

trace文件部分信息

Dump file /oracle/diag/rdbms/sgerp5/sgerp5/incident/incdir_579300/sgerp5_m000_7602504_i579300.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/11.1.0/db_1
System name:	AIX
Node name:	sgerp5
Release:	1
Version:	6
Machine:	00C8F0564C00
Instance name: sgerp5
Redo thread mounted by this instance: 1
Oracle process number: 138
Unix process pid: 7602504, image: oracle@sgerp5 (m000)
--确定是m000进程出现异常,而该进程是awr收集统计信息进程MMON的子进程

*** 2012-05-11 03:52:35.200
*** SESSION ID:(752.5029) 2012-05-11 03:52:35.200
*** CLIENT ID:() 2012-05-11 03:52:35.200
*** SERVICE NAME:(SYS$BACKGROUND) 2012-05-11 03:52:35.200
*** MODULE NAME:(MMON_SLAVE) 2012-05-11 03:52:35.200
*** ACTION NAME:(Auto-Purge Slave Action) 2012-05-11 03:52:35.200
 
Dump continued from file: /oracle/diag/rdbms/sgerp5/sgerp5/trace/sgerp5_m000_7602504.trc
ORA-07445: exception encountered: core dump [dbgrmqmqpk_query_pick_key()+0f88] [SIGSEGV] [ADDR:0xB38F0000000049] 
[PC:0x100213C08] [Address not mapped to object] []

----- Incident Context Dump -----
Address: 0x1104bdb68
Incident ID: 579300
Problem Key: ORA 7445 [dbgrmqmqpk_query_pick_key()+0f88]
Error: ORA-7445 [dbgrmqmqpk_query_pick_key()+0f88] [SIGSEGV] [ADDR:0xB38F0000000049] [PC:0x100213C08] 
[Address not mapped to object] [] [] []
[00]: dbgexExplicitEndInc [diag_dde]
[01]: dbgeEndDDEInvocationImpl [diag_dde]
[02]: dbgeEndDDEInvocation [diag_dde]
[03]: ssexhd []
[04]: 47dc []<-- Signaling
[05]: dbgrmqmfs_fetch_setup [ams_comp]
[06]: dbgrmqmf_fetch_real [ams_comp]
[07]: dbgrmqmf_fetch [ams_comp]
[08]: dbgrip_fetch_record [ami_comp]
[09]: dbgrip_relation_iterator [ami_comp]
[10]: dbgripricm_rltniter_wcbf_mt [ami_comp]
[11]: dbgripdrm_dmldrv_mt [ami_comp]
[12]: dbghmm_delete_info_records []
[13]: dbghmo_purge_hm_schema []
[14]: dbgrupipscb_hm_pgsvc_cbf [diag_adr]
[15]: dbgruppm_purge_main [diag_adr]
[16]: dbkrapg_auto_purge [rdbms_adr]
[17]: kewraps_auto_purge_slave []
[18]: kebm_slave_main []
[19]: ksvrdp [ksv_trace]
[20]: opirip []
[21]: opidrv []
[22]: sou2o []
[23]: opimai_real []
[24]: main []
[25]: __start []
MD [00]: 'SID'='752.5029' (0x3)
MD [01]: 'ProcId'='138.46' (0x3)
MD [02]: 'PQ'='(0, 1336679546)' (0x7)
MD [03]: 'Client ProcId'='oracle@sgerp5.7602504_1' (0x0)

MOS关于该问题记录
问题原因

This is due to Bug 9390347 fixed in 12.1 & 11.2.0.2, where a core dump can occur 
in module dbgrmqmqpk_query_pick_key() whilst purging HM contents from ADR.

解决方案

- Either install our 11.2.0.2 patchset
- Or download and apply Patch 9390347 if available for your version/platform.
- On Windows, you can also install Bundle Patch 11.1.0.7.31 or above.
There is no workaround to this error, however the error is not serious and does not cause any harm to your database.

ORA-00001 Unique Constraint SYS.I_JOB_JOB Violated

Posted on by

IMPDP导入数据发现ORA-00001 Unique Constraint SYS.I_JOB_JOB Violated错误

ORA-39083: Object type JOB failed to create with error:
ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
Failing sql is:
 BEGIN DBMS_JOB.ISUBMIT( JOB=> 63, NEXT_DATE=> TO_DATE('2012-04-27 00:00:00', 
'YYYY-MM-DD:HH24:MI:SS'), INTERVAL=> 'TRUNC(SYSDATE+1)', WHAT=> 'GBEAS1.UPDATE_EMP_INFO;', 
NO_PARSE=> TRUE); END;   
 
Job "GBEAS3"."SYS_IMPORT_FULL_01" completed with 8 error(s) at 16:05:58

错误原因(该job=63已经存在数据库中)

select job, what from   dba_jobs where job=63; 

JOB     WHAT 
-----   -------- 
63      proc_xifenfei

注意:如果该job正在运行,可能需要查询DBA_JOBS_RUNNING

解决办法

1.手工创建job,指定一个不存在的job 号
declare
  m_job number;

begin
  select max (job) + 1 
  into   m_job 
  from   dba_jobs; 

BEGIN DBMS_JOB.ISUBMIT( JOB=> m_job, NEXT_DATE=> TO_DATE('2012-04-27 00:00:00', 
'YYYY-MM-DD:HH24:MI:SS'), INTERVAL=> 'TRUNC(SYSDATE+1)', WHAT=> 'GBEAS1.UPDATE_EMP_INFO;', 
NO_PARSE=> TRUE); END; 
end;
/

2.删除原存在job
exec dbms_job.remove (63);

这样的情况，一般发生在expdp导出数据包含了job(如:全库导出,用户导出),然后导入到目标库,而该job号已经存在导致

OER 7451 in Load Indicator : Error Code = OSD-04500:指定了非法选项

Posted on by

alert 日志错误
OER 7451 in Load Indicator : Error Code = OSD-04500:指定了非法选项

Sun Apr 22 11:15:51 2012
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !
Sun Apr 22 11:16:01 2012
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !
Sun Apr 22 11:16:11 2012
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !

错误信息说明

07451, 00000, "slskstat: unable to obtain load information."
// *Cause:  kstat library returned an error. Possible OS failure
// *Action: Check result code in sercose[0] for more information.

数据库版本信息

SQL> select * from v$version;

BANNER
------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production  <<== 32位数据库
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

操作系统信息

C:\Users\XIFENFEI>systeminfo
主机名:           XIFENFEI-PC
OS 名称:          Microsoft Windows 7 旗舰版 
OS 版本:          6.1.7601 Service Pack 1 Build 7601
OS 制造商:        Microsoft Corporation
OS 配置:          独立工作站
OS 构件类型:      Multiprocessor Free
注册的所有人:     XIFENFEI
注册的组织:       Microsoft
产品 ID:          00426-068-8452196-86428
初始安装日期:     2012/2/28, 20:37:08
系统启动时间:     2012/4/22, 9:16:07
系统制造商:       Dell Inc.
系统型号:         Inspiron N4050
系统类型:         x64-based PC       <<==操心系统是win 7 64位
处理器:           安装了 1 个处理器。
                  [01]: Intel64 Family 6 Model 42 Stepping 7 GenuineIntel ~2300 Mhz
BIOS 版本:        Dell Inc. A06, 2011/11/14
Windows 目录:     C:\Windows
系统目录:         C:\Windows\system32
启动设备:         \Device\HarddiskVolume1
系统区域设置:     zh-cn;中文(中国)
输入法区域设置:   zh-cn;中文(中国)
时区:             (UTC+08:00)北京，重庆，香港特别行政区，乌鲁木齐
物理内存总量:     8,100 MB
可用的物理内存:   5,196 MB
虚拟内存: 最大值: 9,122 MB
虚拟内存: 可用:   5,315 MB
虚拟内存: 使用中: 3,807 MB
页面文件位置:     D:\pagefile.sys
域:               WORKGROUP
登录服务器:       \\XIFENFEI-PC

错误原因

Installed 32-bit Oracle database software on a 64-bit MS Windows OS which is not supported.

Note: For the Database software, you can ONLY install the x64 version on MS Windows (x64).
          You can NOT install the 32-bit version Database software on MS Windows (x64).

解决办法

Install 32-bit Oracle database software only on 32-bit MS Windows OS.

ORA-00600[KSSADP1]

Posted on by

检查数据库发现ORA-00600[KSSADP1]错误

Thu Apr 19 21:16:45 2012
Errors in file /oracle9/app/admin/crm/udump/crm1_ora_442896.trc:
ORA-00600: internal error code, arguments: [KSSADP1], [], [], [], [], [], [], []
Thu Apr 19 21:16:45 2012
Errors in file /oracle9/app/admin/crm/udump/crm1_ora_442896.trc:
ORA-00600: internal error code, arguments: [KSSADP1], [], [], [], [], [], [], []
Thu Apr 19 21:16:45 2012
Trace dumping is performing id=[cdmp_20120419211645]
Thu Apr 19 21:16:46 2012
Errors in file /oracle9/app/admin/crm/udump/crm1_ora_442896.trc:
ORA-00600: internal error code, arguments: [KSSADP1], [], [], [], [], [], [], []
Thu Apr 19 21:16:47 2012
Errors in file /oracle9/app/admin/crm/udump/crm1_ora_442896.trc:
ORA-00600: internal error code, arguments: [KSSADP1], [], [], [], [], [], [], []

分析crm1_ora_442896.trc信息

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_crm1
Release:        3
Version:        5
Machine:        00C420B44C00
Instance name: crm1
Redo thread mounted by this instance: 1
Oracle process number: 2354
Unix process pid: 442896, image: oracle@zwq_crm1 (TNS V1-V3)

*** SESSION ID:(927.39278) 2012-04-19 21:16:45.317
*** 2012-04-19 21:16:45.317
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [KSSADP1], [], [], [], [], [], [], []
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
ksedmp+0148          bl       ksedst               1029746FC ?
ksfdmp+0018          bl       01FD4014             
kgerinv+00e8         bl       _ptrgl               
kgesinv+0020         bl       kgerinv              9001000A02B56F8 ?
                                                   9001000A02B9450 ?
                                                   FFFFFFFFFFF8430 ? 000000458 ?
                                                   900000000CBAFA4 ?
ksesin+005c          bl       kgesinv              FFFFFFFFFFF88E0 ? 1101FAF78 ?
                                                   900000000C0ECC0 ? 000010000 ?
                                                   000000002 ?
kssadpm_stage+00c4   bl       ksesin               102973C84 ? 000000000 ?
                                                   00000001E ? 000000000 ?
                                                   000000069 ? 00000000C ?
                                                   000000000 ? 000000000 ?
ksqgel+0138          bl       kssadpm_stage        000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
kcftis+003c          bl       ksqgel               000000000 ? 4029C61E0 ?
                                                   000000002 ? 0FFFFC16C ?
                                                   102A7977C ? 000000000 ?
                                                   000000003 ? 002A36408 ?
kcfhis+001c          bl       kcftis               
krbbcc+0238          bl       kcfhis               11043B590 ?
krbpgc+001c          bl       krbbcc               
ksmupg+0074          bl       _ptrgl               
ksuded+00b8          bl       ksmupg               102924988 ? 000000020 ?
ksupucg+10ec         bl       ksuded               700000C376F5740 ? 000000000 ?
                                                   000000000 ?
opiodr+0474          bl       ksupucg              100000001 ?
ttcpip+0cc4          bl       _ptrgl               
opitsk+0d60          bl       ttcpip               11000CF90 ?
                                                   442442216B736800 ?
                                                   FFFFFFFFFFFBF00 ? 1102E04BC ?
                                                   1102D7D20 ? 0000006A0 ?
                                                   1102D83C0 ? 0000006A0 ?
opiino+0758          bl       opitsk               000000000 ? 000000000 ?
opiodr+08cc          bl       _ptrgl               
opidrv+032c          bl       opiodr               3C00000018 ? 4101FAF78 ?
                                                   FFFFFFFFFFFF840 ? 0A000F350 ?
sou2o+0028           bl       opidrv               3C0C000000 ? 4A00E8B50 ?
                                                   FFFFFFFFFFFF840 ?
main+0138            bl       01FD3A28             
__start+0098         bl       main                 000000000 ? 000000000 ?
 
--------------------- Binary Stack Dump ---------------------

Cursor Dump:
----------------------------------------
Cursor 1 (110360418): CURROW  curiob: 110369b78
 curflg: 46 curpar: 0 curusr: 0 curses 700000c376f5740
 cursor name: select nvl(max(cpmid),0) from x$kcccp                                        where cpsta = 2
 child pin: 0, child lock: 700000d9b9c5bb8, parent lock: 700000d088e0fa0
 xscflg: 1100024, parent handle: 70000031d588d88, xscfl2: 4040401
 bhp size: 160/600
----------------------------------------
Cursor 2 (110360468): CURBOUND  curiob: 1103656f0
 curflg: c7 curpar: 0 curusr: 0 curses 700000c376f5740
 cursor name: SELECT SUBSTR(VERSION,1,INSTR(VERSION,'.') - 1 )   FROM V$INSTANCE
 child pin: 0, child lock: 700000d21e60930, parent lock: 700000327837ce0
 xscflg: 141024, parent handle: 700000304e2f020, xscfl2: 4000401
 bhp size: 160/600
----------------------------------------
Cursor 3 (1103604b8): CURBOUND  curiob: 1103b6aa8
 curflg: c7 curpar: 0 curusr: 0 curses 700000c376f5740
 cursor name: SELECT SUBSTR(VERSION,1 + INSTR(VERSION,'.',1,1) ,INSTR(VERSION,'.',1,2) - 
 INSTR(VERSION,'.',1,1)  - 1 )   FROM V$INSTANCE
 child pin: 0, child lock: 700000d5e382ee8, parent lock: 700000c93581d40
 xscflg: 141024, parent handle: 700000d73daa1c0, xscfl2: 4000401
 bhp size: 160/600
----------------------------------------
Cursor 4 (110360508): CURBOUND  curiob: 1103b66b8
 curflg: c7 curpar: 0 curusr: 0 curses 700000c376f5740
 cursor name: SELECT SUBSTR(VERSION,1 + INSTR(VERSION,'.',1,2) ,INSTR(VERSION,'.',1,3) - 
 INSTR(VERSION,'.',1,2)  - 1 )   FROM V$INSTANCE
 child pin: 0, child lock: 700000d16de7978, parent lock: 700000c44059d30
 xscflg: 141024, parent handle: 700000259c4a700, xscfl2: 4000401
 bhp size: 160/600
----------------------------------------
Cursor 5 (110360558): CURBOUND  curiob: 1103b3868
 curflg: 46 curpar: 0 curusr: 0 curses 700000c376f5740
 cursor name: SELECT SYSDATE   FROM SYS.DUAL
 child pin: 0, child lock: 700000d589cea48, parent lock: 70000026b311fb0
 xscflg: 100024, parent handle: 700000d2eaee328, xscfl2: 4600409
 bhp size: 280/632
----------------------------------------
Cursor 6 (1103605a8): CURBOUND  curiob: 1103b3408
 curflg: 46 curpar: 0 curusr: 0 curses 700000c376f5740
 cursor name: SELECT TO_CHAR(SYSDATE,'YYYY','NLS_CALENDAR=Gregorian'),TO_CHAR(SYSDATE,'MM','NLS_CALENDAR=Gregorian'),
TO_CHAR(SYSDATE,'DD','NLS_CALENDAR=Gregorian') FROM X$DUAL
 child pin: 0, child lock: 70000033f1753c8, parent lock: 700000db8c6dd18
 xscflg: 100024, parent handle: 700000cbc6ad8b0, xscfl2: 4600409
 bhp size: 160/600
End of cursor dump
ksedmp: no current context area
----- Dump of the Fixed PGA -----

找到相关文档Note:262996.1,经过分析,产生错误的原因是由在本版本的数据库中SGA管理中存在的漏洞造成,但此错误没有对数据库的数据造成损坏及性能影响.

处理建议
1．当前版本ORACLE已经不再提供补丁支持,建议升级到高版本解决(有sr中介绍10.2中解决);
2．由于此报错并没有对数据库的数据及性能造成损坏及影响,可以忽此错误。

ORA-01075: you are currently logged on

Posted on by

rm删除文件后alert中出现错误

Mon Apr 16 21:36:59 2012
Errors in file /home/oracle/oracle/admin/XGS/bdump/xgs_j000_1349.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/home/oracle/oracle/oradata/XGS/sysaux01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/home/oracle/oracle/oradata/XGS/sysaux01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/home/oracle/oracle/oradata/XGS/undotbs02.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

数据库进程还在运行

oracle     779     1  0 21:21 ?        00:00:01 ora_pmon_XGS
oracle     781     1  0 21:21 ?        00:00:10 ora_psp0_XGS
oracle     783     1  0 21:21 ?        00:00:00 ora_mman_XGS
oracle     785     1  0 21:21 ?        00:00:00 ora_dbw0_XGS
oracle     787     1  0 21:21 ?        00:00:00 ora_lgwr_XGS
oracle     789     1  0 21:21 ?        00:00:00 ora_ckpt_XGS
oracle     791     1  0 21:21 ?        00:00:00 ora_smon_XGS
oracle     793     1  0 21:21 ?        00:00:00 ora_reco_XGS
oracle     795     1  0 21:21 ?        00:00:00 ora_cjq0_XGS
oracle     797     1  0 21:21 ?        00:00:01 ora_mmon_XGS
oracle     799     1  0 21:21 ?        00:00:00 ora_mmnl_XGS
oracle     801     1  0 21:21 ?        00:00:00 ora_d000_XGS
oracle     803     1  0 21:21 ?        00:00:00 ora_s000_XGS

尝试登陆数据库

[oracle@dbtest ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 16 21:40:06 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-01075: you are currently logged on


Enter user-name: sys
Enter password: 
ERROR:
ORA-00604: error occurred at recursive SQL level 2
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/home/oracle/oracle/oradata/XGS/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/home/oracle/oracle/oradata/XGS/undotbs02.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

问题原因

Internal triggers are trying to fire but one or more datafiles for the SYSAUX tablespace is offline, 
this is preventing the database from allowing new connections.

NOTE: At this point, you cannot connect to verify the status in V$DATAFILE, 
but you may find an indication of the offline datafile(s) in the alert.log file.

For example:
In one case, a media problem occurred which made disks unavailable.
This caused several files to be taken offline automatically including a SYSAUX datafile.

解决方法
kill进程,重启数据库到mount状态,然后根据特定情况恢复数据库或者online相关文件