进行冷备份恢复时发生ora-01092强制断开连接

Tue Oct 07 08:32:27 2008
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.1 Service Pack 3, CPU type 586
Tue Oct 07 08:32:27 2008
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                = 150
  timed_statistics         = TRUE
  shared_pool_size         = 50331648
  large_pool_size          = 8388608
  java_pool_size           = 33554432
  control_files            = d:\oracle\oradata\person\CONTROL01.CTL, d:\oracle\oradata\person\CONTROL02.CTL, d:\oracle\oradata\person\CONTROL03.CTL
  db_block_size            = 8192
  db_cache_size            = 25165824
  compatible               = 9.2.0.0.0
  db_file_multiblock_read_count= 16
  fast_start_mttr_target   = 300
  undo_management          = MANUAL
  undo_tablespace          = system
  undo_retention           = 10800
  remote_login_passwordfile= EXCLUSIVE
  db_domain                =
  instance_name            = person
  dispatchers              = (PROTOCOL=TCP) (SERVICE=personXDB)
  job_queue_processes      = 10
  hash_join_enabled        = TRUE
  background_dump_dest     = d:\oracle\admin\person\bdump
  user_dump_dest           = d:\oracle\admin\person\udump
  core_dump_dest           = d:\oracle\admin\person\cdump
  sort_area_size           = 524288
  db_name                  = person
  open_cursors             = 300
  star_transformation_enabled= FALSE
  query_rewrite_enabled    = FALSE
  pga_aggregate_target     = 25165824
  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
Tue Oct 07 08:32:35 2008
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Tue Oct 07 08:32:37 2008
alter database mount exclusive
Tue Oct 07 08:32:42 2008
Successful mount of redo thread 1, with mount id 834074533.
Tue Oct 07 08:32:42 2008
Database mounted in Exclusive Mode.
Completed: alter database mount exclusive
Tue Oct 07 08:32:42 2008
alter database open
Tue Oct 07 08:32:42 2008
Errors in file d:\oracle\admin\person\bdump\person_dbw0_2068.trc:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: 'D:\ORACLE\ORADATA\PERSON\TEMP01.DBF'
ORA-27046: file size is not a multiple of logical block size
OSD-04012: 文件大小不匹配 (OS 140521472)Tue Oct 07 08:32:43 2008
Beginning crash recovery of 1 threads
Tue Oct 07 08:32:43 2008
Started first pass scan
Tue Oct 07 08:32:43 2008
Completed first pass scan
49 redo blocks read, 4 data blocks need recovery
Tue Oct 07 08:32:43 2008
Started recovery at
Thread 1: logseq 347, block 3, scn 0.0
Recovery of Online Redo Log: Thread 1 Group 2 Seq 347 Reading mem 0
  Mem# 0 errs 0: D:\ORACLE\ORADATA\PERSON\REDO02.LOG
Tue Oct 07 08:32:44 2008
Ended recovery at
Thread 1: logseq 347, block 52, scn 0.303145158
4 data blocks read, 4 data blocks written, 49 redo blocks read
Crash recovery completed successfully
Tue Oct 07 08:32:44 2008
Errors in file d:\oracle\admin\person\bdump\person_dbw0_2068.trc:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: 'D:\ORACLE\ORADATA\PERSON\TEMP01.DBF'
ORA-27046: file size is not a multiple of logical block size
OSD-04012: 文件大小不匹配 (OS 140521472)Tue Oct 07 08:32:44 2008
Errors in file d:\oracle\admin\person\bdump\person_dbw0_2068.trc:
ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: 'D:\ORACLE\ORADATA\PERSON\TEMP01.DBF'Tue Oct 07 08:32:44 2008
File 201 not verified due to error ORA-01157
Tue Oct 07 08:32:44 2008
Thread 1 advanced to log sequence 348
Thread 1 opened at log sequence 348
  Current log# 3 seq# 348 mem# 0: D:\ORACLE\ORADATA\PERSON\REDO03.LOG
Successful open of redo thread 1.
Tue Oct 07 08:32:44 2008
SMON: enabling cache recovery
SMON: enabling tx recovery
Tue Oct 07 08:32:46 2008
Database Characterset is ZHS16GBK
Tue Oct 07 08:32:46 2008
SMON: about to recover undo segment 1
SMON:  undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON:  undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON:  undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON:  undo segment 4 as needs recovery
SMON: about to recover undo segment 5
SMON:  undo segment 5 as needs recovery
SMON: about to recover undo segment 6
SMON:  undo segment 6 as needs recovery
SMON: about to recover undo segment 7
SMON:  undo segment 7 as needs recovery
SMON: about to recover undo segment 8
SMON:  undo segment 8 as needs recovery
SMON: about to recover undo segment 9
SMON:  undo segment 9 as needs recovery
SMON: about to recover undo segment 10
SMON:  undo segment 10 as needs recovery
Tue Oct 07 08:32:48 2008
Errors in file d:\oracle\admin\person\bdump\person_smon_2096.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'D:\ORACLE\ORADATA\PERSON\UNDOTBS01.DBF'

SMON: about to recover undo segment 1
SMON:  undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON:  undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON:  undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON:  undo segment 4 as needs recovery
SMON: about to recover undo segment 5
SMON:  undo segment 5 as needs recovery
SMON: about to recover undo segment 6
SMON:  undo segment 6 as needs recovery
SMON: about to recover undo segment 7
SMON:  undo segment 7 as needs recovery
SMON: about to recover undo segment 8
SMON:  undo segment 8 as needs recovery
SMON: about to recover undo segment 9
SMON:  undo segment 9 as needs recovery
SMON: about to recover undo segment 10
SMON:  undo segment 10 as needs recovery
Tue Oct 07 08:32:49 2008
Errors in file d:\oracle\admin\person\bdump\person_smon_2096.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'D:\ORACLE\ORADATA\PERSON\UNDOTBS01.DBF'Tue Oct 07 08:32:49 2008
Errors in file d:\oracle\admin\person\udump\person_ora_908.trc:
ORA-00604: ?? SQL ? 2 ????
ORA-00376: ???????? 2
ORA-01110: ???? 2: 'D:\ORACLE\ORADATA\PERSON\UNDOTBS01.DBF'Tue Oct 07 08:32:49 2008
Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604
Instance terminated by USER, pid = 908
ORA-1092 signalled during: alter database open...
Tue Oct 07 10:29:00 2008
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                = 150
  timed_statistics         = TRUE
  shared_pool_size         = 50331648
  large_pool_size          = 8388608
  java_pool_size           = 33554432
  control_files            = d:\oracle\oradata\person\CONTROL01.CTL, d:\oracle\oradata\person\CONTROL02.CTL, d:\oracle\oradata\person\CONTROL03.CTL
  db_block_size            = 8192
  db_cache_size            = 25165824
  compatible               = 9.2.0.0.0
  db_file_multiblock_read_count= 16
  fast_start_mttr_target   = 300
  undo_management          = MANUAL
  undo_tablespace          = system
  undo_retention           = 10800
  remote_login_passwordfile= EXCLUSIVE
  db_domain                =
  instance_name            = person
  dispatchers              = (PROTOCOL=TCP) (SERVICE=personXDB)
  job_queue_processes      = 10
  hash_join_enabled        = TRUE
  background_dump_dest     = d:\oracle\admin\person\bdump
  user_dump_dest           = d:\oracle\admin\person\udump
  core_dump_dest           = d:\oracle\admin\person\cdump
  sort_area_size           = 524288
  db_name                  = person
  open_cursors             = 300
  star_transformation_enabled= FALSE
  query_rewrite_enabled    = FALSE
  pga_aggregate_target     = 25165824
  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
Tue Oct 07 10:29:02 2008
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Tue Oct 07 10:29:03 2008
ALTER DATABASE   MOUNT
Tue Oct 07 10:29:07 2008
Successful mount of redo thread 1, with mount id 834102255.
Tue Oct 07 10:29:07 2008
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE   MOUNT
Tue Oct 07 10:29:07 2008
ALTER DATABASE OPEN
Tue Oct 07 10:29:08 2008
Errors in file d:\oracle\admin\person\bdump\person_dbw0_2980.trc:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: 'D:\ORACLE\ORADATA\PERSON\TEMP01.DBF'
ORA-27046: file size is not a multiple of logical block size
OSD-04012: 文件大小不匹配 (OS 140521472)Tue Oct 07 10:29:08 2008
Beginning crash recovery of 1 threads
Tue Oct 07 10:29:08 2008
Started first pass scan
Tue Oct 07 10:29:09 2008
Completed first pass scan
46 redo blocks read, 3 data blocks need recovery
Tue Oct 07 10:29:09 2008
Started recovery at
Thread 1: logseq 348, block 3, scn 0.0
Recovery of Online Redo Log: Thread 1 Group 3 Seq 348 Reading mem 0
  Mem# 0 errs 0: D:\ORACLE\ORADATA\PERSON\REDO03.LOG
Tue Oct 07 10:29:09 2008
Ended recovery at
Thread 1: logseq 348, block 49, scn 0.303165199
3 data blocks read, 3 data blocks written, 46 redo blocks read
Crash recovery completed successfully
Tue Oct 07 10:29:09 2008
Errors in file d:\oracle\admin\person\bdump\person_dbw0_2980.trc:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: 'D:\ORACLE\ORADATA\PERSON\TEMP01.DBF'
ORA-27046: file size is not a multiple of logical block size
OSD-04012: 文件大小不匹配 (OS 140521472)Tue Oct 07 10:29:09 2008
Errors in file d:\oracle\admin\person\bdump\person_dbw0_2980.trc:
ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: 'D:\ORACLE\ORADATA\PERSON\TEMP01.DBF'Tue Oct 07 10:29:09 2008
File 201 not verified due to error ORA-01157
Tue Oct 07 10:29:09 2008
Thread 1 advanced to log sequence 349
Thread 1 opened at log sequence 349
  Current log# 1 seq# 349 mem# 0: D:\ORACLE\ORADATA\PERSON\REDO01.LOG
Successful open of redo thread 1.
Tue Oct 07 10:29:09 2008
SMON: enabling cache recovery
SMON: enabling tx recovery
Tue Oct 07 10:29:09 2008
Database Characterset is ZHS16GBK
Tue Oct 07 10:29:09 2008
SMON: about to recover undo segment 1
SMON:  undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON:  undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON:  undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON:  undo segment 4 as needs recovery
SMON: about to recover undo segment 5
SMON:  undo segment 5 as needs recovery
SMON: about to recover undo segment 6
SMON:  undo segment 6 as needs recovery
SMON: about to recover undo segment 7
SMON:  undo segment 7 as needs recovery
SMON: about to recover undo segment 8
SMON:  undo segment 8 as needs recovery
SMON: about to recover undo segment 9
SMON:  undo segment 9 as needs recovery
SMON: about to recover undo segment 10
SMON:  undo segment 10 as needs recovery
Tue Oct 07 10:29:11 2008
Errors in file d:\oracle\admin\person\bdump\person_smon_2892.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'D:\ORACLE\ORADATA\PERSON\UNDOTBS01.DBF'

SMON: about to recover undo segment 1
SMON:  undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON:  undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON:  undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON:  undo segment 4 as needs recovery
SMON: about to recover undo segment 5
SMON:  undo segment 5 as needs recovery
SMON: about to recover undo segment 6
SMON:  undo segment 6 as needs recovery
SMON: about to recover undo segment 7
SMON:  undo segment 7 as needs recovery
SMON: about to recover undo segment 8
SMON:  undo segment 8 as needs recovery
SMON: about to recover undo segment 9
SMON:  undo segment 9 as needs recovery
SMON: about to recover undo segment 10
SMON:  undo segment 10 as needs recovery
Tue Oct 07 10:29:12 2008
Errors in file d:\oracle\admin\person\bdump\person_smon_2892.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'D:\ORACLE\ORADATA\PERSON\UNDOTBS01.DBF'SMON: about to recover undo segment 1
SMON:  undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON:  undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON:  undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON:  undo segment 4 as needs recovery
SMON: about to recover undo segment 5
SMON:  undo segment 5 as needs recovery
SMON: about to recover undo segment 6
SMON:  undo segment 6 as needs recovery
SMON: about to recover undo segment 7
SMON:  undo segment 7 as needs recovery
SMON: about to recover undo segment 8
SMON:  undo segment 8 as needs recovery
SMON: about to recover undo segment 9
SMON:  undo segment 9 as needs recovery
SMON: about to recover undo segment 10
SMON:  undo segment 10 as needs recovery
Tue Oct 07 10:29:12 2008
Errors in file d:\oracle\admin\person\bdump\person_smon_2892.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'D:\ORACLE\ORADATA\PERSON\UNDOTBS01.DBF'Tue Oct 07 10:29:12 2008
Errors in file d:\oracle\admin\person\udump\person_ora_1844.trc:
ORA-00604: 递归 SQL 层 2 出现错误
ORA-00376: 此时无法读取文件 2
ORA-01110: 数据文件 2: 'D:\ORACLE\ORADATA\PERSON\UNDOTBS01.DBF'Tue Oct 07 10:29:12 2008
Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604
Instance terminated by USER, pid = 1844
ORA-1092 signalled during: ALTER DATABASE OPEN...

1.你恢复的这个新库和源库的db_block_size不一致吗？还是你的TEMP01用了别的块大小？
2.你当时冷备份了有什么文件？现在的恢复操作具体步骤是如何做的？

因数据库是前任工作人员遗留的，已经无法得知db_block_size值，但估计是默认值8192恢复的操作大体是先安装oracle数据库---新建数据库（路径与原库一致）----shutdown---
将oradata\下的遗留的控制文件，数据文件，重做文件，撤消文件等复制到新建的数据库路径中
---最后startup

看你的日志里还有ORA-27046错误啊？启动是用的备份的SPFILE吗？
重新将冷备份考过去，startup mount->alter system set undo_management=manual scope=spfile;
试试。

调试易

进行冷备份恢复时发生ora-01092强制断开连接

解决方案 »