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

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

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

오라클 로그 확인 데이터베이스 로그 사용자 로그 네트워크 로그 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 접속에 대한 로깅 네트워크 로그

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

alert_<sid>.log 에러 및 기타 정보 일일점검사항 Thread 1 advanced to log sequence 49745 Current log# 2 seq# 49745 mem# 0: /home2/redo0201.log Current log# 2 seq# 49745 mem# 1: /home2/redo0202.log Thread 1 cannot allocate new log, sequence 49746 Checkpoint not complete Thu Aug 8 20:27:23 2002 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:41 2003 Errors in file /home2/product/8.1.5/admin/ORA81/tdump/ora81_s000_479.trc: ORA-00600: 내부 오류 코드, 인수 : [12700], [13064], [41945653], [6], [], [], [], [] Mon Apr 7 11:30:13 2003 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:21 2003 found dead multi-threaded server 'S000', pid = (8, 1)

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

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

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

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

데이터 파일 상태 확인 테이블스페이스 상태 OFFLINE이나 이상 상태에 있는 것은 없는가? 일일점검사항 SQL> SELECT tablespace_name, status, contents, logging, extent_management, 2 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이나 이상 상태에 있는 것은 없는가?

데이터 파일 상태 확인 테이블스페이스 사용율 Freespace는 충분한가? 일일점검사항 SQL> SELECT tablespace_name, totsize "TotSize(K)", 2 TO_CHAR(frsize*100/totsize,'9999.0') "Free(%)", maxsize "MaxExt(K)" 3 FROM ( SELECT SUM(bytes)/1024 frsize, MAX(bytes)/1024 maxsize, tablespace_name 4 FROM dba_free_space GROUP BY tablespace_name) fr, 5 ( SELECT SUM(bytes)/1024 totsize, tablespace_name tname 6 FROM dba_data_files GROUP BY tablespace_name) tt 7* WHERE fr.tablespace_name = tt.tname TABLESPACE_NAME TotSize(K) Free(%) MaxExt(K) ------------------------------ ---------- ------- ---------- INDX 5120 97.7 5000 QUERY_DATA 1024 84.4 864 SYSTEM 117760 3.1 3664 UNDOTBS 30720 6.0 448 USERS 5120 98.8 5056 Freespace는 충분한가?

데이터 파일 상태 확인 데이터 파일 상태 파일들의 상태에 이상은 없는가? 자동 증가되고 있는 파일은 있는가? 일일점검사항 SQL> SELECT d.tablespace_name, d.file_name, d.bytes/1024/1024 "SIZE(MB)", 2 d.status, v.status, v.enabled, 3 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 5 AVAILABLE ONLINE READ WRITE NO 0 QUERY_DATA /disk1/user01/ORADATA/u01/querydata01.dbf 1 AVAILABLE ONLINE READ ONLY NO 0 SAMPLE /disk1/user01/ORADATA/u02/sample01.dbf 10 AVAILABLE ONLINE READ WRITE YES 250 SYSTEM /disk1/user01/ORADATA/u01/system01.dbf 115 AVAILABLE SYSTEM READ WRITE YES 150 UNDOTBS /disk1/user01/ORADATA/u02/undotbs01.dbf 30 AVAILABLE ONLINE READ WRITE YES 30 USERS /disk1/user01/ORADATA/u03/users01.dbf 5 AVAILABLE ONLINE READ WRITE NO 0 파일들의 상태에 이상은 없는가? 자동 증가되고 있는 파일은 있는가?

데이터 파일 상태 확인 데이터 파일 상태 백업 모드에 있는 파일은 없는가? 일일점검사항 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 백업 모드에 있는 파일은 없는가?

임시 파일 상태 확인 임시 파일 상태 상태는 정상인가? 자동 증가로 해놓지는 않았는가? 일일점검사항 SQL> SELECT tablespace_name, file_name, status, bytes/1024/1024 cbytes, 2 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 500 0 NO TEMP /home3/oradata/temp02.dbf AVAILABLE 500 0 NO 상태는 정상인가? 자동 증가로 해놓지는 않았는가?

아카이브 확인 아카이브 대상에 대한 상태 대상 경로에 문제는 없는가? 일일점검사항 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 대상 경로에 문제는 없는가?

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

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

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

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

데이터베이스 상태 확인 로그 스위치 간격 확인 리두 로그 파일의 사이즈는 적당한가? 주간점검사항 SQL> BREAK ON "1st Date" SKIP 1 SQL> SELECT recid, stamp, thread#, sequence#, first_change#, 2 to_char(first_time,'YYYY/MM/DD DY') "1st Date", 3 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 1st Time ---------- ---------- ------- ---------- ------------- -------------- -------- 6995 510364973 1 6995 7.2280E+12 2003/11/18 TUE 00:01:09 6996 510365002 1 6996 7.2280E+12 00:02:53 6997 510365034 1 6997 7.2280E+12 00:03:22 6998 510365067 1 6998 7.2280E+12 00:03:54 6999 510365101 1 6999 7.2280E+12 00:04:27 7023 510463593 1 7023 7.2280E+12 2003/11/19 WED 00:33:05 7024 510465747 1 7024 7.2280E+12 03:26:33 7025 510466429 1 7025 7.2280E+12 04:02:27 리두 로그 파일의 사이즈는 적당한가?

데이터베이스 상태 확인 롤백 세그먼트 상태 확인 OFFLINE 상태에 있는 것은 없는가? 주간점검사항 SQL> SELECT segment_name name, owner, tablespace_name, status, 2 initial_extent/1024 init_ext, next_extent/1024 next_ext, 3 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 52 52 2 249 RBS01 SYS RBS ONLINE 100 100 10 249 RBS02 SYS RBS ONLINE 100 100 10 249 RBS03 SYS RBS ONLINE 100 100 10 249 RBS04 SYS RBS ONLINE 100 100 10 249 OFFLINE 상태에 있는 것은 없는가?

데이터베이스 상태 확인 롤백 세그먼트 사이즈 확인 롤백 세그먼트의 사이즈 및 개수는 적절한가? 주간점검사항 SQL> SELECT n.name, s.status, s.extents, s.rssize/1024 rsize, s.optsize/1024 osize, 2 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 80 4796 4796 0 0 0 RBS01 ONLINE 10 996 996 0 0 0 RBS02 ONLINE 10 996 996 0 0 0 RBS03 ONLINE 10 996 996 0 0 0 RBS04 ONLINE 10 996 996 0 0 0 롤백 세그먼트의 사이즈 및 개수는 적절한가?

데이터베이스 상태 확인 대형 세그먼트 상태 확인 증가량을 예측할 수 있는가? 확장 한계에 도달하지는 않았는가? 주간점검사항 SQL> SELECT ROWNUM, a.* 2 FROM (SELECT owner, segment_type, tablespace_name, segment_name, 3 bytes/1024 s_size, extents, max_extents, initial_extent/1024 i_size, 4 next_extent/1024 n_size, pct_increase 5 FROM dba_segments 6 WHERE extents > 50 OR bytes > 10000000 7 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$ 44,412 223 2147483645 12 200 0 2 SYS TABLE SYSTEM IDL_UB1$ 12,224 123 2147483645 12 100 0 3 SYS TABLE SYSTEM IDL_UB2$ 7,512 76 2147483645 12 100 0 증가량을 예측할 수 있는가? 확장 한계에 도달하지는 않았는가?

데이터베이스 상태 확인 유저별 세그먼트 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 2 96 HR SYSTEM TABLE 6 84 HR SYSTEM INDEX 19 228 OE SYSTEM TABLE 8 4088 OE SYSTEM NESTED TABLE 2 36 OE SYSTEM LOBSEGMENT 5 60 OE SYSTEM LOBINDEX 5 60 OE SYSTEM INDEX 26 1372 SYS SYSTEM TABLE 262 78680 SYS SYSTEM ROLLBACK 1 420 SYSTEM, RBS, TEMP 등에 잘 못 만들어진 세그먼트는 없는가?

데이터베이스 상태 확인 객체의 상태 사용하지 않거나 사용되지 못하고 있는 오브젝트는? 주간점검사항 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 사용하지 않거나 사용되지 못하고 있는 오브젝트는?

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

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

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

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

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

클라이언트 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

클라이언트 NLS_LANG 설정 NLS_LANG 설정에 따른 변화 KOREAN_KOREA.KO16KSC5601 에러 대처 및 해결 방법 클라이언트 NLS_LANG 설정 NLS_LANG 설정에 따른 변화 KOREAN_KOREA.KO16KSC5601 $ sqlplus "/as sysdba" SQL*Plus: Release 9.2.0.1.0 - Production on 금 Dec 19 16:54:46 2003 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 FD@O@; ?- <v >x=@4O4Y AMERICAN_AMERICA.KO16KSC5601 $ sqlplus "/as sysdba" SQL*Plus: Release 9.2.0.1.0 - Production on Fri Dec 19 16:55:24 2003 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'

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.

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.

Metalink Website & Call Center 에러 대처 및 해결 방법 Metalink Website & Call Center http://metalink.oracle.com/ 한국 오라클 고객지원센터: 1588-8501

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

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

감사합니다

Q & A