Oracle常用脚本
1.查看每周生成的归档量
select logtime,
count(*),
round(sum(blocks * block_size) / 1024 / 1024) mbsize
from (select trunc(first_time, 'dd') as logtime, a.BLOCKS, a.BLOCK_SIZE
from v$archived_log a
where a.DEST_ID = 1
and a.FIRST_TIME > trunc(sysdate - 7))
group by logtime
order by logtime desc;
LOGTIME COUNT(*) MBSIZE
--------- ---------- ----------
05-NOV-18 225 11465
04-NOV-18 516 26211
03-NOV-18 510 25946
02-NOV-18 508 25606
01-NOV-18 497 25330
31-OCT-18 495 25095
30-OCT-18 488 24781
29-OCT-18 483 24568
2.健康检查脚本
******************* check basic setting ******************* cat /etc/hosts ifconfig -a ifconfig -a netstat -rn |grep -i default no -a|grep -E 'ipqmaxlen|udp_sendspace|udp_recvspace|tcp_sendspace|tcp_recvspace|rfc1323|sb_max| tcp_ephemeral|udp_ephemeral' ******************* check grid ******************* su - grid id grid umask ulimit -a echo $ORACLE_HOME echo $ORACLE_BASE echo $ORACLE_SID ******************* check patches ******************* /oracle/app/12.2.0.1/grid/OPatch/opatch lspatches ocrcheck |grep "succeeded" ******************* check crs staus ******************* crsctl query css votedisk |grep -i online|awk '{print $1 " " $2 " " $5}' crsctl stat res ora -init crsctl stat res -t -init crsctl stat res crsctl stat resource ora.crf -init |grep -i STATE|grep -i online crsctl stat resource ora.cvu |grep -i STATE|grep -i online crsctl stat resource ora.qosmserver |grep -i STATE|grep -i online "crsctl stat resource -t |grep .vip|while read line do crsctl stat resource $line -f |grep STOP_DEPENDENCIES|grep intermediate|wc -l |read flag if [ ""$flag"" != 1 ];then crsctl stat resource $line -f |grep STOP_DEPENDENCIES >> /home/oracle/work/configbase/config_base46 fi done" ******************* check crs alert ******************* tail -500 /oracle/app/grid/diag/crs/`hostname`/crs/trace/alert.log ******************* check asm alert ******************* tail -500 /oracle/app/grid/diag/asm/+asm/`echo $ORACLE_SID`/trace/alert_`echo $ORACLE_SID`.log ******************* check asm setting ******************* sqlplus / as sysasm show parameter spfile select distinct TYPE from v$asm_diskgroup where name<>'SYSTEMDG' ; select group_number,os_mb/1024 d_s from v$asm_disk group by group_number,os_mb/1024; ******************* check oracle ******************* su - oracle id oracle umask ulimit -a echo $ORACLE_HOME echo $ORACLE_BASE echo $ORACLE_SID /oracle/app/oracle/product/12.2.0.1/dbhome_1/OPatch/opatch lspatches ls -al /oracle/app/oracle/product/12.2.0.1/dbhome_1/bin/oracle |awk '{print $1 " " $3 " " $4 " " $5 " " $9}' ls -l /oracle/app/12.2.0.1/grid/network/admin/listener.ora cat /oracle/app/12.2.0.1/grid/network/admin/sqlnet.ora ls -ltr /oracle/app/oracle/product/12.2.0.1/dbhome_1/network/admin/tnsnames.ora cat $ORACLE_HOME/network/admin/tnsnames.ora ******************* check db datafile autoextensible ******************* select file_name,autoextensible from dba_data_files where autoextensible='YES' union select file_name,autoextensible from dba_temp_files where autoextensible='YES') a; ******************* check tablespace ******************* SET PAGESIZE 999 SET LINESIZE 110 TTITLE LEFT "Tablespace Information" SKIP 1 - LEFT "======================================================================================" SET HEAD ON SET FEEDBACK ON BREAK ON REPORT COMPUTE SUM LABEL 'Total Spaces' OF total_m ON REPORT COMPUTE SUM LABEL 'Total Spaces' OF free_m ON REPORT COMPUTE SUM LABEL 'Total Spaces' OF used_m ON REPORT col tablespace format a25 col ext_mgt format a8 col seg_mgt format a8 col status format a7 set feedback off select b.tablespace_name tablespace, b.total_m, b.free_m, b.used_m, b.used_pct from dba_tablespaces a, (select d.tablespace_name tablespace_name, round((d.sumbytes/1024/1024),2) total_m, round(decode(f.sumbytes,null,0,f.sumbytes)/1024/1024,2) free_m, round(((d.sumbytes-decode(f.sumbytes,null,0,f.sumbytes))/1024/1024),2) used_m, round((d.sumbytes-decode(f.sumbytes,null,0,f.sumbytes))*100/d.sumbytes,2) used_pct from (select tablespace_name, sum(bytes) sumbytes from dba_free_space group by tablespace_name) f, (select tablespace_name, sum(bytes) sumbytes from dba_data_files group by tablespace_name) d where f.tablespace_name(+) = d.tablespace_name order by d.tablespace_name) b where a.tablespace_name=b.tablespace_name union all select b.tablespace_name tablespace, b.total_m, b.free_m, b.used_m, b.used_pct from dba_tablespaces a, (select d.tablespace_name tablespace_name, round((d.sumbytes/1024/1024),2) total_m, round((d.sumbytes/1024/1024),2)-round(decode(f.sumbytes,null,0,f.sumbytes)/1024/1024,2) free_m, round(decode(f.sumbytes,null,0,f.sumbytes)/1024/1024,2) used_m, round(decode(f.sumbytes,null,0,f.sumbytes)*100/d.sumbytes,2) used_pct from (select tablespace_name, sum(bytes_used) sumbytes -- sum(bytes_cached) sumbytes from v$temp_extent_pool group by tablespace_name) f, (select tablespace_name, sum(bytes) sumbytes from dba_temp_files group by tablespace_name) d where f.tablespace_name(+) = d.tablespace_name order by d.tablespace_name) b where a.tablespace_name=b.tablespace_name order by 5; TTITLE OFF ******************* check controfile ******************* sqlplus / as sysdba select count(1) from v$controlfile where status is null; ******************* check redo log ******************* select thread#,count(1) l_f_c,sum(bytes)/count(1)/1024/1024/1024 l_f_s from v$log group by thread#; ******************* check invalid objects ******************* col owner for a30 select owner,count(1) from dba_objects where status='INVALID' group by owner; ******************* check type ******************* select owner,type_name from dba_types where owner is not null and owner not in ( 'APEX_050000', 'CTXSYS', 'DBSNMP', 'DVSYS', 'GSMADMIN_INTERNAL', 'LBACSYS', 'MDSYS', 'OLAPSYS', 'ORDSYS', 'SYS', 'SYSTEM', 'WMSYS', 'XDB' ); ******************* other check ******************* col resource_name for a30 col limit for a30 select resource_name,limit from dba_profiles where profile='DEFAULT' and resource_type='PASSWORD'; select count(1) from dba_directories where directory_name='ORACLE_OCM_CONFIG_DIR2' and directory_path='/oracle/app/oracle/product/12.2.0.1/dbhome_1/ccr/state'; select owner,table_name,DEFAULT_DIRECTORY_NAME from dba_external_tables where owner<>'SYS'; select owner,table_name from dba_tab_columns where data_type='BFILE'; col SEQUENCE_OWNER for a10 col sequence_name for a10 select * from dba_sequences where sequence_name in ('IDGEN1$','AUDSES$'); col SNAP_INTERVAL for a20 col RETENTION for a20 select * from dba_hist_wr_control; col CLIENT_NAME for a30 SELECT CLIENT_NAME, STATUS FROM DBA_AUTOTASK_CLIENT WHERE CLIENT_NAME = 'auto space advisor'; ************** check osw ************** ps -ef | grep osw ************** check rman ************** su - oracle rman target / show retention policy; show controlfile autobackup; show snapshot controlfile name;
