暂无图片
暂无图片
1
暂无图片
暂无图片
暂无图片
oracle巡检脚本 自动化.txt
2467
16页
427次
2022-03-15
免费下载
oracle 巡检脚本(自动化)
转载断崖修竹 最后发布于 2017-03-03 15:24:40 阅读数 2730 收藏
展开
------------------------------------------------------------
vi /home/oracle/scripts/check/Report_oracle_inspection.sql
------------------------------------------------------------
set heading off
select '一、数据库的基本情况' from dual;
set heading off
select ' 1、数据库版本' from dual;
set heading on
select * from v$version;
set heading off
select ' 2、查看数据库基本信息' from dual;
set heading on
set linesize 500
col host_name for a20
select dbid,name,instance_name,instance_name,version,parallel rac,host_name from
v$database,v$instance;
set heading off
select ' 3、实例状态' from dual;
set heading on
select instance_number,instance_name ,status from gv$instance;
set heading off
select ' 4、内存情况' from dual;
set heading on
select * from v$sgainfo;
set heading off
select ' 5cpu 情况' from dual;
set heading on
col STAT_NAME for a20
col COMMENTS for a50
select stat_name,value,comments from v$osstat where stat_name in
('NUM_CPUS','IDLE_TIME','BUSY_TIME','USER_TIME','SYS_TIME','IOWAIT_TIME');
set heading off
select '二、检查 Oracle 对象状态' from dual;
set heading off
select ' 1、查看参数文件位置' from dual;
show parameter spfile
set heading off
col NAME for a50
select ' 2、查看控制文件' from dual;
set heading on
select status,name from v$controlfile;
set heading off
select ' 3、查看在线日志' from dual;
set heading on
col MEMBER for a50
select group#,status,type,member from v$logfile;
set heading off
select ' 4、检查日志切换频率' from dual;
set heading on
select sequence#,to_char(first_time,'yyyymmdd_hh24:mi:ss')
firsttime,round((first_time-lag(first_time) over(order by first_time))*24*60,2)
minutes from v$log_history where first_time > sysdate - 1 order by first_time
,minutes;
set heading off
select ' 5、查看数据文件' from dual;
set heading on
col NAME for a50
select name,status from v$datafile;
set heading off
select ' 6、查看失效 Objects' from dual;
set heading on
set linesize 500
select owner,object_name,object_type from dba_objects where status!='VALID' and
owner!='SYS' and owner!='SYSTEM';
set heading off
select ' 7、查看回滚段状态' from dual;
set heading on
select segment_name,status from dba_rollback_segs;
set heading off
select ' 8、检查是否有禁用约束' from dual;
set heading on
set linesize 1000
SELECT owner, constraint_name, table_name, constraint_type, status
FROM dba_constraints
WHERE status ='DISABLE' and constraint_type='P';
set heading off
select ' 9、检查是否有禁用触发器' from dual;
set heading on
col owner for a10
col taigger_name for a10
col table_name for a30
col table_name for a30
SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status =
'DISABLED';
set heading off
select ' 10Job Disable' from dual;
set heading on
select job,what,last_date,next_date,failures,broken from dba_jobs Where
schema_user='CAIKE';
set heading off
select ' 11、检查失效的索引' from dual;
set heading on
select index_name,table_name,tablespace_name,status From dba_indexes Where
owner='CTAIS2' And status<>'VALID';
set heading off
select '三、检查 Oracle 相关资源的使用情况' from dual;
set heading off
select ' 1、查看表空间的使用情况' from dual;
set heading on
set linesize 1000
SELECT UPPER(F.TABLESPACE_NAME) "tablespace_name",
D.TOT_GROOTTE_MB "tablesapce_size(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "used_tablespace_size(M)",
of 16
免费下载
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文档的来源(墨天轮),文档链接,文档作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。