[Oracle] 데이터베이스 모니터링 스크립트(Basic Info)
Oracle 데이터베이스 관리 업무를 맡고 나서 벼르고 벼르던 데이터베이스 모니터링 스크립트를 정리하고 있습니다. 현재 유지보수 업체에서 사용하는 것을 참고하려고 했는데, 뭔가 2% 부족한 것 같아서 여러 사이트 및 DBA분들의 스크립트를 참고했습니다. 나름대로 일일/주간/월간 단위로 점검해야 할 항목을 정했는데, 제대로 분류했는지 자신이 없네요! 운영하면서 일/주/월 단위 점검항목을 재분류해야 할 것 같습니다. 혹시 좋은 의견있으시면 남겨주세요.
- 점검항목 타이틀 출력(printTitle.sh)
- 데이터베이스 기본 정보 확인
- B01.Dispay_Database_Version.sql
- B02.Dispay_Database_Information.sql
- B03.Dispay_Database_Instance.sql
- B04.Check_Important_Parameter_Values.sql
- B05.Display_Overview_of_Schema.sql
- Check_Basic_Info.sh
- 일일 점검항목
- 주간 점검항목
- 월간 점검항목
- crontab 등록 및 이메일 전송
점검항목 타이틀 출력
sql 프롬프트에서 점검항목 등의 타이틀을 출력하려면 조금 지저분하더라구요. 그래서 Linux Health Check 글에서 사용했던 타이틀 출력함수를 조금 수정해서 sql 파일을 호출하기 전에 먼저 호출해서 타이틀을 출력했습니다.
$ vi printTitle.sh#!/bin/bash # make seperators P=80 S=$(printf '*%.s' {1..80}) D=$(printf -- '-%.s' {1..80}) MAIN_YN=$1 shift echo if [ "$MAIN_YN" == "1" ]; then echo "$S" while [ $# -ne 0 ] do title=$1 n=$((P / 2 - ${#title} / 2)) m=$((P - n - ${#title})) printf "%-${n}s" "**" printf "$title" printf "%+${m}s\n" "**" shift done echo "$S" elif [ "$MAIN_YN" == "2" ]; then echo "$D" while [ $# -ne 0 ] do title=$1 n=$((P / 2 - ${#title} / 2)) m=$((P - n - ${#title})) printf "%-${n}s" "--" printf "$title" printf "%+${m}s\n" "--" shift done echo "$D" else echo "Invalid parameter. Please input 1 or 2 as the first parameter." fi
데이터베이스 기본 정보 확인
B01.Dispay_Database_Version.sql: v$version 뷰로부터 데이터베이스의 버전 정보 확인
$ vi B01.Dispay_Database_Version.sqlSELECT * FROM v$version;
B02.Dispay_Database_Information.sql: v$database 뷰로부터 데이터베이스 기본 정보 확인
$ vi B02.Dispay_Database_Information.sqlcol dbid for 9999999999 heading "DB|ID"; col name for a10 heading "DB|NAME"; col db_unique_name for a10 heading "DB|Unique Name"; col log_mode for a15 heading "Log Mode"; col open_mode for a15 heading "Open Mode"; col remote_archive for a10 heading "Remote|Archive"; col database_role for a10 heading "Database|Role"; col platform_name for a20 heading "Platform"; col flashback_on for a5 heading "Flashback|On/Off"; col CHECKPOINT for a20 heading "Checkpoint Date"; col ARCHIVE for a20 heading "Archive Date"; col CONTROLFILE for a20 heading "ControlFile Date"; col ARCHIVELOG for a20 heading "ArchiveLog Date"; SELECT dbid, name, db_unique_name, log_mode, open_mode, remote_archive, database_role, platform_name, flashback_on FROM v$database; SELECT TO_CHAR(scn_to_timestamp(checkpoint_change#), 'yyyy-mm-dd hh24:mi:ss') AS CHECKPOINT , TO_CHAR(scn_to_timestamp(archive_change#), 'yyyy-mm-dd hh24:mi:ss') AS ARCHIVE , TO_CHAR(scn_to_timestamp(controlfile_change#), 'yyyy-mm-dd hh24:mi:ss') AS CONTROLFILE , TO_CHAR(scn_to_timestamp(archivelog_change#), 'yyyy-mm-dd hh24:mi:ss') AS ARCHIVELOG FROM v$database;
B03.Dispay_Database_Instance.sql: v$instance 뷰로부터 데이터베이스 인스턴스 정보 확인
$ vi B03.Dispay_Database_Instance.sqlcol instance_number for 9 heading "Instance|Number"; col instance_name for a10 heading "Instance|Name"; col instance_role for a20 heading "Instance|Role"; col status for a8 heading "Status"; col database_status for a10 heading "Database|Status"; col active_state for a10 heading "Active|State"; col blocked for a10 heading "Blocked"; SELECT instance_number , instance_name , instance_role , TO_CHAR(startup_time, 'yyyy-mm-dd hh:mi:ss') AS STARTUP_TIME , status , thread# , archiver , database_status , active_state , blocked FROM v$instance;
B04.Check_Parameter_Values.sql: v$parameter 등의 뷰들로부터 파라미터들의 설정 값 및 현재 값을 확인
$ vi B04.Check_Parameter_Values.sqlcol name for a30 heading "Name"; col display_value for a50 heading "Display Value"; col description for a70 heading "Description"; SELECT name, display_value, description FROM v$parameter WHERE name IN ('db_block_size', 'compatible', 'cursor_sharing', 'timed_statistics', 'optimizer_mode', 'recyclebin', 'cluster_database', 'db_block_checking', 'diagnostic_dest', 'audit_trail', 'audit_file_dest', 'background_dump_dest', 'core_dump_dest', 'db_recovery_file_dest', 'user_dump_dest') ORDER BY name asc; col LIMIT_VALUE for 9999999999999 heading "Limit Value"; col CURRENT_VALUE for 9999999999999 heading "Current Value"; col USED_PCT for 999 heading "Used_%"; SELECT name , LIMIT_VALUE , CURRENT_VALUE , DECODE(LIMIT_VALUE, 0, 0, ROUND(CURRENT_VALUE/LIMIT_VALUE*100, 0)) USED_PCT , description FROM ( SELECT name , TO_NUMBER(value) AS LIMIT_VALUE , CASE WHEN name = 'open_cursors' THEN (SELECT COUNT(*) FROM v$open_cursor) WHEN name = 'db_files' THEN (SELECT COUNT(*) FROM v$datafile) WHEN name = 'sessions' THEN (SELECT COUNT(*) FROM v$session) WHEN name = 'processes' THEN (SELECT COUNT(*) FROM v$process) WHEN name = 'sga_max_size' THEN (SELECT SUM(CURRENT_SIZE) FROM v$sga_dynamic_components) WHEN name = 'memory_max_target' THEN (SELECT SUM(CURRENT_SIZE) FROM v$memory_dynamic_components) WHEN name = 'pga_aggregate_target' THEN (SELECT value FROM v$pgastat WHERE name = 'total PGA allocated') WHEN name = 'db_recovery_file_dest_size' THEN (SELECT space_used FROM v$recovery_file_dest) END AS CURRENT_VALUE , description FROM v$parameter WHERE name IN ('open_cursors', 'db_files', 'sessions', 'processes', 'sga_max_size', 'memory_max_target', 'pga_aggregate_target', 'db_recovery_file_dest_size') ) ORDER BY name asc;
B05.Display_Overview_of_Schema.sql: dba_users 등의 데이블들로부터 스키마 정보 확인
$ vi B05.Display_Overview_of_Schema.sqlcol schema_type for a25 heading "Schema Type"; col locked_expired for 999 heading "Locked/Expired|Schema"; col active for 999 heading "Active|Schema"; col username for a12 heading "Active|Schema"; col default_tablespace for a15 heading "Default|Tablespace"; col schema_type for a15 heading "Schema Type"; col total_bytes_in_mb for 999999.90 heading "Total bytes|in MB"; SELECT NVL(b.statement_opt, 'USER SCHEMA') AS schema_type , SUM(CASE WHEN lock_date IS NOT NULL OR expiry_date IS NOT NULL THEN 1 END) AS locked_expired , SUM(CASE WHEN lock_date IS NULL AND expiry_date IS NULL THEN 1 END) AS active FROM dba_users a , dba_logstdby_skip b WHERE a.username = b.owner(+) GROUP BY NVL(b.statement_opt, 'USER SCHEMA'); SELECT a.username , a.default_tablespace , NVL(c.statement_opt, 'USER SCHEMA') AS schema_type , ROUND(SUM(b.bytes/1024/1024), 2) AS total_bytes_in_MB FROM dba_users a , dba_segments b , dba_logstdby_skip c WHERE a.username = b.owner(+) AND a.username = c.owner(+) AND a.lock_date IS NULL AND a.expiry_date IS NULL GROUP BY a.username, a.default_tablespace, c.statement_opt ORDER BY 1 ASC;
Check_Basic_Info.sh
$ vi Check_Basic_Info.sh#!/bin/bash SCRIPT_PATH=/home/oracle/scripts_for_dbcheck sqlplus -s '/as sysdba' <<EOF1 > ${SCRIPT_PATH}/Check_DataBase.result 2>&1 set pagesize 1000 linesize 1000; set heading on; HOST ${SCRIPT_PATH}/printTitle.sh 1 "B01. Display Database Version" @${SCRIPT_PATH}/B01.Dispay_Database_Version.sql HOST ${SCRIPT_PATH}/printTitle.sh 1 "B02. Display Database Information" @${SCRIPT_PATH}/B02.Dispay_Database_Information.sql HOST ${SCRIPT_PATH}/printTitle.sh 1 "B03. Display Database Instance" @${SCRIPT_PATH}/B03.Dispay_Database_Instance.sql HOST ${SCRIPT_PATH}/printTitle.sh 1 "B04. Check the Values of Important Parameters" @${SCRIPT_PATH}/B04.Check_Parameter_Values.sql HOST ${SCRIPT_PATH}/printTitle.sh 1 "B05. Display the Overview of Schema" @${SCRIPT_PATH}/B05.Display_Overview_of_Schema.sql exit; EOF1
Check_Basic_Info.sh 실행 결과(예제)
$ cat Check_Basic_Info.result******************************************************************************** ** B01. Display Database Version ** ******************************************************************************** BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production ******************************************************************************** ** B02. Display Database Information ** ******************************************************************************** DB DB DB Remote Database Flash ID NAME Unique Nam Log Mode Open Mode Archive Role Platform On/Of ----------- ---------- ---------- --------------- --------------- ---------- ---------- -------------------- ----- 1111111111 CDEV CDEV ARCHIVELOG READ WRITE ENABLED PRIMARY Linux x86 64-bit NO Checkpoint Date Archive Date ControlFile Date ArchiveLog Date -------------------- -------------------- -------------------- -------------------- 2024-04-30 02:14:01 2024-04-26 21:44:01 2024-04-30 15:07:00 2024-04-30 02:14:01 ******************************************************************************** ** B03. Display Database Instance ** ******************************************************************************** Instance Instance Instance Database Active Number Name Role STARTUP_TIME Status THREAD# ARCHIVE Status State Blocked -------- ---------- -------------------- ------------------- -------- ---------- ------- ---------- ---------- ---------- 1 CDEV PRIMARY_INSTANCE 2023-08-23 03:19:10 OPEN 1 STARTED ACTIVE NORMAL NO ******************************************************************************** ** B04. Check the Values of Important Parameters ** ******************************************************************************** Name Display Value Description ------------------------------ -------------------------------------------------- ---------------------------------------------------------------------- audit_file_dest /u02/app/oracle/admin/CDEV/adump Directory in which auditing files are to reside background_dump_dest /u02/app/oracle/diag/rdbms/cdev/CDEV/trace Detached process dump directory cluster_database FALSE if TRUE startup in cluster database mode compatible 11.2.0.4 Database will be completely compatible with this software version core_dump_dest /u02/app/oracle/diag/rdbms/cdev/CDEV/cdump Core dump directory cursor_sharing EXACT cursor sharing mode db_block_checking FULL header checking and data and index block checking db_block_size 8192 Size of database block in bytes db_recovery_file_dest /u02/app/oracle/fast_recovery_area/ default database recovery file location diagnostic_dest /u02/app/oracle diagnostic base directory optimizer_mode ALL_ROWS optimizer mode recyclebin on recyclebin processing timed_statistics TRUE maintain internal timing statistics user_dump_dest /u02/app/oracle/diag/rdbms/cdev/CDEV/trace User process dump directory 14 rows selected. Name Limit Value Current Value Used_% Description ------------------------------ -------------- -------------- ------ ---------------------------------------------------------------------- db_files 1024 57 6 max allowable # db files db_recovery_file_dest_size 644245094400 287417320960 45 database recovery files size limit memory_max_target 0 21390950400 0 Max size for Memory Target open_cursors 1000 386 39 max # cursors per session pga_aggregate_target 4294967296 581512192 14 Target size for the aggregate PGA memory consumed by the instance processes 400 76 19 user processes sessions 624 57 9 user and system sessions sga_max_size 8589934592 8506048512 99 max total SGA size 8 rows selected. ******************************************************************************** ** B05. Display the Overview of Schema ** ******************************************************************************** Locked/Expired Active Schema Type Schema Schema --------------- -------------- ------ INTERNAL SCHEMA 16 2 USER SCHEMA 6 10 Active Default Total bytes Schema Tablespace Schema Type in MB ------------ --------------- --------------- ----------- COM TS_DDB USER SCHEMA 4604.06 ..... 12 rows selected.
