-
加我公众号(orasos)
加我微信(17813235971)
加我QQ(107644445)
标签云
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,878)
- DB2 (22)
- MySQL (82)
- Oracle (1,704)
- 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 (72)
- Oracle Bug (8)
- Oracle RAC (56)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (29)
- Oracle备份恢复 (659)
- Oracle安装升级 (107)
- 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)
-
最近发表
- 由于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系统数据库恢复过程
- OBET-Oracle Block Editor Tool使用说明
- 几乎动用了所有手段的Oracle故障恢复
- Oracle Block Edit Tool (obet) 功能增强–2026.07
- Oracle 19c 202607补丁（RUs+OJVM）-19.32
- Patch_SCN快速修复ORA-01555数据库open故障
- 快速处理 ORA-01210: data file header is media corrupt 故障
- ORA-00314: log 3 of thread 1, expected sequence# N doesn’t match 0
分类目录归档：Oracle备份恢复
Controlfile sequence number in file header is different from the one in memory
最近一段时间遇到多个客户类似如下错误,由于某种原因导致rac两个节点oracle实例一段时间之后就crash,然后重启,系统非常不稳定,严重影响业务使用,一个节点alert日志有类似日志
Sat Oct 22 13:01:59 2022
Instance recovery: looking for dead threads
********************* ATTENTION: ********************
The controlfile header block returned by the OS
has a sequence number that is too old.
The controlfile might be corrupted.
PLEASE DO NOT ATTEMPT TO START UP THE INSTANCE
without following the steps below.
RE-STARTING THE INSTANCE CAN CAUSE SERIOUS DAMAGE
TO THE DATABASE, if the controlfile is truly corrupted.
In order to re-start the instance safely,
please do the following:
(1) Save all copies of the controlfile for later
analysis and contact your OS vendor and Oracle support.
(2) Mount the instance and issue:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
(3) Unmount the instance.
(4) Use the script in the trace file to
RE-CREATE THE CONTROLFILE and open the database.
*****************************************************
Submitted all GCS remote-cache requests
Post SMON to start 1st pass IR
Fix write in gcs resources
Reconfiguration complete
SMON (ospid: 31905): terminating the instance
Sat Oct 22 13:02:03 2022
System state dump requested by (instance=1, osid=31905 (SMON)), summary=[abnormal instance termination].
System State dumped to trace file
/u02/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_diag_31863_20221022130203.trc
Sat Oct 22 13:02:04 2022
ORA-1092 : opitsk aborting process
Instance terminated by SMON, pid = 31905
另外的一个节点错误日志如下:
Sat Oct 22 13:02:41 2022
[24610] Successfully onlined Undo Tablespace 5.
Undo initialization finished serial:0 start:152928776 end:152930636 diff:1860 (18 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Sat Oct 22 13:03:10 2022
Error: Controlfile sequence number in file header is different from the one in memory
Please check that the correct mount options are used if controlfile is located on NFS
USER (ospid: 24610): terminating the instance
Sat Oct 22 13:03:10 2022
System state dump requested by (instance=2, osid=24610), summary=[abnormal instance termination].
System State dumped to trace file
/u02/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_diag_24486_20221022130310.trc
Dumping diagnostic data in directory=[cdmp_20221022130310],
requested by (instance=2, osid=24610), summary=[abnormal instance termination].
Instance terminated by USER, pid = 24610
通过节点的日志基本上可以确定是由于Controlfile sequence number异常导致,官方也有类似的文档描述:
Instance Crashed With “Controlfile sequence number in file header is different from the onein memory” (Doc ID 2884958.1)
oracle drop tablespace 恢复最后一招
客户由于不太熟悉oracle数据库,加入错误的数据文件到一个业务表空间,然后经过一系列操作,最终结果是做了drop tablespace xxx including contents and datafiles操作,导致表空间被删除,而且该数据库未做任何备份和归档.通过检查操作系统和数据库alert日志,确认文件已经从os层面彻底删除
对于这种情况,如果立即保护现场,然后通过反删除软件进行恢复,运气好还可以恢复出来被删除的数据文件,然后再通过dul之类的工具恢复其中数据,这个客户库一直没有关闭,而且尝试各种工具恢复,解决均没有正常恢复出来被删除的几个数据文件.对于这种情况，正常os层面的方法肯定无法恢复了,尝试使用基于block层面技术进行扫描磁盘恢复,结果发现运气还不错,绝大部分block都被找到,参考类似恢复方法:Oracle 数据文件大小为0kb或者文件丢失恢复通过类似分析,找出来绝大多数没有覆盖的block,恢复出来被删除的含数据的file 18,20,21，并通过检测整体恢复效果如下
通过dul工具结合客户提供的表定语以及获取到大表id信息,相互关联,快速恢复客户绝大多数数据,最大限度挽回客户损失.
对于oracle 删除表空间之类的操作，我们可以做到block层面深入恢复,理论上只要你被删除的数据文件在磁盘上还有一个block没有被覆盖,我们都可以把里面的数据恢复出来,最大限度的减少因为这种误操作而引起的损失.如果有类似需求无法自行解决,可以联系我们进行最大限度、最快速度的抢救数据.
电话/微信:17813235971 Q Q:107644445
E-Mail:dba@xifenfei.com
oracle open hang 等待cursor: pin S wait on X
客户19.3数据库无法在open过程hang住
分析alert日志
2022-10-18T15:04:57.374918+08:00 db_recovery_file_dest_size of 102400 MB is 9.58% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. 2022-10-18T15:09:55.535116+08:00 ORCLPDB(4):>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=40 ORCLPDB(4):System State dumped to trace file /data/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p000_31225.trc 2022-10-18T15:19:33.374783+08:00 ORCLPDB(4):Undo initialization recovery: err:1013 start: 1911760 end: 2790176 diff: 878416 ms (878.4 seconds) Pdb ORCLPDB hit error 1013 during open read write (1) and will be closed.
这里比较明显,cdb本身open正常,但是其中的ORCLPDB这个pdb无法open,从而显示hang的情况.查询数据库会话情况
SQL> select event,sql_id from v$session where wait_class#<>6; EVENT SQL_ID ---------------------------------------------------------------- ------------- cursor: pin S wait on X 8vyjutx6hg3wh cursor: pin S wait on X 8vyjutx6hg3wh cursor: pin S wait on X 8vyjutx6hg3wh cursor: pin S wait on X 8vyjutx6hg3wh cursor: pin S wait on X 8vyjutx6hg3wh cursor: pin S wait on X 8vyjutx6hg3wh cursor: pin S wait on X 8vyjutx6hg3wh cursor: pin S wait on X 8vyjutx6hg3wh cursor: pin S wait on X 8vyjutx6hg3wh cursor: pin S wait on X 8vyjutx6hg3wh cursor: pin S wait on X 8vyjutx6hg3wh EVENT SQL_ID ---------------------------------------------------------------- ------------- cursor: pin S wait on X 8vyjutx6hg3wh cursor: pin S wait on X 8vyjutx6hg3wh cursor: pin S wait on X 8vyjutx6hg3wh cursor: pin S wait on X 8vyjutx6hg3wh cursor: pin S wait on X 8vyjutx6hg3wh row cache lock 8vyjutx6hg3wh cursor: pin S wait on X 8vyjutx6hg3wh SQL*Net message to client 1dhc13tspcmys cursor: pin S wait on X 8vyjutx6hg3wh cursor: pin S wait on X 8vyjutx6hg3wh cursor: pin S wait on X 8vyjutx6hg3wh EVENT SQL_ID ---------------------------------------------------------------- ------------- cursor: pin S wait on X 8vyjutx6hg3wh cursor: pin S wait on X 8vyjutx6hg3wh cursor: pin S wait on X 8vyjutx6hg3wh cursor: pin S wait on X 8vyjutx6hg3wh cursor: pin S wait on X 8vyjutx6hg3wh cursor: pin S wait on X 8vyjutx6hg3wh cursor: pin S wait on X 8vyjutx6hg3wh cursor: pin S wait on X 8vyjutx6hg3wh cursor: pin S wait on X 8vyjutx6hg3wh cursor: pin S wait on X 8vyjutx6hg3wh cursor: pin S wait on X 8vyjutx6hg3wh 33 rows selected. SQL> select sql_text from v$sql where sql_id='8vyjutx6hg3wh'; SQL_TEXT -------------------------------------------------------------------------------- update /*+ rule */ undo$ set name=:2,file#=:3,block#=:4,status$=:5,user#=:6,undo sqn=:7,xactsqn=:8,scnbas=:9,scnwrp=:10,inst#=:11,ts#=:12,spare1=:13 where us#=:1 SQL> col machine for a30 SQL> / INST_ID SID PADDR SQL_ID EVENT MACHINE PROGRAM ---------- ---------- ---------------- ------------- ------------------------------ ------------ ------------------------ 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) INST_ID SID PADDR SQL_ID EVENT MACHINE PROGRAM ---------- ---------- ---------------- ------------- ------------------------------ ------------ ----------------------- 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 1 4517 00000001907FEC50 8vyjutx6hg3wh row cache lock xifenfei oracle@xifenfei (P000) 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) INST_ID SID PADDR SQL_ID EVENT MACHINE PROGRAM ---------- ---------- ---------------- ------------- ------------------------------ ------------ ----------------------- 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 1 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X xifenfei oracle@xifenfei (P002) 31 rows selected. SQL> l 1 select b.INST_ID,b.sid,b.paddr,b.sql_id,b.event,b.MACHINE,b.PROGRAM from gv$session a,gv$session b 2* where a.event='cursor: pin S wait on X' and a.FINAL_BLOCKING_INSTANCE=b.INST_ID and 3* a.FINAL_BLOCKING_SESSION=b.sid SQL>
通过上述分析,可以确认是在open pdb的过程中被cursor: pin S wait on X等待事件阻塞,而被阻塞的sql是update /*+ rule */ undo$ set …………，基于这样的情况.大概率可以确认是由于bug导致.通过查询mos,确认和Bug 30931981 – Open Reset Logs Hangs With ‘row cache lock’ and ‘cursor: pin s wait for x’ Waits (Doc ID 30931981.8)类似.
不过由于客户的版本是19.3,没有对应的小patch发布.通过对相关恢复事务和恢复方式进行处理,在没有对数据库版本进行任何调整的情况下,顺利打开数据库以最快的速度恢复业务
