#29_140723_WSHOP2_AUTOMATIC DIAGNOSTIC REPOSITORY

# AUTOMATIC DIAGNOSTIC REPOSITORY

l Automatic Diagnostic Repository

- 11g 버전부터 오라클에 문제가 생겼을 때 진단하기 위해서 필요한 파일들을 하나의 디렉토리 밑에 두고 간단한 명령어(adrci)로 진단을 빨리 할 수 있도록 하는 기능

l 오라클 문제가 생겼을떄 진단하기 위한 로그 정보들은 아래의 디렉토리에 저장된다(11g이상부터 적용)

SQL> show parameter diag NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ diagnostic_dest                      string      /u01/app/oracle

l adrci 명령어로 오라클의 문제를 파악하는 방법

[dbdb:trace]$ adrci adrci> show problem ADR Home = /u01/app/oracle/diag/rdbms/dbdb/dbdb: ************************************************************************* PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME                             -------------------- ----------------------------------------------------------- -------------------- -------------------------------- 1                    ORA 600 [kokle_lob2lob13:input mismatch]                    37482                2014-07-17 14:16:26.033000 +09:00       1 rows fetched adrci> show incident ADR Home = /u01/app/oracle/diag/rdbms/dbdb/dbdb: ************************************************************************* INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME                              -------------------- ----------------------------------------------------------- ---------------------------------------- 37481                ORA 600 [kokle_lob2lob13:input mismatch]                    2014-07-17 14:14:21.268000 +09:00       37482                ORA 600 [kokle_lob2lob13:input mismatch]                    2014-07-17 14:16:26.033000 +09:00       2 rows fetched adrci> show alert ADR Home = /u01/app/oracle/diag/rdbms/dbdb/dbdb: ************************************************************************* Output the results to file: /tmp/alert_14992_3086_dbdb_1.ado

l 현재 DB에서 발생하는 로그들을 모아놓은 디렉토리 정보를 보는 뷰

select * from v$diag_info;

  

l 블록이 손상되었을 때 RMAN으로 복구방법

- block : 오라클 data를 저장하느 논리적 공간 중 최소단위

- block이 손상되면 테이블안에서 조회가 되는 것이 있고 안되는 것이 존재

- 위와 같은 현상이 발생하면 어떤 에러가 발생하는가?(ORA-01578)

• 깨진 블록 복구

lab_07_072.sh

- lab_07_072.sh 파일을 /u01/app/oracle/oradata/dbdb 이 경로로 옮긴다

1.    HR 계정의 department 테이블의 블록을 깨트린후 조회

SQL> select file_id, block_id     from dba_extents     where segment_name='DEPARTMENTS';    FILE_ID   BLOCK_ID ---------- ----------          5        168 - 이 블록번호로 해당 블록을 꺠트린다 SQL> exit [dbdb:~]$ data [dbdb:dbdb]$ ls C:\nppdf32Log\debuglog.txt  control0715.bak     lab_07_02.sh    report01.html  sysaux01.dbf   user06.dbf abc.sql                     cre.sql             log.sql         report02.html  system01.dbf   users01.dbf afiedt.buf                  cre_script.sql      log_status.sql  report03.html  temp01.dbf chk.sql                     cre_script0715.sql  recov_file.sql  report04.html  ts200.dmp control01.bak               example01.dbf       redo01.log      report06.txt   ts400.dbf control01.ctl               file_loc.sql        redo02.log      status.sql     ts405.dbf control03.ctl               ins_status.sql      redo03.log      status1.sql    undotbs01.dbf [dbdb:dbdb]$ ./lab_07_02.sh example01.dbf 168 8292 ……………………………………………………………………. - example은 8292사이즈인데 이 파일은 블록번호 168번을 가지고 이것을 깨트리는 명령어 [dbdb:dbdb]$ chmod 777 lab_07_02.sh - 최고권한 부여 [dbdb:dbdb]$ ./lab_07_02.sh example01.dbf 168 8292 - emample01.dbf 란 8292 사이즈를 가지는 168번 파일을 깨트리는 명령어 0+1 records in 0+1 records out 8 bytes (8 B) copied, 0.000249689 seconds, 32.0 kB/s 0+1 records in 0+1 records out 8 bytes (8 B) copied, 0.000243679 seconds, 32.8 kB/s 0+1 records in 0+1 records out 8 bytes (8 B) copied, 0.000222512 seconds, 36.0 kB/s 0+1 records in 0+1 records out 8 bytes (8 B) copied, 0.000169436 seconds, 47.2 kB/s [dbdb:dbdb]$ sqlplus hr/hr SQL> select * from departments; DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- -----------            10 Administration                        200        1700            20 Marketing                             201        1800            30 Purchasing                            114        1700            40 Human Resources                       203        2400 - 블록을 깨트려도 메모리에 이미 올라와있기 때문에 출력은 가능하다 SQL> connect / as sysdba SQL> alter system flush buffer_cache; - db buffer cache를 삭제 SQL> alter system flush shared_pool; - shared pool 삭제 SQL> connect hr/hr SQL> select * from departments; ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 5, block # 171) ORA-01110: data file 5: '/u01/app/oracle/oradata/dbdb/example01.dbf' - ORA-01587이 발생하는 것을 확인할 수 있다

2.    블록 손상여부를 진단 툴 수행

- DB verify 유틸리티

$ dbv file=/u01/app/oracle/oradata/dbdb/example01.dbf blocksize=8192

[dbdb:dbdb]$ dbv file=/u01/app/oracle/oradata/dbdb/example01.dbf blocksize=8192 DBVERIFY: Release 11.2.0.1.0 - Production on Wed Jul 23 16:48:03 2014 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/dbdb/example01.dbf Page 49 is marked corrupt Corrupt block relative dba: 0x01400031 (file 5, block 49) - block 49번이 깨진 것을 확인할 수 있다 Bad header found during dbv: Data in bad block:  type: 67 format: 7 rdba: 0x0a545055  last change scn: 0x0000.000b8580 seq: 0x1 flg: 0x04  spare1: 0x52 spare2: 0x52 spare3: 0x0  consistency value in tail: 0x85801e01  check value in block header: 0xc085  computed block checksum: 0xe47f ……………………………………………………………………………. DBVERIFY - Verification complete Total Pages Examined         : 12800 Total Pages Processed (Data) : 4408 Total Pages Failing   (Data) : 0 Total Pages Processed (Index): 1262 Total Pages Failing   (Index): 0 Total Pages Processed (Other): 5386 Total Pages Processed (Seg)  : 0 Total Pages Failing   (Seg)  : 0 Total Pages Empty            : 1738 Total Pages Marked Corrupt   : 6 Total Pages Influx           : 0 Total Pages Encrypted        : 0 Highest block SCN            : 775084 (0.775084)

3.    block이 깨졌을 때 복구하는 방법

RMAN> blockrecover datafile 5 block 49,50,51,52,171,173;

RMAN> blockrecover datafile 5 block 49,50,51,52,171,173; - 손상된 block 번호를 툴로 확인하고 번호를 적어주고 수행한다 RMAN> exit [dbdb:dbdb]$ sqlplus hr/hr SQL> select * from departments; DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- -----------            10 Administration                        200        1700            20 Marketing                             201        1800            30 Purchasing                            114        1700            40 Human Resources                       203        2400            50 Shipping                              121        1500            60 IT                                    103        1400            70 Public Relations                      204        2700            80 Sales                                 145        2500 …………………………………………………………….. - RMAN 복구 이후 departments 테이블이 조회가능한 것을 확인할 수 있다