- 오라클 로그 삭제를 안했지만 과거 로그가 삭제되는 경우가 있는데 ADR에서 자동으로 관리하기 때문
- ADR의 Base Directory 는 11g 에서 새로 도입된 diagnostic_dest 파라미터 설정 값
- 11g부터는 Alert File 이나 Trace File 이 예전과 같이 *_dump_dest 로 지정된 Directory에 더 이상 발생하지 않는다예전과 같은 방식으로 직접 *_dump_dest 를 지정하더라도 Oracle 은 이를 무시하고 새로운 방식(Automatic Diagnostic Repository)에 따라 각각의 File 에 저장한다
- diagnostic_dest 의 기본경로는 $ORACLE_HOME/log 이지만, 환경변수 $ORACLE_BASE 가 설정되어 있는 경우에는 해당 경로로 설정됨
ADR 디렉토리 경로
ADR 디렉토리 경로와 설정 현황 등을 확인할 수 있습니다
SQL> select * from v$diag_info;
INST_ID NAME VALUE CON_ID
---------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
1 Diag Enabled TRUE 0
1 ADR Base /ORA19/app/oracle 0
1 ADR Home /ORA19/app/oracle/diag/rdbms/ora19c/ora19c 0
1 Diag Trace /ORA19/app/oracle/diag/rdbms/ora19c/ora19c/trace 0
1 Diag Alert /ORA19/app/oracle/diag/rdbms/ora19c/ora19c/alert 0
1 Diag Incident /ORA19/app/oracle/diag/rdbms/ora19c/ora19c/incident 0
1 Diag Cdump /ORA19/app/oracle/diag/rdbms/ora19c/ora19c/cdump 0
1 Health Monitor /ORA19/app/oracle/diag/rdbms/ora19c/ora19c/hm 0
1 Default Trace File /ORA19/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_ora_7835.trc 0
1 Active Problem Count 2 0
1 Active Incident Count 2 0
1 ORACLE_HOME /ORA19/app/oracle/product/19.0.0/db_1 0
12 rows selected.
ADRCI(ADR Command Interpreter) 사용
adrci로 들어가서 명령어를 실행할 수 있습니다
show home 명령어에서 나온 경로의 로그파일들은 retention policy에 의해 자동으로 삭제 됩니다
[oracle@svr1 ~]$ adrci
ADRCI: Release 19.0.0.0.0 - Production on Fri Jun 17 11:04:41 2022
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
ADR base = "/ORA19/app/oracle"
adrci> show base
ADR base is "/ORA19/app/oracle"
adrci> show home
ADR Homes:
diag/rdbms/ora19c/ora19c
diag/rdbms/ljdw_pitr_ora19c/ljDw
diag/rdbms/ncvg_pitr_ora19c/ncvg
diag/rdbms/oradb/oradb
diag/rdbms/asmora/asmora
diag/clients/user_oracle/host_4180807924_110
diag/tnslsnr/svr1/listener
현재 설정된 파일 삭제 정책 확인
일반 trc 파일과 dump 파일들은 short
인시던트와 alert log는 long
adrci> show control
DIA-48448: This command does not support multiple ADR homes
adrci> set home diag/rdbms/oradb/oradb
adrci> show home
ADR Homes:
diag/rdbms/oradb/oradb
adrci> show control
ADR Home = /ORA19/app/oracle/diag/rdbms/oradb/oradb:
*************************************************************************
ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME SIZEP_POLICY PURGE_PERIOD FLAGS PURGE_THRESHOLD
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------- -------------------- -------------------- -------------------- --------------------
814298798 720 8760 2021-12-14 17:30:55.006843 +09:00 1 2 110 1 2021-12-14 17:30:55.006843 +09:00 18446744073709551615 0 0 95
1 row fetched
또는
adrci> select SHORTP_POLICY,LONGP_POLICY from ADR_CONTROL;
ADR Home = /ORA19/app/oracle/diag/rdbms/ora19c/ora19c:
*************************************************************************
SHORTP_POLICY LONGP_POLICY
-------------------- --------------------
720 8760
1 row fetched
파일 삭제 정책 변경
ADRCI> set control(SHORTP_POLICY = 240) <-- 10 days
ADRCI> set control(LONGP_POLICY = 2160) <-- 3 months
adrci> show control
ADR Home = /ORA19/app/oracle/diag/rdbms/oradb/oradb:
*************************************************************************
ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME SIZEP_POLICY PURGE_PERIOD FLAGS PURGE_THRESHOLD
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------- -------------------- -------------------- -------------------- --------------------
814298798 720 8760 2021-12-14 17:30:55.006843 +09:00 1 2 110 1 2021-12-14 17:30:55.006843 +09:00 18446744073709551615 0 0 95
1 row fetched
adrci> set control(SHORTP_POLICY = 240)
adrci> show control
ADR Home = /ORA19/app/oracle/diag/rdbms/oradb/oradb:
*************************************************************************
ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME SIZEP_POLICY PURGE_PERIOD FLAGS PURGE_THRESHOLD
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------- -------------------- -------------------- -------------------- --------------------
814298798 240 8760 2022-06-17 11:16:11.946749 +09:00 1 2 110 1 2021-12-14 17:30:55.006843 +09:00 18446744073709551615 0 0 95
1 row fetched
adrci> set control(LONGP_POLICY = 2160)
adrci> show control
ADR Home = /ORA19/app/oracle/diag/rdbms/oradb/oradb:
*************************************************************************
ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME SIZEP_POLICY PURGE_PERIOD FLAGS PURGE_THRESHOLD
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------- -------------------- -------------------- -------------------- --------------------
814298798 240 2160 2022-06-17 11:17:10.518321 +09:00 1 2 110 1 2021-12-14 17:30:55.006843 +09:00 18446744073709551615 0 0 95
1 row fetched
alert log 확인(파일 오픈 후 vi 명령어로 조작 가능)
adrci> show alert
Choose the home from which to view the alert log:
1: diag/rdbms/ora19c/ora19c
2: diag/rdbms/ljdw_pitr_ora19c/ljDw
3: diag/rdbms/ncvg_pitr_ora19c/ncvg
4: diag/rdbms/oradb/oradb
5: diag/rdbms/asmora/asmora
6: diag/clients/user_oracle/host_4180807924_110
7: diag/tnslsnr/svr1/listener
Q: to quit
Please select option: 1
Output the results to file: /tmp/alert_12186_1398_ora19c_1.ado
2020-07-11 22:39:37.465000 +09:00
Starting ORACLE instance (normal) (OS id: 3736)
****************************************************
/dev/shm will be used for creating SGA
Large pages will not be used. Only standard 4K pages will be used
****************************************************
**********************************************************************
Dump of system resources acquired for SHARED GLOBAL AREA (SGA)
Per process system memlock (soft) limit = 128G
Expected per process system memlock (soft) limit to lock
instance MAX SHARED GLOBAL AREA (SGA) into memory: 1024M
Available system pagesizes:
...
...
특정 메세지 로그 조회
adrci> show alert -p "message_text like '%incident%'"
Choose the home from which to view the alert log:
1: diag/rdbms/ora19c/ora19c
2: diag/rdbms/ljdw_pitr_ora19c/ljDw
3: diag/rdbms/ncvg_pitr_ora19c/ncvg
4: diag/rdbms/oradb/oradb
5: diag/rdbms/asmora/asmora
6: diag/clients/user_oracle/host_4180807924_110
7: diag/tnslsnr/svr1/listener
Q: to quit
Please select option: 1
Output the results to file: /tmp/alert_12186_1398_ora19c_5.ado
2022-02-03 19:29:10.189000 +09:00
Errors in file /ORA19/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_vktm_12290.trc (incident=92201):
ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], []
Incident details in: /ORA19/app/oracle/diag/rdbms/ora19c/ora19c/incident/incdir_92201/ora19c_vktm_12290_i92201.trc
2022-03-17 20:32:47.533000 +09:00
Errors in file /ORA19/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_ora_32045.trc (incident=104946):
ORA-00603: ORACLE server session terminated by fatal error
ORA-00020: maximum number of processes () exceeded
Incident details in: /ORA19/app/oracle/diag/rdbms/ora19c/ora19c/incident/incdir_104946/ora19c_ora_32045_i104946.trc
...
...반응형
incident 로그 확인
adrci> show incident
ADR Home = /ORA19/app/oracle/diag/rdbms/ora19c/ora19c:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
92201 ORA 800 2022-02-03 19:29:10.189000 +09:00
104946 ORA 603 2022-03-17 20:32:47.533000 +09:00
ADR Home = /ORA19/app/oracle/diag/rdbms/ljdw_pitr_ora19c/ljDw:
*************************************************************************
0 rows fetched
ADR Home = /ORA19/app/oracle/diag/rdbms/ncvg_pitr_ora19c/ncvg:
*************************************************************************
0 rows fetched
ADR Home = /ORA19/app/oracle/diag/rdbms/oradb/oradb:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
201 ORA 603 2021-12-14 18:15:24.004000 +09:00
ADR Home = /ORA19/app/oracle/diag/rdbms/asmora/asmora:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
9641 ORA 800 2022-01-30 14:31:56.304000 +09:00
12042 ORA 800 2022-01-30 19:51:09.174000 +09:00
14443 ORA 800 2022-01-30 19:57:21.091000 +09:00
16844 ORA 800 2022-01-30 20:14:05.154000 +09:00
19245 ORA 800 2022-01-30 20:28:10.791000 +09:00
21646 ORA 800 2022-01-30 20:34:59.263000 +09:00
21862 ORA 15064 2022-02-05 11:17:10.949000 +09:00
24047 ORA 800 2022-02-05 11:17:26.645000 +09:00
24255 ORA 15064 2022-02-05 13:55:01.714000 +09:00
26448 ORA 800 2022-02-05 13:55:17.219000 +09:00
29065 ORA 15064 2022-02-13 14:41:17.992000 +09:00
38665 ORA 15064 2022-02-28 20:44:02.653000 +09:00
41065 ORA 15064 2022-03-02 09:29:09.236000 +09:00
48265 ORA 15064 2022-03-09 14:24:27.284000 +09:00
50665 ORA 15064 2022-03-09 22:30:22.062000 +09:00
55465 ORA 15064 2022-03-14 18:36:09.915000 +09:00
60257 ORA 15064 2022-03-20 15:14:55.931000 +09:00
62657 ORA 15064 2022-03-22 21:10:50.859000 +09:00
72257 ORA 15064 2022-04-09 12:21:14.385000 +09:00
76873 ORA 15064 2022-04-13 09:00:52.292000 +09:00
110665 ORA 15064 2022-06-02 19:28:13.776000 +09:00
113057 ORA 15064 2022-06-07 22:27:21.801000 +09:00
115457 ORA 15064 2022-06-08 14:05:35.645000 +09:00
122657 ORA 15064 2022-06-09 15:29:51.399000 +09:00
127465 ORA 15064 2022-06-15 15:44:39.197000 +09:00
129865 ORA 15064 2022-06-17 09:35:31.364000 +09:00
ADR Home = /ORA19/app/oracle/diag/clients/user_oracle/host_4180807924_110:
*************************************************************************
0 rows fetched
ADR Home = /ORA19/app/oracle/diag/tnslsnr/svr1/listener:
*************************************************************************
0 rows fetched
incident detail 확인 (mode = basic, brief, detail)
########### detail ##########
adrci> show incident -mode detail -p "incident_id=129865"
ADR Home = /ORA19/app/oracle/diag/rdbms/ora19c/ora19c:
*************************************************************************
0 rows fetched
<INCIDENT_INFO mode="detail">
<ADR_HOME name="/ORA19/app/oracle/diag/rdbms/ora19c/ora19c">
ADR Home = /ORA19/app/oracle/diag/rdbms/ljdw_pitr_ora19c/ljDw:
*************************************************************************
0 rows fetched
</ADR_HOME>
<ADR_HOME name="/ORA19/app/oracle/diag/rdbms/ljdw_pitr_ora19c/ljDw">
ADR Home = /ORA19/app/oracle/diag/rdbms/ncvg_pitr_ora19c/ncvg:
*************************************************************************
0 rows fetched
</ADR_HOME>
<ADR_HOME name="/ORA19/app/oracle/diag/rdbms/ncvg_pitr_ora19c/ncvg">
ADR Home = /ORA19/app/oracle/diag/rdbms/oradb/oradb:
*************************************************************************
0 rows fetched
</ADR_HOME>
<ADR_HOME name="/ORA19/app/oracle/diag/rdbms/oradb/oradb">
ADR Home = /ORA19/app/oracle/diag/rdbms/asmora/asmora:
*************************************************************************
**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
INCIDENT_ID 129865
STATUS ready
CREATE_TIME 2022-06-17 09:35:31.364000 +09:00
PROBLEM_ID 2
CLOSE_TIME <NULL>
FLOOD_CONTROLLED none
ERROR_FACILITY ORA
ERROR_NUMBER 15064
ERROR_ARG1 <NULL>
ERROR_ARG2 <NULL>
ERROR_ARG3 <NULL>
ERROR_ARG4 <NULL>
ERROR_ARG5 <NULL>
ERROR_ARG6 <NULL>
ERROR_ARG7 <NULL>
ERROR_ARG8 <NULL>
ERROR_ARG9 <NULL>
ERROR_ARG10 <NULL>
ERROR_ARG11 <NULL>
ERROR_ARG12 <NULL>
SIGNALLING_COMPONENT background_proc
SIGNALLING_SUBCOMPONENT <NULL>
SUSPECT_COMPONENT <NULL>
SUSPECT_SUBCOMPONENT <NULL>
ECID <NULL>
IMPACTS 0
CON_UID 0
PROBLEM_KEY ORA 15064
FIRST_INCIDENT 21862
FIRSTINC_TIME 2022-02-05 11:17:10.949000 +09:00
LAST_INCIDENT 129865
LASTINC_TIME 2022-06-17 09:35:31.364000 +09:00
IMPACT1 0
IMPACT2 0
IMPACT3 0
IMPACT4 0
KEY_NAME ProcId
KEY_VALUE 32.1
KEY_NAME Service
KEY_VALUE SYS$BACKGROUND
KEY_NAME Client ProcId
KEY_VALUE oracle@svr1 (TNS V1-V3).20766_139636201874368
KEY_NAME SID
KEY_VALUE 18.12194
OWNER_ID 1
INCIDENT_FILE /ORA19/app/oracle/diag/rdbms/asmora/asmora/incident/incdir_129865/asmora_asmb_20766_i129865.trc
OWNER_ID 1
INCIDENT_FILE /ORA19/app/oracle/diag/rdbms/asmora/asmora/trace/asmora_asmb_20766.trc
ADR Home = /ORA19/app/oracle/diag/clients/user_oracle/host_4180807924_110:
*************************************************************************
0 rows fetched
</ADR_HOME>
<ADR_HOME name="/ORA19/app/oracle/diag/clients/user_oracle/host_4180807924_110">
ADR Home = /ORA19/app/oracle/diag/tnslsnr/svr1/listener:
*************************************************************************
0 rows fetched
'DBMS > ORACLE' 카테고리의 다른 글
| listener.ora / sqlnet.ora / tnsnames.ora 차이점 (0) | 2022.06.20 |
|---|---|
| 패스워드 특수문자 포함 sqlplus 접속 방법 (0) | 2022.06.17 |
| AFD disk 추가/삭제 (0) | 2022.06.09 |
| ASMLIB to AFD(ASM Filter Driver) 마이그레이션 (ASM SINGLE) (0) | 2022.06.08 |
| RMAN RAC(ASM) to SINGLE(file system) DB_NAME 변경하여 RESTORE (0) | 2022.05.27 |
댓글