移動資料檔案導緻資料庫故障恢複

有客戶由于磁盤空間不足，線上把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]