Make a Real DBA 효율적 운영을 위한 데이터베이스 관리자 가이드 ㈜ 신한시스템즈 김 종 근.

Presentation on theme: "Make a Real DBA 효율적 운영을 위한 데이터베이스 관리자 가이드 ㈜ 신한시스템즈 김 종 근."— Presentation transcript:

1 Make a Real DBA 효율적 운영을 위한 데이터베이스 관리자 가이드 ㈜ 신한시스템즈 김 종 근

2 효율적 운영을 위한 데이터베이스 관리자 가이드
효율적 운영을 위한 데이터베이스 관리자 가이드 - 일일 점검 사항 - 주간 점검 사항 비정기 점검 사항 - 에러에 대한 대처방법 및 해결방법 - 효율적 운영이란? - Q & A 

3 일일 점검 사항 오라클 로그 확인 Database 파일 상태 확인 OS 레벨 점검 Backup 상태 확인 각종 통계 관리
일일점검사항 일일 점검 사항 오라클 로그 확인 Database 파일 상태 확인 OS 레벨 점검 Backup 상태 확인 각종 통계 관리

4 오라클 로그 확인 데이터베이스 로그 사용자 로그 네트워크 로그 alert_<sid>.log
일일점검사항 오라클 로그 확인 데이터베이스 로그 alert_<sid>.log ($ORACLE_HOME/network/log) (USER_DUMP_DEST) (BACKGROUND_DUMP_DEST) - 오라클의 주 메시지 파일 <sid>_<processname>_<pid>.trc - 에러/정보에 대한 상세 정보 파일 <sid>_ora_<pid>.trc - 유저 프로세스의 추적 정보 파일 사용자 로그 listener.log - 리스너를 통한 DB 접속에 대한 로깅 네트워크 로그

5 alert_<sid>.log 시작/종료 정보 일일점검사항
Completed: ALTER DATABASE CLOSE NORMAL Thu Aug 14 19:00: ALTER DATABASE DISMOUNT Completed: ALTER DATABASE DISMOUNT archiving is disabled Thu Aug 14 19:00: ARCH shutting down ARC1: Archival stopped Fri Aug 15 08:27: Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 LICENSE_MAX_USERS = 0 Starting up ORACLE RDBMS Version: System parameters with non-default values: processes = 115 shared_pool_size =

6 alert_<sid>.log 에러 및 기타 정보 일일점검사항
Thread 1 advanced to log sequence 49745 Current log# 2 seq# mem# 0: /home2/redo0201.log Current log# 2 seq# mem# 1: /home2/redo0202.log Thread 1 cannot allocate new log, sequence 49746 Checkpoint not complete Thu Aug 8 20:27: ORA-1628: max # extents 254 reached for rollback segment R03 Failure to extend rollback segment 3 because of 1628 condition FULL status of rollback segment 3 set. Tue Jan 28 16:46: Errors in file /home2/product/8.1.5/admin/ORA81/tdump/ora81_s000_479.trc: ORA-00600: 내부 오류 코드, 인수 : [12700], [13064], [ ], [6], [], [], [], [] Mon Apr 7 11:30: Errors in file /home2/product/8.1.5/admin/ORA81/tdump/ora81_s000_491.trc: ORA-07445: 예외 발견: 코아 덤프 [kqlprfd()+88] [SIGSEGV] [Address not mapped to object] [4] [] [] Mon Apr 7 11:30: found dead multi-threaded server 'S000', pid = (8, 1)

7 데이터베이스 파일 상태 확인 데이터베이스 파일 컨트롤 파일 온라인 리두 로그 파일 데이터 파일 아카이브 리두 로그 파일
일일점검사항 데이터베이스 파일 상태 확인 데이터베이스 파일 컨트롤 파일 온라인 리두 로그 파일 데이터 파일 시스템 데이터 파일 사용자 데이터 파일 임시 파일 아카이브 리두 로그 파일

8 컨트롤 파일 상태 확인 컨트롤 파일 파일에 이상은 없는가? 다중화는 되어 있는가?
일일점검사항 컨트롤 파일 상태 확인 컨트롤 파일 SQL> SELECT * FROM V$CONTROLFILE; STATUS NAME /home1/control01.ctl /home3/oradata/oracle8/control02.ctl 파일에 이상은 없는가? 다중화는 되어 있는가?

9 리두 로그 파일 상태 확인 온라인 리두 로그 파일 파일에 이상은 없는가? 다중화는 되어 있는가? 일일점검사항
SQL> SELECT * FROM V$LOGFILE; GROUP# STATUS MEMBER /home3/oradata/oracle8/redo0101.log /home3/oradata/oracle8/redo0201.log /home3/oradata/oracle8/redo0301.log /home1/redo0102.log /home1/redo0202.log /home1/redo0302.log 파일에 이상은 없는가? 다중화는 되어 있는가?

10 리두 로그 파일 상태 확인 온라인 리두 로그 파일 그룹의 상태는 정상인가? 아카이빙은 잘 되고 있는가? 일일점검사항
SQL> SELECT * FROM V$LOG; GROUP# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_TIME NO CURRENT /12/16 16:34 YES INACTIVE 2003/12/16 14:10 YES INACTIVE 2003/12/16 14:30 그룹의 상태는 정상인가? 아카이빙은 잘 되고 있는가?

11 데이터 파일 상태 확인 테이블스페이스 상태 OFFLINE이나 이상 상태에 있는 것은 없는가? 일일점검사항
SQL> SELECT tablespace_name, status, contents, logging, extent_management, allocation_type, plugged_in 3 FROM dba_tablespaces 4 ORDER BY tablespace_name TABLESPACE_NAME STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO PLU INDX ONLINE PERMANENT LOGGING LOCAL UNIFORM NO QUERY_DATA READ ONLY PERMANENT LOGGING LOCAL UNIFORM NO SAMPLE ONLINE PERMANENT LOGGING LOCAL UNIFORM NO SYSTEM ONLINE PERMANENT LOGGING DICTIONARY USER NO TEMP ONLINE TEMPORARY NOLOGGING LOCAL UNIFORM NO UNDOTBS ONLINE UNDO LOGGING LOCAL SYSTEM NO USERS ONLINE PERMANENT LOGGING LOCAL UNIFORM NO OFFLINE이나 이상 상태에 있는 것은 없는가?

12 데이터 파일 상태 확인 테이블스페이스 사용율 Freespace는 충분한가? 일일점검사항
SQL> SELECT tablespace_name, totsize "TotSize(K)", TO_CHAR(frsize*100/totsize,'9999.0') "Free(%)", maxsize "MaxExt(K)" 3 FROM ( SELECT SUM(bytes)/1024 frsize, MAX(bytes)/1024 maxsize, tablespace_name FROM dba_free_space GROUP BY tablespace_name) fr, ( SELECT SUM(bytes)/1024 totsize, tablespace_name tname FROM dba_data_files GROUP BY tablespace_name) tt 7* WHERE fr.tablespace_name = tt.tname TABLESPACE_NAME TotSize(K) Free(%) MaxExt(K) INDX QUERY_DATA SYSTEM UNDOTBS USERS Freespace는 충분한가?

13 데이터 파일 상태 확인 데이터 파일 상태 파일들의 상태에 이상은 없는가? 자동 증가되고 있는 파일은 있는가? 일일점검사항
SQL> SELECT d.tablespace_name, d.file_name, d.bytes/1024/1024 "SIZE(MB)", d.status, v.status, v.enabled, d.autoextensible, d.maxbytes/1024/1024 "MaxSize(MB)" 4 FROM dba_data_files d, v$datafile v 5 WHERE d.file_name = v.name 6* ORDER BY d.tablespace_name, d.file_name TABLESPACE_NAME FILE_NAME SIZE(MB) STATUS STATUS ENABLED AUT MxSz(MB) INDX /disk1/user01/ORADATA/u03/indx01.dbf AVAILABLE ONLINE READ WRITE NO QUERY_DATA /disk1/user01/ORADATA/u01/querydata01.dbf AVAILABLE ONLINE READ ONLY NO SAMPLE /disk1/user01/ORADATA/u02/sample01.dbf AVAILABLE ONLINE READ WRITE YES SYSTEM /disk1/user01/ORADATA/u01/system01.dbf AVAILABLE SYSTEM READ WRITE YES UNDOTBS /disk1/user01/ORADATA/u02/undotbs01.dbf AVAILABLE ONLINE READ WRITE YES USERS /disk1/user01/ORADATA/u03/users01.dbf AVAILABLE ONLINE READ WRITE NO 파일들의 상태에 이상은 없는가? 자동 증가되고 있는 파일은 있는가?

14 데이터 파일 상태 확인 데이터 파일 상태 백업 모드에 있는 파일은 없는가? 일일점검사항
SQL> SELECT B.FILE#, B.STATUS, D.NAME FILENAME, T.NAME TABLESPACE 2 FROM V$BACKUP B, V$DATAFILE D, V$TABLESPACE T 3 WHERE B.FILE# = D.FILE# AND D.TS# = T.TS# 4 ORDER BY TABLESPACE, FILENAME; FILE# STATUS FILENAME TABLESPACE 3 ACTIVE /disk3/user03/DATA/DISK3/data01.dbf DATA01 5 NOT ACTIVE /disk3/user03/DATA/DISK2/indx01.dbf INDX 2 NOT ACTIVE /disk3/user03/DATA/DISK2/rbs01.dbf RBS 1 NOT ACTIVE /disk3/user03/DATA/DISK1/system01.dbf SYSTEM 4 NOT ACTIVE /disk3/user03/DATA/DISK2/temp01.dbf TEMP 백업 모드에 있는 파일은 없는가?

15 임시 파일 상태 확인 임시 파일 상태 상태는 정상인가? 자동 증가로 해놓지는 않았는가? 일일점검사항
SQL> SELECT tablespace_name, file_name, status, bytes/1024/1024 cbytes, maxbytes/1024/1024 mbytes, autoextensible 3 FROM dba_temp_files; TABLESPACE_NAME FILE_NAME STATUS Size(MB) MaxSize(MB) AUT TEMP /home3/oradata/temp01.dbf AVAILABLE NO TEMP /home3/oradata/temp02.dbf AVAILABLE NO 상태는 정상인가? 자동 증가로 해놓지는 않았는가?

16 아카이브 확인 아카이브 대상에 대한 상태 대상 경로에 문제는 없는가? 일일점검사항
SQL> SELECT destination, binding, target, status, error 2 FROM v$archive_dest; DESTINATION BINDING TARGET STATUS ERROR /disk1/user01/ORADATA/ARCHIVE1 OPTIONAL PRIMARY VALID OPTIONAL PRIMARY INACTIVE 대상 경로에 문제는 없는가?

17 OS 레벨 점검 OS 레벨 점검 디스크 스페이스는 충분한가? CPU 사용율은? 메모리 사용율은? 아카이빙 디렉토리
일일점검사항 OS 레벨 점검 OS 레벨 점검 디스크 스페이스는 충분한가? 아카이빙 디렉토리 로깅 디렉토리 CPU 사용율은? 피크 시간 배치 프로세스 시간 메모리 사용율은? 피크 시간 배치 프로세스 시간

18 백업 상태 확인 백업은 잘 되었는가? 백업본 관리는 잘 되고 있는가? 백업 누락? 성공 여부? 테이프 보관 장소는 청결한가?
일일점검사항 백업 상태 확인 백업은 잘 되었는가? 백업본 관리는 잘 되고 있는가? 백업 누락? 성공 여부? 테이프 보관 장소는 청결한가? 테이프 수명은?

19 각종 통계 관리 수집할 통계 대상 선정 통계 관리 방식 결정 동시 세션 수 동시 트랜잭션 수 Lock & Wait 사항
일일점검사항 각종 통계 관리 수집할 통계 대상 선정 동시 세션 수 동시 트랜잭션 수 Lock & Wait 사항 최대 트랜잭션 량 로그 파일 생성량 히트율 파일 I/O량 통계 관리 방식 결정 데이터베이스에 저장 문서 또는 파일로 저장

20 주간점검사항 주간 점검 사항 데이터베이스 상태 확인 각종 통계 관리

21 데이터베이스 상태 확인 로그 스위치 간격 확인 리두 로그 파일의 사이즈는 적당한가? 주간점검사항
SQL> BREAK ON "1st Date" SKIP 1 SQL> SELECT recid, stamp, thread#, sequence#, first_change#, to_char(first_time,'YYYY/MM/DD DY') "1st Date", to_char(first_time,'HH24:MI:SS') "1st Time" 4 FROM v$log_history 5 WHERE first_time >= TRUNC(SYSDATE)-30; RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# 1st Date st Time E /11/18 TUE 00:01:09 E :02:53 E :03:22 E :03:54 E :04:27 E /11/19 WED 00:33:05 E :26:33 E :02:27 리두 로그 파일의 사이즈는 적당한가?

22 데이터베이스 상태 확인 롤백 세그먼트 상태 확인 OFFLINE 상태에 있는 것은 없는가? 주간점검사항
SQL> SELECT segment_name name, owner, tablespace_name, status, initial_extent/1024 init_ext, next_extent/1024 next_ext, min_extents, max_extents 4 FROM dba_rollback_segs NAME OWNER TABLESPACE_NAME STATUS INIT_EXT NEXT_EXT MIN_EXTENTS MAX_EXTENTS SYSTEM SYS SYSTEM ONLINE RBS SYS RBS ONLINE RBS SYS RBS ONLINE RBS SYS RBS ONLINE RBS SYS RBS ONLINE OFFLINE 상태에 있는 것은 없는가?

23 데이터베이스 상태 확인 롤백 세그먼트 사이즈 확인 롤백 세그먼트의 사이즈 및 개수는 적절한가? 주간점검사항
SQL> SELECT n.name, s.status, s.extents, s.rssize/1024 rsize, s.optsize/1024 osize, s.hwmsize/1024 hsize, s.extends, s.shrinks, s.aveshrink 3 FROM v$rollstat s, v$rollname n 4* WHERE s.usn = n.usn; NAME STATUS EXTENTS RSIZE OSIZE HSIZE EXTENDS SHRINKS AVESHRINK SYSTEM ONLINE RBS ONLINE RBS ONLINE RBS ONLINE RBS ONLINE 롤백 세그먼트의 사이즈 및 개수는 적절한가?

24 데이터베이스 상태 확인 대형 세그먼트 상태 확인 증가량을 예측할 수 있는가? 확장 한계에 도달하지는 않았는가? 주간점검사항
SQL> SELECT ROWNUM, a.* 2 FROM (SELECT owner, segment_type, tablespace_name, segment_name, bytes/1024 s_size, extents, max_extents, initial_extent/1024 i_size, next_extent/1024 n_size, pct_increase FROM dba_segments WHERE extents > 50 OR bytes > ORDER BY s_size DESC) a Segment Assigned Max Initial Next PCT Rank OWNER SEGMENT_TYPE TABLESPACE_NAME SEGMENT_NAME Size(K) Extents Extents Extent(K) Extent(K) Increase 1 SYS TABLE SYSTEM SOURCE$ , 2 SYS TABLE SYSTEM IDL_UB1$ , 3 SYS TABLE SYSTEM IDL_UB2$ , 증가량을 예측할 수 있는가? 확장 한계에 도달하지는 않았는가?

25 데이터베이스 상태 확인 유저별 세그먼트 SYSTEM, RBS, TEMP 등에 잘 못 만들어진 세그먼트는 없는가? 주간점검사항
SQL> SELECT owner, tablespace_name, segment_type, count(*), sum(bytes)/1024 "Size(K)" 2 FROM dba_segments 3 GROUP BY owner, tablespace_name, segment_type 4 ORDER BY owner, tablespace_name, segment_type DESC OWNER TABLESPACE_NAME SEGMENT_TYPE COUNT(*) Size(K) HR QUERY_DATA TABLE HR SYSTEM TABLE HR SYSTEM INDEX OE SYSTEM TABLE OE SYSTEM NESTED TABLE OE SYSTEM LOBSEGMENT OE SYSTEM LOBINDEX OE SYSTEM INDEX SYS SYSTEM TABLE SYS SYSTEM ROLLBACK SYSTEM, RBS, TEMP 등에 잘 못 만들어진 세그먼트는 없는가?

26 데이터베이스 상태 확인 객체의 상태 사용하지 않거나 사용되지 못하고 있는 오브젝트는? 주간점검사항
SQL> SELECT owner, object_type, object_name 2 FROM dba_objects 3 WHERE status = 'INVALID' 4 ORDER BY owner, object_type, object_name; OWNER OBJECT_TYPE OBJECT_NAME CICS_NEW FUNCTION FC_AAA FC_DG010_BU_ID 사용하지 않거나 사용되지 못하고 있는 오브젝트는?

27 각종 통계 관리 현재 상태 기록 미래 예측 일중 피크 시간은? 주중 피크 요일은? 월중 피크 일은? 년중 피크는?
주간점검사항 각종 통계 관리 현재 상태 기록 일중 피크 시간은? 주중 피크 요일은? 월중 피크 일은? 년중 피크는? 미래 예측 성능 변화 추이 데이터 증가 추이 백업 시간 증가 추이 복구 시간 추이

28 월간 점검 사항 복구 테스트 수행 백업/복구 정책에 대한 보완 사항 점검 각종 통계 정보 정리 시스템 측면의 보완 사항 점검
월간점검사항 월간 점검 사항 복구 테스트 수행 백업/복구 정책에 대한 보완 사항 점검 각종 통계 정보 정리 시스템 측면의 보완 사항 점검

29 비정기 점검 사항 데이터베이스 변경 작업에 대한 로그 기록 에러 발생 및 해결에 대한 로그 기록 오라클 업그레이드 및 패치
비정기점검사항 비정기 점검 사항 데이터베이스 변경 작업에 대한 로그 기록 에러 발생 및 해결에 대한 로그 기록 오라클 업그레이드 및 패치 비정기적 백업 사용자에 대한 교육

30 에러 대처 및 해결 방법 클라이언트 NLS_LANG 설정 oerr Oracle Metalink Website
Oracle Call Center 기타 오라클 관련 사이트

31 클라이언트 NLS_LANG 설정 NLS_LANG? NLS_LANG 구성
에러 대처 및 해결 방법 클라이언트 NLS_LANG 설정 NLS_LANG? - 클라이언트가 사용하는 언어, 지역, 문자셋에 대한 설정 NLS_LANG 구성 Language_Territory.Characterset Language Territory Characterset : 오라클 메시지, 요일 및 월 명칭 등 : 요일 번호, 날짜 형식, 통화기호 등 : 데이터의 문자셋 예) AMERICAN_AMERICA.US7ASCII KOREAN_KOREA.KO16KSC5601

32 클라이언트 NLS_LANG 설정 NLS_LANG 설정 Windows UNIX
에러 대처 및 해결 방법 클라이언트 NLS_LANG 설정 NLS_LANG 설정 NLS_LANG=AMERICAN_KOREA.KO16KSC5601 ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data Windows \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE UNIX .profile or .cshrc

33 클라이언트 NLS_LANG 설정 NLS_LANG 설정에 따른 변화 KOREAN_KOREA.KO16KSC5601
에러 대처 및 해결 방법 클라이언트 NLS_LANG 설정 NLS_LANG 설정에 따른 변화 KOREAN_KOREA.KO16KSC5601 $ sqlplus "/as sysdba" SQL*Plus: Release Production on 금 Dec 19 16:54: Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 휴지 인스턴스에 접속되었습니다. SQL> startup ORA-01078: failure in processing system parameters LRM-00109: '/oracle/920/dbs/initora920.ora' 8E03:/<v ?- <v AMERICAN_AMERICA.KO16KSC5601 $ sqlplus "/as sysdba" SQL*Plus: Release Production on Fri Dec 19 16:55: Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance. SQL> startup ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/oracle/920/dbs/initora920.ora'

34 oerr oerr 이란? 사용법 - 오라클 에러 메시지 확인 유틸리티(유닉스만 제공) $ oerr
에러 대처 및 해결 방법 oerr oerr 이란? - 오라클 에러 메시지 확인 유틸리티(유닉스만 제공) 사용법 $ oerr Usage: oerr facility error Facility is identified by the prefix string in the error message. For example, if you get ORA-7300, "ora" is the facility and "7300" is the error. So you should type "oerr ora 7300". If you get LCD-111, type "oerr lcd 111", and so on.

35 oerr oerr 사용예 $ oerr ora 1628 $ oerr tns 138 에러 대처 및 해결 방법
01628, 00000, "max # extents (%s) reached for rollback segment %s" // *Cause: An attempt was made to extend a rollback segment that was // already at the MAXEXTENTS value. // *Action: If the value of the MAXEXTENTS storage parameter is less than // the maximum allowed by the system, raise this value. $ oerr tns 138 00138, 00000, "Failed to find ORACLE executable directory" // *Cause: The ORACLE environment is not correctly set. // *Action: Ensure that the ORACLE environment is correctly set and the // errormessages file is in the correct place.

36 Metalink Website & Call Center
에러 대처 및 해결 방법 Metalink Website & Call Center 한국 오라클 고객지원센터:

37 기타 오라클 관련 사이트 한국 오라클 영문 사이트 신한시스템즈 http://www.oracle.com/kr
에러 대처 및 해결 방법 기타 오라클 관련 사이트 한국 오라클 영문 사이트 신한시스템즈

38 효율적 운영? Downtime 최소화 성능 극대화 사용자 실수 방지 필요에 따른 작업시간 최소화 Online 상 문제 해결
문제 해결 시간 최소화 문제 발생 미연에 방지 지속적 튜닝 사용자 교육

39 감사합니다

40 Q & A