有客户由于磁盘空间不足，在线把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的朋友,操作数据库需谨慎,不要在线直接移动数据文件,另外为了更好的恢复效果,更快的恢复,故障之后,最好尽可能的告知所有操作.

ITPub博客

磁盘空间不足迁移数据文件导致故障恢复

最新文章