Oracle运维脚本-巡检单机版

mahan

巡检脚本如下：

一.数据库基本状态和信息            


-----1 数据库信息
select * from v$version;
select dbid,name,created,log_mode from v$database;
select version,instance_name,startup_time,status from v$instance;
---数据库运行时间
select to_char(startup_time, 'YYYY-MM-DD HH24:MI:SS') 启动时间,
       TRUNC(sysdate - (startup_time)) || '天 ' ||
       TRUNC(24 *
             ((sysdate - startup_time) - TRUNC(sysdate - startup_time))) ||
       '小时 ' || MOD(TRUNC(1440 * ((SYSDATE - startup_time) -
                          TRUNC(sysdate - startup_time))),
                    60) || '分 ' ||
       MOD(TRUNC(86400 *
                 ((SYSDATE - STARTUP_TIME) - TRUNC(SYSDATE - startup_time))),
           60) || '秒' 运行时间
  from v$instance;
二．数据库参数信息


---常见参数：
---v$parameter ---session
---v$system_parameter ---system
select name, value, display_value, isdefault
  from v$system_parameter
where name in ('audit_trail',
                'cluster_database_instances',
                'cpu_count',
                'cursor_sharing',
                'db_name',
                'db_recovery_file_dest_size',
                'deferred_segment_creation',
                'disk_asynch_io',
                'event',
                'enable_ddl_logging',
                'filesystemio_options',
                'instance_name',
                'instance_number',
                'job_queue_processes',
                'log_archive_dest_1',
                'log_archive_format',
                'memory_max_target',
                'memory_target',
                'nls_language',
                'optimizer_dynamic_sampling',
                'optimizer_index_cost_adj',
                'processes',
                'parallel_force_local',
                'parallel_max_servers',
                'pga_aggregate_target',
                'query_rewrite_enabled',
                'sec_case_sensitive_logon',
                'sessions',
                'sga_max_size',
                'sga_target',
                'utl_file_dir',
                'undo_management',
                'undo_retention',
                'undo_tablespace')
order by 1;
---常见隐含参数
SELECT x.ksppinm  as name,
       y.ksppstvl as value,
       y.ksppstdf as isdefault,
       x.ksppdesc describ
  FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.inst_id = USERENV('Instance')
   AND y.inst_id = USERENV('Instance')
   AND x.indx = y.indx
   AND x.ksppinm in ('_allow_resetlogs_corruption',
                     '_b_tree_bitmap_plans',
                     '_b_tree_bitmap_plans',
                     '_corrupted_rollback_segments',
                     '_datafile_write_errors_crash_instance',
                     '_gc_policy_time',
                     '_gc_undo_affinity',
                     '_hash_join_enabled',
                     '_offline_rollback_segments',
                     '_memory_imm_mode_without_autosga',
                     '_optim_peek_user_binds',
                     '_optimizer_mjc_enabled',
                     '_optimizer_use_feedback',
                     '_optimizer_join_elimination_enabled',
                     '_optimizer_ads_use_result_cache',
                     '_optimizer_adaptive_plans',
                     '_optimizer_aggr_groupby_elim',
                     '_optimizer_reduce_groupby_key',
                     '_optimizer_cost_based_transformation',
                     '_use_adaptive_log_file_sync',
                     '_undo_autotune')
order by 1;
三．.数据库对象和备份信息


---3 对象信息
---数据库大小
select owner, trunc(sum(bytes) / 1024 / 1024 / 1024,2) as db_GB
  from dba_segments
where owner in ('CJC','CHEN')
group by owner
order by 1;
---表数量
select owner, count(*)
  from dba_tables
where owner in ('CJC','CHEN')
group by owner
order by 1;
---临时表数量
select owner, count(*)
  from dba_tables
where owner in ('CJC','CHEN')
   and temporary = 'Y'
group by owner
order by 1;
---索引信息
select owner, count(*)
  from dba_indexes
where owner in ('CJC','CHEN')
group by owner
order by 1;
---视图数量
select owner, count(*)
  from dba_views
where owner in ('CJC','CHEN')
group by owner
order by 1;
---触发器信息
select owner, count(*)
  from dba_triggers
where owner in ('CJC','CHEN')
group by owner
order by 1;
---存储过程
select owner, count(*)
  from dba_procedures
where owner in ('CJC','CHEN')
group by owner
order by 1;
---无效的对象
select *
  from dba_objects
where status = 'INVALID'
   and owner in
       ('CJC','CHEN');
---JOB和定时任务
SELECT * from dba_jobs;
[oracle@cjcos01 ~]$ crontab -l
---RMAN备份信息
select to_char(start_time, 'yyyy-mm-dd') start_time,
       to_char(start_time, 'day') day,
       round(sum(OUTPUT_BYTES) / 1024 / 1024 / 1024, 2) SIZE_GB
  from v$backup_set_details
group by to_char(start_time, 'yyyy-mm-dd'), to_char(start_time, 'day')
order by start_time desc;
---
select to_char(start_time, 'yyyy-mm-dd') start_time,
       to_char(start_time, 'day') day,
       round(sum(BYTES) / 1024 / 1024 / 1024, 2) SIZE_GB
  from v$backup_piece
where handle is not null
group by to_char(start_time, 'yyyy-mm-dd'), to_char(start_time, 'day')
order by start_time desc;
---大表
select sum(bytes)/1024/1024/1024 from dba_segments where owner='CJC' and segment_name='T1';
select sum(bytes) / 1024 / 1024 / 1024
  from dba_segments
where owner = 'CJC'
   and segment_name in
       (select segment_name
          from dba_lobs
         where owner = 'CJC'
           and table_name = 'T1');
select  0.09375+221.677734375 from dual;
四.数据库文件信息


---表空间信息
select tablespace_name,
       block_size,
       initial_extent,
       next_extent,
       max_size,
       status,
       contents,
       logging,
       extent_management,
       segment_space_management
  from dba_tablespaces;
---41数据文件信息
select tablespace_name,
       file_name,
       file_id,
       status,
       trunc(bytes / 1024 / 1024 / 1024, 2) as FILE_GB,
       autoextensible,
       trunc(maxbytes / 1024 / 1024 / 1024, 2) as MAX_GB
  from dba_data_files
order by file_id;
---4.2临时文件信息
select tablespace_name,
       file_name,
       file_id,
       status,
       trunc(bytes / 1024 / 1024 / 1024, 2) as FILE_GB,
       autoextensible,
       trunc(maxbytes / 1024 / 1024 / 1024, 2) as MAX_GB
  from dba_temp_files
order by file_id;
---4.3控制文件信息
select * from v$controlfile;
---查看控制文件内容
alter database backup controlfile to trace as '/home/oracle/20200614.ctl';
---4.4日志文件信息
select a.group#,
       b.member,
       a.members,
       a.status,
       a.sequence#,
       bytes / 1024 / 1024 as file_mb
  from v$log a, v$logfile b
where a.group# = b.group#
order by 1, 2;
---4.5回滚段
---select * from v$rollname;
select owner, tablespace_name, segment_id, segment_name, status
  from dba_rollback_segs
where status = 'ONLINE';
---4.6归档频率
----查看数据库归档分布及频率
SELECT TRUNC(first_time) "Date",
       TO_CHAR(first_time, 'Dy') "Day",
       COUNT(1) "Total",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '00', 1, 0)) "h0",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '01', 1, 0)) "h1",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '02', 1, 0)) "h2",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '03', 1, 0)) "h3",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '04', 1, 0)) "h4",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '05', 1, 0)) "h5",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '06', 1, 0)) "h6",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '07', 1, 0)) "h7",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '08', 1, 0)) "h8",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '09', 1, 0)) "h9",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '10', 1, 0)) "h10",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '11', 1, 0)) "h11",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '12', 1, 0)) "h12",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '13', 1, 0)) "h13",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '14', 1, 0)) "h14",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '15', 1, 0)) "h15",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '16', 1, 0)) "h16",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '17', 1, 0)) "h17",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '18', 1, 0)) "h18",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '19', 1, 0)) "h19",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '20', 1, 0)) "h20",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '21', 1, 0)) "h21",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '22', 1, 0)) "h22",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '23', 1, 0)) "h23",
       ROUND(COUNT(1) / 24, 2) "Avg"
  FROM gv$log_history
WHERE thread# = inst_id
GROUP BY TRUNC(first_time), TO_CHAR(first_time, 'Dy')
ORDER BY 1 desc;
五．数据库连接信息



select schemaname, count(*)
  from v$session
where schemaname in
       ('CJC','CHEN')
group by schemaname
order by 1;
六.数据库资源限制

select   *   from  v$resource_limit ;

七．数据库权限


---角色
select *
  from dba_role_privs
where grantee in
       ('CJC','CHEN','SCOTT')
order by 1, 2;
---系统权限
select *
  from dba_sys_privs
where grantee in
       ('CJC','CHEN','SCOTT')
order by 1, 2;
---表权限
select * from dba_tab_privs where OWNER IN ('CJC', 'CHEN', 'SCOTT');
---列权限
select * from dba_col_privs where OWNER IN ('CJC','CHEN','SCOTT');
八．数据库高水位


---高水位
---1 查看表统计信息
---2
SELECT owner,
       table_name,
       ROUND((blocks * 8), 2) "高水位空间(KB)",
       ROUND((num_rows * avg_row_len / 1024), 2) "真实使用空间(KB)",
       ROUND((blocks * 10 / 100) * 8, 2) "预留空间(pctfree)KB",
       ROUND((blocks * 8 - (num_rows * avg_row_len / 1024) -
             blocks * 8 * 10 / 100),
             2) "浪费空间(KB)",
       ROUND((ROUND((blocks * 8 - (num_rows * avg_row_len / 1024) -
                    blocks * 8 * 10 / 100),
                    2) / ROUND((blocks * 8), 2)) * 100,
             2) || '%' "浪费空间%"
  FROM dba_tables
where owner in
       ('CJC','CHEN','SCOTT')
   and temporary = 'N'
   and num_rows > 0
   ---and ROUND((blocks * 8), 2) >= 100000
ORDER BY 1, 3 desc;
---3 整理表碎片
/*exec dbms_stats.gather_schema_stats(ownname=>'chenjch',cascade=> true);
整理表碎片
(1)导出表，删除表，再导入表
(2)
alter table t1 move;  ---索引状态UNUSABLE
select * from t1;
create index i_t1_id on t1(id);
select * from t1 where id=1;
select \*+index(t1 i_t1_id)*\ * from t1 where id=1;---ORA-01502
alter index i_t1_id rebuild;
(3)整理表，不影响DML操作
alter table t3 enable ROW MOVEMENT;--启动行移动功能
alter table t3 shrink space compact;  --只整理碎片 不回收空间
alter table t3 disable ROW MOVEMENT;
(4)重置高水位，此时不能有DML操作
alter table t3 enable ROW MOVEMENT;
alter table t3 shrink space; --整理碎片并回收空间，并调整水位线。业务少时执行
alter table t3 disable ROW MOVEMENT;--关闭行移动*/
九.性能

9.1  TOP SQL信息


---elapsed_time
select a.*, elapsed_seconds / executions elapsed_per
  from (select sql_text,
               ---sql_fulltext,
               sql_id,
               round(elapsed_time / 1000000, 2) elapsed_seconds,
               executions,
               buffer_gets,
               disk_reads
          from (select * from v$sql order by elapsed_time desc)
         where rownum <= 100) a
where executions > 0
order by elapsed_per desc;
9.2   等待事件


---V$SYSTEM_EVENT
---v$session_wait
SELECT event,
       total_waits    waits,
       total_timeouts timeouts,
       time_waited    total_time, ---in hundredths of a second
       average_wait   avg
  FROM V$SYSTEM_EVENT
ORDER BY 5 DESC;
9.3 数据库各时间段负载查询



select s.snap_date,
       decode(s.redosize, null, '--shutdown or end--', s.currtime) "TIME",
       to_char(round(s.seconds / 60, 2)) "elapse(min)",
       round(t.db_time / 1000000 / 60, 2) "DB time(min)",
       s.redosize redo,
       round(s.redosize / s.seconds, 2) "redo/s",
       s.logicalreads logical,
       round(s.logicalreads / s.seconds, 2) "logical/s",
       physicalreads physical,
       round(s.physicalreads / s.seconds, 2) "phy/s",
       s.executes execs,
       round(s.executes / s.seconds, 2) "execs/s",
       s.parse,
       round(s.parse / s.seconds, 2) "parse/s",
       s.hardparse,
       round(s.hardparse / s.seconds, 2) "hardparse/s",
       s.transactions trans,
       round(s.transactions / s.seconds, 2) "trans/s"
  from (select curr_redo - last_redo redosize,
               curr_logicalreads - last_logicalreads logicalreads,
               curr_physicalreads - last_physicalreads physicalreads,
               curr_executes - last_executes executes,
               curr_parse - last_parse parse,
               curr_hardparse - last_hardparse hardparse,
               curr_transactions - last_transactions transactions,
               round(((currtime + 0) - (lasttime + 0)) * 3600 * 24, 0) seconds,
               to_char(currtime, 'yy/mm/dd') snap_date,
               to_char(currtime, 'hh24:mi') currtime,
               currsnap_id endsnap_id,
               to_char(startup_time, 'yyyy-mm-dd hh24:mi:ss') startup_time
          from (select a.redo last_redo,
                       a.logicalreads last_logicalreads,
                       a.physicalreads last_physicalreads,
                       a.executes last_executes,
                       a.parse last_parse,
                       a.hardparse last_hardparse,
                       a.transactions last_transactions,
                       lead(a.redo, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_redo,
                       lead(a.logicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_logicalreads,
                       lead(a.physicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_physicalreads,
                       lead(a.executes, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_executes,
                       lead(a.parse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_parse,
                       lead(a.hardparse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_hardparse,
                       lead(a.transactions, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_transactions,
                       b.end_interval_time lasttime,
                       lead(b.end_interval_time, 1, null) over(partition by b.startup_time order by b.end_interval_time) currtime,
                       lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) currsnap_id,
                       b.startup_time
                  from (select snap_id,
                               dbid,
                               instance_number,
                               sum(decode(stat_name, 'redo size', value, 0)) redo,
                               sum(decode(stat_name,
                                          'session logical reads',
                                          value,
                                          0)) logicalreads,
                               sum(decode(stat_name,
                                          'physical reads',
                                          value,
                                          0)) physicalreads,
                               sum(decode(stat_name, 'execute count', value, 0)) executes,
                               sum(decode(stat_name,
                                          'parse count (total)',
                                          value,
                                          0)) parse,
                               sum(decode(stat_name,
                                          'parse count (hard)',
                                          value,
                                          0)) hardparse,
                               sum(decode(stat_name,
                                          'user rollbacks',
                                          value,
                                          'user commits',
                                          value,
                                          0)) transactions
                          from dba_hist_sysstat
                         where stat_name in
                               ('redo size',
                                'session logical reads',
                                'physical reads',
                                'execute count',
                                'user rollbacks',
                                'user commits',
                                'parse count (hard)',
                                'parse count (total)')
                         group by snap_id, dbid, instance_number) a,
                       dba_hist_snapshot b
                 where a.snap_id = b.snap_id
                   and a.dbid = b.dbid
                   and a.instance_number = b.instance_number
                 order by end_interval_time)) s,
       (select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time,
               lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) endsnap_id
          from dba_hist_sys_time_model a, dba_hist_snapshot b
         where a.snap_id = b.snap_id
           and a.dbid = b.dbid
           and a.instance_number = b.instance_number
           and a.stat_name = 'DB time') t
where s.endsnap_id = t.endsnap_id
order by s.snap_date desc, time desc;
9.4   查看系统统计信息



select a.statistic#, a.value, a.name, b.name
  from V$SYSSTAT a, V$STATNAME b
where a.statistic# = b.statistic#
order by 2 desc;
十.数据库告警日志信息

十一.数据库补丁信息

补丁工具版本：

补丁名称：



[oracle@cjcos01 OPatch]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/OPatch
[oracle@cjcos01 OPatch]$ ./opatch lsinventory
十二.操作系统信息



[root@cjcos01 ~]# cat /etc/issue
Oracle Linux Server release 6.3
查看服务器型号
[root@cjcos01 ~]# dmidecode | grep "Product"
# 总核数 = 物理CPU个数 X 每颗物理CPU的核数
# 总逻辑CPU数 = 物理CPU个数 X 每颗物理CPU的核数 X 超线程数
# 查看物理CPU个数
cat /proc/cpuinfo| grep "physical id"| sort| uniq| wc -l
# 查看每个物理CPU中core的个数(即核数)
cat /proc/cpuinfo| grep "cpu cores"| uniq
# 查看逻辑CPU的个数
cat /proc/cpuinfo| grep "processor"| wc -l
# 查看CPU型号
# cat /proc/cpuinfo | grep 'model name' |uniq