分类目录归档：Oracle

ORA-00742: 日志读取在线程 %d 序列 %d 块 %d 中检测到写入丢失情况

发表于 2022 年 11 月 26 日 由 惜分飞

由于主机异常断电，导致oracle数据库无法正常启动,数据库启动报错ORA-07445 kdxlin,ORA-01172,ORA-00312,ORA-00742等错误

Fri Nov 25 11:24:53 2022
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 15 processes
Started redo scan
Completed redo scan
 read 900 KB redo, 386 data blocks need recovery
Started redo application at
 Thread 1: logseq 93214, block 60163
Recovery of Online Redo Log: Thread 1 Group 1 Seq 93214 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG
Completed redo application of 0.46MB
Fri Nov 25 11:25:02 2022
Hex dump of (file 3, block 208) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p004_1988.trc
Reading datafile 'D:\APP\ADMINISTRATOR\ORADATA\XFF\UNDOTBS01.DBF' for corruption at rdba: 0x00c000d0 (file 3, block 208)
Reread (file 3, block 208) found valid data
Hex dump of (file 3, block 208) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p004_1988.trc
Repaired corruption at (file 3, block 208)
Hex dump of (file 3, block 152) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p004_1988.trc
Reading datafile 'D:\APP\ADMINISTRATOR\ORADATA\XFF\UNDOTBS01.DBF' for corruption at rdba: 0x00c00098 (file 3, block 152)
Reread (file 3, block 152) found same corrupt data (logically corrupt)
RECOVERY OF THREAD 1 STUCK AT BLOCK 152 OF FILE 3
Fri Nov 25 11:25:02 2022
Hex dump of (file 3, block 6859) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p001_19268.trc
Reading datafile 'D:\APP\ADMINISTRATOR\ORADATA\XFF\UNDOTBS01.DBF' for corruption at rdba: 0x00c01acb (file 3, block 6859)
Reread (file 3, block 6859) found same corrupt data (logically corrupt)
Fri Nov 25 11:25:13 2022
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p010_7024.trc  (incident=224379):
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\incident\incdir_224379\XFF_p010_7024_i224379.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Nov 25 11:25:13 2022
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p005_12036.trc  (incident=224343):
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\incident\incdir_224343\XFF_p005_12036_i224343.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Nov 25 11:25:18 2022
Sweep [inc][224379]: completed
Sweep [inc][224343]: completed
Sweep [inc2][224379]: completed
Sweep [inc2][224343]: completed
RECOVERY OF THREAD 1 STUCK AT BLOCK 6859 OF FILE 3
Fri Nov 25 11:25:33 2022
Slave exiting with ORA-1172 exception
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p004_1988.trc:
ORA-01172: 线程 1 的恢复停止在块 152 (在文件 3 中)
ORA-01151: 如果需要, 请使用介质恢复以恢复块和还原备份
Fri Nov 25 11:25:34 2022
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p001_19268.trc:
ORA-10388: parallel query server interrupt (failure)
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p001_19268.trc:
ORA-10388: parallel query server interrupt (failure)
Fri Nov 25 11:25:38 2022
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p010_7024.trc:
ORA-00742: 日志读取在线程 %d 序列 %d 块 %d 中检测到写入丢失情况
ORA-00312: 联机日志 1 线程 1: 'D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG'
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Fri Nov 25 11:25:41 2022
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p005_12036.trc  (incident=224344):
ORA-01578: ORACLE 数据块损坏 (文件号 27, 块号 520567)
ORA-01110: 数据文件 27: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMPP.DBF'
ORA-10564: tablespace POWERMPP
ORA-01110: 数据文件 27: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMPP.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 89776
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\incident\incdir_224344\XFF_p005_12036_i224344.trc
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p005_12036.trc:
ORA-01578: ORACLE 数据块损坏 (文件号 27, 块号 520567)
ORA-01110: 数据文件 27: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMPP.DBF'
ORA-10564: tablespace POWERMPP
ORA-01110: 数据文件 27: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMPP.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 89776
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p010_7024.trc  (incident=224380):
ORA-01578: ORACLE 数据块损坏 (文件号 26, 块号 227101)
ORA-01110: 数据文件 26: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMSP.DBF'
ORA-10564: tablespace POWERMSP
ORA-01110: 数据文件 26: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMSP.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 99375
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Fri Nov 25 11:25:51 2022
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\incident\incdir_224380\XFF_p010_7024_i224380.trc
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p010_7024.trc:
ORA-01578: ORACLE 数据块损坏 (文件号 26, 块号 227101)
ORA-01110: 数据文件 26: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMSP.DBF'
ORA-10564: tablespace POWERMSP
ORA-01110: 数据文件 26: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMSP.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 99375
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Fri Nov 25 11:25:54 2022
Aborting crash recovery due to slave death, attempting serial crash recovery
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
 read 900 KB redo, 386 data blocks need recovery
Started redo application at
 Thread 1: logseq 93214, block 60163
Recovery of Online Redo Log: Thread 1 Group 1 Seq 93214 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG
Hex dump of (file 3, block 6743) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_ora_4172.trc
Reading datafile 'D:\APP\ADMINISTRATOR\ORADATA\XFF\UNDOTBS01.DBF' for corruption at rdba: 0x00c01a57 (file 3, block 6743)
Reread (file 3, block 6743) found same corrupt data (logically corrupt)
RECOVERY OF THREAD 1 STUCK AT BLOCK 6743 OF FILE 3
Fri Nov 25 11:26:09 2022
Aborting crash recovery due to error 1172
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_ora_4172.trc:
ORA-01172: 线程 1 的恢复停止在块 6743 (在文件 3 中)
ORA-01151: 如果需要, 请使用介质恢复以恢复块和还原备份
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_ora_4172.trc:
ORA-01172: 线程 1 的恢复停止在块 6743 (在文件 3 中)
ORA-01151: 如果需要, 请使用介质恢复以恢复块和还原备份
ORA-1172 signalled during: alter database open...

尝试人工recover恢复,报ORA-00283 ORA-00742 ORA-00312错误

SQL> recover database;
ORA-00283: 恢复会话因错误而取消
ORA-00742: 日志读取在线程 %d 序列 %d 块 %d 中检测到写入丢失情况
ORA-00312: 联机日志 1 线程 1: 'D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG'

通过特殊这里之后recover库成功

SQL> recover database until cancel;
ORA-00279: 更改 47073228694 (在 11/25/2022 08:11:15 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\XFF\ARCHIVELOG\2022_11_25\O1_MF_1_932
14_%U_.ARC
ORA-00280: 更改 47073228694 (用于线程 1) 在序列 #93214 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG
已应用的日志。
完成介质恢复。

打开数据库报ORA-600 2662错误
20221125215429

使用oracle patch scn工具快速修改 open库成功
patch_scn-ora-600-2662
SQL> startup mount pfile='d:/pfile.txt'
ORACLE 例程已经启动。

Total System Global Area       1603411968 bytes
Fixed Size                        2281656 bytes
Variable Size                  1191186248 bytes
Database Buffers                402653184 bytes
Redo Buffers                      7290880 bytes
数据库装载完毕。
SQL> ALTER DATABASE OPEN;

数据库已更改。

然后逻辑导出数据,导入新库,完成数据迁移工作

发表在 Oracle, Oracle备份恢复 | 标签为 , , | 评论关闭

OGG-01777 Extract abended as it ran out of sequence numbers used to create TRAIL files

发表于 2022 年 11 月 25 日 由 惜分飞

ogg extract 进程报OGG-01777 Extract abended as it ran out of sequence numbers used to create TRAIL files. The maximum number of TRAIL files allowed is 999999.
20221125124140

由于extract进程已经异常,直接对其抽取的trail文件命名新文件

--ggsci中执行
edit param eora_1  
--修改文件
EXTTRAIL ./dirdat/ab

--ggsci中执行
delete exttrail ./dirdat/aa extract eora_1
add exttrail ./dirdat/ab extract eora_1

GGSCI (xff-source) 51> info eora_1

EXTRACT    EORA_1    Last Started 2022-11-24 22:59   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:00 ago)
Log Read Checkpoint  Oracle Redo Logs
                     First Record         Seqno 248386, RBA 0
                     SCN 0.0 (0)

修改extract pump进程

edit params PORA_1
rmttrail ./dirdat/pc

delete rmttrail ./dirdat/pa extract PORA_1
add rmttrail ./dirdat/pc extract PORA_1
alter ext PORA_1 exttrailsource ./dirdat/ab

GGSCI (xff-source) 56> info pora_1 

EXTRACT    PORA_1    Initialized   2022-11-24 22:59   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:01:37 ago)
Log Read Checkpoint  File ./dirdat/ab000000
                     First Record  RBA 0

replcat进程处理

alter rep rep1 exttrail ./dirdat/pc

GGSCI (xff-target) 9> info rep1

REPLICAT   REP1      Initialized   2022-11-24 23:00   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:07 ago)
Log Read Checkpoint  File ./dirdat/pb000000
                     First Record  RBA 0

启动相关进程进行传输即可.
另外可以考虑相对简单一点操作,直接delete/add exttrail，delete/add rmttrail同名文件,省去修改param文件的麻烦,注意最终找trail文件名称和偏移量是否准确,如果不正确注意使用类似命令修改

add exttrail ./dirdat/xx, extract xxx, megabytes 1024
add rmttrail ./dirdat/xx, megabytes 1024, seqno 0 , rba 0, extract xxx
alter replicat xxx, extseqno 0, extrba 0
alter extract xxx，extseqno xxxx，extrba xxxx
发表在 GoldenGate | 评论关闭

ORA-00800: soft external error, arguments: [Set Priority Failed]

发表于 2022 年 11 月 21 日 由 惜分飞

在一套19.14的linux 2节点rac库中,使用sqlplus启动数据库成功,但是alert日志中报ORA-00800: soft external error, arguments: [Set Priority Failed]错误.

2022-09-21T22:20:35.924251+08:00
Starting background process VKTM
2022-09-21T22:20:35.977936+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_vktm_22653.trc  (incident=880052):
ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM],
 [Check traces and OS configuration], [Check Oracle document and MOS notes], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_880052/orcl1_vktm_22653_i880052.trc
2022-09-21T22:20:35.980555+08:00
Error attempting to elevate VKTM's priority: no further priority changes will be attempted for this process
VKTM started with pid=6, OS id=22653

Starting background process LMHB
2022-09-21T22:20:36.467831+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_lms0_22703_22708.trc  (incident=920005):
ORA-00800: soft external error, arguments: [Set Priority Failed], [LMS0], 
[Check traces and OS configuration], [Check Oracle document and MOS notes], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_920005/orcl1_lms0_22703_22708_i920005.trc
2022-09-21T22:20:36.470535+08:00
Error attempting to elevate LMS0's priority: no further priority changes will be attempted for this process

错误提示比较明显,提升进程的优先级失败,通过操作系统命令观察发现确实进程优先级没有提升

[root@oradb01 ~]# ps -eo pid,class,pri,nice,time,args|grep vktm|grep -v grep 
 5656 TS   19   0 00:00:00 ora_vktm_orcl1
30838 RR   41   - 13:08:36 ora_vktm_+ASM1

重新使用srvctl启动数据库,优先级提升正常,alert日志中也无类似警告

[root@oradb01 ~]# ps -eo pid,class,pri,nice,time,args|grep vktm|grep -v grep 
 5716 RR   41   0 00:00:00 ora_vktm_orcl1
30838 RR   41   - 13:18:46 ora_vktm_+ASM1

这个问题一直困惑了很久,今天无意中在mos上发现了相关mos文档,具体参考:(DB50) Clusterware Fails to Start Because CSSD Cannot Get Real-Time Priority (Doc ID 2903663.1),由于 bug 34286265 and bug 34318125(Bug 34649727 Linux: ORA-800 / Set Priority / DB Performance Merge Patch for 19.17 – 34286265 34318125)
20221121210544

尽量不要使用sqlplus去启动数据库,而是选择使用srvctl,避免在rac环境中导致数据库后台关键进程优先级无法提升问题.

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