移动数据文件导致数据库故障恢复

有客户由于磁盘空间不足，在线把oracle数据迁移到其他位置

Tue Jun 01 11:44:32 2021 Thread 1 advanced to log sequence 28754 (LGWR switch) Current log # 2 seq# 28754 mem# 0: /u01/app/oracle/oradata/orcl/redo02.log Tue Jun 01 11:59:54 2021 Non critical error ORA-48113 caught while writing to trace file "/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_mmon_23341.trc" Error message: Writing to the above trace file is disabled for now on... Tue Jun 01 12:00:00 2021 Non critical error ORA-48181 caught while writing to trace file "/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j000_29692.trc" Error message: Linux-x86_64 Error: 28: No space left on device Additional information: 1 Writing to the above trace file is disabled for now on... Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j000_29692 .trc: ORA-12012: error on auto execute of job "XIFENFEI" . "STATISTICS_1_JOBS" ORA-06575: Package or function PKG_STAT_1_2018 is in an invalid state Tue Jun 01 12:12:26 2021

迁移走数据文件之后,数据库报错，并且强制关闭数据库

ORA-01116: error in opening database file 30 ORA-01110: data file 30: '/u02/orcdate/AAAA.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m001_29106 .trc: ORA-01116: error in opening database file 31 ORA-01110: data file 31: '/u02/orcdate/CBD.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Mon Jun 07 10:25:03 2021 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_9817 .trc: ORA-01116: error in opening database file 24 ORA-01110: data file 24: '/u02/orcdate/ABC.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Mon Jun 07 10:25:10 2021 Shutting down instance (immediate) Stopping background process SMCO Shutting down instance: further logons disabled Read of datafile '/u02/orcdate/XXXXXXX.dbf' (fno 21) header failed with ORA-01208 Rereading datafile 21 header failed with ORA-01208 Mon Jun 07 10:25:36 2021 Adjusting the default value of parameter parallel_max_servers from 640 to 485 due to the value of parameter processes (500) Starting ORACLE instance (normal) Mon Jun 07 10:28:20 2021 Shutting down instance (abort) License high water mark = 152 USER (ospid: 7987): terminating the instance Termination issued to instance processes. Waiting for the processes to exit Mon Jun 07 10:28:30 2021 Instance termination failed to kill one or more processes Instance terminated by USER, pid = 7987 Mon Jun 07 10:28:31 2021 Instance shutdown complete

然后又把文件迁移回来,并且进行了一系列数据库恢复,最后我们接手是情况是有多个文件被offline,并且有一个文件报WRONG FILE NUMBER,通过Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)脚本检查,对其中的v$datafile,v$datafile_header,v$tablespace综合分析

确认是WXD_YPT表空间数据文件直接拷贝为WXD表空间数据文件,经过客户确认,WXD数据不重要,客户先忽略.

通过一系列处理，尝试open数据库,报ORA-600 2662错误

SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-00603: ORACLE server session terminated by fatal error ORA-00600: internal error code, arguments: [2662], [3786], [2612118101], [3786], [2612128448], [12583040] ORA-00600: internal error code, arguments: [2662], [3786], [2612118100], [3786], [2612128448], [12583040] ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [2662], [3786], [2612118098], [3786], [2612128448], [12583040]