情況：
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 mark = 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
uj5u.com熱心網友回復：
Fri Jun 02 16:12:19 2017Shutting down instance (immediate)
這是一種正常關閉啊，看看OS層有沒有什么設定，或者看看windows的服務及日志有沒有什么log
uj5u.com熱心網友回復：
非常感謝，我日志貼的不全，可能沒有貼到問題關鍵。附件是整個alter日志，應該是5月29號凌晨應用無法登陸了。
鏈接：http://pan.baidu.com/s/1dFkr5rb 密碼：lecm
作業系統出來Oracle服務關閉有一個報錯，就再沒什么別的報錯了。。
uj5u.com熱心網友回復：
問題已經解決，但并沒有進行任何修改。操作如下：
1. 快速startup資料庫；
2. offline除Oracle系統表空間的其他應用程式表空間；
3. 逐個開啟應用程式表空間；
4. 資料庫服務正常，沒有宕機，重啟Oracle服務器后一切正常；
真是奇葩事件。。。。。
uj5u.com熱心網友回復：
你這解決的方法，根本和自動關閉沒半毛關系，Shutting down instance (immediate)是正常的關閉，看看有沒有任務計劃之類的或者其他人手工關掉了轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/93575.html
標籤：基礎和管理
上一篇：powerdesigner生成的sql檔案匯入oracle產生Warning: Trigger created with compilation errors