Oracle DB 기본 점검 스크립트

Oracle 기본 점검 스크립트

---

column timecol new_value timestamp column spool_extension new_value suffix select to_char(sysdate,'yymmdd') timecol, '_mon.txt' spool_extension from sys.dual;   column output new_value instancename select '_' || value output from v$parameter where name = 'instance_name';   spool &&timestamp&&instancename&&suffix   alter session set sort_area_size=50000000;   prompt ********************************************************************************* prompt *                                                                               * prompt *                                   BASE  INFO                                  * prompt *                                                                               * prompt *********************************************************************************   set head off   select ' HOST_NAME     : '||HOST_NAME from v$instance; select ' Database Name : '||name from v$database; select ' Instance Name : '||INSTANCE_NAME from v$instance; select ' Global Name   : '||value from v$parameter where name='global_names'; select ' Version       : '||VERSION from v$instance; select ' Startup Time  : '||STARTUP_TIME from v$instance; select ' Archive       : '||ARCHIVER from v$instance; col comp_id for a10 col comp_Name for a30 col version for a15 set head on   SELECT substr(comp_id,1,15) comp_id, status, substr(version,1,10) version, substr(comp_name,1,30) comp_name FROM dba_registry ORDER BY 1;   set head off     PROMPT PROMPT ********************************************************************* PROMPT **     Buffer Cache Hit Ratio Statistics                           ** PROMPT **                                                                 ** PROMPT *********************************************************************   SET LINESIZE 70 COL "Get1" FORMAT 999,999,999,999,999 HEADING "Consistent Gets" COL "Get2" FORMAT 999,999,999,999,999 HEADING "DB Block Gets" COL "Get3" FORMAT 999,999,999,999,999 HEADING "Physical Reads" COL "Hit"  FORMAT 999.99              HEADING "Hit|Ratio"   select sum(decode(name, 'consistent gets', value, 0)) "Get1",        sum(decode(name, 'db block gets', value, 0)) "Get2",        sum(decode(name, 'physical reads', value, 0)) "Get3",        round((sum(decode(name, 'consistent gets', value, 0)) +        sum(decode(name, 'db block gets', value, 0)) -        sum(decode(name, 'physical reads', value, 0))) /        (sum(decode(name, 'consistent gets', value, 0)) + sum(decode(name, 'db block gets', value, 0))) * 100, 2) "Hit" from sys.v_$sysstat;   PROMPT PROMPT ********************************************************************* PROMPT **     Library Cache Hit Ratio Statistics                          ** PROMPT **                                                                 ** PROMPT ********************************************************************* SET LINESIZE 70 COL "Executions" FORMAT 999,999,999,999,999 HEADING "Executions" COL "EH"        FORMAT 999,999,999,999,999 HEADING "Executions Hits" COL "Hit1"       FORMAT 999.99              HEADING "Hit|Ratio" COL "Misses"     FORMAT 999,999,999         HEADING "Misses" COL "Hit2"       FORMAT 999.99              HEADING "Hit|Ratio"   select sum(pins) "Executions", sum(pinhits) "EH",        round((sum(pinhits) / sum(pins)) * 100, 3) "Hit1",        sum(reloads) "Misses",        round((sum(pins) / (sum(pins) + sum(reloads))) * 100, 3) "Hit2"   from sys.v_$librarycache;     PROMPT PROMPT ********************************************************************* PROMPT **     Data Dictionary Cache Hit Ratio Statistics                  ** PROMPT *********************************************************************   SET LINESIZE 45 COL "Gets"  FORMAT 999,999,999,999,999 HEADING "Gets" COL "Cache" FORMAT 999,999,999,999     HEADING "Cache Missess" COL "Hit"   FORMAT 999.99               HEADING "Hit|Ratio"   select sum(gets) "Gets", sum(getmisses) "Cache",        round((1 - (sum(getmisses) / sum(gets))) * 100, 2) "Hit"   from sys.v_$rowcache;   PROMPT PROMPT ********************************************************************* PROMPT **     Sort Statistics                                               ** PROMPT **                                                                 ** PROMPT *********************************************************************   SET LINESIZE 80 COL name  FORMAT A30             HEADING "Sort Parameter" COL value FORMAT A30                 HEADING "Value"   select name, value   from v$parameter  where name like '%sort%';   PROMPT ********************************************************************* PROMPT **   Sort Ratio 가 < 1% 이하                                       ** PROMPT *********************************************************************   SET LINESIZE 60 COL Disk  FORMAT 999,999,999,999,999 COL Mem   FORMAT 999,999,999,999,999 COL Ratio FORMAT 999.99 HEADING "Sort|Ratio"   select disk.value Disk, mem.value Mem,        round((1 - (mem.value )/(mem.value+disk.value))*100,2) Ratio   from v$sysstat mem, v$sysstat disk  where mem.name = 'sorts (memory)'    and disk.name = 'sorts (disk)';   PROMPT PROMPT PROMPT ********************************************************************* PROMPT **     Tablespace Statistics                                       ** PROMPT **                                                                 ** PROMPT *********************************************************************   SET LINESIZE 120 COL tablespace_name FORMAT A20            HEADING "Tablespace Name" COL "Bytes"         FORMAT 999,999,999.99 HEADING "TBS Size(MB)" COL "Free"          FORMAT 999,999.99     HEADING "Free Size(MB)" COL "Percent"       FORMAT 999.99         HEADING "Usage(%)" COL "Used"          FORMAT 999,999,999.99 HEADING "Used(MB)" COL "FreeP"         FORMAT 999.99         HEADING "Free(%)"   select * from (  select ddf.tablespace_name,        ddf.bytes/1024/1024 "Bytes",        dfs.bytes/1024/1024 "Free",        round(((ddf.bytes - dfs.bytes) / ddf.bytes) * 100, 2) "Percent" from   ( select tablespace_name, sum(bytes) bytes          from   dba_data_files          group by tablespace_name) ddf,        ( select tablespace_name, sum(bytes) bytes          from   dba_free_space          group by tablespace_name) dfs where  ddf.tablespace_name = dfs.tablespace_name order by ((ddf.bytes-dfs.bytes)/ddf.bytes) desc) where rownum < 6 ;     PROMPT PROMPT PROMPT ********************************************************************* PROMPT **     Datafile I/O Statistics                                     ** PROMPT **                                                                 ** PROMPT ********************************************************************* PROMPT PROMPT     set lines 120   col File_name       form a52        heading 'File name' col tablespace_name form a20        heading 'Tablespace'         col br              form 99,999,999 heading 'BLK Read' col bw              form 99,999,999 heading 'BLK Write' col Weight          form 999.99     heading 'Weight|(%)'   select * from ( select tablespace_name,file_name,        round((phyblkrd+phyblkwrt*1.2)/z.sum*100, 2) "Weight"   from dba_data_files df,        v$filestat     fs,        (select sum(phyblkrd+phyblkwrt*1.2) as sum from v$filestat) z  where df.file_id = fs.file#   order by 3 desc) where rownum<6 ;       PROMPT PROMPT ********************************************************************* PROMPT **     Object Extents > 20                                         ** PROMPT *********************************************************************   col Owner       form a9 col Object_name form a30 col Type        form a1 trunc col Tablespace  form a12 col Size(KB)    form 999,999,999 col Init(KB)    form 999,999 col Next(KB)    form 999,999 col Ext         form 999,999   col Owner       form a9 col Object_name form a30 col Type        form a1 trunc col Tablespace  form a15 col Size(KB)    form 999,999,999 col Init(KB)    form 999,999 col Next(KB)    form 999,999 col Ext         form 999,999 select * from ( select  Owner,         segment_name Object_name,         segment_type Type,         tablespace_name Tablespace ,         Bytes/1024 "Size(KB)",         initial_extent/1024 "Init(KB)",         next_extent/1024 "Next(KB)",         extents Ext   from dba_segments  where owner != 'SYS' and owner != 'SYSTEM'    and extents > 20  order by 8 desc, 1,2,3,4) where rownum < 6;   PROMPT PROMPT PROMPT ********************************************************************* PROMPT **     Tablespace SIZE                                     ** PROMPT **                                                                 ** PROMPT ********************************************************************* PROMPT PROMPT   set head on   set trimspool on column totalsize heading "total size(M)" column usedsize heading "used size(M)" column freesize heading "free size(M)" column tablespace_name format a20 column totalsize format 999,999.9 column usedsize format 999,999.9 column freesize format 999,999.9 column used_percent format 999.9     select a.tablespace_name, a.totalsize,        nvl(b.usedsize,0) usedsize,nvl(round(((b.usedsize/a.totalsize)*100),1),0) Used_Percent,c.freesize   from ( select tablespace_name, sum(bytes)/1024/1024 totalsize            from dba_data_files           group by tablespace_name ) a,        ( select tablespace_name, sum(bytes)/1024/1024 usedsize            from dba_segments           group by tablespace_name ) b,        ( select tablespace_name, sum(bytes)/1024/1024 freesize            from dba_free_space           group by tablespace_name ) c  where a.tablespace_name = b.tablespace_name(+)    and a.tablespace_name = c.tablespace_name(+) order by 1;   set head off     prompt ******************************************************* prompt *                                                     * prompt *        Cache Fusion For  RAC prompt *                                                     * prompt ******************************************************* prompt   set linesize 100 col name format a16 col kind format a10 col partition_name format a10   column partition_name for a15 select inst_id,name, substr(kind,1,10) kind, partition_name,        sum(xnc) xnc,sum(FORCED_READS) reads, sum(FORCED_writes) writes        from gv$cache_transfer        group by inst_id,name, kind, partition_name        having sum(xnc) > 1        order by 1,4 ; prompt prompt ************************************************************* prompt * RESOURCE                                                  * prompt ************************************************************* prompt                                   set head on   col RESOURCE_NAME for a10 col CURRENT_UTILIZATION for 9999 col MAX_UTILIZATION for 9999 col INITIAL_ALLOCATION for a10 col LIMIT_VALUE for a10   select * from v$resource_limit where resource_name in ('processes', 'sessions');   set head off   Prompt prompt 2. Data file total size   select ' TOTAL DATA FILE SIZE  : '||sum(bytes)/1024/1024||' MB' FROM v$datafile union SELECT ' USED SIZE             : '||sum(bytes)/1024/1024||' MB' FROM dba_segments union SELECT ' FREE SIZE             : '||sum(bytes)/1024/1024||' MB' FROM dba_free_space; prompt set head on     prompt prompt 3. Backup check   col time for a80 select 'Hot Backup : '||to_char(min(b.time),'yyyy-mm-dd hh24:mi:ss')||' ~ '||to_char(max(b.time),'yyyy-mm-dd hh24:mi:ss') Time from v$backup b, v$datafile d where b.file#=d.file# and b.status = 'NOT ACTIVE';   select * from v$backup b, v$datafile d where b.file#=d.file# and b.status != 'NOT ACTIVE';   set head off   prompt  prompt  prompt  prompt ************************************************************* prompt *   NEXT CHECK                                              * prompt ************************************************************* prompt    prompt alert log and trace file check show parameter dump prompt prompt archive log list archive log list; Prompt   prompt  prompt  prompt  prompt ************************************************************* prompt *   NLS_CHARACTERSET                                              * prompt ************************************************************* prompt    prompt select * from NLS_DATABASE_PARAMETERS where parameter = 'NLS_CHARACTERSET'; Prompt   prompt  prompt  prompt  prompt ************************************************************* prompt *   NLS_CHARACTERSET                                              * prompt ************************************************************* prompt    prompt Select * from v$version; Prompt   prompt ************************************************************* prompt *                                                  * prompt *************************************************************   select name, state, type, total_mb, free_mb from v$asm_diskgroup; spool off