오라클 ADRCI 정의 및 사용법

2020. 3. 29. 22:21 내맘대로긍정

프린트 하기

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 파라미터 경로임

1
2
3
4
5
6
7
8
$ 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 수를 확인할 수 있음

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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 라고 입력하면 실행됨

1
2
3
4
5
6
7
$ 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 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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 확인

1
2
3
4
5
6
7
8
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 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
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 홈경로 확인

1
2
3
4
5
6
7
8
9
10
11
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 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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
.
.

리스너 로그 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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 에서 나가기

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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> 

도움말 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
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 하루치 로그만 남기고 모두 삭제하는 명령

1
2
3
4
5
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 로그를 남기고 30일 이전로그는 삭제하는 스크립트

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
$ 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;\""

스크립트 실행

1
2
3
4
5
6
7
$ 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;"

참조 :

https://blog.goodusdata.com/157

1174105.1

422893.1

https://positivemh.tistory.com/545

https://positivemh.tistory.com/546

https://positivemh.tistory.com/172

https://oracle-base.com/articles/11g/automatic-diagnostics-repository-11gr1

저작자표시

'ORACLE > Admin' 카테고리의 다른 글

오라클 설치시 SElinux 와 방화벽의 영향도 (1)	2020.04.23
오라클 alert 로그 정리 방법 (0)	2020.04.07
오라클 18c fra 영역 설정하기 (0)	2020.03.23
오라클 유저에게 기본적으로 주는 롤 connect와 resource 에 포함된 권한 확인 (0)	2020.02.28
오라클 redo log switch 와 first_change#, resetlogs_change# (0)	2020.02.28

내맘대로긍정

박영민 OracleDBA@kakao.com

Category

Menu

Comment

Link