巡检过程见我的另一篇博客: http://blog.itpub.net/29785807/viewspace-2699672/
巡检脚本
一.数据库基本状态和信息
-----1 数据库信息
select * from gv$version; select dbid,name,created,log_mode from gv$database; select version,instance_name,startup_time,status from gv$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 gv$instance;
二．数据库参数信息
---常见参数：
---v$parameter ---session
---v$system_parameter ---system
select name, value, display_value, isdefault
from v$system_parameter
where name in ('audit_trail',
'audit_sys_operations',
'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',
'large_pool_size',
'resource_limit',
'resource_manager_plan',
'max_dump_file_size',
'control_file_record_keep_time',
'result_cache_max_size',
'sec_case_sensitive_logon',
'local_listener')
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',
'_corrupted_rollback_segments',
'_datafile_write_errors_crash_instance',
'_gc_policy_time',
'_gc_undo_affinity',
'_gc_defer_time',
'_hash_join_enabled',
'_offline_rollback_segments',
'_px_use_large_pool',
'_memory_imm_mode_without_autosga',
'_partition_large_extents',
'_optimizer_null_aware_antijoin',
'_optim_peek_user_binds',
'_optimizer_mjc_enabled',
'_optimizer_use_feedback',
'_optimizer_join_elimination_enabled',
'_optimizer_ads_use_result_cache',
'_optimizer_adaptive_plans',
'_optimizer_adaptive_cursor_sharing',
'_optimizer_extended_cursor_sharing',
'_optimizer_extended_cursor_sharing_rel',
'_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/20200620.ctl';
---4.4日志文件信息
select a.group#, THREAD#, 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;
---归档文件
select * from v$archived_log;
select * from v$log_history;
---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;
4.11 ASM信息
[grid@rac01 ~]$ asmca
---磁盘组
select group_number, name, block_size, total_mb, free_mb, type, compatibility, voting_files from v$asm_diskgroup;
---磁盘信息
select GROUP_NUMBER, DISK_NUMBER, STATE, OS_MB, TOTAL_MB, FREE_MB, NAME, PATH, CREATE_DATE, MOUNT_DATE, VOTING_FILE, READS, WRITES from v$asm_disk order by 1,2;
4.12 OCR
[grid@rac01 ~]$ ocrcheck -config [root@rac01 bin]# ./ocrcheck [grid@rac01 ~]$ cluvfy comp ocr -n all-verbose
4.13 OLR
[root@rac01 bin]# ./ocrcheck -local
4.14 VOTEDISK
[root@rac01 bin]# ./crsctl query css votedisk
4.15
[root@rac01 bin]# ./oifcfg getif
五 .数据库状态信息
5.1 查看集群名
[root@rac01 bin]# ./cemutlo -n
5.2 查看集群状态
[root@rac01 bin]# ./crsctl check cluster -all
5.3 查看资源状态
[root@rac01 bin]# ./crsctl stat res -t
或
[root@rac01 bin]# ./crs_stat -t -v
5.4查看CRS状态
[root@rac01 bin]# ./crsctl check crs
5.5查看OHASD状态
[root@rac01 bin]# ./crsctl check has
5.6 查看节点信息
[root@rac01 bin]# ./olsnodes
rac01
rac02
5.7 查看数据库名称
[root@rac01 bin]# ./srvctl config database
cjcdb
[root@rac01 bin]# ./srvctl config database -d cjcdb
5.8 查看SCAN
[root@rac01 bin]# ./srvctl config scan
DNS
[root@rac01 bin]# nslookup rac-scan.cjc.com
状态
[root@rac01 bin]# ./srvctl status scan
5,9 查看LISTENER
配置
[root@rac01 bin]# ./srvctl config listener -a
状态
[root@rac01 bin]# ./srvctl status listener
5.10 查看节点信息
[root@rac01 bin]# ./olsnodes
[root@rac01 bin]# ./srvctl status nodeapps -n rac01
六．数据库连接信息
select inst_id, schemaname, count(*)
from gv$session
where schemaname in ('CJC', 'CHEN', 'SCOTT')
group by inst_id, 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;--关闭行移动*/
十 .性能
10.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;
10.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 2 DESC; select * from v$session_wait;
10.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;
10.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;
十一 .数据库告警日志信息
select * from v$diag_info;
十二 .数据库补丁信息
补丁工具版本：
补丁名称：
[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
迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！