ORA-01110: data file 56

最新推荐文章于 2024-10-28 10:44:42 发布

launch_225

最新推荐文章于 2024-10-28 10:44:42 发布

阅读量3.1k

点赞数

CC 4.0 BY-SA版权

文章标签： file oracle database sql thread transformation

本文链接：https://blog.youkuaiyun.com/launch_225/article/details/7903418

本文记录了一次Oracle数据库中UNDO表空间错误删除及后续恢复的过程。同事误删了UNDO表空间的数据文件，并尝试直接删除文件而非使用标准方法，导致一系列错误。最终通过更改参数文件、启动实例、删除表空间等步骤成功解决问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.事件产生大概是这样，版本9.2.0.1

同事加错文件到UNDO表空间，然后进行删除文件（想通过直接删文件，不想通过常规方法）

Thu Aug 23 09:54:26 2012
ALTER DATABASE datafile 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF' offline DROP

Thu Aug 23 09:54:26 2012
ORA-376 signalled during: ALTER DATABASE datafile 'D:\RMDATA\UNDOTBS1\UNDOTB...
Thu Aug 23 09:55:29 2012
Restarting dead background process QMN0
QMN0 started with pid=9
Thu Aug 23 09:56:48 2012
ALTER DATABASE datafile 'D:\RMDATA\UNDOTBS1\UNDOTBS1_02.DBF' offline DROP

Thu Aug 23 09:56:48 2012
ORA-376 signalled during: ALTER DATABASE datafile 'D:\RMDATA\UNDOTBS1\UNDOTB...
Thu Aug 23 09:57:19 2012
ALTER DATABASE datafile 'D:\RMDATA\UNDOTBS1\UNDOTBS1_02.DBF' offline DROP

Thu Aug 23 09:57:19 2012
ORA-376 signalled during: ALTER DATABASE datafile 'D:\RMDATA\UNDOTBS1\UNDOTB...
Thu Aug 23 10:01:00 2012
Restarting dead background process QMN0
QMN0 started with pid=14
Thu Aug 23 10:01:56 2012
Errors in file d:\oracle\admin\rm\bdump\rm_smon_2964.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:06:16 2012
Restarting dead background process QMN0
QMN0 started with pid=15
Thu Aug 23 10:07:04 2012
Errors in file d:\oracle\admin\rm\bdump\rm_smon_2964.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:09:54 2012
Errors in file d:\oracle\admin\rm\udump\rm_j002_4180.trc:
ORA-12012: error on auto execute of job 341
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:09:54 2012
Errors in file d:\oracle\admin\rm\udump\rm_j000_4116.trc:
ORA-12012: error on auto execute of job 443
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:11:16 2012
Errors in file d:\oracle\admin\rm\udump\rm_j000_4976.trc:
ORA-12012: error on auto execute of job 341
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:11:16 2012
Errors in file d:\oracle\admin\rm\udump\rm_j000_4976.trc:
ORA-12012: error on auto execute of job 443
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:11:31 2012
Restarting dead background process QMN0
QMN0 started with pid=19
Thu Aug 23 10:16:24 2012
Errors in file d:\oracle\admin\rm\udump\rm_j000_5812.trc:
ORA-12012: error on auto execute of job 341
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:16:24 2012
Errors in file d:\oracle\admin\rm\udump\rm_j000_5812.trc:
ORA-12012: error on auto execute of job 443
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:16:40 2012
Restarting dead background process QMN0
QMN0 started with pid=19
Thu Aug 23 10:17:19 2012
Errors in file d:\oracle\admin\rm\bdump\rm_smon_2964.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:19:11 2012
drop tablespace undotbs1

Thu Aug 23 10:19:11 2012
ORA-30013 signalled during: drop tablespace undotbs1
...
Thu Aug 23 10:19:46 2012
drop tablespace undotbs1

Thu Aug 23 10:19:46 2012
ORA-30013 signalled during: drop tablespace undotbs1
...
Thu Aug 23 10:21:33 2012
Errors in file d:\oracle\admin\rm\udump\rm_j000_4480.trc:
ORA-12012: error on auto execute of job 341
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:21:33 2012
Errors in file d:\oracle\admin\rm\udump\rm_j000_4480.trc:
ORA-12012: error on auto execute of job 443
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:21:50 2012
Restarting dead background process QMN0
QMN0 started with pid=18
Thu Aug 23 10:22:28 2012
Errors in file d:\oracle\admin\rm\bdump\rm_smon_2964.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:25:18 2012
CREATE UNDO TABLESPACE undotbs_02 DATAFILE 'D:\RMDATA\UNDOTBS1\undotbs1_08.dbf' SIZE 5000M

Thu Aug 23 10:25:18 2012
ORA-604 signalled during: CREATE UNDO TABLESPACE undotbs_02 DATAFILE 'D:\RMD...
Thu Aug 23 10:26:41 2012
Errors in file d:\oracle\admin\rm\udump\rm_j000_6064.trc:
ORA-12012: error on auto execute of job 341
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:26:41 2012
Errors in file d:\oracle\admin\rm\udump\rm_j000_6064.trc:
ORA-12012: error on auto execute of job 443
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:27:05 2012
CREATE UNDO TABLESPACE undotbs_02 DATAFILE 'E:\RMDATA\RM\undotbs1_08.dbf' SIZE 5000M

Thu Aug 23 10:27:05 2012
ORA-604 signalled during: CREATE UNDO TABLESPACE undotbs_02 DATAFILE 'E:\RMD...
Thu Aug 23 10:27:05 2012
Restarting dead background process QMN0
QMN0 started with pid=18
Thu Aug 23 10:27:36 2012
Errors in file d:\oracle\admin\rm\bdump\rm_smon_2964.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:28:53 2012
CREATE UNDO TABLESPACE undotbs_02 DATAFILE 'C:\KFC\undotbs1_08.dbf' SIZE 5000M

Thu Aug 23 10:28:53 2012
ORA-604 signalled during: CREATE UNDO TABLESPACE undotbs_02 DATAFILE 'C:\KFC...
Thu Aug 23 10:31:50 2012
Errors in file d:\oracle\admin\rm\udump\rm_j000_5692.trc:
ORA-12012: error on auto execute of job 341
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:31:50 2012
Errors in file d:\oracle\admin\rm\udump\rm_j000_5692.trc:
ORA-12012: error on auto execute of job 443
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:32:14 2012
Restarting dead background process QMN0
QMN0 started with pid=18
Thu Aug 23 10:32:44 2012
Errors in file d:\oracle\admin\rm\bdump\rm_smon_2964.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:36:58 2012
Errors in file d:\oracle\admin\rm\udump\rm_j000_5524.trc:
ORA-12012: error on auto execute of job 341
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:36:58 2012
Errors in file d:\oracle\admin\rm\udump\rm_j000_5524.trc:
ORA-12012: 自动执行作业 443 出错
ORA-00376: 此时无法读取文件 56
ORA-01110: 数据文件 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:36:58 2012
Errors in file d:\oracle\admin\rm\udump\rm_j000_5524.trc:
ORA-00604: 递归 SQL 层 1 出现错误
ORA-00376: 此时无法读取文件 56
ORA-01110: 数据文件 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'
ORA-12012: 自动执行作业 443 出错
ORA-00376: 此时无法读取文件 56
ORA-01110: 数据文件 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:37:30 2012
Restarting dead background process QMN0
QMN0 started with pid=18
Thu Aug 23 10:37:52 2012
Errors in file d:\oracle\admin\rm\bdump\rm_smon_2964.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:42:06 2012
Errors in file d:\oracle\admin\rm\udump\rm_j000_4844.trc:
ORA-12012: error on auto execute of job 341
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:42:06 2012
Errors in file d:\oracle\admin\rm\udump\rm_j000_4844.trc:
ORA-12012: error on auto execute of job 443
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:42:45 2012
Restarting dead background process QMN0
QMN0 started with pid=18
Thu Aug 23 10:43:01 2012
Errors in file d:\oracle\admin\rm\bdump\rm_smon_2964.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:47:15 2012
Errors in file d:\oracle\admin\rm\udump\rm_j000_4808.trc:
ORA-12012: error on auto execute of job 341
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:47:15 2012
Errors in file d:\oracle\admin\rm\udump\rm_j000_4808.trc:
ORA-12012: error on auto execute of job 443
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:48:00 2012
Restarting dead background process QMN0
QMN0 started with pid=18
Thu Aug 23 10:48:09 2012
Errors in file d:\oracle\admin\rm\bdump\rm_smon_2964.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:52:23 2012
Errors in file d:\oracle\admin\rm\udump\rm_j000_4712.trc:
ORA-12012: error on auto execute of job 341
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:52:23 2012
Errors in file d:\oracle\admin\rm\udump\rm_j000_4712.trc:
ORA-12012: error on auto execute of job 443
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 10:53:15 2012
Restarting dead background process QMN0
QMN0 started with pid=18
Thu Aug 23 10:53:17 2012
Errors in file d:\oracle\admin\rm\bdump\rm_smon_2964.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

---------------------------------------------------------------------------------------------

Thu Aug 23 14:42:01 2012
Errors in file d:\oracle\admin\rm\bdump\rm_smon_2964.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Dump file d:\oracle\admin\rm\bdump\alert_rm.log
Thu Aug 23 14:45:31 2012
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.2 Service Pack 1, CPU type 586
Thu Aug 23 14:45:31 2012
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.1.0.
System parameters with non-default values:
processes                = 600
sessions                 = 700
timed_statistics         = TRUE
shared_pool_size         = 587202560
sga_max_size             = 1868114520
large_pool_size          = 16777216
java_pool_size           = 16777216
pre_page_sga             = TRUE
control_files            = E:\rmdata\RM\CONTROL01.CTL, E:\rmdata\RM\CONTROL02.CTL, E:\rmdata\RM\CONTROL03.CTL
db_block_size            = 8192
db_cache_size            = 1157627904
compatible               = 9.2.0.0.0
log_buffer               = 5242880
db_file_multiblock_read_count= 16
fast_start_mttr_target   = 300
undo_management          = AUTO
undo_tablespace          = UNDOTBS1
undo_retention           = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain                =
instance_name            = RM
dispatchers              = (PROTOCOL=TCP) (SERVICE=RMXDB)
utl_file_dir             = D:\temp
job_queue_processes      = 20
cursor_sharing           = EXACT
hash_join_enabled        = TRUE
background_dump_dest     = D:\oracle\admin\RM\bdump
user_dump_dest           = D:\oracle\admin\RM\udump
core_dump_dest           = D:\oracle\admin\RM\cdump
sort_area_size           = 524288
db_name                  = RM
open_cursors             = 300
star_transformation_enabled= FALSE
query_rewrite_enabled    = FALSE
pga_aggregate_target     = 629145600
aq_tm_processes          = 1
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
CJQ0 started with pid=8
QMN0 started with pid=9
Thu Aug 23 14:45:34 2012
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Thu Aug 23 14:45:34 2012
alter database mount exclusive
Thu Aug 23 14:45:39 2012
Successful mount of redo thread 1, with mount id 2735046863.
Thu Aug 23 14:45:39 2012
Database mounted in Exclusive Mode.
Completed: alter database mount exclusive
Thu Aug 23 14:45:39 2012
alter database open
Thu Aug 23 14:45:39 2012
Beginning crash recovery of 1 threads
Thu Aug 23 14:45:39 2012
Started first pass scan
Thu Aug 23 14:45:39 2012
Completed first pass scan
1 redo blocks read, 1 data blocks need recovery
Thu Aug 23 14:45:39 2012
Started recovery at
Thread 1: logseq 22948, block 2622, scn 0.0
Recovery of Online Redo Log: Thread 1 Group 3 Seq 22948 Reading mem 0
Mem# 0 errs 0: E:\RMDATA\RM\REDO03.LOG
Thu Aug 23 14:45:39 2012
Ended recovery at
Thread 1: logseq 22948, block 2623, scn 2910.35700835
1 data blocks read, 1 data blocks written, 1 redo blocks read
Crash recovery completed successfully
Thu Aug 23 14:45:40 2012
Thread 1 advanced to log sequence 22949
Thread 1 opened at log sequence 22949
Current log# 1 seq# 22949 mem# 0: E:\RMDATA\RM\REDO01.LOG
Successful open of redo thread 1.
Thu Aug 23 14:45:40 2012
SMON: enabling cache recovery
Thu Aug 23 14:45:40 2012
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Thu Aug 23 14:45:41 2012
SMON: enabling tx recovery
Thu Aug 23 14:45:41 2012
Database Characterset is ZHS16GBK
Thu Aug 23 14:45:41 2012
Errors in file d:\oracle\admin\rm\bdump\rm_smon_4700.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 56 cannot be read at this time
ORA-01110: data file 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Thu Aug 23 14:45:41 2012
Errors in file d:\oracle\admin\rm\udump\rm_ora_3044.trc:
ORA-00376: ???????? 56
ORA-01110: ???? 56: 'D:\RMDATA\UNDOTBS1\UNDOTBS1_01.DBF'

Error 376 happened during db open, shutting down database
USER: terminating instance due to error 376
Instance terminated by USER, pid = 3044
ORA-1092 signalled during: alter database open...