OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 ADRCI 정의 및 사용법
ADR(Automatic Diagnostic Repository) 란?
11g NF로 RDBMS 진단을 위한 파일 기반 저장소임
ADR 은 Trace file, Alertlog, Incident dump, Core file 등을 찾을 수있는 중앙 집중식 디렉토리 구조로 되어있음
데이터베이스, ASM (Automatic Storage Management), CRS (Cluster Ready Services) 및 기타 Oracle 제품 또는 구성 요소는 모든 진단 데이터를 ADR에 저장함
각 제품의 각 인스턴스는 자체 ADR 홈 디렉토리 아래에 진단 데이터를 저장함
ADR BASE 의 DEFAULT 경로는 ORACLE_BASE 경로임
또는 오라클 내에서 diagnostic_dest 파라미터 경로임
$ echo $ORACLE_BASE
/oracle/app/oracle
SQL> show parameter diagnostic_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest string /oracle/app/oracle
v$diag_info 뷰 조회시 상기 설명의 각 ADR 디렉토리 경로를 알 수 있으며
Default 세션 Trace와 Problem 수, Incident 수를 확인할 수 있음
SQL>
set lines 200
set pages 1000
col name for a30
col value for a80
select * from v$diag_info;
INST_ID NAME VALUE CON_ID
---------- ------------------------------ -------------------------------------------------------------------------------- ----------
1 Diag Enabled TRUE 0
1 ADR Base /oracle/app/oracle 0
1 ADR Home /oracle/app/oracle/diag/rdbms/orcl19/ORCL19 0
1 Diag Trace /oracle/app/oracle/diag/rdbms/orcl19/ORCL19/trace 0
1 Diag Alert /oracle/app/oracle/diag/rdbms/orcl19/ORCL19/alert 0
1 Diag Incident /oracle/app/oracle/diag/rdbms/orcl19/ORCL19/incident 0
1 Diag Cdump /oracle/app/oracle/diag/rdbms/orcl19/ORCL19/cdump 0
1 Health Monitor /oracle/app/oracle/diag/rdbms/orcl19/ORCL19/hm 0
1 Default Trace File /oracle/app/oracle/diag/rdbms/orcl19/ORCL19/trace/ORCL19_ora_16926.trc 0
1 Active Problem Count 1 0
1 Active Incident Count 1 0
1 ORACLE_HOME /oracle/app/oracle/product/19.0.0/dbhome_1 0
12 rows selected.
ADR HOME 아래에 존재하는 디렉토리
Alert : xml 포멧의 alert log이 저장됨
Cdump : core dump가 저장됨
Trace : 사용자 trace 파일 및 Alert.log 가 저장됨 'Background_dump_dest' 및 'user_dump_dest'는 11g부터 무시됨
11g 이후 모든 trace 파일이 'trace' 디렉토리에 생성됨
Incident : 인시던트 디렉토리는 심각한 오류가 발생했을 때 작성된 덤프 파일을 저장함
중대한 오류 (사건)가 발생할 때마다 디렉토리 이름을 구성하는 데 사용되는 사건 ID와 함께 고유 한 사건 디렉토리가 제공됨
Metadata : alert, incident, pakage 등에 대한 진단 메타 데이터 파일을 저장됨
HM : Health Monitor 가 여러 컴포넌트를 검사한 후 일부 파일을 이 곳에 저장됨
Incpkg, ir, lck, sweep : incident 를 패키징할 때 특정 지원 파일들이 이 곳에 저장됨
Problem - Database 의 Critical 에러 ( 예 : ORA-00600, ORA-07445, ORA-04031 등 )
Incident - 각 사건 숫자 ID 를 내포하고 있는 문제의 단일 occurrence
(Problem 1 회 발생을 의미하며 ORA-00600 에러가 3 번 발생할 경우 ORA-0600 Problem 이 3 번의 Incident 가 기록되었다고 할 수 있음)
ADRCI 사용법
ADRCI 실행
oracle 계정에서 adrci 라고 입력하면 실행됨
$ adrci
ADRCI: Release 19.0.0.0.0 - Production on Sun Mar 29 22:10:46 2020
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
ADR base = "/oracle/app/oracle"
Problem 확인
adrci> show problem
ADR Home = /oracle/app/oracle/diag/rdbms/orcl19/ORCL19:
*************************************************************************
PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
1 ORA 63999 9769 2019-11-21 15:05:28.608000 +09:00
ADR Home = /oracle/app/oracle/diag/rdbms/orcl19t/ORCL19T:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/asm/+asm/+ASM:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/crs/oracle19/crs:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/clients/user_oracle/host_3087630407_110:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/tnslsnr/ORACLE19/listener:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/asmtool/user_oracle/host_3087630407_110:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/asmcmd/user_oracle/ORACLE19:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/kfod/oracle19/kfod:
*************************************************************************
0 rows fetched
Incident 확인
adrci> show incident
ADR Home = /oracle/app/oracle/diag/rdbms/orcl19/ORCL19:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
9769 ORA 63999 2019-11-21 15:05:28.608000 +09:00
ADR Home = /oracle/app/oracle/diag/rdbms/orcl19t/ORCL19T:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/asm/+asm/+ASM:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/crs/oracle19/crs:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/clients/user_oracle/host_3087630407_110:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/tnslsnr/ORACLE19/listener:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/asmtool/user_oracle/host_3087630407_110:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/asmcmd/user_oracle/ORACLE19:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/kfod/oracle19/kfod:
*************************************************************************
0 rows fetched
20200816 추가
incident 확인
adrci> show incident
ADR Home = /oracle/app/oracle/diag/rdbms/orcl11/ORCL11:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
115385 ORA 600 [kzsviver:1] 2020-07-30 08:19:21.462000 +09:00
1 rows fetched
20200816 추가
incident detail 확인
adrci> show incident -mode detail -p "incident_id=115385"
ADR Home = /oracle/app/oracle/diag/rdbms/orcl11/ORCL11:
*************************************************************************
**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
INCIDENT_ID 115385
STATUS ready
CREATE_TIME 2020-07-30 08:19:21.462000 +09:00
PROBLEM_ID 1
CLOSE_TIME <NULL>
FLOOD_CONTROLLED none
ERROR_FACILITY ORA
ERROR_NUMBER 600
ERROR_ARG1 kzsviver:1
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 <NULL>
SIGNALLING_SUBCOMPONENT <NULL>
SUSPECT_COMPONENT <NULL>
SUSPECT_SUBCOMPONENT <NULL>
ECID <NULL>
IMPACTS 0
PROBLEM_KEY ORA 600 [kzsviver:1]
FIRST_INCIDENT 115385
FIRSTINC_TIME 2020-07-30 08:19:21.462000 +09:00
LAST_INCIDENT 115385
LASTINC_TIME 2020-07-30 08:19:21.462000 +09:00
IMPACT1 0
IMPACT2 0
IMPACT3 0
IMPACT4 0
KEY_NAME ProcId
KEY_VALUE 23.57
KEY_NAME Client ProcId
KEY_VALUE oracle@ORACLE11 (TNS V1-V3).16056_140250745501440
KEY_NAME SID
KEY_VALUE 35.1257
OWNER_ID 1
INCIDENT_FILE /oracle/app/oracle/diag/rdbms/orcl11/ORCL11/trace/ORCL11_ora_16056.trc
OWNER_ID 1
INCIDENT_FILE /oracle/app/oracle/diag/rdbms/orcl11/ORCL11/incident/incdir_115385/ORCL11_ora_16056_i115385.trc
1 rows fetched
adr 홈경로 확인
adrci> show homes
ADR Homes:
diag/rdbms/orcl19/ORCL19
diag/rdbms/orcl19t/ORCL19T
diag/asm/+asm/+ASM
diag/crs/oracle19/crs
diag/clients/user_oracle/host_3087630407_110
diag/tnslsnr/ORACLE19/listener
diag/asmtool/user_oracle/host_3087630407_110
diag/asmcmd/user_oracle/ORACLE19
diag/kfod/oracle19/kfod
alert log 확인
adrci> show alert
Choose the home from which to view the alert log:
1: diag/rdbms/orcl19/ORCL19
2: diag/rdbms/orcl19t/ORCL19T
3: diag/asm/+asm/+ASM
4: diag/crs/oracle19/crs
5: diag/clients/user_oracle/host_3087630407_110
6: diag/tnslsnr/ORACLE19/listener
7: diag/asmtool/user_oracle/host_3087630407_110
8: diag/asmcmd/user_oracle/ORACLE19
9: diag/kfod/oracle19/kfod
Q: to quit
오라클 db alert 확인 1 입력
Please select option: 1
2020-02-24 22:00:48.054000 +09:00
Thread 1 advanced to log sequence 70 (LGWR switch)
Current log# 1 seq# 70 mem# 0: +DATA/ORCL19/ONLINELOG/group_1.257.1022509761
2020-02-25 02:00:00.172000 +09:00
Closing scheduler window
Closing Resource Manager plan via scheduler window
Clearing Resource Manager plan via parameter
.
.
리스너 로그 확인
adrci> show alert
Choose the home from which to view the alert log:
1: diag/rdbms/orcl19/ORCL19
2: diag/rdbms/orcl19t/ORCL19T
3: diag/asm/+asm/+ASM
4: diag/crs/oracle19/crs
5: diag/clients/user_oracle/host_3087630407_110
6: diag/tnslsnr/ORACLE19/listener
7: diag/asmtool/user_oracle/host_3087630407_110
8: diag/asmcmd/user_oracle/ORACLE19
9: diag/kfod/oracle19/kfod
Q: to quit
오라클 리스너 로그 확인 6 입력
Please select option: 6
2020-02-02 17:41:38.694000 +09:00
02-FEB-2020 17:41:38 * service_update * +ASM * 0
2020-02-02 17:41:53.709000 +09:00
02-FEB-2020 17:41:53 * service_update * +ASM * 0
2020-02-02 17:42:08.726000 +09:00
02-FEB-2020 17:42:08 * service_update * +ASM * 0
2020-02-02 17:42:10.946000 +09:00
02-FEB-2020 17:42:10 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=ORACLE19)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=318767104)) * status * 0
.
.
show alert 에서 나가기
adrci> show alert
Choose the home from which to view the alert log:
1: diag/rdbms/orcl19/ORCL19
2: diag/rdbms/orcl19t/ORCL19T
3: diag/asm/+asm/+ASM
4: diag/crs/oracle19/crs
5: diag/clients/user_oracle/host_3087630407_110
6: diag/tnslsnr/ORACLE19/listener
7: diag/asmtool/user_oracle/host_3087630407_110
8: diag/asmcmd/user_oracle/ORACLE19
9: diag/kfod/oracle19/kfod
Q: to quit
나가기 q 입력
Please select option: q
adrci>
도움말 확인
adrci> help
HELP [topic]
Available Topics:
CREATE REPORT
ECHO
ESTIMATE
EXIT
HELP
HOST
IPS
PURGE
RUN
SELECT
SET BASE
SET BROWSER
SET CONTROL
SET ECHO
SET EDITOR
SET HOMES | HOME | HOMEPATH
SET TERMOUT
SHOW ALERT
SHOW BASE
SHOW CONTROL
SHOW HM_RUN
SHOW HOMES | HOME | HOMEPATH
SHOW INCDIR
SHOW INCIDENT
SHOW LOG
SHOW PROBLEM
SHOW REPORT
SHOW TRACEFILE
SPOOL
There are other commands intended to be used directly by Oracle, type
"HELP EXTENDED" to see the list
로그 정리 purge -age 의 단위는 분(min)임
아래 명령은 60 * 24 = 1440 하루치 로그만 남기고 모두 삭제하는 명령
adrci> purge -age 1440 -type INCIDENT
adrci> purge -age 1440 -type ALERT
adrci> purge -age 1440 -type TRACE
adrci> purge -age 1440 -type CDUMP
adrci> purge -age 1440 -type HM
ADRCI 를 이용한 자동 로그 정리
로그 정리 스크립트
43200분(30일) 치 리스너, db alert, trace 로그를 남기고
$ cat adrci.sh
#!/bin/ksh
# Usage: PURGE [-i {<id1> | <id1> <id2>} ] |
# [-problem {<id1> | <id1> <id2>} ] |
# [[-age <mins>] |
# [-size <bytes>] |
# [-type {ALERT|INCIDENT|TRACE|CDUMP|HM|UTSCDMP|LOG} ]]
#
# Purpose: Purge the diagnostic data in the current ADR home. If no
# option is specified, the default purging policy will be used.
#
# Options:
# [-i id1 | id1 id2]: Purge a single specified incident, or a range
# of incidents.
#
# [-problem id1 | id1 id2]: Purge a single specified problem, or a range
# of problems.
#
# [-age <mins>]: Purge diagnostic data older than <mins> from the
# ADR home, if the data is purgable.
#
# [-size <bytes>]: Purge diagnostic data from the ADR home until the size
# of the home reaches <bytes> bytes.
#
# [-type ALERT|INCIDENT|TRACE|CDUMP|HM|UTSCDMP|LOG]: Purge a specific
# type of data.
#
# Notes:
# When purging by size, only INCIDENT, TRACE, CDUMP and UTSCDMP data
# is considered.
#
# Some data can not be purged (such as incidents in the 'tracked' state),
# which means that the specified target size may not be reached in all cases.
#
# Examples:
# purge
# purge -i 123 456
# purge -age 60 -type incident
# purge -size 10000000
# LISTENER
for i in `adrci exec="show homes"|grep listener`;do
echo "adrci exec=\"set home $i;\""
adrci exec="set home $i;purge -age 43200";
echo "complete adrci purge -age 43200(30day) listener;\""
done
echo ""
# ADR HOME
#ADR_HOME=`adrci exec="show home " | grep rdbms`
ADR_HOME="diag/rdbms/orcl19/ORCL19"
echo "adrci exec=\"set home $ADR_HOME;\""
# HOME Check
# echo $ADR_HOME
adrci exec="set home $ADR_HOME;purge -age 43200 -type ALERT"
echo "complete adrci purge -age 43200(30day) ALERT;\""
adrci exec="set home $ADR_HOME;purge -age 43200 -type TRACE"
echo "complete adrci purge -age 43200(30day) TRACE;\""
스크립트 실행
$ sh adrci.sh
adrci exec="set home diag/tnslsnr/ORACLE19/listener;"
complete adrci purge -age 43200(30day) listener;"
adrci exec="set home diag/rdbms/orcl19/ORCL19;"
complete adrci purge -age 43200(30day) ALERT;"
complete adrci purge -age 43200(30day) TRACE;"
'ORACLE > ADMIN' 카테고리의 다른 글
| LISTENER.ORA, TNSNAMES.ORA 생성하기 (0) | 2024.03.08 |
|---|---|
| Oracle 12C RAC DB 운영 매뉴얼 (1) | 2024.02.29 |
| 오라클 스케줄러(Scheduler) (0) | 2021.01.15 |
| shrink/Reorg 대상 찾기 (dbms_space ) (0) | 2020.12.30 |
| 파티션 Table 전환 (dbms_redefinition / Mview) (0) | 2019.11.20 |