有客户由于磁盘空间不足，在线把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] Process ID: 14888 Session ID: 198 Serial number: 3
修改数据库scn(参考blog相关link:ORA-600 2662)数据库顺利open,并且协助客户导出数据并导入新库,完成数据库恢复.
这次运气比较好,只是丢失了一点数据,没有引起重大事故.再此提醒：不太了解oracle的朋友,操作数据库需谨慎,不要在线直接移动数据文件,另外为了更好的恢复效果,更快的恢复,故障之后,最好尽可能的告知所有操作.
加我QQ
