情况：
windows 2008 server
Oracle 11g 2.0.2非RAC问题：
一直正常运行1年多，最近突然无法连接，检查发现windows服务（OracleServiceSID）会自动关闭；
进一步发现，mounted状态时，服务不掉；
一旦startup数据库，服务就会掉；操作：
重做一个相同环境数据库，将数据文件、控制文件等整体迁移，问题依然存在；alter部分日志：
Fri Jun 02 16:12:12 2017
QMNC started with pid=27, OS id=6616
Completed: alter database open
Starting background process CJQ0
Fri Jun 02 16:12:13 2017
CJQ0 started with pid=29, OS id=7040
Fri Jun 02 16:12:13 2017
db_recovery_file_dest_size of 40960 MB is 34.56% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Setting Resource Manager plan SCHEDULER[0x310A]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Starting background process VKRM
Fri Jun 02 16:12:16 2017
VKRM started with pid=28, OS id=6604
Fri Jun 02 16:12:19 2017
Shutting down instance (immediate)
Shutting down instance: further logons disabled
Stopping background process QMNC
Stopping background process CJQ0
Stopping background process MMNL
Stopping background process MMON
License high water = 8
Stopping Job queue slave processes, flags = 7
Job queue slave processes stopped
All dispatchers and shared servers shutdown
ALTER DATABASE CLOSE NORMAL
Fri Jun 02 16:12:22 2017
SMON: disabling tx recovery
SMON: disabling cache recovery
Fri Jun 02 16:12:22 2017
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thread 1 closed at log sequence 32189
Successful close of redo thread 1
Completed: ALTER DATABASE CLOSE NORMAL
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Fri Jun 02 16:12:23 2017
Stopping background process VKTM
Fri Jun 02 16:12:25 2017
Instance shutdown complete
Fri Jun 02 16:12:32 2017
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =126
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in server-side spfile E:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILESN67.ORA
System parameters with non-default values:
processes = 750
java_pool_size = 128M
nls_length_semantics = "BYTE"
memory_target = 26240M
control_files = "E:\APP\ADMINISTRATOR\ORADATA\SN67\CONTROL01.CTL"
control_files = "E:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\SN67\CONTROL02.CTL"
db_block_size = 8192
compatible = "11.2.0.0.0"
db_recovery_file_dest = "e:\app\Administrator\fast_recovery_area"
db_recovery_file_dest_size= 40G
undo_tablespace = "UNDOTBS1"
db_securefile = "PERMITTED"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=SN67XDB)"
audit_file_dest = "E:\APP\ADMINISTRATOR\ADMIN\SN67\ADUMP"
audit_trail = "DB"
sort_area_size = 131072
db_name = "SN67"
open_cursors = 750
optimizer_capture_sql_plan_baselines= FALSE
optimizer_use_sql_plan_baselines= FALSE
diagnostic_dest = "E:\APP\ADMINISTRATOR"
Fri Jun 02 16:12:33 2017
PMON started with pid=2, OS id=6472
Fri Jun 02 16:12:33 2017
PSP0 started with pid=3, OS id=6828
Fri Jun 02 16:12:35 2017
VKTM started with pid=4, OS id=6896 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Fri Jun 02 16:12:35 2017
GEN0 started with pid=5, OS id=6040
Fri Jun 02 16:12:35 2017
DIAG started with pid=6, OS id=928
Fri Jun 02 16:12:35 2017
DBRM started with pid=7, OS id=6760
Fri Jun 02 16:12:35 2017
DIA0 started with pid=8, OS id=6992
Fri Jun 02 16:12:35 2017
MMAN started with pid=9, OS id=5612
Fri Jun 02 16:12:35 2017
DBW0 started with pid=10, OS id=6340
Fri Jun 02 16:12:35 2017
LGWR started with pid=11, OS id=6596
Fri Jun 02 16:12:35 2017
CKPT started with pid=12, OS id=7012
Fri Jun 02 16:12:35 2017
SMON started with pid=13, OS id=6156
Fri Jun 02 16:12:35 2017
RECO started with pid=14, OS id=6720
Fri Jun 02 16:12:35 2017
MMON started with pid=15, OS id=6708
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Fri Jun 02 16:12:35 2017
MMNL started with pid=16, OS id=6276
starting up 1 shared server(s) ...
ORACLE_BASE from environment = e:\app\Administrator
Fri Jun 02 16:12:35 2017
ALTER DATABASE MOUNT
Successful mount of redo thread 1, with mount id 2614104627
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Fri Jun 02 16:12:39 2017
ALTER DATABASE OPEN
Thread 1 opened at log sequence 32189
Current log# 5 seq# 32189 mem# 0: E:\APP\ADMINISTRATOR\ORADATA\SN67\REDO05.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
[6888] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:72860422 end:72860469 diff:47 (0 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Fri Jun 02 16:12:39 2017
QMNC started with pid=20, OS id=4824
Completed: ALTER DATABASE OPEN
Starting background process CJQ0
Fri Jun 02 16:12:40 2017
CJQ0 started with pid=22, OS id=6608
Fri Jun 02 16:12:40 2017
db_recovery_file_dest_size of 40960 MB is 34.56% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Setting Resource Manager plan SCHEDULER[0x310A]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Starting background process VKRM
Fri Jun 02 16:12:43 2017
VKRM started with pid=23, OS id=5872
= "DB"
sort_area_size = 131072
db_name = "SN67"
open_cursors = 750
optimizer_capture_sql_plan_baselines= FALSE
optimizer_use_sql_plan_baselines= FALSE
diagnostic_dest = "E:\APP\ADMINISTRATOR"
Thu Jun 01 19:58:45 2017
PMON started with pid=2, OS id=4416
Thu Jun 01 19:58:45 2017
PSP0 started with pid=3, OS id=4424
Thu Jun 01 19:58:46 2017
VKTM started with pid=4, OS id=4464 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Thu Jun 01 19:58:47 2017
GEN0 started with pid=5, OS id=4468
Thu Jun 01 19:58:47 2017
DIAG started with pid=6, OS id=4472
Thu Jun 01 19:58:47 2017
DBRM started with pid=7, OS id=4476
Thu Jun 01 19:58:47 2017
DIA0 started with pid=8, OS id=4480
Thu Jun 01 19:58:47 2017
MMAN started with pid=9, OS id=4484
Thu Jun 01 19:58:47 2017
DBW0 started with pid=10, OS id=4488
Thu Jun 01 19:58:47 2017
LGWR started with pid=11, OS id=4492
Thu Jun 01 19:58:47 2017
CKPT started with pid=12, OS id=4496
Thu Jun 01 19:58:47 2017
SMON started with pid=13, OS id=4500
Thu Jun 01 19:58:47 2017
RECO started with pid=14, OS id=4504
Thu Jun 01 19:58:47 2017
MMON started with pid=15, OS id=4508
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = e:\app\Administrator
Thu Jun 01 19:58:47 2017
MMNL started with pid=16, OS id=4512
Thu Jun 01 19:58:48 2017
alter database mount exclusive
Successful mount of redo thread 1, with mount id 2613980344
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount exclusive
alter database open
Beginning crash recovery of 1 threads
parallel recovery started with 7 processes
Started redo scan
Completed redo scan
read 97 KB redo, 70 data blocks need recovery
Started redo application at
Thread 1: logseq 32183, block 4407
Recovery of Online Redo Log: Thread 1 Group 5 Seq 32183 Reading mem 0
Mem# 0: E:\APP\ADMINISTRATOR\ORADATA\SN67\REDO05.LOG
Completed redo application of 0.07MB
Completed crash recovery at
Thread 1: logseq 32183, block 4601, scn 245868132
70 data blocks read, 70 data blocks written, 97 redo k-bytes read
Thread 1 advanced to log sequence 32184 (thread open)
Thread 1 opened at log sequence 32184
Current log# 6 seq# 32184 mem# 0: E:\APP\ADMINISTRATOR\ORADATA\SN67\REDO06.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
[4568] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:35833 end:36535 diff:702 (7 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Thu Ju
windows 2008 server
Oracle 11g 2.0.2非RAC问题：
一直正常运行1年多，最近突然无法连接，检查发现windows服务（OracleServiceSID）会自动关闭；
进一步发现，mounted状态时，服务不掉；
一旦startup数据库，服务就会掉；操作：
重做一个相同环境数据库，将数据文件、控制文件等整体迁移，问题依然存在；alter部分日志：
Fri Jun 02 16:12:12 2017
QMNC started with pid=27, OS id=6616
Completed: alter database open
Starting background process CJQ0
Fri Jun 02 16:12:13 2017
CJQ0 started with pid=29, OS id=7040
Fri Jun 02 16:12:13 2017
db_recovery_file_dest_size of 40960 MB is 34.56% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Setting Resource Manager plan SCHEDULER[0x310A]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Starting background process VKRM
Fri Jun 02 16:12:16 2017
VKRM started with pid=28, OS id=6604
Fri Jun 02 16:12:19 2017
Shutting down instance (immediate)
Shutting down instance: further logons disabled
Stopping background process QMNC
Stopping background process CJQ0
Stopping background process MMNL
Stopping background process MMON
License high water = 8
Stopping Job queue slave processes, flags = 7
Job queue slave processes stopped
All dispatchers and shared servers shutdown
ALTER DATABASE CLOSE NORMAL
Fri Jun 02 16:12:22 2017
SMON: disabling tx recovery
SMON: disabling cache recovery
Fri Jun 02 16:12:22 2017
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thread 1 closed at log sequence 32189
Successful close of redo thread 1
Completed: ALTER DATABASE CLOSE NORMAL
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Fri Jun 02 16:12:23 2017
Stopping background process VKTM
Fri Jun 02 16:12:25 2017
Instance shutdown complete
Fri Jun 02 16:12:32 2017
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =126
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in server-side spfile E:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILESN67.ORA
System parameters with non-default values:
processes = 750
java_pool_size = 128M
nls_length_semantics = "BYTE"
memory_target = 26240M
control_files = "E:\APP\ADMINISTRATOR\ORADATA\SN67\CONTROL01.CTL"
control_files = "E:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\SN67\CONTROL02.CTL"
db_block_size = 8192
compatible = "11.2.0.0.0"
db_recovery_file_dest = "e:\app\Administrator\fast_recovery_area"
db_recovery_file_dest_size= 40G
undo_tablespace = "UNDOTBS1"
db_securefile = "PERMITTED"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=SN67XDB)"
audit_file_dest = "E:\APP\ADMINISTRATOR\ADMIN\SN67\ADUMP"
audit_trail = "DB"
sort_area_size = 131072
db_name = "SN67"
open_cursors = 750
optimizer_capture_sql_plan_baselines= FALSE
optimizer_use_sql_plan_baselines= FALSE
diagnostic_dest = "E:\APP\ADMINISTRATOR"
Fri Jun 02 16:12:33 2017
PMON started with pid=2, OS id=6472
Fri Jun 02 16:12:33 2017
PSP0 started with pid=3, OS id=6828
Fri Jun 02 16:12:35 2017
VKTM started with pid=4, OS id=6896 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Fri Jun 02 16:12:35 2017
GEN0 started with pid=5, OS id=6040
Fri Jun 02 16:12:35 2017
DIAG started with pid=6, OS id=928
Fri Jun 02 16:12:35 2017
DBRM started with pid=7, OS id=6760
Fri Jun 02 16:12:35 2017
DIA0 started with pid=8, OS id=6992
Fri Jun 02 16:12:35 2017
MMAN started with pid=9, OS id=5612
Fri Jun 02 16:12:35 2017
DBW0 started with pid=10, OS id=6340
Fri Jun 02 16:12:35 2017
LGWR started with pid=11, OS id=6596
Fri Jun 02 16:12:35 2017
CKPT started with pid=12, OS id=7012
Fri Jun 02 16:12:35 2017
SMON started with pid=13, OS id=6156
Fri Jun 02 16:12:35 2017
RECO started with pid=14, OS id=6720
Fri Jun 02 16:12:35 2017
MMON started with pid=15, OS id=6708
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Fri Jun 02 16:12:35 2017
MMNL started with pid=16, OS id=6276
starting up 1 shared server(s) ...
ORACLE_BASE from environment = e:\app\Administrator
Fri Jun 02 16:12:35 2017
ALTER DATABASE MOUNT
Successful mount of redo thread 1, with mount id 2614104627
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Fri Jun 02 16:12:39 2017
ALTER DATABASE OPEN
Thread 1 opened at log sequence 32189
Current log# 5 seq# 32189 mem# 0: E:\APP\ADMINISTRATOR\ORADATA\SN67\REDO05.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
[6888] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:72860422 end:72860469 diff:47 (0 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Fri Jun 02 16:12:39 2017
QMNC started with pid=20, OS id=4824
Completed: ALTER DATABASE OPEN
Starting background process CJQ0
Fri Jun 02 16:12:40 2017
CJQ0 started with pid=22, OS id=6608
Fri Jun 02 16:12:40 2017
db_recovery_file_dest_size of 40960 MB is 34.56% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Setting Resource Manager plan SCHEDULER[0x310A]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Starting background process VKRM
Fri Jun 02 16:12:43 2017
VKRM started with pid=23, OS id=5872
= "DB"
sort_area_size = 131072
db_name = "SN67"
open_cursors = 750
optimizer_capture_sql_plan_baselines= FALSE
optimizer_use_sql_plan_baselines= FALSE
diagnostic_dest = "E:\APP\ADMINISTRATOR"
Thu Jun 01 19:58:45 2017
PMON started with pid=2, OS id=4416
Thu Jun 01 19:58:45 2017
PSP0 started with pid=3, OS id=4424
Thu Jun 01 19:58:46 2017
VKTM started with pid=4, OS id=4464 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Thu Jun 01 19:58:47 2017
GEN0 started with pid=5, OS id=4468
Thu Jun 01 19:58:47 2017
DIAG started with pid=6, OS id=4472
Thu Jun 01 19:58:47 2017
DBRM started with pid=7, OS id=4476
Thu Jun 01 19:58:47 2017
DIA0 started with pid=8, OS id=4480
Thu Jun 01 19:58:47 2017
MMAN started with pid=9, OS id=4484
Thu Jun 01 19:58:47 2017
DBW0 started with pid=10, OS id=4488
Thu Jun 01 19:58:47 2017
LGWR started with pid=11, OS id=4492
Thu Jun 01 19:58:47 2017
CKPT started with pid=12, OS id=4496
Thu Jun 01 19:58:47 2017
SMON started with pid=13, OS id=4500
Thu Jun 01 19:58:47 2017
RECO started with pid=14, OS id=4504
Thu Jun 01 19:58:47 2017
MMON started with pid=15, OS id=4508
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = e:\app\Administrator
Thu Jun 01 19:58:47 2017
MMNL started with pid=16, OS id=4512
Thu Jun 01 19:58:48 2017
alter database mount exclusive
Successful mount of redo thread 1, with mount id 2613980344
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount exclusive
alter database open
Beginning crash recovery of 1 threads
parallel recovery started with 7 processes
Started redo scan
Completed redo scan
read 97 KB redo, 70 data blocks need recovery
Started redo application at
Thread 1: logseq 32183, block 4407
Recovery of Online Redo Log: Thread 1 Group 5 Seq 32183 Reading mem 0
Mem# 0: E:\APP\ADMINISTRATOR\ORADATA\SN67\REDO05.LOG
Completed redo application of 0.07MB
Completed crash recovery at
Thread 1: logseq 32183, block 4601, scn 245868132
70 data blocks read, 70 data blocks written, 97 redo k-bytes read
Thread 1 advanced to log sequence 32184 (thread open)
Thread 1 opened at log sequence 32184
Current log# 6 seq# 32184 mem# 0: E:\APP\ADMINISTRATOR\ORADATA\SN67\REDO06.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
[4568] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:35833 end:36535 diff:702 (7 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Thu Ju
解决方案 »
- Oracle 11g exp里面如何删除一个working space？
- 数据库架构询问
- Oracle离线访问---求助
- oracle 对于大对象 blob 提取到内存上来
- Oracle对字段名大小写敏感？还要加引号？
- oracle sql语句多表联查效率问题
- 报‘动态链接库c:\winnt\system32\user32.dll初始化失败，进程非正常终止’错？sos！小妹急等！
- 请问在ORACLE中有没有像SQLSERVER中那样的种子，来实现自增类型，或方法
- 请问判断系统时间与某字段时间的差值？
- 求一Sql语句,由列到行转换的问题
- Oracle 00600 -> 12514 一个比较复杂的数据库崩溃问题
- 存储过程编码找出物料编码为“1”或“5”开头的物料对应的成本对象/产品为“8”或字母开头的成品关系；一共需出具2014-2017年4年对应清单
Shutting down instance (immediate)
这是一种正常关闭啊，看看OS层有没有什么设置，或者看看windows的服务及日志有没有什么log
附件是整个alter日志，应该是5月29号凌晨应用无法登陆了。链接：http://pan.baidu.com/s/1dFkr5rb 密码：lecm操作系统出来Oracle服务关闭有一个报错，就再没什么别的报错了。。
操作如下：
1. 快速startup数据库；
2. offline除Oracle系统表空间的其他应用程序表空间；
3. 逐个开启应用程序表空间；
4. 数据库服务正常，没有宕机，重启Oracle服务器后一切正常；真是奇葩事件。。