[오라클] Alert Log

Oracle Database에는 데이터베이스 "문제"를 방지, 감지, 진단 및 해결하기 위한 fault dianosability infrastructure가 포함되어 있습니다. "문제"에는 코드 버그, 메타데이터 손상 및 고객 데이터 손상과 같은 심각한 오류가 포함됩니다. fault dianosability infrastructure에는 ADR, alert log등이 포함됩니다. 

 

- Automatic Diagnostic Repository: ADR(자동 진단 저장소)은 trace 파일, alert log, DDL log 및 Health Monitor reports와 같은 데이터베이스 진단 데이터를 저장하는 파일 기반 저장소입니다. 아래 그림은 데이터베이스 인스턴스에 대한 ADR 디렉토리 계층 구조를 보여줍니다. 

---

- Alert log: 모든 데이터베이스에는 데이터베이스 메시지 및 오류의 시간순 로그가 포함된 XML 파일인 alert log가 있습니다. alert log에 포함되는 내용으로는,

- All internal errors (ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60)

- SQL*Plus commands STARTUP, SHUTDOWN, ARCHIVE LOG, and RECOVER같은 관리 작업 

- shared server 및 디스패처 프로세스의 기능과 관련된 여러 메시지 및 오류

- materialized view의 자동 refresh 중 오류

 

V$DIAG_INFO를 쿼리하여 alert log를 찾습니다.

SQL> SELECT NAME, VALUE FROM V$DIAG_INFO;

NAME                  VALUE
--------------------- ------------------------------------------------------------
Diag Enabled          TRUE
ADR Base              /u01/app/oracle
ADR Home              /u01/app/oracle/diag/rdbms/orcl/orcl
Diag Trace            /u01/app/oracle/diag/rdbms/orcl/orcl/trace
Diag Alert            /u01/app/oracle/diag/rdbms/orcl/orcl/alert
Diag Incident         /u01/app/oracle/diag/rdbms/orcl/orcl/incident
Diag Cdump            /u01/app/oracle/diag/rdbms/orcl/orcl/cdump
Health Monitor        /u01/app/oracle/diag/rdbms/orcl/orcl/hm
Default Trace File    /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5201.trc
Active Problem Count  0
Active Incident Count 0
ORACLE_HOME           /u01/app/oracle/product/19.0.0/dbhome_1

 

[oracle@enp0s3 oracle]$ cd diag/rdbms/orcl/orcl/alert/

 

다음은 alert log 내용 중 메모리와 관련된 파라미터 메세지의 일부입니다.

-- 내렸을때 alert log의 가장 마지막 메세지와 재기동 시 가장 첫 메세지
<msg time='2022-01-05T14:03:52.129+09:00' org_id='oracle' comp_id='rdbms'
 msg_id='opistp_real:2303:4046551520' type='NOTIFICATION' group='shutdown'
 level='16' host_id='enp0s3' host_addr='10.0.2.15'
 pid='17764'>
 <txt>Instance shutdown complete (OS id: 17764)
 </txt>
</msg>
<msg time='2022-01-05T14:04:39.443+09:00' org_id='oracle' comp_id='VOS'
 msg_id='opistr_real:1285:1197768316' type='NOTIFICATION' group='VOS'
 level='16' host_id='enp0s3' host_addr='10.0.2.15'
 pid='32117' con_uid='1' con_id='1'
 con_name='CDB$ROOT' attn_msg_id='35782658' id_suffix='DB STARTUP/SHUTDOWN'
 oper_id='startup' flags='15'>
 <txt>Starting ORACLE instance (normal) (OS id: 32117)
 </txt>
 <cause>A command to startup the instance was executed
 </cause>
 <action>Check alert log for progress and completion of command
 </action>
</msg>
.
.
.
<msg time='2022-01-05T14:04:39.838+09:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:24169:527288951' type='NOTIFICATION' group='startup'
 level='16' host_id='enp0s3' host_addr='10.0.2.15'
 pid='32117' con_uid='1' con_id='1'
 con_name='CDB$ROOT'>
 <txt>  processes                = 300
 </txt>
</msg>
<msg time='2022-01-05T14:04:39.838+09:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:24157:3633090201' type='NOTIFICATION' group='startup'
 level='16' host_id='enp0s3' host_addr='10.0.2.15'
 pid='32117' con_uid='1' con_id='1'
 con_name='CDB$ROOT'>
 <txt>  nls_language             = &quot;KOREAN&quot;
 </txt>
</msg>
<msg time='2022-01-05T14:04:39.838+09:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:24157:3633090201' type='NOTIFICATION' group='startup'
 level='16' host_id='enp0s3' host_addr='10.0.2.15'
 pid='32117' con_uid='1' con_id='1'
 con_name='CDB$ROOT'>
 <txt>  nls_territory            = &quot;KOREA&quot;
 </txt>
</msg>
<msg time='2022-01-05T14:04:39.838+09:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:24169:527288951' type='NOTIFICATION' group='startup'
 level='16' host_id='enp0s3' host_addr='10.0.2.15'
 pid='32117' con_uid='1' con_id='1'
 con_name='CDB$ROOT'>
 <txt>  memory_target            = 1152M
 </txt>
</msg>
<msg time='2022-01-05T14:04:39.838+09:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:24157:3633090201' type='NOTIFICATION' group='startup'
 level='16' host_id='enp0s3' host_addr='10.0.2.15'
 pid='32117' con_uid='1' con_id='1'
 con_name='CDB$ROOT'>
 <txt>  control_files            = &quot;/u01/app/oracle/oradata/ORCL/control01.ctl&quot;
 </txt>
</msg>
<msg time='2022-01-05T14:04:39.838+09:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:24157:3633090201' type='NOTIFICATION' group='startup'
 level='16' host_id='enp0s3' host_addr='10.0.2.15'
 pid='32117' con_uid='1' con_id='1'
 con_name='CDB$ROOT'>
 <txt>  control_files            = &quot;/u01/app/oracle/oradata/ORCL/control02.ctl&quot;
 </txt>
</msg>
<msg time='2022-01-05T14:04:39.838+09:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:24169:527288951' type='NOTIFICATION' group='startup'
 level='16' host_id='enp0s3' host_addr='10.0.2.15'
 pid='32117' con_uid='1' con_id='1'
 con_name='CDB$ROOT'>
 <txt>  db_block_size            = 8192
 </txt>
</msg>
<msg time='2022-01-05T14:04:39.838+09:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:24157:3633090201' type='NOTIFICATION' group='startup'
 level='16' host_id='enp0s3' host_addr='10.0.2.15'
 pid='32117' con_uid='1' con_id='1'
 con_name='CDB$ROOT'>
 <txt>  compatible               = &quot;19.0.0&quot;
 </txt>
</msg>
<msg time='2022-01-05T14:04:39.838+09:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:24169:527288951' type='NOTIFICATION' group='startup'
 level='16' host_id='enp0s3' host_addr='10.0.2.15'
 pid='32117' con_uid='1' con_id='1'
 con_name='CDB$ROOT'>
 <txt>  db_recovery_file_dest_size= 2G
 </txt>
</msg>
.
.
.
<msg time='2022-01-05T14:04:40.669+09:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='enp0s3'
 host_addr='10.0.2.15' pid='32117' con_uid='1'
 con_id='1' con_name='CDB$ROOT'>
 <txt>starting up 1 shared server(s) ...
 </txt>
</msg>
.
.
.
<msg time='2022-01-05T14:04:43.445+09:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='enp0s3'
 host_addr='10.0.2.15' pid='32191' con_uid='1'
 con_id='1' con_name='CDB$ROOT'>
 <txt>Using default pga_aggregate_limit of 2048 MB
 </txt>
</msg>