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 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;