내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS환경 : Oracle Linux 6.8 (64bit)
DB 환경 : Oracle Database 11.2.0.4
방법 : 오라클 11g R2 ADRCI 를 이용한 IPS(Incident Packaging Service) 가이드
IPS(Incident Packaging Service) 란?
ORACLE 11g 이상의 버전에서는 problem (Database 에서 발생한 에러코드)과 incident (에러가 발생한 기록)에 관련된 trace file 들을 자동으로 수집해주는 기능을 제공함
이 기능을 IPS (Incident Packaging Service) 라고 하며, 인터페이스로 GUI 환경과 ADRCI command 를 제공함
Database 에 발생한 모든 심각한 에러들은 각각의 incident 를 생성함
IPS 를 통해서 생성된 압축 파일은 에러에 대한 alert.log file, 모든 trace file 과 진단 정보를 포함하고 있기 때문에,
해당 error 에 대한 정보수집을 간편히 수행할 수 있음
용어 설명
Problem - Database 의 Critical 에러 ( 예 : ORA-00600, ORA-07445, ORA-04031 등 )
Incident - 각 사건 숫자 ID 를 내포하고 있는 문제의 단일 occurrence
(Problem 1 회 발생을 의미하며 ORA-00600 에러가 3 번 발생할 경우 ORA-0600 Problem 이 3 번의 Incident 가 기록되었다고 할 수 있음)
사용법
adrci 실행 후 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 | $ adrci ADRCI: Release 11.2.0.4.0 - Production on Mon Aug 17 08:00:29 2020 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. ADR base = "/oracle/app/oracle" adrci> show problem ADR Home = /oracle/app/oracle/diag/rdbms/orcl11/ORCL11: ************************************************************************* PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME -------------------- ----------------------------------------------------------- -------------------- ---------------------------------------- 1 ORA 600 [kzsviver:1] 115385 2020-07-30 08:19:21.462000 +09:00 ADR Home = /oracle/app/oracle/diag/tnslsnr/ORACLE11/listener: ************************************************************************* 0 rows fetched ADR Home = /oracle/app/oracle/diag/tnslsnr/ORACLE11/listener1527: ************************************************************************* 0 rows fetched ADR Home = /oracle/app/oracle/diag/tnslsnr/ORACLE11/listener1523: ************************************************************************* 0 rows fetched ADR Home = /oracle/app/oracle/diag/clients/user_oracle/host_3827751472_80: ************************************************************************* PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME -------------------- ----------------------------------------------------------- -------------------- ---------------------------------------- 1 oci 10847 1 2020-08-12 21:03:29.737000 +09:00 1 rows fetched |
adrci 실행 후 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 | $ adrci ADRCI: Release 11.2.0.4.0 - Production on Mon Aug 17 07:45:23 2020 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. ADR base = "/oracle/app/oracle" 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 ADR Home = /oracle/app/oracle/diag/tnslsnr/ORACLE11/listener: ************************************************************************* 0 rows fetched ADR Home = /oracle/app/oracle/diag/tnslsnr/ORACLE11/listener1527: ************************************************************************* 0 rows fetched ADR Home = /oracle/app/oracle/diag/tnslsnr/ORACLE11/listener1523: ************************************************************************* 0 rows fetched ADR Home = /oracle/app/oracle/diag/clients/user_oracle/host_3827751472_80: ************************************************************************* INCIDENT_ID PROBLEM_KEY CREATE_TIME -------------------- ----------------------------------------------------------- ---------------------------------------- 1 oci 10847 2020-08-12 21:03:29.737000 +09:00 1 rows fetched |
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 |
incident ips pack 실행
incident id 115385 에 관련된 trace file 들을 /tmp directory 에 압축파일로 생성함
1 2 | adrci> ips pack incident 115385 in /tmp Generated package 3 in file /tmp/ORA600kzs_20200817075240_COM_1.zip, mode complete |
tmp 경로에 incident 메세지의 정보를 압축해줌
압축파일 확인
1 2 3 4 5 6 7 8 9 | $ cd /tmp $ ls -ltr total 1068 -rw-r--r-- 1 oracle dba 1051200 Aug 17 07:52 ORA600kzs_20200817075240_COM_1.zip $ unzip ORA600kzs_20200817075240_COM_1.zip $ ls -ltr total 1068 -rw-r--r-- 1 oracle dba 1051200 Aug 17 07:52 ORA600kzs_20200817075240_COM_1.zip drwxr-xr-x 3 oracle dba 4096 Aug 17 07:54 diag |
파일 상세 확인
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 60 61 62 63 64 65 66 67 68 69 | $ cd diag/rdbms/orcl11/ORCL11/ $ ls -ltr total 20 drwxr-xr-x 3 oracle dba 4096 Aug 17 07:54 incpkg drwxr-xr-x 3 oracle dba 4096 Aug 17 07:54 incident drwxr-xr-x 2 oracle dba 4096 Aug 17 07:54 alert drwxr-xr-x 2 oracle dba 4096 Aug 17 07:54 trace drwxr-xr-x 2 oracle dba 4096 Aug 17 07:54 hm $ find . . ./trace ./trace/ORCL11_diag_10932.trc ./trace/TEST2.txt ./trace/alert_ORCL11.log ./trace/TEST.txt ./trace/ORCL11_dbrm_10934.trm ./trace/ORCL11_ora_16056.trm ./trace/ORCL11_mmon_10950.trc ./trace/ORCL11_vktm_10926.trm ./trace/ORCL11_ora_16056.trc ./trace/ORCL11_m000_10982.trc ./trace/ORCL11_dbrm_10934.trc ./trace/afiedt.buf ./trace/ORCL11_vktm_10926.trc ./trace/ORCL11_mmon_10950.trm ./trace/ORCL11_m000_10982.trm ./trace/ORCL11_diag_10932.trm ./hm ./hm/HMREPORT_HM_RUN_41.hm ./hm/HMREPORT_HM_RUN_21.hm ./incident ./incident/incdir_115385 ./incident/incdir_115385/ORCL11_ora_16056_i115385.trc ./incident/incdir_115385/ORCL11_ora_16056_i115385.trm ./alert ./alert/log.xml ./incpkg ./incpkg/pkg_3 ./incpkg/pkg_3/seq_1 ./incpkg/pkg_3/seq_1/config.xml ./incpkg/pkg_3/seq_1/export ./incpkg/pkg_3/seq_1/export/INCCKEY.dmp ./incpkg/pkg_3/seq_1/export/INCIDENT.dmp ./incpkg/pkg_3/seq_1/export/IPS_PACKAGE_FILE.dmp ./incpkg/pkg_3/seq_1/export/IPS_PACKAGE_HISTORY.dmp ./incpkg/pkg_3/seq_1/export/DDE_USER_ACTION.dmp ./incpkg/pkg_3/seq_1/export/EM_USER_ACTIVITY.dmp ./incpkg/pkg_3/seq_1/export/DDE_USER_ACTION_PARAMETER.dmp ./incpkg/pkg_3/seq_1/export/IPS_PACKAGE_INCIDENT.dmp ./incpkg/pkg_3/seq_1/export/PROBLEM.dmp ./incpkg/pkg_3/seq_1/export/IPS_CONFIGURATION.dmp ./incpkg/pkg_3/seq_1/export/INCIDENT_FILE.dmp ./incpkg/pkg_3/seq_1/export/DDE_USER_ACTION_PARAMETER_DEF.dmp ./incpkg/pkg_3/seq_1/export/DDE_USER_ACTION_DEF.dmp ./incpkg/pkg_3/seq_1/export/IPS_FILE_COPY_LOG.dmp ./incpkg/pkg_3/seq_1/export/HM_RUN.dmp ./incpkg/pkg_3/seq_1/export/DDE_USER_INCIDENT_ACTION_MAP.dmp ./incpkg/pkg_3/seq_1/export/IPS_PACKAGE.dmp ./incpkg/pkg_3/seq_1/export/DDE_USER_INCIDENT_TYPE.dmp ./incpkg/pkg_3/seq_1/export/IPS_FILE_METADATA.dmp ./incpkg/pkg_3/seq_1/opatch ./incpkg/pkg_3/seq_1/opatch/opatch.xml ./incpkg/pkg_3/seq_1/opatch/opatch.log ./incpkg/pkg_3/seq_1/metadata.xml ./incpkg/pkg_3/seq_1/crs ./incpkg/pkg_3/seq_1/crs/crsdiag.log ./incpkg/pkg_3/seq_1/manifest_3_1.xml ./incpkg/pkg_3/seq_1/manifest_3_1.html ./incpkg/pkg_3/seq_1/manifest_3_1.txt |
alert log와 다양한 파일들이 있음
incident ips pack 삭제
1 2 | $ cd /tmp $ rm -rf ORA600kzs_20200817075240_COM_1.zip diag/ |
problem ips pack 실행
problem id 1에 관련된 trace file 들을 /tmp directory 에 압축파일로 생성함
1 2 | adrci> ips pack problem 1 in /tmp DIA-48448: This command does not support multiple ADR homes |
ADR home이 여러개라서 명령어가 실행되지 않음
현재 ADR home 목록 확인
1 2 3 4 5 6 7 | adrci> show home ADR Homes: diag/rdbms/orcl11/ORCL11 diag/tnslsnr/ORACLE11/listener diag/tnslsnr/ORACLE11/listener1527 diag/tnslsnr/ORACLE11/listener1523 diag/clients/user_oracle/host_3827751472_80 |
show problem에서 나온 problem이 있는 ADR 경로를 home 으로 지정
1 2 3 4 5 | adrci> set home diag/rdbms/orcl11/ORCL11 adrci> show home ADR Homes: diag/rdbms/orcl11/ORCL11 |
problem ips pack 재실행
problem id 1에 관련된 trace file 들을 /tmp directory 에 압축파일로 생성함
1 2 | adrci> ips pack problem 1 in /tmp Generated package 4 in file /tmp/ORA600kzs_20200817081003_COM_1.zip, mode complete |
tmp 경로에 problem 메세지의 정보를 압축해줌
압축파일 확인
1 2 3 4 5 6 7 8 9 | $ cd /tmp $ ls -ltr total 1060 -rw-r--r-- 1 oracle dba 1051386 Aug 17 08:10 ORA600kzs_20200817081003_COM_1.zip $ unzip ORA600kzs_20200817081003_COM_1.zip $ ls -ltr total 1064 -rw-r--r-- 1 oracle dba 1051386 Aug 17 08:10 ORA600kzs_20200817081003_COM_1.zip drwxr-xr-x 3 oracle dba 4096 Aug 17 08:13 diag |
파일 상세 확인
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 60 61 62 63 64 65 66 67 68 69 | $ cd diag/rdbms/orcl11/ORCL11/ $ ls -ltr total 20 drwxr-xr-x 3 oracle dba 4096 Aug 17 08:13 incpkg drwxr-xr-x 3 oracle dba 4096 Aug 17 08:13 incident drwxr-xr-x 2 oracle dba 4096 Aug 17 08:13 alert drwxr-xr-x 2 oracle dba 4096 Aug 17 08:13 trace drwxr-xr-x 2 oracle dba 4096 Aug 17 08:13 hm $ find . . ./trace ./trace/ORCL11_diag_10932.trc ./trace/TEST2.txt ./trace/alert_ORCL11.log ./trace/TEST.txt ./trace/ORCL11_dbrm_10934.trm ./trace/ORCL11_ora_16056.trm ./trace/ORCL11_mmon_10950.trc ./trace/ORCL11_vktm_10926.trm ./trace/ORCL11_ora_16056.trc ./trace/ORCL11_m000_10982.trc ./trace/ORCL11_dbrm_10934.trc ./trace/afiedt.buf ./trace/ORCL11_vktm_10926.trc ./trace/ORCL11_mmon_10950.trm ./trace/ORCL11_m000_10982.trm ./trace/ORCL11_diag_10932.trm ./hm ./hm/HMREPORT_HM_RUN_41.hm ./hm/HMREPORT_HM_RUN_21.hm ./incident ./incident/incdir_115385 ./incident/incdir_115385/ORCL11_ora_16056_i115385.trc ./incident/incdir_115385/ORCL11_ora_16056_i115385.trm ./alert ./alert/log.xml ./incpkg ./incpkg/pkg_4 ./incpkg/pkg_4/seq_1 ./incpkg/pkg_4/seq_1/config.xml ./incpkg/pkg_4/seq_1/manifest_4_1.html ./incpkg/pkg_4/seq_1/export ./incpkg/pkg_4/seq_1/export/INCCKEY.dmp ./incpkg/pkg_4/seq_1/export/INCIDENT.dmp ./incpkg/pkg_4/seq_1/export/IPS_PACKAGE_FILE.dmp ./incpkg/pkg_4/seq_1/export/IPS_PACKAGE_HISTORY.dmp ./incpkg/pkg_4/seq_1/export/DDE_USER_ACTION.dmp ./incpkg/pkg_4/seq_1/export/EM_USER_ACTIVITY.dmp ./incpkg/pkg_4/seq_1/export/DDE_USER_ACTION_PARAMETER.dmp ./incpkg/pkg_4/seq_1/export/IPS_PACKAGE_INCIDENT.dmp ./incpkg/pkg_4/seq_1/export/PROBLEM.dmp ./incpkg/pkg_4/seq_1/export/IPS_CONFIGURATION.dmp ./incpkg/pkg_4/seq_1/export/INCIDENT_FILE.dmp ./incpkg/pkg_4/seq_1/export/DDE_USER_ACTION_PARAMETER_DEF.dmp ./incpkg/pkg_4/seq_1/export/DDE_USER_ACTION_DEF.dmp ./incpkg/pkg_4/seq_1/export/IPS_FILE_COPY_LOG.dmp ./incpkg/pkg_4/seq_1/export/HM_RUN.dmp ./incpkg/pkg_4/seq_1/export/DDE_USER_INCIDENT_ACTION_MAP.dmp ./incpkg/pkg_4/seq_1/export/IPS_PACKAGE.dmp ./incpkg/pkg_4/seq_1/export/DDE_USER_INCIDENT_TYPE.dmp ./incpkg/pkg_4/seq_1/export/IPS_FILE_METADATA.dmp ./incpkg/pkg_4/seq_1/manifest_4_1.xml ./incpkg/pkg_4/seq_1/opatch ./incpkg/pkg_4/seq_1/opatch/opatch.xml ./incpkg/pkg_4/seq_1/opatch/opatch.log ./incpkg/pkg_4/seq_1/metadata.xml ./incpkg/pkg_4/seq_1/crs ./incpkg/pkg_4/seq_1/crs/crsdiag.log ./incpkg/pkg_4/seq_1/manifest_4_1.txt |
alert log와 다양한 파일들이 있음
ips pack problemkey 실행
problem_key 'ORA 600' 를 가지는 모든 problem 에 관련된 trace file 들을 현재 directory 에 압축파일로 생성함
1 2 3 4 5 6 7 8 9 10 11 12 13 | $ pwd /home/oracle $ adrci ADRCI: Release 11.2.0.4.0 - Production on Mon Aug 17 08:16:56 2020 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. ADR base = "/oracle/app/oracle" adrci> set home diag/rdbms/orcl11/ORCL11 adrci> ips pack problemkey "ORA 600 [kzsviver:1]" Generated package 5 in file /home/oracle/ORA600kzs_20200817081956_COM_1.zip, mode complete |
현재 경로에 problemkey에 대한 정보를 압축해줌
시간으로 ips pack 명령 수행1
최근 8초 이내에 발생한 incident 에 대한 압축 파일을 생성
1 | adrci> ips pack seconds 10 |
시간으로 ips pack 명령 수행2
특정 시간대의 incident 에 대한 압축파일을 생성
1 2 3 | adrci> set home diag/rdbms/orcl11/ORCL11 adrci> ips pack time '2020-07-30 00:00:00.00' to '2020-07-30 23:00:00.00' Generated package 6 in file /home/oracle/ORA600kzs_20200817082607_COM_1.zip, mode complete |
이렇게 ips pack 를 생성후 SR에 업로드하여 원인 분석요청을 하면됨
참조 : http://blog.naver.com/jinh2004/80109023150
https://positivemh.tistory.com/544
https://positivemh.tistory.com/607
https://positivemh.tistory.com/645https://positivemh.tistory.com/631
https://community.oracle.com/thread/1059734?start=0&tstart=0
https://otsteam.tistory.com/59
https://oracle-base.com/articles/11g/automatic-diagnostics-repository-11gr1
https://positivemh.tistory.com/546
https://positivemh.tistory.com/648
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c Oracle Database In-Memory BASE_LEVEL 기능 (2) | 2020.09.02 |
|---|---|
| crsctl start crs wait 옵션 (0) | 2020.08.23 |
| 오라클 11g DB 재기동시 sql_id 는 남아있을까? (v$sql) (0) | 2020.08.13 |
| 오라클 11g R2 tfa(ahf) SQL 성능 진단 자동 수집 가이드(SRDC) (0) | 2020.08.12 |
| 오라클 19c RDA(Remote Diagnostic Agent) 사용 가이드 (0) | 2020.08.09 |