Oracle常用脚本

最新推荐文章于 2025-08-09 20:21:24 发布

放错位的天才

最新推荐文章于 2025-08-09 20:21:24 发布

阅读量130

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 awk

原文链接：http://www.cnblogs.com/dayu-liu/p/9908717.html

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;

转载于:https://www.cnblogs.com/dayu-liu/p/9908717.html