巡检脚本

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

-----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