标签云
asm恢复 bbed bootstrap$ dul kcbzib_kcrsds_1 kccpb_sanity_check_2 kcratr_nab_less_than_odr MySQL恢复 obet ORA-00312 ORA-00704 ORA-00742 ORA-01110 ORA-01200 ORA-01555 ORA-01578 ORA-01595 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-600 kcratr_nab_less_than_odr ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle碎片 OSD-04016 YOUR FILES ARE ENCRYPTED 比特币加密文章分类
- MongoDB (1)
- MongoDB恢复 (1)
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (112)
- 数据库 (1,885)
- DB2 (22)
- MySQL (82)
- Oracle (1,710)
- Data Guard (53)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (168)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (8)
- Oracle ASM (73)
- Oracle Bug (8)
- Oracle RAC (56)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (29)
- Oracle备份恢复 (663)
- Oracle安装升级 (108)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (91)
- PostgreSQL (37)
- pdu工具 (7)
- PostgreSQL恢复 (13)
- SQL Server (34)
- SQL Server恢复 (14)
- TimesTen (7)
- 达梦数据库 (5)
- 达梦恢复 (3)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (51)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (34)
- MongoDB (1)
-
最近发表
- 恢复案例分享:win丢失数据文件恢复
- 记录19c ASM磁盘掉线触发ORA-59048故障处理
- aix平台Oracle数据库由于断电无法启动故障处理
- 断电引起ORA-600 3005故障处理
- 记录一次云存储资源到期之后引起的数据库恢复
- Firebird 损坏数据库数据抢救工具—firebirdext
- Oracle 19c 202610补丁(RUs+OJVM)-19.33
- 由于con$.con#值达到极限,触发数据库增加约束报ORA-600 12807错误
- MongoDB 离线提取工具—mongoext
- .bres勒索病毒加密数据库dmp文件可恢复
- ORA-600 krsm_mrp_rebuild_lf_list.lno_mismatch
- 记录一次0丢失的ORA-00354: 损坏重做日志块标头故障恢复
- 不当数据库恢复操作导致一个月数据丢失
- obet快速修复oracle 位图损坏块
- 不太常见的10.2.0.1的oracle redo损坏恢复
- obet forcecopy功能抢救硬件故障中的数据文件
- kcratr_nab_less_than_odr和system坏块故障处理
- 通过obet 恢复system坏块,打开数据库
- obet dbv功能完整说明
- 分享一例运行在aix上的sap系统数据库恢复过程
分类目录归档:ORA-xxxxx
Query Duration=0与ORA-01555
1.ALERT日志错误
奇怪之处:Query Duration=0 sec,竟然出现了ORA-01555
Tue Feb 7 02:41:34 2012 ORA-01555 caused by SQL statement below (Query Duration=0 sec, SCN: 0x0b2e.efcd78a9): Tue Feb 7 02:41:34 2012 SELECT "ID_NO","CUST_ID" FROM "DBACCADM"."DCUSTMSG" "C" WHERE "ID_NO"=:1
2.ORA-01555解释
超过了undo_retention时间,undo被覆盖导致ORA-01555
[zwq_acc1:/home/oraeye/check]oerr ora 1555 01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small" // *Cause: rollback records needed by a reader for consistent read are // overwritten by other writers // *Action: If in Automatic Undo Management mode, increase undo_retention // setting. Otherwise, use larger rollback segments
3.数据库版本
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
4.undo基本信息
从这里可以发现,两个节点的undo表空间还有很多剩余,缺发生了undo被覆盖从而出现了ORA-01555
SQL> col name for a20
SQL> col value for a15
SQL> SELECT INST_ID, NAME, VALUE
2 FROM GV$PARAMETER
3 WHERE UPPER (Name) LIKE '%UNDO%';
INST_ID NAME VALUE
---------- -------------------- ---------------
1 undo_management AUTO
1 undo_tablespace UNDOTBS1
1 undo_suppress_errors FALSE
1 undo_retention 1800
2 undo_management AUTO
2 undo_tablespace UNDOTBS2
2 undo_suppress_errors FALSE
2 undo_retention 1800
8 rows selected.
TABLESPACE_NAME CURRENT_TOTAL(MB) USED(MB) FREE(MB) FREE% AUT MAX_TOTAL(MB)
------------------------------ ----------------- ---------- ---------- ---------- --- -------------
UNDOTBS1 40950 1587.94 39362.0625 96.12 NO 40950
UNDOTBS2 57330 1926.31 55403.6875 96.64 NO 57330
SQL> SELECT DISTINCT STATUS ,
2 COUNT(*) "EXTENT_NUM",
3 SUM(BYTES) / 1024 / 1024 / 1024 "UNDO(G)"
4 FROM DBA_UNDO_EXTENTS
5 GROUP BY STATUS;
STATUS EXTENT_NUM UNDO(G)
--------- ---------- ----------
ACTIVE 208 .273658752
EXPIRED 7651 2.42865753
UNEXPIRED 941 .752548218
查询MOS[ID 761128.1],发现可能是Oracle bug导致(BUG:6799685 – ORA-1555 ERROR WITH QUERY DURATION=0 AND UNDO_RETENTION=1800和BUG:5475085 – V$UNDOSTAT.EXPBLKREUCNT IS NEVER INCREMENTED)
5.解决方法
Increase the size of the UNDO tablespace and increase the UNDO_RETENTION parameter value to try to prevent required undo expiring too quickly.
基于本库,因为undo空间还有很大剩余,直接设置UNDO_RETENTION=3600即可(可以从一定程度上缓解整个问题,但是要从根本上解决整个问题,需要升级到10.2.0.4及其以上版本)
ORA-00600[4454]
数据库版本信息
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.
早上检查某运营商的结算数据库时发现
Mon Feb 6 16:03:23 2012 Errors in file /oracle9/app/admin/settl/udump/settl1_ora_1355948.trc: ORA-00600: internal error code, arguments: [4454], [], [], [], [], [], [], [] Mon Feb 6 16:03:23 2012 Errors in file /oracle9/app/admin/settl/udump/settl1_ora_1355948.trc: ORA-00600: internal error code, arguments: [4454], [], [], [], [], [], [], []
trace文件信息
*** SESSION ID:(100.35758) 2012-02-06 16:03:23.223
*** 2012-02-06 16:03:23.223
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [4454], [], [], [], [], [], [], []
Current SQL statement for this session:
select trim(operator_id) into :b1 from b_sys_proc where program_name=:b2
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp+0148 bl ksedst 1029746CC ?
ksfdmp+0018 bl 01FD4010
kgeriv+0118 bl _ptrgl
kgeasi+00cc bl kgeriv 1101FAF48 ? 110248038 ?
00000000C ? 110235978 ?
000000040 ?
ktcsptg+00c4 bl kgeasi 1100062D8 ? 110389E88 ?
116600001166 ? 200000002 ?
000000000 ? 70000010738F0E8 ?
0FFFFFFFF ? 0FFFFFFFF ?
opiexe+0524 bl ktcsptg 000000000 ? 000000000 ?
opiefn0+01c0 bl opiexe 49FFFFA640 ? 4900000001 ?
FFFFFFFFFFFA5C8 ?
opiefn+0100 bl opiefn0 01000CF60 ? 1029C61B0 ?
000000002 ? FFFFFFFFFFFC1BC ?
000000001 ? 00000000C ?
00000000B ? 110061F50 ?
opiodr+08cc bl _ptrgl
ttcpip+0cc4 bl _ptrgl
opitsk+0d60 bl ttcpip 11000CF60 ? 000000000 ?
000000000 ? 000000000 ?
000000000 ? 000000000 ?
000000000 ? 000000000 ?
opiino+0758 bl opitsk 000000000 ? 000000000 ?
opiodr+08cc bl _ptrgl
opidrv+032c bl opiodr 3C00000018 ? 4101FAF48 ?
FFFFFFFFFFFF790 ? 0A000EEA8 ?
sou2o+0028 bl opidrv 3C0C000000 ? 4A0147B50 ?
FFFFFFFFFFFF790 ?
main+0138 bl 01FD3A28
__start+0098 bl main 000000000 ? 000000000 ?
………………
SO: 70000010738f0e8, type: 4, owner: 7000001043acd90, flag: INIT/-/-/0x00
(session) trans: 70000010dde1dd8, creator: 7000001043acd90, flag: (100041) USR/- BSY/-/-/-/-/-
DID: 0001-0064-7625733A, short-term DID: 0000-0000-00000000
txn branch: 7000001147dbed8
oct: 0, prv: 0, sql: 700000117b7f778, psql: 0, user: 24/SETTLE
O/S info: user: settle, term: , ospid: 1400836, machine: zwq_jies2
program: /settlement/pkg01/bin/long/long_app@zwq_jies2 (T
application name: /settlement/pkg01/bin/long/long_app@zwq_jies2 (T, hash value=0
last wait for 'SQL*Net message from client' blocking sess=0x0 seq=483 wait_time=240
driver id=54435000, #bytes=1, =0
查询MOS,发现是Bug# 1402161(虽然在mos中声明该bug是在9.2.0.1中已经被修复,但是这里的trace文件中的Call Stack Trace和mos中记录一致,而且网络上也存在很多9.2.0.8中关于该bug的情况),trace文件最后一段可以看出是在节点2上(zwq_jies2)运行/settlement/pkg01/bin/long/long_app程序导致出现该错误
产生该错误原因:
This exception is signalled because the savepoint number is not what was expected. The current transaction savepoint is less than the beginning savepoint of the transaction.
解决方法:
把/settlement/pkg01/bin/long/long_app中的需要运行的程序分割成几个小程序运行。
Break the job into smaller chunks and reconnect for each part of the job to reset the savepoint number.
ORA-00600[ktspNextL1:4]
在检查某运营商的客服数据库时发现如下错误
Tue Jan 31 22:00:50 2012 Errors in file /oracle10/admin/ahunicom/bdump/ahunicom1_j005_24445074.trc: ORA-00600: internal error code, arguments: [ktspNextL1:4], [], [], [], [], [], [], [] Tue Jan 31 22:01:18 2012 Trace dumping is performing id=[cdmp_20120131220118] Tue Jan 31 22:01:18 2012 Errors in file /oracle10/admin/ahunicom/bdump/ahunicom1_j005_24445074.trc: ORA-00600: internal error code, arguments: [ORA-00600: internal error code, arguments: [ktspNextL1:4], [], [], [], [], [], [], [] ORA-06512: at "SYS.PRVT_ADVISOR", line 1624 ORA-06512: at "SYS.DBMS_ADVISOR", line 186 ORA-06512: at "SYS.DBMS_SPACE", line 1500 ORA-06512: at "SYS.DBMS_SPACE", line 1566 ], [], [], [], [], [], [], []
查看trace文件
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 = /oracle10/app/product/db/10.2.0
System name: AIX
Node name: zwq_kfdb1
Release: 1
Version: 6
Machine: 00C5C4764C00
Instance name: ahunicom1
Redo thread mounted by this instance: 1
Oracle process number: 192
Unix process pid: 24445074, image: oracle@zwq_kfdb1 (J005)
*** ACTION NAME:(AUTO_SPACE_ADVISOR_JOB) 2012-01-31 22:00:50.874
*** MODULE NAME:(DBMS_SCHEDULER) 2012-01-31 22:00:50.874
*** SERVICE NAME:(SYS$USERS) 2012-01-31 22:00:50.874
*** SESSION ID:(454.44574) 2012-01-31 22:00:50.874
*** 2012-01-31 22:00:50.873
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [ktspNextL1:4], [], [], [], [], [], [], []
Current SQL statement for this session:
insert into wri$_adv_objspace_trend_data select timepoint, space_usage, space_alloc, quality from table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL
, NULL, NULL, 'FALSE', :5, 'FALSE'))
----- PL/SQL Call Stack -----
object line object
handle number name
70000010d9a65e0 1834 package body SYS.DBMS_SPACE
70000010d9a65e0 3922 package body SYS.DBMS_SPACE
70000010d9a65e0 4233 package body SYS.DBMS_SPACE
70000011a7d9c88 1 anonymous block
700000180678048 344 SYS.WRI$_ADV_OBJSPACE_TREND_T
700000180678048 1485 SYS.WRI$_ADV_OBJSPACE_TREND_T
7000001334867d0 1535 package body SYS.PRVT_ADVISOR
7000001334867d0 1618 package body SYS.PRVT_ADVISOR
70000011f9f64a8 186 package body SYS.DBMS_ADVISOR
70000010d9a65e0 1500 package body SYS.DBMS_SPACE
70000010d9a65e0 1566 package body SYS.DBMS_SPACE
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst+001c bl ksedst1 70000017E9975D0 ? 100000001 ?
ksedmp+0290 bl ksedst 104A2CDB0 ?
ksfdmp+0018 bl 03F2735C
kgerinv+00dc bl _ptrgl
kgeasnmierr+004c bl kgerinv 000000000 ? 000000001 ?
000000005 ? 7000000E19760FC ?
7000000E1976014 ?
ktspGetNextL1ForSca bl 01F94828
n+0104
ktspScanInit+026c bl ktspGetNextL1ForSca A0A0E1B89 ? 10564BCF4 ?
n 80003000804DC ?
ktspGenExtentMap1+0 bl ktspScanInit 000000000 ?
0e8
kteinmap1+00bc bl ktspGenExtentMap1 000000000 ? 000000001 ?
FFFFFFFFFFE3A80 ?
kteinmap+0010 bl kteinmap1 000000000 ? 00000000D ?
kdgini+036c bl kteinmap FFFFFFFFFFE3DA0 ?
kdg_block_auto+018c bl kdgini 000000000 ? A0B4A708C ?
5676A0005676A ?
80003000804DC ? 000000000 ?
70000016AD82170 ? 110195498 ?
70000016AD82148 ?
ktsa_object_space_u bl kdg_block_auto FFFFFFFFFFE4C50 ?
sage+0950 FFFFFFFFFFE4CA0 ? 200000780 ?
70000011A7D9C88 ?
由trace文件中的insert语句可以知道,这个错误是DBMS_SPACE.OBJECT_GROWTH_TREND进行空间分析时被触发
查询MOS[ID 841158.1],发现这个是一个没有公布的bug(5649098),在11.1.0.7中被修复
解决方案:
1.忽略这个错误,因为这个错误是不可重复的,发生的概率不大
2.升级到11.1.0.7及其以上版本
3.如果遇到这个错误,可以手工执行dbms_space.auto_space_advisor_job_proc();

加我微信(17813235971)
加我QQ(107644445)

