[Oracle] ADRCI

728x90

ADR

ADR(Automatic Diagnostic Repository)

11g NEW Feature로 RDBMS 진달을 위한 파일 기반 저장소이다.

ADR은 Trace FIle, alert log, incident dump, core file 등을 찾을 수 있는 중앙 집중식 디렉토리 구조로 되어있다.

데이터베이스, ASM(Automatic Storage Management), CRS(Cluter Ready Service) 및 기타 Oracle 제품 또는 구성 요소는 모든 진단 데이터를 ADR에 저장한다.

각 제품의 각 인스턴스는 자체 ADR 홈 디렉토리 아래에 진단 데이터를 저장함

ADR BASE의 Default 경로는 ORACLE_BASE 경로이다.

또는 오라클 내에서 diagnostic_dest 파라미터 경로임

show parameter diagnostic_dest 

select * from v$parameter where name = 'diagnostic_dest';

v$diag_info 뷰 조회 시 각 ADR 디렉토리 경로를 알 수 있다.

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
 
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 확인

인시던트 디렉토리는 심각한 오류가 발생했을 때 작성된 덤프 파일을 저장함

중대한 오류 (사건)가 발생할 때마다 디렉토리 이름을 구성하는 데 사용되는 사건 ID와 함께 고유 한 사건 디렉토리가 제공됨

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

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

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

SR을 올릴때 사용하는 incident packaging

adrci> show homepath
ADR Homes: 
diag/rdbms/test/TEST01
diag/clients/user_oracle/host_2085451943_11
diag/clients/user_precise/host_2085451943_11
diag/clients/user_norad/host_2085451943_11
diag/tnslsnr/TEST01/test_TEST01
diag/tnslsnr/TEST01/listener_TEST01
diag/tnslsnr/TEST01/listener_TEST01
diag/tnslsnr/TEST01/listener

adrci> set homepath diag/rdbms/test/TEST01
adrci> ips create package incident 656910
Created package 1 based on incident id 656910, correlation level typical
adrci>
adrci> IPS GENERATE PACKAGE 1 in /oracle/diag
Generated package 1 in file /oracle/diag/ORA600critical problem임_20100308163713_COM_1.zip, mode complete

adrci> exit
$ls -altr
total 12744
-rw-r--r--   1 oracle     dba        6456404 Mar  8 16:39 ORA600  critical problem임_20100308163713_COM_1.zip

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

adr 홈 경로 추가

adrci> set homepath diag/rdbms/orclbi/orclbi2

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

trace 파일 default retention( 보관주기) 확인 및 설정

adcri> show homes 
adcri> set home /diag/rdbms/testdb/TESTDB 
adrci> show control

ADR Home = /u01/app/oracle/diag/rdbms/testdb/TESTDB:
*************************************************************************
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      
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- 
2356560798           720                  8760                 2020-10-08 14:01:43.732072 +09:00        2024-09-02 06:22:40.966073 +09:00                                                 1                    2                    110                  1                    2020-10-08 14:01:43.732072 +09:00        18446744073709551615 0                    0                    95                  
1 row fetched

LONGP_POLICY(long term) -- set to 365 days(in hours, 365*24=8760) by default
SHORTP_POLICY(short term) -- set to 30 days(in hours, 30*24=720) by default

설정되어 있는 retention policies (optional) 변경

ADRCI> set control(SHORTP_POLICY = 240)   <-- 10 days
ADRCI> set control(LONGP_POLICY = 1095)   <-- 1.5 months

로그 정리 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 로그를 남기고 30일 이전로그는 삭제하는 스크립트

$ 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;"

참고

https://positivemh.tistory.com/544

https://docs.oracle.com/database/121/SUTIL/GUID-4805C1CD-5F22-417D-B824-AF141F7B8DE5.htm

728x90

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

[Oracle] 10046 event Trace 분석 (3)	2024.09.06
[Oracle]Partition 작업 시 SQL 커서 및 실행 계획 변경 (0)	2024.08.23
[Oracle] OCR VIGING DISK 재구성 (0)	2024.08.14
[Oracle]GPnP(grid plug n play) (0)	2024.08.14
[Oracle] PASSWORD_VERIFY_FUNCTION (4)	2024.08.13

기록지

[Oracle] ADRCI

ADR

ADRCI 사용법

Incident 확인

Incident Detail 확인

SR을 올릴때 사용하는 incident packaging

adr 홈경로 확인

adr 홈 경로 추가

alert log 확인

리스너 로그 확인

show alert 에서 나가기

도움말 확인

trace 파일 default retention( 보관주기) 확인 및 설정

설정되어 있는 retention policies (optional) 변경

ADRCI를 이용한 자동 로그 정리

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

티스토리툴바

« 2024/11 »
일	월	화	수	목	금	토
					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

[Oracle] ADRCI

ADR

ADRCI 사용법

Incident 확인

Incident Detail 확인

SR을 올릴때 사용하는 incident packaging

adr 홈경로 확인

adr 홈 경로 추가

alert log 확인

리스너 로그 확인

show alert 에서 나가기

도움말 확인

trace 파일 default retention( 보관주기) 확인 및 설정

설정되어 있는 retention policies (optional) 변경

ADRCI를 이용한 자동 로그 정리

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

'Oracle/Admin' Related Articles

티스토리툴바