分类目录归档：ORA-xxxxx

delete expired backup报ORA-19606错误处理

发表于 2012 年 12 月 5 日 由 惜分飞

数据库版本(exadata x2 1/4)

SQL> select *from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

使用delete expired backup报ORA-19606错

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of delete command on ORA_DISK_4 channel at 12/05/2012 13:35:07
ORA-19606: Cannot copy or restore to snapshot control file

报错原因

The snapshot controlfile is cataloged as a controlfile coy and is now obsolete. 
RMAN cannot delete this file as it is used by rman.

处理方法[ID 1215493.1]

Since RMAN will continue to use that file as it's snapshot copy, 
you must change the location/name that RMAN is using before it will allow you to delete the file. 

1. Set new name (or location) for RMAN to use for snapshot controlfile:

CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCF_temp.ORA';

2. Remove the snapshot controlfile from the RMAN information as a controlfile copy. 

delete 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFAXMPRD.ORA' on operating system. 

3. Crosscheck and delete the file from RMAN:

crosscheck controlfilecopy 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFAXMPRD.ORA';
delete expired controlfilecopy 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFAXMPRD.ORA';

4. Set the snapshot controlfile name (or location) to original:

CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFAXMPRD.ORA';
5. Or to set the snapshot controlfile name back to default value:
CONFIGURE SNAPSHOT CONTROLFILE NAME clear;

处理方法就是把SNAPSHOT CONTROLFILE的配置路径改变,然后删除控制文件快照

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

ORA-00600[kcfrbd_3]故障解决

发表于 2012 年 12 月 5 日 由 惜分飞

朋友一数据库因为断电,被重建控制文件等操作折腾的最后出现在启动的时候出现ORA-00600[kcfrbd_3]

Wed Dec 05 10:26:34 2012
Thread 1 advanced to log sequence 11
Thread 1 opened at log sequence 11
  Current log# 1 seq# 11 mem# 0: E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
Successful open of redo thread 1
Wed Dec 05 10:26:34 2012
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Dec 05 10:26:34 2012
SMON: enabling cache recovery
Wed Dec 05 10:26:35 2012
Successfully onlined Undo Tablespace 1.
Dictionary check beginning
Dictionary check complete
Wed Dec 05 10:26:35 2012
SMON: enabling tx recovery
Wed Dec 05 10:26:35 2012
Database Characterset is ZHS16GBK
Wed Dec 05 10:26:35 2012
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_548.trc:
ORA-00600: internal error code, arguments: [kcfrbd_3], [2], [2279045], [1], [2277120], [2277120], [], []

replication_dependency_tracking turned off (no async multimaster replication found)
Wed Dec 05 10:26:36 2012
Fatal internal error happened while SMON was doing active transaction recovery.
Wed Dec 05 10:26:36 2012
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_548.trc:
ORA-00600: internal error code, arguments: [kcfrbd_3], [2], [2279045], [1], [2277120], [2277120], [], []

SMON: terminating instance due to error 474

这个错误很明显:数据库已经open成功了，但是因为有事务不能正常被回滚，然后数据库的smon进程异常，从而使得数据库不能正常启动，解决该问题的方法也是很简单，就是常规的undo处理思路(使用人工undo管理,event屏蔽事务,隐含参数屏蔽回滚段)，然后重建undo表空间，这个时候可以结合txchecker来检测是否有异常事务:如果有重要基表对象异常,需要重建库;如果是个别其他对象异常,可以通过重建该对象解决

发表在 ORA-xxxxx | 标签为 , , | 评论关闭

ORA-00600[qmxtriCheckAndRewriteQb0]

发表于 2012 年 11 月 13 日 由 惜分飞

数据库报ORA-00600[qmxtriCheckAndRewriteQb0]

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /u01/oracle/product/10.2.0
System name:	AIX
Node name:	abc
Release:	3
Version:	5
Machine:	00C58A644C00
Instance name: XFF2
Redo thread mounted by this instance: 2
Oracle process number: 434
Unix process pid: 492340, image: oracle@abc

*** ACTION NAME:() 2012-11-12 08:46:47.132
*** MODULE NAME:() 2012-11-12 08:46:47.132
*** SERVICE NAME:(ORCL) 2012-11-12 08:46:47.132
*** CLIENT ID:() 2012-11-12 08:46:47.132
*** SESSION ID:(870.58602) 2012-11-12 08:46:47.132
*** 2012-11-12 08:46:47.132
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [qmxtriCheckAndRewriteQb0], [], [], [], [], [], [], []
Current SQL statement for this session:
SELECT EXTRACTVALUE(配置,'//SYSTEM[@XTH="'||:B1 ||'"]/FILE') , 
WHERE EXTRACTVALUE(配置,'//SYSTEM[@XTH="'||:B1 ||'"]/BM')=:B2  AND ROWNUM<2
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
70000021d535f70        25  procedure ZLTOOLS.ZL_MBRUNLOG_INSERT
7000002b6819368         1  anonymous block
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
ksedst+001c          bl       ksedst1              000000000 ? 000000000 ?
ksedmp+0290          bl       ksedst               104A2C690 ?
ksfdmp+0018          bl       03F26C3C             
kgerinv+00dc         bl       _ptrgl               
kgeasnmierr+004c     bl       kgerinv              7000002F735A838 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   0FFFFBFFF ?
IPRA.$qmxtriCheckAn  bl       03F25970             
dRewriteQb_rec+0194                                
IPRA.$qmxtriCheckAn  bl       IPRA.$qmxtriCheckAn  1000881EC ? 000000000 ?
dRewriteQb_rec+006c           dRewriteQb_rec       000000000 ?
IPRA.$qmxtriCheckAn  bl       IPRA.$qmxtriCheckAn  FFFFFFFFFFF07E0 ? 000000033 ?
dRewriteQb_rec+006c           dRewriteQb_rec       1056037F8 ?
qmxtriCheckAndRewri  bcl      dmqlKMlod+00c0       000000000 ? 110421CB0 ?
teQb+0094                                          FFFFFFFFFFE87C0 ?
qmxtrxq+0210         bl       03F252EC             
qmxtrxop+00a4        bl       qmxtrxq              FFFFFFFFFFF25B8 ?
                                                   700000282F66DD0 ? 110195E98 ?
koksspend+02b0       bl       qmxtrxop             100346AB4 ?
kkmdrvend+01a8       bl       koksspend            000000001 ? 104B3A8A8 ?
                                                   000000000 ?
kkmdrv+004c          bl       kkmdrvend            FFFFFFFFFFE8BE0 ?
                                                   883843401048F2F8 ?
opiSem+13c0          bl       kkmdrv               000000000 ? 000000000 ?
                                                   000000000 ? 11022AC50 ?
opiDeferredSem+0234  bl       opiSem               FFFFFFFFFFE9CE0 ?
                                                   7000001E327CCE0 ? 000000111 ?
                                                   100000001 ?
opitca+01e8          bl       opiDeferredSem       
kksFullTypeCheck+00  bl       03F25230             
1c                                                 
rpiswu2+034c         bl       _ptrgl               
kksSetBindType+0d28  bl       rpiswu2              70000030850C178 ?
                                                   3300000033 ?
                                                   FFFFFFFFFFF0570 ?
                                                   FFFFFFFFFFF0578 ?
                                                   7000002F6F0C700 ?
                                                   33104027D8 ?
                                                   FFFFFFFFFFF1F48 ? 000000000 ?
kksfbc+1054          bl       kksSetBindType       70000030F58F400 ? 1107CB418 ?
                                                   70000001003B800 ?
                                                   10200003000 ? 110000FF8 ?
                                                   7000000100ECAB8 ?
                                                   FFFFFFFFFFF1480 ?
                                                   481A408400003000 ?
opiexe+098c          bl       01F960BC             
opipls+185c          bl       opiexe               FFFFFFFFFFF3900 ?
                                                   FFFFFFFFFFF39E8 ?
                                                   FFFFFFFFFFF38A0 ?
opiodr+0ae0          bl       _ptrgl               
rpidrus+01bc         bl       opiodr               66FFFF54B0 ? 608736A20 ?
                                                   FFFFFFFFFFF67C0 ?
                                                   1510195E98 ?
skgmstack+00c8       bl       _ptrgl               
rpidru+0088          bl       skgmstack            102320840 ? 000000000 ?
                                                   000000002 ? 000000000 ?
                                                   FFFFFFFFFFF5F88 ?
rpiswu2+034c         bl       _ptrgl               
rpidrv+095c          bl       rpiswu2              70000030850C178 ? 110469C28 ?
                                                   11044AA58 ? 000000000 ?
                                                   FFFFFFFFFFF5D60 ?
                                                   3300000000 ? 000000000 ?
                                                   000000000 ?
psddr0+02bc          bl       03F266D4             
psdnal+01d0          bl       psddr0               1500000000 ? 6600000000 ?
                                                   FFFFFFFFFFF67C0 ?
                                                   30100BACC8 ?
pevm_EXECC+01f8      bl       _ptrgl               
pfrinstr_EXECC+0070  bl       pevm_EXECC           10147B2A4 ? 000000000 ?
                                                   700000262828B72 ?
pfrrun_no_tool+005c  bl       _ptrgl               
pfrrun+1014          bl       pfrrun_no_tool       FFFFFFFFFFF6B20 ?
                                                   7000002B6819368 ? 3100ECBB0 ?
plsql_run+06b4       bl       pfrrun               1107D84A8 ?
peicnt+0224          bl       plsql_run            1107D84A8 ? 10001102676F8 ?
                                                   000000000 ?
kkxexe+0250          bl       peicnt               FFFFFFFFFFF7E38 ? 1107D84A8 ?
opiexe+2ef8          bl       kkxexe               11047E1C8 ?
kpoal8+0edc          bl       opiexe               FFFFFFFFFFFB454 ?
                                                   FFFFFFFFFFFB1A8 ?
                                                   FFFFFFFFFFF9628 ?
opiodr+0ae0          bl       _ptrgl               
ttcpip+1020          bl       _ptrgl               
opitsk+1124          bl       01F96AC8             
opiino+0990          bl       opitsk               0FFFFD490 ? 000000000 ?
opiodr+0ae0          bl       _ptrgl               
opidrv+0484          bl       01F95914             
sou2o+0090           bl       opidrv               3C02D99B7C ? 4A076D928 ?
                                                   FFFFFFFFFFFF390 ?
opimai_real+01bc     bl       01F93294             
main+0098            bl       opimai_real          000000000 ? 000000000 ?
__start+0098         bl       main                 000000000 ? 000000000 ?
 
--------------------- Binary Stack Dump ---------------------

通过这个trace的部分信息可以得到:
1.操作系统版本AIX x64(5.3)
2.数据库版本10.2.0.4
3.sql语句调用EXTRACTVALUE函数
4.Call Stack Trace信息

查询MOS[ID 467350.1]发现匹配信息

Cause
Bug 6030982 ORA-600 [QMXTRICHECKANDREWRITEQB0] WITH QUERY USING EXTRACTVALUE FUNCTION

Solution
This bug is going to be fixed in furture 10.2.0.5.0 and 11g
At the mean time , user can workaround by 

set
event = "19027 trace name context forever, level 1"
within init.ora or spfile file then bounce database.

or

SQL> alter session set events ='19027 trace name context forever, level 1';
SQL> Alter system flush shared_pool;
-- Execute affected query

通过mos可以确定:
1.是因为数据库执行EXTRACTVALUE函数遇到该bug
2.在11g和10.2.0.5中修复该bug
3.可以通过设置event = “19027 trace name context forever, level 1″来临时解决该问题

个人处理建议
1.如果数据库方便升级,那建议升级处理
2.如果数据库不便立马升级,建议在业务低估时设置session event 19027,然后 flush shared_pool,执行报错sql,如果问题解决,在合适时间设置system event来临时屏蔽该问题.

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