내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
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/545https://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 |