|
Size: 9854
Comment:
|
Size: 10272
Comment:
|
| Deletions are marked like this. | Additions are marked like this. |
| Line 288: | Line 288: |
== 检查碎片 == {{{ 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; }}} |
Contents
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;