日志是个大问题

这几天接到现场反馈的一个问题，有一个单实```language

例隔一段时间（大约一周）就无法访问了。
于是上去查看具体情况：
alert日志如下：
```Sun Aug 02 04:43:48 2020
Errors in file D:\HIPDATA\ORACLE\diag\rdbms\health\health\trace\health_ora_3314696.trc  (incident=34754):
ORA-04030: 在尝试分配 254488 字节 (QERGH hash-agg,kllcqas:kllsltba) 时进程内存不足
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Sun Aug 02 04:43:49 2020
Errors in file D:\HIPDATA\ORACLE\diag\rdbms\health\health\trace\health_ora_3315024.trc  (incident=34750):
ORA-04030: 在尝试分配 4194328 字节 (QERHJ hash-joi,HT buckets) 时进程内存不足
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Sun Aug 02 04:43:49 2020
Errors in file D:\HIPDATA\ORACLE\diag\rdbms\health\health\trace\health_ora_3315156.trc  (incident=34742):
ORA-04030: 在尝试分配 2093096 字节 (QERHJ hash-joi,QERHJ list array) 时进程内存不足
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Sun Aug 02 04:43:49 2020
Thread 1 advanced to log sequence 438193 (LGWR switch)
  Current log# 1 seq# 438193 mem# 0: D:\HIPDATA\ORACLE\ORADATA\HEALTH\REDO01.LOG
Sun Aug 02 04:43:50 2020
Sweep [inc][34754]: completed
Sweep [inc][34750]: completed
Sweep [inc][34742]: completed
Sun Aug 02 04:44:31 2020
Errors in file D:\HIPDATA\ORACLE\diag\rdbms\health\health\trace\health_ora_3313832.trc  (incident=34751):
ORA-04030: 在尝试分配 131096 字节 (QERGH hash-agg,qeshHDAlloc.3) 时进程内存不足
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
...
Errors in file D:\HIPDATA\ORACLE\diag\rdbms\health\health\trace\health_j000_3323432.trc  (incident=34709):
ORA-04030: out of process memory when trying to allocate 123416 bytes (QERHJ hash-joi,kllcqas:kllsltba)
Incident details in: D:\HIPDATA\ORACLE\diag\rdbms\health\health\incident\incdir_34709\health_j000_3323432_i34709.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Sun Aug 02 06:00:04 2020
Dumping diagnostic data in directory=[cdmp_20200802060004], requested by (instance=1, osid=3323432 (J000)), summary=[incident=34709].
Errors in file D:\HIPDATA\ORACLE\diag\rdbms\health\health\trace\health_j000_3323432.trc  (incident=34710):
ORA-04030: out of process memory when trying to allocate 123416 bytes (QERHJ hash-joi,kllcqas:kllsltba)
ORA-06512: at "SYS.DBMS_SPACE", line 2066
ORA-06512: at "SYS.DBMS_SPACE", line 2542
Incident details in: D:\HIPDATA\ORACLE\diag\rdbms\health\health\incident\incdir_34710\health_j000_3323432_i34710.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file D:\HIPDATA\ORACLE\diag\rdbms\health\health\trace\health_j000_3323432.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_SA_SPC_SY_7455"
ORA-04030: out of process memory when trying to allocate 123416 bytes (QERHJ hash-joi,kllcqas:kllsltba)
ORA-06512: at "SYS.DBMS_SPACE", line 2066
ORA-06512: at "SYS.DBMS_SPACE", line 2542

分析：初步感觉内存不足的问题，于是在查看OS和64位之后，调整内存观察；
然后分析日志又发现如下问题：

Thread 1 advanced to log sequence 436912 (LGWR switch)
  Current log# 6 seq# 436912 mem# 0: D:\HIPDATA\ORACLE\ORADATA\HEALTH\REDO06.LOG
Thread 1 advanced to log sequence 436913 (LGWR switch)
  Current log# 2 seq# 436913 mem# 0: D:\HIPDATA\ORACLE\ORADATA\HEALTH\REDO02.LOG
Thread 1 advanced to log sequence 436914 (LGWR switch)
  Current log# 3 seq# 436914 mem# 0: D:\HIPDATA\ORACLE\ORADATA\HEALTH\REDO03.LOG
Thread 1 advanced to log sequence 436915 (LGWR switch)
  Current log# 1 seq# 436915 mem# 0: D:\HIPDATA\ORACLE\ORADATA\HEALTH\REDO01.LOG
Thread 1 advanced to log sequence 436916 (LGWR switch)
  Current log# 4 seq# 436916 mem# 0: D:\HIPDATA\ORACLE\ORADATA\HEALTH\REDO04.LOG
Mon Jul 27 04:49:37 2020
Thread 1 advanced to log sequence 436917 (LGWR switch)
  Current log# 5 seq# 436917 mem# 0: D:\HIPDATA\ORACLE\ORADATA\HEALTH\REDO05.LOG
Thread 1 cannot allocate new log, sequence 436918
Checkpoint not complete
  Current log# 5 seq# 436917 mem# 0: D:\HIPDATA\ORACLE\ORADATA\HEALTH\REDO05.LOG
Thread 1 advanced to log sequence 436918 (LGWR switch)
  Current log# 6 seq# 436918 mem# 0: D:\HIPDATA\ORACLE\ORADATA\HEALTH\REDO06.LOG
Thread 1 advanced to log sequence 436919 (LGWR switch)
  Current log# 2 seq# 436919 mem# 0: D:\HIPDATA\ORACLE\ORADATA\HEALTH\REDO02.LOG
Thread 1 advanced to log sequence 436920 (LGWR switch)
  Current log# 3 seq# 436920 mem# 0: D:\HIPDATA\ORACLE\ORADATA\HEALTH\REDO03.LOG
Mon Jul 27 04:49:49 2020
Thread 1 cannot allocate new log, sequence 436921
Checkpoint not complete
  Current log# 3 seq# 436920 mem# 0: D:\HIPDATA\ORACLE\ORADATA\HEALTH\REDO03.LOG
Thread 1 advanced to log sequence 436921 (LGWR switch)
  Current log# 1 seq# 436921 mem# 0: D:\HIPDATA\ORACLE\ORADATA\HEALTH\REDO01.LOG
Thread 1 cannot allocate new log, sequence 436922
Checkpoint not complete
.....

每到晚上4点多就会发生Checkpoint not complete，杳看日志共3组，再增加3组继续观察。
隔了一周，现场继续反馈，数据库又挂了。很奇怪， 继续看日志：
发现还是凌晨4点多的后台在抽数据的时候发生日志切换的时候有相关异常，综合分析之后，然后根据前期调整的内容，感觉日志太小导致的，原来是50M，于是加大到200M。每天持续观察发现问题已解决，隔一周也没有发生数据库挂起的情况。
看来日志的大小要根据具体系统的情况设置合适的尺寸。和“小马拉大车”一个道理。