Regular Manual Check

lsnrctl status
tnsping $ORACLE_SID

sqlplus / as sysdba
set lines 300;
column HOST format a7;
SELECT instance_name, database_status, status, host_name FROM v$instance;
exit


more $HOME/saptrace/background/alert_$ORACLE_SID.log
more /oracle/$ORACLE_SID/102_64/network/log/sqlnet.log

Ready

cd /mnt/play/systec/项目/中烟集团

# develop
scp health_check.sh orabid@172.16.33.145:/tmp/systec/
scp health_check.sh orabip@172.16.33.146:/tmp/systec/
scp health_check.sh oracrd@172.16.33.149:/tmp/systec/
scp health_check.sh oracrp@172.16.33.150:/tmp/systec/
scp health_check.sh oraecd@172.16.33.137:/tmp/systec/
scp health_check.sh oraecp@172.16.33.138:/tmp/systec/
scp health_check.sh oraepd@172.16.33.151:/tmp/systec/
scp health_check.sh oraepq@172.16.33.154:/tmp/systec/
scp health_check.sh orasrd@172.16.33.153:/tmp/systec/
scp health_check.sh orasrp@172.16.33.152:/tmp/systec/
scp health_check.sh oraxid@172.16.33.139:/tmp/systec/
scp health_check.sh oraxip@172.16.35.34:/tmp/systec/

# Production

scp health_check.sh orabip@172.16.33.141:/tmp/systec/
scp health_check.sh oracrp@172.16.33.158:/tmp/systec/
scp health_check.sh oraecp@172.16.33.131:/tmp/systec/
scp health_check.sh oraxip@172.16.33.132:/tmp/systec/
scp health_check.sh oraepp@172.16.33.168:/tmp/systec/
scp health_check.sh orasrp@172.16.33.164:/tmp/systec/
scp health_check.sh orabpp@172.16.33.180:/tmp/systec/

System Check

telnet SERVER_IP

USE ansible

# Check database status

export TERM=xterm
ansible GDZY_DEV -m raw -a "lsnrctl status"
ansible GDZY_PRO -m raw -a "lsnrctl status"
ansible GDZY_DEV -m raw -a "echo 'set linesize 200;\n select instance_name,database_status,status,host_name from v\$instance;' | sqlplus / as sysdba" | egrep --after-context=2 ' STATUS'
ansible GDZY_PRO -m raw -a "echo 'set linesize 200;\n select instance_name,database_status,status,host_name from v\$instance;' | sqlplus / as sysdba" | egrep --after-context=2 ' STATUS'

ansible GDZY_DEV -m raw -a "echo 'set linesize 200;\n select instance_name,database_status,status,host_name from v\$instance;' | sqlplus / as sysdba | sed -n '1,14p'"
ansible GDZY_PRO -m raw -a "echo 'set linesize 200;\n select instance_name,database_status,status,host_name from v\$instance;' | sqlplus / as sysdba | sed -n '1,14p'"



grep -A 4 "ORA-" aler*.log | grep -v "ORA-" | grep -v '^\d*$'

# stop database

ansible GDZY_DEV -m raw -a "echo 'set linesize 200;\n shutdown immediate;' | sqlplus / as sysdba"
ansible GDZY_DEV -m raw -a "lsnrctl stop"
ansible GDZY_PRO -m raw -a "echo 'set linesize 200;\n shutdown immediate;' | sqlplus / as sysdba"
ansible GDZY_PRO -m raw -a "lsnrctl stop"

# start database

ansible GDZY_DEV -m raw -a "lsnrctl start"
ansible GDZY_PRO -m raw -a "lsnrctl start"
ansible GDZY_DEV -m raw -a "echo 'set linesize 200;\n startup;' | sqlplus / as sysdba"
ansible GDZY_PRO -m raw -a "echo 'set linesize 200;\n startup;' | sqlplus / as sysdba"

time sh /tmp/health_check.sh | tee /tmp/`hostname`_info_`date +%F`.txt

# New
time sh /tmp/systec/health_check.sh | tee /tmp/systec/`hostname`_info_`date +%F`.txt

scp merlyn@10.160.186.75:/tmp/check_oracle.sql /tmp/

setenv SERVER_IP `netstat -in | sed -n '2p' | awk '{ print $4 }'`

# create file hostname_info, Collect system info
touch /tmp/`hostname`_info_`date +%F`.txt

echo "Network info"
echo "========================================================================================"
netstat -ni >> /tmp/`hostname`_info_`date +%F`.txt

echo "Machinfo info"
echo "========================================================================================"
machinfo >> /tmp/`hostname`_info_`date +%F`.txt

echo "vmstat info"
echo "========================================================================================"
vmstat 1 10

echo "========================================================================================"
echo "iostat info"
iostat 1 10

echo "========================================================================================"
echo "Listener info"
lsnrctl status >> /tmp/`hostname`_info_`date +%F`.txt
tnsping $ORACLE_SID >> /tmp/`hostname`_info_`date +%F`.txt

echo "Package it"
echo "========================================================================================"
sqlplus / as sysdba @/tmp/check_oracle.sql >> /tmp/`hostname`_info_`date +%F`.txt

find $ORACLE_HOME/network/admin -name "*t*n*e?.ora" | xargs tar -cvf /tmp/`hostname`_Oracle_config_`date +%F`.tar
find $ORACLE_HOME/dbs/ -name "init$ORACLE_SID.ora" | xargs tar -rvf /tmp/`hostname`_Oracle_config_`date +%F`.tar

find $ORACLE_HOME/ -name "alert_$ORACLE_SID.log" | xargs tar -rvf /tmp/`hostname`_Oracle_config_`date +%F`.tar
find $HOME/saptrace/background -name "alert_$ORACLE_SID.log" | xargs tar -rvf /tmp/`hostname`_Oracle_config_`date +%F`.tar

tar -rvf /tmp/`hostname`_Oracle_config_`date +%F`.tar /tmp/`hostname`_info_`date +%F`.txt

scp /tmp/`hostname`_Oracle_config_`date +%F`.tar merlyn@172.16.34.252:~

Database status

sqlplus / as sysdba
set lines 200;
column HOST format a7;
SELECT     inst_id,
           instance_number inst_no,
           instance_name inst_name,
           parallel,
           status,
           database_status db_status,
           active_state state,
           host_name host
FROM       gv$instance
ORDER BY   inst_id;

==================================================================

sqlplus / as sysdba
set lines 200;
column HOST format a7;
SELECT instance_name, database_status, status, host_name FROM v$instance;

@?/rdbms/admin/awrrpt.sql

show tablespace usages status

set pages 999
set lines 400
SELECT df.tablespace_name tablespace_name,
 max(df.autoextensible) auto_ext,
 round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
 round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
 round(df.bytes / (1024 * 1024), 2) curr_ts_size,
 round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
 round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
 round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
 nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM dba_free_space fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
 max(autoextensible) autoextensible
 from dba_data_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
UNION ALL
SELECT df.tablespace_name tablespace_name,
 max(df.autoextensible) auto_ext,
 round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
 round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
 round(df.bytes / (1024 * 1024), 2) curr_ts_size,
 round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
 round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
 round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
 nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM (select tablespace_name, bytes_used bytes
 from V$temp_space_header
 group by tablespace_name, bytes_free, bytes_used) fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
 max(autoextensible) autoextensible
 from dba_temp_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
ORDER BY 4 DESC;

TABLESPACE_NAME: This is the Tablespace Name.

AUTO_EXT: If the datafiles are ‘Auto Extendable’ or not.

Please Note: This is using a max function, so if all are ‘NO’, then the ‘NO’ is true for all datafiles, however if one is ‘YES’, then the ‘YES’ is possible for one through to all of the datafiles.

MAX_TS_SIZE: This is the maximum Tablespace Size if all the datafile reach their max size.

MAX_TS_PCT_USED: This is the percent of MAX_TS_SIZE reached and is the most important value in the query, as this reflects the true usage before DBA intervention is required.

CURR_TS_SIZE: This is the current size of the Tablespace.

USED_TS_SIZE: This is how much of the CURR_TS_SIZE is used.

TS_PCT_USED: This is the percent of CURR_TS_SIZE which if ‘Auto Extendable’ is on, is a little meaningless. Use MAX_TS_PCT_USED for actual usage.

FREE_TS_SIZE: This is how much is free in CURR_TS_SIZE.

TS_PCT_FREE: This is how much is free in CURR_TS_SIZE as a percent.

Please Note: All sizes are in Megabytes, this can be changed to Gigabytes by added a ‘/1024’ to the columns.

set linesize 300
SELECT upper(f.tablespace_name) "tablespace_name",
       d.Tot_grootte_Mb "tablespace(M)",
       d.Tot_grootte_Mb - f.total_bytes "used(M)",
       round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2) "use%",
       f.total_bytes "free_space(M)",
       round(f.total_bytes / d.Tot_grootte_Mb * 100,2) "free%"
 FROM      
    (SELECT tablespace_name,
            round(SUM(bytes)/(1024*1024),2) total_bytes,
            round(MAX(bytes)/(1024*1024),2) max_bytes
      FROM sys.dba_free_space
     GROUP BY tablespace_name) f,
    (SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb
      FROM   sys.dba_data_files dd
      GROUP BY dd.tablespace_name) d
WHERE d.tablespace_name = f.tablespace_name    
ORDER BY 4 DESC
/

• select tablespace_name, maxbytes/1024/1024 MAX_SIZE from dba_data_files;
• SELECT NAME from v$datafile;

Displays Space Usage for Each Datafile

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
COLUMN "Tablespace Name" FORMAT A20
COLUMN "File Name" FORMAT A80
 
SELECT  Substr(df.tablespace_name,1,20) "Tablespace Name",
        Substr(df.file_name,1,80) "File Name",
        Round(df.bytes/1024/1024,0) "Size (M)",
        decode(e.used_bytes,NULL,0,Round(e.used_bytes/1024/1024,0)) "Used (M)",
        decode(f.free_bytes,NULL,0,Round(f.free_bytes/1024/1024,0)) "Free (M)",
        decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,0)) "% Used"
FROM    DBA_DATA_FILES DF,
       (SELECT file_id,
               sum(bytes) used_bytes
        FROM dba_extents
        GROUP by file_id) E,
       (SELECT Max(bytes) free_bytes,
               file_id
        FROM dba_free_space
        GROUP BY file_id) f
WHERE    e.file_id (+) = df.file_id
AND      df.file_id  = f.file_id (+)
ORDER BY df.tablespace_name,
         df.file_name
/

database size

select
( select sum(bytes)/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size from v$controlfile) "Size in MB"
from
dual;

Shared Pool Size hit ratio

select round((sum(gets)-sum(reloads))/sum(gets)*100,1) "libiary cache hit ratio %"
from v$librarycache where namespace
in ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER');

data dictionary hit ratio

select round((1-sum(getmisses)/sum(gets))*100,1) "data dictionary hit ratio %"
from v$rowcache;

锁竞争

select  substr(ln.name,1,25) Name, 
        l.gets, l.misses, 
        100*(l.misses/l.gets) "% Ratio (STAY UNDER 1%)" 
from v$latch l, v$latchname ln 
where ln.name in ('cache buffers lru chain') 
and ln.latch# = l.latch#;

排序命中率

select a.value "Sort(Disk)", b.value "Sort(Memory)",
round(100*(a.value/decode((a.value+b.value), 0,1,(a.value+b.value))),2) "% Ratio (STAY UNDER 5%)"  
from v$sysstat a, v$sysstat b
where a.name = 'sorts (disk)'
and b.name = 'sorts (memory)';

数据缓冲区命中率

select round((1-(phy.value/(cur.value+con.value)))*100,1)||'%' ratio 
from v$sysstat phy,v$sysstat cur,v$sysstat con
where phy.name='physical reads' and cur.name='db block gets' and con.name='consistent gets';

v$session_wait

select sid,seq#,event,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where event not like 'SQL%'  and event not like 'rdbms%';

回滚段的争用情况

select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;

无效对象情况

col OBJECT_NAME for a36
SELECT object_name, object_type,status FROM dba_objects WHERE status like 'INVALID';

• Recompiling All Invalid Objects

SQL> @Oracle_home/rdbms/admin/utlrp.sql

检查碎片

 SELECT a.tablespace_name,
         TRUNC (
              SQRT (MAX (blocks)/SUM (blocks))
            * (100/SQRT (SQRT (COUNT (blocks)))),
            2)
            fsfi
    FROM dba_free_space a, dba_tablespaces b
   WHERE     a.tablespace_name = b.tablespace_name
         AND b.contents NOT IN ('TEMPORARY', 'UNDO', 'SYSAUX')
GROUP BY A.tablespace_name
ORDER BY fsfi;