Oracle RAC 용 점검 스크립트
한쪽 노드에서 실행해도 양쪽 노드의 결과물이 나오는 스크립트입니다.
Log 파일은 sqlplus를 접속한 디렉토리에 생성됩니다.
set feedback off
set timing off
set head off
clear columns;
column output new_value inst_name
col output for a16
column timecol new_value today
column spool_extension new_value suffix
col timecol for a8
select value || '_' output
from v$parameter
where name = 'instance_name';
select to_char(sysdate,'YYYYMMDD') timecol,'.log' spool_extension
from sys.dual;
set line 250
set pages 2000
set trimspool on
set head on
spool check_&&inst_name&&today&&suffix
PROMPT
PROMPT ============================================================================
PROMPT = =
PROMPT = STATUS CHECK SCRIPT Ver.3.0 =
PROMPT = =
PROMPT = Made by - Rastalion =
PROMPT = Create date. 2013/10/11 =
PROMPT = Modify date. 2018/06/18 =
PROMPT = =
PROMPT ============================================================================
set head off
set feedback off
select * from v$version;
set head on
col VERSION for a12
col COMP_NAME for a40
col SCHEMA for a12
col status for a13
select COMP_NAME ,VERSION ,SCHEMA, STATUS from dba_registry
/
PROMPT
PROMPT
pause ... please press enter key ....
PROMPT
PROMPT
PROMPT ===============================================================
PROMPT = 0. Database Infomation =
PROMPT = 0.1 DB Infomation =
PROMPT = 0.2 STARTUP Time =
PROMPT = 0.3 IP Address =
PROMPT = 0.4 NLS Character Set =
PROMPT ===============================================================
PROMPT
PROMPT
PROMPT 0.1 DB Infomation
PROMPT ===============================================================
set feedback off
col dbname NEW_VALUE dbname
col now NEW_VALUE today
col month NEW_VALUE month
col instance NEW_VALUE instance
col thread NEW_VALUE thread_number
col thread for 999
col instance for a14
col dbname for a14
col host_name for a16
col platform_name for a30 HEADING 'PLARFORM'
col version for a10
col log_mode for a16
SELECT distinct b.thread# thread
,b.instance_name instance
,a.name dbname
,b.host_name
,a.platform_name
,b.version
,a.log_mode
-- ,b.parallel
-- ,b.archiver
-- ,TO_CHAR(SYSDATE,'YYYY-MM-DD') now
-- ,TO_CHAR(SYSDATE,'YYYY-MM') month
-- ,a.log_mode
FROM gv$database a
,gv$instance b
order by 1
/
PROMPT
PROMPT === Archive log ===
archive log list;
PROMPT
PROMPT
pause ... please press enter key ....
PROMPT
PROMPT
PROMPT
PROMPT
PROMPT 0.2 Database Startup Time
PROMPT ===============================================================
set head off
select to_char(startup_time, 'YYYY-MM-DD HH24:MI:SS') "Startup time" from v$instance;
PROMPT
PROMPT
PROMPT 0.3 IP Address
PROMPT ===============================================================
col "IP Address" for a40
select utl_inaddr.get_host_address() "IP Address" from dual
/
PROMPT
PROMPT
PROMPT 0.4 Character Set
PROMPT ===============================================================
col PARAMETER for a30
col "CharacterSET" for a30
set head on
SELECT PARAMETER, VALUE "CharacterSET" FROM V$NLS_PARAMETERS
where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET')
/
PROMPT
PROMPT
PROMPT
PROMPT ===============================================================
PROMPT = 1. SGA =
PROMPT = 1.1 SGA Total Size (MB) =
PROMPT = 1.2 Shared Pool and Buffer Cache (MB) =
PROMPT = 1.4 Redo Log Buffer Size (MB) =
PROMPT ===============================================================
PROMPT
PROMPT
PROMPT 1.1 SGA
PROMPT ===============================================================
set head on
col "Node" for 999
col "SGA Parameter" for a24
col "Size (MB)" for a10
select INST_ID "Node", NAME "SGA Parameter", to_char(round(VALUE/1024/2014,2),'99,999.99') "Size (MB)" from gv$parameter
where name like 'sga_%'
order by 1
/
col "Node" for 999
col "SGA Size(MB)" for a40
select INST_ID "Node", 'SGA Used Total Size: '||TO_CHAR(round(sum(bytes)/1024/1024,2),'999,999.99')||' MB' as "SGA Size(MB)"
from gv$sgastat
group by INST_ID
order by 1
/
PROMPT
PROMPT
PROMPT 1.2 Shared Pool and Buffer Cache (MB)
PROMPT ===============================================================
col name FORMAT a15 HEADING 'Name'
col pool FORMAT a15 HEADING 'Pool Name'
col tbytes FORMAT 999,999,999.99 HEADING 'Total Size(MB)'
col ubytes FORMAT 999,999,999.99 HEADING 'Used Size(MB)'
col usedp FORMAT a8 HEADING 'Used(%)'
col fbytes FORMAT 999,999,999.99 HEADING 'Free Size(MB)'
col freep FORMAT a8 HEADING 'Free(%)'
PROMPT
PROMPT = Node 1
SELECT a.pool AS pool, a.tbytes/1024/1024 AS tbytes
,(a.tbytes-b.fbytes)/1024/1024 AS ubytes
,b.fbytes/1024/1024 AS fbytes
,ROUND((((a.tbytes-b.fbytes)/a.tbytes)*100),2)||'%' AS usedp
,ROUND(((b.fbytes/a.tbytes)*100),2)||'%' AS freep
FROM (SELECT pool
,SUM(bytes) AS tbytes
FROM gv$sgastat
WHERE pool='shared pool'
AND INST_ID='1'
GROUP BY pool) a
,(SELECT pool
,bytes AS fbytes
FROM gv$sgastat
WHERE pool='shared pool'
AND name='free memory'
AND INST_ID='1') b
WHERE a.pool='shared pool'
union all
SELECT a.name AS name
,SUM(a.bytes)/1024/1024 AS tbytes
,SUM(b.ubytes)/1024/1024 AS ubytes
,SUM(a.bytes-b.ubytes)/1024/1024 AS fbytes
,ROUND(SUM(b.ubytes)/SUM(a.bytes)*100,2)||'%' AS usedp
,ROUND(SUM((a.bytes-b.ubytes)/a.bytes)*100,2)||'%' AS freep
FROM gv$sgastat a
,(SELECT COUNT(1)*8192 AS ubytes
FROM v$bh) b
WHERE a.name='buffer_cache'
AND a.INST_ID='1'
GROUP BY a.name
/
PROMPT
PROMPT = Node 2
SELECT a.pool AS pool, a.tbytes/1024/1024 AS tbytes
,(a.tbytes-b.fbytes)/1024/1024 AS ubytes
,b.fbytes/1024/1024 AS fbytes
,ROUND((((a.tbytes-b.fbytes)/a.tbytes)*100),2)||'%' AS usedp
,ROUND(((b.fbytes/a.tbytes)*100),2)||'%' AS freep
FROM (SELECT pool
,SUM(bytes) AS tbytes
FROM gv$sgastat
WHERE pool='shared pool'
AND INST_ID='2'
GROUP BY pool) a
,(SELECT pool
,bytes AS fbytes
FROM gv$sgastat
WHERE pool='shared pool'
AND name='free memory'
AND INST_ID='2') b
WHERE a.pool='shared pool'
union all
SELECT a.name AS name
,SUM(a.bytes)/1024/1024 AS tbytes
,SUM(b.ubytes)/1024/1024 AS ubytes
,SUM(a.bytes-b.ubytes)/1024/1024 AS fbytes
,ROUND(SUM(b.ubytes)/SUM(a.bytes)*100,2)||'%' AS usedp
,ROUND(SUM((a.bytes-b.ubytes)/a.bytes)*100,2)||'%' AS freep
FROM gv$sgastat a
,(SELECT COUNT(1)*8192 AS ubytes
FROM v$bh) b
WHERE a.name='buffer_cache'
AND a.INST_ID='2'
GROUP BY a.name
/
PROMPT
PROMPT
PROMPT 1.3 Redo Log Buffer Size (MB)
PROMPT ===============================================================
col name FORMAT a15 HEADING 'Buffer Name'
col bytes FORMAT 999,999,999.99 HEADING 'Size(MB)'
SELECT INST_ID "Node", name, SUM(bytes)/1024/1024 AS bytes
FROM gv$sgastat
WHERE name='log_buffer'
GROUP BY INST_ID, name
order by 1
/
PROMPT
PROMPT
PROMPT
PROMPT ===============================================================
PROMPT = 2. Memory Allocation =
PROMPT = 2.1 Library Cache Miss Ratio =
PROMPT = 2.2 Data Dictionary Miss Ratio =
PROMPT = 2.3 Database Buffer Cache Hit Ratio =
PROMPT = 2.4 Redo Buffer Space Wait Ratio =
PROMPT ===============================================================
PROMPT
PROMPT
PROMPT 2.1 Library Cache Miss Ratio
PROMPT ===============================================================
col missratio FORMAT a36 HEADING 'Library Cache Miss Ratio|(If > 1%, increase SHARED_POOL_SIZE)'
select INST_ID "Node",to_char(trunc(sum(reloads)/sum(pins)*100,2))||' %' missratio,
SUM(PINS) "EXECUTIONS",SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"
from gv$librarycache
GROUP BY INST_ID
order by 1;
PROMPT
PROMPT
PROMPT 2.2 Data Dictionary Cache Hit Ratio and Miss Ratio
PROMPT ===============================================================
col missratio FORMAT a38 HEADING 'Data Dictionary Cache Miss Ratio|(If > 9.8%, increase SHARED_POOL_SIZE)'
col hitratio FORMAT a37 HEADING 'Data Dictionary Cache Hit Ratio|(If < 90%, increase SHARED_POOL_SIZE)'
select INST_ID "Node", trunc(sum(getmisses)/sum(gets)*100,2)||' %' AS missratio
,TRUNC((1-(SUM(getmisses)/SUM(gets)))*100,2)||'%' AS hitratio
from gv$rowcache
GROUP BY INST_ID
order by 1
;
PROMPT
PROMPT
PROMPT 2.3 Database Buffer Cache Hit Ratio
PROMPT ===============================================================
COL bcache for a40 HEADING 'Database Buffer Cache Hit Ratio|(If < 70%, increase DB_CACHE_SIZE)'
select s1.inst_id, to_char(100-(s3.value / (s1.value + s2.value)),'999.99')||' %' bcache
from gv$sysstat s1, gv$sysstat s2, gv$sysstat s3
where s1.name = 'consistent gets from cache'
and s2.name = 'db block gets from cache'
and s3.name = 'physical reads cache'
and s1.inst_id = s2.inst_id
and s1.inst_id = s3.inst_id
order by s1.inst_id
/
PROMPT
PROMPT
PROMPT 2.4 Redo Buffer Space Wait Ratio
PROMPT ===============================================================
select round(sum(decode(name, 'redo buffer allocation retries', value))/sum(decode(name, 'redo entries', value)),5)
"Redo Buffer Space Wait(%)",
sum(decode(name, 'redo log space requests', value)) "Redo Space Reqeusts"
from gv$sysstat
/
PROMPT
PROMPT
PROMPT
PROMPT ===============================================================
PROMPT = 3. PGA =
PROMPT = 3.1 PGA, UGA Usage (MB) =
PROMPT = 3.2 Session Check =
PROMPT = 3.3 Currnet Session List =
PROMPT = 3.4 PGA Usage per Client (MB) =
PROMPT ===============================================================
PROMPT
PROMPT
PROMPT 3.1 PGA, UGA Usage
PROMPT ===============================================================
col sum for a40 HEADING 'PGA, UGA session memory';
col uga_sum for a12;
col pga_sum for a12;
select a.INST_ID "Node",'Current PGA, UGA session memory SUM:' as sum,
sum(decode(c.name, 'session pga memory', trunc(value/1024/1024,2))) ||' MB' pga_sum,
sum(decode(c.name, 'session uga memory', trunc(value/1024/1024,2))) ||' MB' uga_sum
from gv$session a, gv$sesstat b, gv$statname c
where a.sid = b.sid
and b.statistic# = c.statistic#
and c.name like 'session%'
group by a.INST_ID
/
PROMPT
PROMPT
PROMPT 3.2 Session Check
PROMPT ===============================================================
col data for a55 HEADING 'Session Current Session / Max Value / Limit Value';
select INST_ID "Node", 'CURRENT / MAX / LIMIT: '|| CURRENT_UTILIZATION||' / '||MAX_UTILIZATION||' / '||to_number(INITIAL_ALLOCATION)||' ' as "data"
from GV$RESOURCE_LIMIT where RESOURCE_NAME = 'sessions'
group by INST_ID, CURRENT_UTILIZATION, MAX_UTILIZATION, INITIAL_ALLOCATION
order by 1
/
PROMPT
PROMPT
PROMPT 3.3 Currnet Session List
PROMPT ===============================================================
col username for a12
col "Client Name" for a30
col osuser for a10
col program for a35
col con for 999 HEADING 'Session';
select distinct inst_id "Node", username, osuser,program,count(1) as con,count(decode(status, 'ACTIVE',1) ) "Active Sessions", machine "Client Name"
from gv$session
where username is not null
and username not in ('SYS','SYSTEM')
group by inst_id,username,machine,osuser,program
order by inst_id,username,machine,osuser,program
/
PROMPT
PROMPT
PROMPT 3.4 PGA Usage per Client (MB)
PROMPT ===============================================================
select machine "Client Name",status,count(*) "Session CNT",
round(sum(pga_used_mem)/1024/1024) "PGA Total(MB)",
round(sum(pga_used_mem)/count(*)/1024/1024) "PGA per Client(MB)"
from gv$session s, gv$process p
where 1=1
--and s.status='active'
and s.paddr=p.addr
and type <> 'BACKGROUND'
group by machine,status
order by 1
/
PROMPT
PROMPT
PROMPT
PROMPT ===============================================================
PROMPT = 4. Datafile and Tablespace =
PROMPT = 4.0 ASM Status (GB) =
PROMPT = 4.1 Datafile Size(GB) =
PROMPT = 4.2 Tablespace usage =
PROMPT = 4.3 Temp Tablespace usage =
PROMPT = 4.4 Datafile Stat =
PROMPT = 4.5 Disk Physical I/O =
PROMPT = 4.6 Internal and External Sort =
PROMPT = 4.7 UNDO Segments Wait Ratio =
PROMPT ===============================================================
set feedback off
set head on
PROMPT
PROMPT
PROMPT 4.0 ASM Status (GB)
PROMPT ===============================================================
--PROMPT This DB is not ASM
col name format a10
col USABLE_FILE_GB format 999,999.00
col TOTAL_GB format 999,999.00
col FREE_GB format 999,999.00
col USABLE_CALC_GB format 999,999.00
select group_number "Group#",
name,
total_mb/1024 TOTAL_GB,
round((total_mb - USABLE_FILE_MB)/1024,2) USED_GB,
USABLE_FILE_MB/1024 USABLE_FILE_GB,
free_mb/1024 FREE_GB,
100-round(free_mb/total_mb*100) "usgae(%)",
((FREE_MB - REQUIRED_MIRROR_FREE_MB))/1024 USABLE_CALC_GB,
type, state
from v$asm_diskgroup;
PROMPT
PROMPT
PROMPT 4.1 Datafile Size(GB)
PROMPT ===============================================================
col data_files_sum for 999,999,999,999.99
col free_space_sum for 999,999,999,999.99
col extents for 999,999,999,999.99
col tbs for a60 HEADING "Used / Total (Free) (GB)"
select 'TBS Size: '||(data_files_sum - free_space_sum) || ' GB / '|| data_files_sum || ' GB (free '|| free_space_sum || ' GB)' AS tbs
from
(select round(sum(bytes)/1024/1024/1024,2) data_files_sum from dba_data_files),
(select round(sum(bytes)/1024/1024/1024,2) free_space_sum from dba_free_space)
/
PROMPT
PROMPT
PROMPT 4.2 Tablespace usage
PROMPT ===============================================================
COL Tablespace for a30
select DISTINCT df.tablespace_name "Tablespace",
round(df.TBS_byte /1048576,2) "Total(MB)",
round(fs.Free_byte /1048576,2) "Free(MB)",
round(((df.TBS_byte - fs.Free_byte)/df.TBS_byte) *100,0) "Used(%)",
db.autoextensible
--,round((df.TBS_byte - fs.Free_byte)/1048576,2) "Used(MB)",
--round((fs.Free_byte/df.TBS_byte)*100,0) "Free(%)"
from ( select tablespace_name, sum(bytes) TBS_byte
from dba_data_files group by tablespace_name ) df,
( select tablespace_name, max(bytes) Max_free, sum(bytes) Free_byte
from dba_free_space group by tablespace_name ) fs,
dba_data_files db
where df.tablespace_name = fs.tablespace_name
and df.tablespace_name = db.tablespace_name
order by 1
/
PROMPT
PROMPT
PROMPT 4.3 Temp Tablespace usage
PROMPT ===============================================================
col "File Name" for a40
col "Tablespace" for a10
col "File#" for 99
col "Rel.File#" for 99
col status for a9
col "Size(MB)" for a12
col "AutoExt." for a8
col "Increment(MB)" for a13
select
file_id "File#",
relative_fno "Rel.File#",
a.tablespace_name "Tablespace",
file_name "File Name",
to_char(round(bytes/1048576),'999,999.99') "Size(MB)",
decode(autoextensible,'NO','n/a',to_char(increment_by*block_size/1024/1024)) "Increment(MB)",
status "Status",
autoextensible "Auto Ext."
from dba_temp_files a,
(select tablespace_name,block_size from dba_tablespaces) b
where a.tablespace_name=b.tablespace_name
order by 1,2;
PROMPT
PROMPT
PROMPT 4.4 Datafile Stat
PROMPT ===============================================================
set feedback on
col Tablespace for a20
col LOCATION for a56
col ON_STAT for a8
col status for a10
col "Used Size(MB)" for a15
col "Free (%)" for a9
col aut for a3
select tablespace_name "Tablespace", file_name "LOCATION", to_char(bytes/1024/1024,'999,999.99') "Used Size(MB)",to_char(((maxbytes-bytes)/maxbytes)*100,'999.99')||' %' "Free %", autoextensible "AUT"
from dba_data_files
where autoextensible = 'YES'
order by 4 ASC
/
set feedback off
PROMPT
PROMPT
PROMPT 4.5 Disk Physical I/O
PROMPT ===============================================================
CREATE OR REPLACE VIEW tot_read_writes
AS
SELECT SUM(phyrds) phys_reads
,SUM(phywrts) phys_wrts
FROM v$filestat
/
col tablespace_name FORMAT a20 HEADING 'Tablespace'
col name FORMAT a56 HEADING 'Datafile'
col read_pct FORMAT 999.99 HEADING 'Reads%'
col wrts_pct FORMAT 999.99 HEADING 'Writes%'
SELECT tablespace_name
,name
,phyrds*100/trw.phys_reads read_pct
,phywrts*100/trw.phys_wrts wrts_pct
-- ,phyrds
-- ,phywrts
FROM v$datafile df
,v$filestat fs
,tot_read_writes trw
,dba_data_files ts
WHERE df.file#=fs.file#
AND df.file#=ts.file_id
ORDER BY 1
/
DROP VIEW tot_read_writes
/
PROMPT
PROMPT
PROMPT 4.6 Internal and External Sort
PROMPT ===============================================================
col disksortratio FORMAT a36 HEADING 'Disk Sort Ratio|(If higher, increase SORT_AREA_SIZE)'
col disksorts FORMAT 999,999,999,999 HEADING 'Disk Sorts'
col memorysorts FORMAT 999,999,999,999 HEADING 'Memory Sorts'
col rowssorted FORMAT 999,999,999,999 HEADING 'Rows Sorted'
SELECT distinct m.inst_id, ROUND(d.value/decode((m.value+d.value),0,1,
(m.value+d.value))*100)||'%' AS disksortratio
,d.value AS disksorts
,m.value AS memorysorts
-- ,r.value AS rowssorted
FROM gv$sysstat m
,gv$sysstat d
-- ,gv$sysstat r
WHERE m.name='sorts (memory)'
AND d.name='sorts (disk)'
--AND r.name='sorts (rows)'
group by m.inst_id, d.value, m.value
order by 1
/
PROMPT
PROMPT
PROMPT 4.7 UNDO Segments Wait Ratio
PROMPT ===============================================================
col rbs FORMAT a22 HEADING 'Rollback Segment'
col miss_ratio FORMAT a34 HEADING 'Wait(Miss) Ratio|(If > 1~2%, increase undo segment)'
SELECT name AS rbs
,TRUNC(waits/gets*100,2)||'%' AS miss_ratio
FROM v$rollstat
,v$rollname
WHERE v$rollstat.usn=v$rollname.usn
ORDER BY miss_ratio asc
/
PROMPT
PROMPT
PROMPT
PROMPT ===============================================================
PROMPT = 5. Redo Log =
PROMPT = 5.1 Redo Log Status =
PROMPT = 5.2 Redo Log File Wait (100/Day is good) =
PROMPT ===============================================================
PROMPT
PROMPT
PROMPT 5.1 Redo Log Status
PROMPT ===============================================================
col group# for 999
col mb for 9999
col member for a45
col seq# for 999,999
col status for a8
col arc for a5
col first_change# for 999,999,999,999,999
select a.group#, a.member, b.bytes/1024/1024 MB, b.sequence# "SEQ#",
b.status, b.archived "ARC", b.first_change#
from v$logfile a, v$log b where a.group#=b.group# order by 1,2;
PROMPT
PROMPT
PROMPT 5.2 Redo Log File Wait (100/Day is good)
PROMPT ===============================================================
PROMPT
PROMPT
PROMPT - Log File Switch Statistics
PROMPT ===========================================
col min FORMAT 999,999,999,999 HEADING 'Min/Day'
col avg FORMAT 999,999,999,999 HEADING 'Avg/Day'
col max FORMAT 999,999,999,999 HEADING 'Max/Day'
SELECT MIN(cnt) AS min
,AVG(cnt) AS avg
,MAX(cnt) AS max
FROM (SELECT TO_CHAR(first_time,'yyyymmdd') AS day
,COUNT(*) AS cnt
FROM v$log_history
WHERE thread#=1
AND TO_CHAR(first_time,'yyyymm')=TO_CHAR(sysdate,'yyyymm')
GROUP BY TO_CHAR(first_time,'yyyymmdd'))
/
PROMPT
PROMPT
PROMPT - Log File Switch Count Per Day
PROMPT ===========================================
col day for a10 HEADING 'Day/Time';
col 01 for a3
col 02 for a3
col 03 for a3
col 04 for a3
col 05 for a3
col 06 for a3
col 07 for a3
col 08 for a3
col 09 for a3
col 10 for a3
col 11 for a3
col 12 for a3
col 13 for a3
col 14 for a3
col 15 for a3
col 16 for a3
col 17 for a3
col 18 for a3
col 19 for a3
col 20 for a3
col 21 for a3
col 22 for a3
col 23 for a3
SELECT TO_CHAR(first_time,'yyyy-mm-dd') AS day
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'00',1,0)),'99') AS "00"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'01',1,0)),'99') AS "01"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'02',1,0)),'99') AS "02"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'03',1,0)),'99') AS "03"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'04',1,0)),'99') AS "04"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'05',1,0)),'99') AS "05"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'06',1,0)),'99') AS "06"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'07',1,0)),'99') AS "07"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'08',1,0)),'99') AS "08"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'09',1,0)),'99') AS "09"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'10',1,0)),'99') AS "10"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'11',1,0)),'99') AS "11"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'12',1,0)),'99') AS "12"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'13',1,0)),'99') AS "13"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'14',1,0)),'99') AS "14"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'15',1,0)),'99') AS "15"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'16',1,0)),'99') AS "16"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'17',1,0)),'99') AS "17"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'18',1,0)),'99') AS "18"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'19',1,0)),'99') AS "19"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'20',1,0)),'99') AS "20"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'21',1,0)),'99') AS "21"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'22',1,0)),'99') AS "22"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'23',1,0)),'99') AS "23"
,COUNT(*) AS "Day Sum"
FROM v$log_history
WHERE thread#=1
AND TO_CHAR(first_time,'yyyymm')=TO_CHAR(sysdate,'yyyymm')
GROUP BY TO_CHAR(first_time,'yyyy-mm-dd')
ORDER BY day DESC
/
PROMPT
PROMPT
PROMPT - Redo Log Space Requests
PROMPT ===========================================
col name FORMAT a30 HEADING 'Name'
col value for 999,999,999 head 'Value|(Near 0 is good, or Increase LOG_BUFFER)'
SELECT 'Redo Log Space Requests' AS name
,value
FROM V$SYSSTAT
WHERE name = 'redo log space requests'
/
PROMPT
PROMPT
PROMPT - Redo History
PROMPT ===============================================================
col thread# for 99
col size_m for 999,999,999 HEADING 'Size(MB)';
col log_c for 999,999 HEADING 'Log Count';
col log_size for 999,999,999 HEADING 'Log Size(MB)';
select a.thread#,a.log_month as month, a.log_cnt * b.log_size as size_m, a.log_cnt as log_c, b.log_size as log_size
from
(select THREAD#,to_char(first_time, 'yyyy-mm')log_month, count(*) log_cnt from v$log_history
group by thread#,to_char(first_time, 'yyyy-mm')) a,
(select thread#, avg(bytes/1048576) log_size from v$log group by thread#) b
where a.thread#=b.thread#
order by a.log_month
/
PROMPT
PROMPT
PROMPT
PROMPT ===============================================================
PROMPT = 6. Contention =
PROMPT = 6.1 Top Wait Event =
PROMPT = 6.2 SYSAUX Segmnet Usage =
PROMPT ===============================================================
PROMPT
PROMPT
PROMPT 6.1 Top Wait Event
PROMPT ===============================================================
col event for a55
select event, total_waits, time_waited
from gv$session_event
where sid = (select sid from v$mystat where rownum = 1)
order by 3 desc;
PROMPT
PROMPT
PROMPT 6.2 SYSAUX Segmnet Usage
PROMPT ===============================================================
col owner for a12
col segment_name for a40
col segment_type for a16
col "Size (MB)" for a12
select owner, segment_name, segment_type, to_char(round(bytes/1024/1024,2),'999,999.99') "Size (MB)"
from dba_segments
where tablespace_name = 'SYSAUX'
and bytes/1024/1024 > 50
order by 4
/
PROMPT
PROMPT
pause ... please press enter key ....
PROMPT
PROMPT
PROMPT ===============================================================
PROMPT = 7. RAC Status =
PROMPT = 7.1 RAC Average CR Block Receive Time =
PROMPT = 7.2 RAC Global Cache Lock Performance =
PROMPT = 7.3 RAC Object ping operation check =
PROMPT = 7.4 RAC TOP 10 FALSE PINGING OBJECTS =
PROMPT ===============================================================
PROMPT
PROMPT
PROMPT 7.1 RAC Average CR Block Receive Time
PROMPT ===============================================================
prompt
set numwidth 20
col INST_ID for 999
column "AVG CR BLOCK RECEIVE TIME (ms)" format 9999999.9
select b1.inst_id, b2.value "GCS CR BLOCKS RECEIVED",
b1.value "GCS CR BLOCK RECEIVE TIME",
((b1.value / b2.value) * 10) "AVG CR BLOCK RECEIVE TIME (ms)"
from gv$sysstat b1, gv$sysstat b2
where b1.name = 'global cache cr block receive time' and
b2.name = 'global cache cr blocks received' and b1.inst_id = b2.inst_id
or b1.name = 'gc cr block receive time' and
b2.name = 'gc cr blocks received' and b1.inst_id = b2.inst_id ;
PROMPT
PROMPT
PROMPT 7.2 RAC Global Cache Lock Performance
PROMPT ===============================================================
prompt
set numwidth 20
set lines 120
column "AVG GLOBAL LOCK GET TIME (ms)" format 9999999.9
select b1.inst_id, (b1.value + b2.value) "GLOBAL LOCK GETS",
b3.value "GLOBAL LOCK GET TIME",
(b3.value / (b1.value + b2.value) * 10) "AVG GLOBAL LOCK GET TIME (ms)"
from gv$sysstat b1, gv$sysstat b2, gv$sysstat b3
where b1.name = 'global lock sync gets' and
b2.name = 'global lock async gets' and b3.name = 'global lock get time'
and b1.inst_id = b2.inst_id and b2.inst_id = b3.inst_id
or b1.name = 'global enqueue gets sync' and
b2.name = 'global enqueue gets async' and b3.name = 'global enqueue get time'
and b1.inst_id = b2.inst_id and b2.inst_id = b3.inst_id;
PROMPT
PROMPT
PROMPT 7.3 RAC Object ping operation check
PROMPT ===============================================================
prompt
select * from gv$lock_activity;
PROMPT
PROMPT
PROMPT 7.4 RAC TOP 10 FALSE PINGING OBJECTS
PROMPT ===============================================================
prompt
set feedback on
set numwidth 8
column name format a20 tru
column kind format a10 tru
select inst_id, name, kind, file#, status, BLOCKS,
READ_PINGS, WRITE_PINGS
from (select p.inst_id, p.name, p.kind, p.file#, p.status,
count(p.block#) BLOCKS, sum(p.forced_reads) READ_PINGS,
sum(p.forced_writes) WRITE_PINGS
from gv$false_ping p, gv$datafile df
where p.file# = df.file# (+)
group by p.inst_id, p.name, p.kind, p.file#, p.status
order by sum(p.forced_writes) desc)
where rownum < 11
order by WRITE_PINGS desc;
set feedback off
PROMPT
PROMPT
PROMPT
PROMPT ===============================================================
PROMPT = 8. Etc. =
PROMPT = 8.1 Alert Log Location =
PROMPT = 8.2 Listener Status =
PROMPT ===============================================================
PROMPT
PROMPT
PROMPT 8.1 Alert Log Location
PROMPT ===============================================================
set head off
col a_log for a90 HEADING 'Alert Log';
select 'Alert Log Location : '||a.value||'/alert_'||b.instance_name||'.log' as a_log
from v$parameter a, v$instance b
where a.name ='background_dump_dest'
/
--windows
--select 'Alert Log Location : '||a.value||'\alert_'||b.instance_name||'.log' as a_log
--from v$parameter a, v$instance b
--where a.name ='background_dump_dest'
--/
PROMPT
PROMPT
PROMPT 8.2 Listener Status
PROMPT ===============================================================
host lsnrctl status
PROMPT
PROMPT
PROMPT ===============================================================
PROMPT = 9. Etc. =
PROMPT = 9.1 Online Backup Status (Hot Backup) =
PROMPT = 9.2 RMAN Backup Status =
PROMPT ===============================================================
PROMPT
PROMPT
PROMPT 9.1 Online Backup Status (Hot Backup)
PROMPT ===============================================================
set feedback on
col STATUS for a10
select file#, status, change#, to_char(TIME,'yyyy-mm-dd hh24:mi:ss') "BACKUP TIME" from v$backup
/
PROMPT
PROMPT
PROMPT 9.2 RMAN Backup Status
PROMPT ===============================================================
col type format a4
col handle format a35 trunc
col file# format 9999999
col duration format a9
select decode(BACKUP_TYPE, 'L', 'ARCH', 'D', 'DB', 'I', 'INC',
'Unknown type='||BACKUP_TYPE) TYPE,
to_char(a.start_time, 'YYYY-MM-DD HH24:MI:SS') start_time,
to_char(a.elapsed_seconds/60, '99.9')||' Min' DURATION,
substr(handle, -35) handle,
nvl(d.file#, l.sequence#) file#, nvl(d.blocks, l.blocks) blocks,
to_char(a.COMPLETION_TIME, 'YYYY-MM-DD HH24:MI:SS') end_time
from SYS.V_$BACKUP_SET a, SYS.V_$BACKUP_PIECE b,
SYS.V_$BACKUP_DATAFILE d, SYS.V_$BACKUP_REDOLOG l
where a.start_time between sysdate-1 and sysdate
and a.SET_STAMP = b.SET_STAMP
and a.SET_STAMP = d.SET_STAMP(+)
and a.SET_STAMP = l.SET_STAMP(+)
order by start_time, file#
/
set feedback off
PROMPT
PROMPT
PROMPT Report Completed Time
PROMPT ===============================================================
set head off
col date for a21 HEADING 'Report Completed Time';
select to_char(sysdate,'yyyy-mm-dd HH24:MI:SS') "date" from dual
/
set head on
set feedback on
spool off
PROMPT
PROMPT
오래전에 만들어 둔 것이라 부족한 부분이 있습니다.
뜯어보면서 수정해 가면서 쓰시면 됩니다.
최신 댓글