在线mv方式迁移数据文件导致数据库无法正常启动---惜分飞

有客户在数据库没有关闭的情况下,直接操作系统层面mv方式把数据文件从一个分区迁移到另外一个分区,再创建ln -s(软连接)的方式实现数据文件不修改路径的方式数据文件迁移,结果数据库重启之后,库无法正常启动,报ORA-01172 ORA-01151错误

Fri Dec 29 09:49:19 2023 ALTER DATABASE OPEN Beginning crash recovery of 1 threads  parallel recovery started with 11 processes Started redo scan Completed redo scan  read 11591 KB redo, 1566 data blocks need recovery Started redo application at  Thread 1: logseq 6320, block 479571 Recovery of Online Redo Log: Thread 1 Group 4 Seq 6320 Reading mem 0   Mem# 0: /data/oracle/oradata/orcl/redo04.log Fri Dec 29 09:49:19 2023 Hex dump of (file 6, block 3598593) in trace file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_p002_6696.trc Fri Dec 29 09:49:19 2023 Fri Dec 29 09:49:19 2023 Hex dump of (file 5, block 27832) in trace file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_p008_6708.trc Hex dump of (file 6, block 3598208) in trace file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_p010_6712.trc Reading datafile '/oadate/xff/xff_01.dbf' for corruption at rdba: 0x01b6e901 (file 6, block 3598593) Reading datafile '/oadate/xff/xff.dbf' for corruption at rdba: 0x01406cb8 (file 5, block 27832) Reread (file 6, block 3598593) found same corrupt data (logically corrupt) Reading datafile '/oadate/xff/xff_01.dbf' for corruption at rdba: 0x01b6e780 (file 6, block 3598208) Reread (file 5, block 27832) found same corrupt data (logically corrupt)     RECOVERY OF THREAD 1 STUCK AT BLOCK 3598593 OF FILE 6 Reread (file 6, block 3598208) found same corrupt data (logically corrupt) RECOVERY OF THREAD 1 STUCK AT BLOCK 3598208 OF FILE 6RECOVERY OF THREAD 1 STUCK AT BLOCK 27832 OF FILE 5 Fri Dec 29 09:49:32 2023 Slave exiting with ORA-1172 exception Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_p010_6712.trc: ORA-01172: recovery of thread 1 stuck at block 3598208 of file 6 ORA-01151: use media recovery to recover block, restore backup if needed Fri Dec 29 09:49:32 2023 Fri Dec 29 09:49:32 2023 Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_p008_6708.trc: ORA-10388: parallel query server interrupt (failure) Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_p002_6696.trc: ORA-10388: parallel query server interrupt (failure) Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_p008_6708.trc: ORA-10388: parallel query server interrupt (failure) Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_p002_6696.trc: ORA-10388: parallel query server interrupt (failure) Fri Dec 29 09:49:32 2023 Aborting crash recovery due to slave death, attempting serial crash recovery Beginning crash recovery of 1 threads Started redo scan Completed redo scan  read 11591 KB redo, 1566 data blocks need recovery Started redo application at  Thread 1: logseq 6320, block 479571 Recovery of Online Redo Log: Thread 1 Group 4 Seq 6320 Reading mem 0   Mem# 0: /data/oracle/oradata/orcl/redo04.log Hex dump of (file 6, block 3598593) in trace file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6690.trc Reading datafile '/oadate/xff/xff_01.dbf' for corruption at rdba: 0x01b6e901 (file 6, block 3598593) Reread (file 6, block 3598593) found same corrupt data (logically corrupt) RECOVERY OF THREAD 1 STUCK AT BLOCK 3598593 OF FILE 6 Aborting crash recovery due to error 1172 Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6690.trc: ORA-01172: recovery of thread 1 stuck at block 3598593 of file 6 ORA-01151: use media recovery to recover block, restore backup if needed Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6690.trc: ORA-01172: recovery of thread 1 stuck at block 3598593 of file 6 ORA-01151: use media recovery to recover block, restore backup if needed ORA-1172 signalled during: ALTER DATABASE OPEN...

sqlplus恢复数据库报错

SQL> recover datafile 6; ORA-00283: recovery session canceled due to errors ORA-00600: internal error code, arguments: [3020], [6], [3578240], [28744064], [], [], [], [], [], [], [], [] ORA-10567: Redo is inconsistent with data block (file# 6, block# 3578240, file offset is 3543138304 bytes) ORA-10564: tablespace xff ORA-01110: data file 6: '/oadate/xff/xff_01.dbf' ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'

alert日志报ORA-600 3020错误

Fri Dec 29 17:43:03 2023 ALTER DATABASE RECOVER  datafile 6  Media Recovery Start Serial Media Recovery started Recovery of Online Redo Log: Thread 1 Group 4 Seq 6320 Reading mem 0   Mem# 0: /data/oracle/oradata/orcl/redo04.log Fri Dec 29 17:43:42 2023 Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_30140.trc  (incident=462294): ORA-00600: internal error code, arguments: [3020], [6], [3578240], [28744064], [], [], [], [], [], [], [], [] ORA-10567: Redo is inconsistent with data block (file# 6, block# 3578240, file offset is 3543138304 bytes) ORA-10564: tablespace XFF ORA-01110: data file 6: '/oadate/xff/xff_01.dbf' ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK' Incident details in: /data/oracle/diag/rdbms/orcl/orcl/incident/incdir_462294/orcl_ora_30140_i462294.trc Fri Dec 29 17:43:42 2023 Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Media Recovery failed with error 600 ORA-283 signalled during: ALTER DATABASE RECOVER  datafile 6  ...

此类故障是由于在线拷贝数据文件,可能有不少最新写入的数据都有数据文件和redo不一致的风险,引起这里的ORA-600 3020最好不要通过allow N corruption的方式跳过,因为可能导致大量数据文件坏块,这样就不光丢失了redo数据,可能数据文件中好的block中的很多数据也丢失.对于这种情况,我们为了减少客户的数据丢失,选择了最少数据丢失的方法:通过bbed修改文件头,然后直接recover 数据文件,open库

Fri Dec 29 18:05:36 2023 ALTER DATABASE RECOVER  datafile 5  Media Recovery Start Serial Media Recovery started Recovery of Online Redo Log: Thread 1 Group 4 Seq 6320 Reading mem 0   Mem# 0: /data/oracle/oradata/orcl/redo04.log Media Recovery Complete (orcl) Completed: ALTER DATABASE RECOVER  datafile 5  ALTER DATABASE RECOVER  datafile 6  Media Recovery Start Serial Media Recovery started Recovery of Online Redo Log: Thread 1 Group 4 Seq 6320 Reading mem 0   Mem# 0: /data/oracle/oradata/orcl/redo04.log Media Recovery Complete (orcl) Completed: ALTER DATABASE RECOVER  datafile 6  Fri Dec 29 18:07:02 2023 ALTER DATABASE OPEN Beginning crash recovery of 1 threads  parallel recovery started with 11 processes Started redo scan Completed redo scan  read 11591 KB redo, 0 data blocks need recovery Started redo application at  Thread 1: logseq 6320, block 479571 Recovery of Online Redo Log: Thread 1 Group 4 Seq 6320 Reading mem 0   Mem# 0: /data/oracle/oradata/orcl/redo04.log Completed redo application of 0.00MB Completed crash recovery at  Thread 1: logseq 6320, block 502754, scn 2657849964  0 data blocks read, 0 data blocks written, 11591 redo k-bytes read Thread 1 advanced to log sequence 6321 (thread open) Thread 1 opened at log sequence 6321   Current log# 5 seq# 6321 mem# 0: /data/oracle/oradata/orcl/redo05.log Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set SMON: enabling cache recovery [2656] Successfully onlined Undo Tablespace 2. Undo initialization finished serial:0 start:933676634 end:933676704 diff:70 (0 seconds) Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Database Characterset is AL32UTF8 No Resource Manager plan active replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC Fri Dec 29 18:07:04 2023 QMNC started with pid=31, OS id=2687 Completed: ALTER DATABASE OPEN

然后逻辑方式迁移数据到新库中,最大程度抢救客户数据

• ORA-00600 dbkif_find_next_record_1
• ORA-600 kcratr_scan_lastbwr 恢复
• ORA-07445: exception encountered: core dump [kdxlin()+4088]处理
• ora-600 kcratr_scan_lastbwr
• Oracle Recovery Tools 解决ORA-600 3020故障
• ORA-600 3020/ORA-600 2662故障
• ORA-00742 ORA-00312故障恢复
• 硬件故障数据库异常恢复
• ORA-00322 ORA-00312恢复
• ORA-600 kcbzpbuf_1故障恢复
• ORA-01172 ORA-01151 故障恢复
• raid强制上线后数据库无法启动故障处理