linux主機共16核32G，down機時間點4：00整，將遠程連接kill掉以后，資料庫自己就回復了。。。之前遇到過好幾次這種情況
process和job_queue_processes 應該是夠用
alert日志如下附件alert.log
Thu Jul 19 15:59:33 2018
LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3135)
LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
Error 3135 for archive log file 3 to 'uni-db-2'
LGWR: Error 1041 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host 'uni-db-2'
Thu Jul 19 16:00:12 2018
Process m000 died, see its trace file
Thu Jul 19 16:00:19 2018
Process J000 died, see its trace file
kkjcre1p: unable to spawn jobq slave process
Errors in file /oracle/product/diag/rdbms/dbnms/dbnms/trace/dbnms_cjq0_23793.trc:
Thu Jul 19 16:01:14 2018
Process m000 died, see its trace file
Thu Jul 19 16:01:15 2018
WARNING: Heavy swapping observed on system in last 5 mins.
pct of memory swapped in [1.48%] pct of memory swapped out [3.62%].
Please make sure there is no memory pressure and the SGA and PGA
are configured correctly. Look at DBRM trace file for more details.
Process J000 died, see its trace file
kkjcre1p: unable to spawn jobq slave process
Errors in file /oracle/product/diag/rdbms/dbnms/dbnms/trace/dbnms_cjq0_23793.trc:
Process J000 died, see its trace file
kkjcre1p: unable to spawn jobq slave process
Errors in file /oracle/product/diag/rdbms/dbnms/dbnms/trace/dbnms_cjq0_23793.trc:
Thu Jul 19 16:05:04 2018
Starting ORACLE instance (normal)
Thu Jul 19 16:06:05 2018
opiodr aborting process unknown ospid (21211) as a result of ORA-609
Thu Jul 19 16:06:26 2018
Thread 1 cannot allocate new log, sequence 36439
Private strand flush not complete
Current log# 3 seq# 36438 mem# 0: /oradata1/dbnms/redo03.log
LGWR: Failed to archive log 3 thread 1 sequence 36438 (3135)
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
Thread 1 advanced to log sequence 36439 (LGWR switch)
Current log# 5 seq# 36439 mem# 0: /oradata1/dbnms/redo05.log
Thu Jul 19 16:06:32 2018
Archived Log entry 37943 added for thread 1 sequence 36438 ID 0x31cb5ee3 dest 1:
Thu Jul 19 16:06:44 2018
Thread 1 cannot allocate new log, sequence 36440
Private strand flush not complete
Current log# 5 seq# 36439 mem# 0: /oradata1/dbnms/redo05.log
LGWR: Standby redo logfile selected for thread 1 sequence 36440 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 36440 (LGWR switch)
Current log# 4 seq# 36440 mem# 0: /oradata1/dbnms/redo04.log
Thu Jul 19 22:00:00 2018
Setting Resource Manager plan SCHEDULER[0x318C]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Thu Jul 19 22:00:00 2018
Starting background process VKRM
Thu Jul 19 22:00:00 2018
VKRM started with pid=105, OS id=15513
Thu Jul 19 22:00:10 2018
Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Thu Jul 19 22:00:45 2018
TABLE SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY: ADDED INTERVAL PARTITION SYS_P90004 (918) VALUES LESS THAN (TO_DATE(' 2018-07-19 10:10:20', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE SYS.WRI$_OPTSTAT_HISTGRM_HISTORY: ADDED INTERVAL PARTITION SYS_P90007 (918) VALUES LESS THAN (TO_DATE(' 2018-07-19 10:10:23', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
Thu Jul 19 22:04:54 2018
Thread 1 cannot allocate new log, sequence 36463
Private strand flush not complete
Current log# 2 seq# 36462 mem# 0: /oradata1/dbnms/redo02.log
LGWR: Standby redo logfile selected for thread 1 sequence 36463 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 36463 (LGWR switch)
Current log# 3 seq# 36463 mem# 0: /oradata1/dbnms/redo03.log
Thu Jul 19 22:05:06 2018
Archived Log entry 37992 added for thread 1 sequence 36462 ID 0x31cb5ee3 dest 1:
Thu Jul 19 22:13:45 2018
End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Thu Jul 19 22:19:07 2018
Thread 1 cannot allocate new log, sequence 36464
Private strand flush not complete
Current log# 3 seq# 36463 mem# 0: /oradata1/dbnms/redo03.log
LGWR: Standby redo logfile selected for thread 1 sequence 36464 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 36464 (LGWR switch)
Current log# 5 seq# 36464 mem# 0: /oradata1/dbnms/redo05.log
Thu Jul 19 22:19:14 2018
Archived Log entry 37994 added for thread 1 sequence 36463 ID 0x31cb5ee3 dest 1:
=================================================================
Trace file /oracle/product/diag/rdbms/dbnms/dbnms/trace/dbnms_cjq0_23793.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/11.2.0/db_1
System name: Linux
Node name: UNI-DB-1
Release: 2.6.32-358.el6.x86_64
Version: #1 SMP Tue Jan 29 11:47:41 EST 2013
Machine: x86_64
VM name: VMWare Version: 6
Instance name: dbnms
Redo thread mounted by this instance: 1
Oracle process number: 45
Unix process pid: 23793, image: oracle@UNI-DB-1 (CJQ0)
*** 2018-06-15 22:00:00.251
*** SESSION ID:(1848.7) 2018-06-15 22:00:00.251
*** CLIENT ID:() 2018-06-15 22:00:00.251
*** SERVICE NAME:(SYS$BACKGROUND) 2018-06-15 22:00:00.251
*** MODULE NAME:() 2018-06-15 22:00:00.251
*** ACTION NAME:() 2018-06-15 22:00:00.251
*** TRACE FILE RECREATED AFTER BEING REMOVED ***
Setting Resource Manager plan SCHEDULER[0x318D]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Closing scheduler window
*** 2018-06-16 02:00:00.157
Closing Resource Manager plan via scheduler window
Clearing Resource Manager plan via parameter
Setting Resource Manager plan SCHEDULER[0x318E]:DEFAULT_MAINTENANCE_PLAN via scheduler window
*** 2018-06-16 06:00:00.093
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Closing scheduler window
*** 2018-06-17 02:00:00.163
Closing Resource Manager plan via scheduler window
Clearing Resource Manager plan via parameter
Setting Resource Manager plan SCHEDULER[0x318F]:DEFAULT_MAINTENANCE_PLAN via scheduler window
*** 2018-07-19 16:04:39.391
Process J000 is dead (pid=18592 req_ver=276613 cur_ver=276613 state=KSOSP_SPAWNED).
*** 2018-07-19 16:04:41.393
Process J000 is dead (pid=18602 req_ver=276614 cur_ver=276614 state=KSOSP_SPAWNED).
*** 2018-07-19 16:04:55.400
Process J000 is dead (pid=18677 req_ver=276621 cur_ver=276621 state=KSOSP_SPAWNED).
Setting Resource Manager plan SCHEDULER[0x318C]:DEFAULT_MAINTENANCE_PLAN via scheduler window
=======================================
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 15008M
sga_target big integer 0
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 5G
SQL> show parameter mem
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 15008M
memory_target big integer 15008M
shared_memory_address integer 0
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 2
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 5
processes integer 1500
=============================
awr
uj5u.com熱心網友回復：
壓力太高，記憶體爆了。
措施：1、優化SQL，加速高并發SQL的處理，減少大量連接堵塞耗費大量PGA的幾率；2、可以嘗試適當降低SGA來釋放更多的記憶體給高峰期的PGA，以免發生swap out
uj5u.com熱心網友回復：
我用的自動記憶體管理，一共15008M這么大，意思是我降低sga_max_size這個的大小，一面SGA把所有記憶體都占滿？
uj5u.com熱心網友回復：
沒看懂你最后一句話的意思。
SGA在實體啟動后，就會占用掉，無論你用不用資料庫，減小SGA，就是留下了更多可用的物理記憶體，而PGA這個pga_aggregate_target引數，是并沒有限制作用的，它僅僅只是個指導值，換句話說，它是可以被突破的，在你PGA不夠用的時候，oracle不會管你是不是物理記憶體夠不夠用，都會去使用，除非被作業系統層面的類似OOM-killer給干掉。
減小SGA，從現在的引數設定來看，就是減小SGA_MAX_SIZE，或者你可以換用SGA_TARGET+PGA_AGGREGATE_TARGET的組合。
uj5u.com熱心網友回復：
很可能是由于linux 和oracle相關的記憶體沒有配置好具體可以加qq，把相關配置發來看看
uj5u.com熱心網友回復：
好的呀，麻煩留下qq號轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/63602.html
標籤：基礎和管理
下一篇：oracle資料遷移后需要的作業